So much Internet…

SQL, SQL, SQL.

When it comes to the SQLs, I know only a fragment of what I could. To fill in those gaps, I read. Oh, do I read. I’ve only read a few SQL books but I have read hundreds of articles and blogs.

Most of the time it goes like this:

“Hmmmm, that looks like an interesting article. Let me give it a nice ‘ol read”

“Oh…what’s this link? To another blog? Sure! Why not?!”

time to reeeeaadddd

Then I must pull myself away and jump back into the databases.

I like blogs that stick to material I am looking for while also keeping my attention. At the same time, I try to avoid blogs that get too personal or complain too much. I keep my personal blog personal and my technical blog focused on the SQLs. I like to read blogs that try to do the same.

Here is my list of the Top 10 blogs I prefer (in no order):

  1. Grumpy Old DBA – the name says it all
  2. DBA Rant – good stories here
  3. SqlBalls
  4. SQL Rockstar
  5. Shaun J Stuart – my recent favorite. Not a ton of updates but great posts
  6. Pinal Dave – Used to be my favorite but there is such a thing as too much content – it’s hard to keep up
  7. Happy SysAdmin – Great for learning some Admin skillz
  8. Paul White – Wowzer. This guys knows a lot.
  9. sqlchicken
  10. SQL Princess – She loves some CTE’s
I like bloggers who I can relate to. Many times they have the same problems I have and/or present alternative solutions.

The last thing I would like to say to all these blogs is – thanks! You’ve helped me a lot.

Do you read blogs? If so, which ones?

 

Share

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

 

Share

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.

Share

Find…underlings…

Find…..underlings…..
searching……
underlings found……

I was recently given the task to create a stored procedure to find people who are under other people from our user’s database.

The requester wanted to put in the user’s ID and have it return everyone under them…and everyone under those people…and so on.

The table structure looked something like this:

<code>CREATE TABLE [dbo].[tblUser]( [UserID] [INT] NOT NULL, [EmployeeNum] [CHAR](7) NULL, [FirstName] [VARCHAR](20) NULL, [LastName] [VARCHAR](20) NULL, [StatusID] [INT] NULL, [ManagerEmployeeNum] [VARCHAR](20) NULL CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED ( [UserID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] </code>

To complete this task I created a temp table, some counters, and a little while loop.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Adam Mikolaj
-- Create date: 23 June 2011
-- Description:	SP to find what people are under what people (mangers)
-- =============================================
CREATE PROCEDURE uSP_FindUnderlings
@UserID INT
 
AS
BEGIN
SET NOCOUNT ON;
 
CREATE TABLE #peopleunder(
employeenum VARCHAR(20))
 
DECLARE
@employeeNum VARCHAR(20),
@countA INT = 1, @countB INT = 0
 
SET @employeeNum = (SELECT rtrim(EmployeeNum) FROM tblUser WHERE UID = @UserID)
 
INSERT INTO #peopleunder
SELECT @employeeNum
 
while @countA &lt;&gt; @countB
	BEGIN
		SET @countA = (SELECT COUNT(*) FROM #peopleunder)
 
                INSERT INTO #peopleunder
		SELECT rtrim(EmployeeNum) AS employeenum FROM tblUser WHERE StatusID = 1
AND (ManagerEmployeeNum IN (SELECT EmployeeNum FROM #peopleunder)
AND EmployeeNum NOT IN (SELECT EmployeeNum FROM #peopleunder)) 
 
		SET @countB = (SELECT COUNT(*) FROM #peopleunder)
 
	END
SELECT * FROM tblUser WHERE EmployeeNum IN (SELECT EmployeeNum FROM #peopleunder)
AND UID &lt;&gt; @Uid AND StatusID = 1
DROP TABLE #peopleunder
END
GO

 

The basic logic behind this is using the loop to continue to insert records into the temp table until it doesn’t find anymore records. I use the counters to calculate the pre-insert and post-insert. If they match, the loop is finished.

Just a simple little SP to start my morning.

Share

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.

Share

Don’t touch that Maintenance Plan Job

Good ol Maintenance plans. With 2005 and beyond we received the glorious SSIS backed Maintenance plans. I love SSIS so natually I love using these plans.

Last week I was working on setting up a new server. We had to make sure we had full, differential, and transactional log backups. No big deal. I just created one maintenance plan with 3 subplans.

I went in and created all the steps that were required. Backup DBs, clean up old ones, and send mails based on pass/failure.

The manager of this project then wanted all the backups copied over to a different machine. I didn’t need to do log shipping or mirroring or anything. The files just needed to be the same on both machines.

To do this I went into the SQL Agent and opened up the job for each of my steps. I created an extra powershell step that used robo copy to copy all of the backups after they were successfully created locally. Saved my jobs and everything was great.

yay jobs!

About half a day later, the operator I set up to receive the pass/fail emails didn’t like getting the transactional log backup emails every 30 minutes and wanted me to remove it from that subplan.

Meh – not a problemo. I went in and disabled that part of the maintenance plan. I saved my work and went on my merry way. A couple hours later, I checked to make sure everything was working correctly. I noticed that my robo copy job wasn’t copying all the new backups. What the hell….

I went back into my jobs and noticed all of my powershell jobs were gone. Poof!

After some testing I found out that when you save a maintenance plan it overwrites whatever was there before. Gotcha!

So TIP ‘o The Day: Do ALL your work for the maintenance plan INSIDE the maintenance plan.

My work around for this was to create another job just for the robo copy. I then added this job to each of the maintenance plans using the ‘Execute SQL Server Agent Job Task’.

Share

Can Scrum Work for the DBA?

Agile! Scrum! Development methodologies! Sprint!

If you have a manager who reads any web page about being a manager, then you have probably heard about Scrum. Scrum development is very attractive from a manager or stakeholder’s point of view. You get short bursts of development and can see results quickly.

With my experience, companies with no scrum experience who want to adapt this method search the web and find a scrum coach and then book 1-3 day sessions. Being in these sessions before, I know they are long and extremely boring.

Having said that though, I have seen scrum in action and think it works great for teams that do it right. If you have a dedicated team working on one project then scrum can do wonders for you. Even if you have a couple projects going it can work well with an experienced scrum master.

Being a DBA though, I find it hard to fit into scrum. I have been part of scrum teams developing some application but usually only brought on to write some SP’s, create a package, or another smaller task. I end up sitting in on 2-3 hour sprint planning meetings and end up with a couple hours worth of work.

The other problem with a DBA and scrum is that a good chunk of the work that I get isn’t planned a week (or 2) in advance.

“Database guy, I need access to server 2930dkdks. It’s holding up production”

“Are you the guy who gives me the reports? I need 10 reports by 1pm today!”

“Mind helping out with this query? I’m not sure why it’s slow, I only use UNION ALL 10 times”

“Mind checking out that server? It’s out of space”

I would love to tell all these people to wait till next sprint but then I would have a lot of angry co-workers complaining to my manager.

This is why I feel scrum might not work for DBAs. Sure, I could factor in 30-40% of my time into each of my sprints to take outside requests but what happens when I only get one user all sprint who needs help for 30 minutes? Or what happens when Joe the engineer deletes the wrong database on the wrong server in the middle of day and you have to spend the rest of the day fire fighting?

The other problem is that I’m not on a team of DBA’s. I do dev work, I do admin work, and I do BI work. Whatever is needed from my co-workers and my company.

I love the idea of Scrum and thinks it works well for developers but not so sure it works for DBAs.

I currently manage my tasks with a to-do list and a backlog. I revaluate the priorities of my to-do list daily and if I finish everything I reach into the backlog until another request comes in. If the backlog is a bit bare, there are always improvements to be made to current systems.

Are you a DBA that uses scrum?

If so, how do you make it work?

If not, how do you get around the manager who loves scrum and insists that you be a part of it?

Share

Recovery Model for Every Database

One thing that is enjoyable about my position is working with people who have no idea what SQL is besides “a difficult version of excel”. This gives me the chance to use what I have learned over the last couple of years and explain it the people who are using it in easy digestable….sandwiches, if you will.
One thing I see lot of is FULL recovery mode with no log backups. Sometimes these databases get tossed into prodution environments by third party tools that have full admin rights. Being I haven’t been here that long, I am still working on locking down all the production servers. So I needed a quick way to find which DBs were in SIMPLE and which DBs were in FULL.

So I ran into a very very cool function recently. DATABASEPROPERTYEX. If you ask this dude the status of a database, he’ll give it to you. Want to know the version? Yup, he can do that too. For the full list, check out – BOL.
I used this guy to tell me the recovery model of every DB on that server.

Check it out:

1
2
SELECT [name] AS dbname,Databasepropertyex([name], 'Recovery') AS recoverymodel
FROM MASTER.dbo.sysdatabases

Team that up with registered servers and you can find out the recovery model of every DB on all your servers!

Share

Where the hell is that column???

“Hey Database dude, I need you to update the PDADE33e433 Table with values for columns GrAnDaMa12333 and WTF32323 with values ‘ASdjendnd’ and ‘BarfFartwekljweljk3′. Cool? Thanks.’

I work with engineers who use databases. They design all the products that my company sells. They are really good at designing but…they use databases. For them, I am a necessary evil. We all get along very well but they would rather be designing products than deal with storing information. Did I mention it’s one of me to about 300 of them?

With those odds it’s very hard to keep track of what they are doing in their databases. With supporting over 100 databases (possibly more, only on month 3) I am still finding it a hard time to try to find out how they are building these databases (that’s a whole different blog entry).

What I do know is that I get requests to change data that they don’t have access to. When this happens I have to figure out where this data is. Most of the time they tell me a server and a table. I always say “Sure, no problem, just make sure you send me an email with the details”

This is where my journey begins. To save time I have built a stored proc that I have been installing in all the master DBs of all the instances. This SP checks all columns in all databases to see if one is LIKE the keyword that I pass in. Feel free to use it. NOTE: it also gives you view names.

SET ansi_nulls ON
GO
SET quoted_identifier ON
GO
/*
-- =============================================
-- Author:    Adam Mikolaj-- Create date: 19 May 2011
-- Description:  Use this SP to search for a column in every database on your server.
-- I created this because sometimes the I have no idea what database a column I need is in. 
-- This quickly searches all your DBs
-- Sample - exec uSP_WheresThatColumn 'Birthdate'
-- =============================================
*/
 
CREATE PROCEDURE Usp_wheresthatcolumn
 
@COLUMN VARCHAR(255)AS
BEGIN
SET nocount ON;
 
CREATE TABLE #whereisit(
database_name VARCHAR(255),
TABLE_NAME VARCHAR(255),
column_name VARCHAR(255))
 
DECLARE @SQL VARCHAR(MAX)
SET @SQL =
 'insert into #whereisit
 exec sp_MSforeachdb  ''
use [?] select ''''?'''' as DATABASE_NAME , TABLE_NAME, COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
 where COLUMN_NAME like ''''%'+@COLUMN+'%'''' and TABLE_NAME not like ''''#%''''
 ''
  '
 
EXEC (@SQL)
SELECT * FROM #whereisit
DROP TABLE #whereisit
END
GO

To call it just type in master..uSP_WheresThatColumn ‘column name’

Enjoy!

Share

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’

Share