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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Principal and replica databases

Author  Topic 

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-09-26 : 05:03:34
Hi all

Is there an easy way (or script) that will allow me to ensure that all the indices on the principal database (and all the tables) are in place on the mirror?

We've got several hundred tables to check and I'm looking for the most efficient way of doing it.

Any help gratefully received.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-09-26 : 07:06:02
If you're talking about database mirroring or availability groups, you don't need to check the mirror/replica. As long as they are fully synchronized they will be identical.
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-09-26 : 07:35:10
That was my unserstanding, but it's not the case.
I've managed to list all the indices on the principal and there a quite a few missing on the mirror (maybe mirror isn't the right term?).
I'm not sure what the setup is but surely this can't be right?
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-09-26 : 20:16:11
More likely you are using replication. If you were mirroring you wouldn't be able to read the replica as it's in a restoring state. You'd also see "Mirror" in Management Studio next to the database name, plus the status (Synchronized, Synchronizing, Disconnected).

I'd suggest running sp_helppublication, this will list your replication objects if there are any. There's more detail about it here: http://technet.microsoft.com/en-us/library/ms189782.aspx
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-09-27 : 03:31:27
Thanks for that, I'll have a bit more of a dig and see what I can find.
Go to Top of Page
   

- Advertisement -