What Are the Types of Security Testing?

We have ~30 DBs on MS SQL server and unfortunatelly DBs rely on each others data, do cross-db joins and system fails on inconsistency of data.

This sounds terrible, and requires fixing ASAP, but i'm not sure that it will happen faster than we need some data from backups.

Is there a way to make backups of 1.5TiB (equally spread across 30DBs) in a way that gives best resulsts in terms of consistency between DBs?

Thanks!

I can recreate the described error but only when highlighting the FETCH NEXT FROM Part and executing that (like @J.D. commented).

When completing the example with CLOSE RRisk, DEALLOCATE RRisk and DROP TABLE #RedsRisk and not selecting any code the code runs multiple times without error.

If your undisclosed access patterns and/or other restrictions don't allow for a https://www.postgresql.org/docs/current/sql-creatematerializedview.html or a trigger solution keeping a table with min/max per device_id up to date, then the closest standard tool might be a https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BRIN , which is much smaller than a corresponding B-tree index, typically by several orders of magnitude. But its efficiency also depends on undisclosed data distribution in your table. See:

• https://dba.stackexchange.com/questions/214646/testing-efficiency-of-a-brin-index-in-postgres/312327#312327

In an effort to document myself I examined a few sources; https://www.postgresql.org/docs/12/runtime-config-replication.html two https://www.postgresql.org/docs/current/logical-replication-quick-setup.html and this https://linuxconfig.org/how-to-create-a-hot-standby-with-postgresql

Now the latter one seems to be updated in 2020, but refers to version 9.2 which is somewhat dated [angst emoji]. There are some discrepancies I want to clear up.

• my understanding is that a hot-standby is open for read-only SQL statements. However the defaults for postgresql.conf on version 12 are:
#wal_level = replica # minimal, replica, or logical
while the https://www.postgresql.org/docs/12/runtime-config-replication.html states wal_level must be set to replica or higher to allow connections from standby servers not hot_standby as is alluded to the external reference. So hot_standbyis not longer a valid value? But to further the confusion, I have not found what distinguishes a replica from a logical replication, just https://www.postgresql.org/docs/14/runtime-config-wal.html

• The https://www.postgresql.org/docs/12/logical-replication-quick-setup.html feels too 'quick' for my tastes. While it tracks pretty much the external source... what's missing from the 'quick' approach?

• I also have had difficulty in searching the docs for how the replica should be configured. How to determine whether as server is hot or warm?

note: I realise this is not a single question, but the issues are still part of the object of configuring a main db and a replica db

I am going to run maintenance procedure similar to described in https://stackoverflow.com/questions/53746197/cannot-delete-rows-from-a-temporal-history-table/53749664#53749664 .

The stored procedure will SET SYSTEM_VERSIONING = OFF , delete 3 million rows on temporal table and set it ON. It will take some time.

The question is how likely transactions on main table be impacted by running the stored procedure?
Am I right that selects on the main table will not be impacted?
What about inserts/ updates on the main table?

Information for reference from Microsoft documentation https://docs.microsoft.com/en-us/sql/relational-databases/tables/stopping-system-versioning-on-a-system-versioned-temporal-table?view=sql-server-ver16#important-remarks

When you set SYSTEM_VERSIONING = OFF and don't remove drop the SYSTEM_TIME period, the system continues to update the period columns for every insert and update operation.

I am developing an ERP using PHP and MYSQL. I have a product page and an invoice page. I am facing an issue, if I delete a product from products page, and I already created an invoice with this product, the invoice will be deleted, and I don't want that to happen. I want that the invoice will stay regardless if the product inside the invoice is deleted or not. Maybe this is happening because I am storing the id of the product in the invoice table (and joining table when select) not the product data itself. Now, I have 3 questions regarding this:

1: What is the best way to store data of a product in invoice? Is it by bringing the id of the product, or by bringing all info related to the product? Actually is it a good practice to store id of the product in the invoice (as I heard before) not all the product data, especially in this situation?

2: What is the best way to delete the product in this situation? Is it for example, by creating deleted or delete_status column and assign 0 if the product is not deleted and 1 if it is deleted, and display all products in the invoice page regardless of delete status (and select products that are not deleted in the products page). Or, just by adding product info to the invoice not the product id, so this way no need to join tables together.

3: Same thing for update, if I change product name, it will change also on an already created invoice with the same product. What is the best way to update the product info and prevent this from happeneing?

One last thing, does foreign key constraints will help me for these situations? Because I tried it once, and I think it makes things worse.

I can't seem to find the answer elsewhere, and i apologize if the question has been done before, but is it a way to force a MySQL server (community edition) to organize the idb files into subfolders ?

Currently, all the files are located into the data in programData (for Windows). I have 1 table (1 idb file) per week, but i'd like them to be automatically organized by year or month of creation. This to avoid having a folder with +1000 and ease up maintenance.

Thanks in advance !

I have a Centos 7.9 server with MariaDB version 10.2.43. All of a sudden on May 2 2022, I have started receiving mysql down alerts. On checking I could see below log information in logs.

Error 2:- May  2 22:09:03 server1 mysqld: 2022-05-02 22:09:03 140534819583744 [ERROR] InnoDB: Page [page id: space=57618, page number=185] log sequence number 6629091300064 is in the future! Current system log sequence number 6629091254594.
May  2 22:09:03 server1 mysqld: 2022-05-02 22:09:03 140534819583744 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

I have managed to recover the data using innodb_force_recovery to the value of 5, restore the databases from backups/dump. I have some doubts regarding the issue. I have checked entire log information for Mariadb and I could see below log entry repeatedly logged days before the InnoDB Crash.

[ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'

I wanted to understand / Clarify few points regarding this issue.

1. Is the aria_log_control error a critical error and is it connected to InnoDB Crash? Why this error?

2. How come the Page log sequence number is in the future for Mariadb service? Is it related to some kind of disk corruption or memory issue?

3. How to monitor this on production environment and prevent the same?

4. Is this a bug with maridb 10.2.43? Will updating to latest stable 10.6 version will fix it?

I am learning about capturing trace via profiler.

I understand that the purpose of capturing trace via profiler is to log events happening on the sql server without impacting performance, because logging via the profiler GUI impacts performance.

Once I have the trace I can open it in profiler and view the events without having impact on sql server. What else can I do with the trace?

I'm trying to investigate a lock situation. I know how to see the current locks on DB by querying sys.dm_tran_locks but what I want is to take a specific SQL Query and having a kind of 'analysis' on it to calculate what locks it will generate. Like an execution plan for locks?

It can also be a way where I run a query and afterwards I see what locks were generated, for me it's the same. I just need a way to log/see logs generated since I currently can only see 'real-time' locks being held.

I specifically need to know as many details of those locks as possible: type, mode, object it's holding, etc.

I'm on Azure SQL Database.