SQL

Two weeks ago I have sent out the invitation for February 2021 #tsql2sday and last week almost 30 people responded with their contributions. I would like to thank you all of the contributors (see the posts’ list below) for their time. Contributors Here is the list of all contributions to February 2021 #tsql2sday in alphabetical…

Read More T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome – write-up

Girl feeding pigeons Girl feeding pigeons

A few weeks ago I had a pleasure to talk about MSX/TSX as part of the SQL Friday series ran by Magnus Ahlkvist (blog|twitter) (if you have missed it see the YouTube recording). What is a multi-server administration? The SQL Server Management Studio offers two components that enable users to manage multiple servers from a…

Read More Multi-server administration with CMS/MSX/TSX

Roses are red,Violets are blue,Community loves dbatoolsdbatools love it too Author unknown what dbatools are? dbatools.io are a set of PowerShell commands to manage SQL Server, contained in a module. Plus it is open-source, created by people who work with the SQL Server and PowerShell for years. Some say it is a command-line version of…

Read More Install community tools with dbatools

dbatools = command-line SSMS dbatools = command-line SSMS

dbatools commands used in this post: New-DbaServiceMasterKeyBackup-DbaServiceMasterKeyNew-DbaDbMasterKeyGet-DbaDbMasterKeyBackup-DbaDbMasterKeyRemove-DbaDbMasterKeyNew-DbaDbAsymmetricKeyGet-DbaDbAsymmetricKeyRemove-DbaDbAsymmetricKeyNew-DbaDbCertificateGet-DbaDbCertificateBackup-DbaDbCertificateRemove-DbaDbCertificateRestore-DbaDbCertificate Service Master Key There are multiple security-related objects that are not easily accessible via SQL Server Management Studio. The first one would be Service Master Key, if exists, can be seen under the master database. Luckily, dbatools can help us to take a backup. SSMS dbatools: New-DbaServiceMasterKey dbatools:…

Read More dbatools.io = command-line SQL Server Management Studio: Keys, certificates

dbatools = command-line SSMS dbatools = command-line SSMS

dbatools commands used in this post: Get-DbaService Stop-DbaService Start-DbaService Restart-DbaService Start-DbaAgentJob Stop-DbaAgentJob New-DbaEndpoint Start-DbaEndpoint Stop-DbaEndpoint Start-DbaTrace Get-DbaTrace Stop-DbaTrace Start-DbaXESession Stop-DbaXESession SQL Services OK, let’s start. While doing some maintenance tasks there is a need to start/stop/restart SQL Services. Using SQL Server Configuration Manager is one way or even straight from SSMS, however, working with multiple…

Read More dbatools.io = command-line SQL Server Management Studio: Start/Stop

dbatools = command-line SSMS dbatools = command-line SSMS

dbatools commands used in this post: Export-DbaScript Export-DbaUser Export-DbaLogin Export-DbaDacPackage Publish-DbaDacPackage Export-DbaInstance Copy-DbaDbTableData Export-DbaDbTableData Import-DbaCsv Script it out The SSMS offers to script out lots of the SQL Server objects, however it can be limited in some areas. Using Get-Dba* commands and piping them into Export-DbaScript may add few more options. For example SQL Agent…

Read More dbatools.io = command-line SQL Server Management Studio: Export-Import

dbatools = command-line SSMS dbatools = command-line SSMS

dbatools commands used in this post: Enable-DbaAgHadr Disable-DbaAgHadr New-DbaAvailabilityGroup Add-DbaAgReplica Add-DbaAgDatabase Add-DbaAgListener Remove-DbaAgListener Get-DbaAgDatabase Set-DbaAgReplica Invoke-DbaAgFailover Suspend-DbaAgDbDataMovement Resume-DbaAgDbDataMovement Sync-DbaAvailabilityGroup AlwaysOn Availability Groups The feature that has been introduced in SQL 2012 is quite an important one. Also, it requires a number of clicks there and there in SSMS. Luckily for us dbatools have set of…

Read More dbatools.io = command-line SQL Server Management Studio: AlwaysOn Availability Groups

dbatools = command-line SSMS dbatools = command-line SSMS

dbatools commands used in this post: Get-DbaSpConfigure Export-DbaSpConfigure Set-DbaSpConfigure Import-DbaSpConfigure Set-DbaMaxDop Set-DbaMaxMemory SQL Server configuration / sp_configure SQL Server allows its users to configure a lot of things. From ‘Ad Hoc Distributed Queries’ through ‘max degree of parallelism’ to ‘xp_cmdshell’. Some of those can be done via SQL instance properties or the famous sp_configure. With…

Read More dbatools.io = command-line SQL Server Management Studio: SQL Configuration

dbatools = command-line SSMS dbatools = command-line SSMS

dbatools commands used in this post: Add-DbaRegServer Add-DbaRegServerGroup Get-DbaRegServer Get-DbaRegServerGroup Move-DbaRegServer Move-DbaRegServerGroup Remove-DbaRegServer Remove-DbaRegServerGroup Export-DbaRegServer Import-DbaRegServer Create registered servers or Central Management Servers The built-in feature of the SSMS allows us to configure a group of SQL instances and run queries against multiple instances at once. With the registered servers you can also build a…

Read More dbatools.io = command-line SQL Server Management Studio: Registered Servers/CMS