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 SQL Management 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 Managment 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 Management Studio tutorial along with sample training video on SQL Server at this location.
Important SQL components within SSMS
Databases: Using this icon/folder, you can create objects like MS SQL databases, tables, views, stored procedures, functions, triggers and constraints. Moreover you can manage users and database roles within the particular database. 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 (user accounts with access to the actual MSSQL Server). You can also create server level roles (similar to Windows groups), setup security audits and other safekeeping mechanisms.
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 MSSQL) and Triggers.
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 within this location
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 for troubleshooting purposes.
A visual representation of the MS SQL Agent is as follows.
SSQL Server Configuration Manager (SSCM)This is another important 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:
Options under SSCMHere 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). Check out the SQL Server videos on YouTube that cover some of these topics.
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 which can be either saved to MSSQL or a standalone SSIS package file in Windows.
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. No offense to your technical know-how!! 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 excellent tools right out of the box:
SQL Profiler:This little tool is a lifesaver utility in SQL Server. It 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. Here is what the SQL Profiler looks like on our end:.