SQL Server Database Migration Checklist

This guide is meant to provide general recommendations for migrating SQL Server databases from one server to another.

Always test database migrations (and subsequent application connectivity) in a lab environment prior to the actual migration.

Note: Server name ‘SOURCE’ is the original SQL Server, ‘TARGET’ is the new SQL Server.

1.1 Database migration checklist

The migration should be conducted in three phases:

1) Pre-migration inventory / checks

2) Database migration

3) Post-migration configuration / checks

1.1.1 Pre-migration phase

For the pre-migration phase, review and document the following tasks on the SOURCE server.

1) Database sizes

2) Data and Log file location

3) Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, etc.)

4) Collect the information of dependent applications, make sure application services will be stopped during the database migration

5) Database logins, users and their permissions

6) Dependent objects (SQL Agent Jobs and Linked Servers)

7) Document maintenance plans

On the TARGET server, conduct the following tasks:

1) Verify adequate disk space exists

2) Appropriate folders have been created

3) SQL Server is correctly installed and configured with latest Service Pack and Cumulative Update

4) Set SQL Server properties; memory, tempdb size, autogrowth and drive location.

5) Appropriate connectivity exists with application servers, web servers, etc.

6) Create databases consistent with planned database migrations

1.1.2 Database migration phase

1) From SOURCE server, get recent list of DB and file location list

2) On SOURCE SQL Server, Script migrate user LOGIN information between servers, save output for subsequent step.

3) Use native SQL Server to back up databases from SOURCE, use backup compression to reduce size of backup (optionally, use detach database, copy db files, then reattach database)

4) On SOURCE server, place databases in Read Only mode

5) Restore databases to TARGET

6) On TARGET SQL, apply script to migrate users/group security (Created in Step 3)

7) Update any custom SSRS DSNs to point to TARGET server.

8) On SOURCE server, script any required SQL Server Agent Jobs (select Job, right click, Script Job as > CREATE to > File ) to create

9) On TARGET, using the file create, then transfer/execute job creation on TARGET. Create schedules as needed.

1.1.3 Post-migration phase

1) Point the application to the new DB server address, or Availability Group LISTENER for AlwaysOn Availability Groups (Connection strings, etc. to altered by the application support team)

2) Restart / review Network connections between all stake holding servers (Network Team)

3) Check the SQL Server Error Log and Windows Error logs for any failures

a. Confirm application functionality with end users

1.2 Transfer logins

If you ever have a need to migrate SQL Server databases between servers; particularly a backup/restore scenario, you need to be aware that the database security login ids are not automatically migrated. Here are some notes on this task.

Once databases are backed up from one instance and restored to another instance, database security needs to be transferred as well.

This task can be a challenge, fortunately Microsoft has created a SQL script that can be used for this purpose. This can be found here:

How to transfer logins and passwords between instances of SQL Server

The process is well documented. The steps are as follows:

1. From the transfer logins and passwords web page, copy/paste the SQL script to SQL Server Management Studio (SSMS) running on the SOURCE server.

2. Execute the script – it will create a new stored procedures on the Master database.

3. From SSMS, run the following query:

4. EXEC sp_help_revlogin

5. The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

6. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

7. Important Before you go to step 5, review the information in the "Remarks" section. (Note: see web page mentioned earlier)

8. Open a new Query Editor window on TARGET server, and then run the output script that is generated in step 3.

Reference: http://support.microsoft.com/kb/918992

Downloading updates fails zero percent complete

Examined a server recently that refused to download deployed security updates. This server was also a Distribution Point in a remote location. Further, this site is running CM Current Branch.

Software Center presented the following:

image

DataTransfer.log showed something similar to the following:

Successfully sent location services HTTP failure message.

Error sending DAV request. HTTP code 500, status 'Internal Server Error'

GetDirectoryList_HTTP('http://<server>:80/SMS_DP_SMSPKG$/<guid>') failed with code 0x87d0027e.    DataTransferService  4/29/2016 8:51:36 PM  3028 (0x0BD4)

Error retrieving manifest (0x87d0027e).  

After review, content boundaries were correct, DP was installed correctly. Enabling HTTP anonymous authentication did not help.

Found a similar problem on the web, with a tip to enable ASP.NET 4.5:

Adding the ASP.NET 4.5 Role (and proposed Features) under Roles and Features > "Web Server (IIS)" > "Web Server" > "Application Development" solved our issue.

For more details see http://go.microsoft.com/fwlink/?LinkID=216771

Took these steps, rebooted the server and all is well.

New Power BI dashboard for CM and Intune Hybrid

This is one of the nicest offerings that I have seen for a while… using PowerBI and access to the CM database.

Highly recommend!

Some sample screen shots:

 

Details on the offering, including the download link:

Exploring your System Center Configuration Manager and Microsoft Intune (Hybrid) data on Power BI Dashboard

https://blogs.technet.microsoft.com/configmgrteam/2016/04/01/exploring-your-system-center-configuration-manager-and-microsoft-intune-hybrid-data-on-power-bi-dashboard/

The SQL Server 2014 Upgrade Advisor

 

1.1 SQL Server Upgrade Readiness

One of the best ways to proceed with SQL Server upgrades is by running the Upgrade Advisor. The SQL Server 2014 Upgrade Advisor analyzes the configuration of installed SQL Server components, and then generates a report of issues that you must address to assure a successful upgrade.

1.1.1 To install the Upgrade Advisor

Either use the Upgrade Advisor installation file (sqlua.msi) in the redist folder of the installation media or download the *msi as part of the Microsoft SQL Server 2014 Feature Pack.

The best to way to get the items you need, use this download link:

Microsoft SQL Server 2014 Feature Pack

https://www.microsoft.com/en-us/download/details.aspx?id=42295

Select SQLDom.msi and SQLUA.msi (x64)

clip_image002

SQL Server 2014 Feature Pack Download

Install the downloaded files in the following order:

1. Install SQLDom.msi

2. Install SqlUA.msi

1.1.2 To Run the Upgrade Advisor

From the Start menu, lauch the SQL Server 2014 Upgrade Advisor.

clip_image004

SQL Server 2014 Upgrade Advisor – Home page

Start the Launch Upgrade Advisor Analysis Wizard. Click Next.

clip_image006

SQL Server 2014 Upgrade Advisor Analysis Wizard

Enter the SQL Server name, select components that you need to evaluate and click Next.

clip_image008

SQL 2014 Upgrade Advisor – Select Components

Select the Instance name, and appropriate Authentication method, click Next.

clip_image010

SQL 2014 Upgrade Advisor – Connection parameters

Select the appropriate databases to analyze, optionally select Analyze trace files and/or SQL batch files, click Next.

clip_image012

SQL 2014 Upgrade Advisor – Select Databases

Review Confirmation settings, then click Run.

clip_image014

SQL 2014 Upgrade Advisor – Confirm Selections

Upgrade wizard result dialog. The Launch Report button will display more detailed results.

clip_image016

SQL 2014 Upgrade Advisor –Results

1.1.3 View Upgrade Advisor Report

The Upgrade Advisor report can be run from the SQL Server 2014 Upgrade Advisor, shown at the begging of the last section, or from the completion dialog. Review and resolve any issues prior to the upgrade.

An example of a successful upgrade analysis report is shown next.

clip_image018

SQL 2014 Upgrade Advisor – Reporting

Use PowerShell to Install Server 2012 roles and features for CM

Using PowerShell to install the Server 2012 roles and features for Configuration Manager is really super easy. Discovered an issue installing several features as the script needed access to the server 2012 source.

Open PowerShell as an administrator, modify the –Source parameter (below) to point to the proper location for sources\sxs (note: UNC will work as well).

image

Get-Module servermanager
Install-WindowsFeature Web-Windows-Auth
Install-WindowsFeature Web-ISAPI-Ext
Install-WindowsFeature Web-Metabase
Install-WindowsFeature Web-WMI
Install-WindowsFeature BITS
Install-WindowsFeature RDC -Source K:\sources\sxs
Install-WindowsFeature NET-Framework-Features -Source K:\sources\sxs
Install-WindowsFeature Web-Asp-Net -Source K:\sources\sxs
Install-WindowsFeature Web-Asp-Net45 -Source K:\sources\sxs
Install-WindowsFeature NET-HTTP-Activation -Source K:\sources\sxs
Install-WindowsFeature NET-Non-HTTP-Activ -Source K:\sources\sxs

Proper TempDB creation for Configuration Manager

As a consultant, one of the major issues I see with SQL Server configuration for Configuration Manager is allowing default settings for TempDB.

The default TempDB size for all versions of SQL Server (prior to SQL 2016 – more on this later), is set to 8MB, file growth 1 MB.

Recently checked a Configuration Manager site SQL Server database properties, and TempDB had grown to 1.5 GB. This was with the default settings of 8MB size, 1 MB file growth. Can you imagine how many data file fragments occurred to get to 1.5GB?!

When SQL Server (MSSQLServer) service is restarted, TempDB gets reset to? The default size! And, the fun starts all over again.

First steps in correcting this; estimate the size of TempDB. There are some great calculators out there, Kent Agerlund did a nice job taking standard MSFT recommendations and converting it to an Excel “calculator”. This is a great starting point to determine the total size of TempDB, based on estimated client counts. Typically TempDB will be approximately 25-30% of the total, estimated CM DB size.

The only flaw with this calculator, it calculates a single TempDB file. TempDB can greatly benefit from multiple data files.

Let’s work through an example:

Projected # clients

10,000

Estimated CM data size

54 GB

Estimated TempDB size

16 GB

Making the assumption that your server has at least 2 processors and 8 cores, you’d want to start TempDB at least four (4) equal sized files, of 4GB each. Now, there may be some benefit in creating TempDB with eight (8) total, equal sized data files. Under no circumstance should you create more than 8 data files.

Now, to create, you can use the SQL Server management studio and add the files… even easier, use PowerShell.

To create using PowerShell follow the following instructions:

Open an elevated PowerShell prompt, create the additional SQL TempDB database files and set their initial size to a total of 16 GB by running the following command (the command is wrapped and should be one line):

Invoke-Sqlcmd –QueryTimeout 0
-InputFile C:\Setup\Scripts\ConfigureTempDB.sql

clip_image002

The SQL TempDB database files.

Note: change paths as appropriate – sample script can be downloaded for free, from book sample files, from my Reporting Services book.

As a side note, SQL Server 2016 now creates the following default TempDB settings. Probably still too small for most production instances. None the less, much better!

image

The SQL Server 2016 TempDB database files.

Intune – Mobile Application Management policy

What are Mobile Application Management policies?

According to Microsoft:

Mobile application management policies in Microsoft Intune let you modify the functionality of apps that you deploy to help bring them into line with your company compliance and security policies. For example, you can restrict cut, copy and paste operations within a managed app, or configure an app to open all web links inside a managed browser.

Source: https://technet.microsoft.com/library/dn878026.aspx

For example; when you first attempt to deploy a managed application, such as Skype for Business, you may receive the following informational dialog.

"The software you are trying to deploy must be associated with a mobile app management policy and there are currently none defined. Create a policy from the Policy workspace."

clip_image002

Application Policy Informational

To create an Application policy (sometimes called MAM), choose New Policy, then Mobile Application Management (platform), then choose Create a policy with the recommended settings, click Create Policy.

Note: This policy is not deployed, it will be associated with a subsequent app deployment. Also, note the available Managed Browser policies.

clip_image004

Policy – Create Mobile Application Management

Resultant MAM policy.

clip_image006

Policy – Mobile Application Management

The only modification made to the default MAM policy, was allow the device settings to control the encryption.

clip_image008

Policy – Mobile Application Management Properties

Now we are ready to Deploy Skype for Business. This application was deployed using the standard process. At the Mobile App Management step, associate the app with the App Management Policy, created in the last step. Click Next, through to Finish.

clip_image010

Deployment – with MAM policy

Resources:

Configure and deploy mobile application management policies in the Microsoft Intune console

https://technet.microsoft.com/library/dn878026.aspx

Multi-Identity and Mobile App Management with Microsoft Intune

http://blogs.technet.com/b/microsoftintune/archive/2015/07/21/multi-identity-and-mobile-app-management-with-microsoft-intune.aspx