publishing SQL 2005 Database to Remote Host

Question

Hi, I've made an ASP.NET 2.0 website and it uses two databases namely aspnetdb and my_db.I ran the aspnet_regsql.exe -E -S localhost -A mr tool and I got the aspnetdb.mdf in my project.I used it for providing login features to my site. Now i want to publish the site to a remote hosting server which I've purchased. They have provided me with the details of the server to which I must connect, and I was successfully able to do so. Now the scenario is this:-

1)My site supports a single MS SQL Server database, and I'm using two databases. How can I merge these two databases into one, and also update all pages with the change occuring in accessing the corresponding database, and then publish it.

2)I want to rename the aspnetdb to some other name, as I'm getting error when I publish the database with the name aspnetdb to the server, (it says the database already exists, but I was not able to find it). So how can I effectively rename it.

3)How to publish the merged database correctly to the remote location, do I have to take the connection strings into account as well? So far since I was the local user, I didn't provide any username and password, but on the site I'd to create a user for the SQL server. So how can I integrate it securely into my project.

Hope I've explained my problem properly, waiting for some solution.

Thanks

Answer 1

man, best tool that has changed my life for this is Redgate Data Compare. it will check both DBs and synchronize anything in them, plus it gives you the SQL Script for that too.

hope this helps.

Answer 2

Got a few questions in here, so I'll break them each out.

1)How can I merge these two databases into one

Devmania mentioned Red Gate Data Compare, so I'll mention Quest Toad for SQL Server, which also does the same thing. Disclaimer, though - I work for Quest. You can download the v4.5 beta, which is free right now, and it has the data & schema compare functions.

and also update all pages with the change occuring in accessing the corresponding database, and then publish it.

Unfortunately, nobody's tool is going to change your code.

2)I want to rename the aspnetdb to some other name, as I'm getting error when I publish the database with the name aspnetdb to the server, (it says the database already exists, but I was not able to find it). So how can I effectively rename it.

Go into SQL Server Management Studio, connect to the database server, right-click on the database name, and click Rename.

3)How to publish the merged database correctly to the remote location, do I have to take the connection strings into account as well?

Check with your hosting provider, because not all of them will allow SSMS access remotely. If they do, then you'll need to script out your database and then apply it to the remote database. Toad includes the ability to script out the schema and data inside your database.

So far since I was the local user, I didn't provide any username and password, but on the site I'd to create a user for the SQL server. So how can I integrate it securely into my project.

In SQL Server Management Studio, go into the database, Security, Logins. You can right-click there, create a new login, and use SQL Authentication. Specify the username and password, and you can give the login just the permissions you want. I would use a separate one for your web application that only had db_datareader and db_datawriter, not db_owner. Owner has the ability to change the schema, and your app shouldn't be doing that. In case you get hacked and somebody steals the username/password, at least they won't change your schema.