SQL Server Workstation

Some notes from a SQL Server 2022 Developer Edition installation on a Windows 11 Pro workstation. The infrastructure is VMware Workstation Pro.

General Preperations

If you clone an exiting VM then you should rename the new SQL Server workstation to something different. And do it before anything else.

As I usually has a sandbox for different versions of SQL Server the name could something like "MSSQL 2022 Workstation". Consider if you should differ between sandbox, workstation, devbox or anything else.

Make sure the Windows installation is up to date.

PowerShell

Install PowerShell and then Windows Terminal from Microsoft Store.

Open Windows Terminal and pin it to the task bar. Then close Windows Terminal.

PS > Install-Module -Name SqlServer
PS > Install-Module -Name dbatools

Storage

Move CD-ROM to Z–drive.

Drive	Name	Size	NTFS	Path	Comments
C	System	64 GB	default		Windows and tools (SSMS)
P	Program	64 GB	default		SQL Server
D	Data	64 GB	64 KB		User database (mdf & ndf)
L	Translog	32 GB	64 KB		User db & tempdb (ldf)
T	tempdb	32 GB	64 KB		tempdb (mdf & ndf)
X	Backup	128 GB	64 KB		Full, diff & log backup

1. Create one disk at a time: One-Piece Flow (Lean)- Must be done manually in VMware Workstation. Can be scripted in vCenter with PowerCLI.
2. In VMware Workstation NVMe can only be added offline.
3. Split virtual disk into multiple files.
4. Do not allocate all space now.
5. „MSSQL Workstation_<Drive>“
6. All partitions are GPT.
7. Only use Simple Volume.
8. Quick format.

SQL Server Installation

1. Connect VM CD-drive to SQL Server ISO-file
2. Run SETUP.EXE
3. Select „SQL Server Developer Edition“
4. Select "Database Engine Services" and "Full-Text…"
5. Place "Instance Root Directory" on another physical drive than Windows, e.g. "P:\Program_Files\Microsoft_SQL_Server\". This is to spread preassure on (local) storage units.
6. Name the (Database Engine) instance, e.g. „SSDB00“. This will allow side-by-side installation of different SQL Server versions. On the other hand I usually does not recommend instanse name with version information. The instance name in shared installations should be generic – like a dumb database key.
7. Change Service Startup Type to "Manual". When running several instances this will help you control local resources on your workstation. With virtual workstations I usually create one VM to each instance.
8. Set „Grant Perform Volume Maintenance Task priviledge to SQL Server Database Engine Service“. This is to speed up database file (auto-)growths which might be when expanding the physical data model. This is really relevant on a workstation where you experiment. On the other hand I would never use Auto Shrink (AUTO_SHRINK) even in a sandbox.
9. Collation tab: Use collation "Latin_General_100 AS KS WS" maybe with UTF-8.
10. Add Current User as SQL Server administrator. Adding (Local) Administrators is not enough.
11. Set Directories. Define seperate paths for
• Data
• TransactionLog
• Backup
• tempdb
  where [tempdb] transaction log is with the other user database transaction logs.
12. Set tempdb data and log
13. Set MAXDOP to 4
14. Set memory to Recommended
15. Run installation
16. Close SQL Server Installation
17. Disconnect CD-drive
18. Restart VM

SQL Server Update

1. Copy latest CU to VM. I use „C:\temp\“
2. Start PowerShell as Administrator. That is Microsoft PowerShell (v7) not Windows PowerShell (v5)
3. Change directory to „C:\temp\“
4. Run the SQL Server CU exe-file
5. Complete SQL Server CU installation
6. Delete SQL Server CU exe-file from „C:\temp\“
7. Restart VM

SSMS Installation

1. Copy latest SSMS installation (SSMS-Setup-ENU.exe) to „C:\temp\“
2. Start PowerShell as Administrator
3. Change directory to „C:\temp\“
4. Run SSMS Setup exe-file
5. Complete SSMS installation
6. Delete SSMS Setup exe-file from „C:\temp\“
7. Start SSMS
8. Cancel suggested connection
9. Pin SSMS to taskbar
10. View Registered Servers (Ctrl + Alt + G)
11. Select properties on SSDB00
• Server name: (local)\SSDB00
• Trust server certificate
• Registered server name: SSDB00
• Connection Properties tab…
• Connect to database: tempdb
• Connection time-out: 5 seconds
• Use custom color: green
• Save
12. View Object Explorer Details (F7)

ODBC

If SQL Server ODBC 17 is already installed, it will most likely generate a error like "The feature you are trying to use is on a network resource that is unavailable.".
The solution is to uninstall SQL Server ODBC 17. This can be done by Control Panel > Remove Program.

SSDB Configuration

Query: ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT;

Query:

EXECUTE master.sys.sp_configure
	@configname = N'remote access',
	@configvalue = N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO

Query:

EXECUTE master.dbo.xp_create_subdir N'X:\MSSQL\Backup';

Query:

EXECUTE master.sys.sp_configure
	@configname = N'backup compression default',
	@configvalue = N'1';
GO
EXECUTE master.sys.sp_configure
	@configname = N'backup checksum default',
	@configvalue = N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.dbo.xp_instance_regwrite
	@rootkey = N'HKEY_LOCAL_MACHINE',
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
	@value_name = N'BackupDirectory',
	@type = N'REG_SZ',
	@value = N'X:\MSSQL\Backup';
GO

Query:

EXECUTE master.sys.sp_configure
	@configname = N'show advanced options',
	@configvalue = N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.sys.sp_configure
	@configname = N'cost threshold for parallelism',
	@configvalue = N'50';
EXECUTE master.sys.sp_configure
	@configname = N'backup compression default',
	@configvalue = N'1';
EXECUTE master.sys.sp_configure
	@configname = N'backup checksum default',
	@configvalue = N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.sys.sp_configure
	@configname = N'show advanced options',
	@configvalue = N'0';
RECONFIGURE WITH OVERRIDE;
GO

AdventureWorks Installation

See SQLAdmin post on Restore AdventureWorks on SQL Server 2019.

;

Plan Explorer Installation

1. Download Plan Explorer from www.solarwinds.com/free-tools/plan-explorer. This requires registration with e-mail and phone#.
2. Start Plan Explorer
3. Pin to Start

History

2024-10-23 – Details added.
2024-09-22 – Element on administrators added. And a few typhographic corrections.
2024-09-09 – Post created.