Introduction
Last year I have started to write articles about SMO. In the first article, I have described what Server Management Objects are. I have shown how to work with database storage objects. In the second article, I have described how to use classes not related to database storage objects. I third article I have described scripting capabilities of SMO. In this article I will demonstrate how to transfer data between databases and how to trace events.
Transferring Data
Sometimes developers need to transfer data from one database to another. There are many ways how to solve this problem. You can move/copy database, detach database from old server and attach it to new one or back up the database and restore it on new server or you will create your own application that transfer database on it’s new server. In this article I will describe the last mentioned way.
To accomplish this task you need to use Transfer class. This class is a utility class that provides tools to transfer objects.
Objects in the database schema are transferred by executing a generated script on the target server. Table data is transferred with a dynamically created DTS package.
This class is in the Microsoft.SqlServer.Management.Smo namespace.
static void Main(string[] args)
{
ServerConnection conn = new ServerConnection(@"posta","kanasz","chaaron");
try
{
Server server = new Server(conn);
Database db = server.Databases["ApplicationErrors"];
Transfer t = new Transfer(db);
t.CopyAllTables= true;
t.Options.WithDependencies = true;
t.DestinationServer = @"ANANAS\ANANAS2009";
t.DestinationLogin = "kanasz";
t.DestinationPassword = "chaaron";
t.DestinationDatabase = "ApplicationErrors";
t.CreateTargetDatabase = true;
t.CopyData= true;
t.DataTransferEvent +=new DataTransferEventHandler(DataTransferEvent_Handler);
t.DiscoveryProgress +=new ProgressReportEventHandler(DiscoveryProgress_Handler);
t.TransferData();
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
catch (Exception err)
{
Console.WriteLine("ERROR:" + err.Message);
}
}
protected static void DiscoveryProgress_Handler
(object sender, ProgressReportEventArgs e
)
{
Console.WriteLine(e.Total + "/" + e.TotalCount + " " + e.Current.Value);
}
protected static void DataTransferEvent_Handler(
object sender, DataTransferEventArgs e)
{
Console.WriteLine("[" + e.DataTransferEventType + "] " + e.Message);
}
Previous example demonstrate how to transfer database from one database server to another database server. 8 properties are set to configure transfer object:
CopyAllTables, Gets or sets the Boolean property value that specifies whether all the SQL Server tables are copied from the source database to the target database in the transfer operation.Options, Represents scripting options object that provides programmatic options that can be set for scripting operations. Its property WithDependencies is property values that specifies whether to include all dependent objects in the generated scipt.DestinationServer, Represents the instance of SQL Server that owns the database that is the destination of the transfer operation.DestinationLogin, Gets or sets the logon for the instance of SQL Server to which the destination database belongs.DestinationPassword, Gets or sets the logon password for the instance of SQL Server to which the destination database belongs.DestinationDatabase, Gets or sets the database that is the destination of the transfer operation.CreateTargetDatabase, Gets or sets the Boolean property value that specifies whether the target database is created in the transfer operation.CopyData, Gets or sets the Boolean property value that specifies whether all the data is copied from the source database to the target database in the transfer operation.
Two events are handled:
DataTransferEvent, Reports that data has been transferred. Using this event you can get information about currently executed script.DataDiscoveryEvent, Reports the progress of the discovery process.
Another example
Following example demonstrate how to use more options of Transfer class. This example allows you to transfer data from one sql database server to another. Instead of this It allows you to generate transferring script and use more transferring and generating options.
Main form of this example consists of three tabs. In first tab you can choose database servers and set login credentials for them.
In second part you can choose transferring options:
Copy All Db Triggers, Gets or sets the Boolean property value that specifies whether all the SQL Server database triggers are copied from the source database to the target database in the transfer operation.Copy All Defaults, Gets or sets the Boolean property value that specifies whether all the SQL Server defaults are copied from the source database to the target database in the transfer operation.Copy All Full Text Catalogs, Gets or sets the Boolean property value that specifies whether all the SQL Server Full-Text catalogs are copied from the source database to the target database in the transfer operation.Copy All Full Text Stop Lists, Gets or sets the Boolean property value that specifies whether all the SQL Server full-text stop lists are copied from the source database to the target database in the transfer operation.Copy All Logins, Gets or sets the Boolean property value that specifies whether all the SQL Server logins are copied from the source database to the target database in the transfer operation.Copy All Objects, Gets or sets the Boolean property value that specifies whether all the SQL Server objects are copied from the source database to the target database in the transfer operation.Copy Data, Gets or sets the Boolean property value that specifies whether all the data is copied from the source database to the target database in the transfer operation.Copy All Partition Functions, Gets or sets the Boolean property value that specifies whether all the SQL Server partition functions are copied from the source database to the target database in the transfer operation.Copy All Partition Schemes, Gets or sets the Boolean property value that specifies whether all the SQL Server partition schemes are copied from the source database to the target database in the transfer operation.Copy All Plan Guides, Gets or sets the Boolean property value that specifies whether all the SQL Server plan guides are copied from the source database to the target database in the transfer operation.Copy All Roles, Gets or sets the Boolean property value that specifies whether all the SQL Server roles are copied from the source database to the target database in the transfer operation.Copy All Rules, Gets or sets the Boolean property value that specifies whether all the SQL Server rules are copied from the source database to the target database in the transfer operation.Copy All Schemas, Gets or sets the Boolean property value that specifies whether all the SQL Server schemas are copied from the source database to the target database in the transfer operation.Copy All Sql Assemblies, Gets or sets the Boolean property value that specifies whether all the SQL Server assemblies are copied from the source database to the target database in the transfer operation.Copy All SPs, Gets or sets the Boolean property value that specifies whether all the SQL Server stored procedures are copied from the source database to the target database in the transfer operation.Copy All Synonyms, Gets or sets the Boolean property value that specifies whether all the SQL Server synonyms are copied from the source database to the target database in the transfer operation.Copy All Tables, Gets or sets the Boolean property value that specifies whether all the SQL Server tables are copied from the source database to the target database in the transfer operation.Copy All Aggregates, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined aggregates are copied from the source database to the target database in the transfer operation.Copy All UDDTs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined data types are copied from the source database to the target database in the transfer operation.Create Target Database, Gets or sets the Boolean property value that specifies whether the target database is created in the transfer operation.Copy All UDFs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined functions are copied from the source database to the target database in the transfer operation.Copy All UDTTs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined table types are copied from the source database to the target database in the transfer operation.Copy All UDTs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined types are copied from the source database to the target database in the transfer operation.Copy All Users, Gets or sets the Boolean property value that specifies whether all the SQL Server database users are copied from the source database to the target database in the transfer operation.Copy All Views, Gets or sets the Boolean property value that specifies whether all the SQL Server views are copied from the source database to the target database in the transfer operation.Copy All Xml Schema Colls, Gets or sets the Boolean property value that specifies whether all the SQL Server XML schema collections are copied from the source database to the target database in the transfer operation.
In the last part you can see transferring script.
Transferring Data
SQL Server trace is mechanism for monitoring activity inside SQL Server. Every time the activity occurs, SQL Server generates event and this event is shipped to monitoring tool (SQL Profiles) which displays all information about event. SQL Trace and SQL Profiler allows you to analyze performance and then tune your database up.
When you want to use tracing capabilities of SQL Server you need to user SMO Trace and replay classes which provide an interface with which you can trace and record events, manipulate and analyze data and replay trace events. All trace and replay classes are located in Microsoft.SqlServer.Management.Trace namespace.
The following basic example logs the name of the first 10 events.
static void Main(string[] args)
{
TraceServer ts = new TraceServer();
ConnectionInfoBase ci = new SqlConnectionInfo(@"ANANAS\ANANAS2009");
((SqlConnectionInfo)ci).UseIntegratedSecurity = true;
ts.InitializeAsReader(ci,
@"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf");
for (int i = 0; i < ts.FieldCount; i++)
{
Console.WriteLine(ts.GetName(i));
}
int eventNumber = 0;
while (ts.Read())
{
Console.Write(ts.GetValue(0) + Environment.NewLine);
eventNumber++;
if (eventNumber == 10)
break;
}
ts.Close();
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey();
}
The first step is to create TraceServer object that represents a new trace on an instance of SQL Server. Next step is to create connection to SQL Server. This connection is represented by ConnectionInfoBase object. When this two objects are created, then you must initialize the reader by InitializeAsReader method. This method accepts 2 parameters. Firs of them is ConnectionInfoBase object and the second is trace definition file. The trace definition file determines the information contained in the trace which events and what columns of trace data are captured for each event.
Next more advanced example shows all information about events in grid. You can start and stop tracing wherever you want. This example consists of two screens: Main screen and Login screen. In Login screen you can set SQL Server instance and credentials.
Main screen displays all information in gridview. In top panel are three buttons:
Login, Select template and Start.
History
- 22 Jan 2012 - Article posted
