Introduction

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.

Requirements

• 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.

Getting started

1. Open the SSMS.

2. In the menu, go to View>Registered Servers

   
   

   Figure 1. Registered Servers

3. 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

4. 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

5. Repeat the steps 3, 4 and 5 to add all the SQL Servers available.

6. 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

7. Let’s start running a simple query showing the server names:

   1 2 3

   select @@servername

8. The query will show the different server names.

   

   Figure 6. The SQL Server Names.

9. You could also create a database in each SQL Server:

   1 2 3

   create database db4

10. If you run the query, you will have a successful or failure message.

    
    

    Figure 7. Creating Databases

11. You can also check the sessions, users, processes blocks using the sp_who2 system procedure:

    1 2 3

    sp_who2

    
    

    Figure 8. The current users, sessions and processes of all the SQL Server instances.

12. 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:

    1 2 3 4 5 6 7 8 9 10 11

    DECLARE @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

13. 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:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

    SELECT [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

14. 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

15. 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:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14

    with f as (     select database_id, type, size * 8.0 / 1024 filesize     from sys.master_files ) select     name,     (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 ) LogSize from sys.databases d

16. 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.

17. The following command will enable advanced options in all the SQL Servers:

    1 2 3 4 5 6

    USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE WITH OVERRIDE;

18. 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:

    1 2 3 4

    EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE WITH OVERRIDE;

19. 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:

    1 2 3

    xp_cmdshell 'mkdir c:\\backupsfolder'

20. If everything is OK, you will have a new folder in the Operative System:

    
    

    Figure 11. The folder backupsfolder created.

21. Finally, let’s run the backup in the folder created:

    1 2 3 4

    BACKUP DATABASE [db4] TO  DISK = N'c:\\backupsfolder\db4_a.bak' WITH NOFORMAT, NOINIT,  NAME = N'db4-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 GO

22. If everything is OK, you will have a backup created for all the SQL Server instances.

    
    

    Figure 12. The database backup

Conclusion

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.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023