SQLLawman

My New Job

January 4th, 2012

Several months ago I accepted a promotion from my employer. This is my first foray into management. I have held onto my SQL responsibilities, but also took on the honor of supervising the Windows Server Team. This is a team of seven talented and capable individuals. One of the challenges to this position is that it came about due to a reorganization and two disparate teams have been brought together to be one server team. Three of the team members work with me at the data center, and four were dispersed across the other hospitals in my organization. Turning these hard working, talented individuals into a cohesive team is my initial goal. Doing this as we open our second data center is adding some to that challenge.
One of my first responsibilities was to hire the eighth member of my team who will at least for a while fill a unique position as a facility and data center based employee. The gentleman I chose brought a fantastic tool set to the team that I will be utilizing in the future. He is also taking his first steps to become a SQL DBA and free up more of my time to lead.
I wish I could say that I have all the tools and knowledge to make this a smooth transition for the team and myself, but instead I am learning how much I need to learn about personalities, and more importantly how to lead my team to fulfill my vision.

But that is all for now. More as I continue to figure out where I am going.

Posted in MSSQL, Work | 2 Comments »

Removing < CR >< LF > During a Query

March 22nd, 2011

As you may have noticed, or not noticed, I have not done anything blog-worthy in quite a while. Well, actually that is not true, I have done a few things in Windows PowerShell that I think are kinda cool but the blog post is still swimming around in my mind and I have not sat down at typed it out, yet. But anyway, I did something this morning that I bet will pop up again and I think I should put it somewhere what I can find it later. So you get to hear about it also. I was building a fairly simple SSIS package. Select data from a database and write it to a text file. They don’t get much easier in my experience. I was using < CR >< LF > as my new row indicator. That seemed to work and I was going on to my next project. The customer called me. It seems that embedded in two of the columns (a text field) the users could and did use < CR >< LF > to format the text in the field. Well that was really messing with my extract. Never fear, dear readers, there is a solution. My good friend Mr. Google (or maybe Mr. Bing) let me know that char(13) was a carriage return and char(10) was a line feed. Well that made it easy. I edited my select statement to look something like this:

SELECT dbo.table.column1
,dbo.table.column2
,replace(replace(dbo.table.column3,char(13),’’),char(10),’’) as ‘column3’
,replace(replace(dbo.table.column4,char(13),’’),char(10),’’) as ‘column4’
FROM dbo.table
ORDER BY dbo.table.column1

I no longer had < CR >< LF > inside the column impacting my extract. Everyone was happy.

Tags: Database, t-sql
Posted in Work | No Comments »

Thoughts of the 2010 PASS Summit

November 14th, 2010

I spent the November 8th through the 11th in Seattle at the PASS summit. It was an incredible time and a very well run event. I had some very particular goals for the conference. First, I wanted to learn more about powershell specifically how it can help the DBA. Second I wanted to talk to the SQL Cat team to get some guidance in a couple issues we are seeing. Next I wanted to catch up with some people I met two years ago at my first PASS conference. Finally, I had assumed that Microsoft will have a product announcement of the next version of SQL Server and I was hoping a CTP version would be in my grubby paw by the end of the week. It was a fantastic week.

I think the PASS did an excellent job putting on this conference. I especially liked how they focused on first time attendees. It really seemed like the theme of welcoming first timers and providing them with an wonderful experience permeated the entire event. That was very cool. I wish there would have been that focus two years ago at my first conference. One of the things that brought be back to the conference was the freshness of the sessions. Other conferences I have attended the “technical” session were simply infomercials for their products. Again, the PASS summit did not disappoint. All the sessions I attended were well done, well rehearsed, and full of great information. But no review would be complete without at a couple of suggestions. Here they are: in the long thin rooms (think 201 & 202) I would suggest putting a second screen and projector half way back in the room. Anyone sitting more than half way may have had a less than optimal learning experience. Second: Since twitter handles were requested on the registration form, why not put a yes/no check box on the form asking if you would like your handle placed on your conference badge. I think that would really help put faces with handles. Seriously, those are very minor suggestions to an altogether excellent event. I really can’t wait to see if I can get back there next year.

I have been hearing how powershell can make life easier for all system admins and DBAs, so I expected to see some sessions focused on that particular technology. I was not disappointed. Aaron Nelson (Twitter: Blog), Allen White (Blog) and Joe Web (Twitter:Blog) made awesome powershell presentations. Each one from a slightly different angle that really helped me start to wrap my head around the power of this scripting language.

I came to the PASS summit with a few little issues from the office that were annoying, but not show stoppers. So I took a little time to go meet the SQL CAT team (Twitter:Blog) to see if I could get these figured out. It only took them a few seconds to understand my question and give me the correct direction to find the solution to my problem. One of the issues involved two logins. One login was a SQL login and the other was an AD login. These two logins were given the same rights to a database, but one could access functions and one could not. I had taken the path of “If A can execute it, why can’t B?” They helped me understand that neither should be able to do it, so A must be picking up rights elsewhere. Within a minute of getting back on that system and ADUC, I was able to understand exactly what was happening. I asked a couple of *gasp* SQL 2000 hardware migration questions and they were gracious enough to reach way back in their minds and provide me with answers. That was a very useful 1/2 hour.

I was not disappointed with the product announcement during the Keynote talks. Microsoft did announce SQL Server ‘Denali’ and provided us with CTP versions of the code. I have installed it and started poking around, but really haven’t dug into it deep enough to start playing with the new features. But that will be part of of technologies I will be playing with during the cold winter evenings we are headed towards.

I was excellent to reconnect with friends I had made at previous SQL Server events, and make new friends, and to see some of the faces behind the tweets that I have been following. My one regret in this area is that I was unable to meet @buckwoody. I saw him from across the room a couple of times, but it never worked out actually talk to him or hear him present. Well, hopefully that opportunity will present itself in the coming years. Other memories of this summit I will hold onto was Andy Leonard doing a lightning talk, without seeing his slides that were created by Allen White. Then getting to talk to Andy the next morning while walking to breakfast. There was also the realization that Brent Ozar and I live four hours apart in the midwest and only see each other when we are on the west coast. There are so many moments like this that just beg to be experienced. If you get the chance to go the the PASS summit. It is well worth the investment.

Posted in Uncategorized | No Comments »

Restoring Last Full Backup

August 31st, 2010

I ran into a small problem where I needed to automate a restore of my last full backup to a different database. Since my full backup file names include a date/time stamp I needed to query msdb for the backup name. Since I figured it out, I thought I’d post it so I would know where of find it the next time I need it, and maybe help someone else out in the process.

declare @bksource varchar(100)
select @bksource=(
select a.physical_device_name
from [msdb].[dbo].[backupmediafamily] a join [msdb].[dbo].[backupset] b on b.media_set_id = a.media_set_id
where b.backup_start_date =
(
select max( backup_start_date ) from [msdb].[dbo].[backupset]
where database_name = b.database_name and type = ‘D’
)
and database_name = ‘<database_name>’
)

– print @bksource

restore database [<Database_Name2>] from disk = @bksource with replace, stats = 10,
MOVE ‘<Database_Name>’ TO ‘i:\mssql\data\test\<Database_Name2>.MDF’,
MOVE ‘<Database_Name>_log’ TO ‘j:\mssql\xlog\test\<Database_Name2>_log.LDF’
GO

Replace anything inside the <> with the proper names and set the correct drive letters.

Tags: SQL Server, t-sql
Posted in MSSQL | No Comments »

DBCC Checkdb

April 25th, 2010

Last weekend I attended SQL Saturday in Chicago.

How do I get all my SQL server healthy?

During one of the sessions there was a brief discussion about DBCC CHECKDB. The long and short of it: It is important, do them, it is not an option to skip this check. It is right up there with Index maintenance and updating statistics. But, no one ever said that you had to run them on your production database. I repeat, no one said you had to run them on your production database. Here’s a couple of thoughts. First when you do your weekly backup/restore test, notice I didn’t say ‘if’, run the DBCC CHECKDB against your restored copy. If you have inconsistencies in your production database, it will also exist in an exact copy of you database. But remember to fix it on your production database and not you copy that you will drop later. Another option is if you use backup/restore to create a report server. This is an excellent way to do the checks and verify your backups are good. We have a couple of systems that we do this every night when the SQL backup is complete, we restore it the report server. Proved the backups are good. I then look for a time that Crystal Info is not pounding the report server and run the dbcc checks against that database copy. I have it e-mail me if it finds any errors. Kills two birds with one stone. Now for the truth in my shop. With over 120 instances and over 1600 databases, a weekly backup test and DBCC CHECKDB is just not practical. So I use a hybrid approach. The servers that refresh a report server daily are easy perfect candidates, our larger (high priority) databases get a restore to a test server and DBCC CHECKDB there, and the other databases get an occasional backup/restore test and DBCC CHECKDB run against the production database once per week during slow times. So think outside the box. Find a way to verify your database’s consistancy.

Dep

Tags: Database, SQL Server
Posted in MSSQL | No Comments »

Blog Site Changes

March 19th, 2010

On a whim a few days ago, I looked at my feedburner statistics. I was shocked to learn that there are 20 people who have subscribed to my feed. So I thought I better spend some more time on my blog in order thank those people for their time and attention. So I am planning to have fewer of these useless posts and concentrate on more meaty posts that might actually help someone out.

Secondly, I decided that I wanted to change my twitter handle and I thought about it for quite a while and thought blending my last name with my job as a SQL Server DBA. So I morphed into SQLLawman. I’m following suit and changed my blog to follow suit.

Finally, I found a new theme for my blog and I really like the simple nature, so I think I’ll work from here to edit and add features as time goes by.

Tags: Twitter, Useless Information
Posted in Introduction | No Comments »

Who’s Winning the Olympics?

February 28th, 2010

I love the Olympics. I enjoy watching the competition, and occasionally I enjoy the behind the scenes, human interest story. But one thing that I struggle with is: Who is winning? Depending win where you look you can see who has the most medals, who has the most gold medals, etc. So I am going to throw my scoring into the mix. Yes, it is probably as convoluted as the scoring in figure skating. (what is the difference between at toe loop and a lutz anyway?) So here’s my scoring:

Every Gold is worth 3 points
Every Silver is worth 2 points
Every Bronze is worth 1 point

Yep, more points for the winners. But this is where my scoring becomes interesting. I divide the number of points by the number of athletes. I found the number of athletes from http://www.vancouver2010.com/. What I find cool about this is that figures out which country sent the best athletes. That’s my idea of who wins. Since this my blog I get to make my own rules. So without further ado, here’s the current top 10: Final Standings Updated 2/28/10 2:55 pm EST

Place	Country	Athletes	Gold	Silver	Bronze	Points	Points per Athlete
1	Republic of Korea	46	6	6	2	32	.696
2	Netherlands	34	4	1	3	17	.500
3	Norway	99	9	8	6	49	.459
4	Germany	153	10	13	7	63	.412
5	Austria	81	4	6	6	30	.370
6	United States of America	215	9	15	13	70	.326
7	Canada	205	14	7	5	61	.298
8	Croatia	18	0	2	1	5	.278
9	People’s Republic of China	90	5	2	4	23	.256
10	Poland	50	1	3	2	11	.220

Tags: Useless Information
Posted in Olympics | 3 Comments »

Free Bacon at the PASS Summit

September 25th, 2009

Yes I’m serious. If you are going to the PASS Summit in Seattle there is an opportunity to get FREE BACON. The SQL Virtualization Virtual PASS chapter is hosting a breakfast and game show on Tuesday November 3rd at 7:30 am and you are invited. Do you have questions about virtualizing SQL? Do you have answers about virtualizing SQL? Do you not care about virtualizing SQL and just want a good breakfast? Come join us. Experts from VMWare will be on hand to answer questions. There will be fun, prizes, and food. Now here is where it gets really cool. We are looking for contestants. If you are thinking about virtualization, actively engaged in virtualizations or already been there, done that, bought the T-shirt you may qualify to be in the game show. Very soon we will have a registration page that will give you the opportunity of a lifetime to play. So what kind of game will this be? Have you seen Wipe Out? A really fun game show about people embarrassing themselves on a muddy, messy, wet obstacle course with “Big Balls”?

Well, this has absolutely nothing to do with that, but it will be fun and energetic all the same. If you are going to the PASS Summit click here for more information and to register for the event. If you want to ensure there is enough bacon for everyone, please register before the event. Unfortunately you will not get to opportunity to meet me this year at the summit, but I’m really hoping for next year. So register, get some free stuff and bacon.

Tags: PASS
Posted in Virtualization Chapter | No Comments »

Find locked SQL accounts on SQL 2005

September 16th, 2009

We are in the process of upgrading a Microsoft product that just hasn’t gotten around to integrating with AD and uses SQL accounts. It also has a really bad habit of locking SQL accounts if your AD policy for failed login attempts is set low. So I got tired of searching through SSMS to unlock accounts, so I wrote a quick script to list the locked accounts and the command to unlock it. I didn’t go as far as to auto-unlock the locked accounts but that would be a simple edit. Here’s the script.

Declare @UserID varchar(25), @status nvarchar(5), @Command varchar(255), @command1 varchar(255)
Declare Account_cursor Cursor
for select name from sys.syslogins where name not like ‘<DOMAIN>\%’
open Account_cursor
FETCH NEXT FROM Account_cursor
INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN
select @status= convert(nvarchar,(loginproperty(@userID,N’IsLocked’)))
if @status = ’1′
BEGIN
print ‘– ‘ + @userID + ‘ is locked’
select @command = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL’
select @command1 = ‘ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL’
print @command
print @command1
– exec (@command)
– exec (@command1)
END
fetch next from Account_Cursor into @userID
END
Close Account_cursor
Deallocate Account_cursor

Thank you @unclebiguns for pointing me toward the function that makes this all possible.

I am guessing that this is not the easiest or most efficient way to do this, so if anyone would like to educate me on a better way, I am very open to learning your techniques.

Tags: Database, SQL Server, t-sql
Posted in MSSQL | No Comments »

Find all Triggers

August 18th, 2009

I was asked to find all tables in a database that had a DML trigger so a developer could ensure that all the old functionality was available in the new database. So once I built my query to give me the information, I thought I’d save it here for future use.

select a.name as ‘Trigger’, b.name as ‘Table’
from sysobjects a
join sysobjects b
on a.parent_obj=b.id
where a.xtype = ‘TR’

This gave me a nice little result set of each trigger and what table it was associated. Maybe I won’t have to re-invent the wheel the next time they ask.

Tags: Database, SQL Server, t-sql
Posted in MSSQL | 1 Comment »