1

Hey, I have a big database in sql server 2005, and since it´s so big I need to replicate it to other sever. I set up sql replication publisher and subscriptor but it doesn´t behave in a stable way. It´s not replicating for every insert and it´s not throwing any exceptions either. Also, I´m concerned about performance here, so I was wondering if maybe there is some other alternative I could use... I don´t need to replicate every row in the database, or all the tables either.

Any comment will be appreciated here!

Thanks!

UPDATE: What I´m trying to achieve here is a load balance for the database. I expect a lot of traffic here, so I´m thinking using the replicated databse to show information to users. Meaning, inserts, updates and deletes will be performed in main databse, and selects will be performed in the new databse. Since the replicated databse will be just for selects, and show information to users, I just need those rows that are active for the app. Also, I´m thinking of having not normalized tables here, to avoid using joins and retrieve the information as fast as I can. the replication I set up is transactional, and really, the problems I have is that following the same process to insert a row, it once replicate the result, and then tryed again and it didn´t. And it has been working like that since.

Ragalante
  • 341
  • 2
  • 5
  • 13

3 Answers3

1

It sounds like you want to use Transactional Replication:

The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.

But you need to recognise that there may be small delays between updates being applied, and their being visible in the subscription database. Most applications can cope with this, but you may have to do more work in the application to prevent performing work based on stale data (if you think about it though, your application should probably already be dealing with these kind of problems, since users typically will look at a screen for (many) minutes before submitting changes.

Seen from your edit that you've already tried transactional replication, and encountered issues. I'd normally recommend practising setting up replication a number of times, and just getting a general feel for how it works before attempting it with the real database. Start with just a couple of tables, and few rows of data. Set up replication (both databases can be on the same server). Experiment with the various options. If replication stops working (as you indicated) you need to go hunting for events and log messages that indicate what has gone wrong/stopped working.

From your description, it really does sound like transactional replication is what you need, so I'd say persevere, and if you find specific errors occurring, search serverfault, or ask new questions there about such errors.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

You could you integration services to create a package which can be scheduled to run and update your second server database.

Simon Black
  • 1
  • 1
  • 3
  • This should be practically instant. I mean, once a insert, update or delete is made in main databse, the other database should be updated immediately, else users might not have updated information all the time... – Ragalante Jan 27 '11 at 14:39
  • Why not use a trigger that runs a procedure to which updates the second database when a insert update delete occurs? Or mirror the the database. – Simon Black Jan 27 '11 at 14:43
  • I thought of triggers too, even if it takes more work to do than setting up a replication or mirror. But could triggers be fast? I mean, do you think triggers would be faster or slower than replication? – Ragalante Jan 27 '11 at 15:01
0

I think triggers would be a good idea, however i have the same situation but in my case i need at all the different locations can post data in their locations and all locations needs to be synced with all other locations data!

in that case i have implemented my own logic of creating a replication table with server ids and sql logs.

on software runs on main server which copy all logs to the each location and execute them.

this solves my problem however it still needs some more optimization like to compress data during travel to increase the replication speed, because i have almost 100000 entries per week in each database, and i have 4 locations.

Kashif Baig
  • 1
  • 1