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.  

SQL Server v2012 - Running Totals by Date - SUM() OVER()

In a previous post, I provided an example for calculating running totals on orders traded, using a correlated subquery and the OVER() clause, which was introduced in v2005. You'll see that here: http://www.sqlfingers.com/2014/10/calculate-running-totals-in-sql-server.html

In this post I am going to show you a much sexier version of the same, using the SUM() OVER() Windows functions, introduced in v2012. Remarkably simple, take a look:

     /* to  load our data */

     DECLARE @Orders TABLE(OrderID INT,OrderDate DATETIME,OrderAmount INT)

     INSERT @Orders VALUES

          (1,'06/15/2015 09:30',20.00),

           (2,'06/15/2015 09:35',22.125),

          (3,'06/15/2015 10:01',50.00),

          (4,'06/15/2015 12:32',-10.00),

          (5,'06/15/2015 13:55',12.75),

          (6,'06/16/2015 09:30',20.00),

          (7,'06/16/2015 09:00',22.25),

          (8,'06/16/2015 10:17',-5.00),

          (9,'06/16/2015 11:44',100.23),

           (10,'06/16/2015 12:36',77.00),

          (11,'06/16/2015 13:22',20.00),

          (12,'06/16/2015 14:58',20.25);

     /* single query */

     SELECT 

          OrderDate,

          OrderAmount,

          RunningTotal = SUM(OrderAmount) OVER (

       PARTITION BY DAY(OrderDate) 

       ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

       ) 

     FROM 

         @Orders

    ORDER BY 

         OrderID;

Here is our result-set:

Two things to note;  First, if you look at the RunningTotal from 6/15 to 6/16, you will see the total ends at the end of one day, and begins anew the next. I did this by partitioning by DAY(OrderDate). If you want to calculate the running total from the beginning to the end of your orders, across all of your trade dates, then you would use this statement:

      SUM(OrderAmount) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING)

These are the results, where you'll see the Running Total is not partitioned by date:

Secondly, a key factor of these new Windows functions is 'framing', which lets us further define our collection using ROWS or RANGE. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which just means to calculate all previous rows up to your current row of data. Similarly, you can use ROWS in your frame, as I have in my first query. The results are the same, but the optimizer actually creates the work table in memory... and improves the performance!!

Not that big of a deal with a 12 record sample, but you can still see it. Run this query in comparison to the top one. SET STATISTICS IO on both, and you will see what I'm saying. Exactly the same results, but better performance using ROWS rather than RANGE.

     SELECT 

          OrderDate,

      OrderAmount,

          RunningTotal = SUM(OrderAmount) OVER (

PARTITION BY DAY(OrderDate) 

ORDER BY OrderDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

)  

     FROM 

          @Orders

     ORDER BY 

          OrderDate;

STATISTICS IO for ROWS:

(12 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#B7361CD1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

STATISTICS IO for RANGE:

(12 row(s) affected)

Table 'Worktable'. Scan count 14, logical reads 73, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#B54DD45F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query Open/High/Low/Close Tick Price data from SQL Server

I got a call from one of my prop trader customers today;  They wanted to pull back the tick prices in 5 and 10 minute bars, showing the OHLC in each interval. That means returning the Open, High, Low and Close prices for each 'bar'. Time is the only consideration for this chart; volume and trading activity have no bearing. In periods of high market activity, there would be more bars than in periods of low market activity. This type of collection allows traders to analyze price movement and market volatility.  

This is an example of the logic I built for them, trimmed down only for AAPL. I have coded it for any interval they choose;  5 and 10 minute intervals, or 1, 2 or 3 minutes, etc. See here:

     -- create and load @tickdata

    DECLARE @TickData AS Table ( 

TimeStamp DATETIME,

Price MONEY,

Symbol VARCHAR(5)

    )

    INSERT @TickData VALUES 

('2015-06-09 09:15:01',22.11,'AAPL'),

('2015-06-09 09:15:02',22.10,'AAPL'),

('2015-06-09 09:15:04',22.25,'AAPL'),

('2015-06-09 09:15:58',22.45,'AAPL'), 

('2015-06-09 09:16:03',23.12,'AAPL'), 

('2015-06-09 09:16:08',23.15,'AAPL'), 

('2015-06-09 09:17:10',22.28,'AAPL'), 

('2015-06-09 09:17:30',22.13,'AAPL'), 

('2015-06-09 09:18:59',22.15,'AAPL'),

('2015-06-09 09:18:02',20.19,'AAPL'), 

('2015-06-09 09:18:04',22.25,'AAPL'), 

('2015-06-09 09:20:58',24.14,'AAPL'), 

('2015-06-09 09:20:03',22.22,'AAPL'), 

('2015-06-09 09:21:08',22.28,'AAPL'), 

('2015-06-09 09:24:10',22.29,'AAPL'), 

('2015-06-09 09:25:30',22.13,'AAPL'), 

('2015-06-09 09:27:37',22.10,'AAPL'), 

('2015-06-09 09:27:40',23.25,'AAPL'), 

('2015-06-09 09:28:43',22.45,'AAPL'), 

('2015-06-09 09:28:50',22.22,'AAPL'), 

('2015-06-09 09:28:52',23.25,'AAPL'), 

('2015-06-09 09:29:53',22.28,'AAPL'), 

('2015-06-09 09:30:54',20.13,'AAPL'), 

('2015-06-09 09:30:56',21.11,'AAPL'), 

('2015-06-09 09:30:59',22.25,'AAPL'), 

('2015-06-09 09:31:02',22.45,'AAPL'), 

('2015-06-09 09:31:03',22.22,'AAPL'), 

('2015-06-09 09:32:07',22.25,'AAPL'), 

('2015-06-09 09:32:11',22.28,'AAPL'), 

('2015-06-09 09:34:12',22.13,'AAPL'), 

('2015-06-09 09:35:15',22.10,'AAPL'),

('2015-06-09 09:35:16',22.25,'AAPL'), 

('2015-06-09 09:36:18',22.45,'AAPL'), 

('2015-06-09 09:37:20',22.22,'AAPL'), 

('2015-06-09 09:37:25',22.25,'AAPL'), 

('2015-06-09 09:40:10',22.28,'AAPL'), 

('2015-06-09 09:40:11',22.13,'AAPL'); 

     -- pull it back out at intervals for the given @TimeInterval

     DECLARE @TimeInterval AS INT

     SET @TimeInterval= 10 -- minutes, 1 = 1 min, 5 = 5 min, etc.

     SELECT

Symbol,

DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102))) [Time],

  (SELECT PRICE FROM @TickData WHERE TimeStamp = MIN(t.TimeStamp)) [Open],

MAX(Price) [High],

MIN(Price) [Low],

(SELECT PRICE FROM @TickData WHERE TimeStamp = MAX(t.TimeStamp)) [Close]

     FROM 

@TickData t

     GROUP BY 

Symbol, 

DATEADD(mi,DATEPART(hh,TimeStamp)*60+    
           (DATEPART(mi,TimeStamp)/@TimeInterval) * @TimeInterval, CONVERT(Datetime,             CONVERT(varchar,TimeStamp,102)));

I've run it for 2, 5 and 10 minute bars, and posted the output below for each. Take a look at that 2 minute bar; there are two spots with a larger than 2 minute jump. This is because it's coded to bring data back only for the bars where price data exists. Try it out on your own data, let me know what you think.

   2 minute bars -  

  

  5 minute bars -   

10 minute bars -  

SQL Server Login Properties

Just a quick post that you can use to check your SQL Server login properties.  Very helpful to keep an eye on the password policies and login state.  It's also something that you can use to see how many failed logins are occurring, and when. 

/* login properties */
SELECT
name [SQLLogin],
CASE WHEN is_policy_checked = 0 THEN 'Disabled'
                WHEN is_policy_checked = 1 THEN 'Enabled' END [EnforcePasswordPolicy],
CASE WHEN is_expiration_checked = 0 THEN 'Disabled' 
              WHEN is_expiration_checked = 1 THEN 'Enabled' END [EnforcePasswordExpiration],
CASE LOGINPROPERTY(name, 'IsLocked')
              WHEN 0 THEN 'No'
              WHEN 1 THEN 'Yes' ELSE 'Unknown' END [IsLocked],
LOGINPROPERTY(name, 'PasswordLastSetTime') [DatePasswordSet],
LOGINPROPERTY(name, 'BadPasswordCount') [FailedLogins],
LOGINPROPERTY(name, 'BadPasswordTime') [LastFailedLogin],
LOGINPROPERTY(name, 'LockoutTime') [DateLockedOut],
CASE LOGINPROPERTY(name, 'IsExpired')
             WHEN 0 THEN 'Password is not expired'
             WHEN 1 THEN 'Password is not expired, change it' ELSE 'Unknown' END [PwdExpired],
CASE LOGINPROPERTY(name, 'IsMustChange')
WHEN 0 THEN 'Must not change password at next login'
WHEN 1 THEN 'Must change password at next login' ELSE 'Unknown' END [PwdChangeNextLogin],
LOGINPROPERTY(name, 'DaysUntilExpiration') [DaysUntilPwdExpires]
FROM 
sys.sql_logins
WHERE 
[name] NOT LIKE '##%'
ORDER BY 
[name]


Take a look at this reference for more detailed LOGINPROPERTY information from MSFT:   
     https://msdn.microsoft.com/en-us/library/ms345412(v=sql.110).aspx
           

Find last modified date for SQL Server database objects

Change control is for the database objects just as much as it is for the application layer. You'd like to think that everybody has a change control process in place, but that is not always the case. There are several third party tools, such as Apex, Red Gate and Quest Software, or there is even the SQL Server Audit tool , which happens to be one of my favorites. The Audit, however, is a much larger deployment, and requires quite a bit more due diligence to put together. When I have a chance, I will put a nice overview on the Audit for your review. Until then, this is just a quick script that you can use to see when an object was changed.   

        /* find last modified date per object */

        SELECT
        name [ObjectName],
        CASE WHEN type = 'U' THEN 'Table'
           WHEN type = 'P' THEN 'Procedure'
           WHEN type IN('AF','FN','FS','FT','IF','TF') THEN 'Function'
           WHEN type = 'V' THEN 'View'
           WHEN type = 'C' THEN 'CheckConstraint'
           WHEN type = 'D' THEN 'DefaultConstraint'
           WHEN type = 'PK' THEN 'PrimaryKeyConstraint'
           WHEN type = 'F' THEN 'ForeignKeyConstraint'
           WHEN type = 'UQ' THEN 'UniqueConstraint'
           WHEN type = 'SN' THEN 'Synonym'
           WHEN type = 'TR' THEN 'Trigger' END [ObjectType],

        modify_date [Modified]

        FROM
        sys.objects
        WHERE 
        is_ms_shipped = 0  -- exclude system objects
        ORDER BY
       [Modified] DESC

I ran it against AdventureWorks2012, this is just a snapshot of the results:

Rename SQL Server Database, Table, Column, Index or Constraint

I can't count the number of times over the years that I have referenced BOL, or done a quick google, checking the syntax for my renames.  Not just constraints or indexes, but tables and columns, too.  Even databases!  In this post I am going to to give you several useful examples, compatible with all versions from v2000 to v2014. 

Side note;  In most cases you can perform these renames in the GUI, but I am a huge advocate of NOT doing renames through the GUI. The performance goes to the tubes very quickly, and you won't have near enough control over the system when these manipulations are occurring.  Don't use the GUI.  Learn and master the tSQL. You won't regret it.

    #1  Rename Database

    v2000 

    EXEC sp_renamedb @dbname = 'OldDatabaseName', @newname = 'NewDatabaseName' 

    v2005 - v2014     

    ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName

    #2  Rename Table

    EXEC sp_rename 

          @objname = 'OldTableName', 

          @newname = 'NewName', 

          @objtype = 'OBJECT'

    #3  Rename Index
    EXEC sp_rename 

          @objname = 'TableName.IndexName',

          @newname = 'NewName',

          @objtype = 'INDEX'

     #4  Rename Column
     EXEC sp_rename 
           @objname = 'TableName.ColumnName',
           @newname = 'NewName',
           @objtype = 'COLUMN'

     #5  Rename Constraint
     EXEC sp_rename 
           @objname = 'ConstraintName',
           @newname = 'NewConstraintName',
           @objtype = 'OBJECT'
     

Remember, #4 only renames the column! It does not account for any referencing objects. For example, if you rename a table that is being called in a procedure, you will need to revise the procedure to reference the new table name. Dropping and re-creating the referencing objects is the recommended way to do this.