[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to mirror two SQL server

Posted on 2006-04-19
14
Medium Priority
?
381 Views
Last Modified: 2012-06-21
Hi experts!

I have two SQL server .. Main data server and Back up data server.

both are running on Windows Server 2003 and having SQL 2000.

What i want to accomplish is to have minimum downtime .... meaning if my Main data server fails .. i can easily point all my client to the back up data server ... with out losing any transactions.. in other words can i have them mirrored like in novell ...

right now, i am just relying on the backup file that we are running every morning in the Main server and restoring it takes about 30 mins... if my main server fails in middle part of the day.. all the transactions that were added in the main server that day will be lost..    

any suggestions? .. im looking at the DTS local package, is this ok?  a step by step process on how to go about this, will be highly appreciated.

before i was testing the replication features of SQL..  but it is very complicated.. and one time i was able to make it work... but only for a few days.. it just stop with an error in the SQL agent .. finally i abandoned the idea.

thank you in advance.

arnel
0
Comment
Question by:arnel1257
14 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 16487599
what kind of replication you were using.

Should use transactional replication but if you are doing any schema changes you should  reinitialize that replication or do use special stored procedure to make schma changes (adding a column)

Also make groups for all you tables dont make one publication for all tables that is bad idea. Instead use multiple publication with few tables.
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 16487942
Simplest way is log shipping..here is a little step by step for basic log shipping...

> Note the IP's and NAME of the production and failover server (ex: 192.168.10.100/SQL1 and 192.168.10.150/SQL2)

On the DOMAIN Controler
---------------------------------------------------------------------------------------------------------
> Create a service domain account (serviceSQL) on which all SQL binaries can run (SQL Server and SQL Server Agent).


On the FAILOVER Server
---------------------------------------------------------------------------------------------------------
> Create and Share a folder on the backup server to receive the backups
> Give all rights to write and delete content on the shared folder to serviceSQL
> Create a complete restore procedure.  
        Ex:
        create procedure spr_restore_full_xxxx
        as
        begin
                restore database xxxx from disk = 'F:\BACKUP\FULL\xxxx.BAK' with
      replace, standby = 'E:\undo_xxxx.LDF'
        end      
> Create a complete apply/restore logprocedure.  
        Ex:
        create procedure spr_restore_log_xxxx
        as
        begin
             restore log xxxx from disk = 'F:\BACKUP\LOGS\xxxx.BAK' with standby
             = 'E:\undo_xxxx_log.LDF'
        end  

On the PRODUCTION Server
---------------------------------------------------------------------------------------------------------
> Create a linked server pointing at the FAILOVER Server so that you can call procedure remotely on the failover server.(Ex: call it SQL2)
> Create a job running once a day with the following steps:
               > STEP 1: backup the database you want to mirror to local disk (ex: backup database xxxx to disk = 'F:\BACKUP\FULL\xxxx.BAK' )
               > STEP 2: once backup is completed, copy the backup from the production server to the shared folder on the backup server (ex: xcopy F:\BACKUP\FULL\xxxx.BAK  \\192.168.10.150\F$\BACKUP\FULL\ /c)
               > STEP 3: Call the remote procedure for running the remote procedure on the FAILOVER Server.
               Ex :
                exec [SQL2].master.dbo.restore_database_full_xxxx
               > STEP 4: Clean both copies of the local and remote backup made
               exec xp_cmdshell 'del \\192.168.10.150\backup\FULL\xxxx.BAK'
               exec xp_cmdshell 'del F:\BACKUP\FULL\xxxx.BAK'

> Create a job running each 10 minutes with the following steps:
               > STEP 1: backup the database logof the db you want to mirror to local disk (ex: backup log xxxx to disk = 'F:\BACKUP\LOGS\xxxx.BAK' )
               > STEP 2: once log backup is completed, copy the backup from the production server to the shared folder on the backup server (ex: xcopy F:\BACKUP\LOGS\xxxx.BAK  \\192.168.10.150\F$\BACKUP\LOGS\ /c)
               > STEP 3: Call the remote procedure for running the remote procedure on the FAILOVER Server.
               Ex :
                exec [SQL2].master.dbo.spr_restore_log_xxxx
               > STEP 4: Clean both copies of the local and remote backup made
               exec xp_cmdshell 'del \\192.168.10.150\backup\LOGS\xxxx.BAK'
               exec xp_cmdshell 'del F:\BACKUP\LOGS\xxxx.BAK'

For doing the failover on the failover server just run this...
use master
go
restore database ARSystem with recovery
then run this to avoid orphan users...

use xxxx
go
exec sp_change_users_login 'auto_fix','xxxxlogin' (xxxlogin stand for all the important logins used by your application)...

Then you just need to point out your applications on the new server.  As soon as your PRODUCTION Server gets back on the network, you can either decide to remain on the FAILOVER Server on which case you MUST DISCONNECT your Synchronisation *OR* you can choose to get back to your original configuration...

This procedure can be improved but it should get you started...Hope this helps...
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:arnel1257
ID: 16488759
i would like to thank angelIII also, unfortunately i didnt see his post right away.. otherwise i would have split the points.. that link is a big help..
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16499700
Yes the link posted by angelIII may prove helpful ...If I am not mistaken there should be way to split points back..Just look at the help section.  There are however issues that are not dealt with in the link provided (such as orphan user once standby db is up)...Between the two sources, you should have sufficient information to start with...Good luck...
0
 

Author Comment

by:arnel1257
ID: 16505820
hi! racimo..

im having trouble in the create a link server step you gave..

how do you point the failover server to the new link .. which i called SQL2.. under SQL2 .. i can see only table and view icon..

im using enterprise manager..  
0
 

Author Comment

by:arnel1257
ID: 16505870
its ok now.. thanks anyway..
0
 

Author Comment

by:arnel1257
ID: 16506850
hi racimo..

its me again.....   i created a job in the production server

step 1 backup the database is ok .. i was able to run it with success..

step 2  (i thought this one is easy) copy the backup file from production server to back up server.... its giving me job failed.

this is the script:
xcopy c:\fullpathofmybackupfolder\xxx.bak \\fullpathofbackupserverfolder\ /c

i tried running it in the command prompt .. and i was able to copy it without any problem..

right now im stuck up with step 2.. can you help me pls...or anybody??




 
 

0
 

Author Comment

by:arnel1257
ID: 16507202
im using on both server - domain\administrator user name.

i think i skip the first part which is create a service domain account (serviceSQL)

how do you do this?? im using Win Nt4 server as my Primary Domain Controller..
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16507291
i think i skip the first part which is create a service domain account (serviceSQL)
how do you do this??

On the domain controller
1) Create the service account

On the the machine running the service
1) Grant Local Machine right to the account you created
2) In Local Security  Settings, grant to possibility for the account to run services (else the service will stop as soon as the session is disconnected)
3) Change the account on which the service i running and restart.
 
0
 

Author Comment

by:arnel1257
ID: 16507400
pardon my ignorance... but how do you create the service account??

is it under administrative tools in NT4??

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16507505
<<is it under administrative tools in NT4?? >>
Theoint is to create a domain user account dedicated to run services (you shall grant R/W rights to that account over shared folder in the standby server to copy the backups)... I am not certain on now to do that under NT4, you may want to ask the question in the appropriate section...I know that this is possible and easy to set up under Active Directory but I do not know how to get the same thing under NT4...

I know this looks tedious but it's not as bad as it seems...(and that's the kind of practical information that lacks in the sql-server-performance procedure posted)...Good luck with the rest of the procedure...
0
 

Author Comment

by:arnel1257
ID: 16523075
hi racino..

its mee again..

after running the procedure spr_restore_full_xxxx.. my database becomes read only.. now everytime i will run the procedure after copying the back up from the production server.. the job fails.. did i miss anything??

btw, i dont know if it has something to do with what happen..  just for testing i run the procedure in the stand by server.. and not in the production server..

i will wait for your reply.. many thanks!
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16527851
<<after running the procedure spr_restore_full_xxxx.. my database becomes read only..>>
This is normal.  To "activate it",you need to run  "restore database XXXX with recovery".  As  long as you don't actually perform the failover this is normal...

<< now everytime i will run the procedure after copying the back up from the production server.. the job fails.. did i miss anything??>>check if the cleanup is all right..If needed delete manually the previous execution backup files on both the production AND failover then run the job again..Make sure the cleaning is done al right during execution time...The job should overwrite the previous executions on failover server UNLESS something prevents to do the backup on the producton server OR the copying on the failover server...Hope this helps...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question