File system

A very popular method of doing this is with the file system. BTRFS and ZFS works under the database at the file system level. Both can be used in a loopback device so you can provide a compressed tablespace without having another partition. There are caveats with this, if the tablespace fails it may take your cluster too.

ZFS

ZFS is the big one here. It's what I would go for.

See also

https://blog.2ndquadrant.com/pg-phriday-postgres-zfs/
https://blogs.oracle.com/observatory/zfs-compression-a-win-win (popular on Oracle too)

Btrfs

Btrfs is a strong contender but it's been in active development for a very long time, and the lack of major distros picking it up as a default has many people questioning whether or not it's ready for "prime time."

https://btrfs.wiki.kernel.org/index.php/Compression

PostgreSQL

GIS Methods (PostGIS)

For Polygons, one method is to simplify polygons by dropping vertices with ST_Simplify.
For Points, one method is spatial clustering.

Both of these result in a loss of information. PostGIS, like most of the features of the database, doesn't have a transparent "magic compression" option.

cstore_fdw

There is also cstore_fdw which is a columnar store that offers compression. It has a different performance profile so ymmv.


Setting up a mirror: can't find remote server

I'll post my own answer to this.

What I did wrong and this should have been very obvious, but apparently I missed this.
The command: ALTER DATABASE [xxx] SET PARTNER = 'TCP:\....:7023' Should have been run first on the mirror and second on the principal.

So in the end, my networking was, indeed, fine, but a blatant mishap when initiating mirroring on my part.


It seems like you could use DATEADD pretty easily.

SELECT DATEADD(YEAR, 3, '2019-01-01') AS math

Just substitute in the columns in your table, something like this:

SELECT DATEADD(YEAR, contract_term, contract_date) AS math
FROM dbo.your_table;


This is happening because your default database block size is set to something other than 32k, and as a result there's no 32k cache buffers available.

To fix this, add:

#change 1024M to the desired amount of cache
db_32k_cache_size=1024M

... to the init.ora for your database instance & then restart the database.

You may also be able to change this without restarting (as sysdba from sqlplus):

ALTER SYSTEM SET DB_32K_CACHE_SIZE=1024M;


Keep one row per ID while keeping previous order (from ORDER BY)

You possibly need something along the lines of groupwise max.


CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING

You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The culprit is the user query blocking the final phase. Look at sys.dm_exec_request.blocking_session_id but make sure to walk up the chain until you find the one spid that is blocking w/o being blocked in turn.


MS SQL Server 2014: which stored procedure is my error in?

Use @@procid to get the currently executing routine and object_name() to convert the id to a name
Hth


Can a 2008 SSIS package Connect to a Newer SQL Instance?

SSIS can connect to many different servers based on the connection type used in the SSIS package. Since even SQL Server 2016 SSIS still uses Native Client 11 (and has since 2012), you should not have any issues connecting from SSIS 2008 R2 to SQL Server 2016. SQL Server 2008 R2 uses Native Client 10 or 10.1 but has no issues connecting to SQL Server 2016 either.

In the connection manager in SSIS, you choose the way to connect to SQL Server. It could be ODBC, OLE DB, or any number of connections types. I say this to say SSIS itself does not care what version of SQL Server you are connecting to but the task you use will determine the connection type. I would assume you are using OLE DB and as such you may choose the provider in the connection string.


https://mikedietrichde.com/2017/04/26/keep-your-patch-versions-between-grid-infrastructure-and-databases-homes-in-synch/

  Can you have different PSU/BP versions between Database and GI homes?
  Yes, you can have a higher version PSU or BP in the Database home than
  in the Grid Infrastructure home managing the resources. This is
  implicitly documented in MOS Note 337737.1 – Oracle Clusterware
  (CRS/GI) – ASM – Database Version Compatibility where it says:

  “The Oracle Grid Infrastructure (GI) /Clusterware (CRS) version must
  be of the highest version down to the 4th digit in the possible
  combinations at all times.“

  Therefore you can deviate that this rule does not apply for the 5th
  digit anymore.


How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc.

For DBMS:s that support queries in check constraints you can do something like:

ALTER TABLE Student Add constraint ...
         CHECK ( (select type
                  from person p
                  where p.<key> = <key>) = 'Student' )

If you DBMS does not support this type of construction you can add a super key in Person consisting of the primary key + the type attribute. Add the type attribute to Teacher and Student, add a check constraint that guarantees the type in those "subtables", and a foreign key that includes the type attribute:

ALTER TABLE Person ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Person ADD CONSTRAINT ... UNIQUE (<key>, type_attribute);

ALTER TABLE Student ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Student ADD CONSTRAINT ... CHECK (type_attribute = 'Student')
ALTER TABLE Student ADD CONSTRAINT ...
     FOREIGN KEY (<key>, type_attribute)
     REFERENCES Person (<key>, type_attribute);

Now it is not possible to add a student as a teacher and the other way around. That there really is a student/teacher for a person with that attribute has to be guaranteed through the transaction that adds the information.


On the MySQL support page they write some about index optimization. (I assume MariaDB isn't deviating too much from this since they're somewhat similar.) Here they write that indexes are not so important on small tables.

  Indexes are less important for queries on small tables, or big tables
  where report queries process most or all of the rows. When a query
  needs to access most of the rows, reading sequentially is faster than
  working through an index. Sequential reads minimize disk seeks, even
  if not all the rows are needed for the query.

On the page about How to avoid full table scans they define the limit as "more than a few rows".

  For tables with more than a few rows, consider redesigning the query
  by adding an index for one or more of the columns tested in the WHERE
  clause.

I personally would add an index just because I always add index to the key on all tables. In your case I don't know if the index would contribute so much though.


Any way to send notifications to different Emails on success/failure of SqlServerAgent job?

You have to use tsql to do that.

in the job step after your main job step add 2 more job steps:

step 2--success email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs success !!',
    @subject = 'Copy and Purge BAKs success',
-- to send file attachments
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email.
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

step 3-- failure email

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YOUR MAIL PROFILE',
    @recipients = 'COMPANYMAIL@COMPANY.COM',
    @body = 'Copy and Purge BAKs failed !!',
    @subject = 'Copy and Purge BAKs failed',
-- to send file attachments
    @file_attachments = 'D:\logs\log.TXT',
  -- Change below if you want to include query results from a table in the email.
  -- Note that below is not in HTML
    @query = 'select column1, column2 from table1';

and then use tsql sp_send_dbmail

Note: If you want HTML email to be send out, I have a script for that to help you out.


Is configuring Database mail considered as a security threat?

In a vacuum, any activated feature is an additional attack vector (or, additional "surface area" for the system). That said, I've never read or heard of any attack or exploit using DB Mail.


What is a LSN (log sequence number)? How does xtrabackup Incremental Backup work?

LSN a.k.a. Log Sequence Number is a 8-bytes integer that acts as a version to REDO log entries(ib_logfile*). Every change to data makes an entry in the REDO log and gets a new LSN. The LSN is incremented with every change.

Every InnoDB index page (where user data are stored) has the LSN field in the header. It's an LSN that last modified the page.

So. If we know what the last LSN (let's call it lsn_full) was when we took a full backup, we know if a particular page changed since then. Because if the page's LSN (let's call it lsn_current) higher than lsn_full, the page has changed since the last backup.

Xtrabackup uses this idea to implement incremental backups. As a matter of fact you can implement both incremental and differential backups with Xtrabackup. Check out options --incremental-lsn and --incremental-basedir.

Whether use incremental backups or not depends on how confident you're about the whole process. It's easy to mess up if you don't fully understand what's going on during the backup and restoration process. (At TwinDB I have to deal with lots of cases where incremental backups were not usable on whatever reason).

I highly recommend verifying backups. The most reliable way to ensure a backup copy is usable is to restore the database from it. So I would suggest algorithm:

grab the latest copy.
restore a start MySQL instance on it.
Check some table if it contains recent data. For example, if you store transactions in the database make sure there are some not older than a day or something.


Yes, it is perfectly safe to do so as long as you don´t gzip the file which is currently written (the last file).

Also in case of a replication setup ensure that the files you are gzipping are already fetched by the slave. You can verify this by checking the output of SHOW SLAVE STATUSon the slave, look here for Master_Log_File, this will give you the logfile of the master which is currently be fetched by the slaves io-thread. Every preceding file is absolutely safe to gzip.


How join 2 table with calculation in the relation ID

WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) > 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) > 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) > 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) > 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) > 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle (parenthesis in mappings removed).
PS. None Tb_Value.ID value must be a prefix of another Tb_Value.ID value. If false then update CHARINDEX checking expression accordingly.

oh maybe becase the VAL i have is VAL1 - VAL50 , because CHARINDEX() make the result like that ? example VAL9+VAL10 and the result VAL1,VAL9,VAL10 ? – Fikri

WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID + '+' Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) > 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) > 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) > 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) > 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) > 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle


As @a_horse_with_no_name says, it's a file created with the original export utility, i.e an exp command rather than expdp. You'll need to import it with the matching original import utility, using the imp command.


This is documented here on BOL: Thread Usage By Availability Groups.  For redundancy purposes, here is the breakdown:

3 to 10 shared threads (but can increase depending on primary replica workload)
(primary replica) 1 log capture thread per primary database
(primary replica) 1 log send thread per secondary database
(secondary replica) 1 redo thread for each secondary database
(secondary replica) during a backup, holds a thread on the primary replica for the duration of the backup

Other points to consider:

There is a shared pool of threads for hadr, and this is the max number of threads except 40 (so hadr worker thread pool could take a significant amount of threads)
Idle threads (about 15 seconds of idle-ness) can be released back to the pool

Other references:

AlwaysON - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption


I finally Figure it out.
Do
On Error Resume Next 'reset Err.obj.

 'Get the Max ID +1
Set rst = Nothing
Set rst = New ADODB.Recordset 'assign memory to the recordset
SQL = "SELECT Max(ApNumber)+1 FROM PayVoucherID "
rst.Open SQL, cnn

'Check if the recordset is empty.
'Copy Recordset to the Temporary Cell
Sheets("MAX").Range("A2").CopyFromRecordset rst

'Insert the Data to Database And Check If no Errors
Sql2 = "INSERT INTO PayVoucherID(ApNumber)Values('" & Sheets("MAX").Range("A2") & "') "
cnn.Execute Sql2
Loop Until (Err.Number = 0)}


Cannot create a stored procedure in any db in one instance SQL Server 2012

Sounds like you've got a server-level DDL trigger that's failing somehow. Check for it under Server Objects -> Triggers & see if you can see why it's failing.


Here is what you can do:

INSERT ... ON DUPLICATE KEY UPDATE

You can do the following

make prev_a assume the value of a
make prev_b assume the value of b
make a assume the value of the new a
make b assume the value of the new b

Here are the statements to create sample data for this test:

use test
DROP TABLE IF EXISTS ev;
CREATE TABLE ev
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Here is the output:

mysql> use test
DROP TABLE IF EXISTS ev;
Database changed
mysql> DROP TABLE IF EXISTS ev;
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE ev
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     ev_id INT NOT NULL,
    ->     ev_loc_id INT NOT NULL,
    ->     a FLOAT DEFAULT 0.0,
    ->     b FLOAT DEFAULT 0.0,
    ->     prev_a FLOAT DEFAULT 0.0,
    ->     prev_b FLOAT DEFAULT 0.0,
    ->     PRIMARY KEY (id),
    ->     UNIQUE KEY (ev_id,ev_loc_id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,5.0,7.0),
    -> (1,3,9.0,3.0),
    -> (2,2,7.0,9.0),
    -> (2,3,3.0,9.0);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ev;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    5 |    7 |      0 |      0 |
|  2 |     1 |         3 |    9 |    3 |      0 |      0 |
|  3 |     2 |         2 |    7 |    9 |      0 |      0 |
|  4 |     2 |         3 |    3 |    9 |      0 |      0 |
+----+-------+-----------+------+------+--------+--------+
4 rows in set (0.00 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,8.0,4.0) ON DUPLICATE KEY
    -> UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    8 |    4 |      5 |      7 |
+----+-------+-----------+------+------+--------+--------+
1 row in set (0.00 sec)

Give it a Try !!!


Is an anomaly free relation necessarily normalized?

A relvar that is considered free of all update anomalies will satisfy 6NF and by implication 5NF and BCNF (other normal forms defined many years ago are no longer deemed useful).


How to optimize query with having and group

If you dont have it already, you could create a index on both fields: is_first and lead_id;

If it's a table with few record you won't see much difference, so I always recommend to check the statistics from the query.


MySQL using up all RAM and SWAP in group replication settings

innodb_log_buffer_size=800M is probably using more than the available memory (after allowing for the OS, MySQL code, etc, etc).

Use the default value.


Oracle : How to grant to a ROLE the ability to grant a ROLE to a USER?

works fine for me on 11.2:

CONN SYSTEM/<>
CREATE ROLE admin_role;
Grant create role to ADMIN_ROLE with admin option;
Grant create user to ADMIN_ROLE with admin option;
CREATE USER john IDENTIFIED BY john;
Grant admin_ROLE to john;
GRANT CONNECT, RESOURCE TO john;
CREATE USER john2 IDENTIFIED BY john2;
GRANT CONNECT, RESOURCE TO john2;

CONN john/john
CREATE ROLE test_role;
grant test_role TO john2;

DROP ROLE ADMIN_ROLE;
DROP ROLE TEST_ROLE;
DROP USER john CASCADE;
DROP USER john2 CASCADE;

Grant, executed in 10 ms

Try redoing it, you probably missed an admin option when you tried for the first time.
Drop everything.


Another way to solve this would be by using Recursive Common Table Expressions which is available since MariaDB 10.2.2. This solution does not need a numbers table but recursively iterates the result until the computation of the SUM is complete.

I made a small example which is similar to your data:

CREATE TABLE test_json (
    prices JSON NOT NULL,
    employee_id INT
);
INSERT INTO test_json (prices, employee_id) VALUES (
    '[{"PRICE":"123"},{"PRICE":"125"}]', 10060
), (
    '[{"PRICE":"5"},{"PRICE":"10"},{"PRICE":"15"},{"PRICE":"20"}]', 10070
);

Now the actual query to retrieve the result:

WITH RECURSIVE json_sum (price_sum, prices) AS (
    -- select all entries you need to calculate the sum for, initialize sum as 0
    SELECT 0 AS price_sum, JSON_EXTRACT(prices, '$[*].PRICE') AS prices FROM `test_json`
    WHERE employee_id = 10060 -- this is your original WHERE condition from your question
  UNION
    SELECT
       -- add the value of the first entry in price array to the sum
       price_sum + JSON_UNQUOTE(JSON_EXTRACT(json_sum.prices, '$[0]')) AS price_sum,
       -- remove the first entry from the price array, so the next iteration will not add it again
      JSON_REMOVE(json_sum.prices, '$[0]') AS prices
    FROM json_sum
    WHERE JSON_LENGTH(json_sum.prices) > 0
)
-- select all sums, filtering out the rows where calculation is incomplete
SELECT price_sum AS quality FROM json_sum WHERE JSON_LENGTH(json_sum.prices) = 0;


If you do have dots in your schema name, this wouldn't be very good practice as postgres expects the input to be schemaname.tablename so now its getting extra arguements.


How to paste chinese characters in psql terminal?

psql delegates the handling of input to a shared library, which is either libreadline or libedit.

But libedit used to not work reliably with multibyte character sets, as has been reported on multiple occasions, here for OSX for example.

From your comment:

  /Library/PostgreSQL/9.4/bin/psql -> libedit@1.47.0 does not work, /Library/PostgreSQL/9.4/bin/psql -> libedit@1.48.0 does not work BUT /Applications/Postgres.app/Contents/Versions/9.4/bin/psql -> libedit@3.0.0 works!

...it seems that by relinking to a newer version, you were able to solve the problem. That's useful to know.

The reason why libedit is used at all while libreadline did a better job first is that the latter is licensed under the GPL, whereas psql is BSD-licensed like the rest of PostgreSQL. A consequence is that psql cannot be shipped in compiled form with the necessary parts that call libreadline, without breaching its license. On the other hand libedit uses the BSD license, so it's OK to ship with it.

The solution for psql packagers so far has been to ship PostgreSQL with libedit and let the users relink it with libreadline if necessary.

On Linux, the Debian/Ubuntu packages even do this dynamically without the user intervening.


How can I set TIMESTAMP's default to future date?

Use a trigger:

mysql> CREATE TRIGGER dateinsert BEFORE INSERT ON testtable
    -> FOR EACH ROW
    -> SET NEW.yourdate =  DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL 10 DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testtable (blah) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtable;
+------+------------+
| blah | yourdate   |
+------+------------+
|    1 | 2016-07-25 |
+------+------------+
1 row in set (0.00 sec)

mysql>


Passwordless mysqldump via shell script in /etc/cron.daily

Solved with the help of this answer.
cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file.
The proper passwordless mysqldump command in the shell script triggered via cron should be:
mysqldump --defaults-extra-file=/path/to/.my.cnf -u [uname] db_name > db_backup.sql

Works like a charm.


I'm not sure why your copying the mdf and ldf files but I'm assuming that you want a copy of the database without breaking the log chain.

This can be done by taking a backup of the database and specifying the COPY_ONLY option which will not effect the log chain http://msdn.microsoft.com/en-us/library/ms191495.aspx


What's the most efficient way to perform a case insensitive search for a word in a text field?

In PostgreSQL, you can use Text Search:

create index on foo_table using gin ( to_tsvector('simple', body) );

select
*
from foo_table
where
  to_tsvector('simple', body) @@ plainto_tsquery('simple', 'word');


What can we do in MySQL 5.0 Replication to address bandwidth concerns?

SUGGESTION #1 : Use Distribution Masters

A Distribution Master is a mysql slave with log-bin enabled, log-slave-updates enabled and contains only tables with the BLACKHOLE Storage Engine. You can apply replicate-do-db to the Distribution Master and create binary logs at the Distribution Master that contains only the DB schema(s) you want binlogged. In this way you reduce the size of outgoing binlogs from the Distribution Master.

You can setup a Distribution Master as follows:

mysqldump your database(s) using --no-data option to generate a schema-only dump.
Load the schema-only dump to the Distribution Master.
Convert every table in the Distribution Master to the BLACKHOLE storage engine.
Setup replication to the Distribution Master from a master with real data.
Add replicate-do-db option(s) to /etc/my.cnf of the Distribution Master.

For steps 2 and 3 you could also edit the schema-only dump and replace ENGINE=MyISAM and ENGINE=InnoDB with ENGINE=BLACKHOLE and then load that edited schema-only dump into the Distribution Master.

In step 3 only, if you want to script the conversion of all MyISAM and InnoDB tables to BLACKHOLE in the Distribution Master, run the following query and output it to a text file:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=BLACKHOLE;') BlackholeConversion FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine <> 'BLACKHOLE'" > BlackholeMaker.sql

An added bonus to scripting the conversion of table to the BLACKHOLE storage engine is that MEMORY storage engine tables are converted as well. While MEMORY storage engine table do not take up disk space for data storage, it will take up memory. Converting MEMORY tables to BLACKHOLE will keep memory in the Distribution Master uncluttered.

As long as you do not send any DDL into the Distribution Master, you can transmit any DML (INSERT,UPDATE,DELETE) you so desire before letting clients replicate just the DB info they want.

I already wrote a post in another StackExchange site that discusses using a Distribution Master.

SUGGESTION #2 : Use Smaller Binary Logs and Relay Logs

If you set max_binlog_size to something ridiculously small, then binlogs can be collected and shipped out in smaller chunks. There is also a separate option to set the size of relay logs, max_relay_log_size. If max_relay_log_size = 0, it will default to whatever max_binlog_size is set to.

SUGGESTION #3 : Use Semisynchronous Replication (MySQL 5.5 only)

Setup your main database and multiple Distribution Masters as MySQL 5.5. Enable Semisynchronous Replication so that the main database can quickly ship binlogs to the Distribution Master. If ALL your slaves are Distribution Masters, you may not need Semisynchronous Replication or MySQL 5.5. If any of the slaves, other than Distribution Masters,  have real data for reporting, high availability, passive standby or backup purposes, then go with MySQL 5.5 in conjunction with Semisynchronous Replication.

SUGGESTION #4 : Use Statement-Based Binary Logging NOT Row-Based

If an SQL statement updates multiple rows in a table, Statement-Based Binary Logging (SBBL) stores only the SQL statement. The same SQL statement using Row-Based Binary Logging (RBBL) will actual record the row change for each row. This makes it obvious that transmitting SQL statements will save space on binary logs doing SBBL over RBBL.

Another problem is using RBBL in conjunction with replicate-do-db where table name has the database prepended. This cannot be good for a slave, especially for a Distribution Master. Therefore, make sure all DML does not have a a database and a period in front of any table names.


Correcting and Detecting overlapped dates with CTE

Your appear to have a couple of problems
1) you are referencing the T2.INI column in the UPDATE statement, but T2 only exists within the EXISTS statements.
2) The CONVERT statements on the date are messing up the UPDATE.

While the following may not be perfectly identical in functionality, it does actually run

;WITH CTE AS(
--GROUP BY IDHR
SELECT row_number() OVER (PARTITION BY T.IDHR ORDER BY T.ULT_ACT ASC) AS IDOR,
T.VALIDO,
T.ID,
T.INI AS INI,
T.FIN AS FIN,
T.ULT_ACT AS ULT_ACT,
T2.fin as T2FIN
FROM tstOver T
inner join tstOver T2
    on T.ID <> T2.ID
    and (T2.INI BETWEEN T.INI AND T.FIN) OR
    T2.INI = T.FIN
    --WHERE EXISTS
    --    (
    --        --CRITERIA FOR OVERLAPS
    --        SELECT 1 FROM tstOver T2
    --        WHERE T.ID <> T2.ID AND
    --            (T2.INI BETWEEN T.INI AND T.FIN) OR
    --            T2.INI = T.FIN
    --    )  )
--SETTING VALIDO TO 0 WHEN THEY OVERLAP AND MAKING RECORDS SEQUENTIAL DATE-WISE
UPDATE CTE SET VALIDO = 0, FIN = (CTE.T2FIN - 1) --4406 BECAUSE T2.INI IT'S DERIVED FROM THE SUBQUERY
  where IDOR > 1


While Azure SQL Database (not the new Managed Instance) does not support custom SQLCLR Assemblies (i.e. the "CLR enabled" server-level configuration option), CLR is still used internally for the following features:

CLR datatypes:

Geometry
Geography
Hierarchyid

Built-in functions:

introduced in SQL Server 2012:

FORMAT
PARSE
TRY_PARSE

introduced in SQL Server 2016:

AT TIME ZONE
COMPRESS
DECOMPRESS
sys.time_zone_info

maybe others

SSIS (Fuzzy Lookup / sp_FuzzyLookupTableMaintenanceInvoke, etc)
Change Data Capture (CDC)
Replication
Master Data Services
Policy Based Managemen (PBM; originally named "Dynamic Management Framework (DMF)" )
External Tables (including External Data Sources and possibly External File Formats): This functionality is new as of SQL Server 2016, and this functionality using CLR internally has been mentioned in other answers here by Joe and Henrik. Both Joe and Henrik indicate being told by Microsoft that External Tables rely upon CLR, and while I have not been able to confirm this directly (by seeing the system App Domain get created when using any of this functionality), I was at least able to confirm that when "Lightweight Pooling" mode is enabled, all 3 of these components fail with the following error:

  Msg 7432, Level 16, State 0, Line XXXXX
  Heterogeneous queries and use of OLEDB providers are not supported in fiber mode.

maybe others

Of course, this does not point to what would be hogging memory. But it should help identify the affected area(s).


SQL Server Agent Job Next Run DateTime Stuck In The Past

This happened because you did a mass disable and then re-enable by updating the enabled flag in msdb.dbo.sysjobs after a massive outage; instead of doing things the right way and using the stored procedure,
sp_update_job.

All of the jobs that were enabled one-by-one via the GUI worked fine, it was just those mass enabled that were a problem. Doing a quick disable then enable resolved all the issues.

See link for further info:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Syntax:

EXEC dbo.sp_update_job  @job_name = N'[Your Job's Name HERE]',
@enabled = 0 --- This disables, set to 1 to enable;

Don't update system tables directly next time.


Store the tenant_id first. When you do this you can enable index key compression.

See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts.

In your case, you can do it like this:

create unique index mytable_idx on mytable(tenant_id,id) compress 1;

alter table mytable add constraint mytable_pk primary key(tenant_id, id);


SQL Server Audit Specification - filter DML by all but one user

You can't do this at the audit specification level, but you can do it at the server audit level. So, if this audit does other things too, and you don't want to filter out those events by login, you will need to create a separate audit. Assuming you want to filter out all activity of any kind by this service account, you can do this:

ALTER SERVER AUDIT OurAudit WITH (STATE = OFF);
GO
ALTER SERVER AUDIT OurAudit WHERE server_principal_name <> N'service account name';
GO
ALTER SERVER AUDIT OurAudit WITH (STATE = ON);
GO

Depending on how they connect, authenticate, perhaps impersonate, and how the login is mapped to the database user, you may need to experiment with this and filter against database_principal_name or session_server_principal_name instead of server_principal_name. I just tested this on SQL Server 2012 and it seems to work as you would expect. I don't believe it will work on 2008 or 2008 R2 (not relevant for you, but for future readers).

I still think that filtering a large portion of your activity seems suspect and not really in the spirit of auditing. You must have pretty laissez-faire auditors if they just trust that you protect your passwords for service accounts. Someone must know them, right? An auditor's job is to not inherently trust anyone.


Is this a standard way to design a database?

This isn't that weird a practice.  Those are called GUIDs, or Globally Unique IDs.  The idea is that, given a GUID, you can tell exactly what piece of data the id belongs to because it will be unique everywhere.  GUIDs are best used when you will be merging different sources of similar data; for example inventory for different stores.

I would do some research and find out as to why this is a common practice in your environment.  Maybe it's needed, maybe it's not.


There is no "perfect" solution to your requirements, but see if MariaDB with Galera is "good enough":

Have 4 or more geographically separated nodes.
If a "quorum" of the nodes (more than 50%) stay connected to each other, they stay in sync -- usually within seconds.
If, say, 3 nodes are talking to each other, but you are connecting to the 4th node (which can't see the other 3), you won't be able to write.  Having a proxy try to reach a writable node will sometimes solve this situation.
If there are multiple network problems, the "cluster" will fix itself after the network is healed.  Meanwhile you can't write.

NDB Cluster replicates via "eventual consistency".  This allows different nodes to be written independently, as you have with a flakey network.


What are the advantages and inconveniences of generalization or specialization of SQL tables?

Solution A is a bad solution if you have to add columns in future.
Using alter table in production environment is discouraged. Can hold the
database because of locks needed. This could not be a problem, but it really depend on the number of rows and concurrent transactions.
On the other hand is very simple to retrieve data, is a sort of denormalization,
so no join imply fast response time.

Solutions B and C are two similar desings. I dont like them. You wont be able to have cumulative
data (count(1), group by..) in a single statement. You still have the problem above, if you need to
add columns. But now this problem is repeated 3 o 4 times, compared to solution A. Not good.

There is a 4th solution, using a table for key/value. In this scenario you have a big table whith
only the common columns, and a second table whith the following fields:

id / bigtable_id (references bigtable primary key) / key / value

Key column will store fields which apply only to certain type of tire coupled whith its value.
For example there could be rows like:

id ==> 1 /bigtable_id ==> whatever needed /key ==> grade   /value ==> A

id ==> 2 /bigtable_id ==> whatever needed /key ==> g-value /value ==> 71

There is an one-to-many relationship between tables.
This is the solution I have seen most of the time. Is simple to manage, no need to alter table:
if you need a new field just add new record/records.

In my opinion the fact that mix label is uncommon, is a secondary issue in design. If the problem
is the number of rows, you can use partitions and subpartitions, but I prefer have the DB desing in
order. Of course this is only my way to see order   ;)

my 2 cent


how to design a good database design which suits for both RDMS and noSQL

The database designs for relational and document stores will be entirely different. You will not be able to take one and simply implement it in the other.

What will endure, however, is the data model - the logical representation of the entity types, connections between them, cardinalities, constraints, attributes, domains and rules governing them. The data model represents what your customers know and how they act. The database is one physical representation of this, limited by the product chosen and hardware. Getting the model right and keeping it up to date will aid current development and smooth any future transition.

The data model is a logical construct. It should not, in theory, be affected in any way by the features of the chosen storage product. From one data model several database designs can be derived. For a relational storage product the translation is quite direct. An entity type from the data model becomes a table in the database. A relationship from the data model becomes a foreign key in the database. Take the example of an order processing system. The entity types "Order" and "OrderLine" become the tables Orders and OrderLines, with OrderLines having a foreign key column of OrderNumber.

For a document store the translation may be more complex. The designer has more choices to make. Order and OrderLine may be mapped to different collections, they may be mapped to different documents within the same collection, or order lines may become sub-documents nested within the order document. The relationship between the logical entity types Order and OrderLine can be implemented either by copying the order's number into the order line's document, having an array of the order line's IDs in the order document or by nesting the order lines as sub-documents within the order document. Which to choose will be determined by the application's requirements. Whatever the physical implementation the logical analysis in the data model remains constant: one Order,  many order lines.

The next obvious point is to be scrupulous in separating to data access tier from the others. This way future rework will be focused on that area. Having good test scripts will pay off now and will be doubly valuable after the rewrite, to speed regression testing.

Finally, the chance of you migrating is slim. I do not doubt you will be successful. I do doubt you can guess now what your business and technical environment will look like several years from now. Everthing I have read by successful software companies says they rewrite the product three times before they get it "right." Use one of those re-writes to change persistence product and concentrate now on delivery and customer service.


The WITH keyword defines the operator which is used to test for "equality" between two rows.

A regular unique index is just a special case of an exclusion constraint. If you were to define a "normal" unique constraint using an exclusion constraint, you would use = as the operator, e.g: exclude using btree (id with =) instead of unique (id)

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01')

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01')

will return false as there is no overlap.


Database level health check did not trigger failover on losing a disk

On the primary node, I took a drive offline that contained one of the data files of the DB that is in the AG. I ran a select * from a table which read from the missing disk [...]

Since you're on 2016, the database level health checks are checking that the database is online (which taking secondary file offline won't change) and that we can write to the transaction log. Since both of these are true, your test passes. That's how it works in 2016.

  Any idea where else I should be looking for why this did not fail over?

Yes, see above. This was changed to encompass more in 2017.


Follow the link given.  You will get details that which versions are compatible with the different edition of SQL Server. Hope it will help you.


Are most of the NoSQL databases key/value stores technically?

MongoDB is not a key-value store; its documents are not just strings in the end. MongoDB stores its data in a format called BSON (Binary JSON), which allows for complex structures including various data types, strings being just one of them. See the BSON Spec for more details, for example:

Examples
The following are some example documents (in JavaScript / Python style syntax) and their corresponding BSON representations.
Example 1:  {"hello": "world"}

\x16\x00\x00\x00                   // total document size
\x02                               // 0x02 = type String
hello\x00                          // field name
\x06\x00\x00\x00world\x00          // field value
\x00                               // 0x00 = type EOO ('end of object')

Example 1:  {"BSON": ["awesome", 5.05, 1986]}

\x31\x00\x00\x00
\x04BSON\x00
\x26\x00\x00\x00
\x02\x30\x00\x08\x00\x00\x00awesome\x00
\x01\x31\x00\x33\x33\x33\x33\x33\x33\x14\x40
\x10\x32\x00\xc2\x07\x00\x00
\x00
\x00


Are JOINs on non-indexed columns faster than sub-queries?

In a query such as this:

SELECT *
FROM
  some_table
  JOIN (SELECT foo, col1 FROM bar) s_sub
  ON (some_table.col1 = s_sub.col1)

MySQL is able to use an index on some_table.col1. So that it starts with the derived table (full scan), then moves on to some_table using index.

This means it can utilize an index. So a subquery does not altogether negates use of an index.

If no index exists on any relevant column, obviously hard work is done (full scan, join buffer) to make the JOIN.


SQL Partition Function without actual values

You cannot create a dynamic VALUES clause in a CREATE PARTITION FUNCTION statement.  An alternative is to use the ALTER PARTITION FUNCTION statement to add a new range to the function.

I've encapsulated the required statements into a stored procedure:

IF OBJECT_ID(N'dbo.partition_function_add_date_range', N'P') IS NOT NULL
DROP PROCEDURE dbo.partition_function_add_date_range;
GO
CREATE PROCEDURE dbo.partition_function_add_date_range
(
      @function_name sysname
    , @range_date datetime
    , @scheme_name sysname
    , @next_filegroup sysname
)
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1
        FROM sys.partition_functions pf
        INNER JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
        WHERE pf.name = @function_name
            AND prv.value = @range_date
        )
    BEGIN
        DECLARE @cmd nvarchar(max);
        SET @cmd = N'ALTER PARTITION SCHEME ' + QUOTENAME(@scheme_name) + N' NEXT USED ' + QUOTENAME(@next_filegroup) + N';';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
        SET @cmd = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@function_name) + N'() SPLIT RANGE (''' + CONVERT(nvarchar(max), @range_date, 120) + N''');';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
    END
END
GO

An example of how to call this stored procedure:

EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF '
    , @range_date = '2018-01-01 00:00:00'
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

This could be added to a SQL Server Agent Job, scheduled to run once a year, prior to rows being inserted into the new fiscal year.  The job step command would run something like this, which will automatically add the next year to the partition function, and any associated tables.

DECLARE @daterange datetime;
SET @daterange = CONVERT(varchar(30), DATEPART(YEAR, GETDATE()) + 1) + '-01-01 00:00:00';
EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF'
    , @range_date = @daterange
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

It's important to realize partition function modifications will affect ALL tables that use the partition function.


How can I set a random timestamp for a row based on a columns value in mysql?

I like to check out a computed column as part of a read only select first just to make sure it shows the values I'd expect.

SELECT *
    ,UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800)) as ended_at_new
        from streams
            WHERE ended_at IS NULL

If you see the correct variation and values you were expecting to see then your update query will work fine.

For extra protection you could run the update in a transaction:

begin tran;

UPDATE streams SET ended_at = FROM_UNIXTIME(
        UNIX_TIMESTAMP(created_at) + FLOOR(0 + (RAND() * 28800))
    )
    WHERE ended_at IS NULL

select count(ended_at) where ended_at IS NULL #should see 0

select * from streams limit 0, 1000; #add better filters to correctly identify records you want to check

rollback tran;

If you are happy with what you saw, change the rollback to commit and run it again.


Running SQL job: The login is from an untrusted domain and cannot be used with Windows authentication

The job is executed as the SQL Agent account, unless you have configured a proxy.  The owner of the job is irrelevant as long as the SQL Agent account can verify that the owner is valid (still exists in AD/SQL).

Verify which account is running the job, either the SQL Agent account or the Proxy account. Then verify that account has permissions into the linked server.  Or create a proxy account for this job.


SELECT permission denied trying to import data into Excel

After reviewing your error. I tried to setup in my TEST environment and I have received an error.

If you setup connection from Excel and after selecting specific tables to test go to connection properties.

You will be able yo see connection string. Try to verify it with your query and see if it is changing any parameters in connection string from your query.

Try it and let us know any progress in it.


The table is probably ENGINE=InnoDB.  The default is probably to commit a transaction after every INSERT.  You seem to be inserting one row at a time.  Is the input file a list of INSERTs?  Where did it come from?  If it came from mysqldump, re run the dump with the option to do multi-row inserts.  If the data is just text, arrange to use LOAD DATA.

Can you connect to mysql from another server?  That way you won't be limited to phpmyadmin.  Etc.

Check the Arabic.  Non-English characters cause trouble for a lot of people.  If they are mangled, you may need to reload after changing something.


List of databases which a user can access in SQL 2008 R2

Try with this

USE master;
GO
EXECUTE AS LOGIN = N'LoginName';
GO
SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1
GO
REVERT;


What happens to a Trigger if a table is dropped and rename and recreated with same name

The SQL:2008, draft version, has this to say in Part 11, Schemata :

(in the description of the INFORMATION_SCHEMA table that describes triggers)

...

CONSTRAINT TRIGGERS_FOREIGN_KEY_SCHEMATA
FOREIGN KEY ( TRIGGER_CATALOG, TRIGGER_SCHEMA )
REFERENCES SCHEMATA,

CONSTRAINT TRIGGERS_REFERENCES_TABLES
CHECK ( EVENT_OBJECT_CATALOG <>
ANY ( SELECT CATALOG_NAME
FROM SCHEMATA )
OR
( EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE ) IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM TABLES ) )

...

The first constraint merely states that a trigger cannot exist if the schema it is in does not itself exist,

The second one states that the EVENT_OBJECT_TABLE (the table from which the trigger is fired) must exist in the catalog and schema, or else if that other condition evaluates to true.

So dropping the table and leaving the trigger can only possibly be a valid option if "that other condition" evaluates to true [or unknown, but let's not get into that].  Let's take a look.

EVENT_OBJECT_CATALOG <> ANY ( SELECT CATALOG_NAME FROM SCHEMATA )

is a <quantified comparison predicate> as per chpt 8.9, pg 421 [Part 2, foundations] in my draft version.  The relevant part of the spec here is :

c) If the implied <comparison predicate> is True for at least one row RT in T, then “R <comp op> <some> T” is True.
d) If T is empty or if the implied <comparison predicate> is False for every row RT in T, then “R <comp op> <some> T” is False.

So, the condition becomes true if there exists at least one row in SELECT CATALOG_NAME FROM SCHEMATA whose catalog name differs from the catalog that the triggering table is in.

So what all of this seems to say is :

If you have only one single CATALOG_NAME in your database, then dropping a table while leaving a trigger that is fired off of it, is invalid.  Either the DROP should be rejected or it should something like CASCADE DELETE its corresponding triggers.

If you have some CATALOG_NAME in your database that is different from the CATALOG_NAME that the triggering table is in, dropping a table while leaving the trigger seems perfectly OK (oddly enough, I must say).

Final disclaimer : Since this is a rule that derives from Part 11 of the SQL standard, of course it only applies to products that claim to support Part 11 of the standard.


sqlcmd gives no sign of failure yet doesn't work

It sounds like your transaction is rolling back (including any rows written to your ErrorLog table).  You haven't posted any TSQL source, so I'm not sure if you have the TRY/CATCH within a transaction or the transaction is within the TRY/CATCH.

I'd suggest following the guidance in this post - (TSQL Try / Catch within Transaction or vice versa?).

  Only open a transaction once you are inside the TRY block and just
  before the actual statement, and commit it straightaway. Do not wait
  for your control to go to the end of the batch to commit your
  transactions.

  If something goes wrong while you are in the TRY block and you have
  opened a transaction, the control will jump to the CATCH block. Simply
  rollback your transaction there and do other error handling as
  required, which might include writing rows to your ERRORLOG table.

Here's the code sample from the post above.

BEGIN TRY

  BEGIN TRANSACTION SCHEDULEDELETE
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
    DELETE   -- delete commands full SQL cut out
 COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
END TRY

BEGIN CATCH
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH


The Target Principal Name Is Incorrect. Cannot Generate SSPI Context (SQL or AD Issue)?

Use setspn -X to look for duplicate SPNs for the SQL Server in question.

Remove any duplicate SPNs that don't line up the SQL Server Service account in question.

Service Principal Names for SQL Server take the form of:

MSSQLSvc/server.domain:port
MSSQLSvc/server:port

Assuming your SQL Server is using the default TCP port, 1433, I would expect you need the following servers:

MSSQLSvc/MASSQL.abc.com:1433
MSSQLSvc/MASSQL:1433

You can create those SPNs using the following command:

SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAIN\Account

Where DOMAIN\Account is the name of the service account used by SQL Server.

If your SQL Server uses a named instance, the SETSPN commands look like:

SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL.abc.com:INSTANCENAME DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:INSTANCENAME DOMAIN\Account


I would start this test FIRST by taking a copy_only backup, see here for details, of DB1 on server 1.

BACKUP DATABASE [DB1] TO DISK = N'<backup location>' WITH COPY_ONLY

This will let you get back to ground zero at the end or if something get messed up. Then you can turn off mirroring using the command

ALTER DATABASE DB1 SET PARTNER OFF;

After mirroring is turned off I would backup the tail of the log, see here for details, using the command

BACKUP LOG DB1 TO DISK = N'<backup location>' WITH NORECOVERY;

and then restore that backup to DB1 with recovery. That will bring the DR site online. At that point you can then setup mirroring from server 3 to server 4.

*Note: Script this out as much as possible. You do not want to be trying to do this manually if this was a real DR situation.

Also, if you have log shipping on server 1 and it is mirroring to server 2 you should setup all the log shipping jobs on server 2 so if you have a failover you can quickly enable the jobs and start log shipping from server 2 to server 3.

At the end of your testing you can restore the copy_only backup to server 1 reset up mirroring to server 2 and log shipping to server 3.

Hope that helps.


I'm mostly familiar with MySQL and MS SQL, so I can't speak to the performance of PostgreSQL on multi-column indexes.  However, my guess is that as long as the number of users is small (< 100,000), a single-column index on user would work well.

Regarding the second portion of your question:

  I also thought about separating users and groups by putting them in
  different tables but as both numbers can grow indefinitely, I couldn't
  think of a way to do that without having an indefinite number of
  tables.

It sounds like the user and group values are repeated in your table. If this is true, the usual thing to do would be to 'normalize' the data.  In this case, you could move the relationship between users and groups to a different table.

You haven't specified whether the user or the group is the most tied to the row in your current table, though I'll guess it's probably the user.

A table layout with users, groups, and data would usually look like:

data_table
data_id    data_col_1    data_col_2    user_id
1          1.2374        pancakes            2
2          8.2978        strawberries        1
3          78.001        hashbrowns          1
...

user_table
user_id    user_name     group_id
1          Travis        1
2          Melanie       1
3          John          3
...

group_table
group_id   group_name
1          sales
2          marketing
3          engineering
...

This assumes the property that you mentioned, where one user is a member of only one group.  If that property changed to allow users to join more than one group, you would delete the group_id column in the users table and create a new 'mapping' table with two columns: user_id and group_id

If your data is organized this way, then user names and group membership can change without modifying all matching rows in the data table.

Here is a more in-depth explanation of DB normalization:
https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/


Need to deny DML access through SSMS using web app credentials

The behavior of a role can't be defined by the application being used.

There are a couple of approaches that might work, but both will require code changes:

Enforce all data manipulation to go through stored procedures (you can grant execute to the app credentials and deny direct insert/update/delete). The app would call the stored procedures. To ensure users aren't calling the procedures directly in SSMS (or from their own VBScripts or other local code), the procedures could check APP_NAME() to decide whether or not to proceed.
If you can't enforce all data manipulation through stored procedures, then you could have INSTEAD OF triggers on all (or the most important) tables, which would perform a similar check against APP_NAME(), and then either perform the operation or not.

Keep in mind that the application name can be spoofed, so anything that relies on the output of APP_NAME() is vulnerable to tampering.

A more foolproof way would be to create separate logins altogether - better guard the credentials for the app, which you trust, from your developers, who you don't. (And as TomV mentions below, use Windows authentication for the app, so developers will have a much harder time pretending to act on its behalf.)


Which constraints can the PostgreSQL planner make use of?

For the most part, it does not use them.

The default setting of constraint_exclusion is 'partition', meaning it only tries to use NOT NULL or CHECK constraints to rule out partitions of partitioned tables.  If you change this to 'on' instead, then it will try to prove cases where a query over a non-partitioned table can't return any rows because the query condition conflicts with the NOT NULL or CHECK constraint.  These proof attempts are not exhaustive, however.  If you are wondering about a specific case, you should try it and see.

alter table pgbench_accounts add constraint foobar check (filler is not null);
explain select count(*) from pgbench_accounts where filler is null;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=751000.44..751000.45 rows=1 width=8)
   ->  Gather  (cost=1000.00..751000.43 rows=1 width=0)
         Workers Planned: 2
         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..750000.33 rows=1 width=0)
               Filter: (filler IS NULL)

set constraint_exclusion TO on;
explain select count(*) from pgbench_accounts where filler is null;
                   QUERY PLAN
------------------------------------------------
 Aggregate  (cost=0.00..0.01 rows=1 width=8)
   ->  Result  (cost=0.00..0.00 rows=0 width=0)
         One-Time Filter: false

If you are left joining to a table using a unique or primary key, and are not actually using any columns from that table, then it will just not do the join because it will not change the number of rows returned per each row of the other table (it will always be one), and the contents of those unreferenced columns don't matter.  In theory it could do the same thing with an inner join if there is a foreign key constraint, but empirically it does not do this.

It can use foreign key constraints to affect the selectivity estimates, however.


Returning a json array slice in PostgreSQL

In PostgreSQL 12, you can obtain JSONB array slice using the jsonb_path_query_array function:

SELECT jsonb_path_query_array('["a","b","c","d","e","f"]', '$[2 to 4]');
 jsonb_path_query_array
------------------------
 ["c", "d", "e"]
(1 row)


PostgreSQL function to insert to table from another table has different structure

You don't actually need a function for this. You can do the same thing you're doing with your sample data with CTAS. I wouldn't use a function anyway. But, if you are here is how you do with a SQL function.

CREATE FUNCTION get_info(year int, quarter int[])
RETURNS int AS $$

  CREATE TEMPORARY TABLE tbl_temp AS
  SELECT *
  FROM foo
  WHERE year = get_info.year
    AND quarter = ANY(get_info.quarter);

  SELECT 0;

$$ LANGUAGE SQL;

Then just use

SELECT get_info(2015, '{1}');
SELECT get_info(2015, '{1,3,4}');

Etc.

Now, if you insist on the syntax given you can do that too.. We'll modify it slightly. PostgreSQL has bools. You should never make use 1 as true, when you actually have true.

CREATE FUNCTION get_info(
  year int,
  q1 bool DEFAULT false,
  q2 bool DEFAULT false,
  q3 bool DEFAULT false,
  q4 bool DEFAULT false
)
RETURNS int AS $$

  CREATE TEMPORARY TABLE tbl_temp AS
  SELECT *
  FROM foo
  WHERE year = get_info.year
    AND (
      (q1 AND quarter = 1)
      OR (q2 AND quarter = 2)
      OR (q3 AND quarter = 3)
      OR (q4 AND quarter = 4)
    );
  SELECT 0;

$$ LANGUAGE SQL;

Using plpgsql for this is way over kill.


'ambiguous column name' error refers to database, table, and column guuu

You have FROM sourcedb.answers and INNER JOIN sourcedb.answers so you are introducing the same table twice without giving either an alias.

Some of the join conditions appear probably wrong as well.

 FROM   sourcedb.answers
               INNER JOIN sourcedb.questions_tests
                 ON sourcedb.questions_tests.testskey = '121212eczema'

doesn't reference sourcedb.answers anywhere in the ON.

INNER JOIN sourcedb.answers_questions
                 ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey

Doesn't reference sourcedb.answers_questions in the ON

I presume you actually need something like

SELECT answers.*
FROM   sourcedb.answers
       INNER JOIN sourcedb.answers_questions
         ON sourcedb.answers.answerskey = sourcedb.answers_questions.questionskey
       INNER JOIN sourcedb.questions_tests
         ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey
WHERE  sourcedb.questions_tests.testskey = '121212eczema'


SP2-0750: You may need to set ORACLE_HOME

I restarted my PC and tried setting ORACLE HOME from Command Prompt as SET ORACLE_HOME=... and it worked

Further more, i did following two things

1-ORACLE_HOME should not contain anything after the path i.e. no \, ;,,

2-I added an environment variable named LANG with value American_America.we8iso8859p15


getting "table is full" error even if the table size is much smaller than tablespace

First of all, are you creating your tables with

CREATE TABLE table_name TABLESPACE hhmefep_tbs STORAGE DISK ENGINE NDBCLUSTER;

or just

CREATE TABLE table_name ENGINE=NDBCLUSTER;

First one is to create table to stores table data on disk, the second, which is default mysql cluster theme and the reason why mysql cluster exist, stores data in memory.

So if you store your data in memory, you should run the command on management node, ( use ndb_mgm command to enter ndb_mgm console)

ALL REPORT MEMORY USAGE

and check if your memory usage is full or not.

Also note that when you use disk tablespaces on mysql, a varchar(100) variable will reserve all the space like you have 100 characters in all rows of the column.

This will be more accurate for you to use to check if TS is full or not.

select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra
from information_schema.FILES
where TABLESPACE_NAME='hhmefep_tbs'
and   FILE_TYPE="DATAFILE";


Keepalived vIP as Galera wsrep_cluster_address

You can put multiple addresses into wsrep_cluster_address, e.g. gcomm://10.1.1.1,10.1.1.2,10.1.1.3, and Galera Cluster will detect it smartly.

I has used Percona XtraDB Cluster (which is based on Galera Cluster too) to run serveral (> 10) clusters in production environment:

One has been running with 2 DB servers and 1 garbd server, and we put all three servers into wsrep_cluster_address.
Another was 7 database servers, and we just put first three servers (which were in different racks) into wsrep_cluster_address.

There were power failures (all three servers), hardware failures, software bugs on these clusters in four years, and they worked pretty well.


Are C++ vectors in a user-defined aggregate function a POD for Postgres?

Yes, they are, because they do not appear in plain C.


Uninstalling SQL Server 2012, No option to select instance

When you deinstall SQL Server you have to select the correct program in the "Progams & features" windows. (appwiz.cpl)

  Hint: It should be the program with no size.

Start the program and you should be guided through the de-installation routine.

If you don't see any option to de-install an instance, then you might not be running the correct program version, or you might not be on the server you are trying to de-install the SQL Server instance from.


Impact of shrinking ldf file after restoring full backup

Is there any side effect for this ?

No, not really. The worst thing that could happen is the log file grows again, given this is a dev server that's not a problem. Worst case is the log takes up all available space and your db goes into read-only... which is easily rectifiable and downtime is fine as it's a dev server.

Shrink away.


Wait for 5.7 where generated fields are supported

mysql> CREATE TABLE sales (
    -> name VARCHAR(20),
    -> price_eur DOUBLE,
    -> amount INT,
    -> total_eur DOUBLE AS (price_eur * amount),
    -> total_usd DOUBLE AS (total_eur * xrate),
    -> xrate DOUBLE);
Query OK, 0 rows affected (0,16 sec)

http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

Or use MariaDB, where virtual/computed columns are working now. These are a beta feature in MySQL 5.7.


Simple count id in MySql table is taking to long

For 2, your COUNT(id) may be checking each value of id in the range to see if it is NULL (the column is marked NOT NULL but I don't know if mySQL's query planner will take notice of this) which is an extra lookup operation because id isn't included in the timedate index. Try use COUNT(*) instead and see if that helps any.

For 1, you are partitioning by a function rather than a direct column, so it isn't going to be able to perform simple partition filtering when querying base on just the column value. Even if you partition based on the column value directly you should n't expect simple partitioning to produce faster results unless you are spreading the data over multiple devices (or even then) - partitioning is more of a management feature than a performance one. You need to provide more detail before we can help more there: what is the full definition of the partitioned table and its indexes? How much slower are you finding it?

For both, you should give the output of EXPLAIN <query> so we can see the query plan that is produced.

(edit the extra information into your question, rather than as comments in response to this answer, otherwise some people would might be able to help may not see it)


SQL beginner, need help with WHERE syntax error

Strings are denoted with Single Quotes.

WHERE titles.price > '13'

If your price column is an int/decimal/float (or any numeric) datatype, you don't have to use a string.

WHERE titles.price > 13

Probably better to use the second WHERE, as it is comparing the NUMERIC values, rather than the string values.  Even if the price column IS a string, it will CONVERT the value to a number in order to compare it against the 13.  It doesn't perform as well, but it is more accurate vs. comparing strings.

Also, your WHERE goes before your ORDER BY.

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

Edit: the code below explains the difference between comparing numbers implicitly and comparing numbers as strings.  The commented WHERE clause yields different results due to the fact that it CONVERTS the price (StringNumber) column to a number before it compares it.

WITH CTE_NumberStrings
AS
(SELECT '1' AS StringNumber
 UNION SELECT '2'
 UNION SELECT '3'
 UNION SELECT '12'
 UNION SELECT '23'
 )
 SELECT StringNumber
 FROM CTE_NumberStrings
 WHERE StringNumber > '13'
 --WHERE StringNumber > 13


What are the main disadvantages of adding relationship between tables at later date?

is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"?

Short answer: No.
Very little you can do to your database is "deadly".
That said, changing Primary and Foreign keys is a pain in most DBMSs and should be avoided, if possible.
Also, you'll need indexes to support all of your new key fields (and the foreign key fields that reference them) and building all of those indexes is likely to take some time and may cause contention problems with a running application, so you'll probably want to do this in a Maintenance Window when you can keep everyone/everything else safely out of the way.

I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.

I'd say that was a Bad Idea.
If you have a Natural, Primary key already, then I'd recommend using it.
Why duplicate the effort to reinvent your own, surrogate key?  Plus, you have to create extra, unique Indexes on the Natural key anyway, which you get "for free" with the Primary Key.


Will my database index be more performant with a smaller varchar size?

If some of the values are 21 characters, dropping to 17 will lose data.  You probably don't want to do that.

Be conservative -- what will future data be like?  Maybe 22 characters?  Drop the length to, say, 30.

No space will be saved by changing the max length -- in the "data" or in the "index".  Well, not because of the change.  However, most ALTERs include some degree of rebuilding the table.  This is likely to squeeze out some, not all, of the freed or wasted space.  To see what I mean,

SHOW TABLE STATUS LIKE 't';   -- current size
OPTIMIZE TABLE t;
SHOW TABLE STATUS LIKE 't';   -- defragmented size
ALTER TABLE t MODIFY COLUMN c VARCHAR(30)...;`
SHOW TABLE STATUS LIKE 't';   -- probably no change to Data_length or Index_length

Performance?  In a complex SELECT, where a temporary table needs to be built, VARCHARs turn into CHARs temporarily.  For utf8mb4, that is 4*255 bytes versus 4*30.  This leads to bulkiness.  If the temp table is too big for RAM, it will spill to disk.  Disk is slower, even SSDs.


Dynamic SQL query - how do I add an int to the code?

George's answer does solve your problem, but it leaves you wide open to SQL injection attacks.

While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the equivalent of an NVARCHAR 128, and you can jam a lot of extra code in there.

To make your code totally safe, you'd want to do this:

DECLARE @stringsvar NVARCHAR(1000) = '';
DECLARE @Emp_id INT = 1;
DECLARE @strvar sysname = N'Employee_test';

SET @stringsvar = ( N'select * from '
                    + QUOTENAME(@strvar)
                    + N' where emp_id'
                    + N' = @iEmp_id' );

PRINT @stringsvar;
EXEC sys.sp_executesql @stringsvar,
                       N'@iEmp_id INT',
                       @iEmp_id = @Emp_id;

Using sp_executesql to issue parameterized dynamic SQL, and quotename to make the table name non-executable code is a much safer choice.

For a little more reference, I'd suggest heading over here: The Curse and Blessings of Dynamic SQL


Proving the usefulness of a specific index

You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. The questions I would have include:

There were 124,000 updates but did they take 10 times longer than they used to?
Did they block users?
Is there more activity because people can do more because it's faster, or just a natural increase in number of queries?

Take a backup. Restore on a dev server. Replay a trace with and without the indexes, and compare (note that the absolute results may differ based on hardware/network etc., and you won't get true effects of concurrency, but the relative differences should be helpful). You can compare a variety of things:

time to run the whole workload
perfmon stuff throughout workload (e.g. it was faster, but did it peg CPU? it was slower, but did it use more CPU too?)
snapshots of sys.dm_os_wait_stats before and after each run
snapshots of sys.dm_exec_query_stats/sys.dm_exec_procedure_stats before and after each run

Make sure you run the workload a couple of times first to prime the cache (because your production system will very rarely be operating on a cold cache).

If you haven't taken a trace of a fairly representative workload, then that's what you need to take the time to do. I can't think of any "fast" ways to prove the impact of these indexes on your actual workload.


Deleting rows from table where values less than an aggregate value

You can select the rows to keep using a left join - http://sqlfiddle.com/#!9/3a677/3

select *
from media_views m1
left join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views)
where m2.companyid is null;

And you can delete the other ones using similar a inner join - http://sqlfiddle.com/#!9/d5d36/2

delete m1 from media_views m1
join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views);

And do not forget to modify the primary key after deleting them so it is not possible anymore.


Three update query vs single update query performance

The first single update reads and writes every row from the table. The second and third then re-read and re-write a sub-set of those rows. Look at the Actual Number of Rows. When the three statements are combined into one, the optimizer figures that if it has to read everything to satisfy the first change then it can piggy-back off that for the second and third change.

Have a look at the XML version of the query plans, specifically the <ComputeScalar> operators and <ScalarOperator ScalarString=""> parts. In the original plan you'll see each is relatively simple and maps very closely to the SQL. For the all-in-one plan it's a monster. This is the optimizer re-writing the SQL into a logically equivalent form. A plan works1 by passing each row through the operators once. The optimizer's doing all the work it has to do to satisfy all three changes as that row passes through one time.

I'd expect the second query to be faster because the data is only read and written once whereas it is touched three times in the first.

As the second query has no predicates (no WHERE clause) the optimizer has no choice but read every single row and process it. I'm surprised the second form takes longer than the first. Are both starting from clean buffers? Is there other work happening on the system? Since it's reading and writing to a temp table the IO is happening in tempdb. Is there file growth or somesuch happening?

By one measure you have achieved your desired outcome. You say you want to make changes "so that the IO can be reduced." The all-in-one does less IO than the three separate statements do in total. I suspect what you really want, however, is reduced elapsed time, and this is obviously not happening.

1 more or less, lots of details omitted.

I ran your routine to generate test data then ran the three single-update statements and the all-in-one statement. Although there are some differences (no clustered index, no parallelism) I get more-or-less the same results. Specifically, the plans are about the same shape and the three individual queries complete in about two seconds and the one big query in about thirty to thirty five seconds.

I set

set nocount off;
set statistics io on;
set statistics time on;

With the plan in cache and the data in memory I get:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 1422 ms,  elapsed time = 1417 ms.

(242906 row(s) affected)

Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 337 ms.

(0 row(s) affected)

Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 734 ms,  elapsed time = 747 ms.

(0 row(s) affected)

I've removed some bits that aren't relevant. Since physical reads is zero for all three the table fits in memory. logical reads is the same for all three which makes sense. As there are no indexes the only approach is to scan every row of the table. The second and third query affect zero rows because I'd run them a few times already. CPU time and elapsed work out as 2500ms.

For the bigger query it is

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#ResultSet...'. Scan count 1, logical reads 125223

 SQL Server Execution Times:
   CPU time = 33093 ms,  elapsed time = 33137 ms.

(242906 row(s) affected)

The same number of pages are read, the same number of rows are updated. The huge differences is the CPU time. This is reflected in casual observation of Task Manager which shows 30% utilisation for the duration of query execution. The question is, why does it take so much?

The individual queries separately have simple calculations and two of the statements have predicates that greatly reduce the number of rows touched. The optimizer has good heuristics for processing these and finds a quick plan. The all-in-one query applies the monster Compute Scalar against every single row. My suggestion is that, for whatever reason, the optimizer cannot unravel the logic into a plan that's quick to run and ends up using a lot of CPU. The optimizer has to work with what its given, which in the second case is complex, nested SQL. Perhaps by refactoring the SQL the optimizer will follow different heuristics and achieve a better outcome? Perhaps some (filtered) indexes or (filtered) statistics will convince it to write a different plan. Maybe persisted computed columns would help it along. Perhaps you just need to give the optimizer what it needs and your first attempt really is the best that can be achieved and you need to find a way to run those three in parallel. Sorry I can't be more scientific.


SQL Server Distributed Availability Group Usage Pattern

The documentation for Distributed availability groups states:

  Although each underlying availability group of the distributed availability group has a listener, a distributed availability group has no listener.

So there is no way for a client to connect to a distributed AG because there is no listener for it.  Clients must connect to the listener of the AG that is currently the primary.

In order to fail over a DAG and provide connectivity for clients, you would need to initiate failover on the DAG, and then create a listener on the DR cluster with the same name as the current listener that clients connect to.

If you are using Enterprise edition, creating a DR node in the cluster and making it an asynchronous replica in the regular availability group is usually a better option, so if you haven't considered that I recommend looking into it.


Is there a way to grant SELECT for every database (including new ones) at a server level?

Since you do not know the schema or tables names to grant them to, no to specifically doing it. You can add your AD account to the db_datareader role within the model database, and this should be copied into each database created on that instance.

In addition, if databases happen to get created via restore or anything other than create database statement you can use Policy Based Management rules to easily verify if your AD account is a member of that DB role and if not take an appropriate action.


Create Table with Int-attribute which can only use values between 6 and 36

Most likely, the intention is for you to create a table and a CHECK constraint on the table

CREATE TABLE foo (
  col1 integer check( col1 between 6 and 36 )
);

And a demonstration that it works

SQL> CREATE TABLE foo (
  2    col1 integer check( col1 between 6 and 36 )
  3  );

Table created.

SQL> insert into foo values( 5 );
insert into foo values( 5 )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0047240) violated

SQL> insert into foo values( 37 );
insert into foo values( 37 )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0047240) violated

SQL> insert into foo values( 12 );

1 row created.


SQL Server: deadlocked on lock communication buffer resources

The full message that is commonly seen:

  Transaction (Process ID 53) was deadlocked on lock | communication
  buffer resources with another process and has been chosen as the
  deadlock victim. Rerun the transaction.

This lock type is commonly seen with deadlock queries that SQL Server has executed as parallel, sometimes referred to as "intra-query parallel deadlocks". I have seen a few statements that this also points out system resources are low, which I guess could be involved to a small degree.

A general guideline that I have noticed to determine if it is parallel deadlock is when you pull the XML deadlock graph (which can be done with the system_health session in 2008 and higher) you will notice different process IDs showing the same bit of code within the execution stack.

As well, looking at the resource list of the deadlock graph and noting the type of waiter event. They will most commonly show "e_xxxxxx", or something like this maybe:

<waiter-list>
 <waiter event="e_waitPipeGetRow" type="consumer" id="process821d828" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process8209198" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process3827c18" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process3809eb8" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process8226b08" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process9acb6d8" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process6188d7828" />
 <waiter event="e_waitPipeGetRow" type="consumer" id="process381cef8" />
</waiter-list>

To try and resolve the issue various paths to take are offered online and in books. I generally start by looking at the execution plan of the query/procedure and focus on the areas that are showing parallel execution. Then from there go through trying to tune the query first and then as last resort may start using query hints.

The most common query hint you will see mentioned to resolve these deadlocks is implementing MAXDOP 1. However, before doing that you might check to see what the server level MAXDOP and Cost Threshold are set to. Cost Threshold is generally set to 5 by default and I like to raise that to 35 or 40 to start out with, if the query in question has a low cost for that section of code it may not need to run in parallel at all. I'm not all that fond of using MAXDOP query hints but that does not mean they do not have their place and purpose. just my opinion.


Metrics to prove or disprove the need for additional DBA's?

Demonstrating ROI on hiring additional DBAs is hard since DBAs don't generate revenue by themselves. They do however allow the organization to function and generate revenue, just like any other supporting staff. A lack of staff in any department makes the organization function less optimal and lose money or lose opportunity. So there is a cost from lack of investment rather than a return on investment.

If you feel you need additional DBAs you must know what tasks you are unable to perform because of lack of time/skill, if it's lack of skill  (such as the SSAS stuff you mentioned) that can be trained, if it's lack of time it could be temporary or structural. I've been in situations where you just know that your life would be a lot easier if you could just automate this and that or set up some alerting but just never get around to because of too much reactive or repetitive work and general fire fighting.

Once you have determined why your team isn't functioning as well as it should it's time to document it.

First of all, document what you do now, why that takes up all your time and make clear up front to your manager the existing problems that need solving are absolutely none of your team's fault. Make sure you don't give the impression that the problem would be solved by hiring 'better' DBAs.
Make an excel sheet showing your workload for the coming weeks if you can, charts impress, and it can show your workload in a single slide.

Once you are clear on that, document all additional tasks and everything that needs improving, but especially document what the business risks and possible costs are, and the savings could be. Also document how much man-hours you think would be needed to make those improvements and perform those tasks.

Things that go on the investment side of the equation are

Salary of the newly hired DBA
Price of training

Things that go on the savings side of the equation are

Projects not finished on time
Preventing downtime that makes the business lose money
Preventing legal risk
Preventing data loss
Consulting fees could be reduced
Cost of burning out employees because of too much work

Once you have compiled the list as complete as you can it should be clear what the best price/reward option is

Getting additional training
Getting additional DBAs
Hiring a temporary consultant to assist in automating stuff, monitoring stuff, performing a migration so that the existing team has more time
Changing the priorities put on you by the business so you get time to get things in place that will allow you to work more efficiently later on


Reverse engineer Oracle database in visio

Check the character set the database is configured to support, then check Visio to see if it's using the same set. Also I think character sets can be configured on a per table basis so if some tables are fine and the new ones are not, take a look at the table character set too.


How to wait for PostgreSQL to be startable / restorable?

initdb doesn't return until it's finished, so there shouldn't be any pause needed between it and server startup.  There have been bugs in PostgreSQL where it completed without flushing everything to disk first though.  I don't know of any left right now, but the nature of bugs is that you don't always know about them.

If you use the pg_ctl command to start the database, use the "-w" parameters for that to wait until startup is finished before returning.  It doesn't do anything fancy--it just does the "is it ready yet?" loop for you.

Note that if you get a server crash with a lot of data that needs to be replayed before the server can start, the timeout set by "-t" on the pg_ctl waiting might be too low.

There is no reason to VACUUM the source databases before doing a pg_dump of them.  While it might speed the dump up a bit, the vacuum itself will take longer than that improvement.


Mariadb 10.2/MySQL 5.7 index condition pushdown on partitioned tables

Support for ICP on partitioned tables was added on this MySQL commit:

https://github.com/mysql/mysql-server/commit/67d2e4ef917e49b0c14cee50e47a498f3f3d95d1

You can check reliably if there is support for in a particular version of MySQL or MariaDB by running the corresponding test.

I do not see such code backported on MariaDB's 10.2-tagged git nor on the offered source downloads.

However, the code doesn't look like insanely crazy, so I would suggest to file a ticket on MariaDB's Jira and ask to backport it. I have searched it briefly and failed to see a similar request. I am a heavy MariaDB and partitioning user and that would be indeed a nice feature to have.


Is there any way to turn off the "loading" message when a page refreshes in Reporting Services?

If you have an ASP.NET developer on hand, have them use the ReportViewer control to embed the report directly into a plain old aspx page. I've done this to a few reports, and they never show the usual "Report is being generated" message that you get when accessing them via Report Manager. (You might have to set AsyncRendering="False" on the ReportViewer control to get the desired behavior.)

This will also allow you to dress up the page a bit, and conceal the various Reporting Services toolbars, which would be ideal for a billboard display like you describe. Put a meta refresh in the page to get a 30-second refresh, and you're set.


In the same query INSERT, can two VALUES being NOW() return a different time?

The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function implementation grabs this value and returns it into the now_time structure.
MySQL docs for NOW() also state:

NOW() returns a constant time that indicates the time at which the statement began to execute. ...

You will always get the same NOW() value from anywhere in the query.
MariaDB [test]> select now(),sleep(10),now();
+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2021-03-22 14:17:05 |         0 | 2021-03-22 14:17:05 |
+---------------------+-----------+---------------------+

Worth noting, that this isn't per transaction:
MariaDB [test]> start transaction
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]>  select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:20:53 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:21:00 |
+---------------------+
1 row in set (0.00 sec)


File /etc/my.cnf.d/auth_gssapi.cnf contains the line

plugin-load-add=auth_gssapi.so

Comment it out and restart the service. The server should stop loading the plugin, it will make the errors go away.

If you don't need the plugin at all, you can also uninstall MariaDB-gssapi packages

MariaDB-gssapi-server-10.1.19-1.el7.centos.x86_64
MariaDB-gssapi-client-10.1.19-1.el7.centos.x86_64


SQL Availability Group restore database to standalone server

That's the error you get when you restore the master database from a server with AlwaysOn Availability Groups configured, or try to restore additional user databases onto a server that had the master database from an AG replica restored.

Don't restore the master database from an AG replica onto a different SQL Server as the master database.

If you absolutely have to restore master to get user objects out of it, restore it as a different database (like master_old) and then transfer the objects you need.


How can I get status messages during a large re-indexing operation with MySQL 5.5?

You need to monitor that progress outside of all mysql client programs.

For this example, let's say your MyISAM table is named workingset in the database mydata and the datadir is the default, /var/lib/mysql

Sidenote : If you are using Windows, run this SQL command to get your datadir

SHOW VARIABLES LIKE 'datadir';

You will have three files representing the table

/var/lib/mysql/mydata/workingset.frm
/var/lib/mysql/mydata/workingset.MYD
/var/lib/mysql/mydata/workingset.MYI

Try a temp table approach to loading the table workingset

use mydata
DROP TABLE IF EXIST workingsetload;
CREATE TABLE workingsetload LIKE workingset;
ALTER TABLE workingsetload DISABLE KEYS;
Perform 7 million row insert into workingsetload here, preferably LOAD DATA INFILE
ALTER TABLE workingsetload ENABLE KEYS;
ALTER TABLE workingset RENAME workingsetold;
ALTER TABLE workingsetload RENAME workingset;
DROP TABLE workingsetold;

During the load of the table workingsetload, there will be a temp table whose first five(5) characters are "#sql-". You can visually monitor in the Linux OS the progress of the loading of workingsetload like this:

cd /var/lib/mysql/mydata
watch -n 1 "ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}'"

You could script this in linux like this:

cd /var/lib/mysql/mydata
ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}' > /tmp/IndexRebuild.txt

You could then parse the columns accordingly and subtract the .MYD sizes to know when the data portion of the MyISAM table is complete. Subsequently, you could then parse the columns accordingly and subtract the .MYI sizes to know when the index portion of the MyISAM table is complete.

For Windows, you could script this using Perl (ActivePerl for Windows) or just have a DOS Batch loop over and over again.

Regardless of OS, once the "ls -l #sql-*.MY[ID]" or "if not exist #sql-*.MY*" comes back with nothing, the reload and indexing are complete.

Give it a Try !!!

UPDATE 2011-07-20 16:55 EDT

Monty has successfully implemented this feature in MariaDB. Check it out !!!


PostgreSQL sometimes fails to detect serialization failures

Doing this in a serialized transaction should work, unless there is a bug.
As a workaround, use this command, which tries to add a new license; if the license key already exists, it adds the new hours to the existing value:
INSERT INTO licenses(license_key, product_id, hours)
VALUES ($1,
        (SELECT product_id FROM products WHERE product_code = $2 LIMIT 1),
        $3)
ON CONFLICT (license_key) DO UPDATE
SET hours = hours + EXCLUDED.hours
RETURNING id;

documentation


Loop over string literals as input to function PostgreSQL 9.4

You can use to_char() ,

CREATE FUNCTION update_all_total () RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', to_char(i, '9999'), 'DAC', '20001');
END LOOP;
END;
$BODY$;

or can use cast ::char(4).

CREATE FUNCTION update_all_total () RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i::char(4), 'DAC', '20001');
END LOOP;
END;
$BODY$;


How to create shared folder between my on-premise Windows Server and Azure VM Server?

If you are new to Azure I would suggest ticking the "Copy the database backups to a different location that the target server can read and restore from" and manually copy the backup files to the target.

I tried setting up an SMB share in Azure to use as the backup location as per this article. The issue I ran into was that Azure does not allow anonymous access to files share (the request for the feature has been under review for more than 2 years)and you also can't enter the credentials in the Microsoft Data Migration Assistant, so this route doesn't seem like a solution at the moment.

The only other way I can think of is creating a permanent link with either ExpressRoute or VPN and then creating a share that can be accessed by both subnets.


Copying Joao's answer (from question comments) so this Q can have an A.

No, there is no problem with that. Single inserts/updates are atomic and reads are always consistent. However, you don't have transaction blocks in MongoDB (executing multiple inserts/updates atomically). – joao Feb 24 '14 at 13:26


Difference between changed compatibility level database and new database

I'm not sure if this gets to the root of your question - if not, please update your question with more information.

According the the documentation for ALTER DATABASE (Transact-SQL) Compatibility Level, the compatibility mode of a database:

  Sets certain database behaviors to be compatible with the specified
  version of SQL Server.

As I understand the documentation, two databases running under the same compatibility mode should operate the same way with regard to those specific behaviors.

Restored databases do not (necessarily) automatically change their compatibility mode.

From the documentation:

  When a database is upgraded from any earlier version of SQL Server,
  the database retains its existing compatibility level, if it is at
  least minimum allowed for that instance of SQL Server. Upgrading a
  database with a compatibility level lower than the allowed level,
  automatically sets the database to the lowest compatibility level
  allowed. This applies to both system and user databases.


What could cause a sleeping session to have an open transaction?

Scenarios that may explain these symptoms include:

The application code has started a transaction and not committed

The application SET IMPLICIT_TRANSACTIONS ON

The stored procedure includes SET IMPLICIT_TRANSACTIONS ON

This DMV query will help troubleshooting. The transaction name value will be "implicit_transaction" if started implicitly. Other possible values include "user_transaction", "DTC Transaction", or a user-specified transaction name. A "user_transaction" value (with no explict proc transaction) indicates the transaction must have been started on the client side and not committed. A value of "DTC Transaction" indicates a distributed transaction, also indicating an  issue with transaction management on the app side.
SELECT
      session_tran.session_id
    , active_tran.name
FROM sys.dm_tran_session_transactions AS session_tran
INNER JOIN sys.dm_tran_active_transactions AS active_tran ON session_tran.transaction_id = active_tran.transaction_id;

It is a good practice to include SET XACT_ABORT ON in stored procedures with explict transactions to ensure transactions are rolled back, especially following a client timeout. That probably won't help here since your proc has no explict transaction, unless the proc happens to raise an error and rollback the erroneous transaction as result.


Why do Statistic recommendations disappear from DB Tuning Advisor after implementing recommended indexes?

After I implement the indexes

I highly recommend you to read Aaron's excellent blog post:

Don't just blindly create those "missing" indexes!

There are stats automatically created with indexes, so there's no statistic to suggest when an index exists (because by definition that statistic is already there). DTA is suggesting both an index and a stat - an index is usually best, but most costly in terms of maintenance; a stat is second-best, and can help with cardinality estimates, but not with things like seeking or sorting.  Not sure why it's obvious these aren't the same stats - but it could also be that those queries have benefitted enough from the indexes you created that there is no further benefit to derive from adding stats.

This answer consists of comments converted into a community wiki answer


Linked server from SQL server on AWS RDS to On premises MS SQL server database

The Implement Linked Servers with Amazon RDS for Microsoft SQL Server doc says RDS supports below options:

SQL Server (EC2 or on-premises) to RDS SQL Server
RDS SQL Server to SQL Server (RDS, EC2, or on-premises)


Query another schema on a different sid Oracle

Your database links are configured with the wrong passwords.


Do not say

!= 'null'

Instead, say

IS NOT NULL

That is, unless you are actually storing the 4 characters "null" in a VARCHAR?

Indexes:

log_follow:  INDEX(other_id, id)   -- for the subquery
darkhast_follower:   no index is useful for this query

The query, as you have it, will scan darkhast_follower until it finds 5 rows matching the all the WHERE clauses. In doing so, it will check log_follow -- This task will be a table scan (slow) if you don't have a suitable index.  Else it will be fast.