Tag: Tips

Categories
Syndication

SA Does Not Mean Speedy Access

This quick write up comes courtesy of a tweet by Jonathan Kehayias (@SQLSarg) yesterday morning (Please Note: OH means overheard, meaning that statement is something Jonathan overheard. He’s WAYYYYY too smart to actually spread something that dumb as valid advice) . Here’s the tweet:

sqlsarg-tweet

Ok, so I’ve seen a couple of stupid things written up in the last few days but this one just might take the cake. If you have been a DBA for any amount of time then you’ve more than likely come across a vendor application that uses the ‘sa’ account for access to the database. I won’t get into details about the sa (or system administrator) account here but check out this article by Ken Johnson at SQLServerCentral.com about it (check out the discussion thread as well to learn more).

So what exactly is wrong with that statement in the tweet? Well, as stated by Jeff Smith (@hillbillyToad) this morning:

hillbillytoad-tweet

“Ok Jorge, stop making fun of me”. No, as long as you access things using sa for “simplicity” or “optimization” I’m going to beat this over your head like an Acme mallet. Using ‘sa’ account for everything is akin to being handed the keys to the bank and being told “yeah, go ahead and make your deposits and withdrawals from your own account but try not to touch anything else while you’re digging around the vault”. Seriously, I’m not kidding. Handing someone the ‘sa’ account is handing them the keys to your SQL kingdom. Think about it, if you write an application that is accessing your database with FULL admin rights, what if someone performs a SQL injection attack and drops your production tables for kicks?

SQL Injection: It happens

Listen folks, I know that security can be a pain but it’s there for a reason. Don’t get lazy and just assume the user needs an admin account to access the database because 9/10 times it doesn’t. You could probably get by fine on creating a new schema with write/read access and maybe EXECUTE stored procedures permissions. In fact, secure yourself from SQL injection attacks by wrapping your code in stored procedures in the first place.

There’s a ton of resources out there to learn how to properly secure SQL Server. Get up to speed by reading up about Security and Protection on MSDN. There’s also tons of videos and demonstrations out there. Check out the Quest Pain of the Week webcast on SQL Injection courtesy of Brian Kelley (@kbriankelley) and Kevin Kline (@kekline). Finally (WARNING: Blatent self-promotion inbound) make sure to check out SQL University’s security week from Semester 1. Bottom line is if someone tells you “this application needs to run as sa”, have them give you a detailed explanation as to why. Part of your job as a responsible DBA is to protect your data and your database servers. If they simply don’t know any better then offer to educate them on schemas, security groups, etc.  And remember, “because its an optimization” is a stupid answer.

Categories
Syndication

Beware Vendor Databases

You're crunchy and taste good with ketchup
HERE THERE BE DBAs

This is just a quick post to serve as a warning sign to those who are DBAs in shops where you routinely get vendor pre-packaged databases (which should encompass about 99% of you). Today we had a vendor installing a new application which required a SQL database, to which I am ever thankful. Unfortunately this database, as I came to discover after a discussion with my junior DBA, that it was actually a restore of a database that was a created after mashing a bunch of data together from disparate systems. Ouch.

Well long story short after the install I took a look at the database server’s (disclaimer: I put them on a shared SQL instance since the app is relatively small) error log to make sure nothing crazy was happening since during the install they had issues with account creations. FYI that turned out to be a small “we need sysadmin” rights issue to which I said ‘NAY!’ and granted them temp rights to complete their install and then revoked those rights when it was complete. Anyhow the error log showed me something interesting:

Message
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Well that’s interesting. I quickly Banged it out and the first solution I found was a KB article (KB917828) that said these were “normal” messages you’d see in a database restored starting from SQL 2005 SP2 and upwards. I read on as I needed to know WHY these messages appeared and BAM, something hit me and sent off alarms:

The whole procedure cache is cleared when certain database level operations are performed in the following scenarios:

  • A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.

Auto_close enabled on a database on my server? NOT ON MY WATCH! I quickly jumped over the database options and sure enough not only was AUTO_CLOSE enabled but AUTO_SHRINK as well. For those of you curious as to why I’d freak out about this, these options are really resource intensive and this being a shared instance I really don’t need that hassle. If you’d like to read about how/why these two options are bad check out Tim Ford’s blog at http://www.ford-it.com (aka @SQLAgentman on Twitter) and read his post on Dealing with SQL Shrinkage.

So remember kids: check, re-check and double-check what your vendor is doing when they setup databases on your systems. Ideally they should provide scripts for you to be able to validate and evaluate before you just “slap it in” to your production environment.

Categories
Syndication Tips Windows

Windows 7 and Multiple Clocks

So unless you’ve been living under a rock for the last few weeks/months, you may have heard about a little thing called Windows 7. Windows 7 is Microsoft’s latest iteration of its Operating System and along with a slew of other stuff (i.e. security enhancements, pretty new stuff to love, Windows management features, etc.) there’s a nice little feature I find comes in handy, especially when you work with a global community like PASS (and you SQL peeps thought this post wasn’t going to pertain to you…)

The feature I’m talking about is multiple clocks in your system tray. I’m not sure if this was available in Vista as well but I thought I’d share with everyone how to do it in Windows 7. First click on your clock in the bottom right-hand corner.

imageimage

Next click on the ‘Change date and time settings’ link to bring up the Date and Time box. Click on the tab for Additional Clocks.

image

Once here you can add up to two additional clocks in addition to your default clock for a total number of 3 possible clocks! Simple check the ‘Show this clock’ box to enable the clock. Use the dropdown menu to select the appropriate time zone you wish to monitor. You can also give the clock a custom label for display purposes. Once you’re done click OK.

imageimageimage

Now if you click on your clock in your taskbar again you should see your newly added clocks!

image

I live in Florida so the clocks I like to monitor are either West Coast or India since those are the two time zones I tend to end up working with the most. Sure I could probably do the math for someplace close like the West Coast but I’m a geek, and I like to see my information quick and at a glance.

And since we’re talking about Windows 7, I’ve included in this blog a zip file of step-by-step directions on how to do this (screenshots included) courtesy of another really cool feature in Windows 7 called the Problem Steps Recorder.  With this tool you can see step-by-step how a user got to a problem or you can use it to document an issue like I have here.

Download Zip File

Categories
SQL University Syndication

SQL University:Computer Lab at SQLServerbeta.com

Good afternoon students, this is just a quick announcement to let you know that SQL University (sort of) is offering a computer lab for students to cut their teeth on SQL 2008 technology. I say sort of because this lab is actually offered via a partnership with PASS, Maximum ASP, and DELL Computers at http://www.sqlserverbeta.com .

The labs offer up a virtual sandbox environment where you can discover and practice everything you learn here in a dedicated hosted environment! Here’s the lab description:

The beta team has created a Microsoft® Hyper-V™ based private cloud to host individual instances of Microsoft® SQL Server® 2008 on which you can perform your experiments.  You will have access to a Hyper-V instance and SA rights to a SQL Server Instance. The instance is available to you for three continuous hours and you are welcome to come back and use the beta instance as often as you like.
 
We have included the following sample databases for you to perform your experiment upon:
  • Adventure Works 2008 OLTP
  • Adventure Works 2008 DW
  • Adventure Works 2008 AS
  • Adventure Works 2008 LT

We will be adding additional labs in the coming months in preparation for the release of SQL Server 2008 R2, so check back often.

So how do you get in on this great deal? Well just head on over to http://www.sqlserverbeta.com and Register!

Categories
Syndication

SQL University: Basic Tools Pt. II

In Tuesday’s class we covered the different authentication methods as well as did a quick walk-through of the SQL Management Studio interface. In today’s lecture we’ll continue looking at SSMS. Some of the things we look at include how to create a query against a database, brief intro to the Profiler tool as well as a brief intro to the Database Engine Tuning Advisor.

Warning: Video is hosted by YouTube. If you cannot see it your company might be blocking that site. My apologies, I will have an alternative method available in future.

In addition to watching the video you guys have a little bit of homework. Don’t worry, it’s not hard. Just try a few of the things on this list to help you get accustomed to the environment.

  • Connect to your local database using SSMS using Windows authentication
  • Browse to the AdventureWorks Database, Open up the Tables folder and select the TOP 1000 Rows from Person.Address table. In comments below, post the AddressLine1 for AddressID 17.
  • [Extra Credit] Browse to the Security folder, Open up the Logins folder. Create a new login called testlogin. Make it SQL Server authentication, give it password of ‘P@$$w0rd’ (NOTE: Thanks to Brian Kelley for pointing this out. Do NOT use your production servers/databases for this excercise. Also NEVER use password this simple in your production environment…ever. Security will be covered in a few weeks.), make its default database AdventureWorks.

Next week we’ll be covering Backup and Restore with Argenis Fernandez (Blog | Twitter). The full syllabus for the semester will be posted shortly.

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
SQL University Syndication

SQL University: Orientation

Ok everyone, please take your seats. Thank you. Welcome to SQL University, my name is Jorge Segarra and I’ll be helping you start on your journey to SQL Server. You’re here because you either are curious about SQL Server and wish to start down that path or you’ve become an accidental DBA in your organization and require knowledge to get your new job done. I’ll be one of your online professors along this journey. Since this is the orientation we’ll start with some basics you’ll need for this semester.

School Supplies (aka stuff you’ll need for SQL University):

What I’ll ask of students is that you go over this list and get yourself prepared. First day of class will start on Monday, September 28th and we’ll be covering how to use the basic management tool for SQL Server: The SQL Server Management Studio. In the meantime here’s your homework assignment:

1. Install SQL Server 2008 Express (Runtime with Management Tools)

2. Install AdventureWorks Databases – Download here

NOTE: AdventureWorks is a ficticious bicycle company. The databases are example databases that are commonly used for practice with SQL Server so that students can learn basics from. Please Use the Recommended Download (All Databases.x86.msi)

3. Go through the whole SQL Tutorial on W3 Schools (If you don’t know SQL syntax consider this MANDATORY. If you already know the language and you’d like a refresher go ahead and do the tutorial anyways)

4. Either watch the video (about 34 minutes) or download and read through the Relational Database Theory paper to familiarize with key concepts and theories.

So now that you have your homework let’s cover how SQL University is going to work. I have amassed a few fellow faculty members that will help me out with classes this semester. Each staff member will be blogging on a different beginner topic and tagging their post with SQL University. I will be linking their classes here so you can have a central place to come back to and see all of the great SQL University content at a glance. With each topic/class I ask that students try to be active and ask questions in the comments section of each post. The more questions posted the more answers can get posted and the more we ALL learn!

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
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