Getting a database on one server to match another

Hello,
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?One time only or regularly?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sorry wasn't too clear was I.
The second server is our disaster recovery server, so we
want our second server database to be as up to date as
possible with the production database.
ta
>--Original Message--
>Hello,
>We have two servers and want out production db on server
1
>to match the production server on server 2.
>Whats the best way of doing this?
>.
>|||Sarah,
Hari already answered this with:
1) Log Shipping
2) Transactional replication
If the disaster recovery server does not need to be operational, then log
shipping would be good.You may also want to consider Double-Take from NSI
Software. This replicates the SQL Server data files at the block level on
disk and ensures transactional consistency.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> Sorry wasn't too clear was I.
> The second server is our disaster recovery server, so we
> want our second server database to be as up to date as
> possible with the production database.
> ta
>
> >--Original Message--
> >Hello,
> >
> >We have two servers and want out production db on server
> 1
> >to match the production server on server 2.
> >
> >Whats the best way of doing this?
> >
> >.
> >|||Thanks Mark,
I'll have a look at transactional replication as we backup
the Production db by log files and to be honest I'm not
too sure on what will happen if we change it so it updates
asap.
Thanks for your time
>--Original Message--
>Sarah,
>Hari already answered this with:
>1) Log Shipping
>2) Transactional replication
>If the disaster recovery server does not need to be
operational, then log
>shipping would be good.You may also want to consider
Double-Take from NSI
>Software. This replicates the SQL Server data files at
the block level on
>disk and ensures transactional consistency.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
>> Sorry wasn't too clear was I.
>> The second server is our disaster recovery server, so we
>> want our second server database to be as up to date as
>> possible with the production database.
>> ta
>>
>> >--Original Message--
>> >Hello,
>> >
>> >We have two servers and want out production db on
server
>> 1
>> >to match the production server on server 2.
>> >
>> >Whats the best way of doing this?
>> >
>> >.
>> >
>
>.
>|||There's a web site at MS called something like "Pattern and practices". There you find a two boos on SQL
Server and high availability. You really need to go through that if you intend to use replication for fail
over. There are some issues you need to plan for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4ae801c42c41$337182f0$a401280a@.phx.gbl...
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> >--Original Message--
> >Sarah,
> >
> >Hari already answered this with:
> >
> >1) Log Shipping
> >2) Transactional replication
> >
> >If the disaster recovery server does not need to be
> operational, then log
> >shipping would be good.You may also want to consider
> Double-Take from NSI
> >Software. This replicates the SQL Server data files at
> the block level on
> >disk and ensures transactional consistency.
> >
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >
> >
> >
> >"Sarah" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> >> Sorry wasn't too clear was I.
> >>
> >> The second server is our disaster recovery server, so we
> >> want our second server database to be as up to date as
> >> possible with the production database.
> >>
> >> ta
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> >We have two servers and want out production db on
> server
> >> 1
> >> >to match the production server on server 2.
> >> >
> >> >Whats the best way of doing this?
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Sarah,
I really don't think transactional replication is a good high availability
solution. There are many drawbacks to using this. Do a search on the web for
high availability sql server and you should find some good information.
Kimberley Tripp did a great presentation on high availability, perhaps
search for that.
Log shipping might be a good way to go, you can have a latency of 5 minutes
with this solution.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4ae801c42c41$337182f0$a401280a@.phx.gbl...
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> >--Original Message--
> >Sarah,
> >
> >Hari already answered this with:
> >
> >1) Log Shipping
> >2) Transactional replication
> >
> >If the disaster recovery server does not need to be
> operational, then log
> >shipping would be good.You may also want to consider
> Double-Take from NSI
> >Software. This replicates the SQL Server data files at
> the block level on
> >disk and ensures transactional consistency.
> >
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >
> >
> >
> >"Sarah" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> >> Sorry wasn't too clear was I.
> >>
> >> The second server is our disaster recovery server, so we
> >> want our second server database to be as up to date as
> >> possible with the production database.
> >>
> >> ta
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> >We have two servers and want out production db on
> server
> >> 1
> >> >to match the production server on server 2.
> >> >
> >> >Whats the best way of doing this?
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks for your help guys
>--Original Message--
>Thanks Mark,
>I'll have a look at transactional replication as we
backup
>the Production db by log files and to be honest I'm not
>too sure on what will happen if we change it so it
updates
>asap.
>Thanks for your time
>
>
>>--Original Message--
>>Sarah,
>>Hari already answered this with:
>>1) Log Shipping
>>2) Transactional replication
>>If the disaster recovery server does not need to be
>operational, then log
>>shipping would be good.You may also want to consider
>Double-Take from NSI
>>Software. This replicates the SQL Server data files at
>the block level on
>>disk and ensures transactional consistency.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>
>>
>>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
>> Sorry wasn't too clear was I.
>> The second server is our disaster recovery server, so
we
>> want our second server database to be as up to date as
>> possible with the production database.
>> ta
>>
>> >--Original Message--
>> >Hello,
>> >
>> >We have two servers and want out production db on
>server
>> 1
>> >to match the production server on server 2.
>> >
>> >Whats the best way of doing this?
>> >
>> >.
>> >
>>
>>.
>.
>