Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-01-27 : 21:19:40
|
| Ok, I finally have my two SQL servers talking. I'm not sure quite what the problem was, but enterprise manager still cannot configure replication properly on one of them ("distribution table does not exist.")I followed the instructions in books online to completely strip replication mannually, using TSQL. I then configured it manually with TSQL again, and it worked flawlessly. Once replication was configured and the database set to "merge publish", I was able to use enterprise manager again to configure publications and articles.So now I'm almost in business (only a month behind schedule)! But I've got one architecture problem I just can't seem to figure out or find documented.My situation is thus: san francisco datacenter has main SQL server and several web servers. New york datacenter has SQL server and a couple of web servers. SF will always be canonical data. NY is largely a data cache to speed access to the east coast and europe, and a backup datacenter if SF gets nuked or something. However, NY needs to be able to run completely independently and be fully functional if/when internet connectivity between SF and NY goes away.I'm good with everything, except the logging table. This table sees about 500,000 inserts a day, and that will only grow. At midnight, stored procedures aggregate the data down into something manageable and then truncate the table.You can probably see the problem: I can merge replicate the logging table (it doesn't even have an identity column) and let each location update its local SQL server. Everything's great when things are working.However, what happens at midnight? Aggregation should work, but I will no longer be able to simply truncate the table. I'll have to do a "delete from" to take everything out. That's going to take a while, and meanwhile new logging requests are going to be pouring in in both SF and NY.Is there any way to expedite simply wiping the table at both locations without the expense of a TSQL "delete" and all of the transaction log activity that's going to cause? I was thinking of changing the aggegation job to drop the article just before the truncate, but then I'm going to lose data that's being written to the table in NY before the article is re-created.Having the NY web servers log to SF isn't an acceptable solution.Anyone have any ideas here? Does MSMQ sound like a likely savior? I've never worked with it.Thanks-b |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-01-28 : 06:30:24
|
| Maybe I'm overlooking something, but what about a drop table and recreate table after the aggrigation? The only problem is what happens to the data that SHOULD be going into that table while its being deleted and recreated. Maybe you can have a staging table to dump to while the drop table is being run, then pull over all the data once the main table is re-created? Pretty complicated solution, but hey, what do you expect at 5:30 am?Hope this gives you a few ideasMichael |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-28 : 15:28:15
|
| Yeah, I'm not wild about the juggling solution; seems to me that it's prone to failure, and this data is too important to lose.It looks like moving all of the realtime stuff to MSMQ is going to be the solution; if I mark them as reliable messages, MSMQ will deal with the retries and ensure that the data eventually gets to the table, even if it is locked for 20 minutes during a delete operation.Cheers-b |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-01-30 : 18:53:05
|
| have you considered "Transactional Replication with Updating Subscribers and Queued Updating" in SQL2000 ?It might do what you want. NY and SF will be kept in sync while both are online, when you take one "offline" for aggregation all updates will be queued at the subscriber.I don't know if that allows you to drop the article and truncate, but it is a lead worth following up maybe. |
|
|
|
|
|
|
|
|
Replication hell solved; architecture question