Automatic Page Repair With SQL Server Database Mirroring - Part I

Do we need to worry about the page corruption ?

May be Yes! 

As you may know - Data are stored and maintained by the SQL Server as Page(s). Right ?

A page can have up to 8 KB of data (8192 Bytes)

When I try to fetch the data from a table, The SQL Server database engine traverse through the pages what are allocated to the tables data and flush the appropriate records to the client (Management Studio or request end-point). But,

If something goes wrong at internal structure which the system may not be able to traverse through the pages we requested because of the logical inconsistent among the pages - May end with an Error called Logical Inconstant with I/O operations!

But, The page corruption occurs in the various levels as follows
Data Pages
Index Pages
Text/Image
File header page (Page ID 0)
Page 9 (Database Boot Page)
Allocation Pages (GAM, SGAM, PFS, IAM, BCM and DCM)

For Detailed info., on each page types:
https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15

The page corruptions can be repaired automatically using Mirroring or Always-On Availability Group

But, Some of the page corruptions can't be repaired automatically by Mirroring or Always-On Availability Group either

Ok. Let's enable Mirroring, try with Failover and corrupt some of the Data Page(s) in Principal database and see whether the automatic page repair works!

let's begin...

My Test Environments

I am going to use SQL Server 2012 Instance "PANDIAN\SQL2012" as Principal, SQL Server 2012 Instance "PANDIAN\SQL2012_1" as Mirror and SQL Server 2016 Instance "PANDIAN\SQL2016" as Witness.

Why I am using Same Version of SQL Server for Principal and Mirror ? let me tell you later

Let's change the SQL management studio session in "SQLCMD Mode" (Query menu --> SQLCMD Mode)

1. To Create a Sample database in Principal Instance
:CONNECT PANDIAN\SQL2012
CREATE DATABASE PrimaryDB1
GO

Connecting to PANDIAN\SQL2012...
Disconnecting connection from PANDIAN\SQL2012...

2. Creating sample table and records
:CONNECT PANDIAN\SQL2012
USE PrimaryDB1
GO
CREATE TABLE TABLE1(ID INT IDENTITY(1,1), NAMES VARCHAR(10))
GO
INSERT TABLE1(NAMES) VALUES('SQL'),('Server'),('SQL Server')
INSERT TABLE1(NAMES) VALUES('Mirroring'),('Failover'),('Testing')
INSERT TABLE1(NAMES) VALUES('Automatic'),('Page'),('Corruption'),('Manual')
GO

Connecting to PANDIAN\SQL2012...
(3 row(s) affected)
(3 row(s) affected)
(4 row(s) affected)
Disconnecting connection from PANDIAN\SQL2012...

3. Sample records from the Table
:CONNECT PANDIAN\SQL2012
USE PrimaryDB1
GO
SELECT * FROM TABLE1

4. Taking FULL Backup from Principal Instance
:CONNECT PANDIAN\SQL2012
BACKUP DATABASE [PrimaryDB1] TO  DISK = N'\\PANDIAN\Personal\MirroringPath\PrimaryDB1.Bak' 
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The shared path should be accessed by Mirror Instance

5. Taking LOG Backup from Principal Instance 
:CONNECT PANDIAN\SQL2012
BACKUP LOG [PrimaryDB1] TO  DISK = N'\\PANDIAN\Personal\MirroringPath\PrimaryDB1.Trn' 
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

6. Restore the FULL Backup in Mirror Instance
:CONNECT PANDIAN\SQL2012_1
USE [Master]
GO
RESTORE DATABASE [PrimaryDB1] FROM  DISK = N'C:\Personal\MirroringPath\PrimaryDB1.Bak' 
WITH  FILE = 1,  
MOVE N'PrimaryDB1' TO N'C:\Personal\DATA\PrimaryDB1.mdf',  
MOVE N'PrimaryDB1_log' TO N'C:\Personal\LOG\PrimaryDB1_log.ldf',  
NORECOVERY,  NOUNLOAD,  STATS = 5
GO

The restore should be in NORECOVERY state

7. Restore LOG Backup in Mirror Instance
:CONNECT PANDIAN\SQL2012_1
USE [Master]
GO
RESTORE LOG [PrimaryDB1] FROM  DISK = N'C:\Personal\MirroringPath\PrimaryDB1.Trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

The LOG backup restore also should be in NORECOVERY state

8. Create ENDPOINT for the Principal Instance
:CONNECT PANDIAN\SQL2012
CREATE ENDPOINT Mirroring
AS TCP (LISTENER_PORT=5022)
FOR DATA_MIRRORING (ROLE=PARTNER,ENCRYPTION=REQUIRED ALGORITHM AES);

9. Give CONNECT Permission to the ENDPOINT for the service account in Principal Instance
:CONNECT PANDIAN\SQL2012
GRANT CONNECT ON ENDPOINT::Mirroring TO [PANDIAN\SQLServerBuddy]

Make sure the SQL Server runs under this service account

10. To make sure the ENDPOINT created
:CONNECT PANDIAN\SQL2012
SELECT serverproperty('servername') [Servername], e.name [Endpoint], e.protocol_desc [Protocol], e.type_desc [Type], e.state_desc [State], e.role_desc [Role], e.encryption_algorithm_desc [ENCRYPTION ALGORITHM],ep.Port [Endpoint Port] FROM SYS.DATABASE_MIRRORING_ENDPOINTS e join SYS.TCP_ENDPOINTS ep ON (e.endpoint_id = ep.endpoint_id) 

See, The ENDPOINT not started yet

11. Let me START the ENDPOINT
:CONNECT PANDIAN\SQL2012
ALTER ENDPOINT Mirroring STATE=STARTED;

12. To make sure the ENDPOINT started
:CONNECT PANDIAN\SQL2012
SELECT serverproperty('servername') [Servername], e.name [Endpoint], e.protocol_desc [Protocol], e.type_desc [Type], e.state_desc [State], e.role_desc [Role], e.encryption_algorithm_desc [ENCRYPTION ALGORITHM],ep.Port [Endpoint Port] FROM SYS.DATABASE_MIRRORING_ENDPOINTS e join SYS.TCP_ENDPOINTS ep ON (e.endpoint_id = ep.endpoint_id)

See, The ENDPOINT is started!

13. Create ENDPOINT for the Mirror Instance

:CONNECT PANDIAN\SQL2012_1
CREATE ENDPOINT Mirroring
AS TCP (LISTENER_PORT=5023) FOR DATA_MIRRORING (ROLE=PARTNER,ENCRYPTION=REQUIRED ALGORITHM AES);

14. Give CONNECT Permission to the ENDPOINT for the service account in Mirror Instance
:CONNECT PANDIAN\SQL2012_1
GRANT CONNECT ON ENDPOINT::Mirroring TO [PANDIAN\SQLServerBuddy]

Make sure the SQL Server runs under this service account

15. Let me START the ENDPOINT
:CONNECT PANDIAN\SQL2012_1
ALTER ENDPOINT Mirroring STATE=STARTED;

16. To make sure the ENDPOINT created and started
:CONNECT PANDIAN\SQL2012_1
SELECT serverproperty('servername') [Servername], e.name [Endpoint], e.protocol_desc [Protocol], e.type_desc [Type], e.state_desc [State], e.role_desc [Role], e.encryption_algorithm_desc [ENCRYPTION ALGORITHM],ep.Port [Endpoint Port] FROM SYS.DATABASE_MIRRORING_ENDPOINTS e join SYS.TCP_ENDPOINTS ep ON (e.endpoint_id = ep.endpoint_id)

17. Create ENDPOINT for the Witness Instance

:CONNECT PANDIAN\SQL2016
CREATE ENDPOINT Mirroring
AS TCP (LISTENER_PORT=5024) FOR DATA_MIRRORING (ROLE=WITNESS,ENCRYPTION=REQUIRED ALGORITHM AES);

18. Give CONNECT Permission to the ENDPOINT for the service account in Witness Instance
:CONNECT PANDIAN\SQL2016
GRANT CONNECT ON ENDPOINT::Mirroring TO [PANDIAN\SQLServerBuddy]

Make sure the SQL Server runs under this service account

19. Let me START the ENDPOINT
:CONNECT PANDIAN\SQL2016
ALTER ENDPOINT Mirroring STATE=STARTED;

20. To make sure the ENDPOINT created and started
:CONNECT PANDIAN\SQL2016
SELECT serverproperty('servername') [Servername], e.name [Endpoint], e.protocol_desc [Protocol], e.type_desc [Type], e.state_desc [State], e.role_desc [Role], e.encryption_algorithm_desc [ENCRYPTION ALGORITHM],ep.Port [Endpoint Port] FROM SYS.DATABASE_MIRRORING_ENDPOINTS e join SYS.TCP_ENDPOINTS ep ON (e.endpoint_id = ep.endpoint_id)

21. To make Principal Instance as a Partner of Mirror Instance
:CONNECT PANDIAN\SQL2012_1
ALTER DATABASE [PrimaryDB1] SET PARTNER ='TCP://PANDIAN:5022'

Connecting to PANDIAN\SQL2012_1...
Disconnecting connection from PANDIAN\SQL2012_1...

22. To make Mirror Instance as a Partner of Principal Instance
:CONNECT PANDIAN\SQL2012
ALTER DATABASE [PrimaryDB1] SET PARTNER ='TCP://PANDIAN:5023'

Connecting to PANDIAN\SQL2012...
Disconnecting connection from PANDIAN\SQL2012...

23. To make Witness Instance as a Witness of Principal Instance
:CONNECT PANDIAN\SQL2012
ALTER DATABASE [PrimaryDB1] SET WITNESS ='TCP://PANDIAN:5024'

Connecting to PANDIAN\SQL2012...
Disconnecting connection from PANDIAN\SQL2012...

It's done.....

24. Let me check the Principal Database and Is in sync

25. Let me check the Mirror Database and Is in sync

So, Both the Databases - Principal and Mirror are in Sync now! Everything will be propagated from the Principal Database to Mirror databases. 

26. Let me check the Database Mirroring Monitor and see how it goes..
Expand Principal "Databases" --> Right Click on the PrimaryDB1 --> Click Tasks --> Click "Launch Database Mirroring Monitor"

Everything looks Good!

See, 
PANDIAN\SQL2012 acts as a Principal
PANDIAN\SQL2012_1 acts as a Mirror

and, Witness is watching from "TCP://PANDIAN:5024" which means "PANDIAN\SWL2016"

What will happen If anything goes wrong with Principal "PANDIAN\SQL2012" ? Or We manually failover it ?

In that case, Mirror instance should become as Principal and Current Principal will be act as a Mirror. Right ?

Let me Failover the Principal manually now and see

27. Manually failover the Principal
:CONNECT PANDIAN\SQL2012
USE [Master]
GO
ALTER DATABASE PrimaryDB1 SET PARTNER FAILOVER

Connecting to PANDIAN\SQL2012...
Disconnecting connection from PANDIAN\SQL2012...

28. Let me check the Database Mirroring Monitor now

As per MSDN - On the former principal, clients are disconnected from the database and in-flight transactions are rolled back

See,

PANDIAN\SQL2012_1 acts as a Principal
PANDIAN\SQL2012 becomes as Mirror

Continues...

The number of columns for each row in a table value constructor must be the same

What is table value constructor?

A set of row value expressions to be constructed into a table.

It can be specified/Used in the following forms

• VALUE Clause in INSERT
• VALUE Clause in SELECT..FROM
• VALUE Clause in MERGE..USING
• VALUE Clause in JOIN

The number of values specified in each list must be the same and the values must be in the same order as the columns in the table.

Usually, We need to use UNION or UNION ALL for Non-Table result set. Right ?


But, The same thing can be achieved by Table value constructor as below 


And, The number of values specified in each list should be matching!! 

If not - We can see the below Error

Msg 10709, Level 16, State 1, Line 1
The number of columns for each row in a table value constructor must be the same.

How does Azure work ?

• It’s a Private and Public platform
• It uses a Technology called as Virtualization – Virtualization separates the tight coupling a computer’s CPU and its operating system using an abstraction layer called hypervisor
• The hypervisor emulates all the functions of a real computer and its CPU in a Virtual machine. It can run multiple virtual machine at the same time and each virtual machine can run any compatible operating system such as Windows, Linux
• Azure takes this virtualization technology and repeats it on a massive scale in Microsoft data centers throughout the world. Each data center has many racks filled with servers
• Each server includes a hypervisor to run multiple virtual machines
• A network switch provides connectivity to the servers. One server in each rack runs a special piece of software called a fabric controller. Each fabric controller is connected to another special piece of software known as the orchestrator.  The orchestrator is responsible for managing everything that happens in Azure including responding to users’ requests
• Users make requests using the orchestrator’s web API. The web API can be called by many tools including the user interface of the Azure portal
• When a user makes a request to create a virtual machine, the orchestrator packages everything that’s needed, picks the best server racks, then sends the package and request to the fabric controller. Once the fabric controller has created the virtual machine, the user can connect to it
• Azure make it easy for developers and IT professionals to be agile when they build, deploy and manage their applications and services. But this agility can have unintended consequences if unauthorized resources are created Or If resource are left running after they’re no longer needed
• The solution to this problem is to use Azure’s resource access management tools as part of your organization’s governance program

Source: https://azure.microsoft.com/en-in/resources/videos/azure-adoption-guide-how-does-azure-work/

Dynamic query with SP_EXECUTESQL vs. EXEC

sp_executesql is recommended over using the EXEC statement to execute a string of query because of the below reasons

1. Self-contained batches

Statements in sp_executesql or EXEC are not compiled to an execution plan until its executed (Not parsed/checked for errors)

DECLARE @hai VARCHAR(5)

SET @hai = 'HAI'

EXEC sp_executesql N'PRINT @hai'

GO

--Or

DECLARE @hai VARCHAR(5)

SET @hai = 'HAI'

EXEC ('PRINT @hai')

GO

The above both block fails - The variable @hai become out of scope/boundary

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@hai".

2. Substituting Parameter values
sp_executesql supports the substitution of parameter values for any parameters specified in the Transact-SQL string, but the EXEC statement does not.

The SQL Server query optimizer will probably match the Transact-SQL statements from sp_executesql with execution plans from the previously executed statements, saving the overhead of compiling a new  execution plan

With the EXEC statement, all parameter values must be converted to character or Unicode and made a part of the Transact-SQL string

USE [AdventureWorks2012]
GO
DECLARE @Query VARCHAR(100), @Param1 INT
SET @Param1 = 12742

SET @Query = 'SELECT * FROM Person.[Address] WITH(NOLOCK) WHERE AddressID =' +  CAST(@Param1 AS VARCHAR)
EXEC(@Query)

A completely new Transact-SQL string must be built for each execution, even when the only differences are in the values supplied for the parameters. This generates extra overhead in the following several ways:

- The ability of the SQL Server query optimizer to match the new Transact-SQL string with an existing execution plan is hampered by the constantly changing parameter values in the text of the string, especially in complex Transact-SQL

statements.

- The entire string must be rebuilt for each execution.

- Parameter values (other than character or Unicode values) must be cast to a character or Unicode format for each execution

sp_executesql supports the setting of parameter values separately from the Transact-SQL string

USE [AdventureWorks2012]

GO

DECLARE @Query NVARCHAR(100), @LParam1 INT

SET @LParam1 = 12742

SET @Query = N'SELECT * FROM [AdventureWorks2012].Person.[Address] WITH(NOLOCK) WHERE AddressID =@Param1'

EXEC sp_executesql @Query,N'@Param1 Int', @Param1 = @LParam1

- Because the actual text of the Transact-SQL statement does not change between executions, the query optimizer should match the Transact-SQL statement in the second execution with the execution plan generated for the first execution.

- Therefore, SQL Server does not have to compile the second statement.

- The Transact-SQL string is built only once.

- The integer parameter is specified in its native format. Conversion to Unicode is not required here.

Note: Object names in the statement string must be fully qualified (i.e: Database.Schema.Objectname) in order for SQL Server to reuse the execution plan.

3. Re-using execution plan

Using sp_executesql can help reduce the overhead while still allowing SQL Server to reuse execution plans.

sp_executesql can be used instead of stored procedures when executing a Transact-SQL statement a number of times, when the only variation is in the parameter values supplied to the Transact-SQL statement. Because the Transact-SQL statements themselves remain constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution!

/*EXEC*/

USE [AdventureWorks2012]

GO

DECLARE @Param1 VARCHAR(10)

SET @Param1 = 'Gail'

EXEC ('SELECT p.FirstName, pp.PhoneNumber FROM Person.Person p WITH(NOLOCK) 

JOIN Person.PersonPhone pp WITH(NOLOCK)

ON (p.BusinessEntityID = pp.BusinessEntityID)

WHERE p.LastName = '''+ @Param1 + '''')

GO

The above statement executed 3 Times with various values(Goel, Gigi, Gail)

Check the execution plan generated (3 Separate Execution plans generated for every run based on the parameter value changes)

/*SP_EXECUTESQL*/

USE [AdventureWorks2012]

GO

DECLARE @Sql nvarchar(500)

SET @Sql = N'SELECT p.FirstName, pp.PhoneNumber FROM Person.Person p WITH(NOLOCK) 

JOIN Person.PersonPhone pp WITH(NOLOCK)

ON (p.BusinessEntityID = pp.BusinessEntityID)

WHERE p.LastName = @LastName';

DECLARE @ParamDefinition nvarchar(25) = N'@LastName Varchar(10)'

DECLARE @lLastName Varchar(10)

SET @lLastName='Gail'

EXEC sp_executesql @Sql, @ParamDefinition,

                   @LastName=@lLastName;

GO

Check the execution plan generated as above(Only 1 execution plan generated and Re-used for all the subsequent runs irrespective of it's parameter value - See - Use Count column as 3)

Here is the script used for the above screenshots:
WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
cp.usecounts [Use Count],
cp.objtype,
c.value('(QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)],
c.value('(QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
c.value('(QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
LEFT(x.[text],50) + '...' + RIGHT(x.[text],10)  [Plan Text]
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS X CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
WHERE x.[text] LIKE '%SELECT p.FirstName%' AND x.[text] NOT LIKE '%sys.dm_exec_cached_plans%' 

Converting to unequal length truncates on Left Or Right ?

When converting string into Binary, I mean converted from the below data type 
CHAR
VARCHAR
NCHAR
NVARCHAR
TEXT
NTEXT to a BINARY data type of "unequal length", SQL Server truncates the data on the RIGHT!

Here is how...
Declare @Varchar Varchar(6), @Binary Binary(2)
Set @Varchar = 123456
Set @Binary = Cast(@Varchar as Binary(2))
Select @Varchar [Actual], Cast(@Binary as Varchar(6)) [Converted]
Go

When converting Numbers into Binary, The data is truncated on the LEFT and Padding is done with hexadecimal "zeros"

Wondering how.... ?

Declare @Source INT, @Target Binary(2)
Set @Source = 123456
Set @Target = Cast(@Source as Binary(2))
Select @Source [Actual], @Target, Cast(@Target as Int) [Converted]
Go

Actually, The Number should have been converted into 0x1E240

But, according to the unequal length of the target conversion, It truncates on LEFT and padding with "0" instead,

I mean "0x1E240" becomes "0x0E240". 

So, When converting back to numbers from binary - It becomes 57920

So, Beware of conversion from or to Binary!!!

SQL Server - SERVER / DATABASE Level Fixed Roles and Permissions

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

Microsoft SQL Server 2017 and Azure SQL Database - Permissions Hierarchy

Permissions hierarchy explained well for the below area

SQL Server

• Server Level Permissions
• Database Level Permissions

Azure SQL

• Database Level Permissions - Outside the Database

DOWNLOAD PDF

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15

Performing consistency check on Secondary/redundant copy of the database is fair enough?

It's actually "NO"

Checking consistency on secondary/copy of the source database does not imply that the source/primary database is free of corruption.

Since, Source and secondary are located in different I/O subsystems involved. right? Which means - consistency checking has to be performed in all environments to examine the actual corruption (I/O Perspective).

Because, None of the SQL Server redundancy technologies propagate the data file pages and I/O subsystem corruptions. Instead - It propagates the Transaction log records to the secondaries. So, there is NO point of performing such a consistency check only in Primary or secondary.

So, performing the consistency check on all the databases in every environment is considered as mandatory!!!

SELECT COUNT(*) - Always scans all the pages/rows ?

SELECT COUNT(*) always scans every rows (all pages/rows) in the table ?

No! - Not exactly! First, Ask the question - "The table has any Index ?"

How many Index are there in that table ? Each column type/size (allocation) ?

Why should I ask these questions ?

Here is the scenario!

A table "SystemObjects" created along with 2062 records: 
SELECT [schema_id], CAST([object_id] AS BIGINT) [object_id] , CAST(name AS NVARCHAR(200)) [name] INTO SystemObjects 
FROM sys.system_objects

Table structure:

Trying to get record count (with Actual Execution Plan)

SELECT COUNT(1) FROM SystemObjects

YES! - Now, we got Table Scan operation with 94%. Yeah - This is what always happen right ?
Partially Yes and No!

Lets create a Non-Clustered Index:
CREATE NONCLUSTERED INDEX nci_obj_id ON SystemObjects([Object_id])

- Non-clustered Index has been created on Object_id column which is 8 Byte!

Trying to get record count (with Actual Execution Plan)

SELECT COUNT(1) FROM SystemObjects









We got Index Scan operation based on object_id column

Lets create an another Non-Clustered Index:

CREATE NONCLUSTERED INDEX nci_schema_id ON SystemObjects([schema_id])

- Non-clustered Index has been created on schema_id column which is 4 Byte!

Trying to get record count again (with Actual Execution Plan)

SELECT COUNT(1) FROM SystemObjects

We got Index Scan operation again. But, this time based on schema_id column!!!

Confusing a bit right ?

See - How the Optimizer decides and goes based on what condition ? You believe Optimizer always tries to go with low cost. right ?

here is how...

Lets explore the current internal structure:
SELECT a.name, b.index_id, b.page_count, b.index_type_desc FROM sys.indexes a cross apply sys.dm_db_index_physical_stats(DB_ID(), a.[object_id] ,a.index_id,NULL,'LIMITED') AS b
WHERE a.[object_id] = OBJECT_ID('SystemObjects')

As you can see, The optimizer chooses to get the info from the smallest structure which will cost very less!!! right ? (Performance perspective)

here - The Optimizer decides to go with lowest pages to process with - So that the whole operation completes quickly/costs very less. Which is very effective/optimized operations.

The optimizer goes with "nci_schema_id" Index which is least pages (5 Pages)

If you remove the Index "nci_schema_id", The optimizer goes with "nci_obj_id" index which is least pages (6 Pages)

Once remove all the Indexes from the table - There is no other go - The Optimizer goes with Table scan (Scans all the Pages - 22 pages)

Here is the synopsis: The Optimizer goes with least pages which can be complete the process as quickly as possible with less cost!