SSMS Drag/Drop Format Options

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 1 Million Dollar Software Giveaway

For a limited time, 1 (one) free AKAWN Monitoring license is being given away to each registered user when using the promo code: 1million

The license does not expire and can be used for either Production or Non-Production monitoring.

If 2 or more licenses are purchased, the free license can be deducted by using the same promo code.

Give the product a try and let us know what you think!

Why use AKAWN Monitoring?

Not everyone will be in a position to use AM for monitoring their SQL Server environment, but the ones that can will benefit from the ever-increasing capabilities i.e. currently you’ll enjoy:

  • Support for all versions from SQL Server 2005 onward

  • Support for all editions of SQL Server

  • The ability to monitor SQL Server on Windows, Linux and Azure SQL Databases

  • Agent-less monitoring with a minuscule CPU/network overhead

  • Schedule checks when you want them to occur and at the frequency you desire

  • Easy check exceptions for expected events

  • Alert and/or page based on alerts

  • Trending of Server and SQL Server performance counters with easy on the eye charts

  • Detailed Active query tracking on a schedule of your choosing with included query plans, locks, waits, connectivity info etc

  • Full, Transaction and Differential backup capabilities

  • Index maintenance and integrity check capabilities

  • 100+ checks, including the mandatory missing indexes, duplicate indexes, top CPU/IO queries, security auditing etc

  • A refreshing environment dashboard for a simple high-level overview of what is occurring in your SQL environment

  • An increasing number of site reports which can be exported to Word/Excel/PDF

  • A monitoring license which never expires

All of this for ~ NZ$1.50 a week, it is almost free, try it now, you can’t go wrong Download Page

Introducing AM v2.0

AKAWN Monitoring (AM) version 2.0 is now ready for download.

Are you tired of fiddling around with PowerShell and TSQL to monitor your environment?

Are you spending more than 1 minute to setup monitoring, alerting, backups, indexing, integrity checks, site reports, performance and query activity trending for a SQL Server instance?

Are you considering monitoring Azure SQL Database and SQL on Linux?

Does it cost you more than $80 in resource effort a year to setup monitoring?

If you answered yes to any of these, it’s worth giving AM a try now Download Page

Identify columns available for extended events

The following query helps identify the columns associated with extended events and the format of the column i.e. int32.

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.

How secure is Azure SQL Database?

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:

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'' 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.

What’s the local time?

SQL Server has a handy view called sys.time_zone_info, but as shown below it’s lacking the local time.

The following is one way to workout the local time:

Or just a specific zone:

Hopefully at some stage Microsoft will look to include the local info by default so this kind of work around is not required, especially as Azure is heavily invested in UTC.

Choosing a SQL Server edition during setup on Linux

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