Webinar Follow-Up: Top 5 SQL Server Challenges Automated with PowerShell
In May, I moderated a webinar on PowerShell for the DBA, which offers unique scalability and programmatic control for just about any workflow on Windows OS or supported versions of Linux. The webinar was a panel format and included experts such as dbatools.io leader Chrissy LeMaire, and major dbatools.io contributors Sander Stad, Rob Sewell, and Kirill Kravtsov. We covered PowerShell cmdlets that address some of the most common and frequently encountered SQL Server challenges, including:
- Automated SQL Server migrations
- Preventative maintenance checks on a daily, weekly, and monthly basis
- Provisioning security for SQL Server databases, including data masking
- Continuous Integration/Continuous Deployment (CI/CD) of code to SQL Server databases
- Logging errors and messages while automating with PowerShell
You can view the full webinar on-demand here.
Late-breaking news! Dbatools.io has launched v1! You’ll notice the answers below in some cases mention v1 as an upcoming release. However, the release just dropped.
The webinar was extremely popular, and a record number of questions poured in during our discussion. In hopes that some of the questions and answers are helpful to a larger audience, our collective panel has answered and shared them below.
Q: Can the automation tools in PowerShell assist with migration of an on-premises database to an AWS RDS instance?
A: That is a great question. Most of the dbatools.io community has worked on Azure, so we’d have to ask around to be sure about AWS. But AWS has plenty of tools to help move on-premises SQL Server to AWS. Check out the AWS Database Migration tools.
Q: Will the UseLastBackup flag work regardless of backup method? I use Ola Hallengren's for instance.
A: Yes! The dbatools.io team uses 100% Ola across the board and dbatools offers a ton of support for it.
Q: Do I need to install dbatool scripts prior to running those commands or will PowerShell automatically recognize them?
A: Great question! You will need to download the module first. Please reference this comprehensive dbatools module download guide.
Q: Does [PowerShell] take care of Operators and Alerts?
A: Yes, all of SQL Agent: Schedules, Jobs, Operators, Alerts, everything.
Q: What's the volume of instance?
A: If by “volume” you mean “how large of a database can the cmdlets handle,” then we can migrate TBs of data, as well as thousands of databases. The beauty of PowerShell is that if it works for one system, it can scale to practically unlimited heights.
Q: What was the URL for Managed Instances?
A: You can find Microsoft's official Managed Instance page here. The migration cmdlet also supports backup/restore using URLs, if that's what you were asking.
Q: Is this fast for multi-terabyte databases?
A: The cmdlet uses the same backend as SSMS (SMO/SQL Management Objects), so as fast as executing T-SQL from SSMS.
Q: I know nothing about PowerShell. Will I still be able to understand and use these commands fairly easily?
A: That’s our goal! The PowerShell team suggests using human-readable naming conventions. Check out this resource, which has some great examples of easy but useful commands. Ultimately, you do not need to know how to program to use dbatools. Finally, we recommend this primer from Rob Farley.
Q: How do you get the execution time to display in a PowerShell command?
A: Check out the dbatools.io community prompt; you'll love it!
Q: How much data is being backed up and moved?
A: In this demo? A few megs. But the migration cmdlet can support GB and TB and probably PB.
Q: Is there a way to automate a backup from on-prem to Azure where it will only move data to Azure that has changed and not on the Azure side yet?
A: Kirill shows how to do this with his module in the webinar recording.
Q: SQL migration on-prem to Azure SQL Database?
A: The migration cmdlet that the demo featured was an Azure SQL Managed Instance, not Azure SQL Database. However, dbatools 1.0 will have more support for Azure including migrations to Azure SQL Database. We do support backups/restores from URL.
Q: For dbatools training, do you have plans to offer an option to come on-site to train DBA teams at their place of business?
A: For on-site training, Rob Sewell may be available; he’s an incredible trainer.
Q: Is there a DTU calculator for determining vCores, Managed Instance vs. Single DB and laos Gen 4 vs. Gen 5?
A: Not currently in dbatools, but that is a great idea. We’re not sure if it's possible, but we'd like to explore. Please file a request at dbatools.io/issues. However, SentryOne has scripts to create rock-solid DTU calculations using the historic performance data of your existing on-prem databases.
Q: My dbatools module does not have the Invoke-DbcCheck.
A: That is the dbachecks module. It is a separate module that depends on dbatools.
Q: I have been using combination of dbatools and T-SQL to automate database transfers and I am happy with the results. Though speed is not as fast as I would prefer, but automating it makes it so much easier and eliminates manual steps.
A: Are you using Copy-Item? We tend to use robocopy when transferring data.
Q: I keep asking questions about how these PowerShell cmdlets work against AWS RDS instances. Can the dbachecks run against these RDS instances?
A: We were just told that RDS supports native SQL Server backup/restore, so it'll work with dbatools. If you have questions or issues, please join the SQL Server Community Slack. We have 9,000 SQL Server pros and there are 2,000 of us in the #dbatools channel that can help.
Q: With the [dbatools] 1.0 release, will commands that work with Azure SQL/elastic pool be marked in the documentation?
A: Great question! We probably won't have time to for the launch of dbatools 1.0, but it's something we’d like to explore. Please follow this newly created issue for updates.
Q: Do we need to have Power BI Pro or it works with Power BI desktop free version?
A: Free! We use both, depending.
Q: Will this work with SQL Server on Linux?
A: The majority of commands will work on a Linux box; however, anything that depends on Windows Remoting protocols (WMI/CIM) won't work at all. Make sure to download the most recent version of dbatools, as the underlying dlls to support Linux were added not so long ago.
Q: How do you recommend to automate any dbatool scripts to run daily?
A: dbatools.io/agent has our recommended approach. (Spoiler: It's Agent and cmdexec with credential.)
Q: Power BI? All explained in the dbachecks link?
A: Check out dbachecks.io/install. We even include an embedded Power BI report you can play with. Please be sure to check out all of the links at the bottom as well.
Q: Is there any way to install dbatools offline, since there is no internet connection in most of the servers?
A: Yes, indeed. That's primarily how we run it. You can use this dbatools.io resource.
Q: Do all of these cmdlets need to use cmd shell? I work in an environment where we have to have cmd shell disabled.
A: You will need PowerShell, not cmd shell. It is available on pretty much any Windows box. We recommend upgrading to PowerShell 5, though, as older versions have limited capabilities and some of the modules might be dependent on them.
Q: What about migrating encrypted databases?
A: If you're asking about dbatools, can do! Check out this migration resource.
Q: Do we have commands to migrate SQL Server to Oracle or vice versa by using dbatools?
A: No, there's no Oracle support or support for other database platforms like Teradata, PostgreSQL, or MySQL.
Q: Does the masking modify the data after it's been migrated to non-production, or is the masking a view on the production data? Or something else?
A: It's static masking, so the data is changed on the destination. So yes, migrate then mask.
Q: Are the masked fields totally random? Can we still use links between tables if we use data masking this way?
A: Yes, we use another open-source project called BOGUS to generate completely random data. We do support advanced masking, however, so if John Doe was masked to Jack Smith in one place, it can be masked to Jack Smith in all locations. Sander blogged about it recently.
Q: How do we get dbachecks modules? What are the requirements?
A: Requirements: Just some PowerShell. Check out dbachecks.io/install for more info.
Q: Is there an easy way to handle database variables on the $publishoptions rather than having them saved in a file etc.?
A: The commands support setting database variables even without the $publishoptions using Function parameters.
Q: What happens to migration approach if there's incompatible schema drift?
A: It fails. You need to really lock down your environments when using migration-based approach and only deliver changes through the defined framework.
Q: Can script-based deployment be integrated with Team Foundation Server as we use TFS as source control?
A: Yes! You can check out your source code as a first step and then run the deployment from that folder or create a package based on those files. However, your scripts should be migrations, not just object definitions like in a database project in VS.
Q: Do you have any best practices on how to implement $cred in PowerShell?
A: People really enjoy the BetterCredentials module by Jaykul. It uses the Windows Credential Store.
Q: What exactly is Pester? I received an error about Pester version conflict when trying to install the dbachecks module.
A: Pester is a PowerShell testing framework. Please join us in Slack in the #dbachecks channel for help to resolve your version issue.
Q: I have tried to install dbatools many times and got errors. Best place to post errors for answers?
Q: I teach SQL and other BI related courses and I'm constantly telling students about DOC xPress, but I haven't really played with it to see what it really does. Do you have a demo version that is not time sensitive that I can play with? My schedule is so busy, I never know when I will have "spare" time to play with tools. I work for myself, so I don't have the budget to purchase all of the tools that I like to tell students about.
A: We recommend that you definitely take a spin with the tool. SentryOne DOC xPress (and all of the SentryOne tools) have free trials associated with them. If your trial runs out of time, drop us an email and we’ll happily extend it.
Kevin (@kekline) serves as Principal Program Manager at SentryOne. He is a founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. Kevin is a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. As a noted leader in the SQL Server community, Kevin blogs about Microsoft Data Platform features and best practices, SQL Server trends, and professional development for data professionals.