AKAWN

Check How A Windows User Connects

You may see a Windows user accessing SQL Server but they are not showing as a login.

The following command should assist with showing which security group(s) gives them access.

EXEC xp_logininfo 'yourdomain\usersname','all';

Changing A Database Owner

A database owner is typically set to ‘sa’ to mitigate having orphaned owners when the person who created the database leaves the business.

The legacy way of changing a database owner was:

USE [databasehavingnamechanged]
GO
EXEC sp_changedbowner 'sa';
GO

The recommended SQL Server 2008 and newer way is:

USE [master]
GO
ALTER AUTHORIZATION ON DATABASE::[databasehavingnamechanged] TO [sa];
GO

Standard SQL Server Agent Alerts

The following alerts are typically setup at a minimum to keep an eye on your SQL Server.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sql_alerts', 
		@enabled=1, 
		@weekday_pager_start_time=90000, 
		@weekday_pager_end_time=180000, 
		@saturday_pager_start_time=90000, 
		@saturday_pager_end_time=180000, 
		@sunday_pager_start_time=90000, 
		@sunday_pager_end_time=180000, 
		@pager_days=0, 
		@email_address=N'destinationemailaddress1@yourcompany.com;destinationemailaddress2@yourcompany.com', 
		@category_name=N'[Uncategorized]'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_14', 
		@message_id=0, 
		@severity=14, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_14', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_15', 
		@message_id=0, 
		@severity=15, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_15', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_16', 
		@message_id=0, 
		@severity=16, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_16', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_17', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_17', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_18', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_18', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_19', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_19', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_20', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_20', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_21', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_21', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_22', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_22', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_23', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_23', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_24', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_24', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_25', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=300, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_25', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_823',
		@message_id=823,
		@severity=0,
		@enabled=1,
		@delay_between_responses=300,
		@include_event_description_in=1,
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_823', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_824',
		@message_id=824,
		@severity=0,
		@enabled=1,
		@delay_between_responses=300,
		@include_event_description_in=1,
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_824', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_825',
		@message_id=825,
		@severity=0,
		@enabled=1,
		@delay_between_responses=300,
		@include_event_description_in=1,
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_825', @operator_name=N'sql_alerts', @notification_method = 1;
GO

Identity Column Reseed Test

It’s quite common for an Identity column to be configured as an INT data type and the business is impacted when it fills-up.

Ideally the column would be changed to the BIGINT data type and the issue is resolved; however, sometimes this change is not quickly possible due to other objects using the column and/or resource constraints i.e. indexes, available disk space etc., and you have to stick with INT and transition to BIGINT at a later stage when possible.

If you need to stay with INT, the common solution is to start the reseed at -2,147,483,648 and let it work back to 0.

A test example for you is shown below:

/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (1,1), c2 float);

/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view records */
SELECT * FROM test_ident;

/* reseed from -2,147,483,648 working back to 0*/
DBCC CHECKIDENT ('test_ident', RESEED, -2147483648); 

/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view records */
SELECT * FROM test_ident;

/* drop test table */
DROP TABLE test_ident;

Below is an example of changing the Identity column to the BIGINT data type, if suitable for your scenario.

NOTE: A schema modify (SCH-M) lock is required on the table while the ALTER command runs.

/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (2147483647,1), c2 float);

/* try insert some records and only 1 will succeed*/
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view record */
SELECT * FROM test_ident

/* change indentity column to bigint */
ALTER TABLE test_ident ALTER COLUMN c1 bigint;

/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5

/* view records */
SELECT * FROM test_ident

/* drop test table */
DROP TABLE test_ident;

NOTE: If you use SSMS to change the column type, it will drop and re-create the table. Therefore, rather use the ALTER TABLE command.

Another consideration is the impact that the reseed will have on your sort order.

Below demonstrates the effect of using a negative increasing reseed vs a negative decreasing reseed and if you need to emulate the BIGINT data type i.e. show values larger than 2,147,483,647.

/* reseed starting from -2147483648 and increasing */
DECLARE @t1 TABLE (ident int, inserted int);
INSERT INTO @t1 (ident,inserted) VALUES (1,1);
INSERT INTO @t1 (ident,inserted) VALUES (2,2);
INSERT INTO @t1 (ident,inserted) VALUES (3,3);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483648,4);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483647,5);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483646,6);
SELECT
 ident
,inserted AS reseed_increasing
,CASE WHEN ident < 0 THEN ((2147483648 * 2) + CAST(ident AS bigint)) ELSE ident END AS emulate_bigint
FROM @t1 ORDER BY ident ASC;

/* reseed starting from -1 and decreasing */
DECLARE @t2 TABLE (ident int, inserted int);
INSERT INTO @t2 (ident,inserted) VALUES (1,1);
INSERT INTO @t2 (ident,inserted) VALUES (2,2);
INSERT INTO @t2 (ident,inserted) VALUES (3,3);
INSERT INTO @t2 (ident,inserted) VALUES (-1,4);
INSERT INTO @t2 (ident,inserted) VALUES (-2,5);
INSERT INTO @t2 (ident,inserted) VALUES (-3,6);
SELECT
 ident
,inserted AS reseed_decreasing
,CASE WHEN ident < 0 THEN 2147483647 - CAST(ident AS bigint) ELSE ident END AS emulate_bigint
FROM @t2 ORDER BY ident ASC;

Deadlock Basics

Below attempts to help with understanding deadlocks:

Objective

Collect both books in the order given.

Rules

Players cannot enter the room at the same time.
There are only 2 books available.
When a player picks up a book, they have to hold it for 1 minute before proceeding.
If a player cannot obtain a book without waiting, the player who entered the room last has to drop their book and restart.

	Player 1	Player 2
Time (HH:MM:SS)	Objective: Obtain Book A and then Book B.	Objective: Obtain Book B and then Book A.
10:00:00	Enters the room.
10:00:01		Enters the room.
10:00:05	Finds Book A and picks it up.
10:00:15		Finds Book B and picks it up.
10:01:01	1 minute has passed. Sees that Book B is held by Player 2 and waits.
10:01:15		1 minute has passed. See that Player 1 has Book A. As per the rules; Player 2 acknowledges that they cannot obtain Book A without waiting and as Player 1 had entered the room first, Player 2 drops their book and exits the room (Player 2 is the DEADLOCK VICTIM).
10:01:16	Picks it up Book B.
10:01:17		Enters the room.
10:01:18		Sees that Book B is held by Player 1 and waits.
10:02:16	1 minute has passed and the objective have been achieved. Drops both books and exits the room.
10:02:17		Sees that Book B is available and picks it up.
10:03:17		1 minute has passed and picks up Book A.
10:04:17		1 minute has passed and the objective has been achieved. Drops both books and exits the room.

If we now think of this in terms of SQL Server, a typical deadlock occurs as follows:

	Session 1	Session 2
Time (HH:MM:SS.nnn)
10:00:00.000	Begins Transaction 1.
10:00:00.001		Begins Transaction 2.
10:00:00.002	Starts a statement updating Table A.
10:00:00.003		Starts a statement updating Table B.
10:00:00.005	Finished updating Table A and waits to update Table B.
10:00:00.007		Finished updating Table B. Needs to update Table A but cannot as it is still held by the Session 1 Transaction 1. Deadlocks, rolls back its changes and exits Transaction 2.
10:00:00.008	Starts a statement updating Table B.
10:00:00.009		Session reconnects and begins Transaction 3. Sees that Table B is held by Session 1 and waits.
10:00:00.101	Finished updating Table B and ends Transaction 1.
10:00:00.102		Starts a statement updating Table B.
10:00.00.106		Finished updating Table B. Starts a statement updating Table A.
10:00.00.108		Finished updating Table A and ends Transaction 3.

Essentially:

Multiple change Statements are operating in a single Transaction.
These Statements are changing more than one object.
While the Transaction is active, the changed objects are locked to allow for a possible rollback of the Statements.
Transactions with multiple change Statements are also accessing and locking objects in a different order to the other multiple Statement Transactions.

What you can try do to help reduce Deadlocks:

Use 1 Statement per Transaction.
If using multiple Statements per Transaction, try ensure that the Statements in the offending Transactions access the objects in the same order, so you end up with normal waits/blocking instead of Deadlocks.

Skype for Business Databases

Below is a list of Skype for Business databases that you may come across.

cpsdyn

lis

rgsconfig

rgsdyn

rtcab

rtcshared

rtcxds

xds

Common XML Characters

When working with XML text the following common conversions are good to know.

XML	Character
&	&
<	<
>	>
"	“
'	‘
	Carriage Return (CR)
	Line Feed (LF)

To cast a text column which contains XML to human friendly text you could use the REPLACE function e.g.

DECLARE @xml_text nvarchar(MAX);
SET @xml_text = '&amp;   &lt;   &gt;   &quot;   &apos;   &#xD;&#xA;';

SELECT @xml_text AS xml_text;

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@xml_text
, '&amp;' , '&')
, '&lt;'  , '<')
, '&gt;'  , '>')
, '&quot;', '"')
, '&apos;', '''')
, '&#xD;' , ' ')
, '&#xA;' , ' ') 
AS converted_text;

A reverse example of the above e.g. xml to nvarchar, would be:

DECLARE @xml xml;
SET @xml = '<test/> ';

SELECT @xml AS [xml];

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(@xml AS nvarchar(MAX))
, '&', '&amp;')
, '<', '&lt;')
, '>', '&gt;') 
, '"', '&quot;')
, '''', '&apos;')
, ' ', '&#xD;')
, ' ', '&#xA;') 
AS converted_xml;

Windows Firewall Install Rule

Which Windows Defender Firewall profile causes the Windows Firewall rule warning?

It is the Public Profile.

If this profile is disabled, the warning does not show.

So the next question; when should the Windows firewall be disabled?

Answer: Never; unless you have other another firewall solution. Rather add an appropriate access rule.

SQL Server License Key Location On Media

You can find your SQL Server license key on your installation media.

It is located in the file called DefaultSetup.ini and is the PID value.

Shown below is 22222-00000-00000-00000-00000 which is the license key for Developer Edition.

What Determines the Installation Collation?

When installing SQL Server, why do you sometimes see that the Database Engine defaults to the SQL_Latin1_General_CP1_CI_AS collation and other times another value?

This value is determined by the system locale which can be found via Control Panel -> Clock and Region -> Region -> Administrative -> Language for non-Unicode programs.

Here in New Zealand we typically use the Latin1_General_CI_AS collation.

When the system locale is changed to “English (New Zealand)” a prompt to restart Windows is received.

After a restart, the result of this change means that the SQL Server installation will now default to Latin1_General_CI_AS.