VMware Press is currently holding a sweepstakes for being a part of their Facebook community. Like their page and then be sure to sign up by June 30, 2012.

One (1) Grand Prize Winner will receive:
One (1) $100 gift card to www.amazon.com
Choice of print or eBook edition of three (3) VMware Press titles (Administering VMware Site Recovery Manager 5.0, Automating vSphere with VMware vCenter Orchestrator and Storage Implementation in VMware vSphere 5)

Nine (9) Second Prize Winners will each receive:
One (1) eBook of choice (Administering VMware Site Recovery Manager 5.0, Automating vSphere with VMware vCenter Orchestrator or Storage Implementation in VMware vSphere 5)

Enter here.

Full Disclosure: I have a couple of book contracts with VMware Press, though I am getting nothing directly for promoting their Sweepstakes.

If you are interested in how you can work smarter with VMware vSphere, Orchestrator is your answer. If you haven’t heard of Orchestrator you are not alone. Until recently it has been one of VMware’s best kept secrets.

Orchestrator is a workflow tool that comes out of the box with a library of predefined workflows. There are also a large number of 3d party plugins that allow it to coordinate processes across multiple platforms and allow it to integrate with your own applications.

One of the coolest things about Orchestrator is that it allows you to enforce your business logic and best practices around how you manage your vSphere infrastructure. Gone are the days of each admin doing things their own way and occasionally forgetting proper procedure.

The other great thing is that it is FREE. It comes with vCenter so there is no excuse not to use it… Unless you don’t know how to use it.

If you aren’t familiar with Orchestrator, information has been a little tough to come by. That has been changed now with the release of the new book, Automating vSphere: With VMware vCenter Orchestrator . The author, Cody Bunch (website | twitter), is a Private Cloud architect and vExpert. I just purchased my kindle edition and started reading it yesterday. I’m pretty excited to learn more about it and will post a review when I complete it.

*Disclaimer: I work with Cody and am his co-author on two upcoming books, Exam Prep for VCAP5 DCA and Exam Prep for VCAP5 DCD. However any opinion expressed here is my own, for better or worse.

Cody Bunch of professionalvmware.com and host of the Brown Bags, is holding a contest. With the help of a few sponsors he has put together a great VMware training package of books, videos, and gear. It’s not as cool as giving away bacon, but it’s the next best thing. Check out professionalvmware.com and let Cody know why you need this lab!

This year I had the privilege of work sending me to VMworld in Las Vegas. I have to say that it was quite the experience and much different than any other conference I have been to. There is just a tremendous community and the experience was great.

Lesson #1 Networking. The value of networking here is fantastic. It was great to meet and speak with some of the great bloggers. It was also a great oppurtunity to meet potential job candidates.

Lesson #2 Bring business cards. See #1 above. I learned this too late for this year and instead found myself tearing off pieces of paper. Not very fun or effecient.

Lesson #3 Hands on labs. These were a great oppurtuniy to play with some of the VMware and storage partners technology without having to build your own lab. My time these days is pretty limited so it was great to sit down for an hour at a time and try out some stuff that we might be interested in implementing.

Lesson #4 Speaker job titles. I found that the content of the sessions were directly correlated to what content was covered in the session. Marketing has its place and sometimes has some important info, however I thought the best sessions were from the architect level guys and those outside VMware. Those were the sessions where real world lessons were shared and discussed.

Lesson #5 Lack of sleep. Be prepared for a very whirlwind week and very little sleep. When there wasn’t something official going on there was probably some sort of community event happening. Each day it got tougher and tougher to get up in the morning. Trudge onward though and every minute is worth it.

Lesson #6 Pack light. I was way over packed. Next year I am going to pack much lighter. A duffle bag with a pair of pants, shorts, and a couple of t-shirts is plenty. You get a backpack with your conference pass so you can throw your stuff in it on the way home. Then use the duffle bag for all of the swag. There is lots of swag and your coworkers who did not get to go will appreciate you sharing.

So those are the things that immediately popped in mind as I set down to write this post. I have made a commitment to start blogging regularly again so I will expand on some of these later. If any of you have your own tips please share with everyone in the comments below.

Well some of you may have been wondering where I went. Most probably never noticed but I have been MIA for the last several months. A few months ago I took a new job within the same company. There were various reasons for doing so, but most of all it was a good fit and an exciting opportunity. So I am no longer a DBA. I am now a Systems Engineer for our private cloud team.

I have really enjoyed being a part of the SQL community and contributing my little part. While I won’t be blogging about SQL anymore, I do plan to start blogging about virtualization sometime soon.

See ya on the other side!
-Jeremy

The other day I was troubleshooting an issue with log shipping where the copy and restore jobs were failing. Here is an example of the error for the copy job, which was also similar to the restore job.

2010-06-29 09:41:52.80 *** Error: Could not retrieve copy settings for secondary ID '[removed]'.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.81 *** Error: The specified agent_id BECBBCC0-6867-4398-BD96-830D62D88558  or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

While it is not completely clear, this means that the copy job was not able to login to the instance and query the log shipping tables in MSDB. When I was first trying to gain access to the instance I had noticed that it was configured a bit strange. Basically they had a default instance and a named instance. The default instance was used as the secondary for log shipping while the named instance was being used for dev work. Well that part isn’t so strange… However they had an alias configured to redirect the server name to the named instance. So the log shipping jobs thought it was connecting to the default instance, it was actually connecting to the named instance. To work around this I just had to add the port number to the jobs as in the example below.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqllogship.exe" -Copy BECBBCC0-6867-4398-BD96-830D62D88558 -server SERVER01,1433

At work our first line of defense for our SQL Servers is our system admins and engineers. Overtime I have written a decent number of scripts to try and help out our operations people so that when an alert comes in they can diagnose issues quickly and accurately. The following script checks and reports on the configuration of the log shipping primary server. It also does a simple analysis showing the most immedaite issue.

--The Job Status shows if the backup job has failed in the last 2 hours.
select
ISNULL(db.name,'') as [Database Name],
CASE
	WHEN db.name IS NULL THEN 'DB is missing.'
	WHEN db.state_desc <> 'ONLINE' THEN 'DB is not online.'
	WHEN db.recovery_model_desc = 'SIMPLE' THEN 'DB is using simple recovery model.'
	WHEN pri.primary_database IS NULL THEN 'Missing log shipping primary config.'
	WHEN sec.secondary_database IS NULL THEN 'Missing log shipping primary secondary config.'
	WHEN job.name IS NULL THEN 'Backup job is missing.'
	WHEN job.enabled = 0 THEN 'Backup job is disabled.'
	WHEN schedule.name IS NULL THEN 'Job is missing a schedule.'
	WHEN schedule.enabled = 0 THEN 'Job schedule is disabled.'
	WHEN jobhist.run_status = 0 THEN 'Job has failed in the last 2 hours.'
	ELSE ''
END AS [Immediate Issue],
ISNULL(db.state_desc,'') as [Database State],
ISNULL(db.recovery_model_desc,'') as [Recovery Model],
ISNULL(pri.primary_database,'') as [Primary DB],
ISNULL(sec.secondary_database,'') as [Secondary DB],
ISNULL(sec.secondary_server,'') as [Secondary Server],
ISNULL(job.name,'') as [Backup Job Name],
CASE
	WHEN job.enabled = 0 THEN 'No'
	WHEN job.enabled = 1 THEN 'Yes'
	WHEN job.enabled IS NULL THEN ''
END as [Job Enabled],
CASE
	WHEN schedule.enabled = 0 THEN 'No'
	WHEN schedule.enabled = 1 THEN 'Yes'
	WHEN schedule.enabled IS NULL  THEN ''
	ELSE 'Unknown'
END AS [Schedule Enabled],
CASE
	WHEN jobhist.run_status = 0 THEN 'Failure'
	WHEN jobhist.run_status = 1 THEN 'Successful'
	WHEN schedule.enabled=0 OR job.enabled=0 THEN 'Disabled'
	WHEN jobhist.run_status IS NULL  THEN ''
	ELSE 'Unknown'
END AS [Job Status]
from sys.databases as db
full outer join msdb.dbo.log_shipping_primary_databases as pri
      on db.name=pri.primary_database
full outer join msdb.dbo.log_shipping_primary_secondaries as sec
      on db.name=sec.secondary_database
      OR pri.primary_id=sec.primary_id
left join msdb.dbo.sysjobs as job
      on pri.backup_job_id=job.job_id
left join msdb.dbo.sysjobschedules as jobschedule
      on job.job_id=jobschedule.job_id
left join msdb.dbo.sysschedules as schedule
      on jobschedule.schedule_id=schedule.schedule_id
left join (select job_id,
			min(run_status) AS [run_status] --0 is failed 1 is successful
			from msdb.dbo.sysjobhistory
			WHERE step_name='(Job outcome)'
			AND DATEDIFF(hh,convert(datetime,
            stuff(stuff(cast(run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
            stuff(stuff(substring(cast(1000000 + run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
            120),GETDATE())<=2 --looks at the job history for the last 2 hours
			GROUP BY job_id) AS jobhist
		ON job.job_id=jobhist.job_id
WHERE
	pri.primary_database is NOT NULL
	OR sec.secondary_database is NOT NULL
ORDER BY
	db.name ASC

I wrote this script quite awhile ago and it seems like I use it pretty much every day. I figured if I used it so much, then others would probably find a good use for it to. It parses the backup history tables in MSDB and returns the relevant information like how long the backup took, what was the size of the backup, what user took the backup, is it a copy only backup, etc.

As written below it returns information for full and differential backups taken in the last week for all databases.

SELECT bu.database_name as [DB Name],
name as [Backup Name],
CASE bu.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as [Backup Type],
CASE bu.is_copy_only
WHEN '1' THEN 'Copy'
ELSE ''
END as [Copy],
bf.physical_device_name as [Physcial Device Name],
bu.backup_start_date as [Start Date],
bu.backup_finish_date as [Finish Date],
CAST(DATEDIFF(s, bu.backup_start_date, bu.backup_finish_date)/3600 AS varchar) + ' hours, '
 + CAST((DATEDIFF(s, bu.backup_start_date, bu.backup_finish_date))/60%60 AS varchar)+ ' minutes, '
+ CAST((DATEDIFF(s, bu.backup_start_date, bu.backup_finish_date))%60 AS varchar)+ ' seconds'
 AS [Total Time],
CAST((bu.backup_size/1024/1024) AS int) AS [Size in MB],
bu.user_name as Username
FROM msdb.dbo.backupset as bu
INNER JOIN msdb.dbo.backupmediafamily as bf ON bu.media_set_id = bf.media_set_id
WHERE
type!='L'
--AND database_name = 'AdventureWorks'
AND DATEDIFF(d, backup_start_date, GETDATE()) <= 7
ORDER BY database_name ASC, backup_start_date DESC

SQL Server guru Paul Randal is hosting this month’s TSQL Tuesday and he has asked a great question that all good companies should be asking themselves. Why are DBA skills necessary?

I would answer that in today’s market being mediocre does not cut it. Companies need to stay ahead of the curve if they want to make it. They have a responsibility to themselves and to their customers. I think that it used to be a badge of honor when sites went down because of some new product or a great deal of attention was thrown their way. That is no longer the case. Web sites are expected to be up and running 24×7, no questions asked. And what is sitting behind most web sites? You can bet there is some sort of database server(s) setting behind it, feeding everything from content, to user preferences, to processing orders.

Yes it is true that you can install SQL Server, create a database and the thing will just run. The question though is how long and how well will it run? Is it all going to come tumbling down on your biggest sales day or during your latest promotion? In my experience if you do not have the dba skills and have been proactively taking care of your server, these are the very times you are going to experience failures.

So when should a dba start looking after the server? In my opinion there should be someone with some dba skills involved before SQL Server is even installed. Questions for server specs such as cpu, memory, disk, all need to be answered from the onset of the project. Now if you have a little 5 or 10GB db do you need to have a full time DBA? Most likely not, but in my experience it is usually somewhere around 10GB that you’ll start to see issues if no one is looking out for the server.

Someone with dba skills should be involved, not only insuring the hardware is up to snuff, but they should also be looking ahead to insure that best practices are being followed and all of the options are configured correctly. Basic things like recovery, max server memory, index maintenance plans, etc all need to be addressed before the lack of attention sneaks up and bites you.

The question of when do you need a full time dba is a bit tougher question to answer. In a nod to Paul, I have to say “It depends.” Most people would probably say that it depends on the size of the database; the larger the database, the more time needs to be spent on it. While this is generally true, it certainly is not a hard and fast rule. I have seen some customers with very small 10GB databases that seem to need constant tuning while others with multi-terabyte databases get by with little tweaking.

How can that be so you ask? The difference is in the forethought put into the server’s physical hardware, the database schema, and the queries being ran against it. If you have good hardware, a good schema, and well written queries you’ll be able to get more bang for your buck.

As an example the other day I had a client that was asking for help because their db server was just getting hammered. He already knew that corners had been cut in the past and the code was bad and that he needed more hardware to compensate. I was able to do some tuning to get them by but it would not be enough for the upcoming holiday season. Now because of negligence in the past, he was having to double the size of the hardware required. And not only was he going to be out money for the hardware but he still has to do something about the code or the site will just never scale for their planned future growth.

I can’t help but think that had a good dba been involved sooner that much of his pain could have been prevented and more money kept in their pocket. Poor planning costs money to redo code, purchase additional hardware, lost sales, and lost customer satisfaction. All too often people get caught up in what is good for the moment and forget to look ahead to the future.

A good dba with good skills can help anticipate and prevent those fires from ever starting, let alone growing out of control. A little TLC can go along way to ensure happy customers, coworkers, bosses, and spouses.

SQLServerPedia is holding an awards vote now through November 8th. Winners will be announced on the 9th.

While I have been honored to be nominated in the New Blogger category, make sure you check out the other categories as well. There are some really great bloggers and articles that have been nominated. It is a great group of people and I am humbled to be amongst them. These are some of the hardest working individuals in the SQL Server community. They deserve a few minutes of your time and a vote if you like what you see.

You can vote at http://pulse.sqlserverpedia.com/awards/.