When you have thousands of SQL Servers, it is very hard to administer all of them. This article will show some tips to help you with these types of tasks.
In this new article, we will show how to run T-SQL scripts against multiple Servers using SQL Server Management Studio (SSMS). To do this, we will use the Central Management Server. The main idea of this feature is to administer multiple servers in a centralized way using queries or policies. This feature is available in SQL Server 2008 or later versions and cannot be applied in older versions.
- SQL Server 2008 or later (in this example, we are using SQL Server 2014).
- We are using 2 local SQL instances with the Adventureworks Database installed in each instance.
- Open the SSMS.
In the menu, go to View>Registered Servers
Figure 1. Registered Servers
You can create New Server Groups or use the current Local Server Groups folder. In this example, we are going to use the existing folder group and add the new server registration to this group.
Figure 2. New Server Registration
You can add the authentication information of the servers to connect to it. You can test the connection and add a description.
Figure 3. Connection properties
You can optionally define in the Connection Properties tab to which database connect by default, the network protocol (TCP-IP, Shared Memory or Named Pipes). You can also define the packet size. If you execute many bulk operations, it may be convenient to increase the packet size. If you do not perform many operations, the other hand may reduce the packet size reduced to increase the efficiency.
You can also specify the connection timeout and execution timeout. It is also possible to encrypt the connection and select a custom color.
Figure 4. The Connection properties
- Repeat the steps 3, 4 and 5 to add all the SQL Servers available.
Once you have all the SQL Servers added, right click on the Server Group and select New Query. With this option, you can run a T-SQL query over multiple SQL Servers.
Figure 5. Running a Query
Let’s start running a simple query showing the server names:123select @@servername
The query will show the different server names.
Figure 6. The SQL Server Names.
You could also create a database in each SQL Server:123create database db4
If you run the query, you will have a successful or failure message.
Figure 7. Creating Databases
You can also check the sessions, users, processes blocks using the sp_who2 system procedure:123sp_who2
Figure 8. The current users, sessions and processes of all the SQL Server instances.
If you have the same databases on the Server with the same tables, you can check the fragmentation in all the servers. The following sample shows how to check the average fragmentation percentage higher than 30% in the Person.Address table of the Adventureworks2014 database:1234567891011DECLARE @db_id SMALLINT;DECLARE @object_id INT;SET @db_id = DB_ID(N'AdventureWorks2014');SET @object_id = OBJECT_ID(N'AdventureWorks2014.Person.Address');SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')where avg_fragmentation_in_percent>30
You can also verify the status of the SQL Server Agent jobs. For example, to verify the SQL jobs that failed in all the SQL Server instances, you can use the following T-SQL query:1234567891011121314151617181920SELECT [instance_id],[job_id],[step_id],[step_name],[sql_message_id],[sql_severity],[message],[run_status],[run_date],[run_time],[run_duration],[operator_id_emailed],[operator_id_netsent],[operator_id_paged],[retries_attempted],[server]FROM [msdb].[dbo].[sysjobhistory]where run_status=0
The information of the job history is in the system table sysjobhistory. The run_status shows the status of the job. If the status of the job is 0, it means that the job failed. If the status is 1, it means that the job is run successfully. 2 is that the job retried and 4 that the job was cancelled.
Figure 9. The status of the jobs
Another popular query is to check the database size of all the databases at the same time. The database size can be obtained from the sys.master_files. In the system table system databases, you can obtain all the databases. You sum all the sizes of the files because one database can have multiple data files and log files. The type 0 are datafiles and the type 1 are log files. The query would be the following:1234567891011121314with fas(select database_id, type, size * 8.0 / 1024 filesizefrom sys.master_files)selectname,(select sum(filesize) from f where type = 0 and f.database_id = d.database_id) DataSize,(select sum(filesize) from f where type = 1 and f.database_id = d.database_id ) LogSizefrom sys.databases d
The result of the query will show the data files and log files of all the databases of all the instances:
Figure 10. The data file sizes and log sizes.
The following command will enable advanced options in all the SQL Servers:123456USE master;GOEXEC sp_configure 'show advanced option', '1';RECONFIGURE WITH OVERRIDE;
Once enabled, we will enable an advanced option xp_cmdshell. This extended stored procedure allows running the command line from the T-SQL. It is a very powerful feature that will work depending on the privileges of the account used to connect in the step 3 to the servers. You will need some administrative privileges to run the command. Let’s enable the xp_cmdshell procedure first:1234EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE WITH OVERRIDE;
The xp_cmdshell is a very powerful command, but a dangerous one for security reasons. That is why it is disabled by default. The following example creates a folder named backupsfolder in the c drive:123xp_cmdshell 'mkdir c:\\backupsfolder'
If everything is OK, you will have a new folder in the Operative System:
Figure 11. The folder backupsfolder created.
Finally, let’s run the backup in the folder created:1234BACKUP DATABASE [db4] TO DISK = N'c:\\backupsfolder\db4_a.bak' WITH NOFORMAT, NOINIT, NAME = N'db4-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO
If everything is OK, you will have a backup created for all the SQL Server instances.
Figure 12. The database backup
In this chapter, we showed how to run different queries in multiple SQL Server instances. We verified the server names, we created a database in all the SQL Server instances, we verified the current users, sessions and processes, we checked the fragmentation higher than 30 of a specific table in all the instances, we also verified if the SQL Agent Jobs have some errors in the execution. We finally enabled advanced configuration options and created a folder in all the SQL Servers. The last step was to create the backup in the new folder. As you can see, administering all the servers in a centralized way can be a straightforward task.
You can also apply SQL policies to multiple Server at the same time. The evaluating policies on multiple instances article will show you how to do it.