Distributing Databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Distributing Databases

Author

Topic

tiamiyu
Starting Member

10 Posts

Posted - 2003-03-11 : 16:47:52

I have four servers that I am trying to distribut a copy of database to.

I have a processing server and three other production server. I want to be able to periodically update the production servers using a copy of the database on the processing server.

I am planning to write a procedure on the processing server that does the following:

1. Detach the database from the processing server
2. FTP the data files and log files to each of the server
3. Re-attach the database back on the processing server
4. From the processing server, detach each of the databases on each of the server and re-attach them using the new files

I know that steps 1 through 3 will work but I am not sure if step four will work.

What I am trying to achieve here is to make sure that I just one point of management and if four any reason, we add another server, we wouldn't have to change any process.

Anybody with a better idea should please run it by me

Thanks

Tiamiyu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-11 : 17:10:15

Why would you use the detach/attach method for this? Each time that you detach the database, it will be offline to the customers. Why don't you use replication for this instead?

Tara

tiamiyu
Starting Member

10 Posts

Posted - 2003-03-11 : 17:41:27

Thanks Tara,
The databases are all static. They are all updated at the processing server. So, there is no reason for using replication. Additionally, all the three production servers are just copies of each other and they will be load balanced! It is only one server that will be down at a time. So, customers will not notice this at all!

Thanks

Tiamiyu

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-11 : 18:02:06

But why take one down at all? Why not use replication or log shipping?

I think you will have to resync your logins and users each time you attach the DB.

-Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-11 : 18:37:12

But customers will notice it because when you want to bring the database online, there will be a timeframe when the database will be unavailable. Step 4 is where they will see the downtime.

You will definitely have to resynch the users each time you do this as Chadmat said.

Detaching/attaching is not meant to be used in this manner because each time you perform a detach, the database is taken offline.

Tara

Edited by - tduggan on 03/11/2003 18:41:03

tiamiyu
Starting Member

10 Posts

Posted - 2003-03-12 : 10:36:43

Thanks Tara and Chadmat for your contributions.

However, we are too worried about the window for which the databases will be down. These databases are just basically used for searching. There is no issue with user synchronization etc that you mentioned.

The way it works is if all the three servers are up, they will load balance one another. i.e any user that login will be routed to the less busy server. However, if two of them are up, thesame will still hold. Except that each of the two will be doing more works than usual. That is not a big deal anyway.

We have looked at the option of replication and log shipping and we don't think it is what we need for this solution. Reason being that it is not all changes that goes on on the processing server that will be pushed to the servers. Some cleanup etc has to be done before hand. It is not that straightforward. And secondly, these servers and not updated daily. They will be updated every month.

Anyway, if you have another idea, that will be great!

Thanks

Tiamiyu

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-12 : 12:39:40

You can do whatever you want, but you have been warned. That's not a good solution.

-Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-12 : 15:09:59

quote:

Anybody with a better idea should please run it by me

Just remember that you asked for a better idea and that's what we gave you.

Tara

tiamiyu
Starting Member

10 Posts

Posted - 2003-03-12 : 15:52:39

Thanks for your input.

Meanwhile, do you think this solution will work anyway!

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-12 : 15:59:51

From what you have described, yes this solution will work, although this solution is mostly used in non-production environments such as development.

When you perform step 2, just ftp the data file over. You do not need to ftp the log files over. So when you perform the attach, just use sp_attach_single_file_db. This stored procedure will create a log file for you.

Tara

tiamiyu
Starting Member

10 Posts

Posted - 2003-03-12 : 16:14:28

Thanks Tara.

What I was looking into as the hurdle is the ability to detach/attach a database on another machine. I want to be able to do all this from the processing server.

Does sp_detach_db assume that the database is on the machine I am running it from?

Thanks

Tia

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-12 : 16:27:07

Well if you are going to do this through a single stored procedure, then you are going to have to use linked servers.

Example:
CREATE PROC usp_DistributeDatabases
AS

--Detach database from processing server
EXEC sp_detach_db 'DBName', 'true'

--Detach database from destination servers,
--this has to be done before you copy the files over
--DestServer1 and DestServer2 are linked servers
--that were already setup
EXEC DestServer1.master.dbo.sp_detach_db 'DBName', 'true'
EXEC DestServer2.master.dbo.sp_detach_db 'DBName', 'true'

--copy files to destination servers
xp_cmdshell 'copy F:\MSSQL\Data\DBName_Data.mdf \\DestServer1\f$\mssql\data'
xp_cmdshell 'copy F:\MSSQL\Data\DBName_Data.mdf \\DestServer2\f$\mssql\data'

--attach database to processing server
EXEC sp_attach_db @dbname = N'DBName',
@filename1 = N'F:\MSSQL\Data\DBName_Data.mdf',
@filename2 = N'F:\MSSQL\Data\DBName_log.ldf'

--attach database to destination servers
EXEC DestServer1.master.dbo.sp_attach_single_file_db @dbname = 'DBName', @physname = 'F:\MSSQL\Data\DBName.mdf'
EXEC DestServer2.master.dbo.sp_attach_single_file_db @dbname = 'DBName', @physname = 'F:\MSSQL\Data\DBName.mdf'

Tara

Edited by - tduggan on 03/12/2003 16:27:59

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-12 : 16:29:25

Yes it does. You *might* be able to get it to work via a remote server or linked server, but I wouldn't recommend it. It'll probably be fine but IMHO it's like starting your car remotely and having it drive over to pick you up automatically...a LOT can go wrong and can create a lot of havoc without two hands on the wheel.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-12 : 16:35:10

As robvolk mentioned, this is not recommended. I provided a solution (well haven't tested it), but I definitely would not recommend going down this path. One of the problems that you are going to encounter is that you cannot detach a database when users are connected to it. So you are going to have to loop through all connections to the database and KILL them.

Tara

tiamiyu
Starting Member

10 Posts

Posted - 2003-03-12 : 16:36:19

Thanks Tara

That was a wonderful suggestion from you

Anyway, I appreicate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources