Replication Between Two SQL Servers

     Replication often plays an important part in your database management strategy. An organization may use database replication for load balancing, offline processing, redundancy, and other typical administrative tasks. This How do I… shows you how to configure replication between two Microsoft SQL Server 2005 systems using the SQL Server Management Studio.

    First, you will need to connect the database engine that contains the data you wish to replicate in SQL Server Management Studio. From there, you will need to right-click on the Replication folder (Figure A) and select Configure Distribution.

Figure A

                        Replication folder

     Next, you will specify how you want to select your distribution server. (Figure B) In most small replication environments, using the server that hosts the data as your distributor is fine. However, if you are replicating large amounts of data on very busy servers, you may wish to set up a dedicated box to be your distribution system. This individual distribution system can be the distributor for multiple SQL Servers if you have that need.

Figure B

                         Distribution server

     You will need to have your SQL Server Agent service configured to start automatically. (Figure C) This is due to the SQL Agent being responsible for managing subscriptions. To configure this through this wizard, the account running the SQL Server Service must have administrator privileges on the computer. If it does not, anyone with administrator privileges can set the SQL Server Agent account to run automatically.

Figure C

                           SQL Server Agent

     Next, you will need to set your snapshot folder. (Figure D) This folder will contain a snapshot (a collection of files, which contain all of the data you are choosing to replicate initially). This folder will need to have sufficient space to contain all the data you will be replicating. If you are only going to be pushing your subscriptions to subscribers, a local path is fine. If your subscribers will be pulling data, you will need to place your snapshot on a network path that is accessible by both systems.

Figure D

                            Snapshot folder

     The settings for distribution database (Figure E) must be determined by the amount of data you will be replicating and the frequency of the replication. The database (by default, named distribution) can be large (if you replicate sporadically) and highly active (if you have a lot of activity in replicated data). Be careful with your placement of this database to make sure you have sufficient storage and that it will not negatively impact disk subsystem performance on your server.

Figure E

                           Distribution

     Now that the distribution aspect is set, you can select the data you would like to publish. To do this, right-click on Local Publications and select New Publication. (Figure F)

Figure F

                           Local Publication

     You will then need to select the database you wish to replicate data from. (Figure G) You will see all of your user databases in this window and simply need to select the database that contains your data and click next.

Figure G

                         Publication database

      Now, you need to select the tables that contain the data you wish to replicate. (Figure H) You can replicate data only from tables that have a primary key. If you data does not have a primary key, you will need to create one before this step. You can choose to replicate a whole table (or tables) worth of data.

Figure H

                            Select tables

     You can also choose to replicate only certain columns from a table. (Figure I) This is useful if you need just a subset of your data to populate a Web-based or other application. Doing this will keep the size of your snapshot down and minimize the space requirements on the subscriber.

Figure I

                          Replicate columns

     You can also filter your data if you need to. (Figure J) However, this is a more advanced option and is not recommended unless you are comfortable with manipulating the data you will be publishing.

Figure J

                              Filter data

      You can choose to take your snapshot then or schedule it. (Figure K) The snapshot will lock tables while it copies the data out of them (to preserve replication integrity). The agent does run speedily, but if you have a large amount of data it can still take some time, so be sure to schedule accordingly.

Figure K

                          Snapshot Agent

        You can then provide credentials for the snapshot agent. (Figure L) If your SQL Server Agent service does not have permissions to the directory you choose to place your snapshots in, you will need to provide a different authentication account to provide this access.

Figure L

                         Credentials

       After selecting from a few more menus (where nothing but the defaults are needed), you can name your publication. (Figure M) You can send this same publication to many subscribers, so you will want to name the publication by the data it contains and not where it is going.

Figure M

                           Name your publication

       You are now ready to set up subscribers to your publication. (Figure N) Subscribers are the servers that will be receiving your replicated data. To do this, right-click on the publication you have just created and select New Subscriptions.

Figure N

                         Set up subscribers

       You will first need to select the publication you wish to replicate. (Figure O) If you are using a separate distributor system, you will need to connect to the SQL Server that houses the publication you will replicate.

Figure O

                        Select replication publication

       Now you can select your subscribers. (Figure P) This is done by selecting Add Subscriber, which will prompt you with the standard SQL Server Management Studio prompt for a server connection. Simply provide the server name and your preferred type of authentication. You can also add multiple servers as subscribers at this point by repeating this step.

Figure P

                          Select subscribers

       You can configure your authentication type for the distribution agent next. (Figure Q) If you wish to use a specific account to push to the data to the subscription server, this is where you do it. You can choose to use the SQL Server Agent account, a Windows account, or an SQL account, depending upon your own security infrastructure.

Figure Q

                         Authentication type

      Next, you will set the schedule. (Figure R) You can set the agent to run continuously, which will provide a near real-time replication of your data. You can also replicate at any schedule you wish. This is useful if you have a slow WAN and need to maximize bandwidth during certain hours.

Figure R

                          Set the schedule

       You can now see your subscriber under the publication. (Figure S) From here, you can modify the properties of your subscription, reinitialize the subscription, view your synchronization status, and bring up the replication monitor.

Figure S

                         Subscriber properties

       By viewing the replication monitor, you can verify that your transactions have been delivered successfully and see the amount of time it took for each step to complete. (Figure T) If you have any errors, you can also view details on them here to help your troubleshooting.

Figure T

                        View replication monitor

Reference: Govind Badkur(http://govindbadkur.blogspot.com)