Most of the tutorials/case studies I have presented in this blog have had SQL Server Enterprise edition as their primary focus. However, the reality is that, this version, if you didn't get the hint from its name, is mostly for big enterprises that need advanced features like SQL Server Agent or Analysis Services. In any other case this version is a bit of an overkill. In these cases, SQL Server Express is a very good starting point because it's free and offers a lot to work with, such as a full database engine, full T-SQL support, full text search, native XML, and some very useful built in tools like SQL Server Management Studio. A few classic examples where SQL Server Express is a very good option are:
- Deploying desktop windows solutions that use SQL Server to users who do not own a SQL Server. Given that it's free, its setup can be embedded in the application's setup so that Windows application can enjoy most of SQL Server's functionality.
- Lightweight web applications that only need to use small SQL Server databases such as websites using a CMS like Umbraco.
- Testing and developing applications with a local database.
Although it has some limitations regarding the processing power it can use, computer memory available and database size, it is suitable for a wide range of applications. Now, this might be something that one does not think of right away, but at some point in time, it's certain that there will be the need to perform some kind of database synchronization. This is where SQL Server Express has another advantage. Some of the synchronization tools, xSQL Schema Compare and xSQL Data Compare included, follow the same 'no charge' pattern set by Microsoft for this version of SQL Server. The Lite version of these two tools, when used with SQL Server Express offers the full functionality of the licensed version. Let's first have a look at the windows desktop solution case which, in my opinion is a very interesting case where schema synchronization is useful.
Suppose you have created and deployed a desktop Windows application that uses a SQL Server database. A good example here might be an app that does some sort of system monitoring and stores any data it finds in the database. SQL Server Express is a clear choice for the database because there is no complex functionality required and the database size is very likely to be small. Let's also suppose that you have just developed a major update for the app that includes a lot of database changes. How would you go about transferring those changes in the clients' databases? One option would be to generate the full schema script from SSMS and during the update's installation on the client, run that script to recreate the database. As you can imagine this is not an optimal choice because it would delete all the data that are already saved in the database. Another option is to manually generate the script but, in most cases, simply mentioning this, would get you a few "are you kidding?" looks as it is a very tedious and error prone process. Buying a comparison tool seems like a big waste of money if you're going to use it just for this one thing, so here is where the Lite version of xSQL Schema Compare comes to the rescue. For absolutely no cost you get a tool that, on SQL Server Express, will compare the 2 database versions and generate a synchronization script that can be run during the installation process and perform all the necessary changes in the client database.
A scenario in which data comparison on SQL Server Express might be necessary is the development of websites with a CMS. Content Management Systems store all the content management-related data in a database. Take Umbraco for example. It uses an element called Document Type to provide the structure of different webpages. This element stores all the info it needs in a database which can very well be a SQL Server Express database as this type provides the necessary functionality and more. It's always a good idea to develop these sites locally or in a development server and once the changes are fully tested, upload those on the live server. Of course along side this upload, the data on the database need to be transferred as well so that the site admin won't have to recreate the new content structures. Backing up and restoring the database is not a good idea because there are some data (like the actual content of the site) that do not need to be transferred, and if the live site is on a Cloud service like Azure, restoring is not possible. Again, the best option here would be a synchronization tool and a very good choice is xSQL Data Compare. Since the synchronization will be performed on SQL Server Express, the FREE Lite version of xSQL Data Compare can be used to transfer the data. Also, the synchronization process is highly customizable (refer to the online help), so you can manually determine which tables and what data in those tables you want to synchronize. This way, only the data regarding the content structure will be transferred.
In conclusion, I think it's safe to say that the two scenarios mentioned here are a very small portion of the huge number of cases where the application of SQL Server Express is enough to fulfill a software's database needs. Keeping in mind that database synchronization is a process that sooner or later will take place in most of these cases, the extension of the 0 cost principle of SQL Server Express to its synchronization tools like in the case of the Lite versions of xSQL Data Compare and Schema Compare is something that can greatly reduce the development and maintenance costs of software.
Suppose you have created and deployed a desktop Windows application that uses a SQL Server database. A good example here might be an app that does some sort of system monitoring and stores any data it finds in the database. SQL Server Express is a clear choice for the database because there is no complex functionality required and the database size is very likely to be small. Let's also suppose that you have just developed a major update for the app that includes a lot of database changes. How would you go about transferring those changes in the clients' databases? One option would be to generate the full schema script from SSMS and during the update's installation on the client, run that script to recreate the database. As you can imagine this is not an optimal choice because it would delete all the data that are already saved in the database. Another option is to manually generate the script but, in most cases, simply mentioning this, would get you a few "are you kidding?" looks as it is a very tedious and error prone process. Buying a comparison tool seems like a big waste of money if you're going to use it just for this one thing, so here is where the Lite version of xSQL Schema Compare comes to the rescue. For absolutely no cost you get a tool that, on SQL Server Express, will compare the 2 database versions and generate a synchronization script that can be run during the installation process and perform all the necessary changes in the client database.
A scenario in which data comparison on SQL Server Express might be necessary is the development of websites with a CMS. Content Management Systems store all the content management-related data in a database. Take Umbraco for example. It uses an element called Document Type to provide the structure of different webpages. This element stores all the info it needs in a database which can very well be a SQL Server Express database as this type provides the necessary functionality and more. It's always a good idea to develop these sites locally or in a development server and once the changes are fully tested, upload those on the live server. Of course along side this upload, the data on the database need to be transferred as well so that the site admin won't have to recreate the new content structures. Backing up and restoring the database is not a good idea because there are some data (like the actual content of the site) that do not need to be transferred, and if the live site is on a Cloud service like Azure, restoring is not possible. Again, the best option here would be a synchronization tool and a very good choice is xSQL Data Compare. Since the synchronization will be performed on SQL Server Express, the FREE Lite version of xSQL Data Compare can be used to transfer the data. Also, the synchronization process is highly customizable (refer to the online help), so you can manually determine which tables and what data in those tables you want to synchronize. This way, only the data regarding the content structure will be transferred.
In conclusion, I think it's safe to say that the two scenarios mentioned here are a very small portion of the huge number of cases where the application of SQL Server Express is enough to fulfill a software's database needs. Keeping in mind that database synchronization is a process that sooner or later will take place in most of these cases, the extension of the 0 cost principle of SQL Server Express to its synchronization tools like in the case of the Lite versions of xSQL Data Compare and Schema Compare is something that can greatly reduce the development and maintenance costs of software.
Posts
0 comments:
Post a Comment