Management and Authoring


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:

  • You no longer have to use different tools to manage SQL Server and Analysis Services, author and test queries, and manage reports. A single integrated application provides a consistent interface to manage servers and create queries across all SQL Server components.

  • All the management dialog boxes in Management Studio are non-modal, resizable, scriptable, and schedulable. Each such dialog includes the link View Connection Properties that can be used to see the server (operating system and hardware), instance (SQL Server version, language, and collation), connection, and authentication details. If you launch a dialog or wizard in Management Studio, because the dialogs are non-modal, you can still go back and do other things in Management Studio, unlike in Enterprise Manager, where dialogs are modal and you have to either close a dialog or launch a new Enterprise Manager instance. Another nice improvement to these dialogs is a progress indicator. If a dialog is performing some lengthy operation, it shows a progress indicator along with the percentage complete.

  • Because Management Studio uses SMO, it leverages all the new enhancements made in the API. This includes the cached object model, optimized instantiation, capture mode, and unique resource name (URN) reference. These features are discussed later in this chapter. Because of these SMO enhancements, Management Studio offers better user interface response and performance, facilitates doing multiple, nonblocking actions, and includes advanced scripting support. Management Studio was designed from the ground up to better support a large number of servers, databases, database objects, and records.

  • Much like Visual Studio .NET, Management Studio also offers the capability to work with solutions and projects, and it offers complete SourceSafe integration. The first time you launch Management Studio, the interface might look like Visual Studio .NET to you. But note that you do not need to learn, license, or install Visual Studio .NET in order to use Management Studio. A SQL Server Management Studio solution is a collection of one or more SQL Server Management Studio projects. A SQL Server Management Studio project is a set of connection definitions and queries that provide a convenient way to organize related queries and connection information.

  • With SQL Server 2000 Query Analyzer, you cannot edit queries unless you connect to a server. The Query Editor does not show up unless a connection is made to the server. The Query Editor inside Management Studio introduces a new feature called disconnected editing that lets you write and edit queries without connecting to any server. If you try to run a query, however, you are prompted to connect to the server.

  • With SQL Server 2000, if you needed help on any Transact-SQL (T-SQL) construct, you had to switch back and forth between Query Analyzer and Books Online. The Dynamic Help feature introduced in SQL Server 2005 displays the context-sensitive help topics as you type T-SQL in the Query Editor. Management Studio provides the option to show the help inside it when you select a help topic from the dynamic help window. This way, you don't have to leave the Query Editor to get help on a topic.

  • The new tabbed interface for queries and the ability to split the current query window to see different parts of the same query file improve the usability experience. Management Studio includes several other usability enhancements. For example, a disabled trigger shows up with a different icon than an enabled trigger in Object Explorer; the XML column value shows up as a hyperlink that you can click in order to view formatted XML in a new tab window; and word wrap and line numbering functionality are provided in Query Editor.

  • The new Template Explorer window in Management Studio provides hundreds of built-in T-SQL, Multidimensional Expressions (MDX), Data Mining Prediction (DMX), and XML for Analysis (XMLA) templates for SQL Server, Analysis Services, and SQL Mobile. You can also create your own folders and templates to standardize the query authoring throughout your development team or organization. You can press Ctrl+Alt+T or select View | Template Explorer to view the Template Explorer window. As in the Registered Servers window, the toolbar buttons on the top of the Template Explorer window allow switching the context between SQL Server, Analysis Server, and SQL Mobile. The right-click menu in Template Explorer provides options for creating a new folder, creating a template, or searching.

  • In case of any error, the error or informational dialog box now displays much more and more detailed information, and it allows you to easily copy the information to the clipboard or email the error details.

  • The Profiler integration with Management Studio allows you to capture deadlock graphs and XML showplans in Profiler and then view and analyze them inside Management Studio.

  • Activity Monitor and Log File Viewer now support filtering. The Activity Monitor also support automatic refresh.

  • Management Studio supports running queries in SQLCMD mode to enable scripting support.

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.

Registered Servers

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.

Object Explorer

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.

Tip

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.

Tip

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:

  • Security This node allows you to manage logins, server roles, and credentials.

  • Server Objects This node allows you to manage backup devices, linked servers, and server-level DDL triggers.

  • Notification Services This node allows you to manage notification services applications.

  • Replication This node allows you to configure and manage publications and subscriptions. Unlike Enterprise Manager, which shows the Replication Monitor as part of the tree, Management Studio launches a separate application (sqlmonitor.exe) when you right-click the Replication or Local Publications node and select Launch Replication Monitor.

  • Management This folder allows you to configure and view SQL Server error logs, create and manage maintenance plans, monitor server activity, and configure SMTPbased Database Mail. The Legacy folder under the Management node allows you to configure MAPI-based SQL Mail, open and migrate SQL Server 2000 DTS packages, and view the database maintenance plans history.

  • SQL Server Agent This node allows you to manage Agent jobs, alerts, operators, and proxies, and it lets you view Agent error logs.

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.

Tip

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.

Tip

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.

Tip

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.

Table 5.2. Query Editor Keyboard Shortcuts

Shortcut

Action

Ctrl+N

Opens a new query window with the current connection.

Ctrl+I

Performs an incremental search. This is a very useful feature. Press Ctrl+I and begin typing; press Esc or arrow keys to cancel searching.

Ctrl+U

Sets the focus on the available databases combo box. You can then press the up and down arrow keys or the first few letters of the database you want to make your current database.

Shift+Alt+Enter

Switches between full-screen and normal window mode.

Ctrl+Shift+U

Converts selected text to uppercase.

Ctrl+Shift+L

Converts selected text to lowercase.

Ctrl+K followed by Ctrl+C

Comments the selected text block. Uses -- (two dashes) for T-SQL, // (two forward slashes) for MDX and DMX, and <!-- & --> for XMLA and XML.

Ctrl+K followed by Ctrl+U

Uncomments the selected text block.

Ctrl+K followed by Ctrl+K

Sets or clears the bookmark. Bookmarks are useful when you want to remember certain lines in a large script file and go back to those lines quickly.

Ctrl+K followed by Ctrl+N

Views the next bookmark line.

Ctrl+K followed by Ctrl+P

Views the previous bookmark location.

Ctrl+K followed by Ctrl+L

Removes all bookmarks.

Ctrl+K followed by Ctrl+W

Views the bookmarks window.

Ctrl+D or Ctrl+T

Sends the results to a grid or text.

F5 or Ctrl+E or Alt+X

Executes the selected query or the entire text in the query window if nothing is selected.

Ctrl+F5

Parses the query.

Ctrl+R

Hides or shows the results window.


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:

1.

Press Ctrl+Alt+G or select View | Registered Servers to bring up the Registered Servers window, if it is not already visible.

2.

Make sure that the Registered Servers window is showing servers of type Database Engine. If it is not, click the first toolbar button on the Registered Servers window or select View | Registered Server Types | Database Engine.

3.

Right-click the Microsoft SQL Servers node in the tree. Select New | Server Group if you would like to create a new group; otherwise, select New | Server Registration to register a server.

4.

On the New Server Registration dialog box that appears, type the server name, including the instance name, or select a server from the combo box and provide the authentication details. Use the Connection Properties tab to set other connection properties, such as the default database, the client network protocol to use, the network packet size, the connection and execution timeout values (in seconds), and whether the connection should be encrypted. Click the Test button to verify the connection and click Save when you're done.

Viewing and Changing Server Properties

Following are the steps for viewing or changing SQL Server instance properties:

1.

Right-click the SQL Server instance in the Registered Servers window and select Connect | Object Explorer or simply double-click a registered server to connect to an instance in the Object Explorer window.

2.

Right-click the server in Object Explorer and select Properties.

3.

Note the changes in this dialog compared to the Server Properties dialog in Enterprise Manager. This dialog is non-modal (that is, you can go back to Management Studio without closing the dialog), is resizable, allows you to schedule or script the changes you make instead of applying them immediately, and contains a progress indicator to show status if applying the change takes a long time. Some other changes on this dialog include the new Permissions page, which allows you to manage the new server-level security settings, such as allowing or denying the ability to create/alter endpoints; and the new Advanced page, which can be used to view and set various sp_configure settings.

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:

1.

Connect to a SQL Server instance in Object Explorer mode.

2.

Right-click Databases and select the New Database menu item.

3.

Use the General tab to specify the database name, owner, collation, and recovery model; add or remove database files; and set file properties such as autogrowth, file size, and filegroup.

4.

Use the Filegroups page to create or remove filegroups and to set the default filegroup.

5.

Use the Options page to view and set various database options such as Auto Create Statistics and Auto Update Statistics.

6.

Click OK to create the database immediately, Schedule to create a job that will create the database later on, at a scheduled time, or Script to generate the script to create the database. If you schedule or script the task, you can click the Cancel button on the New Database dialog to avoid action being performed immediately.

Backing Up or Restoring a Database

Following are the steps for backing up an existing database:

1.

Connect to a SQL Server instance in Object Explorer mode.

2.

Expand the Databases folder in the Object Explorer tree, right-click the database you would like to back up and select Tasks | Back Up. The Backup Database dialog appears.

3.

The Backup Database dialog in Management Studio is very similar to one in the Enterprise Manager, except it now shows the database recovery model and provides option to perform a checksum before writing to the media in order to produce reliable backups. Depending on the recovery model of the database, you can perform full or differential database or backups of files/filegroups or the transaction log. Select the backup type, give a name to the backup set, select the backup destination, and set the various media, reliability, and transaction log settings on the Options page and click OK to immediately perform the backup operation, click Schedule to create a job, or click Script to generate a script to perform the backup.

Similarly, to restore a database or transaction log, you follow these steps:

1.

Right-click a particular database and select Tasks | Restore or right-click the Databases node and select Restore Database.

2.

In the Restore Database dialog that appears, select to restore replication settings or to restore to a particular point in time.

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:

1.

With the Object Explorer window open and while you are connected to a SQL Server 2005 instance, expand a database folder node, right-click Tables, and select the New Table menu item.

2.

The Table Designer appears as a tabbed window inside Management Studio. Specify the first column name as RecordID and the data type as int, uncheck Allow Nulls, and turn on the Identity property for this column, using the Column Properties tab. Right-click this column row and select the Set Primary Key menu item. Add two more columns, named PostType and MemberID, of data type int, and uncheck the Allow Nulls check box. Add two more columns, PostTitle (as nvarchar(100)) and PostText (as ntext).

3.

Press Ctrl+S or select File | Save and name the table tblPosts. The Table Designer toolbar (which you open by selecting View | Toolbars | Table Designer) provides various buttons for managing relationships, indexes and keys, check constraints, and full-text indexes. Try them out if you like.

4.

The tblPosts table should now appear in Object Explorer under the Tables node. If it does not, refresh the tree.

5.

Next, create a nonclustered index and include one non-key column in this index. To do this, expand the tblPosts table, right-click Indexes, and select the New Index menu item. Type the index name as ncIdxMember; leave the type as Nonclustered, click the Add button for the Index Key Columns group, and add the MemberID column. The Options page on the New Index dialog lets you enable or disable the index, set the fill factor, control the locking mechanism, and select an index as online or offline. Click the Include Columns page and add the PostTitle column. The Storage page lets you choose the filegroup on which the index should be created. Click OK to immediately create the index. This new index should appear under the Indexes folder.

6.

Double-click the ncIdxMember index or right-click and select Properties and note that the index properties dialog now includes a new page called Fragmentation that shows details similar to the results returned by running DBCC SHOWCONTIG or accessing the sys.dm_db_index_physical_stats dynamic management function. As a matter of fact, when this page is accessed, Management Studio runs a query against the sys.dm_db_index_physical_stats dynamic management function to obtain the index fragmentation details.

7.

Next, create a new constraint that PostType must have a value between 1 and 5. Right-click Constraints and select New Constraint. The Table Designer and Check Constraints windows appear. On the Check Constraints dialog, type the constraint name as chkPostType, type the expression as [PostType] > 0 AND [PostType] < 6, and click Close. Press Ctrl+S or select File | Save tblPosts and close the Table Designer.

8.

Refresh the Constraints node in Object Explorer, and you should see chkPostType there. If you need to modify or view this constraint, you can right-click it and select the Modify menu item.

9.

Start the Query Editor, select the database in which you created the tblPosts table, and run the following queries:

INSERT INTO tblPosts VALUES (1, 1, N'Test 1', N'Test 1'); GO INSERT INTO tblPosts VALUES (0, 1, N'Test 2', N'Test 2'); GO INSERT INTO tblPosts VALUES (6, 1, N'Test 3', N'Test 3'); GO SELECT * FROM tblPosts; GO

The first INSERT should succeed, and the next two INSERT statements should fail because they violate the chkPostType check constraint.

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:

  • To view the current server activity that includes processes and locks information, you can expand the Management folder and double-click the Activity Monitor node. The Activity Monitor shows the active processes. You can right-click a process and select Details to view the DBCC INPUTBUFFER value for that process or to kill that process. The two other views shown by the Activity Monitor dialog are Locks by Process and Locks by Object. The top-level combo box allows you to select a process or an object, and the grid shows all the locks acquired by that process or on that object, respectively. By default, Activity Monitor filters system processes. You can click the Filter toolbar button to set Show System Processes to True to view all processes.

  • To create a new login account, you can right-click Security and select New | Login or right-click Logins under Security and select New Login. On the new login dialog that appears, you select the authentication type (Windows or SQL Server) and select the default database and language. If it's a SQL Server login, you need to determine whether password policy and password expiration should be applied to this login. You use the Server Roles page to make this new login part of one or more server roles, such as dbcreator. You use the Database Access page to create a user for this login in one or more databases, assign the default schema in that database, and assign the database roles, such as db_datareader. You use the Permissions page to grant or deny server-level permissions such as Create Endpoint.

  • You can right-click any database and select Tasks | Generate Scripts to launch the Generate SQL Server Scripts Wizard, which walks you through scripting one or more objects in the selected database.

  • You can right-click any user database, select Properties, and use the Mirroring page to set up database mirroring for that database. Setting up database mirroring is discussed in Chapter 8, "Reliability and High Availability in the Database Engine."

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.

Express Manager

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):

[View full width]

"C:\Program Files\Microsoft SQL Server 2005 Express Manager\xm.exe " -S .\SQLEXPRESS -E -f c:\1.sql, c:\2.sql




Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net