Enterprise-grade reporting and data analysis has become more decision oriented due to Microsoft’s SQL Server 2005. Moreover, the expenses involved in building and deploying applications have stayed down due to the low cost of the software. SQL Server 2005, being a highly reliable database platform for vital applications, is capable of delivering high levels of performance, security, and availability.
ASP.NET 2.0 lets developers add data to a Web site in a manner that is almost identical to how they would normally add another server control with the help of the control-based approach to data access. The performance of your Web application can be enhanced with the introduction of several new features of ASP.NET 2.0. Combined with SQL Server 2005, the new features of ASP.NET 2.0 allow data to be accessed only when changes have taken place in the database, resulting in extraordinary database scalability enhancements.
By default, almost all ASP.NET 2.0 application services are configured to use the built-in SQL Express provider. When you start using any of these application services for the first time, this provider will automatically create a new database and offer a simple way to get started without any setup problems.
SQL Server 2005 can be installed in two modes, the full-blown version, or the compact Express version. The default mode is SQL Express; when developing large-scale N-tier applications, the full version is preferred.
To use a complete SQL Server 2005 database instance in place of SQL Express for applications development, you need to follow the steps below.
Changing the Provider for Using SQL Server 2005 in Place of SQL Express
Step 1: Create or get a connection string to a blank SQL database instance.
The first step you should take is to create or obtain a connection string to an empty SQL database.
Step 2: Connect your SQL database with the ASP.NET schemas.
Run the aspnet_regsql.exe utility usually installed with ASP.NET 2.0 by navigating to C:WINDOWSMicrosoft.NETFrameworkv2.0.xyz>aspnet_regsql.exe.
You can also run the aspnet_regsql.exe utility from the command line. This will start the wizard and display the Welcome screen.
You can use this wizard to walk through the process of creating the schema, tables, and sprocs for the built-in SQL providers that come with ASP.NET 2.0:
The first screen that appears will be Welcome to the ASP.NET SQL Server Setup Wizard, as shown above. Click on Next to proceed with the wizard.
On the next screen (Select a Setup Option), you will find two setup options:
- Configure SQL Server for application services: This will automatically configure the SQL Server for you by running a script.
- Remove application services information from an existing database: This will run the uninstall script that results in removal of all the structure created in SQL to support ASP.Net 2.0 features.
You need to select the Configure SQL Server for application services radio button to run a script that will configure an existing database or generate a new database for storing the ASP.NET membership, profiles, role management, personalization, and SQL Web event provider information.
Once selected, click Next to proceed.
Now you need to specify your Connection Information.
First specify your SQL Server name. Then choose the authentication method that will subsequently provide SQL Server 2005 access to all your Windows users.
Select Windows Authentication if you want to use your Windows user name and password. Select SQL Server Authentication by specifying the SQL Server username and password if you want to restrict access to SQL Server 2005 for other users.
Finally, select the database that you need to configure to support ASP.NET 2.0 features.
Once done, click Next to proceed.
On the Confirm Your Settings screen, click Previous to change your settings or click Next to proceed.
At this point you will get a screen telling you that the database has been created or modified, which implies that all the database schema and sprocs to support the application services have been installed and configured. Now just simply click Finish to exit the wizard.
Note: Your database administrator (DBA) can see the exact process running in the background by viewing the raw .sql files below the above framework directory. Your DBA can go through those raw .sql files and run them manually to install the database.
Step 3: Point your web.config file at the new SQL database.
Your web.config file in ASP.NET 2.0 now supports a new section called <connectionStrings> that is used in the storage of connection strings. From an administration viewpoint, the added advantage of the new ASP.NET Admin MMC Snap-in is that it lets you configure and manage things in a GUI-based way:
Another feature of ASP.NET 2.0 is that it supports encrypting any section stored in the web.config files. This means you can securely store private data like connection strings without writing any of your own encryption code. By default, the built-in connection string called “LocalSqlServer” that comes with ASP.NET 2.0 is configured to use an SQL Express database. This in turn is configured to use Membership, Roles, Personalization, Profile, and Health Monitoring services by default.
Replacing the connection string value of “LocalSqlServer” in your application’s local web.config file will help your application get an automatic advantage in your newly created SQL database. For example, if you create your database on the local machine in an “appservicesdb” database instance and connect it using Windows Integrated security, you should change your local web.config file to specify this:
<configuration>
<connectionStrings>
<remove name=LocalSqlServer/>
<add name=”LocalSqlServer” connectionString=”Data Source=localhost;Initial Catalog=appservicesdb;Integrated Security=True” providerName=”System.Data.SqlClient”/>
</connectionStrings>
</configuration>
Save this information to start using the newly created and defined SQL Server database for all of the built-in application services.
Note: The use of the “LocalSqlServer” connection string name is the only disadvantage with the above approach, as it seems odd when you install the database on another machine. You can give it your own name by adding an entirely new connection string and pointing the existing providers to the new connection string name for the default LocalSqlServer.
]]>