November 1, 2017 at 4:07 am
Hello All,
I would like to upgrade my SQL Server 2012 to SQL Server 2016.
I created new machine and i would like to backup all the Databases and restore them on the new machine.
I will use sp_help_revlogin in order to export and import the logins.
In the end of the process i will change the Application connection string to the new machine name.
My question is - what is the order of this process?
Should i export and import the logins with sp_help_revlogin first and then to restore the databases?
or should i restore the databases first and only than to export and import the logins with sp_help_revlogin?
Can you please advise?
Thanks in advance!
November 1, 2017 at 4:21 am
I restore the databases first then run the script created by sp_help_revlogin.
Thanks
November 1, 2017 at 4:41 am
Before you change the application's connection string, do yourself a favour and create a DNS alias for the new server. Call it SQLMyApplication or something that fits your organisation's connection string. That way, the next time you move the databases to a different server, you'll only need to point the DNS alias at the new server, and you won't need to touch the configuration of the application.
John
November 1, 2017 at 6:39 am
Hi NorthernSoul,
Thank you for the answer!!!
Hi John Mitchell-245523,
An alias DNS is not an option - due to very bad experience.
November 1, 2017 at 7:56 am
Please have a look at the Migration checklist. Hope this helps
https://www.mssqltips.com/sqlservertip/1936/sql-server-database-migration-checklist/
November 1, 2017 at 9:03 am
89netanel - Wednesday, November 1, 2017 4:07 AMHello All,
I would like to upgrade my SQL Server 2012 to SQL Server 2016.
I created new machine and i would like to backup all the Databases and restore them on the new machine.
I will use sp_help_revlogin in order to export and import the logins.
In the end of the process i will change the Application connection string to the new machine name.My question is - what is the order of this process?
Should i export and import the logins with sp_help_revlogin first and then to restore the databases?
or should i restore the databases first and only than to export and import the logins with sp_help_revlogin?Can you please advise?
Thanks in advance!
I was able to seriously minimize down time when we did the same thing when we migrated to 2012. Disclaimer, I'm doing this from memory and, hopefully, haven't forgotten anything and hopefully have the correct order.
First, you need to do some preparation. One of the big things is that you need to check all stored code for the presence of things like @@SERVERNAME and a couple of other things that I can't remember off the top of my head. If you have ANY, then you need to search the web for the script that will rename what that returns so that you can rename it to be the same as the old server or every bit of code that uses it will fail.
Since you're migrating to 2016, you also need to do a search for the Trace Flag that will set the system to use the OLD Cardinality Estimator and have a script ready to rock to not only immediately change it server wide but to also update the startup parameters. The new Cardinality Estimator killed performance on several of our larger stored procedures and we just didn't have the time to rewrite them and retest them.
The "new" Upgrade Advisor was going to send MS way too much information about our system when we did the migration from 2012 to 2016. You should check the "discontinued" list for 2014 and 2016 and make sure none of that is present in your code if, like us, you decide against running the advisor.
Already knowing how long all backups would take (we'll call that time "X") because of the history I had, I wrote a script to generate two scripts. One to change all databases back to their current recovery model, whatever is was, and another to change all databases to the FULL recovery model. The backup routines I wrote previously auto-magically add transaction log backups to any databases that are in FULL recovery and have had a proper full backup.so didn't have to worry about log files so much. Of course, the full backup routine includes ALL databases including the system databases.
You also need to write a script that will take a DIF backup of every database and another that will take a "Tail Log" backup.
Last and certainly not least, if you have any SQL Agent jobs you want to migrate (and you will), you should script all of those out. We didn't have any SSIS, SSRS, or any of the other 4 letter words in SQL Server so I can't help there but, if you do, make sure that you know how to migrate that stuff, as well and I'm not talking about just the jobs. Rumor has it that you'll need to change connection information in every (for example) SSIS package there is and that can take a very long time. Hopefully someone will chime in here on an easier way to do it.
You also need to write a script that will zing through all database restored to the new box and change them to the 2016 compatibility level.
You also need to write a script to bring any OFFLINE user databases back online in case the poo hits the fan. You see what that's used for later.
You also need to write 3 scripts concerning backups. One that will restore all user database with NO RECOVERY. One that will find and restore all the DIF files with NO RECOVERY. And, one that will find and restore all the Tail Log backups WITH RECOVERY.
Last but not least, setup a web server that's the same as the existing web servers except pointed to the new box.
I can't stress the following enough. You need to make sure all of this is going to work before the big day. To do that, Test everything except the final Tail Log Backups on the new box several days before the big day to make damned sure you have anything unexpected that will happen on the big day. That means that you'll need to do a WITH RECOVERY after the DIF file restores below. AND, this also means testing the apps using the new web server against the new box.
For the backup/restores on the big day, here's what we did...
1. When the day comes but well before the scheduled outage, take a FULL backup of all the user databases and then immediately restore them to the new server but don't allow them to recover... we still have a DIF and a log file to restore. That should already be in the script but wanted to say it again. The original system remains online for this.
2. About a half hour before the scheduled outage and making sure that all the FULL backups have completed, turn off all backup jobs and then run the script that will do DIF backups on all the user databases. Immediately run the script that will find and restore all of those DIF backups to the new server. Again, as a reminder, this must be done with NO RECOVERY, which should be the way the script was written. At this point, the original server is still viable and handling transactions just like any other time.
You might also want to run your sp_revlogin thing to capture any late additions.
3. When the outage time starts, run the script to take TAIL LOG backups of all the user databases. This will also take all those databases OFFLINE so that even if they forget a web server or other thing that hits on the database, no new data will be written to the databases. Immediately run the script that will find and restore all those TAIL LOG backups being sure to use WITH RECOVERY (which should be built into the script already).
4. Run the script to change the compatibility of all the user databases to the correct level (supposedly all 2016).
5. Run the script to change ownership to the correct logins.
6. Run your other scripts like the sp_revlogin script you generated, the SQL Agent job scripts, SSIS corrections, whatever, etc, etc.
7. Using the web server you'd previously setup, do your smoke tests to make sure everything is running correctly.
8. Test a couple of jobs (like backups, etc) to make sure all that's working.
9. If all that's good, then start repointing the web servers and other things that need to point to the new box and you're done. Well, unless you have performance issues in which case you'll need to run that script that enables the Trace Flag to use the old cardinality estimator. Of course, you should be logging the things that do have performance problems so you can fix them in the future and finally disable the trace flag.
If the proverbial poo hits the fan at any time, "just" turn off or repoint any webservers back to the original box and run the script that will bring all of the databases back online. Then you have time to figure out what you did wrong.
Total downtime for our restores was actually measured in minutes. The sanity tests afterwards was what took the longest.
The bottom line is that this isn't as easy as people think. You need to make a plan, stand up some boxes, and test the hell out of it all well BEFORE the big day. If you don't think it's worth it, then you may be unfortunate enough to find out that it actually would have been worth it. Our migration went almost flawlessly with only some connection issues to fix on the web servers thanks to having to alias the box with the same name, which we weren't able to test previously.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2017 at 9:19 am
Jeff Moden - Wednesday, November 1, 2017 9:03 AMLast and certainly not least, if you have any SQL Agent jobs you want to migrate (and you will), you should script all of those out. We didn't have any SSIS, SSRS, or any of the other 4 letter words in SQL Server so I can't help there but, if you do, make sure that you know how to migrate that stuff, as well and I'm not talking about just the jobs. Rumor has it that you'll need to change connection information in every (for example) SSIS package there is and that can take a very long time. Hopefully someone will chime in here on an easier way to do it.
Very comprehensive, Jeff, as usual!
I haven't used SSIS in anger since SSISDB was brought in, but certainly in the days when you had package files, you had the choice of whether to use package configurations or not. If you didn't, then all server names, share names, database names and everything else that might change from one environment to another would be hard-coded, and you'd have to open each package file and change them, hoping you didn't miss anything. If, on the other hand, you took the time to do it properly when the packages were developed, it would be much easier and you'd only need to change configuration files (or tables).
89netanel - Wednesday, November 1, 2017 6:39 AMAn alias DNS is not an option - due to very bad experience.
I'm intrigued to know what the experience was! But the DNS alias is still an option, even if something went wrong last time. Learn from that experience and make sure it goes well next time.
John
November 1, 2017 at 7:12 pm
Thanks for the info on the SSIS connections, John. I knew the latter was possible in later editions of SSIS but didn't know such a thing could be done in, say, SQL Server 2005. Of course, you hit the nail squarely on the head when you say that someone with some good knowledge had to actually have the foresight to do it that way.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2017 at 3:30 am
I don't use configurations (Configuration files) in my packages, I develop and test them on my dev system and change any data source entries in the SQL job steps in production.
We are in the process of preparing migrating to 2016, all I did for one job (91 SSIS packages) is script it out, edit the output to globally change the server name and run the script on the new server,easy peasy lemon squeezy 😀
However changing all the packages for 2016 was the pain, but then that is another story........ :blink:
Far away is close at hand in the images of elsewhere.
Anon.
November 2, 2017 at 3:37 am
David Burrows - Thursday, November 2, 2017 3:30 AMI don't use configurations (Configuration files) in my packages, I develop and test them on my dev system and change any data source entries in the SQL job steps in production.
We are in the process of preparing migrating to 2016, all I did for one job (91 SSIS packages) is script it out, edit the output to globally change the server name and run the script on the new server,easy peasy lemon squeezy 😀
However changing all the packages for 2016 was the pain, but then that is another story........ :blink:
Yes, even better! I suppose that could be thought of as using the job step as the configuration file. I think you still need to take the trouble to make those entries configurable during development, don't you? At least, you would have done in the old days.
John
November 2, 2017 at 4:16 am
John Mitchell-245523 - Thursday, November 2, 2017 3:37 AMI suppose that could be thought of as using the job step as the configuration file.
Yes it could be thought of that way.
I think you still need to take the trouble to make those entries configurable during development
Depending on your environment it could be. I have one dev, one UAT and one Production server so it is easier for me. I set the connections in the package to dev for testing and just override them in the SQL jobs, either UAT or Production. You can even override them when executing the package via GUI or DOS.
I did try using config files in the beginning but could not get them to work properly probably due to my lack of experience or stupidity :crying:. But now using config files local to the server could be a lot easier, even for me.
Far away is close at hand in the images of elsewhere.
Anon.
November 2, 2017 at 5:49 am
FWIW (free) dbatools may help you out if you are open for a bit of Powershell
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2017 at 8:35 am
Same thing but different approach:
Create the new server with a new IP and add a U to the end of the server name (for Upgraded - for example OldServerNameU). Do all the work indicated above on the U server then after all DB's are restored and on-line on the new U server and ready to cut-over:
You will have to do a sp_dropserver/sp_addserver on the upgraded SQL/server instance name to change the name of the SQL instance to match the old SQL Instance Name. If it was a named instance then you would have to be sure and install the new SQL named instance with the same name as the old SQL Named Instance name as you can't rename a named instance like you can a default SQL Instance:
Named Instance:
SELECT @@ServerName
EXEC master.dbo.sp_dropserver 'NewServerNameU\OldSQLInstanceName'
EXEC master.dbo.sp_addserver 'OldServerName\OldSQLInstanceName', @local = 'local'
Default Instance:
SELECT @@ServerName
EXEC master.dbo.sp_dropserver 'NewServerNameU'
EXEC master.dbo.sp_addserver 'OldServerName', @local = 'local'
Now all apps still work as expected as the same server name/ip is in play on the upgraded server as no connection strings changes are required.
If things go south you can quickly reverse the order and re-ip/re-name the new and old servers back and then bring the old DB's back on-line for a back-out plan (side by side SQL migration). After 30 days then the old server can be decommissioned.
November 7, 2017 at 4:13 am
restore the databases first then the logins.
if you have a login with a default database that doesnt exist the script will error
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 8, 2017 at 9:59 am
We have done a lot of similar successful upgrades this year. We used a powershell script to drive the process. The sequence of actions we had is as follows:
1. Backup/Restore databases - with consideration to changing file locations.
2. Create the SQL Server logins - script generated by sp_revlogin or dbatools. I preferred dbatools as it included server role memberships.
3. Resolve orphaned database users - the DB users will be orphaned!
4. Create SQL agent operators*
5. Create credentials and proxies*
6. Create linked servers*
7. Create SQL jobs*
* from script generated from the source servers.
We used DNS aliases for SQL Servers that host a multitude of applications. We had two aliases pointing to the new server: one alias was named like the old server; this enabled existing apps to connect to the new server without any app configuration changes. The other new alias was named to conform to a new naming standard that is transparent of the server name.
We also had issues with the new cardinality estimator especially for complicated views and stored procedures. Our solution was to set the databases' compatibility still to SQL 2016 but set the LEGACY_CARDINALITY_ESTIMATION =ON. We only do this if we see a performance hit in SQL 2016.
Hope this helps!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply