Join Simon Allardice for an in-depth discussion in this video SQL Server core concepts, part of SQL Server 2008 Essential Training.
You'll often hear SQL Server 2008 referred to as a database, but it isn't. SQL Server 2008 is not a database. It's a database management system and there is a big difference. More specifically it's a relational database management system or RDBMS. Okay, you might be concerned that we're 15 seconds into this course and I'm already starting to hit you with abbreviations. But as you almost certainly know, SQL Server is not your only choice for managing databases. You have Oracle, DB2, MySQL, Access. There are dozens of RDBMS products.
So if we're going to understand what SQL Server is, we have two questions. One, why do we need an RDBMS in the first place? And two, if we're going to have one, since there are many available, why would we want this one, SQL Server, and what's special about this one? So why have one at all? Well the easy answer, you have some data. You, the company that you work for, the clients that you have, or even the business that you're wanting to build. This information could be about customers, products, employees, orders.
It could be information about hits on your website. So it could be text, but it could also be documents. It could be images, audio, or video. Now if you just had a small amount of data, you might not need a database management system. There is nothing really wrong with just using a bunch of text files, or spreadsheets, and storing your stuff in there. But this is not a very robust way to do it, because once starts off as a small amount of data has a tendency to turn into a large amount of data and there is your problem.
What happens when two people try to edit this file at the same time? How about 200 people at the same time? What stops people from going into this and adding invalid or missing data? If this spreadsheet solution is nice and speedy when there are 20 or 250 lines in it, what happens when there are two million lines in it? So often the first pinpoint is having a lot of data. Now what a lot of data means could differ from place-to-place. It could be 500 piece of information, 20,000, a million, 100 million pieces of information that you've got to manage, and to ask questions of this, to query it in a variety of ways.
So we take it and we put this in a database. The RDBMS, your management system, manages this database. It controls what gets accessed to that data. If you want to get to the data, you go through the database management system. But of course, it's not just necessary to have a database because you've got a lot of data. The other key is that whatever is in this database is important. You can't lose that. This is critical to your business. But many companies, particularl, in the web world your, data is your business.
Think of Amazon.com, it's a database. eBay, Craigslist, Google, even lynda. com, the business is the information. Sure, on these sites there are some great programming and a nice front-end that could be a website or an iPhone application but it's all about getting to this data. But it would be wrong to think about a database management system just giving you a place to dump your data. That's the easy part. The true power of a database management system lies in the other stuff that it gives you, the invisible stuff, the things you can't see. The security on this data, the enforced integrity of it, the ability to get to it really fast, and get to it reliably.
This has to be robust. It needs to withstand lots of people at the same time, and even to correctly survive hardware issues without corrupting your data. Now of course, most database management systems promise this, so why SQL Server? Well there are a lot of good reasons to use SQL Server. It's been around for 20 years. It's a very mature product with an immense amount of features. Now of course the choice may have been made already. This may be the chosen database system of the business that you worked for or the clients you have.
Not surprisingly, it's very common in a Microsoft-oriented shop because the integration is great with other Microsoft products, things like SharePoint, ASP.NET for web development, Silverlight, all the Office products particularly like Excel. Those are great with SQL Server. Now if you are a web developer, another very common reason is that many web hosting companies will allow you to rent and create a SQL Server database and often inexpensively, when it's still quite rare to find other enterprise-level databases like Oracle or DB2 on offer.
Now SQL Server also comes with many add-ons for advanced features. There are parts of SQL Server that deal with reporting, with business intelligence and data analysis, integration for making SQL Server work with other database management systems and other systems. It's not just a place to dump some data. Now in many larger organizations, it's not even an either/or. You may have many databases managed by different RDBMSs. You've got SQL Server and Oracle, SQL Server and MySQL, DB2, PostgreSQL.
Now as I mentioned earlier, you often hear the term "database" being used to describe the software product you're using such as Oracle or SQL Server. My database is Oracle. My database is SQL Server. Well that can be a little confusing when you're first learning this. Again, SQL Server is a database management system. We use it to create and manage different databases. One SQL Server can manage many different databases inside it. In the diagram, you actually often see these databases represented as cylinders.
But a great thing about it is these cylinders are significantly transferable. Once you know one, it's easy to work with the others because all these SQL-oriented databases, and there are dozens, kind of work the same way. They have the same core idea at heart. Your data is stored in these different databases. The databases themselves are built of one or more tables. Tables consist of columns and rows. Now we're going to go into the construction of databases a little later. I'm expecting you probably have some exposure to these concepts.
If you've got some database experience with a desktop database application like Microsoft Access or FileMaker, that's great knowledge. But one key difference is that those are single self-contained applications. When you open up Access, it's what you use to create the database, to enter data into it, to manage it, to create reports on it, even to install it on the desktop of your end user. SQL Server is not like that. It's better to think of SQL Server as an ecosystem.
It's not one program, but it's a collection of different components to choose from. The core database engine is first installed, typically on a dedicated server or even a dedicated group of servers. And then there are a variety of different services and tools that you can choose or you can ignore if you don't need them. Major optional components of SQL Server include reporting services. This is for creating and distributing reports based on your data. Analysis services for really deep data analysis and business intelligence.
There is integration services talking between SQL Server and external sources of data, other applications, other databases. Now on top of that, you have a collection of different applications that you can install, or not, for working with this stuff. Those include SQL Server Management Studio. This is the big one for creating and managing your databases. There is Configuration Manager to say what parts of your DBMS are turned on and who can talk to them. There is Business Intelligence Development Studio for creating complex analysis and integration projects.
Even have things like Visual Studio if you're a developer. Now if you wanted to be a SQL Server DBA, a database administrator, you would be expected to know all of these and more besides. Now one thing that often surprises people coming from desktop database applications is that SQL Server does not provide any kind of application targeted at the end user. There is no SQL Server data entry program. The idea is that you will build that yourself. You will provide a website, or a desktop application, or a mobile application that's going to be specific to the database you create.
Now we're not going to focus on application building in this course. We are going to talk about these components. The core of the database itself and the things that you use to create and manage your databases.
- Using T-SQL (Transact-SQL)
- Managing databases with SQL Server Management Studio
- Understanding database normalization
- Using SELECT statements
- Building indexes
- Monitoring database size and integrity
- Backing up and restoring databases
- Creating functions and stored procedures
- Managing database permissions
- Creating and formatting reports
- Adding charts to reports
- Creating and executing a simple SSIS package
Skill Level Beginner
Q: I'm having problems installing the free Express R2 version of SQL Server on Windows XP. I tried 64-bit and 32-bit versions. In the videos, the author installs from a DVD. Do I need to do the same?
Q: The link to the installer for the AdventureWorks sample database, as shown in the Chapter 2 movie "Installing sample databases," no longer works. Where can I find the installer?
A: Microsoft has reorganized its site. The sample files are still there, but they're a bit harder to find. To install them:
1) Visit http://msftdbprodsamples.codeplex.com/.
2) Click the link to "SQL Server 2008 R2 OLTP."
3) Click the AdventureWOkrs2008R2 Data File link and agree to the conditions to download the MDF file.
4) Move the MDF file to your SQL Server Directory, usually located at C:\Program Files\Microsfot SQL Server\MSSQL 10_50.MSSQLSERVER\MSSQL\DATA.
5) Open the SQL Sever Management Studio and connect to your instance using an account with administrative privileges.
6) Attach the sample database by right-clicking the Databases folder in the Object Explorer and choosing Attach from the pop-up menu.
7) Click the Add button in the next menu and navigate to the MDF file in the Locate Database Files window that appears. Select it and click OK.
8) Remove the reference to the log file in the "AdventureWorks2008R2" database details: pane by selecting the Log entry and clicking removing.*
9) Click OK to return to SQL Server Management Studio and complete the attachment process.
*MDF files are the "data" files for SQL Server databases. They often come along with LOG files (ldf files). This one didn't so we need to REMOVE the reference to the non-existent log file. Select the second row in the lower section (it should say File Type: Log and Message: Not Found) and click the REMOVE button.
For an illustrated version of these instructions (with screenshots), click here for a PDF version.