DallasDBAs.com

Explaining SQL Server in plain english

DBATools.io

SQL Server Migration and Upgrade

by 2 Comments

(This post written by Jon Biggs (t), one of our Senior DBAs)

We are currently performing migrations with upgrade of multiple-instance SQL Servers to new servers. The migrations are going smoothly (knock on wood), and I wanted to relay some information about the migration process. There are four phases you need to perform when migrating a SQL instance to a new server. These phases are Review, Prepare, Test, and Migrate. It sounds simple enough but let’s take a deeper look into what goes into each phase.

In this case, the Test server and instances are migrated, but the matching Production server and instances have not been. A followup blog is expected with any lessons learned.

Review

During the review phase we check the existing environment for best practices, the SQL databases for upgrade readiness and the target SQL server for appropriate sizing.

When it comes to best practices there are Microsoft SQL Server best practices, vendor best practices and industry best practices that need to be considered. Here are two new best practices that I dealt with during the migrations:

  • AWS EC2 best practice is to utilize Lock Pages in Memory (LPM). While LPM was not a new best practice to me, it was surprising to learn that AWS recommends this configuration when running SQL Server on EC2 VMs.
  • Microsoft SQL Server best practice when using FILESTREAM is to disable “last access time” and to disable “8 dot 3 naming” on the volumes where FILESTREAM data is stored. Disabling 8 dot 3 naming led me to the options of disabling this setting globally or per volume. It turns out that per volume is the default configuration for Windows Server.

All best practices need to be reviewed and discussed on whether they will be implemented or not. Check the current environment and determine why a best practice is not followed and if it should be followed.

If you are upgrading SQL Server to a new version during the migration, Microsoft provides a tool named the Data Migration Assistant to help determine database upgrade readiness. This tool will assess databases for compatibility issues by SQL Compatibility Level. You may find that you have issues that exist in your databases at your current compatibility level.

 

screenshot of Data Migration Assistant
(click to enlarge)

When migrating to a new SQL server this is a great time to determine if you need to modify the resources on the server. Do you need more or faster CPUs? Do you need more memory? Do you need larger storage volumes? This needs to be discussed and decided during the review step. Microsoft provides an advanced tool named the Database Experimentation Assistant which can be used to determine how your workload will perform on the target SQL Server. It will also provide query compatibility issues and degraded queries and query plans.

Prepare

During the prepare phase we are determining the migration method, making changes to service accounts and permissions, deciding on shared feature migration such as SQL Server Integration Services and SQL Server Reporting Services, configuring the target SQL Servers, and using Query Store for query regression review.

There are multiple methods of migrating the databases to a new SQL Server instance. These include:

  • Backup/Restore. You can simply backup the databases, copy the backup files to the new server and restore the databases. This can be a time-consuming process which you may not have the downtime for. A few things to help speed this up:
    • Backup the databases directly to the new server, eliminating the copy step. This requires the network share to allow the source SQL Server service account write permissions. This is the method I am using for the migrations as we have the time to do this, and we are using SQL Server Standard.
    • If you have a SAN, move the volume with the backup files to the target SQL Server, also eliminating the copy step.
    • You can perform a full backup of the databases ahead of time and restore them with NO RECOVERY. When the migration window starts you can perform a differential backup of the databases and restore them with RECOVERY. A caveat here is that you must perform Copy-Only backups if a full backup is required before the migration starts, otherwise you will break the backup chain.

There are a couple of tools to help make the backup/restore method easier.

  • DBATools.io is a set of Powershell commands for migrating databases, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, etc. A caveat with using dbatools to migrate databases with the Start-DbaMigration command is that it does not move Full Text Index Catalog(s) to a new location if you are migrating to different paths on the target SQL Server. If the source path does not exist on the target SQL Server, the restore of the database will fail.
    • If this is the case, you can manually backup/restore the database using with MOVE commands and it will be successful.
    • There are other dbatools commands for migration that can be used to avoid this scenario as well.
  • Microsoft Database Migration Assistant can be used to migrate databases using the backup/restore method.
  • Detach/Copy/Attach. You can simply detach the databases from the source SQL server instance, copy the database files to the target SQL Server and attach them to the target SQL Server instance. This is faster than backup/restore but does require time to copy the files over the network. There is also the risk of data corruption occurring during the copy process.
    • If you have a SAN, move the volume(s) with the database files to the target SQL Server.
    • If you have a SAN, replicate or snapshot the volume(s) with the database files to new volumes. Shut down the source SQL Server services so the databases are in a clean, shutdown state prior to final replication or snapshot.
  • Log Shipping. With log shipping you can have the databases restored on the target SQL Server ahead of migration with NO RECOVERY and continuously restore the Transaction Log backups to the target SQL Server to keep the databases synchronized. When it is time to migrate, the final Transaction Log restore is done with RECOVERY and the databases are brought online. This is also an older Disaster Recovery method for SQL Server and the databases can be brought online without a restore of the last set of Transaction Log backups.
  • Always On Availability Groups. If you are using the Enterprise version of SQL Server and upgrading from 2012 or later, you can utilize Always-On Availability Groups to migrate and upgrade. Configure a target AG Secondary replica that is a higher version of SQL Server than your source SQL Server and failover to the target AG Secondary replica to finalize your migration with upgrade.

During install and configuration of SQL Server you can choose Windows accounts to use for the various SQL Services. Some of the common services that need Windows accounts are:

  • SQL Server database engine service
  • SQL Server Agent service
  • SQL Full-text Filter Daemon Launcher service
  • SSIS service
  • SSAS service
  • SSRS service

For these services it is an industry best practice that you utilize domain accounts. When setting the services to utilize domain accounts this can be done during installation or using the SQL Server Configuration Manager utilities after installation. It is highly recommended that accounts are set using either method above as the minimum required permissions should be set automatically for the domain accounts. If you set service accounts manually through the services control panel, you will have to manually set permissions for the accounts. It is also an industry best practice that you allow SQL Server to set permissions for domain accounts and do not elevate the permissions for those accounts without an acceptable reason.

Prior to migration you need to determine which shared features will be migrated to the target SQL server. Not all SQL shared features have to run on the same server as the SQL Server database engine service. These include SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS). Plan out the migration of these shared features as they will be done separately from the database migrations. Starting with SQL Server version 2017, SQL Server Reporting Services is a stand-alone installation that is no longer part of the SQL Server installation. This means:

  • You can only have one SSRS installation per Windows Server now. You can no longer have an installation of SSRS per SQL Server instance.
  • If you are migrating multiple SQL instances to a new server and you have multiple installations of SSRS on your source server(s) you will have to plan out how to combine them into one installation or choose to migrate them to separate Windows Servers.
  • You can use the rs.exe utility to migrate reports from one SSRS install to another SSRS install. This can be done at various folder levels. In our migration we migrated one SSRS install over to the target server as-is and then migrated a second SSRS install into a sub-folder on the target server.

When planning out the target SQL Server configuration is the time to make changes to the SQL Server environment. Aside from the best practice configurations that have been reviewed and agreed upon, now is the time to clean up the database file structure if necessary. According to industry best practices you should separate your database files as follows:

  • System database files
  • TempDB database files
  • User Database files (.mdf, .ndf)
  • User Database log files (.ldf)

Each type of file above has different requirements on the storage sub-system. Depending on your environment you can have separate I/O queues for each volume or you can have physical drives that meet different read and write patterns. Plan this out appropriately when configuring the target SQL Server(s). Logically separating the files can make future infrastructure changes easier.

An option you can use during migration is the Query Store. You can enable Query Store in the databases once they are running on the target SQL Server (SQL 2016 and higher) and leave the database compatibility level to the same level it was on the source SQL Server. After a few weeks of running and collecting data in Query Store you can update the compatibility level of the databases to the new level of the target SQL Server. Query Store provides reports on “Regressed Queries” and “Query Wait Statistics” that can be used to determine which queries are performing worse on the newer version. Query Plans can be forced to quickly revert queries to their previous plan prior to the compatibility level change and can then be evaluated for tuning.

Test

During the test phase we are testing our migration plan. There are always unforeseen issues during a migration regardless of how well you plan. Here are things testing can help with:

  • Migration method timing can be gathered during testing. You might have looked at your backup times and doubled it for migration but restore could take longer than anticipated. Getting an accurate length of downtime for the migration will help meet expectations.
  • Tool or script usage can be tested. Did you have any unexpected failures during the migration?
    • For me I had issues with dbatools and full text index catalogs that I had to work through.
  • SQL feature migration can be tested. Was it straightforward migrating SSIS or SSRS?
    • For me I had issues restoring the SSIS database master key that I had to work through.
    • I also had issues with getting SSRS Subscriptions working again on the target server due to a stored procedure referencing the SQL server by name.

Aside from just testing the migration plan and working through the timing and unforeseen issues you should plan for end user acceptance testing. Here are things end user acceptance testing can help with:

  • Once the SQL server has been migrated, do the applications still work correctly?
  • Did you have trouble changing DNS or connection strings?
  • Did you forget about a server that was part of the application that needed to be changed?
  • Were any settings on the SQL Server that affect the application missed?
    • For me I missed setting trace flags in SQL on the target SQL server.
    • I also missed static ports that were set on the source SQL server for the named instances.
    • These are things I know should be done but when juggling moving parts, they can get dropped and the testing phase is the appropriate time to catch them.
  • Is all functionality of the application still working when using the target SQL server?
  • Is your testing plan comprehensive of all the components affected by the migration?

If you have a test environment for your application, then test phase can also be used to migrate the test environment permanently to the target SQL Server(s). If you do not have a test environment, then this is a good time to consider implementing one. If you are unable to implement a test environment, then use the production target SQL Server for your test migration and consider it a dress-rehearsal for the final production migration.

Migrate

Once you have reviewed, prepared, and tested your migration then it is time to migrate. You should have a solid idea of how long you will need for downtime and how long it will take to test the new environment. Plan for rollback to the source environment in the event of unforeseen issues:

  • If issues come up during migration or testing, then determine the window of time allowed to resolve those issues before rolling back to the source environment. If you cannot be down for more than X hours and the testing has not been completed by that time you need a plan to get the source environment back online to maintain business continuity.
  • When planning for rollback, define steps that are go/no go for rollback. If you reach a point where it is more beneficial to resolve issues than roll back or where live data is being inserted into the target databases, then you can define a point where rollback is no longer an option.
    • You can mandate that all parties involved in the migration give consent to proceed past a point where rollback is no longer an option.

Define and agree on success criteria for the migration. Success criteria will help define when a migration is considered successful. If this is defined ahead of the migration and is acceptable to all parties involved, when the criteria are all met you can send out communication calling the migration a success. This will help with future communication claiming the migration was a failure or the project stretching out for weeks and having to discuss whether an issue is a “migration” issue or a normal operating issue. Examples of success criteria can be:

  • End user acceptance testing is completed with all tests passed.
  • Applications Y and Z come online.
  • If application X is not online, it will not affect the success of the migration.
  • SSIS packages run successfully.
  • Reports and schedules run successfully.
  • 48 hours of production run time will determine a successful migration regardless of new issues.
  • Issues opened during the first 24 hours of production run time need to be resolved to determine a successful migration.

A SQL Server migration is fundamentally the same as any system migration. You may have some considerations which are specific to SQL server but overall, the phases of the migration will be the same. If you properly follow the four phases of Review, Prepare, Test, and Migrate then you will not just have a successful migration of your SQL Server, you will have a successful migration of your SQL Server in the eyes of your client or manager.

TIL: Dismount-DbaDatabase and Mount-DbaDatabase

by Leave a Comment

We have a double feature for today’s dbatools blog post, as these two commands go hand-in-hand.

Todays commands: Dismount-DbaDatabase and Mount-DbaDatabase

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test'

Detach-DbaDatabase and Attach-DbaDatabase can be used as aliases. For the purpose of this blog i’m using the official names, because reasons™

Note that the .mdf and .ldf files are still present, meaning detaching a database can keep you from deleting a database you were told wasn’t needed anymore, only to instantly have someone ask why they can’t get to the database they need. If the files are still there, you can attach it and it’s good as new.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -Force

If the database is part of a mirror or something, -Force can be used to detach it anyways.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -UpdateStatistics

This will update the statistics of the database before it’s detached.

$testAttach = New-Object System.Collections.Specialized.StringCollection
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test.mdf")
$testAttach.Add("C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\test_log.ldf")

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -FileStructure $testAttach

The -FileStructure parameter can be used to point out where the files to attach are. If not specified, it’ll use backup history to figure out what you mean, if possible.

Mount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test' `
    -DatabaseOwner 'sa'

The -DatabaseOwner parameter lets you set the, well, database owner, when you attach the database. Defaults to sa.

Dismount-DbaDatabase `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database 'test', 'square[]brackets'

The -Database parameter can take multiple values.

That’s all i got for this one, not too complicated unless you’re doing something with credentials and stuff. Follow me (b|t) on twitter!

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup
Get-DbaAgentJobHistory
Measure-DbaBackupThroughput
Get-DbaErrorLog

Future Dbatools posts:

None yet!

TIL: Get-DbaErrorLog

by Leave a Comment

Ed: SQLDork is a Junior DBA on the team here, learning the DBATools in addition to normal DBA stuff™

Today’s dbatools command is Get-DbaErrorLog, which is very similar to a script we frequently run on client servers to check for errors that need fixing.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Basic command usage, using Select-Object to filter out the columns i don’t really care about, then piping the results to Out-Gridview.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Adding the -LogNumber Parameter to check the archived errorlogs, defaults to current log only.
NOTE: If you want the results ordered by date, the values passed into -LogNumber must be in descending order.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Source spid5s `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

The -Source parameter lets us filter out some stuff™. I can’t think of a better way to describe that. The documentation uses -Source Logon as an example, but my sample data didn’t have that so i used a spid.

(Ed: The Source can be a spid, “logon”, “Server”, or anything you see in the errorlog Source column in the SSMS Log Viewer)

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    -Text "instance" `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Out-Gridview

Using -Text as a rudimentary where clause, but this one only supports 1 string. We can do better.

Get-DbaErrorLog `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -LogNumber 5, 4, 3, 2, 1, 0 `
    | Select-Object `
        SqlInstance, Source, LogDate, Text `
    | Where { `
                ( `
                $_.Text -Like "*CheckDB*" `
            -or $_.Text -Like "*Error*" `
            -or $_.Text -Like "*UTC*" `
                ) `
           -and ( `
                $_.Text -NotLike "*found 0 errors*" `
           -and $_.Text -NotLike "*finished without errors*" `
           -and $_.Text -NotLike "*informational message*" `
                ) `
            } `
| Out-GridView

This formatting feels very strange to me, but it does a very similar thing to that errorlog query we use that i mentioned earlier.

(Ed: The Where-Object or Where is PowerShell, not DBATools, click here)

Shoutout to Kevin (b|t) for helping me figure out the syntax on this, and also for pointing out that he changed the -SqlInstance parameter to his own machine name when he was testing this. Saved me a bunch of time troubleshooting connection errors.

Speaking of connection errors, follow me (b|t) on twitter, even though i don’t really tweet that much. Unless i retweet pictures of dogs, that’s always a possibility.

Pluralsight course for new SQL Server DBAs

TIL: Measure-DbaBackupThroughput

by Leave a Comment

Today’s dbatools command: Measure-DbaBackupThroughput

This one’s a smaller command, but that doesn’t mean it’s without use. If you need information on backup speeds (throughput) or early warning of possible hardware issues, such as in a RAID 5 setup, this can help you.

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Basic command usage, then we pipe it through a select statement and into a gridview. Selecting only those specific columns to cut down on redundant information, and also to get it all in one picture easier. Only measures full backups by default.

 

Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Last `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview

Maybe we don’t want all the backup history for every database. Maybe we just want the most recent history for one database. The -Database parameter lets us specify which databases to check, and the -Last parameter returns only the most recent backup of each type.



Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Since (Get-Date).AddDays(-28) `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




The -Since parameter combined with Get-Date lets us get all the information since a certain point in time relative to now. Presumably this can also be done with a hard-coded date, but that seems like an edge-case scenario to me.




 


Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -Database test `
    -Type Differential `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




Use  the -Type parameter to get information on different kinds of backups, accepts “Full”, “Log” or “Differential”.

Note: -Type parameter does not accept multiple values with commas, probably because the results would then have to have up to 3 times as many rows, one for each backup type.




 


Measure-DbaBackupThroughput `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -ExcludeDatabase msdb, master, model, tempdb `
    | Select-Object `
        SqlInstance, Database, AvgThroughput, `
        AvgSize, AvgDuration, MinThroughput, `
        MaxThroughput, MinBackupDate, `
        MaxBackupDate, BackupCount `
    | Out-Gridview




If we don’t want specific databases, we can use -ExcludeDatabase for that. Useful for only checking the user databases. You don’t have to specifically exclude tempdb, but i did it anyways, just for fun.




Speaking of just for fun, i also took backups of the test database onto 3 different drives, C and two external drives. The results are somewhat less useful than i’d like, though:




Speaking of external drives, go follow me (b|t) on twitter, where i mostly talk about two things:


    

  1. Things related to SQL Server.
    2. 

  2. Things that may or may not be related to SQL Server.
    3. 



Previous Dbatools posts:


Backup-DbaDatabase

Restore-DbaDatabase

Get-DbaLastBackup

Get-DbaAgentJobHistory


Future Dbatools posts:


None yet!


Data Bits – Episode 8 – Chrissy LeMaire


 by  1 Comment 
Microsoft Data Platform and PowerShell MVP, Chrissy Lemaire (b|t) sat down with us in the virtual studio Saturday, and we had a blast getting to know her!


Quotables:


“This is horrible, this not what I want to do” (Oracle reference)

“This is an at risk system”

“Hold on, let me fix my tongue”




Chrissy’s shout-outs:


Real Cajun Recipes


DBA Tools In a Month of Lunches


SQL Community Slack


 


My Pluralsight course for new SQL Server DBAs


TIL: Get-DbaAgentJobHistory


 by  Leave a Comment 
Another blog post, another dbatools command.


Today’s command: Get-DbaAgentJobHistory


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object * `
    | Out-Gridview 


Basic command, gets all the job history info and pipes it to gridview, because there’s a LOT of info here. Probably a good idea to filter it down to the interesting stuff.


The Duration column behaves a bit weird:

For durations under 1 minute, it’ll say x number of seconds or milliseconds. (Yellow arrow in picture below)

Anything over a minute gets formatted as minutes:seconds:milliseconds. (Red arrow)

Unless it’s over an hour, in which case it’s hours:minutes:seconds, which looks exactly the same. (Blue arrow)




Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Out-Gridview


Not using Select-Object * cuts down on columns a lot, but we can do better.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-10 `
    | Out-Gridview




Using -StartDate to only return all the job history from the last 3 days, default value is Jan 1, 1900.


I’m like, 90% sure this job doesn’t have any results from the 20th century though.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 1900-01-01 `
    -EndDate 1999-12-31 `
    | Out-Gridview


This doesn’t even pull up a gridview on account of there’s no data, which means my earlier theory was right! On a more useful note, -EndDate lets us exclude results from a given date onwards. Default value is $(Get-Date).


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Out-Gridview




If you don’t care about which steps were successful and which failed, -ExcludeJobSteps cuts it down to reporting the job run attempt as a whole.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object * `
    | where Status `
        -Match Failed `
    | Out-GridView




Filter to just the failure results. Useful if you want to check which jobs failed and when.


Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object `
        SqlInstance, StartDate, EndDate, `
        Duration, Status, Job, InstanceID, `
        Message, StepName, SqlSeverity, JobName, `
        RunStatus, RunDate, RunDuration, RetriesAttempted `
    | where Status `
        -Match Failed `
    | Export-Csv `
        -Path C:\output.csv `
        -NoTypeInformation




Two things here:

One: Filter out the columns we don’t care about in Select-Object, we don’t need to be doing Select * From * Where * = * here.

Two: Output the data to a csv file so we can play around with it in excel.


Three: There is no three, this is the end of the post. This is also the part where i (b|t) link to my twitter a bunch of times to generate traffic. Also because it’s funny.


Previous Dbatools posts:


Backup-DbaDatabase

Restore-DbaDatabase

Get-DbaLastBackup


Future Dbatools posts:


None yet, unless there are and i forgot to update this part. If that’s the case, go yell at me on twitter!