This is the standard requirement that I get from a lot of clients. Sadly those three requests aren’t going to go well together. In IT (and really in anything) you have to pick two:

Done Properly

Done Fast

Done Cheap

If you want something done properly and done fast it isn’t going to be cheap. If you want it done properly and cheaply it won’t be done quickly. If you want it done fast and cheap then odds are it won’t be done properly.

Doing good work takes money to get the right resources to get the project done correctly. Getting the project done quickly requires that you get someone who has done this sort of thing before. When it comes to large complex systems or with cutting edge platforms your pool of potential people to work on the project gets very small very quickly. The smaller the pool of people who can work on something the more those people are going to charge to get the work done. The reason for this is simple, with skill and practice comes higher rates.

When working on projects that require that you bring in outside help from consultants to need to pick the two of those three things above that you want. If you’ve gone through this thought exercise and you still want all three you need to rethink the thought exercise. And to make the whole process worse you’ve only really got two of those items to choose between. Do you want the project done quickly, or do you want it done cheaply. The reason for this is that having the project done incorrectly isn’t an option.

When I say quickly I don’t mean actually quickly based on a calendar. I mean on what ever “short” time schedule the project manager has some up with. If the project takes 6 months and it needs to be done in exactly 6 months no matter what that means it’s getting done quickly. If however the project takes 6 months, but you don’t need it done for the next 18 months you can probably get people who are a little less expensive to work on it because there is extra time built in to the schedule so things can run a little slower.

When putting those projects together keep these things in mind as talking to your consultants so that you are prepared for the answers that they give you.

The digital magazine “eForensics Magazine” has put together their 1st issue talking specifically about SQL Server security. There are articles written by a bunch of SQL Server experts including the two articles which I have in the issue. There’s a free teaser of the issue which you can download as well (it’s down at the bottom of the page) before you buy.

Recently I was doing log shipping for a client in preparation to move their databases from one data center to another, when I was asked to change the drive that one of the target databases was being restored to.

No problem, I’ll just use ALTER DATABASE … MODIFY FILE (name=’xxx’, filename=’yyy’) to change the files, stop SQL, move the database files, and restart no problem.

Well apparently when doing this I managed to not reset the permissions correctly but only on the ndf file. Because SQL was able to access the MDF the database came up on NORECOVERY waiting for more logs to be restored. But when the next transaction log file was attempting to be restored, the error message that I got was not all that helpful to the problem at hand.

Msg 4319, Level 16, State 5, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘xxxxxxx’. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I tried restoring the older log backup and that didn’t help any. So I went and looked at the permissions again, and apparently SQL didn’t have write access to the NDF file so it wasn’t able to roll the logs forward. Thankfully I noticed the problem right away when I manually did the next log restore otherwise this could have been a nightmare to track down given that oh so helpful error message.

I’m pleased to say that I’ve been able to get a discount code setup for all my clients and blog post readers to get you $150 off the cost of entry to the SQL PASS BA Conference. To use the code simply register for a new registration and enter the discount code “BABN9H” in the discount code field. And that’s it. You save $150 off the registration fee for the PASS BA Conference.

If you don’t know what the BA Conference is, it is the premiere Business Analytics / Business Intelligence conference and unlike most conferences it is put on by data professionals not venders who have an agenda to push on you.

I ran into a problem at a client recently that I’m shocked that I haven’t run across before. I couldn’t figure out how to change my domain password when connected to their servers via remote desktop. You see the problem is that 99% of the time when I’m working with a client, either at their site or from my home I’m using my own machines to work, and simply connecting to their servers via remote desktop. This means that when I press control+alt+delete to get the change password option that option is for my local machine not the remote machine. As there’s no start menu on Windows 2012 the good old “Windows Security” option isn’t there when connected remotely. All the official help docs tell you to simply press control+atl+delete to get the change password option. Well that doesn’t work very well via RDP.

Thankfully you can simply press control+alt+end instead and that will bring up the same menu as control+alt+delete but on the remote machine so that you can actually change your password and stop using the crappy default password that the help desk setup for you.

So the other day I typo’ed something. Usually this isn’t that big a deal, but in this case when fixing tempdb for a customer I told SQL to put two logical files into the same physical file. I was able to get it to let me do this because I first added the files, then realized that I had put them on the wrong drive so I just changed them using an ALTER DATABASE tempdb MODIFY FILE command to move them to the correct drive. Then I had the client restart the SQL Server later when it wasn’t being used.

When SQL tried to come online however there were some lovely errors in the log and SQL wouldn’t come up.

2013-12-08 13:59:38.23 spid9s Error: 5161, Severity: 16, State: 1.
2013-12-08 13:59:38.23 spid9s An unexpected file id was encountered. File id 3 was expected but 9 was read from “E:\TempDBs\tempdev2.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
2013-12-08 13:59:38.24 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

This was a problem.

So I fired up the SQL Server using the “-c -f -m” parameters. This bring the SQL Server up as a command line application (-c), using a minimal config (-f) and in single user mode (-m). However when I tried to run the ALTER DATABASE tempdb MODIFY FILE commands all I was getting was an error about the tempdb not having the files that I was looking for. Apparently when you start SQL Server using a minimal config it only uses the first two tempdb database files, so I wasn’t able to make changes to the files.

Thankfully there’s another way to fix this sort of problem, but it isn’t for everyone. I had to go and edit the master.sys.master_files catalog view manually. Now thankfully it’s a lot easier then it seems, it’s just update statements. But you are using just T-SQL, probably in sqlcmd (at least that is what I was using) to make the changes. Now normally you can’t change catalog views manually using T-SQL. To make these changes you have to connect to the database engine using the DAC or the Dedicated Administrative Connection. Now if you are in a situation where you have to edit this stuff be VERY careful. If you run an update statement without a WHERE clause in here you’ll destroy every database on the system. But for those brave enough to try it, it’ll get the job done.

After running a bunch of update statements for files 3-9 on database_id 2 (which is the tempdb database on every SQL Server out there) a quick restart of the SQL Server service and the customer was back up and running again.