Does backing up a database shrink the transaction log size?

The transaction log file (LDF) is made up of lots of virtual log files (VLFs) inside. Think of it like a cabinet with several pull-out drawers. You could choose a large cabinet, or a small one - but it's still going to be a fixed size with just different numbers of drawers.

As SQL Server works, it puts your transactions into drawers (VLFs.) It starts at one end of your cabinet, fills up the first drawer, then when space runs out in that drawer, it moves on to the next drawer.

When you back up the transaction log, what you're really doing is:

Finding the first drawer that has transactions in it that haven't been backed up yet
Copying those transactions elsewhere
If that drawer is no longer actively in use (because SQL Server has moved on to the next drawer), then you're marking that drawer as available to reuse (think of it as throwing all the contents out)

Backups don't change the size of your cabinet (log file).


How can I invoke an SSIS package from SQL Agent, via T-SQL, using a proxy

I ended up using a PowerShell step in SQL Agent to work around this: job step type "PowerShell" with a short script, which uses Invoke-Sqlcmd to call the stored procedure. Feels awkward, but it works under the alternate credentials.


Fetch rows which belongs to a certain geo location

Keeping lat long like that isn't spatial. You need to make a point

Point(long,lat)

I would store the result of the above on the row, rather than individual lat/long cords. Regardless of what you store on the row, that's what you have to work with. Not storing that on the row will just be slower, as you'll have to calculate it every time.

Next what you want is the Bounding Box test. In MySQL they're designated with an MBR prefix.

SELECT *
FROM table
WHERE ST_Contains(
  ST_Buffer( Point(lng,lat), DISTANCE_IN_DEGREES ),
  Point(google.long, google.lat)
);

You'll have to calculate DISTANCE_IN_DEGREES yourself. MySQL doesn't have a geography type, so you can't just use a fixed distance like meters, miles, or feet. You have to do the math yourself. This is a lot of work in itself.

ST_Contains doesn't use an index. For that you'll need MBRContains. Or you could just use PostgreSQL, for that it'll look like this,

SELECT *
FROM table
WHERE ST_DWithin(
  ST_Buffer( ST_MakePoint(lng,lat)::geography, DISTANCE_IN_METERS ),
  ST_MakePoint(google.long, google.lat)::geography
);

Not just is that shorter, but from the docs

  This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

So you don't have to use two functions like you would in MySQL MBRcontains (bounding box w/ index), and ST_Contains.


After some experimentation, I figured out that the code works if I don't prefix my variable names with a '@':

DECLARE D_Type varchar(1);
DECLARE D_TableId tinyint;
DECLARE D_IsActive bigint;
SET D_Type='U';
SET D_TableId = 2;
SET D_IsActive = (SELECT IsActive FROM AuditDB.ExportLogTableDefinition WHERE TableId=D_TableId);
IF D_IsActive=1 THEN


How to remove the duplicate records from primary filegroup after partitioning?

Once SQL Server acquires disk space from the operating system and allocates it to a database it will not voluntarily give it back.  It takes time and effort to allocate this space so it is more efficient for the database to hang onto it once it has acquired it.  If you genuinely have moved the data and not copied it (you would have to post your SQL for us to be sure) there will be a lot of free space in the files of the PRIMARY filegroup. Have a look at this post for hints how to find the free space in a file.

To force SQL Server to hand back this free space to the operating system you will have to uses a SHRINK command.  This is well documented in Books Online.  There are potential performance problems with SHRINK, so use it after proper consideration.


How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?

There's currently no way to modify which unique index a foreign key constraint is "attached to" because this is supposed to be an internal implementation detail, the same way that a unique constraint uses a unique index behind the scenes to enforce the constraint.

In fact, as you saw, if you have multiple candidate indexes, the one the foreign key attaches to seems non-deterministic (it might go by index id, index width, or something else; I haven't played with it in-depth), and it cannot be specified in the DDL statement either (memory says there's a Connect item requesting that feature, though).

So in this case, at least for now, you'll need to drop both the foreign key and the existing unique constraint before re-establishing the relationship, to ensure your change script works correctly in all scenarios.


Cap on number of rows matching a condition in Postgres

Your program could manually number the records for each user then you could put a unique constraint on the combination of the user_id and the recordnumber (or make it the primary key) and put a check constraint on recordnumber cheking it is between 1 and 30.


postges: psql csv import fails although file exists

The most common reason for COPY failing is what is suggested in the hint, but then the error is a "Permission denied".

Since you get "No such file or directory", and a simple ls shows that the file exists, there are first a few plausible explanations that you want to check:

/home/centos/bugs.csv is a soft link pointing to an non-existing file. Use ls -l to be sure an check the destination if it's a soft link.
127.0.0.1:5432 is redirected at the network level to a different machine. This technique is widely used to connect to remote services through SSH tunnels.
Your OS uses fancy security/isolation features that forbids postgres to read /home/centos/bugs.csv without issuing a Permission denied error. Try ls -l /home/centos/bugs.csv under the postgres OS account.
Your server and client don't run under the same MOUNT namespace, due to containerization.

In cases #2 #3 or #4, just use \copy as suggested in the HINT part of the error message.


Issues with 'plus symbol' output in PSQL on update query

This:

UPDATE master_list
SET group = 'JustAdoptingAdvice'
WHERE group = 'JustAdoptedAdvice+';

should work perfectly fine if the field was really JustAdoptedAdvice+, but I don't think it is. psql is showing that + to mean continuation ... I suspect there's actually a newline there.

UPDATE master_list
SET group = 'JustAdoptingAdvice'
WHERE group = E'JustAdoptedAdvice\n';

There could be whitespace after the newline too, so maybe even:

UPDATE master_list
SET group = 'JustAdoptingAdvice'
WHERE group ~ 'JustAdoptedAdvice[[:blank:]\n]+';

which says "JustAdoptedAdvice followed by one or more of tab/space/newline".

Demo showing it's likely to be a newline:

regress=> CREATE TABLE t4(x text);
CREATE TABLE
regress=> INSERT INTO t4(x) VALUES (E'JustAdoptedAdvice\n');
INSERT 0 1
regress=> SELECT * FROM t4;
         x
-------------------
 JustAdoptedAdvice+

(1 row)

To be sure, look at the hex representation of the utf-8 encoding of the string:

regress=> SELECT encode( convert_to(x, 'utf-8'), 'hex') FROM t4;
                encode
--------------------------------------
 4a75737441646f707465644164766963650a
(1 row)

You'll see that it ends with 0x0a, which is a newline.


That depends on your disk subsystem.

A full backup is going to involve a metric crud-load of I/O (that is a technical term).  That is going to put a huge load on your I/O subsystem.  Depending on how I/O bound your system is, how much spare bandwidth your I/O subsystem has, how fast your I/O subsystem is, etc. the impact will range from "yeah, maybe it's a bit slower" to "everything times out, the system is unusable, users will be visiting the DBA team shortly with flaming pitchforks to express their displeasure."

I am hard-pressed to imagine a whole lot of situations where I'd ever want a full backup running on a non-trivial production database during anything close to business hours.  If you have a real need to do this, I'd tend to be investigating things like DataGuard that would potentially allow you to offload the load of doing the backup to the standby database.


In which extent can I use the Oracle Data Provider for .NET Managed Driver?

Oracle Data Provider for .NET FAQ

  Q: I bundle ODP.NET with an application I've built, which I want to distribute or sell. Does Oracle charge licensing fees for this?

  A: In most cases, Oracle does not charge any fees for distributing the
  Oracle client nor ODP.NET with a third-party application. However, it
  is best to consult the licensing agreement from which you acquired
  rights to the Oracle software. If you purchased Oracle licenses
  directly from Oracle, consult your Oracle licensing contract or your
  Oracle sales representative. If you downloaded ODP.NET from OTN,
  consult the licensing agreement that you agreed to prior to
  downloading the software.


Try changing your * with just the column name. Although it means the same, now it should be clear to SQL Server:

SELECT
    [StoreName] = (SELECT storename
                     FROM OPENQUERY(192.168.5.812, 'SELECT storename
                                    FROM stores') x
                    WHERE x.storename = y.storename)
    [StoreManager] = y.storemanager
FROM
    managerinfo y ;


This question is very broad, and the answer depends very much on the nature of your workloads and your planned SQL Server setup. However, assuming your future hardware supports NUMA (why wouldn't it?) and given that

  With SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. [...] the SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores

one might reason that the vendor thinks 8 cores per socket is the optimal configuration in most cases.

You might also find this old article useful.


The (possibly) most efficient way, first group, then join:

SELECT
    product_name, c
FROM
    (
      SELECT product_id, COUNT(*) AS c
      FROM order_details
      GROUP BY product_id
    ) AS counts
    /* The trick is now to JOIN this virtual table with your product table */
    JOIN product p ON p.product_id = counts.product_id
ORDER BY
    c DESC ;

You can check that it works in this SQL Fiddle (scaled by a 10 factor).

This SQL is very close to standard, so it should work not only with MySQL, but with most SQL databases (I've tested it with MySQL, PostgreSQL and SQLite).

Most contemporary databases will probably execute the following query with the same efficiency, provided that NOT NULL and REFERENCES are in place (and you type a bit less, and probably make it easier to understand):

SELECT
    p.product_name, COUNT(*) AS c
FROM
    order_details od
    JOIN product p ON p.product_id = od.product_id
GROUP BY
    p.product_id
ORDER BY
    count(*) DESC ;

Or, without using aliases:

SELECT
    product.product_name, COUNT(*) AS c
FROM
    order_details
    JOIN product ON product.product_id = order_details.product_id
GROUP BY
    product.product_id
ORDER BY
    count(*) DESC ;


Database Implementations of ORDER BY in a Subquery

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY optimized away anyway.

And in Oracle and PostGres, just because the syntax is supported, does not mean it is obeyed. And just because you observe it as being obeyed in some scenario, does not mean that it will continue to be obeyed as new versions come out or with subtle changes to your data, statistics, the query itself, or the environment.

I can assure you that, without a doubt, if you want a guarantee about order, you need to put the ORDER BY on the outermost query. This should be a doctrine you hold close no matter what platform you're using.

You are asking for a link that officially states that something is not supported. This is like looking in your car owner's manual for an official statement that your car cannot fly.


Column to be populated using other columns values

The question on why you think you need that aside, you can simplify the concatenation using concat_ws()which you can easily use in the trigger as well:

CREATE OR REPLACE FUNCTION update_en()
RETURNS TRIGGER AS
$$
BEGIN
  NEW.codigo := concat_ws('.', new."PP", new."MM", new."DD", new."BB", new."QQ", new."LL", new."ID", new."UU");
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;


PostgreSQL 9.3, set database (or all) to read only mode

default_transaction_read_only  might work for your case.
The command below will make new sessions start their transactions in read-only mode:
ALTER DATABASE name_of_database SET default_transaction_read_only to ON;

and to go back to normal:
ALTER DATABASE name_of_database SET default_transaction_read_only to OFF;

This takes effect for new sessions, not for sessions that are already connected.
The effect can also be obtained at the user level (which might be more convenient in some cases):
ALTER USER name_of_user SET default_transaction_read_only to ON;

While in read-only mode, a session can always issue SET default_transaction_read_only to OFF; to get out of this mode, or start any individual transaction with BEGIN read write;. But in general programs don't do that, so that default_transaction_read_only is sufficient to deny them the ability to write.


Is there some drawback to define SPARSE columns?

Columns which are SPARSE but don't have NULL values in it will take up more space than a non-SPARSE column.  You'll only want to use a SPARSE column when the data is actually sparse.


MySQL create table: error 1005 errno: 150 "Foreign key constraint is incorrectly formed"

At times the best way to overcome an error within your InnoDB table is to check the InnoDB table status by using this command.
   SHOW ENGINE INNODB STATUS;

This will enlighten you on what you are doing wrong.


Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement?

You can easily try it out with the following script:
CREATE TABLE my (col1) AS
   SELECT * FROM generate_series(1, 100000);

ANALYZE my;

CREATE INDEX my_null ON my (col1) WHERE col1 IS NOT NULL;

/* use a prepared statement similar to JDBC */
PREPARE stmt1(integer) AS SELECT * FROM my WHERE col1 = $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt1(1);
EXPLAIN (COSTS off) EXECUTE stmt1(2);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);
EXPLAIN (COSTS off) EXECUTE stmt1(4);
EXPLAIN (COSTS off) EXECUTE stmt1(5);
/* from the sixth execution on, a generic plan is used */
EXPLAIN (COSTS off) EXECUTE stmt1(6);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);

DROP INDEX my_null;

CREATE INDEX my_pos ON my (col1) WHERE col1 > 0;

/* use a prepared statement similar to JDBC */
PREPARE stmt2(integer) AS SELECT * FROM my WHERE col1 > $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(1);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99998);
/* PostgreSQL continues to use a custom plan! */
EXPLAIN (COSTS off) EXECUTE stmt2(0);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);

DROP TABLE my;
DEALLOCATE ALL;

Here is an SQL fiddle that tuns the code.
Explanations:
With a prepared statement, PostgreSQL can cache query plans for the database session.
Using such a generic plan that stays the same regardless of the parameter values has the advantage that plan time is saved.
But PostgreSQL doesn't always use a generic plan, because sometimes generating a custom plan that respects the parameter value can be better.
To decide what to do, PostgreSQL uses the following heuristic:

The first five executions will always use a custom plan.

If the estimated cost of the custom plans is not cheaper than the estimated cost of the generic plan, PostgreSQL will use the generic plan from the sixth execution on.

From PostgreSQL v12 on, you can configure the behavior with the plan_cache_mode configuration parameter.
Why does the first statement use a generic plan?
For the first statement, an index scan can always be used, even if the argument is NULL (because nothing has to be done in this case) . So PostgreSQL will use the generic plan after the fifth execution.
You can recognize the generic plan from the $1 in the EXPLAIN output.
Why does the second statement continue to use a custom plan?
With a generic plan, PostgreSQL can only use a sequential scan, because the index can only be used for some parameter values. For some of the first five executions the custom plan is significantly cheaper than the generic plan, so PostgreSQL continues using custom plans.
To answer your question:
If it is necessary to add an extra WHERE condition that makes sure that PostgreSQL knows it can use an index depends on the individual case.
You can add the extra condition without a disadvantage, it might help the optimizer and cannot do harm.
But even with the extra condition you cannot be certain that the index will be used.


Error while inserting into array of timestamps

1535673858 is neither in the ISO format, nor in any of the other valid date input formats.

To convert a Unix epoch value into the internal Postgres type, you must use the single-argument to_timestamp() function:

INSERT INTO ex(taken_ats) VALUES(ARRAY[to_timestamp(1535673858), to_timestamp(1535678856)]);


how much innodb_buffer_pool_size need to keep under my.ini file?

innodb_sort_buffer_size defaults to 1MB.  That is probably good enough.  However, since you have a huge amount of RAM, let's say 4MB might be a little bet better.

I assume you set innodb_buffer_pool_size to somewhere around 500G -- Setting that is the most important thing for InnoDB.  (Exactly 500 is not important.).  And innodb_buffer_pool_instances should be 16?

The above recommendations assume that all (or most) of the use of the server will be for MySQL.  And it assumes you are using InnoDB, not MyISAM.


How to get all logged in users to SQL Server database for this week?

You have a few options, some of which were already pointed out.

First, though, if these events haven't been captured so far there is no "historic" way of obtaining the data. There may be ways to get some of the data but chances are you won't get all or even most of it.

Going forward, however, it seems you only want those who successfully connect to the instance. This can be complicated if something such as connection pooling with a sql login, but I'll leave that as something for you to decide on how you want to track it back to the users.

Server Side Trace

Even though traces are deprecated they still work well. If it's just a successful login needed then this can be fairly low overhead. It has well known functions to load into tables and do your analysis.

http://msdn.microsoft.com/en-us/library/cc293613.aspx

Extended Events

Since you only need the successful logins, there is an event "sqlserver.login" that can work well for you. Send it to an event_file target and it's fairly simple as well. There are also functions to load the files and do your analysis.

http://technet.microsoft.com/en-us/library/bb630317(v=sql.105).aspx

Logon Triggers

Probably my least favorite option but here for completeness. The logins could be logged to a table, but why re-invent the wheel? Trace and extended events will do this for you.

http://msdn.microsoft.com/en-us/library/bb326598.aspx

SQL Audit

I didn't bunch this with extended events, even though in reality it leverages the same framework. This is an enterprise only feature, and while it can do this and is targeted for items such as this... I feel most aren't running enterprise edition.

http://msdn.microsoft.com/en-us/library/cc280386.aspx


SQL Server Replication or Backup via sftp

In order for replication to work, the publisher and subscriber need to be able to talk to the distributor directly so I don't think this will work in your current setup.

Depending on the amount of data you're producing, a version of log shipping may give you what you're looking for, but you are limited by the database being in a state that allows you to restore further logs and you will still get some disruption when the logs are restored.

The two 'seamless' options I can think of to give you more frequent updates are:
1) restoring your backups to a new database on the on prem server and then using SSIS or stored procedures etc to push this into your actual database(s).
2) using Enterprise Edition and AlwaysOn groups to restore the data to the primary server, which will then be replicated over to the read only secondary which your reporting services etc are pointing at.


You will have to do it the way you proposed in your question.

In cases of many columns, you can get the code generated for you. I find string manipulation in Excel really useful for generating SQL code.


How to get cross apply to operate row by row on a view?

It is not possible to fully guarantee evaluating the view per row of the outer query, without using something that introduces a new T-SQL execution scope, for example a non-inline (multi-statement, BEGIN...END) table-valued function. This is pretty much the advice given in response to your previous question How to use merge hints to isolate complex queries in SQL Server.

  At one time I thought there was a claim that cross apply was guaranteed for row by row execution when it calls a UDF

This does not apply to inline table-valued functions, since the definition is expanded into the calling query before optimization begins.

That said, there are some things you can do to strongly encourage the desired outcome.

  The execution plan shows that the execution branch for the view part is ignoring the predicate on ObjectId whereas in the original case it uses them to perform index seeks.

You are expecting the ObjectId values to be evaluated "inside the view" using index seeks for each driving row. This is the correlated nested loops join (apply) style of execution. Note that using the APPLY T-SQL language element does not guarantee that physical execution will use the apply style.

It very much sounds as if SQL Server is choosing to execute with the ObjectId values tested at the Nested Loops Join operator instead. This is a non-correlated, or naive, nested loops join execution pattern.

Most likely, this is caused by the join hints you are using inside the view. Join hints are generally to be avoided, because they greatly restrict optimizer freedom, and not just for the physical type of join. In particular, join hints also force the order of joins for the whole query (just as if you had use a FORCE ORDER hint) and prevent several optimizations related to aggregation placement and strategy, and much else besides.

If you really must have join hints inside a view (something I would strongly suggest you avoid in general), you might find the most reliable way to get the plan shape you desire is:

Create an inline (RETURNS TABLE) function using the view definition (not referencing the view).
Provide @ObjectId as a parameter to the function.
Placing a predicate using the parameter within the function body in a way that makes an index seek more likely.
A FORCESEEK table hint inside the function may be used if truly every use should result in a seek.
Call the new inline function using APPLY.

I generally dislike using specific syntaxes and hints in an attempt to force a certain physical plan shape. You may have more general success by parameterizing queries and guaranteeing the plan shape using a plan guide.


Is it possible to use Change Tracking on a Replication target?

does anyone know if it's possible to use Change Tracking on a replication target?

When you say replication target, do you mean subscriber ? Irrespective, of it, CT will work with replication.

Things to consider when implementing Change Tracking (CT):

Make sure you have retention & clean up of the data in the hidden CT tables.
Enable CT on the tables that you need. Dont go haywire and enable it on all the tables in the database.
Read carefully the article written by Kendra - Performance Tuning SQL Server Change Tracking


You can set Maxsize on both the data and the log files of your database.
If the Max size of your database is 20GB you have to make sure the combined max size of both is 20 GB.

Remember to allocate enough room for the log file, so you don't run into issues early on.

You can do this by going into SSMS, right click your database, properties and go to Files.
If you want to script it, it can also be done.
Take a look at the BOL to find the syntax :   ALTER DATABASE FILE

Perhaps this is also useful for you:
Transaction Log Administration Best Practices


Your IN subquery has 3 columns, so it can't be compared with the one value of Extras.foodId.

You could use EXISTS instead and move the IN condition inside, making it a correlated subquery:

select e.*
from Extras as e
where e.extra = 'Potatoes'
  and exists
      ( select *
        from Food as f
          join t1 on t1.id = f.id
          join t2 on t2.id = f.id
          join t3 on t3.id = f.id
        where f.f = 'something'
          and e.foodId in (t1.chickenId, t2.beefId, t3.fishId)
      ) ;

Another possibility would be to keep the IN syntax you have and convert the 3 columns into one (and each row into 3) with CROSS APPLY:

select e.*
from Extras as e
where e.extra = 'Potatoes'
  and e.foodId in
      ( select v.foodId
        from Food as f
          join t1 on t1.id = f.id
          join t2 on t2.id = f.id
          join t3 on t3.id = f.id
          cross apply
            ( select t1.chickenId union
              select t2.beefId    union
              select t3.fishId
            ) as v (foodId)
        where f.f = 'something'
      ) ;

If the first query you provide is guaranteed to return one row always, you could also use a simple join without any subqueries:

select e.*
from Food as f
  join t1 on t1.id = f.id
  join t2 on t2.id = f.id
  join t3 on t3.id = f.id
  join Extras as e
    on e.foodId in (t1.chickenId, t2.beefId, t3.fishId)
where e.extra = 'Potatoes'
  and f.f = 'something' ;


Counting duplicate instances within column in SQL when grouping by another column

This should do what you require (fiddle):

Create table j (tid serial, nom text, jid text);

Insert into j (nom, mid) values
('Sam', 'a'),
('Sam', 'b'),
('Ali', 'b'),
('Tim', 'c'),
('Tim', 'd'),
('Finn', 'a'),
('Mona', 'c'),
('Bill', 'z'),
('Sam',  'x'),
('Sam',  'y'),
('Finn', 'w');

Then:

With dup_jobs AS
(
  Select jid from j
  Group by jid
  Having count(1) > 1
)
Select j.nom, count(1)
From j
Join dup_jobs dj
  On j.jid = dj.jid
Group by j.jid
Order by j.jid

Result:

Ali 1
Finn 1
Mona 1
Sam 2
Tim 1

Note that Bill has no jobs in common with anyone else and therefore doesn't appear. Sam's jobs 'x' and 'y' are not shared with anyone else and similarly aren't counted.

Interesting question (+1). p.s. welcome to the forum! :-) Oh, and BTW, in future could you please provide sample table structures (DDL) and data (DML) in the form of a fiddle - it helps us to help you and if there's only one definitive source for the tables and data, it helps prevent errors and duplication of effort.


Best practise for polymorphic associations in MySQL

This question (in various forms) crops up regularly on this forum.

It is called EAV (Entity Attribute Value). For good reason, it is called an antipattern. There are many reasons not to use this form of data model. They are outlined here, here and here (and links within + look at other posters' replies in the same threads).

Not everyone agrees that EAV is always a bad thing, just most of the time. Aaron Bertrand (a big hitter on this group) has written this post on the matter. My advice, however, is to stick with a conventional design and (probably) make life easier for yourself down the line.

Why not just have three main tables as you describe

Person
Company
Loan

And two "subsidiary" tables

Address
Note

Then Person, Company and Loan can have Address_id and Note_id fields. No need for complicated linking tables (at least not yet :-) ). Or to start with, you could even include Note and Address in your main table (if there's ever going to be one Address and one Note (BLOB) field)?

Whatever you do, avoid EAV until you're an experienced data-modeller. p.s. welcome to the forum!

[EDIT]

Could you tell us which RDBMS you are using? It might have some bearing on my replies. The moderators here don't like long ping-pong type chats.

I  "threw together" a diagramme and script which should get you started.

Any further problems/issues, let me know. Much of app design and data modelling is trial and error - you produce a data model, start programming against it and you find all sorts of ways in which your original ideas can't work. That's what prototyping is for. Give it a bash and then work around issues.

And the script from that diagramme.

CREATE TABLE IF NOT EXISTS `mydb`.`Person` (
  `Person_id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NOT NULL,
  `Employed_by` INT NULL,
  PRIMARY KEY (`Person_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Loan` (
  `Loan_id` INT NOT NULL AUTO_INCREMENT,
  `Amount` DECIMAL NOT NULL,
  `Other_fields` VARCHAR(45) NULL,
  PRIMARY KEY (`Loan_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Person_loan` (
  `Person_id` INT NOT NULL,
  `Loan_id` INT NOT NULL,
  INDEX (`Person_id` ASC),
  INDEX (`Loan_id` ASC),
  INDEX (`Person_id` ASC, `Loan_id` ASC),
  CONSTRAINT `Person_Loan_P_FK`
    FOREIGN KEY (`Person_id`)
    REFERENCES `mydb`.`Person` (`Person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Person_Loan_L_FK`
    FOREIGN KEY (`Loan_id`)
    REFERENCES `mydb`.`Loan` (`Loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Company` (
  `Company_id` INT NOT NULL AUTO_INCREMENT,
  `Company_name` VARCHAR(45) NULL,
  PRIMARY KEY (`Company_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Works_for` (
  `Person_id` INT NULL,
  `Company_id` INT NULL,
  INDEX `Works_For_P_FK_idx` (`Person_id` ASC),
  INDEX `Works_For_C_FK_idx` (`Company_id` ASC),
  CONSTRAINT `Works_For_P_FK`
    FOREIGN KEY (`Person_id`)
    REFERENCES `mydb`.`Person` (`Person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Works_For_C_FK`
    FOREIGN KEY (`Company_id`)
    REFERENCES `mydb`.`Company` (`Company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Company_loan` (
  `Company_id` INT NOT NULL,
  `Loan_id` INT NOT NULL,
  INDEX `Company_loan_C_L_PK` (`Company_id` ASC, `Loan_id` ASC),
  INDEX `Company_Loan_L_FK_idx` (`Loan_id` ASC),
  CONSTRAINT `Company_Loan_C_FK`
    FOREIGN KEY (`Company_id`)
    REFERENCES `mydb`.`Company` (`Company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Company_Loan_L_FK`
    FOREIGN KEY (`Loan_id`)
    REFERENCES `mydb`.`Loan` (`Loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


How to make a transition from SQL Server DBA to Oracle?

Oracle and SQL Server have a number of architectural and idiomatic differences, and several key bits of terminology are used differently in the documentation.  It's quite a few years since I did this, but some of the major idiomatic differences are:

Oracle has no direct equivalent to tempdb.  Global temp tables are persistent entities and you do not create them on the fly as you would with a temp table in SQL Server.
Idiomatic PL/SQL is much more procedural than idiomatic T-SQL.  On the flip side, PL/SQL has features to support fast cursor ops including parallel loop constructs.  Often, intermediate cursor results are used in ways similar to the sort things one would use temp tables for in SQL Server.  Look up how to use '%' to get some polymorphism in cursor variable types.
SQL*Plus is a command line tool similar to SQLCMD.  On a vanilla installation it may be all you have available.  If you want a GUI tool like SSMS you can get SQL Developer or third party tools such as TOAD.  TOAD is quite expensive.
Learn the role of rollback segments (used in Oracle's MVCC) and the joys of the 'snapshot too old' error.
Get a metalink account if possible.
Read the concepts guide.  It has a lot of useful starter information.
Tablespaces are roughly equivalent to filegroups.
SQL and the SQL dialect embedded within PL/SQL are not quite the same thing in Oracle.
Oracle didn't support recursive CTEs until quite recently.  You can do recursive queries with CONNECT BY.
Read the docs for RMAN to see how to do backups.  You can do a few neat tricks like merge differential backups into a single restorable backup.
The system data dictionary has 'ALL', 'USER' and 'DBA' versions of the tables.  'USER_' shows objects owned by the login.  'ALL_' shows all objects the login can read.  'DBA_*' is
a global view, but you have to have appropriate permissions to see that.  You will probably use the data dictionary more with Oracle than with SQL Server, so get to know it.
Clustered tables are called 'index ordered tables' in Oracle.  'Clustereed tables' in
Oracle refer to a very different feature.  In Oracle, a clustered table is a facility that allows physical storage of master/detail tables to be comingled based on a common key.  The feature is not widely used.
Indexed views are called 'materialized views' in Oracle.  They work somewhat differently, with enough nuances to merit their own question.
Oracle does not have a concept equivalent to a 'database' in SQL Server.  In Oracle, a schema is just the owner of some database objects.
PL/SQL has an explicit module structure called 'packages'.
Look at using sequences instead of identity columns.
Getting a query plan is a bit fiddlier in older versions of Oracle (prior to 10g).  You have to create a table (of the right format) to dump the plan to, then 'EXPLAIN PLAN' the query into the table to get the query plan.  You can then get the query plan back out of the table.  Everybody and their dog have favourite query plan queries.  Do a google search and find one, then frig it to taste.  10g added a package called dbms_xplan, which simplifies working with query plans.
If you're running Oracle on a unix or linux host, having a working knowledge of shell scripting wouldn't hurt.
Oracle has support for autonomous transactions - you can commit an inner autonomous transaction and expect it to stay committed if the parent rolls back.  SQL Server nested transactions are not true nested transactions and have different semantics.  You could emulate this behaviour with a CLR stored procedure in SQL Server by opening an independent connection and performing the autonomous operation through that.

Oracle comes with less B.I. tooling out of the box, although Oracle do sell OLAP servers, reporting tools and ETL tooling.

Oracle bundle a bulk load tool called SQL*Loader that is similar in function to bcp.
The closest equivalent to SSIS is Oracle Warehouse Builder (OWB), but this is a separate item and not bundled with the base system.
Oracle make a reporting tool called Oracle Reports, but this is also not bundled with the base system.  This is the closest equivalent to Reporting Services.
Oracle also make a ROLAP tool called Discoverer.  This is the closest product to Report Builder that Oracle produce.  More recent versions ('Drake') have support for Oracle's OLAP engine.
Oracle used to have an OLAP product called Express.  At one point they built it into the core Oracle DBMS engine, storing its native data structures in opaque blobs.  It is not totally compatible with Express, and has a propritary interface with very little support from third-party OLAP front-end tools.
Oracle B.I. Enterprise Edition is what used to be Siebel Analytics.  This has some OLAP functionality but I'm not familiar with it.  Maybe someone who has used it might like to comment.  Without directly admitting that Siebel was a completely separate product from its own B.I. tooling, Oracle stated that there was 'no upgrade path' from Oracle B.I. Standard Edition to Enterprise Edition.  Note that both the Standard and Enterprise B.I. suites are separate products and not bundled with the base system.
In practice, the closest thing to SSAS sold by Oracle is Essbase (formerly Hyperion).  This is a separate product and not bundled with the base system.

Not surprisingly, Oracle's B.I. strategy is viewed by industry pundits as something of a dog's breakfast.

There are probably other things that I can't think of OTOH; I'll add them as I think of them.


Mirroring in SQL Server Standard Edition

Check the following system view:

SELECT *
FROM master.sys.database_mirroring;

There is a wealth of information in there about the status of your mirrored databases.

If the column mirroring_state_desc contains 'SYNCHRONIZED' then it is indeed synchronized.

What SYNCHRONIZED indicates is that all outstanding transactions from the master have been successfully committed into the mirror database.

If it shows a status of SYNCHRONIZING then the mirror is "behind" the principal to some degree.

You can also use the Database Mirroring Monitor app to monitor the status of mirrored databases.


How to solve "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" error?

I do not have any clear explanation for this problem solution. However I used SID instead of service name in connection string and it worked.

Edit: A service name is more flexible than a SID would be. A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name.A database on the other hand has a single SID. And a single SID goes to a single database. So I would say it is safe to use SID rather than service name.


What is the reasoning behind the CAP theorem?

OK, let's imagine that you have a distributed database.  Let's say you have a node in Oregon and one in California.  The CAP theory says that you will run into problems when setting up this type of database.

For example, if you query data from one database, it needs to be the same as the data in the other database.  This insures that whatever value you have in one database is guaranteed to be in the other (Consistency of the CAP theory).
Doing this allows you to update the data in one database and query it from another, getting the same results.

When we update the data in the Oregon node, the data is sent to the California node so that the databases are consistent.  In order to truly maintain consistency, we have to insure that both databases get the update before either are allowed to truly save the data (two-phase commit using distributed transactions).  In other words, if the California database can't save the data for some reason (e.g. hard drive failure), then the database in Oregon will not save the data and will fail the transaction.

The problem with distributed transactions like the one above comes when we want to have high availability.  In this scenario above, the process of trying to get both databases in sync is a very, very slow process.  (Imagine, we have to send the data from Oregon to California, make sure it gets there, make sure that both databases have locks on the data, etc.)  This causes major problems when we want a system that is fast and responsive even during times of high demand.  (This is the Availability of the CAP theorem.)

Usually, what we do in order to insure high availability is we use replication instead of distributed transactions.  So instead of guaranteeing that California can accept the data, we just go ahead and store it in the Oregon node and then send the data to California when we get around to it.  This guarantees that we can always store the data, regardless whether California is ready to store the data or not.

This improves Availability, but at the cost of Consistency.  See, if someone updates the data in Oregon and then someone (at the same time) reads the data in California, they are not getting the new data--the databases are no longer consistent.  In fact, they won't be consistent until Oregon sends the data to California!

So, that's the Availability -vs- Consistency trade-off.

Partition Tolerance is the third aspect of the CAP theory.  Partitioning is, in this context, the idea that a database (or other distributed system) can break into separate sections and still function correctly.

The question becomes, what happens when both databases are running correctly, but the link from Oregon to California is severed?

If we update the database in Oregon, we need to get the data to California one way or another (distributed transaction or replication).  However, if the link between the two is severed, then the system has become partitioned and the databases are no longer linked together.

When this happens, your choices are to stop allowing updates (to maintain Consistency) at the cost of Availability or to allow the updates (to maintain the Availability) at the cost of consistency.

As you can see, partition tolerance creates direct trade-offs between Consistency and Availability.

There's obviously more to it than that, but those are a couple of examples on how these three major aspects of distributed systems work for and against each other.  Julian Browne's explanation of CAP theory is an excellent place to learn more.


Critical query plans removed from sys.dm_exec_query_stats while trivial items are kept

There are a bunch of different reasons why a statement might be recompiled, including both performance and correctness-related reasons.
For instance, an index used by the query may have been dropped, in which case the plan must be recompiled because it genuinely can't be run in that state.
Or statistics may have been updated enough that an automatic recompilation occurs.
Depending on the load on your server, and how frequently this statement really gets recompiled, you might be able to capture the actual reason using this extended events session:
CREATE EVENT SESSION [recompiles] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
(
    WHERE ([sqlserver].[query_hash]=(111))
)
ADD TARGET package0.event_file (SET filename=N'recompiles')
WITH (STARTUP_STATE=OFF)

This will fire for every recompile on the system, but will only store the ones with a query hash that matches what you put in the filter.
To be clear: don't start this thing up and then leave for the weekend.  Try it on a test system, try it briefly on production and make sure it's not too disruptive, etc.  Don't take down your server just to track this down
In case it's helpful, the XE defines all of these statement recompile reasons on SQL Server 2019:
SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = N'statement_recompile_cause';

map_value

Schema changed

Statistics changed

Deferred compile

Set option change

Temp table changed

Remote rowset changed

For browse permissions changed

Query notification environment changed

PartitionView changed

Cursor options changed

Option (recompile) requested

Parameterized plan flushed

Test plan linearization

Plan affecting database version changed

Query Store plan forcing policy changed

Query Store plan forcing failed

Query Store missing the plan

Interleaved execution required recompilation

Not a recompile

Multi-plan statement required compilation of alternative query plan


Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor

Question: How to prevent this error or work around it?

fsync ensures that the data is physically written to disk, so that in case of a crash or loss or power a few seconds after the backup, no data is lost in unflushed memory buffers. Unfortunately it doesn't seem to work in your case. You may add --no-sync to avoid this step, although the errors shown are probably harmless, in the sense that surely they don't affect the contents of the backup. You may also use sync as an external program if you're wary of skipping the sync.

  Additional question: Could I do a file level backup and copy the data
  folder from windows to linux, given an identical Postgres version and
  both servers stopped?

No. The data files are not portable in general, and especially not across totally different operating systems.


Your series table is compound. You need to split it up.

Series ( series_id, name )
Series_Book ( book_id, series_id, series_ordinal )

Now you can easily seek to the series_ordinal-1, series_oridnal+1 for the series. Storing the count() as number of books also seems redundant.

Genre and Theme are also pretty nebulous. That method of classification doesn't scale well, or work all that well with humans. Normally you want hierarchical tags, and cloud tags; otherwise, enter "is the Beatles pop or rock?"


Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables

A1: Almost always CPU pressure implies lack of INDEX or poorly formulated SELECT.

A2: Same pid -- so that connection is doing one LOAD after another without stopping?  That's OK.  One thing to check when you do the SHOW is the Time column -- it is the elapsed time for the current query if running, or the amount of time it has been idle (if it says Sleep).

A3: "...DATA_080" -- You have differently named tables, each with multiple partitions?  Or are you saying that "...DATA_080" is what you mean by "partitioning"?  There is a big difference.

A3: "InnoDB tables created with hundreds of partitions" -- inefficient.  How many such tables?  How many partitions?  What style of partitioning?  Let's see SHOW CREATE TABLE (with only a sampling of the partitions listed).

A4: Are the LOADs in the slowlog?  Crank long_query_time down to 0.3 to see if you catch something else.

A6: 40 GB vs 128GB of RAM.  You could increase the buffer_pool, but it probably won't change things much.  How much does top say that mysqld is using?  (I suspect the 90% is bogus.)

A7: "to improve the overall performance?" -- Wait!  I have not heard what the performance problem is.  So far, it sounds like the only heavy task is lots of LOADs?

Thanks for the attachments!

Observations:

Version: 5.7.20-log
120 GB of RAM
Uptime = 17d 00:34:34
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

You are writing (and flushing) 990 blocks per second.  That is rather high, and implies that you do have SSD drive(s).
You asked about performance; this seems to be the limiting factor, in spite of iostat not pointing it out.
How many rows in a typical LOAD DATA?

The large number of partitions leads to the need to open lots of partitions (which are effectively 'sub-tables').  Let's work on that by discussing the partitioning.\
Meanwhile change some settings:

open_file_limit = 10000
table_open_cache = 5000
table_definition_cache = 3333

Are many of the 4001 tables partitioned?!

Increase both innodb_page_cleaners and innodb_buffer_pool_instances to 16.

If it turns out you are using SSDs, change innodb_flush_neighbors to 0.

innodb_log_file_size is 1G; change to 5G.  However, it is not necessarily easy to do so.  Skip this recommmendation if necessary.

Perhaps increasing bulk_insert_buffer_size to 32M would be beneficial, especially since you are doing a lot of LOAD DATAs.

You mentioned not many SELECTs in the slowlog; what about UPDATEs? --

Com_update = 197 /sec
Com_update_multi = 0.48 /sec

I guess this would explain why SHOW PROCESSLIST does keeps seeing a different table:

Com_load = 21 /sec
Innodb_rows_inserted = 4829 /sec

And, does that mean that the average file being loaded has 230 rows?
And, with only 20 Connections/second, there must be hundreds of LOADs in a single connection.

What's in the Stored Routines.

Several ALTER TABLEs per minute (on average) -- What's up?

Along with SHOW CREATE TABLE, please provideSHOW TABLE STATUS` for at least one of the many tables.  I want more details before judging these:

  OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0028`; -- can free 82 MB
  OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0029`; -- can free 82 MB
  OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0035`; -- can free 112 MB
  (etc)

If you raise innodb_buffer_pool_size, don't increase it to more than 90G.

Details and other observations:

( Innodb_buffer_pool_pages_flushed ) = 1,456,312,527 / 1470874 = 990 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 40960M / 122880M = 33.3% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (8M / 0.20 + 40960M / 0.70) / 122880M = 47.7% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( Opened_tables ) = 44,353,045 / 1470874 = 30 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 26,430,658 / 1470874 = 18 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( Table_open_cache_overflows ) = 43,864,852 / 1470874 = 30 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 44,353,021 / 1470874 = 30 /sec
-- May need to increase table_open_cache

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 40960M / 8 = 5120MB -- Size of each buffer_pool instance.
-- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 -- page_cleaners
-- Recommend setting innodb_page_cleaners to innodb_buffer_pool_instances

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((8736963 + 1456312527) ) / 1470874 = 996 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_os_log_written ) = 2,138,679,386,624 / 1470874 = 1454019 /sec -- This is an indicator of how busy InnoDB is.
-- Very idle or very busy InnoDB.

( Innodb_log_writes ) = 151,318,300 / 1470874 = 102 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,470,874 / 60 * 1024M / 2138679386624 = 12.3 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_dblwr_writes ) = 27,805,868 / 1470874 = 19 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( bulk_insert_buffer_size / _ram ) = 8M / 122880M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( (Queries-Questions)/Queries ) = (1084892343-158114534)/1084892343 = 85.4% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (39274680 + 290107639 + 1191197 + 0) / 25 = 1.32e+7 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 2,955,230 / 1470874 = 2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( sort_buffer_size ) = 24M -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok.
-- This may be eating into available RAM; recommend no more than 2M.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (39274680 + 1191197 + 0 + 0 + 290107639 + 710124) / 1470874 = 225 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( Com_alter_table ) = 121,112 / 1470874 = 0.082 /sec
-- Why so many ALTERs?

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 -- For parallel slave threads
-- slave_pending_jobs_size_max must not be less than max_allowed_packet

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( back_log / max_connections ) = 80 / 151 = 53.0%

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally small:

(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.32
slave_net_timeout = 60

Abnormally large:

Binlog_cache_use = 105 /sec
Binlog_stmt_cache_use = 0.32 /sec
Com_create_trigger = 0.0073 /HR
Com_create_view = 0.015 /HR
Com_dealloc_sql = 0.082 /sec
Com_drop_trigger = 0.0073 /HR
Com_drop_view = 0.015 /HR
Com_execute_sql = 0.082 /sec
Com_insert_select = 0.51 /sec
Com_insert_select + Com_replace_select = 0.51 /sec
Com_load = 21 /sec
Com_prepare_sql = 0.082 /sec
Com_show_engine_mutex = 0.0049 /HR
Com_truncate = 0.24 /sec
Com_update = 197 /sec
Com_update_multi = 0.48 /sec
Handler_delete = 63 /sec
Handler_update = 2396 /sec
Innodb_buffer_pool_bytes_dirty = 1,866.6MB
Innodb_buffer_pool_pages_data = 2.59e+6
Innodb_buffer_pool_pages_dirty = 119,461
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.34
Innodb_buffer_pool_pages_total = 2.62e+6
Innodb_data_fsyncs = 211 /sec
Innodb_data_writes = 1127 /sec
Innodb_data_written = 33895601 /sec
Innodb_dblwr_pages_written = 989 /sec
Innodb_log_write_requests = 2631 /sec
Innodb_os_log_fsyncs = 103 /sec
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 2,496.0MB
Innodb_pages_created = 40 /sec
Innodb_pages_read + Innodb_pages_written = 1.48e+9
Innodb_pages_written = 990 /sec
Innodb_rows_deleted = 63 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 4891 /sec
Innodb_rows_inserted = 4829 /sec
Innodb_rows_updated = 2396 /sec
Performance_schema_rwlock_instances_lost = 2.04e+10
Qcache_free_memory = 256.0MB
innodb_io_capacity_max = 6,000
join_buffer_size = 64MB
max_tmp_tables = 64
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210

Abnormal strings:

bind_address = 0.0.0.0
have_ssl = YES
have_symlink = DISABLED
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_fast_shutdown = 1
innodb_large_prefix = ON
innodb_undo_directory = ./
log_bin_trust_function_creators = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN

Schema Critique

decimal(28,2) occupies 13 bytes.  Consider whether some other datatype will be sufficiently precise for your application, yet save a lot of space:  BIGINT and DOUBLE each take only 8 bytes hold 16-17 significant digits; no decimal places for the former, floating decimal for the latter.
Does tz_offset need a 4-byte integer?  Shrink it, too.
What is the purpose of the PARTITIONing?  If performance, then show us the query that depends on partitioning.  (I may shoot down any performance benefit.)  If it is purging "old" data, then I strongly recommend switching to weekly partitions; this will save disk space and not hurt anything else, plus avoid some of the criticisms I had when looking at the STATUS.  You would not have to change the existing partitions, rather make weekly partitions going forward, thereby gradually improving things without having to take a big downtime to make the change.
Do not pre-build the partitions; do it "just-in-time".  But also have a "future" partition to collect anything that accidentally lands in the table before building tomorrow's partition.  More discussion in my blog .
When you don't explicitly specify a PRIMARY KEY, InnoDB creates a hidden 6-byte PK.  Please show me the main queries, so I can help devise a better set of keys, possibly avoiding that PK.  If necessary, I may recommend a MEDIUMINT UNSIGNED AUTO_INCREMENT (3 bytes, 16M max); this would easily allow 669,536 rows (as per `SHOW TABLE STATUS).

Crude estimates of space (MB per table) after implementing suggestions:

       current  proposed
Data      76       50
Index     95       60?
Free      70       10
         ---      ---
TOTAL    241      120

Shrinking the tables in half may cut the 990 writes/second in half.  (There are several unknowns, especially with indexing.)

Since Data_free for the ~100 partitions is about equal to the predicted savings due to OPTIMIZE TABLE, I expect that OPTIMIZE won't really save anything.  Each partition is always left with 4-7MB of "free" space.


As @Rick James mentioned, a GROUP BY function should do the trick here, and you can use  This SO Answer to limit it to the last 12 months.  For your example, the query would look something like this:

SELECT
    COUNT(DISTINCT user_id) as customers,
    SUM(amount)*.01 as amount,
    SUM(amount_refunded)*.01 as refunded,
    MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- AND `user_id` IN (
--  SELECT `id` FROM `users` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- )
GROUP BY MONTH(`created_at`);

NOTE: I commented the subquery for user_id because it would mean the results were only for those customers who both created an account and made a purchase in the last year, and I wasn't sure if that was your desired result.  If so, just uncomment it.  I edited the subquery so that it should work in that manner


Oracle: How to create a "reverse" index primary key?

The primary key index (including index parameters) can be specified with the using index clause.  You can include the reversed specification there.

create table T(X number primary key
                 using index (create unique index T_PK on T(X) reverse));


Do the searching and inserting capability of Sql Server 2008 engine in a table decreases with an increase in total rows in a table?

Do the searching and inserting capability of SQL Server 2008 engine in
  a table decreases with an increase in total rows in a table?

Yes the performance will eventually be effected but you can counter this with the addition of suitable indexes. Indexes will be easily be sufficient for managing the performance of tables that are ~50GB. If you get a lot more data in the future columnstore indexes and partitioning may be worth a look but they do require the Enterprise Edition of SQL Server. Columnstore indexes would require and upgrade to SQL Server 2012 or later.

  Is there per row data size limit?

A table can contain a maximum of 8,060 bytes per row but VARCHAR and NVARCHAR columns can be stored off row to provide more space. More details here


Can expdp be piped directly into impdp, without intermediate files?

As @mutap stated in the comments, you can expdp/impdp over the network.

Technically, you will be doing the DataPump export and import over a database link without the need for an intermediate file.

Bash PIPE is not needed.

Example:
https://oracle-base.com/articles/10g/oracle-data-pump-10g#NetworkExportsImports


How to safe delete (shred) a MySQL database?

InnoDB leaves user data in several places.

ibdata1 if innodb_file_per_table=OFF or respective *.ibd file if innodb_file_per_table=ON. This is where InnoDB stores the data.
ib_logfile0 and ib_logfile1 stores recent modifications.
Double write buffer (which resides in ibdata1 no matter what innodb_file_per_table is) stores recent modifications.

To reliably delete user data you need to shred ibdata1 - because of the double write buffer. If you do that, MySQL instance will be destroyed. So, I would say it's impossible to "shred" a particular database/table.

In your case I would create a replica with mysqldump (or mydumper, not with xtrabackup or a file system snapshot) and destroy the original instance/disk/server.


Can't find names with criteria that has special symbols in PostgreSQL

|| is the operator to concatenate strings in SQL. You probably want OR.

Additionally: LIKE without a wildcard does not make sense. e.name_related like ',' is the same as e.name_related = ','. Multiple ORs are the same as IN so you can simplify your condition to:

(
  e.name_related IN ('', ',', '.') OR e.name_related like '%x%' OR
  e.d_name in ('', '.', ',') OR e.d_name like '%x%' OR
  e.father_name IN ('', '.', ',') OR e.father_name like '%x%' OR
  e.d_father_name IN ('', '.', ',') OR e.d_father_name like '%x%'
)


As User, Doctor and Patient have very different attributes best you can do is split into 3 tables. As User is the first object you are accessing (registration-login), best Users will function as a father to Doctors and Patients tables.

For example -
Users - UserName, FirstName, LastName, EMail, PhoneNumber, Company/Address/Active (Many keep encrypted Password in the DB as well).

You can add Role - 1 for Doctor, 2 for Patient (3 for Nurse, 4 for Clinic Manager and so on).
Doctor and Patient can have the same Ids as User or different so you need to add DoctorId/PatientId in Users or add UserId in Doctors/Patients. Again, as Users come first, it's best to go for an option where the Doctor Id can be found from the User.
For easier setup, you can create different Ids for Doctors and Patients, as Doctors have little and Patients many, you can just keep 1000 values to Doctors and 1001+ for Patients, or you can use different sequences / smart increment value, so Doctors can be 1,3,5 and Patients 2,4,6.
Now Doctors can have additional tables such as Skill Sets where Patient have Patient Records.
Note you can download many medical key-value static tables online, as updates are keep coming in the industry, best is to integrate your system with reliable services.
From experience, I believe managing the patient record and the doctor mail box will be your hardest task, as Doctors need to query the full patient history and every day, they also need the latest updates on their patients.


My Database Tables Revert to MyISAM Instead of InnoDB (MySQL)

You need USE my_database; before, so that you will be in that database.

The SELECT generates a bunch of ALTERs, but does not perform them.  To perform them:

Plan A:  (Straightforward)  Copy & Paste them into mysql commandline tool or suitable window in some UI.

Plan B:  Build a Stored Procedure that loops ('cursor') through the output to prepare/execute/deallocate-prepare each one.


Cast NTEXT to INT for calculation and comparison

See comment by Martin Smith and https://stackoverflow.com/q/2133946/327165 for overview of why not to use ntext.

In order to get numeric records only, including records with numbers that have quotation marks try this: SELECT REPLACE(CAST(TextValue AS NVARCHAR(MAX)),'''','') FROM Table a WHERE ISNUMERIC(CAST(a.[Value1] AS NVARCHAR(MAX))) = 1


Is it possible to see the (completed) SQL Statements executed by a specific session?

yes, read up on FGA (fine grained auditing).

http://www.oracle-base.com/articles/10g/auditing-10gr2.php

this should do what you want to accomplish.


I would go back to testing and find out why the SID migration did not work. It is a migration Microsoft supports with Active Directory. I would suggest engaging the support for the Data Center you are moving to for assistance. (or even Microsoft...)

If the SIDs cannot be fixed your only option is going to be recreating the accounts and permissions. Thinking about it, since Data Center moves could be in your future again (or at least the companies) you might rethink some of those permissions where all of it is groups, even if you are dealing with one account currently.

You can execute queries against the ReportServer instance to get those permissions, but not one to modify the logins and such.


Hierarchical references abstraction with strict model

Here is a possible solution.  Navigating the structure from top to bottom may be a little gross but maybe the app side objects can handle it.

I think you can build this out using 2 tables dbo.Device and dbo.Combiner.  Each table has a column that references who its parent object is, regardless of whether it is a device or a combiner.  We can then use a couple different types of constraints and a couple of triggers to ensure that the data follows the path you are looking to use.

Table Structures (now your objects probably have more data points than just a name but for the sake of simplicity I am only going to keep track of a name)

dbo.Device

CREATE TABLE dbo.Device
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name VarChar(100) NOT NULL,
    ParentDeviceID INT NULL,
    ParentCombinerID INT NULL,
    CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueParentDeviceIDDevice
ON dbo.Device(ParentDeviceID)
WHERE ParentDeviceID IS NOT NULL

ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Device] FOREIGN KEY([ParentDeviceID])
REFERENCES [dbo].[Device] ([ID])
GO

ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentCombinerID])
REFERENCES [dbo].[Combiner] ([ID])
GO

ALTER TABLE [dbo].[Device]
ADD CONSTRAINT CHK_Device CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
GO

dbo.Combiner

CREATE TABLE dbo.Combiner
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name VarChar(100) NOT NULL,
    ParentDeviceID INT NULL,
    ParentCombinerID INT NULL,
    CONSTRAINT [PK_Combiner] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueParentDeviceIDCombiner
ON dbo.Combiner(ParentDeviceID)
WHERE ParentDeviceID IS NOT NULL

ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentDeviceID])
REFERENCES [dbo].[Device] ([ID])
GO

ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Combiner] FOREIGN KEY([ParentCombinerID])
REFERENCES [dbo].[Combiner] ([ID])
GO

ALTER TABLE [dbo].[Combiner]
ADD CONSTRAINT CHK_Combiner CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
GO

Each table holds who its parent object is via ParentDeviceID or ParentCombinerID. The Unique Filtered Index on ParentDeviceID helps ensure that a given Device can only be the parent of one object (A Unique Constraint would stop multiple rows with a NULL value).  It isn't guaranteed so that is why we are going to use some triggers down the road.  The two CHECK Constraints help ensure that any given object can only have 1 parent object instead of having both a Device and Combiner as a parent. The Foreign Keys ensure that the values stored in ParentDeviceID and ParentCombinerID are valid values for those two tables.

Triggers

We can insert a Trigger on each table.  These Triggers need to ensure:

On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on dbo.Device.ParentDeviceID

-- On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on dbo.Device.ParentDeviceID
CREATE TRIGGER dbo.CombinerCheck ON dbo.Combiner
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM INSERTED I
            INNER JOIN Device D
                ON I.ParentDeviceID = D.ParentDeviceID
    )
    BEGIN
        RAISERROR ('This Combiner cannot be added since it''s parent Device is already a parent to a different Device.', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert/Update
        RETURN
    END
END

--On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
CREATE TRIGGER dbo.DeviceCheck ON dbo.Device
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM INSERTED I
            INNER JOIN Combiner C
                ON I.ParentDeviceID = C.ParentDeviceID
    )
    BEGIN
        RAISERROR ('This Device cannot be added since it''s parent Device is already a parent to a Combiner.', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert/Update
        RETURN
    END
END

As an alternative this StackOverflow question apperently give the same or similar effect with a Unique Index on a View.  If you want to avoid Triggers than this may be a solution for you.  https://stackoverflow.com/questions/16314372/ms-sql-server-cross-table-constraint. I am not familiar with this approach but maybe it is a better solution for you.

Examples

Implementing the 1st Example provided (just 3 devices in a chain) would look something like this.  Just 3 records in dbo.Device:

+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | 1              | NULL             |
|  3 | D3   | 2              | NULL             |
+----+------+----------------+------------------+

Implementing the 2nd Example provided (1 Device --> 1 Combiner --> 3 Devices) would look something like this.  4 Records in dbo.Device and 1 Record in dbo.Combiner.

Device
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | NULL           | 1                |
|  3 | D3   | NULL           | 1                |
|  4 | D4   | NULL           | 1                |
+----+------+----------------+------------------+

Combiner
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | C1   |              1 | NULL             |
+----+------+----------------+------------------+

One more, slightly more complicated example to display all the relationship options:

dbo.Device
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | 1              | NULL             |
|  3 | D3   | NULL           | 1                |
|  4 | D4   | NULL           | 1                |
|  5 | D5   | NULL           | 1                |
|  6 | D6   | NULL           | 2                |
|  7 | D7   | NULL           | 2                |
|  8 | D8   | NULL           | 2                |
+----+------+----------------+------------------+

dbo.Combiner
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | C1   | 2              | NULL             |
|  2 | C2   | NULL           | 1                |
+----+------+----------------+------------------+

Hopefully this give you all you need from the database side.  Again I think this will be complicated on the App side navigating the table structures.  But the database should keep the relationships the way you want to.


At the SQL Server conference I attended in Sydney in 1999, Richard Waymire said that Microsoft's intention was to allow a variable page size in SQL Server 2000 but that it turned out to be really difficult, so they put it in the too-hard-basket and went with 8k as a compromise.


Here I suppose that id is ordered chronologically just like timestamp field.

UPDATE Chron AS w
   SET w.timestamp = IF( w.timestamp = D, @ts, @ts := w.timestamp )
 ORDER BY w.id ASC
;

The only requirement is that first (by id) record should have the correct timestamp.

Also that query update the broken timestamp to the value of the previous correct/corrected row and if you have the long continuous run of rows with broken timestamps result will be different from the "closest value". Anyway the "closest value" need a complicated query so "previous value" is good enough if you need some fast solution.


Real-time merge of two databases on different servers

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 of Merge replication with two application servers and a distribution database. I would re-visit the customer requirements, determine exactly what he wants, not what he thinks he wants, and not how he wants to do it, and then come up with a solution to provide to the customer.

Unless you or your customer are willing to buy a third party product, some form of SQL Server replication is going to have to be used. Here are some links (2008 R2):

Merge replication

Replication overview


Postgresql BDR Update Update conflict detection

The manual discusses much of this.

Yes, commits are timestamped. Conflict resolution uses commit timestamp, not the timestamp of an individual tuple insert/update/delete. See track_commit_timestamps in the postgres docs.

In your example, node A might or might not see a conflict. If B's update happened first (according to B's clock), and B's update reaches A before C's does, A doesn't see a conflict. But if C's update reaches A before B's, and B's happened earlier, then when B's update arrives at A, A needs to know to discard it since it's already applied a newer one. Otherwise it'd become inconsistent with the state of B (which will apply C's update when it receives it) and C (which has overwritten B's older update with its own newer one).

Because it's a loosely coupled replication system with no global lock manager, snapshot manager, transaction manager, etc, there's no one concept of "now" and no one globally consistent state. Or at least it's fuzzy due to latency and replication lag. You'll see such systems (somewhat incorrectly) referred to as "AP", for the "availability" and "partition tolerence" of CAP. It's an eventually consistent system with weaker inter-node guarantees than made by stock PostgreSQL. You can concurrently UPDATE a tuple on two different nodes. You can INSERT the same key into a UNIQUE-constrained column (or same PRIMARY KEY) on two different nodes. etc.

When the nodes replicate and sync up these conflicts need to be resolved so that all nodes have the same outcome and no errors result. We can't just ERROR because the xacts already committed on their origin nodes and we have no way to "un-commit" them; even if we did, we shouldn't, since other xacts on the local nodes might've used those committed changes as inputs into other computations so we're stuck with them.

So we have to do things like pick the newest of two inserts. We use the timestamp of commit for this. It's sent on the protocol messages, and it's recorded by postgres for local commits.

The old row values don't matter for this. For example, we only care that some peer node updated table "x" but we've updated it more recently, so we presume the newer update should be kept. Otherwise when our change replicated to the peer node, it'd apply our change and if we'd also applied theirs, we'd have different final states. To get a consistent result, we must discard their change.

Similarly, if two new rows are inserted, we must consistently pick one of them to keep when they conflict on the same PK. We pick the newest of the two rows based on recorded commit timestamp.

So yes, it relies on clocks. The clocks don't have to be in perfect sync, but roughly right is desirable, otherwise you might overwrite newer tuples with significantly older ones when doing conflict resolution, i.e. pick the wrong "last" update. The manual should discuss clock sync a bit more.

Having access to old row values would be nice in some cases, but is doesn't let you know for sure what the right outcome is. You still have to pick one of multiple possible "correct" results. Consider this:

given table

CREATE TABLE x (
  id integer primary key,
  y integer not null
);

INSERT INTO x (id, y) VALUES (1, 1);

Two nodes run

UPDATE x SET y = y + 1 WHERE id = 1;

at the same time. Both produce a new local row with value y = 2 as a result. Now each replicates to the other.

If we know the old value was 1 and the new value is 2, we might say "well, duh, the correct result is 3 on both nodes".

But we have no way to know that one or both nodes didn't instead run

UPDATE x SET y = 2;

instead of y = y + 1. So it might want the final result to be 2, not 3. The only way to know is to know the application logic. BDR can't tell based on just the old and new tuples.

So really we don't need the old tuple values.


Find Max() updated time for a group of users

See if its what you want
-- this main query retrieve all users under the subquery domain
SELECT a.UserID
    , substring_index(a.UserID, '@', -1)
    , MAX(a.lastupdate)
FROM MyTable a
WHERE substring_index(a.UserID, '@', -1) IN (

    -- this subquery search all domain inactive in last 12 months
    SELECT substring_index(a.UserID, '@', -1) AS domain
    FROM MyTable a
    GROUP BY substring_index(a.UserID, '@', -1)
    HAVING MAX(a.lastupdate) < '2020-03-31'

)
ORDER BY substring_index(a.UserID, '@', -1), a.lastupdate


Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

No, he's not right. If a block is not in the InnoDB cache, then it has to be fetched, which means it will come either from disk or from the ZFS cache, at which point two copies of it exist in main memory. If you use that block, it will come out of the InnoDB cache. If you write that block, it will go from the InnoDB cache to the disk. The ZFS cache is just a helpless spectator in this scenario.

However, if your issue is INSERT performance, it's unlikely to be related to this unless your system is under a very heavy load... is it? From the docs:

  If an index record should be inserted
  into a nonunique secondary index,
  InnoDB checks whether the secondary
  index page is in the buffer pool. If
  that is the case, InnoDB does the
  insertion directly to the index page.
  If the index page is not found in the
  buffer pool, InnoDB inserts the record
  to a special insert buffer structure.
  The insert buffer is kept so small
  that it fits entirely in the buffer
  pool, and insertions can be done very
  fast.

  Periodically, the insert buffer is
  merged into the secondary index trees
  in the database. Often it is possible
  to merge several insertions into the
  same page of the index tree, saving
  disk I/O operations. It has been
  measured that the insert buffer can
  speed up insertions into a table up to
  15 times.

  The insert buffer merging may continue
  to happen after the inserting
  transaction has been committed. In
  fact, it may continue to happen after
  a server shutdown and restart (see
  Section 13.2.6.2, “Forcing InnoDB
  Recovery”).

  Insert buffer merging may take many
  hours when many secondary indexes must
  be updated and many rows have been
  inserted

SHOW ENGINE INNODB STATUS should show you exactly what the DB is waiting on. Also see if strace can help.


Without implementing #2, your query would probably look like this:

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      @starttime StartTime,
      DATEADD(mi, 15, @starttime) EndTime

    UNION ALL

    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)
    FROM
      time_cte
    WHERE
      EndTime < @EndTime
  )
SELECT
  t.StartTime,
  UnitCount = COUNT(d.NumCavalier)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < t.EndTime
GROUP BY
  t.StartTime
;

Now in order to split the COUNT(d.NumCavalier) into three values based on the value of FormIdNb, you check that value when counting – like this:

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)

    UNION ALL

    SELECT
      EndTime,
      DATEADD(mi, 15, EndTime)
    FROM
      time_cte
    WHERE
      EndTime < @EndTime
  )
SELECT
  t.StartTime,
  ActiveUnitCount   = COUNT(CASE WHEN d.FormIdNb < 3 THEN d.NumCavalier END),
  IdleUnitCount     = COUNT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END),
  InactiveUnitCount = COUNT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < t.EndTime
GROUP BY
  t.StartTime
;

This is called conditional aggregation: you are aggregating the results based on some condition.

One of the charms of the method is that you can still get the total count in the same output, if necessary, by including COUNT(d.NumCavalier) into the resulting column set.


MySQL Workbench - "deploying" schema into a real database

What you want is called either Forward engineering (create a new set of schemas + db objects based on your model) or Synchronization (apply changes in your model to an existing setup and incorporate changes from that setup into your model). The latter feature works best if you first reverse engineered an existing setup into a model and work from there. Synchronization does a 2-way merge and allows to manually exclude changes from being taken over to the other side or even change the direction for changes, if the automatic detection is not right.


Should I enable db_writer in public facing website

Yes it would be a security risk. db_datawriter gives the account access to all tables in the database. This means that anyone getting control of the web user can delete all content.

A better solution would be to create an new role in the database. Assign the entity framework user to this role and only grant the bare minimum privileges needed to run the app to the role. Ideally, the Entity Framework would only read tables via GRANT SELECT (maybe even only some columns) and perform all writes via stored procedure with GRANT EXECUTE enabled.

An even better solution would be to not use Entity Framework at all. Like every other ORM on the planet - it is horrible way to access a database. Tell your programmers to learn SQL instead of fooling about with Object Oriented database access frameworks.


#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

You could use the window function row_number, like so:

select id, collection, name from (
  select id, collection, name,
  row_number() over (partition by collection order by id) rn
  from stac_item
) t
where rn < 3
order by id


It normally stands for Surface Area of Attack and refers to the amount of components installed and or configuration options that are enabled. Normally the more components you install the wider the surface area for attack.

The concept is not specific to SQL Server and refers broadly to the chance that any software can be compromised by a malicious attack.

In the SQL Server world we have the SQL Server Configuration Tool that can be used to minimize the risks, in the Oracle world I believe it is called Configuration Hardening:

SQL Server: Surface Area Configuration

Oracle: Configuration Hardening

As a general best practice when reducing the surface area for an attack is to only install the components that you know will be used/needed and only enable them when they will be used.


MySQL NDB Cluster settings to prefer data node on localhost than remote

I would recommend that you upgrade to MySQL NDB Cluster 7.5. Then you can take advantage of a new relevant feature:
https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-system-variables.html#sysvar_ndb_read_backup

With the read_backup feature used in a setup like yours--with only one node group (shard/partition)--all reads can be done locally at the given data node and no cross data node work is ever required.

Another related new aspect of this feature is that when it's used, the API node (the mysqld process) will automatically select/use a data node available on localhost as the transaction coordinator.

So this should give you exactly what you're looking for here! I expect the "read backup" feature to be extremely popular with many MySQL NDB Cluster users, especially those with setups similar to yours. :)

For some additional information:

https://blogs.oracle.com/MySQL/entry/mysql_cluster_7_5_is
http://mikaelronstrom.blogspot.co.uk/2016/10/read-any-replica-in-mysql-cluster-75.html


Modulo operator with nonintegers - unexpected results

I will start with a link: What Every Programmer Should Know About Floating-Point Arithmetic.
In short, float-point arithmetic types, like the float and double mysql types should never be used for precise arithmetic. And your col mod 0.1 is trying to do exactly that, a precise arithmetic check. It is trying to find if the value in col is an exact multiple of 0.1. And it fails miserably, as it is obvious from your results.
The solution is to use a fixed type, like decimal(m, n) to do these types of checks.
As for why you got different results in Oracle, it's because you used NUMBER which is implemented differently (with decimal precision). The equivalents in Oracle of float and double are BINARY_FLOAT and BINARY_DOUBLE and use binary precision. See the notes in Oracle docs: Overview of Numeric Datatypes:

Oracle Database provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all of the basic functionality provided by the NUMBER datatype. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.
Note:
BINARY_DOUBLE and BINARY_FLOAT implement most of the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754).

If you insist to use double (I see no reason why), a few more tests with the mod operator/function in mysql, reveals that:
When you use integers for the arguments, say you do x mod K, the result is always an integer between 0 and K-1. The expected mathematical behaviour.
When you use floats/doubles, say you do x mod K, the result is any number from 0.0 to K included. (as your tests which show 5850.0 mod 0.1 to result in 0.1). I haven't done millions of tests, so there may be cases where the result can even be larger than K! Floating-point arithmetic can give weird results.
So, a workaround (which may not always work!) would be to use this condition:
( mod(col, 0.1) <> 0.0  AND  mod(col, 0.1) <> 0.1 )

To be honest, I feel sad I even made the workaround suggestion. Please read again the What Every Programmer Should Know About Floating-Point Arithmetic and don't use the workaround.
Use fixed-precision types for precision arithmetic operations.


how to update SSRS execution account automatically through power-shell?

Changing the passwords:

If you are using a Active Directory account you can use the ActiveDirectory PowerShell module.

Set-ADAccountPassword -Identity elisada -OldPassword (ConvertTo-SecureString -AsPlainText "p@ssw0rd" -Force) -NewPassword (ConvertTo-SecureString -AsPlainText "qwert@12345" -Force)

If you are using a Sql Server user than it would be using T-SQL

ALTER LOGIN UserName WITH PASSWORD = 'P@ssw0rd';
    GO

Updating the Service:

$service = gwmi win32_service -computer [computername] -filter "name='ReportServer'"
$service.change($null,$null,$null,$null,$null,$null,$null,"P@ssw0rd")


How can I have a gap in the SQL Server Job Agent history for a job?

Check the duration of the job prior to the first slot it "missed." Typically job history won't have gaps unless:

The previous instance of the job is still running - SQL Server knows not to start two copies of the same job concurrently, so it just skips that slot.
The job was disabled during that time.
Someone deleted the history from msdb.dbo.sysjobhistory.


How to resolve TNS error in Oracle when using an ODBC DSN

Make sure that your tns files (tnsnames.ora, sqlnet.ora) are in a certain directory that is readable by all users.  for example "/oracle/network/admin/"
Make sure that all your users and processes that need to work with the database[s] have the environt variable TNS_ADMIN defined with the value "/oracle/network/admin/".
Doing so gives a good chance that all processes look to the same configuration.


Efficient SELECT for array of tuples in PostgreSQL

If you aren't fixed on the array input, you can use a tuple comparison.

select *
from records
where (id, updated) in ( (1, timestamp '2019-01-01 00:00:00'),
                         (2, timestamp '2019-01-02 00:00:00') )

That can make use of a regular btree index on (id, updated)

Note that this uses = for both values and is equivalent to

where (id = 1 and updated = timestamp '2019-01-01 00:00:00')
   or (id = 2 and updated = timestamp '2019-01-02 00:00:00')

But you want to compare the timestamps using >. You can do that if you join against a values clause:

select r.*
from records r
  join (
    values
       (1, timestamp '2019-01-01 00:00:00'),
       (2, timestamp '2019-01-02 00:00:00')
  ) as t(id,upd) on  r.id = t.id
where r.updated > t.upd;

Online example: https://rextester.com/GGC83046


Best practices to run time offset delayed replication

I haven't looked into the built-in replication in Postgres 9, but if you're using Slony-I with log shipping, you can just wait to apply the logs files 'til after they're a given age.  We use a perl script under cron that connects to the master server and pulls the log files, then applies them.  It should be nothing to pull stuff to the replica to queue, and then go through and apply them, stopping when you hit the ones that are too new.


Database schema tracking patient stats over time

Does readingID need to be ZEROFILL?
Does birthdate need to be DATETIME?
Use InnoDB.
Do not use EAV!  10 columns of stats is no big deal, and you can use appropriate datatypes for each column.

That is, have only 2 tables -- One for Users, one for Readings.

If you graph things, please use dates for the x-axis.  I saw one clinic that evenly spaced the readings across the x-axis.  That's just plain bad.

For weight you could pick FLOAT or DECIMAL(4,1) or SMALLINT UNSIGNED (4/3/2 bytes, sufficient for pounds or kg -- but be consistent on what you store)

For Gender, is using CHAR, do CHAR(1) CHARACTER SET ascii and allow more than the usual choices (these days!).

If you want to disallow two sets of measurements in a single day, get rid of readingID and have PRIMARY KEY(memberID, date).  This will have the side effect of making looking up all the info about a person more efficient.

2M rows/year is "medium sized"; not a problem.  By getting rid of stat, you are down to 200K rows/year, and the entire dataset will be closer to "tiny".  The database would be perhaps 8MB/year.  (Versus perhaps 100MB/year for EAV with stats.)

The insert rate would be 200K/year.  MySQL can easily handle 200K/day, so the server would be very idle.

With my suggestions, should able to handle even a million rows without further tweaking.  (With EAV, you would be struggling long before that.)

Why or why not EAV

EAV shines when there is a lot of variation in attributes.  Your application has a fixed set of attributes -- Everyone has a pulse rate; at worst you might store NULL if that test was not performed.

If, on the other hand, you choose to store the hundred(?) readings you can get from blood samples, ...  Usually blood work focuses on a small number of possible tests.  That is, the data is very sparse.

For blood work, I would recommend having a column of JSON text.  (Depending on whether you are using a recent MySQL or MariaDB, there might be a JSON datatype, or just a TEXT datatype; the effect is similar.)  In this column you would list only the readings that you have information for.  Meanwhile, the 'regular' readings (pulse, temp, etc) would have their own columns.  You might have a 'real' column that says "there is some bloodwork reading that is out-of-range" as a clue to look into it.

Yes, JSON has hassles when rummaging inside it.  But I claim that EAV is even worse -- for size, for speed, for coding.

In your Comment you mentioned adding 10-20 new readings.  The necessary ALTER TABLE .. ADD COLUMN .. NULL, ADD COLUMN .. NULL; would be a one-time nuisance.  But I still prefer it to either EAV or JSON.

Another thing to note:  INDEXes are not always useful.  When you have values that are mostly missing (columns, EAV, or JSON), they may be costly to search.  If you would like to show us some of the queries, we can discuss further.

For the simple query of "SELECT * FROM Readings WHERE userID = 123" to build graphs, then the optimal schema is a single Readings table with or without JSON, and without EAV.  And I gave the optimal PRIMARY KEY.


You can use the following update.

update #BlueBell set rglname = b.rglname
from #BlueBell a
inner join (
            select region, rglname
            from #BlueBell where rglname is not null
            ) b on a.region = b.region

Check your desired output here

select *
from #BlueBell


Taking a simple approach:

Execute inside psql, enable \timing and run your query. Also check query execution plan:

explain select <your query>;

Then run vacuum for your table:

vacuum (full, analyze, verbose) employee_employee;

And check if query execution plan was improved, you'll see what's the costly stage:

explain select <your query>;

Considering that your query is a simple query, there's no much to do beyond that inside the database.
Optionally evaluate if a materialized view fits your need.


Microsoft SQL Server 2008 R2 Subscriptions

Data Driven Subscriptions is a feature that is only available in SQL Developer or Enterprise Edition.


Want to timeout or skip SQL Server index rebuild statements if taking too long

First, I would recommend that you use one of the free scripts/tools to manage index rebuilds. One of the best known is Ola Hallengren's DB maintenance suite. This lets you control how fragmented an index must be before you try to reorganize or rebuild it.

Second - if you still have issues where the process seems to hang, try creating two jobs. The first job would actually run the index rebuild process. The second job would ensure that the first job doesn't run longer than necessary.

Let's call the first job 'Index Maint Job'. Do not schedule this job.

The second job would be scheduled for when you want the index maintenance to be run, and would execute this script:

DECLARE @jobname nvarchar(128) = N'Index Maint Job';
DECLARE @proc_start_date datetime = GETDATE();

EXECUTE sp_start_job @job_name = @jobname;

WAITFOR DELAY '003:00:00';

IF (EXISTS (SELECT 1
              FROM sysjobactivity ja
                     INNER JOIN sysjobs j ON (ja.job_id = j.job_id)
             WHERE j.name = @jobname
               AND start_execution_date >= @proc_start_date
               AND stop_execution_date IS NULL
           )
   )
BEGIN
    Print N'Aborting job ''' + @jobname + N'''...';
    EXECUTE sp_stop_job @job_name = @jobname;
END
ELSE PRINT N'Job ''' + @jobname + N''' finished.';

This launches the job, waits until it's been given whatever time you feel is sufficient to complete, checks if it's still running, and stops it if it is.

(I had originally planned to use TRY ... CATCH to catch the error you get if the job isn't running and you try to stop it; however, that's a SQL Server Agent error, which TRY ... CATCH can't handle. So, we check to see if the job's still running instead.)

The key element to know here is the sp_start_job does not wait for the job to finish; it returns once the job is started. So, this job has control of itself again at that point, and can wait however long you like (current setting [WAITFOR DELAY '003:00:00'] is three hours) to see if the job is still running, and stop it if it is.

FYI: As other have noted, an index reorganization happens gradually; I have a large fragmented PK index to try to defrag, and was planning on implementing this with Ola's scripts to make sure the process didn't run too long.


Oracle client throwing ORA-12541: TNS:no listener

Could it be that you have started the listener process as root? I can see you are trying lsnrctl status as user root, so please make sure that you start the listener process as the oracle user, and then start the database (also as the Oracle user)

Also if you get "Connected to an idle instance", this means that you are connecting to an instance that is not running, or you have set the environment wrong for connecting to the instance you want to connect to.

Make sure you have the Oracle software directory in your path, such as /u01/app/oracle/product/11.0/dbhome_1/bin

and ORACLE_SID set to the database instance name.


Generate Schema and Data to SQL script and Run it on another DB programmatically

It is possible to generate DDL and Data from SQL Server Management Studio (SSMS):

Object Explorer > yourServer > yourDatabase > right-click > Tasks > Generate Scripts ...

Within the 'Advanced' options change 'Types of data to script' to 'Schema and Data.

Also, here is a simple example of using Powershell to script data for tables I did a few years ago:

Example: Scripting Data with SMO and Powershell

So it's possible, and you will find many Powershell examples for scripting tables.  However backup and restore would be much more straightforward!  If the tables are empty, then it's inconsequential if you move them over right?