Hi all,
i was wondering if for the use of logshipping two Enterprise Edition
versions of SQL Server are necessay or logshipping can also work
between a Enterprise Edition and a Standard Edition.
Does anyone know an url for me where i can read more about this. I've
found quite a lot of articles but not one telling me explicit that
logshipping does not work between an EE and a SE version.
Greetings,
Sjaak
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
Could somebody please guide me on how best to Implement SQL Server Log Shipping. I have gone through BOL without luck. Thanks.
View 2 Replies View RelatedI need to create a RO copy of a production DB owned by an outside company. We are connectd via a WAN link, but cannot use replication. They are proposing using an initial load via tape, and sending us a text file nightly with the days changes to the DB. We will then need to load that data using BCP, DTS or some other method. Does any one have any ideas on using log shipping instead of the text file. It would only be practical to get a fresh load of the entire DB once a quarter or once a month at most. It is a 40+ GB database and we are expecting 100 to 200 MB of logs per night. For business reasons, we are limited to some type of file transfer mechanism for the data transfer, and cannot really change their backup schedule which is nightly fullbackups and tlogs every 30 minutes.
View 1 Replies View RelatedCan anyone point out me , What does Log shipping mean ?
Is it backup of Log file ? or moving backup logs to any other destination ?
Thanks
Karthik
Hi all,
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
Any help is highly appreciated.
thanks in advance.
Minesh.
This might end up being fairly lengthy...I'm in the midst of implementing log shipping as a "warm stand-by" solution at my company. All the components appear to be in place: I'm using cmd shell to copy the backup device file to a remote server and then execute a RESTORE stored procedure on the remote server.
The copy and restore work just fine. The problem I'm having is with the transaction log dumps and restores.
We normally dump transaction logs (and then truncate) every hour. With the log shipping being implemented, we're going to want to do separate log dumps every ten or fifteen minutes, copy that dump over to the remote server, and then apply that log to the database.
Here's the question: for the log ship portion, I don't truncate the log. But after the "normal" log dump occurs, things get tossed out of whack. When you try to apply a log, I get the message "database has not been rolled forward enough....".
Has anyone encountered this type of issue and if so, how did you work around it? I'm assuming it's a simple of issue of certain options you set on your dumps and scheduling....
I'd appreciate any help....
Thanks!!!
Does anyone know if Log Shipping in SQL 2000 Ent also ships over database schema changes?
View 2 Replies View RelatedWe are considering implementing log shipping. Do the sql server logs keep track of the logs that are shipped and applied through log shipping? Or is there some other way to make sure that all logs have been shipped and applied?
View 1 Replies View RelatedI have been successful in getting log shipping working but still have some nagging questions that I cannot find answers to.
1. I had a situation where the copy for one TranLog took much longer than the 15 minute interval I have it setup for. It seemed to get stuck on that copy. Is that how it is supposed to behave.
2. Related to the question above, weekly, I have jobs that reorganize, check integity, recalc statistics. Would these jobs create very large log files? If so, how do others deal with this?
3. Is there any documents available that discuss testing converting your secondary server/database to your primary and back again?
4. Is there a way to setup Email notification to report out-of-sync conditions?
Any other gotcha's that people have run into.
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
Hi
While configuring log shipping, if i choose the "allow database to assume primary role" then the "ceate and initialise new database " option is selected by default..Does this happen all the time or am i missing something.What if i have already initialised the destination database.
Thanks
Madhukar Gole
Hi all,
I am testing my log - shipping strategy. I have tried with northwind database and it was successfully created and is operating. However in order to test I have created a new Test Table in the primary database to see whether it is working. From now on database shows that it has been loading and I cannot see any tables it is grayed and it says loading. What would be the problem? When I checked the logs it has been copying to the secondary database and it doesn't show any error in the log-shipping monitor. It seem everything is cool accept this loading part. If some one help me I really appreciate it.
Thanks
LS
:confused:
Hi all,
I am using SQL 2k EP Editions with SP2 on Win 2k Advance servers. Since more than week or so I am trying to establish log shipping between two servers. But its not working.
I am using database maintainence plan wizard to set up log shipping. Every thing works fine as far as wizard is concern, it creats plan for log shipping. But my log shipping is not working. The plan to back up log on source database is working fine. I can see the job history and the log files in the backup folder. But I have found that the job on the standby server to copy log file on network folder is failing and so the job to restore log on stand by server. I get the following message
"sqlmaint.exe failed with error state....."
Little reaserch on the standby server found that sql server is using maintainence plan to copy and restore log files, but i do not see any database maintainence plans on standby server as well as I have checked that there is no plan id in sysjobs table on either server.
I have sa rights. The account used by sql service and sql agent have admin rights and they do have rights to access the network folder for both the servers. So there is no rights problem.
I have followed all steps published in white paper for setting up log shipping on microsoft web site.
I have searched microsoft KB but it is of no use for sqlmaint.exe.
Any help is highly appreciated.
thanks in advance.
Minesh.
Hi All,
I failed to configure the Logshipping in
SQLServer2000, through database maintenance plan wizard,
at last step I got the following error.
Microsoft SQL-DMO(ODBC SQLState:42000)
Error 50007:xp_repl_encrypt:Error executing
srv_paramsetoutput
I am running SQLServer2000 Enterprise Edition with
Servicepack2, and Windows2000AdvancedServer with
Servicepack2 on both machines and bothe machines are in
the domain, both SQLServerservices are running under
Domain account.
thanks in advance,
Rob.
HI,
Is there any effect on taking full backups on Primary Server while log Shipping is in place? If we take a full backup is that applied to the secondary server?
thanks
We would like to use log shipping for a Near Real Time (5 min delay)reporting server. SQL 2000 requires exclusive use of the database when the logs are applied. This presents a problem since there may be a couple of hundred users on the NRT server. Even if they only make connections when running the reports there could be some contention issues. Has anybody used log shipping in this type of scenario?
View 2 Replies View RelatedIs it possible to log ship a database from server A (source) to server B (read only) when the database on server A is partly replicated (transactional repl) to several other servers ? All hints and advices are highly appreciated.
View 2 Replies View RelatedDoes anyone know why some databases in SQL 2K Enterprise are not available for log shipping? In other words when you select a database Log Shipping this option is not available (grayed out). Other DB's on the same server log ship without problem.
Any ideas?
I think Microsoft has an answer to this but their site is not working so I can't get to the Q270006 which supposed to discuss that. If you do a search in KB for "Log Shipping is disabled" you will see the WebCast link which references Q270006 but then if you click on the link to go to the Q link does not work and search Q270006 is not found in KB..
Hello,
to use Log Shipping solution, Entreprise Edition is required for both Primary and Secondry Server (you agree ?).
Do you have an idea about licence regarding the secondry Server (suppose that you have to switch 2 or 3 per year. The second server will be used 2 or three times per year).
In some plateform (xeon CPU), the logical number of CPU is 2 times the physical number (SQL Server See 2 CPUs. You have 1 physical CPU).
Do you have an idea about licence in this case ?
Thanks a lot ...
Hi,
I have following question ( same was asked to me by my collegue)
I have Log shipping between two servers ( say Server A - Primary & Server B - Secondary).
One user is logged in to B Server and fired a long query on the same database / object. it is taking a very long time to execute.
Q : How this will impact the log shipping process ?
Ans : Log shipping will fail as the secondary db will show as " database is in use" for log shipping process.
Q : How to recover the log shipping process?
And : Notify the user to disconnect the session / or kill the user session. But i don't know how to recover the log shipping.
Thanks in advance.
-- Chetan
All, I am new to log shipping and I am troubleshooting an issue with it. Basically somebody restored a backup from a day ago on the primary server, now the load is failing on the destination (obviously). I read that I should disable the jobs and backup and restore to the destination server to get it going again. I have some questions on this though, since the destination is a standby I read that I need to use the WITH STANDBY clause and specify a standby file name to use. Where do I find out the standby file name to use here and also is this the only way to get it back in synch?
Thanks in advance.
Future guru in the making.
I am attempting to set up log shipping from my primary dell clustered 2node sql server to a backup quad xeon server in case of a failure. When Ifollow the information given from Microsoft regarding this and I get tothe point where I am at "Add Destination Database" I am being promptedfor my server name, which shows the primary one ONLY. I dont seem to findany documentation on where I add in the secondary server name...Searching online has yielded no responses for me and MS only says toregister the servers... but where? and do I need to restart? I am onlytrying to be extra cautious as this is a live database.Thank you in advance for your help...
View 5 Replies View RelatedHello:I'm using MS SQL Server 2000 developer editionand trying to implement log shipping to the secondary server.I copied a database MDF and LDF files from primary to secondary serverand all data is there, but this database still in recovery mode so I can notapply log shipping to it.My question is:how to put the database on secondary server in standby or non-recovery modeto apply log shipping?Thanks,GB
View 1 Replies View RelatedI am trying to set up a dual log shipping configuration in SQL 2005 where I have one primary server to two secondary servers.
All of the following is being done from the primary server.
If I fill out the entire log shipping configuration window with both servers and then press OK, and let it all get set up, and then run the log shipping report, I only get status information for one secondary database - the other one is there but has no information available, yet the setups on both secondarys are OK and the jobs are succeeding on them.
If I then go back into the log shipping screen for my database and remove the server that I'm getting no status information for and press OK to actually remove it, then go back in and re-add it and THEN run the log shipping report I now get information for the server I just deleted and re-added, but no status information for the other server. If I look in the secondary database table, I only see a record for whatever secondary server is currently correctly showing status information.
When looking at the scripts generated by the log shippping screen for my setup, I noticed the stored procedure call below gets executed from the proc sp_delete_log_shipping_secondary_database that the script calls. This SP is only getting passed the database name, which is the same on both secondary servers and would therefore be the same for both servers' entries in the secondary database table on the primary server. Therefore, I think it is deleting ALL records in the secondary database table that have that name before re-adding the record for the one server and that's what's causing my issue, i.e. if my database is called "userdb" it is deleting all entries for "userdb" regardless of whether they are on the primary or secondary servers and then only adding one server back. Am I wrong?
exec @retcode = sys.sp_delete_log_shipping_secondary_database_internal
@secondary_database = @secondary_database
,@ignoreremotemonitor = @ignoreremotemonitor
,@overwrite = 0
Can some one help in understanding Log Shipping. My production database is over 50 GB and we want to implement Log shipping to ensure a standy by server. I know how to implement the Log shippinng and what it does but I am not sure how to perform Log shipping with daily FULL database backup on production server. Consider this scenrio.
1. 1:30 AM Monday Full database backup on production server
2. At 1 hour interval transaction backup on production server and applied on Standby server.
3.1:30 AM Tuesday Full database backup on production server.....
Here I am getting confused. DOo I have to apply this full back up again on standy servers, because if I don't then my LSN for next transaction log will be different.
So how can i take a full database backup of the production server while implementing Log Shipping.
Thanks,
Punu
I'm fairly certain that this posting should not be in replication, but here goes. I'm not a SQL expert, and I know just enough to keep things running. I'm looking to add some recovery options on top of my current backup to tape rotation for 2 core SQL servers. I setup log shipping and that appears to be working without an issue. I left it in standby mode instead of recovery and I also did a run through on switching the server where the primary db resides. That worked great and on the secondary data was up to date and all looked great.
Now I would like to test this process by taking my primary (test)SQL server down and force the secondary server to have the db available for read/write. Can anyone explain to me the best practies for performing the role change in this scenario?
SQL Server 2000 Enterprise running on Windows 2000 Server
Regards and thanks in advance,
Sean
Hi All.
We have configured log shipping on our SQLServer 2005 (SP2) servers and this has been working fine for the last three weeks.
The primary, secondary and monitor are all on different machines within in a group. A service account exists on each of the machines with the same username / password, and this is used for the folder shares etc.
On Saturday afternoon the monitor job started reporting that one of the backups was out of date and I then recieved over 900 failure email messages (one every 3 minutes!)
It seems that although all the jobs are running correctly on both the primary and secondary servers, the log_shipping_monitor_primary table on the primary server is not being updated.
Is there anything else I need to check for errors?
Also, is it normal to receive so many emails, ideally i would just get a mail the first time it noticed it had failed.
Thanks in advance
David
Can stand by server in log shipping host production database used by other applications? if yes, what is the effect of log shipping on other databases?
View 4 Replies View Related
Does anyone know the procedure (if it exists) to manually configure log shipping. I
Don€™t have SQL Enterprise Edition only Standard. Thanks for everyone being there
to help.
SQL 2000 Standard Edition SP4
My primary SQL Server box is 2005 Standard x64 running on 2 quad-core processors. I'm licensed for 2 processors.
I'd like to set up some kind of redundancy. The hardware I have available is about 4 years old and is currently running SQL Server 2000. Let's assume I upgrade the old box to SQL Server 2005 Standard. A couple of questions:
1) It seems the hardware gap between the 2 will make little difference in a log shipping. Please confirm this. Any other things that might be an issue?
2) Do I need lots of CALs on the backup box for Log Shipping? Or can I get by with 5?
3) I'd like to schedule log shipping pretty often--maybe every 5 or 10 minutes. Will this be an issue?
Thanks!
Hi everyone
In my project, sometimes, I need to run log shipping manually rather that it runs automally when the schedule occured.
manually run log shipping means call the log shipping in code.
Thanks.
Hi All,
Is anyone can tell me the difference between log shipping and clustering? I look at it and I think that this is the same thing as they use two different servers.
TIA