Web Hosting Upsize Tool for SQL Express

Brad and Polita on my team have been working on a cool tool for hosting providers that will enable developers to easily deploy and manage ASP.NET 2.0 database driven applications written using SQL Express on a hosted web-server.

 

Once the tool is deployed by a hoster, a developer will be able to copy their ASP.NET 2.0 web application using FTP or any other file-transfer mechanism to deploy the files for the app underneath the web-root like they do content today (note: the new “Copy Web Site” tool in VS 2005 could also be used for this).  If they want they can also use the app_offline.htm trick I posted about last week to avoid people accessing their app until the site is fully deployed and the database enabled.

 

The developer can then access the upsize tool online (it is an ASP.NET web app that hosters expose).  It allows the developer to pick the SQL Express database (typically stored in the /app_data directory) that they want to enable.  The tool then automates uploading this SQL Express database into a full-blown SQL Server database account on the back-end (note: a hoster in a shared environment would typically partition this SQL database into several hundred or a thousand different database accounts). 

 

As part of this process the new tool will perform a “deep clean” check to verify that the database being uploaded is correct and doesn’t have any malicious things in it (something hosters worry about in shared environments).  It can then also automatically fix up connection string references within the application’s web.config file to point to the new SQL Server account location (note that VS 2005 doesn’t store connection-string settings in code anymore – and instead uses the new <connectionstring> section in .NET 2.0 to store things).

 

After that, the ASP.NET 2.0 web application w/ SQL database should be good to go on the web.  This should hopefully be a much easier solution than what people go through today, and really enable non-access-based database development for the masses. 

 

Thanks,

 

Scott

 

P.S. We are targeting to have this tool available free for hosters in the November timeframe.

Published Saturday, October 15, 2005 3:42 PM by ScottGu

Comments

# re: Web Hosting Upsize Tool for SQL Express

Saturday, October 15, 2005 8:15 PM by Marcus McConnell
You read my mind! Our web host customers have been asking for an easy way to handle this exact situation ever since we started talking with them about SQL 2005 and Express.

# re: Web Hosting Upsize Tool for SQL Express

Sunday, October 16, 2005 1:05 AM by LukCAD
For me is only one question. Who will provide the permission of file security for the ASPNET user if some wrong permissions will inherited with copied files? I had today a bad experiment when i download files(with ascx extention) on Windows 2003 via other file-transfer(not ftp - but better i done it). The downloaded files was been created on the Window XP Professional previously. And what do you think was? I will need to go directly to Windows 2003 to replace permission entries on all child objects for ASPNET user. Only after it my site was been correctly started. So i wonder how it will automatcally corrected by a cool tool for hosting providers. May be i am too wrong with my permission access knowledges, but problem just was for me solved only via direct access to files from server.
Sincerely, LukCAD

# re: Web Hosting Upsize Tool for SQL Express

Sunday, October 16, 2005 3:11 PM by scottgu
Hi Luk,

FTPing files over shouldn't cause the ACLs to have problems on the hosted side -- these will typically just inherit down from those provided by the hoster.

It could have been the app_data directory that you had problems with. This needs read/write access for SQL Express scenarios -- which might have been what you were running into. Note that with the hosting upsize wizard I mentioned above this is not needed, since the data is uploaded into SQL and will not require any write permissions below the web root.

Hope this helps,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Sunday, October 16, 2005 4:53 PM by William Burrows [MVP]
Great news Scott - I have been (unsuccessfully) trying to get my ASP .NET 2.0 apps running on various ISPs machines since the beta releases. The hosters have had lots of stories (including that they will not support SQL Express - they will stick with Access - not a good response since the Access Provider was withdrawn).

So please let all the hosters aware of this and STRONGLY encourage that they make it available to their clients.

# re: Web Hosting Upsize Tool for SQL Express

Sunday, October 16, 2005 5:12 PM by scottgu
Hi William,

Yep -- we will definitely be encouraging hosters pretty hard to deploy this. It should really make a great difference for .NET based hosting.

Thanks,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Monday, October 17, 2005 9:27 PM by DWoo
Is this tool going to be part of the final release of WebDeveloper 2005 or seperate download?

# re: Web Hosting Upsize Tool for SQL Express

Monday, October 17, 2005 10:15 PM by scottgu
Hi DWoo,

This will be a separate download, since it is installed and managed by a hosting company -- not the end user developer. Everything the developer needs is included in Visual Web Developer.

Hope this helps,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Tuesday, October 18, 2005 1:26 AM by Kurt
Isn't it quite stupid by a hosting company to use this tool?
It's just another thing that will drain resources from the server and cause problems because there's a lot of people that don't know how to program and the test their new scripts on their hosting account instead of localy. SQL Server should be installed on a seperate server, dedicated for SQL server.
I'm shure this tool is perfect for "cheap" hosting companies, but for serious hosting companies it's not an option. SQL Express i a tool for development and testing and has nothing to do on a live production server.

# re: Web Hosting Upsize Tool for SQL Express

Tuesday, October 18, 2005 2:31 AM by scottgu
Hi Kurt,

This tool doesn't use SQL Express at all on the hosting server. Instead, it uploads the SQL Express database into a full-blown SQL Server instance. This full-blown SQL Server is typically running on a different server box.

Hope this helps,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Tuesday, October 18, 2005 5:54 AM by Ian Ringrose
For some “none work” project I looked at ASP.NET + SqlServer but the hosting always cost to match. It seems that if you want reasonable priced database hosing you have to using MySql + latex based tools. As my skills are in .NET and I do not wish to have to learn how the other half of the world lives, the projects did not get done.

This also has the effect that a lot of people that learn web programming in the spare time can not use .Net as it cost to match, this can not be good for Microsoft in the long term.

A cheaper way of ISP hosting SQL server is needed, maybe a “free” version of the full SQL server that can be use if EACH database is under 100Mb with no limit on the number of CPUs etc and tools to stop any one database slowing down the rest to match.

# re: Web Hosting Upsize Tool for SQL Express

Tuesday, October 18, 2005 9:23 AM by Terry
Am I missing something. Should SQL Express be used only for development, and when uploading to ones webhost its actually using SQL Server. How can that be "for the masses". Do you know how expensive an SQL Server account is on a serious webhost.

I been using SQL Express for some months now and were very happy that I didn't have to use Access anymore. And I now had an alternative to MySQL for my small, semilarge applications.
So does SQL Express actually means, "Free awesome tool for everyone as long as your not using it for live applications smaller than a bank application, cause then you cant afford it.".

Tell me I'm very very wrong.

# re: Web Hosting Upsize Tool for SQL Express

Wednesday, October 26, 2005 9:17 AM by Dan Kahler
This is awesome - I've seen this use case WAY more frequently than I'd have expected.

Any chance you'll be making the source code available with this, or - with any luck :) - perhaps providing an article that explains how the actual SQL Express to full-blown SQL 2005 database conversion step is performed?

# re: Web Hosting Upsize Tool for SQL Express

Sunday, October 30, 2005 4:58 PM by scottgu
Hi Dan,

Yep -- we'll definitely be posting some whitepapers on how this upgrade works. I'm not sure yet whether we've decided to release the source or not.

Thanks,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Sunday, October 30, 2005 5:08 PM by scottgu
Hi Terry,

SQL Express is good for dedicated hosting scenarios, and for scenarios where you have a small number (4-10) customers on a box (where each can get their own instance of SQL Express).

When you have 100-1000 customers on a box (massive shared hosting), having a separate instance of SQL Express for each customer doesn't scale. That is where/why you'd want to use SQL Server -- and carve up a separate database for each customer.

Hosting prices for SQL Server have changed dramatically in the last 12 months. Hosters can now pay a $50/month license (no upfront fees) to setup a SQL Server for multiple customers. For 1000 customers in a hosting scenario, this translates to 5 cents per customer per month in license fees -- which is a heck of a lot cheaper than in was before.

Hosters are in turn passing on these savings to customers -- it is pretty easy now to find ASP.NET + SQL Server hosting for < $10/month (and coming down).

Hope this helps,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Friday, November 4, 2005 10:23 AM by Derek Feagin
When I push my SQL Express database up to the ISP and they are willing to host SQL Express databases as the tool is free - does that mean that they have to spin up a new instance of SQL Express for every site? Lets say I have 10 clients that I support on this particular ISP server - can all of their databases run in the same instance of SQL Express - I am looking to be able to move from Access to SQL Express without being a burden to the ISP in having to ask them to set something up every time I want to drop a new database out there.

Thanks,
Derek

# re: Web Hosting Upsize Tool for SQL Express

Tuesday, November 8, 2005 1:54 PM by scottgu
Hi Derek,

A lot depends on the security model of your host server. By default, I believe SQL Express will execute databases in a unique process per unique account on the system. So if you have 10 clients all with the same account, they can share the same database process. If you have 10 clients with separate app-pools with separately configured accounts, then they'll each have separate SQL Express instances.

Hope this helps,

Scott

# re: Web Hosting Upsize Tool for SQL Express

Friday, November 11, 2005 11:14 AM by Zachary Burns
Scott,

Can you give me information on not even using SQL Express. For example, I installed VS 2005 Team System for Developers and did not install SQL Server 2005 Express, then installed SQL Server 2005 Developer Edition. Now my ASP.NET configuration menu in the VS IDE comes up with errors. Is there a fix for that?

Zack

# re: Web Hosting Upsize Tool for SQL Express

Thursday, May 25, 2006 9:35 AM by Joe Shiebler
Greetings, I developed a site with Visual Web Developer Express 2005. VWD Express includes the sql Express 2005 utility. I hired a shared site at CrystalTech after several phone calls where they said the sql Express would not be a problem. But after I uploaded they said the sqldatabase had to be on a different dedicated machine and that I needed to activate some tools to make it fit. I originally used the book VWD Express for Dummies (Very Good), everything worked on my machine and all others on my lan could operate my site via IIS but I was stumped by my site requirements and had to cancel. Now I am looking for a cheap site to continue development. Anyone know a site where i can upload straight up with ftp? Thanks

# re: Web Hosting Upsize Tool for SQL Express

Tuesday, June 20, 2006 12:56 PM by JK
Hi,

Can you tell me anything more about this tool and/or where I can find a host that has implemented this?

# re: Web Hosting Upsize Tool for SQL Express

Wednesday, August 23, 2006 9:21 PM by Kevin
WebHost4Life said they support SQL Express, but I have had endless problems with the files being locked. After a lot of prompting they replied: If you keep on using sql express you will see the same problem again. This is a recommendation from Micorosoft not to use sql express on shared hosting environment. It's insecure and inrobust. We have provided customers Sql2005 with that provide the same compatibility and better features. You can look to migrate your database to sql2005.

# re: Web Hosting Upsize Tool for SQL Express

Saturday, August 26, 2006 3:07 AM by ScottGu

Hi Kevin,

For shared hosting we typically recommend upsizing a SQL Express database to SQL 2005 -- since SQL 2005 can hosting 1000+ databases on a single box.  The code you write against SQL 2005 is the same as for SQL express -- so you shouldn't need to make any changes to your app to enable this.

Hope this helps,

Scott

# Already available?

Sunday, September 3, 2006 2:06 AM by Kevin
Scott, looks like DiscountASP are already doing something similar: www.discountasp.net/sp_sqlhosting_tools.aspx

# re: Web Hosting Upsize Tool for SQL Express

Monday, October 2, 2006 11:40 AM by Peter Gloor

After I didn't succeed with SQL Server 2005 Express on a shared hosting plan at CrystalTech I moved to WebHost4Life who claimed to support SQL Server 2005 Express.

After resolving some issues I finally managed to create a Personal website from the VS 2005 starter kit.

Later I experienced database access problems because databases with the same names already existed in other paths (even in paths owned by other users). The only way to overcome the problems was to give each database a very unique name.

However, even it works, my conclusion and recommendation is NOT to use SQL Server 2005 Express on a share hosting plan. In addition performance is much better!

Peter

# re: Web Hosting Upsize Tool for SQL Express

Monday, November 13, 2006 5:31 AM by Handy
I have host my site with SQL Express on webhostforasp.net and it works fine. They also have the tools to upload and attach my local sql express database.

# re: Web Hosting Upsize Tool for SQL Express

Friday, November 24, 2006 12:05 AM by web maintenance

This also has the effect that a lot of people that learn web programming in the spare time can not use .Net as it cost to match, this can not be good for Microsoft in the long term

# re: Web Hosting Upsize Tool for SQL Express

Thursday, January 4, 2007 9:47 AM by Julia

I have just developed a system using .net 2.0 and sqlserver express 2005, using authentication. The website is for a local youth footbal club and we can't afford to pay too much for hosting - what is the best thing to do now? Try to find a site that will use sqlserver express, spend lots of money (the cheapest I can find in the U.K. is about £180 per year), attempt to put the data into mysql or Access?

I am fed up that I have now gone down this route because of the expense.