Database Mirroring – Login failure after role switch

After configuring database mirroring and testing a failover, you may be unable to log into the new principal database. This occurs because the database user is not associated with a SQL server login (mismatched SIDs). Running sp_change_users_login ‘auto_fix’, ‘userName’ will correct this, but we need to avoid the situation altogether so a seamless failover is possible.

During the mirroring setup, we can run the following query on the principal to generate the SQL necessary to correctly create the logins on the mirror server.

SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name != 'sa'

Generating SQL from Visio (non-Enterprise Architect versions)

Creating an ERD without the ability to generate SQL from it is of questionable value.  If you’re not using a CASE tool such as ERwin (and why not?), you may need to generate a DDL from your Visio layout.  If typing out SQL statements proves too laborious, here is a great workaround to generate workable SQL from your Visio diagram.

A company called Orthogonal Software has a Visio plug-in called Orthogonal Toolbox that will allow you to export the data from the Visio document to an XML File.  Additionally, Orthogonal has provided a couple XSLT’s that you can apply (look for XSLT on that page). One of which has been altered to export SQL.

Once you have the plug-in installed and the XSLT’s downloaded, it’s pretty simple.  Use the plug-in to export to XML, and apply the stylesheet when exporting.  Presto- DDL statements for your structures.

 

Row many columns and rows are in my tables…and which tables are largest?

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable ‘sp_spaceused ”?”’
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC
DROP TABLE #temp