Transcript

Overcoming Limitations of Microsoft SQL Server Management Studio

Virtually everyone who works with Microsoft SQL Server uses SQL Server’s Management Studio (SSMS), even those with access to similar solutions. Many data professionals are quite familiar with SSMS, and it is a nice tool. However, it also has limitations. Third-party software offers capabilities such as performance monitoring, security and compliance management, backup and restore management, including instant recovery, and inventory management. Third-party supplements for SSMS should provide these benefits for environments that run many instances of SQL Server simultaneously as well as single instances.

Performance monitoring

SQL Server Management Studio:
+ displays availability and performance metrics
– not suitable for extensive performance management

Third-party software:
+ continuously monitors large SQL Server environments
+ displays alerts and summaries of alerts, and send notifications
+ performs diagnostics with deep-dive drill-downs

Regulatory compliance

SQL Server Management Studio:
+ view user activity and manage user access to databases – not suitable for extensive auditing

Third-party software:
+ continuously monitors large SQL Server environments
+ uses lightweight data collection mechanisms that minimize server impact
+ tracks user activities and data changes

Security monitoring

SQL Server Management Studio:
+ displays user activity and manage user access to databases
– not suitable for extensive security management

Third-party software:
+ identifies vulnerabilities in SQL Server and Azure environments to harden security policies
+ analyzes user permissions across database objects
+ creates reports on user permissions

Backup management

SQL Server Management Studio:
+ start and stop backups and restores, and displays backup settings and status
– not suitable for extensive backup and restore management

Third-party software:
+ manages large SQL Server environments
+ provides dynamic compression to balance backup time against compression
+ provides network failure resilience to reduce backup failures

Inventory management

SQL Server Management Studio:
+ automatically discovers instances and provides basic management of instances
– not suitable for extensive inventory management

Third-party software:
+ discovers instances with multiple advanced methods
+ manages large SQL Server environments
+ manages editions, versions, patches, and licensing for compliance with Microsoft licensing requirements

SSMS lacks many capabilities that DBAs need to fulfill their roles effectively. Fortunately, third-party tools are available to fill these gaps in SSMS’s functionality.