October 26, 2011 at 5:48 pm
Thanks for everything,
I don't know if my process is perfect but it works and I just want to share it.
1.- Make a full backup from source DB (remote instance) and restore it in destination with NORECOVERY mode (local instance) in my case because I don't want to do it in my logshipping process by the big size of my db.
2.- create a service domain account (DOMAIN\servicesql) and add to "Administrators" group
3.- Add this user and password on service properties "Log On" - (SQL server Agent (MSSQLSERVER)) and restart the service.
4.-Create a folder in source instance (OUT) and add "change" permissions for this new user. Repeat this process for destination instance in a new folder (IN)
5.- Add this new login in each SQL instance with DBO permission in both DB
6.- Built a LogShipping process from source instance
7.- Check the 2 new LogShippin Jobs in source and destination and according to the schedule and check the TRN files in each folder
8.- Check the status by right click on destination instance - Reports - Standard Reports - Transaction Log Shipping Status.
Good Luck and thanks again.:-P
October 26, 2011 at 5:58 pm
Bye.
October 27, 2011 at 6:28 pm
Thanks for sharing your production LogShipped schedule, I just have a question with your Log Shipping schema. the Job backup (every 15 mins) job copy (every 15 mins) and Job Restore (Every 15 mins starting from 22:00 to 06:00).
I understand that point because in case of disaster you have all TRN files in destination folder and you can apply all TRN files in ascending order like:
RESTORE LOG DBNAME FROM DISK='F:\LOGSHIP_IN\DBNAME_20111027170000.trn' WITH RECOVERY
But, Imagine the amount of TRN files until 22:00 that Job Restore began
¿What if you have 100s or more TRN files to apply?
When I try to apply all trn files from graph interface I get the error: the volume on device F:\Folder\TRN_File1.trn is not part of a multiple family media set.
¿is there a way to restore all TRN files in order to optimize the time with simple restore?
Thanks for sharing your experiences.
October 28, 2011 at 6:04 am
So, I think that your question is, how do you restore the log files to the offline copy when the restore job is turned off during the day? Currently the restore job is turned off during production hours, 14 hrs * 4 logs per hour = 56 log files. The creation of the log shipping "wizard" on the primary database, creates 2 jobs on the secondary server: a copy the logs from the primary server to the secondary server, and a restore the next log file job. The copy jobs, essentially does that, with lots of error checking thrown in. The restore job looks at the earliest log file in the directory it is told to look in, checks to see if the file is the next file to apply, checks to see if it has already been restored, and either skips the file with an error condition if the file is too late, (there is a hole in the log file chain, you're missing a log file(s) ),or skips the log file because it has already been restored manually, or restores the log file and then does the process again until all of the log files are restored. Restoring the log files can be done at any time that you want to inturrept access to the database by starting the log-shippin restore log file job. I suggest that when you have your log shipping set up and running that you run through the history and examine each of the job steps to see what is happening in the job. The log shipping jobs as generated by SQL Server are very well thought out and very robust, forgiving many faults that may occur.
October 28, 2011 at 8:50 am
Ok, imagine you have a disaster in (source database) what procedure would you apply in destination database for leaving ready to use it.
thats my first time and I was thinking about this steps (Destination Instances)
1.- Check the last TRN file applied to my Standby/Read-Only Database
2.- Check how many files need to be apply to the database
3.- Execute/force the Job_Restore for apply the rest of TRN Files
4.- Open a new query and execute RESTORE DATABASE DBNAME WITH RECOVERY
for leave ready to use it.
let me know about your real experience
October 28, 2011 at 9:00 am
Steps for failover:
Run the copy job and also Run the restore job at the same time. Ensure that there are no more files to copy or restore.
run restore with with recovery.
These jobs that are set up work. Why not use the wheel that you already have instead of making a new one:-)
October 28, 2011 at 9:37 am
Thanks for everything.
Regards.
October 28, 2011 at 9:50 am
willian.funes (10/28/2011)
Ok, imagine you have a disaster in (source database) what procedure would you apply in destination database for leaving ready to use it.thats my first time and I was thinking about this steps (Destination Instances)
1.- Check the last TRN file applied to my Standby/Read-Only Database
2.- Check how many files need to be apply to the database
3.- Execute/force the Job_Restore for apply the rest of TRN Files
4.- Open a new query and execute RESTORE DATABASE DBNAME WITH RECOVERY
for leave ready to use it.
let me know about your real experience
read my last post.
---------------------------------------------------------------------
November 1, 2011 at 12:40 pm
Hi, the first time I did setup the logshipping process, the destination database changed to StandBy/Read-Only after the first restore was applided and it was nice because users can make select to tables and others. ¿what moment change state from Restoring to StandBy/Read-Only?
mi question is because I did repeat all logshipping process with another DB and the logsshipping process work fine but this DB never change state to StandBy/Read-Only and keep it on "Restoring" state..
¿would you mind explaining me the diferences?
My logshipping process is working now with the next schedule:
Backup Job: every 15 mins from 02:00 to 23:59
Copy Job: every 15 mins from 02:00 to 23:59
Restore Job: every 15 mins from 22:00 to 06:00
November 1, 2011 at 12:51 pm
The choice to have the log shipping be standby or restore mode is made back when you are initially setting up log shipping. It looks you need to look at the Secondary Server setup.
November 1, 2011 at 3:56 pm
You're right the last step when you setup the Restore_Job say "StandBy mode" could be an little mistake... I've set again the database and I hope it work.
My logship procedure include the firs backup and restore (with no recovery) manually and I hope it were not the error reason.
Thanks again.
November 1, 2011 at 4:22 pm
I just have finished the new logshipping process with the "StandBy Mode" choice at the end but when I force the "Restore_Job" just for apply the trn backup the database don't change to "StandBy/Read-only mode".
In my process first I make a backup file from source database
next I restore this backup in destination instance "with no recovery" option
and finally I setup the logshipping from source database with "StandBy Mode" and the end of process when I set the "Restore_Job"
¿what would be the problem?
November 2, 2011 at 5:41 am
try refreshing the SSMs display (right click on database and select 'refresh'
---------------------------------------------------------------------
November 3, 2011 at 9:31 am
I have noticed the problem occurs when I define the job schedule for restoring database since 22:00 to 06:00 and the backup_job and Copy_job in 15 mins from 12:00 am to 11:59 pm, because when I set the logshipping by default with 15 mins for each one the process change the state correctly to Stanby/Read-only.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply