Tuesday, December 27, 2016

On Vacation!

Happy New Year to you! I'm on vacation this week, and will see you after the new year!

Tuesday, December 20, 2016

Merry Christmas!

Merry Christmas to you ! Here's hoping you enjoy your holidays with family, no matter where you are.

Tuesday, December 13, 2016

Scripts from Index Defrag DBA Minute

Here's the scripts from last week's DBA Minute on Index Fragmentation.

set nocount on;
use WideWorldImporters;

if OBJECT_id('dbo.TblHeap') is not null drop table dbo.TblHeap;
if OBJECT_id('dbo.TblHeapWithPK') is not null drop table dbo.TblHeapWithPK;

select sysdatetime() as [StartAt];

CREATE TABLE dbo.TblHeap (
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why

TblHeapid BIGINT IDENTITY primary key,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why

select sysdatetime() as [TablesBuilt];

--Insert 10,000 rows.

WHILE @i <= 10000
 INSERT dbo.TblHeap (col1) SELECT REPLICATE('ASDF',200) ;
 INSERT dbo.TblHeapWithPK (col1) SELECT REPLICATE('ASDF',200) ;
 SET @i = @i + 1;

select sysdatetime() as [TblHeap built];

-- what's in the tables?
select top 10 * from dbo.TblHeap;
select top 10 * from dbo.TblHeapWithPK;

-- frag level?
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);

-- force page splits
update dbo.TblHeap set col1 = REPLICATE('x',1024);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',1024);
update dbo.TblHeap set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeap set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);

delete from dbo.TblHeap where (TblHeapid % 5 ) <> 0;
delete from dbo.TblHeapWithPK where (TblHeapid % 5 ) <> 0;

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);


select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);

Tuesday, November 29, 2016

SQLCMD vs. Registered Servers

I always like it when my regular testing produces a usable example of something fun. For example, for a long time I have held that even on a local VM, connecting using SQLCMD is slower than Registered Servers.

I love SQLCMD, don't get me wrong, and I've written some very useful system maintenance scripts that would be very, very difficult to have gotten right any other way. That said, in my opinion, SQLCMD is just another tool for us to use when solving various problems.

For what I wanted to do with this test, though, was run a dead-simple query, SELECT GETDATE(); against all seven SQL 2016 instances on my VM.

Using SQLCMD, it took 29 seconds to run, and using Registered Servers, the same query against all seven instances took less than one second. Amazing performance.

I then added a bit of timing metrics to the output of the SQLCMD version, and figured out that SSMS, using Registered Servers, is so much faster because it's a multi-threaded, multi-connecting application. SQLCMD is, but it's very nature, single threaded, due to running all commands in one SPID.

Like I said earlier, I've had great experiences using SQLCMD and use it regularly for a whole host of things, but it's selling point isn't speed.

Tuesday, November 22, 2016

Enjoy your Thanksgiving!

If you're in the US, enjoy your time with family and friends this week for the Thanksgiving Holiday!

Tuesday, November 15, 2016

Another "Oh Nice!" Moment

I didn't realize you can log in to a remote instance using SQL Server authentication and SQLCMD from with SSMS.

The syntax looks like this:

:CONNECT servername\instance -U usernamegoeshere -P passwordgoeshere

Tuesday, November 8, 2016

Summit 2016 Wrap Up

This year, I was fortunate enough to be able to attend PASS Summit 2016.

As a first timer, I have a few post-Summit thoughts:

  1. Take hand sanitizer next time. Wound up having to buy some from a local pharmacy, and it was an easily packed item.
  2. Pack extra vitamin C + cold/flu prevention strategy. Again, I ran out, and had to buy some from a local place.
  3. Take an extra suitcase. Books, goodies, mementos for the kids all add up, and it would have been easier to pay for a second checked bag than throwing things away.
  4. Remember the kilt. It was awesome seeing the display of support for the Women In Technology lunch.
  5. Build in a weekend before or after to decompress, best spent in Seattle doing "stuff". I flew in on far too tight a timeline this time around, and missed seeing all the city had to offer due to an almost punishing schedule.

Tuesday, November 1, 2016

The type initializer has failed ! Oh Noes!

So I was trying to connect to my VM, and I got a very strange error.

The full error message says

Cannot connect to
The type initializer for "Microsoft.SqlServer.Management.Common.ConnectionInfoHelper" threw an exception. (Microsoft.SqlServer.ConnectionInfo)
AccessToken (Microsoft.SqlServer.ConnectionInfo)

As I was researching why my copy of Management Studio was unable to connect, I realized I was still on a previous version, 13.0.15700.28, and that the most current version was 13.0.16000.28.

One upgrade later to the latest release of Management Studio 2016, and I'm back in business, and able to connect successfully.

Wednesday, October 19, 2016

Getting Perfmon Data

In so many of the SQL Server instances I manage, getting to the console isn't possible (or isn't advisable) and having the ability to get these things via SQL is a great help.

To do that, we can grab a quick select like this, to see the various counters available:

select *
from sys.dm_os_performance_counters

For example, if I wanted to monitor how many user connections I had throughout the day, I could save the results of the query below to a monitoring table.

select *
from sys.dm_os_performance_counters
where object_name = 'MSSQL$WORLDWIDE:General Statistics'
and counter_name = 'User Connections';

Tuesday, October 11, 2016

Creating a stored proc in every database

So, the other day, I needed to create a stored procedure in every database. This is what I came up with.

First, we need to make a test set of simple, empty databases:

use master;

create database CreateSPTest1;
create database CreateSPTest2;
create database CreateSPTest3;
create database CreateSPTest4;
create database CreateSPTest5;

Now, here was my first pass at writing a simple loop to get an object created in the target databases.

declare @currentDB sysname;
declare @sqlToRun nvarchar(max);
declare LoopDBs cursor for
select [name] from sys.databases where name like '%SPTest%';
open LoopDBs;
fetch next from LoopDBs into @currentDB;
while @@FETCH_STATUS = 0
set @sqlToRun = '';
set @sqlToRun = 'USE ' + @currentDB + '; create table t1(c1 int);';
--print @sqlToRun;
exec sp_executesql @sqltorun;
fetch next from LoopDBs into @currentDB;
close LoopDBs;
deallocate LoopDBs;

Next, I wanted to extend my loop, above, to create an uber-simple stored procedure in every database. Here's where I hit my first roadblock. No matter what I seemed to, I received an error that looked like this:

Msg 111, Level 15, State 1, Procedure usp1, Line 1 [Batch Start Line 36]
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

For what it's worth, here's the loop I was using. As you'll see, the only difference between this and the snippet above is the set @sqlToRun variable.

declare @currentDB sysname;
declare @sqlToRun nvarchar(max);
declare LoopDBs cursor for
select [name] from sys.databases where name like '%SPTest%';
open LoopDBs;
fetch next from LoopDBs into @currentDB;
while @@FETCH_STATUS = 0
 set @sqlToRun = '';
 set @sqlToRun = 'USE ' + @currentDB + '; create proc usp1 as select getdate();';
 --print @sqlToRun;
 exec sp_executesql @sqltorun;
 fetch next from LoopDBs into @currentDB;
close LoopDBs;
deallocate LoopDBs;

Lastly, this is what I came up with after a quick Google-Fu. In short, I found that sp_executesql calls can be nested, and, if the first call to sp_executesql contains the USE statement, then the second will operate against the proper database.

declare @currentDB sysname;
declare @sqlToRun nvarchar(max);
declare @sqlForInside nvarchar(max);
declare @params nvarchar(4000);
set @params = '@sqlToRunInside nvarchar(max)';
declare LoopDBs cursor for
select [name] from sys.databases where name like '%SPTest%';
open LoopDBs;
fetch next from LoopDBs into @currentDB;
while @@FETCH_STATUS = 0
 set @sqlForInside = 'create proc usp1 as select getdate();';
 --print @sqlToRun;
 set @sqlToRun = '';
 set @sqlToRun = 'USE ' + @currentDB + '; exec sp_executesql @sqlToRunInside';
 exec sp_executesql @sqltorun, @params, @sqlToRunInside = @sqlForInside;
 fetch next from LoopDBs into @currentDB;
close LoopDBs;
deallocate LoopDBs;

Tuesday, July 5, 2016

DBA Minute: Get rid of a database

Today we’re going to be reviewing four ways to get rid of a database from a SQL Server
• Method #1 – Drop via GUI
• Method #2 – Drop via TSQL
• Method #3 – Detach via GUI
• Method #4 – Detach via TSQL

Tuesday, June 28, 2016

Sample Data Generator

OK, so sometimes I need to really quickly make a sample table to destroy or modify or whatever.

When that's true, I use a form of the below to do so.

There's a couple of things I'd like to point out about this script.

First, you can run this in any database you have create permissions in, and it really doesn't get that big if you don't want it to. You want a larger table? Fine! Increase the value of SELECT TOP 123 to SELECT TOP 1234567 in the section labelled "Make the sample table"

Does our sample table exist? yes? nuke it. Then, create and fill it in one go, followed by a primary key.

IF OBJECT_ID('dbo.numbers') IS NOT NULL DROP TABLE dbo.numbers;

INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;


Now comes the fun part, where we update back with a quasi-random number. I say "quasi-random" because, with the batch size set at 500, and an initial set size of 123, from above, all the rows are going to get updated to the same value at the same time.

This is intentional, as when I was developing this, I needed to work on a grouping / windowing function, and I needed things that I could group, that I knew would group, into groups that I knew existed.

If you want something closer to a true random value for each row, set the batch size to 1, and run the script. It'll take longer, sure, but it'll look quite a bit more random.

-- declare the variables
DECLARE @batchsize BIGINT = 500;

-- set the loop limit
SELECT @maxID = MAX(N) FROM dbo.numbers;

-- loop over the inside query using the loop limiter to control, and
-- the batch size to control the size of "bites" we take out of the target
WHILE @currid < @maxID
 UPDATE dbo.numbers
 SET somenum = RAND()*100
 WHERE N > @currID AND N <= (@currID + @batchsize);

 SET @currID = @currID + @batchsize;


-- prove it worked
SELECT TOP 5000 * FROM dbo.Numbers WHERE n IN (1,12,123,1234,12345,123456);

Tuesday, June 21, 2016

A/D Test Lab, Rinse And Repeat

This part covers the non-unique portions of the lab setup - that is, those steps that can be done as many times as needed to achieve the desired state (i.e. Rinse And Repeat!)

The last step in this process is to stand up a server to eventually run SQL Server, and a domain user, because I DON'T want to be installing software as Administrator!

First, I'm going to make a new user, like so, while logged in to the domain controller:

New-ADUser -Name "Ironman" -GivenName Tony -Surname Stark -SamAccountName ironman -UserPrincipalName ironman@contoso.com -AccountPassword (ConvertTo-SecureString -AsPlainText "Password1!" -Force)
Enable-ADAccount ironman
Add-ADGroupMember "Enterprise Admins" ironman
Add-ADGroupMember "Domain Admins" ironman

Next, I'm going to adjust the member server, after having logged in as the Administrator account I created during Windows setup. Here's the script block:

$netadapter = Get-NetAdapter -Name Ethernet1

$netadapter | Set-NetIPInterface -DHCP Disabled

$netadapter | New-NetIPAddress -AddressFamily IPv4 -IPAddress -PrefixLength 24 -Type Unicast -DefaultGateway

Set-DnsClientServerAddress -InterfaceAlias Ethernet1 -ServerAddresses

The first half is essentially the same as what I listed in the Stand Up The Domain post, so let's walk through it.

First, I'm going to get validation that the adapter that I think exists, actually does. In my copy of VMWare, the adapters are returned by Get-NetAdapter as a zero-based naming scheme, so the first, and default, adapter is Ethernet0. I've added an adapter, so it's going to be Ethernet1. If for some reason, there was a problem, or I didn't hit "Save", or something else went wrong, then I won't have the adapter to configure, and the variable $netadapter is going to wind up $null.

Next, we disable the default DHCP. This line is going to prevent the adapter from calling back out to the VMNet2 network post-configuration and getting a potentially confusing IP address. It's not really bad that it gets one, if this machine is a client (Windows 10, for example), but since this is going to be a server, a dynamic address won't do.

An interesting side note is that the adapter to NAT with the host on VMNet0 DOES use DHCP, and I want it to NOT have a fixed address.

The next line is, really, the important one, as it sets the static IP address and settings.

Lastly, we find the DNS server being defined for this internal-only network being pointed at the Primary Domain Controller.

In the second half, then, what we have is

Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False

Rename-Computer "MemberServer"

add-computer -Credential contoso\ironman -DomainName contoso.com


This block will first completely disable the Windows Firewall (again, see my previous notes, and DON'T do this in Production).

Then, because we're working on a member server, and we're already logged in using the Administrator account, I can go ahead and rename the computer as a part of this script.

Next, I'm going to join the domain by calling the join command. It will prompt me for the password to use for the attempt, and after that succeeds, the script reboots the computer. Unless you are 100% certain that this is going to work when you run it, my advice is don't run the Restart-Computer until you're sure, as it'll reboot so fast, you won't get a chance to write down any error messages prior to the machine clearing the screen in preparation to reboot.

And, finally, here's the complete script for a member server:

$netadapter = Get-NetAdapter -Name Ethernet1

$netadapter | Set-NetIPInterface -DHCP Disabled

$netadapter | New-NetIPAddress -AddressFamily IPv4 -IPAddress -PrefixLength 24 -Type Unicast -DefaultGateway

Set-DnsClientServerAddress -InterfaceAlias Ethernet1 -ServerAddresses

Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False

Rename-Computer "MemberServer"

add-computer -Credential contoso\ironman -DomainName contoso.com


Tuesday, June 14, 2016

A/D Test Lab, Stand Up The Domain

Standing up the domain is a bit different from the "Rinse And Repeat" portion of this exercise. In some ways, standing up a new domain in a new forest is simpler, because this is the part I only have to do once, and these scripts don't change, even if I blow away the domain and bring it back, because I always have to have a domain controller!

To do so, I needed to create an Active Directory Forest, a domain within that forest, and change the settings on the server I installed that is going to act as my Primary Domain Controller (PDC).

Also, this is for a contained test lab, that is shut down at night, so I chose not to run Windows Firewall in between the machines. That IS something I want to add to these scripts, and it's planned for a later revision. Onward!

IP TABLE PDC Server #1 Server #2 Server #3
So, the first thing I need to do is set the IP address on the secondary controller - that is, the one attached to VMNet2 - so that the machines can have inter-domain conversations.

You'll also notice that, because I have to run these commands as Administrator, it's a good time to go ahead and rename the computer. I'll expand more on what the $netadapter lines do in the Rinse And Repeat post.

$netadapter = Get-NetAdapter -Name Ethernet1

$netadapter | Set-NetIPInterface -DHCP Disabled

$netadapter | New-NetIPAddress -AddressFamily IPv4 -IPAddress -PrefixLength 24 -Type Unicast -DefaultGateway

Set-DnsClientServerAddress -InterfaceAlias Ethernet1 -ServerAddresses

Rename-Computer "pdc"


Now, I want to install a new domain in my new forest. You'll notice that for purposes of this public-facing script, I'm sharing the ConvertTo-SecureString call, and in a production script, I would leave this out, for the wizard to prompt me for the proper value.

Install-windowsfeature -name AD-Domain-Services -IncludeManagementTools

Import-Module ADDSDeployment

Install-ADDSForest -domainname "contoso.com" -DomainMode 6 -DomainNetbiosName "CONTOSO" -ForestMode 6 -InstallDNS -Force -SafeModeAdministratorPassword (ConvertTo-SecureString -AsPlainText "password" -Force)

And that's it! I now have a domain, talking on a secondary LAN, that my member servers can reach!

Tuesday, June 7, 2016

A/D Test Lab, Build The Machines

In order to build the test lab, let's define what we're trying to do.

Here's a quick picture, but essentially I need to build a domain controller, a SQL Server (shocking, right?), and a client machine.

In order to do that, I'm using VMWare Workstation, and although I BELIEVE this will work under Hyper-V, I haven't tested it.  It's worth talking about here what my network setup is, which VMWare makes super simple!

It's a pretty straightforward system setup - during machine creation, I have one NIC in the machine, and then, after install, I install another NIC with a different MAC, pointed at  a private network, VMNet2, selected as machines only.

Here's how I set that up in VMWare. Open Workstation, and then navigate to Edit >> Virtual Network Editor.

Then, I created a new network with the following settings.

I'm using evaluation software for all this, which I got from US Technet evaluations, which you can find here:

It's worth noting that all the actual work of standing up the domain is separate from standing up the machines.

VMWare allows me to shortcut the process if it's an O/S that it knows about, and if not, then it still installs pretty quickly, albeit with a few more clicks of the mouse.

All the machines were loaded with a simple load, consisting of only the default administrator account, and no additional software.

Tuesday, May 31, 2016

A/D Test Lab, Introduction

The challenge:
I need to create a test lab using Active Directory, so I can run SQL Server in various testing configurations. Also, having my own domain under total control makes me giggle with uncontrollable personal glee, so there's that.

Anyway, back to the point.

I need to create a test lab, which has the following requirements:

1. Fully self-contained
All domain traffic should stay within the domain. This is important, as quite a bit of the work I want to do on this is going to involve being offline or on slow connections, and if my servers are hanging and timing out waiting for the slow internet connection I'm on at the time, I'm never going to get anything done.

2. Fully configured
This can't be a single-box setup, or I won't be able to configure things like SQL clustering. This has to be an actual domain, with a primary domain controller, member servers, etc.

3. Easily destroyed
One of the requirements for doing this is that I have to be able to blow away and bring back the entire thing within minutes. I don't have the patience to wait for hours for machines to build - ultimately, I want the entire thing, from pushing the "File >> New VM" button to starting the install of SQL to be as close as possible to 5 minutes as I can get.

4. Free
This entire setup HAS to use evaluation copies or release candidates. It has to work with what I can get for free, so I can be sure that not only can I do it for free, but anyone who follows along at home can do so, too.

I'm presenting below a series of articles about how I did exactly that. I'll cover these four steps

  • Building the machines
  • Stand up the domain
  • Add a domain user
  • Add the member server

Tuesday, May 24, 2016

SQL Server Error 0x8004100e

You might be forgiven for thinking that SQL Server 2014 components, every single one of them, should be backwards compatible with 2012.

For example, let's say I have a test Active Directory (AD) lab made up of two servers.

The first is the domain controller and has the SQL Server 2014 Management Tools - Complete installed on it.

The second server is the SQL 2012 machine, and it has only the database engine, although I've seen the same issue with the machines where more than just the engine is installed.

The good part of all this is that, using SQL Server Management Studio (SSMS), I'm able to use my test domain credentials and connect to the SQL Server 2012 instance, so I know SQL's working, the firewall is either proper or turned off (This IS a lab setup), and run queries, which return the expected results. That's the good news - to everyone else (users, clients, etc.) this machine looks and acts online.

The downside is that I need to administer this machine remotely, using Computer Management via Server Manager's "All Servers" tab.

And I can't, because I get the following error in Computer Management when I navigate to Server Manager >> All Servers >> *ServerName* >> Computer Management >> Services And Applications >> SQL Server Configuration Manager.

error 0x8004100e
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Noe that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid Namespace (0x8004100e)

If you right click the message, and click "Help", what you get isn't really all that helpful:
Microsoft has moved the documentation for this product online in order to provide the most current information to our customers. See the Microsoft Developer Network (MSDN) Resources page (http://go.microsoft.com/fwlink/?LinkId=299888) for more information.

Which, if you click the link, takes you to the page "Install SQL Server 2014"

Remember, now, that I'm dealing with a target server that has SQL Server 2012 on it, and I have 2014 tools on the machine I'm using to access it. Installing 2014 is a red herring - it's not the real issue!

The fix, for the test lab, was to install Client Tools Connectivity on the Domain Controller from the SQL Server 2012 media, and then, not surprisingly, to reboot.

Had this been a production domain, and not just a test lab, I think I would differently about the need to reboot, but there you have it!

Tuesday, May 17, 2016

Atlanta Bound!

I'm going to be presenting in Atlanta this weekend at SQL Saturday 521 on 21 May. I hope to see you there!

Tuesday, May 3, 2016

Baltimore Presentation

Thanks to everyone that came out to my Session on Auditing in SQL Server!

Credit and a "Thanks!" to Slava for the picture

Tuesday, April 26, 2016

Presenting in Baltimore on 30 April

So this weekend, I'm presenting in Baltiimore at SQL Saturday 506. If you're in town, swing over and join us!

Tuesday, April 19, 2016

SSMS 2016

In SQL Server 2016, SQL Server Management Studio (SSMS) has been de-coupled from the installer media. You can now find it here:

It'd stink to have an install with no SSMS, so there you go.

Tuesday, February 23, 2016

HammerDB under Windows 10 returns odd transaction count error

I'm running Windows 10, and am trying to run HammerDB using a 4K display as my primary monitor against SQL Developer 2014.

First thing I noticed is that the tree of choices is missing. A quick drag from the LEFT (!) made the tree of choices appear.

So I set up a quick test using Autopilot, 2 users, and logged the results. To which, HammerDB said it couldn't get the transaction counts, because SQL Server returned a non-numeric transaction count data.


Tuesday, January 12, 2016

Disconnected monitors under Windows 10

I'm running Windows 10, and recently got a new docking station, and needed to disconnect a display port shown by the dock as "active".

Display settings >> Multiple Displays >> Disconnect This Display >> Apply

In Windows 7, the monitor disconnected and the icon in "Display Settings" for the specific monitor would go gray, and still be there.

To reconnect the now-disconnected monitor in Windows 7 was easy:
Clicking on the gray icon >> "Extend desktop to this display" >> Apply.

In Window 10, the display disappeared when I disconnected it in "Display Settings". Hitting "Detect" revealed absolutely nothing!

What I've found is that there's more than one way to manage your displays. (!)

For example:

  1. Open the control panel
  2. Click "Adjust screen resolution" under the "Appearance and Personalization" section. 
  3. Click the missing display, which should be black now
  4. Click Multiple Displays >> Extend desktop to this display
  5. Click Apply
Et Voila! There's your missing monitor!