By: Jeremy Kadlec | Updated: 2010-02-23 | Comments (10) | Professional Development Career Planning
I am new to SQL Server and I have heard the term used in a number of different capacities and in different contexts. So what exactly is it? What are the common components? How does it work? How do people use it? I am brand new to the technology, where do I get started?
SQL Server originally was a relational database engine. Over time it has grown to include a number of new technologies. SQL Server 2008 is the current version and it consists of the following components:
- SQL Server Relational Engine
- SQL Server Integration Services
- SQL Server Reporting Services
- SQL Server Analysis Services
With this basic set of products outlined, let's try to outline what SQL Server is used for and who uses it, at a high level. SQL Server is a large suite of products, so this tip will try to cover the basic concepts with URL's for additional information. This tip is intended to serve as a stepping stone to learn SQL Server.
The relational engine is most often referred to as 'SQL Server' in most DBA, Developer, IT and Business circles. At a high level, SQL Server is installed on a Windows Server. SQL Server is managed by a set of services which can be started, stopped, paused or disabled. The two main services are the SQL Server service and SQL Server Agent.
The SQL Server service is the main service and it is responsible high level items like: databases, security, replication, etc. The key item to cover is the concept of the database. In many respects a database is a logical concept. In general you have two types of databases. First, are system databases that are from Microsoft and used to manage SQL Server. Second, are user defined databases that a DBA would build to support an application need by a business.
The database is made up of tables, code, indexes, security, etc. The tables have rows and columns storing the data. The most common coding objects are stored procedures, views, functions, referential integrity, triggers, etc. Indexes are built on tables to improve the access to the data. Security is established to prevent access to data and/or execute particular commands.
On to the physical side of the database. Databases typically have 2 files when they are built. First, is the database file which typically has an extension of MDF. All of the items (i.e. table, view, stored procedure, etc.) associated with the database are stored in database i.e. *.MDF file. Second is the transaction log file which typically has an extension of LDF. At a high level, the transaction log is responsible for storing versions of the data before and after the changes in order to maintain the data integrity.
This explanation is intentionally at a high level because SQL Server is such a large product, but we would be remiss not to include additional key components of the relational engine:
- Backup and Recovery - Ability to issue backups and restores of the databases
- Full Text Search - Ability to issue a catalog to improve complex free form querying
- Service Broker - Queuing based technology internal to the database engine
- Database mirroring - High availability tool to maintain multiple copies of a complete database
- Replication - Ability to replicate a portion of a database to multiple SQL Servers
- Maintenance - Ability to rebuild indexes, statistics, etc. in order to improve data access and performance
SQL Server Agent
SQL Server Agent is the second SQL Server service we will outline relative to the SQL Server engine. It's primary responsibility is scheduling Jobs in order to execute particular operations at specific points in time. SQL Server Agent also has the ability to notify operators based on specific errors, Job failures or business conditions.
SQL Server Relational Engine Programming Languages
The main programming language in SQL Server is called Transact-SQL or T-SQL. SQL is an abbreviation for structure query language. This language can be divided into two broad categories. First is DDL, which is an acronym for data definition language. These commands are to CREATE, ALTER and DROP database objects such as tables, views, functions, indexes, etc. Second is DML, which is an acronym for data manipulation language. These commands are primarily SELECT, INSERT, UPDATE and DELETE. This portion of the language is where programming logic like IF, IF...ELSE, WHILE, etc. would be used.
In general T-SQL is the most widely used language for DBAs\Developers and probably the best place to start learning SQL Server. However, SQL Server does support other programming languages internal to the database engine and in some cases, these languages are preferred. Here is a brief explanation:
- CLR is the acronym for Common Language Runtime which extends executing compiled .NET code directly from the database engine.
- "LINQ is a set of extensions to the .NET Framework that encompasses language-integrated query, set and transform operations. It extends C# and VB with native language syntax for queries and provides class libraries to take advantage of these capabilities, available only in .NET Framework 3.5." (Source - Introduction to Language Integrated Query (LINQ))
- SMO is an acronym for SQL Server Management Objects which has an object hierarchy built on the .NET Framework. Check out this tip - Getting started with SQL Server Management Objects (SMO).
- PowerShell has gained a great deal of popularity recently with Network, System and Database Administrators since it's introduction in 2006. It is also built on the .NET Framework and leverages SMO when working with SQL Server objects directly.
SQL Server Integration Services
Integration Services was introduced in SQL Server 2005. It replaced Data Transformation Services (DTS) which was introduced in SQL Server 7.0. In a nutshell, Integration Services is a engine to perform data extraction, transformation and loading (ETL). This is a complicated way of saying moving data from one location to another. The locations can be SQL Server databases, flat files or other database platforms such as Oracle, DB2, Access, Sybase. The SQL Server Integration Services development is conducted inside of the Business Intelligence Development Studio.
The Business Intelligence Development Studio offers a feature rich development tool to efficiently manage the code, change management, error handling, etc. The Integration Services Package can be executed directly or scheduled with SQL Server Agent. Although Integration Services offers a very feature rich solution, you may encounter other T-SQL commands that also meet the ETL needs of many organizations to include BCP, BULK INSERT, OPENROWSET, etc. Keep in mind Integration Services is a separate installation option (Tip 1, Tip 2 and Tip 3) when you install SQL Server.
SQL Server Reporting Services
Reporting Services was originally released after the introduction of SQL Server 2000. Reporting Services provides report authoring (development), rendering and management features. In many environments, Reporting Services is installed on a separate SQL Server just to handle reporting needs. Keep in mind Integration Services is a separate installation option (Tip 1, Tip 2 and Tip 3) when you install SQL Server. Here is another valuable tip for SQL Server 2005 - Adding Reporting Services to an existing SQL Server installation. During the installation process new SQL Server programs are installed in addition to two databases to support the report meta data and temporary objects. After the installation, configurations are needed to access the report meta data and to setup features to email, encrypt the data, etc.
SQL Server Analysis Services
Analysis Services is the primary business intelligence tool in SQL Server. Analysis Services provides the means to build and query multi-dimensional data. Analysis Services and the relational engine have a number of parallel concepts. Both have databases, programming languages, security, backup and recovery features, etc. The key concept with Analysis Services are cubes. If you are a visual person you can think of cubes as a very large "Rubix Cube" with a number of different ways to access the data to determine trends, opportunities, etc.
Here are some tips to begin learning about Analysis Services:
- How To Build a Cube From an Existing Data Source Using SQL Server Analysis Services
- How To Build a Cube Without a Data Source Using SQL Server Analysis Services
- Getting Started with SQL Server Analysis Services
- All Analysis Services tips
SQL Server Tools
SQL Server has a variety of tools to meet particular administrative and development needs. These tools include:
- SQL Server Management Studio
- Primary tool to manage SQL Server 2005 and 2008
- Primary development tool for SQL Server 2005 and 2008
- Data modeling
- Code development - Stored procedures, views, functions, etc.
- Installs for free as a portion of the SQL Server 2005 or 2008 installation
- Business Intelligence Management Studio (BIDS)
- Enterprise Manager (EM)
- Query Analyzer (QA)
- Primary tool for coding or writing queries in SQL Server 2000
- Installs for free as a portion of the SQL Server 2000 installation
- SQL Server Profiler and
- Performance monitoring tool to capture transactions issued against SQL Server including the reads, writes, start time, end time, etc.
- Installs for free as a portion of the SQL Server installations
- Performance Monitor
- Windows tool to capture macro level statistics
- Portion of Windows operating system with SQL Server counters dependent on the version and installations
- Applicable tips
- Books Online
- Official product documentation from Microsoft
At MSSQLTips, we have organized the industry leading tools in one place. Check them out now.
Who works with SQL Server?
In reality more people work with SQL Server in their day to day tasks than anyone probably realizes. Many web sites and core business applications are supported by SQL Server. Typically SQL Server based applications are design, built, maintained and enhanced by DBAs, Developers, Data Modelers, Network Admins, System Admins, Storage Admins, etc. In a business setting, users interact with SQL Server in the following ways:
- Core Business applications
- Web and desktop
- SharePoint applications
- Reporting applications
- Decision support applications
- Dashboards and score cards
- This tip is intended as a stepping stone to learn SQL Server. If you are new to SQL Server check out the URLs for the particular section of tips that is most interesting to you.
- If you still have questions about SQL Server or need to figure out the next steps in your learning process, please add entries to the forum link below or give us some feedback.
- As you learn SQL Server stop back to MSSQLTips to expand your knowledge and contribute to the community.
Last Updated: 2010-02-23
About the author
View all my tips