Introduction
A task every DBA performs each year is to upgrade SQL Server Instances from lower to higher versions, which can be an in-place upgrade or parallel. The in-place upgrade uses the Microsoft SSMS tool and pretty straight forward if all goes well, though its usually is not a preferred method in most organizations.
The de facto method is to install the newer version of the SQL Instance and move the objects from older version. Additional tasks would include mapping users to databases, replicating source server permission for logins at the destination and sometimes keeping the passwords for SQL Logins intact. The task may involve migrating every objects or certain objects within the instance as per the business requirements. Any mid-level to senior-DBA will be familiar with this task.
However, what if we can have a tool to perform most of this task in situations, when IT personnel executing this is not a full-time DBA? What if a small business plans to upgrade its SQL Server but can’t afford hiring a Database Expert? What if a developer wants to periodically refresh his dev environment and wishes to do so himself (provided he has access to copies of backup files)? A DBA can run a batch job to copy these full backups every week to a folder. In-turn the developer can copy the backup files and perform the restore at his own time.
SQL Migrator
The tool we are evaluating today is "SQL Migrator" which is a 32-bit application that can address most of the challenges we have stated above. It’s free to download, simple to use and can assist IT professionals with limited SQL Server administration back ground to upgrade or Migrate their Environment. It can also be used by developers who wishes to periodically restore databases on his dev boxes without having to rely on a DBA. The tool can be downloaded from our website www.datamatetechnology.com . The two msi files under Microsoft® SQL Server® 2016 Feature Pack need to be installed on your machine before using this tool as listed below.
They are both available to download from: https://www.microsoft.com/en-us/download/details.aspx?id=52676
Why SQL Migrator
It makes life simple for the person executing the task without having to dwell deep into internal working knowledge of database administration. It directs the users to execute the task serially for the most common objects that get moved between Instances. The GUI based instruction make it extremely simple to follow every step in a sequential manner. However this version of the tool has few limitations when migrating certain objects like Credentials or Linked Server Providers.
Once the software is installed successfully, you will observe three options within the Datamate Technology Folder. The usability "HelpGuide" with instructions, the actual "SQL Migrator" software and the default folder where the application and system logs are created on the task performed. Lets click "SQL Migrator" to get started.
As you can see, there are two options to choose from. I will explain "Migrate All SQL Server Objects" under Part 2 of this article.
DB Refresher
Choose “Refresh User Databases“ and verify the pre-requisites. Click continue if the pre-requisites are met.
As the name suggests, this features helps developers\admins to refresh databases using an existing backup file. However, there are two specific limitations that may turn off some viewers here. Firstly, only Full Backups are supported using "With Recovery" option at this time. Secondly, during restoration, the data files and log files will be placed in default data and log folder as specified at the instance Configuration level. This cannot be modified and if these values are missing in the default parameters, the data and log files will be restored on the master datafile location of the target Instance. So, you may wonder, why would I be bothered to use this feature when I have so much more control with SSMS. To explain why, let’s continue to the next screen
1. The next screen gives you the option the enter the Target SQL Instance Name:
All Versions from SQL Server 2005 till SQL Server 2016 are supported. In our case, we are connecting to SQL Server 2014 Version. Only if you connect with sysadmin privileges can you move forward to the next step. In this case, my Windows Login is a sysadmin. Click Next.
2. You need to enter the folder name that contain the native Full Backup Files with ”. Bak” extension. A network share or mapped drive is also accepted. The logged in account should have Full Control permission on the folder.
When you click Verify Access, it performs the following checks for you to move forward
a. Scans only the Backup files with the “.Bak” Extension. The rest will be skipped. It reads only those Full Backup Files that are qualified to be restored. For Example, a backup file of a higher version (SQL Server 2016) will not be read as the Target Version is lower (SQL Server 2014).
(So, if your backup files\folders are unorganized, you don’t need to worry about finding the supported versions of backups.)
b. Adds the SQL Server Service account to the "folder security container" with Read Permissions. You also don’t have to worry about what account is used to run the SQL Server service. However, if you are using a non-domain account (Network service, Local system etc.), executing this folder permission may fail sometime. Under such circumstances, add read only permission manually to the folder for “Everyone”.
Click Next to continue.
3. Click “Show Databases” on this screen to view the database names for the backup files with Date and time denoting when the backup ran. Select the database(s) to restore under the list “Available Backups with Date” and click add. Once you have finished adding all the databases you want to restore, check if you wish to “overwrite any existing database” on the target or set the compatibility mode to the newer version (If allowed).
Click Migrate Databases. Relax and you observe the “Selected databases” get restored automatically and sequentially.
To review logs for activity performed or any error thrown, go to the “SQL Migrator Log Folder” present under the start menu Hive of the application.
Read Part 2 on using the feature "Migrate All SQL Server Objects".
References and Acknowledgements
We like to thank Chrissy Lemaire, an MCC and an MVP who has written some amazing PowerShell scripts to manage SQL Server environments using PowerShell. One such script that inspired us to develop this tool can be found on Microsoft TechNet Site.
https://gallery.technet.microsoft.com/scriptcenter/Use-PowerShell-to-Migrate-86c841df
