Data Management

How do I... Configure transactional replication between two SQL Server 2005 systems?

By // August 28, 2007, 4:18 AM PST

Image 1 of 20

  • By Josh Hoskins<br /><br />Replication folder

    This gallery is also available as a TechRepublic blog post and download.

    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 and select Configure Distribution.

  • Distribution server

    Next you will select how you want to select your distribution server. 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.

  • SQL Server Agent

    You will need to have your SQL Server Agent service configured to start automatically. 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.

  • Snapshot folder

    Next you will need to set your snapshot folder. 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.

  • Distribution

    The settings for distribution database 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 of large size (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 have sufficient storage, and it will not negatively impact disk subsystem performance on your server.

  • Local Publication

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

  • Publication database

    You will then need to select the database you wish to replicate data from. 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.

  • Select tables

    You will then need to select the tables that contain the data you wish to replicate. You can only replicate data 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.

  • Replicate columns

    You can also choose to only replicate certain columns from a table. This is very useful if you only need 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.

  • Filter data

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

  • Now or schedule

    You can then choose to take your snapshot then, or schedule it. 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 please schedule accordingly.

  • Credentials

    You can then provide credentials for the snapshot agent. 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.

  • Name your publication

    After selecting from a few more menus (where nothing but the defaults are needed), you can then name your publication. 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.

  • Set up subscribers

    You are now ready to set up subscribers to your publication. 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.

  • Select replication publication

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

  • Select subscribers

    Now you are able to select your subscribers. This is done simply 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 simply repeating this step.

  • Authentication type

    You can configure your authentication type for the distribution agent next. 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.

  • Set the schedule

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

  • Subscriber properties

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

  • View replication monitor

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

By Josh Hoskins

Replication folder

This gallery is also available as a TechRepublic blog post and download.

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 and select Configure Distribution.

Related Topics:

Data Management Big Data Analytics Data Centers Digital Transformation Cloud