how to update a local ms sql database when remote mssql db is updated

more ▼

asked Jul 15, 2012 at 05:10 PM in Default

avatar image

10 2 2 3

You're providing no information here on what you are looking for. You could be looking at doing distributed updates using a trigger which will require a properly configured MSDTC or you could be looking at something as simple as transactional replication. You need to give further details around what you actually need before someone can provide any level of assistance.

Jul 15, 2012 at 06:31 PM SirSQL
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Assuming the title is the question, there are a couple of ways of doing this that I would recommend looking into. I would not suggest triggers. You're going to have to run them through linked servers and that can be very inefficient. Instead, I'd suggest you look into mirroring, which can copy one database to another. If you're running SQL Server 2012 you can use the Availability Groups which allow for read only copies in mirroring.

If you need to have a read only copy but you only want to move a few tables or a few columns within some tables, then I'd look at replication. With replication procedures you can pick and choose what gets moved through replication at a very granular level.

Both these topics are available in detail in the books online.

more ▼

answered Jul 16, 2012 at 08:11 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

I wholly support the ideas and advice from @Grant Fritchey and would add that it might be worth investigating Service Broker as a solution. This would make sure that the updates are made, and in a consistent order but neither system is going to impact on the other within a transaction. This would assist in keeping server performance consistent.

It all depends on what your system needs to achieve and what best suits your scenario.

more ▼

answered Jul 16, 2012 at 08:46 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

Excellent suggestion. I keep forgetting about that as an option.

Jul 16, 2012 at 09:35 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 15, 2012 at 05:10 PM

Seen: 1296 times

Last Updated: Jul 16, 2012 at 09:35 AM

Copyright 2018 Redgate Software. Privacy Policy