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, July 5, 2016
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.
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.
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
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:
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
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:
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
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
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.
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.
And that's it! I now have a domain, talking on a secondary LAN, that my member servers can reach!
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!
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!
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.
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
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
If you right click the message, and click "Help", what you get isn't really all that helpful:
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!
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!
Subscribe to:
Posts (Atom)