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.
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?
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?
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.
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?
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)
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.
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.
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..
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....
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?
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
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.
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.
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? !!
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)
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 ?
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?
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.)
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?
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 ?
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?
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?
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.
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)" ...
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.
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?
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.??
