Lesson 1: Administering a SQL Server Service

Estimated lesson time: 90 minutes

SQL Server 2005 offers many ways to programmatically administer your SQL server. This includes not only the SQL Server service, but Analysis Services as well. This ability can be useful if you need to automate tasks or offer users a controlled interface. This lesson will review these options and identify key functions that you might wish to allow.

Designing Server Management Objects Applications

Server Management Objects enables you to administer a SQL Server service by controlling objects in the SQL Server object model. These objects include, but are not limited to, databases, tables, and columns. You can also access objects and properties that are new to SQL Server 2005, such as HTTP endpoints and the XML schema collection. Data Definition Language (DDL) is a component of SQL, and it can be used to create and delete databases and database objects. Essentially, any task that can be performed using DDL code in a SQL query window can also be performed with SMO and a Visual Studio .NET application.

SMO enables you to administer and automate many of the tasks that would normally have to be done with SQL Server Management Studio. Some of the more common tasks you can automate include backups, restores, index maintenance, and job management. You might also want to expose a limited subset of functionality to key people in your organization. SMO enables you to create a custom interface for your SQL Server service and automate certain functionality.

The main object in an SMO application is the Server object. This represents an instance of SQL Server and is the top-level object in the SMO hierarchy. There are collection items beneath this, such as a DatabaseCollection, StoredProcedureCollection, TableCollection, and UserDefinedFunctionCollection. There are several objects that reside within the hierarchy, but they will only be used if you need to perform a particular function.

SMO Assemblies and Namespaces

To begin programming with SMO, you need to set a reference to some of the key SMO assemblies. These assemblies are installed with the Client Tools option in the SQL Server 2005 installation program, as shown in Figure 4-1.

image from book
Figure 4-1: Add Reference to the Microsoft.SqlServer.Smo assembly, which is used in an SMO application

The following represent common assemblies you will need to reference in your Visual Studio 2005 application:

Microsoft.SqlServer.Smo Contains the core classes for database engine objects and is the main assembly used when building an SMO application
Microsoft.SqlServer.SmoEnum Contains support for the SMO classes and many of the enumeration classes
Microsoft.SqlServer.SqlEnum Also contains enumerations used by your SMO application
Microsoft.SqlServer.ConnectionInfo Contains classes used to make a connection with your SQL Server instance

Depending on what task you need to accomplish, you might need to add references to more assemblies. For example, if you need to perform a task that involves the Service Broker, you will need to set a reference to the Microsoft.SqlServer.ServiceBrokerEnum assembly. The following are the remaining assemblies that you might need to reference:

Microsoft.SqlServer.ServiceBrokerEnum Contains support for programming against Service Broker, which is a message-based communication platform that enables features such as asynchronous programming.
Microsoft.SqlServer.WmiEnum Contains support for the Windows Management Instrumentation (WMI) provider, which is used to perform scripting tasks.
Microsoft.SqlServer.RegSvrEnum Contains support for working with registered servers. It is used as support for the SMO infrastructure and contains only two classes.

Each of the assemblies mentioned above support different namespaces that are needed for your SMO application. Table 4-1 lists the different SMO namespaces available.

Table 4-1: Server Management Object (SMO) Namespaces
Open table as spreadsheet
Option	Description
Microsoft.SqlServer.Management.Common	Contains classes used to connect to SQL Server, run Transact-SQL statements, and manage transactions
Microsoft.SqlServer.Management.Nmo	Contains classes used when developing applications involving notification services
Microsoft.SqlServer.Management.Smo	The core namespace for the SMO library and used to represent all the core SMO objects
Microsoft.SqlServer.Management.Smo.Agent	Contains classes used to write applications that deal with the Microsoft SQL Server Agent
Microsoft.SqlServer.Management.Smo.Broker	Contains classes used when writing applications that deal with SQL Server Service Broker
Microsoft.SqlServer.Management.Smo.Mail	Contains classes used when writing applications that utilize the SQL Server Database Mail service
Microsoft.SqlServer.Management.Smo.RegisteredServers	Contains classes used when dealing with the Registered Servers service
Microsoft.SqlServer.Management.Smo.RegSvrEnum	Contains classes used by the SMO infrastructure to manage the Registered Servers service
Microsoft.SqlServer.Management.Smo.Wmi	Contains classes used to provide programmatic access to Windows Management Instrumentation (WMI)
Microsoft.SqlServer.Management.Trace	Contains the classes used to read trace data from files, tables, or rowsets

Exam Tip

For this exam, you should focus primarily on designing Server Management Objects applications. Even though other libraries, such as Replication Management Objects and Analysis Management Objects are important, they are not focused on as heavily as applications created with SMO.

Connecting to a Server

Once you have added references to the core SMO assemblies, you will need to make a connection to your SQL Server instance. You do so by first adding a reference to Microsoft.SqlServer.Management.Smo at the top of your code file, as in the following example.

 //C# using Microsoft.SqlServer.Management.Smo; 'VB Imports Microsoft.SqlServer.Management.Smo

The next step is to make a connection to the SQL Server instance. If you are doing this using Windows authentication, then the following code could be used to connect to a SQL Server instance named ".\SQL2005STD" and display properties about that server in a message box:

 //C# Server svr = new Server(@".\SQL2005STD"); MessageBox.Show("Name:" + svr.Name      + "; InstanceName:" + svr.InstanceName); svr = null; 'VB Dim svr As New Server(".\SQL2005STD") MessageBox.Show("Name:" + svr.Name _      + "; InstanceName:" + svr.InstanceName) svr = Nothing

If you had to connect to a server using logon credentials, then you would need to use a ServerConnection object to specify the logon credentials. To do this, you would need to add a reference to the Microsoft.SqlServer.ConnectionInfo assembly and also include a directive to the Microsoft.SqlServer.Management.Common namespace. You could then use the following code to make the connection:

 //C# ServerConnection svrConn = new ServerConnection(); svrConn.LoginSecure = false; svrConn.Login = "username"; svrConn.Password = "password"; svrConn.ServerInstance = @".\SQL2005STD"; Server svr = new Server(svrConn); MessageBox.Show("Name:" + svr.Name     + "; InstanceName:" + svr.InstanceName); svrConn = null; svr = null; 'VB Dim svrConn As New ServerConnection svrConn.LoginSecure = False svrConn.Login = "username" svrConn.Password = "password" svrConn.ServerInstance = ".\SQL2005STD" Dim svr As New Server(svrConn) MessageBox.Show("Name:" + svr.Name + _    "; InstanceName:" + svr.InstanceName) svrConn = Nothing svr = Nothing

After the connection has been established, you can perform various tasks, such as backing up the database, restoring the database, performing integrity checks, and other administrative tasks. You can also perform more complex tasks, such as creating and scheduling SQL Server Agent jobs. In the lab for this lesson, we will walk through how to create a new database using an SMO application.

Important

Additional sample code

SQL Server 2005 includes sample code as part of the Documentation and Tutorials. Several samples provided involve creating SMO applications. These samples are not installed by default, but you can install them later by executing Setup.exe on the first SQL Server 2005 CD. To learn how to do this, refer to http://msdn2.microsoft.com/en-us/library/ms160898.aspx. Once installed, you can locate the SMO samples in the \Microsoft SQL Server\90\Samples\Engine\Programmability\SMO directory.

Designing Replication Management Objects Applications

Replication Management Objects enables you to programmatically configure your replication topology. Replication is the process of copying data and database objects from one database to another. You can also synchronize databases so that changes made to one database are reflected in the other database. This can be very useful for creating and maintaining a remote copy of your data. The primary database might be used for online transaction processing, and the remote copy can be used for reporting or some other use.

RMO objects can be used to create and configure publications, articles, and subscriptions. Normally, you would use Microsoft SQL Server Management Studio to accomplish these tasks, but with RMO objects, you can do it programmatically. Before RMO was available, replication was configured programmatically using SQL-DMO.

There are many steps that can be performed to configure your replication topology. Which steps you choose depends on the type of replication you are trying to configure. In all cases, you will need to configure replication, create an initial snapshot of the data, and then set up a way to synchronize and propagate data in the future.

The main objects involved with a replication are the Publisher and the Distributor. Once these are configured, you can create publications that specify the type of replication used and what data will be replicated. You can then create subscriptions that will either push or pull the data in order to keep the data up-to-date.

This section will not go into all the details associated with replication, because that is a huge topic in itself. For more information about the replication process, refer to the section titled "SQL Server Replication" in SQL Server Books Online at http://msdn2.microsoft.com/en-us/library/ms151198.aspx.

RMO Assemblies and Namespaces

Replication is installed using the SQL Server 2005 Installation Wizard. To use RMO, you will need to set references to key RMO assemblies. The two assemblies that provide replication functionality include the following:

Microsoft.SqlServer.Rmo.dll This is the main assembly for RMO; it encapsulates the functionality used to configure replication. When you add this reference in Visual Studio 2005, the component will be named Microsoft.SqlServer.Replication.NET Programming Interface, but it should still be pointing to the Microsoft.SqlServer.Rmo.dll file.
Microsoft.SqlServer.Replication.dll This assembly enables you to synchronize subscriptions by controlling the replication agents. When you add this reference in Visual Studio 2005, the component will be named Replication Agent Library, but it should still be pointing to the Microsoft.SqlServer.Replication.dll file.

Important

Namespace used in both RMO assemblies

What can get a little confusing when working with the RMO objects is that the namespace used for replication is Microsoft.SqlServer.Replication. This is the case for classes that reside within the Microsoft.SqlServer.Rmo assembly as well as the Microsoft.SqlServer.Replication assembly.

The RMO class library, which is represented by the Microsoft.SqlServer.Rmo assembly, contains dozens of classes that can be used to perform some of the following tasks:

Specify the replication server, which can represent the distributor, publisher, subscriber, or all three.
Create and edit profiles for certain agents that are used when an agent job is created.
Specify a distributor and distribution database.
Specify a publisher and publication database.
Create and define articles, and then add or remove columns from them.
Create a subscription for transactional or merge replication.
Define a pull or push subscription.
Implement special business logic that handles events during the merge replication process.
Set a schedule for a snapshot to be referenced.
Monitor the replication process and get information about jobs.
Specify an alternate publisher when creating a merge publication.

The Replication class library that is represented by the Microsoft.SqlServer.Replication assembly contains several classes that can be used to accomplish some of the following tasks:

Create a snapshot agent that can be used to create the initial snapshot and generate a partitioned snapshot for a subscription.
Create a merge synchronization agent that can be used to synchronize subscriptions and validate that the subscription has the correct data.
For merge synchronizations, specify whether the upload, download, or both phases are performed.
Specify an alternate snapshot folder for the subscription.
Synchronize subscriptions to transactional or snapshot publications.
Process exceptions specific to a certain replication task.
Retrieve status information from the replication agent during synchronization.

Specifying a Publisher and a Distributor

Even though you might need to set references to both RMO assemblies, you will only reference one namespace in your code, Microsoft.SqlServer.Replication. You will need to establish a connection with SQL Server, so you will also add a reference to the Microsoft.SqlServer.ConnectionInfo assembly and add a directive to the Microsoft.SqlServer.Management.Common namespace in your code. The code reference would look like the following:

 //C# using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Replication;\ 'VB Imports Microsoft.SqlServer.Management.Common Imports Microsoft.SqlServer.Replication

Regardless of the type of synchronization that is performed, you will need to specify a publisher and a distributor. It is possible that the publisher, distributor, and subscriber can exist on the same machine. The following code is used to specify the distributor, distribution database, publisher, and publication database:

 //C# ReplicationServer rSvr; ReplicationDatabase rDb; DistributionDatabase dDb; DistributionPublisher dPub; //Connect to the server ServerConnection conn = new ServerConnection(@".\SQL2005STD"); conn.Connect; //Specify the Distribution Database dDb = new DistributionDatabase("DistributionDatabaseName", conn); dDb.MaxDistributionRetention = 12; dDb.HistoryRetention = 24; //Specify the Distributor and create the //Distribution database rSvr = new ReplicationServer(conn); rSvr.InstallDistributor((string)null, dDb); //Specify the Publisher, which in this case is the //same as the distributor dPub = new DistributionPublisher(@".\SQL2005STD", conn); dPub.DistributionDatabase = dDb.Name; dPub.WorkingDirectory = "\\\\" + "SQL2005STD" + "\\repldata"; dPub.PublisherSecurity.WindowsAuthentication = true; dPub.Create(); //Specify the Publication Database rDb = new ReplicationDatabase("PublicationDatabaseName", conn); rDb.EnabledTransPublishing = true; rDb.EnabledMergePublishing = true; //Disconnect conn.Disconnect; 'VB Dim rSvr As ReplicationServer Dim rDB As ReplicationDatabase Dim dDB As DistributionDatabase Dim dPub As DistributionPublisher 'Connect to the server Dim conn As New ServerConnection(".\SQL2005STD") conn.Connect() 'Specify the Distribution Database dDB = New DistributionDatabase("DistributionDatabaseName", conn) dDB.MaxDistributionRetention = 12 dDB.HistoryRetention = 24 'Specify the Distributor and create the 'Distribution database rSvr = New ReplicationServer(conn) rSvr.InstallDistributor(CType(Nothing, String), dDB) 'Specify the Publisher, which in this case is the 'same as the distributor dPub = New DistributionPublisher(".\SQL2005STD", conn) dPub.DistributionDatabase = dDB.Name dPub.WorkingDirectory = "\\\\" + "SQL2005STD" + "\\repldata" dPub.PublisherSecurity.WindowsAuthentication = True dPub.Create() 'Specify the Publication Database rDB = New ReplicationDatabase("PublicationDatabaseName", conn) rDB.EnabledTransPublishing = True rDB.EnabledMergePublishing = True 'Disconnect conn.Disconnect()

In the previous example, the publication and distribution database existed on the same machine. We also specified that the publication database would allow for both transactional and merge publishing. The next step from here would depend on what type of replication we wanted to perform. It would also depend on the type of subscription and whether it was a pull or push subscription. Microsoft Developer Network (MSDN) contains some useful how-to sections on the following topics:

How To: Create a Publication (RMO Programming)

http://msdn2.microsoft.com/fr-fr/library/ms146941.aspx
How To: Creating, Modifying, and Deleting Subscriptions (RMO Programming)

http://msdn2.microsoft.com/fr-fr/library/ms147918.aspx
How To: Synchronize a Pull Subscription (RMO Programming)

http://msdn2.microsoft.com/en-us/library/ms147890.aspx
How To: Define an Article (RMO Programming)

http://msdn2.microsoft.com/fr-fr/library/ms146883.aspx
How to: Create a Push Subscription (RMO Programming)

http://msdn2.microsoft.com/en-US/library/ms146863.aspx
How to: Synchronize a Push Subscription (RMO Programming)

http://msdn2.microsoft.com/en-us/library/ms146910.aspx

Best Practices

Secure your replication topology

To prevent unauthorized access of your data, you should always encrypt data that is replicated to unsecured domains or across the Internet. This can be done using one of several methods, including Secure Sockets Layer (SSL), virtual private networks (VPNs), or IP Security (IPSec).

Designing Analysis Management Objects Applications

If you have wisely ventured into using SQL Server Analysis Services, you will be pleased to know that SQL Server 2005 offers a way to programmatically control your SSAS objects. Analysis Management Objects enables you to create, manage, and process cubes and dimensions. You can also control data-mining objects by creating and processing mining models. All of the functionality you would normally need SQL Server Management Studio to perform can be done programmatically using AMO.

Prior to AMO, programmatic communication with Analysis Services was done using XML for Analysis (XMLA). With AMO, XMLA is still being used, but AMO represents a layer between the XMLA message and the application. Just like with SMO and RMO, the classes are represented in a hierarchical structure. The main objects are the Cube, Dimension, and MiningStructure. Which of these objects is utilized depends on the type of operation that needs to be performed. If you are performing Online Analytical Processing (OLAP), which is a process where large quantities of raw data are stored in a multidimensional format, then you will focus on cubes and dimensions. Cubes are multidimensional structures built from one or more tables in a relational database. Dimensions are the attributes used to describe the data within a cube. If you are performing data-mining operations, then you will focus on the mining structure and mining model objects. A mining structure defines what data will be included in a mining model, which is the end result that will be processed and used to provide predictions about the data.

SSAS is a huge and complex topic. This book does not go into great detail regarding using SSAS, but if you are interested in learning more about SSAS, refer to the section titled "SQL Server Analysis Services (SSAS)" in SQL Server Books Online at http://msdn2.microsoft.com/en-us/library/ms175609.aspx. If you are specifically interested in learning more about data mining, refer to "Data Mining with SQL Server 2005" by ZhaoHui Tang and Jamie MacLennan (Wiley, 2005). This well-written and thorough book guides you through everything you need to know about getting the most out of data mining with SQL Server 2005.

AMO Assemblies and Namespaces

Analysis Services is installed using the SQL Server 2005 Installation Wizard. The main AMO library uses only one assembly, Microsoft.AnalysisServices, and one namespace, Microsoft.AnalysisServices. The AMO class library provides well over 100 classes that can be used to administer all aspects of your SSAS. To reduce confusion, the classes can be divided into several categories.

AMO Fundamental Objects

Regardless of whether you are performing OLAP or data mining, you will need to utilize the Server, Database, DataSource, and DataSourceView objects. A Database represents all the data objects that can be used in your application. This can represent cubes, dimensions, and mining structures, and you can have more than one database in the databases collection. The DataSourceView object is built from the DataSource object; it can contain data from multiple DataSource objects or partial data from one DataSource object. The DataSourceView will be used as the data source for your OLAP cube, dimension, or mining model.

AMO OLAP Classes

These classes enable you to create, edit, and delete the objects for OLAP processing. Some of the key objects from this class can include the following:

Dimension Contains information used to represent a collection of data from one or more cubes.
Cube Represents a multidimensional database that can contain different dimensions and measures.
MeasureGroup A Measure is an expression used to evaluate some piece of data, and a MeasureGroup contains one or more related measure expressions.
Action This is a stored Multidimensional Expression (MDX) that can affect how a cube or dimension is processed.
KPI KPI is a Key Performance Indicator; it represents a collection of calculations that are used to evaluate data and determine if it meets a predetermined goal.
Perspective Can be used to controls what data from a cube the user sees.
ProactiveCaching Enables you to get the performance benefits of an OLAP data source, while still getting some of the real-time benefits from a relational data source.

AMO Data Mining Classes

These classes enable you to work with data-mining objects to create mining structures and mining models and then process the results. Without AMO, your only option for working with data mining is to use SQL Server Business Intelligence Development Studio. Data mining enables you to make predictions about the data that has been processed. This can be useful for finding patterns among a large set of data.

The data-mining structure identifies the data and columns that will be processed. Each column to be processed will be identified as Key, Input, Predictable, or InputPredictable. When defining the column, you will use one of two subclasses to identify the column structure:

ScalarMiningStructureColumn Columns that hold single values. For example, a column containing someone's age would only have one value.
TableMiningStructureColumn Columns that hold multiple values and actually embed a table inside the column. For example, a purchase detail column could contain multiple records for each item that was purchased.

The data itself will be assigned from a DataSourceView, which was one of the AMO fundamental objects. The data can come from either an OLAP data source or a relational data source.

One or more algorithms will be assigned to this structure. The algorithm is the piece that tells Analysis Services how to interpret the data that it processes. Once the algorithm has been applied to data from the mining structure, it becomes the mining model. You can then process the mining model as many times as necessary with the Process method.

Best Practices

Use multiple algorithms

It is a good idea to assign multiple algorithms to your mining structure. This will enable you to compare the results from processing each algorithm and identify the algorithm that delivers the optimal results.

AMO Security Classes

Security for all SSAS objects is handled with roles and permissions. Multiple users are assigned to a single role. One thing that can get confusing is that the terms users and members mean essentially the same thing. Each role object will be assigned to multiple users or members. It is this role object that determines what the user can do with the other SSAS objects.

There are several different permission objects, and one or more of these can be assigned to a role. Permissions can be assigned to a Database, DataSource, Dimension, Cube, MiningStructure, and MiningModel. The action associated with the permission determines exactly what the user can do with that object. The following is a list of possible actions:

Read Specifies whether the user can read the data or content of the object. This permission can be overridden by other permissions depending on the object. It can be set with a value of None or Allowed.
ReadDefinition Specifies whether the user can read the data definition for the object. This is different than the Read permission, which is particular to content; this is particular to structure or definition. The permission can be set with a value of None, Basic, or Allowed.
Write Specifies whether the user has write access to the data or content of the object. This permission can be set with a value of None or Allowed.
Process A Boolean value determines whether the user can process that object and any objects it contains.
Administer This permission only applies to the Database object, and it is a Boolean value that determines whether the user can administer the database.

AMO Administrative Classes

A handful of classes are provided to help administer SSAS. These classes enable you to perform the following functions:

Back up and restore the SSAS database
Monitor SSAS using Traces
Add functionality by including external assemblies
Script out objects and operations as XMLA
Exception handling

Querying Data with ADOMD.NET

Having data reside in a cube, dimension, or mining model does you little good unless you can query the data inside these objects. ADOMD, which is Active Data Objects for Multidimensional objects, is a .NET Framework data provider that works specifically with SSAS.

ADOMD.NET is divided into two categories: client and server programming. Which one you use depends on where you want the processing to occur. When using this provider, you will need to add references to one of the following assemblies to your project:

Microsoft.AnalysisServices.AdomdClient.dll
Microsoft.AnalysisServices.AdomdServer.dll

You will then need to add one of the following directives to your code:

 //C# using Microsoft.AnalysisServices.AdomdClient; using Microsoft.AnalysisServices.AdomdServer; 'VB Imports Microsoft.AnalysisServices.AdomdClient Imports Microsoft.AnalysisServices.AdomdServer

To query data from a multidimensional source, you first need to make a connection to the server. If you are creating an application for the client, you can use the AdomdConnection object with the Microsoft.AnalysisServcices.AdomdClient namespace to accomplish this. The following example could be used to make a connection to an OLAP database named "TestASDatabase":

 //C# //Make a connection to a fictional database named "TestASDatabase" //for a user with the ID of "username" and a password of "password" AdomdConnection conn = new AdomdConnection(); conn.ConnectionString = @"Data Source=.\SQL2005STD;" +      @"Initial Catalog=TestASDatabase;" +      @"UID=username;" +      @"PWD=password;"; conn.Open(); 'VB 'Make a connection to a fictional database named "TestASDatabase" 'for a user with the ID of "username" and a password of "password" Dim conn As New AdomdConnection() conn.ConnectionString = "Data Source=.\SQL2005STD;" + _       "Initial Catalog=TestASDatabase;" + _       "UID=username;" + _       "PWD=password;" conn.Open()

After the connection has been established, you can query data from the source and return it to one of the following objects:

Cellset The Cellset provides a multidimensional view of the results and enables you to drill down into the data. Of course, this method is the most resource intensive, so it should only be used when necessary.
DataSet This is the same DataSet object that is used with ADO. The difference is that you will use a AdomdDataAdapter to populate the DataSet. Use this object whenever there is a need for results to be stored in a DataSet object.
AdomdDataReader Similar to the DataReader in ADO, this is a forward-only view of the query results. Care should be taken to close these objects so they do not consume unnecessary resources.
XmlReader The XmlReader retrieves the results as XML; it is the object that requires the least amount of resources.

Creating a DataSource and DataSourceView

The DataSource and DataSourceView are key objects that will be used in all SSAS applications. To create these objects, you will need to include a reference to the Microsoft.AnalysisServices namespace, because this will be used to get our Server and Database objects. The following example can be used to create a DataSource and DataSourceView from the AdventureWorks database on the instance named ".\SQL2005STD". In this case, the DataSourceView will be based on data coming from a single table. Typically, data might come from multiple related tables and utilize named queries.

 //C# //Create the server object and connect using the DataSource keyword Server svr = new Server(); Svr.Connect("DataSource=./SQL2005STD;"); svr.CaptureXml = false; //Specify the SSAS database Database db = svr.Databases.GetByName("TestASDatabase"); //Create the data source which will be named the same as the database RelationalDataSource rDs = new RelationalDataSource("AdventureWorks",          "AdventureWorks"); //Specify the connection string to the relational DB rDs.ConnectionString = "Provider=SQLOLEDB; " +     @"Data Source=.\SQL2005STD;" +     @"Initial Catalog=AdventureWorks;" +     @"Integrated Security=SSPI;"; //Add the new data source to the SSAS DB db.DataSources.Add(rDs); //Create a new DataSourceView //We will name the view the same as the DB and the data source DataSourceView dsV = new DataSourceView("AdventureWorksDV",  "AdventureWorksDV"); //Create a dataset to populate the view OleDbDataAdapter da = new OleDbDataAdapter("", rDs.ConnectionString); DataSet ds = new DataSet(); da.SelectCommand.CommandText = "SELECT * FROM Person.Contact"; da.FillSchema(ds, SchemaType.Mapped, "Person.Contact"); db.DataSourceViews.Add(dsV.ID); db.DataSourceViews["AdventureWorksDV"].DataSourceID = rDs.ID; db.DataSourceViews["AdventureWorksDV"].Schema = ds; //Update the database db.Update(UpdateOptions.ExpandFull); 'VB 'Create the server object and connect using the DataSource keyword Dim svr As New Server() svr.Connect("Data Source=.\SQL2005STD") svr.CaptureXml = False 'Specify the SSAS database Dim db As Database db = svr.Databases.GetByName("TestASDatabase") 'Create the data source which will be named the same as the database Dim rDs As New RelationalDataSource("AdventureWorks", "AdventureWorks") 'Specify the connection string to the relational DB rDs.ConnectionString = "Provider=SQLOLEDB; " + _     "Data Source=.\SQL2005STD;" + _     "Initial Catalog=AdventureWorks;" + _     "Integrated Security=SSPI;" 'Create a new DataSourceView 'We will name the view the same as the DB and the data source Dim dsV = New DataSourceView("AdventureWorksDV", "AdventureWorksDV") 'Create a dataset to populate the view Dim da As New OleDbDataAdapter("", rDs.ConnectionString) Dim ds As New DataSet() da.SelectCommand.CommandText = "SELECT * FROM Person.Contact" da.FillSchema(ds, SchemaType.Mapped, "Person.Contact") db.DataSourceViews.Add(dsV.ID) db.DataSourceViews("AdventureWorksDV").DataSourceID = rDs.ID db.DataSourceViews("AdventureWorksDV").Schema = ds 'Add the new data source to the SSAS DB db.DataSources.Add(rDs) 'Update the database db.Update(UpdateOptions.ExpandFull)

Lab: Administrative Programming

In Lab 1, you will perform basic administrative tasks using some of the tools mentioned in this lesson. In Exercise 1, you will create a simple Windows-based application that can be used to create a new database on your SQL Server 2005. Exercise 2 will walk you through creating a data-mining structure using SQL Server Business Intelligence Studio.

Exercise 1: Create a Database by Using an SMO Application

In this exercise, you create a simple Windows application that uses SMO to create a new database on your SQL Server. The application will ask you to supply the SQL Server instance name and the name of the new database, and then it will create the database and display database property information in a list box.

Open Microsoft Visual Studio 2005.
Click File, New, and Project.
In the New Project dialog box, expand the Other Project Types node, and select Visual Studio Solutions. Type TK442Chapter4 as the name of your blank solution, and place it in a directory of your choosing. A new solution file will be created, and you can add multiple projects to this solution. You will add one project for each lab in included in this chapter.
Select File, Add, and New Web Site. Select Windows Application as the template, and type Lab1 as the project name. Set the language by selecting Visual Basic or Visual C# from the language drop-down list box. By default, Visual Studio will select the language specified when it was first configured.
Select File, Add Reference. From the .NET tab, select the following assemblies:
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoEnum
- Microsoft.SqlServer.SqlEnum
- Microsoft.SqlServer.ConnectionInfo
From the Toolbox, drag two label controls onto the Default design surface. Use the following property values for these controls:
```
 Control 1: Name = lblInstance Text = "Server Instance:" Control 2: Name = lblDatabase Text = "Database Name:" 
```
From the Toolbox, drag two textbox controls onto the Default design surface. Use the following property values for these controls:
```
 Control 1: Name = txtInstance Control 2: Name = txtDatabase 
```
From the Toolbox, drag one button control onto the Default design surface. Use the following property value for this control:
```
 Name = btnCreate Text = "Create a database" 
```
The resulting design window should look similar to Figure 4-2.

Right-click the Form1 file from Solution Explorer, and select View Code. At the top of the code file, add the following directives:

 //C# using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; 'VB Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Common

Within the same code window, add the following code beneath the Form_Load method:

 //C# private void btnCreate_Click(object sender, EventArgs e) {     String strInstance;     String strDatabase;     Server svr;     Database db;     FileGroup fg;     DataFile df;     LogFile lf;     try     {         //Get the Instance name and Database name from the textbox         if (txtInstance.Text.Length > 0 && txtDatabase.Text.Length > 0)         {            strInstance = txtInstance.Text.Trim();            strDatabase = txtDatabase.Text.Trim();            //Change the cursor to a wait cursor            this.Cursor = Cursors.WaitCursor;            //Connect to the server            svr = new Server(strInstance);            //Get a list of the current databases on the server            //and make sure it does not already exist            if (svr.Databases.Contains(strDatabase))            {                MessageBox.Show("This database already exists. Please" +                   " select a different name and try again");                return;            }            //Create a new database object            db = new Database(svr, strDatabase);            //Create a new file group named PRIMARY            //You can add one or more data files to this            //group. In this example, we are only adding one            fg = new FileGroup(db, @"PRIMARY");            //Create a new data file and name it based on the master database            //for the server. Then set properties for the data file            df = new DataFile(fg, strDatabase + @"_Data",                svr.Information.MasterDBPath + @"\"                + strDatabase + @"_Data" + @".mdf");            df.GrowthType = FileGrowthType.KB;            df.Growth = 1024;  // In KB            //Add the file to file group            fg.Files.Add(df);            //Add the filegroup to the database            db.FileGroups.Add(fg);            //Create the transaction log.            lf = new LogFile(db, strDatabase + @"_Log",               svr.Information.MasterDBPath + @"\" + strDatabase               + @"_Log" + @".ldf");            lf.GrowthType = FileGrowthType.KB;            lf.Growth = 1024; // In KB            //Add the logfile to the db            db.LogFiles.Add(lf);            //Create the DB            db.Create();            //Create a message that shows            //the database was created successfully            MessageBox.Show("A database named, " + db.Name +                 " was created on " + db.CreateDate +                 " with a size of " + db.Size + "MB" +                 " and " + db.SpaceAvailable + " MB of avaialble space." +                 " It currently has a status of " + db.Status);         }         else         {            MessageBox.Show("Please enter SQL Server instance name" +                  " and new database name before continuing");         }      }      catch (Exception ex)      {          MessageBox.Show("The following error was encountered: " +             ex.Message);      }      finally      {          //Release resources          svr = null;          db = null;          fg = null;          df = null;          //Set the cursor back to the default          this.Cursor = Cursors.Default;      }   } 'VB Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click     Dim strInstance As String     Dim strDatabase As String     Dim svr As Server     Dim db As Database     Dim fg As FileGroup     Dim df As DataFile     Dim lf As LogFile     Try         'Get the Instance name and Database name from the textbox         If txtInstance.Text.Length > 0 And txtDatabase.Text.Length > 0 Then             strInstance = txtInstance.Text.Trim             strDatabase = txtDatabase.Text.Trim            'Change the cursor to a wait cursor             Me.Cursor = Cursors.WaitCursor             'Connect to the server             svr = New Server(strInstance)             'Get a list of the current databases on the server             'and make sure it does not already exist             If svr.Databases.Contains(strDatabase) Then                 MessageBox.Show("This database already exists. Please" + _                         " select a different name and try again")                 Return             End If             'Create a new database object             db = New Database(svr, strDatabase)             'Create a new file group named PRIMARY             'You can add one or more data files to this             'group. In this example, we are only adding one             fg = New FileGroup(db, "PRIMARY")             'Create a new data file and name it based on the master database             'for the server. Then set properties for the data file             df = New DataFile(fg, strDatabase + "_Data", _                svr.Information.MasterDBPath + "\" _                + strDatabase + "_Data" + ".mdf")             df.GrowthType = FileGrowthType.KB             df.Growth = 1024 'In KB             'Add the file to file group             fg.Files.Add(df)             'Add the filegroup to the database             db.FileGroups.Add(fg)             'Create the transaction log.             lf = New LogFile(db, strDatabase + "_Log", _                 svr.Information.MasterDBPath + "\" + strDatabase _                 + "_Log" + ".ldf")             lf.GrowthType = FileGrowthType.KB             lf.Growth = 1024 'In KB             'Add the logfile to the db             db.LogFiles.Add(lf)             'Create the DB             db.Create()             'Create a message that shows             'the database was created successfully             MessageBox.Show("A database named, " + db.Name.ToString + _               " was created on " + db.CreateDate.ToString + _               " with a size of " + db.Size.ToString + "MB" + _               " and " + db.SpaceAvailable.ToString + _   " MB of available space." + _               " It currently has a status of " + db.Status.ToString)         Else             MessageBox.Show("Please enter SQL Server instance name" + _                 " and new database name before continuing")         End If     Catch ex As Exception         MessageBox.Show("The following error was encountered: " + _                 ex.Message)     Finally         'Release resources         svr = Nothing         db = Nothing         fg = Nothing         df = Nothing         'Set the cursor back to the default         Me.Cursor = Cursors.Default     End Try End Sub

Click File, and then click Save All.
Click Build, and then click Build All, and ensure that the build succeeded.
Press Ctrl+F5 to start the project without debugging. When the form appears, enter the server instance name and the name of a database (servername\instance) that does not already exist on your SQL Server. Click Create a database. The cursor should change to an hourglass, and within a few seconds, a message box should appear that informs you that the creation was successful.
Open Microsoft SQL Server Management Studio.
Connect to the instance of SQL Server that you specified when you created the database. Expand the Databases node and look for the new database you created earlier. You can right-click the name of the database and select Properties. If you go to the Files page, you will see the data file and log file that were created in the code. (See Figure 4-3.)

image from book
Figure 4-2: Creating a new database

image from book
Figure 4-3: Files page for the properties of the newly created test database

Exercise 2: Create a Data-Mining Structure

In this exercise, you create a data-mining structure using SQL Server Business Intelligence Development Studio. You will walk through creating a DataSource and DataSourceView that uses data from the AdventureWorks database.

Open SQL Server Business Intelligence Development Studio. The application will open to the Start Page and will look just like the Start Page for Visual Studio 2005.
Click File, New Project.
Select Business Intelligence Projects as the Project type and Analysis Services Project as the template.
Type TK442Chapter4Lab1Exercise2 as the project name, and choose a location on your local machine. Click OK to add the project, and create a new solution file. Note that this solution file will be separate from the one created in Exercise 1.
Right-click the Data Sources folder from Solution Explorer, and click New Data Source. This will start the Data Source Wizard. In the Welcome dialog box, click Next.
Click New to create a new data source. From the Connection Manager dialog box (see Figure 4-4), enter the SQL Server name in which the AdventureWorks database is installed. Select the type of authentication to use, and select the AdventureWorks database from the database drop-down list box. Click OK to return to the Data Source Wizard.
Click Finish twice to complete the wizard.
Right-click the Data Source Views folder from Solution Explorer, and click New Data Source View. This will start the New Data Source View Wizard. In the Welcome dialog box, click Next.
In the Select a Data Source dialog box, click Next to select the AdventureWorks relational data source.
Select the Person.Contact and Person.Address tables from the Available Objects list, and click the > arrow to move the tables to the list of Included Objects. (See Figure 4-5.) Click Next to continue.
Click Finish to complete the wizard.
On the Build menu, click Deploy to deploy your project to the localhost.

image from book
Figure 4-4: Connection Manager dialog box used when creating a new data source for an Analysis Services project

image from book
Figure 4-5: Select Table and Views dialog box, used when creating a new data source view for an Analysis Services project

The completed lab is available in the \Labs\Chapter 04 folder on the companion CD.

Important

Lab requirements

You will need to have SQL Server 2005 installed before you can complete this lab. You will also need to have administrative abilities for this instance of SQL Server. Refer to the Introduction for setup instructions.