SQL Server Tips & Tricks
Just wanted to send out a quick note that I’ll be presenting via LiveMeeting for the New Jersey SQL User Group next Tuesday, February 16th at 6:40 PM EST. I’ll be doing my Policy-Based Management talk. I will post the link to the presentation here when I get it.
NJ SQL User Group meeting page
LiveMeeting link: https://www323.livemeeting.com/cc/usergroups/join?id=B6MF59&role=attend&pw=
Well the title says it all and you’re thinking “well obviously we’ll see it live if we’re there” but that’s not the big news: I’ll be broadcasting my SQLSaturday sessions via LiveMeeting this Saturday! That’s right, for those who can’t make it and want to get a taste of SQLSaturday, join us in my sessions over the internet! Big thanks to Jermiah Peschka (Blog | Twitter) for setting this up for me. Links to the LiveMeetings are listed below for the two sessions I’ll be doing:
UPDATE: Ok Jonathan Kehayias (Blog | Twitter) has brought to my attention that internet at the event might be spotty at best so this live event may or may not happen. Follow me on Twitter for updates as they happen…
Policy Based Management 101 (10:15 am-11:15 am EST) – LiveMeeting Link
Twitter and SQL Server:A Perfect UNION (1:15-1:30 pm EST) – LiveMeeting Link
I’m going to try and have the sessions recorded, let’s see how it goes!
AUDIO INFORMATION
-Computer Audio(Recommended)
To use computer audio, you need speakers and microphone, or a
headset.
FIRST-TIME USERS
To save time before the meeting, check your system to make sure it is
ready to use Microsoft Office Live Meeting.
http://go.microsoft.com/fwlink/?LinkId=90703
TROUBLESHOOTING
Unable to join the meeting? Follow these steps:
1. Copy this address and paste it into your web browser:
https://www.livemeeting.com/cc/8000181573/join
2. Copy and paste the required information:
Meeting ID: 9KHZ82
Entry Code: dnP`2rr&G
Location: https://www.livemeeting.com/cc/8000181573
If you still cannot enter the meeting, contact support:
http://r.office.microsoft.com/r/rlidLiveMeeting?p1=12&p2=en_US&p3=LMInfo&p4=support
NOTICE
Microsoft Office Live Meeting can be used to record meetings.
By participating in this meeting, you agree that your communications
may be monitored or recorded at any time during the meeting.
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.
Next week we’ll be covering Backup and Restore with Argenis Fernandez (Blog | Twitter). The full syllabus for the semester will be posted shortly.
For those who don’t know, PASS has several Virtual Chapters. Each VC has its own distinct topic they focus on. The most recent VC established is the Virtualization Virtual Chapter (say that 3 times fast). Our group is dedicated to helping spread the education of virtualization to folks who are thinking of jumping into the virtualization realm with their SQL Servers. We’re pretty excited as we have some top notch virtualization experts such as Duncan Epping (Blog | Twitter), Scott Lowe (Blog | Twitter) and Brent Ozar (Blog | Twitter) who will be syndication their blogs on the Virtualization VC’s blog feed.
Tomorrow afternoon Wednesday, September 30th at 12pm EST | 4pm GMT, the PASS Virtualization Virtual Chapter is proud to host our first webcast featuring Denny Cherry (Blog | Twitter) discussing the pros and cons of moving SQL Servers into a virtual environment. We’ll be talking about when it’s a good idea, when it’s a bad idea, and why there’s no hard-set answers. We’ll be focusing on how to make this decision and how to gather the metrics you need to make a good decision. Audio will be provided through the speakers. This presentation will be presented via Windows LiveMeeting so make sure you arrive early and make sure everything is ready to go on your end. See you tomorrow at the presentation!
Meeting Invite Link: https://www.livemeeting.com/cc/usergroups/join?id=BP7D93&role=attend&pw=x_](2cfJ4
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.
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.
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.
For those who weren’t able to make the event, some of the recordings from the 24 Hours of SQL PASS event are now available on-demand from the PASS Summit website. You can register for the webcasts at the link below:
Registration for 24 Hours of PASS Selected Sessions
You may be asking “well what about the other sessions?”. The rest of the sessions should be available online after the Summit in November. But that shouldn’t matter much because you’re going to PASS…right? RIGHT?!?!