Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: SQL

Many software systems use multiple databases to support an application. If the system requires all databases to be available on the same server, then issues can arise in a mirrored setup. Problems occur when one or more databases fail over unexpectedly, but others remain as Principal. In this scenario, the required response is for any remaining principals to also failover to the secondary server. We can achieve this response by proactively monitoring the mirroring status of all databases.

To setup the mirroring status check, setup a WMI Alert that queries DATABASE_MIRRORING_STATE_CHANGE.

Add a new alert and select WMI Alert from the Type box. The namespace should automatically be populated with the correct setting. The following example should be pasted into the Query box. It checks the status of three mirrored databases. Change the database names accordingly:-

SELECT *
FROM DATABASE_MIRRORING_STATE_CHANGE
WHERE ((State = 7 or State = 8) AND DatabaseName = 'Database1') OR ((State = 7 or State = 8) AND DatabaseName = 'Database2')
OR ((State = 7 or State = 8) AND DatabaseName = 'Database3')

State 7 indicates a Manual Failover whilst state 8 indicates an Automatic Failover. If any database now fails over then the alert should fire.

Obviously we need to setup an appropriate response for the alert. Create an Agent job containing the following SQL task:-

if exists (select database_id
from sys.database_mirroring
where db_name(database_id) = 'Database1'
and mirroring_role_desc = 'principal')
ALTER DATABASE Database1 SET PARTNER FAILOVER
GO

if exists (select database_id
from sys.database_mirroring
where db_name(database_id) = 'Database2'
and mirroring_role_desc = 'principal')
ALTER DATABASE Database2 SET PARTNER FAILOVER
GO

if exists (select database_id
from sys.database_mirroring
where db_name(database_id) = 'Database3'
and mirroring_role_desc = 'principal')
ALTER DATABASE Database3 SET PARTNER FAILOVER
GO

The above code checks to see whether any mirrored databases are still set as principal and automatically fails them over. Open up the Alert in SQL Agent and select this newly created job to execute if the alert is fired. Now, if one or more of the databases fails over, the remaining databases will also do so.

Tags: , , , , , ,

SQL Server offers an OUTPUT command to capture data that is changed, either through updating, deletion or insertion. The ‘before’ and ‘after’ images of the data are stored in system tables named DELETED and INSERTED. It is possible to query the applicable table to retrieve data that has been deleted or inserted by your sql statement. In the case of updates, the old data can be found in DELETED and the newly written data in INSERTED.

The following example demonstrates the process. An Audit table is created to store the results. An UPDATE is then issued against a number of columns. OUTPUT writes the old and new versions of the data (along with the id of the row changed and the date and time) to the audit table. Adding the output clause to stored procedures will allow you to audit all data changes in your database, although there will obviously be some overhead due to additional writes.

CREATE TABLE Audit_Table (CustomerID varchar(12),
                         OldAddress1 varchar(50),
                         OldAddress2 varchar(50),
                         OldAddress3 varchar(50),
                         OldAddress4 varchar(50),
                         OldAddress5 varchar(50),
                         OldPostCode varchar(12),
                         NewAddress1 varchar(50),
                         NewAddress2 varchar(50),
                         NewAddress3 varchar(50),
                         NewAddress4 varchar(50),
                         NewAddress5 varchar(50),
                         NewPostCode varchar(12),
                         DateChanged datetime);

UPDATE CustomerTable
SET Address1 = '99 My Road',
      Address2 = 'My Area',
      Address3 = 'My City',
      Address4 = 'My County',
      Address5 = 'My Country',
      PostCode = 'AA1 1AA'
OUTPUT INSERTED.CustomerID,
	    DELETED.Address1 AS 'OldAddress1',
	    INSERTED.Address1 AS 'NewAddress1',
	    DELETED.Address2 AS 'OldAddress2',
	    INSERTED.Address2 AS 'NewAddress2',
	    DELETED.Address3 AS 'OldAddress3',
	    INSERTED.Address3 AS 'NewAddress3',
	    DELETED.Address4 AS 'OldAddress4',
	    INSERTED.Address4 AS 'NewAddress4',
	    DELETED.Address5 AS 'OldAddress5',
	    INSERTED.Address5 AS 'NewAddress5',
	    DELETED.stu_capc AS 'OldPostCode',
	    INSERTED.stu_capc AS 'NewPostCode',
	    GETDATE() AS 'DateChanged'
INTO AuditTable
WHERE CustomerTable.CustomerID = 'AB123456';

Tags: , , , , ,

The T-SQL script at the bottom of the page produces a simple data dictionary for a SQL Server Database, along with descriptions of its objects. It produces HTML output that can be uploaded to a web server, or you could copy and paste into Word. We all hate creating documentation, don’t we?! The example includes tables, columns and stored procedures, but it would be fairly simple to tweak the query to include additional objects such as views, users, primary and foreign keys etc.

Before running the query, it is highly recommended to add descriptions to the tables, columns and procedures. You can do this within SQL Server Management Studio (right click the object, select Properties and then the Extended Properties tab), but I find it easier to use the built-in sys.sp_addextendedproperty procedure.

Adding a Description to a Table:-

EXEC sys.sp_addextendedproperty 
@name=N'Description', 
@value=N'Add Your Description Here' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'Your Table Name Here'
GO

Adding a Description to a Column:-

EXEC sys.sp_addextendedproperty 
@name=N'Description', 
@value=N'Add Your Description Here' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'Your Table Name Here', 
@level2type=N'COLUMN',
@level2name=N'Your Column Name Here'
GO

Now we can generate the documentation….

--Ensure no additional lines are printed to the output
SET NOCOUNT ON

--Declare Variables
DECLARE @dbase varchar(50)
DECLARE @full_table_name varchar(100)
DECLARE @table_name varchar(100)
DECLARE @table_desc varchar(500)
DECLARE @table_date varchar(500)

-- Declare Variables for HTML Output
DECLARE @column_html varchar(max)
DECLARE @index_html varchar(max)
DECLARE @proc_html varchar(max)

--Set database name
SET @dbase = (SELECT DB_NAME() As 'Database Name')

-- Start HTML Generation
PRINT '<HTML><body>'
PRINT '<h1>' + @dbase + '</h1>'

-- Setup Table cursor
DECLARE cr_tables CURSOR READ_ONLY FOR 

select distinct t.name
from sys.TABLES t
	left join sys.extended_properties x
		on t.object_id = x.major_id
where t.name <> 'sysdiagrams' 
order by t.name asc

-- Open Cursor
OPEN cr_tables
FETCH NEXT FROM cr_tables
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN

-- Select extended table properties
select @full_table_name = (s.name + '.' + t.name), @table_desc = convert(varchar(1000), x.value), @table_date =  modify_date
from sys.TABLES t
	left join sys.extended_properties x
		on t.object_id = x.major_id
	left join sys.schemas s
		on t.schema_id = s.schema_id
where t.name = @table_name
order by t.name asc

-- Print Table Properties
PRINT '<h2>' + @full_table_name + '</h2>'
PRINT '<b>Description:-</b> ' + @table_desc + '<br>'
PRINT '<b>Last Modified:-</b> ' + @table_date

-- Print HTML Table Columns
select @column_html = '<h3>Columns</h3><table border = "1" cellpadding = "5" cellspacing = "0">
 <tr><td><b>Column</b></td><td><b>Description</b></td><td><b>Column Ordinal Position</b></td>
 <td><b>Data Type</b></td>
 <td><b>Max Length</b></td>
 <td><b>Precision</b></td>
<td><b>Nulls Allowed</b></td>
 <td><b>Identity</b></td></tr>'

-- Get SQL Columns
select @column_html = @column_html + '<tr><td>' + convert(varchar(100), c.name)  +'</td><td>' + ISNULL(convert(varchar(200), x.value), 'No description available') + '</td><td>' + convert(varchar(3), column_id) + '</td><td>' + ty.name + '</td><td>' + convert(varchar(4), c.max_length) + '</td><td>' + convert(varchar(4), c.precision) + '</td><td>' +
	convert(varchar(3), CASE c.is_nullable 
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END) + '</td><td>' +
	convert(varchar(3),CASE c.is_identity 
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END) + '</td></tr>'
from sys.columns c
	left join sys.extended_properties x
		on c.object_id = x.major_id
			and c.column_id = x.minor_id
	left join sys.tables t
		on c.object_id = t.object_id
	left join sys.types ty
		on c.system_type_id = ty.user_type_id 
	where type_desc = 'USER_TABLE'
		and t.name = @table_name
order by t.name, c.column_id asc

PRINT @column_html + '</table>'

-- Get Index Info
select @index_html = '<h3>Indexes</h3><table border = "1" cellpadding = "5" cellspacing = "0">
 <tr><td><b>Index</b></td><td><b>Index Type</b></td><td><b>Is Primary Key</b></td>
 <td><b>Fill Factor</b></td></tr>'

select @index_html = @index_html + '<tr><td>' +  i.name + '</td><td>' + i.type_desc 
+ '</td><td>' +
	convert(varchar(3), (CASE i.is_primary_key 
		WHEN 1 THEN 'Yes'
		ELSE 'No'
	END))
	+ '</td><td>' +
	convert(varchar(3), fill_factor) + '</td></tr>'
from sys.tables t
	right join sys.indexes i
		on t.object_id = i.object_id
where i.name is not null
	and t.name is not null
		and t.name = @table_name
order by t.name asc, Is_Primary_Key desc, i.name asc

PRINT @index_html + '</table><br>'

FETCH NEXT FROM cr_tables
INTO @table_name

END

-- Generate HTML for Stored Procedures
select @proc_html = '<br><h2>Stored Procedures</h2><table border = "1" cellpadding = "5" cellspacing = "0">
 <tr> <td><b>Stored Procedure</b></td><td><b>Description</b></td><td><b>Last Modified Date</b></td></tr>'

select @proc_html = @proc_html + '<tr><td>' + p.name + '</td><td>' + convert(varchar(100), x.value) + '</td><td>' + convert(varchar(50), p.modify_date) + '</td></tr>'
from sys.procedures p
	left join sys.extended_properties x
		on p.object_id = x.major_id
where p.is_ms_shipped = 0
	and p.name not like 'sp_%'

PRINT @proc_html
PRINT '</table>'

PRINT '</HTML></body>'

close cr_tables
deallocate cr_tables
SET NOCOUNT OFF

Tags: , , , , ,

The HAVING clause is used in conjunction with a GROUP BY so that aggregate functions can be used in a comparison. Think of it as a WHERE clause for groupings.

The following example should help to explain further.

The following example will fail because the WHERE clause is used to evaluate data on a row by row basis. The query is an attempt to limit the results returned to those customers with an order count greater than five.

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
WHERE COUNT(customer_id) > 5
GROUP BY customer_id

You will receive an aggregate related error, as the WHERE clause can only operate on a single row. To achieve the desired result, we remove the WHERE clause and add a HAVING clause after the GROUP By. Running the following query will now work:-

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
GROUP BY customer_id
HAVING COUNT(customer_id) > 5

Tags: , , , , ,

UK Postcodes can be difficult to handle in SQL, especially when trying to group results based on area code. Postcodes in the UK come in a variety of formats, the most common being AA9 9AA, A9 9AA, A99 9AA and AA99 9AA. Problems arise due to the variation in the total number of characters in a code, the different combinations of numbers and letters, and also the location of the space.

If you need to write a query to select the characters up to but excluding the space (e.g. AA9, A9, A99 or AA99), then the following approach can be used. It uses the LEFT command in conjunction with CHARINDEX to select the left side of the full code.

SELECT CompanyName, LEFT(Postcode, CHARINDEX(' ', Postcode) -1) AS 'Postcode'
FROM CompanyAddresses

However, if you need to select only the leading letters and not numbers (A or AA), then you can use a CASE statement:-

SELECT CompanyName, CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode'
FROM CompanyAddresses

Using either of the above two methods, you can successfully group results into Postcode areas. Use either method to group and select on Postcode.

SELECT CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END AS 'Postcode', COUNT(CompanyName) AS 'Companies in Postal Area'
FROM CompanyAddresses
GROUP BY CASE
WHEN ISNUMERIC(RIGHT(LEFT(Postcode, 2), 1)) = '0' THEN LEFT(Postcode, 2)
ELSE LEFT(Postcode, 1)
END

Tags: , , , , , , , ,

Simple information about indexes can be found by running

sp_helpindex 'HumanResources.Employee'

where ‘HumanResources.Employee’ is the name of a table. index_name, index_description and index_keys are displayed in the results window:-

sp_helpindex

Tags: , , ,

I am often called upon to restore a copy of a live database to a development server. Taking a one-off backup in the normal manner would cause problems, as the sequence of LSNs (Log Sequence Numbers) would be disrupted on the production server. This means that subsequent log or differential backups would follow the one-off backup, rather than the file produced from the regular scheduled backup plan. Thankfully there is a solution….

Issuing a backup with the COPY_ONLY option stops the log sequence chain from being broken. Use the option as follows:-

BACKUP DATABASE [LiveDB]
TO DISK = 'E:\Backups\TestRestoreFile.bak'
WITH INIT,  NAME = 'Full Database Backup', COPY_ONLY,  STATS = 10

Unfortunately, it is not possible to select a COPY_ONLY backup through the Management Studio GUI. The backup command has to be issued through T-SQL. It is also not possible to restore through the interface. As you can see from the following screenshot, you can select the backup file, but the backup set will not appear in the list.

RestoreTestDB
To perform the restore, issue the following command, specifying the target location(s) for data and log files. Include REPLACE to overwrite any existing test database. I would also recommend you rename the files so that they now match the name of the test database, rather than the live database.

RESTORE DATABASE [TestDB]
FROM  DISK = 'E:\Backups\TestRestoreFile.bak'
WITH  FILE = 1,
MOVE 'LiveDB_data' TO 'E:\Data\TestDB.mdf',
MOVE 'LiveDB_log' TO 'E:\Logs\TestDB_log.ldf',
NOUNLOAD,  REPLACE,  STATS = 1
GO

If you now issue log backups on the production server, the LSNs will be in the correct sequence and the COPY_ONLY backup will effectively have been ignored.

Tags: , , , , , , ,

%d bloggers like this: