Wednesday, July 29, 2015

Query SQL Server Mirror details -- sys.database_mirroring

Just one more quick post today;  As I mentioned in my last post, I am preparing to perform a rolling upgrade for one of my customer's high performance mirror sessions. Well, as I'm covering all of the bases, I wanted to check sys.database_mirroring, just to be sure the mirror details are as I expect them to be.

Here is a quick query that you can use for exactly that. It will tell you which of your databases are mirrored; and it will provide the mirror state and safety, along with the mirroring role and mirroring partner.  Take a look, let me know what you think.

   SELECT
      DB_NAME(database_id) [Database],
      CASE WHEN mirroring_guid IS NOT NULL THEN 'Mirroring is On' 
        ELSE 'Mirroring is not configured' END [IsMirrored],
      mirroring_state_desc [MirrorState],
      CASE WHEN mirroring_safety_level = 1 THEN 'High Performance' 
           WHEN mirroring_safety_level=2 THEN 'High Safety'  
              ELSE NULL END [MirrorSafety],
      mirroring_role_desc [MirrorRole],
      mirroring_partner_instance [MirrorPartner]
   FROM 
      sys.database_mirroring

These are the results from my customer's server, where that black smudge is the name of their Mirror SQL Server:
















Pretty handy, I'd say. Take a look at this for more details on sys.database_mirroring:
     https://msdn.microsoft.com/en-us/library/ms178655(v=sql.110).aspx

Query to list all SQL Server instances installed on a server

I am preparing an upgrade for one of my customers. It's a rolling upgrade of a v2008 mirror session, to v2012. This particular customer has a tendency of installing new instances without telling me, so I wanted to quickly check all instances on the server that I would be upgrading.

I know it's in the registry, at this location:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names

But I'm always trying to query details like this from the engine... I am sure there are things that I cannot query from the system catalogs, but this isn't one of them. :-)  Check it out, this is MUCH quicker than regedit!

        DECLARE @instances TABLE ( 
             value CHAR(100),
              instances CHAR(100),
             data CHAR(100)
)

INSERT @instances (value,instances,data)
EXECUTE xp_regread
            @rootkey = 'HKEY_LOCAL_MACHINE',
            @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
            @value_name = 'InstalledInstances'

/* look at your data */
SELECT
instances [InstanceNames]
FROM
@instances 

These are the instance names from my own server:











Pretty cool.