Database Mirroring - High Safety Mode

Oct 1, 2006

Hello!

I'm reading about database mirroring in High Safety Mode (Synchronous with no witness). I'm a little confused about what happens if the mirror server disappears.

From Books Online
"If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data)."
http://msdn2.microsoft.com/en-us/library/ms179344.aspx

I was to the course [Upgrading your Database Administration Skills to SQL Server 2005] and the course material does not say the same."Scenario: Synchronous mirroring without a witness server. If the mirror server becomes unavailable, the principal server will take the database offline. This action attempts to maximize the protection of the database and ensure the integrity of its contents"

I've made tests that shows that Books Online is right,
but I would like to know if this is a behaviour that has changed.

I also would like to know if it's possible to run in a more secure mode,
where exposed operations is not allowed.

Best regards

Ola Hallengren

View 1 Replies


ADVERTISEMENT

Recovery :: Where To Place Witness Server For Mirroring Using High-safety And Auto-failover

Apr 9, 2015

We have 2 data centers, site 1 and site 2.  Site 1 is generally our primary, and site 2 is our Disaster Recovery (DR) site.  I want to setup a SQL instance to have extremely high availability.  Therefore I was looking at using DB mirroring, with synchronous data writing, high-safety and auto-failover.  This requires the usage of a witness server.My problem with this setup, is that if the witness and principal are both at site 1, if site 1 goes away (power failure, asteroid impact, lol, anything else that would be a *true* DR scenario), then there is no failover to the mirror server at site 2.   However, if you put the witness at site 2, anytime the WAN link between site 1 and site 2 has an issue, the DB will auto-failover to site 2.  Is the reason for the recommendation of the witness being at the primary site because the thinking is that WAN link failure is likely more common than a *true* DR scenario that takes out all of site 1?

View 3 Replies View Related

High Safety Changed To High Performance After Fail Over ?

Mar 6, 2008

Hi There

I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.


If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.


So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?

Thanx

View 4 Replies View Related

High Availability For Database Mirroring

Oct 30, 2006

Hello,

I would like implementing a database mirroring architecture with SQL server 2005 but i have questions.

If i don't use a cluster architecture, i would like knowing if there's any solution to move the alias SQL server from the primary to the secondary by script.

Anyone got a pb like me? or any solution?...

Thanks for your help...

View 10 Replies View Related

High Availability To High Protection Without Reconfiguring Mirroring

Apr 23, 2007

Hi,

Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.

If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?

Thanks,
J.

View 3 Replies View Related

Database Mirroring In Comp. Mode 80

May 22, 2007

Hi

Is it possible to use Database Mirroring when running a SQL Server 2005 database in compatibility mode 80 (SQL Server 2000)?



If upgrading to SQL Server 2005 we would have to run Principal, Mirror and Witness in mode 80 to support our Transact-SQL Queries. To upgrade our Queries would be equally (or more) costly then to buy new SAN hardware for a cluster in wich case we might well continue running our 2000 cluster. (Buying hardware is slightly more time efficent then rewriting some 1500 queries)



Please advise, thank you



View 3 Replies View Related

HA (High Availability) With Mirroring And Replication

Jan 11, 2008



Here is the situation we are trying to resolve. The client has 2 locations, each has local appliations running on a database. db schema on both locations are the same, data is different and won't overlap.

The requirements are:
1. at each location the application can read and write
2. near zero down time for applications on each site
3. db on one site also has the data from the other site for DR purpose

The client is running on SQL 2005 STD SP2

We looked at the approach of setting up db mirroring on each location + 2-way transactional replication between both locations. The mirroring was fine, and I was able to set up transactional replication from mirrored publisher to a non-mirrored subscriber. But, from what I experienced and from reading, there is no way to have the subscriber db to be mirrored, since Distribution Agents simply doesn't have the option to specify Failover Partnr for mirroring, so I guess it is not supported. Any comments on that?

Assuming that's correct, then the only way of using SQL out-of-box technique seems to be using Clustering on each location instead of mirroring, then the 2-way transactional replication works on clustered subscribers I think (although I haven't tested it). Peer-2-Peer replication would have been a good candidate between sites in this case, but STD version of sql 2005 ruled that out.

Any suggestions and comments are welcome.

Thanks

View 7 Replies View Related

Mirroring :: Async Mirror Setup For High Number Of Databases

Oct 27, 2015

I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.

What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.

What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.

View 4 Replies View Related

SQL 2012 :: High Memory Utilization For Reporting Services In AlwaysOn Mode?

Mar 26, 2015

We have two server configured in always on mode .

windows is sql server 2012 64 bit edition and sql server is 2012 64 bit edition.

RAM installed on both server is around 65 gb of which 49 gb is max server memory allocated for sql services on both servers.

database related to reporting services are also in always on group .

We have also configured for reporting services and both are running on their respecting server.

Issue is on primary server reporting service is using almost 7 gb while on secondary it is using 10 gb even when there are 5 reports and its used within offices .

what issue and how to check why ssrs is using high memory..

any query , perfmon counters

reports are randomly used at client side

i have checked memory utilization through task manger..

View 0 Replies View Related

Transact SQL :: Trigger Database In Suspect Mode And Get It Out To Normal Mode?

Jul 27, 2015

how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.

   i am using SQL server 2008 R2

View 5 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

@@IDENTITY And Thread Safety

Jun 29, 2005

I am currently using @@Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @@Identity from table",conn);string identity=cmd.ExecuteScalar();Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?

View 4 Replies View Related

Safety Of Using .NET Static Members As Configuration Transport.

Jan 4, 2008

Situation:
We have a backend system which stores our configuration data. In order to pull configuration information from this system our components need to have access to three pieces of data: application name, module name, and the variable to pull.

This same system allows us to log various events. It serves as a central point of configuration, maintenance and monitoring for all of our environments.

Desired Outcome:
We'd like to create a connection manager, log provider, and task "finalizer." We'd like to initialize the variables named above in one location and have all created components pull their required information from that one location.

Assumptions:
* Custom configuration managers are not possible.
* Custom connection managers and logging providers do not have access to the package variables.
* Custom tasks have access to the package variables.


Problems:
Logging Provider: The logging provider does not have access to the package level variables. The only variable that appears to be supported is ConfigString. Currently our component allows configuration of the required data using a "Variable::Variable" format in the ConfigString. We want the logging provider to pull its config data from elsewhere.

Connection Manager: The connection manager does not appear to have access to package level variables. The UI for configuring the connection manager does have access (through the IDtsVariableService). Our current component requires the designer to enter the required data into the component.

We're looking to expand our components to include further integration into our backend. We'd like to resolve the issue of components not having access to the package level variables.


Probed Solutions:
Custom Package Executable: Created as a replacement for DTExec. This fails because we have situations where we may need to run jobs via SQLAgent. Loaded the package, set all the variables and components properly, then executed.

Manual Initialization: Configure each component separately. This fails because we'd like to have a single point of maintenance for these configuration variables.


DTSTask and Static Variables: The heart of this post.

We have created a DTSTask that exists as the first item in the Control Flow and which configures the package level variables and a set of static variables. By "static variables" I refer to the following:


public static class DataStore
{

public static string ApplicationName;
public static string ModuleName;
}

Our components are all capable of accessing these static variables and everything functions wonderfully. (The variables are WORM; the above structure is not what I'm using as I'm aware that SSIS is multi threaded and all appropriate access controls should be used.)

There are concerns over the lifetime of these variables. We depend on the initial task to set these variables; a type constructor is unable to initialize them for us. Static variables are tightly tied to the lifetime of the AppDomain, so I have the following questions:

1. Are AppDomains reused by SSIS? (Is there a chance that a previously configured static variable will be used by a second package?) Research I've done indicates that the SSIS runtime exists in the context of the executing assembly so with DTExec or DTExecUI I'm confident that the AppDomain is not recycled; however, I'm unsure as to the answer in terms of jobs launched by SQLAgent.
2. Are AppDomains ever dynamically created by the SSIS runtime? I know that dynamic creation of AppDomains is explicitly forbidden by the SQL Server 2005 CLR implementation, but I'm not sure about the SSIS runtime. Research has seemed to indicate (but never explicitly state) that this is not done by the current implementation of SSIS.

3. What effect, if any, does "execute out of context" have on static variables? Research has indicated that as long as the package being executed has the appropriate task as the first item that the impact is nil.

Thank you for reading this question, and thank you in advance for any input or comments you may have.
--bms

View 6 Replies View Related

Database-with High And Efficient Performance

Feb 11, 2008



Hi,


I am working in Trade enivornment and i have experience only on SQL 2000.


Each day approximately 4 million data gets loaded and transactions being happened in my server.


Please let me know which database(Sql/Oracle..) can i use for high performance with free or one license.


I am looking for a database which is having hhigh speed in retrieving large data and having massive storage with no performance issues.


Some suggested me to use Informix, but still im doing a R&D work on it.


Please suggest me with two or three different datases with their high performance features.


Thanks in advance




View 1 Replies View Related

Excel: Client Safety Options Do Not Allow Pass Through Statement To Be Issued...

Jun 1, 2005

Hi.. My Finance user create a microsoft Excel OLAP file that connect to our SQL server. And I have created the same ODBC setting for every PCs.

But for a few PCs, I encountered the following error "Client Safety Options do not allow pass through statement to be issued to the data source" When trying to connect the OLAP file to SQL server after entering the same SQL account.

Any idea what is the isssue ??

View 3 Replies View Related

Data Mining :: How To Give Prediction For Public Safety Sector

Jun 29, 2015

which things(I mean columns/elements) we need to consider to implement crime prediction in an efficient way.Now Using PAST DATA , we are giving prediction for,  Location wise + crimes wise how many (count of Incidents) Incidents going to be occur in future.

View 3 Replies View Related

High Volume Database Query Optimization

Dec 10, 2007

Hello every body

i am doing a research about high volume database treatment (maybe a database with tera bytes volume) , so is there any optimization or specialization for queries deal with such database? !!

View 5 Replies View Related

SQL 2012 :: High CXPACKET On Single CPU Database Instance

Jul 2, 2015

Why would I see high CXPACKET waits on a database instance with only one CPU? Since the server only has 1 CPU parallelism can't be used, or are I'm missing something here!?

SQL version
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

max degree of parallelism is set to 0

View 5 Replies View Related

SQL 2012 :: Monitoring Table In High Transaction Database

Nov 4, 2015

I am developing a process to monitors a table in a high transaction database. The process will count the number of lines in the table to verify if it has changed or it is stuck. Due to the fact that the database has a lot of transaction I don't want to execute a query on database too often.l Is there another suitable way to accomplish this goal ?

View 2 Replies View Related

Recovery :: 2012 R2 High Availability Database Not Replicating?

May 19, 2015

Discovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day.  Suspend Data Movement appears to be working perpetually without finishing.  The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager.  The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green.  

The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.  There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:

The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?

View 2 Replies View Related

DB Engine :: High Availability Database Backup Error

May 19, 2015

Backing up all databases on a sql server that hosts secondary high availability databases as well as other databases.  The other databases back up fine, but the high availability secondaries all get the same error:

BackupDiskFile::OpenMedia: Backup device 'F:MSSQLBackupdbnamedbname_backup_2015_MM_DD_tttttt_ttttttt.bak' failed to open. Operating system error 2(The system cannot find the file specified.)

View 11 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

Read Only Database Restore Bringing Database In Single User Mode?

May 9, 2012

I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.

why it is bringing the database into single user mode ?

View 1 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

Can The Implementation Of Database Shared File Method To Achieve The Objective Of High Availability?

May 2, 2008

Hi,
can I assign a NAS(Network Attached Storage) server to store the database file(readable and writable) and assign other several MS SQL database servers which will use the same database file in NAS to achieve the objective of high availability?

If it can, how can I set it up in MS SQL Server or it requires another 3rd patry software to set it up?

Thanks.

View 4 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

Cannot Put Database In Offline Mode Because Lock Could Not Be Placed On Database

Sep 12, 2011

One annoying problem has occurred....I want to put database in offline mode....but it is giving me some error...

"ALTER DATABASE failed because a lock could not be placed on database [database]. Try again later. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)" ...

View 7 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







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