So, in the previous post we talked about changing the number of SQL Error logs. With that done, we now need to actually make use of all these logs. I like keeping each days errors in its own log file. Then if a problem arises and it was last week Tuesday, well then look in the corresponding log file.
There is a system stored procedure sp_cycle_errorlog, that will do exactly the above, close one log and open a new log. All I do now is schedule this stored procedure as a SQL Agent Job to run at midnight every day. Thats easy enough on 1 server, but remember, I have 151. So I script out the SQL Agent Job. Great, now I have a .sql file that I need to deploy to 151 servers. Thats if I'm not using a master - target server configuration. That in itself is a fantastic concept, but I've never seen it at any client I've started at.
Another blogger, SerialSeb, posted a very handy piece of code that does what I need to do. Check out his post.
Basically what he does is create a batch file that contains a sqlcmd command. This command as you know can execute the contents of a file against a said SQL server. Then the code runs the batch file using cmd /c. Go to the command prompt and type: cmd /?. It returns "/C Carries out the command specified by string and then terminates". If the batch file exists the contents of it are overwritten. Good one Seb.
Great, so unit of work encapsulted, got list of servers, its go time.
foreach ($server in $serverlist) {Execute-SqlFile $file $server $dbname $WindowsAuthentication=true}
Where $file is the .sql file containing the T-SQL for the SQL Agent job. $dbname doesn't matter because the .sql file changes the database to MSDB anyway.
Go forth and deploy.
Monday, April 20, 2009
Change the number of SQL Error Logs using Powershell
One of the first places to start troubleshooting a potential SQL problem is to look at the SQL Error Logs. If there is a serious problem, it will normally show up in this log. The thing is, the default setting for SQL is to keep 6 logs. Each log is created when SQL Server starts up. So if a SQL server is restarting often, which is already a problem, you could have very little SQL error log history. On the other hand if SQL stays up for weeks or months at a time, which is a good thing, you will potentially have a very big error log to sift through to find any errors.
I recommend configuring 31 log files. This number can be anything from 6 to 99. I choose 31 because that will give me a months worth of error logs. How so if the error logs only get created when SQL Server restarts? I'll cover that in a subsequent blog.
But first, actually changing the number of error logs. One could obviously do it in Enterprise Manager or SQL Management Studio. But in my case I started at a client with 151 SQL servers of differing versions. Click click, click click. Very tedious. You guessed it, Powershell! :-)
What I like to do is create functions that encapsulate the unit of work that I want to accomplish. Once I have this function I can easily and neatly deploy this unit of work to a list of servers.
The function to change the number of SQL Error Logs is quite straighforward. It uses the Reg command to add the NumErrorLogs value to a certain registry key. There is actually no way to do this through T-SQL, unless I use xp_regwrite extended stored procedure. But that would entail connecting to SQL to write a value to the registry. Not necessary. I actually do connect to SQL, but only to determine the version of SQL. This I guess I could also do in the registry, but in my function I've connected to SQL using SMO.
So lets look at some code. Following is the function code:
The code for get-sqlserver is:
Great, so now I have my unit of work defined as Change-ErrorLogs. You can probably guess the rest. But if you can't, here is the code:
Servers2.txt contains a list of all my SQL Servers. Grab that list into $ss. And then for each server ($s) in the list ($ss), Change the number of error logs (Change-ErrorLogs). Tada!
I recommend configuring 31 log files. This number can be anything from 6 to 99. I choose 31 because that will give me a months worth of error logs. How so if the error logs only get created when SQL Server restarts? I'll cover that in a subsequent blog.
But first, actually changing the number of error logs. One could obviously do it in Enterprise Manager or SQL Management Studio. But in my case I started at a client with 151 SQL servers of differing versions. Click click, click click. Very tedious. You guessed it, Powershell! :-)
What I like to do is create functions that encapsulate the unit of work that I want to accomplish. Once I have this function I can easily and neatly deploy this unit of work to a list of servers.
The function to change the number of SQL Error Logs is quite straighforward. It uses the Reg command to add the NumErrorLogs value to a certain registry key. There is actually no way to do this through T-SQL, unless I use xp_regwrite extended stored procedure. But that would entail connecting to SQL to write a value to the registry. Not necessary. I actually do connect to SQL, but only to determine the version of SQL. This I guess I could also do in the registry, but in my function I've connected to SQL using SMO.
So lets look at some code. Following is the function code:
function Change-ErrorLogs ($s)
{
$svr = get-sqlserver $s
if (($svr.get_information().version.major) -eq 8)
{
#SQL 2000
reg add \\$s\HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer /v NumErrorLogs /t REG_DWORD /d 31
write-host "SQL 2000 Server - $s Config changed"
}
else
{
#SQL 2005
reg add "\\$s\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer" /v NumErrorLogs /t REG_DWORD /d 31
write-host "SQL 2005 Server - $s Config changed"
}
}
The code for get-sqlserver is:
function Get-SqlServer
{
param([string]$sqlserver=$(throw 'Get-SqlServer:`$sqlserver is required.'))
#When $sqlserver passed in from the SMO Name property, brackets
#are automatically inserted which then need to be removed
$sqlserver = $sqlserver -replace "\[|\]"
Write-Verbose "Get-SqlServer $sqlserver"
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
#trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect()
return $server
} #Get-SqlServer
Great, so now I have my unit of work defined as Change-ErrorLogs. You can probably guess the rest. But if you can't, here is the code:
$ss = gc 'servers2.txt'
foreach ($s in $ss) {Change-ErrorLogs $s}
Servers2.txt contains a list of all my SQL Servers. Grab that list into $ss. And then for each server ($s) in the list ($ss), Change the number of error logs (Change-ErrorLogs). Tada!
Wednesday, April 8, 2009
Resolving a list of IP Addresses to Hostnames using Powershell
Following on from my post on Auditing and Summarizing Logon activity on a SQL Server, I figured I need to automate the resolving of IP Addresses to Host names to make the Audit information more useful.
So off I go to Google or MSDN and find [System.Net.Dns]::GetHostbyAddress($IP). Nice! This function can be called from Powershell and does exactly what I need it to do, resolve an IP Address (string) to a hostname.
I created a Powershell Function that wraps this function and adds some rudimentary error handling. I need error handling (who or what doesn't) because the hosts that populates list of IP addresses may not always be available and the host name resolution will fail. Powershell Resolve-IP function code:
Cool, so now we can grab our list of IP Addresses from our AuditLoginHistory table. I do this by using a SQL SMO function called get-sqldata that runs a T-SQL query and returns the results. So my T-SQL Query is:
select distinct host from auditloginhistory
I then use a foreach loop to iterate through the IP Addresses and resolve each IP address to a hostname. The final results, in my case, is exported to a .csv. I'll then Bulk Insert this csv list of IP Addresses and Host names into a SQL table using another SQL SMO function. Then I'll join the Host_IP table to my AuditLoginHistory and display both IP Address and Hostname.
Below is the Powershell script that runs the above:
So off I go to Google or MSDN and find [System.Net.Dns]::GetHostbyAddress($IP). Nice! This function can be called from Powershell and does exactly what I need it to do, resolve an IP Address (string) to a hostname.
I created a Powershell Function that wraps this function and adds some rudimentary error handling. I need error handling (who or what doesn't) because the hosts that populates list of IP addresses may not always be available and the host name resolution will fail. Powershell Resolve-IP function code:
Function Resolve-IP ($IP) {
trap {
write-host "An error occured: "
write-host "ID: " $_.ErrorID
write-host "Message: "$_.Exception.Message
throw "Couldn't Resolve $IP"
}
$results = [System.Net.Dns]::GetHostbyAddress($IP)
$results
}
Cool, so now we can grab our list of IP Addresses from our AuditLoginHistory table. I do this by using a SQL SMO function called get-sqldata that runs a T-SQL query and returns the results. So my T-SQL Query is:
select distinct host from auditloginhistory
I then use a foreach loop to iterate through the IP Addresses and resolve each IP address to a hostname. The final results, in my case, is exported to a .csv. I'll then Bulk Insert this csv list of IP Addresses and Host names into a SQL table using another SQL SMO function. Then I'll join the Host_IP table to my AuditLoginHistory and display both IP Address and Hostname.
Below is the Powershell script that runs the above:
. ./Function_Resolve-IP.ps1
$hosts = get-sqldata 'sql1' master "select distinct host from auditloginhistory"
foreach ($IP in $hosts) {Resolve-IP $IP.host.tostring()}
$ipex = {foreach ($IP in $hosts) {Resolve-IP $IP.host.tostring()}}
$ipex.invoke() | Select @{N="HostName";E={$_.HostName.tostring()}}, @{N="IP_Address";E={$_.get_AddressList()}} | export-csv "host-ip.csv" -NoTypeInformation
Monday, April 6, 2009
Auditing and Summarizing Logons to SQL Server
I'm sure you've come across a scenario where at a new client or employer you want to embark on a SQL Server Login cleanup. But a lot of the logins are from the dark ages and the current IT staff don't know which logins are used by who and for what.
Well there are two ways to view which logins are logging onto the SQL server and when: The one way is to enable successful and failed logon attempts at the SQL server level. This will write an entry to the SQL Error Log for every failed or successful logon. On a busy server this would make the error log huge. Besides that, the audit information is in a flat text file, which can be viewed, but not summarised easily.
Another way to audit logons to your SQL Server is to enable a server wide Logon Trigger, which as you know is available in SQL 2005 & 2008. See BOL for more details. This trigger exposes some XML Data about the logon and this data is made accessible using the EVENTDATA function.
The solution I've written to audit and summarize logon information uses a logon trigger, two logon Audit tables and a daily summarizing script. I've created these tables in the Master database, but they should ideally be created in a user database. The first table, AuditLogin stores all the data that results from the logon trigger firing. I've chosen to store ServerName, EventTime, ServerLogin, ClientHost, LoginType and IsPooled event data. The trigger basically writes these values to the AuditLogin table for a LOGON event. Code:
This is all good and well, now we know who is logging onto our Server and when and from where. But this trigger firing can fill a large hard disk quickly. On a busy server there may be millions of entries into this Audit table daily. This is mostly as a result of connection pooling and how a connection is kept alive by an application server like IIS. So for the most part I don't worry to much about huge amounts of logons from pooled connections from application servers. The logon entries I'm more interested in are for non pooled connections.
So what we need, from an audit summary point of view is a distinct record for each logon from a distinct host and the logons pooled status, and the last time this logon logged in and how many times it logged in. This will give me some valuable information from the millions of records or raw logon data. Information like which logins have not logged in since Logon Auditing has been enabled, which generic logins are being used from which hosts and which non pooled logins are logging in the most. From my original mission of establishing which logins are used and how often, we can now determine this.
Now I need to take the daily logon audit information and summarize it and merge todays summarized information with the historic logon information. This is a very common scenario in a Data Warehouse environment. I NEED to summarize and TRUNCATE my AuditLogin table after I've summarized the data in it because of the huge amount of data that goes into this table every day. Knowing that login x logged on at a particular time yesterday is of little value. So I don't need the detail data, unless there is a security breach. Summarize, Merge to History, Truncate. Treat it mean, keep it clean.
The below code uses some incremental load logic I've gleened from my Data Warehouse experience. Basically the first full load and subsequent incremental loads of my AuditLoginHistory table use the same code. Thats pretty handy. I can run the same code to load the history table from the first load.
Basically each record in the history table is identified by Login, Host and Pooled. The two other fields are LoginCount and LastLoggedIn. So what the below code does is first summarize todays logon information. Then it compares it to the History information. If the same record exists, the LoginCount and LastLoggedIn is updated. If the record doesn't exist, a new one is inserted into the history table.
Summarizing Code:
Below is the code for the two Audit tables:
Lastly, a caveat to make sure the trigger fires and doesn't cause logon failures: Ensure that all the objects that get created are accessible to public. Otherwise logons will fail because the user logging in causes the trigger to fire and the write to the Audit table is done in the context of the user thats logging on.
Well there are two ways to view which logins are logging onto the SQL server and when: The one way is to enable successful and failed logon attempts at the SQL server level. This will write an entry to the SQL Error Log for every failed or successful logon. On a busy server this would make the error log huge. Besides that, the audit information is in a flat text file, which can be viewed, but not summarised easily.
Another way to audit logons to your SQL Server is to enable a server wide Logon Trigger, which as you know is available in SQL 2005 & 2008. See BOL for more details. This trigger exposes some XML Data about the logon and this data is made accessible using the EVENTDATA function.
The solution I've written to audit and summarize logon information uses a logon trigger, two logon Audit tables and a daily summarizing script. I've created these tables in the Master database, but they should ideally be created in a user database. The first table, AuditLogin stores all the data that results from the logon trigger firing. I've chosen to store ServerName, EventTime, ServerLogin, ClientHost, LoginType and IsPooled event data. The trigger basically writes these values to the AuditLogin table for a LOGON event. Code:
/****** Object: DdlTrigger [trigLogon] Script Date: 04/06/2009 10:57:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trigLogon]
ON ALL SERVER
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml
SET @LogonTriggerData = eventdata() ;
INSERT INTO AuditLogin
(ServerName, EventTime, ServerLogin, Col_ClientHost, Col_LoginType, IsPooled)
(SELECT @@SERVERNAME, @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), ORIGINAL_LOGIN(), @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)'), @LogonTriggerData.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit'))
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trigLogon] ON ALL SERVER
This is all good and well, now we know who is logging onto our Server and when and from where. But this trigger firing can fill a large hard disk quickly. On a busy server there may be millions of entries into this Audit table daily. This is mostly as a result of connection pooling and how a connection is kept alive by an application server like IIS. So for the most part I don't worry to much about huge amounts of logons from pooled connections from application servers. The logon entries I'm more interested in are for non pooled connections.
So what we need, from an audit summary point of view is a distinct record for each logon from a distinct host and the logons pooled status, and the last time this logon logged in and how many times it logged in. This will give me some valuable information from the millions of records or raw logon data. Information like which logins have not logged in since Logon Auditing has been enabled, which generic logins are being used from which hosts and which non pooled logins are logging in the most. From my original mission of establishing which logins are used and how often, we can now determine this.
Now I need to take the daily logon audit information and summarize it and merge todays summarized information with the historic logon information. This is a very common scenario in a Data Warehouse environment. I NEED to summarize and TRUNCATE my AuditLogin table after I've summarized the data in it because of the huge amount of data that goes into this table every day. Knowing that login x logged on at a particular time yesterday is of little value. So I don't need the detail data, unless there is a security breach. Summarize, Merge to History, Truncate. Treat it mean, keep it clean.
The below code uses some incremental load logic I've gleened from my Data Warehouse experience. Basically the first full load and subsequent incremental loads of my AuditLoginHistory table use the same code. Thats pretty handy. I can run the same code to load the history table from the first load.
Basically each record in the history table is identified by Login, Host and Pooled. The two other fields are LoginCount and LastLoggedIn. So what the below code does is first summarize todays logon information. Then it compares it to the History information. If the same record exists, the LoginCount and LastLoggedIn is updated. If the record doesn't exist, a new one is inserted into the history table.
Summarizing Code:
with LoginCnt(ServerLogin, LoginCount, IsPooled, Host)
AS
(
select ServerLogin, count(*) AS LoginCount, IsPooled, Col_ClientHost from auditlogin
group by ServerLogin, IsPooled, Col_ClientHost
)
, LastLogin(ServerLogin, Host, LastLoggedIn, IsPooled)
AS
(
select serverlogin, Col_ClientHost, IsPooled, max(EventTime) AS LastLoggedIn from AuditLogin
group by ServerLogin, Col_ClientHost, IsPooled
)
login trigger
--SELECT lc.ServerLogin, lc.Host, ll.LastLoggedIn, lc.LoginCount, lc.IsPooled
--FROM LoginCnt lc INNER JOIN LastLogin ll
--ON lc.ServerLogin = ll.ServerLogin
--AND lc.Host = ll.Host
SELECT lc.ServerLogin, lc.Host, ll.LastLoggedIn, lc.LoginCount, lc.IsPooled
INTO #AuditTmp
FROM LoginCnt lc INNER JOIN LastLogin ll
ON lc.ServerLogin = ll.ServerLogin
AND lc.Host = ll.Host
ORDER BY ISPooled ASC, LoginCount DESC
GO
BEGIN TRAN
DECLARE @SL VARCHAR(128)
DECLARE @Host VARCHAR(30)
DECLARE @LastLoggedIn DATETIME
DECLARE @LC INT
DECLARE @IP BIT
Declare AuditCur CURSOR FOR
SELECT ServerLogin, Host, LastLoggedIn, LoginCount, IsPooled FROM #AuditTmp
OPEN AuditCur
FETCH NEXT FROM AuditCur INTO @SL, @Host, @LastLoggedIn, @LC, @IP
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM AuditLoginHistory
WHERE ServerLogin = @SL AND Host = @Host AND IsPooled = @IP) = 1
BEGIN
UPDATE AuditLoginHistory
SET LastLoggedIn = @LastLoggedIn,
LoginCount = LoginCount + @LC
WHERE ServerLogin = @SL AND Host = @Host AND IsPooled = @IP
END
ELSE
BEGIN
INSERT AuditLoginHistory(ServerLogin, Host, LastLoggedIn, LoginCount, IsPooled)
VALUES(@SL, @Host, @LastLoggedIn, @LC, @IP)
END
FETCH NEXT FROM AuditCur INTO @SL, @Host, @LastLoggedIn, @LC, @IP
END
CLOSE AuditCur
DEALLOCATE AuditCur
DROP TABLE #AuditTmp
TRUNCATE TABLE auditlogin
IF @@ERROR <> 0
ROLLBACK TRAN
ELSE
--PRINT 'Successful'
COMMIT TRAN
Below is the code for the two Audit tables:
USE [master]
GO
/****** Object: Table [dbo].[AuditLogin] Script Date: 01/19/2009 08:20:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditLogin]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AuditLogin](
[AuditEventId] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](100) NOT NULL,
[EventTime] [datetime] NOT NULL,
[ServerLogin] [nvarchar](100) NOT NULL,
[Col_ClientHost] [varchar](50) NULL,
[Col_LoginType] [varchar](50) NULL,
[IsPooled] [bit] NULL,
CONSTRAINT [PK_AuditLogin] PRIMARY KEY CLUSTERED
(
[AuditEventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
GRANT INSERT ON [dbo].[AuditLogin] TO [public]
GO
USE [master]
GO
/****** Object: Table [dbo].[AuditLoginHistory] Script Date: 04/06/2009 10:37:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AuditLoginHistory](
[ServerLogin] [varchar](128) NULL,
[Host] [varchar](30) NULL,
[LoginCount] [int] NULL,
[LastLoggedIn] [datetime] NULL,
[IsPooled] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Lastly, a caveat to make sure the trigger fires and doesn't cause logon failures: Ensure that all the objects that get created are accessible to public. Otherwise logons will fail because the user logging in causes the trigger to fire and the write to the Audit table is done in the context of the user thats logging on.
Thursday, April 2, 2009
Tracking database growth over time
We had a problem recently where our Sharepoint environment space requirements have grown beyond what was originally envisaged. The Sharepoint team wanted to know how the different SQL databases have grown over time.
Dang I thought, we don't record the size of a database at a particular point in time, over time. Then I thought, well we back the databases up frequently and we could look at the backup file sizes to get an indication of how big the database is, since there is a reasonable correlation between data, index and log space used and the backup size. But we only keep 2 weeks worth of backups on disk and to recall tapes for a year would be a mission. Then it dawned on me that the backup history tables in the msdb database would contain the database name, backup time and backup size!
Great, so I have my data source. The Backupset table just so happens to have 200000 records in it, with data for 80 databases. Well we can exclude all db's that aren't Sharepoint databases, and we can only look at type = 'D' for full database backups. But we still have daily full backups for each sharepoint database for the last 18 months. From a trending point of view, without being overwhelmed by masses of data, I want to see database growth by month. Ok so I want the 1st, or last backup for each database for each month for the last 18 months. Now there's a bit of T-SQL to write. I figured a couple of While loops would do the trick to loop through each month and year, and in those loops have a cursor get the backup info into a table variable for each database. Here is the code:
I'm sure there are other ways of doing this. Feel free to comment and add how you would do the above.
As a side, there is a post by Vyas Kondreddi posted in 2001 that does a similar thing, but doesn't summarize the size per database by month. It only returns all data for one database. Also, it reports size as the size of the mdf. If I create a sharepoint database with an initial size of 1GB and it takes 8 months to get to 1GB, then I won't have a true reflection of the actual size of the data, the size of the mdf with lots of empty space is academic. The actual data space used is important from a data growth point of view. Hence I use the backup_size field from the Backupset table.
The cool thing now is I can look at a database going back 18 months and see the size of the full backup at the end of each month. Now just pull this data into reporting services, have a series for each database and there we go! Database growth over time.
Dang I thought, we don't record the size of a database at a particular point in time, over time. Then I thought, well we back the databases up frequently and we could look at the backup file sizes to get an indication of how big the database is, since there is a reasonable correlation between data, index and log space used and the backup size. But we only keep 2 weeks worth of backups on disk and to recall tapes for a year would be a mission. Then it dawned on me that the backup history tables in the msdb database would contain the database name, backup time and backup size!
Great, so I have my data source. The Backupset table just so happens to have 200000 records in it, with data for 80 databases. Well we can exclude all db's that aren't Sharepoint databases, and we can only look at type = 'D' for full database backups. But we still have daily full backups for each sharepoint database for the last 18 months. From a trending point of view, without being overwhelmed by masses of data, I want to see database growth by month. Ok so I want the 1st, or last backup for each database for each month for the last 18 months. Now there's a bit of T-SQL to write. I figured a couple of While loops would do the trick to loop through each month and year, and in those loops have a cursor get the backup info into a table variable for each database. Here is the code:
DECLARE @Db_BackupHistory TABLE
(database_name VARCHAR(255),
backup_size BIGINT,
backup_start_date DATETIME)
DECLARE @Y INT
DECLARE @M INT
SET @Y = 2007
SET @M = 1
WHILE @Y < 2010
BEGIN
WHILE @M < 13
BEGIN
DECLARE @DB VARCHAR(255)
DECLARE CurDB CURSOR FOR
SELECT DISTINCT database_name FROM backupset where database_name like 'Sharepoint_%'
OPEN CurDB
FETCH NEXT FROM CurDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @Db_BackupHistory(database_name, backup_size, backup_start_date)
select TOP 1 database_name, backup_size, backup_start_date from backupset
WHERE database_name LIKE @DB and type = 'D'
and DATEPART(mm, backup_start_date) = @M and DATEPART(yy, backup_start_date) = @Y
order by backup_start_date DESC
FETCH NEXT FROM CurDB INTO @DB
END
CLOSE CurDB
DEALLOCATE CurDB
SET @M = @M + 1
END
SET @M = 1
SET @Y = @Y + 1
END
select database_name, backup_size/1024/1024 AS [BackupSize(MB)], backup_start_date from @Db_BackupHistory
order by database_name, backup_start_date
I'm sure there are other ways of doing this. Feel free to comment and add how you would do the above.
As a side, there is a post by Vyas Kondreddi posted in 2001 that does a similar thing, but doesn't summarize the size per database by month. It only returns all data for one database. Also, it reports size as the size of the mdf. If I create a sharepoint database with an initial size of 1GB and it takes 8 months to get to 1GB, then I won't have a true reflection of the actual size of the data, the size of the mdf with lots of empty space is academic. The actual data space used is important from a data growth point of view. Hence I use the backup_size field from the Backupset table.
The cool thing now is I can look at a database going back 18 months and see the size of the full backup at the end of each month. Now just pull this data into reporting services, have a series for each database and there we go! Database growth over time.
Tuesday, March 31, 2009
Searching for that code that formats a number in all my code files
If you write any type of code you must have come across this scenario:
I've written code before that formats a number nicely in Powershell, but which text file or script or function did I have that code in?
Well today I got a handy piece of Powershell code that did a whole lot of things but one thing in particular was format a number. Now I'd looked for examples on this before and didn't quite find what I was looking for. And this piece of code did exactly what I wanted. But nowhere in the Powershell code did it say "format number". I could add a comment and thats probably not a bad idea, but normally code comments are to increase readability of the code and logic and not as tags for future searches.
Well there is a very simple way to get around this problem. For the .txt or .ps1 or even .sql or .dtsx file right click and go to the properties of the file. Then click on the Summary tab. There is a Keywords text box. In here you can type keywords or tags that will help you find the code snippet or example that you think you'll need in future. In my case I typed in format number. I then searched (F3) the parent folder for all files using the second option in search, which is A word or phrase in the file. A searched for format number and bingo, 1 result from 747 files! And it was the correct file. :-) To add multiple keywords or phrases separate them with semi colons (;).
Thursday, March 26, 2009
SQL Metadata: What to store and where to store it
Being at the Consulting and Contracting end of the IT industry, I've moved around a bit in my career. The same challenge is faced when starting at a new client: What are all these databases used for? Who owns them? How are they accessed? There are a number of questions that simply can't be determined from an ERD or querying a dmv or looking at the underlying objects.
This information is database metadata and it is almost never stored in the database. Or nearly never stored, anywhere. That brings me to my first point: Database metadata should be stored in the database. This ensures that this metadata does not get orphaned or lost or decoupled from what it is describing. It gets backed up. It is easily viewable. It is secure. Ok, so we have decided on a place to store this metadata, but what exactly are we going to store?
I've found that the best answer to the "what metadata are we going to store" question are the resident DBA / sys admin staff. They know the questions that are relevant to their databases that cannot be found in the database. That highlights my next point, don't keep database metadata about what can be derived from existing metadata in system tables or dmv's. Like "Database Creation date". That is stored in sys.databases. Or the biggest tables and their record counts, also not static and viewable in many different ways.
So what are vaild metadata attributes? There are a number of them, like Database Owner, Access methods, growth requirements, backup requirements, etc. I've come up with a list of 14 attributes at my current client and I think thats a good amount. Not so much that its tedious maintaining, but at the same time still useful.
Ok, so we've talked about the reason behind keeping metadata and what we are going to keep and where. Now we'll talk about the how bit. You may be familiar with three system stored procedures that add, update and drop metadata. These are:
sp_addextendedproperty
sp_dropextendedproperty
sp_updateextendedproperty
These are documented in BOL. As each name suggests, these are used to Add, Update and Delete extended property information, or in our case, metadata. Once we've added our metadata we can view it using a function called fn_listextendedproperty.
I add the DB_NAME() metadata function to give the database metadata context:
Great, so now we have a mechanism to store and view our metadata. But I think we need to take a step back. Lets suppose that we decide that "Database Owner" is a good database metadata attribute. Now we go forth and run sp_addextendedproperty 'Database Owner', 'Joe Black'. On our next database we fervently run sp_addextendedproperty 'Db Owner', 'Joe Smith'. Do you spot the problem? If I want an enterprise view of all databases on all servers and their respective owners, I would be in a pickle. Thats because I'm adding the same attribute - Database Owner, but its spelled differently on each database. So we need to enforce Domain integrity. Simply defined this is: A domain defines the possible values of an attribute. I want to make sure that in all my databases on all my servers the Business owner metadata attribute is stored as "Business Owner".
What I do is create a wrapper (DJ Sproc ) to wrap around the sp_addextendedproperty. Code to follow:
What the above code does is essentially enforce Domain Integrity for the metadata attribute names. These are defined in the first step above of "what to store". Now what I need to do is deploy this wrapper sproc to all databases on all my servers so that I am enforcing domain integrity on all database metadata. The reason I need to do this and not just create a wrapper sproc in master or model is that when adding metadata, the current database context is used. There is no way of specifying that metadata x belongs to db y. You have to physically change the database context to the relevant user database. And as we know this is tricky. I've checked and there doesn't seem to be a way of issuing the "USE DB1" statement and then execute another statement that will use DB1 as its context.
So I deploy my wrapper sproc to each database. Great. I have 50 SQL servers with an average of 40 user databases on each!! No problem, enter sp_msforeachdb, xp_cmdshell and osql. My arson of little TSQL nukes. . String them together as follows and we have the powerrrrrr:
What this line of code does is run the undocumented but very powerful sp_msforeachdb system stored procedure. Basically for each database on a server, do x y and z. This I use in my osql command. This comand prompt command can run .sql files from a said location. So I save my wrapper sproc in f drive in the above location. This should ideally be a file share on the network somewhere. So the above line of code runs the usp_AddMetadata wrapper sproc on each user database on Server SQL1. Now if I have 40 SQL servers I take this line of code, paste it into Excel (or I could do it in TSQL) with my server list, do some string concatenation and generate 40 lines of code, one for each server. Boom!!
Clearly the above approach was prior to me learning the power of Powershell. The above approach is valid, but a bit clunky. I'll get to re-writing it in Powershell when I get a few spare CPU cycles.
As a side, because I've added the usp_AddMetadata sproc to the model system database, any new databases will have the wrapper sproc added to it automatically.
In the same way that I've created a wrapper for sp_addextendedproperty, I also create a wrapper for sp_updateextendedproperty, with the same logic and constraints.
The above is all very cool and in some ways geeky. The real power, from a management CIO point of view for example, is when I create a view or SSRS report that shows a list of all databases on all servers and who the database owner is. Or all databases that are past their sell by date. Or all databases that have grown to beyond double their initial size. Now that is seriously powerful. I have an enterprise 10000 foot (feet) view of my SQL environment.
Besides the CIO view, from an audit or capacity planning or provisioning point of view, this metadata makes DBA's and sys admins jobs a whole lot easier.
This information is database metadata and it is almost never stored in the database. Or nearly never stored, anywhere. That brings me to my first point: Database metadata should be stored in the database. This ensures that this metadata does not get orphaned or lost or decoupled from what it is describing. It gets backed up. It is easily viewable. It is secure. Ok, so we have decided on a place to store this metadata, but what exactly are we going to store?
I've found that the best answer to the "what metadata are we going to store" question are the resident DBA / sys admin staff. They know the questions that are relevant to their databases that cannot be found in the database. That highlights my next point, don't keep database metadata about what can be derived from existing metadata in system tables or dmv's. Like "Database Creation date". That is stored in sys.databases. Or the biggest tables and their record counts, also not static and viewable in many different ways.
So what are vaild metadata attributes? There are a number of them, like Database Owner, Access methods, growth requirements, backup requirements, etc. I've come up with a list of 14 attributes at my current client and I think thats a good amount. Not so much that its tedious maintaining, but at the same time still useful.
Ok, so we've talked about the reason behind keeping metadata and what we are going to keep and where. Now we'll talk about the how bit. You may be familiar with three system stored procedures that add, update and drop metadata. These are:
sp_addextendedproperty
sp_dropextendedproperty
sp_updateextendedproperty
These are documented in BOL. As each name suggests, these are used to Add, Update and Delete extended property information, or in our case, metadata. Once we've added our metadata we can view it using a function called fn_listextendedproperty.
I add the DB_NAME() metadata function to give the database metadata context:
SELECT DB_NAME() AS DbName, name as PropertyName, value as PropertyValue
FROM ::fn_listextendedproperty(NULL, NULL, NULL, NULL, NULL, NULL, NULL)
Great, so now we have a mechanism to store and view our metadata. But I think we need to take a step back. Lets suppose that we decide that "Database Owner" is a good database metadata attribute. Now we go forth and run sp_addextendedproperty 'Database Owner', 'Joe Black'. On our next database we fervently run sp_addextendedproperty 'Db Owner', 'Joe Smith'. Do you spot the problem? If I want an enterprise view of all databases on all servers and their respective owners, I would be in a pickle. Thats because I'm adding the same attribute - Database Owner, but its spelled differently on each database. So we need to enforce Domain integrity. Simply defined this is: A domain defines the possible values of an attribute. I want to make sure that in all my databases on all my servers the Business owner metadata attribute is stored as "Business Owner".
What I do is create a wrapper (DJ Sproc ) to wrap around the sp_addextendedproperty. Code to follow:
CREATE PROC usp_AddMetadata
@MetadataName VARCHAR(100), @MetadataValue VARCHAR(500)
AS
BEGIN
IF @MetadataName NOT IN ('AppDesc','AppType','BackupReq','Contacts','GrowthReq','Lifespan',
'Owner','Type','InitSize','DevDBName','ProdDevQA','SSISPack','BusOwner','ConnectionType')
BEGIN
RAISERROR('Metadataname is not valid. Must be in the following list: AppDesc, AppType, BackupReq, Contacts, rowthReq, Lifespan, Owner, Type, InitSize, DevDBName, ProdDevQA, SSISPack, BusOwner, ConnectionType', 16, 1)
RETURN
END
ELSE
BEGIN
EXEC sp_addextendedproperty @name = @MetadataName, @value = @MetadataValue
END
END
What the above code does is essentially enforce Domain Integrity for the metadata attribute names. These are defined in the first step above of "what to store". Now what I need to do is deploy this wrapper sproc to all databases on all my servers so that I am enforcing domain integrity on all database metadata. The reason I need to do this and not just create a wrapper sproc in master or model is that when adding metadata, the current database context is used. There is no way of specifying that metadata x belongs to db y. You have to physically change the database context to the relevant user database. And as we know this is tricky. I've checked and there doesn't seem to be a way of issuing the "USE DB1" statement and then execute another statement that will use DB1 as its context.
So I deploy my wrapper sproc to each database. Great. I have 50 SQL servers with an average of 40 user databases on each!! No problem, enter sp_msforeachdb, xp_cmdshell and osql. My arson of little TSQL nukes. . String them together as follows and we have the powerrrrrr:
sp_msforeachdb 'xp_cmdshell ''osql -SSQL1 -E -d? -i"f:\work\sql\2005\usp_AddMetadata.sql"'''
What this line of code does is run the undocumented but very powerful sp_msforeachdb system stored procedure. Basically for each database on a server, do x y and z. This I use in my osql command. This comand prompt command can run .sql files from a said location. So I save my wrapper sproc in f drive in the above location. This should ideally be a file share on the network somewhere. So the above line of code runs the usp_AddMetadata wrapper sproc on each user database on Server SQL1. Now if I have 40 SQL servers I take this line of code, paste it into Excel (or I could do it in TSQL) with my server list, do some string concatenation and generate 40 lines of code, one for each server. Boom!!
Clearly the above approach was prior to me learning the power of Powershell. The above approach is valid, but a bit clunky. I'll get to re-writing it in Powershell when I get a few spare CPU cycles.
As a side, because I've added the usp_AddMetadata sproc to the model system database, any new databases will have the wrapper sproc added to it automatically.
In the same way that I've created a wrapper for sp_addextendedproperty, I also create a wrapper for sp_updateextendedproperty, with the same logic and constraints.
The above is all very cool and in some ways geeky. The real power, from a management CIO point of view for example, is when I create a view or SSRS report that shows a list of all databases on all servers and who the database owner is. Or all databases that are past their sell by date. Or all databases that have grown to beyond double their initial size. Now that is seriously powerful. I have an enterprise 10000 foot (feet) view of my SQL environment.
Besides the CIO view, from an audit or capacity planning or provisioning point of view, this metadata makes DBA's and sys admins jobs a whole lot easier.
Subscribe to:
Posts (Atom)