Select random database records between subset of records having the same count(*) in a query

To solve this I did the following (see the fiddle)

Created a table:

CREATE TABLE test  -- 22 records
(
  the_word TEXT,
  no_of_times INTEGER
);

Populated it with the data at the end of this answer.

Then ran the following query:

SELECT the_word, no_of_times FROM test
ORDER BY no_of_times DESC, RAND() LIMIT 15;

I tested this several times and obtained results like the following. The first 6 records were always the same - keywords1-6. The remaining 9 records were always randomly from keyword7-22.

Result (sample - first 10 shown):

 the_word   no_of_times
keyword1            100
keyword2             90
keyword3             80
keyword4             70
keyword5             60
keyword6             50
keyword17            40
keyword19            40
keyword22            40
keyword18            40

This is considerably more elegant than my first answer - guess I overcomplicated things!:-)

=========================== Data (22 records) ================

INSERT INTO test VALUES ('keyword1',  100);
INSERT INTO test VALUES ('keyword2',  90);
INSERT INTO test VALUES ('keyword3',  80);
INSERT INTO test VALUES ('keyword4',  70);
INSERT INTO test VALUES ('keyword5',  60);
INSERT INTO test VALUES ('keyword6',  50);
INSERT INTO test VALUES ('keyword7',  40);
INSERT INTO test VALUES ('keyword8',  40);
INSERT INTO test VALUES ('keyword9',  40);
INSERT INTO test VALUES ('keyword10', 40);
INSERT INTO test VALUES ('keyword11', 40);
INSERT INTO test VALUES ('keyword12', 40);
INSERT INTO test VALUES ('keyword13', 40);
INSERT INTO test VALUES ('keyword14', 40);
INSERT INTO test VALUES ('keyword15', 40);
INSERT INTO test VALUES ('keyword16', 40);
INSERT INTO test VALUES ('keyword17', 40);
INSERT INTO test VALUES ('keyword18', 40);
INSERT INTO test VALUES ('keyword19', 40);
INSERT INTO test VALUES ('keyword20', 40);
INSERT INTO test VALUES ('keyword21', 40);
INSERT INTO test VALUES ('keyword22', 40);


Improving the query standards and performance run time

Clustered Index is must.Unless we do not have table structure and their description we cannot say which column should be CI.

Provide volume of data in each table and expected volume of output.

DATEPART(hour,TABLE1.COL1) BETWEEEN 1 AND 2 is not SARGAble.

So you can rewrite the query as below.Always check the performance with parameter instead of HARD coded value.

I mean that in real life scenario, we use local variable or Proc's parameter.So test query performance using using same.
Check that Parameter Sniffing problem is tolerable or not and how it can be minimized.If Parameter Sniffing is tolerable then ignore it.
Option Recompile should be rarely use .

declare @FromDate Datetime=cast(getdate() as date)
declare @FromDatetime=dateadd(hour,1,@FromDate)
declare @ToDatetime=dateadd(hour,2,@FromDate)

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM
       TABLE1 T1
     inner join  TABLE2 T2 on T2.ID=T1.ID
     inner join  TABLE3 T3 on  T3.ID=T1.ID -- try once T3.ID=T2.ID
WHERE
   T2.COLUMN1='Data'
AND   T3.COLUMN2='Value'
AND   T1.COL1>=@FromDatetime
AND   T1.COL1<=@ToDatetime

Alternatively,

It appear that you don't need Table2 and Table3 column in your result set,

So you can rewrite in this manner,

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM dbo.TABLE1 T1
WHERE
exists(select 1 from dbo.TABLE2 T2
     inner join  dbo.TABLE3 T3 on T3.ID=T2.ID
     where T3.ID=T1.ID and T2.COLUMN1='Data' AND   T3.COLUMN2='Value')
AND   T1.COL1>=@FromDatetime
AND   T1.COL1<=@ToDatetime


What you need is a breakdown by user and hostname along with a total

SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

This will handle host address that have a colon separating hostname and port number

I hope everybody is not logging in as root


SQL Service Account creation in AD using PowerShell

If you have installed the Active Directory PowerShell components, and the account under which you are running the session has sufficient permission to perform the action, then it is actually pretty simple:

New-ADUser -Name "Jo Bloggs" -GivenName Jo -Surname Bloggs`
-SamAccountName jbloggs -UserPrincipalName jbloggs@corp.contoso.com `
-AccountPassword (Read-Host -AsSecureString "AccountPassword") `
-Path 'OU=Whatever,OU=Blah,DC=corp,DC=contoso,DC=com' `
-PassThru | Enable-ADAccount

The documentation is pretty extensive and can be found here:
https://technet.microsoft.com/en-us/library/ee617253.aspx


Is Percona xtrabackup the right choice for my 500GB MySQL backup?

From the sound of this, you could use a different DB infrastructure.

Based on your aforementioned needs, I have a suggestion but would require a little compromise.

SUGGESTION #1 : Use MySQL Replication on the Same Server but Different Disk

Your second need (No need of real-time replication) would have to take a slight backseat in this suggestion. Since you have an external harddrive big enough for XtraBackup, why not use as a datadir for a second instance of MySQL on the same box?

I scripted my own service to provision dedicated mysql instance for ports 3307 - 3399. Here are my past posts on this:

Sep 17, 2012 : mysqlservice command syntax
Sep 30, 2011 : Running multiple instances on the same host

SUGGESTION #2 : Use MySQL Replication on the Different Server

In consideration of your first need (Low Cost), if you have access to a commodity server with adequate diskspace, setup MySQL Replication to that external server. That way you can run backups on the Slave as follows

STOP SLAVE;
XtraBackup
START SLAVE;

You can do this with zero impact (no server load, no disk I/O) on the Master

SUGGESTION #3 : Parallel mysqldumps

You can setup parallel mysqldumps of databases or tables. This may result in a shorter window of time for backups. XtraBackup essentially does a checkpointed transactional backup with point-in-time sensitive operations so that the point-in-time of the backup is when the backup finished. With parallel mysqldumps on a slave which is not replicating gives you backups whose point-in-time is the beginning of the backup. Doing mysqldump on a stopped slave would be storage engine agnostic. In other words, it would not matter whether tables are InnoDB or MyISAM.

If you apply this to SUGGESTION #1, this will incur server load and disk I/O since both Master and Slave reside on the same machine.

If you apply this to SUGGESTION #2, there is no penalty on the Master. You can run backups at will on the Slave. You also will not have to concern yourself with locking MyISAM tables.

Please see my past posts on doing parallel mysqldumps

Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
May 22, 2012 : MySQL Server Backup
Oct 01, 2011 : Quick MySQL Backup
Apr 17, 2011 : How can I optimize a mysqldump of a large database?


Are you able to tnsping realdb_1, realdb_2 individually?

If you're using SCAN, but haven't manually added node specific entries to your tnsnames.ora, this problem is likely to occur upon requesting connection to a specific node, since SCAN may be configured to resolve to the service_name of the RAC, rather than to the "instance_name".

Here's my tnsnames.ora:

USAPROJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usaproj.localdomain)
    )
  )

USAPROJ1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usaproj.localdomain)
      (INSTANCE_NAME = usaproj1)   <-------
        )
  )

I created another entry, usaproj1, and in addition to service_name parameter, I added "instance_name", to specify the node I wanted to connect.

Hope it helps.


Concatenation of setof type or setof record

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a  standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).


It's sensible to think about how to scale your application in future, but you can start with a simpler deployment that can grow with your requirements.

  what should i choose? having 1 database sounds good as it will save me a lot of resources and will make my life easier, but separating the services to many database sound logic to me as one service don't need to see collections or be able to access a collection that have nothing to do with his job.

My recommendation would be to set up a single MongoDB deployment (ideally starting with a replica set or hosted service with high availability & data redundancy):

A single deployment of MongoDB can have multiple databases; a MongoDB deployment per service adds unnecessary admin complexity.
For your use case it sounds reasonable to have a database per service and setup a user per service so actions are limited to direct interaction with the expected database.
A database per service also limits potential impact of administrative actions (eg. a foreground index build) that may create database-level contention.

Later you can scale/tune the deployment starting with options like directoryPerDB (eg. faster storage for some databases) or partitioning your data using sharding.

  I'm not sure how mongoDB handle many connections to the same instance, or to same database, as you can see it might get a lot of calls in a second even if i have pretty low amount of users.

This is a more nuanced question as it depends on server resources as well as what those connections are doing and how/if your driver implements connection pooling. Ideally I would load test your actual application and determine how resource usage scales with increased user activity. If you are administering your own MongoDB deployment you should review the production notes and in particular make sure you have your ulimit settings increased appropriately to ensure you don't run out of file descriptors.

If you're concerned about ease of scaling you could also consider a hosted database-as-a-service solution (for example, MongoDB Atlas).

  I read about mongo Sharding, from what i understood it happens per database and not on the instance level, that's why i'm thinking about the 1 database option, as it quite useful in my case, am i right?

Sharding is initially enabled at the database level but the partitioning happens at the collection level based on a shard key. You can have a mix of sharded/unsharded databases and sharded/unsharded collections in the same MongoDB deployment.


Updating databases that user's can manipulate

One possible way is adding an is_deleted tinyint column, with 0 as default. Instead of deleting, you can set it to 1, and then filter these out in the queries. This way you always keep all countries that ever got into the table.

In some previous projects of mine we had quite a lot of these, setting them using ON DELETE triggers.  In turn, the queries nearly all went to views that were defined with a condition in the WHERE clause like

...
AND is_deleted = 0 -- well, it was Postgres, so originally AND NOT is_deleted
...

The downside of this is an additional level of dependencies (or two). The other solution is changing the queries to filter out the rows that are marked as deleted.


using port range for connecting to Postgres

No, that's not possible.  A single Postgres server can only listen on a single port.

But: the communication through the port is not a bottleneck.

For every connection made, Postgres creates a dedicated backend process once that is established the backend process does all the work.

If you want to increase network throughput you could install a second network interface and make Postgres listen on both interfaces.

If concurrent use of a single server port was a problem, websites wouldn't be able to handle the load they can. After all a webserver only listens on a single port as well (80 or 443)


SQL Server 2014/2016 (Win2K12R2) and Winsock limits - how to cope?

The information presented here makes it look likely connections are rejected due to the TCP backlog queue being full of partially opened connections. The other item I ask folks to double-check in cases like this is that MPP (Memory Pressure Protection) is either globally disabled, or disabled for the SQL Server listener port.
As you indicated, SOMAXCONN is the value passed in when the SQL Server listening socket is opened to give the queue depth for the backlog queue.  Unfortunately, as you're probably aware, up through Windows Server 2019 SOMAXCONN is not exposed or configurable to administrators, but is instead set to a "reasonable value" by Windows.  Although I've not seen documentation for how Windows determines this value, I imagine it increases as RAM on the system increases.  CPU count may also  play a role.
One way to explore whether it is a TCP backlog queue full of partially-opened connections is by counting connections in the SYN Received state.
For example the following powershell will give counts for connections in each state for each (LocalPort, OwningProcess).
Get-NetTCPConnection | Group-Object -Property LocalPort, OwningProcess, State | Select -Property Name, Count | Sort Name

Unfortunately, while trend of connection counts will be helpful, without knowing SOMAXCONN it may not be definitive.
Unfortunately Windows does not provide the TCPExt stats in 'netstat -s' output; 'resets received for embryonic SYN_RECV sockets' could be especially useful here.
Without knowing the formula or resulting values for SOMAXCONN on a system, it's hard to know how much RAM would result in a higher SOMAXCONN value and more breathing room for partially opened connections. You mentioned a system with 2TB of RAM saw similar connection failures, it's likely you won't get much larger of a SOMAXCONN value than that.
And if the connection burst can't be throttled or capped at a lower maximum, then the only intervention with reasonable confidence is trying to speed up getting connections through the process.  Eliminating any use of paging space if present can help.  In a VM, oversubscription of CPU or memory can hurt (by slowing down connection completions during a connection burst).  In some cases RSS can help, but you noted that has already been tried.  WFP filters (Windows filtering platform) such as anti-virus and anti-malware can add overhead to connection completion; so any WFP filters in use may warrant review.
Use High Performance Power Plan rather than Balanced Power Plan.  If a connection burst immediately follows a period of low activity on the target server, the CPU will be starting to complete partially opened connections at a lower clock speed and this can contribute to a full backlog queue followed by connections denied.


Insert and select the row autoincremented id

No.  Instead:

INSERT ...
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() is session-specific, so there is never any confusion with other threads also inserting into the same table.

Also, there is no need for BEGIN ... COMMIT for this pair of statements.  (You may need it if there are other statements.)


There's a lot going on in your question, so I'm going to break down my response into sections.

  I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable FKs to do that, i fear they will become untrusted and the parent tables will be in conflict.

You do NOT need to disable Foreign Keys in order to delete data from tables that have Referential Integrity defined; you just need to delete data from the child table(s) BEFORE you delete referenced data from the parent table.  When you disable the Foreign Key before issuing the DELETEs and then delete data only from a parent table, you will most certainly run the risk of having orphaned records.  Trying to re-enable the FKey constraint would also throw an error in this scenario.  This MSSQL Tips article does a decent enough job walking through why this is important, so I won't rehash it here.

  I read about "ON CASCADE DELETE" but i think that only works for children tables.

The cascaded deleted would identify if the table you're currently trying to delete data from is a parent table and issue appropriate deletes from underlying child tables prior to removing the records from the parent table.  All of this activity is nested within an implicit transaction, so depending on how much child-level data references said parent records, you may find yourself running a long-running transaction that generates a lot of blocking.  There are a number of issues with cascaded deletes (and updates), but taking this approach is a personal preference.  I think David Spillet's answer to this dba.se question does a good job of touching some of the risks/rewards with cascaded statements.

  Am i missing an easy solution to this problem?

Yes, if this is a test environment and you don't care about the data, the fastest approach would be what I would consider the nuclear option, as follows:

Drop all Referential Integrity constraints
Truncate all tables (this is why we disable the check constraints)
Recreate all Referential Integrity constraints
Insert any default data back into the tables of your choosing

The following script will accomplish this without much effort (or any checking).  Be careful with this; it is indiscriminate and will wipe all user data from your database without a second thought:

DECLARE @objectName NVARCHAR(1024), @fkeyName NVARCHAR(256), @refName NVARCHAR(1024), @colSelf NVARCHAR(MAX), @colReference NVARCHAR(MAX), @dSQL NVARCHAR(MAX)

-- Walk through the tables dropping RI
DECLARE fkeyDefinitions CURSOR STATIC
FOR
    -- Based on query lifted from RJB's SO Post: https://stackoverflow.com/a/36818102/944748
    SELECT  '[' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + OBJECT_NAME(t.object_id) + ']' AS objectName
        , fk.name
        , '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + OBJECT_NAME(fk.referenced_object_id) + ']' AS refName
        , STUFF((SELECT ', ' + QUOTENAME(c.name)
                 FROM sys.columns AS c
                    INNER JOIN sys.foreign_key_columns AS fkc
                        ON fkc.parent_column_id = c.column_id
                       AND fkc.parent_object_id = c.[object_id]
                 WHERE fkc.constraint_object_id = fk.[object_id]
                 ORDER BY fkc.constraint_column_id
                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_self
        , STUFF((SELECT ', ' + QUOTENAME(c.name)
                 FROM sys.columns AS c
                    INNER JOIN sys.foreign_key_columns AS fkc
                        ON fkc.referenced_column_id = c.column_id
                       AND fkc.referenced_object_id = c.[object_id]
                 WHERE fkc.constraint_object_id = fk.[object_id]
                 ORDER BY fkc.constraint_column_id
                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_reference
    FROM    sys.tables t
        LEFT JOIN sys.foreign_keys fk
            ON t.object_id = fk.parent_object_id
    WHERE   t.type = 'U'
    --AND t.name LIKE 'blah%'  -- for only a subset of tables, uncomment/add more conditions to the predicate

-- go through the fkey cursor and drop all RI
OPEN fkeyDefinitions
FETCH NEXT FROM fkeyDefinitions
INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT [' + @fkeyName + ']'
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM fkeyDefinitions
    INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference
END

-- go through new cursor truncating all the tables
DECLARE truncUserTables CURSOR
FOR
    SELECT  '[' + OBJECT_SCHEMA_NAME(object_id) + '].[' + OBJECT_NAME(object_id) + ']' AS objectName
    FROM    sys.tables
    WHERE   type = 'U'
    --AND t.name LIKE 'blah%'  -- for only a subset of tables, uncomment/add more conditions to the predicate

OPEN truncUserTables
FETCH NEXT FROM truncUserTables
INTO    @objectName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'TRUNCATE TABLE ' + @objectName
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM truncUserTables
    INTO    @objectName
END

CLOSE truncUserTables
DEALLOCATE truncUserTables

-- go through the fkey cursor a second time re-creating RI
FETCH FIRST FROM fkeyDefinitions
INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'ALTER TABLE ' + @objectName + ' ADD CONSTRAINT [' + @fkeyName + '] FOREIGN KEY (' + @colSelf + ') REFERENCES ' + @refName + '(' + @colReference + ')'
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM fkeyDefinitions
    INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference
END

CLOSE fkeyDefinitions
DEALLOCATE fkeyDefinitions

I even setup a dbfiddle.uk demo if you want to see it in action.


Delete the second record where a user has two records

Solution

Create a TempTable(record_table_keep) with minimum id for every subid,catid combination
Perform a DELETE JOIN between record_table and record_table_keep using LEFT JOIN
Only delete records from record_table whose id is not in record_table_keep

Proposed Query

CREATE TABLE record_table_keep
SELECT id,subid,catid FROM record_table WHERE 0=1;
ALTER TABLE record_table_keep ADD PRIMARY KEY (id);
INSERT INTO record_table_keep (id,subid,catid)
SELECT MIN(id) id,subid,catid FROM record_table GROUP BY subid,catid;
DELETE A.* FROM record_table A
LEFT JOIN record_table_keep B USING (id)
WHERE B.id IS NULL;
DROP TABLE record_table_keep;

Give it a Try !!!


Transactional Replication.
As one idea, this sounds like a reasonable candidate for transactional replication. Checksums, cyclical redundancy checks (CRC), and row counts are used to validate source data with the destination data.
From Validate Replicated Data, Books Online:

How Data Validation Works
SQL Server validates data by calculating a row count or a checksum at the Publisher and then comparing those values to the row count or checksum calculated at the Subscriber. One value is calculated for the entire publication table and one value is calculated for the entire subscription table, but data in text, ntext, or image columns is not included in the calculations.
While the calculations are performed, shared locks are placed temporarily on tables for which row counts or checksums are being run, but the calculations are completed quickly and the shared locks removed, usually in a matter of seconds.
When binary checksums are used, 32-bit redundancy check (CRC) occurs on a column-by-column basis rather than a CRC on the physical row on the data page. This allows the columns with the table to be in any order physically on the data page, but still compute to the same CRC for the row. Binary checksum validation can be used when there are row or column filters on the publication.


Error Grouping By Multiple Columns using CASE

I guess that when Type has a certain value ('Test'), you want the Structure and Description columns to be considered for the grouping but otherwise to not use them at all. This is one way to do this:

GROUP BY
    Type, Account,
    CASE WHEN Type = 'Test' THEN Structure ELSE '' END,
    CASE WHEN Type = 'Test' THEN Description ELSE '' END

The '' should be a (any) constant expression that has the same datatype with Structure and Description respectively.

Alternatively, you could separate the 2 cases, which will give you more flexibility on adjusting the SELECT lists:

SELECT
    ---
FROM
    ---
WHERE
    Type = 'Test'
GROUP BY
    Type, Account, Structure, Description

UNION ALL

SELECT
    ---
FROM
    ---
WHERE
    (Type <> 'Test' OR Type IS NULL)
GROUP BY
    Type, Account ;


Merge multiple rows into one row with sql

SELECT [Employee]
      ,[Year]
      ,[Period]
      ,[Activity]
      ,Sum([Day1]) as Day1
      ,Sum([Day2]) as Day2
      ,Sum([Day3]) as Day3
      ,Sum([Day4]) as Day4
      ,Sum([Day5]) as Day5
      ,Sum([Day6]) as Day6
      ,Sum([Day7]) as Day7
FROM [HoursAccounting]
where Employee = '1234'
group by Employee, [Year], Period, Activity;


Concurrent connections to the same table

One approach:

At the beginning of the operations use SELECT GET_LOCK('no-nonsense');
At the end, do SELECT RELEASE_LOCK('no-nonsense');


BCP utility:“Copy direction must be either 'in', 'out' or 'format'”

Same version of Powershell on the servers/vms hosting SQL Server 2008R2, 2012, and 2016?
A parsing error that removes the double quotes around the query would result in this error.


Why does mysql allow me to enter password for non existing mysql user

I'm sure someone else will be able to provide a more informed answer than this, but a common-sense guess would be that this is a security feature.

If someone is attempting a brute-force attack, they will need the username and password using the system described above. If MySQL reported invalid users before prompting for a password, half of the work is already done for the attacker.

This is purely theoretical, as I haven't tested anything or been informed on the subject by anyone else - just an educated guess based on other systems.


https://www.hackerrank.com/domains/sql/join

https://www.hackerrank.com/domains/sql/advanced-join

As far as I remember, the solution you give is evaluated on different datasets.

Also you can choose which RDBMS to use: DB2, SQL Server, MySQL, Oracle (unfortunately no PostgreSQL).


Changing a user from being linked to a login to being without login

There is no method of directly altering a user to be loginless.  Having said, you could simply drop and recreate the login:

DROP USER foobar;
CREATE USER foobar
WITHOUT LOGIN;

Of course, you'd want to gather details about what rights the user has prior to dropping it, so you can recreate those permissions after the CREATE USER statement.

Attempting to connect a loginless user to a login, via the following syntax:

ALTER USER foobar
WITH LOGIN = foobar;

Does not work, contrary to what I reported here previously.  That attempt results in the following error:

  Msg 33016, Level 16, State 1, Line 5
  The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.


Report Builder cannot publish to report server

So what exactly is "DataSource1"? You need to make sure that the datasource for the report you're executing is configured correctly. Here is some info for that: http://technet.microsoft.com/en-us/library/ms159736.aspx


Delete files from disk, with a job, respecting some rules ( SQL Server 2008 )

Try something like this:
-Install SQL 2012 feature pack to get invoke-sqlcmd or change it to use sqlcmd.

-You can just pass in the raw TSQL but I would turn it into a stored procedure and just pass that in. Something like:

CREATE PROCEDURE [GetDeleteDate]
AS
select ArquivoServidor from tblExchange
  where DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30

####Loop through results and execute######
##Define the return as an array by putting it in @()
$lol = @(Invoke-Sqlcmd -Server $ServerInstance -Query "exec [YourAdminDatabase].[dbo].[GetDeleteDate]" | select-object -Expand Column1)

This will now create an array with the return item as 1 value.  You can now use this array in a loop in powershell to perform whatever work you need done and add error handling.  If you need to then execute a new SQL query and pass in the variable you'd do something like this:

#$lol.Count  ##this is to test how many items are in your array.
foreach ($row in $lol)
{
#Write-Output $row
Invoke-Sqlcmd -Server $ServerInstance -Query $row -verbose
}

Or if you want to pass the variable as a item to delete in a loop in powershell, feel free to do that too.  The $lol variable is an array with all the data requested.  At that point you can do whatever you need to do with it in PS using the loop above.  It could be a simple del, remove-childitem, etc. Whatever you're comfortable with.  Let us know if you need help writing the delete portion but in the comments you mentioned you're familiar with PS so I figured you could use what you like best.  If you have spaces in your file path make sure to encapsulate it within single or double quotes.


How to run procedure in Oracle SQL developer

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.

begin
  hotel.fill_city(10000);
end;
/

should correctly invoke your procedure.

Second, you have issues with the naming of your local variables.  Normally, you would not create local variables like city and postal_number that are the same as the names of columns in tables in your database.  That makes it far too easy to introduce errors in your code where you intend to refer to the local variable but scope resolution rules mean that you are really referring to the column name.  For example, if you write the perfectly valid function

CREATE OR REPLACE FUNCTION get_dname( deptno IN NUMBER )
  RETURN VARCHAR2
IS
  dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO dname
    FROM dept
   WHERE deptno = deptno;
  RETURN dname;
END;

in your WHERE clause, both references to deptno will resolve to the column in the dept table, not to the parameter deptno.  That means that no matter what value you pass in to the function, the SELECT statement will return every row from the table and, thus, throw a too_many_rows error.  Normally, you would come up with a convention on how to name variables and parameters that would not conflict with your table naming conventions.  Prefixing parameters with p_ and local variables with l_ is one common convention.  That turns our function into something like this

CREATE OR REPLACE FUNCTION get_dname( p_deptno IN NUMBER )
  RETURN VARCHAR2
IS
  l_dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO l_dname
    FROM dept
   WHERE deptno = p_deptno;
  RETURN l_dname;
END;

The other option would be to use explicit scoping prefixes when referring to local variables (i.e. get_dname.dname and get_dname.deptno) rather than altering the names of your local variables.


What do the entity-relationship diagram (ERD) symbols used in DBeaver mean?

I found something while putting together this question. It seems the current standard used is IDEF1x, according to this github issue.

I found a PDF cheat sheet with the following content showing all the relationships:


Postgres JOIN conditions vs WHERE conditions

Logically, it makes no difference at all whether you place conditions in the join clause of an INNER JOIN or the WHERE clause of the same SELECT. The effect is the same.

(Not the case for OUTER JOIN!)

While operating with default settings it also makes no difference for the query plan or performance. Postgres is free to rearrange joins and JOIN & WHERE conditions in its quest for the best query plan - as long as the number of tables is not greater than the join_collapse_limit (default 8). Details:

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

For readability and maintainability it makes sense to place conditions that connect tables in the respective JOIN clause and general conditions in the WHERE clause.

Your query looks just fine. I would use table aliases to cut back the noise, though.

Minor detail:

int2 '1' or even 1::int2 are more sensible than (1)::INT2. And while comparing to a value of well defined numeric data type, a plain numerical constant 1 is good enough, too.


Delayed replication false alerts and migration delays

The way I understand it, the hidden secondary will always be behind by the number of seconds defined by slaveDelay. In your case, at any moment in the day, the hidden secondary will always be behind by three hours from the last write to the oplog. If there is no more write activity, then the hidden secondary will be completely caught up in three hours. You can check the status of replication in Mongo directly by connecting to each node and running rs.printReplicationInfo()

I configured a 3 sharded cluster using a hidden secondary that is exactly 180 seconds behind, using 2.6.3

Here is the configuration of the two secondaries, the last is hidden and 180 seconds behind.

{
        "_id" : 1,
        "host" : "myhost:27001"
},
{
        "_id" : 2,
        "host" : "myhost:27002",
        "priority" : 0,
        "slaveDelay" : 180,
        "hidden" : true
},

Priority is set to 0 to keep the hidden secondary from participating in elections.

Started inserting documents on the shard and am now looking at the oplog using db.printReplicationInfo().

MongoDB's documentation on db.printReplicationInfo()

  If run on a secondary, the method calls
  db.printSlaveReplicationInfo(). See db.printSlaveReplicationInfo()

Visible Secondary mongo --port 27001

shard2:SECONDARY> db.printReplicationInfo()
configured oplog size:   50MB
log length start to end: 592secs (0.16hrs)
oplog first event time:  Tue Aug 26 2014 08:46:45 GMT+0000 (UTC)
oplog last event time:   Tue Aug 26 2014 08:56:37 GMT+0000 (UTC)
now:                     Tue Aug 26 2014 08:58:45 GMT+0000 (UTC)

Hidden Secondary mongo --port 27002

shard2:SECONDARY> db.printReplicationInfo()
configured oplog size:   50MB
log length start to end: 722secs (0.2hrs)
oplog first event time:  Tue Aug 26 2014 08:43:46 GMT+0000 (UTC)
oplog last event time:   Tue Aug 26 2014 08:55:48 GMT+0000 (UTC)
now:                     Tue Aug 26 2014 08:58:48 GMT+0000 (UTC)

180 seconds later, the hidden secondary catches up mongo --port 27002

shard2:SECONDARY> db.printReplicationInfo()
configured oplog size:   50MB
log length start to end: 592secs (0.16hrs)
oplog first event time:  Tue Aug 26 2014 08:46:45 GMT+0000 (UTC)
oplog last event time:   Tue Aug 26 2014 08:56:37 GMT+0000 (UTC)
now:                     Tue Aug 26 2014 09:02:18 GMT+0000 (UTC)

So whether you are writing to the oplog or not, the secondary will continue to keep up by N seconds as defined in slaveDelay. If your monitoring tool is saying 40 hours, verify first that you are getting the same information using mongo's replicationInfo commands.

db.getReplicationInfo()

db.printReplicationInfo()

rs.printReplicationInfo()

The output of db.printReplicationInfo() is identical to that of rs.printReplicationInfo()

Update

http://docs.mongodb.org/manual/core/replica-set-delayed-member/

Concerning your question about delayed chunk migrations

Sharding

  In sharded clusters, delayed members have limited utility when the
  balancer is enabled. Because delayed members replicate chunk
  migrations with a delay, the state of delayed members in a sharded
  cluster are not useful for recovering to a previous state of the
  sharded cluster if any migrations occur during the delay window.

If I understand your comment correctly.

If secondaryThrottle were set to false, the balancer could continue to migrate chunks without waiting for a delayed member. I'm not sure that's 100% accurate, but that's how I interpreted their documentation about secondaryThrottle and delayed members.


Best way to sync a table between two sql server databases with an identity key

I would go this route:

SET IDENTITY_INSERT <table> ON
...
DBCC CHECKIDENT(<table>)
SET IDENTITY_INSERT <table> OFF

SET IDENTITY_INSERT:
http://msdn.microsoft.com/en-us/library/ms188059.aspx

DBCC CHECKIDENT:
http://msdn.microsoft.com/en-us/library/ms176057.aspx


Is worth change databases collation from Latin1_General to Modern spanish, migrating to a new server

Modern_Spanish and Latin1_General both support the same character set. The difference between the two is the treatment of certain characters when sorting/doing comparisons. For example in Modern_Spanish n and ñ are considered different characters (rather than a character and an accented version of the same character).

Personally I'd stick with Latin1_General as changing the collation may subtly change the behavior of applications (e.g. yesterday this list was sorted one way, today it's sorted a different way, etc.). I'd only change the collation as part of the migration if you understand and want the behavior change.


postgres deadlock without explicit locking

Suppose session 1 does:

begin transaction;
update a set col = val where pk = 1;
update a set col = val where pk = 2;
commit;

and at the exact same time session 2 does the opposite:

begin transaction;
update a set col = val where pk = 2;
update a set col = val where pk = 1;
commit;

Then session 1 will be waiting for session 2 to commit or rollback the update on the row where pk = 2 while at the same time session 2 will be waiting for session 1 to commit or rollback the update on the row where pk = 1. Deadlock.


postgresql cannot drop a table when autovacuum holds lock on it

If no delete or update happened, autovacuum shouldn't process the table, per autovacuum_analyze_threshold. See Automatic Vacuuming for the relevant configuration parameters.

You may set log_autovacuum_min_duration to zero to help figure out what's being autovacuumed.

In any case, it is possible to completely disable autovacuum for a particular table, with:

ALTER TABLE tablename SET (autovacuum_enabled = false,  toast.autovacuum_enabled = false);

This is documented in  Storage parameters.


The mistake is in your understanding of transitive dependency. From wikipedia: Transitive dependency

  In mathematics, a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or distinct collections of attributes) in the relation. Suppose all three of the following conditions hold:

1. A → B
2. It is not the case that B → A
3. B → C

  Then the functional dependency A → C (which follows from 1 and 3 by the axiom of transitivity) is a transitive dependency.

In your case though, (2) does not hold:

1. U → T                                  -- correct
2. It is not the case that T → U          -- wrong
3. T → V                                  -- correct

Therefore {V} is NOT transitively dependent on {U}.


If you're just going to delete this extra data manually on the odd occasion, you can simply turn off the binary logging for the current session. On the MySQL prompt run the following:

set sql_log_bin=0;

Then any query you enter won't be sent to the binary log, and thus won't be sent across to the slaves.

Once you're finished, re-activate binary logging with

set sql_log_bin=1;

Reference: MySQL manual


How to use an RSA public key as the primary key to a table in postgres

I would say store them as bytea, but not for that reason. Simply because pg_crypto expects the keys as bytea. That establishes a convention. They likely have good reason for it: it seems intuitively faster.

Also there is no reason to store them as a PRIMARY KEY. You can sanely just store them in an indexed field.


I suppose you can solve it by using an explicit cast.

CREATE FUNCTION test(tic text)
RETURNS TABLE (foo VARCHAR, bar int)
AS $$
DECLARE
  sql text;
BEGIN
  sql := 'SELECT ''' || tic || ''', NULL';
  RETURN QUERY EXECUTE sql;
END
$$
LANGUAGE plpgsql;

SELECT test('A');

  ERROR:  structure of query does not match function result type DETAIL:
  Returned type text does not match expected type character varying in
  column 1.

CREATE FUNCTION test1(tic text)
RETURNS TABLE (foo VARCHAR, bar int)
AS $$
DECLARE
  sql text;
BEGIN
  sql := 'SELECT ''' || tic || '''::VARCHAR, NULL::int';
  RETURN QUERY EXECUTE sql;
END
$$
LANGUAGE plpgsql;

SELECT test1('A');

| test1 |
| :---- |
| (A,)  |

db<>fiddle here


Automatic create SQL database and table relationship

You could use database diagrams, drag and drop your tables and use the designer to create the relationships for you. Open SSMS, expand the server, expand Databases, expand the database you want and it should be the first folder. If it asks to install things, say yes and away you go.


SQL Server Import Export Wizard Error - Server throw Exception

Since you are getting the error when saving and not executing the package,  you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

Your user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions


OK, so show processlist; revealed that some queries got stuck in "Waiting to lock metadata" on that table. show engine innodb status \G helped identify the offending transaction and then simple KILL <id> unlocked the system.


How to check the default schema of WINDOWS_GROUP?

How to check the default schema of WINDOWS_GROUP

The default schema of a group can be checked with your script.

When this script shows NULL for a group it means that the default schema was not explicitly set for this group.

but when executed with other account, my user is not listed

This means that this account is not explicitly mapped to this database, but it's a member of one ore more Windows groups that are mapped.

The rule here is:

  If the user has a default schema, that default schema will used. If
  the user does not have a default schema, but the user is a member of a
  group that has a default schema, the default schema of the group will
  be used. If the user does not have a default schema, and is a member
  of more than one group, the default schema for the user will be that
  of the Windows group with the lowest principal_id and an explicitly
  set default schema.

When you see that

  someone create an object without specifying a schema identifier, a new
  schema is created with his/her windows credentials

this means that this user the user is member of one or more Windows groups and none of these group has a default schema.

If you want to prevent this "schema creation" you should not leave users-Windows groups without default schema, just assign dbo schema to dbo:

alter user [someDomain\someGroup] with default_schema = dbo

To check your user's groups mapped to this database execute the following code:

select *
from sys.user_token
where principal_id > 0

You can filter here on type = 'WINDOWS GROUP' but when non filtered, you see all the tokens, so if you don't see windows account as it is, it's because it was not explicitly mapped to this database and it reaches the database through Windows group(s)

UPDATE

This behaviour is called implicit schema creation and is documented here:
CREATE SCHEMA (Transact-SQL)

  Implicit Schema and User Creation

  In some cases a user can use a database without having a database user
  account (a database principal in the database). This can happen in the
  following situations:

  A login has CONTROL SERVER privileges.

  A Windows user does not have an individual database user account (a
  database principal in the database), but accesses a database as a
  member of a Windows group which has a database user account (a
  database principal for the Windows group).

  When a user without a database user account creates an object without
  specifying an existing schema, a database principal and default schema
  will be automatically created in the database for that user. The
  created database principal and schema will have the same name as the
  name that user used when connecting to SQL Server (the SQL Server
  authentication login name or the Windows user name).

  This behavior is necessary to allow users that are based on Windows
  groups to create and own objects. However it can result in the
  unintentional creation of schemas and users. To avoid implicitly
  creating users and schemas, whenever possible explicitly create
  database principals and assign a default schema. Or explicitly state
  an existing schema when creating objects in a database, using two or
  three-part object names.


Partition tables based on month and year in SQL Server

Firstly, it must be said that using SQL's native table partitioning support is preferred.  But that requires SQL Enterprise, which is a lot of money, so, presumably, here we are.

A few things for you to check:

Partition column must be part of primary key
Partition column must have one and only one CHECK constraint
Partitions must not overlap.  Perhaps you have a typo, or my eyes fail me, but your constraints on sales_1 and sales_2 overlap.
Make sure the constraints are checked (WITH CHECK CHECK CONSTRAINT).  Given your definition in the question, it probably isn't the issue, but it is in general a necessary troubleshooting step (take a look at SELECT name, is_not_trusted FROM sys.check_constraints)

EDIT

Apparently DATEPART doesn't work.  Probably you should just specify the dates directly (s_date >= '2014-01-01' AND s_date < '2014-06-01'), etc.  You could also use persisted custom columns.

See: https://stackoverflow.com/questions/11155758/partitioned-view-not-working

Some other things to note about partitioned views, most of which I ran into quickly:

IDENTITY columns will prevent inserts.  What I did to get around this was create a "staging" table with the same structure (and an identity column), and add a trigger that just inserts into the partitioned view and deletes from the staging table.  At some point I will look into using SQL 2012's SEQUENCEs
You can't run an update/delete query on the PV if it has a self-join on the PV or any of its tables
All columns have to be specified in an INSERT statement.  DEFAULT doesn't work or help.  You can't omit nullable columns or columns with a default value.
If you use replication, it tends to disable the constraints (NOT FOR REPLICATION), so you'll have to make sure that doesn't happen

You can see here at the bottom: http://technet.microsoft.com/en-us/library/aa933141(v=sql.80).aspx


When is SELECT necessary in PL/pgSQL functions?

This is only an educated guess.

It seems that the SELECT is unnecessary because it's already present, e.g. in the implicit SELECT ... INTO that an assignment := is equivalent to. From the documentation:

  An assignment of a value to a PL/pgSQL variable is written as:

  variable { := | = } expression;

  As explained previously, the expression in such a statement is
  evaluated by means of an SQL SELECT command sent to the main database
  engine.

An example may be shown here, where:

name := (SELECT t.name from test_table t where t.id = x);

can also be written as

name := t.name from test_table t where t.id = x;

This seems possible because the second simplified form is equivalent to a SELECT INTO in PL/pgSQL:

SELECT test_table.name INTO name FROM test_table WHERE id = x;

The first form would be equivalent to:

SELECT (SELECT t.name from test_table t where t.id = x) INTO name;

, which has a redundant layer of SELECT that can be removed.

On the other hand, when an implicit SELECT is not present, an explicit SELECT seems necessary. For example, the same function cannot be used directly (i.e. without SELECT)

string_to_array(my_str,'_');

if it's not in an assignment to a variable.


MySQL trigger after insert with insert and delete instructions does not work properly

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this:

ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

You are probably using MyISAM for your table. The DELETE does not cause a rollback, because the engine is not transactional. If you switch to InnoDB (which is usually the best choice) you will notice that the trigger and the original INSERT will completely fail.


What is SQL Server Parallel Data Warehouse?

It isn't inexpensive. I don't know the price tag but you are talking large capital expense measured in hundreds of thousands of dollars. It is an enterprise scale solution that is optimized for large scale enterprise data warehouses. As such, you may find it tough to get a great answer here other than things like the link I provide below.

You said you already did the research, so I won't provide you too many links other than the this one which does a great job explaining PDW.

If you have the budget and enterprise scale that requires PDW, you would be much better off talking to Microsoft and asking them for discussions with reference customers. This right now isn't deployed en masse, so you will get individualized attention if you are in the market for a massively parallel data warehousing appliance.


Two servers cannot give you as much HA as three.  And the three need to be geographically spread across 3 data centers.  That way, if a datacenter fails (and they do), the other two have a quorum and can continue running.

If you have already implemented your application using InnoDB, then the simplest is to go with 02: InnoDB Cluster.  Yes, it can have one active and multiple passive nodes.  I don't think there are $$ licenses for 01 and 02.

Another option is (again, no $$ license) is to have 3+ servers using InnoDB, plus Galera's clustering solution.  (Or MariaDB, which has Galera builtin.)


SQL Server takes an hour to fail over to secondary node

First, I want to address a few things.

  Each node has it's own storage, nodes are 32 core with 32 GB memory (don't ask me why it is configured this way). The storage at each node is a read only copy when the node is secondary. Until today I was told that it was shared storage. ... He then explained that it does have it's own storage at each site but it is not active on the secondary node. Blocks of data are replicated between the primary and secondary nodes.

This is block replicated storage and we have no idea how or what is doing it. It could be done through software, it could be done through hardware, it could be a combination of both.

  I spoke with the sys admin about it because the quorum has a file share witness not a disk witness.

There is nothing wrong with having a FSW. It's also the most common witness type.

Now to methodology:

  What's happening is that the host is failing over (unclear as to why), the most likely culprit is heartbeat timeout.

The cluster logs will tell you why. Generate and go through the cluster log to understand why the failover took place. If, for example, the root cause was failed heartbeats then it would require extra packet logging on each node to understand why the heartbeats were failing.

  When the nodes fail over SQL Logs that it was sent a shutdown command.

Yes, so you know the time this was given in the SQL Server errorlog. Thus, the issue had to happen before this in the Cluster Log. This should start your timeline of events which will ultimately lead to the overall time it took for SQL Server to come up.

  Sometimes the second (SQL) node comes up within a minute or so.

We need you to define, "comes up".

It's ambiguous whether you mean that the SQL Server service was attempted to be started by the cluster or the database you want to be online and accessible for user queries is online and accessible. I don't know to which you are referring.

In either case, if it's the cluster to bring the SQL Server resource online (service) then that is all in the cluster log of the node that now owns the resource group. It should be trivial to find an online call to the resource as you can quickly search for the name in the log. If it's for the database to be accessible, then you can look at the SQL Server errorlog and see how long it took for the startup messages and the recovery messages. Both should be trivial and can be added to your timeline of events and times.

  Other times it takes an hour for SQL Server to come online on the secondary node. On the OS Side the failover is quick, it's just SQL Server that takes and hour.

Again, I'm unsure what you mean by the "OS Side". The call to online the resource is successful and SQL Server starts up but the database isn't accessible or the resource group moves over but the resources aren't started in a timely manner?

  I have not found any logs which indicate anything like this though.

AFAIK there is no log that says why a database took a long time to start up. You need to create a timeline of events and extract the data from the multiple sources to build said timeline. Then you can piece together what happened and about how much time each step takes. You can focus in on the long steps and break them down into their individual pieces.

For example, when I do an RCA for these events I start with:

When did the originating issue occur and what was it?
When was SQL Server affected and how did it affect it?
Let's assume, there was a failover.
When did the failover occur on the previous owning node?
when did the resource group move over to the next owning node (if not that last one to own it)?
When was the call to online the SQL Server resource first successful and were there any unsuccessful attempts before that?
When did SQL Server log it started the service?
What order were the databases started and at what time?
When did each database finish the recovery phases and how long did that take?
When was the first successful user connection?
Were there any other notable errors, warnings, or informational messages in the logs?

Once you have that overall timeline you can find the pieces that took the longest and investigate further from there.


Solution to your problem is below :-

On line 115 there is a line that says

removeIO

Add this after removeIO:

# Remove annoying warning message since MySQL 5.6
if [[ -s "$log_errfile" ]]; then
sedtmpfile="/tmp/$(basename $0).$$.tmp"
grep -v "Warning: Using a password on the command line interface can be insecure." "$log_errfile" > $sedtmpfile
mv $sedtmpfile $log_errfile
fi

And you will receive no more daily emails regarding that it is unsafe to use passwords on the command line.

Referred Link:

http://www.redeo.nl/2013/11/automysqlbackup-warning-using-password-command-line-interface-can-insecure/

But I don't know if this is the cleanest solution.

To make this solution work for MySQL 5.7+, the grep statement needs to be modified to take account of the altered warning message:

grep -v ".*: \[Warning\] Using a password on the command line interface can be insecure\." "$log_errfile" > $sedtmpfile


Why does pg_dumpall use the wrong encoding in Ubuntu 16.04 / Postgres 9.5?

Set the environment variable PGCLIENTENCODING to UTF8, then the dump will be created in UTF-8.


Reasons to maintain FKs on an empty, deprecated table?

The FK would still need to check with the empty table to see if the key was used on a delete operation.  But this would be a quick check and never deny if the table is empty.


You need to include the aggregate operation to get the sum of population.  So:

SELECT Province, SUM(Population) AS PopulationSum FROM CANADA GROUP BY Province

In english it would be, I want the sum of the population column, by province from the Canada table.


MariaDB mysqldump lacks all data - how do I force including it?

Before posting my question I did just a few more tweaks to see what might be going wrong.  I may have stumbled into an answer.  So I posted the question anyway and I'm going to post a potential answer here in hopes that it helps the next person.

The -d option in the second row I thought was short for --databases, but it appears to do something else.  I don't see it in the docs (https://mariadb.com/kb/en/library/mysqldump/) but when I changed that to --databases I started getting results with the INSERT INTO statements.

So, instead of:

/usr/bin/mysqldump -u root -p -d databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Use:

/usr/bin/mysqldump -u root -p --databases databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Or (without naming the option at all):

/usr/bin/mysqldump -u root -p databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql


It depends on the similarities between the two entities in the context of the business logic. If they share all the same attributes (columns) except for the fact one is a business and one is a person you theoretically can store them as the same entity in a table like Customer with just a column to distinguish between the two such as a boolean based field IsBusiness.
But in most cases, developers will find that there are unique attributes to one or the other that they need to store as well, and will normalize into separate tables.
Having more context on exactly what you expect to store, even such an example diagram of what you've thought about so far, may help in providing a more specific and concrete answer.


Sybase Database Migration from 11.5 to 15

Sybase ASE does not support a direct upgrade from version 11.5 to version 15, so you will have to export your database from the ASE 11.5, and import it to ASE 15.

From the $SYBASE/$SYBASE_ASE/scripts/ directory in your ASE 15 installation, run the installupgrade file into your ASE 11.5 installation, to install some utility stored procedures to assist with the migration.

isql -Usa -Ppassword -SOLD_SERVER_NAME -iinstallupgrade

Now login to each database, and run the sp_checkreswords command to make sure none of your DDL is using words reserved for the system.  If reserved words are found, you will need to alter the code, and remove/change the reserved words.

Use the ddlgen utility to export your full database structure (Tables, indexes, stored procedures, defaults, user defined datatypes, etc.)

At this time, you may want to review the code generated by ddlgen, to make sure it makes sense in your new environment.  You may want to make changes to device locations, device names and device sizes.  For example, ASE versions prior to 12.5 had a device size max of 2 Gb, so if your database was 10 Gb, you were forced to have 5 devices.  ASE 15 will allow device sizes of up to 1 Tb (on a server with a 2k page size), so you would only need one device to store the entire database.

You may also want to seperate the index and trigger creation scripts from the rest of the DDL, to allow you to run those independantly.  This comes in handy when you import data into the new server.

Once you are satisfied with the scripts, you can run them against the new server, and it will create all of your database structure on the new server.

At this point it's a matter of transitioning the database, using the bcp utility, you will need to export your data on a Table by Table basis.  Depending on the size of your databases, and the number of tables in each database, this can take a while.  I would recommend trying the -n (native) version of bcp instead of the -c (character) if it's possible.

To import the data into your new database, you will also want to use the bcp utility, but you will likely want to make sure that your databases do not have triggers or indexes as this will enable the 'unlogged' version of bcp to run, allowing it to run faster and not fill the transaction log as it runs.  If you can not run the unlogged bcp then you will want to make sure you have the appropriate database options ('truncate log on checkpoint', 'abort tran on log full') and set the batch size for your import to a reasonable value.

At this point, you can create in indexes and triggers (if they haven't been created up to this point), and start testing the server.

I would also recommend reviewing Sybase's Migration Guide, as it will likely go more in depth, and cover things I may have forgotten/skipped over.  A lot of the details will depend on the migration environement, so planning and preparation are key.


Incorrect syntax error when creating view

Seems pretty simple, you have a long dash (–) where you meant to have a minus sign (-). The difference is subtle to the human eye, but not to SQL Server.


How to get the spatial index in CREATE TO script?

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately.  This can be done to the clipboard and pasted to the query window generated by the script table as command.

I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options since the are an integral part of the table definition, whereas indexes are not.

The generate and publish scripts wizard can also be used, but it is a lot of steps if you are just doing a few items.
Technet, How to: Generate a Script


You could try RTF(ine)Manual. Start here. This line is worthy of attention in your case "Berkeley DB has no notion of schema and data types in the way that relational systems do".


How to maintain trees and their roots in SQLite

Finding all nodes in a subtree to update them requires a recursive common table expression:

-- update the root of all nodes in 'a's subtree to 'x'
WITH RECURSIVE subtree(id) AS (
    VALUES('a')
    UNION ALL
    SELECT Node.id
    FROM Node
    JOIN subtree ON Node.parent = subtree.id
)
UPDATE Node
SET root = 'x'
WHERE id IN subtree;

In SQLite, CTEs are not allowed in triggers.


Why is this rollback needed when using sp_addextendedproperty in a stored procedure?

This line..

SELECT OBJECT_DEFINITION(OBJECT_ID('sp_addextendedproperty'))

...shows that sp_addextendedproperty has 2 x COMMIT. This won't be a problem if the transaction can be COMMITed (i.e. isn't "doomed") but will leave a transaction open otherwise.

This could well be a bug to be reported on MS Connect

A catch block like this will show if the transaction is doomed

begin catch

SELECT XACT_STATE() -- -1 = doomed, 0 = none, 1 = commit or rollback

    SET @error = ERROR_NUMBER();

    if @error = 15135
        Print 'Table missing no extended properties added: ' + @table_name;
    else if @error = 15233
        Print 'Table has already property: ' + @table_name + ' - ' + @property_name;
    else
        Print 'unexpected error ' + str(@error);

end catch

I've not seen this (an open txn) at work (still SQL Server 2005 SP3) and got the details from a local SQL Server 2008 on a VM. Could be version related.

Edit: just seen the Q update

Using SET XACT_ABORT ON will force a rollback anyway


index creation - Slow Query - PostgreSQL 9.2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.:
AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'

Alternatively, create the index without typecasting:
CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path);

Note also that the character / in regexp has a special meaning, and although ~ can deal with it unescaped (because it is implied), other regexp functions (such as regexp_replace or regexp_matches) will likely fail, so you may want to always escape them, as a general practice:
AND (s.full_path)::text ~ '\/userfiles\/account\/[0-9]+\/[a-z]+\/[0-9]+'

In any case, regex expressions may not benefit from a present BTREE index, unless they're anchored:
AND (s.full_path)::text ~ '^\/userfiles\/account\/[0-9]+\/[a-z]+\/[0-9]+'

The query may still not use the index if the regex comparison is anchored, when it considers that the results would not actually filter much (from collected statistics) and a sequential scan would perform better, e.g. when most of the rows in that table actually start with /userfiles/account/.
Edit - optimising the query
Your query is not optimised for large sets. Firstly, most of you filtering is based on a non-indexed comparison (split_part(full_path, '/', 4)::INT = 12115). Secondly, you apply the filtering condition to the results of the join; PostgreSQL is often smart enough to translate the filter to the subquery, but it is best to construct your queries in a way that will ensure that PostgreSQL will the do the right thing.
For example, filter by account_id directly from full_path:
CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); -- ensure your index is not typecasted

SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/12115/[a-z]+/[0-9]+' -- filter account directly into string comparison
  AND i.st_size > 0;

Alternatively, you can create a function index by the account_id part of full_path:
-- This index is both useful to filter any rows that do not conform to the below full_path formatting
-- and to search within them for a numeric account_id
-- (note: the index would only be used if the exact same regex comparison is performed on full_path when searching for an account_id)
CREATE INDEX CONCURRENTLY ix_account_id_from_full_path ON gorfs.inode_segments USING btree ((split_part(full_path, '/', 4)::INT))
WHERE (full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+');

SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE split_part(s.full_path, '/', 4)::INT = 12115
  AND i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0;


T-SQL View -- How to 'pre-fetch' schema using scalar function, then populate using table query

I'm wondering if these views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO to populate data into the view.

No, this isn't possible with a view. You could do something broadly along these lines with a multi-statement function (MSUDF), but:

This would still require a static schema definition for the table variable; and
Careful design would be required to avoid terrible performance; and
All potential base query predicates must be supplied as optional parameters

A multi-statement function would potentially materialize the entire 'view' result set in a table variable, before any predicates in the base query were applied. If the result is large, this overhead will likely be prohibitive.

You could push predicates into the MSUDF using parameters, but then the function body becomes a mess of conditional predicates of the form column = @value OR @value IS NULL, which would require OPTION (RECOMPILE) in the MSUDF to optimize well.

Note that even with the best possible outcome, the users of the view will have to change their queries from view references to MSUDF references (with all parameters specified, DEFAULT or NULL passed for those that are not needed). This may not be workable.

All that said, you need to be certain what is causing the performance problem in the first place. The question does not supply an example of a problematic query plan, so some of what follows is educated guesswork:

There are two features of the current scheme that leap out at me:

The use of ISNULL and calculated columns prevents the optimizer from pushing base-query predicates down into the underlying in-line functions and base tables. You might get better results for the ISNULL part by rewriting the LEFT JOIN as a UNION ALL of an inner join and an anti-semi-join of the two tables. Properly written, this would expose only base column names, allowing the optimizer to push predicates successfully.
The in-line functions use an MSUDF to split the supplied CSV into row values. This is a pattern that often causes optimization problems, because the size and distribution of the result is unknown. A query plan that is optimal for a CSV of '1,2,3' is likely not optimal for a NULL CSV. Again OPTION (RECOMPILE) could help with this. It will simplify the compiled logic for the NULL case, avoid parameter-sniffing issues, as well as providing cardinality information for the MSUDF result. Distribution statistics will still not be available though.


preserve generate_series rows through joins

If you want the COUNT of each element of the series you can use an scalar subquery.

WITH gs AS
(
    SELECT generate_series(NOW() - '72 hours'::interval, NOW(), '1 hour') AS t
)
SELECT gs.t, (SELECT COUNT(packets.id)
              FROM   packets
              JOIN   devices
              ON     devices.id = packets.device_id
              WHERE  devices.mandate_id = 3
              AND    packets.transceived_at > gs.t
              AND    packets.transceived_at <= gs.t + '1 hour')
FROM   gs;


Does SQL Server Date Correlation Optimization only work on DateTime fields, or will a Date work, too?

Yes, it works for both date and datetime columns. The experiment is rather simple - create a database with the option on, create tables with both types, insert data that satisfies a correlation pattern (like all dates in one table are after the dates in the other table), run some queries that exploit that pattern, and then observe the auto-created stats are the same for both sets of tables.

Create the database and turn on the option:

USE master;
GO
CREATE DATABASE splut;
GO
ALTER DATABASE splut SET DATE_CORRELATION_OPTIMIZATION ON;
GO
USE splut;
GO

Create two tables with correlated date columns, and two tables with correlated datetime columns:

CREATE TABLE dbo.d1(id int primary key, d date);
CREATE TABLE dbo.d2(id int not null foreign key references dbo.d1(id), d date);
CREATE TABLE dbo.d3(id int primary key, d datetime);
CREATE TABLE dbo.d4(id int not null foreign key references dbo.d3(id), d datetime);
GO

Insert some correlated data:

INSERT dbo.d1(id, d) SELECT DISTINCT ABS(object_id),'20190101' FROM sys.all_objects;
INSERT dbo.d2(id, d) SELECT id,DATEADD(DAY, ABS(id)%20, '20190101') FROM dbo.d1;
INSERT dbo.d3(id, d) SELECT DISTINCT ABS(object_id),'20190101' FROM sys.all_objects;
INSERT dbo.d4(id, d) SELECT id,DATEADD(DAY, ABS(id)%20, '20190101') FROM dbo.d3;

Now run a query that might benefit from correlation statistics (though, really, any join will do):

SELECT TOP (10) * FROM dbo.d1 INNER JOIN dbo.d2 ON d1.d = d2.d;
SELECT TOP (10) * FROM dbo.d3 INNER JOIN dbo.d4 ON d3.d = d4.d;

Now, check the stats:

SELECT OBJECT_NAME(s.object_id), s.name, s.stats_id,
   sc.stats_column_id, c.name, c.system_type_id, t.name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND sc.column_id = c.column_id
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.auto_created = 1 AND OBJECT_NAME(s.object_id) IN (N'd1',N'd2',N'd3',N'd4');

Results on my machine (your stats names will vary) show that stats are created both on (id, date/datetime column of parent) and (date/datetime column of child):

I'll leave it as an exercise for the reader if this holds true for time, datetime2, and datetimeoffset, or if it will also work if one column is date and the other is datetime.


Installing a SQL Server patch on a node within a 2-Node FCI

The way we go about SQL patching and windows updates for a 2 node cluster is.

In Failover Cluster Manager, remove the node you want to patch from the Possible owners (makes sure it cant failover to this node until you are ready)
Patch the inactive node
Re-add the node to the Possible owners
Failover to the inactive node
Leave this running for a day or two to make sure there are no issues
Remove the first node from the Possible owners (now the inactive node)
Patch the now inactive node
Re-add the node to the Possible owners
Fail-back

As long as your inactive node does not have the Quorum or Cluster Group patching or rebooing the inactive node should have zero impact.

The easiest way to make sure the node you want to patch does not own anything is using Powershell.

Open Powershell
Run the following command "import-module failoverclusters"
Then run "get-clustergroup"

The output will show you what resources you have in the cluster and what node the currently live on.

Here is an article from MS on the topcic, https://support.microsoft.com/en-au/kb/958734.


MySQL: can I put the database into a maintenance mode? (i.e. no user connections)

As said by Brad Silva, you can skip connections, but you need to restart the mysql server anyway ( with --skip-networking ), as skip_networking is a read only variable.

When finished, restart mysql without this parameter.

EDIT: When I say it's a read-only variable, I mean you cannot simply set it in the console, like other variables you can switch at runtime. This requires a restart, unless I'm wrong.


The easiest way is to create the needed trace from the SQL Profiler tool and then save it as SQL script. Put this saved script inside an sql job, as a step, and schedule it for once daily run (at midnight). Don't forget to set your trace to save to files somewhere. You will have daily trace saved that you'll be able to select by queries or manually.

You can find some details in the answer of this question: Using SQL Profiler on a database that's in production, especially the article of automating server side tracing.


SQL Server Demo Environment on Docker Desktop (Win 10): DB storage und upgrade paths

Performance of SQL Server using shares to host data files is almost always poor, regardless of the underlying storage subsystem. You should be mounting a host directory as a volume in your docker container to get better performance. See this link for more information.


Strategy to deal with multi billion row simulation results and partitioning

We were doing something not unlike this with clickstream data.  We unplugged SQLServer and plugged in Vertica (a columnar analytics ANSI-SQL compliant RDBMS)...we've never looked back.  Multi-minute queries dropped to millsecond queries, and data loads dropped from hours to seconds. If/when you start outgrowing it, add more nodes and rebalance online. Very nicely done product.

The Community Edition is free for 3 nodes and 1Tb of data (which gets compressed a LOT more than you would expect, so 1Tb is quite a lot of data), and the commercial version (unlimited nodes) is about half the cost of SQLServer EE for TB-size data (it's licensed by data size).  You might want to give it a look. ;-)

Btw, I have no affiliation with Vertica or HP, I'm just a very pleased customer/data architect.

Cheers,
Dave Sisk


Is it free to download an Oracle Enterprise Edition image from Docker?

Try downloading the official (example?) Docker image from Oracle.
Oracle has put many things on GitHub. Docker images are one of them.
https://github.com/oracle/docker-images/tree/master/OracleDatabase

Licensing questions are off topic for this site. But (AFAIK), for educational purpose only, you can use EE for free.


You can do a simple GROUP BY with DATEPART applied to the time column. I'm assuming that you'll always get a row every 15 minutes and you don't need to worry about gaps in the data.

SELECT
  code
, date_column AS [date]
, DATEPART(HOUR, time_column) AS hourly
, SUM(value) AS value
FROM test_table
GROUP BY
  code
, date_column
, DATEPART(HOUR, time_column);

The following references may be useful to you:

DATEPART (Transact-SQL)
GROUP BY (Transact-SQL)
SUM (Transact-SQL)


MySQL running delete trigger on truncate

TRUNCATE does not fire ON DELETE triggers in any 5.x version. But it does have to lock the table and have to wait for any other open transactions that hold locks on the table to release them.

That's what the process list is showing. There is a (transaction with a) delete query that is running and has some lock on the table and is blocking the TRUNCATE (which is Waiting for table metadata lock). As soon as the other transaction commits (or rolls back) and the lock is released, TRUNCATE will manage to get the metadata lock and continue.

There are some differences across the minor 5.x versions regarding the existence of any foreign key constraints that reference the table that affect how TRUNCATE is processed. See this question “TRUNCATE doesn't run ON DELETE trigger” true or false for details but this seems irrelevant in your case, since you are on 5.5, where the TRUNCATE would raise an error if there were any foreign key references from other tables.

On relevant matters, you should upgrade to the latest 5.5 version (5.5.49). Your servers are 4 years and 22 releases behind the latest 5.5 version.

Since you edited that the staging server is 5.5.49 (that's good!) it would be best to update your dev servers to the same version.

I wouldn't entirely dismiss the possibility of a bug but we'll need more evidence to be convinced, like the output of SHOW ENGINE INNODB STATUS; when the issue appears (the Transactions part would show relevant info about locks and blocking).


How to Install postgresql-client-11 on Ubuntu 18.04

Install it from PostgreSQL's own apt repository, adapted from these instructions.

sudo apt-get install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt install postgresql-client-11
pg_basebackup -V

  pg_basebackup (PostgreSQL) 11.7 (Ubuntu 11.7-1.pgdg18.04+1)


I have 2 parent tables with 1 child and I want to perform a query that involves both parents

Use aromareference table to fetch all referenced records from both tables, hops and hopsdescriptors.

Then filter records by hops.HumanName (or whatever field of hops or hopsdecriptors), and show the required columns.

SELECT hd.HumanName
FROM aromareference a
     INNER JOIN hops h ON a.HopsId = h.ProductId
     INNER JOIN hopsdescriptors hd ON a.DescriptorID = hd.DescriptorID
WHERE h.HumanName = 'Centennial'


You can't move the column without copying the data. There's one rather exotic idea to pull this of: You move all other columns and leave the filestream column in place.

Depending on the number of rows and schema changes required this can be a lot faster (or slower). You might have to completely gut some tables and create complicated scripts. I only would do this approach if you really need to avoid this downtime.


Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints.

By the YAGNI principle, you should only code for "real-life" current requirements - a primary key that may (or may not) arrive in 5 years is not worth considering now!

Surrogate keys are very widely used in practice, much to the horror of purists: the rest of us just use them! See this Stack Overflow answer (as well as the rest of the thread).


Incorrect string value: '\\xD0\\xA2\\xD0\\x9E\\xD0\\x9E...' for column Username in MySQL DataGrip specific

A bit strange case, but I'll leave the answer we've came up to ourselves:

Fixed after I've used NVARCHAR (or VARCHAR CHARACTER SET UTF8;), not VARCHAR for variable declaration and for temporary table's creation scheme. The problem appeared ONLY when we used temp table in the script. For regular tables our VARCHAR with default DB collation/charset worked OK.
I still don't know why DBForge ignored it and calling procedure
created in it caused no problems (but there really were problems: "?"
instead of UTF8 characters).

UPDATE: added issue on DataGrips tracker, because I think that failing to execute the procedure is not the correct behavior in such case: https://youtrack.jetbrains.com/issue/DBE-11789


Msg 7391, Distributed Transactions (DTC) on SQL Server

I would imagine its either:

EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

  Use this option to protect the actions of a server-to-server procedure
  through a Microsoft Distributed Transaction Coordinator (MS DTC)
  transaction. When this option is TRUE (or ON) calling a remote stored
  procedure starts a distributed transaction and enlists the transaction
  with MS DTC.

Or could be an issue with MSDTC configuration, which is a laborious and lengthy checklist to run through, documented fully in Recommended MSDTC settings for using Distributed Transactions in SQL Server.


Here is 24 hours

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

or

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400);

Here is 48 hours

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY);

or

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 48 HOUR);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 2));

Here is 30 days

SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY);

or your way

SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 30));

Your last expression WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400) is feasible, but requires you to do the number of seconds difference. My additional suggestions lets MySQL do the date computation.


Business Continuity Plan for web application and database

Business Continuity can be a complicated beast and you'll need to involve affected departments/users to make sure everyone knows the 'plan'.  The main two things you'll tend to see over and over are: RTO (Recovery Time Objective) and RPO (Recovery Point Objective)

RTO basically is the maximum time allowed to get things back to 'normal' or at least functioning.  You would need to take into consideration small problems (server went down) to big problems (the building just exploded).

RPO basically is, how much data could we 'possibly' lose should a bad problem happen?  5 minutes, 1 hour, 1 day, NONE?

There are lots of good stuff on the Internet about this topic - I think it would be time well spent doing some internet searches for IT Business Continuity Plan.

Here are a couple of good articles right off the bat:

http://www.cio.com/article/2381021/best-practices/how-to-create-an-effective-business-continuity-plan.html

https://www.brentozar.com/archive/2014/05/new-high-availability-planning-worksheet/

https://www.brentozar.com/archive/2011/12/letters-that-get-dbas-fired/

https://www.brentozar.com/archive/2015/09/why-rpo-and-rto-are-actually-performance-metrics-too/


Postgres 12: declare a function as constant or immutable ignoring parameters?

Declare the no-op version as an SQL function then it will be in-lined as a constant value by the optimiser.

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE sql AS
$inner$
SELECT 0;
$inner$;


DELETE without RETURNING doesn't return a result set. So you can't use fetch... calls with it, like cursor.fetchall().

The affected row count is stored in cursor.rowcount for DML.


Strategy for making Postgres support multiple versions of client code

I cannot immediately see a problem with your idea, except that you have to change all code to use functions instead of data modifying SQL statements.

I can think of an alternative as follows: When you install your schema changes, also create a number of views that look just like the old tables and “do the right thing”:

SELECTs on the view return the same data you would get in the old version.
The views have triggers for INSERT, UPDATE and DELETE that perform the correct actions on the underlying tables.

Since the names of the views will collide with the table names, place the views in a different schema. You could pick a schema name based on the application version, and you can use search_path to assign different default schemas to the two application versions.

Once all clients are using the new version, you can drop the schema with the views.


Using an RDBMS for querying tens of Terabytes of time-series data?

Answering this from one point of view: SQL Server 2012 columnstores could easily handle this. I've seen it working and once segment elimination and batch processing kick in, those TBs are reduced to very few actual IOs an the results get churned in few ms (ie. most data is eliminated upfront and there is simply no need to scan 100s of TBs). The queries you asked about are exactly the kind columnstores are designed for. This is such an efficient storage/processing paradigm that there's simply no need for distributed computing, even at hundreds of TB of data.


what is the password to user mongodb when mongodb is installed in ubuntu?

Launch Shell as User:

If all else fails, on Ubuntu 14.04 LTS running on AWS, try this...

me@local $ ssh -i aws.pem ubuntu@foo
ubuntu@foo $ sudo -u mongodb bash   # OR whatever shell (zsh)
mongodb@foo $ mongod --dbpath /data/db --fork

OF COURSE, you'll add a much larger set of flags to the command. Note also that this assumes you're authenticated as ubuntu on an AWS EC2 instance. The first portion of each line to the left of the $ is the user@hostname

Invoke Command as User:

YMMV but this should work too...

mongodb@foo $ sudo -u mongodb mongod --dbpath /data/db --fork


Why is SSMS showing red squigglies(sp?) even though the table exists?

In short, the tables are not recognized because the IntelliSense local cache is not up to date.

While you are in the Query Editor window you can press CTRL + SHIFT + R to refresh the cache. This should update IntelliSense's cache and the red squiggles should disappear.

Please see this link for more information.


From the INSTALL document

                                Short Version

./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

So,

initdb initialises the system after the binaries have been put in place, either through an rpm or a source compile.

postgres launches the server (which is what you're trying to do)

createdb xyz creates a database of name xyz

psql is the command-line client

You could try

./bin/pg_ctl -D ./data/ -l logfile start

On running initdb, you should have received a message

===========================

Success. You can now start the database server using:

./bin/postgres -D ./data/

or

./bin/pg_ctl -D ./data/ -l logfile start

===========================

What I can suggest to you is to run the pg_ctl option - it's the one that I use, for what it's worth.

You appear to have confused the server command with a database access command. You should run psql foobar to access the foobar database, provided the server is already running - see above how to do that.


mysql master-slave replication and status of data consistency

Before I address your question, some terminology issues...

GTID uniquely identifies each thing replicated.  This is an improvement on the previous Replication technology -- but mostly when you have multiple machines and need to recover from some kind of failure.  It helps automate things.

bin_log_format = STATEMENT or ROW or MIXED refers to what is transferred from Master to Slave.  STATEMENT (SBR) is the old way; ROW (RBR) is the new way.  There are many subtle reasons why RBR is 'better'.

SHOW SLAVE STATUS provides, among other things, whether or not replication has stopped due to some error.  It reports a crude measure of staleness (Seconds_Behind_Master), but not "consistency" (whether the changes arrived and were correctly applied).

You do not need a checksum on each row.  It would be clumsy for you to add such.  The network layer (TCP/IP, etc) does checksumming to 'guarantee' delivery and to validate the contents of the delivery.

If you want to do your own consistency checking, I recommend Percona's pt-table-checksum .  It takes care of a lot of end-cases that are not obvious to someone starting out to do the checking himself.


TempDB Configuration using 8 TempDB Data File

Check these articles for some recommendation.

https://cloudblogs.microsoft.com/dynamics365/no-audience/2015/09/25/ax-performance-analyzing-key-sql-server-configuration-and-database-settings/

https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/configure-sql-server-and-storage-settings

Also, you could check for tempdb auto grow events, if there are, you could enable IFI, also check if Lock Pages in Memory is enabled.


Generally, you shouldn't be using the Posix/ or Etc/ timezones if they're on your system. This isn't a PostgreSQL thing, it's your distributions libc database (sometimes packaged as tzdata/zoneinfo) for the internal timezone functions. Most of that POSIX stuff is nasty and old. PostgreSQL has a document on it which mentions it.

I guess to answer the question specifically,

  Two different versions are provided: - The "posix" version is based on the Coordinated Universal Time (UTC). - The "right" version is based on the International Atomic Time (TAI), and it includes the leap seconds.

There really is no reason that I know of not to use the IANA names.


Why is BULK INSERT Considered Dangerous?

Given that there are probably just as many unfounded fears as there are unknown risks, I would think that it is difficult to really say why a policy is in place without asking whomever created the policy why they are concerned.

However, I would guess that it probably has something to do with what BULK INSERT / SqlBulkCopy / BCP / OPENROWSET(BULK ...) allow someone to do, namely:

disable constraints (CHECK, DEFAULT, and FOREIGN KEY I believe)
disable triggers (if there are audit triggers in place, by-passing them would probably be deemed undesirable; for more explanation on this particular issue, please see @DVKs answer)

The various options are described in the following documentation:

SqlBulkCopyOptions Enum
BULK INSERT

I did not mention the table locking issue noted by @RDFozz since that is not specific to BULK INSERT: anyone can table a TABLOCK / XLOCK or set the TRANSACTION ISOLATION LEVEL to SERIALIZABLE.

UPDATE

I came across two additional pieces of information that might help narrow this down:

The issues of being able to disable Triggers, disable Constraints, and set IDENTITY_INSERT ON might not be an overwhelming reason to see ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS (starting with SQL Server 2017), or the bulkadmin server role as a threat. The reason is that in order to do any of those three things just mentioned, the User needs to have ALTER TABLE permissions on that Table, or on the Schema that the Table exists in. Ownership chaining does not cover DDL modifications. So, if the User doesn't have ALTER TABLE, then the ability to do these three things is a non-issue.
What hasn't been discussed so far, and what might ultimately be the security issue is that both BULK INSERT and OPENROWSET(BULK... access external resources, outside of SQL Server. When accessing SQL Server via a Windows Login, that Windows account will be impersonated (even if you switch the security context using EXECUTE AS LOGIN='...') for doing the file system access. This means that you can only read files that you have been given permission to read. Nothing wrong with that.

BUT, when accessing SQL Server via a SQL Server Login, then the external access is done in the context of the SQL Server service account. This means that someone with this permission could read files that they should otherwise not be able to read, and in folders that they should not have access to.

At minimum, if SQL Server was set up to run as an account created just for SQL Server (the preferred method), then such a User could read only those files that the "SQL Server" account has access to. While this is a limited problem, it still allows for reading files such as SQL Server log files (and I did test the following example and it does work):

SELECT tmp.[Col1]
FROM   OPENROWSET(BULK
   N'C:\Program Files\Microsoft SQL Server\MSSQLxx.InstanceName\MSSQL\Log\ERRORLOG.1',
                  SINGLE_NCLOB) tmp([Col1]);

Most people won't have access to the MSSQL\Log folder, so this would be a way to circumvent existing security restrictions.

And, if SQL Server is running as the Local System account, then I suspect that the scope of the problem only increases, and that a User with this permission would be able to read a wide range of system-related files.

AND, this is likely why the other methods of doing bulk imports — BCP and SqlBulkCopy — do not require the bulkadmin permission / role: they are initiated outside of SQL Server and will handle file system permissions on their own. In those cases, SQL Server never reads the file (or reaches outside of SQL Server), it just receives the data to import from the file that is being read by the external process.

Possible implications aside, it was said in the question:

  For an application's benefit, BULK INSERT is far more efficient, faster,..

Ok, go on...

  and relieves the programmer of the need to parse files outside of SQL.

Whoa Nelly. Let's stop right here. T-SQL is usually not the best choice of languages for parsing. It is often best to do the parsing before inserting stuff into the DB. One way to do this is to use Table-Valued Parameters (TVPs). Please see my answer to another question (here on DBA.StackExchange) that deals with topic of pre-parsing and validation along with efficient bulk importing of said data:

T-SQL: CSV->table pipeline with custom parsed numeric data, lookup values


On which machine we should install WAL-G?

As WAL-G helps you both pushing and pulling backups and WAL to and from a storage, you have to install it on both the master and replica.
You can consider using a ready-made image, like Spilo, which would solve these issues for you.
Finally, there is official Patroni support on GitHub, you will get answers way faster there.


Does a dual key or single key lookup perform better on a table with a lot of data?

I don't think it would really matter.  You can cluster on both without concatenating them:

CREATE CLUSTERED INDEX ixc_MyIndexName ON MyTable (GroupA, GroupB)

Which will accomplish the same thing but not require you to modify your data.


You're getting a trivial and simple parameterized plan for your simple query.

Add AND 1 = (SELECT 1); to the end of your query, and you'll get the constant scan/contradiction detection.

Using just AND 1 = 1 doesn't always work if the aim is to also avoid trivial plans. See Query Plans: Trivial Optimization vs Simple Parameterization by Erik Darling.

You should also get into the habit of being careful about data types. A string is not a date, and there is no special T-SQL syntax to specify a date literal. You can use CONVERT to be explicit, e.g. SalesDate > CONVERT(date, '20160101', 112).

Related reading: Why Doesn’t Partition Elimination Work? by Paul White.