Webinar Recap
The dbatools.io team and I held 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 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.
The original webinar was extremely popular, and we recently aired it again with a live Q&A session. During both sessions, a record number of questions poured in during the 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.
Webinar Q&A
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?
A: So sorry about that; please post at dbatools.io/issues or join us on Slack in the #dbatools channel.
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.
Q: Sometimes we have to copy a database to another server which has lower SQL Server version. How would dbatools help (I believe it uses backup/restore which won’t work in this case)?
A: Happy you’re enjoying dbatools! SQL Server does not allow backups to be restored to a lower version, but what you can do is explore BACPACs.
Q: During restore I want to retain target database users and permissions, remove the source users, and delete orphan users. Can it be done with this script?
A: You can indeed Remove-DbaDbOrphanUser. Retaining database users and permissions is done via the backup/restore. To remove the source user, try the command Remove-DbaDbUser.
Q: Does the instance to instance migration also handle moving any encryption keys?
A: Not yet, but it’s on the agenda and will be finished by the time “Learn dbatools in a Month of Lunches” is finished because it’s part of the book.
Q: Would it also copy replication agents’ profiles and Change Data Capture (CDC) enabled databases?
A: Replication has very limited support at this time. CDC should be supported. If you have issues, please file a report at dbatools.io/issues.
Q: Is restore PowerShell rolling full, diffs, and logs?
A: Restore-DbaDatabase supports restoring full, diff, and log backups. If you’re doing it via two (or more) separate commands, -Continue switch can be used to continue the restoration process.
Q: To use the dbatools migration function, do the source and destination SQL Server versions need to match or could this be used, for example, to migrate 2016 databases to a new 2019 server?
A: We try to be as flexible as possible and they absolutely do not have to match. You can go from clusters to stand-alone, 2012 to 2019, and so on.
Q: Do dbachecks work on Azure SQL Managed Instance?
A: Some commands do, though support for Azure is limited at this time.
Q: Is there anything that handles SQL Server Integration Services (SSIS) database migration? (trying to move from 2012 to 2017)
A: This is a challenging one. The closest we’ve come is: Copy-DbaSsisCatalog.
Q: Is database cloning the same as SQL Server database snapshots?
A: The database cloning is not the same as the database snapshots. The cloning works by creating an image (VHD) of the database. The clone is a differential disk which only contains the delta.
Q: Can you comment a section out inside JSON?
A: JSON does not support comments
Q: 1) Which version of PowerShell should I use for SQL Server 2014 and 2016? And 2) What level of access is required to run all these PowerShell scripts?
A: dbatools requires PowerShell 3.0 and above. We recommend PowerShell 5.1 or higher for speed and security reasons.
The answer to your second question is usually sysadmin.
Q: Would I still have to copy the production database to a lower environment to apply the masking?
A: Yes, the data must exist in order to mask it.
Q: How do I find out the current dbatools version and update across my network without spending much time? My dbatools have different versions on different systems, hence commands never work consistently across all, and it’s a headache to maintain.
A: Check out dbatools.io/gallery or Find-Module dbatools to see the latest version.
Q: Are the shown commands certified to work with SQL Server on Linux?
A: None of our commands are certified, but we try our best to support Linux. 75% of our commands work on Linux.
Q: Do you have anything developed to update a test database from production with data, so as not to disturb developer code or additional columns added to tables by a developer? Everyone could use this.
A: Sounds interesting. Please file an issue with feature requests at https://github.com/sqlcollaborative/dbaclone.
Q: In scheduled dbatools.io scripts, are they done in SQL Agent or Windows Task Scheduler?
A: We recommend SQL Agent. Please visit dbatools.io/agent for more information.
Q: Restores and adding information to a table that can be looked at later is just too cool! What about getting the actual errors in an email from scheduled jobs?
A: This seems like something fun to explore. Please file a detailed issue at dbatools.io/issues.
Q: How would we mask data when we restore a database onto a dev server? At what point would the masking take place?
A: I guess it depends on your situation, but I would not want to restore production data on dev. I would do the data masking on another server and make a backup of that and restore that backup onto the dev server.
Q: How do you reconcile these DBA-friendly Continuous Integration/Continuous Delivery (CI/CD) PowerShell packages with the .NET EF migration offerings beloved of developers?
A: EF deployment routine is a black box, which you choose whether to trust or not. It can be made more transparent by only generating SQL files, which (after proper review process) are then integrated into your own deployment framework.
Q: We have a NetBackup solution in place for backups. Is there any option configuration to use NetBackup for backup and restore?
A: Not at this time. We do not support any third-party backup/restore vendors, but you can reach out to NetBackup and ask if they can add support to dbatools.
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.
