EF Code First Cookbook — Recipe #3: Installing Tools to Manage and Trace SQL Server

As you progress and cook applications from new EF Code First recipes, you will need to view and manage databases. While Visual Studio’s Server Explorer is great tool to have a look at the structure of your databases, it is definitely not the best to manage them. Also, an appropriate SQL profiler helps you understand how EF Code First things go on behind the scenes.

A good cook knows the most useful tools if she wants to create quality feast. Before going on with more advanced recipes, in this post you will learn how to obtain the tools that help you cook more delicious goodies.

Recipe #3: Install the Toolset to Manage and Trace Databases

There are several tools you can utilize to manage your SQL Server databases. In this recipe you will install the two most important ones.

Ingredients

• SQL Server 2008 R2 Express with Tools
• AnjLab SQL Profiler

Preparations

Generally, when you install Visual Studio 2010, SQL Server 2008 Express is also installed on your machine, unless you manually exclude it from the setup options. If you were able to cook Recipe #1 and #2, it means that SQL Server Express is already working on your machine.

Installing SQL Server 2008 R2 Express with Tools

Even if you installed SQL Server 2008 Express with Visual Studio 2010, the set of management tools is still missing from your machine. Without it you can manage SQL databases only from command line tools. This is great, but not as easy as with using SQL Management Studio. On this page you’ll find links to download SQL Management Studio according to your machines operating system (32 or 64-bit versions), as shown in Figure 1.

Figure 1: Links to download SQL Server 2008 R2 Express with database tools

1. Click the appropriate link and download the installation kit executable.
2. When the download completes, start the executable (SQLEXPRWT_x86.exe or SQLEXPRWT_x64.exe). After the install kit extracts itself (and you allow it to run when asked), select the New installation or add features to existing installation task link, as shown in Figure 2.

   

   Figure 2: SQL Server Installation Center welcome screen

3. In about half a minute the installation is prepared and the Licence Terms screen is displayed. Check the ‘I accept the licence terms’ check box and click Next.
4. In the Feature Selection page, the Database Engine Services and SQL Server Replication check boxes are set by default. If you have SQL Server Express already installed (together with Visual Studio 2010), uncheck these options. Check the Management Tools - Basic option, as shown in Figure 3. In the unlikely event you have not installed SQL Server Express, check the Database Engine Services option as well. Click Next.

   

   Figure 3: Setting options on the Feature Selection page

5. In a few seconds the wizard displays the Error Reporting page, click Next again. The installation process starts and in a couple of minutes the Management tools are installed on your machine. The Complete page is displayed. Click Close to exit the wizard.
6. Close the SQL Server installation Center.
7. In the Start menu navigate to All Programs >> Microsoft SQL Server 2008 R2 and click SQL Server Management Studio. When the application is started, check whether you can connect to the SQLEXPRESS instance with the settings, as shown in Figure 4.

   

   Figure 4: SQL Management Studio connection dialog

8. When your successfully connect to the SQLEXPRESS instance, the Object Browser window is displayed, as shown in Figure 5.

   

   Figure 5: The Object Browser window

Installing AnjLab SQL Server Profiler

The standard SQL Server Profiler that comes together with non-express SQL Server versions does not work with SQL Server Express — and it is not free. Having an SQL Server Profiler is very useful when you want to peek SQL statements sent by Entity Framework to the database server. The AnjLab SQL Server profiler for SQL Server 2005/2008 is a simple and great open source tool. You can find the download link at the bottom of the page, as shown in Figure 6.

Figure 6: The download link of AnjLab binaries

Download the zip file and decompress it with your favorite tool. Start the SqlExpressProfiler.msi file, and use the ‘Next, Next, Next’ approach to install it — you can optionally change the default installation options.

Tasting SQL Server Management Studio

It’s time to check how you can manage your SQL Server databases. If you have used SQL Server Management Studio before, you can skip this section, if not, the following steps — where you will delete a database — give you a basic feeling:

1. Open either the Recipe01 or Recipe02 solution in Visual Studio. You can download the source code from here and here, respectively. Build and run the application, it will create a sample database in your SQLEXPRESS instance.
2. Launch SQL Server Management Studio and connect to the SQLEXPRESS database Engine instance.
3. Expand the Databases node, and you can see the either Recipe01.OrderDatabaseContext or Recipe02.OrderDatabaseContext nodes, or maybe both, as shown in Figure 7.

   

   Figure 7: The list of databases in SQL Server Management Studio

4. Right-click the database node representing any database coming from the recipes, and select Delete from the context menu. Set the Close existing connections check box, as shown in Figure 8, and click OK. If you can see both databases, repeat these steps to delete the other database, too.

   

   Figure 8: Deleting a database with SQL Management Studio

5. Keep the solution open in Visual Studio.

Tasting AnjLab SQL Profiler

AnjLab Profiler can be used to catch and view operations that have been executed on SQL Server Express. The information you obtain with the profiler tool can be utilized to understand what Entity Framework does in the background. It is useful both for getting acquainted with EF internals and solving performance issues. To grasp the basics of the profiler, follow these steps:

1. Launch the profiler from the Start menu (Start >> AnjLab >> AnjLab.SqlProfiler)
2. Create a new trace with File >> New Trace (Ctrl + N).
3. On the General tab of the Trace Properties dialog, set Authentication to Windows Authentication, as shown in Figure 9.

   

   Figure 9: General trace properties in AnjLab

4. On the Events tab set the check boxes in the All column of the RPCCompleted and SQLBatchCompleted events, as shown in Figure 10, and click Run. AnjLab is now listening to calls that arrive to SQL Express.

   

   Figure 10: Setting event trace properties in AnjLab

5. Go back to Visual Studio, and Run the solution with Ctrl + F5. If you have closed the solution, open it again.
6. Turn back to Anjab, and you can see the SQL Statements executed, as shown in Figure 11.

   

   Figure 11: SQL Statements caught by AnjLab

Going On

Now, you have a basic understanding of creating simple database-aware objects with Entity Framework Code First. You also have some experinece with the SQL Server Management Studio and AnjLab tools that may help you in managing your databases and monitor the communication between the framework and the underlying SQL Server, repectively. It is time to learn new features through new recipes.