|
|
Chapter 2: SQL Server Management Studio
Thursday, July 29, 2004
|
|
|
Written by an experienced and well-respected author team, this book will help you quickly learn the new features of SQL Server 2005. With
plenty of Spatial examples, you'll be able to architect your own applications and exploit the newly included features. Case studies focus
on advanced Spatial applications in healthcare, telecom, retail, and distribution industries.
But the book's greater aspiration is offer a deeper look at the database internals and features—not just the programming features.
Sticking to this goal, the authors discuss changes to T-SQL, analytic functions, stored procedures, tables, indexes,
transactions, and performance tools. Because it is based on extensive feedback from training courses, discussion lists, and customers, this
book will offer best practice approaches to the most common problems with which you now struggle.
ISBN: 1-59059-385-5
Copyright © 2004 by APress. All rights reserved.
www.APress.com
More Book Details
By Tony Bain, Robin Dewson
With SQL Server 2005, it is a brave new world for DBAs when it comes to server
management. One of the design goals for SQL Server 2005 was to change the way that
DBAs interact with and manage the database server. In SQL Server 2000, you had
scenarios where you would have to open four different tools to get your work done:
Enterprise Manager, Query Analyzer, Profiler, and Analysis Manager. Nevermind that
the addition of new technologies such as Reporting Services and SQL Server 2005
Mobile Edition (the renamed SQL Server CE Edition) present their own management
tools separate from the ones already pointed out. While each tool was best of breed for
the tasks that you wanted to perform, switching between tools and remembering different
user interfaces, command syntax, and just the overall headache of having separate tools
lessened users’ experiences with SQL Server. After hearing this feedback and realizing
that something had to be done, SQL Server Management Studio was born.
SQL Server Management Studio (often referred to as simply Management Studio)
combines together all the tools that you previously used separately, and adds additional
capabilities. Furthermore, Management Studio is a completely rewritten application that
uses the latest in Microsoft technologies, namely Winforms and the .NET Framework.
No longer do you have to deal with the idiosyncrasies of the Microsoft Management
Console, such as modal dialog boxes, for most of your administrative tasks, upon which
Enterprise Manager was built. In this chapter, we’ll go through some of the new
enhancements in Management Studio, including the following:
- Connecting to and managing multiple components of SQL Server from
the unified Management Studio console
- New user interface enhancements including asynchronous treeview and
filtering capabilities, nonmodal dialog boxes, dialog boxes that can
output scripts of their actions, robust code authoring capabilities, and
summary views
- New capabilities such as the dedicated admin connection, deadlock
visualization, Performance Monitor correlation, Maintenance Plan
Designer, SQLiMail, dynamic management views, and enhanced help
- New command-line tools, such as SQLCMD
- New API, called Server Management Objects (SMO), which replaces
DMO
Connecting to and Managing Your SQL Servers
The very first thing you will notice is that you no longer have to fire up multiple tools to
manage your different SQL Server services from the relational engine to Analysis
Services. Management Studio integrates all the essential management capabilities into
one interface. You can connect to multiple SQL Server components in Management
Studio as shown in Figure 2-1.
Figure 2-1. Connecting to multiple SQL Server components
Context-Sensitive Menus for Each Object Type
The components that you can manage with Management Studio are the relational engine,
Analysis Services, Reporting Services, Data Transformation Services, Notification
Services, and even SQL Server Mobile Edition. Management Studio is smart enough to
recognize when you click a particular type of object to display the correct context menu
for that type of object. For example, when you right-click an Analysis Services cube,
Management Studio lets you process the cube as one of the menu options as shown in
Figure 2-2.
Figure 2-2. Custom context-sensitive menus for different SQL components
Mobile Database Support
For those of you using newer SQL technologies such as SQL Server Mobile Edition,
you’ll be able to easily manage your mobile databases right from Management Studio.
One caveat here is that you do need ActiveSync installed on the machine running
Management Studio, and the device must be connected to the physical machine as well.
For those of you who haven’t heard though, SQL Mobile Edition will also run on the
Tablet PC operating system so you can build applications that leverage Microsoft’s two
mobile operating systems, Windows Mobile Edition (formerly known as Windows CE)
and Table PC, with one database. Figure 2-3 shows connecting to and browsing mobile
SQL Server databases from the Management Studio console.
Figure 2-3. SQL Server Mobile Edition support
SQL Server 2000 Support
Yes, you read that right. Management Studio will support administering both SQL Server
2005 and SQL Server 2000 servers. Features that are not supported by SQL Server 2000
such as database mirroring will of course not appear in Management Studio. This allows
you to use one tool to manage a mixed environment that contains both versions of the
database.
New User Interface Enhancements
Beyond connecting to multiple components, Management Studio introduces a number of
new user interface features that DBAs have been asking for. These user interface
enhancements fix some of the pet peeves of DBAs worldwide. For example, have you
ever wanted to open more than one dialog box in Enterprise Manager? Unfortunately you
can’t in EM, but you can in Workbench. Let’s step through some of these new user
interface enhancements introduced in Management Studio.
Asynchronous Treeview and Object Filtering
If you have worked with a large number of objects in Enterprise Manager, you know that
when you expand the treeview on the left-hand side, you can step away for a cup of
coffee, a quick game of ping pong with your fellow DBAs, and check the SQL Server
newsgroups all in the time it takes for EM to return back the information. With
Management Studio, loading objects is done asynchronously so you can start opening an
object that has many children while at the same time doing other activities in the user
interface.
Another new enhancement is the ability to do rich filtering in many of the views in
Management Studio. For example, you can filter tables based on characters contained in
the name of the table, the schema name, or by the creation date of the table as shown in
Figure 2-4.
Figure 2-4. Filtering objects
Nonmodal and Resizable Dialog Boxes
One of the most requested features for EM was the ability to have multiple dialog boxes
open at once. Having this capability would allow the DBA to compare settings side-byside
for two objects, as well as allow other scenarios. Management Studio includes this
capability as well as allowing the dialog boxes to be resizable. Now, you may be saying
to yourself, why highlight this as a top-level feature? Well, if you talk to as many DBAs
as we do, you’ll hear them curse EM for its modal mode. Figure 2-5 shows the new
dialog box user interface in Management Studio with multiple dialog boxes open.
Figure 2-5. The new dialog box interface
Script To and Schedule Enhancements
Another capability that DBAs always wanted was to be able to see what EM is doing on
their behalf. The main reason for this is the ability to take what happens automatically by
setting some user interface in dialog boxes and writing scripts that can execute on a
schedule that do the same thing. With Management Studio, you no longer have to fire up
Profiler and start a trace and then run EM to see what commands are sent to the server.
Instead, right from the user interface, you can tell Management Studio to take what it
would send to the server based on your settings in the UI and instead script out the
commands to the file system, clipboard, or to a new query window.
Beyond scripting enhancements, you can now schedule the commands the server
would run based on your dialog box settings. What Management Studio does is create an
agent job to run whenever you set the schedule to be. This is useful if the server is busy
and you want to have the actions run at another time when the server is less busy, or you
want to have the script that the dialog box creates to run on a recurring basis. This feature
is in the product to help save you time and management steps. Plus, multiple jobs can
share the same schedule in the new SQL Server Agent. Figure 2-6 shows creating a
schedule from a dialog box to run later.
Figure 2-6. Scheduling the running of a backup job to run later
If you take a look at the code that is generated by Management Studio to back up
your database, you can see the commands that Management Studio would send to the
server. You can take the following T-SQL that Management Studio generates and use it
in your own applications or just look through it to see T-SQL best practices or learn how
Management Studio performs its functionality against the server.
BACKUP DATABASE [AdventureWorks] TO DISK =
N'c:\\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME =
N'AdventureWorks-Full Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
Code Authoring Improvements and Additions
For those of you who spend your time writing SQL Server code, whether you use T-SQL,
Multidimensional Expressions (MDX), or XML for Analysis (XML/A), you will be
happy to know that all of these languages are supported in Management Studio. One new
addition to the set of query languages that SQL Server supports both in the product and in
the management tools is XQuery. You’ll learn more about XQuery in Chapter 5.
Another enhancement is the tight integration with source control. You can check-in
and check-out virtually everything inside of Management Studio. This allows you to
know who made a change, when they made it, and roll back to a previous version if
necessary.
Finally, Management Studio contains new capabilities to make writing stored
procedures, views, and functions easier. This new capability is called assisted editors.
Rather than having to manually create the header information for these types of objects,
you can use the assisted editors to quickly point and click to set information as shown in
Figure 2-7.
Figure 2-7. Assisted code editors in Management Studio
Summary Views
The final piece of Management Studio that we will look at in user interface enhancements
is Summary Views. For those of you who have used the Taskpad feature in EM,
Summary Views will not look much different to you. The main difference between the
two is that Summary Views leverages the new Reporting Services report control included
with SQL Server 2005. Figure 2-8 shows the new Summary Views technology.
Figure 2-8. Summary Views show vital information about database objects.
New Capabilities
Of course, the changes to Management Studio is about a lot more than a few interface
enhancements. It brings functional enhancements and new, often time-saving capabilities.
Let’s look at some of these now.
Dedicated Admin Connection
Have you ever had a server where the CPU was maxed and you could not connect to the
server to kill off the query that was consuming all the resources? If so, the dedicated
admin connection will probably be your favorite feature in SQL Server 2005. Whenever
the server starts up, SQL reserves some resources for administrators to connect to the
server.
DeadLock Visualization
Troubleshooting deadlocks is sometimes a difficult process in any database system. To
make this process easier, SQL Server 2005 allows you to graphically show deadlocks in
the system as shown in Figure 2-9. You can use this graphical representation to quickly
track down why the system is deadlocking and take steps to minimize the deadlocks in
the system.
Figure 2-9. Deadlock visualization
Performance Monitor Correlation
There may be times when you see spikes in Performance Monitor on your SQL Server
machine such as a high CPU usage, large amounts of memory consumption, or overall
slower performance. You could be scratching your head wondering what happened in
SQL Server to cause this performance jump. Before SQL Server 2005, you would have to
capture a trace, use sysprocesses, and capture your Performance Monitor logs. After
doing all this, you would need to manually reconcile what happened between them to
figure out why performance was suffering in the system.
With SQL Server 2005, you still need to capture a trace and your Performance
Monitor logs. However, Profiler has the capabilities to let you attach Performance
Monitor logs and then scroll through your T-SQL statements while Profiler automatically
shows graphically what happened in your Performance Monitor logs. The process also
works if you click in the Performance Monitor user interface in Profiler, which jumps
you to the statement that correlates to that timestamp. This capability will save you time
in troubleshooting your SQL Server environment. Figure 2-10 shows an example of
Performance Monitor correlation in Profiler.
Figure 2-10. Performance Monitor correlation
Let’s now see how to do a Performance Monitor correlation in SQL Server 2005. The
first thing you need to do is start Performance Monitor and start capturing some
information from your computer. The fastest way we know to start Performance Monitor
is to go to the Start menu, click Run, type in perfmon, and hit Enter. From there, you’ll
want to create a new counter log under the Performance Logs and Alerts by right-clicking
the Counter Logs node and selecting New. Then, you can click the Add Counters button
and add new counters such as % processor time. You’ll also want to schedule your
logging to start either manually or on a scheduled basis using the Schedule tab. Once
you’re done, hit OK and make sure to start your logging if you select to use the manual
start option.
Next, you’ll want to set up a trace on your SQL Server through Profiler. You can do
this by clicking New Trace under the File menu in Profiler. Name your trace and make
sure to set it to save the trace to a file.
Finally, you can blast your server to simulate some SQL activity. The easiest thing to
do is to use the DTS import/export wizard to export the AdventureWorks sample
database and import that database to a new database. You can access the import/export
wizard by right-clicking the AdventureWorks database in Management Studio and then
select Export Data. Once this is done, you can stop capturing in both Performance
Monitor and Profiler.
In Profiler, from the File menu, select Import Performance Data. Select the location
where you stored your Performance Monitor log. Then, select from the File menu Open
and then Trace. Select the location where you stored your Profiler trace. Now, you can
use Performance Monitor correlation between the two to figure out what effect on the
processor a certain SQL statement had.
Maintenance Plan Designer
For any DBA, there are a common set of operations you want to perform on your
databases such as backups, shrinking, integrity checks, and updating statistics. With SQL
2000, you could use the Database Maintenance Wizard to create and schedule these tasks.
With SQL Server 2005, the Database Maintenance Wizard still exists, but is now called
Maintenance Plans. However, you will want to look at using the new Maintenance Plan
Designer.
The Maintenance Plan Designer is based on the new Data Transformation Services
(DTS) designer. By basing it on the new designer, you can draw out your administrative
tasks in a graphical workflow and have different actions based on whether the tasks
succeed or fail. The tasks include the ability to run agent jobs or T-SQL scripts. With this
level of flexibility, you can use the Maintenance Plan Designer to draw out most of the
workflow used for database maintenance. Figure 2-11 shows the Maintenance Plan
Designer.
Figure 2-11. The new Maintenance Plan Designer
SQLiMail
No more MAPI. Yes, you read that right, no more MAPI. For any DBA who has set up
SQLMail, you know that you need to get MAPI onto your server and that MAPI can
sometimes be fickle. SQL Server 2005 introduces SQLiMail, which is SMTP based, not
MAPI based. Beyond removing the MAPI requirement, SQLiMail has been integrated
with the new Service Broker technology so that mail is queued and SQLiMail returns
back quickly to the application even though the mail hasn’t really been sent yet. This will
allow you to scale your applications that use SQLiMail. Finally, SQLiMail is 64-bit
enabled, cluster aware, and supports HTML mail.
There is a caveat though with SQLiMail that will require you to keep SQLMail
around even in SQL Server 2005. SQLiMail cannot read mail. This means that if you
have applications that need to read mail from a SQL Server stored procedure or other
server-side code, you will need to use SQLMail or write your own component that reads
mail using Collaboration Data Objects (CDO) or WebDAV to access your Exchange
Server. Let’s take a look at how you would write some code that uses SQLiMail. This
code does not show all the features of SQLiMail, so if you want to see even more
functionality you can use, we’ll point you to SQL Books Online.
In the code that follows there are three examples. The first one sends the number of emails
you specify to SQLiMail. This is just to show the better performance of e-mailing
in SQL Server 2005 and how mails are queued using Service Broker even though they
haven’t been delivered yet.
The second example shows how you can send an e-mail with an attachment.
The final example shows how you can send an e-mail with HTML formatting and an
attachment. HTML formatting is a new feature in SQLiMail. By having this capability
you can customize how your e-mails look for your end users.
/***********************************/
/* Send @lots of emails */
/***********************************/
DECLARE @i INT
,@lots INT
,@subject NVARCHAR(100)
,@start_time DATETIME
SET @lots = 5
SET @start_time = GETDATE()
SET @i = 1
WHILE(@i <= @lots)
BEGIN
SET @subject = 'Demo Message ' + CAST(@i AS VARCHAR)
EXECUTE sendimail_sp @profile_name = 'TestProfile'
,@recipients = 'user1@thomrizdomain.com'
,@body = 'My Test Message'
,@subject = @subject
SET @i = @i + 1
END
PRINT 'Sent ' + CAST(@i-1 AS VARCHAR) + ' e-mails in ' +
CAST(DATEDIFF(ms, @start_time, GETDATE())/1000.0 AS VARCHAR) + ' seconds'
GO
/*************************************/
/* Send an email with an attachment */
/*************************************/
EXECUTE sendimail_sp
@profile_name = 'TestProfile'
,@recipients = 'user1@thomrizdomain.com'
,@body = 'Attached is the result of sys.processes.Danger! Will Robinson! Danger!'
,@subject = 'Result of sys.processes'
,@query = 'SELECT spid, status, loginame FROM sys.processes'
,@attach_query_result_as_file = 1
GO
/******************************************/
/* Send an HTML email with an attachment */
/******************************************/
EXECUTE sendimail_sp
@profile_name = 'TestProfile'
,@recipients = 'user1@thomrizdomain.com'
,@body = '<BODY><H1><CENTER>Sunshine!</CENTER></H1></BODY>'
,@subject = 'To brighten your day'
,@file_attachments = 'C:\sunshine.jpg'
,@body_format = 'HTML'
GO
Dynamic Management Views
In SQL Server 2000 you were probably accustomed to querying system tables such as
sysprocesses, sysobjects, syslocks, and syslockinfo in order to investigate what was going
on in the database. In SQL Server 2005, these become system views. Several views have
been added that have no equivalent on the old SQL Server 2000 system tables. For
example:
- sys.modules: To find details of the modules loaded into SQL Server.
- sys.assemblies: To find out what .NET assemblies are loaded into the
server.
- sys.sysprocesses: Information contained in this view can be used to
build customized views for waits, connections, and other information.
You can also schedule an agent job to run to query the Dynamic Management Views
on a regular basis to collect performance information and place this information into a
data warehouse. Off the data warehouse, you can use Reporting Services to figure out
chart trending information for your systems. Look at Chapter 7 for more information on
Reporting Services. Figure 2-12 shows using the new Dynamic Management Views.
Figure 2-12. Dynamic Management Views
Profiler Enhancements
With SQL Server 2005, Profiler gets an overhaul as well. The new Profiler now supports
the ability to trace both DTS and Analysis Services commands. SQL Server 2000 was
limited to tracing relational database calls only. By having these capabilities, you can use
these traces to debug any problems you have in these additional components of SQL
Server. Also, Performance Monitor correlation works with these new trace types.
Profiler allows you to save the trace file as XML. Furthermore, a traced ShowPlan
result can be saved as XML and then loaded into Management Studio for analysis.
Finally, Profiler also integrates with the new Database Tuning Advisor, which
replaces the Index Tuning Wizard. The DTA has a rich, new interface and works with the
newer features in SQL Server 2005 such as recommending partitioning your tables using
the new table partitioning features in the database engine.
SQL Computer Manager
Say goodbye to the Client Network Utility, Server Network Utility, and SQL Service
Manager and hello to SQL Computer Manager. The SQL Computer Manager supports a
number of different versions of SQL Server, including SQL Server 2000 and SQL Server
7.0, and components other than just the relational engine, such as Analysis Services and
Reporting Services. The SQL Computer Manager is a single interface where you can see
whether your services are running and how the connectivity is configured to your SQL
components.
SQL Computer Manager is MMC-based and can be launched standalone or as part of
the Computer Manager interface. Yes, MMC still is used in some parts of SQL Server
management, but for the most part you will be using the new Management Studio
interface. By integrating with the Computer Manager, you can see your SQL information,
such as protocol settings and service status, as part of your overall system information.
Plus, SQL Computer Manager uses Windows Management Instrumentation (WMI) to
talk to the SQL Server so it does not have to make queries to databases to get its
information. Figure 2-13 shows the new SQL Computer Manager and its integration into
the overall Computer Manager.
Figure 2-13. The new SQL Computer Manager
Enhanced Help and Community Integration
One of my favorite features is the new dynamic help that is tightly integration with the
SQL community as part of Management Studio. For those of you who have used Visual
Studio, you already know about dynamic help. What SQL Server 2005 can do is watch
what you’re doing in Management Studio and, as you perform tasks, the dynamic help
window changes to offer topics that may interest you for your current task. For example,
as you are clicking through the treeview to manage your server, dynamic help displays a
link to the help on managing your server.
Ask any technologist what their favorite tools are when working with Microsoft
technologies, and they will almost always mention Books Online, a web search engine,
MSDN or Technet, third-party SQL Server websites, and the Microsoft newsgroups. The
reason for all these different technologies is that they all contain a wealth of information
or at least access to a wealth of information. Through the new help system, SQL Server
2005 combines all of these tools into a single location. From help, you can search not
only Books Online locally, but also MSDN online, and SQL community sites. Also, if
you have a question, you can post that question to the newsgroups to try and get it
answered. You can pivot on whether you want Books Online results to appear in the main
user interface as shown in Figure 2-14 or you can make the other results, such as
community search results, the primary results as shown in Figure 2-15.
Figure 2-14. Highlighted results from Books Online
Figure 2-15. Highlighted results from third-party community sites
SQLCMD
Replacing oSQL and iSQL, which both still ship with SQL but are deprecated, is the new
SQLCMD command-line utility. Unlike its predecessors, SQLCMD uses OLEDB to
connect and run the T-SQL batches that you type into the tool. Plus, SQLCMD supports
newer features in SQL Server 2005 such as the ability to connect to a server using the
dedicated administration connection that we discussed earlier under the “Dedicated
Admin Connection” section.
SQLCMD also allows you to place parameters in your scripts and then pass those
parameters to SQLCMD. For example, you may want to script the backing up of a
database but may not want to have to write a script for every database or every backup
device. Instead, you may want to pass the name of the database and the name of the
device to which to back up the database. With SQLCMD, the script would contain the
following line of code:
BACKUP DATABASE $(db) TO DISK = "$(path)\$(db).bak"
The dollar sign tells SQLCMD that the script contains a variable, and then the name
of the variable is contained in parentheses. When you call SQLCMD, you can use the
new -v parameter to pass in the appropriate values for your script variables. For example,
from the SQLCMD command line you could run a backup.sql to back up the
AdventureWorks database to a local folder, as follows:
SQLCMD –E –i backup.sql –v db="AdventureWorks" path="c:\backups"
Upon execution of this command, SQLCMD will return the following response:
Processed 17112 pages for database 'AdventureWorks',
file 'AdventureWorks_Data' on file 1. Processed 2 pages for
database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.
BACKUP DATABASE successfully processed 17114 pages
in 50.374 seconds (2.783 MB/sec).
You can also set variables in your scripts whereby one script loads another script,
possibly to dynamically set variables from the second script. For example, you may want
to take the backup example and connect to 50 servers with 100 databases. You could use
variables to quickly write a SQLCMD script to perform this functionality.
Server Management Objects (SMO)
The final piece of new technology that we will look at for managing SQL Server 2005 is
the new administration object model called SMO. This section will introduce you to
SMO, and Chapter 11 will provide more details on SMO. SMO is the successor to SQLDMO.
SMO supports all versions of SQL Server back to SQL Server 7.0, so you can use
SMO as your primary API across multiple versions of SQL. SMO also allows you to
automate the administration of new features in SQL Server 2005 including XML, web
services, and snapshot isolation, as well as many of the other enhancements. The easiest
way to learn about SMO is to look at a sample application. You will find that SMO is
very approachable for the novice developer but still powerful enough for the experienced
professional.
The application you will see how to create is a simple object browser that will look at
your databases, the tables contained in those databases, and then the script used to create
that database. The application is a Windows-based application written in C#.
To get started using SMO in Visual Studio, you need to first add a reference to the
object model. You can do this using the Add Reference command off the Project menu.
You will find SMO under the namespace Microsoft.SqlServer.SMO as shown in Figure
2-16.
Figure 2-16. Adding a reference to SMO in Visual Studio
Now that you have your reference, you can start building the application. The
application is a couple of simple buttons, list boxes, and text boxes, so you can skip over
the user interface creation and instead jump to the code that implements the user interface
functionality. Figure 2-17 shows the completed user interface in Visual Studio.
Figure 2-17. The user interface for the SMO application
To make it easier, you’ll add a few using directives in your code for SMO as well as
other .NET Framework classes you will use. The code that follows does this:
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using System.Text;
After setting your directives, the code declares some variables that you will use across
methods. The first one declares a new Server object from SMO. The Server object is the
base object from which other objects are created in SMO. For example, you can retrieve
databases, tables, logins, and other database information from the children of the Server
object.
Secondly, the code declares for arrays of different types such as Database and Table
objects. We use fixed length arrays here for simplicity but you could use dynamic arrays
using the ArrayList class. These arrays will hold the objects that the application uses for
fast access to these object types. You can specify the servername if you have more than
one server when calling the constructor for the Server object. Plus, you can use SQL
authentication rather than Windows-based authentication.
Server srvSQLServer = new Server();
//You could also use dynamic arrays here
//with ArrayList
Database[] arrDBs = new Database[100];
Table[] arrTables = new Table[1000];
Now to the meat of the code. Let’s take a look at what happens when you click the
Connect button in the user interface. The first thing is the UI and arrays are cleared of any
existing information. Next, the code steps through all the Database objects in the
Database collection under the Server object. If the database is not a system database,
which is checked by using the IsSystemObject property, the database is added to the
array of Database objects.
private void btnConnect_Click(object sender, EventArgs e)
{
listDatabases.Items.Clear();
listTables.Items.Clear();
txtSQLScript.Clear();
ClearArray();
listDatabases.DisplayMember = "Name";
int i = 0;
foreach(Database tmpdb in srvSQLServer.Databases){
if (!tmpdb.IsSystemObject){
listDatabases.Items.Add(tmpdb.ToString());
arrDBs[i] = tmpdb;
i++;
}
}
}
Next, when the user clicks on a database in the user interface, the code walks through
the tables in that database using the Tables collection and Table object under the
Database object.
private void listDatabases_SelectedIndexChanged(object sender, EventArgs e)
{
listTables.Items.Clear();
txtSQLScript.Clear();
listTables.DisplayMember = "ToString()";
Database tmpdb = new Database();
tmpdb = arrDBs[listDatabases.SelectedIndex];
int i = 0;
foreach (Table tmptable in tmpdb.Tables){
if (tmptable.IsSystemObject != true){
listTables.Items.Add(tmptable.ToString());
arrTables[i] = tmptable;
i++;
}
}
}
Finally, when the user selects a table in the user interface, the code uses the Script
method to have SMO generate the T-SQL script to re-create the table.
private void listTables_SelectedIndexChanged(object sender, EventArgs e)
{
StringCollection sc = new StringCollection();
//Get the table's script
sc = arrTables[listTables.SelectedIndex].Script();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < sc.Count; i++)
{
sb.AppendLine(sc[i]);
}
txtSQLScript.Text = sb.ToString();
}
There you go. Your first SMO application! Of course, this is a simple example of
what you can do with SMO. You will find that SMO is very functional and very
approachable as a developer. One thing to note is that even though SMO is 100% written
in .NET, SMO supports COM interoperability so that you can call SMO through from
your COM code such as Visual Basic or VBScript.
Summary
In this chapter we’ve taken a whirlwind tour of the new management technologies in
SQL Server 2005. Hopefully you’ve seen how SQL Server 2005 takes SQL Server
management to the next level with new management tools, new APIs, and even new
command-line tools. Get familiar with all of these technologies since you will be using
one or more of them to manage and monitor your SQL Servers.
|
|
|
109 people have rated this page.
Average rating: 3 out of 9
|
About the authors
Tony Bain - Tony Bain is the founder of Tony Bain & Associates. He has worked with SQL Server for the last 6 years, and he is passionate about all database technologies, especially when they relate to enterprise availability and scalability. Tony currently holds the Microsoft Certified System Engineer, Microsoft Certified Solution Developer, and Microsoft Certified Database Administrator certifications.
Robin Dewson - Robin Dewson has been hooked on programming ever since he bought his first computer in 1980, a Sinclair ZX80. His first main application of his own was a Visual FoxPro application that could be used to run a Fantasy League system. It was at this point he met up with a great help in his PC development life, Jon Silver at Step One Technologies, where in return for training, he helped Jon with some other Visual FoxPro applications. From there, realizing that the marketplace for Visual FoxPro was limited, he decided to learn Visual Basic and SQL Server.
Starting out with SQL Server 6.5, Robin soon moved to SQL Server 7 and Visual Basic 5, and became involved in developing several applications for clients both in the UK and the United States. From there, he moved to SQL Server 2000 and Visual Basic 6. Robin currently is consulting at Lehman Brothers in London, where he has been for nearly 5 years, using Visual Basic 6 and Sybase on a trading system called Kojak. Robin is also the author of Beginning SQL Server 2000 Programming.
|
|