{"id":1152,"date":"2026-11-04T10:26:13","date_gmt":"2026-11-04T10:26:13","guid":{"rendered":"https:\/\/www.vps.tc\/blog\/?p=1152"},"modified":"2026-11-04T09:30:58","modified_gmt":"2026-11-04T09:30:58","slug":"windows-server-sql-server-installation-initial-settings","status":"publish","type":"post","link":"https:\/\/www.vps.tc\/blog\/en\/windows-server-sql-server-installation-initial-settings\/","title":{"rendered":"How to Install Microsoft SQL Server on Windows Server"},"content":{"rendered":"<div class=\"aiw-summary\" id=\"aiw-ozet\">\n<p class=\"aiw-summary-title\">Quick Summary &#8211; Installing SQL Server on Windows Server<\/p>\n<p>The hypothetical SQL01 lab plans a SQL Server 2022 Developer installation on Windows Server 2022. The goal is not just a running service, but restricted access and recoverable data.<\/p>\n<ul>\n<li><strong>Environment and licensing<\/strong> \u2014 4 vCPUs and 8 GB RAM are an example starting capacity; Developer is for development and testing only and does not license production use.<\/li>\n<li><strong>Minimal installation scope<\/strong> \u2014 Install only Database Engine Services, using the default MSSQLSERVER instance and the NT SERVICE\\MSSQLSERVER service account.<\/li>\n<li><strong>Limited application permissions<\/strong> \u2014 Use Windows authentication for local administration; if SQL authentication is required, enable mixed mode and grant the application only the necessary object permissions.<\/li>\n<li><strong>Measurable starting settings<\/strong> \u2014 This lab uses 4096 MB for max server memory, four equally sized tempdb data files, and one tempdb log file as starting settings.<\/li>\n<li><strong>Protected remote connections<\/strong> \u2014 Restrict static TCP 1433 access to application and management IP addresses, and use Mandatory encryption with a verifiable TLS certificate for SSMS connections.<\/li>\n<li><strong>Proof of recovery<\/strong> \u2014 Keep full and transaction log backups off-server as well, and do not consider the backup process complete until you restore to a separate database.<\/li>\n<\/ul>\n<\/div>\n<p class=\"aiw-lead\">A running SQL Server service does not mean setup is finished. To install SQL Server on Windows Server, prepare licensing and storage, install the Database Engine, assign an administrator, and apply updates. Before use, verify memory settings, TLS-protected TCP access, and backup recovery. These examples describe a hypothetical, non-production Windows Server 2022 lab.<\/p>\n<div class=\"aiw-toc\" style=\"border:1px solid #dbe3ea;border-radius:8px;padding:16px 20px;margin:0 0 28px\">\n<p class=\"aiw-toc-head\"><strong>Table of Contents<\/strong><span class=\"aiw-toc-meta\"> \u00b7 18 min read<\/span><\/p>\n<ol style=\"margin:10px 0 0;padding-left:22px\">\n<li><a href=\"#check-vps-resources-storage-and-licensing-before-installation\">Check VPS Resources, Storage, and Licensing Before Installation<\/a><\/li>\n<li><a href=\"#install-the-sql-server-2022-database-engine\">Install the SQL Server 2022 Database Engine<\/a><\/li>\n<li><a href=\"#choose-windows-authentication-or-mixed-mode\">Choose Windows Authentication or Mixed Mode<\/a><\/li>\n<li><a href=\"#set-initial-memory-database-file-and-tempdb-options\">Set Initial Memory, Database File, and tempdb Options<\/a><\/li>\n<li><a href=\"#restrict-tcp-1433-access-with-tls-and-firewall-rules\">Restrict TCP 1433 Access with TLS and Firewall Rules<\/a><\/li>\n<li><a href=\"#verify-the-ssms-connection-layer-by-layer\">Verify the SSMS Connection Layer by Layer<\/a><\/li>\n<li><a href=\"#complete-the-first-backup-with-a-restore-test\">Complete the First Backup with a Restore Test<\/a><\/li>\n<li><a href=\"#check-before-connecting-your-application\">Check Before Connecting Your Application<\/a><\/li>\n<li><a href=\"#frequently-asked-questions\">Frequently Asked Questions<\/a><\/li>\n<li><a href=\"#sources\">Sources<\/a><\/li>\n<\/ol>\n<\/div>\n<h2 id=\"check-vps-resources-storage-and-licensing-before-installation\">Check VPS Resources, Storage, and Licensing Before Installation<\/h2>\n<p>An installer opening proves little. I would rather catch a missing volume now than move database files later.<\/p>\n<p>The hypothetical, non-production <code>SQL01<\/code> lab uses Windows Server 2022 Standard Desktop Experience 21H2, build family 20348, with 4 vCPU and 8 GB RAM. Microsoft Learn&#8217;s SQL Server 2022 requirements support this OS and require x64: minimum RAM for non-Express editions is 1 GB; minimum free disk space is 6 GB. These are installation minimums, not sizing targets. The lab&#8217;s starting capacity needs headroom for Windows, data, logs, and backups.<\/p>\n<h3>License for the intended use<\/h3>\n<p>Microsoft&#8217;s edition comparison identifies Developer as development\/test only, not licensed for production. This lab uses SQL Server 2022 Developer 16.x, default instance <code>MSSQLSERVER<\/code>, and only Database Engine Services. Express permits free production use within CPU\/memory restrictions and a 10 GB limit per relational database. Assess Standard or Enterprise production licensing separately; Windows Server licensing does not cover SQL Server.<\/p>\n<p>No Active Directory is present. Sign into SQL01 with an authorized local account; open PowerShell as administrator. This read-only example checks elevation, OS, resources, volumes, and directories:<\/p>\n<pre><code>$identity = [Security.Principal.WindowsIdentity]::GetCurrent()\n$principal = [Security.Principal.WindowsPrincipal]::new($identity)\n$principal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)\n\nGet-CimInstance Win32_OperatingSystem |\n    Select-Object CSName, Caption, Version, BuildNumber, OSArchitecture\n\nGet-CimInstance Win32_ComputerSystem |\n    Select-Object NumberOfLogicalProcessors,\n        @{Name='RAM_GiB'; Expression={[math]::Round($_.TotalPhysicalMemory \/ 1GB, 2)}}\n\nGet-Volume -DriveLetter C,D,L,T,B -ErrorAction Stop |\n    Select-Object DriveLetter, FileSystem, HealthStatus,\n        @{Name='Free_GiB'; Expression={[math]::Round($_.SizeRemaining \/ 1GB, 2)}}\n\n'C:\\Install\\SQL2022','D:\\SQL\\Data','L:\\SQL\\Log',\n'T:\\SQL\\TempDB','B:\\SQL\\Backup' | ForEach-Object {\n    [pscustomobject]@{\n        Path = $_\n        Exists = Test-Path -LiteralPath $_ -PathType Container\n    }\n}<\/code><\/pre>\n<p>Expected results: elevation <code>True<\/code>, four logical processors, approximately 8 GiB RAM, and SQL01 reporting Windows Server 2022 Standard, 64-bit, build <code>20348<\/code>. Pre-created volumes should report NTFS and Healthy. Compare free space with projected growth; separate drive letters do not guarantee physical I\/O isolation. Directory <code>False<\/code> indicates a missing folder; <code>True<\/code> does not prove write permission.<\/p>\n<p>Get installation media and the current approved CU from Microsoft; verify the CU number and full build before publication. Review Windows Update status and setup-rule restart warnings. Complete required restarts first.<\/p>\n<p>Back up the existing system before installation. Follow the recovery preparation in <a href=\"https:\/\/www.vps.tc\/blog\/en\/windows-server-backup-methods-scheduling-recovery\/\">Windows Server Backup: Methods, Scheduling, and Recovery<\/a>; a VPS snapshot alone is insufficient. I would not start setup without confirming access to the backup and the provider&#8217;s console or recovery environment.<\/p>\n<div class=\"aiw-callout aiw-callout-warn\">\n<p class=\"aiw-callout-label\">Caution<\/p>\n<p>SQL Server 2022 Developer does not license production workloads; owning the VPS does not change that. The example&#039;s 4 vCPUs and 8 GB RAM are neither Microsoft&#039;s minimum requirements nor a guarantee of adequate capacity for every application.<\/p>\n<\/div>\n<h2 id=\"install-the-sql-server-2022-database-engine\">Install the SQL Server 2022 Database Engine<\/h2>\n<p>I keep component selection narrow: every extra adds maintenance. Start small. This hypothetical, non-production SQL01 lab uses 4 vCPU, 8 GB RAM, and Windows Server 2022 Standard Desktop Experience 21H2 (20348 build family). Microsoft Learn&#8217;s requirements document confirms OS support for SQL Server 2022.<\/p>\n<p>Prepare Microsoft-sourced media in <code>C:\\Install\\SQL2022<\/code>. With SQL01&#8217;s authorized local account, open elevated PowerShell to inspect the installer signature:<\/p>\n<pre><code>Get-AuthenticodeSignature -FilePath 'C:\\Install\\SQL2022\\setup.exe' |\n    Format-List Status, StatusMessage, SignerCertificate<\/code><\/pre>\n<p>Expect <code>Valid<\/code> and Microsoft as signer. This checks only that file, not the entire media. Stop if verification fails. Run <code>setup.exe<\/code> as administrator; under Installation, choose a new standalone SQL Server installation. Resolve prerequisite errors.<\/p>\n<p>Select <strong>Developer<\/strong>. Microsoft Learn&#8217;s edition documentation permits development and testing, not production; assess production licensing separately. Under Feature Selection, select only <strong>Database Engine Services<\/strong>. In Instance Configuration, choose Default instance: <code>MSSQLSERVER<\/code>.<\/p>\n<p>Under Server Configuration, retain the engine&#8217;s virtual account, <code>NT SERVICE\\MSSQLSERVER<\/code>. Match collation to application requirements, including Turkish-character comparisons and case sensitivity. Under Database Engine Configuration, select Windows Authentication. SQL01 has no Active Directory: click <strong>Add Current User<\/strong> to explicitly make your authorized local account a SQL Server administrator.<\/p>\n<p>In Data Directories, set user data to <code>D:\\SQL\\Data<\/code>, logs to <code>L:\\SQL\\Log<\/code>, and local backups to <code>B:\\SQL\\Backup<\/code>. On the TempDB tab, set both directories to <code>T:\\SQL\\TempDB<\/code>. These are pre-created NTFS volumes; separate drive letters do not guarantee physical I\/O isolation.<\/p>\n<p>Finish installation, then apply Microsoft&#8217;s current, approved cumulative update (CU). Verify its number and full build before publication. Restart if required; check the service in SQL01&#8217;s PowerShell:<\/p>\n<pre><code>Get-Service -Name MSSQLSERVER | Select-Object Name, Status<\/code><\/pre>\n<p>Expect <code>Running<\/code>. Through an authorized SQL connection, record the version:<\/p>\n<pre><code>SELECT\n    SERVERPROPERTY('ProductVersion') AS ProductVersion,\n    SERVERPROPERTY('Edition') AS Edition,\n    SERVERPROPERTY('InstanceName') AS InstanceName;<\/code><\/pre>\n<p><code>ProductVersion<\/code> should match the approved full <code>16.x<\/code> build; <code>Edition<\/code> should identify Developer. The default instance returns <code>NULL<\/code> for <code>InstanceName<\/code>. If installation fails, inspect <code>Summary.txt<\/code> under <code>C:\\Program Files\\Microsoft SQL Server\\160\\Setup Bootstrap\\Log<\/code> and <code>Detail.txt<\/code> in that run&#8217;s timestamped subfolder before retrying.<\/p>\n<div class=\"aiw-callout aiw-callout-example\">\n<p class=\"aiw-callout-label\">Example<\/p>\n<p>This is a hypothetical lab check, not a real incident. If setup fails on SQL01, inspect Summary.txt and the relevant installation&#039;s Detail.txt under C:\\Program Files\\Microsoft SQL Server\\160\\Setup Bootstrap\\Log. No test execution or observed error output is claimed.<\/p>\n<\/div>\n<h2 id=\"choose-windows-authentication-or-mixed-mode\">Choose Windows Authentication or Mixed Mode<\/h2>\n<p>I would keep the default Windows Authentication during setup. Manage SQL01 locally with the authorized local Windows account added as a SQL Server administrator during installation. Being a Windows administrator does not automatically grant SQL Server <code>sysadmin<\/code> privileges.<\/p>\n<p>This hypothetical, non-production lab has no Active Directory. SQL01 does not automatically trust your Windows 11 client account; matching usernames are insufficient. Remote SSMS 20.2 connections follow the optional Mixed Mode branch. Microsoft Learn&#8217;s edition documentation confirms SQL Server 2022 Developer is not licensed for production; assess production licensing separately.<\/p>\n<p>If you need SQL authentication, select Mixed Mode during setup. To switch later, open SSMS server properties, then Security, and select <strong>SQL Server and Windows Authentication mode<\/strong>. Restart the Database Engine service-<code>MSSQLSERVER<\/code> for this default instance-to apply it. Schedule the interruption: existing connections will disconnect. Mixed Mode retains Windows Authentication without requiring <code>sa<\/code> to be enabled.<\/p>\n<p>Keep privileges narrow. Before this example, use an authorized administrator connection in SSMS to create SQL login <code>AppLogin<\/code>. Generate its password in a password manager, store it in a secure vault, leave password-policy checking enabled, and assign no additional server roles. Keep real passwords out of query files and application accounts separate from human administrators.<\/p>\n<p>Run this T-SQL once as an authorized administrator. It creates the not-yet-existing <code>AppDb<\/code> and a hypothetical work-item table, maps the application user to its login, and adds it to a custom role with only table-level read and insert permissions.<\/p>\n<pre><code>USE master;\nGO\nCREATE DATABASE AppDb;\nGO\nUSE AppDb;\nGO\nCREATE TABLE dbo.WorkItems\n(\n    WorkItemId int IDENTITY(1,1) NOT NULL PRIMARY KEY,\n    Title nvarchar(200) NOT NULL\n);\nGO\nCREATE USER AppUser FOR LOGIN AppLogin;\nCREATE ROLE AppWriter AUTHORIZATION dbo;\nGRANT SELECT, INSERT ON OBJECT::dbo.WorkItems TO AppWriter;\nALTER ROLE AppWriter ADD MEMBER AppUser;\nGO\nEXECUTE AS USER = 'AppUser';\nSELECT\n    HAS_PERMS_BY_NAME('dbo.WorkItems', 'OBJECT', 'SELECT') AS CanSelect,\n    HAS_PERMS_BY_NAME('dbo.WorkItems', 'OBJECT', 'INSERT') AS CanInsert,\n    HAS_PERMS_BY_NAME('dbo.WorkItems', 'OBJECT', 'DELETE') AS CanDelete;\nREVERT;\nGO<\/code><\/pre>\n<p>Without other permissions, expect <code>1<\/code>, <code>1<\/code>, <code>0<\/code>: read and insert allowed, no delete permission. This checks database-user permissions, not remote connectivity or passwords. <code>REVERT<\/code> restores the previous execution context. Do not use <code>sa<\/code>, <code>sysadmin<\/code>, or <code>db_owner<\/code> for application connections.<\/p>\n<p>Authentication does not replace transport encryption; configure TLS separately following Microsoft Learn&#8217;s connection-encryption documentation. If the application needs updates later, grant <code>UPDATE<\/code> only on the required object, not broad database permissions.<\/p>\n<h2 id=\"set-initial-memory-database-file-and-tempdb-options\">Set Initial Memory, Database File, and tempdb Options<\/h2>\n<p>Windows still needs RAM. Leave room. On hypothetical, non-production SQL01 with 8 GB RAM, I&#8217;d start <code>max server memory<\/code> at <strong>4096 MB<\/strong>, adjusting after measurement. This neither caps every SQL Server process allocation nor reserves all remaining memory for Windows.<\/p>\n<p>Microsoft Learn recommends at least 4 GB RAM for non-Express SQL Server 2022 editions-a hardware recommendation, not an engine setting. Microsoft&#8217;s edition documentation restricts Developer to development and testing; production licensing needs separate consideration.<\/p>\n<p>In SSMS 20.2, connect with sufficient privileges to SQL01&#8217;s default <code>MSSQLSERVER<\/code> instance. These commands configure memory and report its effective value, default paths, and existing files.<\/p>\n<pre><code>USE master;\nGO\nEXEC sp_configure 'show advanced options', 1;\nRECONFIGURE;\nEXEC sp_configure 'max server memory (MB)', 4096;\nRECONFIGURE;\nGO\nSELECT name, value, value_in_use\nFROM sys.configurations\nWHERE name = 'max server memory (MB)';\n\nSELECT\n    SERVERPROPERTY('InstanceDefaultDataPath') AS default_data_path,\n    SERVERPROPERTY('InstanceDefaultLogPath') AS default_log_path;\n\nSELECT\n    DB_NAME(database_id) AS database_name,\n    name AS logical_name,\n    type_desc,\n    physical_name,\n    size \/ 128.0 AS size_mb,\n    is_percent_growth,\n    CASE WHEN is_percent_growth = 0\n         THEN growth \/ 128.0 END AS growth_mb\nFROM sys.master_files\nWHERE database_id = 2 OR database_id &gt; 4\nORDER BY database_id, file_id;<\/code><\/pre>\n<p>Expect <code>value_in_use<\/code> to show <code>4096<\/code> without restarting. Default data and log paths should be <code>D:\\SQL\\Data<\/code> and <code>L:\\SQL\\Log<\/code>. Check <code>physical_name<\/code> too: changing defaults does not relocate files. Fresh installations may have no user-database rows.<\/p>\n<h3>Start with equally sized tempdb data files<\/h3>\n<p>For this 4 vCPU lab, use <strong>four equally sized data files and one log file<\/strong> under <code>T:\\SQL\\TempDB<\/code>. Example sizing: 256 MB initially per file, with fixed 128 MB growth. Verify free space first; actual capacity depends on workload.<\/p>\n<p>Count existing files in SSMS under <code>tempdb<\/code> properties, Files. Already have four data files? Don&#8217;t add more. Equalize data-file sizes and growth increments without shrinking larger files to fit the example. Use fixed MB growth for user databases too; 128 MB isn&#8217;t universal.<\/p>\n<p>Rerun the query: <code>is_percent_growth=0<\/code> and <code>growth_mb=128<\/code> indicate the example&#8217;s fixed growth. Changing <code>tempdb<\/code> paths requires a service restart; verify necessary destination file access for <code>NT SERVICE\\MSSQLSERVER<\/code> beforehand. Separate drive letters don&#8217;t guarantee physical I\/O isolation.<\/p>\n<p>I&#8217;d leave <code>MAXDOP<\/code> and <code>cost threshold for parallelism<\/code> unchanged pending measurement. Record current settings; monitor CPU usage, waits, disk latency, and memory pressure under representative load. Pre-size files for expected usage rather than treating autogrowth as capacity planning.<\/p>\n<h2 id=\"restrict-tcp-1433-access-with-tls-and-firewall-rules\">Restrict TCP 1433 Access with TLS and Firewall Rules<\/h2>\n<p>TLS encrypts traffic; firewalls restrict who connects. I would use both. This hypothetical, nonproduction SQL01 lab runs the default <code>MSSQLSERVER<\/code> instance on Windows Server 2022. Microsoft&#8217;s edition documentation confirms SQL Server 2022 Developer is not licensed for production.<\/p>\n<p>In SQL Server Configuration Manager, enable <strong>TCP\/IP<\/strong> under <strong>SQL Server Network Configuration \u2192 Protocols for MSSQLSERVER<\/strong>. With <strong>Listen All = Yes<\/strong>, <strong>IPAll<\/strong> controls ports: clear <strong>TCP Dynamic Ports<\/strong>, then set <strong>TCP Port<\/strong> to <code>1433<\/code>. Dynamic-port value <code>0<\/code> requests allocation, not disabling. Clear other IP sections&#8217; dynamic-port values too.<\/p>\n<p>Following Microsoft Learn&#8217;s encryption instructions, import a private-CA certificate and private key into <strong>Local Computer \u2192 Personal<\/strong>. The valid certificate must support <strong>Server Authentication<\/strong>, include SAN <code>sql01.lab.example.com<\/code>, and meet Microsoft&#8217;s key-provider requirements. Establish CA-chain trust on both clients; without Active Directory, do not expect automatic certificate distribution.<\/p>\n<p>Under <strong>Manage Private Keys<\/strong>, grant <code>NT SERVICE\\MSSQLSERVER<\/code> private-key read access. In Configuration Manager&#8217;s protocol properties, select it under <strong>Certificate<\/strong>; set <strong>Flags \u2192 Force Encryption = Yes<\/strong>. TCP and certificate changes require an engine restart, disconnecting existing sessions.<\/p>\n<p>This example requires elevated PowerShell on SQL01. Replace documentation-only <code>example.com<\/code> and TEST-NET-1 addresses with actual private-network values.<\/p>\n<pre><code>New-NetFirewallRule -Name 'SQL01-TCP1433-Clients' `\n  -DisplayName 'SQL01 TCP 1433 approved clients' `\n  -Direction Inbound -Action Allow -Protocol TCP `\n  -LocalAddress 192.0.2.10 -LocalPort 1433 `\n  -RemoteAddress 192.0.2.20,192.0.2.30 `\n  -Profile Any -Enabled True |\n  Format-Table DisplayName, Enabled, Direction, Action\n\nRestart-Service -Name MSSQLSERVER<\/code><\/pre>\n<p>The rule allows application client <code>192.0.2.20<\/code> and management client <code>192.0.2.30<\/code>. Expect <code>Enabled=True<\/code>, <code>Direction=Inbound<\/code>, and <code>Action=Allow<\/code>; this table does not verify TLS. Keep default inbound blocking and remove broader SQL allowances. At the provider firewall, allow TCP 1433 only from these same sources, never the general internet.<\/p>\n<p>In Windows 11&#8217;s SSMS 20.2, use <code>tcp:sql01.lab.example.com,1433<\/code>, <strong>Encryption=Mandatory<\/strong>, and unchecked <strong>Trust server certificate<\/strong>. Keep application certificate validation enabled too. Specifying the static port removes the need for SQL Server Browser discovery: disable Browser in this lab and leave UDP 1434 closed.<\/p>\n<div class=\"aiw-callout aiw-callout-tip\">\n<p class=\"aiw-callout-label\">Tip<\/p>\n<p>A direct connection using static TCP port 1433 does not require SQL Server Browser or UDP 1434 access. sql01.lab.example.com and 192.0.2.10, 192.0.2.20, and 192.0.2.30 are documentation placeholders; replace them with your actual private-network hostname and addresses. The certificate&#039;s SAN must match the actual DNS name the client uses.<\/p>\n<\/div>\n<h2 id=\"verify-the-ssms-connection-layer-by-layer\">Verify the SSMS Connection Layer by Layer<\/h2>\n<p>I would not diagnose this from an SSMS error dialog alone. Separate the layers. In the hypothetical, non-production SQL01 lab, check DNS, then TCP from the Windows 11 management client (<code>192.0.2.30<\/code>). Replace documentation-only <code>example.com<\/code> and TEST-NET-1 addresses with your private network values. Microsoft Learn&#8217;s editions documentation confirms SQL Server 2022 Developer is not licensed for production.<\/p>\n<pre><code>Resolve-DnsName -Name sql01.lab.example.com -Type A\nTest-NetConnection -ComputerName sql01.lab.example.com -Port 1433 -InformationLevel Detailed<\/code><\/pre>\n<p>Expect <code>192.0.2.10<\/code> and <code>TcpTestSucceeded : True<\/code>, not recorded test results. If the address differs, check the DNS record and client resolver. TCP success proves port reachability, not SQL authentication or certificate validity. Unanswered ping does not establish TCP failure.<\/p>\n<p>In SSMS 20.2, use <code>tcp:sql01.lab.example.com,1433<\/code>, set <code>Encryption<\/code> to <code>Mandatory<\/code>, and leave <code>Trust server certificate<\/code> unchecked. With no AD, this remote example follows the optional mixed-mode branch: select <code>SQL Server Authentication<\/code>. Microsoft Learn&#8217;s encryption documentation explains that the client must trust the certificate chain and the certificate must match the connection&#8217;s server name.<\/p>\n<p>This password-free <code>Microsoft.Data.SqlClient<\/code> string requires a username and password supplied separately through secure secret management; without credentials, it cannot connect.<\/p>\n<pre><code>Server=tcp:sql01.lab.example.com,1433;Database=master;Integrated Security=False;Encrypt=True;TrustServerCertificate=False;<\/code><\/pre>\n<ul>\n<li><strong>Connection refused:<\/strong> Check SQL01&#8217;s <code>MSSQLSERVER<\/code> service, TCP\/IP enablement, and TCP 1433 listener.<\/li>\n<li><strong>Timeout:<\/strong> Check routing, VPS network rules, and Windows Firewall&#8217;s TCP 1433 allowance from <code>192.0.2.30<\/code>.<\/li>\n<li><strong>Certificate trust error:<\/strong> Fix the trust chain, validity dates, and FQDN match-not by disabling validation.<\/li>\n<li><strong>18456:<\/strong> Check mixed mode, SQL account status, password, and target database access. Read SQL ERRORLOG for the detailed reason and state code.<\/li>\n<\/ul>\n<p>These queries read the current SQL ERRORLOG and this session&#8217;s encryption status. SQL Server 2022 requires <code>VIEW ANY ERROR LOG<\/code> and <code>VIEW SERVER PERFORMANCE STATE<\/code>, respectively. I would not grant broad diagnostic privileges to the application account.<\/p>\n<pre><code>EXEC master.sys.sp_readerrorlog 0, 1;\n\nSELECT session_id, net_transport, encrypt_option, auth_scheme\nFROM sys.dm_exec_connections\nWHERE session_id = @@SPID;<\/code><\/pre>\n<p>If remote access fails, inspect the log using SQL01&#8217;s authorized local Windows account. Expect <code>net_transport = TCP<\/code> and <code>encrypt_option = TRUE<\/code> remotely; encryption alone does not prove certificate validation. Local success cannot substitute for the remote connection: after fixing it, rerun the query in SSMS on the management client.<\/p>\n<h2 id=\"complete-the-first-backup-with-a-restore-test\">Complete the First Backup with a Restore Test<\/h2>\n<p>A backup file exists. Not finished. In SQL01&#8217;s hypothetical, non-production SQL Server 2022 Developer lab, I would accept <code>AppDb<\/code> only after a separate restore and data checks. These commands are illustrative, not tested. Microsoft Learn&#8217;s edition documentation excludes production use from Developer licensing; assess production licensing separately.<\/p>\n<p>Microsoft Learn&#8217;s backup documentation specifies a data backup after switching to <code>FULL<\/code> to start the log backup chain. The first full backup supplies it. Our hypothetical schedule: <strong>full backups every 24 hours, transaction log backups every 15 minutes<\/strong>. Choose actual frequency by acceptable data loss. Full backups cannot replace log backups; missing log backups can cause growth, and other factors can prevent log-space reuse.<\/p>\n<p>Verify <code>NT SERVICE\\MSSQLSERVER<\/code> can create\/read backups in <code>B:\\SQL\\Backup<\/code> and create destination data\/log files. Never grant Everyone full control. Existing <code>AppDb<\/code> must have exactly one data and one log file; logical names come from the catalog. Destination files must not exist. Run the block together to preserve variable scope.<\/p>\n<pre><code>USE master;\n\nIF DB_ID(N'AppDb_RestoreTest') IS NOT NULL\n    THROW 50000, N'AppDb_RestoreTest zaten var; i\u015flem durduruldu.', 1;\n\nIF (SELECT COUNT(*) FROM sys.master_files\n    WHERE database_id = DB_ID(N'AppDb')) &lt;&gt; 2\n    THROW 50001, N'Bu \u00f6rnek yaln\u0131zca iki dosyal\u0131 AppDb i\u00e7indir.', 1;\n\nDECLARE @dataLogical sysname, @logLogical sysname;\nSELECT @dataLogical = name\nFROM sys.master_files\nWHERE database_id = DB_ID(N'AppDb') AND type = 0;\nSELECT @logLogical = name\nFROM sys.master_files\nWHERE database_id = DB_ID(N'AppDb') AND type = 1;\n\nIF @dataLogical IS NULL OR @logLogical IS NULL\n    THROW 50002, N'Bir veri ve bir g\u00fcnl\u00fck dosyas\u0131 gerekli.', 1;\n\nALTER DATABASE [AppDb] SET RECOVERY FULL;\n\nDECLARE @id nvarchar(36) = CONVERT(nvarchar(36), NEWID());\nDECLARE @full nvarchar(260) =\n    N'B:\\SQL\\Backup\\AppDb_' + @id + N'_full.bak';\nDECLARE @log nvarchar(260) =\n    N'B:\\SQL\\Backup\\AppDb_' + @id + N'_log.trn';\n\nBACKUP DATABASE [AppDb] TO DISK = @full\nWITH CHECKSUM, STATS = 10;\nBACKUP LOG [AppDb] TO DISK = @log\nWITH CHECKSUM, STATS = 10;\n\nSELECT @full AS FullBackupPath, @log AS LogBackupPath;\nRESTORE VERIFYONLY FROM DISK = @full WITH CHECKSUM;\nRESTORE VERIFYONLY FROM DISK = @log WITH CHECKSUM;\nRESTORE FILELISTONLY FROM DISK = @full;\n\nRESTORE DATABASE [AppDb_RestoreTest]\nFROM DISK = @full\nWITH MOVE @dataLogical TO N'D:\\SQL\\Data\\AppDb_RestoreTest.mdf',\n     MOVE @logLogical TO N'L:\\SQL\\Log\\AppDb_RestoreTest_log.ldf',\n     NORECOVERY, CHECKSUM, STATS = 10;\n\nRESTORE LOG [AppDb_RestoreTest]\nFROM DISK = @log\nWITH RECOVERY, CHECKSUM, STATS = 10;\n\nDBCC CHECKDB (N'AppDb_RestoreTest') WITH NO_INFOMSGS;\nSELECT name, state_desc\nFROM sys.databases\nWHERE name = N'AppDb_RestoreTest';<\/code><\/pre>\n<p>Unique names avoid overwriting backups. <code>FILELISTONLY<\/code> lists files; <code>MOVE<\/code> redirects paths. <code>NORECOVERY<\/code> leaves restoration pending until log application with <code>RECOVERY<\/code> opens the database. Expect <code>ONLINE<\/code> without <code>CHECKDB<\/code> consistency errors. Microsoft Learn cautions that <code>RESTORE VERIFYONLY<\/code> cannot replace restoration. <code>CHECKSUM<\/code> cannot prove application-data correctness either: check critical tables and application reads.<\/p>\n<p>Set SQL Server Agent to start automatically. Schedule separate full\/log backup jobs without restore-test commands. Configure failure notifications to an operator through Database Mail; confirm delivery. Independently monitor Agent stoppages and overdue backups. This immediate log backup creates no schedule.<\/p>\n<p>Local <code>B:<\/code> cannot protect against server loss. Consider <a href=\"https:\/\/www.vps.tc\/blog\/en\/windows-server-backup-methods-scheduling-recovery\/\">Windows Server Backup: Methods, Scheduling, and Recovery<\/a> for server-level planning alongside SQL backups. My final operational step would be an access-restricted off-server copy, with scheduled restore rehearsals from that copy.<\/p>\n<div class=\"aiw-callout aiw-callout-example\">\n<p class=\"aiw-callout-label\">Example<\/p>\n<p>The hypothetical AppDb schedule takes a full backup every 24 hours and a transaction log backup every 15 minutes; these intervals are not a ready-made prescription for every workload. RESTORE VERIFYONLY does not replace an actual restore to AppDb_RestoreTest. Copy backups from B:\\SQL\\Backup to off-server storage too; a separate drive letter does not protect against server loss.<\/p>\n<\/div>\n<h2 id=\"check-before-connecting-your-application\">Check Before Connecting Your Application<\/h2>\n<ul class=\"aiw-checklist\">\n<li>Verify Windows Server compatibility, licensing, pending restarts, a system backup, and access to the provider&#039;s console.<\/li>\n<li>Prepare the NTFS directories D:\\SQL\\Data, L:\\SQL\\Log, T:\\SQL\\TempDB, and B:\\SQL\\Backup, and check the service account&#039;s required permissions.<\/li>\n<li>Use official SQL Server 2022 media, verify the current approved CU package, and record the full build after installation using SERVERPROPERTY.<\/li>\n<li>Add the authorized local Windows account as a SQL administrator, and exclude sa, sysadmin, and db_owner from application access.<\/li>\n<li>Evaluate the 4096 MB memory starting setting, fixed-MB file growth, and four equally sized tempdb data files against workload measurements.<\/li>\n<li>Restrict TCP 1433 at both firewall layers, and check DNS, Test-NetConnection, and SSMS certificate validation separately.<\/li>\n<li>Create the initial full backup and log backup jobs under the FULL recovery model, then verify failure notifications, off-server copies, and a restore to a separate database.<\/li>\n<\/ul>\n<div class=\"aiw-cta\">\n<p>Before allowing the application to connect, restore the AppDb backup to a separate AppDb_RestoreTest database. Record the recovery time and verify that it meets your service target.<\/p>\n<p class=\"aiw-cta-action\"><a href=\"https:\/\/www.vps.tc\/en\/vps\">Explore VPS plans<\/a><\/p>\n<\/div>\n<h2 id=\"frequently-asked-questions\">Frequently Asked Questions<\/h2>\n<div class=\"aiw-faq\">\n<details class=\"aiw-faq-item\" open>\n<summary>Can I use SQL Server 2022 Developer for free on a Windows VPS?<\/summary>\n<p>SQL Server 2022 Developer is free for development and testing; it does not grant the right to run a production database. Hosting it on a Windows VPS does not change those license terms. For production, evaluate an appropriate commercial license or Express if its resource and feature limits suit your application. Check Windows Server licensing separately from SQL Server licensing.<\/p>\n<\/details>\n<details class=\"aiw-faq-item\">\n<summary>Are 4 vCPUs and 8 GB RAM enough for SQL Server?<\/summary>\n<p>4 vCPUs and 8 GB RAM are the starting capacity for this non-production lab, not a performance guarantee for your application. Data volume, concurrent queries, and storage latency change what you need. The example starts max server memory at 4096 MB, but that setting is not an absolute cap on all memory consumed by the SQL Server process. Adjust capacity using measurements.<\/p>\n<\/details>\n<details class=\"aiw-faq-item\">\n<summary>What is the difference between Windows authentication and mixed mode?<\/summary>\n<p>Windows authentication uses Windows accounts; mixed mode adds sign-in with a SQL Server username and password. In this example without Active Directory, a remote Windows account is not automatically trusted. If SQL authentication is required, enable mixed mode and restart the Database Engine service. Give the application a separate account with narrowly scoped permissions rather than using sa.<\/p>\n<\/details>\n<details class=\"aiw-faq-item\">\n<summary>Which ports should I open for remote SQL Server connections?<\/summary>\n<p>This example uses static TCP 1433 for the Database Engine. Allow only the application and management clients&#039; actual private IP addresses in Windows Firewall and the provider firewall. Do not expose the port to the public internet. When the client specifies the server and port directly, SQL Server Browser discovery is unnecessary, so this connection path does not require UDP 1434.<\/p>\n<\/details>\n<details class=\"aiw-faq-item\">\n<summary>How do I distinguish an SSMS certificate error from error 18456?<\/summary>\n<p>First check DNS resolution, then TCP 1433 reachability with Test-NetConnection. In SSMS 20.2, select Encryption=Mandatory and leave Trust server certificate disabled. For certificate errors, check the trust chain, validity period, and SAN hostname. Error 18456 is a login failure; inspect its state code in SQL ERRORLOG for the detailed reason. Disabling certificate validation is not a permanent fix.<\/p>\n<\/details>\n<details class=\"aiw-faq-item\">\n<summary>Do I need transaction log backups after the first full backup?<\/summary>\n<p>Under the FULL recovery model, schedule regular transaction log backups after the initial full backup; full backups alone do not provide the log truncation needed for log space reuse. The hypothetical lab uses full backups every 24 hours and log backups every 15 minutes. Choose intervals based on acceptable data loss. Keep off-server copies and perform an actual restore test rather than relying only on RESTORE VERIFYONLY.<\/p>\n<\/details>\n<\/div>\n<h2 id=\"sources\">Sources<\/h2>\n<ul class=\"aiw-sources\">\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/install\/hardware-and-software-requirements-for-installing-sql-server-2022?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">Microsoft Learn &#8211; SQL Server 2022 Hardware and Software Requirements<\/a> \u2014 learn.microsoft.com<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/editions-and-components-of-sql-server-2022?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">Microsoft Learn &#8211; SQL Server 2022 Editions and Supported Features<\/a> \u2014 learn.microsoft.com<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/configure-sql-server-encryption?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">Microsoft Learn &#8211; Configure SQL Server Connection Encryption<\/a> \u2014 learn.microsoft.com<\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/\" target=\"_blank\" rel=\"noopener\">Microsoft Learn &#8211; Documentation Portal for SQL Server Backup and Restore<\/a> \u2014 learn.microsoft.com<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Finishing the setup wizard does not mean your database is ready for use. This hypothetical SQL Server 2022 lab on Windows Server 2022 works through authentication, memory settings, TLS, restricted network access, and a restore test.<\/p>\n","protected":false},"author":2,"featured_media":1150,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-1152","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-windows"],"lang":"en","translations":{"en":1152,"tr":1151},"pll_sync_post":[],"_links":{"self":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts\/1152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/comments?post=1152"}],"version-history":[{"count":1,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts\/1152\/revisions"}],"predecessor-version":[{"id":1154,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/posts\/1152\/revisions\/1154"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/media\/1150"}],"wp:attachment":[{"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/media?parent=1152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/categories?post=1152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vps.tc\/blog\/wp-json\/wp\/v2\/tags?post=1152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}