Data Management

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


This blog post is also available as a TechRepublic gallery and TechRepublic 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 (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

Now or schedule
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
8 comments
SchultzyBeckett
SchultzyBeckett

The conversions companies hope to fill this niche. While the scale of conversions will be limited, these companies could serve to prod major automakers to offer a greater variety of these vehicles, say some automotive analysts.

schultzy @ https://www.yourmechanic.com/

Mr.Binh
Mr.Binh

hello

I have a question in Figure I, with the column not replicate, the subscriber will be drop column in table.

for example: Column BirthDate in employees table will drop subscriber table

How to retain column BirthDate in subscriber table


Thank you



David Wynn
David Wynn

I see only the table structures replicated. The data is not replicated for all tables. How can we get data to replicate. Thank you.

poojak
poojak

Please tell me how to perform replication on same sql server

sarangpitale
sarangpitale

Hi Joshua, I have a question regarding snapshot folder. Scenario in my company is replication with updatable subscriptions is scheduled to run every five minutes. Now i have observed that every 5 mins files having size 8 mb are created in the snapshot folder. which totals for almost 2.25 gb files each day. Is there any option where we can make the files older which are more than 3 days old deleted automatically. if not, please suggest any alternative which requires least human intervention.

Ramnatha.acharya
Ramnatha.acharya

Whe i configure i get following error can you please help ASAP =================================== SQL Server could not configure 'blrkpnq0027srv' as a Distributor. (New Publication Wizard) =================================== An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Program Location: at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand) at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn) at Microsoft.SqlServer.Replication.ReplicationServer.InstallDistributor(String password, DistributionDatabase distributionDB) at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.InstallDistributor(WizardInputs inputs, Boolean bScripting) at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.InstallDistributor(IProgress progress, Boolean bScripting, ApplicationException& outerEx, StringBuilder command) =================================== Password validation failed. The password does not meet the requirements of the password filter DLL. Changed database context to 'master'. (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=15119&LinkId=20476 ------------------------------ Server Name: blrkpnq0027srv Error Number: 15119 Severity: 16 State: 1 Line Number: 1 ------------------------------ Program Location: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

cshekar17
cshekar17

really good one it helps me setting up transactional replication

alalani
alalani

This article was good for basic knowledge. Is it possible to use sql 2005 as a distributor and sql 2000 as a publisher. Subscriber could be any. OR sql 2000 as publisher and distributor but subscribers as sql 2005. Please point me if there is any reference material available on this topic. Thanks a bunch. alalan

Editor's Picks