SQL2005 Log Shipping Roll Change Where Secondary Is Readonly Standby
Dec 13, 2006
Hi,
I am trying to implement a log shipping scenario in sql 2005 where the secondary server is in standby mode with the ability to roll change during failover.
With the help of BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2d7cc40a-47e8-4419-9b2b-7c69f700e806.htm) I can implement my scenario in Recovery mode, but not in standby mode. I use the following sql to put my primary in standby
BACKUP LOG [database]
TO DISK = @filename
WITH STANDBY = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupROLLBACK_UNDO_database.BAK'
GO
which works, but then my restore job fails on the last step. How can I put my primary db in standby mode in such a way that the log shipping restore will work?
Thanks
Andy
View 6 Replies
ADVERTISEMENT
May 12, 2008
We are using MSSQL 2005 and Log Shipping.
After making our secondary SQL server primary, how can we put the secondary SQL server back into standby mode?
View 1 Replies
View Related
Jul 19, 2006
Any idea to copy a standby/readonly database. We a have a standby/readonly DB from logshipping for hot standby and we would like to make a copy of this standby/readonly DB into a read/write DB in the same server for daily testing. Thanks in advance!
View 6 Replies
View Related
Jan 14, 2008
Hello
We have set up Log shipping between Primary and Secondary DB. The secondary DB is right now option: Standby/Read-Only. I can not take Backup of Secondary DB now.
Shall we disable Log shipping and change the DB Option to Multi-user mode and take backup? or any different method, without disabling log shipping?
please advice. Thanks in advance.
Jay
View 1 Replies
View Related
Nov 11, 2007
Hi all,
I have a question about log shipping and assembly on the secondary server. I have configured log shipping to the secondary server and the secondary server in standby/read-only mode. I have test everything for reading from that database on the secondary server; and all ended with great seccess, except for assembly.
My assembly simply turn bytes into text or the other way using UTF-8 encoding, nothing serious, and I have checked that assembly on both server have the same thing every where. However, I continue getting the error:
Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65574. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'asp.clr, Version=2.5.0.1, Culture=neutral, PublicKeyToken=9ed52bcb68360e98' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
I have tested this assembly on the secondary in a read-write database, and it works fine. I can see both assembly sharing the same same thing ...
Is there a limitation for read-only database on assembly using?
Thanks
View 1 Replies
View Related
Jul 31, 2006
Hi,
I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error:
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupLogShipCI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind of defeats the purpse of having it in read only mode so that data can be accessed? Couldn't really depend on it as a stable source of data for running data extracts? I suppose it could all just come down to timing everything in the appropriate sequence? Anyway.....
To sum up:
Is there anyway to access the standby server without causing the restore process to fail?
Both the Primary and Secondary are SQL 2K5.
Thanks!
View 9 Replies
View Related
May 29, 2008
hi all,
For data ware house project, the reporting team needs to know the delta changes to the master database.
one way we were thinking was to use log shipping and run reports / ETL off the secondary server. But the team needs to know which records got changed and i was thinking of adding timestamp columns to the necessary tables (only on the secondary database schema) and that way we can track the changes.
But from my research, it seems like secondary database needs to have similar schema as promary database.
Is log shipping, can my secondary db have a bit different schema? if so how to do it?
If not, how to accomplish the above secanrio, with out adding new columns (if possible) in the master database and with low over head.
thanks
lucy
View 3 Replies
View Related
Oct 2, 2006
Hi,
We have SQL Server 2005 configured with mirroring to protect from physical errors. We also have a need for an (out of sync is ok) reporting server and we'd like to reduce our downtime in the event of a logical error.
The primary database is already being backed up (full and t-logs) to a shared network drive.
Can I implement the second half of log shipping (i.e. the stuff you do to the secondary) so that I don't have to change the current backup schedules on the primary server?
Specifically in the list of sp's below, can I start halfway down at sp_add_log_shipping_secondary_primary (Transact-SQL) ?? Without having to run the primary sp's?
sp_add_log_shipping_primary_database (Transact-SQL)
sp_add_jobschedule (Transact-SQL)
sp_add_log_shipping_alert_job (Transact-SQL)
sp_add_log_shipping_secondary_primary (Transact-SQL)
sp_add_log_shipping_secondary_database (Transact-SQL)
sp_add_log_shipping_primary_secondary (Transact-SQL)
Or do I have to ditch my current backup maintenance plans on the primary and start again?
Thanks
Ed
View 1 Replies
View Related
Jun 26, 2007
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
View 6 Replies
View Related
Sep 5, 2015
I want to configure the logshipping to secondary database.
Already the primary database transaction log backup running every 15 minutes through (maintanance plan)
I want to skip the transaction log backup job in logshipping configuartion(disable the job)
want to use the existing transaction log backup and copy and restore in secondary using logshipping configuration
is it possible in sql 2008 r2 standard edition.
View 1 Replies
View Related
Apr 23, 2008
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
View 3 Replies
View Related
Dec 29, 2007
I made B server which get logs from primary server A as a secondary server in the log shipping solution.
it always shows RESTORING in B server, it seems not to accessible.
my question is <if A failed down , how to revoke the B server as the primary one>
View 3 Replies
View Related
Feb 13, 2008
I have setup Log shipping between two SQL 2005 servers, and everything seems to be working well. The files are transferring and restoring correctly.
My question is whether I need to add any backup procedures for the secondary server to prevent the secondary server's log file size from growing continuously. Should I be doing a transaction log backup on the secondary server? Or will that break the Log chain?
If it makes a difference, the secondary server is in Standby mode after applying the logs.
Any advice would be appreciated,
~ Michelle
View 4 Replies
View Related
Nov 6, 2006
We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.
I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.
When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.
The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.
Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:MSSQLBackupMyDemoMyDemo_20061106115619.trn'
Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0
Any ideas or suggestions?
View 3 Replies
View Related
Jun 3, 2015
I checked the server and found that LS restore job failing and Backup and copy jobs running fine without any issue. and also observed that Copy folder the trn file existing on secondary server. i try to restore trn file im getting the error. and observed that last log backup file that it restored at the secondary database on May2nd,2015.
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
From Restore job histort details below.
Message
2015-06-02 12:25:00.72*** Error: The file 'xxxx\_20150530104503.trn' is too recent to apply to the secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2015-06-02 12:25:00.73Searching for an older log backup file. Secondary Database: 'database'
2015-06-02 12:25:00.73*** Error: Could not find a log backup file that could be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.74Deleting old log backup files. Primary Database: 'database'
View 3 Replies
View Related
Apr 19, 2006
Dear All
Please I need an urgent help
After i finished all Transaction Log Shipping Configuration.
I tried to use the database in the secondary database but i couldn't access it
i saw it in SQL Managment Studio as (Restoring......)
i tired to make a database snapshot from it , i had a message
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Please urgently
View 1 Replies
View Related
May 5, 2015
We tried to configure log shipping using script generated by GUI and when executed specific script which is meant for secondary server the database is not created and threw below error.
Msg 15010, Level 16, State 1, Procedure sp_add_log_shipping_secondary_database, Line 50
The database 'BUBALLO' does not exist. Supply a valid database name. To see available databases, use sys.databases.
Note: Only Copy, restore and alerts jobs have been created.
The account I'm trying to configure log shipping is the service account by which SQL and agent services are running and folder in where data and log files are intended and to be created is open to all (everybody has read/write permissioins) believe the issue is not with permissions.
View 4 Replies
View Related
Mar 17, 2014
I've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.
Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).
This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Is this just cosmetic?
Here is an Example of how the Studio shows the incorrect path for the secondary.
Example:
(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,
Log shipped to
(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR
View 2 Replies
View Related
Apr 24, 2007
I set up Log Shipping with just 2 servers primary and secondary. When I run from the Wizard for the very first time keeps failing at the stage of saving Secondary Server Configuration info. When i instead run the generated script this problem disappears but then restoring of transactions fails - the process can backup transactions from the Primary server , copy them accross to the secondary and fails on the restore. Any ideas why.
View 6 Replies
View Related
Jun 18, 2015
I received an alert from one of my two secondary servers (all servers are running 2012 SP1):
File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.
The detail in the job step shows this additional information:
*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.
I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.
View 0 Replies
View Related
Oct 2, 2014
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
View 2 Replies
View Related
Nov 14, 2007
I've been reading a million and one posts on replication
My scenario is that i have a live SQL 2000 server. In a DR invokation, i'e i've lost my live sever, i want to be able to access the same data at the DR (SQL 2000) site and have it accessable to the users. DR server has a different name to the live box.
Replicate
Master
CRMDatabase
Data changes all the time but can have hourly replication of transaction logs for this example. I've currently researched a sp called update logons but this has to be fed each account name to enable them on the new server. There must be a way to activate all CRMDatabase logons with the new server?
Could someone be kind enough to lead me through a step by step guide on the best solution.
Thanks a lot
David
View 3 Replies
View Related
Nov 9, 2015
I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??
View 3 Replies
View Related
Apr 20, 2015
I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.
Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?
When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?
View 3 Replies
View Related
Jul 20, 2005
The pimary server has crashed. I couldn't able to runsp_change_primary_role procedure. I ran sp_change_secondary_role insecondary server which is giving errors. sqlmain.exe failed. Anyideas?
View 9 Replies
View Related
Sep 25, 2015
I want to redirect the logshipping primary backup folder to another drive, how to change the configurations steps to move the primary logship folder to another location within the same server!
View 2 Replies
View Related
Jan 24, 2015
i have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?
View 2 Replies
View Related
Jun 26, 2007
Once the SQL2005 cluster is installed can I change the "physical" server name of the 2 server nodes in the cluster. I do not want to change the virtual server name but the "physical" Windows server name....
Steve Dunn
sdunn@esiindy.com
View 1 Replies
View Related
Mar 30, 2007
I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;
The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;
"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"
TOM will be setup with db roles as well
When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.
"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"
At this point I get an error:
RunTime error -2147217900 (80040e14)
ODBC SQL Server Driver][SQL Server] Cannot alter
the login 'TOM', becuase it does not exist or you do
not have permission.
Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?
Thanks in advance for your help.
CH
View 7 Replies
View Related
Nov 19, 2007
Hi,
The standby file (.tuf file) got deleted accidentally on the secondary. How can I recover the database without the standby file?
View 3 Replies
View Related
Nov 29, 2006
I have downloaded and installed ASP.NET Ajax Sample applications from
http://ajax.asp.net/default.aspx?tabid=47&subtabid=471
I am trying to run the AJAX TaskList example under C:ProgramMicrosoft ASP.NETASP.NET AJAX Sample Applicationsv1.0.61025TaskList
First I moved the content of the TaskList folder to a virtual IIS directory, making it possible to debug the website on my local IIS server.
When I run the example I get prompted to Login or register as a new user. When I submit the registration form I receive the following error message:
Failed to update database "C:INETPUBWWWROOTTASKLISTAPP_DATAASPNETDB.MDF" because the database is read-only.
How can I modify the permissions for ASPNETDB.MDF in order to run the TaskList example.
Thanks
View 4 Replies
View Related
Dec 28, 2006
I copied over a small website to 2003 Web Server and got: Exception Details: System.Data.SqlClient.SqlException:
Failed to update database
"D:INETPUBWWWROOTLUNCHCOUNTAPP_DATALUNCHCOUNT.MDF" because the
database is read-only. It works just fine in VS - what to do? Thanks
View 2 Replies
View Related
