Database Mirroring And Log Shipping

Oct 3, 2006

Hello All Database Mirroring Experts,

I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:

"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring
session is configured with an additional server instance known as the witness.
If the principal database is lost for any reason after the database is
synchronized and if the mirror server and witness can still communicate
with each other, automatic failover occurs. An automatic failover
causes mirror server to assume the principal role and bring its
database online as the principal database. For more information, see Automatic Failover
[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log
shipping backup location is accessible to the new principal/primary
server, its backup jobs begin to ship log backups to that location. The
database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored.
The secondary servers continue to copy log backups without knowing that
a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx

Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is
unaffected by a mirroring failover and that only valid log is restored?

Let me elaborate the situation (if anything I said is incorrect, please correct me )
Here is the time line of the failover happens:

------- tn-1 ---------- tn ---------- tf -------- tn+1 ---------------> t

----------------> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.

Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.

Thanks a lot,
Terence

View 3 Replies


ADVERTISEMENT

Log Shipping V Database Mirroring (again!)

Oct 25, 2006

Hi

I'm looking at replicating our primary SQL server to a secondary offsite server (linked via 100Mb so effectively LAN speed). What are people's preference when deciding on a solution?

On the surface mirroring looks much better but having dug a little I've found it is recommended only 10 databases are mirrored per instance. That said, I've found a post from someone who is upto 58 databases mirrored. Are there similar limitations with log shipping?

Does anyone have any experience of mirroring and is using it in prefence to log shipping?

Our current recovery strategy is the classic restore the SQL dump from tape onto a rebuilt server so either method will be a vast improvement. None of our databases are mission critical that they need upto the second replication. 15 minute replication would be fine leading me to think that log shipping may be better given the possible limitations of mirroring

For recovery, I was considering amending the DNS records of the Database servers. Does anyone see any issues with this approach? I understand there is a automatic failure function if using mirror but this may require the application to be coded correctly?

Thanks in advance for any feedback



Robert

View 4 Replies View Related

Log Shipping Vs Database Mirroring With SQL Server Cluster

Oct 24, 2007


Dear,

Are possible build a solution with SQL Server 2005 Cluster and Database Mirroring? I have the following scenario:

1) Site A
SQL Server 2005 with 2 nodes and active/passive

2) Site B
SQL Server 2005 for Disater Recovery with Log Shipping.

Are possivel change Log Shipping by the Database Mirroring? If is possible, are better:


a) Synchronous(with or without witness)
b) Asynchronous

Thanks a lot! Sandro.

sgpcosta@hotmail.com

View 2 Replies View Related

DR: Replication Vs. Log Shipping Vs. Clustering Vs. Database Mirroring........

Aug 8, 2006

Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)

I have a spec of knowledge in these areas:

SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)

SQL 2005 Replicaiton

SQL 2005 Log Shipping.


SQL 2005 Database mirroring. ( needs three servers)



Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.



TIA,



barkingdog

View 8 Replies View Related

Data Mirroring Vs. Log Shipping Vs. Replication

Sep 20, 2006

Hi everyboby,

Can anybody tell me the differences, advantages and disadvantages between these three solutions? When do I may use one or another?
Could you recommend me any documentation?

Thanks a lot,

Radamante71.

View 4 Replies View Related

Mirroring :: Log Shipping Cross Different Domains

Oct 27, 2015

SQL Server 2012 SP2 - WIndows 2008 R2.

We are currently doing Mirroring from one domain to another (On a different subnet/network) via

Network1

DomainABCSQLAccount1 - Password1 -----> SQL Server Service Account
DomainABCSQLAccount2 - Password2 -----> SQL Server Agent Service Account

Network2

DomainABCSQLAccount1 - Password1 -----> SQL Server Service Account
DomainABCSQLAccount2 - Password2 -----> SQL Server Agent Service Account

And it works. We are planning to do the Log shipping but this time different Domain but same userID and Password

Network1

DomainABCSQLAccount1 - Password1 -----> SQL Server Service Account
DomainABCSQLAccount2 - Password2 -----> SQL Server Agent Service Account

Network2

DomainDEFSQLAccount1 - Password1 -----> SQL Server Service Account
DomainDEFSQLAccount2 - Password2 -----> SQL Server Agent Service Account

Is this possible without TRUST between the domains ?

View 3 Replies View Related

Regular Backup Co-exist With Log-shipping (or SQL Mirroring)

May 14, 2007

Suppose I have a 3rd party SQL backup software to backup my databases, can I also setup a stand-by server for log shipping?

I'm not sure if this is possible? If not, is SQL mirroring a choice instead of log shipping?

Thanks a lot!

View 1 Replies View Related

SQL Failover - Mirroring, Log Shipping, Clustering, Load Balancing

Jun 14, 2007



Hello.



I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:



I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:

1) How can I revert back to SQL1 once it is ready?

2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?

3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.

4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?



Another setup:



We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:

1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?

2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?

3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.



I read so much topics about this, but the more I research, the more I get confuse.



Any suggestions, comments, advice is greatly appreciated!

View 6 Replies View Related

Database Mirroring | Can Witness Live On Mirroring Server?

May 3, 2008



Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)

For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?

View 7 Replies View Related

Mirroring :: Database Mirroring Changes In Application

Oct 12, 2015

Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....

View 5 Replies View Related

SQL 2012 :: Partition On Log Shipping Database (Primary Database)

Jun 11, 2014

I would like to do partitioning one of my client, but that primary database is participating in log shipping.

If I do partitioning on primary, automatically apply those changes on secondary db? if not, what is the way?

Note: I am adding new filegroups and ndf file in different drives on primary database.

View 2 Replies View Related

SQL 2012 :: Database Mirroring And NT Authority Account For Database Engine?

Dec 2, 2014

I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?

View 2 Replies View Related

The Database Is Being Closed Before Database Mirroring Is Fully Initialized

Jun 12, 2007

When I issue this command:


ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234'
I get this error message:




The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed.
What does that mean, and how do I fix it?

View 8 Replies View Related

Database Mirroring Hangs On ALTER DATABASE SET PARTNER

Jun 26, 2005

Hi,

View 6 Replies View Related

Log Shipping, More Than One Database

Oct 2, 2006

Hi Folks

anyone could provide steps, links, on how to bring secondary online, with more than one database being log shipped ?



thx

View 1 Replies View Related

Database Mirroring Monitor Not Working For One Database

Apr 2, 2008

I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank

If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.


Both servers are SQL Server 9.0.2047 Enterprise Edition.


Any idea what might be going on here?


Thanks in advance.

View 3 Replies View Related

Log Shipping For Training Database

May 12, 2008

We have a user Training database that we would like to keep updated from our Production database. Users would make different modifications to the Training and Production databases. It is the user updates on the secondary that bother me and I have not seen anything that indicates Log Shipping would fail if new random records are added to both the secondary and primary.

Would Log Shipping be appropriate for this nightly update of Training from Production?

Thanks

View 2 Replies View Related

Renaming Database After Log Shipping.

Jun 30, 2007

In order to move from one data center to another, I am planning to use log shipping.
Lets say,

Current Data center - DC1
Current Server- S1
Current Database- DB1

New Data Center -DC2
New Server -S2

Now, on the New Server S2, I already restored DB1 as DB1 for QA testing. QA has to do some testing before we move from DC1 to DC2. In the mean time, I need to start log shipping from S1 to S2, so that we can fail-over to S2. Now since DB1 database is already present on S2, I can not use the same name. I will have to use some other name (say DB1_New).
I can not wait for QA to finish their testing. I have to start log shipping before that. When QA is done, I will delete the DB1.

So for this log shipping, -

Primary - S1- DB1
Secondary - S2 DB1_New

So when we fail-over, we will have the database name as DB1_New. But all the apps use the name DB1. So I will have to rename the database as DB1.

So want to know, will this renaming the database (DB1_New to DB1) cause any issues?

Anyone had similar experience?


Thanks

View 3 Replies View Related

Log Shipping - Switching Recovery Model In Log Shipping

May 13, 2007

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

View 1 Replies View Related

Database Mirroring

Oct 12, 2007

Hi All,

Does anybody know the OS requirements for Database Mirroring? I checked BOL and Google but couldn't find any info on that.

Thanks.

View 5 Replies View Related

Database Mirroring

Feb 27, 2008

Hi All,

I have read on the web that high protection mode not recommended, except in the event of replacing the existing witness server. But I can't find the reason why anywhere. Can anybody explain? Thanks.

View 2 Replies View Related

Database Mirroring

Mar 12, 2008

Hi All,

Couple of questions about database mirroing.

1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?

2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?

Thanks.

View 1 Replies View Related

Database Mirroring

Mar 15, 2007

for a database mirroring , which stretergy is good.
Creating with witness server or without it.?

I am firsttime doing this , so i am posting it to the forum..

and also if we are creating the database mirroring with witness server for automatic failover , will the witness server need the same amount of harddisk space like the pricipal or mirror server.??

thanks

View 2 Replies View Related

Database Mirroring

Jul 30, 2007

we want to migrate our production server , without taking the database down.so what we did was , we mirrored the databases from production to one of our development servers and we took the prodcution server for uprgrade.

so today we are moving back (mirroring) all the databases from that development server to our new production server.

my question is , when i move

productionserver_old(principal server) - having the script for principal server

development server(mirror server---->principalserver)) - having the script for mirror server , which is now acting as principal server

new production server(mirror server) - having the script for mirror server. will become principal server , once i moved all the databases

now while mirroring between the developement server and the new production server, i have to run the scripts for the mirror server only.

so both the servers are havign the mirror script now...

is that a problem, or any other way i can handle this....

View 12 Replies View Related

Database Mirroring

Mar 21, 2008

Hi All,

Does anybody know any good Tutorials for Database Mirroring (Automatic Failover) or Manual Sync with Mirror Server. I tried some sites online, but doesn't have detailed steps..
pls.help as i need to sync data with One of my Mirror using Sql Server 2005.

thanks

View 2 Replies View Related

Database Mirroring

Mar 14, 2008

Hi,

I have some questions about database mirroring. we have almost 40 databases in one server. Can I set up the database mirroring for all the databases. Is it going to any affect on performance. we have already setup the mirroring on almost 30 databases, and I need to set up for the rest of 10. please some body could help on this. Thank you.

View 3 Replies View Related

Mirroring Between Two Database

Jul 7, 2006

Hi,
i'm a novice of sql server and I have a problem.
I have to reply a server in which there are database that are managed with sql server 2000 sp3; what I must make in order to set up the mirroring between the database of the two server so that the data are always aligned ?

thanks for the attention
best regards

alessandro

View 3 Replies View Related

Database Mirroring

Apr 18, 2007

I am new user of SQl and preparing the exam database mirroring....can you tell me how i start the mirroirng ..what is my first tep..and is there only one instance

View 3 Replies View Related

Database Mirroring

Mar 12, 2008

Hi All,

Couple of questions about database mirroing.

1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?


2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?


Thank you.

View 6 Replies View Related

Database Mirroring

Jan 4, 2008



Can we have database mirroring for two databases. Like i have an application where in the db used are two, namely DB1 and DB2, DB1 is used in the connection string and with the help of DB1 the tables and sps of DB2 are handled.

in this case can i go ahead with database mirroring. Please comment and give the idea or link on the same.

View 1 Replies View Related

Database Mirroring

Oct 25, 2006

Hi,

Is there a way to automatically mirror/backup a SQL Express database with a SQL Server 2005 licenced system?

They will be on seperate machines on the internet.

I have done manual backup and restores but would like to allow constant updates to allow both databases to be synchronized.

I read that the SQL Server 2005 version has a mirroring facility but I also read that this is not possible in the Express version.

What other (automated) options do I have?

Thanks Andrew

View 1 Replies View Related

Log Shipping Does Not Ship To Secondary Database

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

T-SQL (SS2K8) :: How To Configure Log Shipping To Secondary Database

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







Copyrights 2005-15 www.BigResource.com, All rights reserved