Schema Compare multiple databases at once...

I recently had the need to compare a "Gold" or "Master" copy of our database to the 300 client copies of that database that exist in our Production environment.  I'm not alone...many fellow DBAs have had the same need.   Google searches for this confirm it.   This is for an upcoming upgrade to the application that will need post-upgrade comparison/verification.

There are 3rd party tools that do SQL Compares...my particular favorite is aptly named SQL Compare from Red Gate.  I've been using it off an on for 10 years.   I don't know if it can be set up to hit more than one database at a time.  The other issue is that I don't have a copy here.

Microsoft's SQL Server Data Tools will also do this within Visual Studio.   Still one database at a time.  I forget where, but someone pointed me to the fact that SSDT uses SQLPackage.exe under the hood to do the work.  I figure if I can run it at a command line I can script out all of the databases.  I'm not much of a DOS scripting guy, so everything that follows is just my hack version...but it works, and not just on my machine!

I got most of this from StackOverflow user Mike Hyde's response here
I had a followup "gotcha" question on SO here

The process:

Create a Test master database:

 --Create Gold copy  
 ( NAME = N'Test',  
      FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Test_Gold.mdf',  
      SIZE = 4096KB , FILEGROWTH = 1024KB )  
      LOG ON ( NAME = N'Test_log',  
      FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Test_Gold_log.ldf',  
      SIZE = 1024KB , FILEGROWTH = 100MB)  
 --Create some Objects  
 USE [Test]  
 CREATE TABLE [dbo].[Orders]  
      [OrderID] [int] NOT NULL,  
      [OrderDate] [datetime] NULL  
      ON [PRIMARY]  
 ALTER TABLE [dbo].[Orders]  
      ADD CONSTRAINT [DF_Orders_OrderDate]   
      DEFAULT (getdate()) FOR [OrderDate]  
 CREATE TABLE [dbo].[OrderDetails]  
      [OrderDetailID] [int] NOT NULL,  
      [OrderID] [int] NOT NULL,  
      [ItemQty] [int] NOT NULL  
      ON [PRIMARY]  
 Create View vOrders AS  
      INNER JOIN  
      dbo.Orders ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID  

Create 5 copies as Test1, Test2, etc.:

 --Back it up  
 Backup Database test  
 To Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\backup\test.bak'  
 --restore x 5  
 Restore Database Test1  
 From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\backup\test.bak'  
 With Move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test1.mdf'  
      , Move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test1_log.ldf'  
 Restore Database Test2  
 From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\backup\test.bak'  
 With Move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test2.mdf'  
      , Move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test2_log.ldf'  
 Restore Database Test3  
 From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\backup\test.bak'  
 With Move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test3.mdf'  
      , Move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test3_log.ldf'  
 Restore Database Test4  
 From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\backup\test.bak'  
 With Move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test4.mdf'  
      , Move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test4_log.ldf'  
 Restore Database Test5  
 From Disk = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\backup\test.bak'  
 With Move 'test' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test5.mdf'  
      , Move 'test_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\test5_log.ldf'  

Add a column to a table in Test4, and a view to Test5:

 Use Test4;  
 Alter Table dbo.Orders  
      Add CustomerName varchar(500)  
 Use Test5;  
 Create View VOrderDetails AS  
 Select * from dbo.OrderDetails  

So at this point we have a gold copy (Test), 3 that we know match it (Test 1/2/3) and 2 that have Drifted.   Get used to the term drift...3rd party vendors are using it and later, we will create scripts to fix 'drift'.

Finally it gets interesting!

Use SQLPackage to Extract a .dacpac file from the "Gold" database:

We will use SQLPackage.exe to create a .dacpac file.  Search your box for the file...likely it will be buried in Visual Studio's folders or SQL Server's.  Once I found it, I added that folder to my PATH variable so I didn't have to CD to it in the CMD window each time.

My default install has SQLPackage here:
C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin

If you cannot edit your PATH due to company restrictions, use this at the beginning of your script:
SET PATH=%PATH%;C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin

Now, SQLPackage needs three basic parameters (over simplified, I know):
  1. Action
  2. Source
  3. Target

On my system, this is the command I will use:

  sqlpackage.exe /a:Extract /scs:Server=US1213113W1\SQL2014;Database='+[name]+'; /tf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\'+[name]+'.dacpac'   

Make sure this is all one line.  CMD will freak out on the CR;LF and tabs.  Also, change the server name in /scs and path in /tf.  /a is the action....we are Extract-ing the schema into a .dacpac file named in the /tf parameter.

Paste this into a CMD window, hit Enter and you should get this back:

Connecting to database 'Test' on server 'MyLaptop\SQL2014'.
Extracting schema
Extracting schema from database
Resolving references in schema model

Successfully extracted database and saved it to file 'C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac'.

If it fails, the error messages are pretty specific about servername, database, etc.

Dynamically create a script that generates the SQLPackage call:

Now on to the part that gets you to multiple databases "at once."  Technically, this is not all at once...there are multiple commands being run in order, not in parallel.  But, you can still check Twitter or get coffee while they run instead of sitting in "Point and Click Heck".

For comparing to the live databases, I used:

 Set NoCount On  
 -- Extract the gold copy from Test  
      'sqlpackage.exe /a:Extract /scs:Server=US1213113W1\SQL2014;Database='+[name]+'; /tf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\'+[name]+'.dacpac'  
 from sys.databases  
 where [name] like 'Test'  
 --Create a compare script with test.dacpac as the source  
 --and all databases starting with Test as the targets  
      'sqlpackage.exe /a:Script /sf:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Test.dacpac /tsn:US1213113W1\SQL2014 /tdn:'+[name]+' /op:C:\Users\hillke4\Documents\SQLScripts\DACPACS\Deltas\'+[name]+'.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True'  
 from sys.databases  
 Where 1=1  
   and [Name] like 'test%'  
   and [Name] <> 'Test'  

Note the parameters in the Compare section:

    /a - the action to be performed.  Script creates a .sql file with the changes to be made at the target
    /sf - source file to compare from.  the "gold copy"
    /tsn - target server name
    /op - output path for the .sql file
    /p - Property setting to drop tables, views and other objects that should not be there
    /p - Property setting to drop indexes that should not be there (different from other objects)

There are a ton of different '/p' settings and options.  If you don't include the first one I did (dropobjectsnotinsource), you may get back an empty .sql file even when you know there are differences.  This was the topic of my follow up question on Stack Overflow.  Schema Compare in VS showed the new objects, CMD didn't.

Go to the SQLPackage.exe link for all of the specifics.   This is highly configurable to your needs.

Your output should resemble:

 Extract the Gold .dacpac:  
 sqlpackage.exe /a:Extract /scs:Server=MyLaptop\SQL2014;Database=Test; /tf:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac  
 Compare to the target databases:  
 sqlpackage.exe /a:Script /sf:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac /tsn:MyLaptop\SQL2014 /tdn:Test1 /op:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Deltas\Test1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True  
 sqlpackage.exe /a:Script /sf:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac /tsn:MyLaptop\SQL2014 /tdn:Test2 /op:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Deltas\Test2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True  
 sqlpackage.exe /a:Script /sf:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac /tsn:MyLaptop\SQL2014 /tdn:Test3 /op:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Deltas\Test3.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True  
 sqlpackage.exe /a:Script /sf:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac /tsn:MyLaptop\SQL2014 /tdn:Test4 /op:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Deltas\Test4.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True  
 sqlpackage.exe /a:Script /sf:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Test.dacpac /tsn:MyLaptop\SQL2014 /tdn:Test5 /op:C:\Users\Kevin3NF\Documents\SQLScripts\DACPACS\Deltas\Test5.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True  

Copy and paste the /a:Extract line into a CMD window (don't forget the PATH variable mentioned earlier) if you have not already done this part.  Run it by pressing Enter.

Copy all of the /a:Script lines (plus a carriage return at the very end) into a CMD window, and they will start running immediately.  Without the final CR;LF at the end, the last one will just sit there waiting for you to hit enter.

Any yellow text you see is warning you about possible data loss on dropping Tables and Indexes.

*** The object [Test] exists in the target, but it will not be dropped even though you selected the 'Gene
rate drop statements for objects that are in the target database but that are not in the source' check bo
*** The object [Test_log] exists in the target, but it will not be dropped even though you selected the '
Generate drop statements for objects that are in the target database but that are not in the source' chec
k box.

*** The column [dbo].[Orders].[CustomerName] is being dropped, data loss could occur.

The first two are the data and log logical file names, and the 3rd is warning about data loss from dropping the CustomerName column that was added to Test4.   The dependent view also gets a metadata refresh after that column is dropped...a nice feature to have.

View the results, looking for exceptions:

At this point, you should have a .sql file for each of your target/client databases, assuming no connection issues.   Ideally, they are all in the same place, which will help with the last step (actually running the scripts is your task...I'm just taking you through generating them).

The way I choose to analyze mine is not to look at each one, but rather to look for exceptions in the size of the file.   There is some text in each file that is exactly the same.   Any additional text is T-sql code that drops, creates or alters something.

Sample output .sql file, with the DROP statements in red:

Deployment script for Test5
This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
:setvar DatabaseName "Test5"
:setvar DefaultFilePrefix "Test5"
:setvar DefaultDataPath "c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\"
:setvar DefaultLogPath "c:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\"
:on error exit
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
:setvar __IsSqlCmdEnabled "True"
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
        ALTER DATABASE [$(DatabaseName)]
USE [$(DatabaseName)];
PRINT N'Dropping [dbo].[VOrderDetails]...';
DROP VIEW [dbo].[VOrderDetails];
PRINT N'Update complete.';

My technique for comparing file sizes is to open a CMD Window by using Shift-RightClick on the folder they are in, choose 'Open Command Window Here', then run a 'DIR', which will list the files with the size in bytes.  Windows Explorer default is KB.  There are no doubt a bunch of ways to do this, so pick whatever you prefer.   My results:

Note that the three we didn't change are all 1,330 bytes.  Test4 is larger, as is Test5.  They are different from each other due to the exact T-SQL in them to perform the drops necessary to bring them back to gold.  These are my exceptions.

In my environment, it is appropriate for me to stop here and start going through the exceptions manually.  For you, it may be appropriate to automatically execute the scripts.  Look at the SQLPackage link from Microsoft for the /Action: Publish General parameter.

WARNING:  Publishing a .dacpac file that has any data in it overwrites the existing data in the target. This may be fine for a 'State' table, but not at all for Orders.

Note:  If you are comfortable with PowerShell, please go look at this article from Chris Sommer, which he wrote in response to me asking the Compare Multiple Databases question.  It covers the same basics I just did, but without the looping to get all the DBs at once.   He did the script and blog in about 2 hours...

Note2:  All of my testing and scripting was done on a laptop, running Windows 7, SQL 2014 Dev edition, Visual Studio and SSDT 2015.

Note3:  Special thanks to whoever wrote this Code Formatter for Blogspot posts!

All of this works in my environment, but its possible I missed a step in the writing.  Please help me QA this post.  If something is not clear or I made some egregious error, please please please make a Comment.  If it works, and helps you please let me know that as well!

Thanks for reading,

The OnPurpose DBA


Min and Max Server Memory in English

This one is for the new DBAs...

There is a lot of confusion on memory settings in SQL Server.  Specifically Min and Max settings found in the Properties of an instance:

There are dozens, if not hundreds of blog postings that will go into beautiful detail about all of the intricacies of SQL Server memory...after all, SQL lives there!   Thus, it is very important to have a basic understanding early in your SQL DBA career....especially if you are an accidental DBA.

In the screenshot above, I have set the Min and Max on my laptop to 2GB and 14GB.  Its a 16GB laptop, so I left 2GB for the O/S...pretty basic stuff.

Max Server Memory is fairly obvious...when SQL Server reaches this point, it stops allocating and starts paging to disk as necessary.

This is pretty much the same as setting a throttle control on a car.  Or a restrictor plate in NASCAR.  Without it you can run the car engine all the way to the max if you want, but eventually the other systems are going to fail (Cooling, transmission, brakes, etc.).  Same thing if you don't leave memory for the O/S.

Min Server Memory seems to get the most bad information spread around.   SQL Server does NOT automatically grab memory up to the Min setting when it starts.  However, once it gets there, it doesn't give back.

Back to the car analogy...if you start up and head off down the road at 20 mph, you are above the default (0), but not at the max (100+, depending on the car and the tires...).  If you set the cruise control, you can accelerate up and down above 20, but you won't go below that unless you hit the brakes.

So that's it...by default, SQL installs at idle and full speed ahead.  Its your job to turn on the cruise control and not redline the engine until it blows.

There are holes in the analogy if you dig deep enough, but this should give you a real-world base to work from as you grow in your skills.



Login Failed, pt 2

In my last post I hoped to convince you to pay attention to all of the various "Login Failed for user..." messages that you see in your SQL Server ERRORLOGS.   ALL of them.

Yes, some you can ignore based on the environment or the person.   Jim the web guy on a Dev box is just not that much of a security threat (unless you let him touch Prod, but that's a different post).

Some of you have one or two servers, and reviewing ERRORLOGs is no big deal to do manually.  More of you have tens and tens of them.   Some of you have thousands (I'm looking at you in Managed Hosting environments such as Verizon, Rackspace, etc. where customers pay you to do this).

By now, hopefully you are aware that you can issue queries against all servers or groups of servers at once.   If not, a very quick how-to in SSMS:

  1. View-registered servers (Ctrl-ALT-G)
  2. Expand Database Engine
  3. Right-Click Local Server Groups
  4. Set up groups as you see fit (mine are Prod and non-prod)
  5. Register servers in the appropriate group

To query a group, right-click the group (or Local Server Groups for all of them), select New Query and off you go.  The results will append the server name as the first column.

Note that my servers are listed in order, but the results are not.   Results come in the order SQL Server was able to connect and receive the results back:

Side note...running Select @@version is a great way to ensure all of the SQL Servers are up and running, especially after a Patch weekend, or even just first thing in the morning.

Now for the stuff you actually wanted to read/learn...how to read the ERRORLOGS all at once:

We are going to dump the results of sys.xp_readerrorlog into a temp table, and then query just like any other table:

Create Table #Errorlog
(Logdate datetime,
 ProcessInfo varchar(50),
 LogText varchar(5000))
--Dump all the things into the table
insert into #Errorlog
EXEC sys.xp_readerrorlog
 0 -- Current ERRORLOG
,1 -- SQL ERRORLOG (not Agent)
--Query just like you would anything else:
Select *
from #Errorlog
Where 1=1
and (LogText like '%Error%'
or LogText like '%Fail%')
And Logdate > getdate() -3
--Clean up your mess, you weren't raised in a barn!
Drop Table #Errorlog
My results:

If I'm doing this on a Monday, I set the date to look back to over the weekend...otherwise 1 or 2 days.  But whatever works best for you.  There is nothing magic in here.  For more details on xp_readerrorlog, click the link.  No point in me re-writing a perfectly good explanation.

Hopefully this will help you pay more attention to what's going on in your ERRORLOGs, whether for Login Failed, or even just to find all backups for a certain DB.  Just change the Where clause to fit your needs.

If I saved you any time at all, I've done my job.  Let me know if you actually use this!

Fries are awesome, but I'm trying to ShrinkFile my gut...



Security fail.

This...just no:

Relax...its ok...

Sometimes its good to sit back, listen, nod and hear what is being said before speaking.   Actually, that is almost always the best idea.

Case in point:

I am the team lead here (small team of 3...SQL, Windows and storage admins...we overlap.).

I cam back from lunch yesterday and one of my guys very passionately launched into "We need to have a meeting!", "The developers want too many deployments!", "We need change management!", etc.

All of his points were true.  This is a small team with very few procedures and practises, and our job is to get a handle on this.   We are also at the end of the development process for v1.0 of an internal application...which is being demonstrated today.   Not the best time to suddenly change things.

So I listened while he made his case, agreed with most of what he said and asked some questions when he was done:

1.  What problem are you trying to solve by forcing change windows today that don't exist?
2.  How many "deployments" are we being asked to do each day?  (A deployment here could simply be ALTERing a stored proc, and the target is a Pre-Production database)
3. Should we be focusing on the other issues here we have already identified?  Where does this rank in the list? (Backups, security, perf, etc. all rank higher and are more actionable)

What it boiled down to is that we don't really have a problem...he just got hit with three requests in a short time frame, due to the upcoming demo to the executive staff.

We get maybe 2 requests a day from the Devs, and have 3 people capable of deploying them.  At this time, on this project...all a forced window will do is alienate 10 of the 15 team members.   Yes, it is a good idea, but lets phase it in for better acceptance, when the team is not under the gun.  Production release is only a month away...

Sometimes its best to relax, look at the bigger picture and make the best decision for the team.

Imma buy this guy lunch, with fries :)



Refreshing SQL Server development databases

Refreshing the Dev environment

I started a new contract recently.  This is a global company, but I am on a relatively small product team within it.  10 developers, 3 admins (SQL, O/S, Storage), 2 Business Analysts.

The company has a formal Change Management process, which is great.  However, this team manages and operates outside of that structure...not so good.   Devs have sysadmin to production. For now.

I and the other DBAs are working on all of the things you would expect to be missing from this sort of environment...consistent backup strategy, documentation, proper security, etc.

As with most places our Developers have a Dev copy of the database and are told not to touch the Pre-Prod version.  But, since we are test loading data there, Dev is way out of date.  Also, there are objects in Dev not moved to Pre-Prod yet.  So, they regularly point their queries, procedures and even the website to the PP database.  And then forget what they are connected to after lunch.

This makes for interesting hallway conversations!

The solution of course is to refresh the Dev db from Pre-prod, without losing any Dev code or users.

DBAs that have been around for awhile know where I am going with this...

Basic steps:

1. Backup the Production (or Pre-prod in my case) database.   Or pull from your overnight backup.   You DO have an overnight backup, right?   RIGHT?!?!?   ;)

2. Restore that backup to the Dev server as MyDB_Refresh:

-- Get the logical and physical filenames
Restore filelistonly
From disk ='B:\Backup\FULL\MyDB_FULL_20160810_000012.bak'

---Restore, not overwriting the existing Dev db
Restore Database MyDB_Refresh
From disk ='B:\Backup\FULL\MyDB_FULL_20160810_000012.bak'
With move 'MyDB' to 'B:\Data\MyDB_Refresh.mdf'
       ,move 'MyBD_log' to 'B:\Log\MyDB_Refresh_log.ldf'

3.  Use SQL Server Data Tools/Visual Studio, or a 3rd party tool such as Red Gate SQL Compare to compare and synchronize the schema in MyDB (Dev copy) to MyDB_Refresh.  SSDT works well for this if you don't have a license for a vendor tool, or don't want to download a trial version.  The Refresh database is the Target...

4. Once you have the tables, procedures, USERS, and other objects exactly the same as the old Dev database, you need to rename both databases:

--Make sure all connections are closed:
Use master;

exec Sp_renameDB 'MyDB', 'MyDB_Old'

exec sp_renameDB 'MyDB_Refresh', 'MyDB'

If you are having trouble getting exclusive use because Devs or apps keep re-connecting, you need to use a 2-step process (run both at the same time):

--Take the database offline and kill connections
Alter database MyDB Set offline
with rollback immediate

--Bring it back online and rename all at once
Alter database MyDB set online
use master;
Sp_renamedb 'MyDB', 'MyDB_Old'

Change the database names if you need to do the same on MyDB_Refresh.  You can use the same offline/online code when you are doing an emergency restore in prod and the app servers won't let go of their connections.

Compare a select of tables between Prod and Dev to make sure the data is refreshed, and double check using schema compare that Dev is different than Prod (likely, since that is what Devs do...).

This is a basic process, best done in a maintenance window, early in the morning or late at night.  And you can automate most of this.  I have not tried to automate schema compares other than using SQLPackage.exe in the SSDTools to "upgrade" a target data-tier application, or create a Drift report.   But that is way beyond the scope of this post.  Maybe another time :)

Please feel free to post up your processes, or enhancements to this one that some new DBA or Windows admin may be able to use 3 years from now when he finds this post on Google.

Have a fantastic day!



Join vs. IN Not IN

Experienced DBAs and Developers...might as well pass on by this one :)

Today, I want to turn SQL into English for (mostly) Developers and (some) DBAs that are asked to develop or tune queries.

No matter where I go, I always run into a Dev that loves to use NOT IN.   I get it.  Its simple, it makes sense, and it works.  You will get back the correct rows.

BUT!!!   Lets talk through a simple example as to why a JOIN is going to be far more efficient and make your DBAs and managers very happy....


You are a book lover, and want something new to read.  So you go to the local Narnes and Boble.   When you get there, you find something that sounds familiar, but you can't remember if it is in your collection at home or not.

The Setup:

I created a table (AllBooks) with a thousand rows and another table (MyBooks) with 49.  Both have a BookID (pretend its the ISBN number so the analogy works).

My two choices for determining what books I do not own...look for books NOT IN my current collection, or JOIN the two lists (yeah...the analogy breaks down a bit here, but stick with me).

The Queries:

Basic stuff, right?   Both will return 951 records (books) that I do not own.  And, very quickly...because the tables are tiny.   Sub-1 second is fast.

The issue here is HOW the rows are compared.

English version now, techy stuff later:

In the first query, this is equivalent to you standing at the bookstore and calling home to have someone check to see if the book in your hand is already in your collection.  EVERY time.  One by one.

In the second, you got really smart and brought a list with you, which you are comparing to the books on the shelf at the store.   You've got both "lists" in one place, so it is far more efficient.

Techy stuff/Execution Plans:

Disclaimer...I'm not trying to teach you to read execution plans or even know the deeper meaning of the various operators...just going to show the plans behind the queries above.  If you DO want to know more than you can ever use, go download Grant Fritchey's e-book.   And Follow/Stalk Grant on Twitter.  He loves that.

The first query uses Nested Loop operators to work through the two lists:
"For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows"

The second uses a Hash:
"Use each row from the top input (MyBooks) to build a hash table, and each row from the bottom input (AllBooks) to probe into the hash table outputting all matching rows"

These were run at the same time.  85% of the time spent on the NOT IN, compared to 15% on the JOIN.   This was for a very small sample of data...1000 rows and 49 rows.   Take this up tens of thousands, or millions and you'll start to see significant delays in getting results using NOT IN.

Short story long...if you are writing or tuning queries, this is an easy win.

Go get some fries.  And some books....49 is a pitifully small number for someone of your intelligence and ability :)



Login Failed for user...

We've all seen them.

Login failed for user 'MyDomain\Bob' (password issue)
Login failed for user 'MyDomain\Nancy' (default database issue)
Login failed for user 'blah, blah, blah...'

But what about Login Failed for user 'Insert Chinese characters here', Reason, An attempt to logon using SQL Authentication failed.

Wait...nobody in the company has a username with Chinese characters.   And we don't have SQL Authentication turned on....

Do not just let these messages pass you by!

These come with a client IP address at the end.  I did a ping -a on the one I got, and found:

Somebox.qualys.morestuff.mydomain.com, along with 4 replies.   So at least it was a valid internal IP address.

From here, I noticed Qualys in the machine's FQDN.  As luck would have it I was recently on a Vulnerability Management team (elsewhere), and Qualys was the name of one of the scanning tools we used to look for Vulnerabilities on the servers, routers, etc.

Now...I can make assumptions, but I'm not going to when it comes to something like this.   I checked all the SQL Servers in my area of responsibility and found this on all but one of them.

I wrapped all of the data and findings in a nice package and sent it off to the boss to engage the security team for proper investigation and remediation.  I suspect the Qualys server has a problem...this doesn't look like one of its checks, but I'm not the expert on that.

So the point of this is not to teach you about all the ways to trouble shoot login failed messages, but rather to make sure you are investigating who is failing to log into your SQL Server and WHY.

That is a serious RGE and CLM you don't need.

That is all for today.

Waffle fries for lunch :)

The OnPurpose DBA

This page is powered by Blogger. Isn't yours?