26

You have a few options: Set up MySQL replication between the servers. Your internal server can act as the Master, and the web host server as the slave. Any updates performed on the Master will be replicated to the slave immediately (assuming a working connection). This will most likely be the easiest and most effective option to choose. To use replication, ...


14

Well I might not get it, but I try to answer it. You said you need a high performance solution which runs often (minimum all 2 minutes) and you need a good approach which should be fast without locking. But you don't want a blackbox system. Instead of a blackbox system, which is used on millions of installations with good results, you try to invent the ...


13

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...


9

RDS for MySQL 5.6 supports "in" and "out" migration strategies of live workloads using MySQL's native replication, but RDS for MySQL 5.5, they only support "in" migration... which is unfortunate, because essentially every possible combination except the one that you need is available. That would have been nice. :( If your dump download took 3 hours and ...


9

I will try to enumerate some options here with advantages and disadvantages as I percieve them: SQL Server Replication - this is the best and most optimized native SQL Server tool for this task. But there are several problems: a. for all your clients, regardless on they are SQL Express databases or not, you will need SQL Server CAL license. ...


9

The short answer is no, there is not a DMV that contains this information. It would be a significant amount of overhead for SQL Server to track what table was associated with each log record that had been received but not committed. As you can read in Reading the transaction log in SQL Server – from hacks to solutions, you can read the log records with a ...


8

Plan A: You charge your client an arm an leg to develop code to keep track of diffs, avoid dup keys, upload data, etc. Plan B: Multi-source replication (available at least in MariaDB). With Multi-source, each offline laptop can be a "Master". The "online server" is a Slave. All writes must be done on the Master(s). Not being able to write to the ...


6

Based on this I'd say you didn't have enough wal_keep_segments on the master, weren't using a replication slot, and either had hot_standby_feedback off or had the connection drop for long enough for the master to remove needed WAL. And you are presumably not using WAL archiving (archive_command on master, restore_command on replica) as fallback. So the ...


4

You can also use options like symmetricDS which can help in synchronizing two databases. With this you will be able to select the tables which needs to be synchronized that way you can save internet bandwidth. This would be also suit in the scenario when there is lack of connectivity between two locations.


4

I would not design a system to do lookups on the fly. Instead I would design the system to validate if a given number is on the DND list at the time the number is added to your system, and weekly (right after the DND list is updated). Then you don't need to check the number each time you send an SMS. If you follow that design pattern, you'll only need to ...


4

What are maintenance mode tasks? The "maintenance mode tasks" message is referring to a counter of successive calls to the replSetMaintenance command and (as at MongoDB 3.4) isn't associated with specific queued tasks. The replSetMaintenance command is used to keep a secondary in RECOVERING state while some maintenance work is done. A RECOVERING member ...


4

What you see is a common experience for anyone running standbys and bigger queries on the master. There are a couple of possible causes: There are also additional types of conflict that can occur with Hot Standby. These conflicts are hard conflicts in the sense that queries might need to be canceled and, in some cases, sessions disconnected to ...


4

So a number of things here... (e.g. this will be a long answer and may go through a number of iterations): First and foremost did this AG experience a failover? If not, then checking the ERRORLOGs on the Primary node is where I suggest you start your troubleshooting process; if a failover did occur, check the ERRORLOGs on the node formally known as the ...


3

Why not create one separate db for your SSO, which is accessible for each site? Copying data around will create problems when two sites are editing "the same field". If you only want to use a copy to set up, you could: CREATE TABLE newdb.users LIKE otherdb.users; INSERT newdb.users SELECT * FROM otherdb.users; If you really want to continuously ...


3

Creating a Scale Out database at Internet scale is pretty huge step. You will face a lot of issues that are not critical on a single big database. From your notes I see that you understand some of the basic issues you face. Since Microsoft has papers on using SQL Server for scale out, I suggest that you study those first. Your scale out strategy will need ...


3

I'd tend to export each table as CSV from Access, something it's quite good at. Then import it into PostgreSQL using COPY, possibly via a preprocessor like a Python script with psycopg2 if there's lots of mangled / invalid data. If there are lots of tables, VBA (Visual Basic for Applications) or similar can be used on the Access side to automate the export, ...


3

Is it possible to set up async synchronisation (high-performance) on MS SQL Server 2012 globally Yes, with AlwaysOn Availability Groups having commit mode set to asynchronous commit for a replica. That'll be by-database, so you'll have to group the databases into the AG that you want to see that happen with. but somehow force synchronous replication for ...


3

SQL Server replication, creates jobs which runs through SQL Server Agent, but because SQL Server Agent doesn't run with SQL Server Express Edition, Replication also can not run and the feature is not added in the edition and you can not implement replication. So if you need to sync both databases you need to upgrade your database to standard edition or ...


3

You have a few options: Transactional replication: this has been around for quite some time and works nicely within its narrowly defined scenario. Unfortunately, the use of non-SQL Server subscribers is deprecated. Change data capture + SSIS: this is the Microsoft recommended solution. It's a bit more involved than replication (to me anyway) but it's also ...


3

No, that is not a possible way: Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it. You can configure how export should work if a table already exists but if there is a table that s referenced by another table then TRUNCATE and REPLACE will not work. Even if there is ...


3

I may be understanding this slightly wrong so I apologise if I am. If you have two databases that you need to be identical and are on 2014 then use the AlwaysOn High Availability Group. Since you're data centres are at separate locations use the Async mode This will mean the database is kept completely up to date (all be it possibly with a few second ...


3

If the GUID is the absolute truth about what is supposed to match, then the GUID must be the PRIMARY KEY. Get rid of id. If you want semi-automated "conflict resolution based on time", you want MySQL's NDB Cluster.


3

User acceptance testing (UAT) usually involves clicking around in an application, which usually means making changes and testing them. This is especially true if you think you're going to "replicate those issues/bugs to our development environment" - it sounds like you're thinking about synchronizing in both directions. That requires a writable database, ...


3

Based on your scenario Data Sync is the best fit. It's the only Azure PaaS solution for Data Synchronizaion. Transactional replication is supported for on-premise to Azure. If sync in this direction is enough for you, you can use replication. See Transactional Replication to Azure SQL DB now in public preview


3

No. In order for a log to be restored to a database, the log sequence number (LSN) of the log has to be the correct number to match what the database requires for the next log to be restored. If you could somehow get the LSNs to match, restoring a log from a different database would corrupt it as some operations contain page IDs, and the page IDs are not ...


3

If i want to change the DR to Synchronous Commit will there be any performance issues ? Well, it will take longer to commit transactions, as the primary will wait for the log records to be saved on both sync replicas during each commit. Whether that causes a "performance issue" depends on the transaction throughput, network bandwidth, distance to the DR ...


3

You have two interacting problems here: transferring data between Azure databases, and updating data maintained by system versioning. And possibly a third: live data in dev. Transferring Data in Azure If Azure Data Sync is not appropriate then there are other options that you might consider. You could perhaps use SSIS packages or other ETL options to ...


3

You seem to want to do data replication in almost real-time. Why haven’t you considered SQL Server data replication or even better, an availability group? Our data warehouse is setup as a cluster-less availability group and the TB sized DBs are kept in sync.


2

This sounds like your customer does not entirely understand what is possible with SQL Server. It is also possible that he is confusing SQL Server with Oracle. With Oracle, you can spin up one database and have two database instances running independantly and operating of the same data set. With SQL Server, the best you are going to probably do is some sort ...


2

Here are some tools that have been around for a while, though I have not used any of them: https://github.com/jmafc/Pyrseas http://www.andromeda-project.org/ https://labs.omniti.com/labs/pgtreats/wiki/getddl I have a feeling there is at least one more floating around, but a quick search didn't trigger any other links for me.


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