T-SQL Tuesday #32

July 11^th

It was a sweltering hot day, the type of day that you could fry an egg on the sidewalk. I got into work a few minutes early. I had started this job only 3 weeks ago, so I still have to make an impression. The agenda was a light for the day. I have been working on a project that was shelved until the company hired more help.

That is where I come in; I was hired to shoot some pool. The IT department had been a bit behind the eight ball before they hired me. I have been cruising along getting the lay of the land, figuring out what systems connected to what, and in which manner.

Since I have started, I’ve been asking questions. Questions people don’t like me asking. I’ve been poking around, asking why they are doing X, why not Y instead. There’s a lot of “we’ve been told by the vendor”.
Seems like I was on the right track.

I obtained the latest sp_blitz from the Bent Ozar PLF (w|t) crew, ran it on of on the production instances.

I ran through the results with a co-worker. He didn’t seem surprised.
I had looked down a long, hard road for the SQL environment.
I copped out, I’m not proud, but hey I’m the new kid on the block, I can’t rock the boat that much right now.

The company had tabled a workstation deployment, that was supposed to be done in conjunction with the switch over to an Active Directory environment. The AD infrastructure has been setup, a few minor tweaks and it would be ready for users.
There had already been a few cutovers of the populace, I guess I was one of them. I had no choice, I was told that I would have nothing to do with Novell Netware, that was fine by me. I had my run-ins with Netware a few years back, I was fond of it then, I am not fond of it now.

I started to crank out the new workstations, nice machines, i7 procs, 8GB DDR3. I had asked questions about the usage of these machines, what type of workstations that they would be replacing. Old Dell something-or-others were to be fed to the scrap pile.

Well, it’s about closing time, they are shutting off the lights, it’s getting dark in here.

Outside, it’s starting to rain.

(The events of the day have been fictionalized, however everything stated did take place, just not in the noir style. This is in response to Erin Stellato's T-SQL Tuesday announcement)

The End of the Beginning

For those of you whom I have never met or just haven't told, I have started a new job. Today was my first day. I went from being a Senior Network Administrator, to *egads* a Network Administrator I!
This is a good change, a scary change, but a good one. Lots of unknowns: people, technologies, different ways of doing things, SQL databases.
Learning the different tech is the easy part, much harder is getting the lay of the land dealing with the different personalities.

Onward to the great unknown

Chicken or the egg?

We’ve started the move to Microsoft Dynamics CRM 2011. So begins the task of exportation of information and the resulting importation of said information. Sound easy enough, however both CRM 4.0 and 2011 don’t have the most…robust import/export feature set.

Off I start creating a report (remember, poor export tools) of all of our active accounts. Everything is going just fine, the export went well it was only a few accounts to test the process with, no big deal.

Start the import process in CRM2011, looks different, maybe this will be a better experience than the export. At step ‘n’ I map the fields in the export with the associated fields in CRM2011 that went nice and easy. So at this point, I am thinking that the import process for the contacts should go swimmingly as well.  I get to the “finish” screen for the importation. Click “run”. Navigate to the import status page, “failed”.

Damn

“Alright, must be a logic error that I missed”, I think to myself. I view the details of the failed import, yup, sure enough. The process bombed on the primary contact field. It is an option list, well seeing how there are no contacts in the CRM2011 yet, doesn’t really surprise me that it failed at import.

Damn

I go create the export report for the contacts. I start the import on the CRM2011 server. I pause.

Damn

I cannot import the contacts with the parent customer field mapped.

I suddenly had the urge for breakfast.

Went to the gang, told them that there was a hitch-in-the-giddy-up about whether to import accounts or contacts first. We decided that it was best to import accounts, not map primary contact. After they are imported go ahead and do the contacts. The downside is that the accounts won’t have a primary contact filled out. So we plan on filling the primary contacts in as we touch those accounts.

Has anyone found a way to programmatically import accounts with the associated primary contacts?

#mememonday

Thomas LaRock (w|t) actually came up with a #memeMonday writing assignment that I am able to participate in! His question seems innocuous enough, “if You today, had the chance to go back to You when you started your IT career, what advice would You (of today) tell You (of yesteryear)?”

First advice: Don’t worry about the time paradox that we would be creating, it’ll be fine.

Second: Listen to this, (point at my Boss) man he knows what he is talking about.

Third: Ask more questions, get more involved with projects, don’t just fill a role while at work, make one.

Fourth: Involve yourself more with the open source community, just because you aren't a programmer, doesn’t mean you can’t contribute to projects

Fifth: While you’re at it, learn a programming language it will help you in the long run when working with developers.

Sixth: Take this sports almanac, put it to good use.

So the major gist of our talk would be to learn more about the things that pique your interest, dive deeper in the subjects. Ask questions, learn from people who know more than you.

Already? Sheesh, that was quick.

Great, just as I was getting the hang of SQL 2008R2 (still no luck getting the hang of Thursday), 2012 has come out (with CU1 being released just a few weeks ago). I was one of the many whom waited with bated breath for the virtual launch event and watched in awe of the fail. I am excited for the new features, especially the Always On, Availability Groups. With 2012 database mirroring is going the way of the stage coach. Which puts a huge crimp in my HA/DR plans. Currently I am utilizing mirroring with TDE, which I have yet to find any documentation on how to use TDE with AG. Granted I looked days after release, so I am hoping that there is more out there on the intertubes now. I am also looking at the newest interface for “power users” for data modeling and the SharePoint/SSRS integration.

We upgraded to 2008R2 almost a year ago now, what I have to decide is if it is worth the upgrade to 2012 so soon. I will have to talk with our developer(s) and have them check out any of new developer tools or enhancements and see if they think it is worth it. I would like to stay fairly current with our database infrastructure. I have to worry about the changes to the licensing model.

I also have to make sure that our various internal programs and 3^rd party software will be compatible with the newest version. With the current shift that is going on in house, I have to at least make sure that MS Dynamics CRM 2011 works fully with SQL 2012. I am sure it will, but with Dynamics soon to be the center of business, I cannot afford to go with “I’m sure everything will work”.

On the shoulders of giants

In my previous post I had said that I was going to break out my maintenance plans and roll my own scripts.
That was a dumb idea.
Sure, writing something from scratch would've given me knowledge, yadda yadda yadda, and experience. But there is also the tested and true, "don't reinvent the wheel". While wanting to the the former, I subscribed to the latter. Off to The Google, I went. The first hit was Ola Hallengren's website.

When I was perusing the site, I kept thinking "why didn't I discover this sooner?"

After setting up the nuts and bolts of the maintenance solution on my local instance, off I went to check things out. The solution out of the box, sets up agent jobs without schedules. The various jobs are full backups of the system databases, user databases, diff and log backups of the user databases. There is also a job that cleans up the solution's own logging table. That table will get full quickly as it will log every index change, stat update, log/diff/full backup, every DBCC that is run etc... It will also create DBCC jobs for system and user databases. Penultimately it will create an agent job that will run to "optimize" indexes.

For the backups, we have a different LSA for each of our user databases, so I couldn't just use the same job for all of them. I just copy and pasted the contents of the job steps in each of the log, diff, and full jobs (that were created automatically) into new agent jobs that I had created. The best part is that you can specify which databases that you don't want to be backed up. 
 
The DBCC and Index Optimize stored procs take the same format in regards to which databases that you want to do the work on. For our environment I have added DBCC and Index Optimize as steps in each agent job along with the desired type of backup step.

I am glad that I have gotten away from maintenance plans. It feels good to try and swim in the deep end. I know I won't drown as the shoulders of giants aren't too far below me.

I used to like Maintenance Plans, but not any more

When I started to hold the mantle of DBA I got jazzed about maintenance plans. They were easy, they made sense, and it was graphical. Three things I look for in any administration task I do. So I am thinking "awesome, I've got everything set, I'm backing up, doing maintenance on indexes and stats alike".

Yeah, no one likes maintenance plans.

Brent Ozar (web | Twitter)  explains why actual DBAs don't like maintenance plans. Who knew? People who've been at this longer than I have. I have come across many an odd error due to a failed maintenance plan. The best one yet has been an error about invalid credentials when connecting to a file share where the backup is being written (which is another post to debate whether or not this is a valid/accepted thing to do). The way I used to fix it? Change from using the DNS(\\archive\SQL\backup\) name to using the IP (\\192.168.1.107\SQL\backup\) address. No, I'm not kidding. Brent (and probably others) is correct in saying that maintenance plans just do weird things.

I have decided that enough is enough. I have the self confidence now with my understanding of the way things work that I am going to take the plunge and convert my maintenance plans. I have broken down my maintenance plans into pieces. Instead of doing DBCC, rebuild/reorg indexes, update stats, and backing up all in one plan, this will be done all in one job. Each plan task will be broken out into t-sql scripts and made into steps into the main agent job.

In following postings, I will go step by step of the deconstruction of my maintenance plans.

SQL Family

Well I might as well throw my unknown hat into the ring. There was/is/has been a buzz around the Microsoft SQL Server community about the idea of "SQL Family". Well I am the first one to say that I am anything but active in the community, I have been known to call on family members for help. I am not even sure if I can say that I am a part of this family, part of the community, sure.

Like most lurkers I have the sqlhelp hash tag as a column in my TweetDeck. I try to offer advice where I am almost fairly certain I won't stick my foot in my mouth (which is few and far between the offering of help, not the sticking of feet in mouth)). This is somewhat a tragedy of the commons, as a few people are always there to offer help where need to the majority. I do feel a bit bad about abusing the help being offered.

I'm not even sure I belong, since being a DBA is only a portion of my job description. The main part consist of being a systems administrator. Do I still count for being a part of "the family"? I like being a sys admin, I like being a DBA I know at one point soon I'll have to choose between the two, as a person can only do one thing well, or multiple things in a mediocre manner.

I'd like to get more involved in the SQL community, however my wife and I just had our first son little under 4 months ago. Free time is something I only have at work...I kid, I am always busy at work. Time and monetary resources are a bit short at the moment. The most involvement that I can muster is to troll the dba.stackexchange board, and that's not even MSSQL centric. 


So for now, I'll sit on the side lines and every now and then add my two cents.