The management aspect of SQL Server 2005 administration refers to tasks such as creating new databases and database objects, performing backups and restores; managing logins, users, and other security settings; scheduling jobs and reviewing job history; importing and exporting data; and processing Analysis Services objects such as cubes and dimensions. Authoring, on the other hand, refers to the ability to write and execute queries and scripts, design new SSIS packages, design and create new Analysis Services objects such as cubes and dimensions, and create new reports. SQL Server Management Studio and Business Intelligence Development Studio are the two tools that provide the management and authoring capabilities in SQL Server 2005. SQL Server 2005 Express Edition includes Express Manager, which can be considered a highly trimmed-down version of Management Studio for managing and authoring relational database objects.
SQL Server Management Studio
SQL Server Management Studio can be used to manage SQL Server versions 7, 2000, and 2005, Analysis Services version 2005, Report Servers 2005, SQL Server 2005 Mobile Edition (formerly SQL CE) databases, and SSIS (formerly DTS) servers. Management Studio also extends the management capabilities to support other SQL Server subsystems, such as notification services, Service Broker, and Database Mail.
The following are some of the features of Management Studio:
Figure 5.1 shows Management Studio in action. The Registered Servers window shows a SQL Server 2005 instance and a SQL Server 2000 instance; the Object Explorer window shows a connection to an instance of SQL Server 2005, Analysis Services 2005, SQL Mobile Database, SSIS Server, and a SQL Server 2000 instance; the Properties window shows the current query window options; typing the T-SQL word BEGIN in the Query Editor brings up the related help topics in the Dynamic Help Window; Template Explorer shows the SQL Server templates and a list of recently used templates; finally, the Solution Explorer window shows a project open.
Figure 5.1. SQL Server 2005 Management Studio is a new application for managing servers and authoring queries.
The SQL Server Management Studio windows are discussed in detail in the following sections.
The Registered Servers window allows you to register SQL Server, Analysis Server, Report Server, SQL Mobile Database, and SSIS Server. For each type of server, it lets you create a new server group; add, edit, and remove server registration; and connect Object Explorer or Query Editor to the selected registered server. The icon next to each server indicates whether the server is running. You can export the server registration details into an XML file and import it onto another server by using the Registered Servers window (by right-clicking in the window and selecting either Import or Export). You can double-click a registered server to connect and view the selected server in the Object Explorer window.
A hierarchical tree view allows you to interact with the objects on the server. For SQL Server, the tree looks similar to the one shown by SQL Server 2000 Enterprise Manager; it contains databases, tables, views, replication, security, and so on. For Analysis Server, the tree looks similar to one shown in Analysis Manager; it contains data sources, cubes, dimensions, and so on. For Report Server, Object Explorer provides options similar to the Report Manager web interface. This includes managing reports and report folders, security, schedules, and jobs. For Mobile Database, Object Explorer allows you to manage tables, views, and Merge Replication subscriptions. For SSIS Server, Object Explorer can be used to view the status of running packages and enables you to work with packages stored in the file system and the msdb system database.
Unlike Enterprise Manager, Object Explorer is designed to handle and work well with a large number of servers, databases, and database objects. When you expand a tree node or folder, Object Explorer asynchronously retrieves only the information required for that node and not for child nodes. Object Explorer allows you to filter the nodes based on various parameters, such as name, creation date, and schema. The Object Explorer tree is limited to 65,536 objects. If you have more than 65,536 objects, Object Explorer does not list them, and the only way to see those objects is to use filtering.
The Connect button on the Object Explorer toolbar allows you to connect to a server that is not registered. After you connect to the server, you can right-click it and select Register to add that server to the Registered Servers list.
For SQL Server, Object Explorer groups system databases in one folder, group database snapshots under another folder, and lists all other user databases in the tree. Unlike Enterprise Manager, which lists the objects on the right side in a grid view, Management Studio shows all the tables, views, and other database objects as tree nodes. If you need to script more than one object, you can right-click a database and select Tasks | Generate Scripts. This brings up the Generate SQL Server Scripts Wizard, in which you can select a database and database objects that you want to script. Pressing the F7 key or by selecting View | Summary brings up a read-only report on the right side for the currently selected item in Object Explorer. Depending on the item selected in the Object Explorer tree, the Summary tab may provide more than one reports. The Report toolbar button on the top of the Summary tab can be used to navigate between multiple reports.
Object Explorer does not allow you to select multiple items. The multi-select support is provided via the Summary page. On Summary page, you can select multiple tables, views, columns, users, schemas, and other objects, and then you can right-click and select Delete to remove the selected objects.
The stored procedures, database-level DDL triggers, functions, .NET assemblies, types, rules, and defaults are grouped under the Programmability node. The full-text catalog and partitioning schemes and functions are shown under the Storage node. The database users, roles, schemas, and symmetric keys are under the Security node.
To hide or show system objects in Object Explorer, select Tools | Options and check or clear the Hide System Objects in Object Explorer check box after you select Environment | General.
In addition to databases, these are the other nodes in the Object Explorer tree:
The Filter icon on the Object Explorer toolbar is enabled when you select nodes such as Tables, Views, or Stored Procedures. Figure 5.2 show the Object Explorer and Summary windows inside Management Studio. Note that system objects are hidden in Object Explorer, the tables list is filtered, and that you can select multiple objects on the Summary tab.
Figure 5.2. Object Explorer allows you to filer objects, and the Summary tab supports multi-selection.
The Query Editor
SQL Server Management Studio includes a rich Query Editor that can be used to author and edit T-SQL, MDX, DMX, XMLA, and SQL Server Mobile Edition queries. Much like Query Analyzer, the Query Editor window in Management Studio provides syntax coloring; can output results to a grid, text, or file; and can display estimated or actual execution plan and client statistics.
You can split the Query Editor window into two parts by using the splitter control above the scrollbar. This can be useful when you want to simultaneously view different parts of the same file.
The Options dialog box provides various settings that you can change to tune the Query Editor environment. You can view query, results, messages, and other windows as tabbed or MDI child windows; view line numbers and enable word wrapping; set query execution options such as various SET options; set query result options such as maximum number of characters displayed in a grid cell or a text column; switch between SQL Server 2005 and SQL Server 2000 keyboard schemes; and so on.
You can press Shift+Alt+Enter or select View | Full Screen to view Query Editor in full-screen mode and maximize the query editing area. You press Alt+U or Shift+Alt+Enter again to switch back to normal mode.
You can select the File | New (or press Ctrl+N) to start a new query window, or you can right-click the server in Registered Servers select Connect | New Query to launch a new query window. Selecting File | New | Project (or pressing Ctrl+Shift+N) allows you to create a new project and a new solution or add a new project to an existing solution. The three project types available include SQL Server Scripts, Analysis Server Script, and SQL Mobile Scripts. After you create a new project, you can use the Solution Explorer window to define new connections and create new queries.
You might have noticed that if any Microsoft Office product, such as Word or Excel, shuts down abnormally, the next time you launch the application, you are prompted to recover the document you were working on. The SQL Server Management Studio supports a similar feature. Let's say you were working on a bunch of script files and did not save all the changes you made. Now, if somehow the Management Studio tool shuts down unexpectedly, the next time you launch Management Studio, it will allow you to recover the script files that you were working on, preserving the changes you made in the last session.
Management Studio contains a built-in web browser. You can launch the web browser by pressing Ctrl+Alt+R or by selecting View | Web Browser | Show Browser. Try typing a Web address (such as http://www.microsoft.com) in the Query Editor window, and you should notice that the editor turns the text into a hyperlink. You can press Ctrl+click the URL to launch the web page in a web browser inside Management Studio. The General page after you select Tools | Options | Text Editor | All Languages or Plain Text or XML provides an option to enable or disable this single-click URL navigation feature.
Table 5.2 lists few useful keyboard shortcuts that you can use while working in Query Editor.
Microsoft has removed some of the functionality from Management Studio Query Editor that is available in Query Analyzer. For example, you can play a .wav sound file when the query finishes executing in Query Analyzer. The Results tab of the Options dialog in SQL Server 2000 Query Analyzer provides an option to play a Windows message beep or a .wav file when a query finishes. No such feature is available in Management Studio.
The other windows in Management Studio include Template Explorer, which can be used to access commonly used T-SQL, MDX, DMX, XMLA, and Mobile queries; Solution Explorer, which lets you manage multiple scripts and connections as a SourceSafe integrated project; the Properties window, which shows the context-specific metadata; and Help windows, which include dynamic help, an index, and the contents.
The General tab of the Options dialog in SQL Server 2000 Query Analyzer provided an option to change the template file location used by the Templates window in Query Analyzer. However, to create a new template folders and template files, you had to go to the file explorer and create folders and files there, and then you had to press F5 or right-click and select the Refresh menu item in the Templates window for the new folders/files to appear in the tree. In other words, the Templates window did not provide any option to add or remove template files and folders. With SQL Server 2005, the template folder for the Template Explorer window is fixed to %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchnewitems, and Template Explorer right-click menu options are provided for creating and removing template folders and files.
Common Administration Tasks
The goal of the following sections is to show you how to perform some common administration tasks by using SQL Server Management Studio. First, you need to start SQL Server Management Studio and try out the following to see how to get things done using Management Studio.
Registering a Server
Following are the steps to register a SQL Server instance:
Viewing and Changing Server Properties
Following are the steps for viewing or changing SQL Server instance properties:
Similarly, you can right-click the SQL Server Agent folder in Object Explorer and select Properties to view and set agent properties such as job history, log size limit, SQL Server event forwarding, and so on.
Creating a New Database
Following are the steps for creating a new SQL Server database:
Backing Up or Restoring a Database
Following are the steps for backing up an existing database:
Similarly, to restore a database or transaction log, you follow these steps:
The right-click Tasks menu on any user database allows other operations as well, including attaching, detaching, copying a database, shrinking, importing/exporting data, and generating scripts.
Managing Indexes, Triggers, and Constraints on a Table
Under each database, Object Explorer shows folders such as Database Diagrams, Tables, Views, Synonyms, Programmability, Service Broker, Storage, and Security. All the user tables are shown under the Tables folder. If you expand a table node, you see folders such as Columns, Keys, Constraints, Triggers, Indexes, and Statistics. You can expand each such folder to see existing objects and right-click a folder to create a new item of the selected type. For example, you can right-click TRiggers and select the New Trigger menu item to launch Query Editor window to create a new trigger. Let's create a table, and then create an index and a constraint on this new table:
Viewing SQL Server and Agent Error Logs
The Log File Viewer in SQL Server Management Studio has been enhanced to consistently show SQL Server and Agent log files, as well as Windows event logs. A single screen allows you to view zero or more SQL Server log files, zero or more Agent log files, and all the Windows event log sources. It also allows filtering, searching, exporting, and loading of log files. You can rearrange the columns shown, but the first column, the Date column, is fixed, and if you scroll to the right, the Date column does not scroll, so you can continue to see the date while viewing the other columns on the right side.
Figure 5.3 shows the Log File Viewer screen with the current SQL Server log, current Agent log, and System Windows NT event log source selected, and the log rows are filtered for text containing the word SQL. Different icons in front of the Date column value and the Source column indicate the type and source of the log file row.
Figure 5.3. The Log File Viewer in SQL Server Management Studio shows SQL Server and Agent log files and Windows NT event log data, on a single screen.
You can use the SQL Server Logs folder under the Management folder, or the Error Logs folder under the SQL Server Agent node in Object Explorer to launch the Log File Viewer. Once the Log File Viewer window is open, using it to view, filter data, and so on is very intuitive. The blue filter icon on the toolbar means no filter has been applied, and the green filter icon means rows are filtered based on the provided conditions.
By default, SQL Server keeps the last six log files. You can change this number by right-clicking SQL Server Logs and selecting Configure. Similarly, you can right-click Error Logs under SQL Server Agent and select Configure to set Agent log properties such as the error log file location and the kind of information to log.
Some Miscellaneous Administration Tasks
Before concluding this section, let's look at some other things that you can do by using Object Explorer in Management Studio:
Business Intelligence Development Studio
As mentioned in Chapter 4, "A Tour of New Features in SQL Server 2005," Microsoft's strategy for business intelligence in SQL Server 2005 is to integrate, analyze, and report. The Business Intelligence (BI) Development Studio is a new integrated application that supports this strategy. The BI Development Studio tool can be used to build and deploy end-to-end business intelligence solutions.
Like SQL Server Management Studio, BI Development Studio also borrows the Visual Studio .NET shell and supports working with SourceSafe integrated solutions and projects.
BI Development Studio allows you to create SSIS projects, SQL Server Analysis Services 2005 (SSAS) projects, and SQL Server 2005 Reporting Services (SSRS) projects. You can create a BI Development Studio solution containing a mix of projects of type SSIS, SSAS, and SSRS and then deploy the entire business intelligence solution to a test or a production environment. Figure 5.4 shows the BI Development Studio environment with an open solution containing an SSIS project, an SSAS project, and an SSRS project.
Figure 5.4. BI Development Studio supports designing, developing, and deploying end-to-end business intelligence solutions.
Integration Using SSIS
As mentioned previously, in SQL Server 2005, the DTS platform has been renamed SSIS. It is a new platform that lets you bring data from various sources such as file systems, databases, XML files, and web services; transform and integrate the data; and send the data to various types of destinations, such as file systems, databases, mobile devices, analysis and reporting servers, and so on.
You can author SSIS packages by creating an Integration Services Project in BI Development Studio. The SSIS package designer inside BI Development Studio provides an enhanced interface that simplifies the authoring and debugging of the SSIS packages. The packages created using the SSIS designer are saved as XML files with the .dtsx file extension. You can right-click a package and select View Code to see the XML for an SSIS package definition.
The SSIS designer now separates control flow, which includes process-oriented and discrete tasks, from data flow, which includes data-oriented tasks. Examples of control flow tasks include For and Foreach Loop Container tasks, the File System task, the Send Mail task, the Execute SQL task, and so on. You can either convert your DTS 2000 packages by building new SSIS packages from scratch and making use of the new SSIS features, or you can use the Execute DTS 2000 Package Task control flow task and execute a SQL Server 2000 DTS package from within an SSIS package. The data flow tasks are categorized in three groups: source, transformation, and destination. Examples of data flow tasks include Flat File Source, XML Source, OLE DB Source, Fuzzy Grouping, Lookup Transform, Sort Transform, Flat File Destination, SQL Server Mobile Destination, DataReader Destination, and so on. Figure 5.5 shows the new SSIS designer environment. The SSIS package in this figure contains an OLE DB source, a data conversion transform, and an OLE DB destination. The data conversion error rows are directed to a flat-file destination.
Figure 5.5. The new SSIS package designer inside BI Development Studio separates control flow from data flow, provides several built-in task controls, and allows you to send erroneous rows to a different route.
SSIS provides a very robust debugging environment. You can set breakpoints at various steps in the extraction, transformation, and loading (ETL) process and view data as it is flowing through the steps. SSIS also offers you the flexibility to route erroneous rows to one path and all other rows to another path. The SSIS designer in BI Development Studio includes several built-in control and data flow tasks that you can use in your SSIS package. If you don't find a task control that suits your need, you can use the SSIS.NET-based extensibility programming model to build custom control and data flow task controls. The enhanced configuration and logging support in SSIS facilitates building and troubleshooting complex SSIS packages. See Chapter 12, "SQL Server Integration Services Overview," for more details on SSIS.
Analysis Using Analysis Services 2005
The things that business intelligence information workers used to do in Analysis Manager with SQL Server 2000 are now available inside BI Development Studio, with several usability and user experience enhancements. After you create a new Analysis Service project, you can use various wizards and templates provided by BI Development Studio to create the rest of the Analysis Services objects, such as data sources, data source views, cubes, dimensions, mining models, and so on, to be deployed in a single Analysis Services database. You can either create a new solution for the project or add the project to an existing solution. Figure 5.6 shows an Analysis Services project open inside BI Development Studio.
Figure 5.6. The SQL Server 2000 Analysis Manager functionality is now available inside BI Development Studio, with several user interface and usability enhancements, as well as support for new Analysis Services 2005 features.
As shown in Figure 5.6, the Analysis Services project contains items such as data sources, data source views, cubes, dimensions, and so on. You can right-click Data Source and select New Data Source to launch the Data Source Wizard; similarly, you can right-click Cubes and select New Cube to launch the Cube Wizard. Analysis Services is discussed in further detail in Chapter 13, "What's New in SQL Server Analysis Services 2005."
Building Reports Using Reporting Services
The final major component of BI Development Studio is the ability to author and edit reports. Prior to this, you had to use Visual Studio .NET to author the Reporting Services reports. Now, because reporting is an integral part of the business intelligence development life cycle, it is integrated into BI Development Studio. You can add reports to your business intelligence solution by creating a new report project, and you can either add it to the current solution or create a new solution for the project.
When you select a project of type Report Project, BI Development Studio presents an empty environment. You start by defining data sources, and then you create new reports, update the project properties to specify the deployment report server, and so on. BI Development Studio also contains another project type, called Report Project Wizard, that walks you through defining a data source, creating a report, and specifying the deployment server information. When you finish that wizard, you have a report ready for preview and deployment.
The report designer in BI Management Studio is very similar to the Visual Studio .NET report designer. It has a Toolbox window from which you can drag and drop items onto the designer, a Data tab that lets you define the source for report data, a Layout tab that is the primary design surface, and the Preview tab that lets you view your report in action. (Refer to Figure 5.4 to see a report in preview mode.) You can right-click the project node in the Solution Explorer tree and select Properties; then, under the General tab you can set the TargetServerURL property where the report should be deployed. More details on Reporting Services can be found in Chapter 13.
As you might know, Microsoft SQL Server 2000 Desktop Engine (MSDE) does not include any graphical tools. The only querying tool that is bundled with MSDE is osql.exe. With SQL Server 2005, Microsoft changed the name from MSDE to SQL Server 2005 Express Edition. This free, redistributable, limited edition of the SQL Server engine includes two graphical tools: SQL Server Configuration Manager and Express Manager. Express Manager is a lightweight database management and query authoring tool that can be used to manage SQL Server 2005 Express Edition, SQL Server 2005 Developer Edition, and SQL Server 2000 MSDE instances. As shown in Figure 5.7, Express Manager shows a Management Studio Object Explorerlike window on the left side and a T-SQL Query Editor window on the right side.
Figure 5.7. SQL Server 2005 Express Edition includes graphical tools for managing SQL Server instance and services and for authoring T-SQL queries.
The Express Manager application limits you to connecting to only one SQL Server instance at a time. However, you can have multiple instances of Express Manager running, and each can connect to a different server.
The Express Manager executable (xm.exe) accepts various command-line parameters that you can use to connect to a particular instance and to open one or more files into the editor. For instance, the following command connects to a local SQL Server 2005 Express Edition named instance (-S parameter) by using a trusted connection (-E), and it opens two script files (-f):