Install SQL-Server Replication
1. Run the MS SQL Setup.exe
2. Select Installation in the left hand panel
3. Click New SQL Server stand-alone installation or add features to an existing installation
4. Setup Support Files
Click install
5. Setpup Support Rules click next
6. Click Add features to an existing instance of SQL 2008
Select MSSQLSERVER
Next
7. Select Database Engine Service/SQL Server ReplicationNext
8. Disk space requirements
click next
9. Error and usage reporting
click next
10. Installation rules click next
11. Ready to install click Install
12. Installation progress click next
13. Complete click close
Setup the publisher
1. Open Microsoft SQL Server Manager Studio
2. Browse to Replication/Local Publications
3. right mouse click on Local Publications and select New Publication
4. New publication wizard click next
5. Select 'YourServerName' will act as its own Distributor; SQL Server will create a distribution database and log
Next
6. Select Yes, Configure the SQL Server Agent to start automatically
Next
7. Leave the snap shot folder in it's default location
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ReplDataNext8. Select the database that you want to synchronize
Next
9. Publication Type.
Transactional publication:The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.
Transactional publication with updatable subscriptions:The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.
Merge publication:The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.
Select Transactional publication with updatable subscriptions
Next
10. Article
Select all the Tables including sysdiagrams(dbo)
Next
11. Articles issued click next
12. Filter Table Rows click
next
13. Snap Shot Agent select Create a snap shot immediately and keep the snapshot available to initialize ssubscriptions
Next
14. Agent Security
Click Security settings for SnapShot Agent and Queue Reader Agent
15. For both steps in 14 I selected
Run under the SQL Server Agent Service account (This is not recommended security best practice)
(I dont want to manage a lot of different service accounts so I choose just the account I use the one I configured for SQL Agent)
16. Wizard Actions Select
Cretae the publication
Next
17. Click Next and the Create Publication wizard will start
18. Close
Setup the Subscription
(Before starting create a linked server to the publisher that uses the current security context, it's needed in step 15)
1. Open Microsoft SQL Server Manager Studio and connect to the target server
2. Browse to Replication/Local Subcriptions
3. right mouse click on Local Subscriptions and select New Subscription
4. New subscription wizard click next
5. Select
6. Open the SQL Server that has the published database
7. Select the database and publications that was just created
8. Distribution Agent Location
Select Run each agent at it's Subscriber (pull subscriptions)(The alternative is to run all agents at the distributor (push subscriptions) which can be easier to administer)
Next
9. Subscribers Choose the Subscription database where the data will go(At this point you can also create a new database)
10. Distribution Agent SecurityClick the (...) button
11. Run under the SQL Server Agent Service account (This is not recommended security best practice)
(I dont want to manage a lot of different service accounts so I choose just the account I use the one I configured for SQL Agent)
OK
12. Distribution Agent Security Click Next
13. Synchronization Schedule Select Run continously (the alternative is to run on demand or to define a schedule)
Next
14. Updateable SubsscriptionsSelect Simultaneously commit changes (the alternative is to queue changes and commit when possible)
Next
15. Login For Updateable scriptionsSelect Use a linked server or remote server that you have already defined
16. Initialize SbscriptionsSelect Immediately
Next
17. Wizard actions select Create the subscriptions
Next
After this something like this will apear
• Create subscription(s).
Create a subscription to publication 'TEST' from Publisher 'CHZUPRES913'.
Create subscriptions at the following Subscriber(s):
CHZUPRES912
• Subscription database: TEST
• Agent location: Subscriber
• Agent schedule: Run continuously
• Agent process account: SQLServerAgent service account
• Connection to Distributor: Impersonate process account
• Connection to Subscriber: Impersonate process account
• Initialize: Immediately
• Replicate changes: Simultaneously commit changes
18 Creating Subscription(s)Close
