Getting Started – SQL Server Overview

Overview:

Now that we have covered the basics of T-SQL and how to get the information you will need to make your queries, I'm going to focus on getting familiar with SQL. This blog will briefly cover an overview of SQL Server 2005 and some of the various tools/utilities you can use with it.

SQL Server 2005 – The Platform:

Believe it or not, SQL Server 2005 is NOT an application (like Visual Studio, Microsoft Office, etc); it's an actual platform. What would make this a platform vs. being an application? This is because SQL Server 2005 is a layer (a data layer in particular) within the Microsoft Operating System. When we are done with this blog you'll understand this concept much better; for now, you may want to look at the MSDN website: SQL Server Overview. This will show you that SQL Server offers different components that make up the entire SQL Server Platform; those components are: Integration Services, Database Engine, Reporting Services, Analysis Services, Service Broker, Full-Text Search, Replication, and Notification Services.

SQL Server Components:

Each of these components have different functions it is intended to perform; the basic idea behind having these services is that you can leverage data in almost any fashion you see best fit. Examples of leveraging could be:

• To import/export data to/from other databases (including, Access, Oracle, MySQL, etc)
  
• To generate live-time reports
  
• To achieve live-time notifications of events or data milestones
  
• To hold data that is considered unorganized and get useful information from within this data
  

This is only some of the uses you can make of data and SQL Server. You'll find that nearly any way of storing, retrieving and/or manipulating the data may be performed. It all boils down the knowing what component you can get the most leverage using and how to present that data to the user and/or to the other components in a meaningful way.

How data is gathered, retrieved, and manipulated:

This is only the tip of the iceberg. With the advances made today you'll find that data is an evolving volatile market that is important to almost everyone; if you think about it data runs most things. Your bank account uses data to know how much money you have, you've used, and where you got it from and spent it at. When you shop the grocery stores keep data to know what is being sold, sitting on the shelves, and they will use this data to know what items to keep in stock that target your local area. Ever notice that some items you find in the store near your home aren't carried in the same store that's located a couple of states away? This is usually because they have collected data that says your area likes that item and the other area may not have as high of sales for it.

How data is accessed and stored:

So, now that you have a general understanding that SQL Server is way more than just an application and you can do immense of data storage and extract almost any information at any given time; the question then comes to how can you access the data and/or get more leverage.

This answer can be quite complex; it generally comes down to what you want to do with the data and how you want to do it. The common methods are to use an application to access the methods, or to create an application that can present the data, and capture the data, in a unique method suited best for whatever audience is targeted.

A common used application to create databases, store data, view data, generate reports, and/or manipulate data is by using built-in tools designed by Microsoft. The most common tool used is SQL Server Management Studio (SSMS). This tool provides a seamlessly integrated solution to access any databases stored within SQL Server, and to create any database to be stored in SQL Server. You can also write direct T-SQL statements, stored procedures, and perform a large number of tasks (such as backing up the database, restoring the database, monitoring the activity on an attached database).

Another commonly used application is Visual Studio (VS). This is a development software application used to develop custom software solutions that can interact with the SQL Server or any attached database within the SQL Server platform. This can be done with only using the wizards and controls within the VS environment to create a simple access solution to the data; however, you'll find that ONLY using the built-in wizards and controls will lack many features you would want (such as the ability to search all the records for specific words, the ability to mass delete records, backup the database, etc). If you wanted to have these features, then you would use the VS environment with custom coding to access the data and to present the data to the end-user. There are many books dedicated to doing these types of things, and go beyond the basis of this blog.

How SQL Server is configured and/or monitored:

SQL Server comes with utilities and tools that can help to configure it and to monitor it. A common tool used for configuring SQL Server is the SQL Server Surface Area Configuration tool, sometimes in conjunction with the SSMS (see above) tool; but, it doesn't stop there. Another useful tool included with SQL Server is the SQL Server Configuration Manager. These types of tools allow you to tell SQL Server where it is accessed from, how the database is stored, and many other options.

When it comes to monitoring the database platform you will find that there are many tools/utilities available for this too. SQL Server comes pre-bundled with: SQL Server Profiler and Database Engine Tuning Advisor. Also if you go to MSDN's SQL Server 2005 Tools and Utilities website you will find other tools/utilities such as: RML Utilities and SQL Server 2005 Best Practices Analyzer.

Conclusion:

As you probably know by now, SQL Server is far beyond any application and includes so many components, tools, and utilities that you could spend years just learning how to use them and to capture the information that you want. You probably have even started to realize that SQL Server has tools for its components!

It's well worth to mention that not all tools/utilities will work on all editions of SQL Server; however, Microsoft has gone through great lengths to ensure that SQL Server can be used by just about anyone with a data need, from the single user to the multi-world enterprises. Microsoft has even gone to the extent of making it's commonly used tools available for use with its "free" (at time of this blog) express edition. Sometimes the tools may have slightly modified names (such as, SQL Server Management Studio Express) and there are some options that aren't available for the lower end SQL Server Platforms (such as 64-bit support, and remote login <internet> access), but in most cases you will find that if you are not concerned about those areas or any other affected components (such as reporting services, analysis services) you can get by with using the Express edition.

Even if the express edition won't work for your needs, it's usually worth the time to download it and test out some basic uses to see if its bigger brothers can address those needs. Microsoft can even offer evaluation and development editions that will provide great means of determine the usefulness of the product.

Most of all, don't forget, there are tons and tons of information available for free to help you along the way; when those aren't quite cutting it for you any longer or you don't have access to the free content information you will find tons and tons of books that cover just about all topics you can think of and more!

Additional Resources:

SQL Server 2005 Tools and Utilities on MSDN: http://msdn2.microsoft.com/en-us/sqlserver/bb671105.aspx
SQL Server Overview: http://msdn2.microsoft.com/en-us/library/ms166352.aspx