Asking "What AWS instance to choose for write-heavy PostgreSQL database server" is somewhat like asking "which spoon should I use to cut up this loaf of bread".

The answer is: don't use a spoon, use a knife. Or in this case, real hardware like a dedicated server with a batch of good quality SSDs and a decent raid controller that has a battery-backed write-back cache. Or at least a VPS with very strong I/O performance guarantees.

If in doubt, benchmark.

That said, you can make AWS perform significantly better than the defaults by using provisioned I/O EBS volumes on EBS-optimized instances, improving performance while retaining data durability. Alternately, you can sacrifice durability in exchange for more performance by using a high I/O instance with the database on the instance store; in this case you will need a careful backup and replication scheme because all your data will be lost if your instance stops or crashes.

If you need more assistance, contact me directly.


Postgresql Replica is in sync but repmgr says node unattached

Maybe you can try running the following command from the standby node:

/usr/pgsql-10/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby follow

The paths of the repmgr file and the repmgr.conf configuration file may vary according to the installation.


EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:
EXPLAIN ANALYZE SELECT f1();
Optimization
PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for the duration of the session. Details:

Function Performance

EXPLAIN function bodies
Like @Daniel already commented, you can use the additional module auto_explain to get more details (lots of details). Statements inside plpgsql functions are considered "nested statements". Be sure to set:
SET auto_explain.log_nested_statements = ON;

Detailed instructions:

Postgres query plan of a function invocation written in plpgsql

As an exception to the rule, simple SQL functions (not plpgsql) may be "inlined", i.e. the function code is inserted into the outer query and everything is executed like there wasn't a function to begin with. The query plan includes detailed information in such cases.


Extract all of the keys, including nested, from postgres JSONB column

You can use a recursive query for that:

with recursive all_keys (ky, nested) as (
  select t.ky,
         case jsonb_typeof(p.data -> t.ky)
           when 'object' then p.data -> t.ky
           else null
         end
  from profiles p
    cross join jsonb_object_keys(p.data) as t(ky)
  where id = 5
  union all
  select t.ky,
         case jsonb_typeof(ak.nested -> t.ky)
           when 'object' then ak.nested -> t.ky
           else null
         end
  from all_keys ak
    cross join jsonb_object_keys(ak.nested) as t(ky)
  where nested is not null
)
select ky
from all_keys;

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


How to alter column to make it primary key when one already exists mysql8?

If you would like to have other tables reference just the title field, then you can add a UNIQUE index.
First, edit the tours table to add a NOT NULL constraint to the title field:
ALTER TABLE `tours` MODIFY `title` VARCHAR(255) NOT NULL;

Now you can create the index:
CREATE UNIQUE INDEX `udx_title` ON `tours` (`title`);

Be sure to confirm that all values in title are unique before doing this. Once done, you will be able to have other tables reference the field.

Hat tip to Vérace for pointing out that title could be NULL, which we do not want.


What can be the downside of always having a single integer column as primary key?

Other than additional disk space (and in turn memory usage and I/O), there's not really any harm in adding an IDENTITY column even to tables that don't need one (an example of a table that doesn't need an IDENTITY column is a simple junction table, like mapping a user to his/her permissions).

I rail against blindly adding them to every single table in a blog post from 2010:

Bad habits to kick : putting an IDENTITY column on every table

But surrogate keys do have valid use cases - just be careful not to assume that they guarantee uniqueness (which is sometimes why they get added - they should not be the only way to uniquely identify a row). If you need to use an ORM framework, and your ORM framework requires single-column integer keys even in cases when your real key is either not an integer, or not a single column, or neither, make sure that you define unique constraints/indexes for your real keys, too.


Movement of Standby database from one location to another location

The easiest solution would be to stop transport of the logs from the primary, stop redo apply on the standby, make the move, then re-enable both... This is usually accomplished via:

On Primary

alter system set log_archive_dest_state_N = defer scope=both;

*Replace N with the archive_dest number where your transport is defined... which you should be able to easily tell via

show parameter log_archive_dest;

Then look for a an entry that points to your standby... something like "SERVICE=UNIQUENAMEOFSTANDBY..."

On Standby

alter database recover managed standby database cancel;
shutdown immediate;

Any logs that are generated on the primary will remain on the primary while the standby is unavailable.

After the move:

On Standby

startup mount;
alter database recover managed standby database disconnect from session;

on Primary

alter system set log_archive_dest_state_N = enable scope=both;

Any accumulated logs on the primary are shipped over and applied to the standby.

You can check to see if the standby is up to date via:

On Standby

select current_scn from v$database;

On Primary

select scn_to_timestamp(<SCN_FROM_ABOVE_QUERY>) from dual;

If the idea of the standby being unavailable for an extended period of time is unacceptable, then create a second standby at the new site first while the current standby is still on-line and available.

It is also important to note that if your standby site is more than 100 miles from your primary site, it is recommended to either not use real time apply (due to network lag) or that you use Oracle Far Sync (12c only).


Reached maximum possible value for auto_increment function set on column

While inserting single record (by both INSERT .. VALUES and INSERT .. SELECT) you will obtain

  Duplicate entry 'max_value_for_datatype' for key 'PRIMARY'

While inserting multiple records you will obtain (even when only one of them causes overflow)

  Failed to read auto-increment value from storage engine

fiddle

  Will it be handled automatically?

No. You must handle it - and the best way is to solve this issue before it occures.

PS. If you are interested in to know what messages will be produced while inserting record(s) by LOAD DATA/XML you may test it by yourelf.


Migration of table from MySQL to Oracle using GoldenGate

From my experience with GoldenGate, it's better to do initial loads with database native tools.  In this case, you would use:

SELECT * FROM TABLENAME INTO OUTFILE 'filename';

https://dev.mysql.com/doc/refman/5.7/en/select-into.html

Then load into Oracle with SQL*Loader.

Example control file for SQL Loader:

$ cat example1.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Example taken from:
https://www.thegeekstuff.com/2012/06/oracle-sqlldr/

If you cannot stop data changes to perform the load, you need to:

Start your extract
Export the data
Load the data
start your replicat with HANDLECOLLISIONS enabled

After your replicat has caught up, remove HANDLECOLLISIONS.

For some good Tutorials, I've used https://gavinsoorma.com/category/goldengate/

Also the Oracle TechNet forums were responsive for GoldenGate questions - even for some really obscure scenarios.


Retrieving orders that only contain certain product IDS

If Impala supports GROUP BY, COUNT and HAVING, then you can use the following.

Here we select the records for matching dates and products, then we count how many product_ids we actually found for each transaction on each day, and we take only those results for which we found all 5 product_ids.

SELECT transaction_id, trans_date, COUNT(product_id) as counter
FROM   Transactions
WHERE  trans_date IN ('2019-03-07','2019-03-06','2019-03-08','2019-03-09','2019-03-10')
AND    product_id IN ('000000000021003503', '000000000060280873', '000000000060281480', '000000000060281642', '000000000020059281')
GROUP BY transaction_id, trans_date
HAVING count(product_id) = 5

If a single transaction is allowed to span multiple dates, then you can just leave out the trans_date from the SELECT and GROUP BY parts.


Is it possible to use two different drivers for sql server on the same machine. Say 2000 and 2008

Yes you can use the SQL 2000 and SQL 2008 driver on the same machine at the same time.


Misaligned Log IO Issues and Unmatched LUN sector sizes in AlwaysOn

Since you are running  SQL Server 2014 SP1 which is build 12.0.4100, it includes all the updates from the lower RTM branch including all the CUs. You still need to enable TF 1800 (as start up parameter with a restart of SQL Server service - this has to be done during maintenance window, since this is a downtime for your application.)

I guess the root of my question is, does flag 1800 have any affect at all after SQL 2014 CU5?

Yes, TF 1800 is required to be turned ON even if you are on SP1. This is because you have mis-aligned disk sector size.

The disks that store the log files of the primary and secondary replica in an AlwaysOn Availability Group (AG) have different sector sizes. For example:

The primary replica log file is located in a disk that has the sector size of 512 bytes. However, the secondary replica log file is located in a disk that has the sector size of four kilobytes (KB).
The primary replica log file is located in an on-premise local system that has the sector size of 512 bytes. However, the secondary replica is located in Windows Azure Storage that has the sector size of four kilobytes (KB).

If you want to understand more about the problem, refer to

Storage Spaces/VHDx and 4K Sector Size by Bob Dorr
This blog post shows how to address the problem by modifying the sector size - Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log

Its always a best practice to have same hardware configuration on primary and secondary servers - Disk, CPU, RAM, etc.


Order result by number of occurence of a word in a text

SELECT LENGTH(text)-LENGTH(REPLACE(text,'@',''))as counting from table
ORDER BY counting DESC

SQL Fiddle


Find tables with columns with empty and NULL values in Postgresql

Assuming "empty" and "blank values" means empty string ('').
This function checks whether the passed table has any NULL or empty values ('') in the passed column (which must be a string type or some other type where the empty string is valid; not the case for numeric types for instance):
CREATE FUNCTION f_has_missing(_tbl regclass, _col text, OUT has_missing boolean)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE
   format($$SELECT EXISTS (SELECT FROM %s WHERE %2$I = '' OR %2$I IS NULL)$$, _tbl, _col)
   INTO has_missing;
END
$func$;

Call:
SELECT f_has_missing('tablename', 'column')

Or, optionally, schema-qualified:
SELECT f_has_missing('schema.tablename', 'column')

db<>fiddle here
Probably most important: Never concatenate parameter values into SQL code blindly. That's begging for SQL injection. I sanitized the code with format(). See:

Table name as a PostgreSQL function parameter

There are a couple of smart expressions to check for both:
(string_col = '') IS NOT FALSE
(string_col <> '') IS NOT TRUE
coalesce(string_col, '') = ''

See:

Best way to check for “empty or null value”

But I chose this plain and more verbose expression for two reason:
string_col = '' OR string_col IS NULL

I have grown fond of simple, obvious code, and none of the above is as clear as this. But more importantly, this expression can use an index on (string_col), while the above cannot - which makes a big difference for big tables. See the added demo in the fiddle!
db<>fiddle here
Obviously, we want a different name for the function than "has_nulls", now.
I use an OUT parameter for convenience and short code. Now we can assign to it and be done.
And I use regclass as IN parameter for the table name. This way I can provide a schema explicitly or not. Again, see:

Table name as a PostgreSQL function parameter

Aside, one might loop through all columns of a table or a whole db to find any such column at once. Related:

Replace empty strings with null values
Set empty strings ('') to NULL in the whole database


Killed DbccFilesCompact found running for days

I just killed it today and it went away immediately... hope this helps someone else. Thanks!


SQL Server 2016 SP2 - ONLINE REBUILD DEADLOCKed Itself - is this a bug?

Sounds like an intra-query parallelism deadlock.

They're generally considered bugs because there's not much you can do about it, other than to rerun the query or drop the maxdop of the statement down (if its happening often for the same query).

Here is some information to help troubleshoot them, hope this helps.

https://support.microsoft.com/en-us/help/4089473/better-intra-query-parallelism-deadlock-troubleshooting-in-sql-server2


how to resolve ELFCLASS32 error in mysql for UDF lib_mysqludf_sys.so

You didn't say, in your question what the "file not present" message was, but I'm guessing it's this:

ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so'
(errno: 0 /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so: cannot open shared
object file: No such file or directory)
ERROR: unable to install the UDF

That makes sense, because you're telling gcc to write the file to /usr/lib/lib_mysqludf_sys.so... that's the -o option in your command line.  Give gcc the path MySQL is expecting and the rest of the installation should work.

gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c \
-o /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so \
-L/usr/lib/x86_64-linux-gnu/libstdc++.so.6

Also, whatever you're planning to do with this... don't say I didn't recommend against it.  You're introducing both a potential security vulnerability and potentional performance and stability liability if these tools are deployed.  This is not because there's anything wrong with the utility, but because of the amount of somewhat unorthodox functionality it opens up.

It's pretty cool, I admit.  The lib_mysqludf_sys group of user-defined functions allow some interesting but easily-misappropriated capabilities, letting you spawn system commands and get either their generated output with sys_eval() or their return value with sys_exec() but really, I tend to suspect there's a reason why MySQL doesn't have these capabilities built in.

mysql> select sys_eval('df -k | grep xvda | tr -d "\n"') as cool_function_but_bad_idea;
+-----------------------------------------------------+
| cool_function_but_bad_idea                          |
+-----------------------------------------------------+
| /dev/xvda1       8256952   1216636   6620888  16% / |
+-----------------------------------------------------+
1 row in set (0.81 sec)

mysql> select sys_exec('/bin/false'), sys_exec('/bin/true');
+------------------------+-----------------------+
| sys_exec('/bin/false') | sys_exec('/bin/true') |
+------------------------+-----------------------+
|                    256 |                     0 |
+------------------------+-----------------------+
1 row in set (1.59 sec)

Update to address this error message:

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o
/usr/lib/lib_mysqludf_sys.so -fPIC gcc: error: lib_mysqludf_sys.c: No such
file or directory gcc: fatal error: no input files compilation terminated

The statement in question is directing "gcc" to compile a 64-bit version from the source code, which is found in lib_mysqludf_sys.c.  This is one of the files you downloaded, so, to compile it, you need to be inside the directory where you downloaded the lib_mysqludf_sys files.  This error suggests that you aren't.  The download package isn't limited to 32 bit, it's just that it only builds a 32 bit version unless you use the gcc -m64 -fPIC ... statement.


Do I need an Availability Group Listener if I have "Failover Partner" in my connection string?

Failover partner is for when you are using database mirroring, you should not use it for Availability Groups. While it, strictly speaking, will work, it will only do so when the primary server is offline, and is not designed for this kind of scenario.

Using a listener within an Availability Group is the way to go. This is a virtual network resource that will point you to the correct primary (and is required in the event that you want to use read-routing at any point in the future). Using a listener you should not have to perform an app restart to have client redirection, it should just work.


Is there any point in specifying the precision and scale of a number for a foreign key?

Connor MacDonald blogged about this over in NUMBER data type... what harm can it do? as well as Ask Tom: "How do I determine how much storage will be required for NUMBER(p, s)?". In short, it does matter.

Take this table:

CREATE TABLE T (
    x1 number,
    x2 number(6,3)
);

x1 will be 21 bytes while x2 will be 2 bytes. By not specifying the data type, you're taking up extra space that may not be needed.


Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to?

It is absolutely possible - the ever helpful documentation comes to the rescue, again:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

Which means you can do something like this:

COPY my_table (mt_id, mt_name, mt_created_by, ...)
    FROM 'filename' [...]

What you cannot do is to refer columns of the CSV file.  To overcome this, one can create an intermediate table with the matching number and type of columns, do the COPY into it, then do an INSERT ... SELECT ... to the final destination.  Based on an important remark from Patrick7, the intermediate table can be defined as UNLOGGED, saving a lot of WAL overhead when the table is big.


Postgresql improve select performance/parallelism in huge table

Although I didn't reach an answer for the issue (due to time constraints), I think that the most useful tip to remark is:

Use EXPLAIN (ANALYZE, BUFFERS) ... on many queries (with different values) to try to understand what is happening. In my case, this put back CLUSTER as a possible optimization option.

What I would do if I had the time to try to optimize the table:

Change the indexed values for the smallest possible. In my case, that would mean changing the user_id from VARCHARto UUID.
Execute CLUSTER to reduce the number of pages fetched.


From looking at a Profiler trace of the SSMS dialog opening the queries are essentially:

Size:

SELECT sum(df.size) * 8 / 1024.0 Size_MB
FROM sys.database_files df;

and

Space Available:

with q as
(
    SELECT
    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
    (SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
    FROM sys.partitions p
    join sys.allocation_units a
      on p.partition_id = a.container_id
    left join sys.internal_tables it
      on p.object_id = it.object_id
)
select (DbSize - SpaceUsed) * 8 / 1024.0 SpaceAvailable_MB
from q;

So Size includes the log file, but Space Available does not.  It's only the unallocated space in database files.


More table or more database for better performance?

Performance shouldn't be a concern: security, consistency, data integrity should be

Security: If each web site has a database this implies that the data for that web site is
private and separate. So 40000 databases with 10 tables each.
Consistency: managing 40k websites requires utter consistency. Why pick 400x1000 or 4000x100? How will you separate tables? How do you know which site uses which set of tables in which DB?
Data integrity: If you restore one database, do you really want to reset the data for another 399 or 3999 websites too?

So: 40k databases, one per web site.

Finally, do you have 40k web sites or is this premature optimisation?


PostgreSQL: Auto update updated_at with custom column

Use the spi module's moddatetime

spi module, moddatetime

moddatetime — Functions for Tracking Last Modification Time

  moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

  To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

  There is an example in moddatetime.example.

Example / Synopsis

From the above referenced file,

DROP TABLE mdt;

CREATE TABLE mdt (
    id      int4,
    idesc       text,
    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER mdt_moddatetime
    BEFORE UPDATE ON mdt
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime (moddate);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

UPDATE mdt SET id = 4
    WHERE id = 1;
UPDATE mdt SET id = 5
    WHERE id = 2;
UPDATE mdt SET id = 6
    WHERE id = 3;

SELECT * FROM mdt;

Your Application

So this is what you would need to.

CREATE EXTENSION spi;

ALTER TABLE users ALTER timestamp_at SET DEFAULT now();

DROP TRIGGER IF EXISTS
  update_users_updated_at ON users;

CREATE TRIGGER mdt_users
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (timestamp_at);


As per Microsoft, having same service account is the prerequisite :

All server instances that host an availability replica for the availability group must use the same SQL Server service account.
 The domain administrator needs to manually register a Service Principal Name (SPN) with Active Directory on the SQL Server service account for the virtual network name (VNN) of the availability group listener. If the SPN is registered on an account other than the SQL Server service account, authentication will fail.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability


Execute code ONLY if we have the ability to do full text search on database (mysql >= 5.6.4)

Plan A:
Notice how you can comment out things if the version is older than something:  (I am running 5.6.12.)

mysql> SELECT /*!50604 '5.6.4 is running', */ 'new or old';
+------------------+------------+
| 5.6.4 is running | new or old |
+------------------+------------+
| 5.6.4 is running | new or old |
+------------------+------------+

mysql> SELECT /*!50700 '5.7 is running', */ 'new or old';
+------------+
| new or old |
+------------+
| new or old |
+------------+

You can see lots of examples in the output of mysqldump.

Plan B:
You could also parse @@version inside a Stored Procedure (or client code).

Plan C:
Or, maybe this

mysql> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES
        WHERE Variable_name = 'Junk';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES
      WHERE Variable_name = 'innodb_ft_cache_size';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+


SQL Server Management Studio - Debugger buttons greyed

You need to open the network ports & firewall ports between your desktop running SSMS, and the remote computer.

Just generally speaking, though, I'd highly recommend against running the debugger on a live server. You're going to force SQL Server to hold your locks open for longer periods of time, and you can easily cause a blocking storm. Instead, I'd strongly recommend debugging your code locally on your own dedicated instance of SQL Server. (Developer Edition is free.)


How to predict how much space a VACUUM FULL would reclaim?

You need the pgstattuple extension to get the amount of free space.
So you could run
SELECT t.oid::regclass AS table_name,
       s.table_len AS size,
       dead_tuple_len + s.approx_free_space AS reclaimable
FROM pg_class AS t
   CROSS JOIN LATERAL pgstattuple_approx(t.oid) AS s
WHERE t.relkind = 'r'
ORDER BY (s.dead_tuple_len::float8 + s.approx_free_space::float8)
       / (s.table_len::float8 + 1.0) DESC;

where size and reclaimable are in bytes.
Be warned that this query is somewhat expensive.
That should give a pretty good estimate of the space that can be reclaimed, but in practice it will be slightly less, because

there is always some free space in each 8kB block that is too small to fit another row

some of the dead tuples might not be reclaimable if you have long running transactions


Where appropriate, the database engine can do a backwards scan, ie go straight to the end of the index and work backwards so it's nice and efficient.  You can see this on your query execution plans under the 'Scan Direction' attribute of scan operators.  For your example, with your composite primary key on WarehouseId,OrderId then this will work:

As you are doing select * the clustered index already covers the whole query so no additional bookmark lookups are required.


List all databases

select * from sys.databases

List SSRS Reports

use [ReportServer]

SELECT * FROM [dbo].[Catalog]
WHERE [Type] = 2
ORDER BY [Path]

List all Logins

select * from sys.syslogins

List users having permission to reports
from stackoverflow link
https://stackoverflow.com/questions/6600480/ssrs-determine-report-permissions-via-reportserver-database-tables

    select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
   inner join dbo.Policies B on A.PolicyID = B.PolicyID
   inner join dbo.Users C on A.UserID = C.UserID
   inner join dbo.Roles D on A.RoleID = D.RoleID
   inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName


The error message is quite explicit:

Error: datadir must be specified.

The documentation is clear on this too:

  --datadir=DIRECTORY The source directory for the backup. This should be the same as the datadir for your MySQL server, so it should be read
  from my.cnf if that exists; otherwise you must specify it on the
  command line.

  When combined with the --copy-back or --move-back option, --datadir
  refers to the destination directory.

Note the last sentence, and make sure you're providing the correct path.

The documentation is here.


MySQL Replication not working properly on Ubuntu Lucid Lynx Server

:D it was binlog_do_db. I removed it and now it is working fine.


MySQL transaction and replication between two datacenters

You can setup replication using SSL.

With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and look at the data being sent or received, or even change the data while it is in transit between client and server.
When you must move information over a network in a secure fashion, an unencrypted connection is unacceptable. Encryption is the way to make any kind of data unreadable. Encryption algorithms must include security elements to resist many kinds of known attacks such as changing the order of encrypted messages or replaying data twice.
MySQL supports secure (encrypted) connections between clients and the server using the Secure Sockets Layer (SSL) protocol. SSL uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard.

Source: https://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html


Best Practice for SQL Server data validation

SSIS is, indeed, clunky when it comes to importing data for all the processing prowess that it seems to tout. One of the commenters, Kin I believe, had it right with creating a temporary table. Here's the code we use to import data directly from Excel into a temporary table to be parsed for compliance. As you can see in the example below, using T-SQL, OPENROWSET reads the specific tab on the Excel Spreadsheet called SQL Server Instance Details and imports the data into the SQL Table TempServerMap.

After, the data is imported into the temporary table you have a world of options. You can insert the data into another table that has all the SQL business rules you need on each of the columns--kicking out the data that doesn't qualify--I say this not as a necessarily "best way" but for using the least amount of code in the beginning fine tuning your process of finding errors. You'd need to control the error handling here to catch the bad apples (rows) so you can review them later. Alternately, you could create functions, or procedures, or triggers or all of the above that check the data in advance to make sure the do comply with business requirements.

And if you're looking to communicate those errors to others, just create an SSRS (Reporting Services) report that queries your new error table and give them the proper permissions to view the report. It's web based and easily accessible in an intranet environment. If you have external associates you can have SSRS e-mail the report to them just as easily.

The main point here is: get your data away from Excel and into SQL Server so it can be manipulated effectively.

Some DBAs might be offended at the replace/exec of the code below. If that's the case, just hardcode the select statement with specific excel file names.

declare  @cmdstring varchar(8000) ='select * into SQLSunSet.dbo.TempServerMap from OPENROWSET (''Microsoft.Ace.OLEDB.12.0'', ''Excel 12.0;Database=<MapsExcelFile_SQLServerUsageTracker>'', ''select * from [SQL Server Instance Details$]'')'
set @cmdstring = replace(@cmdstring,'<MapsExcelFile_SQLServerUsageTracker>',@MapsExcelFile_SQLServerUsageTracker)

if exists (select * from sysobjects where type ='u' and name ='TempServerMap') drop table TempServerMap

        --print (@cmdstring)
     exec (@cmdstring)

Many people seem to turn to SSIS in hopes of not coding T-SQL. But I usually find that SSIS is like faulty four-wheel drive. It just get's you stuck more than you would have been a lot farther down the road.

P.S. to use OPENROWSET effectively you might need to install
the Microsoft Access Database Engine 2010 Redistributable (Really--a Microsoft Office Engine--not just for MS Access). This can be downloaded here:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Additionally, you might need to configure SQL Server to run OPENROWSET and also use the Office engine within T-SQL using the following code after it's installed:

exec sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO

exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


Restoring database between different version of SQL Server (SoftwareVersionBuild)

Migrating a database specifically from 2008 R2 SP2 to 2008 R2 SP1 will be fine*.

Migrating up or down service pack levels on the same SQL Server version is generally OK, because the internal database version number usually does not change.

There have been a couple of exceptions to this general rule. The one that might interest you concerns databases where support for 15,000 partitions has been enabled (changing the internal database version). If this scenario might apply to you, download and review the SQL Server Technical Article below:

Support for 15,000 Partitions in SQL Server 2008 SP2 and SQL Server 2008 R2 SP1.

Even so, going directly from a 2008 R2 SP2 instance to SP1 should not present any problems. Always have verified (restored and DBCC-checked) backups just in case.

* Caveat: If the SP2 database had support for 15,000 partitions enabled, it would not be accessible when restored to R2 RTM as an intermediate step. Applying SP1 would then resolve this. Good to know to avoid panic at the RTM stage :)


SQL server compress backup for lower version databases

I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's.

As a good practice and suggested by Microsoft you should change compatibility level of database to match with server version on which it is hosted. There are few legacy applications which might require version as 80 for that you have to test, I cannot tell upfront whether you change it or not. You should see what all features were deprecated in 2008 R2 as compared to SQL Server 2000 below two articles would help you

Breaking Changes to DB feature in SQL server 2008 R2

Deprecated database engine features in SQL Server 2008 R2

Deprecated SQL Server features in SQL Server 2008 R2

Regarding changing compatibility level if you face any issue you can immediately change it back to 80, that is why i said a round of testing is required. If you read Change compatibility Level of database it would really help you

  For the Migrated DB's having compatibly 80, are we good to take compressed backups?

Yes you can take compressed backup of 80 compatibility database hosted on SQL Server 2008 R2 subject to condition SQL Server edition is Standard or higher edition


Huge MySQL slave replication lag (days) at almost idle servers. Slave is mostly 'Waiting for dependent transaction to commit'

Check these two server parameters (my.cnf):

slave_pending_jobs_size_max should be same or more than the value of max_allowed_packet
https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_slave_pending_jobs_size_max
slave_transaction_retries value should be twice of slave_parallel_workers.


How do I correctly design this database?

Your first option is most likely the best. A table called users, a table called groups and a junction table called user_groups. User_groups allows for the many to many relationship between groups and users. As for size, what are you thinking in terms of "large"? 20,000 records? A million? It might be possible database types and programmers think differently in terms of "large", because I can't foresee this table having more than a few hundred thousand rows if the website gets popular and there are a lot of groups and that doesn't seem large. As long as the tables are indexed properly and the queries are written efficiently it should not be an issue...you would only be querying out of the table for specific users or specific groups anyway. Whichever RDBMS engine you use is designed to operate on tables structured this way. There's no good design reason to break the groups table into multiple tables - it means having to maintain new tables for each new group exactly as you said, plus you run into complicated queries where you want to bring back data from more than one group at once.


Is filling up plan cache causes a decrease in space allocated for data cache?

Yes.  The Plan Cache and Page Cache share the available server memory.  The Plan Cache is capped, as growing a large plan cache rarely provides much marginal benefit.

AFAIK, this old doc page is still accurate:

  Memory Allocated To Plan Caching

  Most memory used by SQL Server is allocated to the Buffer Pool, which
  is used to store data pages. SQL Server steals a proportion of this
  memory for use in caching query plans.

Cache Pressure Limit SQL Server 2008 and  SQL Server 2005 SP2 [and later]

  75% of visible target memory from 0-4GB + 10% of visible target memory
  from 4Gb-64GB + 5% of visible target memory > 64GB

Which means that the plan cache will be trimmed when it exceeds that target, effectively capping the amount of memory used for plan caching.

Plan Caching in SQL Server 2008


OLTP to OLAP process causing deadlocks and impacting customers

Perhaps this problem has been solved meanwhile. Databases need dedicated knowledge and skills, any 10 year old kid can play around with SQL Server Management Studio, but creating and maintaining databases is not that easy.

For SQL Server , make sure that Snapshot Isolation level is enabled. It's disabled by default, with the database in data locking mode, where long running queries block write acces, or even certain read accesses. I think Snapshot Isolation was introduced in SQL Server for analysis/OLAP.

Also, the OLTP tables should have proper indices. There should be somebody in the company knowing at least the basics of all this.


After Update Mysql 8.0.13 to 8.0.15 MySql Server Fails with error code = 1265

I found the solution myself.
On the old mysql 8.0.13 server I corrected the field SQL_MODE of one of the procedures, what happened to be too long.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES;

I found some (old) stored procedures I did not need anymore, and the entries in field SQL_MODE of one of that stored procedures were too long.
After deleting that stored procedure, starting the new 8.0.15 server and "mysql_upgrade" everything is fine, the server can now start without any errors.


Configure Oracle 11.2+ ACL to work with mail server having an internal IP address

Here's what I finally (!) got to work. Simply set smtp_out_server equal to mail.example.com, then add mail.example.com to the ACL using, from SQLDeveloper:

-- leave <lower|upper>_port null to allow access to all ports
exec dbms_network_acl_admin.assign_acl (acl => 'http_permissions.xml', host => '*.example.com');
commit;

Notice above that I used *.example.com instead of mail.example.com because doing it this way also allows UTL_HTTP requests to www.example.com (kill 2 birds with one stone). Also, by not specifying upper and lower port bounds, we enable access for all ports. If you want, you can specify the exact ports as done in the original posting above.

Here's the UTL_SMTP related code:

g_mail_conn := UTL_SMTP.OPEN_CONNECTION('mail.example.com', 26);
UTL_SMTP.EHLO(g_mail_conn, 'mail.example.com');
UTL_SMTP.MAIL(g_mail_conn, p_sender_email);
...

Note that I'm using port 26 for SMTP, whereas most of the world uses port 25 (so just update your code accordingly). Also, there's no authentication (e.g. username/password) for the non-SSL port (26; no need, since I'm using an internal IP address).

The UTL_MAIL command also works from SQLDeveloper:

exec utl_mail.send(sender => 'name1@example.com', recipients => 'name2@example.com', message => 'Hi there');


SQL Server FCI won't start after OS Upgrade

Prior to Windows Server 2012 R2, doing an in-place upgrade of Windows for an FCI is not supported. Per Microsoft's documentation:

  Upgrading the Windows operating system of a failover cluster is not
  supported for operating systems before Windows Server 2012 R2. To
  upgrade a cluster node running on Windows Server 2012 R2, see
  Cluster Operating System Rolling Upgrade.

Because your starting OS was Windows Server 2008 R2, you are unable to do an in-place upgrade to Windows Server 2012 R2. Instead, you will need to build a new Windows Cluster, do a fresh SQL install for a new FCI, then migrate to that new FCI.

You can do this using existing hardware by removing a node from the cluster, doing the fresh install of both Windows & SQL, then repeating the process one node at a time to move servers from the old cluster to the new cluster.

Once you are upgraded to Windows Server 2012 R2, you will be able to do in-place OS upgrades in the future.

Given that you've already upgraded the OS, your best bet is going to be to uninstall SQL Server, and re-install the FCI from scratch.


SELECT person_id, MAX(answer_1), MAX(answer_2), MAX(answer_3)
FROM (<your_sub_query>) subquery_alias
GROUP BY person_id

would do the job.


As @sp_BlitzErik suggests, this is simpler to do in SQL directly. If it needs to be part of an SSIS package for some reason, write a SQL script to do the job, and put that in the package as an "Execute SQL" task. And yes, this is actually one of those rare times where using a cursor actually makes sense.

However, if for some reason you can't use sp_send_dbmail, you can do this in SSIS - just not in the data flow.

Execute the query that generates your list in an "Execute SQL" task, and put the resultset in an object. You may need to modify your query to make sure there's only one resultset - use SET NOCOUNT ON, etc. Then, have a "Foreach" Loop run over the object, putting the various column values in variables. Inside the "Foreach" loop, put the "Send Email" task. Set up all the necessary values as expressions (the last tab in the task editor window), with each expression being the appropriate variable populated by the loop.


It somewhat begs the question of why not use rman for your backups instead of user managed backups.  rman has been the robust solution since at least 9i, and here we are with 12c being mature.


Do primary keys change when indexes are rebuilt or reorganized?

No, they will not.

Rebuilding or reorganizing an index should never alter the underlying data itself, only its placement within database pages / extents.

However, as an aside have a read through this post - you might find that you don't need to rebuild these indexes in the first place.


What sort of database setup is used for an App Store or Google Play Store?

If it's just for a demonstration and won't be deployed to any users, and embedded database, for example SQLite, is a good choice. Not that there's anything wrong with running a local or shared instance of PostgreSQL or MySQL, but it will be marginally more work to set up.


Introduce a third database and use synonyms. For each table (I'll call it dbo.MyTable for this example), it looks something like this:

Point the synonym in the "shell" database to the version in the "live" database. So create synonym shell.dbo.MyTable for live.dbo.MyTable.
Do your most nasty data load in the "load" database
When you're ready to cut over, drop and recreate the synonym from step 1: drop synonym shell.dbo.MyTable; create synonym shell.dbo.MyTable for load.dbo.MyTable

You've effectively swapped your live and load databases and the application is none the wiser.


According to MySQL Documentation on SET PASSWORD

The SET PASSWORD statement assigns a password to an existing MySQL
user account.   If the password is specified using the PASSWORD() or
OLD_PASSWORD() function, the literal text of the password should be
given. If the password is specified without using either function, the
password should be the already-encrypted password value as returned by
PASSWORD().
With no FOR clause, this statement sets the password for the current
user. Any client that has connected to the server using a nonanonymous
account can change the password for that account

However, you just been struck by lightning because the same webpage says

Important
SET PASSWORD may be recorded in server logs or in a history file such as ~/.mysql_history, which means that plaintext passwords may be
read by anyone having read access to that information. See Section
6.1.2, “Password Security in MySQL”.
Note
If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or
UPDATE statement without reading Section 6.1.2.3, “Password Hashing in
MySQL”, first. The password format changed in MySQL 4.1, and under
certain circumstances it is possible that if you change your password,
you might not be able to connect to the server afterward.

Your password change is forever, so to speak.
HOW TO FIX IT
Suppose your user is myuser@localhost, you can have someone with ALL PRIVILEGES run this
UPDATE mysql.user SET password=OLD_PASSWORD('[your password]')
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you have no DBA but can restart mysql, then do this
service mysql restart --skip-networking --skip-grant-tables
mysql -e"UPDATE mysql.user SET password=OLD_PASSWORD('[your password]') WHERE user='myuser' AND host='localhost'"
service mysql restart


Why CDC records nothing while being enabled?

Check to make sure the SQL Agent is running, and that there is a capture job created that is enabled and running for the CDC instance, it should show Change Data Capture Collection Agent / executing.


A reason the physical row locator is not stored in the non-clustered index leaf nodes is because the value would need to be updated whenever the physical row location changes due to page splits, reorgs, etc. Having only the CI key as the unique row locator on non-clustered indexes avoids this maintenance, which would be especially costly on a table with many non-clustered indexes.

Note that this isn't as much of a consideration with heaps because those do not suffer from page splits, although forwarding pointers is a consideration when rows are updated to a larger size.


Percona cluster + Wordpress blogs, low performance

Galera/Percona XtraDB Cluster is an solution that provides high availability, read scaling and full consistency. Although in some cases it can provide some write scaling (mainly due to poor SQL writing), it is not focused on it, as it does not provide sharding, so it has to write to all nodes in a (virtually) synchronous way. This is important to decide if Galera is for you.

Having said that, some comments in your setup:

Virtualisation, while not a problem per-se, can add some component of indeterminism, which is critical for Galera as it will perform as bad as the worst performing node. Make sure all nodes have a predictable performance.
you can reduce some durability parameters in the cluster (for example, setting innodb_flush_log_at_trx_commit = 2), as HA will be provided by node redundancy in the case of a crash. This will increase performance.
Certain workloads may need client changes, as they are hurtful for Galera performance. In particular, frequent updates on the same rows can case collision and frequent rollbacks of transactions, reducing the throughout. Identify hotspots with the Percona/XtraDB cluster statistics and  profiling and try to avoid them from client side. In some cases, some commercial applications may need to change some code slightly to adapt to a cluster infrastructure.
Network performance is critical. You say that the latency between nodes is 0.1 ms. A transaction will take an overhead at least equal to double the latency of the slowest node.


Looking at your query we can see that there are lots of details that cannot be discussed here.  In order to get a trigger to work, here a small test setup that may help you to do the next steps ...

Not quite sure if the "LINESTRING M" values are stored in the same table as the original x/y values, that's why the example is written with 2 tables in mind: (1) "shape_table", containing objects of type shape, and (2) "m_values" with columns for road_id and "linestring m" (name shortened to m).  DDL as below.

create or replace type shape_t as object (
  road_id number
, vertex_index number
, x number
, prev_x number
, y number
, prev_y number
, dist_to_prev number
, member function getdistance return number
);
/

create table shape_table ( shp shape_t ) ;

create table m_values ( road_id number,  m varchar2(4000) );

After inserting some test data:

SQL> select * from shape_table;
SHP(ROAD_ID, VERTEX_INDEX, X, PREV_X, Y, PREV_Y, DIST_TO_PREV)
-----------------------------------------------------------------------------
SHAPE_T(100, 1, 0, 0, 5, 0,   0)
SHAPE_T(100, 2, 10, 0, 10, 5, 11.18)
SHAPE_T(100, 3, 30, 10, 0, 10, 22.36)
SHAPE_T(100, 4, 50, 30, 10, 0, 22.36)
SHAPE_T(100, 5, 60, 50, 10, 10, 10)

SQL> select * from m_values;
ROAD_ID  M
100      LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9 )

Now, if we have procedure that re-calculates the "LINESTRING M" and uses it for updating (the SELECT used here looks different from the one in your question, due to various reasons*)

create or replace procedure refresh_m_value
is
  m_roadid number := 0 ;
  m_linestring varchar2(4000) := '' ;
begin
-- generate the new M LINESTRING
  select
    road_id
  , 'LINESTRING M ( '
    || listagg( x || ' ' || y || ' ' || dist_ , ', ' )
       within group ( order by vi )
    || ' )' linestring
  into m_roadid, m_linestring
  from (
    select
      s.shp.road_id road_id
    , s.shp.vertex_index vi
    , round( s.shp.x, 2 ) x
    , round( s.shp.y, 2 ) y
    , sum ( s.shp.dist_to_prev )
        over ( partition by s.shp.road_id order by s.shp.vertex_index ) dist_
    from shape_table s
  ) roads_
  group by road_id ;
-- write the new M LINESTRING
  update m_values
  set m = m_linestring
  where road_id = m_roadid ;

end refresh_m_value;
/

Use a trigger for calling the procedure after the original values have been UPDATEd.  Add a log table for debugging/testing etc.

-- table for log messages generated by the trigger (see INSERTs in trigger code)

create table trigger_log ( date_ date, message_ varchar2(128) );

create or replace trigger reset_m
  after update on shape_table
declare
  error_code NUMBER;
  error_message VARCHAR2(128);
begin
  refresh_m_value;
  insert into trigger_log (date_, message_)
    values (sysdate, '[trigger: reset_m] refresh_m_value executed ') ;
exception
  when others then
    error_code := SQLCODE;
    error_message := SUBSTR(SQLERRM, 1 , 128);
    insert into trigger_log (date_, message_)
      values (sysdate, '[exception] ' || error_code || ' - ' || error_message);
end;
/

Test the trigger, check the tables (and log).

update shape_table s
set s.shp.x = 0, s.shp.y = 5
where s.shp.road_id = 100
  and s.shp.vertex_index = 1 ;

select * from shape_table;

select * from trigger_log;

select * from m_values;

More details about the (imaginary) TYPE shape_t, and populating the "shapes_table" can be found here.  However, using the dbfiddle, the TYPE was not created successfully, due to ORA-01031: insufficient privileges.


Catch OLE DB provider "SQLNCLI10" for linked server "[Some Server]" returned message "Query timeout expired"

Simply reconfigure the Linked Server to have no timeout.  Or at least a longer timeout value.

EXEC master.dbo.sp_serveroption @server = N'<server_name>'
    , @optname = N'query timeout'
    , @optvalue = N'1200';  --1200 second timeout

To configure the server so that no linked servers ever timeout, make this change:

EXEC sys.sp_configure 'remote query timeout', 0;

Then ensure the linked server has sp_serveroption 'query timeout' is set to 0.


Change defaults of SSMS generating script

Most of those settings can be found here:

Tools -> Options -> SQL Server Object Explorer -> Scripting

Of the two you specifically asked for, "Script extended properties" is present there. But "Script DROP and CREATE" is not. Perhaps it is called something different from one interface to the other, but I can't see anything that would correspond. It might be because Drop vs. Drop/Create vs. Create isn't so much an option as it is a "what do you want to do with this object?" decision.

(Tested in SSMS 17.9.1.)


If they are indeed INTEGERs, it should be simply

UPDATE
  yourtable
SET yourcolumn =
    FLOOR(yourcolumn/1000) * 10000 + MOD(yourcolumn, 1000)


MYSQL food ordering portal database design

First: don't code, draw.  It's much easier to make mistakes in diagrams than it is to try and visualize relations, keys, etc. in code.  At this point in design column types are irrelevant.  Focus on keys and relations.
This may be a good starting point*:

Here we have customers who can place orders for restaurants.  Those orders cannot contain items not offered by that restaurant.  Those orders can include the same item at different prices (buy 1, get 1 50% off, etc).  Those orders can include fees, including delivery fees which can be looked up from a schedule.
Questions not answered by this diagram:

How do we determine if a OrderLine is a menu item or a charge and enforce that?
How is the status of an Order defined?
What exactly encompasses OrderLineCharge?
Should the order request a menu item/quantity which is then becomes an OrderLine?
When an MenuItem is requested but can't be fulfilled, what happens?
How are price changes stored?
Should we track types of cuisine or categories of menu items?
How is delivery tracked?
How are payments tracked?
How do we track quantity discounts ($2 each, 3 for $5, etc)?
Should it be 'OrderId' or 'OrderNumber'

That is a lot of questions we won't solve with lines of code or table definitions, at least not yet.
The next steps I'd recommend are:

Familiarize yourself with ERDs, specifically IDEF1X which are easily read and understood
Start thinking about business rules and how they might shape your data model - what problems can you solve through constraints/relations?  Which problems will require the transactions to contain rules?

*I'm leaving a lot out because this isn't something that can be answered in one step - I don't know if this is a homework assignment, hobby project, or actual work.


Multiple availability groups--can each availability group have its own listener?

Yes, you can and should create one listener per Availability Group.  This is mentioned in passing in the MS Docs article Configure a listener for an Always On availability group:

  You can create only one listener per availability group through SQL Server. Typically, each availability group requires only one listener.


How to Identify Who or What is Restarting SQL services via Service Control?

If you have the default trace running, and you should have it running, it may be able to help you see what happened. The default trace is lightweight and among other things does track Server Stop and Server Start. See Feodor Georgiev's article:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

The trace logs recycle fairly quickly on a busy server, so you would need to check as soon as possible after a Server Stop and Server Start. Toward the bottom of the heading Security Audit Events there is code to ‘Audit Server Starts and Stops’.  (Tweaked only slightly to include the default count of trace logs.)

SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), 4) T  -- 4 is the default number of trace log files
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Server Starts and Stops' )


Get latest status of an item in the database

Use Partition by to get last record where Status is failed.

    ---Test table
    CREATE TABLE [dbo].[items](
        [id] [int] NULL,
        [Item-FK] [int] NULL,
        [timestamp] [datetime] NULL,
        [status] [varchar](200) NULL
    ) ON [PRIMARY]
    GO

--dummy data
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (1, 123, CAST(0x0000A3E0006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (2, 123, CAST(0x0000A3E1006DB876 AS DateTime), N'FAILED')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (3, 124, CAST(0x0000A3E1006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (4, 124, CAST(0x0000A3E1006DB876 AS DateTime), N'ok')
INSERT [dbo].[items] ([id], [Item-FK], [timestamp], [status]) VALUES (5, 125, CAST(0x0000A3E1006DB876 AS DateTime), N'FAILED')

--- Query to get all items who's last status is 'Failed'
    SELECT  *
    FROM    items
    WHERE   [Item-FK] IN (
            SELECT  [Item-FK]
            FROM    ( SELECT    row_number() OVER ( PARTITION BY [Item-FK] ORDER BY [timestamp] DESC ) iid,
                                *
                      FROM      items
                    ) item
            WHERE   iid = 1 AND [status] ='FAILED')

EDIT: you can use EXISTS instead on IN

SELECT  *
FROM    items itm
WHERE   EXISTS ( SELECT [Item-FK]
                 FROM   ( SELECT    row_number() OVER ( PARTITION BY iitm.[Item-FK] ORDER BY iitm.[timestamp] DESC ) iid,
                                    *
                          FROM      items iitm
                        ) item
                 WHERE  item.iid = 1
                        AND item.[status] = 'FAILED'
                        AND itm.[Item-FK] = item.[Item-FK] )


Cannot find the user, because it does not exist or you do not have permission

You've created the login for user1 and user2 to the master database not the database you want.

rearrange to:

CREATE LOGIN USER1
WITH PASSWORD = 'password';
GO

CREATE DATABASE my_database;
ALTER AUTHORIZATION ON DATABASE ::my_database TO USER1;

CREATE LOGIN USER2
WITH PASSWORD = 'password';
GO
USE my_database;
GO
CREATE USER USER2 FOR LOGIN USER2;
GO

Then you can add permissions to that user as it will exist in that database.

NOTE: you may want to go into your master database (or whichever database you were running those commands in before) and remove USER1 and USER2


Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?

Better?  It depends.
If the primary key of TABLE_LC is (lc_num, brn_role) then you could do something like this:
SELECT
  lc_Num
 ,MAX
    (
      CASE
        WHEN brn_role = 1010 THEN brn_name
        ELSE NULL
      END
    ) AS Advising_Bank
 ,MAX
    (
      CASE
        WHEN brn_role = 2020 THEN brn_name
        ELSE NULL
      END
    ) AS Issuing_Bank
FROM
  (
    SELECT
      LC.lc_num
     ,LC.brn_role
     ,LC.brn_code
     ,BRN.brn_name
    FROM
      TABLE_LC LC
    INNER JOIN
      BRN BRN
        ON BRN.brn_code = LC.brn_code
  ) x
GROUP BY
  lc_num

Which, you'll note:

Is about the same amount of code
Is harder to understand
Even though it only performs one join, it still might not produce the best query plan, depending on whether or not TABLE_LC is index-organized.

You could also use PIVOT, but again, probably not that much time savings and you're left with the problem of dealing with goofy column names.
Joins on primary keys/appropriate indexes are generally not that expensive, so avoiding them only makes sense in certain situations.


App domain is marked for unload due to memory pressure

Microsoft has a KB article about this behavior. You got a 32-bit OS with 32-bit application, so the process' address space is 4 GiB. AWE can be used to access more memory, but it is limited only for buffer pool usage. As per the documentation:

  The SQL Server buffer pool can fully utilize AWE mapped memory;
  however, only database pages can be dynamically mapped to and unmapped
  from SQL Server's virtual address space and take full advantage of
  memory allocated through AWE. AWE does not directly help supporting
  additional users, threads, databases, queries, and other objects that
  permanently reside in the virtual address space.

Short-term solution: review that your CLR code minimizes memory usage.

For a long-term solution, upgrade to 64-bit.

Addendum

Kimberly Tripp has written a white paper about CLR stuff from DBA point of view. She's got a few nice queries and Perfmon counters that are of use for tracking CLR resource use.


Is there any danger using binary data over an UTF-8 MySQL connection?

From my experience storing such data, is it perfectly safe to transmit binary data when the character encoding of the connection is configured as UTF-8. The character encoding is just that: a way of representing the characters in binary form (this is the crux of the issue in Python bytes/str problems). MySQL rightly does not try to 'encode' binary data.

From the fine manual (emphasis added by me):

  The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.

You should be aware that "the connection" is not UTF-8, but rather UTF-8 is the encoding that the application and MySQL have agreed upon in order to represent text characters.


Read uncommited does not work in UPDATE WHERE NOT EXISTS

There is no "first update", "second update" here, there is one statement, one atomic update. And this update produces 2 rows that are the same.

If you want "the second" update to not succeed you should write 2 explicit updates, not one, inside 1 transaction. But in this case nolock would have no sense because the same transaction sees its own uncommitted values without any hint at all.


How do I add versioning to this CMS I'm designing?

The standard way is to use a History Table pattern.

If you have a table like this:

create table foo (
  int foo_id primary key,
  name varchar(x) not null
);

You would have a corresponding table

create table foo_history (
  int foo_id,
  int version,
  name varchar(x),
  inserted_at timestamp not null default current_timestamp,
  operation_code char(1) not null, -- D=deleted, U=updated [, I=inserted]
  by_username varchar(x) not null,

  primary key (foo_id, version)
);

The data in this table would be filled by triggers on foo.

If you use Java, you could use Hibernate Envers to automate this for you.


Is it normal that our Wordpress' database crashed multiple times in this month alone?

Don't ever use MyISAM. Ever. Under any circumstances. Just don't. If you need its performance characteristics for some reason, use MariaDB with Aria tables.

MyISAM is not crash safe, and will corrupt data when it breaks.

Also, my webserver with WordPress has over 5k posts and has never crashed its db.

This is not normal behavior.


Why does removing these LOWER calls change the execution plan like this?

SQL Server maintains statistics on substrings in string columns in the form of tries that are usable by LIKE queries.

See the String Summary Statistics section for more about this.

Wrapping the column in a function call quite likely blocks any attempt to sniff the value of the parameters and use the statistics on the column to estimate the number of matching rows and so it falls back on guesses. The differing cardinality estimates can cause different execution plans.


Oracle using sub-optimal index on bind variables

I would create a composite index on (CUSTOMER_ID, ORDER_DATE) and that should fix all your issues, assuming ORDER_DATE is a DATE data type.  And assuming ORDER_DATE should not be null, ensure the table definition says NOT NULL.  I am certain Oracle is not using "some random value" if you gathered statistics correctly.  You tagged your question with both 11g and 12c; you running different versions on different instances?

Is your bind variable the correct data type for the ORDER_DATE?  If this is a vendor application, you may need to create a function-based index on the ORDER_DATE column to produce a reasonable date that matches the format of the bind variable.


General Question on how to setup db's for a social networking site

There have been volumes upon volumes written on normalization. No one here can tell you how normalized your database should be without information. The best advice is to read and fully understand what normalization is and, once you have that understanding, you'll be able to apply it to your application and database design.

Storing images in database? All I can say is test test test. It's going to depend on the application, hardware, table-size, etc. Luckily, for something like that, it's easy enough to store both the image and path in the same table and you can easily QA each.

I say this with absolutely no malicious intent, but if you need to ask about database design at that high of a level, you may be much better off contracting an experienced DBA/Data Architect to do the design legwork once you have the application layer designed.


MySQL Memory & max_connections Performance Tuning

After thorough research, we determined that the problem was that WordPress was being attacked and it was causing MySQL to use the maximum amount of memory allotted to it in the configuration files.  Even caching of WordPress pages could not stop this illegitimate traffic from using all of the memory because of the sheer volume of IP addresses the attackers had at their disposal.  We ended up getting CloudFlare and that seems to have resolved the problems by blocking the DDoS at the DNS level. We will also be looking at getting more memory for the server, to prevent this from happening with heavy legitimate traffic in the future.


Data Tier Applications (DAC) on replicated databases

The short answer is: SSDT (dacpacs) does not support SQL Server replication.

The longer answer is that you might be able to work around this limitation by using pre and post deploy scripts.  And maybe write your own deployment contribution extension to handle replicated objects better.

For the pre and post deploys scripts approach you could write something that captures the current replication state (publications, articles, subscribers and subscriptions) and then pulls all that down.  Then the dacpac updates the current schema and a post deploy script(s) recreates the replication.

If you're strict on your replication topology and configuration this could work.  It won't be easy though!  Another similar approach, which would also buy into your "get the database under souce control" is you only allow replication to be configured from scripts and these become your pre/post deploy changes (much like above but less dynamic work).

Another approach would be to use another system for database control and deployments.  Something like:

DbUp
FlywayDB

These both take the database migrations approach rather than model first approach.  These would probably make the replication aspects easier to handle.


update column based on sum of data from another table

If you have a primary key, you don't have duplicate rows.

In Oracle, you do multi-table UPDATEs by using MERGE, and not UPDATE, because that is inefficient.

merge into table1
using (select col1, col2, col3, col4, sum(val2) sum_val2 from table2 group by col1, col2, col3, col4) table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.sum_val2, table1.status = 'V';

If col1, col2, col3, col4 are really a composite primary key, no need to GROUP BY and SUM:

merge into table1
using table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.val2, table1.status = 'V';


Secondary Clustered DTC will not work when Primary goes offline - SQL Server Issues

I don't believe what you want to do is possible through Windows or SQL Server configuration. Per Microsoft's guidance here, SQL Server will choose an MSDTC instance when it starts and, if that MSDTC instance fails, distributed transactions will fail:

What happens if the MSDTC Resource that was picked at SQL Service startup fails?
If the MSDTC instance that is being used by a specific SQL Server Group fails, SQL Server does not automatically attempt to use the next available MSDTC Resource such as the default cluster instance or the local machine instance of MSDTC.
You would need to completely remove the failed instance of MSDTC from the SQL Server group to use another instance of MSDTC.
Likewise, if you create a mapping for SQL Server and the mapped instance of MSDTC fails, your distributed transactions will also fail. If you want  SQL Server to use a different instance of MSDTC, you must either add an instance of MSDTC  to the local cluster group of the SQL Server or delete the mapping.

This matches the behavior you're seeing; SQL Server successfully opens DTC transactions through DTC1 but doesn't automatically switch to using DTC2 if DTC1 fails.
In some cursory tests I found:

DTC transactions resumed working when the failed MSDTC instance recovered.

SQL Server was indeed able to automatically switch to using a different MSDTC resource without a restart after the MSDTC resource it was using was deleted (as opposed to just failed).

The tests I ran weren't comprehensive and there might be scenarios where these findings aren't true.
The more important point in the document is that you may not need to cluster MSDTC at all:

What will SQL do if I do not Cluster MSDTC in Windows 2008 and above?
If there is no MSDTC resource in the cluster then SQL Server will use the MSDTC service that is running locally on the node.
What is the Simple recommendation for MSDTC with Windows 2008 and later?
In Windows 2008 and later you either Create a clustered instance of the MSDTC resource for EVERY SQL Server instance /Group that requires its functionality or DO NOT CLUSTER MSDTC at all.

Based on this, you should be able to achieve pretty good high availability by relying on local MSDTC resources (in which case you might need to configure them to restart automatically on failure) or through adding a clustered MSDTC resource to each SQL Server cluster group if you prefer for some reason. Windows can handle restarting the service on failure, and SQL Server appears to successfully resume creating DTC transactions once the MSDTC service recovers.
If you're trying to protect against scenarios not covered here (e.g., what happens if the DTC resource fails and can't restart automatically?) you might be forced to deal with that manually or at the application layer.
It will be interesting to see if Microsoft provides you with different advice.


accessing, updating a large MYSQL table advice and guidance

With the data visibility being only 7+1 days and keeping all the data for much longer, it sounds like what you really need to implement is partitioning by day. This will also make it orders of magnitude easier and quicker to purge the data you no longer wish to keep.

For archiving, you can use a 2nd replicated server that has longer partition retention. For example, the primary server can keep only 8 daily partitions, bit your scheduled event can omit partition dropping from the replication log by toggling sql_log_bin around partition dropping. The result would be that your primary server only has 8 days of data on it and is kept small and fast, while your archive replica keeps all the partitions for a much longer period, up to 8192 partitions at least, which is the limit on the number of partitions on a table. You then have 20-odd years to implement additional further archiving if you really need to keep the data forever. ;-)

In addition to that - it may be worth sharing your table definitions and slow queries - there is always the possibility that there is a more efficient way to query for the data or a better way to index, or a way to pre-aggregate for queries that have to touch thousands of rows.


How can i Update the status of field with a trigger?

Please consider creating a minimal, complete, and verifiable example for this and any future questions you create.  As it is, your question is extremely imprecise and open to a lot of interpretation about what the actual problem is you are trying to solve.

Aside from that, there are several issues I see immediately.

SQL Server DML triggers use the "special" virtual tables, inserted and deleted, as placeholders containing the rows that will have been modified.  You should use them in your statements to only update the affected rows.  Microsoft Docs has a great article about that here.

An example for your first trigger:

create trigger MyTrigger on myTable
after update
as
begin
   set nocount on;
   update talleres
   from talleres t inner join inserted i on t.talleres_id = i.talleres_id
   set status='active'
   where FirstDate=getdate()
end
go

In the above example, I'm assuming you have a column that uniquely identifies rows in the talleres table and the MyTable table.

The WHERE firstdate = getdate() construct is likely going to be problematic since getdate() returns a very precise value that is perhaps unlikely to match any rows in talleres.


The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.

There are 3 SQL*Plus shutdown commands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:

shutdown normal or just shutdown: waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open. This used to be the only shutdown mode that did not cancel running transactions.
shutdown transactional: disconnects sessions once currently running transactions complete, preventing new transactions from beginning.
shutdown immediate: disconnects all sessions immedately and rolls back interrupted transactions before shutting down. Note that the disconnections are immediate, but the shutdown may not be as any interrupted transactions may take time to roll back.

The fourth mode of shutdown is shutdown abort. This is like pulling the power cord - the instance stops now without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide says:

  This mode is intended for emergency situations, such as when no other form of shutdown is successful.

All the examples you give perform a checkpoint as part of the shutdown [normal] or shutdown immediate so explicit checkpointing is presumably to reduce the time required for recovery.

general advice:

Do not use shutdown normal.
Use shutdown transactional for attended shutdown only, when you want to minimise cancelled transactions (attended only because this kind of shutdown is not guaranteed to shut the database down at all if timeouts are breached).
Use shutdown immediate for unattended shutdown or when you do not care about currently running transactions.
Do not use shutdown abort (plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to minimise downtime this mode may be appropriate.


MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

At 250/hour, all possible techniques are plenty fast.  Anyway, I will throw my 2 cents in...

Multi-row INSERT of 100 rows will run 10 times as fast as 100 individual INSERTs.

Multi-row INSERT may lead to gaps in AUTO_INCREMENTs.  In general, the command will preallocate all the ids it might need, then 'burn' the ones id did not use.  (REPLACE burns lots, since it is DELETE + INSERT.)

For efficient "normalization" do something like:

INSERT IGNORE INTO HostNorm (host_name)
    SELECT DISTINCT s.host_name
        FROM Staging AS s
        LEFT JOIN HostNorm AS n  ON n.host_name = s.host_name
        WHERE n.host_id IS NULL;

A list of possibly new values is in Staging.
HostNorm is where 'new' rows need to be put.
Only 'new' rows are fed to INSERT (see LEFT JOIN).
IGNORE is to accommodate having multiple threads (connections) doing similar inserts that could collide.  (A rare collision would lead to a burned id.)
There is no cap on how many rows this can handle.
This works best with, say, 100 rows.
This is designed for very high speed input; it should work fine for your relatively slow ingestion rate.

More discussion in my blog.


Snapshot replication - How to detect when a snapshot has arrived on the Subscriber from the Subscriber DB

Change it to a pull subscription, and watch for the SQL Agent job to stop.  When it stops the snapshot has been applied.  Or you could get really creative and setup a linked server to the distributor if you want to keep it as a pull subscription and watch for the job on the distributor to stop.


Generate nested array with OPENJSON very slow

I had a hard time parsing your query, however I believe this returns the same result, and is much faster:

SELECT Parent = c.Serial
    , Children = (
        SELECT c.Serial
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        FOR JSON PATH
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
FOR JSON PATH;

The plan for the above query looks like:

The plan for your query looks like:

If we add the following index, we can make the first variant even faster:

CREATE NONCLUSTERED INDEX IX_AggregationChildren_IX0
ON dbo.AggregationChildren (AggregationId)
INCLUDE (AggregationChildrenId,Position);

Clearly, you'd need to evaluate this against your workload, however.

I've created a minimally viable complete example setup to use for testing:

USE tempdb;

IF OBJECT_ID(N'dbo.AggregationChildren', N'U') IS NOT NULL
DROP TABLE dbo.AggregationChildren;
IF OBJECT_ID(N'dbo.Aggregation', N'U') IS NOT NULL
DROP TABLE dbo.Aggregation;
IF OBJECT_ID(N'dbo.Code', N'U') IS NOT NULL
DROP TABLE dbo.Code;
GO

CREATE TABLE dbo.Code (
    CodeId int NOT NULL
        CONSTRAINT PK_CODE
        PRIMARY KEY
        CLUSTERED
    , Serial nvarchar(20) NOT NULL
);

CREATE TABLE dbo.Aggregation (
    AggregationId int NOT NULL
        CONSTRAINT PK_AGGREGATIONS
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT FK_Aggregation_Code
        FOREIGN KEY (AggregationId)
        REFERENCES dbo.Code (CodeId)
)

CREATE TABLE dbo.AggregationChildren (
    AggregationChildrenId int NOT NULL
        CONSTRAINT PK_AGGREGATION_CHILDS
        PRIMARY KEY
        CLUSTERED
        CONSTRAINT FK_AggregationChildren_Code
        FOREIGN KEY (AggregationChildrenId)
        REFERENCES dbo.Code (CodeId)
    , AggregationId int NOT NULL
        CONSTRAINT FK_AggregationChildren_Aggregation
        FOREIGN KEY (AggregationId)
        REFERENCES dbo.Aggregation (AggregationId)
        ON DELETE CASCADE
    , Position int NOT NULL
)

I re-formatted the constraint clauses to more kind to my brain; essentially, the above code is the same as the DDL in your question.

This populates the three tables with enough data to make meaningful comparisons:

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Code (CodeId, Serial)
SELECT s1.Val
        + (s2.Val * 10)
        + (s3.Val * 100)
        + (s4.Val * 1000)
        + (s5.Val * 10000)
    , CONVERT(bigint, CRYPT_GEN_RANDOM(8))
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Aggregation (AggregationId)
SELECT s1.Val
    + (s2.Val * 10)
    + (s3.Val * 100)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3;

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.AggregationChildren (AggregationChildrenId, AggregationId, Position)
SELECT s1.Val
        + (s2.Val * 10)
        + (s3.Val * 100)
        + (s4.Val * 1000)
        + (s5.Val * 10000)
    , s1.Val
        + (s2.Val * 10)
        + (s3.Val * 100)
    , s1.Val
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5;

These are the row counts for each table:

╔════════╦═════════════╦═════════════════════╗
║  Code  ║ Aggregation ║ AggregationChildren ║
╠════════╬═════════════╬═════════════════════╣
║ 100000 ║        1000 ║              100000 ║
╚════════╩═════════════╩═════════════════════╝

My version of the query:

SELECT Parent = c.Serial
    , Children = (
        SELECT c.Serial
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        FOR JSON PATH
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
FOR JSON PATH;

To compare the output of both queries, I've created two user-defined-functions, as in:

CREATE FUNCTION dbo.fn_json_test_1()
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT Parent = c.Serial
            , Children = (
                SELECT c.Serial
                    , cac.Position
                FROM dbo.Code cc
                    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
                WHERE cac.AggregationId = a.AggregationId
                FOR JSON PATH
            )
        FROM dbo.Code c
            INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
        FOR JSON PATH
    );
END;
GO

GO
CREATE FUNCTION dbo.fn_json_test_2()
RETURNS nvarchar(max)
AS
BEGIN
    RETURN (
        SELECT c.Serial as Parent,
            (Select co.Serial, agc.Position
              from AggregationChildren agc, Aggregation ag, Code co
              where agc.AggregationId = a.AggregationId
              and co.CodeId = agc.AggregationChildrenId for json path) as children
        from Aggregation a, Code c
        where c.CodeId = a.AggregationId for json path
    );
END;
GO

Now, I can compare the output for both queries via:

DECLARE @res1 nvarchar(max) = dbo.fn_json_test_1();
DECLARE @res2 nvarchar(max) = dbo.fn_json_test_2();

SELECT CASE WHEN @res1 <> @res2 THEN 'mismatch' ELSE 'match' END;

The result is:

The results do NOT match.  The output of my query contains less child nodes than your query.  I'm going back to the drawing board, and will simplify the test bed to see where the disparity lies.

The simplified testbed consists of 10 rows in the Code table, 2 rows in the Aggregation (Parent) table, and 8 rows in the AggregationChildren (child) table:

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) n(Val)
)
INSERT INTO dbo.Code (CodeId, Serial)
SELECT s1.Val
    , CONVERT(bigint, CRYPT_GEN_RANDOM(8))
FROM src s1

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1)) n(Val)
)
INSERT INTO dbo.Aggregation (AggregationId)
SELECT s1.Val
FROM src s1;

;WITH src AS
(
    SELECT n.Val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7)) n(Val)
)
INSERT INTO dbo.AggregationChildren (AggregationChildrenId, AggregationId, Position)
SELECT s1.Val + 2
    , s1.Val % 2
    , s1.Val
FROM src s1;

Row counts:

SELECT Code = (SELECT COUNT(1) FROM dbo.Code)
    , Aggregation = (SELECT COUNT(1) FROM dbo.Aggregation)
    , AggregationChildren = (SELECT COUNT(1) FROM dbo.AggregationChildren)

╔══════╦═════════════╦═════════════════════╗
║ Code ║ Aggregation ║ AggregationChildren ║
╠══════╬═════════════╬═════════════════════╣
║   10 ║           2 ║                   8 ║
╚══════╩═════════════╩═════════════════════╝

The predicted pattern should be two parent json arrays, each with 4 child arrays.

My Results:

[
  {
    "Parent": "-5601362097731340301",
    "Children": [
      {
        "Serial": "-5601362097731340301",
        "Position": 0
      },
      {
        "Serial": "-5601362097731340301",
        "Position": 2
      },
      {
        "Serial": "-5601362097731340301",
        "Position": 4
      },
      {
        "Serial": "-5601362097731340301",
        "Position": 6
      }
    ]
  },
  {
    "Parent": "-8896860091721838065",
    "Children": [
      {
        "Serial": "-8896860091721838065",
        "Position": 1
      },
      {
        "Serial": "-8896860091721838065",
        "Position": 3
      },
      {
        "Serial": "-8896860091721838065",
        "Position": 5
      },
      {
        "Serial": "-8896860091721838065",
        "Position": 7
      }
    ]
  }

Your query:

[
  {
    "Parent": "-5601362097731340301",
    "Children": [
      {
        "Serial": "5802227619253639548",
        "Position": 0
      },
      {
        "Serial": "5802227619253639548",
        "Position": 0
      },
      {
        "Serial": "4504664379821512162",
        "Position": 2
      },
      {
        "Serial": "4504664379821512162",
        "Position": 2
      },
      {
        "Serial": "6561435639659176802",
        "Position": 4
      },
      {
        "Serial": "6561435639659176802",
        "Position": 4
      },
      {
        "Serial": "-7417083263182709739",
        "Position": 6
      },
      {
        "Serial": "-7417083263182709739",
        "Position": 6
      }
    ]
  },
  {
    "Parent": "-8896860091721838065",
    "Children": [
      {
        "Serial": "-7646118996434234523",
        "Position": 1
      },
      {
        "Serial": "-7646118996434234523",
        "Position": 1
      },
      {
        "Serial": "-6372739442099935942",
        "Position": 3
      },
      {
        "Serial": "-6372739442099935942",
        "Position": 3
      },
      {
        "Serial": "-882384147532911428",
        "Position": 5
      },
      {
        "Serial": "-882384147532911428",
        "Position": 5
      },
      {
        "Serial": "4293317573306886053",
        "Position": 7
      },
      {
        "Serial": "4293317573306886053",
        "Position": 7
      }
    ]
  }
]

Your query has too many children; my query returns the predicted number of children, and it returns the correct Position values, however is returning the incorrect Serial values.

The "bug" in my query appears in the inner query.  The incorrect query is:

SELECT c.Serial
    , cac.Position
FROM dbo.Code cc
    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
WHERE cac.AggregationId = a.AggregationId
ORDER BY c.Serial
FOR JSON PATH

The correct version is:

SELECT cc.Serial --changed "c." to "cc."
    , cac.Position
FROM dbo.Code cc
    INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
WHERE cac.AggregationId = a.AggregationId
ORDER BY cc.CodeId --not a big deal, but different order for children in output
FOR JSON PATH

The corrected query now looks like:

SELECT  Parent = c.Serial
    , Children = (
        SELECT cc.Serial
            , cac.Position
        FROM dbo.Code cc
            INNER JOIN dbo.AggregationChildren cac ON cac.AggregationChildrenId = cc.CodeId
        WHERE cac.AggregationId = a.AggregationId
        ORDER BY cc.CodeId
        FOR JSON PATH
    )
FROM dbo.Code c
    INNER JOIN dbo.Aggregation a ON c.CodeId = a.AggregationId
ORDER BY c.Serial
FOR JSON PATH;

And returns the following result:

[
  {
    "Parent": "-195930341251513493",
    "Children": [
      {
        "Serial": "-6126601633786720400",
        "Position": 1
      },
      {
        "Serial": "5216562173012877678",
        "Position": 3
      },
      {
        "Serial": "-1992909345438478098",
        "Position": 5
      },
      {
        "Serial": "8329388691987940194",
        "Position": 7
      }
    ]
  },
  {
    "Parent": "8774608126018975726",
    "Children": [
      {
        "Serial": "-3380643917643646211",
        "Position": 0
      },
      {
        "Serial": "-2042609074595538493",
        "Position": 2
      },
      {
        "Serial": "7345460002653774160",
        "Position": 4
      },
      {
        "Serial": "-2126530822210070443",
        "Position": 6
      }
    ]
  }
]


For the timeout issue, you can increase the Query Timeout value in the merge agent.

See this Replication Merge Agent reference to specifics on the QueryTimeOut.


What's the difference between Linked Server solution and SSIS solution?

Linked Servers allow you to connect from SQL Server on an adhoc basis to another datasource, be it SQL Server, Oracle, or something else. Adhoc is the key word, so occasional use is fine. You'll see a lot of negative comments online about performance, hopefully Microsoft will fix in the next SQL Server after Denali.

SSIS is a more robust way of moving and transforming data, with very good exception handling. Getting some data out of Oracle should be straightforward (try the import/export wizard), but SSIS is generally considered to have a steep learning curve. On the other hand, it will make you a more valuable database professional.

It's worth mentioning replication - whilst not trivial, it's a great way of getting data around the enterprise.


It would be helpful to have SHOW CREATE TABLE, but I will make some guesses...

You have INDEX(QualityLevel), and that index is smaller than the buffer_pool?
You have been doing a bunch of queries; these were not the first?  (Hence some things are cached.)
You did not use SQL_NO_CACHE to avoid the Query cache?

How much RAM do you have?  innodb_buffer_pool_size=4GB is rather small unless you have only 6GB.

The COUNT(DISTINCT ..) seems too fast -- probably caching helped.

When doing timing tests:

SELECT SQL_NO_CACHE ...   -- to avoid the Query cache.

and run the query twice -- to compensate for I/O caching.

Responding to comments

You should switch to InnoDB.  Then, with 250GB of RAM, set innodb_buffer_pool_size = 200G.  This will help InnoDB performance.  (Note: MyISAM does not use that at all; instead, it uses key_buffer_size, which should be set to about 50G for a MyISAM-only setup.)

In general, adding a useful index is worth doing -- in spite of the negative cost considerations (disk space an insert cost).

When building a composite (multi-column) index, the cardinality of the individual columns is irrelevant.  The index becomes a unit, the components of which are not noticed.

For this query:

select count(recordID) from patient_records
      where QualityLevel>10.14 and snpID='.';

the following is optimal in this order:

INDEX(snpID, ,      -- because of `=`
      QualityLevel, -- a range, so comes after `=`
      recordID)     -- tacked on to make it "covering"

A note about COUNT(recordID) -- that checks which rows have a non-NULL value for recordID.  This test is unnecessary since it is declared NOT NULL.  I suggest getting in the habit of simply saying COUNT(*).

With COUNT(*), INDEX(snpID, QualityLevel) would be as good.

To design the optimal indexes, you need to know the SELECTs, DELETEs, and UPDATEs.  You cannot pre-guess.  See my Cookbook .

Another note:  The order of WHERE clauses does not matter; the Optimizer tries all reorderings of the AND clauses.  It will try all reorderings of multiple tables JOINed together.  (LEFT may or may not limit what the Optimizer will try.)

need to build a web interface for these queries

It is passe to say "Exactly 56627747 widgets" in a web interface.  Even "About 56 million" is rarely used these days.  And that could be efficiently achieved by a nightly job that recomputes that, and other, statistics about your data.

If you do need both precision and speed, then build a Summary Table and query it.  That table could, for example, have daily counts for the number of widgets in various quality brackets.

Summary Tables are the key to billion-row datasets.


You are getting two rows per document because you are grouping by documents.status. You need to use SUM() slightly differently and then remove documents.status from the GROUP BY clause:

Select   documents.code
        ,positions.fullname
        ,SUM(case when documents.status='In' THEN positions.quantity ELSE 0 END) AS DocIn
        ,SUM(case when documents.status='Out' THEN positions.quantity ELSE 0 END) AS DocOut
from positions
inner join documents
 on documents.unique_id=positions.unique_id
 group by documents.code, positions.fullname


Can I compress two rows with a difference in two columns into a single row with both columns?

Do I understand correctly and you need something like:

SELECT prod1.prod_id,
       link1.prod_link_disabled,
       loc1.location_id AS src_location_id,
       loc1.location_id AS dst_location_id,
       loc1.customer_id,
       cust1.customer_name
  FROM product AS prod1
  JOIN product_link AS link1 ON link1.prod_id = prod1.prod_id
  JOIN region AS region1 ON region1.region_id = link1.region_id
  JOIN location AS loc1 ON loc1.loc_id = region1.loc_id
  JOIN customer AS cust1 ON cust1.customer_id = loc1.customer_id
  JOIN product AS prod2 ON prod2.prod_id = prod1.prod_id
  JOIN product_link AS link2 ON link2.prod_id = prod2.prod_id
  JOIN region AS region2 ON region2.region_id = link2.region_id
  JOIN location AS loc2 ON loc2.loc_id = region2.loc_id
  JOIN customer AS cust2 ON cust2.customer_id = loc2.customer_id
 WHERE prod1.is_source AND
       NOT link1.prod_link_deleted AND
       NOT prod2.is_source AND
       NOT link2.prod_link_deleted AND
       prod1.prod_id = 5874;

Sure, you can remove excessive JOINs.


Maintenance plan fails on non-existent database

In order to identify where this is coming from (because who knows which step in your maintenance plan is actually tripping on this), I recommend running the following tedious code, which will identify views that show the database name even if it is a database that no longer exists in sys.databases (dropped, renamed, what have you):

DECLARE @db SYSNAME = N'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}';

DECLARE @sql NVARCHAR(MAX) = N'SELECT N''--''';

SELECT @sql += N'
 UNION ALL SELECT TOP (1) ''SELECT * FROM msdb.'
   + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
   WHERE ' + QUOTENAME(c.name) + ' = '''''' + @db + '''''';''
 FROM msdb.' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 + ' WHERE ' + QUOTENAME(c.name) + ' = @db'
 FROM msdb.sys.all_objects AS o
 INNER JOIN msdb.sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 INNER JOIN msdb.sys.all_columns AS c
 ON c.[object_id] = o.[object_id]
 WHERE c.name IN (N'database_name',N'dbname',N'db_name',N'Database Name')
 AND o.type IN (N'U',N'V');

SELECT @sql += N'
 UNION ALL SELECT TOP (1) ''SELECT * FROM master.'
   + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
   WHERE ' + QUOTENAME(c.name) + ' = '''''' + @db + '''''';''
 FROM master.' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
 + ' WHERE ' + QUOTENAME(c.name) + ' = @db'
 FROM [master].sys.all_objects AS o
 INNER JOIN [master].sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 INNER JOIN [master].sys.all_columns AS c
 ON c.[object_id] = o.[object_id]
 WHERE c.name IN (N'database_name',N'dbname',N'db_name',N'Database Name')
 AND o.type IN (N'U',N'V');

SELECT @sql;

EXEC sys.sp_executesql @sql, N'@db SYSNAME', @db;

This should produce output in the bottom pane like this (hopefully only a few rows):

--
SELECT * FROM msdb.[dbo].[backupset]
  WHERE [database_name] = 'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}';

You can copy and paste that output into the top pane and run it, and then act accordingly. For rows you find in backupset, for example, you can just delete them. If they are in catalog views this may be a little tougher - tell us what you find and we should be able to figure out how to fix it.

(Also, this isn't exhaustive, there are likely other places a database name might be stored using other variations on the name column. By design, I'm avoiding the obvious places like sys.databases.name, because you claim you've already checked there.)

Now, I would recommend Ola Hallengren's free maintenance scripts or even paid solutions like SQL Sentry Fragmentation Manager* for various types of maintenance, over the cumbersome and buggy native maintenance plans.

Short of that, stop using "all user databases" since this seems to cover more than just the set of databases that are currently present. I'll be interested to hear if you find this database name in any of the catalog views, backup-related, or job-related tables. I also have a tip publishing soon about searching all string columns across all databases on an instance, and it might be even more productive than the targeted script I wrote above. I'll try to remember to come back here and update this post with a link once it has been released.

Disclaimer: I work for SQL Sentry.


Is a relation R(A,B,C) with functional dependencies {AB->C, C->B} in BCNF?

Given AB->C, C->B we can infer only that AB and AC are keys. It follows that C is not a superkey and therefore R does not satisfy BCNF.

The following is a possible decomposition that would satisfy BCNF

R1{A,B} KEY {A,B}
R2{A,C} KEY {A,C}
R3{C,B} KEY {C}


How to structure a MongoDB database to allow for multiple users to use the same account with restrictions based on roles?

When working on an authorization requirement for any application, there are three essential elements that are part of the setup:

•   The resources that need to be secured/authorized
•   A list of roles and users that are part of these roles
•   A mapping between the resources and the roles that define who can access what