Category Archives: Videos

Move DB Files with Regex

Hey guys, I just posted a new vid on how to use Regex to format DB files so you can easily move them.
The situation is this… you’ve got lots of DBs you want to move to a new drive or to a new server, and you need to write the ALTER DATABASE commands to move all the files, then you need to script the move statements at the cmdline like Powershell. The problem is to be effective, you need some code to parse the filename from the rest of the path.
Here I’m going to show you how to use Regex to parse that out so you can easily build the statements you need. I can write the script to move hundreds of DB files in under a minute. Come watch.
http://midnightdba.itbookworm.com/Video/Watch?VideoId=407

And here’s the demo code so you can follow along.
MoveDBFiles

Powershell till you drop

Ok, well I’ve been very busy again and released 3 new PS vids today. 

There’s one on dropping tables.  I use regex to make this happen, so even if you’re not interested in dropping tables, you can come learn how to do a simple regex.

http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellDropTables/PowershellDropTables.wmv

 

The next one is on truncating tables.  Here I just limit it by a specific schema.

http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellTruncateTables/PowershellTruncateTables.wmv

And the last one is just damn good.  I show you how to get sp_configure functionality in PS.  There’s a trick to it so don’t discard it cause you think you can figure it out on your own. 

http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellServerConfigSettings/PowershellServerConfigSettings.wmv

 

Get IP and DNS in Powershell

Hey guys, I posted a new video last night on how to get IP and DNS info from your servers.  I know there are more ways to do it so if you guys have a way you like better send it to me and I’ll make another vid.

http://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspxhttp://midnightdba.itbookworm.com/VidPages/PowershellGetIPandDNS/PowershellGetIPandDNS.aspx

 

Hey, how about some new Powershell vids?

I’ve been a busy little guy this week.  I’ve posted 4 new videos.

The first one is on cycling the SQL error log from Powershell.  Well, not really, you’re really deploying the solution to other boxes using Powershell.
You can see it here:
http://midnightdba.itbookworm.com/VidPages/PowershellCycleErrorLog/PowershellCycleErrorLog.aspx

The next one is about reading the SQL error log from Powershell.

You can see it here:  http://midnightdba.itbookworm.com/VidPages/PowershellReadErrorLogs/PowershellReadErrorLogs.aspx

The next 2 are a short series on changing DB permissions in Powershell.
You can see them here:

http://midnightdba.itbookworm.com/VidPages/PowershellChangeSQLPermissions/PowershellChangeSQLPermissions.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellChangeSQLPermissions2/PowershellChangeSQLPermissions2.aspx

SSRS Videos

I posted 2 new vids this week on SSRS.  The first is a data-driven subscription primer that’ll get you up and running with not only what it takes to set it up, but the different methods you have for sending the results exactly the way you want them.  I even go out into Profiler to show you the effect it has on the system.  It’s about 30mins, and you can find it here:  http://midnightdba.itbookworm.com/VidPages/SSRSDataDrivenSubscription/SSRSDataDrivenSubscription.aspx

The 2nd vid is about deploying reports from VS.  This is a common question I get from noobs and because it’s not obvious how it works, I went ahead and filmed it.  I also cover one of the common problems you have when deploying reports using this method and how to solve it.  It’s about 4mins and can be found here:
http://midnightdba.itbookworm.com/VidPages/SSRSDeployReports/SSRSDeployReports.aspx

 

 

The answer is No

There are times in a DBA’s life when the answer is No.  And for once I’m not talking about a DBA asking a girl out.  This time I’m talking about requests we get from our users.  I had such a request today.

A manager of another group came up and asked for a generic account that he could use to connect to the DB from an application.  The answer is No.  Sorry dude, but we don’t give out generic account unless it’s absolutely necessary.  And of course, then the insistance sets in that he has to have the generic account because it’s an application.  I said no, the app itself is running under an acct, and I’ll be happy to give that acct the perms.  He said, well it’s not that easy, this is a web app.  And I said well, that changes things significantly, and you’re right it’s not that easy.  He said see, I told you.  I said, it’s even easier.

You see, with a web app you can set the app pool to run under any acct you like, so connecting to a DB under certain credentials is wicked easy.  And it really is.  He said well, we also need to be able to connect using SSMS to be able to run a couple SPs manually.  So we were hoping to be able to use that acct for that as well.  Again, No.  Give me the accts you want to have these perms and I’ll make it happen. 

Now that’s the end of the conversation, but here’s a word for you guys reading this.  There are several ways to make this happen depending on how you layout the group (either in AD or SQL).  The point I’m trying to make here is that sometimes you have to make sure you service your customers in a way that’s best for the company.  They quite often ask for something specific that you can’t give them, but you can give them the equivalent.  Often times users ask for things a certain way because that’s all they know.  They get too caught up in solving a problem so they try to solve the problem the only way they know how… and in this case it was asking for a generic account.  But this is where we as DBAs need to step up and have the courage to guide them.  They may get upset, they may even insist that it be done their way, but they’re not DBAs, and they’re ass isn’t on the line.  It’s our job to make sure that it gets done right.

Now, just for completion here’s a video I did quite some time ago that shows you how to run a website under a specific account.  It’s in IIS 6, but it’ll show you how it’s done, and you should be able to transfer that skill to IIS 7 if you have to.  And if the app has a windows service it’s even easier… just run the service under the account you like and you’re golden.

Change your process

This is an excellent example of how you need to be flexible with your processes, even when you’re in the middle of a project.

We started a project to move a DB to a new set of disks. Since the files are large, we probably weren’t going to be able to fit it into a single downtime so we were going to just move one file at a time over the next few weeks until they were all done. Well, due to circumstances out of our control, now they all have to be done at the same time. The problem is that now the file copies are going to take in excess of 6hrs, which is way longer than any downtime they would give us. I know, right? Don’t worry, I’ve got big problems with them forcing us to do operations in a large chunk like this, and then saying we can’t have time to do it. So we were doing our test last week and it did indeed take about 6hrs to copy all the files. And I don’t know why it didn’t hit me before, but why not change the process? The copy process was there because we were going to piecemeal the operation over several weeks, but since that’s gone, then maybe it’s time to come up with a new strategy.

So that’s what I did. My new strategy is an even simpler backup/restore op. All I have to do when I restore is map the files to their new locations and I’m golden. So so far it’s 6hrs to copy, and I know I can backup in 20-25mins. So my restore I’m guessing will be about 30mins (give or take).

Of course, the backup/restore won’t perform that well on its own. You have to tune it so it’ll use the resources to its advantage. This is where knowing how to tune your backups can come into play. And while I often say that tuning backups is quite often frustrating because you can’t use a lot of your resources because you’ve still gotta leave room for user processing on the box, this is one of those times that you can crank it all the way up. Everyone’s going to be offline for this op, so the box is completely mine. I can use every ounce of RAM and CPU on the server if I want. And that’s what I’m going to do. If you’re interested in how to go about tuning your backups, you can look at my recent SQLSAT session on the topic. I did it for SQL #90 in OKC. You can find the vid page for it here: http://midnightdba.itbookworm.com/Events.aspx

So anyway, the point is that just because you’ve come up with a way to do something, don’t set it in stone. If the scope changes in such a way that you can now do it a better way, then don’t be afraid to say “Stop, I’ve got a better way”. You may get some pushback from your peers because the project plan is already done and we need to just move forward with what we’ve got, but that’s when you need to push back and say no, this process was developed for a different circimstance and now it’s a different scenario completely. So this is no longer a viable method.

Powershell Homework Answers

Ok, I got some answers from the PS homework, so now it’s time for my solutions. If you didn’t get a chance to look at the assignments, take a look at them here real quick if you like and test yourself:
http://www.midnightdba.com/DBARant/?p=647
As with anything there can be multiple solutions to each problem, and while I may not give every single version of a solution, I’ll cover the major ones as I think of them. At times I’m also going to expand on the solution and show you a complete script you can use in your environment.

1. Get the total size of all the DBs on a server.

Method 1:

DIR | %{ $Total += $_.Size };
$Total

This is a basic method which doesn’t really require much explanation, but if you would like a full discussion of it, you can go here: http://www.midnightdba.com/DBARant/?p=488

Method 2:

DIR | measure -property Size -sum

Again, there’s not much to say about this.

However, as a bonus, here’s a lovely script that will give you some good size info on your DBs, and then give you the sums at the bottom.

param($ServerName = "localhost")
$ServerName

cd SQLSERVER:\SQL\$ServerName\DEFAULT\Databases;

$a = dir;

$Size = @{Label="Size(MB)"; expression={"{0:N2}" -f ($_.Size)}};
$DataSpaceUsage = @{Label="DataSpaceUsage(MB)"; expression={"{0:N2}" -f ($_.DataSpaceUsage/1024)}};
$IndexSpaceUsage = @{Label="IndexSpaceUsage(MB)"; expression={"{0:N2}" -f ($_.IndexSpaceUsage/1024)}};
$TotalSpaceUsage = @{Label="TotalSpaceUsage(MB)"; expression={"{0:N0}" -f (($_.DataSpaceUsage + $_.IndexSpaceUsage)/1024)}};
$SpaceAvailable = @{Label="SpaceAvailable(MB)"; expression={"{0:N2}" -f ($_.SpaceAvailable/1024)}};

$a | FT Name, $Size, $DataSpaceUsage, $IndexSpaceUsage, $TotalSpaceUsage, $SpaceAvailable -auto

#### Now get the total and put it into diff measures.

$Total = ($a | measure -property size -sum)

# $a | %{$Total += $_.size}

$TotalInMB = @{Label="Total(MB)"; expression={"{0:N2}" -f ($_.sum)}};
$TotalInGB = @{Label="Total(GB)"; expression={"{0:N2}" -f ($_.sum/1024)}};
$TotalInTB = @{Label="Total(GB)"; expression={"{0:N2}" -f ($_.sum/1024/1024)}};

$Total | FT $TotalInMB, $TotalInGB, $TotalInTB

CD C:\

DIR | %{ $Total += $_.Size };
$Total

2. Cycle through a list of servers and get the service account that all the SQL services are set to start with.

$Servers = "Server1", "Server2", "Server3"

$Servers | %{ 
$curServer = $_ ## Current server in the loop.
gwmi win32_service -ComputerName $_ | ?{$_.DisplayName -match "SQL"} 
} | Format-Table SystemName, DisplayName, StartName -auto

This is one of those that has a few subtle nuance versions, but there’s no need for me to go into each one.
However, here’s my original post on the topic.
http://www.midnightdba.com/DBARant/?p=386

3. Script all the SPs in a DB to a txt file. Make sure they’re separated by GO stmts or the script won’t run.

dir | %{$_.Script() | out-file c:\SP.txt -append; "GO" | out-file c:\SP.txt -append; $_.Name;}

There are a few ways to do this one as well, but again they’re just nuances. And when I say a few ways, I’m really only referring to the proper way to do it in PS, and not the overly complicated SMO versions. While you can definitely script this in SMO, it’s so long I don’t even consider it a solution in comparison to these 1-liners.
As well, I’ve got 2 vids that explain the topic in full. Here’s what you need to know:
http://midnightdba.itbookworm.com/VidPages/PowershellScriptDBObjects/PowershellScriptDBObjects.aspx
http://midnightdba.itbookworm.com/VidPages/PowershellScriptDBObjectsWithOptions/PowershellScriptDBObjectsWithOptions.aspx

4. Change the default file location for a group of servers.

$a = dir
$a.settings.DefaultFile = "C:\MyNewFiles"

Ok, while I haven’t officially blogged on this one, there are 2 similar blogs that show you how to get to the level you need to be in order to run this one. So follow those other ones, and you’ll know how to do this one.
http://www.midnightdba.com/DBARant/?p=589
http://www.midnightdba.com/DBARant/?p=601
There’s also my video that shows you how to do this and I think it’s more comprehensive.
http://midnightdba.itbookworm.com/VidPages/PowershellServerProps/PowershellServerProps.aspx

5. Cycle through all the jobs on a server and change all their owners to sa.

dir | %{$_.OwnerLoginName = "sa"; $_.Alter(); $_.Name;}

Now, there’s not a lot of explanation here. That’s because I’ve got a vid that explains it all.
http://midnightdba.itbookworm.com/VidPages/PowershellChangingJobOwners/PowershellChangingJobOwners.aspx
Ok, that’s it for the first PS homework assignments. You’ll notice that I assumed you knew where to run these from. I can’t very well teach every single basic every single time. However, I tried to give you resources to find the answers.