SQL Server 2008 and SQL Server 2008 R2 or 2012 or latest version provide a suite of tools for managing and administering the SQL Server Database Engine and other components. The following sections provide an overview of the primary tools for day-to-day administration, management, and monitoring of your SQL Server environments.
SQL Server Management Studio (SSMS)
SSMS is the central console from which most database management tasks can be coordinated. SSMS provides a single interface from which all servers in a company can be managed. SSMS is examined in more detail on the topic of “SQL Server Management Studio.”
Following are some of the tasks you can perform with SSMS. Most of these tasks are discussed in detail later in this website:
Much of SQL Server Managements Studio’s interaction with SQL Server is done through standard T-SQL statements. For example, when you create a new database through the SSMS interface, behind the scenes, SSMS generates a CREATE DATABASE SQL statement to be executed in the target server. Essentially, whatever you can do through the SSMS GUI, you can do with T-SQL statements. As a matter of fact, nearly every dialog in SSMS provides the capability to generate the corresponding T-SQL script for the action(s) it performs. This capability can be very useful as a timesaver for tasks that you need to perform repeatedly, avoiding the need to step through the options presented in the GUI. If you’re curious about how SSMS is accomplishing something that doesn’t provide the capability to generate a script, you can run SQL Profiler to capture the commands that SSMS is sending to the server. You can use this technique to discover some interesting internal information and insight into the SQL Server system catalogs.
SQL Server Configuration Manager
SQL Server Configuration Manager is a tool provided with SQL Server 2008 or latest version for managing the services associated with SQL Server and for configuring the network protocols used by SQL Server. Primarily, SQL Server Configuration Manager is used to start, pause, resume, and stop SQL Server services and to view or change service properties.
SQL Server Agent
SQL Server Agent is a scheduling tool integrated into SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs. SQL Server Agent also handles automated alerts—for example, if the database runs out of space. SQL Server Agent is a Windows service that runs on the same machine as the SQL Server Database Engine.
The SQL Server Agent service can be started and stopped through either SSMS, the SQL Server Configuration Manager, or the ordinary Windows Services Manager. In enterprise situations in which many SQL Server machines need to be managed together, the SQL Server Agent can be configured to distribute common jobs to multiple servers through the use of Multiserver Administration. This capability is most helpful in a wide architecture scenario, in which many SQL Server instances are performing the same tasks with the databases.
Jobs are managed from a single SQL Server machine, which is responsible for maintaining the jobs and distributing the job scripts to each target server. The results of each job are maintained on the target servers but can be observed through a single interface. If you had 20 servers that all needed to run the same job, you could check the completion status of that job in moments instead of logging in to each machine and checking the status 20 times.
The SQL Server Agent also handles event forwarding. Any system events recorded in the Windows system event log can be forwarded to a single machine. This gives a busy administrator a single place to look for errors. More information about how to accomplish these tasks, as well as other information on the SQL Server Agent, will be discussed on , “SQL Server Scheduling and Notification.” page
SQL Server Profiler
The SQL Server Profiler is a GUI interface to the SQL Trace feature of SQL Server that captures the queries and results flowing to and from the database engine. It is analogous to a network sniffer, although it does not operate on quite that low a level. The Profiler can capture and save a complete record of all the T-SQL statements passed to the server and the occurrence of SQL Server events such as deadlocks, logins, and errors. You can use a series of filters to pare down the results when you want to drill down to a single connection or even a single query.
You can use the SQL Profiler to perform these helpful tasks:
We will discuss more about it when we get into “SQL Server Profiler.”
Replication is a server-based tool that you can use to synchronize data between two or more databases. Replication can send data from one SQL Server instance to another, or it can replicate data to Oracle, Access, or any other database that is accessible via ODBC or OLE DB. SQL Server supports three kinds of replication: . Snapshot replication . Transactional replication . Merge replication.
The availability and functionality of replication might be restricted, depending on the edition of SQL Server 2008 you are running. Replication copies the changes to data from your tables and indexed views, but it does not normally re-create indexes or triggers at the target. It is common to have different indexes on replication targets than on the source to support different requirements.
With snapshot replication, the server takes a picture, or snapshot, of the data in a table at a single point in time. Usually, if this operation is scheduled, the target data is simply replaced at each update. This form of replication is appropriate for small data sets, infrequent update periods (or for a one-time replication operation), or management simplicity.
Transactional Replication Initially set up with a snapshot, the server maintains downstream replication targets by reading the transaction log at the source and applying each change at the targets. For every insert, update, and delete operation, the server sends a copy of the operation to every downstream database. This is appropriate if low-latency replicas are needed.
Transactional replication can typically keep databases in sync within about five seconds of latency, depending on the underlying network infrastructure. Keep in mind that transactional replication does not guarantee identical databases at any given point in time. Rather, it guarantees that each change at the source will eventually be propagated to the targets.
If you need to guarantee that two databases are transactionally identical, you should look into Distributed Transactions or database mirroring. Transactional replication might be used for a website that supports a huge number of concurrent browsers but only a few updates, such as a large and popular messaging board. All updates would be done against the replication source database and would be replicated in near-real-time to all the downstream targets. Each downstream target could support several web servers, and each incoming web request would be balanced among the web farm. If the system needed to be scaled to support more read requests, you could simply add more web servers and databases and add the database to the replication scheme.
Merge Replication With snapshot and transactional replication, a single source of data exists from which all the replication targets are replenished. In some situations, it might be necessary or desirable to allow the replication targets to accept changes to the replicated tables and merge these changes together at some later date. Merge replication allows data to be modified by the subscribers and synchronized at a later time. This synchronization could be as soon as a few seconds, or it could be a day later.
Merge replication would be helpful for a sales database that is replicated from a central SQL Server database out to several dozen sales laptops. As the sales personnel make sales calls, they can add new data to the customer database or change errors in the existing data. When the salespeople return to the office, they can synchronize their laptops with the central database. Their changes are submitted, and the laptops get refreshed with whatever new data was entered since the last synchronization.
Immediate updating allows a replication target to immediately modify data at the source. This task is accomplished by using a trigger to run a distributed transaction. Immediate updating is performance intensive, but it allows for updates to be initiated from anywhere in the replication architecture. More details on replication are available on “Replication.” page.
The database mirroring feature available in SQL Server 2008 or latest version provides a solution for increasing database availability. Essentially, database mirroring maintains two copies of a single database that reside on different instances of SQL Server, typically on server instances that reside on computers in different locations. In a typical database mirroring scenario, one server instance serves as the primary database to which the client applications connect, and the other server instance acts as a hot or warm standby server.
Database mirroring involves re-applying every modification operation that occurs on the primary database onto the mirror database as quickly as possible. This is accomplished by sending every active transaction log record generated on the primary server to the mirror server. The log records are applied to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. The mirror database is an exact copy of the primary database. For more information on setting up and using database mirroring, we will see it on, “Database Mirroring.” page.
SQL Server 2008 provides the capability to issue full-text queries against plain character based data in your SQL Server tables. This capability is useful for searching large text fields, such as movie reviews, book descriptions, or case notes. Full-text queries can include words and phrases, or multiple forms of a word or phrase. Full-Text Search capabilities in Microsoft SQL Server 2008 are provided by the Microsoft Full-Text Engine for SQL Server (MSFTESQL). The MSFTESQL service works together with the SQL Server Database Engine. You specify tables or entire databases that you want to index. The full-text indexes are built and maintained outside the SQL Server database files in special full-text indexes stored in the Windows file system. You can specify how often the full-text indexes are updated to balance performance issues with timeliness of the data.
The SQL Server Database Engine supports basic text searches against specific columns. For example, to find all the rows where a text column contained the word guru, you might write the following SQL statement:
select * from resume where description like ‘%guru%’
This statement finds all the rows in the resume table where the description contains the word guru. This method has a couple problems, however. First, the search is slow. Because the Database Engine can’t index text columns, a full table scan has to be done to satisfy the query. Even if the data were stored in a varchar column instead of a text column, an index may not help because you’re looking for guru anywhere in the column, not just at the beginning, so the index cannot be used to locate the matching rows. What if you wanted to search for the word guru anywhere in the table, not just in the description column? What if you were looking for a particular set of skills, such as “SQL” and “ability to work independently”? Full-text indexing addresses these problems. To perform the same search as before with full-text indexing, you might use a query like this:
where contains(description, ‘guru’)
To perform a search that looks for a set of skills, you might use a query like this:
where contains(*, ‘SQL and “ability to work independently”’)
For more information on setting up and searching Full-Text Search indexes, we will see it on , “SQL Server Full-Text Search” page.
SQL Server Integration Services (SSIS)
SSIS is a platform for building high-performance data integration solutions and workflow solutions. You can build extract, transform, and load (ETL) packages to update data warehouses, interact with external processes, clean and mine data, process analytical objects, and perform administrative tasks. Following are some of the features of SSIS:
SSIS is a tool that helps address the needs of getting data—which is often stored in many different formats, contexts, file systems, and locations—from one place to another. In addition, the data often requires significant transformation and conversion processing as it is being moved around. Common uses of SSIS might include the following:
For more information on creating and using SSIS packages, please see “SQL Server Integration Services.”
SQL Server Analysis Services (SSAS)
SSAS provides online analytical processing (OLAP) and data mining functionality for business intelligence (BI) solutions. SSAS provides a rich set of data mining algorithms to enable business users to mine data, looking for specific patterns and trends. These data mining algorithms can be used to analyze data through a Unified Dimensional Model (UDM) or directly from a physical data store. SSAS uses both server and client components to supply OLAP and data mining functionality for BI applications.
SSAS consists of the analysis server, processing services, integration services, and a number of data providers. It has both server-based and client-/local-based analysis services capabilities. This essentially provides a complete platform for SSAS. The basic components within SSAS are all focused on building and managing data cubes. SSAS allows you to build dimensions and cubes from heterogeneous data sources. It can access relational OLTP databases, multidimensional data databases, text data, and any other source that has an OLE DB provider available. You don’t have to move all your data into a SQL Server database first; you just connect to its source. In addition, SSAS allows a designer to implement OLAP cubes, using a variety of physical storage techniques directly tied to data aggregation requirements and other performance considerations.
You can easily access any OLAP cube built with SSAS via the Pivot Table Service, you can write custom client applications by using Multidimensional Expressions (MDX) with OLE DB for OLAP or ActiveX Data Objects Multidimensional (ADO MD), and you can use a number of third-party OLAP-compliant tools. MDX enables you to formulate complex multidimensional queries. SSAS is commonly used to perform the following tasks:
Included for Analysis Services in SQL Server 2008 R2 is PowerPivot for Excel and PowerPivot for SharePoint. PowerPivot for Excel and SharePoint are client and server components that integrate Analysis Services with Excel and SharePoint. PowerPivot for Excel is an add-in that allows you to create PowerPivot workbooks that can assemble and relate large amounts of data from different sources.
PowerPivot workbooks typically contain large, multidimensional datasets that you create in a separate client application and use with PivotTables and PivotCharts in a worksheet. The PowerPivot add-in removes the one million row limit for worksheets and provides rapid calculations for the large data that you assemble.
PowerPivot for SharePoint extends SharePoint 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint. Together, the PowerPivot client add-in and server components provide an end-to-end solution that furthers business intelligence data analysis for Excel users on the workstation and on SharePoint sites. SSAS is a complex topic. For more information on MDX, data cubes, and ways to use data warehousing analysis services we will see in depth on , “SQL Server 2008 Analysis Services.” page.
SQL Server Reporting Services (SSRS)
SQL Server Reporting Services is a server-based reporting platform that delivers enterprise, web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.
Reporting Services includes the following core components:
There are two design tools for building reports: Report Designer, a powerful development tool integrated with Visual Studio, and Report Builder 3.0, which is a simpler point-and click tool that you use to design ad hoc reports. Both report design tools provide a WYSIWYG experience.
Reports are described using the Report Definition Language (RDL). RDL contains the description of the report layout, formatting information, and instructions on how to fetch the data. After a report is defined, it can be deployed on the report server, where it can be managed, secured, and delivered to a variety of formats, including HTML, Excel, PDF, TIFF, and XML. Various delivery, caching, and execution options are also available, as are scheduling and historical archiving.
One major set of enhancements in SQL Server 2008 R2 includes the new and enhanced features in Reporting Services, which includes
Report Manager has also been updated in the SQL Server 2008 R2 release to provide an improved user experience and look and feel. This includes an updated color scheme and layout in an effort to provide easier navigation to manage report properties and Report Server items. You can now use a new drop-down menu on each report or Report Server item in a folder to access the various configuration options for the report or item you choose. Following are some of the key enhancements to Report Manager in SQL Server 2008 R2:
SQL Server Service Broker
SQL Server Service Broker provides a native SQL Server infrastructure that supports asynchronous, distributed messaging between database-driven services. Service Broker handles all the hard work of managing coordination among the constructs required for distributed messaging, including transactional delivery and storage, message typing and validation, multithreaded activation and control, event notification, routing, and security.
Service Broker is designed around the basic functions of sending and receiving messages. An application sends messages to a service, which is a name for a set of related tasks. An application receives messages from a queue, which is a view of an internal table. Service Broker guarantees that an application receives each message exactly once, in the order in which the messages were sent.
Service Broker can be useful for any application that needs to perform processing asynchronously or that needs to distribute processing across a number of computers. An example would be a bicycle manufacturer and seller who must provide new and updated parts data to a company that implements a catalog management system. The manufacturer must keep the catalog information up-to-date with its product model data, or it could lose market share or end up receiving orders from distributors based on out-of-date catalog information. When the parts data is updated in the manufacturer’s database, a trigger could be invoked to send a message to Service Broker with information about the updated data. Service Broker would then asynchronously deliver the message to the catalog service. The catalog service program would then perform the work in a separate transaction. When this work is performed in a separate transaction, the original transaction in the manufacturer’s database can commit immediately. The application avoids system slowdowns that result from keeping the original transaction open while performing the update to the catalog database. For more information on using Service Broker, we will talk on this “SQL Server Service Broker” section.
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute offering SQL Server and T-SQL (SQL Server) Training, SQL Database Administration (SQL DBA) Training and Business Intelligence (MSBI / SQL BI) Training.
We have been working with numerous consultancies in USA & UK. We undertake training on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 versions. We make sure that all our sessions are very much interactive and well structured with doubts clarifications during and after each course.
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE