With the release of SQL Server 2017 CU 6 today, you now have the ability to add the replication Distribution database to an Always On Availability Group.
The current setup steps and limitations can be found here.
So you’ve been working on scripts in SSMS v17.x and suddenly it freezes and won’t let you interact with it at all.
No fear, when you close (kill) the SSMS process using Task Manager and reopen it you should be prompted to recover your files. The caveat being that you haven’t disabled Save AutoRecover in the Tools – Options menu item.
However, if you’d rather not take the risk of not being prompted and as per the previous screenshot, you should hopefully be able to find your scripts under your Documents\Visual Studio 2015 folder.
When saving data you may have wondered if converting a null value to another character i.e. a space, impacts disk space usage in your database.
Why would you convert a null to a space or dash? Well some people/organizations, rightly or wrongly, prefer this for reporting purposes or some other business requirement.
OK, lets review 3 common storage types, int, char and varchar:
Finally we get a difference of 3 bytes when using a null rather than another character when using varchar.
Conclusion, not all data types allow for space savings when using nulls.
With the newly released SSMS v17.6 there has been a new Drag/Drop section added to Tools -> Options menu item as shown below:
When both are set to True you’ll get the existing functionality
When both are set to False you will now get:
Notice that the Database Version column will still have brackets as it has a space in the name even though Surround object names with brackets when dragged is set to False.
The following query helps identify the columns associated with extended events and the format of the column i.e. int32.
SELECT b.[name] AS package ,a.[object_name] AS [event] ,a.column_id ,a.[name] AS [column] ,a.column_type ,a.[type_name] AS column_storage ,a.column_value ,a.[description] AS column_desc ,b.[description] AS package_desc FROM sys.dm_xe_object_columns a, sys.dm_xe_packages b WHERE 1=1 AND a.object_package_guid = b.[guid] AND a.column_type <> 'readonly' ORDER by b.[name], a.[object_name],a.column_id;
As the data is typically extracted and used on SQL Server, I would have thought it would be more meaningful for Microsoft to actually use the SQL Server types i.e. bigint, nvarchar(MAX), instead we are supplied with a you guess field and it just adds another unnecessary frustration for the DBA / developer.
As part of a revamp of AKAWN Monitoring to include Azure SQL Database and Linux support the usual look at available monitoring options is being under taken.
Azure SQL Database is one of the trickier to monitor as it is pretty locked down and xml parsing for session events info can be hellish (one of the reasons the current AKAWN Monitoring is moving away from use of xml storage).
As locked down as it is, there are still ways to look into the inner workings. Just one example of the security things I’ve discovered is:
SET NOCOUNT ON; IF OBJECT_ID('tempdb..#temp') IS NOT NULL BEGIN DROP TABLE #temp; END CREATE TABLE #temp (EventType nvarchar(30), [Parameters] smallint, EventInfo nvarchar(4000)); WHILE (1=1) BEGIN DECLARE @sqlcmd nvarchar(MAX); DECLARE @session_id varchar(3); DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT session_id from [sys].[dm_exec_sessions]; OPEN item_cursor FETCH NEXT FROM item_cursor INTO @session_id WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd = 'INSERT INTO #temp (EventType,[Parameters],EventInfo) EXEC (''DBCC INPUTBUFFER (' + @session_id + ') WITH NO_INFOMSGS'')' ; EXEC (@sqlcmd ) FETCH NEXT FROM item_cursor INTO @session_id END CLOSE item_cursor; DEALLOCATE item_cursor; SELECT DISTINCT * FROM #temp WHERE EventInfo IS NOT NULL; WAITFOR DELAY '00:00:05'; END IF OBJECT_ID('tempdb..#temp') IS NOT NULL BEGIN DROP TABLE #temp; END
The above method will show buffer activity for all sessions, which is a big no no and something Microsoft will likely need to look into locking down.
What does it show? Well basically everything i.e. it shows the checks performed, shows when and where backups are going along with the syntax used:
(@DbName nvarchar(36),@BackupStats int,@BufferCount int,@TransferSize int)BACKUP LOG @DbName TO URL = N'https://xxxx.blob.core.windows.net/server-akawnxxxx/backup/xxxx/Log/2017-11-26T12-02-06/2017-11-30T23-13-25_2017-11-26T12-02-06.log' WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STATS = @BackupStats, BUFFERCOUNT = @BufferCount, MAXTRANSFERSIZE = @TransferSize
If you collected info for long periods, who knows what could be revealed.
The above was collected from a standard azure database and not master, although you could just as well use it.
I haven’t seen anyone else mention this, so you’re reading about this first here.
SQL Server has a handy view called sys.time_zone_info, but as shown below it’s lacking the local time.
SELECT * FROM sys.time_zone_info;
SELECT *, DATEADD(MINUTE, CAST( SUBSTRING ( current_utc_offset ,1 , 1 ) + CAST( (CAST(SUBSTRING(current_utc_offset ,2 , 2 ) AS int) * 60) + CAST(SUBSTRING(current_utc_offset,5,2) AS int) AS varchar(3)) AS int) ,GETUTCDATE()) AS local_time FROM sys.time_zone_info;
SELECT *, DATEADD(MINUTE, CAST( SUBSTRING ( current_utc_offset ,1 , 1 ) + CAST( (CAST(SUBSTRING(current_utc_offset ,2 , 2 ) AS int) * 60) + CAST(SUBSTRING(current_utc_offset,5,2) AS int) AS varchar(3)) AS int) ,GETUTCDATE()) AS local_time FROM sys.time_zone_info WHERE [name] = 'New Zealand Standard Time';
You may be wondering how you specify the edition of SQL Server when installing on Linux.
As shown below; when you get to the step
sudo /opt/mssql/bin/mssql-conf setup the following edition options are presented to you.
Here option 2 (Developer) was selected and this presented a further set of 3 questions before the setup successfully completes:
- 1) Accept the license terms
- 2) Choose the language
- 3) Confirm the sa password
With DBAs now coming to grips with having to support SQL Server on Linux, following configuration best practices is key.
Using setup defaults will likely get introduce your SQL Server to the OOM Killer, an unexpected outage and potential questions about your DBA skills.
It is available for download from https://www.microsoft.com/en-us/sql-server/sql-server-downloads