Thursday, December 22, 2011

Step by Step Guide to Configure Snapshot Publication In SQL Server 2008

About Snapshot:
In Snapshot Replication an entire copy of the publication is moved from the publisher to Subscriber. You can schedule your refreshes how often you want. Snapshot replication uses the distributor agent. If you are using a pull replication, the distributor agent resides on the subscription server, where as in push replication, the agent resides on the distributor. In push subscription, snapshot replication consumes a large amount of overhead on the distribute server. It suggest that most snapshot subscribers use a pull pull replication at regularly scheduled intervals. 

Step by Step Guide to Configure Snapshot Publication In SQL Server 2008: 
In this example I am using following servers for configuring Snapshot Publication.

Step 1:  Before configuring Snapshot Publication, you have to configure Distribute Server, if you are not configured then click here for Step by Step Guide to Configure Default Instance as a Distribution Server. 

Step 2: Open SQL Server Management Studio and connect to the Source/publisher, server/instance of SQL Server 2005/2008. Expand replication, right click on Local Publications.

Step 3: After clicking New Publication, this brings you to the new publication wizard, click Next to Continue. 

Step 4: On the Distributor Screen, select the second option, "use the following server as the distributor" and click Add. (If you are using same server for Distribute and Publisher then select first Option and Click Next)

Step 5: After Clicking Add new window will open like connect to server, give the distributor server name and credentials and click connect. After successfully connected then the screen will be look like below.

Step 6: Now you are at Administrative Password screen, give the password and click Next to Continue.


Step 7:  On the Publisher Database Screen, highlight AdventureWorks and click Next to Continue.

Step 8: On the publication Type screen, you can choose what type of publication to create. In this article i am covering Snapshot Replication so highlight Snapshot Replication and click Next to Continue.

Step 9: On the Article Screen you can select what data and object you want to replicate, (you can also set the properties for an article by clicking Article Properties. I am not covering those things in this article). Click Next to Continue.

Step 10: Click Next. 

Step 11: On the Snapshot Agent Screen, check the box to create a snapshot immediately and check the schedule snapshot agent to run at the following times, and Click Change.

Step 12: On the Job Schedule Properties, Set the Daily frequency. (In my case every Sunday at 12:30 PM the Snapshot job runs.) Click OK, and Click Next.

Step 13: On the Agent Security screen, you are asked how the agents should log in and access data. To set this Click the Security Settings button.

Step 14: Usually you would have to create an account for the agent to run under, in simple you can run the agent using the SQL Server Agent Account. Select the radio button as shown below and click OK.

Step 15: Now you back at the Agent Security Window, Click Next to Continue. 

Step 16: On the Wizard Actions screen. leave the Create the publication box checked, and click Next to continue.

Step 17: On the Complete the Wizard Screen you need to enter a name for the new Publication, so enter Adventure_Publisher and click Finish.

Step 18: When the wizard is finished click Close.

Click here to know more how to Configure Snapshot Subscription In SQL Server 2008 through Step By Step.
How Snapshot Replication Works. Setting Up Snapshot Replication A Step-by-step Guide. Snapshot Replication in Microsoft SQL Server. ransactional replication. snapshot replication sql server 2008. snapshot replication sql set up snapshot replication sql server 2008. sql snapshot replication 2005. snapshot replication sql server 2005. snapshot replication in sql server 2005 step by step. snapshot replication in sql server 2008 step by step. snapshot replication in sql server 20012 step by step guide. step by step tutorial. step by step guide to configure snapshot replication. Step by Step snapshot Installation. Step-by-step procedure. snapshot replication step by step. Step by Step Guide to. sql server step by step. sql server step by step replication ebook. microsoft sql server step by step replication ebook. sql server replication step by step tutorial. step by step installation of sql replication. microsoft sql server 2008 replication step by step. What is Snapshot Replication.


Post a Comment