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.