AWS RDS: Manual snapshot doesn’t result in event with binlog position?

I have an RDS database and need to create a binlog slave on an ordinary MySQL server on an EC2 instance. According to this: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html the way to do this is to:

  1. Create a manual snapshot, which should also result in an event in the log containing the binlog position.
  2. Create a new RDS database from the snapshot and use mysqldump to dump data

I have created the snapshot, but I see no binlog position anywhere. Do I need to set some parameter, add some option or …? Without a solution to this, I’m stuck, simply. This is a production database, so I can’t simply stop it until mysqldump finishes (~24 hours).

Can I make a short-lived readonly snapshot for reporting in Azure SQL DB?

I have an Azure SQL DB (OLTP), that is under considerable load, let’s call it AppDB. AppDB is transactionally consistent, but new rows are written to it constantly. Now I need to populate ReportingDB based on AppDB state every 30 minutes. Reporting population job runs several moderately big queries against AppDB, those queries, unfortunately, can’t be wrapped in a transaction but still have to all run on consistent data. That is, I can’t have situation Query 1 runs=> new rows inserted into AppDB => Query 2 runs. All my queries have to see data the way it was when Query 1 started.

I was hoping I can use Snapshots to create a read-only snapshot for Reporting job to use. From description, creation of such a snapshot should be fast and subsequent "copy on write" performance hit should be manageable. Snapshots lifetime would be under 10 minutes on average.

But now it looks like Azure SQL does not support CREATE DATABASE ... AS SNAPSHOT OF ..., it only supports CREATE DATABASE ... AS COPY OF ..., which I expect to be a lot slower (probably not meant to be used for reporting snapshot).

What would be a good way to create quick and short-lived read-only snapshots for reporting in Azure SQL DB?

P.S. We have considered replication, but it is not an option for us at the moment due to policy limitations.

Pg_rewind Uitility – PostgreSQL Snapshot standby

We have Primary-secondary PostgreSQL Streaming replication.

I have done some testing.

Steps.

1.Promote secondary

2.Run some DML on secondary which is in Write mode(another new primary)

3.Down new primary

4.Re-attach with primary using pg_rewind utility.

So, Can we use these steps to create snapshot standby database like we have in Oracle database.

Create DB snapshot without specifying the FILENAME attribute

I create DB snapshot as one of the tasks in SSIS package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded FILENAME attribute.

CREATE DATABASE Sales_snap ON   (    NAME = SalesData,    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData_snap.ss' ),   (    NAME = SalesData1,    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData1_snap.ss' ),   (    NAME = SalesData2,    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SalesData2_snap.ss' )  AS SNAPSHOT OF Sales  

Is there a way to use some default path for FILENAME instead of copy-pasting the same string 20 x 3 times? Alternatively, is there any way to specify base path only once and then reuse it in other SSIS tasks?

A graph database suitable for analyzing a heap snapshot?

It looks like recommendation questions aren’t explicitly OT, so here goes:

I’m working on some tooling for analyzing a dump of the heap of a running program. The dump is just a list of nodes with associated metadata and references to other nodes (possibly-cyclical).

I don’t have any experience with graph databases, and I’m wondering if I would save myself a lot of time by building tooling around a graph DB. So I’m looking for recommendations and pointers to resources, and advice.

Some specific questions:

  • are there any graph databases that have functionality built in for computing a dominator tree? (googling this didn’t seem to get any results)
  • are there any DBs that have tooling for visualizing a huge graph?

SQLServer: Why is a Shared lock blocking on an IX lock when Read Committed Snapshot is enabled?

Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot of the deadlock graph.

enter image description here

For reference: The left node is a basic insert into the sync_entities table, where the right node is "update sync_entities set A=B, C=D from sync_entities e join sync_revisions r on e.id=r.entity_id where r.id in ()"

Based on my understanding of lock compatibility, an Requesting an S lock when an IX lock exists would block only if Read Committed Snapshot is off, but it is turned on for this database, and has been for a very long time. Am I missing something?

SQL Server Snapshot replication – Failing to start the Publisher snapshot agent

I am trying to set up a Snapshot replication using an Azure SQL Managed Instance. While trying to see the Snapshot Agent status, I see this error. Failed to connect to Azure Storage ” with OS error: 53.

While configuring the Distribution wizard, I had the option to set the Snapshot folder as well as the Storage account connection string.

I got the storage account connection string from the Azure Portal and pasted that in. I am in doubt about the Snapshot folder. What value should I set in there ?

Is it a folder inside Azure Storage account or in the Distributor SQL Server instance ? In the Distribution wizard, it said, that the details of the folder would also be in the Azure portal. Is there a place where I could get it ?

I am getting a feeling that if I have the correct setting for this, my snapshot replication would work just fine.

Can anybody guide me to find out the problem ?

Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level?

The MySQL doc for START TRANSACTION says:

The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

I’m surprised, as I thought that the SERIALIZABLE isolation level, being above REPEATABLE READ, would also offer a consistent read.

What prevents WITH CONSISTENT SNAPSHOT from working with SERIALIZABLE transactions?

I even found this bug report from 2012 where the doc originally said that this worked with both REPEATABLE READ and SERIALIZABLE, and they explicitly changed it to REPEATABLE READ only, so it looks like there is a technical reason for this. I tested it and MySQL does generate a warning when used with SERIALIZABLE.

Which FLUSH command before taking EBS volume snapshot?

The FLUSH TABLES WITH... command has a few flavors. The manual meagerly describes the differences between:

  • FLUSH TABLES WITH READ LOCK
  • FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT
  • FLUSH TABLES … FOR EXPORT

Am I interpreting the manual correctly that if one needs to perform a disk volume snapshot, such as the EBS Snapshot, the preferred way now is to use the FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT right before initiating the snapshot?