January 10, 2018 at 12:15 pm
All,
I will be kicking off a migration project very soon wherein I will setup DB Mirroring in order to do a final cut off at the planned date saving me sometime. Currently, we have around more than 50 databases sitting around to be migrated. I obviously don't want to do everything one by one. I was hoping if there is a script or someone might have used it before that would basically mirror all the user dbs and then at the day of migration I could just use another T-SQL that would loop through the databases synchronize it and fail it over and once everything is confirmed okay we can remove mirroring looping through the databases.
January 10, 2018 at 12:26 pm
ffarouqi - Wednesday, January 10, 2018 12:15 PMAll,I will be kicking off a migration project very soon wherein I will setup DB Mirroring in order to do a final cut off at the planned date saving me sometime. Currently, we have around more than 50 databases sitting around to be migrated. I obviously don't want to do everything one by one. I was hoping if there is a script or someone might have used it before that would basically mirror all the user dbs and then at the day of migration I could just use another T-SQL that would loop through the databases synchronize it and fail it over and once everything is confirmed okay we can remove mirroring looping through the databases.
Have you ever set up mirroring?
January 10, 2018 at 1:09 pm
Are you setting up mirroring just for the migration? That would seem to be creating extra work. Database backups from the old server and restores to the new server can be scripted. If you are restoring to a new 2016 server, then the databases will be upgraded during the restore. We migrated from SQL Server 2008 failover clusters to 2014 a few years ago, and in-place upgrade to 2016 this year, and the backup/restore part wasn't very painful.
January 10, 2018 at 1:42 pm
RandomStream - Wednesday, January 10, 2018 12:26 PMffarouqi - Wednesday, January 10, 2018 12:15 PMAll,I will be kicking off a migration project very soon wherein I will setup DB Mirroring in order to do a final cut off at the planned date saving me sometime. Currently, we have around more than 50 databases sitting around to be migrated. I obviously don't want to do everything one by one. I was hoping if there is a script or someone might have used it before that would basically mirror all the user dbs and then at the day of migration I could just use another T-SQL that would loop through the databases synchronize it and fail it over and once everything is confirmed okay we can remove mirroring looping through the databases.
Have you ever set up mirroring?
What made you feel that and you asked me that question?
January 10, 2018 at 1:44 pm
lptech - Wednesday, January 10, 2018 1:09 PMAre you setting up mirroring just for the migration? That would seem to be creating extra work. Database backups from the old server and restores to the new server can be scripted. If you are restoring to a new 2016 server, then the databases will be upgraded during the restore. We migrated from SQL Server 2008 failover clusters to 2014 a few years ago, and in-place upgrade to 2016 this year, and the backup/restore part wasn't very painful.
Yes it is only for the migration. I have a limited downtime and I can't afford to backup 50+ databases all at once and then do the restore. Every scenario is different. I am not sure if the question was understood correctly I know what I am doing I just want an automated way of doing it rather manually doing it for each and every DB which will consume a lot of time.
January 10, 2018 at 1:54 pm
ffarouqi - Wednesday, January 10, 2018 1:44 PMlptech - Wednesday, January 10, 2018 1:09 PMAre you setting up mirroring just for the migration? That would seem to be creating extra work. Database backups from the old server and restores to the new server can be scripted. If you are restoring to a new 2016 server, then the databases will be upgraded during the restore. We migrated from SQL Server 2008 failover clusters to 2014 a few years ago, and in-place upgrade to 2016 this year, and the backup/restore part wasn't very painful.
Yes it is only for the migration. I have a limited downtime and I can't afford to backup 50+ databases all at once and then do the restore. Every scenario is different. I am not sure if the question was understood correctly I know what I am doing I just want an automated way of doing it rather manually doing it for each and every DB which will consume a lot of time.
I don't mean to be blunt but I suspect that you have not worked with mirroring much. If you are looking for a way to failover all databases at once, and (a big) IF, there is witness instance in the mix, all you have to do is to stop sql server service on the node running all principals. The failover will occur automatically.
January 10, 2018 at 4:35 pm
RandomStream - Wednesday, January 10, 2018 1:53 PMffarouqi - Wednesday, January 10, 2018 1:44 PMlptech - Wednesday, January 10, 2018 1:09 PMAre you setting up mirroring just for the migration? That would seem to be creating extra work. Database backups from the old server and restores to the new server can be scripted. If you are restoring to a new 2016 server, then the databases will be upgraded during the restore. We migrated from SQL Server 2008 failover clusters to 2014 a few years ago, and in-place upgrade to 2016 this year, and the backup/restore part wasn't very painful.
Yes it is only for the migration. I have a limited downtime and I can't afford to backup 50+ databases all at once and then do the restore. Every scenario is different. I am not sure if the question was understood correctly I know what I am doing I just want an automated way of doing it rather manually doing it for each and every DB which will consume a lot of time.
I don't mean to be blunt but I suspect that you have not worked with mirroring much. If you are looking for a way to failover all databases at once, and (a big) IF, there is witness instance in the mix, all you have to do is to stop sql server service on the node running all principals. The failover will occur automatically.
I have worked with Mirroring in the past so this is not new to me. We don't have a witness in the mix and I don't want to do a force failover. Unfortunately, the way you are describing me is not what I am looking for. I am not sure if you did understood my question correctly. I am looking for a script that would automate my mirroring setup instead of me doing it manually one by one. I am least bothered about the failover stuff I can take care of that afterwards that is not an issue. Hopefully, next time you be blunt please proof read the question and then comment about whether the person knows the subject or not. In case if you have ever done anything like this in the past and if it makes sense for you just share the script or else best of luck bro.
January 10, 2018 at 4:46 pm
ffarouqi - Wednesday, January 10, 2018 4:35 PMRandomStream - Wednesday, January 10, 2018 1:53 PMffarouqi - Wednesday, January 10, 2018 1:44 PMlptech - Wednesday, January 10, 2018 1:09 PMAre you setting up mirroring just for the migration? That would seem to be creating extra work. Database backups from the old server and restores to the new server can be scripted. If you are restoring to a new 2016 server, then the databases will be upgraded during the restore. We migrated from SQL Server 2008 failover clusters to 2014 a few years ago, and in-place upgrade to 2016 this year, and the backup/restore part wasn't very painful.
Yes it is only for the migration. I have a limited downtime and I can't afford to backup 50+ databases all at once and then do the restore. Every scenario is different. I am not sure if the question was understood correctly I know what I am doing I just want an automated way of doing it rather manually doing it for each and every DB which will consume a lot of time.
I don't mean to be blunt but I suspect that you have not worked with mirroring much. If you are looking for a way to failover all databases at once, and (a big) IF, there is witness instance in the mix, all you have to do is to stop sql server service on the node running all principals. The failover will occur automatically.
I have worked with Mirroring in the past so this is not new to me. We don't have a witness in the mix and I don't want to do a force failover. Unfortunately, the way you are describing me is not what I am looking for. I am not sure if you did understood my question correctly. I am looking for a script that would automate my mirroring setup instead of me doing it manually one by one. I am least bothered about the failover stuff I can take care of that afterwards that is not an issue. Hopefully, next time you be blunt please proof read the question and then comment about whether the person knows the subject or not. In case if you have ever done anything like this in the past and if it makes sense for you just share the script or else best of luck bro.
Incidentally there was a new thread asking for similar script this morning. Unfortunately I do not have any handy and we have moved away from mirroring. Good luck.
January 11, 2018 at 4:20 am
This was removed by the editor as SPAM
January 11, 2018 at 7:21 am
As ever with SQL Server there are many ways to skin a cat, you could setup Transactional replication, provided your 50 databases are not constantly hammered.
1. Restore your 50 dbs on new server.
2. Configure replication, have your new server/instance as your distributor/subscriber. You can run scripts to set all this up
3. Come migration day, stop all user access on publisher server. Stop replication.
4. Point users to the new upgraded databases.
Above is only very high level, YMMV.
qh
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply