Listed below is the Fall 2009 Syllabus for SQL University. Please refer back here often as weekly topics/presenters/dates may be subject to change. Each week each professor is asked to present between 2 and 3 articles on a given topic. The days where you see as Optional are listed in case the presenter that week chooses to take advantage of that 3rd day.
Good morning everyone! I just wanted to pass along this announcement that we now our page up for our inaugural faculty for SQL University. You can find the Faculty page here. Yesterday we unveiled our new SQL University bookstore. At the bookstore you can find book titles that are highly recommended by fellow DBA’s, developers and database professionals from around the world. In the future we will also be adding things such as various tools for you to try out so stay tuned for those.
Another announcement is that tonight at 8pm EST Tom LaRock (Blog | Twitter) will be holding his weekly broadcast on UStream for the PASS Elections. We have chosen this forum as our press conference as the invitation has been extended to him to become SQL University’s athletic director. What does that job entail? Well come join us in the broadcast and find out as its going to be a REALLY exciting dynamic to SQL University!
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.
Over the last few months I have been doing the rounds at various user groups and SQL Saturday events presenting on Policy Based Management. In the spirit of my on-going SQL University project as well as the upcoming book I’m co-authoring with Ken Simmons (Blog | Twitter) and Colin Stasiuk (Blog | Twitter), I’ve thrown together this brief video walk-through on Policy Based Management.
Welcome to the first day of SQL University. Today we’re going to be talking about basic tools you’ll be using as a database administrator (DBA).
Throughout our lessons you will notice I will be linking heavily to SQL Server Books Online. Books Online is the official documentation for all things SQL Server. This is important to know as many administrators and developers refer to this documentation on a daily basis as well as in everyday conversation. You can access Books Online in one of two ways. One is directly via the website on MSDN or you can actually download Books Online (Click to Download Latest as of 9/23/09) so that you can access and refer to the documentation even when no network access is available. While downloading it for offline use can be beneficial (and portable) be aware that Microsoft does update Books Online with new information which means you would have to download and install the latest version of Books Online when this occurs. The good news is that when you use the local version of Books Online it does ask you up front if you want to use the internet as the first point of reference. Another advantage of having Books Online locally installed is that you can bookmark topics and searches so you can save time if you find yourself referring to a certain topic (which I can almost guarantee you will!). That being said make sure you explore the various links given to fully get the most out of the content delivered here at SQL U.
The most basic tool in the SQL Server toolset for an administrator or developer is a management graphical interface called the SQL Server Management Studio (SSMS). SSMS is where you can access, configure, manage and administrate your servers. The following video walks you through the basics of SSMS so you can become familiar with it. Before you watch the video there are a few things you need to know about SQL Server.
Authentication
In order to connect to a server or database you need to provide it valid credentials. This method is referred to as authentication. SQL Server recognizes two different types of authentication: Windows authentication and SQL Server Authentication. Windows authentication (sometimes also referred to as Integrated Security) is when you provide SQL Server Windows account credentials. This can be either a Windows domain account (i.e. domainusername) or a local Windows account (i.e. local-machineusername). By default Windows authentication is the default authentication method selected when you open SSMS, and of note, is also more secure. We’ll get in to the hows and whys of that in another class. When you open SSMS, the Windows credentials for the account you are logged into the machine as will automatically pass to SSMS. For instance if I’m logged into my computer as a user called JSEGARRA, that is on a domain called MSDOMAIN, SSMS will open and you will see in the box for username (will be greyed out) MSDOMAINJSEGARRA.
The second method of authentication is SQL Server Authentication (sometimes also referred to as just SQL Authentication). This method of authentication is useful for instances that, for whatever reason, do not have access to a Windows domain account or just a domain in general. SQL accounts are created and kept within the database instance itself. An example of a use for this type of authentication method would be a database server that resides outside of a company firewall so that the public needs to get to it. Typically these servers are kept in what’s called the DMZ (demilitarized zone), which is an area that belongs to the company but is segregated from the internal network for security reasons. Since the DMZ is outside of the normal network you wouldn’t be able to authenticate with a domain account so instead we use local credentials like a SQL account.
Best of Both Worlds
For those curious, yes you CAN have both Windows authentication and SQL authentication enabled on your database server. This mode is called Mixed mode since you’re mixing both types of authentication methods. Be aware, however, that this increases your attack surface as you’re opening more holes to access your database server. Microsoft best practices recommend using Windows authentication for security reasons (account is managed at domain level, leverage AD groups, etc.).
Video: Walkthrough of SSMS pt 1. (9:02)
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.
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):
SQL Server 2008 Express (Runtime with Management Tools) – Download link
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.
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)
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!
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’re that awesome for me because I know what’s going on. By that I mean I’ve 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’s that have no idea where to start? The one place I noticed this trend the most was at SQL Saturday. While there are individual sessions that are aimed at beginner level I haven’t seen any tracks or actual dedicated sessions to starting SQL Server from scratch. For instance what are the basic tools of the trade? What’s 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’s just my take. My hometown user group, the Tampa SQL Server User Group 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 PASS 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!
