8.9.14

I'm Speaking at SQL Saturday 300 - Kansas City and a Few Questions About SQL Saturdays!

This is just a short update. I will be speaking at SQL Saturday Kansas City! I really hope to see you all there. This will be my last SQL Saturday this year. If anyone has seen my presentation, how do you think I did?

I've wondered a few things about SQL Saturdays from other people... Please indulge me.

How often do you attend a SQL Saturday outside of your state?

How often do you attend a SQL Saturday outside of your state that you're not speaking at?

Do you see any benefit to having multiple SQL Saturdays in the same state?

Do you see any benefit to having multiple SQL Saturdays in the same location? Basis for the idea is a twice a year thing for smaller states.

Do you learn a lot when you go?

How often have you found a vendor with a product you've never known about?

I'm really curious what your answers might be. As a reward, I'll purchase a 30 day Plural Sight card and give it away October 15th to a random person who answers these questions for me. A show of good faith, here's my answers.

1) How often do you attend a SQL Saturday outside of your state?
4-6 times a year

2) How often do you attend a SQL Saturday outside of your state that you're not speaking at?
Once a year roughly. (I need to get better about that)

3) Do you see any benefit to having multiple SQL Saturdays in the same state?
In very large states, I can see a good reason. (California, Texas, ect)

4) Do you see any benefit to having multiple SQL Saturdays in the same location? Basis for the idea is a twice a year thing for smaller states.
Currently no. I don't think we could get vendor support for something like that without having a building gifted to us as well for the event.

5) Do you learn a lot when you go?
I learn a fair bit when I can attend the sessions. I make a lot of connections as well.

6) How often have you found a vendor with a product you've never known about?
Twice actually. We bought their product within 180 days of viewing it.

See, that wasn't so bad.^.^ 

21.7.14

Speaker Buddy System

SirSQL made a rather stirring post recently. He talked about a speaker buddy system to help out new speakers in our community that are trying to make the jump to a SQL Saturday or any larger event. I think it's a fantastic idea. He brought on a story of a girl named Anna who felt so put off by her experience that she'll never speak again. Anna was made up for his post to emphasize his point. I'm going to add a personal story to this as well.

I've been helping with SQL Saturdays in OKC, OK for going on 4 years. I decided to get into speaking this year. I haven't given a presentation or had any face time in front of a large group (3+ people) since high school.My first presentation was at our local user group. They gave great feedback and gave me some great pointers. My second one was in Tulsa's local user group. Great people, great presentation and useful feedback. 3 days after that, me and my wife drove down to Houston's SQL Saturday and I gave my first presentation at a SQL Saturday. 

I noticed a few things that I hadn't before. I've been to multiple SQL Saturdays in various states. I've helped presenters who struggled figuring out how that school or building sets up their projection system. I've seen speakers and people in general that were lost to where they're supposed to go. I've seen presenters rushing in the crowd with the rest of us trying to get to a room on time. The difference for them is that they still need to sit down, set up, check that the presentation view or demo comes over correctly and calm down for the presentation.

 When I sat behind the desk for my first SQL Saturday, I had to figure out what strange settings and controls their presentation setup had. I have no clue why it seems like every school and conference center insists on having a completely different presentation setup. I digress. I was a bit familiar with the drill. I got in the room, started setting up immediately, checked the screens and just waited. I was excited, nervous and a bit on edge to see how many people came in the room and what sort of questions I'd get.

I had 4 people show up. One was a couple who stared at their phones the entire time. My presentation went a bit faster than it should have since I had no audience to get some back and forth with. I was not prepared for a 4 person silent room. I had one question that was related to but off the main topic and in an area I'm not 100% familiar with. If that had been my first interaction with SQL Saturdays, it would have been my last. I had driven 9 hours down on my weekend to stand in front of 4 people to give a quick nervous presentation and not be able to answer the one question asked, even if it was off topic. 

I would have loved to have someone in there that's done this a few times. They might have been able to stir a few questions out and help me see how to spin up a crowd. They could have given me some advice on what I did wrong or right. If I hadn't worked with a dozen or so different projection setups in the past, they would have been extremely useful in helping me not stress over hooking up a screen.

Now, I know that's not how all SQL Saturdays are. Very few actually end up like that. I know my topic is a bit strange anyways. I've signed up to speak and 5 locations this year. Houston, Baton Rouge, OKC, Kansas City and St. Louis. I've been accepted to the first 4 and St. Louis hasn't closed yet. I plan on treating each and every one of these as my first event. I think a first time speaker buddy system is a fantastic idea. I hope it catches on quickly.

7.7.14

Collaborated Training: What Legacy Will You Leave?

My very first IT job was a bit daunting for me. I really wanted to do my best but I wasn't really sure what they did. One of our supervisors had built a large collection of documentation and a list of our most common issues. It helped so much. That doesn't work as well in the database world. I do document my code and am building documentation on our projects so that everyone has better information. How do I leave behind something that someone else could use beyond simple documentation?

SharePoint

Odd lead in I know. What I plan on doing is simple though. Every day I come across something I've not learned yet but may have some interest in. I plan on creating a folder with the main term listed with an X at the end. This will only signify that I have nothing in that folder yet. When I get time later, I'll go back to each X and see what strikes me as interesting that day.  

Expanding on this idea

At first I'm going to make it simple and keep it to my close friends. The idea would be that anyone can create the folder and put items in each one. Something you didn't even think about may appear suddenly full of information. Something like this could start small scale and end up being a strong repository for multiple people within a few user groups or even beyond. I know we have forums now... but they don't really isolate information for long term use short of searching.

Keeping it clean

I might build it out at the start... but honestly moving stuff around later shouldn't be an issue. At first it will be just a list of items. It'll slowly transform into groups of folders divided by subject. I'm thinking a master legend that's updated weekly/monthly may be good as well. I'm going to set a few requirements on people that upload information that I plan to follow as well. All documents will be labeled BriefInformation-Date-VersionNumber(if applicable). Information within the document should include proper citing. If someone is recording a complete work of someone else, they will need to identify where they got it, when it was obtained and a link back to that location. I should be able to get better ideas of what to write as I slowly build out my personal list of things I don't know.

Negatives

Yes, there is a down side of sorts. If you move on quickly against your will... you may lose control of this. That's where you need to maintain good backups or have an agreement that you can retrieve this before you leave. A worst case scenario is that they may assume they can continue on without you and replace you with a cheaper newer model. That's always the fear of making work look too easy. It's good to declare goals and show accomplishments. There are just simply things you cannot predict. 

Legacy

It's a strong word to use... but hear me out. It's easy to keep a copy of good works for personal use if you keep good backups. Now I'm not saying that you should keep proprietary information... that should have its own folder. Consider what you could gather with this. Most things we learn come from a necessity of some sort. Consider all the things you've learned over the last year or so. How helpful would that have been in one central location for you? Think of all the good an internal WIKI would do? 

I pose this to you. Create this for your own environment and keep a copy for you should you leave. Every new place you go set it up again and keep working on it. How far can this go in a month, year, or even a decade? I've forgotten well more than I can remember. Take all that information you hold and store it mentally offline. This could save you and your co-workers months of effort. Every place you leave will be able to spin the new guy up with ease.  

Final Product

I plan on having something soon to show off for you all. I want to show a working skeleton with instructions and suggestions. I plan on showing price options and alternates for those a bit more cash strapped. I want to have something up and working in the next month that my close friends can demo and work. If anyone has any suggestions, I'll gladly listen to them. I'd even like to eventually get a developer friend or two to work up an app for my phone to hit my site and let me pull up information or write down ideas on the go. If this already exists... throw me a line, I'd love to know.

What do you all think of this idea?

23.6.14

Plagiarism: A Community Destroyed

Personal Thoughts

Just to be clear, I don't expect everyone to have my same thoughts. I personally fully support SQLServerCentral and how the help a great community exist. They host tons of articles and moderate a forum well enough to build a rather strong following of people that are highly involved. 

Then we have these articles... Plagiarism Apology notes, Stealing whole books, and many other things... just search Plagiarism and see what all pops up. I try my best to note every author, provide extra links, and overall just take any efforts to give the proper author his due credit.

This is a short post. Just something that spurred from today's events.

16.6.14

Come see me in Baton Rouge Aug 2nd!

I've been accepted for SQL Saturday Baton Rouge!

Yes, I am a little excited. I've wanted to become a speaker for some time. I'm excited to be speaking at my second SQL Saturday. My first session was at SQL Saturday Houston. I'll be honest, I chose the less used topic of fill factor. I honestly didn't expect it to be a session picked very often. So far I'm 2/2 on submissions. I'll be speaking at the last spot of the day,enough about that for now.

There are many other great speakers coming to SQL Saturday Baton Rouge! It's really hard to beat a free event with tons of free training and contacts you can make going forward. I honestly thing SQL Saturdays are what will keep MS SQL Server ahead in the game. I've not seen any other DB Software offer so much training and support for free or have near as strong of a community. Honestly, if you know of one... I'd love to know about it. 

Free Training?!

I've talked about a few free training sources before. SQL Saturdays, User Groups, website links I personally like, ideas on what I did as a junior DBA, Lists of many other resources and I even post scripts I find useful. You might even be able to talk your boss into a free lunch while getting some free training! 

What I'd like to see is some of you post something you've learned. I don't mean just here, I mean anywhere. What have you been learning recently? What are some subjects you wish you knew more about? Is there something you've been working with that you didn't find much information about? These can all lead to good presentations! Have you thought about speaking at your User Group? They don't bite... It's a great place to get started. 

Thank You!

Honestly, thank you all. I've been writing since November. From the people who've posted on my site, the e-mails I've received, and just the number of people who've visited the site... Thank you. This was a big motivation to start trying to present and I'm even working on a second presentation now. I encourage anyone who wants to speak to do so. 

9.6.14

Documentation: What Can We Document?

          I think I'd like to do a small short series on documentation. I personally love documentation. I honestly think most of us like documentation, we just hate writing it. There are quite a few excuses I've heard against writing it.
  1. I don't have time to document as I code.
  2. Changes need to be in place now, I can't waste time documenting.
  3. This isn't my code.
  4. There's too much to document now.
  5. We plan on deprecating this system in the next year or two.
  6. Everyone's already familiar with this system.
It doesn't honestly take much time to add some documentation. We don't need everything to have the letterhead, pretty backgrounds, screenshots and instructions for every single line. Here are a few examples of fast commenting.

-- in T-SQL adding two dashes allows your comment to sit right next to or under your code. This is great for explaining a bit toggle or what options exist in a SP.

/*
In T-SQL, adding the /* at the start of a comment will comment anything out until you end with the */, no matter how many lines deep it is. Great for revisions, modification explanations, author credit, date created and many more uses.
*/

in SSIS you can right click the open area and insert an annotation. you can set signs such as, "Don't run this step without running the previous step." and many other variations. 

You can even create your documentation in Excel or Word for larger projects or even to document what tables and SP's a certain job needs. 

The one thing I'd want documented more than any of these other things would be changes. A change log for the network, Domain Controllers, Server, SQL instance, tables, stored procedures, just about anything. A simple file share with folders for each group would be useful for a change log. Share Point may be a better target if you have that in your environment. File names could be as simple as Servername.doc. 

/**************************************************************
Admin: John Doe
Change: Modified Max RAM to 177152.
Reason: Leaving 10% to the OS and other functions.
Date: 6/9/2014
**************************************************************/

/**************************************************************
Admin: Jane Doe
Change: Modified Remote Admin Connections to 1
Reason: Ability to remote the DAC when the server is having issues. Temporary change while troubleshooting Database issues related to Ticket #0003425552
Date: 4/21/2014
**************************************************************/

A year later we may see a setting, wonder why or who changed it and now we have a searchable document to answer these questions.


3.6.14

Busy Busy Weeks, Finally Some Good News! SQL 2008 / 2008 R2 Will Get Their Final Service Pack!

We will actually be getting our final Service Pack! 

The information can be found here. You may still want extended support if you just simply cannot upgrade... but at least you're going out with one final update. This is a copy and paste from the link listed above, "We are planning to ship one last Service Pack for both SQL Server 2008 and SQL Server 2008 R2. Because of the maturity of SQL Server 2008 and 2008 R2, these Service Pack(s) will be an exception in terms of timing and will ship after mainstream support of these releases ends on July 8th 2014."

This is awesome! Of course, but why is this awesome!?

Obviously we will love having one final Service Pack, that much is given. What I personally thing is so fantastic about this is that the community requested that they give us one more Service Pack. Glen Berry requested one through connect and got almost 900 votes here. Christoph Muthmann put in the same request specific to SQL Server 2008 R2 here. Brent Ozar even did a post asking just the simple question, "Did Microsoft stop releasing SQL Server Service Packs?"

I guess it means we won, but what did we win?

Ok, yes it's more about them just finishing off their products life cycle with one final update to close the door with a happier feeling all around... but it's more than that. Here's where I'll step off the ledge and just give an opinion. It's felt like Microsoft hasn't been listening to anyone who actually buys their product... Look at the numbers of people that just wanted a start menu option that has hurt windows 8 so bad. Yes, I know it's there now... that's not the point. Well, I guess it is the point? If they're finally listening to us, now is the perfect time to start asking for things back like the MCM, some sort of higher level training or cert, or just various requests that would help out the community as a whole.

A bit far fetched, I know...

That doesn't mean we shouldn't try. It appears someone is listening now. Let's take advantage while the ears are open. The one thing I'd like to see is either the return of the MCM or at least open up the MCM testing and labs so we can at least see these famed tests. (I hope this link works... it was giving me fits today)

20.5.14

Tulsa User Group

I love user groups.

I have a special place in my heart for people who go out and give their time and knowledge away for free. I've written before about user groups. I used to think a good year of training involved a pretty specific list.


1) 12 local user group meetings (monthly visit)
2) Local SQL Saturday
3) Try to visit a few SQL Saturdays outside of your state if you can. Houston, Dallas, Kansas, New Mexico all have SQL Saturdays and are close to here. (Oklahoma, ours is August 23rd, 2014!)
4) Keep up with free training.
5) Try to present something, even a lighting talk for 15 minutes just to get up and involved.

I think I need to expand that list now. Jeremy Marx did a fantastic presentation on BIML. So far it was the best one yet. Tulsa invited me up to speak not too long ago and I have to say that it was a rather awesome experience. The people were exceedingly friendly, the venue was rather nice and it was overall just a great event. 

I'd like to add to my list. I think it's worth it to make that 90 minute drive each way if the training is right. Those same presentations that you're going to a SQL Saturday for are given first in these user groups. We go to presentations that interest us or at least address a specific problem. You have the ability to ask the presenter specific questions without a very strict time limit and when you do get into a discussion after the event... you're not rushing to the next class. 

I'm not saying to go to every user group meeting in a 200 mile radius... but I am saying that it's worth it to check into the ones around you. Every now and then they'll have one of those presentations worth taking a half day from work to attend.

To the Tulsa group; thank you for inviting me to such a wonderful event. I had a lot of fun.

12.5.14

SQL Saturday Houston

I spoke for my first time at a SQL Saturday in Houston this past week. I had a lot of fun doing so! I got to meet other speakers starting out, see some of the old hats and overall just get to give something back to the community. 

A few thoughts on SQL Saturday Houston.

It was run rather well. I really liked the food layout and the rooms. It was a very nice facility and short of finding the building, the whole event was really well done. I did show up before they put out the "SQL Saturday this way" signs in all fairness. 

The rooms were nice and clean, the food was good, the facility was well managed and the coordinators were practically jogging it seemed. They did a great job.

More than a few presenters

There were 54 presentations, 9 tracks 6 deep. If you wanted any track, you may even have had 2-3 to choose from. I'd like to thank all of the presenters that mostly came out to do this for free on their own time to either get their name and brand out or just to give back to a community that gives so much. Even a vendor said they preferred SQL over Oracle just because of how open and helpful the community is. Bravo for that.

Overall it was a fantastic experience and I have to give it to the folks down in Houston. They did an amazing job. If you get time, swing by their local user group. http://houston.sqlpass.org/ If their user group is half as good as the SQL Saturday was, you'll surely be impressed. 

I also recently went up to Tulsa for their user group and That post is scheduled for next Monday.

To both Tulsa and Houston; Thanks for having me! It was a blast.

7.5.14

Searching SSIS Packages or searching XML In General

Today I'm posting a small script with a simple purpose. I wanted to search all of my SSIS packages in MSDB for a specific word. This does not work on encrypted packages. That being said, here's the script in question.

SELECT 
  Name
, CONVERT( xml, CONVERT(varbinary(max),PackageData) )   AS XMLSource
FROM msdb.dbo.sysssispackages
--where convert ( nvarchar(max), CONVERT( xml, CONVERT(varbinary(max),PackageData) ) ) = ''

I know it has a lot of converts, but it works. It takes about 19 seconds to run on my server if that helps. 

If there are better ways to do this, please add it in the comments. I'd love to get a better version for my archives. 

I know this is a short post. Prepping for my first SQL Saturday has taken a bit more time than I expected. Thanks for reading everyone!

28.4.14

Resume Generating Event (RGE)

No, I haven't lost my job. This is just one of those topics that's good to get out there. There are honestly few things that can cause you to question if you still have a job the next day, but those things still exist. There are ways to mitigate RGEs, but do you know what they are in your area?

Service Level Agreements (SLA)

Gathering information on exactly how important a database is required for a real disaster. Disaster recovery planning is reliant on knowing your SLAs. You need to know how long a database can be down, which one is the most important, at what point do you have to start calling 3rd party people (customers, vendors, app support), how much data can we afford to lose and at what point you should stop fixing the broken server and failover to other hardware. I know, I know... No one likes giving these answers. My experience normally goes like this.

Question: How long can the database be down?
Answers: It should never be down. As little as possible. Why? Are we having issues?

Question: Which one is most important?
Answers: They're all important. Can't you bring them all up at once?

Question: What point do I start calling 3rd party people?
Answers: What's wrong now? That's case by case, call me first.

Question: How much data can we afford to lose?
Answers: None. None. None. Why? What have you done? None. We should never lose data.

Question: What point should I just stand up a new server?
Answers: We don't have spare servers. Why would you need a new one?

What can I do now?

Well, we can take some preventive action. Some of this is harder than you'd expect without first knowing what your SLA's truly are. Here's a few things you can do anyways today to help until you get these answers.

Find where your backups are stored.
Make sure the backups are stored on a different physical medium than the databases.
Make sure you test your backups occasionally to see if they're even good.
Make sure you have all your drivers for anything that's not standard.
Keep a log of what all databases are on a particular server.
Keep a log of the average size (uncompressed) of your databases per server.
Keep a log of the standard settings you might use for that server. (Ram, drive structure, version number)
Update the phone logs or at least your personal contacts with everyone you need to call if a 2AM incident happens.

Is there some sort of form that can be used?

My next post will include a list of the questions I'd want answered for each database as well as a short list of questions I need to ask myself. Having a printed list for each database, or set of databases if they have the same requirement, can be a career saver.

I plan on making a form to make this a bit easier. I will at the very least create an Excel or Word list with examples. I think this is good to have from your highest Sr. DBA to your multi-hat Network Admin who's being forced to manage a rogue database. Having this signed off by your boss may make the difference of keeping your job during a major outage. A little CYA never hurt anyone.

14.4.14

5 Years Running! OKCSQL!

I want to congratulate OKCSQL on a 5 year run. 

I personally wasn't here for that whole duration, but we have leaders and members that have been. We’re still growing and have plenty of room for new members. We have an awesome group! We have some fantastic sponsors even. GDH has been a sponsor for our group the entire 5 years. This month we have additional sponsors as well, Principal TechnologiesRedgate and Dell.

Jen and Sean McCown are speaking in person for us!

They're even doing a special double header for us. Here is a quick copy and paste from the OKCSQL site:

"Sean McCown is our first in person speaker and The title for the talk is: DIY Performance Reporting, it will start at approximately 6:15 and will cover:
Stop relying on vendors to provide you with performance data. Between Windows and SQL Server you've already got all you need to collect and report on server performance. And it’s far more flexible than you’ll ever get from a vendor. This is often called a poor man’s method, but it’s so much more than that. I’m going to show you what your options are for collecting performance data for free and you’ll even walk away with a framework you can plug into your own environment and start using tomorrow with very little effort.

Jen McCown is our next in person speaker and The title for the talk is: How to Build a SQL Solution, it will start at approximately 7:15 and will cover:

In this session, you’ll learn about SQL Server stored procedures (SPs): what they are, when and why you’d use them, and how you’d go about developing a solution with one. We will address common SP myths and learn about planning for performance. Most of all, we’ll walk through examples to explore the process of solution building in SQL Server."

This is a completely free event. We have prizes, pizza, and great training. Everyone is invited. Please come visit us and get some free training. We meet every second Monday of the month. I hope to see you there!

7.4.14

I'm Speaking at SQL Saturday Houston, May 10th.

I will be speaking at my first SQL Saturday. I'll be in Houston May 10th. I'm rather surprised to be honest. No, I don't have that sinking feeling that my presentation isn't good enough or that I'll show up at the event in my underwear with everyone laughing... those fears will come the few nights before. Honestly it's just because this is the first SQL Saturday I've ever submitted too. I expected to see rejections the first few times.

I have submitted to 4 SQL Saturdays this year. I will be submitting to a 5th once they solidify the date. My hopes were to speak at one event at least. I'm rather ecstatic. I would be happy to present at each and every one of them. (I'm sure the shine will wear off in time) My presentation will be on Fill Factor: Performance or Nuisance? The premise behind this presentation is not a deep dive into how the internals work. It's more built around why the changes matter, how they impact the system and how it can or cannot help. 

I'd be happy to see you all out there! I'm hoping to get out and meet more people. So far my interactions with SQL Saturdays is making sure the rooms are ready, tables setup, cookies in the speakers room... that sort of thing. It'll be a little strange seeing it from the other side.

I've written before about the list if things I'm taking with me to make sure everything works. My list has both grown and shrunk. Below is the list of what I'm planning on taking. If you have any suggestions, I'd be more than happy to listen!

Presentation Items:
Laptop and power cord.
Spare laptop (eventually something more like the Surface Pro... but for now just a spare laptop)
DVI to VGA converter
USB to DVI/HDMI converter
USB backup of my presentations, databases and installs
External mouse/Presentation mouse (I still need to pick one up... and soon)
Wireless Hotspot (Just in case)
Print outs of the slides in case the projector has issues( Thanks Andy Yun!)

Travel Items:
Extra complete change of clothes including shoes. Changes of clothes = Days Traveling +1
Travel toothpaste/brush
Headphones
Power inverted (car)
Refrigerated cooler (car)
Travel pillow (plane)
Backpack
Bathing suit
Cash: Tolls + 1 Tank of gas

I think that about covers everything. I'm not entirely sure. Any other suggestions?

31.3.14

SQL 2014 Release Date, April 1st

I'm not a fan of anything releasing on April Fool’s Day, but that's not for me to decide. I personally cannot wait to upgrade to 2014.  I want to see the full power behind PCI-e based SSDs pushing extended ram. I want to have the security of having a server go down and it not take out both sides of the Availability Group. If you haven't heard, when one goes off, it shuts them both down. I want to rebuild my partitioned indexes online. I have lots of wants, but how long will I have to wait to install?

I'm torn here. An old theory was to wait for Service Pack 1 before installing anything by Microsoft. Did we replace Service Packs with new versions? How long will we have to wait for the next "version"?  I hope I'm wrong here. I do love new features. I love upgrades. I do not like upgrading every year. 

I have this feeling that we're about to see releases every other year at most. We may quite possibly see a release per year. I do not want to see this happen. larger agencies stay on older tech longer. I think that coupled with the increased prices for SQL Server with the core licensing may in fact push people off of the Microsoft stack. They may not lose enough to hurt them bad enough, but they will lose some. 

What other Database platforms would you all consider? I personally hope we get back to seeing service packs and keep this stack strong. We have too strong of a community to see it split. 


This is a quote from their SQL Saturday posting, "We are encouraging participation in our food drive to benefit the Westside Cares food pantry located at the facility where the SQL Saturday event will take place.  Toward the end of the event, we plan to give people additional chances to win SWAG based on the amount of items each person brings for donation to the food pantry."

I really hope we can we can get enough people to bring food to make a large difference. 

If you disagree, please let me know. I'd love to hear other opinions.

24.3.14

SQL Server Enterprise is Cheaper than Standard

Free Month of Plural Site

I'm interested in seeing your responses. Any relevant response will be accepted and a free month of Plural Site will be given away. You do not have to agree with me to be relevant. I want to hear what you think.

Now this may seem a bit far-fetched, hear me out. 

This is a bit long winded for me.

First we will address the minimum requirements. We are comparing core licensing only. You must purchase at least 4 cores. Enterprise Edition costs $6,874 per core. That's a starting price of $27,496. Standard Edition costs $1,793 per core. That's a starting price of $7,172.

Now I know what you're thinking, a $20,324 difference seems pretty open and shut against this. This is where I ask that you hear me out. It gets a bit tricky here. Let's start comparing the benefits of upgrading.

Limits Standard Enterprise
RAM
64 Unlimited
Indexing Offline Online
Table Compression No Yes
Fast Recovery No Yes
Table Partitioning No Yes
Resource Governor No Yes
I know there are a lot more difference relating to BI, AS, RS and many other aspects. Let's just get enough out there to prove the point. I just told you that a $20,000 dollar cost was a savings yes? How can we save $20,000 by spending it? 

How much do you make a year? 

How about your other DBA, or the JR you're about to hire? How many Developers do you have on staff? How many of them are over worked trying to keep your old Standard server running? Look at your database closely. Let's answer some questions; we'll address this question last.

Do you have the maximum ram that your server can support in it? 

That may be 192, 384, any other number. Unless your server is older, it should support more than the 64GB of RAM that Standard does. I know I know, Windows Server Standard only supports 32GB of RAM, but that changed in Server 2012. 2TB is the RAM limit now. I know SQL 2014 allows for 128GB of RAM standard, but that still means more room to grow.

Do you have processor cores just going idle most times? 

Just because you have 12 cores doesn't mean you need to license 12. You can set SQL to use the limit of what you license it to use. Only license what you need.

Do you have SLA's to meet that have been difficult due to maintenance windows? 

Online indexing allows you to rebuild tables just about any time. You do still get a minor lock at the start and end of an online operation, but that's far better than during the whole process.

Do you have issues with archiving those massive tables?
 
Is their performance falling behind? Partitioning can help you swap parts of the table in and out while being minimally intrusive. You can even address fragmentation per partition instead of hitting that 10 billion row table all at once. In 2014 you can even do that operation online now!

Do you have multiple databases on the same server fighting for resources?

Well now you can split them up logically instead of having that same conversation about splitting them up physically. 

Are you fighting for more space or even considering moving to an Enterprise SAN?

Page compression is a beautiful thing. It should pose no problems on archived tables. If CPU is not a bottle neck currently, you can expand the window of what you compress. Heavily used tables may not benefit as much... but here's where partitioning can work with this. Page Compression saves quite a bit of space. This may be just enough of a space saver to allow you to request those SSDs you've been wanting.

Let's add this all together.

Yes there is a cost up front, but now you no longer need to hire a 3rd full time DBA or Developer. If you get that system on SSD's since we're using page compression to keep our sizes small and partitioning to keep our archived data on slow disks, our response time is faster. We were previously running to the edge of our RAM at all times, now we have cached static data from a month ago. These disks are only being accessed to present changes. Our DBAs are getting more sleep now that their fragmentation jobs aren't blocking all night long. Our maintenance windows are getting shorter and our SLAs have more room to breathe. 

This view will not fit all organizations. As always, the phrase "It depends" will fit in this scenario as well. Think carefully about all these issues and the time you've burned fixing them. You could be working on that next project to make your company even more money.

I mentioned Developers in this post. I'll explain now why. With compression, partitioning, more caching, faster access to those tables... you can hide a lot of "quick" codding with that much faster power. I'm not saying that we should code poorly because we can. I'm saying that we can code how we need because we can. Once it's up and working, you can then go back and fine tune. 

Enterprise Edition has a higher cost. Asking for more SSD's and more RAM has an additional cost. Not having your talented knowledgeable DBAs and Developers quit due to long hours, continually fighting uphill battles and being denied tools or extra personnel will cost you a lot more in the end. Training your next DBA alone may make up this cost. Not losing your customers due to the inability to meet SLA requirements has a large cost monetarily and to your reputation. 

Let's make the world happier, one Database shop at a time.

17.3.14

My Favorite Free Scripts


I'll start off with Ola Hallengren's scripts. I'm not a fan of reinventing the wheel. If someone makes gold, I tend to want to use it. Ola's scripts are fantastic. They're even already updated for 2014! You do have to contact them for the CTP version of the scripts.

These scripts cover your backups, index maintenance, verifies the integrity of your databases and logs the results of all of this for you. You can set up one part or all. This covers all of your 101 DBA requirements to keep your servers running in a safe fashion.  They even give examples of what you might want to throw into the script! It doesn't get much easier than this.


Adam Machanic wrote a great script called "Who is Active". He even has a 30 part blog series on the ins and outs of this procedure found here. Just about anything you want to know can be found there. It's a fantastic script. I would familiarize yourself with it prior to relying on it for day to day operations. 


Kendra Little has a great video and some sample scripts to run to view what indexes your server is wanting. As she will stress, do not just put them all into your database. Too much of a good thing can be really really bad. This is where we look for all that beautiful low hanging fruit.


On the same site as the Missing Indexes video by Kendra Little, there is a great script called SP_BlitzIndex. This was written by the same group. It's the step beyond just looking for missing indexes. Take the time to look through this when you have some time. This is not a place I'd look with a fire to put out... At first anyways. Learn about it in detail before assuming too much and jumping into it.


SP_Blitz was written by the Brent Ozar group and it helps you identify many pain points really quickly and even includes a link to what those mean and suggestions on how to handle it. Honestly, how nice is it these exist for us?!

What Can We Do?

So here we have 5 fantastic scripts. What will these accomplish? 
1) We start off with Ola's script and get our backups and index fragmentation under control. 
2) We find out from our users if anything in particular is slow or below SLA requirements. 
3) We run the SP_Blitz and see what shows up as a major issue.
4) We get a quick break down of what the Missing Indexes are suggesting.
5) We put all this together in a solid actionable list. 
6) We present our findings and come up with a solution to work on.

Yes I use the word "We" a lot. I do this on purpose. Correcting this many problems isn't a one person operation. You want the other product heads involved. You need to know what this could break or even if there's a problem you're currently trying to fix. If they need a report to respond in under a minute, spending a week trying to get it from 45 seconds to 5 seconds isn't where the focus should be unless everything else is working perfectly. 

There Is More Out There!

These are just 5 of the many many scripts out there for free that have been provided just to make your job easier. I've said it before, I'll say it again... MS SQL Server has a fantastic community around it. Few come even close. These 5 scripts alone can help a DBA sustain a workshop with minimal effort. Use this not as a crutch, but as a starting point to make everything even better. 

If you all know any other great scripts that can be added to this collection, I would love to hear it!

3.3.14

Issues Installing SQL 2008 R2 on Server 2012 R2 Clustering

Oh so the nightmare begins. 

At the bottom I've included a set of links where I found my answers. What I'm doing here is just giving a comprised list of what I had to do to get it to work. I ran into a few issues almost immediately.

I got an error showing that a 2003 patch was not installed.
Windows Server 2003 FILESTREAM Hotfix Check      failed

This required a work around. You need to slipstream install SP1 or SP2. I did not write this step by step. I used what I found here written by Peter Saddow.

1. Copy your original SQL Server 2008 R2 source media to C:\SQLServer2008R2_SP1
2. Download the SQL Server 2008 R2 SP1 packages from here. You need to download all Service Pack 1 architecture packages:
•SQLServer2008R2SP1-KB2528583-IA64-ENU.exe
•SQLServer2008R2SP1-KB2528583-x64-ENU.exe
•SQLServer2008R2SP1-KB2528583-x86-ENU.exe
3. Extract each of the SQL Server 2008 SP1 packages to C:\SQLServer2008R2_SP1\SP as follows:
•SQLServer2008R2SP1-KB2528583-IA64-ENU.exe /x:C:\SQLServer2008R2_SP1\SP
•SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:C:\SQLServer2008R2_SP1\SP
•SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:C:\SQLServer2008R2_SP1\SP
Ensure you complete this step for all architectures to ensure the original media is updated correctly.
4. Copy Setup.exe from the SP extracted location to the original source media location. Here is the robocopy command:
•robocopy C:\SQLServer2008R2_SP1\SP C:\SQLServer2008R2_SP1 Setup.exe
6. Copy all files not the folders, except the Microsoft.SQL.Chainer.PackageData.dll, in C:\SQLServer2008R2_SP1\SP\ to C:\SQLServer2008R2_SP1\ to update the original files. Here is the robocopy command:
•robocopy C:\SQLServer2008R2_SP1\SP\x86 C:\SQLServer2008R2_SP1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll
•robocopy C:\SQLServer2008R2_SP1\SP\x64 C:\SQLServer2008R2_SP1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll
•robocopy C:\SQLServer2008R2_SP1\SP\ia64 C:\SQLServer2008R2_SP1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll
7. Determine if you have a DefaultSetup.INI at the following locations:
•C:\SQLServer2008R2_SP1\x86
•C:\SQLServer2008R2_SP1\x64
•C:\SQLServer2008R2_SP1\ia64
If you have a DefaultSetup.INI at the above locations, add the following lines to each DefaultSetup.INI:
PCUSOURCE=".\SP"

If you do NOT have a DefaultSetup.INI, create one with the following content:
;SQLSERVER2008 R2 Configuration File
[SQLSERVER2008]
PCUSOURCE=".\SP"
and copy to the following locations
•C:\SQLServer2008R2_SP1\x86
•C:\SQLServer2008R2_SP1\x64
•C:\SQLServer2008R2_SP1\ia64
This file will tell the setup program where to locate the SP source media that you previously extracted.
8. Run setup.exe as you normally would.

To add again, this post is just to give a good central location for how to get 2008 R2 installed on server 2012 R2. The original post was here.

There was a second error showing that the cluster service verification failed. You can fix this via the GUI, but it is much easier to open a Powershell window and just copy and paste, “add-windowsfeature RSAT-Clustering-AutomationServer” This was written by Emilson Barbosa Bispo on this page.

All in all it was a good learning experience. I know that it’s normally best to upgrade your OS when you upgrade SQL…. But those licenses are expensive. This goes double if it’s an enterprise license and you have a few servers that don’t need to be up to date just yet.

I really hope this is helpful for someone. I know finding these saved me a lot of time. What’s the worst thing you’ve had to work on? I’d love to hear other stories of interesting fixes.If you run into any other errors while doing this, let me know. I may have come across it and have an easy answer for you.



How to Slip Stream




25.2.14

Free Month To PluralSight!

Free Month of PluralSight!

Ok, personally this is pretty cool. www.SQLSkills.com is giving away a free month to pluralsight! Well, let me rephrase a bit. They're giving away a free month of PluralSight to people who run a bit of code on their SQL Server(2005 and up) for 24 hours. The code and full post is here. I understand that they're essentially paying us for a 24 hour snippet of data but look at all the rewards? We get 30 days of PluralSight and a free second opinion oh what those wait stats should mean to us. 

They have said that they only have a limited supply. I submitted my own results and I received the code within about 2 minutes of sending the E-Mail. I personally got a subscription to PluralSight during Black Friday where they had a rather large discount for a year. I have one single code that I do not need and I'd like to hear from someone learning that doesn't have a server they can use to take hold of this great offer SQLSkills is hosting. 

I'd like to donate my key to you.

I'll draw the name out of a hat before the end of the day. I'm looking for people who need a key and don't have a server to use. 

I think this is great and would like to commend all of our SQL Blog posters who give away training for free. They've gone a step further and given away access to training that has other peoples training as well for free. 


24.2.14

SQL Saturday OKC 309!

SQL Saturday OKC is coming up!

The event will be August 23rd, 2014!


Now is the time to get signed up. We already have three great precons ready.

BECOME AN ENTERPRISE DBA WITH SEAN AND JEN MCCOWN 

REAL WORLD SSIS: A SURVIVAL GUIDE WITH TIM MITCHELL

PRACTICAL SELF-SERVICE BI WITH POWERPIVOT FOR EXCEL WITH WILLIAM E. PEARSON III  

All three of these sessions are being done by well respected authors. These are all on an early bird special until July 15th. The normal price is 120, currently they're 99$. We will are excited to host this great event again. If you're not familiar with the SQL Saturday concept, SQLCenturion wrote a rather extensive blog post on his experiences running them here. I have my own take on them from an attendee perspective as well.

I will be submitting to OKC and I hope many other speakers do as well. Given the list we've had in the last few years, I think we'll have a fantastic event once again. I really hope to see you all there.

If you  plan on coming out and would like any local information such as where to find a good restaurant, where is the good theater, or where can I play some putt-putt, feel free to drop a line. I will be at the event and the after party for it. I'd love to meet the people willing to endure this blog. ^.^


While we're on the subject of free training, don't forget to talk your boss into a free lunch or to work on the other free training sites available to you. 

Never stop learning or growing. 

17.2.14

First Presentation Ever And My 50th Post

Last Monday was the first time I've ever presented anything. Honestly, it was the first time really speaking in public in front of a large group. I learned a few things then. This may seem obvious to anyone who's done this even  through high school... but I didn't really do that.

Brent Ozar has a good blog on how to start a blog right here. I decided after reading multiple blogs on SQLServerCentral and other sites that I wanted to start one. I started watching Sean and Jen McCown's Midnight web show, Friday at 11pm central, and going to multiple SQLSaturdays. I decided I wanted to present for the first time.

I did a presentation on fill factor. I chose this topic because I didn't see much around on it and I really wanted to explore what all it meant. I've come to understand a lot more while building the presentation. I'm going to share a few things that helped and didn't along the way.

I started off recording my presentation and giving it to just myself, then I made my poor wife listen to it a few times. It is surprisingly helpful to have someone with no knowledge on the subject sit there. They ask the questions you never expect. That added two slides a bit better explanation and a reminder that I repeat my self way too often. I noticed that I talk a little to fast and I do not transition well. I'm sure that will be easier when the nerves aren't acting up. I made an emergency run to best buy two days before the presentation because I didn't own a web cam so I made an emergency run to best buy. I plan on going back and recording my presentation a few more times until I become a lot more comfortable with it before I try to take this into a SQL Saturday style setting.

I brought along laser pointer presenter device. I was using SQLCenturions. It does the job rather well. The timer alone makes it worth it honestly. I could see that I was 20 minutes ahead of schedule. I also had to borrow my poor daughters laptop. I have a lovely alienware laptop and couldn't use it due to not owning a Display port to anything adapter. It has HDMI out and that doesn't register for the HDMI to VGA adapters. I have recently purchased a   converter. It honestly works rather well.

I found out 5 minutes before my presentation that I had planned it on a 4th gen i7 with 16 GB of ram and a 1TB SSD and was going to go live with a 2nd gen i3, 4 gigs of ram and windows 8 that I finished installing on the way to the event. I was not prepared near well enough. I am very happy I had a backup at all.

Now this all sounds bad and like a bad experience... but it was quite the opposite. I had a fantastic time. I received great feed back that I can actually work on and ideas on how to do so. I felt like I was finally giving back to a community that for so long I've used to train. I would do it again in a heart beat.


I'm way to the left out side of this picture... I'm fine with that. This is the Dell room that Dell has given us to use once a month for free. GDH brings us the pizza and all of these people came to listen to my first time presentation with snow in the forecast. I think it was amazing. First time presenting, on a subject that most don't really use on a daily basis, and we had a rather good turn out.

If anyone else in the OKC area want's to come to any of these meetings, they're free. Check us out at http://www.okcsql.org/.We would be excited to see you all there.

Below are the links to the three things that I referred to above. There may be cheaper and better versions of these... If you know of them, please let me know. I'm still building my travel bag so I can be better prepared. So far it includes:
1)  Logitech HD Pro Webcam C920
2) Logitech Professional Presenter
3) USB 3.0 to HDMI and DVI Dual Screen Adapter
4) A spare laptop cord
5) A tablet with internet access (through my phone works for now)
6) My presentation setup on Amazon's web service... Just in case
7) A spare mouse
and the more ovbious
8) My laptop
9) (days of travel + 10% rounded up) changes of clothes.
10) Spare shoes
11) Enough cash to use a taxi

This is all I have so far. I have been bitten by the presenting bug and hope to do this a lot more.


10.2.14

Do Not Set Maximum Ram To 0 Ever - Mistakes We Make

I'm going down memory lane here and remembering one of the first things I did as a DBA. I was told by one of our other DBA's that if you have 40GB of RAM for a database and it's not working very well, the fix is to modify the max RAM from 40,960 to 1,024 then back to 40,960 after it cleared everything out. I understand now that This isn't the best way to handle it. Queries may fail, everything that was cached will have to cache again, and a slew of other performance issues and possible failed reports.

Here comes the bad part. I had the bright idea at the time that if setting it to 1GB fixed the issue... why not set it to zero? Well SQL doesn't accept zero with any grace. It modifies it to 16 MB of ram. SQL just will not run with that much RAM. SSMS wouldn't load, the website went down, nothing was working. We had to stop all services and login through SQLCMD after starting SQL up in single user mode with the minimal configuration switch. 

Now we have downtime in the middle of production hours. I will say there are better ways to fix this, but the way we fixed it was restoring master from earlier that day that still had the correct setting. I wasn't really familiar with working in a DOS or PowerShell window with SQL at the time. This obviously caused down time and didn't help my reputation any. 

We All Make Mistakes

I now know what not to do. I know to verify things I do not completely understand and cannot logically pick apart. I know how to research more efficiently and I have a better Disaster Recovery plan. We all make mistakes, really we do. No one is perfect. The thing is though, what are we doing to get better? 

This is my request to all of you. Post a story about a mistake you've made and what you did to overcome it. Tell me what you've learned and how you plan on preventing it in the future. I think we could all simply learn from our mistakes or we can help others by letting them learn from ours.

I'm sure someone out there has a good story about deleting a table because they forgot the where clause and the transaction to wrap it in.

3.2.14

What Tables Are In My Filegroups

One issue I've found a bit troublesome is trying to find out what's in a specific Filegroup. Let's say you're trying to clear off a lun or just a drive and you see a file labeled, "iudexes7.ndf". Now unless you built this and have a steal trap for a memory... or just are fantastic at documentation... you probably have no clue what's in this file. If you target the offending database, you can either run the query below as is and gather this data, or add the where clause and target just that file.

Below is a good script for exploring and cleanup.

select sch.name AS SchemaName,tbl.name AS TableName,idx.name AS IndexName,ds.name AS Filegroup,
data_compression_desc,total_pages,total_pages*8/1024 AS SizeInMB,max_column_id_used,fill_factor
from sys.partitions p
inner join sys.allocation_units au  on au.container_id = p.hobt_id
inner join sys.filegroups fg  on fg.data_space_id = au.data_space_id
inner join sys.tables tbl on tbl.object_id = p.object_id
inner join sys.indexes idx on idx.object_id = p.object_id
inner join sys.schemas sch on sch.schema_id = tbl.schema_id
inner join sys.data_spaces ds on ds.data_space_id = au.data_space_id
and idx.index_id = p.index_id
--where ds.name = 'primary'
order by ds.name, idx.name

This is a good way to move files off a specific drive, clean up wasted space or even just help with some space issues related to a specific file. Happy hunting!

27.1.14

Forcing Results To Conform For Exports

This one is a bit off for me. I've had to make SSIS exports kick out in specific ways such as how a number is returned or how many spaces a column returned every time regardless of how long the actual return was. Here's a few things I've put in use that seemed to work out well in this situation. If you all have any others I don't have here that are more common or a better fit, I'm always interested adding something new to my list of tools.

Two of these were created by Igor Nikiforov. They are included at the bottom of this page. They need to be added before this script will work. If you take nothing else from this post, please visit Igor's page and look at a few of his User Defined Functions. These are very useful if you're background isn't strong into coding. 

The original query is a simple select from adventure works. 

select 
addressid, addressline1, addressline2, city, StateProvinceID, postalcode, modifieddate 
from AdventureWorks2012.Person.Address

These are a few of the conversions we used to get the outputs to fit as we needed to match an older method. 

select 
 dbo.padl(addressid,10,'0') as AddressID 
,left(addressline1 + space (40),40) as AddressLine1
,Case when addressline2 is null then '' else addressline2 end as AddressLine2 
,isnull(city,'No City Listed') as City
,dbo.padl(StateProvinceID,3,'0') as StateProvinceID 
,dbo.padr(convert(char(15),postalcode), 15, ' ') as ZipCode 
,convert(varchar,ModifiedDate,110) as Date 
,convert(varchar,ModifiedDate,108) as Time 
from AdventureWorks2012.Person.Address 
order by convert(varchar,ModifiedDate,112) desc, convert(varchar,ModifiedDate,108) desc



/****** Object:  UserDefinedFunction [dbo].[PADR]    Script Date: 01/26/2014 23:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PADL(), PADR(), PADC() User-Defined Functions
 -- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
 -- PADR similar to the Oracle function PL/SQL RPAD 
Create function [dbo].[PADR]  (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
     begin
       declare @length smallint, @lengthPadCharacter smallint
       select  @length  = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
       select  @lengthPadCharacter  = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

       if @length >= @nLen
          set  @cString = left(@cString, @nLen)
       else
          begin
             declare  @nRightLen smallint
             set @nRightLen  =  @nLen - @length -- Quantity of characters, added on the right
             set @cString =  @cString + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
 end

     return (@cString)
    end


/****** Object:  UserDefinedFunction [dbo].[PADL]    Script Date: 01/26/2014 23:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PADL(), PADR(), PADC() User-Defined Functions
 -- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
 -- PADL similar to the Oracle function PL/SQL  LPAD 
Create function [dbo].[PADL]  (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
      begin
        declare @length smallint, @lengthPadCharacter smallint
        select  @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
        select  @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

        if @length >= @nLen
           set  @cString = left(@cString, @nLen)
        else
  begin
              declare @nLeftLen smallint,  @nRightLen smallint
              set @nLeftLen = @nLen - @length  -- Quantity of characters, added at the left
              set @cString = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cString
           end

    return (@cString)
   end

22.1.14

PowerShell Remote Commands

This is something I've found useful here recently when I had a server acting up. It's a simple way to send commands to a remote server such as 'shutdown -r'

Enable-PSRemoting -Force

Enables to you actually remote through PowerShell.


Test-WSMan Server1

Just tests that you can reach the server through this method.


Invoke-Command -ComputerName Server1 -ScriptBlock { Get-ChildItem C:\ } -credential Domain\UsrID

If you're only sending a single command, you'd replace the Get-ChildItem C:\" with what you're wanting to run.


Enter-PSSession -ComputerName Server1 -Credential Domain\UsrID

This command allows me to interact on a long term method. Basically if I'm sending more than a single command, this would work better.


I'm sure there are other methods. I like short concise code. How do you all connect?