Last week I was working on a project to help us better identify over 600 databases we have in production. The final solution wound up including the following components:
- Store various contact people and internal information on the databases themselves as Extended Properties. We are tagging all of our production databases with a business owner, system owner, and development lead, as well as information describing the application(s) that the database supports. While extended properties are a rarely used functionality within SQL Server, they are a good fit for our purposes:
- Extended properties travel with the database, so when we restore production databases to our development and testing SQL instances (either weekly or nightly in most cases) the non-production database copies will automatically have the same extended properties. This is beneficial for us because we only have to maintain the content on the production databases and the information will automatically “trickle down” to the non-production environments with our existing restore processes.
- Extended properties are simple, expandable and native to SQL Server. Extended Properties are implemented as simple name/value pairs so they are extremely flexible. We will easily be able to alter the types and number of tags we want to assign to the databases without extensive development efforts. You can also view the DB extended properties by right clicking on any database in SSMS which is a nice to have for DBAs and SysAdmin troubleshooting issues either on the machine itself or with remote access via SSMS.
- Extended properties are backwards compatible to SQL 2005. We still have a handful of SQL 2005 instances and a few dozen SQL 2008 instances in our environment, so we wanted a mechanism that would work the same for everything we are running today.
- Establish a centralized dataset of the database properties to support a Reporting Services report that is current and usable by application support and other less technical or privileged teams (no SSMS access is most cases). We often make database server performance information available to non-DBA team members with custom SSRS reports that render various monitoring data (session activity, wait stats, etc). A report that allows users to review the internal contact people and applications associated with specific databases is very useful for our application support team to determine who to contact if there are issues or questions.
In the initial phase of this effort, we were provided a spreadsheet that was exported from our disaster recovery planning system with the application name, system owner, business owner, and lead developer. This information didn’t include a direct link to individual databases, so we went through and updated the spreadsheet with servernames (instances) and DB names. We then developed a powershell script to go through the spreadsheet, connect to the various remote servers and assign DB level extended properties to the database in question. This was great for our initial effort, but as you might imagine, there were several applications that we couldn’t map directly to a database and we needed to work with various business users to identify the appropriate app/db mappings. We got the initial dataset done and propagated out to the various databases, but we needed a simple mechanism to allow users to assign an app/app owners set to a database easily from a web based front end.
In the end, we decided to provide a stored procedure on our central monitoring database instance that would take application/owner information as parameters and add the database level extended properties on the remote servers automatically. Since we already had a decent set of working code to do this in powershell (from the initial import), we decided to implement the procedure by having it call a powershell ps1 script with parameters to shell out from the SQL stored procedure by using sp_executesql.
The powershell file is attached at the end of this post for reference, but here is the syntax for calling the PS1 script file with arguments(parameters) from within a SQL stored procedure.
create procedure [dbo].[set_DatabaseProperties] (@ServerName varchar(100) = NULL, @DatabaseName varchar(100) = NULL, @App varchar(500) = 'UNKNOWN', @AppDesc varchar(500) = 'UNKNOWN', @ApprovalUser varchar(500) = 'UNKNOWN', @BusinessFunction varchar(500) = 'UNKNOWN', @BusinessOwner varchar(500) = 'UNKNOWN', @Dept varchar(500) = 'UNKNOWN', @LeadDeveloper varchar(500) = 'UNKNOWN', @ManagedBy varchar(500) = 'UNKNOWN', @SystemOwner varchar(500) = 'UNKNOWN', @Type varchar(500) = 'UNKNOWN' ) /************************************************************************************ ** ** 07/25/13 MRaftree Initial Creation ** */ AS BEGIN DECLARE @CmdExecStr varchar(5000) if (@ServerName is not null and @DatabaseName is not null and @App <> 'UNKNOWN') BEGIN -- build a command line string to execute the PS1 file with each argument in double quotes and separated by a space Also, check the location of the powershell.exe file --on your database server itself and make sure the path below is right Select @CmdExecStr = 'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe -File "C:\Powershell\SetDBProperties.ps1" "' + isnull(@ServerName,'Unknown') +'" "' + isnull(@DatabaseName,'Unknown') +'" "' + @App +'" "' + @AppDesc +'" "' + @ApprovalUser +'" "' + @BusinessFunction+'" "' + @BusinessOwner +'" "' + ISNULL(@Dept,'Unknown') +'" "' + @LeadDeveloper +'" "' + @ManagedBy +'" "' + @SystemOwner +'" "' + ISNULL(@Type,'Unknown') +'" ' print @CmdExecStr create table #returncode (value varchar(1000)) insert into #returncode exec master..xp_cmdshell @CMDExecStr select * from #ReturnCode END END
Here is the ps1 file itself for reference: SetDBProperties.ps1