Migrate Microsoft SQL 2005/2008 to MS SQL 2016
- 07/09/2016 5:29 PM
ServerFreak are replacing our legacy MS SQL 2005 and MS SQL 2008 servers with MS SQL 2016.
We recommend you begin migrating your databases to SQL Server 2016 as soon as possible, in order to take advantage of the new features and performance improvements it provides, as well as allowing yourself plenty of time to conduct your migration in a controlled manner.
Below are the dates we will be switching off the old SQL Servers, we recommend migrating early so we can assist with any unlikely issues that you may have with your migration.
|SQL Server Name||Service Termination Date|
|MS SQL 2005||October 1st 2016|
|MS SQL 2008||December 1st 2016|
You will need to Login to MSPControl Panel (Previously known as WebsitePanel/DotNetPanel) at http://cp.dnpserver.com
Locating connection string.
Using either FTP or File Manager, locate and edit the file on your webspace that contains your MS SQL connection string, typically this is in web.config file on asp.net based websites, other languages such as asp or php will have different files, consult your website application documentation or your developer if you need help locating this file.
Locate Your Database
Make a backup of your database
In MSPControl menu, go to WEB > Databases, you should see MS SQL 2005/2008 and 2016 (you should see at least 2 SQL versions, either 2005 or 2008 and 2016, if you only see the legacy version but not 2016, contact support)
Click the Legacy version ie MS SQL 2005 or MS SQL 2008.
Your list of databases will load.
Once you click the Legacy SQL Version you will see a list of databases.
This example below has just 1 SQL database and 1 SQL user
Backup the database (do this for each database listed)
Click the database name
expand maintenance tools
Make a note of the file name
check "zip backup" if its not already selected
backup destination: Copy to Folder and set it to the root folder \ as show below
Click backup button below.
You will be returned to the previous page if the backup was sucessful.
Create the new SQL 2016 database & SQL User
In MSP Control go to web menu, databases and choose MS SQL 2016
Create a new database and a new MS SQL User, when creating a user, assign the user to the database you've just created.
We recommend you create a new password for your SQL user, make the password unique and strong, we recommend you visithttp://passwordsgenerator.net/ to generate a secure password. Make a note of the password in a text editor such as notepad as you'll need this later.
Restore your data to the new database
On the SQL 2016 page, click your newly created database
from the maintenance menu click Restore database
Choose "Hosting Space"
Select the file we created earlier in step 3
if sucessful you'll be returned to the database page without any errors being displayed on screen.
update your sql connection string
Go back to your database connection string from step 1
update the following:
SQL Server address/IP Address to: 22.214.171.124
Update your SQL Database Name, SQL username & SQL Password if these were changed.
Save/upload the file and check your application is working okay connecting to SQL 2016.
if you get any error, check you have updated the IP, the database name/username/password/
Remove the old user & database
Go back to your legacy SQL 2005 or 2008 database page
Delete just the SQL user
- check your website/application still works, as if you were acidently using the legacy SQL and not 2016, you'll now get an error
if your website/application still works, now delete the legacy SQL database too.
This is the end of the migration, your SQL database has now been upgraded.