Tag Archives: SQL

Speaking at the Columbus SQL Server User Group

Posted on January 11, 2012 by | 1 comment

This week on Thursday, January 12th, 2011 a 6pm I will be speaking virtually at the Columbus, OH SQL Server User Group.  I will be giving an introduction to Policy Based Management.

If you are a chapter leader and would be interested in having me speak at your user group contact me.

Abstract

This talk will give you an introduction into monitoring SQL Server using Policy Based Management and Central Management System which is provided out of the box with SQL Server 2008. I will then show you how you can combine these features with the Enterprise Policy Management Framework on codeplex to provide monitoring for your whole SQL Server farm. This topic assumes you know nothing about PBM and CMS and will include lots of examples with only a few PowerPoint slides.

1 Comment

Posted in Events, SQLServerPedia Syndication

Tagged , , , , ,

Meme Monday: I Got 9 SQL Backup Problems but a Disk Ain’t One…

Posted on May 2, 2011 by | 2 comments

First of all I had no idea Hugo did a version of “99 Problems.” I may be biased because I have two turntables and a microphone (If you volunteer to help or speak at  SQLSaturday #80 I can give you a demo) but its true Jay-Z’s version is much better than Hugo’s version.

I got two turntables and a microphone

I got two turntables and a microphone

Now that we have who’s version of “99 Problems…” is better resolved lets dive into this months Meme Monday. If you are not familiar with Meme Monday check out Tom LaRock’s (blog | @SQLRockStar) blog post for more details.

Here are nine problems directly related to backups and monitoring backups that I have seen in my IT career that spans an ISV shop, consulting and the corporate world. I hope you don’t find these in your shop. Here they are in no particular order.

  1. No Automated process to backup your databases
  2. No retention policy or standards
  3. No Automated process to verify that backups succeeded
  4. Database Mail is not enabled (You cannot get any job failure emails with out DB mail)
  5. Notification for backup jobs don’t include an operator to alert SQL team on failure
  6. DBA doesn’t practice recovering databases from backups
  7. No automated  jobs to do transactional log backups on databases in “Full”  or “Bulk-Logged” Recovery mode
  8. Backups do not exist outside the production server
  9. Backup drive ran out of space (Oh snap, I broke rule #1 I cannot talk about disks)

2 Comments

Posted in meme monday, SQL Server, SQLServerPedia Syndication

Tagged , ,

First SQLSaturday in Wheeling, West Virginia was a Success!

Posted on December 30, 2010 by | 2 comments

After SQL Saturday #36 on May 1st 2010 my wife and I headed to Spain.  Somehow this post got lost in the shuffle when I got back. I thought it is important to share my thoughts of the event so here it is.

The first SQLSaturday in West Virginia can be summed up in two sentences.  The SQL Server Community is bleeping awesome.  The Ohio Valley is bleeping awesome. I know unfortunately I will leave some people out but I would like to thank the following individuals for their advice and support Andy Warren, John Parkinson, Thomas LaRock, Jeremiah Peschka, Justin Siebert, Joelle Ennis, and The Greater Wheeling Chapter of AITP.


Its now a little past a month six months after the first SQLSaturday in West Virginia and I am glad to share my thoughts on the event.  First, I would like to say we held it down for the DBA’s in the small cities who have dreams of hosing a SQLSaturday.  Don’t quote me on this but I believe we are the only SQLSaturday host city with a population under 50,000.

When we first started this mission in November 2009 our goal was to have 50 to 100 people register.  We actually had registration at 100 and had 70 people attend.  We had three tracks that delivered 16 13 sessions presented by 11 9 speakers.

Things that worked

  • Using WVNCC as venue – The West Virginia Northern Community College campus in Downtown Wheeling allowed us to use their facility.  We had the ballroom which held 100 people, two class rooms held 50+ and two smaller class rooms that held 30 attendees. This was a great fit and we are very grateful that they hosted the meeting for us.
  • Volunteers – We had a super all-star team of volunteers on hand for the SQL Saturday. Did I mention that the volunteers were awesome? There were, and they all did a great job.
  • Selected Speakers – All of the speakers did a great job.  The evaluations showed that everyone appreciated the presentations. We need a better way to deliver results to speakers. We will work on that for next year.
  • Lunch – The box lunches from Panera Bread were a hit.  The food arrived as scheduled.  One of our volunteers was also arranged to have the Catholic Charities pick up the leftover food.  Once again, did I mention that our volunteers rock?

Things that need improved

  • Get more people involved – While we had plenty of support for the big day only two people did the majority of the pre-day work. Hopefully, we can get more people involved earlier next year.
  • Build Higher Goals –  Our venue can support more people. We tried our best to keep it simple because we didn’t know what we were doing as this was the first major information technology event in Wheeling, WV for years. Now we know what we are doing so we should try to go buck wild next year.
  • Evaluations – I will take the blame here and say that we failed.  We didn’t have a good system for generating feedback.
  • More Speakers – We cut it close with filling up our three tracks. Next year I we will need to be more active with pulling speakers.  If you are interested in speaking next year contact me and I will get you on our contact list.

Thanks to the speakers

We would like to give a shout out to all the speakers for making SQLSaturday in Wheeling happen.  You were all awesome.  Check out their blogs and tweets.

Thanks to the sponsors

Whats Next?

  • 2nd Annual SQL Saturday in West Virginia – This is correct, we are excited to give it another run and are targeting June 2011. We will shoot out an update to let everyone know when we have an official date.
  • Wheeling SQL Server User Group – Hey John there is no SQL Server User Group in West Virginia what are you smoking?  First, I don’t smoke and second there will be. Our first meeting will be on January 20th. Check out my blog during the first week of January for more information.

Related Posts:

2 Comments

Posted in Events, SQL Server, SQLServerPedia Syndication

Tagged , , ,

Policy-Based Management Myths Series Finale

Posted on December 20, 2010 by | Leave a comment

Welcome to the Policy-Based Management myths finale.  Do to some great questions at SQL Saturday # 61 in DC I am going to focus on some myths that come up with Policy-based Management.  Today is the final installment, you can catch the other myths below.

You cannot use Policy-Based Management with SQL Server 2008 R2 Express

Just like the two other myths the answer is true and false.  This one, really through me for a loop mainly because I don’t normally use SQL Express. With that said, if you want to store or use SSMS to create a policy while connected to SQL Express instance the answer is false.  If you want to evaluate an existing SQL Express instance the answer is true.

Why can’t I create policies or store them in SQL Express 2008 R2?

When I read the following article on SQL Server 2008 R2 Express site I would think that the flexible section make it seam like you could store and create policies with that edition of SQL Server.  Unfortunately, you will notice that Policy-Based Management is disabled. This isn’t too bad, lets just enable it.  Oh snap……….  You get the friendly error message that states, “Policy-Based Management cannot be enabled on this edition of SQL Server.”

image

How do I evaluate policies against SQL Express 2008 R2?

So, I cannot enable Policy-Based Management on SQL Server 2008 R2 Express edition can I still evaluate and apply changes to this instance of SQL Server? Yes, you can, once you have your policies built you can use Central Management Server to evaluate and apply changes.

Below is a screen shot of me evaluating the built-in “Last Successful Backup” policy against an instance of SQL Server 2008 R2 Express.  You will see that it fails because well I don’t use Express so I don’t have the databases backed up.

image

Note: There will be more to come in a future blog post on setting up Central management Server and using it to execute Policy-Based Management policies.

How can I build a policy to work against SQL Express 2008 R2?

Connect to another instance of SQL Server using, Standard,  Enterprise, or Developer to generate your policies via SSMS and store the policy on a central instance of SQL Server (not express) or on a file share.

Related Posts:

Leave a comment

Posted in SQL Server

Tagged , ,

Policy-Based Management Myths (2 of 3)

Posted on December 16, 2010 by | Leave a comment

A few weekends ago I gave an “Introduction to Policy-Based Management and Central Management System” at SQL Saturday 61 in DC. Do to some great questions I am going to focus on some myths that come up with Policy-based Management this week.  Check back through the week to catch all three myths.  Today we will cover the second myth.

By default a policy using the database facets will run against all databases

The myth is true and false.  Actually, using the default database target condition (every database) the myth is true. If you build your own condition for the target then the myth can be false.

Lets take another look at the “Auto Shrink Database” policy that comes out of the box with SQL Server 2008. When you evaluate the policy you will see that the system databases (msdb, master, model, tempdb) are not included.

image

My first thought is to checkout the target properties.  Maybe for some reason the system databases are exempt.  Looking at the target below you will see that every database is suppose to be included.  Obviously, this means all databases are included, right? No, sorry system databases are not included.

image

How do I evaluate policies against all databases?

Lets create a new condition that we will use as our target condition.  In a nutshell we need a condition that shows all databases when they are system objects or are not system objects.  If you select the database facet you will notice there is an IsSystemObject field.  This is our ticket getting all databases.

image

Lets now change the target condition to use the condition created above. We will no longer have every databases as our target condition and select our new System and User Databases target condition.

image

Now lets evaluate the policy again.  You will now see all user databases and system databases as I expected when you used the default target condition.

image

Come back on Monday to find our last installment of the Policy-Based Management myths.

Related Posts:

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , ,

Policy-Based Management Myths (1 of 3)

Posted on December 14, 2010 by | 1 comment

A few weekends ago I gave an “Introduction to Policy-Based Management and Central Management System” at SQL Saturday 61 in DC. Do to some great questions I am going to focus on some myths that come up with Policy-based Management this week.  Check back through the week to catch all three myths.  Today we will cover the first of the following three myths.

You cannot execute and apply policies against an instance of SQL Server 2000.

The answer is yes and no.  You can execute and apply policies against an instance of SQL Server 2000 if the policy executed runs against features supported in SQL Server 2000. This is easy to do if you have a central management server setup.  I will walk you through the steps below assuming a CMS exists.

1. Right click on the node in your Configuration Management System that hosts your instances of SQL Server 2000 and right click and select evaluate policies as shown below.

image2. Click on the ellipse next the source and select the policy files you would like to execute or select a sever where the policies reside.  In this example we will execute the Database Auto Shrink built-in best practice policy.  By default they are located at C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033\. Once you have loaded the Database Auto Shrink policy click execute and the policy will be evaluated against every server in your CMS group.

image3.   The following are the results of executing the “Database Auto Shrink” policy against a group of development SQL Server 2000 instances.  You will noticed that the policy failed as there are databases that have auto shrink equals true. We can click on the checkboxes and apply the policy to force the databases to be compliant with our policy.

image

4. Click yes on the next popup screen will apply the policy to modify all selected targets that do not comply.  Make sure you want to do this before you apply the policy.

image

5. After selecting apply you should see that you were able to apply the changes to make your SQL Server 2000 databases are compliant with the policy.

Can you show me an example of a policy that will not work on SQL Server 2000?

Database Mirroring is not supported in SQL Server 2000 as it was delivered in SQL Server 2005 SP1.  If you have a policy that checks to see if mirroring is enabled and you run it against a SQL Server 2000 instance the policy will fail.  In fact, you will receive an “Property ‘IsMirroringEnabled’ does not exist error.

image

Check back during the week to catch the next Policy-Based Management myth.

1 Comment

Posted in SQL Server

Tagged , ,

Speaking at PASS Member Summit 2010

Posted on November 9, 2010 by | Leave a comment

I am excited to share with everyone that I am speaking at the PASS Summit in Seattle on Wednesday this week.   The Summit is by far the biggest event for database administrators and anyone who likes to work with data. I would refer to it as the Super Bowl for Database Administrators.  I will be participating in both a chalk talk and lightning talk.

Chalk-Talk is a forty minute interactive talk in the vendor expo hall.  I will be presenting SQL Server 2008 for Developers with a twist.  I will provide a list of topics and the crowd (yes this is you everyone) can pick the topics we cover.  That’s right everyone, even though I think Table Value Parameters is the most under used feature for developers we can skip it if you are more interested in other topics.

Lightning-Talk is a quick five minute talk about anything the speaker wants to talk about.  There is a session each day dedicated to Lightning talks and they will include seven speakers.  I will give a talk over using Profiler to troubleshoot third party applications.  This tip was very important to me when I became a production DBA and I am sure it will help others.

Leave a comment

Posted in SQL Server, SQLServerPedia Syndication

Tagged , , , ,

I don’t get the Data and Log File Location Policy.

Posted on November 8, 2010 by | Leave a comment

Recently I have been spending some time to evaluate Policy Based Management.  Its a nice tool to monitor SQL Server and to prevent bad things from occurring.  At work we have a nice sized SQL Server farm so I am always interested in using tools that make my life easier.

First thing I noticed about PBM is that there are several policies out of the box that can be imported via SSMS.  The majority of the free policies are several best practices in the SQL Server community.  For example, its well known that you should keep data and transactional logs on separate spindles. Therefore, initially I was excited to see a policy for Data and Log File location that included a check condition for Data and Log Files on Separate Drives until I viewed the details of the check condition.

image

Looking at the image above you will see four conditions. The first condition is obvious and with an initial look I would just run with that condition.  The next three are OR conditions so if any of them are true then the policy succeeds for the database.  I fully understand why we look at the @DataAndLogFilesOnSeperateDrives and the @IsSystemObject is included.  Its valid to exclude system databases because its likely to have some system databases and transactional logs on the C: Drive.

There are two condition that are puzzling to me.  Are they puzzling to you? Why would there be a condition to exempt databases that are less than 5120 MB in size?  Why was the value 5120 selected? Is there some unknown reason why Microsoft didn’t select 5121? Seriously, does 5120 have value or am I missing something?  Why would we also exempt databases that have a status not equal to normal?

Because I am unsure  why @Status and @Size are included in the policy I decided to review the Database Performance  facet attributes just to verify that I understand them correct.  The attributes are shown below.  I still very curious towards why these attributes are included.  I am not saying they are good or bad because honestly I am not sure.

image

Ask the Experts

This week I am going to be attending the PASS Member Summit in Seattle.  There will be a section of the vendor expo for Ask the Experts and that is exactly what I intend to do.  Worst case it will be a good way to start a conversation. I will follow-up here with my findings.

Leave a comment

Posted in SQLServerPedia Syndication

Tagged , ,

SQL PASS Summit 2010 on a Budget

Posted on August 26, 2010 by | 3 comments

The following is some information I would like to share with the community about how I plan to travel to Seattle for the SQL Server Pass Summit.  Please take my information with a grain of salt because this is the first time I am attending.  Everything below comes from research and tweets. If you are a regular please leave comments so others can see your travel tips.

Summit 2010

PASS%20Summit%20Banner%20300x300
Can we fast forward to November?

The SQL PASS Summit 2010 is the best opportunity for SQL Server DBA’s to connect, share and learn. Your first obstacle towards getting into the Summit is to well pay for general admission to the summit. You basically have two options here, either you pay the general admission fee (this is what I am doing this year) or you can get someone to sponsor you. A great option here would be your employer. Your employer also isn’t your only option for sponsorship. Speaking of getting someone else to sponsor you MSSQLTIPS and Idera is currently looking to send someone to PASS.  Give it a shot it could be you!

If you are paying for yourself you will want to pay ASAP because PASS has a sliding scale.  Just like many other conferences there is a price before sessions are announced and different prices as you get closer to the event.  You want to avoid paying at the door because the price is usually a lot more.

The following are some links that will help you save some money on attending the 2010 Summit.

How do I get there?

How you get to the PASS Summit will depend on your location and its distance to Seattle.  I  live in Wheeling, WV which is an easy hour drive to Pittsburgh so I will be flying.  If you are also flying you might want to checkout bing.com and setup email alerts to track the change in flight prices.  At this time I see that there are round-trip flights under $300 from Pittsburgh.

In order to travel through the Seattle Metro area check out the public transportation system.  It looks like they have bus, and a monorail.  The Seattle Center Monorail can take you from the airport to downtown for $5.00 round-trip.

Where Should I stay?

The PASS website recommends the following two hotels.

Looking at bing.com and  traveladvisor.com I found a few hotels within a miles of the convention center under $100 per night.  If you are trying to stretch your money I would recommend checking them out.

Do you have any friends that are attending PASS? If so, you might want to recommend sharing a room. This is a great way for you to split the costs of a hotel room.

Where should I eat?

If you are attending the Summit there is good news. Breakfast and Lunch is included daily. This means you will only have to worry about dinner.   There will also be some evening events where you might be able to snag a bite to eat.  On Monday night you can attend the PASS Summit 2010 Welcome Reception.  On Tuesday night you can also attend the Exhibitor Reception.

Another thought towards saving some $$ on dinner is to talk it up with the vendors.  They are there to get to know you and see if their products can solve your problems.  If your team has a budget for SQL tools (I really hope your team does) I bet you could convince a vendor to take you out to dinner.  Even if you don’t have a budget for SQL Tools I bet you could convince some vendors to take you out to dinner.  Remember most people in sales try to build relationships before they sell you on a product or idea.

Can you buy me a drink?

I never really was a big fan of beer and alcohol until I started my career in Information Technology (we will leave the company name out of this story). There were several internal functions to attend for networking and they all had beer.  I quickly noticed that all the bigwigs always had a beer in their hands. Being fresh out of college I followed suite and soon fell in love with beer (a trip with the wife to tour Samuel Adams in Boston also helped).

Anyways back to saving money. If you like to grab a drink (I defiantly fall into this category) it looks like its cheaper to go away from the convention center.

According to @SQLDBA its cheaper to go to the Tap House across the street from the convention center. If you also like to sample local beer the Rock Bottom Brewery is another  recommended place within walking distance from downtown.

What are your travel plans?

PASS Summit vets what am I missing? How else can people save some $$ on their quest to their first Summit conference? Let us know we are all looking forward to your recommendations.

3 Comments

Posted in SQLServerPedia Syndication

Tagged , , , , , , , ,

Moving SharePoint to the data center

Posted on August 3, 2010 by | 2 comments

I cannot speak for the whole legal industry but where I work a lot of people love some SharePoint.  It’s like 50 Cent says, “We love SharePoint like a fat kid loves cake.”  And trust me we love some cake.  With this mad love of SharePoint comes great collaboration and with this great collaboration comes tons of binary files stored in a database.  What does this mean to the DBA? SharePoint now consists of VLDB’s. 

Hmm… How do we move the VLDB’s across the USA and keep them in sync?

With the built in features of Log Shipping, database Mirroring, Transactional Replication with SQL Server I knew it was possible to migrate the databases and keep them in-sync.   At the time I wasn’t exactly sure of the best way to do this so I used the bat phone.

While some people love SharePoint I love Twitter. Twitter allows me to communicate with several great DBA’s.  For example, I used #sqlhelp which is the equivalent of getting Batman on the bat phone.  This time it was Brent Ozar ( Twitter | Blog) who confirmed my gut feeling that Log Shipping was the way to go.

So…. How do you do it?

The complete process I used is documented at mssqltips #2073.  This tip walks you though the process of 22 steps to get the job done.  I hope this tip helps out other DBA’s that need to migrate VLDB’s from one location to another location without using third party tools.

If you have any comments or suggestions please forward them along.

2 Comments

Posted in SQL Server, SQLServerPedia Syndication

Tagged , , , ,