Getting to Know the SQL Server 2005 Compact Edition Tools
In this Chapter
- Using SQL Server Management Studio
- Using the Query Analyzer
- Using Visual Studio
One main limitation of the SQL CE 2.0 database released in 2002 was the need to manage the database either using a query analyzer tool on mobile devices or by running a program to manipulate data on a device. To allow rapid application development, Microsoft has added tight integration between SQL Server Compact Edition 3.x, SQL Server 2005, and Visual Studio 2005.
Using SQL Server Management Studio, developers and administrators can manage the SQL Server Compact Edition database located on a device or on a desktop. You can design tables, columns, constraints, and indexes using SQL Server Management Studio.
Similarly you can use Visual Studio features to create, design, and manipulate the SQL Server Compact Edition database. Visual Studio also helps you to deploy a database on a device.
In this chapter you will be familiarized with the available tools. You will also learn how to use SQL Server Management Studio and Query Analyzer to manage and manipulate the SQL Server database. You will become familiar with the Visual Studio interface and will learn how to include SQL Server Compact Edition in your project. You will also learn how to set up a connection with the SQL Server Compact Edition database and finally how to deploy the application on platforms.
Using SQL Server Management Studio
SQL Server Management Studio provides an integrated environment to manage the SQL Server family of databases. Using SQL Server Management Studio, you can manage SQL Server 2005 database, SQL Server Compact Edition 3.x database, Integration Services, Analysis Services, and Reporting Services.
SQL Server Management Studio is installed on your computer along with the SQL Server 2005 installation. By default the SqlWb executable is installed at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE.
The biggest advantage of SQL Server Management Studio is that you do not need to learn a new interface for managing the SQL Server Compact Edition database. If you are already using SSMS for managing SQL Server, you can use the same interface for managing the SQL Server Compact Edition 3.0 database.
Starting SQL Server Management Studio
You can start the SQL Server Management Studio by clicking on Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Using SSMS you can create a project and a solution. The project contains a connection to a database and the corresponding SQL scripts. A container solution contains related projects. A project provides a mechanism to organize files, not the database objects. To create a new project and solution use the File | New menu option and specify the project and the solution name.
The SSMS user interface provides two types of windows—component windows and document windows. Component windows display windows listed under the View options such as Object Explorer, Registered Server, Solution Explorer, and so on. Document windows are used for queries, scripts, and files. The SQL Server Management Studio windows are shown in Figure 3.1. You can use various options to customize SSMS windows by using the Tools | option.
Figure 3.1 SQL Server Management Studio Windows
This section will describe these windows. The Bookmark feature is a new feature that you can use to create a shortcut to a line in a project file. To create a bookmark, choose Toggle Bookmark from the Edit menu.
Getting Connected to SQL Server Compact Edition Database
When you start SQL Server Management Studio, it starts with the Connection dialog box. In this dialog box you will specify that you wish to connect to SQL Server Compact Edition database. You need to specify the path of the SQL Server Compact Edition database file and initiate the connection. To connect to the SQL Server Compact Edition database follow the steps given below:
- Start the SQL Server Management Studio. The Connect to Server dialog box will appear as shown in Figure 3.2.
Figure 3.2 Connect to SQL Server CE
- Select SQL Server Compact Edition Server Type.
- Specify the name of the SQL Server Compact Edition database file in order to open an existing database. You can choose Browse for More options to browse the database file.
- Click on the Connect button to connect to the database.
This example demonstrates a simple scenario—opening an SQL Server Compact Edition database. Using the dialog box shown in Figure 3.2, you can open a password-protected encrypted database and create a new SQL Server Compact Edition database.
Using Object Explorer
Object Explorer is an SQL Server Management Studio component. Using this component you can connect to SQL Server, Integration Services, Analysis Services, Reporting Services, and an SQL Server Compact Edition database.
Object Explorer is displayed as part of the SQL Server Management Studio user interface. If Object Explorer is not visible, click on the View | Object Explorer menu option.
As shown in Figure 3.3, the Object Explorer window displays a tree structure view for all the objects. For each server type, Object Explorer displays a different set of nodes. For an SQL Server Compact Edition database, it displays Tables, Views, Programmability, and Replication nodes.
Figure 3.3 Object Explorer
Figure 3.1 shows a connection to both SQL Server database and the SQL Server Compact Edition database. All available connections are shown in a tree view. You can expand the tree nodes to see underlying objects. Object Explorer can display up to 65,536 objects.
You can right click on any node revealing a list of operations that can be performed on the object. For SQL Server Objects, the Filter option is available to allow filtering of objects. For SQL Server Compact Edition, the Filter option is not available.
Disconnecting from the SQL Server Compact Edition Database
To disconnect from the SQL Server Compact Edition database, simply select the database. Right click and then click on the Disconnect option.
After disconnecting from the SQL Server Compact Edition database, Object Explorer is refreshed. You can use the Tools | Options menu option to customize a window layout of SSMS as shown in Figure 3.4.
Figure 3.4 Object Explorer
Connecting to SQL Server Compact Edition Database from Object Explorer
To reconnect to the SQL Server Compact Edition database, click on the Connect button in Object Explorer. You will be shown options to connect to SQL Server Database Engine, Analysis Services, Integration Services, Reporting Services, and the SQL Server Compact Edition database. Select the SQL Server Compact Edition database option.
You will be shown the Connect to Server dialog box. Specify the path of the SQL Server Compact Edition database as you did earlier, thereby connecting to the SQL Server Compact Edition database.
Registering a Database Server
Using the Register Server option you can store the connection information. While connecting to an SQL Server Compact Edition database that is already registered, you do not need to resupply all the connection parameters. The Register Server Option is useful for connecting to databases that you connect to frequently. Use the Register Server Option to store connection information for SQL Server Compact Edition, SQL Server, Analysis Services, Integration Services, and Reporting Services.
At the time of registration, you store the connection information in SQL Server Management Studio. To store this information, use the Register a Database Server option.
- To register a server, select the SQL Server Compact Edition database in SQL Server Management Studio Object Explorer.
- Right click on database and click the Register option as shown in Figure 3.5.
Figure 3.5 Register a Database Server
- In the Register Server dialog box, specify where you want to place the database in the Server Group box. By default the SQL Server Compact Edition database is selected in Figure 3.6.
Figure 3.6 Specify a Server Name
- To create a new group, click on the New Group button.
- In the New Server Group dialog box, enter a Group name and Group description as shown in Figure 3.7.
Figure 3.7 Specify a New Server Group
- Click Save.
- The new Group name is entered under SQL Server Compact Edition database.
- Click Save.
Connecting to a Registered Database
You can view all registered servers and connect to one of the registered databases. You can access all the Registered Servers by clicking on the View | Registered Servers option.
Clicking on Registered Server will start the Registered Server Explorer tab. Register Server Explorer has five tabs in the top menu. The five tabs correspond to five types of database server that you can connect using SQL Server Management Studio. Earlier in this chapter you learned that when using SQL Server Management Studio you can manage the SQL Server 2005 database engine and Integration Services. As shown in Figure 3.8, select the tab for SQL Server Compact Edition to access the Example Registered Server Group that you created earlier.
Figure 3.8 List of Registered Servers
Select your Database Group and Database and click to connect.
Using Tables Node
You will be using the Tables node to define the tables and columns structures. You can right click on the Tables node and use options to create new tables. You can expand the Tables node by right clicking on any existing table and view/modify the properties of the table.
You can also expand the table to see the columns. You can see the column properties by right clicking on all columns.
Using Views Node
SQL Server Compact Edition provides INFORMATION_SCHEMA views to help you obtain metadata information about the SQL Server Compact Edition database. The INFORMATION_SCHEMA view gets included in each SQL Server Compact Edition database. You can click on views folders to see INFORMATION_SCHEMA views available. When you click on Views, your result will look similar to one shown in Figure 3.9.
Figure 3.9 Information Schema Views
The Tables view contains one row for each table that is accessible to the current user.
The Columns view contains one row for each column that is accessible to the current user.
The Indexes view contains one row for each index that is accessible to the current user.
The Key_Column_Usage view contains one row for each column that is defined as key in the current database.
The Table_Constraints view contains one row for each table constraint in a database.
The Referential_Contraints view contains one row for each foreign key constraint in a database.
The Provider_Types view contains the data type supported in SQL Server Compact Edition.
In the upcoming chapters you will learn how to utilize these views for problem solving.
Using the Programmability Node
The programmability node is more useful in SQL Server 2005. In SQL Server 2005 this node contains subnodes for Stored Procedures, User Defined Data Types, etc. In the SQL Server Compact Edition database, Programmability nodes display the data type available in SQL Server CE.
Using the Replication Node
Merge Replication is a mechanism to synchronize data between SQL Server and SQL Server Compact Edition. In Merge Replication, SQL Server acts as Publisher of data and SQL Server Compact Edition as Subscriber of data. In the Object Explorer of the SQL Server Compact Edition database you have a Replication node. The Replication node will have a Subscriptions subnode. This node will show the subscriptions available for SQL Server Compact Edition database. If you have set up the subscription, this node will display the subscription information as shown in Figure 3.10. You can check out the subscription properties by right clicking on the subscription.
Figure 3.10 Replication Node
If the SQL Server Compact Edition database has not been configured to act as Subscriber, the Subscription node under Replication node will be empty.
You will learn more about Merge Replication and Subscription in Chapter 12.
Using the Query Editor
SQL Server Management Studio has a query editor. To write queries for SQL Server CE, click on either the New Query button or the File | New Query menu option. Clicking on New Query will start the new query editor or ask you to specify a connection. Query Editor can be connected or disconnected to an SQL Server Compact Edition database. Query Editor color highlights query syntax and provides tracking indicators.
You can also open existing queries using the File | Open | File... option.
From Object Explorer you can select the SQL Server Compact Edition database, right click, and select New Query to open a query editor.
The Query Editor has an upper pane in which you can write queries. The lower pane displays the results of query execution. The results displayed are read-only. In the results pane there are two tabs: Results and Messages. The Result tab displays the data after successful execution. The Message tab displays an error message if the query has errors or it displays a message indicating the number of rows impacted as a result of a query. You can click on the Message tab to see the message. The track changed indicator in Query Editor shows which lines of an SQL script are changed.
The results are displayed in the Results section as shown in Figure 3.11.
Figure 3.11 Query Editor
Using a Graphical Execution Plan
When you write an enterprise application, it is very likely that you will want to isolate and tune performance bottlenecks. SQL Server Management Studio fulfills this key objective and helps you to understand where the most time is used in query execution. SQL Server Management Studio provides a solution to seeing the execution plan for queries. The query execution plan gives you insight and enables you to tune queries.
Using the Estimated Execution Plan
The Estimated Execution Plan option parses the query and then generates the execution plan. In this option, SQL Server Compact Edition does not execute the query but generates the execution plan. You can see the graphical representation of the execution plan with Query Display Estimated Execution plan.
Using the Actual Execution Plan
The Actual Execution plan generates the query and then shows the plan used by SQL Server Compact Edition. You can see the graphical representation of execution plan with Query | Include Actual Execution plan.
In Chapter 15, SQL Server Compact Edition Performance Tuning, you will learn how to exploit this feature of SQL Server Management Studio in order to debug and tune SQL Server Compact Edition database queries.
Managing the Database
Using SSMS you can create a new SQL Server Compact Edition database. Not only can you modify the properties of the database, you can also drop the database. With Management Studio you can even do additional operations such as the following:
- Verify the SQL Server Compact Edition database.
- Repair the SQL Server Compact Edition database.
- Shrink the SQL Server Compact Edition database.
- Compact the SQL Server Compact Edition database.
In this section you will also use SSMS interface to complete these operations.
Using Replication Wizards
For enterprise applications you will be synchronizing the data between SQL Server and the SQL Server Compact Edition database. The SQL Server Compact Edition database provides a built-in mechanism Merge Replication and Remote Data Access to synchronize the data with SQL Server. To implement Merge Replication you need to configure a Web Server, set up the Distributor, set up the Publication, and set up the Subscription.
Before using a Merge Replication you need to provide configuration details—central database, remote database, Snapshot agent folder, security mechanism, etc. You can set up this configuration either programmatically or by using Wizards. SQL Server Management Studio provides tools and wizards to set up publication, subscription, and a snapshot folder. It even generates a template code that can be used for Data Synchronization. The set of wizards remains the same whether you are setting up Merge Replication between two SQL Server instances or between SQL Server and SQL Server CE database.
Using the Publication Wizard
For database synchronization between SQL Server and SQL Server CE database, SQL Server first needs to publish the data. The database instance publishing the data is called Publisher. By using the SQL Server 2005 Publication Wizard you can specify the Publisher, Publication type, snapshot folder, and articles to publish.
Using the Subscription Wizard
The SQL Server CE database synchronizing data with backend SQL Server is called Subscriber. The subscription wizard allows you to create and manage subscriptions for SQL Server and SQL Server CE databases. You can use the properties dialog box for modifying properties of existing subscriptions.
Using the Configure Web Synchronization Wizard
You need to set up a Web Server for both Merge Replication and Remote Data Access. SQL Server Management Studio provides a Configure Web synchronization wizard. This wizard helps you to specify the Web Server, virtual directory, and authentication mechanism.
To use Web Synchronization Wizard, Publication Wizard, and Synchronization Wizard, you first need to learn the Merge Replication and Remote Data access features. You will learn how to use these wizards in Chapter 12, Synchronizing Data with Merge Replication.