March 28, 2011 at 2:23 pm
We are testing if our Server goes down how long would it take to be back up & running.
Scenario - Transaction Replication Server1 is Publisher/Distributor
Replication runs fine on Server 2, disconnect from network, delete subscriber on Server 2.
Can open Application and Read/View from Server 2 Database.
Problem is trying to write/insert to Server 2
Error is "Violation of Primary Key constraint Cannot insert duplicate key in object"
I've tried reseeding all tables EXEC sp_msforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'
this works but still get the error?
March 29, 2011 at 2:58 am
This is quite a difficult one to diagnose. From your post I read it as you're using a subscriber as a DR server and have since dropped this out of replication to test your plan.
Are you given a table name that it is failing to insert on? If not can you run a sql profiler trace to capture the code/sp thats failing? We will then need to see the DDL of said tables and proc.
March 29, 2011 at 3:22 am
Kathy Massey (3/28/2011)
We are testing if our Server goes down how long would it take to be back up & running.Scenario - Transaction Replication Server1 is Publisher/Distributor
Replication runs fine on Server 2, disconnect from network, delete subscriber on Server 2.
Can open Application and Read/View from Server 2 Database.
Problem is trying to write/insert to Server 2
Error is "Violation of Primary Key constraint Cannot insert duplicate key in object"
I've tried reseeding all tables EXEC sp_msforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'
this works but still get the error?
If your prime goal is disaster recovery, then why go in for replication? Replication has a delay associated and has a potential for data loss.
Instead, I would suggest that you use database mirroring or log shipping. Synchronous Database mirroring is zero data loss, while log shipping is also quite fast.
The difference between the two is that in case of mirroring, the secondary (i.e. the mirror) will not be accessible as long as the primary is active. As soon as the primary goes down, automatic failover takes place and secondary assumes it's place as the primary. In case of log shipping, both databases are always up and running - only difference is that the secondary is at a time lag when compared to the primary.
Also, if you are planning for SQL Server 11 "Denali", you should research the High Availability enhancements planned - I hear they are quite good.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
March 29, 2011 at 8:17 am
Nakul Vachhrajani (3/29/2011)
Kathy Massey (3/28/2011)
We are testing if our Server goes down how long would it take to be back up & running.Scenario - Transaction Replication Server1 is Publisher/Distributor
Replication runs fine on Server 2, disconnect from network, delete subscriber on Server 2.
Can open Application and Read/View from Server 2 Database.
Problem is trying to write/insert to Server 2
Error is "Violation of Primary Key constraint Cannot insert duplicate key in object"
I've tried reseeding all tables EXEC sp_msforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'
this works but still get the error?
If your prime goal is disaster recovery, then why go in for replication? Replication has a delay associated and has a potential for data loss.
Instead, I would suggest that you use database mirroring or log shipping. Synchronous Database mirroring is zero data loss, while log shipping is also quite fast.
The difference between the two is that in case of mirroring, the secondary (i.e. the mirror) will not be accessible as long as the primary is active. As soon as the primary goes down, automatic failover takes place and secondary assumes it's place as the primary. In case of log shipping, both databases are always up and running - only difference is that the secondary is at a time lag when compared to the primary.
Also, if you are planning for SQL Server 11 "Denali", you should research the High Availability enhancements planned - I hear they are quite good.
I'm a little confused as to how logshipping can be considered superior to replication? All of the options mentioned are considered High Availability technologies and we dont know what the purpose of replication is.
Perhaps the DR server also serves as real time reporting? This cannot be done properly in logshipping or mirroring as they both provide a static image of the database and require users to disconnect to update them. We shouldnt put down their current setup without knowing the reasoning behind it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply