SQL Server is a Database Management System (RDBMS) from Microsoft. It is also known as MS SQL. SQL Server is an Enterprise level database application just like Oracle, DB2 or MySQL. There are several different versions of MS SQL, the most common ones are SQL Server 2008 and SQL Server 2012. If you want to try a free SQL Server version, please visit this Microsoft site.
Historically speaking, SQL Server was developed as collaboration between Microsoft and Sybase. SQL Server version 4.2 was available in 1993. The first version of MS SQL developed completely by Microsoft was SQL 6.0 and then SQL 6.5. In 1998, Microsoft came up with SQL Server 7 followed by SQL 2000. Microsoft pretty much built the next version MS SQL 2005 from the ground up. Next version of Microsoft SQL Server 2008 was available for production in 2008. The latest product SQL Server 2012 link is on the market now.
SQL Server is a massive product with lots of moving parts and pieces. In is post, we are going to look at a handful of the important ones in the order of their importance. If you are interested in learning MS SQL, please visit our partner sites at SQL Server 2008 Tutorial, SQL Azure Tutorial and SQL Server 2012 Tutorial.
What is SQL Server Management Studio (SSMS)?
SQL Server Management Studio is by far the most important tool in SQL Server. It is used for administration in addition to development of objects like MS SQL databases, tables, stored procedures, logins and SQL jobs. In order to launch SSMS you can follow this path:
Programs > Microsoft SQL Server 2008 > SQL Server Management Studio
Here is a screen shot of What SQL Server Management Studio looks like on our computer, by the way we are using MS SQL 2008 R2 version:
If you are a visual person like me, you can watch FREE SQL Server tutorial along with sample videos on SQL Server at this location.
Important components within SSMS are:
Databases: Using this icon/folder, you can create objects like MS SQL databases, tables, views, stored procedures, functions, triggers and constraints. You will be spending most of your time in MS SQL right here, guaranteed!
Security: Within this folder, you have the ability to create new logins (for access to MSSQL Server). You can also create server level roles (similar to Windows groups), and setup security audits.
Server Objects: Here you can create backup devices, endpoints (point of entry to a SQL Server), Linked Servers (non SQL providers that can interact with MS SQL).
Replication: This is the ability of SQL to synchronize data between a primary and a secondary server(s). With replication, you can configure items like publishers, subscribers, publications, articles, etc.
Management: You can perform SQL Server management tasks like data-tier applications, policy management, setup maintenance plans; look at SQL Server logs, setup Database mail and configure Full Text Search.
SQL Server Agent: Vital component in SQL 2008 component for database administration. You can manage SQL instance, create jobs, alerts, operators, proxy accounts and also review SQL Server agent logs.
A visual representation of the MS SQL Agent is as follows.
SSQL Server Configuration Manager (SSCM)
This is another vital management tool for SQL Server. You can control SQL Server services, Server protocols; limit Client level protocols, client aliases and other surface area configuration pieces. You can get o SSCM by doing the following:
Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configure Manager
I have included a screen shot of SSCM from our MSSQL server:
Here are the available options under SSCM:
SQL Server Services: With this, you can control items like MSSQL Server service (database engine), SQL Agent service, SQL Server Integrations service (SSIS) and SQL Server Reporting service (SSRS).
SQL Server Network Configuration: Here you can configure protocols (network communication standards) for connection to Microsoft SQL including TCP/IP, Named Pipes and Shared Memory
SQL Native Client Configuration: Just like on the server side, you can configure protocols at the client side. In addition you can setup alias to connect to the SQL Server.
Import and Export data:
Using the SQL Server import and export wizard (SSIEW), you can move data to and from SQL Server. Microsoft SQL supports a number of different providers like Oracle, IBM DB2, flat file (.txt), Office applications, OleDB and ODBC compliant drivers. SSIEW walks you through all the necessary steps and at the end will create an Integration Services (SSIS) package. We will cover Integration Services under Business Intelligence Development Studio.
What are SQL Server Performance tools?
It’s just a fact of life, sooner or later your SQL Server database will grow and grow and run into performance issues. It may be a combination of higher volume of data, poor database design or inefficient Transact SQL queries. In order to solve the problem, SQL Server provides you the following two tools right out of the box:
SQL Profiler can capture every single SQL Command, stored procedure and database process that you are running on SQL Server. In a sense, you create a trace which stores a chronological list of every event on SQL Server. You can then open the trace and review it later for performance tuning and query analysis.
Database engine tuning advisor (DETA)
This next performance tuning tool is also an excellent utility that can help you find not only missing indexes but show you how to improve existing indexes in SQL Server. First you have to either create a SQL Profiler trace or a TSQL script, next you need to be loaded it up into database engine tuning advisor which will then make its recommendations.
What is SQL Server Business Intelligence Development Studio (BIDS)?
When you install SQL Server on your box, you will also be given Business Intelligence Development Studio. This is a cut down version of Visual Studio and allows you to create projects on Integration Services (SSIS), Reporting services (SSRS) and Analysis Services (SSAS). You can launch BIDS by doing this:
Programs > Microsoft SQL Server 2008 > SQL Server Business Intelligence Development Studio
Like we mentioned in the Import and Export data section, SQL Server Integration Services is an ETL (Extract, Transform and Load) Tool that can move data back and for between MSSQL and other data providers. Our trainers cover a basic way to create a SQL Server at Integration package in this sample training video.
Here is what a SSIS package looks like in BIDS. We are loading a flat file (.txt) into SQL Server.
SQL Server Reporting Service (SSRS) is a full blown reporting solution from Microsoft. SSRS uses RDL (Report Definition Language) and XML to create and manage reports. You can use BIDS to create a report. You can deploy it to the Reporting Server which will process reports and handle end-user transaction. It has the ability to allow users to not only pull SQL reports but also push reports on demand at a fixed schedule.
Here is video on SSRS from YouTube:
This is what an SSRS report looks like in BIDS.
SQL Server Analysis Services (SSAS) is part of the business Intelligence set of tools from Microsoft. SSAS will let you perform Online Analytical Processing (OLAP) which lets you create multi-dimensional cubes. SSAS also enables its users to do Data Mining which lets you analyze massive data by different attributes. This will uncover patterns and useful information that may not be readily available from an online transactional Processing (OLTP) system.
Miscellaneous topics on MS SQL:
What else is there in MSSQL? you ask. Well a lot! SQL Server has many more components and features that we have not even mentioned. Here are a few important ones:
SQL Server Failover Clustering: This provides High Availability support for the entire SQL Server instance. First you create Windows Server Failover cluster and then add one or more nodes to it.
Database Mirroring: This is a High availability solution in SQL Server at the database level. In case of a database failure, the database is switched from the Principal (active) to the Mirror Server. In this manner, there is minimal interruption from the data consumer point of view. You can take a look at this Database Mirroring video on YouTube.
SQL Replication: This is the ability to synchronize data between different MSSQL databases. This is a popular option for mobile Sales professionals who are on the road and can sync with the master database on as needed basis. You can setup on of these types: Snapshot, Merge and Transactional replication.
Transact SQL (TSQL): This is the flavor of Structured Query Language (SQL) for Microsoft. You can manipulate your data using commands like SELECT * FROM TABLE. Our partner has more than 60 TSQL scripts to help you be a better SQL DBA or SQL Developer!
Sqlcmd Utility: Sqlcmd is a command line utility in SQL Server. You can use it to execute TSQL statements, stored procedures and other script files from the command prompt. This is useful when you use batch files or run automated processes like setting up a new server.
Services: Using Windows Services manager, you have ability to Start and Stop MS SQL Services. You can get to this by either typing in services.msc in the Start menu or by following this path
Control Panel > Administrative Tools > Services
SQL Server Related Links:
-SQL Server Home Page
-SQL Server 2012 Training
-SQL Server Central – Excellent resource on MSSQL topis
-How to implement Security in MS SQL 2008?