Moving a SQL Server 2005 Database from the local network to an online server.

Hi All, first post.

I have done a bit of searching around and cant find a clear answer to this question.

Current Setup
Desktop application (c#) that connects to a SQL Server 2005 express database on the same local network as the application (currently 3 users)

It is only a very small company and has just taken on their first remote worker, but expects to take on another 6-8 over the next few months. They have asked for the database to be moved online.

The application was written in such a way that everything has been done using no stored procs, or views, it is all native SQL.

This will be my first DB hosted online and before I go ahead and do anything I just wanted to make sure what I have to do is correct, sorry if this is a very basic question, although I have been programming for a long time, I have never had the chance to do any online databases before.

Will this work.
1.Find a SQL Server 2005 Hosting company.
2.Move the database to the server.
3.Setup the users permissions.
3.Alter the connection string in the application to point to the new location.

So the only thing that would change would be a new connection string in the application preferences?

Or am I living in a dream world, because nothing is ever that simple.

One thing I am worried about is the security/visiblity of the database and data as it travels from the server to the client and back.

Thanks for any advise you can give.

Markhmmm I thought this would be something alot of people would have done before.|||It's not something done. You don't want to access any DB directly over the internet. Many bad things can happen.

You have several better options.

1) Setup a vpn. No exactly simple, in many cases, but should be doable. There's free utilities, and all traffic is encrypted.

2) Use a multi-tier driver solution that allows some level of access control and encryption.

You may have real difficulty in connecting. Due to some serious goofs in SQL Security, there were some vicious worms that targetted MSSQL, to the point where many networks block the well known ports.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

Move entire SQL Server instance

I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.

Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?

This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.

Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?

Also, are there any issues going from Windows Server 2000 to Windows Server 2003?

Thanks in advance.gee, why would you want to move temp? most people would not need to move model either.

I would...

1. backup your source user databases.
2. restore them to the new server.
3. migrate what you need from master and msdb and you can do some of this with DTS I believe and you can have EM script some other things. It really depends on your needs.|||You can also backup and restore the msdb database, if you have large numbers of jobs and/or DTS packages (shudder) to move. You will have to go in and update the sysjobs table, so the SQL Agent on the new machine does not think it is hosting jobs from a master job server, though. There is no good way to go through jobs and DTS packages for all the hard-coded server names, though.|||I've migrated lots of instances (from non-clustered to clustered, from old h/w to new) and have never attempted to restore a backup of master, model or msdb.

- Most of master gets updated when databases are restored/attached.
- Jobs can be scripted (keep new db names the same as old)
- DTS packages can be backed up using a script (and moved using a variation of the same script)

I'm always leery of restoring master, model or msdb because you might get some ugliness in there as well.

Just my $.02.

Regards,

hmscott|||something else that has worked in the past ...
1. Build out your new server.
2. Stop sql server on both old and new servers.
3. Rename the master mdf and ldf on the new server
4. Copy the master mdf and ldf from the old server to the new server in the location specified for the new server
5. Fire up the new server.
6. Modify sysservers for the new server name (unless you will be using the same server name).
7. Use microsoft methods for moving temp and restoring msdb
8. Detach and reattach the user database files, or backup and restore the user database file, whichever you prefer.|||I'd take hmscott's advice:
- Install the new instance
- restore all user databases
- Script everything else you need from the old instance

Because:
- Master: only contains the logins needed, just script them. If you use the same sid's in the script you don't even have to use sp_change_users_login (when you only have Windows Authentication you don't even have to do that)
- Model: most people don't do anything special with model and leave it standard. If you have customized stuff in there do a compare and script it.
- msdb: script (jobs, DTS, etc)! If you really want to, this is the only one I'd restore (prepare for some problems though).
- tempdb: don't touch it, it's useless to try. Each restart will empty tempdb so there's nothing in there to re-use. If there is, you're doing something wrong.|||Thanks for all the insight, in case you couldn't tell this is my first migration and I'm just trying to make sure I don't miss anything. I'll let you know what I did and how it turned out.|||Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?|||that's correct|||Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?
Why wouldn't you want to install SP4?

Btw: if the old server has logshipping, replication or linked servers don't forget to script or rebuild them too. They're also in master...|||I probably will install SP4 on the source. I was just wondering because the source server is going to get wiped out and have a clean install of Windows Server 2003 and new installation of SQL anyways.

No logshipping, replication or linked servers to worry about.|||Sorry, I read your post too quickly. You said source-server not target :o

You do not need to install SP4 on the source.