Edition: Saturday, August 3rd, 2024
A few weeks ago I needed to configure replication between two SQL Server 2022 named instances, on separate networks. In this configuration, the publisher would push data to the subscriber and all related replication jobs would reside at the publisher.
It's been a while since I last configured SQL Server replication. In fact, it's been around 8-9 years since I've needed to set it up from scratch. After a few hours of frustration, I spun up a home lab and got to work.
Today, I want to cover a few of the hurdles I encountered and how I got around those using SQL Server Aliases.
Let's dive in.
Scenario
Let's say you're asked to configure replication between two SQL Server instances. One of those SQL Server instances is a named instance. It resides on a separate network but is accessible via an IP address or the fully qualified domain name (FQDN) and listens on port 1533. The SQL Server Browser service is disabled.
- Publisher - SRV1.HOMELAB.LOCAL (SQL Server 2022)
- Subscriber - SRV2.HOMELAB.LOCAL\CENTRAL (SQL Server 2022, browser is disabled so we'll need to use the port 1533 when connecting.)
We're using the StackOverflow database (thanks to Brent Ozar) as the publication database and Merge replication. We'll select only the VoteTypes and PostTypes tables (they're small). We'll name our subscription database StackOverflow_Central.
I've created an SQL Authenticated login, repluser, on SRV2.HOMELAB.LOCAL\CENTRAL and granted db_owner permissions within the StackOverflow_Central database. All agents will run at the publisher and the repluser login will be used to connect to the subscriber.
You'll be able to configure the subscription, using SRV2.HOMELAB.LOCAL,1533, but you'll get an error at the end.
We have two issues:
- Whatever server name you use for the subscriber, must match SELECT @@SERVERNAME. In our case, this must be SRV2\CENTRAL.
- You can't use the FQDN and the port.
Here's where SQL Server aliases can help.
SQL Server Aliases
SQL Server aliases are a way to create alternate names for SQL Server instances, allowing clients to connect using these aliases rather than the actual server name or IP address. This is similar to creating a CNAME in DNS or using the local hosts file. However, neither of those options allows you to specify a port to connect to (aliases do).
Key Points:
- Simplified Connections: Aliases provide a simplified way to connect to SQL Server instances, which can be particularly helpful in environments where named instances are used, NetBIOS resolution is disabled, the SQL Server Browser service is disabled, or simply the server name cannot be resolved to an IP.
- Configuration: Before SQL Server 2022, you could use SQL Server Configuration Manager to add 32-bit and 64-bit aliases. In fact, Microsoft's documentation still states this can be done (Aliases (SQL Server Configuration Manager) - SQL Server | Microsoft Learn). The aliases, once created, can still be viewed using SSCM you just can't create them. There are several methods for configuring aliases. We'll cover the SQL Server Client Network Utility and Ansible. But, if you're looking for additional methods, check out Vlad Drumea's blog post here.
- Network Protocol: When creating an alias, you choose the network protocol (typically TCP/IP) and specify the port number if it's not the default port for SQL Server.
- Cross-Domain Connections: Aliases can help in scenarios where SQL Server instances reside in different domains, and direct connections using fully qualified domain names (FQDN) or IP addresses won't work.
- Migration and Redundancy: They are useful during server migrations or for providing redundancy. By pointing an alias to a different server, clients can connect to the new server without changing connection strings.
Configuring aliases using the cliconfg utility
In our scenario, we'll create an alias using the cliconfg utility and reconfigure replication. We need to add the alias on the publisher (SRV1). You'll find this utility located at C:\windows\system32\cliconfg.exe (64-bit) and C:\windows\syswow64\cliconfg.exe (32-bit) and we'll use both but the steps are the same for each.
1. Open cliconfg and enable the TCP/IP protocol.
2. Click the Alias tab and then click Add.
3. The Server alias must match the output of SELECT @@SERVERNAME on the subscriber. In this case, the value should be SRV2\CENTRAL.
4. Under network libraries, select TCP/IP.
5. Enter the server name or IP address.
6. Uncheck dynamically determine port and enter 1533.
7. Click Ok.
At this point, we'll need to reconfigure the subscription to use SRV2\CENTRAL. I'll do that using SQL Server Management Studio (SSMS) on SRV1.
Success!
This can be a lengthy process if you have several servers to configure. Ansible and PowerShellDSC provide a way for configuring aliases - automatically, across hundreds of servers (if needed).
Automating SQL Server Alias Configurations With Ansible
The following is a sample playbook we could use to ensure an alias is configured on multiple servers. This is a simplification but gives you an idea of how this can be done automatically during the build phase of your SQL Server instances or client machines.
The playbook will use the win_copy and win_dsc ansible modules. It will create both the 32-bit and 64-bit aliases.
Environment:
Here's how I have my lab configured. If you build one to follow along in the examples, be sure to create snapshots first. This way you can easily reset your environment for the next walkthrough.
- Hardware - Geekom A7 Mini PC Ryzen - 64 GB RAM / 2 TB drive
- Hypervisor - VMware Workstation Pro 17.5 (get this for free if you're using it for personal use - VMware Workstation Pro: Now Available Free for Personal Use)
- Virtual Machines - All built using a single Windows Server 2022 Standard (eval) Hashicorp Packer image and managed with Vagrant.
- DC1 - Domain Controller (HOMELAB.LOCAL)
- SRV1 - SQL Server - Contains the OS drive and 3 uninitialized NVMe disks.
- SRV2 - SQL Server - Contains the OS drive and 3 uninitialized NVMe disks.
- SRV3 - Client machine - Windows Subsystem for Linux (Ubuntu) - Ansible Control Node. Visual Studio Code is installed and used to create the playbooks throughout this series.
Conclusion:
SQL Server Aliases are a great way to simplify your setup and to work around common issues when not all of your SQL Server instances or client machines reside on the same network and/or domain. If you are interested in learning more about automation and the use of Ansible, be sure to sign up for the waitlist below.
Ansible For SQL Server DBAs (join the wait list) - https://www.automatesql.com/ansible.
Thanks for reading! Have a great week!