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 ( | ). The full syllabus for the semester will be posted shortly.
Ok everyone, please take your seats. Thank you. Welcome to SQL University, my name is Jorge Segarra and I鈥檒l be helping you start on your journey to SQL Server. You鈥檙e here because you either are curious about SQL Server and wish to start down that path or you鈥檝e become an accidental DBA in your organization and require knowledge to get your new job done. I鈥檒l be one of your online professors along this journey. Since this is the orientation we鈥檒l start with some basics you鈥檒l need for this semester.
School Supplies (aka stuff you鈥檒l need for SQL University):
SQL Server 2008 Express (Runtime with Management Tools) 鈥�
NOTE: If you already have a SQL 2008 instance to play with don’t worry about step 1. This is meant for those who are starting from scratch. For stuff like SQL Server Agent we’ll discuss it but assignments won’t depend on you having it.
SQL Server Books Online 鈥� |
Basic understanding of SQL Syntax –
Basic understanding of Relational Database Theory 鈥� |
What I鈥檒l 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鈥檒l be covering how to use the basic management tool for SQL Server: The SQL Server Management Studio. In the meantime here鈥檚 your homework assignment:
1. Install SQL Server 2008 Express (Runtime with Management Tools)
2. Install AdventureWorks Databases 鈥�
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鈥檛 know SQL syntax consider this MANDATORY. If you already know the language and you鈥檇 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鈥檚 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!
It’s going to be a fun month! Next week I’ll be speaking at the on Policy Based Management 101. I also got confirmation that not only was my PBM presentation accepted for on October 17th but I’ll also be doing a MINI session on !
I’m REALLY psyched about SQL Saturday as there are going to be some awesome presenters there such as Andy Leonard ( | ), Ken Simmons ( | ), Joe Webb ( | ), Brian Knight ( | ), Kevin Kline ( | ), Andy Warren ( | ), Buck Woody ( | ), Kendal Van Dyke ( | ), Chad Miller ( | )听补苍诲 many more! The speaker lineup looks like a veritable East Coast edition. If you can’t make it to the Summit this year (and I highly encourage you to do so if you can) then definitely try to make it to SQL Saturday #21!
And as if the lineup of talks at the SQL Saturday event wasn’t enough to blow your mind, the week leading up to it there is a week of! Again, this looks like a rock star line up featuring Andy Warren, Brian Knight, Kevin Kline, Andy Leonard and Buck Woody. Each session is $150 and WELL worth the investment.
Recently I was thinking about my experiences with all of the SQL Server learning events and while they have all been absolutely awesome I realized one thing: they鈥檙e that awesome for me because I know what鈥檚 going on. By that I mean I鈥檝e been around SQL Server for awhile now so when I attend the sessions I already have a basic understanding of everything that is being discussed. Granted I always learn something new from these sessions but what about the first timers and accidental DBA鈥檚 that have no idea where to start? The one place I noticed this trend the most was at . While there are individual sessions that are aimed at beginner level I haven鈥檛 seen any tracks or actual dedicated sessions to starting SQL Server from scratch. For instance what are the basic tools of the trade? What鈥檚 SQL Server Management Studio? Who are the go to people for help?
Given this fact I would like to propose that organizers of SQL Saturday events consider putting in a dedicated track for Entry Level SQL folks. My suggestion is to call it SQL University but that鈥檚 just my take. My hometown user group, the will be organizing a SQL Saturday event in the coming months and I hope to get this program implemented in there. My hope is that individuals who are new to SQL Server, or would like to get into it, have a dedicated track in which they can feel comfortable going to without expectation of certain knowledge. In the meantime I will be doing a series of blog posts entitled SQL University which will cover the world of SQL Server from the ground up. In these posts I’ll cover basics such as tools to use, basic dba tasks etc. But that’s not all, I’d like to extend the help of my fellow bloggers on this project. If you’d like to become a SQL University professor hit me up at jorge@sqlchicken.com. I’ll need professors in the different aspects such as administration, development and business intelligence. For this first semester of SQL-U I’d like to keep the classes at the 100-level. We’ll see how the project develops from there.
So, friends, what are your thoughts? Would you like to see something like this implemented? Do you think it will help raise awareness of organizations like and bring in more attendees to our events? Let me hear your thoughts in the comments.
Update: WOW the response for this project has been really great! I now have a small pool of professors that will be helping out with this first semester. Due to this I’m closing the call for bloggers at this time. A big thank you to them (I’ll announce them soon) and a big thank you for all those willing to help out!
So the inaugural event for has come and gone. In its wake its left hundreds of eager minds reeling from absolutely amazing content overload, bleary and blood-shot eyes around the globe and one .
First off the staff at PASS and all of the presenters deserve a standing ovation for the amazing job they did putting this event together. From marketing the event across multiple platforms (i.e. Twitter, Facebook, Summit site) as well as adding a fun bonus add-on with having Tom LaRock (), aka the , do a full 24 hour co-broadcast via Ustream. While I wasn’t able to have the insanity fortitude of Tom and brave all 24 hours (did anyone else actually accomplish this insane feat?) I did manage to catch most of the ones that were of most interest to me. I’ll recap in order of sessions attended.
Hour 1 (8:00 PM EST) – 10 Big Ideas in Database Design by Louis Davidson (|) and Paul Nielsen (| )
I won’t lie, I pretty much missed the entirety of this session trying to get my desktop working so I could bring up the Live Meeting. The only thing I had working was the laptop running Tom’s and chat. Luckily this setup allowed me to listen to the session so I was in and out as far as paying attention while I tried to troubleshoot my desktop woes. It’s Dr. SQL and Paul Nielsen, can’t go wrong with these guys and from what I caught there was a lot of good information that people should pay attention to when developing. When this session becomes available on demand I’ll definitely be watching it.
Hour 2 (9:00 PM EST) – Using PowerShell to Get the Most Out of SQL Server by Allen White ()
Powershell sessions always leave me feeling like less of a man for not knowing and using Powershell yet. TMI? Anywho, Allen did a great job showing some cool tips and tricks in showing how you can access and control different aspects of SQL Server via Powershell.
Hour 3 (10:00 PM EST) – Team Management Fundamentals by Kevin Kline ( | )
This was one of my favorite sessions that I attended. Kevin’s topic was about how to effectively manage an IT team. Some of the stuff he mentioned is pretty universal but there are caveats when dealing with tech folks that he covered and covered well. Grab his slide deck and check it out, excellent presenter on a great topic. It’s funny because today Brent Ozar ( | ) wrote up an article as well.
At this point I bowed out for the evening so I could grab some shut eye and get up early for Gail Shaw’s ( ) Effective Indexing presentation at 6 am. Alas, I missed that one but got to the office early to catch the next one. I WILL be watching Gail’s presentation on demand when its available as effective indexing is something every DBA and developer needs to take the time and fully understand.
Hour 12 (7:00 am EST) – Reporting Services Inside and Out: The Things You Should Know by Simon Sabin (|)
Reporting Services is one of those pieces of SQL Server that you look at it and think “damn I could do soooo many cool things with it”. You attend the webcasts, read the books, and if you’re anything like me you end up too busy to get to that awesome idea you had. Simon did a bang up job walking through SSRS and showing some cool tips and tricks with it. I especially liked the part where he created a report that dynamically changed pictures of either a check mark or an X depending on the result of the query on the column. I’ll definitely be re-watching that later on to try that out. Funny part of this session came when Simon had to excuse himself to take a call as he and his wife are expecting their baby to be born any day now. God speed Simon and hope your bundle of joy comes soon and healthy!
Hour 13 (8:00 am EST) – Query Performance Tuning 101 by Grant Fritchey (|)
This was one of the most heavily attended sessions (about 450 people!) and for good reason, Grant is a fantastic presenter and his knowledge on the subject of performance tuning is second to none. I attended a session of Grant鈥檚 a couple of weeks ago and I got something new out of that. Same thing happened with this session. Grant鈥檚 book, , is on my list of must-buy books and if you have any interest in development or performance tuning it should be on yours as well. If you ever get a chance to attend any session by Grant I HIGHLY encourage you to do so. After that hour you鈥檒l come away feeling like you want to re-write every bit of code within your grasp (and in some shops that probably wouldn鈥檛 be a bad idea!).
<insert a few hours of me doing my actual job here>
Hour 17 (12:00 pm EST) 鈥� Building a Better Blow by Steve Jones (|)
Maybe its because I absolutely love Steve as a presenter (make sure to catch his weekly podcast, ), maybe its because I鈥檝e taken up blogging in the last few months, whatever it was I have to say this one of my favorite sessions overall. Steve basically went over reasons why starting and maintaining a blog can be beneficial for you both personally and professionally. He also covered a topic of great importance in this realm which was basically DO NOT STEAL OTHER PEOPLE鈥橲 CONTENT!!! Seriously, it happens more than you think and if you get caught you just look really, really bad. Other highlights of this presentation were magically seeing my own profile come up in his slide deck as an example of a basic profile (thanks again Steve!) as well as watching Tom make Steve laugh on the air since apparently Steve had Tom鈥檚 broadcast up on another monitor.
<Insert a few more hours of meetings and doing work. Yeah, I know, you鈥檙e shocked>
Hour 24 (7:00 pm EST) 鈥� Embed Reporting Services into Your Applications by Jessica Moss (|)
As I mentioned before Reporting Services is one of the those really cool features I wish I took more advantage of. Jessica is the SQL community鈥檚 resident expert on the topic and she does a great job of walking through the product and clearly explaining everything. This was a pretty cool session in which she showed the differences between the types of Reporting Services reports you can use and create. For instance did you know that in addition to .rdl files there are .rdlc files that are client side reports? Well up until last week I didn鈥檛 know that!
Overall Thoughts
Overall I thought this was a fantastic event and I can鈥檛 thank and the enough. There were some glitches in the links for the Live Meeting links but thanks to the awesome community that problem was quickly handled via Twitter, chat rooms and blog posts. These events are mind-blowingly awesome in that you ALWAYS walk away with some new piece of knowledge and best of all鈥T WAS FREE! The other thing about an event like this is that this is just a taste of what the annual can offer you. At the not only do you get all of this mind-blowing content but you get to interact with presenters, you鈥檙e surrounded by the , and you get to expand your social network thanks to all skills you got from the AT the conference. So what are you waiting for? Print out your , present it to your boss and get your rear to the PASS Summit. Make sure to by September 15th to take advantage of the discounted pricing. After the 15th the price goes up $400.
Recently after one of my presentations a Twitter-buddy of mine, Argenis Fernandez ( | ), showed me something interesting. By default when you create a new policy the against targets default says 鈥楨very Database鈥� (see screenshot below).
Well this is a tad misleading as it apparently means 鈥渆very鈥� 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鈥檛 work. So how do we create a new condition/target that allows us to run a policy against both user AND system databases? We鈥檙e 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 鈥楴ew condition鈥︹��
First you want to give this condition a new name. For this demo we鈥檒l call it 鈥楨very 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 鈥楧atabase鈥� 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.
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 鈥楾rue鈥�.
Now that you鈥檝e 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 鈥楥lick here to add a clause鈥�. When you click on it you鈥檒l notice you get a new row to be able to create a new expression. For the first column (AndOr) we want to select 鈥極R鈥�. This is important because if you select AND in this instance, you鈥檒l 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:
Click OK and you should now see your new 鈥楨very 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 鈥楨valuate Policies鈥︹��
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.
To verify we鈥檙e using the right target click on the policy name (in this case 鈥楧atabase Auto Shrink鈥�). This will open the policy itself and you can verify that the correct target is in place. You should see your 鈥楨very 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.
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 鈥楩ile鈥︹�� otherwise lets select the 鈥楴ew Query Editor Window鈥� so we can see our result.
For this particular policy you should see something similar to this be output to SSMS:
Declare @condition_id intEXEC 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 OUTPUTSelect @condition_id
GO
And that鈥檚 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.
Chris Shaw ( | ) started another . He tagged , Brent tagged and Tom tagged me. This particular quiz is a two-part question and here are my responses. Excuse my lack of wit and charm. It鈥檚 late, my caffeine supply is running low and my cat is eyeballing me in a most peculiar fashion.
Do you feel that you have a reliable SAN solution? If so, what鈥檚 the secret?
It鈥檚 hard for me to answer this one given that just a few weeks ago our data center (SAN included) came crashing down hard. Now given that the problem was a faulty generator test and not the SAN itself that was the problem I can鈥檛 really place blame there. Honestly I can鈥檛 say anything really bad about our SAN. Tons of disk space, tons of cache, it does what its supposed to do. We鈥檙e also in the middle of finding a replacement for a SAN administrator so what SAN solutions we have in place now could radically change in the coming months. As far as secrets go I鈥檇 say you just need to make sure you have an open line of communication with your SAN administrator. They have no idea what鈥檚 going on in your world and you have no idea what鈥檚 going in theirs. Clear communication of needs need to be there as a SAN admin worth his salt is going to know what they need to do on their end to make sure you get the best performance on your end (i.e. proper RAID levels for your LUNs depending on needs, I/O throughput, etc.).
Explain Database Mirroring in layman鈥檚 terms
Everyone else seems to be giving off-the-wall answers to this so I鈥檒l give it a go as well. Not going to lie, took me a good portion of my drive to Melbourne last week to finally come up with an example.
Think of clustering as a conjoined twin. You鈥檙e talking to the same body. Both heads can hear the conversation but ultimately the two are stuck together because they have to share the same base trunk. Now if you were to punch one twin in the face and knock him out you鈥檇 still be able to talk to the other head but you鈥檙e still lugging around that base. Now, imagine a set of regular twins (non-conjoined). This is your database mirroring in that you have two separate entities. You knock one out but the other is still chugging along just fine. Only difference being that the location of the second one doesn鈥檛 really matter because he doesn鈥檛 have a shared trunk to deal with. I鈥檓 sorry if that explanation sucks, if you鈥檙e bored written by someone who doesn鈥檛 have a fascination for using genetic defects to compare feature sets. Ok time for me to tag a couple of unwilling victims fellow bloggers:
