Tag Info

Hot answers tagged

13

For all those suggesting mirroring - it's not going to be possible to mirror 400 databases. You'll run out of worker threads way before you hit 400, whether on 32- or 64-bit. There are at least 2 worker threads per database on the principal and at least 3 per database on the mirror. It's really got nothing to do with the skills of the DBA. It depends ...


10

Yes, database mirroring is a reliable technology and is in use by many Enterprise customers today. I've helped people set up mirroring both while inside and outside Microsoft - I used to own database mirroring when I responsible for the SQL Server Storage Engine. As with any HA technology, you need to understand your requirements and limitations before ...


6

Nothing. This is the normal state of the mirror. As long as the principal says "Synchronized", everything's in sync between the servers and the mirror is working correctly. See this Technet article for more details: http://technet.microsoft.com/en-us/library/cc917681.aspx#ELAA. The only thing I would suggest is to test the failover, but only if the ...


5

Although network bandwidth comes into play in a big way, the absolute number one factor to consider is what is the transaction log generation rate on the principal? If the app and your maintenance doesn't generate any transaction log, then network bandwidth is really irrelevant. If it does generate log, then network bandwidth has to be able to handle the ...


5

Microsoft published a really good whitepaper on database mirroring that includes some good examples on how much performance impact you get from synchronous mirroring. You're totally right in that there's going to be a performance hit. Do a ping from the primary box to the database mirror and look at the round trip times in milliseconds: that's going to be ...


5

As others have said, there are lots of built-in ways in SQL Server 2008 to make two servers synchronize, real-time or on a schedule. Some points you should take into account: How "far" are the two servers (i.e. how much bandwidth and latency between them)? How big are the two databases? How often and how much do data change? Do data actually change, or ...


5

Data will be committed, otherwise the mirroring would be known as the 'high unavailability' feature. The log(s) on the principal will grow as it must hold all transactions that are no shipped over to the mirror. When eventually the mirror comes back up the principal will show PRINCIPAL/SYNCHRONIZING as the log will be shipped over to the mirror. The log(s) ...


5

Don't "segment out" the spiders. Trying to "segment out" WWW spiders is fighting against the WWW spider owners, who want, as far as possible, their spiders to see what everyone else sees. Go down that route, and you'll find yourself in a constant arms race with the spider owners. Check your site design. High spider traffic is sometimes symptomatic of bad ...


4

Express or Developer editions won't do it as you're already using mirroring. Basically as soon as you use the instance for anything apart from hosting redundant copies of databases through log-shipping or mirroring, you must buy a license. It's a bit sucky as most people would expect that testing restore sequences and running consistency checks would be ...


4

1) No the log chain is not broken - the mirror database is an exact byte-for-byte copy of the principal. It will be in the FULL recovery model and knows what state the backups are in from the various LSNs (log-sequence numbers) in the boot page of the database. (See Search Engine Q&A #20: Boot pages, and boot page corruption.) Note that any in-flight ...


4

For monitoring, querying the sys.database_mirroring DMV is probably the easiest. It contains fields for mirroring_role_desc and mirroring_state_desc. Link to BOL: http://technet.microsoft.com/en-us/library/ms178655.aspx The SQL Error log also provides information about these, and there is an event you can catch with SQL Trace as well, I believe.


4

I believe if you are not using a witness you can still enable the mirrored instance by using Forced Service. This method should pass the services on to your mirror but there is a risk of data loss. This technet article should be able to start telling you the involved processes as well as the risks involved. The T-SQL to force the mirror to take over is: ...


4

It depends on the endpoint authentication type used. The DBM endpoint itself will use only one port, typically configured at 5022 (TCP). But DBM supports two types of authentication: WINDOWS and CERTIFICATE. If CERTIFICATE is used then the DBM port is enough. However when WINDOWS authentication is used then a Kerberos or NTLM handshake has to succeed between ...


3

Bandwidth will definitely be one consideration. Another will be the server resources which are required to run database mirroring for that many databases. Database mirroring will be contently shipping transactions from the active server to the mirror, this will require a small amount of resources per database. Multiplied by 70 databases that little bit ...


3

Have you looked at the built in mirroring functions? Technet has a brief description here. The primary server sends its transaction log records to a secondary server, which then replays the transaction log. Mirroring can be set up synchronously or asynchronously.


3

For scheduled and ad hoc database synchronization you can use ApexSQL Diff (for object schema synchronization) and ApexSQL Data Diff (for record synchronization) They cannot be used for real time synchronization. IMO, it's best to use database mirroring for real-time


3

Solution 1 is close to sharding but the entire architecture needs to be considered and designed to accomplish that in the best manner. Sharding typically comes up with large scale installations pushing limits of the technology platforms. Solution 2 or dual master replication would be applicable but as your links are physically separate it would be risky to ...


3

Error 1416 often occurs when the mirror database has been recovered. Ensure that the mirrored database is in a Restoring state (NORECOVERY option) Run the query below on the mirror server and verify the state of the database. select database_id, user_access_desc, state_desc, recovery_model_desc, is_broker_enabled, log_reuse_wait_desc from sys.databases ...


3

I created a series of batch files and SQL scripts to do the steps below. I only included the scripts for the mirroring stuff but the other referenced SQL scripts are pretty basic SQL commands (except the restore database script, that one is kind of tricky due to how I've implemented it) and creating them will give you an opportunity to change the SQLCMD ...


3

My guess is that yes it would,but I cannot be certain unless I test it out for myself. BOL looks like it shares my opinion, but in vaguer terms!. Any pages on the mirror that change need to be copied to the snapshot file first. Not sure how optimised the process is & how much more performance degradation you get when you create a 2nd snapshot. From ...


3

Snapshots do not directly affect the mirroring session. To allow the principal to continue, the mirror must harden the log, not commit. This simply means the received log from the principal must be written and persisted into the log files. It is the recovery process that continuously applies the log to the mirror that will have to handle the copy-on-write ...


3

I would strongly discourage you from regularly shrinking the log in a mirrored situation. One day your log will grow on the primary and not have disk space to grow on mirror, primary will not be able to ship log and will be pin the log in place until it grows ad-nauseam and you'll be cursing the job that shrinked your log as you won't be able to resume the ...


3

Yes, mirroring is where transactions are replicated from primary to mirror. So one database will be in restoration mode Perhaps a read of 2 whitepapers may assist One and Two


3

Given that is at exact 10 minute marks (7:30, 7:40, 7:50) I would first suspect a periodic job of some sort. An internal 10 min. timer (eg. a checkpoint interval) would run every 10 mins, but unlikely at exactly 10 minute marks. It could be either a job on the mirror, or a job on the principal that generates log that requires processing on the mirror. So ...


3

With SQL DB mirroring and replication, if @@servername doesn't match the actual hostname, you WILL have issues. This typically will happen if a server is renamed after SQL is installed. Also, the servers need to be able to communicate via hostname, whatever handles that is up to you (DNS, hosts files, WINS, etc) If your hostname does not match the ...


3

I have zero knowledge of Amazon EC2 but can answer from SQL Server perspective. For a given principal, only one mirror is possible until SQL Server 2008 R2. This is changing in SQL Server DENALI but you have to wait for additional details. Sync is always 1 directional except from principal to mirror with one caveat when database corruption happens on ...


3

You're asking the right questions and have a good handle on the problem, however the bar is set fairly high. You're hitting a wall looking for something that may be elusive with technology that was developed five years ago. And you've already test-driven two vendors in the field and they aren't up to the challenge. Plan for the future. What you have now ...


3

What you have missed is that MS Programers re no total idiots. When DBA comes back up it knows it is outdated and wont answer until it has taken the data from B. A has no "real" priority, especially not with a principal available. B will be there and know it has a newer authorization. If A would go on answering and the data on B be lost, the feature would ...


3

In my experience, detach/attach is the fastest method. The bottleneck would probably be how quickly you could copy the files across the network. Assuming the two databases have identical Windows accounts (if you're using SQL accounts you may have to update SIDs), you could probably use something like this script that I have laying around from before I ...


2

Paul, 1. Typically a few seconds, up to a couple of minutes depending on ... (you know the rest). Were I to setup auto failover I'd go for several minutes. That way site to site VPN connections would have time to come back up, Cluster could restart, etc. At the minimum I'd probably go with 4 minutes longer than it would take the nodes of the cluster to ...



Only top voted, non community-wiki answers of a minimum length are eligible