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

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;
go

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

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
begin
set @sqlToRun = '';
set @sqlToRun = 'USE ' + @currentDB + '; create table t1(c1 int);';
--print @sqlToRun;
exec sp_executesql @sqltorun;
fetch next from LoopDBs into @currentDB;
end
close LoopDBs;
deallocate LoopDBs;
GO

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
begin
 set @sqlToRun = '';
 set @sqlToRun = 'USE ' + @currentDB + '; create proc usp1 as select getdate();';
 --print @sqlToRun;
 exec sp_executesql @sqltorun;
 fetch next from LoopDBs into @currentDB;
end
close LoopDBs;
deallocate LoopDBs;
GO

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
begin
 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;
end
close LoopDBs;
deallocate LoopDBs;
GO

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

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;

GO



SELECT TOP 123 N=IDENTITY(INT, 1, 1), NULL AS somenum

INTO dbo.Numbers

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



ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

GO

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;

DECLARE @currID BIGINT = 0;

DECLARE @maxID BIGINT;



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

BEGIN

 UPDATE dbo.numbers

 SET somenum = RAND()*100

 WHERE N > @currID AND N <= (@currID + @batchsize);



 SET @currID = @currID + @batchsize;



END

GO



-- prove it worked

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

GO

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 10.0.1.110 -PrefixLength 24 -Type Unicast -DefaultGateway 10.0.1.100

Set-DnsClientServerAddress -InterfaceAlias Ethernet1 -ServerAddresses 10.0.1.100

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

Restart-Computer

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 10.0.1.110 -PrefixLength 24 -Type Unicast -DefaultGateway 10.0.1.100

Set-DnsClientServerAddress -InterfaceAlias Ethernet1 -ServerAddresses 10.0.1.100



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

Rename-Computer "MemberServer"

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

Restart-Computer

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

10.0.1.100 PDC
10.0.1.110 Server #1
10.0.1.120 Server #2
10.0.1.130 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 10.0.1.100 -PrefixLength 24 -Type Unicast -DefaultGateway 10.0.1.100

Set-DnsClientServerAddress -InterfaceAlias Ethernet1 -ServerAddresses 10.0.1.100


Rename-Computer "pdc"

Restart-Computer

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!

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:
https://www.microsoft.com/en-us/evalcenter/

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.