x

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

more ▼

asked Jul 15 '12 at 05:10 PM in Default

haneesh gravatar image

haneesh
10 2 2 2

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 '12 at 06:31 PM SirSQL
show all comments (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 '12 at 08:11 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

(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 '12 at 08:46 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

Excellent suggestion. I keep forgetting about that as an option.
Jul 16 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x21

asked: Jul 15 '12 at 05:10 PM

Seen: 878 times

Last Updated: Jul 16 '12 at 09:35 AM