In part 1 of this series, I discussed pre-installation steps. There are many guides available online for the actual installation process so I’m not going to give a step by step installation guide here. Rather, I’m going to focus on what to think about after the install is complete.
Recent versions of SQL Server will now go out and download additional content for you before the install actually happens so you’re not left on the base, RTM release of the product. However, one thing to consider post installation is still, are you on the latest patch? How do you know? Where can you go to compare the version number of the SQL Server you installed to what is currently available? Well, you can look back at this post to answer that question.
Once you’ve determined whether or not additional patching is needed and completed that, then what? Well, there is a lot of post install configuration still to do. For example, do you need to migrate Linked Servers, SQL Agent jobs, SQL Operators? What about setting up database mail or moving Logins? Each one of these can take up a lot of time. What if there was a quick way to handle those things? Enter DBATools.
DBATools is a fantastic collection of hundreds of PowerShell commands that have been developed and tested by users worldwide. With a simple command, linked servers can be migrated, with their password information intact. The same with SQL Server logins and their passwords and permissions. Database mail? No problem! In fact, if you call Start-DBAMigration and pass a source and target SQL instance, then you can migrate the entire instance in a single command. DBATools is fantastic for ongoing maintenance tasks and a bunch of other things too so be sure to check out all that it can do for you as a DBA.
The next item on my install guide is trace flags. Trace flags control the behavior of SQL Server in a variety of ways. As one example, trace flag 3226 controls whether successful backups are logged in the SQL Sever error log. Setting this flag prevents those “backup successful’ messages from cluttering up your error log.
The important thing to remember if you’re doing a migration is that you most likely want the new SQL Server to behave in the same way as the old one. To find out what trace flags are globally enabled on a SQL Server, run the following code.
If you want a list of trace flags and what they do, those lists do exist but they are sometimes hard to find. Here is one such list that tells you whether you should be concerned about a particular trace flag being enabled or not.
If you’re starting from scratch with the database and application you’re installing, be sure to find out if the vendor has any recommendations and why they recommend those particular trace flags. Also, do your own research about trace flag functionality and see why you personally may or may not want to use a particular flag.