p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 (http://p2p.wrox.com/forumdisplay.php?f=264)
-   -   Database Deployment to Shared Servers; SQL Script (http://p2p.wrox.com/showthread.php?t=58277)

englere May 23rd, 2007 10:23 PM

Database Deployment to Shared Servers; SQL Script
Many people use shared-hosting sites and they have great difficulty populating a remote database if they aren’t in the SA role. I will explain the newest solution from Microsoft shortly, but first I want to discuss the problems with the other options. First, VS cannot generate SQL scripts for data. Second, the free Express version of SQL Server Management Studio doesn't have the import feature to suck data into a target DB. Third, even if you have a full version of SQL Server, the SQL Server Management Studio's Import feature has a nasty bug that prevents it from importing the identity values correctly. You can make your own SSIS package to do the job, but it's not nearly as easy as the import feature.

To work-around these problems I wrote a small console program in C# that can connect to the local and remote DB’s and it can copy the records over correctly without corrupting the identities:

Although this program seems to work fine, and many people have used it, it would be far more convenient if we had a tool that could create a huge SQL script that could create the objects AND populate the data tables. There are several commercial tools that can do this, but they’re expensive and they don’t always work as advertised.

Thankfully, Microsoft has now developed a free solution for us that will create a huge SQL script from an existing database! But as you might expect, there are a couple small wrinkles. I’ll explain more in the next message of this thread.

englere May 23rd, 2007 10:32 PM

Due to the problems people have in copying data from one server to another, many times the best option is just to create a huge SQL script that contains the data in the source DB, and then you can load it in the target DB. This has many advantages: it can be run on a target DB using any kind of SQL provision offered by a hosting company, it doesn't require a full edition of SQL Server, it allows you to inspect and possibly modify the script first, etc.

The new SQL Server 2005 Database Publishing Wizard can be used to generate this kind of script that can create the objects (procedures/UDFs, etc) and also populate the data. By default, the script you create will first drop all of the existing objects in the target DB, which is usually desirable because you can re-create the whole DB this way after making changes to the source DB.

This article explains the Wizard a bit – please read this:

To start the wizard, you can right click on a connection object that's tied to a form in VS 2005, or from a right-click on a database in the server explorer in VS, or you can run it from a command line:

  CD \Program Files\Microsoft SQL Server\90\Tools\Publishing

It needs a Connection string in most cases since it can't seem to find your SQL Server even if you have the browser service running (maybe this problem was unique to my system). This is a common connection string used with SQL Server Express - note that the DB name isn't needed here because it will prompt you to select a DB next:

  Data Source=.\SqlExpress;Integrated Security=SSPI;

I don't know if it can connect to a file using an appropriate connection string with a full path to a DBF in your app_data folder (using "AttachDBFilename=" in the connection string), or if you first have to officially connect the DBF to your SQL Server first. In my case, I had already attached my DB to my SQL Server so it showed up as a Database
when viewed in SQL Server Management Studio Express.

  exec sp_attach_db @dbname=N'TheBeerHouse',

If you change from an auto-connect in app_data to a connect-by-name using the above stored procedure call, you may have to change your connection string in Web.config also:

 <add name="LocalSqlServer"
  connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=TheBeerHouse;"

My next message in this thread continues this talk and explains some work-arounds.

englere May 23rd, 2007 10:41 PM

As good as the Database Publishing Wizard is, it has some limitations. It can't handle procedures or UDFs set as ExecuteAsUser (the offending clause in SQL is: WITH EXECUTE AS 'dbo'), and these objects are often found in ASP.NET 2.0 databases that are used with the VS 2005 Diagramming support that lets you make changes to DB objects visually. If you’re willing to give up the diagramming support from the source DB, you can drop the sysdiagrams table, and the related objects from your source DB before you run the wizard:

  sysdiagrams (table)
  fn_diagramobjects (Functions, Scalar Valued)
  sp_alterdiagram (Stored Procedure)
  sp_creatediagram (Stored Procedure)
  sp_dropdiagram (Stored Procedure)
  sp_helpdiagramdefinition (Stored Procedure)
  sp_helpdiagrams (Stored Procedure)
  sp_renamediagrams (Stored Procedure)
  sp_upgraddiagrams (Stored Procedure)

Before creating a SQL script from a source DB you should also delete some of the old records you don’t really want to copy. You should empty the aspnet_WebEvent_Events table first, and you may want to remove your anonymous users, and perform other maintenance like that first.

delete from aspnet_WebEvent_Events
delete from aspnet_profile
where userid in
 (select userid from aspnet_users where isanonymous = 1)
delete from aspnet_users where isanonymous = 1

IMPORTANT! Make sure that you have deleted any testing administrator accounts before you deploy to a production server (such as the common admin/admin account). You must have a good admin account with a known and somewhat secure password because the SQL generated by the publishing wizard will delete the entire existing target DB and re-create it using only the data present in the source DB at the time this script was created.

Please try this Wizard - I'm sure you'll like it.


motemape May 28th, 2007 03:40 PM


I would like to try this method, as it appears to be the only alternative when using the wizard. It is best to have all SQL Script that populate tables

What is the consequences of droping the sysdiagrams table and other records on the system? Will it be possible to build it afterward? Does it matter?

Thanks again for valuable advices :)


If you’re willing to give up the diagramming support from the source DB, you can drop the sysdiagrams table, and the related objects from your source DB before you run the wizard:

  sysdiagrams (table)
  fn_diagramobjects (Functions, Scalar Valued)
  sp_alterdiagram (Stored Procedure)
  sp_creatediagram (Stored Procedure)
  sp_dropdiagram (Stored Procedure)
  sp_helpdiagramdefinition (Stored Procedure)
  sp_helpdiagrams (Stored Procedure)
  sp_renamediagrams (Stored Procedure)
  sp_upgraddiagrams (Stored Procedure)


englere May 31st, 2007 07:50 PM

I never use the diagram method of modifying tables, so it wouldn't hurt me to lose this, but you can copy the DB and do some testing. It's pretty easy to make a local copy of the whole DB (you can just copy the MDF if you want). I'm guessing it will just recreate these objects the next time you create a DB diagram as explained in the book.


vegaliming February 10th, 2008 08:17 AM

hi£¬englere £¬you are so hard working~~~~

be careful for your health.

so THX U for your help~~~~~

vega lee

tectrix May 17th, 2008 07:00 AM

What do I need to do if I want to change the name of the database from "ASPNETDB" to something else like "MyDB" if I'm using ASP.NET's Membership and my other tables also exist in the same db of ASPNETDB ?
(I'm asking this for the deployment time on shared server)


All times are GMT -4. The time now is 04:38 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.