PowerShell

Keeping their SQL Server instances under control is a crucial part of the job of a DBA. SQL Server offers a wide variety of DMVs to query in order to check the health of the instance and establish a performance baseline. My favourite DMV queries are the ones crafted and maintained by Glenn Berry: the SQL Server Diagnostic Queries. These queries already pack the right amount of information and can be used to take a snapshot of the instance’s health and performance. [Read More]

The March release of Azure Data Studio now supports Jupyter Notebooks with SQL kernels. This is a very interesting feature that opens new possibilities, especially for presentations and for troubleshooting scenarios. For presentations, it is fairly obvious what the use case is: you can prepare notebooks to show in your presentations, with code and results combined in a convenient way. It helps when you have to establish a workflow in your demos that the attendees can repeat at home when they download the demos for your presentation. [Read More]

As you probably know, SQL Server Express doesn’t ship with SQL Server Agent. This is a known limitation and many people offered alternative solutions to schedule jobs, including windows scheduler, free and commercial third-party applications. My favourite SQL Server Agent replacement to date is Denny Cherry’s Standalone SQL Agent, for two reasons: It uses msdb tables to read job information. This means that jobs, schedules and the like can be scripted using the same script you would use in the other editions. [Read More]

The problem One of the tasks that I often have to complete is manipulate some data in Excel, starting from the query results in SSMS. Excel is a very convenient tool for one-off reports, quick data manipulation, simple charts. Unfortunately, SSMS doesn’t ship with a tool to export grid results to Excel quickly. Excel offers some ways to import data from SQL queries, but none of those offers the rich query tools available in SSMS. [Read More]

Sometimes you have to copy all user databases from a source server to a destination server. Copying from development to test could be one reason, but I’m sure there are others. Since the question came up on the forums at SQLServerCentral, I decided to modify a script I published some months ago to accomplish this task. Here is the code: ## ============================================= ## Author: Gianluca Sartori - @spaghettidba ## Create date: 2013-10-07 ## Description: Copy user databases to a destination ## server ## ============================================= cls sl "c:\" $ErrorActionPreference = "Stop" # Input your parameters here $source = "SourceServer\Instance" $sourceServerUNC = "SourceServer" $destination = "DestServer\Instance" # Shared folder on the destination server # For instance "\\DestServer\D$" $sharedFolder = "\\DestServer\sharedfolder" # Path to the shared folder on the destination server # For instance "D:" $remoteSharedFolder = "PathOfSharedFolderOnDestServer" $ts = Get-Date -Format yyyyMMdd # # Read default backup path of the source from the registry # $SQL_BackupDirectory = @" EXEC master. [Read More]

If you are adopting Sql Server Data Tools as your election tool to maintain database projects under source control and achieve an ALM solution, at some stage you will probably want to import all your databases in SSDT. Yes, it can be done by hand, one at a time, using either the “import live database” or “schema compare” features, but what I have found to be more convenient is the “import dacpac” feature. [Read More]

Recently I had to implement log shipping as a HA strategy for a set of databases which were originally running under the simple recovery model. Actually, the databases were subscribers for a merge publication, which leaves database mirroring out of the possible HA options. Clustering was not an option either, due to lack of shared storage at the subscribers. After turning all databases to full recovery model and setting up log shipping, I started to wonder if there was a better way to implement it. [Read More]

You may be surprised to know that not all keyboard layouts include the backtick key, and if you happen to live in a country with such a layout and want to do some PowerShell coding, you’re in big trouble. For many years all major programming languages took this layout mismatch into consideration and avoided the use of US-only keys in the language definition. Now, with PowerShell, serious issues arise for those that want to wrap their code on multiple lines and reach for the backtick key, staring hopelessly at an Italian keyboard. [Read More]