Six months ago how you would go about setting up Active geo replication for your SQL Databases would be different to today, yes things (naturally) do change but for this specific area it has changed for the better – again something that you would expect right?
Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?
A very common requirement which can be satisfied by various tools. Personally I like using Visual Studio 2017 Community Edition and I thought I would do a quick overview of it.
If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I feel that it is taking up too much DTU (Database Transaction Unit) usage hence I can free up resources for other operations.
Who should be running DBCC CHECKDB for Azure SQL Database? Should it be Microsoft or should customers be scheduling it? All official information just tells you that you CAN run it (below shows the green tick) but still no clarity around the question.
Quite a mouth full for a title but never the less very exciting. With the new version of SQL Server Management Studio (SSMS) 17.2 You now have the option to use Azure AD authentication for Universal Authentication with Multi-factor authentication (MFA) enabled, by that I mean use a login via SSMS that is enabled for MFA where below I will show you the two step verification using a push notification to my iPhone. (Yes iPhone I love it)
SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and get the relevant lock it needs because the other process involved is preventing it from getting the resource it requires.
In this quick video clip I create a deadlock scenario and check how to get some deadlock information via system health extended events session.
Below is the T-SQL for querying the system health session. This was against a SQL Server 2014 install so you may need to change the file location.
SELECT top(1)
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime2') AS [TimeOfDeadlock],
CONVERT(xml, event_data).query('/event/data/value/child::*') AS [DeadlockGraph]
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null)
WHERE object_name = 'xml_deadlock_report'
ORDER BY [TimeOfDeadlock] DESC
Scaling up and down your SQL Database is something that is quite common to do. I want to discuss the impact of moving up and down tiers, in terms of your transactions and connections.
Azure does a lot for your SQL Database, from backups to automatic tuning but it still doesn’t have an index maintenance policy straight out of the box via the portal. Some may not care about rebuilding your indexes but it is still something I like to do, the question is, how can I automate this because I am not a fan of manually running code for index rebuilds.
The answer is via Azure Automation.
If you do – shame on you and shame on me because I do.
