I’ve been working on a project this year to upgrade SQL Server versions for around 80 application databases, with most of the upgrades requiring both SQL Server and Windows Server upgrades to get to the future state we were looking for. The general process for each of these was to build a new virtual machine with the upgraded operating system, install the desired SQL Server version and then migrate the application databases during an arranged downtime window.
I’m going to focus on the final step of this process for this post – migrating the databases during the downtime windows. Luckily for me, dbatools made this both easy and repeatable.
Step 1 – Check for connections
First step when we get into the downtime window is to check whether there are any active connections to the database you want to migrate. We don’t want any data being changed while we migrate, there’s a command for that:
Get-DbaProcess -SqlInstance SourceServer -Database MigratingDatabase |
Select Host, login, Program
If there are connections and it’s safe to remove them (if they are still coming from the application it might be worth talking to the app owners first) you can pipe them to another handy dbatools command:
Now that there are no connections we can move the database. Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the application taken down so I felt confident no connections would be coming in.
With one line of code we can select the source and destination servers, the database name, specify that we want to use the backup and restore method, and then provide the path to a file share that both instance service accounts have access to:
There are a lot more options available on this command, including setting the number of backup files to use, which can speed things up if you have a large database. I recommend checking out the command based help for all the available options.
Step 3 – Migrate the user logins
Once the database is on the new server we can use the following to copy the associated logins across. The nice thing about using this command is it ensures the user SIDs match up on the destination and you don’t end up with any orphan SQL Logins.
Now that the database and associated logins have been migrated we can set the source database offline. I did this so if there were any issues getting the application up we could quickly revert back while ensuring nothing was still accessing the old copy.
In the end I was able to use 5 lines of PowerShell to get these application databases migrated to their new homes. After some testing I dropped the old offline copy of the database and eventually decommissioned the old servers.
I hope this gives you some ideas of how dbatools can help make your database migrations easier and more efficient.
In software development, an “issue” can be anything from you hit an error running an application or you get an unexpected result from an operation. You can also say an issue is a spelling error in our documentation. Our goal is to fix any “issue” whether it is a bug, an unexpected error (especially these) or considering a feature you think is missing from the module.
However, before we can fix it…you have to let us know.
Where to begin
This is purely based on what type of an issue you need to submit, but all of them will start by going to our repository and filling out an issue on GitHub. You can do this in 4 easy steps:
Open your favorite browser, go to our GitHub issues shortlink [dbatools.io/issues](https://dbatools.io/issues)
Click on the “Issues’ tab of the repository
Take a minute or two and search the current issues (helps us to keep duplicate count low; if you find a match give it a thumbs up.)
If you do not find anything in the current issues, click on “new issue” button and fill out a new one.
The remainder of this post is going to do a review of filling out a new issue. The more information you can provide helps our unpaid developers in getting a fix!
Before you start your new issue
Just like you submit a request to your own internal IT support, it helps to give as much information as you can up front when you submit an issue. Understand that this project is 100% community supported and we do this in our free time. If we get all the information up front, it is a huge time saver for us to fix the issue and return a resolution in a timely manner.
A feature that you want is to submit is basically: tell the story of what problem you are looking to solve. When you open a new issue we have a template setup. You can delete everything up to where you see the text ## Feature Request, below that, is where we need you to answer the question. Remember that even with a feature request the more details you can provide will help your cause in potentially getting the feature added.
What problem or scenario would you like to solve with dbatools?
This is the primary question we need you to answer for the feature you desire. We provide some header sections but you can add/remove those if it helps to organize your thoughts. We are not asking for an essay mind you but think of it as if you were going down an elevator with one of us, you have about 2-3 minutes to verbally explain what you wanted. Obviously, you can provide more information and detail, but consider putting that conversation down as a synopsis so we know where you are coming from and such.
Bugs and Errors
Bugs and errors are both a little different but in the area of submitting an issue, we will treat them the same for this post. While some errors may be expected we cannot really help you determine that unless you give us all the details. There are just some scenarios that you may find bugs and errors that we have not, or cannot account for due to limitations in our test lab(s). Every environment will be different and telling us about it all will aid in getting it fixed.
A few general steps
The top section of our issue template has 3 items that we ask you to verify, sometimes this can resolve your problem right off the bat…sometimes not.
Verified running the latest release of dbatools?
We push a new release to our master branch and the PowerShell Gallery on average every 2-3 days (based on when Chrissy’s on vacation). A release, on average, will always contain bug and error fixes, so just doing an Update-Module dbatools and restarting your PowerShell session may resolve your issue.
Verified errors are not related to permissions?
You would be surprised how often this can happen, so we have to ask. With the number of security issues, companies are having that affect their profits the whole least-privilege administration is more common nowadays. So if you know you are not sysadmin in SQL Server or local administrator if the command happens to touch the OS in some manner, it is a good thing to double-check before continuing.
Can duplicate in new/clean PowerShell session?
Not all modules play nice with each other. We have found in particular that the sqlserver module can cause issues with our module loading properly. So if you happen to import modules prior to or after importing dbatools, it helps to identify that none of those modules are adversely affecting dbatools execution.
Version and Edition information
Just the basics. This information is pivotal when we get down to debugging bugs and errors, helps to ensure we have a similar setup in our lab(s) to more closely get root cause. As well, we may identify flavors of each that may not play well together (e.g. credential prompts when using CIM/WMI). In particular, if you happen to be working with the migration commands (Copy-Dba* commands) or moving an object in some other process, it can help to include the version information for your source and destination/target.
Steps to Reproduce
Reproducible issues are the best kind, they also tend to be the ones that are the easiest to fix (tend to be mind you).
In order to reproduce the problem, we will need you to provide the command you are executing. If you can provide the values you are passing into the command it will help, but if for security reasons you cannot provide it all try to give some example values. You may also need to obfuscate the output from your PowerShell session if you want to provide screenshots.
Collecting logs and errors
We have added two commands to the module that can help us more quickly determine the issue if you are able to provide them.
This command simply outputs the messaging that has been generated since you imported the module into your session. This is one reason why duplicating your issue in a new/fresh console can help. Reissue the command and let the error or bug occur again, then run this command and you will see all the output generated from the command. This output includes an internal command that may have been executed as well.
This command will generate a compressed file of collected information from the client machine, one you are using dbatools on. Think of this like you submit a support ticket to Microsoft support, they will have your run a few utilities on the offending machine to let it collect various logs and in-memory information. We do not capture as much as Microsoft, but information pertinent to the PowerShell session you have in use. I encourage you to run this and review the contents of that compressed file before attaching to an issue; ensure you are not violating any security policies.
This is a quick and dirty method and depends on the number of errors you may have encountered. PowerShell has a buffer or cache that errors from your PowerShell session are stored in. Checking this cache of errors right after you encounter an error will let you get the low down on what happened.
In order to see this you simply need to execute $error, that variable will contain the recent errors. To pull out the last error that happened you can run the following line:
$error | Select-Object *
Just to give some closure…The process that follows once you submit an issue will on average be:
One of the team will post the issue either verifying the information, providing some things to try, or confirming the issue.
Once the issue is confirmed that a code change will be required, as time permits, a developer will take on getting the code fixed.
You will see the issue closed either by the developer or once the change is committed to the master branch.
I chose the GNU GPL for nostalgic reasons and I also wanted companies who used dbatools to contribute back to the project. What I didn’t realize was that licensing dbatools as GPL would hurt integration which ultimately impacts adoption. Permissive like the MIT don’t subject users to such restrictions.
Considering this and our lessons-learned from dbatools, we released dbachecks under the MIT license. The reception at SQL Bits was amazing. A number of comments were made expressing thanks and optimism about our selection of the MIT license.
And, after speaking with a number of community members about the restrictive GPL license, it became clear that dbatools must also switch to MIT.
As of version 0.9.300, dbatools is now MIT licensed! If you would like to continue using the GPL version, please fork v0.9.211 and take it from there.
If your project appreciates this switch, please comment in the section below or to let us know!
As of version v1.0, dbachecks contains 15 commands. This article provides an overview of these commands. Rob Sewell offers a fantastic deep-dive article that explains the commands below in greater depth.
Usage can be approached in two ways:
Run directly from the command line
As simple as Invoke-DbcCheck -SqlInstance sqlprod01 -Checks SuspectPage, LastBackup
Command line execution is good in a pinch, but ongoing checks are the ultimate goal. In order to do this, you can do the following:
Set your desired configuration
Configs can be set for specific environments like Production, Test or Development or for an application, like SharePoint or a custom-built app
Export your configuration
Export your environment or application configuration so that it can be easily imported by your scheduled task
Schedule checks using Task Scheduler or SQL Server Agent
I personally prefer SQL Server Agent
Get notified via email or load up in Power BI
Since the output of Invoke-DbcCheck is a PowerShell object, you can extend responses and notifications any way you wish. We provide two built-in ways.
Whichever approach you use, the commands below will help you easily accomplish validation of your environment.
All checks are performed using Invoke-DbcCheck. This command is basically a wrapper for Invoke-Pester. This means that supported Invoke-Pester parameters work against Invoke-DbcCheck, plus a few bonus parameters.
In this module, a “Check” is synonymous with a “Tag” in Pester. So you can Invoke-DbcCheck and specify a Check that you want to run. You can see a list of the available Checks with Get-DbcCheck.
We also made it convenient to run groups of checks. So LastBackup will run LastFullBackup, LastDiffBackup and LastLogBackup.
This command lists all checks, check groups along with their required server type, either SqlInstance or ComputerName.
dbachecks comes with its own configuration system! This allows you to set values that are required for your environments. We set reasonable defaults, but your requirements may vary, especially between production and test/dev.
What are reasonable defaults? Well for instance, out of the box, dbachecks tests to ensure that log backups have been taken within the last 15 minutes. You may want this to be 60 minutes instead. To set this new value, run the following:
Imports dbachecks configs from a json file. The basic idea is that you set your configs, export them, then import them and run on a regular basis.
Converts Pester results and emails results formatted using ReportUnit. Basically wraps the Send-MailMessage cmdlet which sends an e-mail message from within Windows PowerShell.
Converts Pester results and exports file in the required format for launching the Power BI command. You will need to refresh the Power BI dashboard every time to see the new results. Basically, behind the hood it is running this:
Start-DbcPowerBi also supports alternative paths, in the event, you specify a new path using Start-DbcPowerBi.
Clears the data source directory created by Update-DbcPowerBiDataSource (“C:\windows\temp\dbachecks*.json” by default). This command makes it easier to clean up data used by PowerBI via Start-DbcPowerBi.
Retrieves raw configuration values by name. Can be used to search the existing configuration list.
Retrieves a list of all available tags. Simplistic, similar to Get-Verb.
Opens the default location of the json config file for easy edits. Follow with Import-DbcConfig to import changes.
Saves all required modules, including dbachecks, dbatools, Pester and PSFramework to a directory. Ideal for offline installs.
Updates all required modules, including dbachecks.
This command reference will continue to be updated as we add more commands. The shortlink is dbachecks.io/commands.
Read Full Article
Scroll to Top
Separate tags by commas
To access this feature, please upgrade your account.