It is a nice surprise to discover that Microsoft is once more fully-committed to SQL Server Management Studio (SSMS), Server Management Objects (SMO) and SQL PowerShell (SQLPS). As a result, I’m now happily sitting in front of a version of Management Studio that even works properly to Azure SQL Data Warehouse (ASDW). I like this new commitment, because SSMS is a great way to work with SQL Server. It is as familiar to a DBA or Database Dev as a saw to a carpenter.
These three components, SSMS, SMO and SQLPS, are inextricably linked. SSMS uses SMO to interact with the servers and SQLPS gives PowerShell the SQL Server drive and Cmdlets that interact with SQL Server via SMO. All in all, this buys you guaranteed compatibility for every version of SQL Server back to 2008, and it works pretty well for earlier versions, whether you are pointing-and-clicking, or scripting..
SSMS got very little love over the years and was merely patched to keep up with SQL Server’s new features. It seemed to most observers that it was in line for ‘sunsetting’, though insiders will only admit that it somehow got neglected, in the same sort of way that one loses the remote in the cushions of the sofa.
What really went wrong for SSMS? Its problems had come with SQL Azure. Because SQL Server in Azure is hosted, it can be updated rapidly. With more rapid developments with ASDW and SQL Azure, SSMS had to be able to keep pace or die. Various attempts were made at hosted replacements for SSMS over the years, before Microsoft finally decided that what was required was an easily-updated SSMS. The idea of regular monthly release was a radical one that required that a lot of behind-the-scenes work had to be done before we, the users, saw anything good happening.
Basically, SSMS had been bound up with the ‘classic’ on-site SQL Server and its two-year release cycle. Before it could be released, there was a rats-nest of dependencies to sort out. For example, SMO had evolved from being the powerhouse behind SSMS to being used by both the server and workstation. PowerShell had grown to become the obvious means of automation for server-based processes via SQL Agent as well as for the workstation-based administrator via SSMS or from the PowerShell console. An example of the problem was that SQL Server’s PowerShell components had been included in the SSMS (tools) installer as well as the SQL Server engine install. This required a redesign of the architecture, with the aim of avoiding sharing components with the SQL Server engine.
Unsurprisingly, there are a few changes now. PowerShell users will want to load the
SQLPS module by using Import-Module SqlServer (so-renamed to differentiate it from SQLPS which is used by SQL Server Agent.) In return for this, we are now getting a lot of useful Cmdlets for SQL Agent, the error logs, and for Azure. There are improvements to Invoke-SqlCmd to allow better-integration with .NET data objects. There is even a Trello board where you can make suggestions for improvements for the module formerly known as SQLPS. . https://trello.com/b/NEerYXUU/powershell-sql-client-tools-sqlps-ssms. There are a few more smiling SQL Server DBAs now.