TSQL to report DB Mirroring status and configuration

Have you been faced with the need to report on, or verify the configuration of your SQL Server Database Mirroring. Well maybe you have or maybe you haven't, if you only have a small number of databases with DB Mirroring then this may not be very useful, but for me with 50-100 databases with DB Mirroring I needed a way to quickly check what the status and safety level of the mirrored databases were. Not to mention the fact that I needed to easily identify the databases that were no mirrored to report back to the application owners so they knew what was not in a DR configuration.

Hopeful this query can save you some time or at least give you a starting point if facing a similar situation:

SELECT a.name
, ISNULL(b.mirroring_role_desc, 'NOT MIRRORED') as mirroring_role_desc
, ISNULL(b.mirroring_state_desc, '') as mirroring_state_desc
, ISNULL(mirroring_safety_level_desc, '') as mirroring_safety_level_desc
FROM sys.databases a
INNER JOIN sys.database_mirroring b on b.database_id = a.database_id
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
ORDER BY a.name

Export the SSRS Report Server's folder / item structure using TSQL

If your faced with the task of migrating your Reports from SSRS 2000/2005 to SSRS 2008 as I am then you're probably looking for a way to export your folder structure to help with the planning. You might be even using the migration as a way to clean up the security and how some of the items are stored (e.g. Data Sources). All of this is not easy without a way to export the folder/item structure from your existing environment. The following TSQL should help you gain an overview of your structure and allow you to start planning the migration.

Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name

In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:

SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName

When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration