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 dbatoolsStorage
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 |
- Create one disk at a time: One-Piece Flow (Lean)- Must be done manually in VMware Workstation. Can be scripted in vCenter with PowerCLI.
- In VMware Workstation NVMe can only be added offline.
- Split virtual disk into multiple files.
- Do not allocate all space now.
- „MSSQL Workstation_<Drive>“
- All partitions are GPT.
- Only use Simple Volume.
- Quick format.
SQL Server Installation
- Connect VM CD-drive to SQL Server ISO-file
- Run SETUP.EXE
- Select „SQL Server Developer Edition“
- Select "Database Engine Services" and "Full-Text…"
- 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. - 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.
- 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.
- 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. - Collation tab: Use collation "
Latin_General_100 AS KS WS" maybe with UTF-8. - Add Current User as SQL Server administrator. Adding (Local) Administrators is not enough.
- Set Directories. Define seperate paths for
- Data
- TransactionLog
- Backup
- tempdb
where [tempdb] transaction log is with the other user database transaction logs. - Set tempdb data and log
- Set
MAXDOPto 4 - Set memory to Recommended
- Run installation
- Close SQL Server Installation
- Disconnect CD-drive
- Restart VM
SQL Server Update
- Copy latest CU to VM. I use „C:\temp\“
- Start PowerShell as Administrator. That is Microsoft PowerShell (v7) not Windows PowerShell (v5)
- Change directory to „C:\temp\“
- Run the SQL Server CU exe-file
- Complete SQL Server CU installation
- Delete SQL Server CU exe-file from „C:\temp\“
- Restart VM
SSMS Installation
- Copy latest SSMS installation (
SSMS-Setup-ENU.exe) to „C:\temp\“ - Start PowerShell as Administrator
- Change directory to „C:\temp\“
- Run SSMS Setup exe-file
- Complete SSMS installation
- Delete SSMS Setup exe-file from „C:\temp\“
- Start SSMS
- Cancel suggested connection
- Pin SSMS to taskbar
- View Registered Servers (Ctrl + Alt + G)
- 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
- 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
- Download Plan Explorer from www.solarwinds.com/free-tools/plan-explorer. This requires registration with e-mail and phone#.
- Start Plan Explorer
- 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.
Posts
