As a child/pre-teen/teenager/young adult, OK, most of my life, I’ve been labeled as “serious” or “grumpy”. Maybe I deserve it sometimes – when I’m deep in thought or focused on something, I don’t like to be bothered. I also have a low tolerance for drama or “stuff that doesn’t interest me”, like sports, Justin Bieber, or America’s Next Top Model. However, I like to think that I also have a pretty good sense of humor. In fact, I think I’m hilarious.

It’s T-SQL Tuesday time again, and the topic this month is “Why So Serious?”. In honor of April Fool’s Day, the assignment is to write about a SQL-related prank that you’ve pulled on your coworkers. I’ve got a good one!

Magic Mirror

I’m not going to describe how to set this up, you can find tons of information on AlwaysOn yourself. All that you need to know in order to follow along is that I have two servers, DBSDEV1A and DBSDEV1C. These two servers are part of an AlwaysOn availability group – DBSDEV1A is the primary, DBSDEV1C is the secondary. Between them is mirrored a database named VueDev, available as a readable secondary on DBSDEV1C.

I made the claim to my fellow DBAs and developers that I had found a way to write to the read-only mirror on DBSDEV1C. I even wrote a script to reproduce my claim, showing that I could indeed insert data into the database object named dbo.WriteableMirrorTable in the VueDev database residing on DBSDEV1C (the secondary replica).

-- Run these statements on the secondary replica
SELECT @@SERVERNAME, DB_NAME();
SELECT * FROM dbo.WriteableMirrorTable;
INSERT INTO dbo. WriteableMirrorTable(RowValue) VALUES ('TracyTest');
SELECT * FROM dbo.WriteableMirrorTable;

The output from these statements (shown below) shows that:

1. We are indeed on the secondary replica, in the read-only database named VueDev.
2. Prior to the INSERT statement, there is only one row in dbo.WriteableMirrorTable.
3. After the INSERT statement, there are now two rows in dbo.WriteableMirrorTable.

I have, indeed, written data to the read-only secondary database! How was I able to achieve such an astonishing feat? Some things are better left unexplained, so I’ll just wrap this up by sharing a bit of T-SQL code with you. You can figure it out from here…

-- Create this table on the primary replica
CREATE TABLE WriteableMirrorBaseTable
(
RowID INT IDENTITY(1 ,1),
RowValue VARCHAR(25 ),
RowDate DATETIME CONSTRAINT DF_WriteableMirrorBaseTable_RowDate DEFAULT ( GETDATE())
);
GO
-- Create this view on the primary replica
CREATE VIEW WriteableMirrorTable
AS
SELECT RowID , RowValue , RowDate
FROM DBSDEV1A .VueDev. dbo.WriteableMirrorBaseTable ;
GO
-- Run these statements on the secondary replica
SELECT @@SERVERNAME , DB_NAME ();
SELECT * FROM dbo.WriteableMirrorTable ;
INSERT INTO dbo. WriteableMirrorTable ( RowValue) VALUES ('TracyTest' );
SELECT * FROM dbo.WriteableMirrorTable ;