Why do we need a manual database?
Database has a crucial role in developing a website. A database is a set of information that stores the entire website data including theme options, posts, images, widgets and so forth.
In Sitefinity development, assigning a database for your project is the very first step you got to do. The moment you entered the platform, Sitefinity had automatically created a series of pre-defined tables.
It serves for several purposes, for back up, cloning, migrating…but the most vital use that your latest increment must be distributed carefully in order to deploy the final production.
In the previous tutorial, you had the idea of how to configure a Sitefinity project with SQL Server Express database.
One day you completed your project, everything happy but you are keen to test out a couple more things for your project. Or simply you have gone so far with the development but figured out the database you have been using since day 1 was SQL Server Express and you want to assign a custom database for your project. Or you want better security for your database and give the accessing permissions to the reliable users only.
In this tutorial, I will be showing you the use of Microsoft SQL Server and how to configure your current Sitefinity to the new database.
Install Microsoft SQL Server
What you should have got in your computer now is SQL Server Installation Center, this is an extra function comes along with MS SQL Server Express that I indicated in the article Install Sitefinity with Microsoft SQL Server Express.
Open it up and install SQL Server and SQL Server Management Tools.
The installation is quite straight forward. Bear in mind, you will encounter where it asks you to name the instance, keep the name clear and reasonable. Once the setup is done, you are now ready to configure your new database.
NOTE: You don’t need to install MS SQL Server Express if you intend to use a manual database.
When installing SQL Server, the sa account is very important, keep your credentials safe. The identity sa stands for system administrator who has the highest power to control all databases.
Create a new database
Open MS SQL Server Management Studio, log in with your sa account. On the first go your Server name may remain SQL Express Instance, make sure you select the new one that you just created earlier then hit Connect.
When you are inside, expand and select the Database, right click and perform a New Database.
Name your new database as you wish and leave everything else as default.
You now successfully created a custom database, all we need to do now is connect your Sitefinity project to this database.
Assign the new database
Go back to Internet Information Services (IIS), dig down to your project site and jump into the project’s directory by clicking on Explore.
This will lead you to the config file that contains the string to determine which database your project is using. This file is located in: %\App_Data\Sitefinity\Configuration\
Use a simple text editor program like Notepad to edit DataConfig.config file. Make sure you open this file as an admin to be able to save.
When you open it, the string looks like this with the database connected to SQL Server Express settings.
We have to replace it with your new database by changing the string to:
data source=ZENWHITELAP\DEITATOWN;UID=sa;PWD=yourpassword;initial catalog=deitatownDB
Explanation:
data source: your SQL Server Instance that you set up at the beginning, always with format YourPCName\YourSQLServerInstanceName
UID: user ID, by default it is always sa
PWD: your password associated with sa
initial catalog: your new database name
Clear the browser cache
Open your browser and fire up your project to see if there is any change. A couple of things might happen, either your site is good to go for setting up along with your new database or it will throw you an error page not found.
This simply due to browser cache has stored your old SQL Express database settings, all you need to do is clearing your browser cookies.
Depends on what browser you are using, if you are using Chrome like I am, just go to Settings and delete all cookies that contain .local.
After that, start your site from IIS again and you should have got the Startup page showed up that indicates your site is running on the new manual database.
Conclusion
With this new database, you can decide who has access to it or share, migrate a lot more comfortably.
You can use SQL Server Management Studio to create new users and assign the privileges for them under the Login section. Now you have the idea of using a manual database for Sitefinity projects, not only SQL Server but for Oracle or MySQL as well.
If you have any good trick with MS SQL Server, do not hesitate to share with us especially when it comes to back up or cloning because we all don’t want to harm the heart of a website right?