In recent years, ITIL has become a buzzword and many companies have rightly moved toward managing releases and changes more effectively by utilizing its principles. Since IT tends to be able to control it's own activities, the implementation of at least a semblance of these principles and rules can be fairly easy to accomplish. However, the user community, customers and developers that work closely with them are another matter entirely. And this poses a threat to the integrity of the ITIL process.
When the administrators of servers have to follow rules for releases and changes, it's a good thing. How many problems have resulted in your organization over the years due to finger pointing and no one in IT willing to "fess up" for what was done? Probably too many to count. With the onset of ITIL however, all this was supposed to end or at least be greatly reduced right? Certainly. But what about the users and the developers that may find their bread buttered by those same users? Do we make them follow the rules? I hope the answer is yes but consider this.
If a client/customer/user/developer has admin privileges on a server, are they also bound to follow ITIL rules? I think they should. But has anyone bothered to speak to them about it? I'm guessing the answer to these two questions is no. Does IT management even think about these things? In my view, anyone whose primary function is not server administrative in nature is what I would call a non-qualified admin and has no business being in the local admins group or a 'sysadmin' in SQL Server. Yes I know that dev/test boxes may require exceptions but not every developer needs to be a sysadmin even in Development environments.
But even leaving dev/test environments aside, there are still too many non-qualified admins in production and we all know why this happens. The dreaded "squeaky wheel" gets the grease and there are always exceptions made. Shame on us! IT pros should be the only ones having administrative access to a server. Last time I checked, if a non-qualified admin screws up a server, the qualified admins are still the ones called in to fix it. That's why we should be trying to eliminate, if not greatly reduce the number of these scenarios. I shudder when I walk into a new company and the first thing I see in the local admins group is a list so long that I have to use the scroll bar to see it all. (That might be a good thing to ask to see as part of the hiring process, but fat chance at that right?!!!) It should be no surprise then that my eyes tend to roll quite distinctly when in that same company we are nitpicking about change management tickets needing to be created for restores from production to development environments. Talk about penny-wise and pound foolish!!!
In summary, I think it is foolhardy to insist on proper ITIL procedures for IT people without accounting for the non-qualified admins that exist on servers in the enterprise. If they must have admin access, they also must follow the rules. It's unconscionable for the company to suffer loss not to mention making IT pros work extra hours due to issues that can be traced to one of these individuals making unauthorized changes.
Wednesday, May 1, 2013
Wednesday, April 3, 2013
Script to Generate Database Allocated Size Report
SQL Server has many nice built-in reports but I wrote this one because I couldn't find a quick reference to the current space allocations of all the databases on a server instance. Sample output is shown below. I hope you find this script as useful as I have.
declare @DBSize decimal(12,2),
@name char(35),
@Header varchar (8000),
@EntireLine varchar(8000),
@DecimalValue decimal(10,2),
@TotalSize decimal(12,2) , -- = 0,
@TotalLine varchar(8000)
select @totalsize = 0
DECLARE Databases_Cursor Cursor
FOR SELECT sd.name, sum(smf.size)
FROM sys.master_files smf inner join sys.databases sd on smf.database_id = sd.database_id
where sd.name not in ('Tempdb') --and (sd.name like 'c13_CCHP_Rolling%' or sd.name like 'dbname2%')
group by sd.name, smf.database_id
order by
-- sd.name -- *** Sort by db name
sum(smf.size) desc -- *** Sort by db size
select @Header = 'SQL Instance: ' + @@Servername + char(13) + char(10) + char(13) + char(10) +
'Database Allocated Size (GB)' +
+ char(13) + char(10) + '________________________________________________'
+ char(13) + char(10) + char(13) + char(10)
print @Header
OPEN Databases_Cursor
FETCH NEXT FROM Databases_Cursor
INTO @name, @DBSize
WHILE @@FETCH_STATUS = 0
Begin
select @Entireline = @name + ' ' + Cast(CONVERT(DECIMAL(10,2),@DBSize * 8 / 1000000, 8) as nvarchar) + ' GB'
select @TotalSize = @TotalSize + @DBSize
print @Entireline
FETCH NEXT FROM Databases_Cursor
INTO @name, @DBSize
END
Select @TotalLine = ' __________ ' + char(13) + char(10) + char(13) + char(10) +
' Total: ' + Cast(CONVERT(DECIMAL(10,2),@TotalSize * 8 / 1000000, 8) as nvarchar) + ' GB'
print @TotalLine
Select @TotalLine = char(13) + char(10) + char(13) + char(10)
print @TotalLine
CLOSE Databases_Cursor
DEALLOCATE Databases_Cursor
GO
declare @DBSize decimal(12,2),
@name char(35),
@Header varchar (8000),
@EntireLine varchar(8000),
@DecimalValue decimal(10,2),
@TotalSize decimal(12,2) , -- = 0,
@TotalLine varchar(8000)
select @totalsize = 0
DECLARE Databases_Cursor Cursor
FOR SELECT sd.name, sum(smf.size)
FROM sys.master_files smf inner join sys.databases sd on smf.database_id = sd.database_id
where sd.name not in ('Tempdb') --and (sd.name like 'c13_CCHP_Rolling%' or sd.name like 'dbname2%')
group by sd.name, smf.database_id
order by
-- sd.name -- *** Sort by db name
sum(smf.size) desc -- *** Sort by db size
select @Header = 'SQL Instance: ' + @@Servername + char(13) + char(10) + char(13) + char(10) +
'Database Allocated Size (GB)' +
+ char(13) + char(10) + '________________________________________________'
+ char(13) + char(10) + char(13) + char(10)
print @Header
OPEN Databases_Cursor
FETCH NEXT FROM Databases_Cursor
INTO @name, @DBSize
WHILE @@FETCH_STATUS = 0
Begin
select @Entireline = @name + ' ' + Cast(CONVERT(DECIMAL(10,2),@DBSize * 8 / 1000000, 8) as nvarchar) + ' GB'
select @TotalSize = @TotalSize + @DBSize
print @Entireline
FETCH NEXT FROM Databases_Cursor
INTO @name, @DBSize
END
Select @TotalLine = ' __________ ' + char(13) + char(10) + char(13) + char(10) +
' Total: ' + Cast(CONVERT(DECIMAL(10,2),@TotalSize * 8 / 1000000, 8) as nvarchar) + ' GB'
print @TotalLine
Select @TotalLine = char(13) + char(10) + char(13) + char(10)
print @TotalLine
CLOSE Databases_Cursor
DEALLOCATE Databases_Cursor
GO
Friday, March 15, 2013
Script to Generate Restore Statements
There are many instances when using the SSMS GUI is not practical. One of these is when backing up or restoring large numbers of databases such as when doing server migrations and/or consolidations. I wrote a simple script years ago that only read from the sys.databases table but that proved unusable the first time I ran into a database that had multiple data files. The script below joins sys.databases with sys.master_files and the resulting script will generate the proper restore command no matter how many mdf, ndf or ldf files are in the database. You will likely have to modify the code to adjust for your backup location or for different preferences regarding command line arguments. This will run under SQL 2005 or SQL 2008.
-- Purpose: Generate 'restore database' statements
-- Instructions: Edit 'where' clause and/or backup path information in variables as
-- needed. Run script to generate sql, then edit/modify as needed and run selected sql statements.
--
set nocount ON
DECLARE @RestoreDB VARCHAR (8000)
Declare @Move varchar (8000)
Declare @MoveWithoutComma varchar (8000)
Declare @Go varchar (8000)
Declare @firsttime varchar (5) -- = 'True'
declare @sd_name varchar(255)
declare @smf_name varchar(255)
declare @smf_physical_name varchar (255)
declare @hold_sd_name varchar (255)
select @firsttime = 'True'
DECLARE Database_cursor CURSOR FOR
SELECT sd.name,smf.name, smf.physical_name
FROM sys.master_files AS smf inner join sys.databases AS sd
on smf.database_id = sd.database_id
-- Edit where clause as desired
where sd.name not in ('master', 'tempdb', 'msdb', 'model')
ORDER BY sd.name
OPEN Database_cursor
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
Select @hold_sd_name = @sd_name
WHILE @@FETCH_STATUS = 0
Begin
if @firsttime = 'True'
Begin
-- Edit backup path as needed
select @RestoreDB = 'RESTORE DATABASE [' + @sd_name + ']' + char(13) + char(10) + 'FROM DISK = ''J:\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'
+ @sd_name + '.bak''' + char(13) + char(10) + 'WITH MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''', replace ,' + 'stats = 10' + char(13) + char(10)
print @RestoreDB
select @Go = 'GO' + char(13) + char(10)
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
select @hold_sd_name = @sd_name
else select @firsttime = 'False'
end
else
Begin
-- Move with comma ','
select @Move = 'MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''','
-- Last 'Move' without comma ','
select @MoveWithoutComma = 'MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''''
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
Begin
print @MoveWithoutComma
print @Go
select @hold_sd_name = @sd_name
select @firsttime = 'True'
end
Else print @Move
end
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
GO
-- Purpose: Generate 'restore database' statements
-- Instructions: Edit 'where' clause and/or backup path information in variables as
-- needed. Run script to generate sql, then edit/modify as needed and run selected sql statements.
--
set nocount ON
DECLARE @RestoreDB VARCHAR (8000)
Declare @Move varchar (8000)
Declare @MoveWithoutComma varchar (8000)
Declare @Go varchar (8000)
Declare @firsttime varchar (5) -- = 'True'
declare @sd_name varchar(255)
declare @smf_name varchar(255)
declare @smf_physical_name varchar (255)
declare @hold_sd_name varchar (255)
select @firsttime = 'True'
DECLARE Database_cursor CURSOR FOR
SELECT sd.name,smf.name, smf.physical_name
FROM sys.master_files AS smf inner join sys.databases AS sd
on smf.database_id = sd.database_id
-- Edit where clause as desired
where sd.name not in ('master', 'tempdb', 'msdb', 'model')
ORDER BY sd.name
OPEN Database_cursor
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
Select @hold_sd_name = @sd_name
WHILE @@FETCH_STATUS = 0
Begin
if @firsttime = 'True'
Begin
-- Edit backup path as needed
select @RestoreDB = 'RESTORE DATABASE [' + @sd_name + ']' + char(13) + char(10) + 'FROM DISK = ''J:\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'
+ @sd_name + '.bak''' + char(13) + char(10) + 'WITH MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''', replace ,' + 'stats = 10' + char(13) + char(10)
print @RestoreDB
select @Go = 'GO' + char(13) + char(10)
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
select @hold_sd_name = @sd_name
else select @firsttime = 'False'
end
else
Begin
-- Move with comma ','
select @Move = 'MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''','
-- Last 'Move' without comma ','
select @MoveWithoutComma = 'MOVE ''' + @smf_name + ''' TO '''
+ @smf_physical_name + ''''
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name
if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
Begin
print @MoveWithoutComma
print @Go
select @hold_sd_name = @sd_name
select @firsttime = 'True'
end
Else print @Move
end
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
GO
Thursday, March 14, 2013
Script to Generate Registered Servers
I've always been a fan of Registered Servers in SSMS. However, a few years ago I took a job with a large company that had several hundred servers distributed across DEV, Test and Prod environments. The existing DBA's had made an attempt to organize the servers in a Central Management Server but it quickly became out of date as servers came and went on almost a daily basis and maintenance quickly became a nightmare.
The script below utilizes metadata stored in a maintenance database of some sort. You have to figure that out. I've included a sample table and view. The particular table I used stores the server name, instance name, and environment among other things. Several cursors run consecutively to build the various parts of the XML script. If you have a place where you store this kind of metadata about your SQL Servers, you should be able to modify the cursors (there are 4 of them) and variables.
I've modified the script I posted back in 2013 and it currently works for SQL Server 2016. It should still work for previous versions as well as I have not needed to touch the XML that is generated.
Here is the table, view, main script and instructions.
/****** Object: Table [dbo].[SQL_Metadata_Table] Script Date: 4/9/2017 10:40:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Here's a sample metadata table and view design. The view must also be created to use the "Registered Servers"
script as written. Obviously, you can make modifications to suit your own needs. The script as I've
written it only requires an "Environment" and "Server_Instance_Name" column but you may want to track
more data elements than this.j
To run the script, you must have created the table and view and entered some SQL Instance data.
The output from the script should be saved as a '.regsrvr' file. You then 'import' the file into
Management Studio. Right-click 'Local Server Groups', 'Tasks' and 'Import'. Choose the file you
just saved and click 'OK'. File should import successfully and your registered servers appear.
*/
CREATE TABLE [dbo].[SQL_Metadata_Table](
[Server_Name] [varchar](255) NOT NULL,
[Instance_Name] [nvarchar](255) NULL,
[Server Desc] [nvarchar](255) NULL,
[Environment] [nvarchar](255) NULL, -- i.e. DEV, TEST, PROD
[Primary_DBA] [int] NULL, -- Numeric value for each DBA i.e. Dave = 1, Tom = 2, Mary = 3
[Secondary_DBA] [int] NULL, -- Same as above
[Status] [smallint] NULL, -- Active = 1
[Server_Comment] [varchar](255) NULL)
go
CREATE VIEW [V_SQL_Metadata_Table]
AS
SELECT [Environment],
Server_Instance_Name = CASE
WHEN [Instance_Name] IS NULL THEN [Server_Name]
ELSE [Server_Name] + '\' + [Instance_Name]
END
FROM [dbo].[SQL_Metadata_Table];
go
-- Script follows
declare @Environment varchar (50),
@Server varchar (50),
@Header varchar (8000),
@MasterServerName varchar (50),
@SubGrpLine1 varchar (8000),
@SubGrpLine2 varchar (8000),
@SubGrpLine3Header varchar (8000),
@SubGrpLine3 varchar (8000),
@SubGrpLine3Footer varchar (8000),
@SubGrpLine4Header varchar (8000),
@SubGrpLine4 varchar (8000),
@SubGrpLine4Footer varchar (8000),
@SubGrpLineFooter varchar (8000),
@EntireLine varchar(8000),
@Footer varchar (8000)
-- Set Master Server Name
select @MasterServerName = 'Insert your server/instance name here'
--
-- Generate XML for generic header
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
select @Header = '<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
<identity>
<name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
<baseURI>http://documentcollection/</baseURI>
</identity>
<xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/system/schema/RegisteredServers</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="ServerGroup">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="RegisteredServer">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
<instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</sml:Uri>
</sfc:Reference>
</sfc:Collection>
</RegisteredServers:RegisteredServers>
<RegisteredServers:ServerGroups>
<sfc:Collection>'
print @Header
--
-- Generate XML to create 1st Section for SQL Server Groups
--
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine1 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine1
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLineFooter = '</sfc:Collection>
</RegisteredServers:ServerGroups>
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @MasterServerName + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @MasterServerName + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLineFooter
--
-- Generate XML to create 2nd Section for SQL Server Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine2 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Environment + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine2
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML to create Section for SQL Server Servers within Groups
--
-- Section Header
select @SubGrpLine3Header = '<document>
<docinfo>
<aliases>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>'
Print @SubGrpLine3Header
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine3 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '/RegisteredServer/' + @Server + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine3
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLine3Footer = '</sfc:Collection>
</RegisteredServers:RegisteredServers>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine3Footer
--
-- Generate XML to create 2nd Section for SQL Server Servers within Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine4 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '/RegisteredServer/' + @Server + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Server + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @Server + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Server + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLine4
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML for final footer
--
select @Footer = ' </instances>
</RegisteredServers:bufferData>
</xs:schema>
</data>
</document>
</definitions>
</xs:bufferSchema>
</model>'
print @Footer
GO
The script below utilizes metadata stored in a maintenance database of some sort. You have to figure that out. I've included a sample table and view. The particular table I used stores the server name, instance name, and environment among other things. Several cursors run consecutively to build the various parts of the XML script. If you have a place where you store this kind of metadata about your SQL Servers, you should be able to modify the cursors (there are 4 of them) and variables.
I've modified the script I posted back in 2013 and it currently works for SQL Server 2016. It should still work for previous versions as well as I have not needed to touch the XML that is generated.
Here is the table, view, main script and instructions.
/****** Object: Table [dbo].[SQL_Metadata_Table] Script Date: 4/9/2017 10:40:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Here's a sample metadata table and view design. The view must also be created to use the "Registered Servers"
script as written. Obviously, you can make modifications to suit your own needs. The script as I've
written it only requires an "Environment" and "Server_Instance_Name" column but you may want to track
more data elements than this.j
To run the script, you must have created the table and view and entered some SQL Instance data.
The output from the script should be saved as a '.regsrvr' file. You then 'import' the file into
Management Studio. Right-click 'Local Server Groups', 'Tasks' and 'Import'. Choose the file you
just saved and click 'OK'. File should import successfully and your registered servers appear.
*/
CREATE TABLE [dbo].[SQL_Metadata_Table](
[Server_Name] [varchar](255) NOT NULL,
[Instance_Name] [nvarchar](255) NULL,
[Server Desc] [nvarchar](255) NULL,
[Environment] [nvarchar](255) NULL, -- i.e. DEV, TEST, PROD
[Primary_DBA] [int] NULL, -- Numeric value for each DBA i.e. Dave = 1, Tom = 2, Mary = 3
[Secondary_DBA] [int] NULL, -- Same as above
[Status] [smallint] NULL, -- Active = 1
[Server_Comment] [varchar](255) NULL)
go
CREATE VIEW [V_SQL_Metadata_Table]
AS
SELECT [Environment],
Server_Instance_Name = CASE
WHEN [Instance_Name] IS NULL THEN [Server_Name]
ELSE [Server_Name] + '\' + [Instance_Name]
END
FROM [dbo].[SQL_Metadata_Table];
go
-- Script follows
declare @Environment varchar (50),
@Server varchar (50),
@Header varchar (8000),
@MasterServerName varchar (50),
@SubGrpLine1 varchar (8000),
@SubGrpLine2 varchar (8000),
@SubGrpLine3Header varchar (8000),
@SubGrpLine3 varchar (8000),
@SubGrpLine3Footer varchar (8000),
@SubGrpLine4Header varchar (8000),
@SubGrpLine4 varchar (8000),
@SubGrpLine4Footer varchar (8000),
@SubGrpLineFooter varchar (8000),
@EntireLine varchar(8000),
@Footer varchar (8000)
-- Set Master Server Name
select @MasterServerName = 'Insert your server/instance name here'
--
-- Generate XML for generic header
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
select @Header = '<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
<identity>
<name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
<baseURI>http://documentcollection/</baseURI>
</identity>
<xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/system/schema/RegisteredServers</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="ServerGroup">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="RegisteredServer">
<xs:complexType>
<xs:sequence>
<xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
<RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
<instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</sml:Uri>
</sfc:Reference>
</sfc:Collection>
</RegisteredServers:RegisteredServers>
<RegisteredServers:ServerGroups>
<sfc:Collection>'
print @Header
--
-- Generate XML to create 1st Section for SQL Server Groups
--
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine1 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine1
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLineFooter = '</sfc:Collection>
</RegisteredServers:ServerGroups>
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>
<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/RegisteredServer/001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">001 ' + @MasterServerName + ' - *** Master Maintenance Server ***</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @MasterServerName + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @MasterServerName + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLineFooter
--
-- Generate XML to create 2nd Section for SQL Server Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
group by [Environment]
order by [Environment]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine2 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Environment + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine2
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML to create Section for SQL Server Servers within Groups
--
-- Section Header
select @SubGrpLine3Header = '<document>
<docinfo>
<aliases>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:RegisteredServers>
<sfc:Collection>'
Print @SubGrpLine3Header
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine3 = '<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/' + @Environment + '/RegisteredServer/' + @Server + '</sml:Uri>
</sfc:Reference>'
print @SubGrpLine3
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
select @SubGrpLine3Footer = '</sfc:Collection>
</RegisteredServers:RegisteredServers>
</RegisteredServers:ServerGroup>
</data>
</document>'
print @SubGrpLine3Footer
--
-- Generate XML to create 2nd Section for SQL Server Servers within Groups
--
DECLARE ServerGroup_Cursor Cursor
FOR SELECT UPPER([Environment]),UPPER([Server_Instance_Name])
FROM [Dave1].[dbo].[V_SQL_Metadata_Table]
-- Sample where clause where Status = 1
order by [Environment], [Server_Instance_Name]
OPEN ServerGroup_Cursor
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
WHILE @@FETCH_STATUS = 0
Begin
select @SubGrpLine4 = '<document>
<docinfo>
<aliases>
<alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '/RegisteredServer/' + @Server + '</alias>
</aliases>
<sfc:version DomainVersion="1" />
</docinfo>
<data>
<RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<RegisteredServers:Parent>
<sfc:Reference sml:ref="true">
<sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/'+ @Environment + '</sml:Uri>
</sfc:Reference>
</RegisteredServers:Parent>
<RegisteredServers:Name type="string">' + @Server + '</RegisteredServers:Name>
<RegisteredServers:Description type="string" />
<RegisteredServers:ServerName type="string">' + @Server + '</RegisteredServers:ServerName>
<RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
<RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
<RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
<RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=' + @Server + ';trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
<RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">None</RegisteredServers:CredentialPersistenceType>
</RegisteredServers:RegisteredServer>
</data>
</document>'
print @SubGrpLine4
FETCH NEXT FROM ServerGroup_Cursor
INTO @Environment, @Server
END
CLOSE ServerGroup_Cursor
DEALLOCATE ServerGroup_Cursor
--
-- Generate XML for final footer
--
select @Footer = ' </instances>
</RegisteredServers:bufferData>
</xs:schema>
</data>
</document>
</definitions>
</xs:bufferSchema>
</model>'
print @Footer
GO
Subscribe to:
Posts (Atom)