SQL Server Replication
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Replication Process
The following process will be followed when the company has multiple locations and they want to synchronize the data between the multiple locations. The supported database is SQL Server 2005 or above. There will be one primary place which is called “Publisher” and all other locations will be “Subscriber(s)”.
Check Points
SQL Server should be running on the multiple locations
-
Service packs available for windows & SQL server
-
SQL server and computer name must be same for publisher & subscriber machines
What is Public IP Address?
Public IP Addresses (also known as Static IP Addresses) are IP addresses that are visible to the public. Because these IP addresses are public, they allow other people to know about and access our computer.
- Check the computer name on the subscriber machines
- Check the SQL server name on the subscriber machine in SQL Query Analyzer as fallows:
Examples
The following example shows the usage of @@SERVERNAME.
SELECT @@SERVERNAME AS 'Server Name'
- If the SQL Server name and Computer name is not same then change the SQL Server name to Computer name
Examples
The following example removes the remote server ACCOUNTS and all associated remote logins from the local instance of SQL Server.
sp_dropserver 'ACCOUNTS', NULL
Examples
The following example creates an entry for the remote the server vtpl061 on the local server.
sp_addserver 'vtpl061'
Server Configuration Manager Tool
Alias can be created on the client machine by running the SQL Server Configuration Manager tool. Under the SQL Server Native Client folder, right click on the “Aliases” item and select the “New Aliases” item. When doing this the “Alias – New” dialog box will be displayed. In the new alias dialog box, you can create an alias where you can specify the instance name and the port number it is using. Below is a screen shot of how I would create an alias for my public IP “192.168.0.56” with server name “VTPL061” using port 1433.
Identified an “Alias Name” of “192.168.0.56”, which is using a “Port No” of “1433”, and a “VTPL061” value of “192.168.0.56”. Once I’ve specified this information I can either click on “OK”, or “Apply” and then “OK” to create my alias.
- Check the SQL server name is configured with Public IP (in C:\WINDOWS\system32\drivers\etc\ hosts); if not manually set it, as fallows:
- Port 1433 should be open on all the subscribers and Publisher. To Check whether the port is opened or not; check the following on command line, you should get a blank line.
Ex: VTPL061 is the name of the server on the subscriber and the IP address is 192.168.0.56
