6

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

SQL Server Mirroring is part of the core engine. When you setup mirroring, SQL Server enables a TCP endpoint to support the flow of log records from the primary server to the secondary server, and to optionally connect the witness server. Mirroring actually consists of sending log data from the primary to the secondary. Since Mirroring can be configured ...


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


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

This is an older question, but there are some things that were not explained well and this will hopefully shed some light. The how to shrink was answered, mostly, but this will explain the "why" part of what you are actually doing. Backups, specifically log backups, do several things. The data is written to disk as a backup set to be used or discarded ...


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


2

By design, one mysqld process cannot simultaneously listen to two different Masters. The CHANGE MASTER TO command only allows you to set one Master as a source to read. In order to emulate this, you would have to alternate between the two Masters programmatically. How do you do that ? I described in StackOverflow how to Connect a Slave Manually to ...


2

This question is too big; you'll likely not get specific answers on it. In order to have "multi-master" replication, meaning both servers responding, you need to solve that problem for each protocol separately (SQL, SMB, HTTP, etc.). A much easier route is to only use one server at a time in a active-passive scenario but you're still talking a highly ...


2

Database mirroring is limited to database only and does not include any information at the instance level, such as logins and jobs. These will need to be migrated over using a script or manual process. Microsoft has provided a stored procedure called sp_help_revlogin that will export the logins (and the SIDs associated with your SQL users) and the output ...


2

The mirroring feature uses the log to keep track of things until it knows that the other server has those changes. So, no, the ldf is not just for point-in-time recovery. (It's also important for some replication schemes, but you aren't doing that.) Even TRUNCATE_ONLY will not throw away logged changes for stuff that SQL might need. The classic example is ...


2

Do a transaction log backup, with whatever method you feel most comfortable with. This will cause the transaction logs that have already been committed to the database to be deleted from disk. Ideally, you should actually create a database maintenance task to do this for you on a recurring basis for precisely this reason - to eliminate the old transaction ...


2

Database mirroring (DBM) and availability groups (AG) are two different features. DBM is deprecated in SQL Server 2012 and availability groups are new. AGs can be configured on standalone instances that are insalled on WSFC (Windows Server Failover Clustering) nodes. There is no witness with AGs, and even with DBM, a witness was only required in high safety ...


2

You'll get that error if you try to run the alter database [yourdb] set partner... statement at the primary first. You need to run that alter statement at the secondary and then run it at the primary. So, in the link that you pointed to, it seems that the last thing in step 10 hasn't been run yet.


2

You've created an ENDPOINT on the witness, right? Read this on how to create a witness endpoint. Check that there isn't either the Windows firewall or a network device blocking the endpoint port on either principal to witness or mirror to witness. I always like to use: telnet hostname port To test that the ports are open. You should get a connection ...


1

I depends on what software you're dealing with but... Usually (historically speaking) you would not want to put database storage on a NAS, because that would mean using a network filesystem like NFS or CIFS, etc... which could cause all sorts of data integrity and performance problems. There is still iSCSI though which is not a filesystem., but it still ...


1

Probably too late, but, I would suggest spinning up a new 10xl unecrypted RDS from a snapshot, and then create a new blank 10xl encrypted RDS. These faster RDSs will hopefully speed it up. After you have the encrypted RDS, snapshot it, and then restore to whatever normal size RDS you use. Also, try to do the export and import to an EC2 instead of your local ...


1

Your intuition is likely correct. You can verify that the SIDs are different between your nodes by running: select name, sid from sys.server_principals on each node and comparing them. As far as how you're supposed to keep them in sync, do so at creation time (unfortunately, there's no way that I know of to change the SID post facto). That is, the create ...


1

As you describe your application, I am afraid it will not be very easy to 'mirror'. Especially if the data has to change in real time (as it does with GDocs). I am afraid MySQL is not a very good database for such deployment. Filesystem is yet another trouble. The best thing you could do in long term, is probably redesigning the whole thing to use better ...


1

Yes, this is possible. Mirroring occurs at the database level. A single SQL server instance can receive mirrors from as many other SQL servers as you want.


1

There are certain situations where something will happen to the principal server and it won't be able to synchronize without reestablishing the mirroring sessions. But if you are in high-safety mode and have a witness server setup it should be possible for DBA to get caught up with DBB. This is explained a bit more in books online in the Role Switching ...


1

It sounds like you've probably got a problem with your endpoints. If you haven't already you need to make sure that you've got the endpoints configured correctly and started on both servers. You can confirm they're there by running: SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints If you've already done this I would recommend ...


1

Good point by @darin in terms of Table Storage being locally-redundant within a single data center (3 copies), and optionally geo-redundant (which is asynchronously done across data center). A few things to add: Table Storage is not on a single server. That is, there's no single point of failure. This is a highly-available storage system. Even if internal ...


1

When using Azure Table storage, you get "local", with-the-same-DC, redundancy with the basic service. "Geo-redundant" storage allows you to spread your data around the world, but costs extra. Details are here.


1

Percona XtraDB Cluster is designed for exactly this use case. Write-to-any,Read-from-any operation. Synchronous multi-master replication. Based on the Galera project. Complete, up-to-date, high-performance Mysql Fork. Minimal changes to your app's database code (unlike Mysql Cluster). Fully API compatible with your distribution's (recent)Mysql. Just ...


1

SQL Server 2012 introduces contained databases ( http://msdn.microsoft.com/en-us/library/ff929071.aspx) - essentially these are databases which are somewhat isolated from the rest of the SQL Server instance. By setting your database to partial containment you can created contained users - the credentials and privileges are stored in the actual database ...


1

MSSQL Database mirroring does not update mutual security or login settings. However, you can use Windows integrated security and add new users to a "webapp" security group.


1

It's limited to databases only.


1

As I understand the internals of mirroring both the mirror and the witness would need to loose the connection to the principal for the failover to happen.


1

Can you attach the SQL Profiler to all three instances involved and monitor for these events: Audit Database Mirroring Login Database Mirroring State Change Broker:Connection Event Then attempt again to establish the mirroring session. Make sure you select all columns when adding the events. Start from a blank template. 2011-12-09 20:04:07.983 Database ...


1

Ideally you want to have Transactional Logs on a RAID 1 by themselves. User databases on a separate RAID depending on how many disks you can throw at it. RAID 10 for this would be one of the best options. Finally a RAID 1 for OS/Program Files etc... If you have the ability for more disks seperating TempDB onto its own array is also beneficial. This article ...


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