MSSQLTips: Tip #50 – Different ways to sort multi-server SQL Server query results using Central Management Server

Central Management Server (CMS) is a SQL Server feature that allows you to manage multiple SQL Servers at the same time. You can run T-SQL scripts or execute Policy-Based Management policies on multiple servers. You can also group SQL Servers and run the scripts on a specific group of SQL Servers.

How else can CMS be used? How can we overcome some of the limitations when we run queries against CMS Server Groups?

In this tip we will show you a couple of CMS tricks. In our examples below we will show how you can sort the CMS query results for different scenarios. We will also provide a couple of examples of alternative uses of CMS.

Please read the latest MSSQLTips post: “Different ways to sort multi-server SQL Server query results using Central Management Server“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQLTips: Tip #49 – SQL Servers Assessment for the Meltdown and Spectre Vulnerabilities

2018 started out with bad news for most IT professionals. A new “speculative execution side-channel attacks” vulnerability affected many modern processors and operating systems. This vulnerability is very complex and requires patching on many layers, including hardware, operating systems and the application layer.

Database Administrators (DBAs) have to be prepared to patch every SQL Server starting with SQL Server version 2008 and the recommended patching procedure is different for different servers depending on SQL Server configuration settings and features used.

How can we determine what servers are affected and what steps should we take to protect SQL Server?

In this tip we will provide a “Meltdown/Spectre SQL Server Assessment Script” that you can run in Central Management Server (CMS) against all of your SQL Servers. This script will produce a report with recommendations/scenarios for patching and display configurations/features that are not secure and enabled on SQL Server.

Then we will provide a couple of remediation steps to review unsecure use cases.

After remediation we will run the Main Assessment Script again and review the final recommendations.

Please read the latest MSSQLTips post: “SQL Servers Assessment for the Meltdown and Spectre Vulnerabilities“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQLTips: Tip #48 – SQL Server Database Mail Health Check with Policy Based Management

Email alerting is an important feature that helps to automate a lot of Database Administration tasks. How can we be sure that Database Mail works properly? If there is email problem there is no another email that will notify us of an issue and it could be days before we realize that there is a problem.

In this tip we will provide a solution to check all of your servers for the different types of Database Mail issues. We are going to use Policy-Based Management (PBM) to perform these checks.

Please read the latest MSSQLTips post: “SQL Server Database Mail Health Check with Policy Based Management“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQLTips: Tip #43 – SQL Server Agent Jobs Monitoring Script

Problems with SQL Server Agent Jobs can lead to missing backups, missing notifications about a failed job and sometimes missing mission critical tasks that are run as scheduled jobs.

Some of the common SQL Server Agent Job problems are described in other MSSQLTips tips. In this tip we have provided a single script that will check the jobs for a number of issues including:

  • Disabled SQL Server Agent Jobs
  • Failed SQL Server Agent Jobs or Job Steps
  • SQL Server Agent Jobs without a Description
  • SQL Server Agent Jobs without Schedules
  • Disabled SQL Server Agent Job Schedules
  • SQL Server Agent Jobs without Categories
  • SQL Server Agent Jobs Owned by a DBA or Other Privileged User
  • SQL Server Agent Jobs with no Owner or Owner Login is Denied “CONNECT TO SQL”
  • No SQL Server Agent Notifications on Job Failure or Completion
  • SQL Server Agent Operators Issues
  • SQL Server Agent Missing Job History
  • Potential SQL Server Agent Job Step Issue or Step Won’t Execute

Please read the latest MSSQLTips post: “SQL Server Agent Jobs Monitoring Script“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQLTips: Tip #42 – SQL Server Edition Post-Downgrade Steps

After downgrading SQL Server Enterprise Edition to the Standard one some of the configuration settings are reset to the default values. In this tip we provide examples of some of the issues you may have after the SQL Server Edition downgrade.

Please read the latest MSSQLTips post: “SQL Server Edition Post-Downgrade Steps“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #24 – SQL Server 2016 Query Store Queries

What are some practical applications of using the Query Store? What queries can be run against the Query Store? What sort of questions can I get answered?

Please read my latest MSSQL Tip: “SQL Server 2016 Query Store Queries“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #22 – Compare SQL permissions using SQL Server Data Tools

In this new tip you will find out how to use Visual Studio and SQL Server Data Tools to compare a database permissions and to copy permissions (including users and logins).

Please read the latest MSSQL Tip: “Compare SQL permissions using SQL Server Data Tools“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #11 – Script to get CPU and Cores for SQL Server 2012 Licensing

This MSSQLTips tip will help you to audit your SQL Server licensing with PowerShell script.

Please read the latest MSSQL Tip: “Script to get CPU and Cores for SQL Server 2012 Licensing“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

MSSQL Tips: Tip #10 – Different Ways to Find SQL Server Object Dependencies

In my 10th MSSQLTips tip you will learn about different ways to display objects dependencies and you will learn how to use provided scripts in your day-to-day work.

Please read the latest MSSQL Tip: “Different Ways to Find SQL Server Object Dependencies“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.