| Author |
Topic |
|
cz
Starting Member
19 Posts |
 Posted - 2010-09-08 : 14:22:10
|
| Hi,I have primary SQLA and secondary SQLB through data mirroring. Since table reindexing(online) will take a few hours and slow down SQLA, I would like to switch over to SQLB, perform reindexing on SQLA and switch back to SQLA. Is this possible? How do I re-sync the SQLB and SQLA after reindexing?Thanks |
|
|
cz
Starting Member
19 Posts |
Posted - 2010-09-08 : 14:37:12
|
| And be aware that this is a 24x7 environment.Thanks |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cz
Starting Member
19 Posts |
Posted - 2010-09-08 : 15:49:59
|
| Thanks tkizer.So it is impossible with data mirroring, I guess log shipping wont work either, but how about replication :1) do table reindexing or other maintenance on subscriber2) promote it to publisher3) previous publisher becomes subscriberYour feedback is much appreciated. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cz
Starting Member
19 Posts |
Posted - 2010-09-08 : 16:03:37
|
| SQL 2005 Ent. We have about 40 databases with 1.2T in total, and 2 SANS with Fiber connection.We can upgrade to 2008 Ent if needed.Thx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cz
Starting Member
19 Posts |
Posted - 2010-09-08 : 16:19:48
|
| Not only for the table reindexing, we could also use this for other maintenance tasks, which may take long time. So we would like to have a second server which we can run tasks on and also can bring in sync with production.I like the bidirectional replication idea. I don't need load balancing at this point, so only one way replication is active at any time, is this correct?Thx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-08 : 16:27:10
|
| I don't know, that would be up to you. It will be very complex to implement.What other maintenance tasks are you referring to?I have a 1TB database that gets 3000 transactions per second. I don't have any need to do my maintenance tasks on a second server except for DBCC CHECKDB. For the DBCC CHECKDB part, we do backup/restore to a different server and then run DBCC CHECKDB there, as is the best practice.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
cz
Starting Member
19 Posts |
Posted - 2010-09-08 : 16:41:20
|
| Thanks Tara for your valuable points!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-08 : 19:41:06
|
| just FYI: replication will not replicate reindex scripts.. replication will only replicate transactions - insert/update/delete/stored procs etc.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-09 : 12:18:43
|
| The OP seems to think that he can run maintenance on subscriber while the publisher is serving production users, and then switch direction to the subscriber and continue the workload.. what I was trying to alert was, if he is doing maintenance on subscriber while the production was taking on load, the transactions are going to queue up in the distribution db since distrib wont be able to apply them on the subscriber until the maintenance is done.. this will increase the latency.. when the maintenance is done, depending on the amount of transactions generated it can be a while before subscriber catches up with publisher. At this point, if he wants to redirect production users to subscriber they need modify the connection string in the application server which means downtime.. Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-09 : 13:46:04
|
| agree... my recommendation would be to schedule a downtime each week and do the maintenance during that window.. the business needs to know/understand this maintenance is important for efficient workload during the week..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-10 : 20:32:32
|
| I meant reindexing.. online indexing is a great option... but not everyone uses it...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
sql mirroring + table reindex