Category: Tips

Categories
Syndication Tips Uncategorized

Technical Presentations: Give it a Shot!

If you have never done a technical presentation I HIGHLY recommend you do try it at least once. Volunteer to speak at your local user group or SQL event. Andy Warren (Blog | LinkedIn) recently added the idea of mini-presentations to both his user group meetings as well as the upcoming SQL Saturday #21 in Orlando. The mini-presentations are 15-minute time slots to give first-time presenters the opportunity to practice technical presentation and public speaking skills. If a user group or event is a bit too much for your first time do what I did: user your co-workers! The first time I was going to present my PBM talk I didn’t want to go to my user group without practicing first. So ask your co-workers if they wouldn’t mind letting you practice on them during lunch hour. It makes it much easier doing the presentation to a group of folks you already know and are comfortable with so its a good way to ease into the public presentations. Heck, if you’re feeling really saucy you can start a series of lunch-n-learns within your department and provide free training for everyone: Win-win y’all!

Still don’t have the warm and fuzzies? Well the fine folks at PASS have compiled a great list of resources for learning how to present at events. Check that page and there are TONS of great advice from absolutely awesome speakers like Steve Jones, Kimberly Tripp and Paul Randal.

The last few months I have been travelling around presenting on Policy Based Management at various groups and events. At each event I have learned something new either from conversations with the audience or things simply not going right which cause me to work on those things the next time. But the one thing I haven’t really gotten much (other than the occasional side conversation with a few folks) is presentation feedback. I’ve signed up over at SpeakerRate.com and have put up the different events I’ve spoken at so far. So if you’ve had the pleasure/displeasure of attending one of my sessions I would really love some feedback. Thanks in advance!

Categories
PASS Syndication Tips

PASS Summit Hotel/Travel Forum

Are you going to the SQL PASS Summit? Would you like to save some money on hotel by finding someone to split a room with? Want to share insider information on local spots you can dine at on the cheap? Well I hinted on Twitter recently that I was working on something for PASS with Jeremiah Peschka (Blog | Twitter) and thankfully we finally have it done: PASS Summit Forum

This forum has been setup so attendees can share information on lodging arrangements using the official hotels (and room blocks) for PASS Summit. Of note is that I said the official room blocks and hotels because a lot of people have done a lot of hard work to get the best rates possible for room blocks. We are looking to fill these blocks as much as possible so that in the future these hotels not only welcome us back with open arms but we can negotiate a better rate in the future (win-win y’all!).

In addition to having a single place to coordinate lodging you can also coordinate things like rides to/from the airport. You might be able to save some nice change by getting a limo van to pick up a group of folks at a hotel rather than everyone scurrying one by one. Also you can coordinate things like photowalk tours and much more! So make sure you go check it out!

Categories
Policy Based Management Syndication Tips

Policy Based Management: Against All Databases Condition

Recently after one of my presentations a Twitter-buddy of mine, Argenis Fernandez ( Blog | Twitter ), showed me something interesting. By default when you create a new policy the against targets default says ‘Every Database’ (see screenshot below).

all-databases1

Well this is a tad misleading as it apparently means “every” in the sense that it will apply this policy against every USER database. This means if you want to ensure autoshrink is not enabled on your system database (master, msdb, model, tempdb), for example, applying this policy with this default target setting won’t work. So how do we create a new condition/target that allows us to run a policy against both user AND system databases? We’re going to create a new custom condition that allows us to look at both types of targets.

First click on the down arrow next to Every and go down to the bottom of the list and select ‘New condition…’

new_condition1

First you want to give this condition a new name. For this demo we’ll call it ‘Every database – User and System’. The next step is choosing the Facet as well as the property we want to look at. For this particular condition we want to use the ‘Database’ facet (which is the default facet selected). Next click on the box under Field and click the down arrow to view all of the properties available under that facet.

new_condition3

The field we want to select for this is the @IsSystemObject property. Once you select it, make sure your Operator value is set to ‘=’, then click on the field under Value. Once again, when you click on the field and click on the arrow you are given pre-set options. For this value select ‘True’.

new_condition4

Now that you’ve selected your first clause that states to apply to system object, we will create a second clause that applies to user objects as well. To start click on the next line where it says ‘Click here to add a clause’. When you click on it you’ll notice you get a new row to be able to create a new expression. For the first column (AndOr) we want to select ‘OR’. This is important because if you select AND in this instance, you’ll never return a valid entry since an object is either a system object OR a user object, never both. Next select the @IsSystemObject again from Field value and finally set the value to FALSE. Your Expression should now look like this:

new_condition5

Click OK and you should now see your new ‘Every database – User and System’ in the Against Targets box. Click OK to close your policy. To test it apply your policy against your database via your Central Management Server (CMS). To do that drill down to your server, right-click the database click ‘Evaluate Policies…’

new_condition6

From the Evaluate Policies box click on the ellipses box to select your Source. Your source is where your policies are located. With PBM you can either select individual policies (XML file) or you can select a SQL 2008 Server that has the policy you want. In my case I am selecting a server that contains the policies so all of the policies on that server will appear in my list. Select the policy we modified with the new target.

new_condition7

To verify we’re using the right target click on the policy name (in this case ‘Database Auto Shrink’). This will open the policy itself and you can verify that the correct target is in place. You should see your ‘Every database – User and System’ in the Targets window. Click Close to close the policy. With your policy checked click on the Evaluate button to proceed. The policy will then run and show you your results. In the target details box you should see your policy has run against all your databases, both user and system.

user-and-system-databases

There is also a way to create this through a script. I created this particular via the SSMS gui but if you want to export it you can let SSMS create the T-SQL Script for you and share the code. To do this right-click on the condition in SSMS, Script Condition as, CREATE TO. To have it go directly to creating the .SQL script file for you select ‘File…’ otherwise lets select the ‘New Query Editor Window’ so we can see our result.

export-condition

For this particular policy you should see something similar to this be output to SSMS:

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'System and User', @description=N'Allows you to select both user databases as well as all system databases.', @facet=N'Database', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>OR</OpType>
  <Count>2</Count>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>EQ</OpType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>Bool</TypeClass>
      <Name>IsSystemObject</Name>
    </Attribute>
    <Function>
      <TypeClass>Bool</TypeClass>
      <FunctionType>True</FunctionType>
      <ReturnType>Bool</ReturnType>
      <Count>0</Count>
    </Function>
  </Operator>
  <Operator>
    <TypeClass>Bool</TypeClass>
    <OpType>EQ</OpType>
    <Count>2</Count>
    <Attribute>
      <TypeClass>Bool</TypeClass>
      <Name>IsSystemObject</Name>
    </Attribute>
    <Function>
      <TypeClass>Bool</TypeClass>
      <FunctionType>False</FunctionType>
      <ReturnType>Bool</ReturnType>
      <Count>0</Count>
    </Function>
  </Operator>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

And that’s it! You now have your T-SQL Code to create your new condition on other SQL 2008 Servers. Note of caution, however, be careful about how you use this as messing with system databases can be disasterous if you’re not careful.

Categories
SQL Server Syndicated Syndication Tips Tutorial

How to Create a Server-Side Trace with SQL Profiler

This morning on Twitter my friend (and as per request, “handsome moron”, but trust me he’s no moron) Jeremiah Peschka (Twitter) asked about scheduled traces. I suggested he do a server-side trace. Unfortunately when you Google how to do this it just tells you how to do this via T-SQL but to my dismay I didn’t see an article on how to create/set this up from SQL Server Profiler. So this will be a quick rundown on how to create your trace using the GUI of SQL Server Profiler and schedule your trace to run at a time of your choosing (so that way you don’t have to wake up at 4 am to kick off a trace).

1. Cut a hole in the box…oh wait, wrong instruction set. Open up SQL Server Profiler and create a new trace. To open Profiler you can either launch it from within SSMS (SQL Server Management Studio) by going to the Tools menu and selecting SQL Server Profiler. The other way to launch it is by clicking on Start button, going to Programs, Microsoft SQL Server 2008 (or 2005), Performance Tools and you should see the shortcut for Profiler there.

clip_image002

2. Check your save to options. Since this will be running on the server itself you’ll have to make a decision here. If you save to table try to save to a database that is NOT the monitored server, preferably into a separate database/table you create for this purpose. You can create a separate database for this purpose and either keep the database for reporting purposes or drop it when you’re done with your analysis. If you don’t want to create a whole new database you can create a new table in an existing database. The advantages of saving to a table are that you can use native SQL tools to do your analysis or even Excel. Preferably you’ll want to save trace data to a file as its faster but for this demo the save to database method is the one we will use.

a. When you select this option you will be prompted to connect to a SQL instance of your choosing. Select the server you’ll be doing your trace on.

b. Here you can name your new table (creates table for you, assuming you have permission to do so).
clip_image004

c. Your other option is to save to file. This will create a series of .TRC files wherever you specify. The advantage to using this option is that your results become portable in that you can move and import those files wherever you need them to do your analysis (i.e. copy them to your local PC and do analysis locally).

3. Configure your trace’s stop time. This is important as you want to make sure you have a consistent slice of time measured. This can later be modified within the script if you’d like.
clip_image006

4. At this point we can customize our trace. Click on the Events Selection tab. Here you can select the events and filters you’d like to trace on. Be careful as to not choose too many options/filters as this can bog down the trace and cause performance problems on the server itself since you’re trying to log so many things at once. For purposes of this demo I’ve chosen the Standard (default) template and default settings.
clip_image008

5. Run your trace…and then stop it immediately. This sounds a little strange but keep in mind we’re not running our trace right now, we just want the script that makes up this trace. You start the trace by clicking the Run button at the previous screen. Once the trace starts you stop it by clicking on the Stop button or by going to the File Menu and selecting Stop Trace.
clip_image010

6. Collect your trace script. Once you’ve stopped your trace go to the File menu, Export, Script Trace Definition, select For SQL Server 2005-2008. Select a location to save your script (.SQL) file.

7. To see your script, open it up in SSMS. In the script you’ll see all the options you chose in the Profiler GUI. The @DateTime variable is the option for when to stop your trace so modify that as needed. Of note, check the number after @TraceID ouput (in screenshot it’s 0). If you leave it at 0 the trace file will fill up and not rollover. To avoid this, replace that with 2. You can read all the options on Books Online.

UPDATE: One of the unfortunate parts of this is that the scripted trace only supports writing trace to a flat file, so you can’t specify SQL table like in the GUI version. In this trace file make sure you specify location for your trace file location as well.
clip_image012

8. Now to schedule your newly created script. In SSMS connect to the server you want to trace on. Go to your SQL Server Agent and expand it so you can see the Jobs folder. Right-click the Jobs folder and select New Job.
clip_image014

9. In the New Job Step dialog, click on the Open button. Browse to where you saved your trace script file, select it and click Open. It might prompt you twice, just repeat. Once you’ve loaded it you should see the contents of your script file in the Command window. NOTE: At this point you can also choose what account to run this script as by selecting an account from the dropdown menu under Run as. Click OK when you’re done.
clip_image016

10. Next we need to schedule your trace. Click on the Schedules link on the side menu. You can pick an existing schedule or create a new one. Here you’ll want to create a New schedule. Give your schedule an easily identifiable name. Since we’re creating a custom schedule you can do a one-time job or you can choose to run this on a recurring schedule (i.e. once a month). The recurring schedule would be advantageous if you’re looking to monitor your SQL performance on a regular basis and want to make sure you trace the same actions every time. One thing to note is that you want to set your start time here under the ‘Occurs once at’ section. Remember, you’ve already scheduled your stop time for the trace within your script. Once you’re done customizing your schedule click OK when you’re ready to proceed.
clip_image018

11. Click OK to finish creating your new job. To see your newly created job look under the Jobs folder in SQL Server Agent. Congratulations, you’ve now created an automated server-side trace using SQL Server Profiler!

Categories
Tips Windows

Remote Server Administration Tools for Windows 7 (RC)

If you’re like me and trying out the Windows 7 RC on your everyday machines, there are certain key tools you find you need to do your work. For us system administrators Remote Server Administration Tools is definitely (or should be) one of those. I installed the previous version of tools on my workstation and it blew up on me (whoops!). Thankfully Microsoft, in their infinite wisdom, has released a version of this software for us Windows 7 folks! Just follow the directions on the page to figure out how to install the tools.

http://www.microsoft.com/downloads/details.aspx?FamilyID=f6c62797-791c-48e3-b754-c7c0a09f32f3&displaylang=en

UPDATE: The RTM version of the tools have been released! Check it out

Categories
Tips vmware

How to Kill a Stuck VM in ESX 3.5

This morning my VM’s decided to become schizophrenic and stop opening consoles. Virtual Center showed the VM’s as still running but some weren’t responding to pings, some were but all had this error when trying to connect via console:

error connecting to vmfs volumes

Ok, well luckily this was a test environment VM so I tried hard restarting it and I got this lovely message:

Operation failed since another task is in progress.

Really?!? Crap, ok time to head to the oracle of all knowledge Google and BLAM-O I found this thread in the VMware Communities forum with my solution. I learned some nice new things including nice commands via the vmware-cmd as well as the vm-support commands which ended up helping me in the end. Here’s the solution from the thread, thanks to users Duncan Epping and Kim Rubeck for these commands/tips.

vmware-cmd -l = list all the vm’s running, copy and paste the full path to the below command.vmware-cmd <config> stop trysoft
if trysoft doesn’t work you could always kill the process ->
ps -ef | more
find the proces related to the world
kill it.

ps -auxwww | grep -i <vmname>

Find the PID and kill -9 pid

or

vm-support -x – to list vmid’s
vm-support -X <vmid> to kill it.

For the record my solution wound up being identifying the vm’s PID and killing the process directly. After doing that Virtual Center shows the VM as not running. Once that happens simply power it back up like you normally would.

 

Categories
Tips Windows

Got an Installation Stuck in a Loop?

Today I was troubleshooting an issue where an MSI is constantly trying to install itself on the server but cannot find the files needed to complete the install. The errors encountered were the following:

Source: MsiInstaller
Event ID: 1001

Detection of product ‘{GUID}’, feature ‘{program}’ failed during request for component ‘{GUID}’

What happened here was that when the MSI did its install it copied temporary install files in a folder. I believe we ran out of space on that particular server during this time thus throwing the installer for a loop of confusion. So how do you clean up this mess? Well Microsoft offers a tool for free that takes care of situations like these. It’s called the Windows Installer CleanUp Utility.

Here’s the description from the KB article on what it does:

When you are working on your computer and installing a new program, the installation suddenly fails. Now you are left with a partly installed program. You try to install the program again, but you are unsuccessful. Or, maybe you have problems trying to remove an old program because the installation files are corrupted. Do not worry. Windows Installer CleanUp Utility might be able to help. You can use the utility to remove installation information for programs that were installed by using Windows Installer. Be aware that Windows Installer CleanUp Utility will not remove the actual program from your computer. However, it will remove the installation files so that you can start the installation, upgrade, or uninstall over.

After you run the utility, give your machine a restart and try to reinstall the troubled application.

Categories
Advice DBA SQL Server Syndicated Syndication Tips

Starting the SQL Journey: Part II

So in my last post I talked about my personal journey. In this post I’ll go step-by-step what I did to help along the path of learning all this SQL goodness. 

Tip 1 – Crawl Before You Walk…

Don’t know what a SQL statement is? Think T-SQL is an alternative to Coffee-SQL? Fear not, like everything else in this crazy world there’s tons of resources to learn from. One of the first places I learned anything from was W3 Schools website. The site is more aimed at web developers but they have a section for learning SQL (note: SQL is NOT SQL Server. SQL is a language, SQL Server is a product). There are also numerous other websites that teach SQL from the ground up such as SQL Tutorial or SQLCourse. A quick Google search will get you a plethora of options.

Tip 2 – Choose Thy Path!

Moving on from learning basic SQL syntax its time to learn your product. To learn by theory is one thing but its another to have hands on experience with the tools and actually see what a query result looks like and what not. Now I realize that I’m a SQL Server guy but I’ll be fair and list several vendor options. These are all the free editions of the respective platforms:

While I gave several options, this blog is SQL Server-centric so my examples and walk-throughs will keep within that realm. Which brings us to the next step…

Step 3 – Don’t Pigeon Hole Yourself, LEARN EVERYTHING!

Ok maybe not everything but you get the gist. This tip not only applies to learning your specific product but pretty much anything. Just because you’re a DBA doesn’t mean you shouldn’t/can’t learn some basic web development so you know what’s going on on the other side of your queries and vice versa. One of the things that allowed me to reach my current position is that I was always looking to learn more, more, more. Yes, I’m a SQL Server guy but what’s Oracle? How’s it work? What are the differences between SQL Server and Oracle? What else is out there? Even within SQL Server you have so much to learn than just basic server setup. There’s not only the administration piece but there’s also performance tuning, all the business intelligence pieces and the wide world of development and much more. I personally ended up going down the DBA track myself but I still like to play around with the Business Intelligence pieces like Reporting Services and SSIS since they’re interesting pieces unto themselves and work-wise I’m a better employee and professional if I’m able to understand all the pieces of the puzzle.

Tip 4 – There’s a Community, Make Use of It

One of the best things I ever did was start attending user groups. My first experience with them was with the Tampa VMware User Group community. I lucked out since my manager volunteered to be the group’s leader so meetings were held in our offices. The meetings were useful since you got to meet others in the community who made use of the same technology in different ways. Getting to meet others and swap stories was a really cool experience. From there I learned about the local SQL user groups (SQL Server and the BI group) and started attending those. Lately I’ve been branching out more in to the community by making use of social networking tools such as LinkedIn and Twitter. I’ve also started contributing to the community by starting this blog. I can’t tell you how much more satisfying experience it has been, for me anyways, to really be actively involved in the technical community.

Tip 5 – That Guy Seems Smart, Let’s Ask Him!

Before I started getting actively involved with the community my primary objective was just to learn, learn, learn! In doing so one of the first things you’ll learn besides the topic you’re focusing on is who are the people in your field who are the respective experts. For instance it was a SQL Saturday event that I attended where I first learned about Brian Knight and how he was one of the best SSIS gurus around. So from then on whenever I needed to learn something related to SSIS I would defer to his blogs and his recommendations. I’m sure everyone has their own methods of learning from others but I just wanted to share my methodology. Another method that pays off is finding not only those guys who know their stuff but are also entertaining (that’s how I discovered Brent Ozar and SQLBatman).
 
Tip 6 – Never Stop Learning
This week is the SSWUG Virtual Conference and one of the great things that I’ve witnessed is that even the field experts are always learning something new from everyone else. In my opinion the one greatest thing to move forward is the willingness to always keep learning. And almost as important is this quote I heard once: “The ability to succeed is directly proportional to your ability to ask for help.”
 
So that’s pretty much all I can think of for now. Feel free to add your own tidbits and advice in comments below!

Categories
DBA SQL Server Syndicated Syndication Tips

Starting the SQL Journey: Part I

odysseus Recently at the VMware user group meeting I was approached by someone I used to work with. He worked in a non-IT role at my previous job and at his new job he was getting more involved in technical roles. He was attending the VMware meeting in order to understand virtualization better and expand his understanding of the technology. A question he posed to me was “So you’re the DBA at the hospital? How did you get started?”

So this entry will be two-parts, the first addressing how I personally got started and second will be a few tips for someone just starting on their path to follow.

Personally I fell into the database administration role due to my formal education background. In college I had taken some database courses. Originally I thought I would pursue a web development career so I was familiar with creating a web application with a database back-end. Fast forward post graduation and I had no .NET experience which was what the industry was calling for and I couldn’t find a job. Eventually I landed a role as a desktop technician for the Southwest Florida Water Management District. While in that position I learned that the one guy who held my position previously was the one doing the SQL Server administration and had a similar background to mine. Due to this fact I was named his secondary. It’s here that I got my first taste of the administrative side of SQL Server. I learned the basics of the SQL Server architecture,

As time passed my hunger to learn more than just how to do backups grew. Aside from asking questions of my fellow administrators I took to the web and read anything and everything I could. Another important step I took was joining up with the local SQL User Group. This allowed me to see not only what others were doing with SQL Server but it was my first experience with the SQL Community which was key in my journey. Attending the meetings grew into attending any event I could be it a paid training session my company allowed me to go to or any free event I could like SQL Saturday to help me expand my skills.

Eventually I moved on to my current position where I’ve managed to leverage some more advanced SQL DBA skills as well as taken on new challenges such as Sharepoint technologies.I continue using the same methods to learn and grow as I did when I started only now I’ve added some new tricks such as leveraging the power of social networks. In my next article I’ll go in to more detail about each step.

So how did you start your journey? I’ve never done this yet so I’ll try this calling out thing by tagging some of the people that have helped me out through their work. Everyone else feel free to leave your stories in comments!

Brent Ozar
SQLBatman
Pinal Dave
Jonathan Kehayias

Update: Part 2 of this great adventure