Cross Database Certificates – Trouble with Triggers

The weather has been awesome here for the last few days.  Sixty plus degree temperatures has made it feel more like May than February.  It isn’t supposed to last much longer, but I have enjoyed it.  I took the boat in for an engine tune-up this weekend, which means I should get it back just in time for most the ice to be coming off the lakes.  I’m hoping to take a couple more shots at the Wolf River walleye run this spring.  Last year didn’t provide good results.

I took my sons to a park on the edge of a lake this past weekend and happened to be watching while an unfortunate ice fisherman’s ATV fell through the ice.  I’m not sure how these ice fishermen know what ice is good versus what ice is bad, but you can see from the main picture above that not all of them know either.  Fortunately, only the front tires went through and another ATV came over and pulled him out.

I ran into an issue with cross database certificates recently.  I have blogged about how to set these certificates up here – they are a handy way to enable permissions across databases.  However, I ran into a problem where the permission chain failed due to a trigger on the original table that updated a separate table.  Here is the SQL  to replicate the issue:

CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE DATABASE A;
CREATE DATABASE B;

USE A;

CREATE TABLE dbo.SPtoUpdate
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    );
INSERT INTO dbo.SPtoUpdate
        ( ID , ILoveFishing )
VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
CREATE TABLE dbo.TriggerToInsert
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    , ChangeDate DATETIME2
    );
GO

CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
    FOR UPDATE
AS
    DECLARE @datetime DATETIME2;
    SELECT  @datetime = GETDATE()

    INSERT  INTO dbo.TriggerToInsert
            ( ID , ILoveFishing , ChangeDate )
    VALUES  ( 1 , 'Yes' , @datetime );
GO

CREATE CERTIFICATE BExecutor
   ENCRYPTION BY PASSWORD = 'Obfuscated'
   WITH SUBJECT = 'Execute sp from B to A',
   START_DATE = '20140101', EXPIRY_DATE = '20300101'
GO

BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

CREATE USER BExecutor FROM CERTIFICATE BExecutor

GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
--Also give insert on dbo.TriggerToInsert
GRANT INSERT ON dbo.TriggerToInsert TO BExecutor

USE B
GO

CREATE USER [GuggTest] FOR LOGIN [GuggTest];
EXEC sp_addrolemember N'db_owner', N'GuggTest'
GO

CREATE PROCEDURE dbo.UpdateTableInA
AS
    BEGIN
        UPDATE  A.dbo.SPtoUpdate
        SET     ILoveFishing = 'Walleye'
        WHERE   ID = 2;
    END

GO


CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

EXEC MASTER..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
GO

ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
    WITH PASSWORD = 'Obfuscated'
GO

--Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA

It turns out you can counter sign a trigger with the certificate, and this will allow the permission chain to succeed. By doing this, you don’t even need to grant the certificate user permission to the second table. Here is the syntax to do that:

ADD COUNTER SIGNATURE TO dbo.SPtoUpdateTrigger
BY CERTIFICATE BExecutor
WITH PASSWORD = 'Obfuscated';

Use this technique to work with cross database permissions that have to access tables with triggers.

Recursive Common Table Expressions

Wind can be an ally or an enemy of the fisherman.  Both in terms of comfort and in changing the mood and location of the fish, wind is something that can’t be ignored.  As it relates to the fish, wind can often turn fish on.  The term “muskie chop” refers to medium sized waves that can help create good conditions for fishing.  The wind does a couple things: it restricts the light by creating waves that break up the sun, and it also creates a current that can move fish to specific locations that can be targeted.  The other factor to consider related to wind if fisherman comfort.  I love fishing the colder months, but you’d better make sure you’re dressed for the weather.  There is no indoors in a fishing boat, so if it’s going to be windy and cold, bundle up.  At the same time on those hot, sunny, humid July days, you may not want to even be out unless there is some wind to cool you down.  Keeping all these factors in mind, it’s important to remember that wind is strongest when it has a large open space to build up it force.  If you want to avoid the wind, head to the upwind side of the lake.  If you want to embrace the wind, head to the downwind side.

In SQL Server, a recursive common table expression (CTE) could be compared to wind building up power as it moves over the lake.  A recursive CTE will call itself, and in doing so use the previous results to build to a final results set.

I recently had a perfect use case for this concept.  I had to take dollars given to me on a monthly level and distribute it to each day within the month.  Using a recursive CTE, I told SQL Server to give me the monthly total divided by the days in the month for each day in the month.  Below is an example of how I set it up:

CREATE TABLE #SalesTotalsByMonth
    (
      FirstOfMonth DATE
    , Channel VARCHAR(10)
    , SalesTotal DECIMAL(10 , 2)
    );
INSERT  INTO #SalesTotalsByMonth
        ( FirstOfMonth , Channel , SalesTotal )
VALUES  ( '2016-01-01' , 'Web' , 165473.99 ),
        ( '2016-01-01' , 'In-store' , 56998.45 ),
        ( '2016-01-01' , 'Mail' , 4645.85 )
,       ( '2016-02-01' , 'Web' , 27463.56 ),
        ( '2016-02-01' , 'In-store' , 61423.78 ),
        ( '2016-02-01' , 'Mail' , 5341.56 )
,       ( '2016-03-01' , 'Web' , 487356.67 ),
        ( '2016-03-01' , 'In-store' , 15734.56 ),
        ( '2016-03-01' , 'Mail' , 3104.85 )
,       ( '2016-04-01' , 'Web' , 478236.78 ),
        ( '2016-04-01' , 'In-store' , 24675.67 ),
        ( '2016-04-01' , 'Mail' , 1024.56 )
,       ( '2016-05-01' , 'Web' , 167524.89 ),
        ( '2016-05-01' , 'In-store' , 31672.78 ),
        ( '2016-05-01' , 'Mail' , 1798.67 )
,       ( '2016-06-01' , 'Web' , 347652.19 ),
        ( '2016-06-01' , 'In-store' , 41675.19 ),
        ( '2016-06-01' , 'Mail' , 801.78 )
,       ( '2016-07-01' , 'Web' , 247653.02 ),
        ( '2016-07-01' , 'In-store' , 59713.02 ),
        ( '2016-07-01' , 'Mail' , 2097.19 )
,       ( '2016-08-01' , 'Web' , 891642.23 ),
        ( '2016-08-01' , 'In-store' , 67134.23 ),
        ( '2016-08-01' , 'Mail' , 3752.02 )
,       ( '2016-09-01' , 'Web' , 342591.24 ),
        ( '2016-09-01' , 'In-store' , 77123.24 ),
        ( '2016-09-01' , 'Mail' , 2406.23 )
,       ( '2016-10-01' , 'Web' , 246758.25 ),
        ( '2016-10-01' , 'In-store' , 81214.24 ),
        ( '2016-10-01' , 'Mail' , 3012.24 )
,       ( '2016-11-01' , 'Web' , 267423.26 ),
        ( '2016-11-01' , 'In-store' , 91023.26 ),
        ( '2016-11-01' , 'Mail' , 2034.24 )
,       ( '2016-12-01' , 'Web' , 265219.56 ),
        ( '2016-12-01' , 'In-store' , 34167.02 ),
        ( '2016-12-01' , 'Mail' , 1010.26 );

WITH    recurse
          AS ( SELECT   stbm.Channel
                      , stbm.SalesTotal / DATEDIFF(DAY , stbm.FirstOfMonth , DATEADD(MONTH , 1 , stbm.FirstOfMonth)) AS Revenue
                      , DATEDIFF(DAY , stbm.FirstOfMonth , DATEADD(MONTH , 1 , stbm.FirstOfMonth)) AS daysleft
                      , stbm.FirstOfMonth AS [Sales Day]
               FROM     #SalesTotalsByMonth stbm
               UNION ALL
               SELECT   recurse.Channel
                      , recurse.Revenue
                      , recurse.daysleft - 1
                      , DATEADD(DAY , 1 , recurse.[Sales Day])
               FROM     recurse
               WHERE    recurse.daysleft > 1
             )
    SELECT  recurse.[Sales Day]
          , recurse.Channel
          , SUM(recurse.Revenue) AS Revenue
    FROM    recurse
    GROUP BY recurse.Channel
          , recurse.[Sales Day];

DROP TABLE #SalesTotalsByMonth;

The important thing to note here is the general pattern for a recursive CTE – the initial expression with a UNION ALL that calls the CTE.  Be sure to put the upper limit in the WHERE clause of the bottom half to avoid infinite recursion.

My final results gave me the total per day.

Float to varchar – conversion confusion

I don’t own my own ice fishing gear.  Between the shanty, the auger, the tip-ups, the rods/reels, and all the other miscellaneous equipment, you’re looking at a $500 inventment minimum. If you want to do it comfortably, it’s probably closer to $1,000. So I have been relying on friends and family to go out about once a winter. Since I’m not familiar with the winter patterns, this is probably better anyway.
I recently asked my brother in law if he wanted to go out, along with our kids. He said we would find a weekend when it would work, but looking at the forecast, I’m thinking that may not be for quite a while.
Weather Forecast.PNG
Rain is not good for ice. Since it’s already mid-January, we may be looking at February before the ice hardens back up enough to trust.
I recently ran into an issue that caused me a few minutes confusion. I was given a file that contained IDs in Excel. I needed to update some values in a table that contained these IDs as the primary key. I uploaded the data into SQL using the Data Upload wizard in SSMS. I used all the defaults except for giving the table a unique name that included the date. I use this type of nomenclature so I can periodically drop all the tables that have been created by ad-hoc uploads.
I did a quick SELECT from the newly created tables to ensure everything looked correct, and it did.
20170117-conversion-confusion-initial-select
Next I joined to my table that needed to be updated. I found the field to be joined on, OrderNumber, was created as float in my newly uploaded table. In the table to be updated, the column was a varchar(100). I did a simple CAST to try to join them together. I was surprised to see no results returned. I tried again while trimming each of the columns to join, and again no results.
20170117-conversion-confusion-joined-result
This was not making any sense to me. I next picked an order I knew was in both data sets and SELECTed the rows from each one separately to see if they should match. This showed the same order in both data sets.
20170117-conversion-confusion-both-tables-separately
From what I could see, both these columns should join together perfectly. I tried formulating the query differently, but this again provided no results.
20170117-conversion-confusion-different-query-setup
Lastly, I ran just the subquery separately. This gave me the clue I needed to figure out what was happening.
20170117-conversion-confusion-subquery-only
The conversion from FLOAT to varchar was bringing over the scientific form of the number as characters. This was clearly not going to match the order numbers in the other table. To fix, I used the STR function, and was able to make my update.
20170117-conversion-confusion-actual-update

So next time you are converting a float to a varchar, remember to use the STR function.  If not, you may get unexpected results.

SQL Server moving system databases Part 2 – master

I decided not to participate in the Rhinelander Hodag Muskie Challenge this past year.  While I enjoyed the experience in 2015, I didn’t feel compelled to try again quite yet.  Having caught no fish, it felt like a bit of a waste of money.  Instead of fishing for free and enjoying the peace and serenity of the lakes, instead it cost $250 and we were jammed into small lakes with several other boats trying to find some free water to fish.  I definitely see myself giving it another shot some time in the future, maybe even this year (2017).  The thrill of catching a big fish would only be magnified by thrill of hoping our catch was greater than all the other boats’ catches.  It’s just something that needs to be enjoyed in moderation, so it doesn’t become a waste of money when fishing is slow.

In my previous post, I demonstrated moving the msdb and model system databases.  This time I’m going to show how to move master, which is more complicated.  The master database in SQL Server holds all the system level information for SQL Server – all the logins, linked servers, endpoints and other system-wide configuration settings.  The master database also holds information about all the other databases and the locations of their files.  Because it is the “main” database, moving the files becomes more difficult – and dangerous.  If you lose the master database, you are going to have problems with basic requirements such as logging in.  Microsoft help states “SQL Server cannot start if the master database is unavailable”.

The first step in moving the database is to open SQL Server Configuration Manager and go to the Properties for SQL Server (MSSQLSERVER).

20170109 Move master database.PNG

Under the Advanced tab, you will see the current file locations specified in the Startup Parameters field.

20170109-move-master-db-current-location

The -d argument is the location of the data file and the -l argument is the location of the log file.  The -e is the location of the error log, in case you want to move that as well.

Update the file locations in the startup parameters to wherever you plan to move the files.

20170109-move-master-db-new-location

You will get a message that the changes were saved, but don’t go into effect until the service is restarted.

20170109-move-master-service-restart-required

Stop the service.

20170109-move-master-stop-service

Now move the files from their current location:

20170109-move-master-current-files-in-folder

To the new locations you specified in the startup parameters.

20170109-move-master-new-folder-location-for-data

20170109-move-master-new-folder-location-for-log

Lastly, start up the SQL Server Service, and you should be good to go!  You can verify the new file locations by running this query:

SELECT  name
      , physical_name AS CurrentLocation
      , state_desc
FROM    sys.master_files
WHERE   database_id = DB_ID('master');
GO

20170109 move master new location query.PNG

SQL Server moving System Databases Part 1 – msdb and model

I can remember a few distinct unpleasant memories while fishing.  In one, I was fishing out on the Rainbow Flowage with my Dad, oldest son, and nephew.  Since there wasn’t much room in the boat, I decided not to bring my muskie tackle box.  I brought my pole with a single lure on it to do a bit of muskie fishing, but our targeted species that day was walleye, bass, and panfish.

About an hour into the trip I swung my pole hard to push out another cast and the lure simply popped off the line.  The sound of line snapping is never a welcome one for a fisherman.  Not only did I lose a lure worth around $20, but I was also finished muskie fishing for the trip, since I hadn’t brought any other lures.  The lesson in the story is that your gear is only as strong as its weakest link.  In my case, the line snapped, but it also could have been the leader breaking that ended my day early.

I recently ran out of C: drive space on my test environment database server.  The drive is small at only 33 GB, so there just wasn’t much space to free up.  I took a look and noticed the system databases, except for tempdb, were all residing on the C: drive.  While there wasn’t much space between them, I still thought moving them would be the best way to free up a bit of space on the drive.

I looked at the database properties in SQL Server Management Studio (SSMS) to find the current location of the system database files.

20170106-move-system-dbs-old-file-location

I found them to be in the default folder for SQL Server 2008 installations.  I navigated to the drive to confirm they were there and see what else was in there.

20170106-move-system-dbs-old-files-in-folder

You can see the total used space for the three system databases is around 186 MB.  Again, this is a very small amount of space, but it is the only space I know that I can free up.

The next step is to move the files’ locations in SQL Server’s records.  I do this with an ALTER DATABASE command for each file.

20170106-move-system-dbs-update-sql-locations

With this complete, SQL Server will now look for the files in that location the next time the service is started.  So I need to stop the service.  I did it through the SQL Server configuration manager, but you can do it from the Windows Services screen as well.

20170106-move-system-dbs-stop-service

Next I need to go onto the server and physically move the files from their current folder to the new location that I specified.

20170106-move-system-dbs-new-log-files-in-folders

20170106-move-system-dbs-new-data-files-in-folders

This I handled with a simple cut and paste in Windows Explorer.  The last step is to start the service back up.  Everything came up fine and I am now able to see the files are in the new locations.

20170106-move-system-dbs-new-file-location

In the next post I will tackle moving the master database, which has a few wrinkles that make it much harder to move.

Import XML into SQL Server

One thing any responsible fisherman needs is a pair of pliers.  You may need different sizes for different types of fish.  For panfish, something small and skinny is going to help get in their small mouths to extract deep hooks.  For muskie, you’re going to want an extra-long pair to get to the hooks while keeping your hands away from those viscous teeth.  I’ve found that Northern Pike are particularily good at swallowing hooks into the back of their throats.  The last thing to remember is that a cut in the gills is almost always a death sentence for the fish.  Since the gills are what re-oxygenates the blood, their is a high volume of blood flow there, and even a small cut can cause a fish to bleed out in minutes.  If you aren’t confident you can remove a deep hook without damaging the fish, it’s usually best to just cut the line as close to the hook as possible.  Studies have shown the fish are adept at shaking hooks off once they are free of the fisherman.

northern-pike

I recently was provided a file that I needed to extract a bunch of data from.  The problem was that the file was xml, not the easiest to import into SQL Server.  I took a two step approach to solve this problem.  The first step was to import the data into SQL Server as a blob:

CREATE TABLE dbo.MarketplaceDecemberXML
(
id INT IDENTITY
PRIMARY KEY
, XMLData XML
, LoadedDate DATETIME
);

INSERT  INTO dbo.MarketplaceDecemberXML
( XMLData
, LoadedDate
)
SELECT  CONVERT(XML , BulkColumn) AS BulkColumn
, GETDATE()
FROM    OPENROWSET(BULK '\\ServerName\DataImport\All Orders.txt' , SINGLE_BLOB) AS oro;

The next step is to pull out any data that is needed.  There are a few ways to do this, but the easiest for me was to use the OPENXML function.  This function required me to load the xml data into an xml variable, then I could read different data based on the depth to which I did the initial opening.

DECLARE @XML AS XML
, @hDoc AS INT
, @SQL NVARCHAR(MAX);

SELECT  @XML = adx.XMLData
FROM    dbo.MarketplaceDecemberXML adx;
EXEC sys.sp_xml_preparedocument @hDoc OUTPUT , @XML;

SELECT  MarketplaceOrderID
, FulfillmentChannel
, PurchaseDate
, CustState
, OrderStatus
FROM    OPENXML(@hDoc, 'VendorEnvelope/Message/Order/FulfillmentData/Address')
WITH
(
VendorOrderID VARCHAR(100) '../../VendorOrderID',
OrderStatus VARCHAR(100) '../../OrderStatus',
FulfillmentChannel VARCHAR(100) '../FulfillmentChannel',
PurchaseDate DATETIME '../../PurchaseDate',
CustState VARCHAR(100) 'State'
);

EXEC sys.sp_xml_removedocument @hDoc;
GO

Here is a screenshot of some of the data I was working with:

20161215-import-xml

The important thing to note is that I used the ../ to move up in the hierarchy.  Using this method I was able to extract all the data I needed and place it in a table where I could query it at will.

Experimenting with SQL Server Memory

In my “career” as a muskie fisherman, I’ve caught or seen fish in some very unlikely places. I’ve caught fish over very deep water with seemingly no nearby structure as well as in water as shallow as just a few feet. I’ve had muskies strike or chase northern pike and walleye as I brought them into the boat. I’ve had bites when I was expecting nothing. The key to catching more fish is being will to test new locations, techniques, and lures.

shallow-muskies
I recently wondered what would happen if I was forced to drastically reduce memory available to SQL Server. Most DBAs know that if SQL Server is low on available memory and there is RAM to spare on the server, you can increase the memory SQL Server can use in the server properties. But what happens if you reduce the memory SQL Server can use below what it is currently using? I decided to try it out in our Test environment.

The initial maximum memory usage was 7 GB:

20161130-lower-memory-limit-below-current-usage-initial-setting

In looking at the SQL Server process in Windows Task Manager, I see it is using all plus a bit:

20161130-lower-memory-limit-below-current-usage-current-usage

I turned the max down to 4.5 GB:

20161130-lower-memory-limit-below-current-usage-change-setting

Looking at the Windows Task Manager shortly after the change, I can see the amount be using by SQL Server has been reduced as expected.

20161130-lower-memory-limit-below-current-usage-new-usage

If I look at my Memory Usage tracking at that time, I can see buffer pool was the area that was directly affected.  The buffer pool is where any data pages in SQL Server are cached for quick reading/writing.

20161130-lower-memory-limit-below-current-where-memory-changed

So next time you need to adjust the max memory downward, be aware this will immediately reduce the number of pages in the buffer pool.