SQL – dealing with a WTF moment

No clue on this one.

An engineer asked me to move his two databases from a DEV environment to his production environment.

No big deal. Let me take a backup, move it over, restore it, bam….done. Ah, crapola, looks like he was developing on a 2008 R2 instance and production is 2008. Well that’s not good but it’s still do-able.

I figured I could script out the database (and data) using the ‘generate scripts’ option in SSMS. I would make sure to check the 2008 option.

script

I copy my script to the second server, open it up, change the location, run it and we are……NOT ok.

huh?

This looks ok to me. What the hell is the problem with this one insert statement?

I copied the one insert statement over to a new query window and then it shows up like this:

huh x 2

W….T….F…Only half of the insert statement copied over.

I end up commenting this one line out and trying to run the script; zero problems.

I then went back and copied the insert statement into 2 parts, the part ending in ‘999 and the second part starting with ‘, N’Rest… and I have no problems.

So to double the crazy, the second database had one record in the same table, in the same column (varchar(100)) that did the exact same thing.

I checked the collations and they were the same. What kind of character or break could be after that 999 that would break the query? Let alone my clipboard?

Server 1 was a SQL express 2008 R2 w/ advance services and the second was a SQL standard 2008, does that matter? Both 64 bit-ers.

I’m stumped. Any ideas from the SQL world?

UPDATE:

I found out what was breaking it. Bill Fellows ( blog | @billinkc ) had a good idea of checking it in hex-editor. Turns out there was an invisible CREATE DATABASE statement shoved in there. NO idea why though – http://twitpic.com/5t88c2

 

You want to start down the powershell road?

      POOOWWWWWEERRRRSHHHEEEELLLLL!!!!!

Yup, you’ve heard of it. You want to use it because everyone is talking about it. They say, “It’s soooooo awesoooommmmmeeee man, you gotta powershell it up!”

I also jumped on that ship and started into the powershell adventure.

Before we get into any powershell fun, we need to figure out where to go to use powershell.

Even before that, we need to figure out if we even HAVE powershell:

  • Go to Run and type in powershell, if you have it, a new powershell window will pop up.

If  you don’t have powershell, you should get powershell v2. GOTTA get that v2.

You have two options if you want to do some powershelling. You can use Windows Powershell or you can use Windows Powershell Integrated Scripting Environment (ISE).

I recommend using the ISE. It’s makes life 1000x easier.

If you want to run scripts from regular powershell, it’s not as easy as it seems.

Here’s how:

Step one:

  • Are you even allowed to run powershell? Probably not off the bat.

The FIRST thing you need to do is check if you can run scripts or not.
To do this type in Get-ExecutionPolicy

Now, if you haven’t run powershell before it’s probably set to restricted.

To change this you need to run this command:
Set-ExecutionPolicy (whatever option you choose)

What are your options?

  • Restricted – Can’t do jack. No scripts for you.
  • RemoteSigned – all downloaded scripts have to be signed but yours are fine
  • Allsigned – Only scripts that are signed and trusted can be run.
  • Unrestricted -FREEDOM! You can run any untrusted script.

Once you figure out which execution policy you want, you can run scripts.

Your other option here is go use the ISE. The ISE is very user friendly. You can type out your whole scrip, hit run, and bammajamma = scriptness!

“Do I even have the ISE?” Well, go to your windows search and type in ISE. If nothing shows up, you probably don’t have it installed.

It’s super easy to install. Just open up a new powershell window and type in: Import-Module ServerManager; Add-WindowsFeature PowerShell-ISE

You should get a nice pretty:

yay!

Now you are ready to go! So…..powershell away. More to come in my adventures in powershell.

Double Hop of DOOM

I recently had to battle through THE DOUBLE HOP OF DOOOOOMMM!! It was a very rewarding experience and I think every DBA should battle this beast at one time or another in their career.
So what is a double hop?
Let’s take a step back, what’s a single hop?

mmmmmm hops

A single hop is where you have your SQL server (let’s call it Server 1 for now) with a linked server (Server 2) set up. The hop is where you connect to your linked server. You “hop” from your local SQL instance to the linked server. Piece of cake.

Now the double hop is where you have a client connecting to Server 1 but wants to access the linked server (Server 2). This becomes a bit of a headache when you are using windows authorization. I was trying to access Server 2 from the client and kept getting the great “NT AUTHORITY\ANONYMOUS LOGON”. After lots and lots of reading I realized it was more of a Active Directory issue than a SQL issue.  Here is the best article I’ve found that explains how this all works.

So how after battling active directory for a couple of days, I finally broke through the wall.

Here is what you need to know:

  • Figure out what an SPN is and how to use SETSPN (the above article does a great job)
  • Add an SPN for both servers
  • Add an SPN for your service account – (this is the one that got me)
  • Enable Delegation for your servers in AD
  • Enable Delegation for your SQL SERVICE ACCOUNT
  • restart SQL services.

It was a bit of a battle but we were able to break the anonymous wall and access the legendary linked server. Best advice – keep reading, keep playing with the SPNs.

Adding domain users with TRY CATCH

I was recently given a task to give 20 people read permissions on a dev server. My organization uses AD groups to manage these on the production side of things but on the dev side, it’s a bit like the wild west.

I wasn’t sure if these users were already in the database or if they were new. I figured this was a good time to play around and create some sort of auto inserter for me. I wanted to be able to put the person’s user name in and wanted it to return if they were in the system or not. If they were, great, add them to this DB, if not, add them to the server and then DB.

I ran into the situation where there was a name on the list of people I had who’s username was wrong. Well…how can I check AD to make sure they were there without it blowing up? I couldn’t find a solution so I decided to use TRY…CATCH and grab the error.

Before anyone says anything about automatically entering users, this was just for my personal use and is not intended to be placed in any application.

Check it out:

USE MASTER
SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
-- =============================================
 
-- Author:    Adam Mikolaj-- Create date: 12 May 2011-- Description:  Little stored procedure for entering in users
 
-- =============================================
 
CREATE PROCEDURE uSP_AutIinserter
 
 @USER VARCHAR (100),
 
@dbname VARCHAR(100),
 
@DOMAIN VARCHAR(100)
 
AS
BEGIN
 
DECLARE @LiveUser BIT,@Sqls VARCHAR(500)
 
IF EXISTS (SELECT *FROM syslogins
 
WHERE name LIKE '%' + @USER + '%')
 
BEGIN
 
SET @LiveUser = 1
 
END
 
ELSE
 
SET @LiveUser = 0
 
IF @LiveUser = 0
 
BEGIN
 
SET @Sqls = 'CREATE LOGIN [' + @DOMAIN + '\' + @user +'] 
 
FROM WINDOWS WITH DEFAULT_DATABASE=' +@DBName+''
 
BEGIN TRY
 
EXEC (@Sqls)
 
END TRY
 
BEGIN CATCH
 
SELECT Error_message() AS errormessage;
 
END CATCH
PRINT 'USER IS now IN the system' END
ELSE
PRINT 'USER already IN the system'END
 
GO

To call this procedure just do this:

uSP_AutoInserter ‘Dude’, ‘DB’, ‘domain’

Book review – DBA Survivor: Become a Rock Star DBA

I recently read through Thomas LaRock’s book “DBA Survivor: Become a Rock Star DBA”. It was a nice read that walked the reader through the basics of being a DBA.

I have mixed opinions about this book though. Some of the first chapters are about starting a new DBA job and what you should be doing. This was rather convenient for me because at the time I was starting a new job. LaRock gives a list of things you should first do and check. This is great in theory but my first day I was rushed into a meeting and tossed onto a project. Before I could get to number one on his list, I was already knee deep in fixing jobs, answering SQL questions, and sitting in meetings.

The beginning of the book talks about getting a DBA job, who to talk to, what to read, etc. Lots of good advice for people starting out in the industry. It then moves on to what to do when you first get your first DBA job. Again, I think this is great in theory but I don’t know anyone who got their first DBA job through an interview. Everyone usually starts somewhere else – QA (my personal experience), developer, sysadmin, or something other related field. I did enjoy this part of the book though. It was able to fill in some holes for me.

The next part talks about office basics and what to do and not to do. If anyone has worked in an office, they’ve probably encountered most of what he is talking about here. Eat lunch with people, don’t talk about people, be nice, and most common sense stuff.

The next couple chapters is where it starts to get good. We’re talking about the raids, the HA’s, the SLA, and all the interesting stuff about database life. There are some great examples of what you should use and where. This is the perfect juicy stuff that would satisfy a newb but could also reteach an old timer something he/she hasn’t been exposed to in awhile. Chapter 6 is the best section by far. This is where the author gets into troubleshooting. I reread this section more than once.

The book then goes in and out of sounding like a support book for people having trouble in an office –  “Be nice”, “Work/Life Balance”, “Job Security”, and so on. I think these are important in any job but I didn’t particularly care for these sections. Especially the sections on watching what you eat. Not all DBAs are big fat dudes.

hmmmm clustered or non clustered indexes...

The last part of the book focuses on training. I thought this part was ok because I never really tried to persuade my boss to send me for training. I’m not against training but I probably wouldn’t spend a lot of time trying to get the money for it either (although I have to admit, PASS Summit is pretty cool).

Overall I thought this book was a good quick read and perfect for people new to the IT field and new to the database industry. If I was a professor in college, I would definitely recommend this book to new graduates or even people thinking about becoming a DBA. I give it 2.5 sandwiches out of 5.

 

Managing your SQL Server logs

“I am getting an error. Something’s broke. My application works fine. It’s the database.”

We’ve heard that guy more than once. So what do you do? I usually open up my box of SQLs and check out the logs.

This is usually a pretty simple process. Open the management folder, open the SQL Server Logs folder, double click your log. It’s a nice place to start.

Now what happens when you have 500,000 records in there? It takes FOREVER!

Luckily SQL 2011 (Denali) has a feature in there to stop the whole log from loading. If you aren’t on SQL 2011 (Denali) then you have to sit there and wait…and wait…and wait…

I recently found this beauty – EXEC MASTER.sys.Sp_cycle_errorlog

This boots your current log to second place and creates a new one. Now you have a nice clean fresh log to fill up.

So what happens to the logs? Well the SQL default is to keeps 6. Almost every time you open up that folder, you see Current + 6 old ones. After 6, they are recycled.

You are able to configure the amount of logs you keep too, between 6 and 99. Just right click on the ‘SQL Server Logs’ –> Configure.

SQL creates a new log every time the server is restarted. For most people, this is way too far apart. On my servers, I set up a weekly job that run this command. That way I know I have 1 week worth of data in each of my logs.