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 :).

Here 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 enjoy performance tuning and feel I bring a holistic perspective to the task. When I have time I like to reverse engineer slow code – rather than just refactoring the slow bits :).

Below, I will mostly 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.

Who needs a Data Architect

Adding “Technical Credit” to a project (that is the opposite of “Technical Debt”) makes it a pleasure to work with it in the future. As everything just seems so …easy.

However, it takes discipline to complete a project, only to set aside a perfectly working solution, and build it again from the ground up. Not tweaking the first build but armed by the memory of it. And repeat 🙂

The soft machines that manage our data naturally evolve towards complexity unless we actively stop it. Anyone who has ever had to work with “open source” will know the pain of “patch city” where there is no sign of what was once (probably) an elegant core. Open source solutions are open source because they have no central governance, or big picture planning.

Who changed the data?

Frankly I was stumped! I had been through all the procedures that mentioned the table, I had scrolled through jobs, and audit logs. I had even looked through code embedded in reports. But I just could not see how a Customers title was being changed from ‘Mrs’ to ‘Ms’ in a report.

My last resort, and one I wish I had thought of sooner, was to put a trigger on the table to stop the specific text appearing. Here is the code:-

CREATE TRIGGER [dbo].[SALES_BY_SITE_stop] 
ON [dbo].[SALES_BY_SITE] 
AFTER INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT 1 FROM INSERTED 
   WHERE Customer = 'Ms SomeName')
   BEGIN
      ROLLBACK TRANSACTION
      RAISERROR ('"Ms SomeName" was blocked',16,1)
   END
END

Now it has to be said – it is generally bad practice to break something to fix something.

Luckily, it turned out that within a few minutes an email popped-up to say a job had failed. So I immediately removed the trigger and re-ran the job.

I then spent the next few hours trawling through some procedures and views to a base table that contained … the incorrect data. Cake time! 🙂

Practical use of indexes

I treat non-clustered indexes as “shadow copies” of a table, but ordered differently.

Just like in a spreadsheet, a database table is a grid of data. But unlike a spreadsheet you cannot change the order by clicking on a heading with a mouse.

So by creating differently ordered copies of a table you can improve the performance of specific queries.

Additionally, you can pare down these shadow copies to just the columns and rows your query need. Like photographing fish in a barrel 🙂

The down side of having multiple copies of a table, is that they each individually need to be kept in sync whenever the data changes.

“Authors” quiz with answers

For Cliffton. I hope it helps.

/* AuthorsWithMoreThanOneBook.sql */


/* 1 create the table */
DROP TABLE IF EXISTS #authors;
CREATE TABLE #authors 
(auth_name VARCHAR(50),
 book_title VARCHAR(50));


/* 2 add some data */
INSERT INTO #authors VALUES
('Isaac Asimov', 'iRobot'),
('Ben Bova', 'The towers of Titan'),
('Ben Bova', 'A long way back');


/* 3 confirm the table contents */
SELECT * FROM #authors;


/* 4 answer using the HAVING clause */
SELECT auth_name
FROM #authors
GROUP BY auth_name
HAVING COUNT(*) > 1;


/* 5 answer using a WINDOW function */
SELECT auth_name
FROM
(SELECT auth_name,
    ROW_NUMBER() OVER (PARTITION BY auth_name 
    ORDER BY (SELECT book_title)) AS book_count
 FROM #authors
) AS qty
WHERE book_count > 1;

Academic Year function

As an exercise in ‘Clean code’ this is about the best I could manage:-

--fnGetAcademicYear.sql


/* Returns academic year of supplied date */
CREATE FUNCTION [dbo].[fn_Get_AcademicYear]
(
   @in DATE
)
RETURNS INT
AS
BEGIN

  DECLARE @out INT;

  SELECT @out=CASE WHEN DATEPART(MONTH, @in) > 7 
              THEN CONCAT(YEAR(@in), YEAR(@in) + 1)
              ELSE CONCAT(YEAR(@in) - 1, YEAR(@in))
              END;

  RETURN @out;

END;
GO

With these sorts of things, traditionally you say item1 + item2 + number, where you want item1 bolted onto item2, and item2 to have had some maths done to it. The ‘+’ symbol serving as both a combiner and a mathematical operator.

To force ‘+’ to be a combiner and not a maths operator you use CONVERT or CAST to change the objects to text strings.

Happily CONCAT is an alternative we can use to get away from the confusing dual purpose of ‘+’. And clean up all those conversions. This operator only joins items together, and never does maths on them. 🙂

Updating a table from SSRS

It’s relatively straight forward to write to a table from an SSRS report once you realise that every time a report is refreshed it tries to run any code it finds in its datasets. This gives us the opportunity to go “off campus”.

Consider a report with a drop-down list called “Change”, containing three choices “View” (the default), “Add”, and “Remove”. And a dataset containing … 

    IF @Change = 'ADD'
        INSERT INTO [dbo].[SomeTable]
        VALUES (@Param1, @Param2);

    IF @Change = 'REMOVE'
        DELETE FROM [dbo].[SomeTable]
        WHERE Column1 = @Param1
		AND Column2 = @Param2;

	SELECT * FROM [dbo].[SomeTable];

You could open the report. Paste some values into Param1 and Param2. Choose “Add” from the drop-down. Then click “Refresh Report”.

And maybe limit access 🙂

SSRS return TOP(n) rows

Although I cannot show the slow report that returned 4729 pages (shame ;)) I can use the generic ReportServer database for a demo.

It’s all about timing. First we need a parameter called param_rows that is an integer with a default value of -say- 100.

Next we need a dataset where param_rows is nested …

-- SlowestReports.sql

DECLARE @Rows INT = COALESCE(@param_rows, 100)

SELECT TOP(@Rows) ItemPath Report,
       TimeStart,
       TimeDataRetrieval
FROM [ReportServer].[dbo].[ExecutionLog3]
WHERE ItemPath <> ''
AND ItemAction = 'Render'
AND RequestType = 'Interactive'
AND Status = 'rsSuccess'
AND TimeStart BETWEEN @FromDate AND @ToDate + 1
ORDER BY TimeDataRetrieval DESC;

Then we can abuse the date range, with performance balanced by the number of rows returned (unlike “Filter” which returns all the rows but limits those displayed) …

Ok abuse doesn’t really work with ReportServer (that only holds 60 days by default) but you get the idea 🙂

Moving TempDB

I have only had to do this a few times in a VM but want to capture the steps. Note that with Azure VM – The New datafiles will be the same size as the Old datafiles. 

/* MovingTempDB.sql */
 
/* 1 save the current catalog paths to "rollback" (just-in-case) */
 
    USE master;
    GO
	select space(4) + 'USE master;' cmd
	union
	select space(4) + 'GO'
	union
    SELECT space(4) + 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + name + '], FILENAME = [' + physical_name + ']);'
    FROM sys.master_files 
    WHERE database_id = DB_ID(N'TempDB')
    ORDER BY cmd desc;

/* 2 manually create the "new" destination folder(s) */
  
/* 3 change the catalog */
 
    USE master;
    GO
    ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [D:\TempDB\tempdb.mdf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [D:\TempDB\tempdb_2.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [D:\TempDB\tempdb_3.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [D:\TempDB\tempdb_4.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [D:\TempDB\tempdb_5.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [D:\TempDB\tempdb_6.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [D:\TempDB\tempdb_7.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [D:\TempDB\tempdb_8.ndf]);
 
    /* confirmation message
    "The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started." */
   
/* 4 manually reboot the server */
 
/* 5 manually check the new files have been created */
  
/* 6 manually delete old files, and empty the recycle-bin */

/* 7 ideally pre-grow all tempdb data files */

/* x rollback if needed */

    USE master;
    GO
	ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = [F:\SQLLogs\templog.ldf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [H:\TempDB\tempdb.mdf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [H:\TempDB\tempdb_2.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [H:\TempDB\tempdb_3.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [H:\TempDB\tempdb_4.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [H:\TempDB\tempdb_5.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [H:\TempDB\tempdb_6.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [H:\TempDB\tempdb_7.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [H:\TempDB\tempdb_8.ndf]);

Azure database backups

I was unable to find a listing of SQL database backups in the Azure Portal. Eventually I found this helpful DMV.

--AzureDatabaseBackups.sql

SELECT @@SERVERNAME server_name,
       DB_NAME() database_name,
       backup_start_date,
       backup_finish_date,
       CASE backup_type 
	       WHEN 'D' THEN 'Full'
           WHEN 'I' THEN 'Differential'
           WHEN 'L' THEN 'Transaction Log'
           ELSE '' END backup_type
FROM sys.dm_database_backups
WHERE in_retention = 1 /* Available */
ORDER BY backup_start_date DESC;

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.

DTU’s ≡ Money

Stored Procedure Template

Normally I adopt the local standard, but failing that here is my current Stored Procedure template suitable for all cloud environments. This year I have gone ultra simple 🙂

-- NewProcName.sql

USE [SomeDbName];
GO

/* =====================================
   Author:		Richard
   Date:		10 Jul 2023
   Description: What it should do
   ===================================== */

CREATE PROCEDURE [schema].[NewProcName]
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

		/* Do things */

END
GO

Login with no default database

This problem can occur when databases are dropped or renamed. Here is my query that will list all logins where the default database is missing (or offline):-

SELECT [name] Login,
       default_database_name MissingDefaultDB
FROM sys.server_principals
WHERE DATABASEPROPERTYEX(default_database_name, 'Status') IS NULL
AND default_database_name IS NOT NULL
ORDER BY [name];

Removing duplicates from a large table before adding a clustered primary key

The challenge with this one, was to remove over 2,000 duplicate rows from a 600,000,000 row heap, before a multi-column clustered primary key could be added.

(I am so happy to finally be able to document this, from a while ago. The phrase “uniquely challenging” was never so apt.)

My first idea was to create a empty copy of the source table, add a “ignore duplicates” index, then start an INSERT [dbo].[targettable] SELECT * FROM [dbo].[sourcetable]. However, I stopped after an hour as it was going to take too long.

My second idea was to use SSIS to populate the target-table. Again this was just too slow.

My third idea was to just remove the duplicates (2,000 being a much smaller number than 600,000,000). First though, a disclaimer, I only considered this ** high risk ** solution because the data was in a QA environment (not Live), and people were waiting for me.

Step one then, was to populate a control-table with the duplicates …

SELECT column1, column2, column3, column4, count(*)
INTO #tmp1
FROM [dbo].[sometablename]
GROUP BY column1, column2, column3, column4
HAVING COUNT(*) > 1;

(“column1” etc were not the real column names by-the-way 🙂 ). Step two was to loop through this list removing duplicates from the permanent table

WHILE exists (SELECT 1 FROM #tmp1)
BEGIN

	DECLARE @1 BIGINT, @2 INT, @3 BIGINT
	DECLARE @4 VARCHAR(10)

	SELECT TOP(1) 
		@1 = column1, 
		@2 = column2, 
		@3 = column3, 
		@4 = column4 
	FROM #tmp1

	DELETE TOP(1) 
	FROM [dbo].[sometablename] 
	WHERE column1 = @1
	AND column2 = @2
	AND column3 = @3
	AND column4 = @4

	DELETE 
	FROM #tmp1
	WHERE column1 = @1
	AND column2 = @2
	AND column3 = @3
	AND column4 = @4

END

Step three was to implement the clustered primary key. (by-the-way I was half expecting this to fail with a duplicates error, but happily the above loop had cleared a single duplicate from each pair, and thats all that was needed).

ALTER TABLE [dbo].[sometablename] 
ADD CONSTRAINT pk_sometablename
 PRIMARY KEY CLUSTERED
 ([column1], [column2], [column3], [column4]);

** just to point out – I consider the loop “high risk” because, an error message, a typo, accidentally running it twice, or faulty logic, could have resulted in disaster 🙂

Resource Governor

The Resource Governor is one of those rarely used tools, as its impact is inherently ‘negative’. It restricts resources from a user-group, but cannot be seen to boost performance of a more favoured group.

For example, you could restrict the SA login to use just 5% of the CPU resource. The effect of this would be that SA can use 100% of the CPU resource, until another user-group wants to use more CPU than is currently available. SA will then be throttled back, and back, until it is only using 5%. Five percent is the minimum SA can use.

It is not easy to see this throttling in action – without firing up perf-mon. Also, the GUI view within SSMS is not-so-good, showing only some of the configuration. Therefore this is one of those things that I work with completely at the code level.

So, here then is my crib-sheet …

-- ResPool.sql

-- 1 create custom resource pool of available hardware
USE master;
GO
CREATE RESOURCE POOL MxResourcePool WITH
(
	MIN_CPU_PERCENT = 0,
	MAX_CPU_PERCENT = 5
);
GO

-- 2 create a group and link it to the resource pool
USE master;
GO
CREATE WORKLOAD GROUP MxWorkloadGroup
USING MxResourcePool;
GO

-- 3 populate the group
USE master;
GO
CREATE FUNCTION dbo.fn_MxClassifier()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
DECLARE @wg AS sysname
IF SUSER_NAME() = 'sa'
	SET @wg = 'MxWorkloadGroup'
ELSE
	SET @wg = 'default'

RETURN @wg
END;
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_MxClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- check config

SELECT s.session_id,
s.host_name,
s.program_name,
s.nt_user_name,
s.login_name, 
w.name WorkgroupAssignment,
r.name ResourcePoolAssignment

FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups w
  ON s.group_id = w.group_id
JOIN sys.dm_resource_governor_resource_pools r
  ON w.pool_id = r.pool_id
WHERE s.host_name IS NOT NULL
ORDER BY nt_user_name-- s.session_id desc

-- undo

ALTER RESOURCE GOVERNOR DISABLE;
DROP WORKLOAD GROUP MxWorkloadGroup;
DROP RESOURCE POOL MxResourcePool;
DROP FUNCTION dbo.fn_MxClassifier;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

To sync a materialized view

To manually keep a results cache updated I recommend concatenating multi column primary keys rather than using full joins. If it looks simple it should last.

/* 1 Insert */

INSERT INTO 
	[mv].[sometablename]
SELECT 
	*
FROM 
	[vw].[vw_someviewname]
WHERE
	[pkcomposite1] + [pkcomposite2]
NOT IN
	(
	SELECT
		[pkcomposite1] + [pkcomposite2]
	FROM
		[mv].[sometablename]
	);


/* 2 Delete */

DELETE FROM
	[mv].[sometablename]
WHERE
	[pkcomposite1] + [pkcomposite2]
NOT IN
	(
	SELECT
		[pkcomposite1] + [pkcomposite2]
	FROM
		[vw].[vw_someviewname]
	);


/* 3 Update */

UPDATE
	MAT
SET
	MAT.[somecolumn1] = VW.[somecolumn1],
	MAT.[somecolumn2] = VW.[somecolumn2]
FROM
	[mv].[sometablename] MAT
JOIN
	[vw].[vw_someviewname] VW
ON
	MAT.[pkcomposite1] + MAT.[pkcomposite2]
	=
	VW.[pkcomposite1] + VW.[pkcomposite2]
WHERE
	MAT.[somecolumn1] <> VW.[somecolumn1]
OR
	MAT.[somecolumn1] <> VW.[somecolumn2];

Log shipping to SSRS

I like simplicity, and job schedules can add a ton of it. I set up one job on the primary server to backup logs, and one job on the secondary server to restore them from the primary share.

Both schedules were set to – every 15 minutes between 18:00 and 08:00 – so no overhead during the working day.

Incidentally, so I would never have to manually fix log shipping of a 2 TB database, I set up a 01:00 [full backup] job on the primary …

Which on completion would start a [full restore] job on the secondary (to “standby”)…

The first step of the [full backup] job was to check log shipping latency, and only proceed if it were over 2 hours.

Normally log shipping would run all night and the secondary server would be updated to 08:00. If there were an issue then the [full backup] / [full restore] jobs would “fix” log shipping and again the secondary would be updated to 08:00. If there were a more fundamental failure then the [full backup] / [full restore] jobs alone, would update the secondary to 01:00.

The final touches were to set the [full restore] job to notify me if it were ever executed, and to remove all notifications from the [log restore] job 🙂

Moving datafiles

Just so I can find this script when I need it …

--MoveDataFile.sql

USE master;
GO

-- 1 alter the metadata to the new path

ALTER DATABASE [SomeDatabaseName] MODIFY FILE (NAME = SomeLogicalFileName, FILENAME = 'F:\SQLData\SomeFileName.mdf');

-- 2 take database offline

ALTER DATABASE [SomeDatabaseName] SET OFFLINE;

-- 3 move the datafile via windows explorer

-- 4 bring database online

ALTER DATABASE [SomeDatabaseName] SET ONLINE;

Recovery Interval not optimal

The SQL Server “Recovery Time Interval” setting used to be ‘0’ by default, which could have a performance impact during a Checkpoint, by amplifying lazywriter contention.

Indirect checkpointing can alleviate this and has been available since SQL Server 2012. Indeed the default value was changed to ’60’ (seconds) from SQL Server 2016 onwards.

Splitting strings in practice.

Quite often on technical forums a request to extract part of a text string is tackled in splendid isolation. I’ve used the technique below with postcodes before, but this time wanted to split the text in an SSRS log file to isolate report names from paths.

For example the string …

/Finance/Monthly/Outgoing/Summary

… means the report called “Summary” is contained in the “Outgoing” folder within the “Monthly” folder inside the “Finance” folder in the root.

The tricky bit being that the nesting levels vary. There are reports in root. And there are reports buried ten layers deep like russian dolls.

Task one, getting the report names is relatively easy. You could use the REVERSE function to help grab the text up to the “first” slash using CHARINDEX.

Task two, getting the path, is where forums go deep, with strings of functions to extract all the text to the left of the last slash (where there are an unknown number of slashes).

Where as, it is often far simpler to use REPLACE to remove the already found report name from the whole string, leaving just the path.

SELECT
       Report,
       REPLACE(ItemPath,Report,'') [Path],
       ...
FROM
(
    SELECT RIGHT(ItemPath, CHARINDEX ('/', REVERSE(ItemPath))-1) Report,
           ...

Cannot drop user as it owns a schema

Whilst removing orphaned user accounts, I came across this error …

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Looking at the properties of the user within the database I noticed that they owned the db_datawriter schema. Within the properties of that schema I was able to type db_datawriter as the new owner. After which I was able to remove the user from the database.

Here is a scripted way to do this …

https://www.mssqltips.com/sqlservertip/3439/script-to-drop-all-orphaned-sql-server-database-users/

Testing Read-Only Routing using SSMS

There are much better and easier ways to test a read-only routing configuration of an Availability Group. But here is my walk-through using SQL Server Management Studio.

  1. In SSMS connect to “Database Engine”
  2. Type in the name of the listener, a comma, then the port used by the listener
  3. Click the “Options” button
  4. On the Connection Properties tab choose “Connect to Database” then “Browse Server”
  5. Choose a database protected by the AG
  6. On the Additional Parameters tab type “ApplicationIntent=ReadOnly”
  7. Click “Connect”
  8. Run a read-only query like “SELECT @@SERVERNAME”
  9. The result should be the Secondary instance (AKA secondary replica)
  10. Delete the query-pane and connection
  11. Then set it up again – but without “ApplicationIntent=ReadOnly”
  12. Now the result of the query should be the Primary replica.

BONUS

The “much better ways” include using SQLCMD from another server. For example running this should return the name of the Secondary replica.

sqlcmd -S (ListenerName),(ListnerPort) 
-d (ProtectedDatabaseName) 
-q "SELECT @@SERVERNAME;" 
-K ReadOnly

Then, running it again without the last line should return the name of the Primary replica.

Note: It does not matter which Listener/Port you choose if there is more than one.

Todays failed jobs

I cobbled this together to run on multiply servers via CMS. It is simpler and more trustworthy than alerts from monitoring-software.

--JobFailures.sql

SELECT
    J.name,
	H.FailedAt,
    H.Message
FROM
    msdb.dbo.sysjobs AS J
    CROSS APPLY (
        SELECT TOP(1)
            FailedAt = msdb.dbo.agent_datetime(T.run_date, T.run_time),
            Message = T.message
        FROM
            msdb.dbo.sysjobhistory AS T
        WHERE
            T.job_id = J.job_id
		AND
			T.run_status = 0 -- failed
		AND
			msdb.dbo.agent_datetime(T.run_date, T.run_time) > getdate()-1 -- in the last 24 hrs
--			msdb.dbo.agent_datetime(T.run_date, T.run_time) > getdate()-3 -- covering the weekend
        ORDER BY
            T.instance_id) H

Original code from here …

https://stackoverflow.com/questions/54215008/sql-agent-job-last-run-status

Quick row count

When COUNT(*) was too slow to get total rows from a billion row heap …

SELECT MAX(REPLACE(CONVERT(VARCHAR(20), 
       CONVERT(MONEY, rowcnt), 1), '.00', '')) [rows]
FROM sys.sysindexes
WHERE OBJECT_NAME(id) = 'TableName';

Original code from here …

https://www.i-programmer.info/programming/database/6576-sql-server-quickly-get-row-counts-for-tables-heaps-indexes-and-partitions.html

Adding a database to an Azure backup policy

Twice I had to ask how to do this – so it is probably time for me to write it down.

Setting the scene – this was a virtual SQL Server hosted on Azure where I had already configured a backup policy within the Portal, and then wanted to add another database to that policy.

Clicking around in the Backup center and Backup policy screens did not help. The trick was to go through the motions of taking a backup.

  1. Within the Backup center click “+ Backup”

2. On the next screen make sure the Datasource type is “SQL in Azure VM” before selecting the right vault.

3. Choose “Configure Backup” …

4. Click “Add” …

5. Then – you can choose the Instance (or AlwaysOn AG), database, then Enable Backup. Simples 🙂

Removing Partitions

Remembering that indexes are partitioned not tables, I used this query to list them …

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS [schema],
       OBJECT_NAME(i.object_id) AS [object],
       i.name AS [index],
       s.name AS [partition_scheme]
FROM sys.indexes i
JOIN sys.partition_schemes s
  ON i.data_space_id = s.data_space_id;

I found the quickest way to remove partitioning is :-

  • Make a copy of the partitioned table with all the data (EG: “tablename2”)
  • Recreate all indexes, keys, and constraints (specifying the “ON [PRIMARY]” option)
  • Drop the partitioned table
  • Rename the copy to the table that was just dropped
  • Drop any partition schemas and functions
SELECT * FROM sys.partition_schemes
--DROP PARTITION SCHEME somename

SELECT * FROM sys.partition_functions
--DROP PARTITION FUNCTION somename

Replicating data from SQL Server to MySQL

Ok not really “Replication”. More like keeping a MySQL table in sync with a SQL Server table using both varieties of SQL.

I avoided using a linked-server as I wanted this to be able to cope with bulk loading (sadly SSIS was not available).

I created a SQL Job with two steps 1) Export data to CSV and 2) Import into MySQL. The SQL code is highly parameterised so it can be reused.

Job Step 1: Export data to a CSV file

/* ExportDataToCsvFile.sql */

	DECLARE @datasource VARCHAR(100) = 'SomeTableName';
	DECLARE @cmd VARCHAR(400);
	SELECT @cmd = 'BCP SomeDatabase.dbo.' + @datasource + ' out D:\Export\' + @datasource + '.csv -t, -c -T';
	EXEC master..xp_cmdshell @cmd;

Line 1: Is just to let me know I have my own copy of this code block.
Line 3: Should be updated to the data source specific to each project (NOTE: For simplicity the data-source and CSV file both share this name.)

Job Step 2: Import CSV into MySQL table

/* ImportCsvIntoMYSqlTable.sql */

	DECLARE @table VARCHAR(100) = 'SomeTable'; /* << change this */
	DECLARE @database VARCHAR(100) = 'SomeInstance_SomeDatabase'; /* << change this */
	DECLARE @sql VARCHAR(2000) = '';
	DECLARE @cmd VARCHAR(8000);
	DECLARE @IsError INT;


/* 1 Build MySQL script to empty then refill table */

	SET @sql = @sql + 'START TRANSACTION;';
	SET @sql = @sql + 'DELETE FROM ' + @table + ';';
	SET @sql = @sql + 'LOAD DATA LOCAL INFILE ''G:\\Export\\' + @table + '.csv'' INTO TABLE ' + @table
	SET @sql = @sql + ' FIELDS TERMINATED BY '','' ENCLOSED BY ''\"'' LINES TERMINATED BY ''\r\n'';';
	SET @sql = @sql + 'COMMIT;';


/* 2 Execute it */

	SET @cmd = 'G:\Export\MySql\bin\mysql.exe --defaults-extra-file=G:\Export\MySql\'' + @database + '.cnf -e "' + @sql + '";';
	EXEC @IsError = master..xp_cmdshell @cmd;
	IF @IsError <> 0 RAISERROR('INFILE Error', 16, 1);


/* 3 Defragment table and Update stats */

	SET @sql = 'OPTIMIZE TABLE ' + @table + ';';
	SET @cmd = 'G:\Export\MySql\bin\mysql.exe --defaults-extra-file=G:\Export\MySql\' + @database + '.cnf -e "' + @sql + '";';
	EXEC @IsError = master..xp_cmdshell @cmd;
	IF @IsError <> 0 RAISERROR('OPTIMIZE Error', 16, 1);

Lines 3: Will need to be changed for each project. And names both the CSV file and target MySQL table.

Line 4: Is a previously created file used by MySQL to connect to a target instance & database.

Lines 12 to 16: Builds up a string of MySQL commands to empty then refill the table from the CSV file.

Lines 12 & 16: These two lines create a single transaction. This serves two purposes. 1) The ‘old data’ will not be deleted if the ‘new data’ load fails. 2) The switch-over from ‘old data’ to ‘new data’ will be instant.

Send an Email with a PDF attached

There are many posts on how to automatically generate of a PDF receipt and email it by leveraging SSRS. Here is how it went for me.

  1. Create a report that shows the details required.
  2. Create a parameters table.
  3. Subscribe to the report using the parameters table.
  4. Create a stored-procedure to populate and fire the subscription.

On a version of SQL Server Reporting Services (SSRS) that supports data-driven-subscriptions (DDS) I created a report called ‘SingleInvoice’ with one input parameter ‘invoice number’.

Outside of this report, in preparation for the DDS, I created a data source pointing to the local [ReportServer] database.

Within the [ReportServer] database I created a table called [dbo].[InvoiceParams]

CREATE TABLE [dbo].[InvoiceParams](
	[InvoiceNumber] [VARCHAR](100) NULL,
	[ToEmailAddress] [VARCHAR](200) NULL,
	[CCEmailAddress] [VARCHAR](200) NULL,
	[BccEmailAddress] [VARCHAR](200) NULL,
	[ReplyToEmailAddress] [VARCHAR](200) NULL,
	[IncludeReport] [BIT] NULL,
	[RenderFormat] [VARCHAR](20) NULL,
	[Priority] [VARCHAR](15) NULL,
	[Subject] [VARCHAR](150) NULL,
	[Comment] [VARCHAR](150) NULL,
	[IncludeLink] [BIT] NULL,
	[Active] [BIT] NULL,
	[DateInserted] [DATETIME] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT (NULL) FOR [CCEmailAddress]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT (NULL) FOR [BccEmailAddress]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT (NULL) FOR [ReplyToEmailAddress]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT ((1)) FOR [IncludeReport]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT ('PDF') FOR [RenderFormat]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT ('Normal') FOR [Priority]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT ((0)) FOR [IncludeLink]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT ((1)) FOR [Active]
ALTER TABLE [dbo].[InvoiceParams] ADD  DEFAULT (GETDATE()) FOR [DateInserted]
GO

To simplify the stored procedure I defined default values for all the columns I would not be dynamically populating.

Next I created a Data-driven subscription on the report with a schedule in the past – so it would never fire. For Destination I chose E-Mail.

Within the subscription I edited the dataset and chose the previously created shared data source [ReportServer].

I added this query before clicking ‘Apply’….

SELECT * 
FROM dbo.InvoiceParams
WHERE Active = 1;

Back in the New Subscription form, I completed the Delivery options like this …

Within the user database I created this stored-procedure …

/*==================================================
  Author:		Richard Smith
  Create date:	10 Jul 2020
  Description:	To Email PDF receipts - demo version
  Test: Exec [dbo].[EmailReceipts] 'INV123456789',
            'Richard.Smith@company.com'
  =================================================*/

ALTER PROC [dbo].[EmailReceipts]
    @InvoiceNumber VARCHAR(100),
    @ToEmailAddress VARCHAR(200),
    @Subject VARCHAR(150) = 'test subject',
    @Comment VARCHAR(150) = 'test body',
    @SubscriptionID NVARCHAR(260) = '987654321' 
                       /* Report = "SingleInvoice" */
AS
BEGIN
    SET NOCOUNT ON;


/* 1 Save the inputs */

    INSERT INTO [ReportServer].[dbo].[InvoiceParams] 
            (InvoiceNumber, ToEmailAddress, [Subject], Comment)
    VALUES (@InvoiceNumber, @ToEmailAddress, @Subject, @Comment);


/* 2 Trigger subscription. Which will send the report (+ inputs) to the email-subsystem-queue */

    EXEC [ReportServer].[dbo].[AddEvent] @EventType = 'TimedSubscription', @EventData = @SubscriptionID;
    WAITFOR DELAY '00:00:10';


/* 3 If no longer in queue, flag as sent */

    IF NOT EXISTS (SELECT 1 FROM [ReportServer].[dbo].[Event] WHERE EventData = @SubscriptionID)
        UPDATE [ReportServer].[dbo].[InvoiceParams] 
        SET Active = 0
        WHERE InvoiceNumber = @InvoiceNumber
        AND ToEmailAddress = @ToEmailAddress;


/* 4 Manage the log */

	DELETE FROM [ReportServer].[dbo].[InvoiceParams] WHERE DateInserted < GETDATE()-30;
	SELECT * FROM [ReportServer].[dbo].[InvoiceParams] ORDER BY DateInserted DESC;

END;
GO

When executed with an email address and invoice number this stored procedure will send an email to the email address with the PDF invoice attached.

NOTE

To find @SubcriptionID I used this …

SELECT SCH.SubscriptionID
FROM [ReportServer].[dbo].[Catalog] CAT
JOIN [ReportServer].[dbo].[ReportSchedule] SCH
  ON CAT.ItemID = SCH.ReportID
WHERE CAT.Path= '/ReportPath/ReportName';

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;

GCloud cheat sheet

GCloud is the native language of Cloud Shell. Cloud Shell is the command line interface of Google Cloud Platform. Here are some examples …

gcloud sql connect someinstancename — Connect to a SQL Server instance.

use “somedatabase” — Change (from default Master) to a named database. Don’t forget the double-quotes.

select @@version — Display the SQL Server version, patch level, and edition. Also the operating system.

Except and Intersect

Here is the simplest working example of EXCEPT and INTERSECT I can come up with …

/* Except.sql */

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

IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2;
CREATE TABLE #t2 (c2 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 */

SELECT * FROM #T1
EXCEPT
SELECT * FROM #T2
UNION
(SELECT * FROM #T2
EXCEPT
SELECT * FROM #T1); /* = 1 & 3 */

I use this frequently whilst refactoring to check the outputs are identical. And sometimes 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 (against each user database) 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';

To fix this you can either remove the orphaned user account or create a matching login. Removing a user account is tricky to script as you need to remove individual attributes first.

Creating a login depends on the type, SQL or Windows. For SQL logins paste the name, SID, and password* into this command and execute it.

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

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

To create a Windows login use this command …

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

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.