Overview

SQL Server 2005 Replication Before Replication

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.

In any replication scenario, there are two main components:

Publishers have data to offer to other servers. Any given replication scheme may have one or more publishers.

Subscribers are database servers that wish to receive updates from the Publisher when data is modified.

Merge replication allows the publisher and subscriber to independently make changes to the database. Both entities can work without an active network connection. When they are reconnected, the merge replication agent checks for changes on both sets of data and modifies each database accordingly. If changes conflict with each other, it uses a predefined conflict resolution algorithm to determine the appropriate data. Merge replication is commonly used by laptop users and others who can not be constantly connected to the publisher.

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
• Check SQL server is installed with replication system files Eg: "C:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE"
• Windows login should have the admin permission since replication will write the snapshot on the drive.
• If there is a firewall or antivirus software installed then allow the public IPs and open 1433 port in them.
• Visit Tutorial: Preparing the Server for Replication

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.

Note: Public IP is required only to subscriber machine. Since we can register the subscriber SQL server in the Publisher SQL server and build the replication.

• 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'

Examples
The following example creates an entry for the remote the server vtpl061 on the local server.
sp_addserver 'vtpl061','Local'

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:

Note: If Server name and Domain name is there, then mention all the info

• 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