Always On.
No it’s not a US vs UK thing.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15#RDBMSHA
Also a note on the abbreviation AG:
Always On.
No it’s not a US vs UK thing.
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15#RDBMSHA
Also a note on the abbreviation AG:
No; data compression only targets what you specify.
Things to be aware of:
Heap test:
/* create table */
CREATE TABLE test_compression_heap (c1 int IDENTITY (1,1), c2 float);
/* insert some records */
INSERT INTO test_compression_heap (c2) SELECT RAND();
GO 10000
/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_heap(c2);
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress heap */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* remove heap compression */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* drop test table */
DROP TABLE test_compression_heap;
Clustered index test:
/* create table */
CREATE TABLE test_compression_clustered (c1 int IDENTITY (1,1), c2 float);
/* insert some records */
INSERT INTO test_compression_clustered (c2) SELECT RAND();
GO 10000
/* create clustered index */
CREATE CLUSTERED INDEX CI_c1 ON test_compression_clustered(c1);
/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_clustered(c2);
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress clustered index */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* remove heap compression */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* drop test table */
DROP TABLE test_compression_clustered;
Below is a query to help you identify candidates for compressing.
Run in the database you would like to check.
NOTE: sp_estimate_data_compression_savings samples (up to 5000 used pages) the source object and loads this data into an equivalent table (called #sample_tableDBA05385A6FF40F888204D05C7D56D2B) and index created (called sample_indexDBA05385A6FF40F888204D05C7D56D2B) in tempdb. The table or index create in tempdb is then compressed to the requested setting and the estimated compression savings is computed.
SELECT 'EXEC sp_estimate_data_compression_savings @schema_name=''' +b.[name] + ''',@object_name=''' + a.[name] + ''',@index_id=' + CAST(c.index_id AS varchar(10)) +',@partition_number=' + CAST(c.partition_number AS varchar(10)) +',@data_compression=''PAGE'';/*indexname=' + CASE WHEN d.[name] IS NULL THEN 'heap' ELSE d.[name] END + ',size_gb=' + CAST(CAST(((d.dpages * 8) / 1024 / 1024.) AS decimal(20,2)) AS varchar(100)) + '*/'
FROM sys.tables a, sys.schemas b, sys.partitions c, sysindexes d
WHERE 1=1
AND a.[schema_id] = b.[schema_id]
AND a.[object_id] = c.[object_id]
AND a.[object_id] = d.[id]
AND c.[index_id]= d.[indid]
AND ((d.dpages * 8) / 1024 / 1024.) > 1 /* larger than 1GB */
AND c.data_compression_desc = 'NONE'
ORDER BY ((d.dpages * 8) / 1024 / 1024.) DESC;
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';
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
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
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;
Below attempts to help with understanding deadlocks:
Objective
Rules
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:
What you can try do to help reduce Deadlocks:
Below is a list of Skype for Business databases that you may come across.
cpsdyn |
lis |
rgsconfig |
rgsdyn |
rtcab |
rtcshared |
rtcxds |
xds |
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 = '& < > " ' 
';
SELECT @xml_text AS xml_text;
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@xml_text
, '&' , '&')
, '<' , '<')
, '>' , '>')
, '"', '"')
, ''', '''')
, '
' , ' ')
, '
' , ' ')
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))
, '&', '&')
, '<', '<')
, '>', '>')
, '"', '"')
, '''', ''')
, ' ', '
')
, ' ', '
')
AS converted_xml;
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.
© 2011-2023 AKAWN®. All rights reserved. Privacy Policy.