Greetings

Welcome to my technical blog. I like data and databases they seem to be at the heart of everything, although my perspective may be a tad biased :).

Below I plan to jot down things that trip me up in work, along with things I find interesting. There will be no “level” to my posts, some will seem blindingly obvious. Hopefully others will be deeper and of more interest.

I tend to write in the first person (“… this worked for me” rather than “this may work for you if your environment is similar to mine”) in the hope that you can adapt my succinct notes that ACTUALLY WORKED to your situation.

Replicating MSSQL to MySQL

ok well its not really “Replication”, its keeping a MySQL table in sync with MSSQL using a SQL Job.

Whilst great for small, frequent, data updates, this method (using a linked-server) is not suitable for bulk data movement, where SSIS, BCP, or SQLCDM would be much faster.

Job Step 1 Remove old data from MySQL

USE SomeDatabase;
GO

DELETE FROM OPENQUERY
            (SomeMySQLInstance,
             'SELECT ID, Code, Comments FROM SomeMySqlTable'
            )
WHERE ID NOT IN
      (
          SELECT ID
          FROM [SomeServer].[dbo].[SomeView]
      );

PRINT 'Rows Deleted = ' + CONVERT(varchar(10), @@rowcount)
 

Job Step 2 Copy new data to MySQL

USE SomeDatabase;
GO

INSERT OPENQUERY
       (SomeMySqlInstance,
        'SELECT ID, Code, Comments FROM SomeMySqlTable'
       )
SELECT *
FROM [SomeDatabase].[dbo].[SomeView]
WHERE ID NOT IN
      (
          SELECT ID FROM SomeMySqlInstance...SomeMySqlTable
      );

PRINT 'Rows Inserted = ' + CONVERT(varchar(10), @@rowcount)
 

Just to decode that a bit …

  • Step2 Line12:”SomeMySqlInstance…SomeMySqlTable” refers to a Linked Server connection (called “SomeMySqlInstance”), where the default database is the one we want (containing the table “SomeMySqlTable”).
  • Lines 11 & 9: “[SomeView]” is the source of the current data that the MySQL table needs to be synced with. It is just a shortcut way to not need to save a stored-proc output to a table for consumption by OPENQUERY. Note that the view needs a unique ID column.
  • The PRINT statements are just for the jobs history.

Removing duplicate rows

No need to over develop this – once you realise that the DELETE command can include the TOP option.

More? Ok. Create a SELECT command that shows the issue. EG:-

SELECT ID
FROM dbo._op2
WHERE ID = 'X123456';

Add TOP to return just the unwanted rows. IE: if the above query should return 1 row but erroneously returns 2 rows use TOP(1), if it returns 5 rows use TOP(4) …

(NOTE: It does not matter which particular rows get deleted … they are duplicates remember)

SELECT TOP(1) ID
FROM dbo._op2
WHERE ID = 'X123456';

Change SELECT to DELETE and remove the column name …

DELETE TOP(1)
FROM dbo._op2
WHERE ID = 'X123456';

… and only run it once 😉

Stop notification spam

Although I have previously tackled this problem by rolling my own notifications. This time I realised that the SQL jobs that run every minute or two are of low importance – and I don’t really want emails when they fail. I will notice at some point during the working day.

Here is the code to lists jobs that run frequently and are configured to send notification emails. Along with the commands to remove those notifications.

/* JobNotificationEmailStop.sql */

SELECT S.[name] JobName,
    SS.freq_subday_interval [ScheduleFreq(mins)],
    'EXEC msdb.dbo.sp_update_job @job_name = N''' 
    + S.[name] 
    + ''', @notify_email_operator_name = N'''';' 
    CommandToDisableEmailNotification

  FROM msdb.dbo.sysjobs S
  JOIN msdb.dbo.sysjobschedules SJ
    ON S.job_id = SJ.job_id
  JOIN msdb.dbo.sysschedules SS
    ON SS.schedule_id = SJ.schedule_id

 WHERE SS.freq_subday_interval > 0
   AND S.notify_level_email > 0
 ORDER BY SS.freq_subday_interval;
 

Except and Intersect

Here is the simplest working example of EXCEPT and INTERSECT I can come up with (for Will)

/* Except.sql */

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1 (#1 INT);
INSERT INTO #t1 VALUES (1), (2);

IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2;
CREATE TABLE #t2 (#2 INT);
INSERT INTO #t2 VALUES (2), (3);

SELECT * FROM #t1
EXCEPT
SELECT * FROM #t2; /* = 1 */

SELECT * FROM #t1
INTERSECT
SELECT * FROM #t2; /* = 2 */

SELECT * FROM #t2
EXCEPT
SELECT * FROM #t1; /* = 3 */

I use this frequently whilst refactoring to check the outputs are identical. And rarely when syncing a MySQL table to MSSQL.

How to check SQL Jobs are actually doing something.

Looking through the Database Mail log today, I accidentally discovered a job that had been busy sending emails for I-don’t-know-how-long using an email profile that no longer worked. The output of the job was ‘success’ as the emails had been successfully queued with the Database Mail sub-system.

After finding the emails would have been empty anyway, I disabled the job. But it made me wonder if there might be other jobs that were busy doing nothing – hour after hour – day after day.

Knowing the dangers of weakening the system, I did not want to fail a job or job-step just to flag a maintenance issue.

The lowest-risk change I could think of making (to the many, legacy, unfamiliar jobs) was to leave pertinent messages in the job history log using the PRINT command. For example:-

IF EXISTS (SELECT 1 FROM SomeTable)
   BEGIN 
      PRINT 'YES: there is new data'
      (Do meaningful stuff)
   END
   ELSE
      PRINT 'NO: there is no new data';

Then in the future I might notice that there is Never any new data!

Move a MySQL database

There are many ways to copy (or move) a MySQL database (aka schema) from one server (aka instance) to another (including the data).

On this occasion I used the Export and Import utilities within “MySQL Workbench” (like doing a backup and restore). The fact that the source and target instances were both hosted on GCP was irrelevant (no brackets required!).

1. Connect to Source and start the Data Export utility …

2.Within the utility, I left the defaults as they were, apart from …

  • TIC the schema I wanted to export (see screenshot)
  • Select “Export to Self-Contained File” as I wanted all the tables
  • and create a meaningful dump-file name

3. Because the Export and Import utilities would be using different logins, I clicked “Advanced Options” within the Export utility, and typed “OFF” over the top of the default “AUTO” setting for set-gtid-purged …

…before clicking the “Return” then “Start Export” back on the main page.

4. To keep it simple, I closed and reopened MySQL Workbench before connecting to the Target instance. Then from the Server menu I chose Data Import …

5. I left all the defaults as they were except …

  • I chose “Import from Self-Contained File”
  • and navigated to the dump-file
  • I clicked “New” and typed the schema name that would receive the import.

6. Finally. On the “Import Progress” page I clicked “Start Import”. Then waited about five minutes before anything seemed to happen

Backup KeePass

Its important to have a backup copy of your password vault. An easy and robust solution is to use the Windows Task Scheduler program to schedule a regular backup.

To start, create a folder to hold the script and the backup. For example C:\Temp\Backup\.

Next, create and test a command that copies your keepass file to the backup location EG: “XCOPY C:\Users\88776655\Downloads\Database.kdbx C:\Temp\Backup\ /Y” and save it in a text document within the backup folder. EG: “C:\Temp\Backup\BackupKeePass”.

Change the properties of the “Backup” folder so you can see the file extensions IE: BackupKeePass.txt” …

Then change the extension from txt to bat and test it works (right-click, open).

  • Start up the Task Scheduler by typing it into the Windows search bar.
  • Right-click on “Task Scheduler Library” and create a new folder “My Tasks”.
  • Right-click on “My Tasks” and choose “Create Task”. Give it a name like “Backup KeePass” and click the radio-button “Run whether a user is logged in or not”
  • On the Triggers tab, schedule it or set it to run every log on.
  • On the Actions tab create a new action to start a program and browse to the bat file.
  • On the Settings tab, select “Stop task if runs longer than 1 Hour”, and click OK.

To test make a change in KeePass and save it. The reboot , login and examine the date the backup was created.

Log Shipping to GCP

Whilst it is possible to configure “log shipping” between different versions of SQL Server, the result can only be used for disaster recovery.

This is because “standby mode” (where the data could be read on GCP) requires the same versions of SQL Server on the primary and secondary servers.

The automatic upgrading of the database objects (from say 2008r2, to say 2017), when brought online could not happen fast enough to support the continuous nature of “standby mode”.

Capturing input parameters

Often when a stored-procedure is executed I want to know the parameters that were input. Which is handy for performance tuning.

There is a mechanism to automatically save input parameters with the cached execution plans, but quite often this does not work well.

On this occasion I embedded the facility right into the procedure as a temporary measure (please, don’t talk to me about triggers brrr).

CREATE PROCEDURE [dbo].[sp_SomeName]
 @ID UNIQUEIDENTIFIER = NULL,    
 @Record VARCHAR(50) = NULL    
AS  
BEGIN

 /* log parameters for performance tuning 1 of 2 */

  IF OBJECT_ID('[SomeDatabase].[dbo].[tbl_SomeTable]') IS NULL
  	SELECT GETDATE() STIME, GETDATE() ETIME, @ID ID, @Record RC 
  	INTO [dbo].[tbl_SomeTable]
  ELSE
  	INSERT INTO [dbo].[tbl_SomeTable]
  	SELECT GETDATE(), GETDATE(), @ID, @Record

 /* log parameters for performance tuning 1 of 2 */

...

Overkill really, but at the end of the procedure I added …

 ...

/* log parameters for performance tuning 2 of 2 */
  
  UPDATE [dbo].[tbl_SomeTable]
  SET ETIME = getdate()
  WHERE ETIME = STIME;
  
 /* log parameters for performance tuning 2 of 2 */

END
GO

Note: the real procedure had many more input parameters, and I suspected they are all set to null. Which would explain the poor performance.

Still, best to know what we’re optimizing for 🙂

Uploading data to Google’s cloud platform

This project was to refresh data every night from a production SQL Server table to a cloud-based MySQL Server. (see my previous post on preparation).

On the GCP website

I created a minimal MySQL instance (which I think of as a “Server”) with a single zone and a Standard machine type (being 1vCPU and 3.75 GB of memory).

Storage I set to a 100GB HDD. Then added an Authorised Network comprising an ip-address range covering our production servers along with a friendly name. I disabled auto storage increase, backups, and point-in-time recovery.

I created a database specifically for this project, and a User (with password) restricted to the same ip-address as above.

Finally, because I would be copying data from a MS-SQL table into a MySQL table (which is not 100% compatible), I set flag “sql_mode” to “ANSI”.

On the Production SQL Server

I downloaded the current MySQL 32 bit ODBC driver and configured a new ODBC data source.

Then I created a 2-step SQL Job scheduled to run every night.

Step-1 Collects and saves the data into a purpose made table using a stored-procedure. Replacing the old data.

Step-2 Uploads the table from step-1 into the GCP database using an SSIS package.

To help (with tasks like creating, deleting, viewing and testing tables), I installed the MySQL admin tool “My Workbench” locally. Even so, the SSIS package took a time to perfect. It had to :-

  • Connect to the source and target servers.
  • Replace all the target data every time it ran – for robustness.
  • Refer to reserved column names using double-quotes.
  • Ensure target data-type and sizes exactly matched the source.

Uploading data to Google’s cloud platform – preparation

Gotcha! that title makes no sense.

My preparation would all be in my head for this data move from Microsoft SQL Server 2019 to MySQL 8.0. Accessing the former from SQL Server Management Studio and the later through My Workbench.

The cloud is not a new operating system.

The data would NOT be “uploaded”, just copied from a remote Windows SQL Server to a remote Linux MySQL server. And that sounds like a job for SSIS. (preparation complete 🙂 )

Orphaned users

Servers have Logins, and databases have Users.

A Login and a User account are linked, sharing the same name and the same SID. Naturally, for each Login there can be many User accounts – one in each database.

Now, if you backup a database on one server and restore it onto another server. It may contain Users within that database, that do not have a corresponding Login on the second server.

Execute this command to list any orphaned Users

SELECT 	DP.type_desc,
        DP.SID,
        DP.[name] UserName
FROM [sys].[database_principals] DP
LEFT JOIN [sys].[server_principals] SP
       ON DP.SID = SP.SID
WHERE SP.SID IS NULL
AND DP.authentication_type_desc = 'INSTANCE';

There are two types of User account – SQL and Windows. For orphaned SQL Users paste the name, SID, and password* into this command and execute it to create the missing Login.

(*If you do not know the Login password that was used on the old server then create a new one. They are not linked)

USE [master]
GO
CREATE LOGIN [SomeLogin]
WITH PASSWORD = 'SomePassword',  
SID = 0xSomeSid;

For orphaned Windows Users use this command …

USE [master]
GO
CREATE LOGIN [SomeDomain\SomeLogin] 
FROM WINDOWS 
WITH DEFAULT_DATABASE = [master];

Expensive Spaghetti

Now that cloud computing has made the cost of things more visable. It is easier to see how much money is being wasted carrying around legacy spaghetti code.

Where once I.T. Managers prioritized the task of cleaning up inefficient code that works as “one day”. Now a cloud-compute-unit can be tied directly to cold-hard-cash. “Technical Debt” suddenly has an individual price sticker.

Subtree Cost = DTU’s = Money

Set every users default schema to DBO where its blank

In this quick script I am assuming the Windows domain is called ‘DOM’ …

-- ChangeDefaultSchemaToDboWhereNull.sql

DECLARE @cmd varchar(1000) 

SET @cmd = 
'USE ? IF DB_ID(''?'') > 4 SELECT ''USE ?; ALTER USER ['' + name + ''] WITH DEFAULT_SCHEMA = [dbo]''
 FROM sys.database_principals
 WHERE default_schema_name IS NULL
 AND [name] LIKE ''DOM\%'''

IF OBJECT_ID('tempdb..#output') IS NOT NULL DROP TABLE #output
CREATE TABLE #output
(command varchar(1000))

INSERT INTO #output
EXEC sp_MSforeachdb @cmd

SELECT * 
FROM #output


SQL Snapshot worksheet

— snapshots.sql

–1. create a snapshot

USE master;
GO
CREATE DATABASE Credit_Snap
ON  
    (
    NAME = CreditData,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\CreditData.ss'
    ),
    (
    NAME = CreditCatalog,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\CreditCatalog.ss'
    )
AS SNAPSHOT OF Credit;

–2. restore database from a snapshot

USE master;
GO
DECLARE @kill VARCHAR(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';'
  FROM master..sysprocesses
 WHERE dbid = DB_ID('Credit')
   AND spid > 50;
EXEC (@kill);
RESTORE DATABASE Credit FROM DATABASE_SNAPSHOT = 'Credit_Snap';

–3. delete snapshot

USE master;
GO
DROP DATABASE Credit_Snap;

— testing

SELECT *
FROM [Credit].[dbo].[member]
WHERE member_no = 22

BEGIN TRAN
UPDATE [Credit].[dbo].[member]
SET Firstname = 'DRY'
WHERE Firstname = 'CRRY'
ROLLBACK TRAN

SELECT aa.*
FROM [Credit].[dbo].[member] aa
JOIN [Credit_Snap].[dbo].[member] bb
  ON aa.member_no = bb.member_no 
WHERE aa.Firstname <> bb.Firstname;

Transactional Replication causing High CPU

In Publication Properties the setting “Subscriptions never expire …” has a surprising effect on the job “Distribution clean up: distribution”.

This job removes orphaned and replicated transactions from the Distribution database once the retention period has expired.

However, “Subscriptions never expire …” stops this procedure from removing orphaned transactions – left by a deleted subscription – or for any other reason.

This results in the Distribution database growing and high CPU.

To fix this, allow subscriptions to be able to expire.

So failed subscriptions may be deleted if not fixed within a year. This preserves the robustness of “Subscriptions never expire”, whilst allowing orphaned transactions to be cleaned up.

Change SQL Server Collation

To change the default collation of my SQL 2019 instance on my laptop to “SQL_Latin1_General_CP1_CI_AS” I …

– Found the saved the location of sqlservr.exe into notepad.

– Added [cd ] in front of the path (that’s cd and a space, without the square brackets)

– Added a second line in notepad [sqlservr -m -T4022 -T3659 -s”SQL2019″
-q”SQL_Latin1_General_CP1_CI_AS”] (without the square brackets)

– Stopped the SQL Server service

– Opened a Command Prompt as Administrator

– Executed the first command (cd …)

– Executed the second line (sqlservr …)

– Rebooted.

Save emergency contact details to Azure Blob Storage

This was part of the business continuity plan, so that HR had a list of employees next-of-kin etc, off-site.

I created a Job with 2 steps (Export using BCP and Import using AZCopy).

-- bcp.sql


-- Create the command to be executed

	DECLARE @cmd VARCHAR(1000) = 
                'bcp SomeDatabase..SomeView out 
                E:\SomePath\SomeFileName.txt -c -t, -T -S' + @@servername;

-- Run this if command-prompt is disabled

	IF (SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell') = 0
	BEGIN
		EXEC sp_configure 'show advanced options', 1; RECONFIGURE; -- show advanced options
		EXEC sp_configure xp_cmdshell, 1; RECONFIGURE; -- enable command-shell
		EXEC xp_cmdshell @cmd; -- run the command
		EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE; -- disable command-shell
		EXEC sp_configure 'show advanced options', 0; RECONFIGURE; -- hide advanced options
	END;

-- Run this if command-prompt is enabled
		
	ELSE
		EXEC xp_cmdshell @cmd;

Step-1 overwrites the file each time.

In preparation for the azcopy command I used Azure Explorer to connect and create a fileshare called “BCExtract”.

Right-Clicking on this I chose “Generate Shared Access Signature”, and “Ad hoc Shared Access Signature”. I left all the defaults as they were except I increased Expiry time a bit.

I pasted the “Shared Access Signature URI” into notepad, added a backslash, then the “Shared Access Signature Token” making one long string.

Before this string I added “azcopy copy ” then the local path to the CSV file in double quotes.

-- azcopy.sql


-- Create the command to be executed

	DECLARE @cmd VARCHAR(1000) = 
		'azcopy copy "E:\SomePath\SomeFileName.txt" 
                "https://SomeLocation/SomeSharedAccessToken"'

-- Run this if command-prompt is disabled

	IF (SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell') = 0
	BEGIN
		EXEC sp_configure 'show advanced options', 1; RECONFIGURE; -- show advanced options
		EXEC sp_configure xp_cmdshell, 1; RECONFIGURE; -- enable command-shell
		EXEC xp_cmdshell @cmd; -- run the command
		EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE; -- disable command-shell
		EXEC sp_configure 'show advanced options', 0; RECONFIGURE; -- hide advanced options
	END;
	
-- Run this if command-prompt is enabled
		
	ELSE
		EXEC xp_cmdshell @cmd;

Dropping a user that owns a schema (Error: 15138)

Manually highlight and run #1. Paste the result into #2.

-- DropFailedForUser.sql

-- The statement (that caused the error)

   USE [master] -- in this case
   GO
   DROP USER [Dom\SomeUser]
   GO

/* The Error ...

   Drop failed for user 'Dom\SomeUser'
   The database principal owns a schema in the database
   and cannot be dropped. Error: 15138
   */

-- #1. find the name of the schema

   SELECT [name]
   FROM sys.schemas s
   WHERE s.principal_id = USER_ID('Dom\SomeUser');

-- #2. transfer ownership of the schema to 'dbo'

   ALTER AUTHORIZATION ON SCHEMA::[SomeSchemaName] TO dbo;

-- repeat "The Statement"

Redgate SQL Data Compare

I love this tool for refactoring. With a result set of over 3,000 rows across 60 columns, eyeballing similar outputs in a spreadsheet just would not do.

To use this tool, I modified the original query to output INTO a new table “_output” in the current database. Near the start of the query I put an IF EXISTS/DROP statement (more commonly used with temp tables), and at the bottom of the query I selected star from _output.

After improving the original query code (and saving it with a new name), I modified it similarly to the above – outputting results INTO table “_output” but in a DIFFERENT database.

I configured “SQL Data Compare” to use every column of the first “_output” table as a “comparison key”. And can now confirm the two tables called “_output” in different databases are identical.

Drop all tables that start with underscore

In an ironic twist of fate I adapted my ‘Drop all temp-tables’ script, to drop all tables beginning with an underscore.

Yes, it is ironic, because it uses a temp-table to store the working list of tables to be deleted. Whilst my original script used a real table to store a list of temp-tables.

Well … ok then

-- DropAllTablesStartingWithAnUnderscore.sql

IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
SELECT [name] 
INTO #tables
FROM sys.tables
WHERE [name] LIKE '/_%' ESCAPE '/'

DECLARE @table VARCHAR(200), @cmd VARCHAR(500)
WHILE (SELECT COUNT(*) FROM #tables) > 0
BEGIN
	SET @table = (SELECT TOP(1) [name] FROM #tables)
	SET @cmd = 'drop table ' + @table
	EXEC(@cmd)
	DELETE FROM #tables WHERE [name] = @table
END

(In case you were wondering why I created these underscore-tables in the first place. I was refactoring a stored-procedure that took over an hour to run, and had a large number of temp-tables. I wanted to persist those temp-tables for another day, and not have to start from scratch.)

Find the partner of a bracket in SSMS

Faced with a barrage of tabbed T-SQL in SSMS it can sometimes be quite difficult to see the close bracket that signifies – for example – the end of a CTE.

TIP: swipe from the inside of the bracket outwards to highlight (in grey) the bracket itself, and also its partner. EG: swipe right-to-left across an open bracket.

Running CHECKDB on TempDB

Normally I would not bother, but when CHECKDB runs on TempDB it cannot use a snapshot so has to lock all the temp tables. This script will wait for exclusive access for up to a minute.

DECLARE @outcome VARCHAR(50) = 'TempDB is currently too busy for CHECHDB', 
	@endtime DATETIME = DATEADD(mi,1,GETDATE())
WHILE GETDATE() < @endtime
BEGIN
  IF NOT EXISTS (SELECT 1 FROM sys.dm_tran_locks WHERE request_mode = 'X' AND resource_database_id = 2)
  BEGIN
    DBCC CheckDB([tempdb]) WITH NO_INFOMSGS;
    SET @outcome = 'success'
    BREAK;
  END
  WAITFOR DELAY '00:00:01';
END
SELECT @outcome

A TDE test restore

Post migration, I wanted to make sure an encrypted database on a new SQL 2014 Enterprise edition server could be restored.

I installed SQL 2014 Developer edition on a second machine and initially got the expected error …

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint.
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

1. I checked if the target server was enabled for TDE …

SELECT * FROM sys.symmetric_keys

This returned one row, I checked against the source server and that returned two rows. I concluded that the target server was NOT yet TDE enabled.

2. To enable TDE on the target …

USE Master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '[SomePwIJustMadeUp]';
GO

3. Next I copied over the two files (*.cer and *.pvk) from the backup location to the target server and installed the certificate into Master …

USE Master
GO
CREATE CERTIFICATE [NameOfTheDatabase]_Cert2
FROM FILE = '[PathAndNameOfLocalCopyOfCertFile].cer'
WITH PRIVATE KEY (FILE = N'[PathAndNameOfLocalCopyOfPvkFile].pvk',
	  DECRYPTION BY PASSWORD ='[TheSourcePwIGotFromKeepAss]');
GO

4. After which I was able to restore the database as normal.

Docker installs

The traditional way to the install something like 7zip on a Windows laptop would be:-

  • Use Google to find the software’s website.
  • Go there and download a zip or MSI file
  • Run the (unzipped) installer or MSI file
  • Start the software by clicking a desktop icon or finding it in “all programs”.

The equivalent steps in docker would be:-

  • Find the software on docker hub with docker search
  • Downloads the software image with docker pull
  • Create a container from the image with docker create
  • Start the software by running the container with docker start

However, a better way would be:-

  • Find the software on docker hub with docker search
  • Download, unpack, install, and start the software with docker run

Searching every Procedures for a string.

To search every stored procedure in every database on every server (ranging from SQL Server 2005 to SQL Server 2016) for the string ‘QueryTraceOn’, I first registered every server within SSMS.

Right-clicking on the registered server folder, I chose ‘new query’ and ran ‘select 1’ to exclude from my list any server with issues.

Once I had an error free list, I ran this code (which took around 40 minutes) …

-- SearchProcs4String.sql

EXEC sp_MSforeachdb 'use ?
SELECT db_name() [Database], ROUTINE_SCHEMA + ''.'' 
+ ROUTINE_NAME [Proc]
FROM INFORMATION_SCHEMA.ROUTINES WITH (NOLOCK)
WHERE ROUTINE_DEFINITION LIKE ''%QUERYTRACEON%'';'

Getting away from Dedupe jobs

Duplicate data should ideally be stopped at the front end. However if a table already contains duplicate data you may want to bash out some code to clean it up. And schedule a SQL job to run the code regularly.

However, there are mechanisms baked right into SQL Server to manage this more efficiently (step away from scripting everything – devops 😉 )

True enough, you need to run code (once) to clean out all the current duplicates, but going forward a unique filtered index can keep them out.

For this particular project “duplicate data” meant that an ID column should not contain a number already in that column if the Country was ‘UK’ and the Package ID was ‘5’.

Here is a simplified example of my solution …

-- ix_BlockDupCustIDs.sql

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1 (CustID INT, CountryCode CHAR(2), PackageID INT);

CREATE UNIQUE INDEX ix_BlockDupCustIDs 
ON #t1 (CustID) 
WHERE CountryCode = 'GB' AND PackageID = 5;

INSERT INTO #t1 VALUES (1, 'GB', 5) -- yes
INSERT INTO #t1 VALUES (2, 'GB', 5) -- yes
INSERT INTO #t1 VALUES (1, 'GB', 4) -- yes
INSERT INTO #t1 VALUES (1, 'US', 5) -- yes
--INSERT INTO #t1 VALUES (1, 'GB', 5) -- no, duplicate
--INSERT INTO #t1 VALUES (2, 'GB', 5), (3, 'IR', 1) -- no for both
--UPDATE #t1 SET PackageID = 5 WHERE PackageID = 4 -- nope

SELECT * FROM #t1;

How to run SQL Server within Docker

Here is my bare-bones walk-through for setting up SQL Server Developer Edition within Docker for those of us who don’t have time to learn docker right now.

1. I navigated to the Docker website and read about Docker Desktop before installing Docker Desktop for Windows (and creating an account) from my Windows 10 Pro laptop.

During installation I chose to use Windows containers by default, rather than Linux.

2. To find SQL Server Developer Edition, I reached out to the Docker Hub online library from my laptop with this command (typed into a Command-Prompt or Powershell window)

docker search mssql

… which brought back this list …

Annotation 2020-04-04 113431

The second item down on the list is from Microsoft, is in a Windows container, and is the Developer edition.

I copied the name into Notepad and composed this command around it to download, extract, and run SQL Server within a docker container …

docker run -d -p 1433:1433 -e sa_password=Pa55word# -e ACCEPT_EULA=Y --restart always -v C:\temp:c:\temp -v sqldata:c:\var\opt\mssql microsoft/mssql-server-windows-developer

Breaking down that command …

  • Docker = invoke Docker
  • Run = download, extract, and execute something from Docker Hub.
  • -d = detached mode. That is, run independent of the command line.
  • -p 1433:1433 = map external port 1433 to internal port 1433.
  • -e sa_password=Pa55word# = create an environment variable called sa_password containing the text “Pa55word#” (without the double quotes).
  • -e ACCEPT_EULA=Y = create another variable called ACCEPT_EULA containing the value “Y”.
  • –restart always = automatically start when Windows starts
  • -v c:\temp:c:\temp = map my laptop folder c:\temp to the docker folder c:\temp
  • -v sqldata:c:\var\opt\mssql = persist user databases
  • microsoft/mssql-server-windows-developer = this is the thing to “Run” (see above).

When complete (after 30 minutes or so) I connected via my regular laptop SSMS (IE: outside of the container) …

Annotation 2020-04-04 110654

… and added it to my registered servers as “Docker SQL Server Developer edition”

(Incidentally, that server name “dot” implies the local default instance on the default port. I could just as correctly have typed “localhost” or “localhost,1433”. Or from another laptop I would have used the IP address of the host laptop.)

Next I changed my Windows setting so the Docker icon would always be visible …

Capture

… so that on subsequent boots, I just need to wait for the icon to stop wobbling before connecting to the database via SSMS (and no need for any pesky Docker commands).

To import a backup I moved it to my laptop c:\temp and restored from there using SSMS.

“And finally” (as the late, great, Ronnie Corbett used to say), if after a while it all goes horribly wrong, as software tends to do, you can either uninstall docker and start this article again.

Or venture into the docker command-line to list (docker ps), stop (docker stop ~), then remove the SQL container (docker rm ~). Before using the green command above again.

More on pesky Docker commands soon.

Migrating to SQL Server 2017 / 2019

(for JH) When migrating to SQL Server 2017 or SQL Server 2019 the name of the game is “Risk Mitigation”. Avoid the temptation to bundle other improvements into the project. Business continuity is first, last, and everything in between.

Server Architecture should be duplicated Exactly in the new environment.

Server Rationalization – nope. Before the migration would be best, because if unexpected issues arise you are on familiar ground. But you have unused logins? databases? jobs? – double nope (Before or after, not during).

SQL Server Role based security – nope. That has been best practice for the last 20 years. This is not the time to make up for lack of database administration. 21 years will be fine.

High Availability – Avoiding downtime is important and HA helps with that. But as long as you have a mature backup system in place, this can wait until the migration is complete. Nope.

Backups – Are less clear cut. But even if the old system is a quirky high maintenance mill stone. It is still familiar, and you don’t want to add unfamiliar complexity at this time. This should be next on your list though.

Disaster Recovery – There are new toys, like clusterless AG’s but again – nope.

Compatibility Level – Should match the old system. A few months after the deed is done, you can quietly try switching less important databases, one at a time (database administration is a perpetual business).

SSIS Packages – Should be rationalized from Packages to Projects … after the migration. Oh and if a package fails after being moved and upgraded, change the environment NOT the package.

Encryption is much easier with modern versions of SQL Server. Schedule this for a few months down the line.

Cardinality Estimator – You may have picked up on a theme here 🙂 – nope. Set it to legacy for reliable performance, for now.

Start SSMS as another user

There are a few ways to open SQL Server Managaement Studio as another Windows user. My usual approach is to create a shortcut on my desktop that uses the built in ‘Runas.exe’ command.

In notepad I assemble the 3 parts needed …

1. Full path to runas.exe
2. The Windows account I want to use
3. The full path to the SSMS executable.

Here is an example …

C:\Windows\System32\runas.exe /user:ZGROUP\rsmithadmin "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

Log Shipping for Migration

The trouble with backing up databases in ‘old production’ then restoring them to ‘new production’ is that it takes time.

And there may be some unforeseen delay switching the front-end apps over.

Resulting in ‘old production’ being updated with new data, and ‘new production’ becoming out of date.

Log-shipping is an ideal, built-in, tool that can be used to keep ‘new production’ in sync with ‘old production’ during that phase between backup/restore and switching the front-end to ‘new production’.

This time around there was no need to script the setting up of log-shipping. There were only 13 databases, so using the GUI did not take long.

The idea is to complete the backup/restore a week or so before the switch-over and set-up log-shipping to keep the data in sync.

Then at the designated switch-over time, it takes only a moment to bring ‘new production’ on-line, as a fully up-to-date copy of ‘old production’.

Here is my crib-sheet …

 

Preparation

  • Primary and secondary servers should have as near as possible the same instance settings eg: max-memory, numa configuration, CLR, max dop, etc
  • Ensure user databases are using full recovery model
  • Create shared folder (on the target ideally)
  • Default backup compression is enabled (ideally)
  • Reduce VLF counts
  • Configure file share folder and connectivity
  • Disable tlog backups on primary

Preparation – Secondary Instance

  • Ensure enough space for databases
  • Matching drive letters for datafiles and logfiles (ideally)
  • Configure file share

Preperation – Monitor Instance

  • Ideally separate from primary and secondary

Security

  • Config login is a sys admin role
  • SQL server service account on primary needs read/write permission on backup directory (for the backup job)
  • SQL server service account on secondary needs read permission on backup share and read/write permission to secondary share (for copy / restore jobs)

Configuring log-shipping

  • Manually backup and restore database (with no recovery)
  • Use notepad to cut and paste connection strings and paths
  • Transfer logins, jobs and linked servers

The switch over

  • Manually execute the backup, copy, and restore jobs a final time
  • Manually restore each database “with recovery”
  • Detach old databases (so there is no chance of them being updated)
  • Point front-end-applications to new back-end server

Post Migration

  • Full backups (the old ones cannot be restored now)
  • Update all statistics
  • Check compatability level
  • Execute dbcc checkdb
  • Enable plan-store (read/write)
  • Monitor health and performance

Installing SQL 2017 on a cluster

Remote onto node-1 with the SQL DVD inserted (or mounted), and Windows Failover Cluster already created with shared drives D, L, T, and S. (and ideally Z for backups, but not in this case).

Preperation

  • Re-boot (if “access denied” end task “Service Host: DCOM Server Process Launcher (4)”)
  • From Failover Cluster Manager run “validate cluster” (expect warnings for software level and only one network)
  • Copy media to local drive (IE: SQL Server, CU, and maybe SSMS)
  • Ensure you have service accounts for Engine and Agent.
  • Take a screenshot of Windows uninstall screen – to aid possible uninstall

Run on each node individually

  • Advanced
  • Advanced cluster preparation
  • Product Key, next
  • Licence Terms, tic, next
  • Microsoft Update, next
  • Prepare Failover Cluster Rules (Windows Filewall Warning), next
  • Feature Selection #1, [Database Engine Services]
  • Feature Selection #2, root = [S:\]
  • Instance Configuration, instance name, Instance ID
  • Server Configuration, Agent and Engine (automatic), svc accounts, Grant perform VMT tasks
  • Feature Configuration Rules, next
  • Ready to Install, install

On Active Node

  • Advanced
  • Advanced cluster completion
  • Cluster Node Configuration, Create AD name for instance
  • Cluster Resource Group, next
  • Cluster Disk Selection, tic all required disks (EG: D, L, T)
  • Cluster Network Configuration, tic IPv4, untic DHCP, choose address within subnet (EG: 10.8.0.1)
  • Server Configuration, next

Shared Settings

  • Database Engine Configuration #1: sa pw, add current user, sql service account.
  • Database Engine Configuration #2: Root = D:\, Data = D:\MSSQL, Log = L:\MSSQL, Backup = D:\Backup
  • Database Engine Configuration #3 (tempdb): Data = T:\MSSQL, Log = L:\MSSQL
  • Ready to Install, Install

Uninstall – if needed

  • If it failed before “Advanced cluster completion” just remove from each node via Contol Panel.
  • If it fails at the very end. You will need to run “Remove node from Cluster”, in the Maintenance tab of the media installation program.

 

Azure Backups and Restores

Backups

“Azure SQL Database” includes automatic (compressed) backups. The retention period is 7 days for Basic and 35 days for Standard and Premium. Backups are geo-replicated. The backup schedule is weekly full, hourly diff, and tlog backups every 5 minutes.

All backups support point-in-time recovery with a 12 hour RTO, and 5 minute RPO for in-region backups.

Max backup storage size is twice the tier database size limit (EG: Standard = 500GB of backup space). If exceeded a) request to reduce retention period. b) pay extra at standard read rate.

Long term backup archiving can be done using a) manual export to BACPAC on Azure Blob Storage. b) Long-Term-Retention to Azure Backup Vault for up to 10 years.

Restores

Databases can be restored (if within retention period) but Servers CANNOT. Further, all databases and their backups are deleted if you drop a Server.

Local restores can be to a point-in-time, can be renamed, but cannot overwrite an existing database.

Install Azure SQL Server and Databases

Portal click-by-click

1. Create SQL Server
– Home / SQL Servers / Add
– Subscription: “FT”
– Resource Group: “(New) SD-resource”
– Server Name “SD-server”
– Region: “(Europe) UK South” (London)
– Admin login “SD-login” / pw
– Allow Azure Services to access this server: “No”
– Enable advanced security: “Not now”
(wait 2 minutes)

2. Create SQL Database
– Home / SQL Databases / Add
– Database name: “SD-db”
– Elastic pool?: No
– Resources: “5 DTU’s (Basic)”
– Data source: “None” (blank database)
(wait 2 minute)
– Home / SQL Databases
– Database Features
– TDE: “Off”

3. Server level Connectivity
– Home / SQL Servers / Server name: “SD-server.database.windows.net” (cut)
– SSMS (paste) / SQL Server Authentication / SD-login / pw

3b. Messages
– “The requested name is valid, but no data of the requested type was found” =
server name was wrong.
– “New Firewall Rule” = your i/p is not on the server firewall list. Do you want to add it ? = Yes

4. (optional) database connectivity
a. exec sp_set_database_firewall_rule dbfwrule1, ‘86.168.15.119’, ‘86.168.15.119’;
b. select * from sys.database_firewall_rules
c. exec sp_delete_database_firewall_rule dbfwrule1

Notes
– Azure SQL DB offers 3 service tiers: Basic, Standard, and Premium.
– These tiers define performance and recovery.
– Performance is measured in DTU’s
– Multiple DB’s can share resources through Elastic Database Pools.

Postcode search

The issue was that some postcodes were stored with spaces and some without.

This was further complicated by some user inputs (into the “Postcode Search” SSRS Report) had a space and some did not.

The root cause of the slow report was that the 90 MILLION stored postcode was being retrieved and manipulated (to remove spaces) before being compared with the ONE input.

--- OLD CODE -----------------

DECLARE @PostCode VARCHAR(8)
SELECT Forename,
       Surname,
       AccountNumber AS CustomerNo,
       AccountStartDate,
       AddressLine2 AS Address,
       PostCode,
       DateOfBirth
FROM [dbo].[SV_Customers]
WHERE (CountryCode = 'GB')
      AND (REPLACE(Postcode, ' ', '') = @PostCode);

My insight was to manipulate just the ONE input postcode before comparing it TWICE (with and without a space) to the un-manipulated postcodes stored in the database.

The first task then, was to split the input postcode into two parts. In all formats the last 3 characters were number, letter, letter.

So after saving the last part of the postcode separately, it was easy to deduce that the first part must be the whole thing minus the last part.

--- NEW CODE ------------------------

DECLARE @PostCode VARCHAR(8)
DECLARE @pc2 CHAR(3) = RIGHT(@PostCode, 3);
DECLARE @pc1 VARCHAR(4) = RTRIM(REPLACE(@PostCode, @pc2, ''));

SELECT Forename,
       Surname,
       AccountNumber AS CustomerNo,
       AccountStartDate,
       AddressLine2 AS Address,
       Postcode,
       DateOfBirth
FROM [dbo].[SV_Customers]
WHERE CountryCode = 'GB'
      AND (PostCode = @pc1 + @pc2         -- without space
        OR PostCode = @pc1 + ' ' + @pc2); -- or with space

The final task was to write the WHERE clause as simply as possible for long term maintenance. That’s the DBA in me 🙂

T-SQL Window Functions

“Window” sounds a bit like the singular of Microsoft’s Operating System, huh?

But no, imagine that each cell in a spreadsheet has two little glass “Windows”, one in the ceiling of its cell and one in the floor.

Then the occupant of cell C3 could look up at C2 and wave, or down at C4 and blow a raspberry.

But there’s more, C3 can now look up and down past C2 and C4 at ALL the values in the C column.

Now instead of cells in a spreadsheet imagine cells in a database table.

create table #t1 (c int)
insert into #t1 values (10), (20), (30), (40)

select * from #t1

select *,
    lag(c, 1) over(order by c) [Waving up],
    lead(c, 1) over(order by c) [Rasberrying down],
    SUM(c) OVER() [Sum of c]
from #t1

drop table #t1

WindowResults3

Stored Procedure Template

I always try to adopt the local standards. But where I’m setting one, here’s my Stored Procedure starting template …

-- NewProcTemplate.sql

USE DemoDW
GO

/* ========================================================================
Author:		Richard (RbS)
Date:		19 July 2019
Usage:		To list SalesPeople by Store. 
Example:	Exec [DemoDW].[dbo].[SPU_DimSalespersonGetByStore] @Store = '1'
Safe4Prod:	NO! {by default}
============================================================================ */

ALTER proc SPU_DimSalespersonGetByStore -- SPU_{Object}{Action}
               @Store NVARCHAR(50)
AS
BEGIN; SET NOCOUNT ON;

 SELECT StoreName, SalespersonName
 FROM [DemoDW].[dbo].[DimSalesperson]
 WHERE StoreName = @Store;

END
GO

NOTE: I do not develop within this template. To stay open minded I always start development from a simple select star statement. Then when that’s all good, its pasted in here, parameterized, tested, and adjusted (thanks Doug).

Migration with Log-Shipping

I had a requirement to script a repeatable SQL 2014 ent to SQL 2016 std migration. This was to be for up to 200 databases and therefore needed to be automated.

I chose to encapsulate a blend of TSQL and Powershell in a non-scheduled SQL Job. And as we were going UP a version but DOWN an edition, I felt log-shipping would be the best option.

I idea was to run the job a week or so before hand. Then at the time of the migration (a weekend), just 15 minutes of data (per database) would need to traverse the network.

The SETUP job had 9 steps :-
1. Create a control table
2. Backup (because you never know)
3. Decrypt
4. Move Logins and Fix Orphans
5. Shrink the log-file
6. Log-Ship: Initial Full backup to remote-server
7. Log-Shipping: Initial Restores on Remote in recovery mode.
8. Log-Shipping: Create BACKUP jobs locally
9. Log-Shipping: Create COPY and RESTORE jobs remotely.

Step-1

-- 1.ControlTable.sql

USE msdb;
GO

IF OBJECT_ID('[msdb].[dbo].[LSList]') IS NOT NULL
    DROP TABLE [msdb].[dbo].[LSList];
GO

CREATE TABLE [msdb].[dbo].[LSList] ([database] NVARCHAR(255) NOT NULL,
                                    backup_directory NVARCHAR(255) NOT NULL,
                                    backup_share NVARCHAR(255) NOT NULL,
                                    backup_destination_directory NVARCHAR(255) NOT NULL,
                                    pre_mig_backup INT NOT NULL,
                                    is_encrypted INT NULL,
                                    LS_backup INT NULL,
                                    start_time_offset INT NOT NULL);

INSERT INTO [msdb].[dbo].[LSList] ([database],
                                   backup_directory,
                                   backup_share,
                                   backup_destination_directory,
                                   pre_mig_backup,
                                   start_time_offset)
VALUES (N'DatabaseName1', N'h:\shipping', N'\\LocalServerName\shipping', N'\\RemoteServerName\shipping', 0, 2);

INSERT INTO [msdb].[dbo].[LSList] ([database],
                                   backup_directory,
                                   backup_share,
                                   backup_destination_directory,
                                   pre_mig_backup,
                                   start_time_offset)
VALUES (N'DatabaseName2', N'h:\shipping', N'\\LocalServerName\shipping', N'\\RemoteServerName\shipping',0, 4);

-- populate encryption flag

UPDATE [msdb].[dbo].[LSList]
    SET is_encrypted = 1 -- yes
    WHERE [database] IN (SELECT db.[name]
                                          FROM sys.databases db
                                          JOIN sys.dm_database_encryption_keys dm
                                          ON db.database_id = dm.database_id );

-- select * FROM [msdb].[dbo].[LSList]

Step-2

-- 2.PreMigBackups.sql

-- select * from [msdb].[dbo].[LSList]
-- update [msdb].[dbo].[LSList] SET pre_mig_backup = 0

DECLARE @Query  NVARCHAR(MAX),
        @dbname VARCHAR(200);

WHILE (SELECT COUNT(*) FROM [msdb].[dbo].[LSList] WHERE pre_mig_backup = 0) > 0
BEGIN
    SET @dbname = (   SELECT TOP 1 [database]
                        FROM [msdb].[dbo].[LSList]
                       WHERE pre_mig_backup = 0);

    SET @Query = N'BACKUP DATABASE [' + @dbname + '] 
	TO  DISK = N''H:\SQL Backup\' + @dbname + '_' + replace(convert(varchar(16), getdate(),126), ':','') + '.bak'' 
	WITH COPY_ONLY, NOFORMAT, INIT,  STATS = 10';

    EXEC sp_executesql @Query;

    UPDATE [msdb].[dbo].[LSList]
    SET pre_mig_backup = 1
    WHERE [database] = @dbname;
END;

Step-3

-- 3.decrypt.sql

DECLARE @Query  NVARCHAR(MAX), @dbname VARCHAR(200);

/* 
 is_encrypted 
 null = no
 1 = yes
 0 = not any more
*/

WHILE (SELECT COUNT(*) FROM [msdb].[dbo].[LSList] WHERE is_encrypted = 1) > 0
BEGIN

    SET @dbname = (SELECT TOP 1 [database] FROM [msdb].[dbo].[LSList] WHERE is_encrypted = 1);

  /* 1 set encryption off */

    SET @Query = N'ALTER DATABASE [' + @dbname + N'] SET ENCRYPTION OFF;';
    EXEC sp_executesql @Query;

  /* 2 pause until decrypted */

    WHILE (  SELECT dm.encryption_state
                    FROM sys.databases db
                    LEFT JOIN sys.dm_database_encryption_keys dm
                       ON db.database_id = dm.database_id
                    WHERE [name] = @dbname)  1
    BEGIN
        WAITFOR DELAY '00:00:10';
    END;

  /*3 drop key */

    SET @Query = 'USE [' + @dbname + ']; DROP DATABASE ENCRYPTION KEY';
    EXEC sp_executesql @Query;

  /* 4 log changes then move on */

    UPDATE [msdb].[dbo].[LSList]
       SET is_encrypted = 0
     WHERE [database] = @dbname;

END;

-- Stop MLB

-- DECLARE @Query  NVARCHAR(MAX),
--        @dbname VARCHAR(200);

IF OBJECT_ID('tempdb..#tlist') IS NOT NULL
    DROP TABLE #tlist;
SELECT [database]
  INTO #tlist
  FROM [msdb].[dbo].[LSList];

WHILE (SELECT COUNT(*) FROM #tlist) > 0
BEGIN

    SET @dbname = (SELECT TOP 1 [database] FROM #tlist);

    SET @Query = N'EXEC [msdb].[smart_admin].[sp_set_db_backup]
					@database_name = [' + @dbname + N'],
					@enable_backup = 0'; -- off

    EXEC sp_executesql @Query;

    DELETE FROM #tlist
     WHERE [database] = @dbname;

END;

Step-4

Powershell.exe "Export-DbaLogin -SqlInstance LocalServerName -Append  -Path C:\temp\LocalServerName-logins.sql"

Powershell.exe "Export-DbaUser -SqlInstance LocalServerName -Append  -Path C:\temp\LocalServerName-users.sql"

Powershell.exe "Copy-DbaLogin -Source LocalServerName -Destination RemoteServerName -ExcludeSystemLogins"

Step-5

Powershell.exe "Repair-DbaOrphanUser -SqlInstance RemoteServerName"

Managed Backups

Managed Backups were a great new feature with SQL 2014 and above. They allow backups to the cloud and are managed from within SSMS.

There is a GUI but its just for initialization. Configuration all happens through TSQL. Here is my work sheet …

-- managedBackups.sql

-- view server config

USE msdb;
SELECT * FROM smart_admin.fn_backup_instance_config();

-- view server config details

USE msdb;SELECT db_name, is_managed_backup_enabled, retention_days, storage_url, encryption_algorithm
FROM smart_admin.fn_backup_db_config(NULL)

-- disable individual log backup

USE msdb;
EXEC smart_admin.sp_set_db_backup
@database_name = [DISC_Green_Abbey_8230003_test],
@enable_backup = 0;

Caching result sets

(For Sam) I wanted to performance tune a stored-procedure that was just one big SELECT statement (used to return all current Orders).

The code was just about as optimum as it could get, and returned around 8,000 rows each time, taking about 35 seconds to do so.

I saved the output over a few consecutive days and noticed (crucially) that most of the rows were the same each day.

My big-idea then, was to pre-cache (and pre-format) the results on “Day One”, and just append new rows to that going forward.

The final working stored-procedure contained 5 labeled areas:-

 - (1. Create and fill a cache-table if there isn't one)
 - 2. Save a thin version of the current data to a temp-table
 - 3. Add only NEW data to the cache-table
 - 4. Remove DELETED data from the cache-table
 - 5. Output the cache-table

1. If the cache-table didn’t exist, run the original query, but saving INTO a cache-table. Mostly this step was not executed, but I wanted the stored-procedure to be complete.

There was a DateTime column in the results set that was guaranteed to be unique. I made this the primary-key of the cache-table.

2. In a separate window, I stripped back the original query until just the DateTime column was returned. Unnecessarily, I added code to the top to delete any temp-table called “#thin” if it already existed (my habit). Then I added code to save the stripped back query results INTO a temp-table … called “#thin”.

This step would run every time, and the output could be compared with the old data (in the cache-table) to add any new rows, and knock off any old ones.

3. The original query was executed but with a WHERE clause added, like WHERE prod.DateTime not in (SELECT DateTime FROM #thin). The 2 or 3 (fat) rows returned from this step were appended to the cache-table.

4. A simple DELETE removed any rows from the cache-table where the DateTime was not in the #thin table.

5. The Cache-table was SELECT’ed in full as the stored-procedures output. Which typically ran in around 7 seconds. Despite the extra round-trip to the database.

Testing. After a day or two compare the old / new result sets in spreadsheet tabs and adjust indexing accordingly (As always, full responsibility lies with the implementer).

Addendum. To help performance I later changed Step-3 from …

WHERE prod.DateTime not in (SELECT DateTime FROM #thin)

… to …

LEFT JOIN cache.table cac ON cac.DateTime = prod.DateTime
WHERE cac.DateTime IS NULL

ORDER BY CASE

In TSQL I recently discovered how to use the CASE command in the ORDER BY clause to sort results in custom ways.

For example, to order countries with the UK and USA at the top then the rest alphabetically would in the past have caused me to either generate a calculated ‘CountrySort’ column or UNION two queries.

Now I can do this …

ORDER BY CASE
		WHEN countryid = 1 THEN 'AAA'
		WHEN countryid = 23 THEN 'AAB'
		ELSE countryname END

Which translates as …

‘Order by countryname
having first replaced the countryname with ‘AAA’ where the countryid is 1
and ‘AAB’ where its 23′.

Here are the results (including countryid for clarity)…

countrysort

Column Max Length

From my “Spreadsheet sizer” script, this one helped me move sensibly away from pesky varchar(max) columns.

-- ColumnMaxLength.sql

DECLARE @TableName VARCHAR(255) = 'customers' --<< input
DECLARE @SchemaName VARCHAR(255) = 'dbo' 
DECLARE @sqlcmd varchar(max) 

select @sqlcmd = stuff((SELECT ' union all
select ' 
+ QUOTENAME(table_schema,'''') + ' [Schema], ' 
+ QUOTENAME(TABLE_NAME,'''') + ' [Table], ' 
+ quotename(column_name,'''') + ' [Column],
max(datalength(' + quotename(column_name) + ')) MaxLength 
from ' + quotename(table_schema) + '.' + quotename(table_name)
from information_schema.columns
where 1=1
AND table_name =  @TableName
AND table_schema = @SchemaName
order by column_name
for xml path(''),type).value('.','varchar(max)'),1,11,'')

exec(@sqlcmd)