Deploying a local database to your ISP (SQL Server Database Publishing Wizard)


I have seen a few posts lately where people are having a hard time getting a database up to their ISP. While there are tutorials on using SQL Express and an embedded database (in the App_Data folder), I have seen very few posts that deal with publishing to a database directly. I am going to cover the SQL Server Database Publishing Wizard.

Before going forward, you have to have SQL Server installed locally. This can be SQL Server Express. You should also have Visual Web Developer installed, although I do not think this is mandatory, as the tutorial will show.

Direct Publishing

First, let’s take the direct route. It is the easiest way to get the database up. The less direct route will use most of the same steps, although it is a bit more complex, as you have to run the script on the server.

First, download the SQL Server Database Publishing Wizard 1.1 from Microsoft. The page can be found here. It is a fairly simple install, so I won’t go through its steps. Once you are installed, we can go on.

1. Start the Database Publishing Wizard – In Vista, you can type in the search box (just above the start button) to quickly get to it. If you have already upgrade from Vista to Windows XP ;-P, you can go to Program Files >> Microsoft SQL Server Database Publishing Wizard >> Database Publishing Wizard and start it there.

Start

2. Click Next on the Welcome Screen

3. Select your local database to log into. For most of you, you will only have to change localhost (the default) to .SQLEXPRESS, as shown below:

Step1
SELECT SOURCE SERVER

NOTE: Since this is a web site, you can copy and paste your connection string for ASPNETDB into the bottom box and get in that way.

Select-Database 
CONNECTION STRING METHOD

4. Click next and select a database. If you have provided a connection string, one will be pre-selected for you.

Step2
SELECT DATABASE

5. Click next.

Now, here is where paths may diverge a bit. If you have the ability to log onto your hosting database server via a web service, you can do it rather simple. We are going to show that way first.

 

Step3
SELECT AN OUTPUT LOCATION

6. Click on Publish to Shared Hosting Provider (where the arrow is pointing) and then click the More button.

More 
SELECT A HOSTING PROVIDER

7. Click the New Button (circled above) and then fill in the form.

Host
CREATE A HOSTING PROVIDER

8. In this instance, I have no databases, so I going to click New and create one.

StopMakingSense
CREATE A NEW DATABASE

9. Click OK, pick the database you created and then click OK. Then click finish and publish your database.

My Provider has no web service

You have finished the first five steps and find you have no web service. Here is the longer way. This time, leave the Select an Output Location as Script to file and click finish.

Publish
CONFIRMATION SCREEN

There are three things to check here.

  1. You are publishing the correct database
  2. You know where it is publishing to (you will need this to publish)
  3. You are publishing BOTH Schema and Data

If you follow the instructions above, you should only be concerned with the middle one, as you need to find the file. Now, click finish again. When it is complete, you should see a screen like this:

EndGame
COMPLETED

Publishing the Database

If you have a script, you need to publish it. You will need the server name and credentials your ISP gave you to log into the database server. You will also need a tool to do it. Since you probably have SQL Server Express 2005, you should also have the SQL Server Management Studio Express. If not, you can download from here. I will use SQL Server Management Studio (full version, not express); the only difference is my screens may look a bit different than yours. Here are the steps:

1. Open the Management Studio

2. Alter the Server name and user id and password to log into your server. The pieces you will have to change are circled below. You will also have to change authentication to SQL Server Authentication to enter user id and password.

LogIntoSqlServer

3. Select your database in the Object Explorer (upper left side of the tool) or click View >> Object Explorer if it is not showing. With an ISP, you will see a long list generally (unless they have filtered permissions properly), so select yours.

Choose-Database

4. Click on the New Query Button

New-Query

5. Click on the Open File button and find your file (alternatively you can choose either File >> Open >>. File or the keystroke Control + O).

Open-File-button

6. Click the Execute button (or press F5)

Execute

Your database is now deployed to the server.

Fixing the Website

If you developed locally, you will have the wrong connection string. Let’s fix that.

1. Open your web development tool (this can be Visual Studio, Visual Web Developer Express, Expression Web, Dreamweaver, etc.).

2. Open the web.config file

3. Find the <membership> section so you know which connection string to change (bold below):

<membership>
  <providers>
    <add
      name="AspNetSqlMembershipProvider"
      type="System.Web.Security.SqlMembershipProvider, …"
     
connectionStringName="ASPNETDB"
      enablePasswordRetrieval="false"
      enablePasswordReset="true"
      requiresQuestionAndAnswer="true"
      applicationName="/"
      requiresUniqueEmail="false"
      passwordFormat="Hashed"
      maxInvalidPasswordAttempts="5"
      minRequiredPasswordLength="7"
      minRequiredNonalphanumericCharacters="1"
      passwordAttemptWindow="10"
      passwordStrengthRegularExpression=""
    />
  </providers>
</membership>

4. Go the <connectionStrings> section and find that connection string. In this case, we are looking for ASPNETDB.

<connectionStrings>
    <add name="ASPNETDB" value="Data Source=.SQLEXPRESS;AttachDbFilename=&quot;C: somewebsiteApp_DataASPNETDB.MDF&quot;;Integrated Security=True;Connect Timeout=30;User Instance=True"/>
</connectionStrings>

5. Change the connection string to the one for your host

<connectionStrings>
    <add name="ASPNETDB" value="server={server name};database={your database name:;UID={user id};pwd={password};"/>
</connectionStrings>

You will obviously have to change {server name} to the actual server, like p3swhsql-v15.shr.phx3.secureserver.net for one of Go Daddy’s servers. But, once that is done, you should be rocking.

Hope this helps …

Peace and Grace,
Greg

Twitter: @gbworld

Advertisements

12 Responses to Deploying a local database to your ISP (SQL Server Database Publishing Wizard)

  1. Sanjay says:

    This is absolutely the best tutorial I have seen on this subject. It solved all my problems.Thanks a lot,Sanjay

    • BD says:

      It took me two days to find this post – this was after dealing with GoDaddy tech supoort twice. Nobody could tell me how to upload a local SQL Express DB to goDaddy shared hosting. Database Publishing Wizard does not work on GoDaddy 2008 SQL DB’s so I had to find another way to do it. I was about ready to move everything back to 2005. This worked for me on the VERY FIRST TRY! Excellent recommendations – thank you very much!

      BD

  2. Gregory says:

    I am glad this tutorial helped you out. This is, unfortunately, a topic I have not seen covered anywhere else, although it is something lots of people are trying to do.Peace and Grace,Greg

  3. Unknown says:

    This never worked for me. Though test connection is successful I always get Internal Error during begin publish( System.web.service)

  4. Ron says:

    Sure wish I could try this tutorial out but the Database Publishing Wizard always says this when I use it: Internal error during begin publish. (System.Web.Services)

  5. SINIS says:

    If you are getting Internal error during begin publish. (System.Web.Services), when publishing to godaddy. Use your primary FTP account and should work like a charm. Hope this helps

  6. Merv says:

    I have a problem with SQL Server Database Publishing Wizard. When I try to change the Server from “localhost” to “.\SQLEXPRESS”, I get an error message that indicates that the server was not found. I have the following packages installed on my XT machine.Microsoft SQL Server Database Publishing WizardMicrosoft Visual Web Development ExpressMicrosoft .NET Framework SDK v2.0Microsoft SQL Server 2008What is my problem and what is the solution?

  7. Gregory says:

    Merv:The problem is the database publishing wizard is not seeing the SQL Express instance. The most likely cause is one of the following:1. SQL Express turned off2. Do not have the right protocol opened up on SQL Express (shared memory is the only one by default) 3. You named the SQL Express instance when you installed it rather than using defaultIt is also possible the SQL Browser service needs to be turned on. The .\SQLEXPRESS is a shortcut for (local)\SQLEXPRESS or localhost\SQLEXPRESS.If you never intend on installing SQL Server proper, you can also enable the TCP/IP protocol and set the IP address to 1433. Then you can simply use (local) or localhost. I would not recommend this direction if you have any intention of installing some version of SQL Server (other than Express) at some other time.Peace and Grace,Greg

  8. N says:

    Dave this helped me tremendously! Thank you. Now I am working through another error – that can possibly be resolved with GoDaddy technical support (haha!) . This is the error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

  9. http://tinyurl.com/400afelix23312 says:

    I personally was basically looking for creative concepts for my personal web site and
    found your own article, “Deploying a local database to your ISP (SQL Server Database Publishing Wizard) Stop Making Sense”, do you really mind in the event that I work with many of ur concepts?
    Thanks a lot ,Terence

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: