Skip to content

Planning & Installation

Setting Up the Environment

Purpose of the setup:

  • Install and manage SQL Server on a separate Windows Server.
  • Work with domain accounts, service accounts, logins and users.
  • Deliberately place databases, logs and backups on separate locations.
  • Use SSMS from a Windows client.
  • Test backup/restore, permission management, jobs, performance and troubleshooting without production risk.

Basic principle:

  • identities centralized via Active Directory;
  • SQL Server as a separate member server;
  • management from a client with SSMS;
  • data, logs and backups logically separated;
  • no regular user accounts for SQL services.

Reference Architecture

Internet / host network
        |
        v
+----------------------+
| Domain Controller    |
| AD DS + DNS          |
+----------+-----------+
           |
           | internal lab network
           |
    +------+------------------+
    |                         |
    v                         v
+------------------+    +------------------+
| SQL Server       |    | Windows Client   |
| Member server    |    | SSMS / management|
+------------------+    +------------------+

Roles

Role Function
Domain Controller Active Directory, DNS and optionally routing/NAT for the lab network
SQL Server Member server running SQL Server
Windows Client Management workstation with SQL Server Management Studio

I join the SQL Server itself to the domain. The client can be domain-joined, but that's not always necessary for a lab. If I mainly want to test SQL authentication or standalone connectivity, a non-domain client is sometimes exactly what's interesting.

Naming and Network

Use consistent names. Troubleshooting goes much faster when hostnames, IPs and roles are clear.

Component Example Note
Domain Controller dc01.lab.local AD DS + DNS
SQL Server sql01.lab.local Member server
Client win11-admin SSMS and management
Domain lab.local Lab domain
DC IP 10.10.10.10 DNS for the lab
SQL IP 10.10.10.20 Static IP
Client IP 10.10.10.100 Static or DHCP

DNS first

In Windows domains, DNS is not a detail. The SQL Server and client must use the domain controller as their DNS server, otherwise you'll get strange domain and authentication issues.

Server Planning

Domain Controller

Choices:

Component Choice
OS Windows Server
Roles Active Directory Domain Services, DNS
Network 1 internal NIC, optionally an extra WAN/NAT NIC
IP Static
Disk Enough for OS, AD and logs

In a small lab, the domain controller can also handle routing/NAT. Keep in mind though: in production you separate the domain controller, firewall/router and database infrastructure.

SQL Server

Choices:

Component Choice
OS Windows Server
Domain status Member server
SQL edition Developer edition
Network 1 internal NIC
IP Static
Management SSMS from a client

Separate storage logically:

Volume Use
C: OS, SQL Server binaries
D: Data files (.mdf, .ndf)
L: Log files (.ldf)
B: Backups

On a small VM this may physically be the same underlying disk. The logical separation stays useful for structure, permissions and troubleshooting.

Service accounts

Don't run SQL Server services under a regular user account. Preferably use Managed Service Accounts or Group Managed Service Accounts. For a simple lab, a dedicated domain account is fine, but document clearly what it's used for.

Windows Client

Choices:

Component Choice
OS Windows 11
Software SQL Server Management Studio
Network Same lab network as SQL Server
DNS Domain controller
Domain member Optional, depending on test scenario

Use the client as a management station. This lets you immediately test connectivity, firewall rules, name resolution and authentication.

Installation Order

  1. Create the lab network.
  2. Install the domain controller.
  3. Configure static IP and DNS.
  4. Create the domain.
  5. Install the SQL member server.
  6. Join the SQL server to the domain.
  7. Create service accounts.
  8. Install SQL Server.
  9. Configure data, log and backup locations.
  10. Install SSMS on the client.
  11. Test DNS, domain login and SQL connectivity.

Planning Before Installation

Record before installation:

  • What hostname will the server get?
  • What IP address and DNS server will it use?
  • Will this be a default instance or a named instance?
  • Which accounts will run the SQL services?
  • Where will data files, log files and backups go?
  • Which authentication model will I use: Windows-only or mixed mode?
  • Should SQL Server Agent be installed and started automatically?
  • Which firewall rules are needed for management from the client?
  • Is this purely a lab, staging, or something that should later become production-like?

Basic Check After Installation

Check after installation:

hostname
ipconfig /all
nltest /dsgetdc:lab.local

Then check in SSMS:

  • connection by hostname, not just by IP;
  • Windows authentication with a domain account;
  • status of SQL Server and SQL Server Agent;
  • default locations for data, logs and backups;
  • presence of the system databases: master, model, msdb and tempdb.

Pitfalls

Problem Likely cause
Server can't join the domain Wrong DNS server configured
SSMS only connects via IP Name resolution/DNS not correct
Windows authentication fails Client has no domain context or wrong credentials
SQL Server Agent doesn't start Check service account, permissions or edition/configuration
Backups fail SQL service account has no write permission on the backup folder
Slow or unusual database growth Autogrowth and initial file sizes not deliberately configured

Basic rule

Plan first, then install. SQL Server quickly works "roughly right," but poor choices around accounts, storage and naming keep coming back later in permission management, backups and troubleshooting.

Download SQL Server

SQL Server Downloads

Exam Setup

Domain DatabJBE.local DC: AdservJBE - (win serv 2025 Desktop) Nic1 - WAN ip 192.168.18.3 - gateway 192.168.18.2 - DNS 9.9.9.9 Nic2 - LAN ip 10.10.10.3 - gateway xx - DNS 10.10.10.3

Memb: CoreJBE - (windows serv 2025 - Core) Nic1 ip 10.10.10.4 gateway 10.10.10.3 - Dns 10.10.10.3

Vm Virt netw settings vmnet 8 - Nat 192.168.18.0 Gateway 192.168.18.2 - DHCP OFF vmnet 1 - Host only 10.10.10.0 - DHCP OFF

Serv3: MemberJbe - win serv 2025 Deskt)10 nic1 10.10.10.5

win10: clientJBE Nic1 10.10.10.6

Service Account Core: svc-sql Service account Member: svc-ssrs