How to create new Subscription?

We already covered Configuring Distributor and Publisher as well as Creating Publication . Now there is time to tell more about creating Subscriptions. As always you can create it by using GUI or T-SQL. In this post I will cover GUI setup of new subscriptions.

We already created Publication and we are ready to create subscription. Go to Replication tree on Publisher and extend it. Right click on Publication and choose New Subscriptions:

New subscriptions

Wizard will Open and you will need to choose under what publication you want to create new subscription:

choose publication

Now you will need to choose if the subscription will be Push or Pull. The main difference is where the Distribution Agent will be located. Pull -> Distribution Agent will be located on Subscriber, Push -> Agent will be located on Distributor. More about PUSH vs PULL I will cover in separate post.

PushorPULL

In next window you will need to choose the new subscriber. Note: you can create in that window Multiple subscriptions. You can add more than one server (!) which is pretty great if you need to send same data to multiple subscribers:

Choose subscriber

At this point you will make first connection to the subscriber there are two things you need to keep in mind. 1st You need to have access to that server from the place you creating the subscription.  2nd you need to use same or higher version of SSMS than your Subscriber is… otherwise u will get an error (more about this here: The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication. ) Both conditions are not needed when using T-SQL.

Once you connect to subscriber you need to choose subscriber DB:

Subscriberdb

Next window is Security Settings for Distribution Agent. As always you need to choose processing account. That is the account that distrib.exe will be running under (more about this here: Understanding Replication Agents). Second, how this agent will connect to Distributor and third is the account that will be used to connect to Subscriber. If Processing account don’t have permissions to subscriber or Subscriber is in different domain just use SQL Authentication (or grant permissions if possible).

Security1Security2

In next step you need to decide if the Distribution Agent should run continuously or not. In 99% cases you want to run continuously (for some reason you using Transactional replication). It pretty much means that Agent will be running all the time and will be started every time SQL Agent will start.

continously

Last step is to choose if you want to initialize Subscription and when. Initialize means new snapshot will be generated (or not if using Immediate Sync) and schema/data will be synced to subscriber. If you choose not to, the agent wont send ANY schema and start replicate data from the point when subscription will be created. The only thing that Distribution Agent will “sync” will be replication procedures. Those will be delivered before any data. Replication Procedures will be always send down in either option (if initialize then replication procs will be included in snapshot).

You can also choose if initialization will be done later (when snapshot agent will be started) or Immediately (means snapshot will be kicked off as soon as Subscription will be created).

Initilize

Last window you need to decide that either Subscription will be created or just script generated (or both).

Create

It is good practice to save the script as it is bit easier later if you want to re-create or create more same subscriptions.

Once you finished wizard you will see the Subscription under Publication that you choose at the beginning. In next post I will describe more how to create subscirption by using T-sql. Using T-SQL u have a bit more control over the settings and you avoiding few issues that can occure when setting up by using SSMS.

One Reply to “How to create new Subscription?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s