how to mirror two SQL server

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
arnel1257Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
imran_fastCommented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arnel1257Author Commented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
arnel1257Author Commented:
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
arnel1257Author Commented:
its ok now.. thanks anyway..
0
arnel1257Author Commented:
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
arnel1257Author Commented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
arnel1257Author Commented:
pardon my ignorance... but how do you create the service account??

is it under administrative tools in NT4??

0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
arnel1257Author Commented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.