Sql-server – Transactional Replication guidance

azure-sql-databasereplicationsql server

As a real newcomer to Transactional Replication I would appreciate some guidance.

I have a production database on premise and the need to take two tables from here and copy them regularly to a SQL Azure database. This is so that our suppliers can run a very simple label printing program from their premises so they can stick labels on the goods they supply to us.

We raise a Purchase Order in house on our ERP system and send it to our supplier. They produce the goods and print the labels. Stick the labels on the goods and send them to us.

That is it. Nothing fancy in the way of filters or otherwise (This can all be handled by the program).

Is this a fairly easy thing to set up? Is there an idiots guide anywhere that could show me how to go about this?

Best Answer

There are many ways by which you can transfer data and all depends on this part of question..

copy them regularly to a SQL Azure database

1.If you want once a day,you can write ssis package and do the logic of checking/merging data in that

2.You also can write C# program which does this for two tables and run this through SQLServer agent..

3.if you are looking for near realtime data transfer,i would go with Transactional replication.This is supported now..

Setting up is very easy task and same as Onpremises ,but with one exception of no pull subsribers .

This article from Joe Sack will help you on how to set up..

There are also some limitations which you need to account for..

The publisher and distributor must be at least at one of the following versions:

SQL Server 2016

SQL Server 2014 SP1 CU3

SQL Server 2014 RTM CU10

SQL Server 2012 SP2 CU8

You can read this link for more details..

Related Solutions

Sql-server – Transactional Replication Concerns

1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.

There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small. One big problem that can become an issue is if you have a local distributor, especially if you are dealing with a large amount of publications being replicated to multiple subscribers in a push configuration. If that is the case, one way to alleviate the server impact is to have a remote distributor.

Also, by design with transactional replication the Log Reader Agent is doing just that: reading the transaction log of the publisher. There are many strategies to reduce this impact, such as replicating stored procedure executions instead of the resulting data.

2.If the subscriber server goes down, will the primary still function

Yes, the publisher will still function. Commands to get replicated will be queuing up, and depending on how long your retention is setup for and how long the subscriber is disconnected you may need to reinitialize the subscription.

I recommend this TechNet reference: Enhance Transaction Replication Performance. There are a handful of great strategies in that document that should be considered.

Sql-server – Transactional Replication – Snapshot metrics

The metrics I want to capture are:

The size of the snapshot generated for each publication.

The time it took to generate the snapshot.

The time it took to apply the snapshot at the subscriber.

Is it possible to get this information by querying distribution?

You cannot get such metrics using T-SQL. You can check the replication status using T-SQL though.

Depending of how large/big your publication database is (for small databases in same network, you should not even look at the time/size as it will be super fast), you can follow below steps to get a ROUGH estimate (ball park figure) of the size and time of snapshot to get generated and apply at the subscriber :

Note: I put emphasis on Rough estimate as depending on what you are replicating e.g. store procedures, indexes, constraints, etc there will be additional files generated as well.

Select top 10 (depends on you - how many you choose) articles with highest row counts from the publication database and BCP OUT the data. This will give a better judgement for space requirement of snapshot agent. This would be resource intensive operation and hence should be scheduled during quiet activity of the server.
```
e.g. bcp publisher_database.article_name out largeTable1.dat -T -c
```
Note: Above will give you an estimated time taken by BCP and the size of the BCP file.

So above point will answer you 1st and 2nd points.
Now to get the time it took to apply the snapshot to the subscriber, you have to run bcp in from the publisher server specifying the SUBSCRIBER_SERVER_NAME
```
e.g. bcp subscriber_database.article_name in -S SUBSCRIBER_SERVER_NAME\instance_name largeTable1.dat -T -c
```
This way you get an estimate of how long will it take to apply snapshot to subscriber.

Best Answer

Related Solutions

Sql-server – Transactional Replication Concerns

Sql-server – Transactional Replication – Snapshot metrics

Related Question