Can any one Explain me What is the Use of Cascading Replication in Postgresql?

If Your application can get data from more than one database You can use cascading replication for load balancing(One App get data from multiple bases) but only in case when it's synchronous replication(in case of cascading rep. it's heavy).

In case of M1 fail You can do Python script that will place S1 as master(including updating port, config, and connecting with S2). It's not hard, with Python You can do it in about one hour. Other option is Python script that will restore M1 with data from S1, use Python library: PSYCOPG2


I want to join the below mentioned queries into single query

Is this what you need?

( SELECT ... )
UNION ALL
( SELECT ... )

The GROUP BY on the first SELECT seems to be useless; please explain why you have it.  I am less certain about the second query.

Not sargable: DATEDIFF(NOW(),update_date) > 30; change to A.update_date > CURDATE() - INTERVAL 20 DAY.

Recommend:

A:  INDEX(order_status, pending_order_type, update_date)
C:  INDEX(subscription_id, charge_amount)

When JOINing, please use FROM B JOIN C ON ... instead of FROM B, C WHERE ...

(If you need further discussion, pleas provide SHOW CREATE TABLE.)


That query looks in the procedure cache for a cached plan that contains the text StatRiskPrem_udf.  The result set will contain the following:

The object type that is cached
The cached object name
The amount of times the plan was used
The cached SQL text
The cached query plan

I would guess that the objective is to find out how many times that UDF was referenced.  I wouldn't say that's extremely accurate, though, because the procedure cache is a revolving door, especially under memory pressure.  But that's the gist of the query.


Matching 3 columns in row - if one column is null then exclude it from comparison

Assuming you want all NULLs to count as equal, I think this will work. But I haven't thoroughly tested it, or even thought about it for very long...

 select A,B,C,'Equal' =
    case when COALESCE(A,B,C,0) = COALESCE(B,C,A,0)
          and COALESCE(B,C,A,0) = COALESCE(C,A,B,0)
     then 'Y' else '' end
 from @t

COALESCE will pick the first non-NULL value from its arguments, so when testing A against B and then B against C, in each case we look 'forward' for a non-NULL value, with a fallback for when they're all NULL


archiver stuck after set log_archive_dest_2 to fast recovery area oracle

So you told the primary database, that it should put archivelogs in the FRA, but only when it has the standby role. After forcing log switches a few times, your repeated attempt of forcing a log switch hung, because with your settings, you basically prevented the database from archiving logs, and the database can not switch to a new log file group, because it could not archive any other previous log sequences, so it can not reuse the files.

Here is an example of the parameters needed in the primary database from the documentation:

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_DEST_1 is a local destination that can archive any log in any role.

LOG_ARCHIVE_DEST_2 is a remote destination that points to the standby.

You should have something like this:

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'

Also, here is the whole process of Creating a Physical Standby.


MySql with rollup - not giving the total. What am I doing wrong?

Your query is not actually aggregating anything (there is not any aggregation funcion).  You should just try:

SELECT
    IFNULL(product_name, 'GRAND TOTAL') AS product,
    -- Need an aggregate function on the SELECT
    -- I think you just want to SUM all your revenue
    SUM((oi.item_price - oi.discount_amount) * oi.quantity) AS total_amount
FROM
    order_items oi
    JOIN products p ON p.product_id = oi.product_id
GROUP BY
    product_name
    WITH ROLLUP ;

... which I think is what you're trying to do.

This strange behaviour arises from the fact that MySQL (and MariaDB, for that matter) allows, in versions previous to the current one, for a GROUP BY to use unaggregated columns that are not part of the GROUP BY list. (See Why does MySQL allow “group by” queries WITHOUT aggregate functions?). This is not standard SQL and should best be avoided. This is handled in current version with ONLY_FULL_GROUP_BY.

Check it at dbfiddle here


Shell script for backup binlog files for every 8 hours in MySQL

For those who still need this. May try using binlog puller module by percona folks. It works perfect and you may add a watchdog for it.


Snapshot Isolation vs Read Committed - OLTP and Reporting Databases

Just to add to the other answer.

SQL Server supports two different flavors of READ COMMITTED, legacy locking READ COMMITTED and READ COMMITTED SNAPSHOT.  If you've ever built and supported a high-volume OLTP application on locking READ COMMITTED you know why RCSI was introduced (in addition to making it easy to port Oracle applications to SQL Server).

Locking READ COMMITTED is tricky to get concurrency and is prone to deadlocking, as readers block writers and writers block readers.  Deadlocks are a kind of bug in your application, but they're famously hard to find in testing.  So you have a chance to make a choice that reduces the number of hard-to-find-in-testing bugs.  That's worth a lot.  RCSI also increases the concurrency of your application, enabling you to scale up to use multiple cores more easily.

So, RCSI increases the scalability and reliability of your application, at the cost of a little extra bookkeeping for UPDATES and DELETES (and INSERTS if you have a trigger), and an extra 14 bytes per row.

RCSI is simpler to program to over all, the main thing is that you sometimes need to opt in to a locking read using the UPDLOCK table hint when you want to read a row and immediately update it and need to ensure that no other session does the same thing concurrently.


Stored Procedure return unwanted cursor result

Instead of using a cursor*, write the update and insert operations explicitly as set-based operations:

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
    @sourceInverterID bigint,
    @from datetime2(7)
)
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @RowsAffected integer = 0;

    -- TODO: Add error handling

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

        -- Update existing rows
        UPDATE CID
        SET [Status] = DID.[Status]
        FROM [data].InverterData AS DID
        JOIN cache.InverterData AS CID
                WITH (SNAPSHOT)
            ON CID.InverterID = DID.InverterID
            AND CID.[Timestamp] = DID.[Timestamp]
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from;

        SET @RowsAffected += @@ROWCOUNT;

        -- Insert new rows
        INSERT cache.InverterData
            (InverterID, [Timestamp], [Status])
        SELECT
            DID.InverterID,
            DID.[Timestamp],
            DID.[Status]
        FROM [data].InverterData AS DID
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from
            AND NOT EXISTS
            (
                SELECT 1
                FROM cache.InverterData AS CID
                    WITH (SNAPSHOT)
                WHERE
                    CID.InverterID = DID.InverterID
                    AND CID.[Timestamp] = DID.[Timestamp]
            );

        SET @RowsAffected += @@ROWCOUNT;

    COMMIT TRANSACTION;

    -- TODO: Use the @RowsAffected result
END;

Ensure both tables have a (non-hash) index on InverterID, [Timestamp].

* Unlike some other database engines, cursors are usually quite inefficient in SQL Server. The overhead and per-row operations almost always make a set-based solution faster and more efficient.


How to give a table a "primary selection" to a table it is many-to-many with?

The fact that some relation is "primary" is an attribute of the relation. So it must be stored in relation table. For example, with the name is_primary.

Variant 1.

The field is defined as BOOLEAN (or INT(1) if no BOOLEAN datatype exists).

To ensure that there is only one primary relation per user we may create unique index by (user_id, is_primary). In such case all another, non-primary, records must have NULL in is_primary field. And when we need to change the primary relation we must alter two records - set NULL to previous primary and set TRUE to current one. Of course, the field is defined as nullable with DEFAULT NULL.

Variant 2.

The field is defined as DATETIME (or TIMESTAMP).

To ensure that there is only one primary relation per user we may create unique index by (user_id, is_primary) again. But now we do not need to "clear" the field value in previous primary - we only set this field in new primary to current timestamp, and, when we need the current primary, we simply select the record with the greatest value in this field.

The field do not need to be defined as nullable.

When we insert new record which is not primary, we may use an expression similar to COALESCE(MIN(is_primary) OVER (PARTITION BY user_id) - INTERVAL 1 SECOND, '1980-01-01'). The number of seconds in 10 years practically guarantees that there won't be some problems with set this field value to the new record.

Moreover, some kind of "change history" will exist - for each record we can see does it was somewhere primary one, and if it was - when it was assigned to primary the last time.


/usr/sbin/mysqld was deleted and replaced by another mysqld with different inode

At first glance, this seems extremely suspicious, but based on the information below, there may be a very logical and non-dangerous, although counter-intuitive, explanation.

Out of curiosity, I looked for important-but-deleted files on some Linux machines and I found such interesting things as...

COMMAND     PID       USER   FD      TYPE     DEVICE   SIZE/OFF       NODE NAME

bash      30851       root  txt       REG      202,1     959120     142485 /bin/bash
bash      31501       root  txt       REG      202,1     955024     131092 /bin/bash (deleted)

Yikes.  Really?

These systems are apt vs yum systems but the principle is the same... it's inconceivable in the world of windows but entirely sensible and not all that unexpected in the world of Linux (or any Unix afaik) to overwrite files that are "open" by another process.  The space allocated to the files is freed as soon as all of the processes that are holding them open... close them.

The log, in my case, confirms that "bash" was involved in one of the packages that has been upgraded since the last reboot but some of the copies of bash that are running are actually still the old bash that was running before the update.

However... your binaries are the same size where mine are different, so this would seem to suggest that it wasn't an outright upgrade.  This led me to speculate that it might have been an upgrade of the package that contained mysqld but that didn't change mysqld itself, causing the file to be overwritten by an identical copy of itself.  But that seems like speculation.

I don't like speculation, so I kept digging.  I use Ubuntu these days, but still have some old Red Hat machines around, so I took a look in the directories in /etc/cron.* and stumbled upon prelink.

I realize you said there are no cronjobs running on the system... but are you sure about that?  Not even the "factory" default cronjobs in /etc/cron?

01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly

If there really are no cron jobs running, then of course, the rest of this post is nonsense, but it would seem very unusual to me to literally have no cron jobs on a Red Hat machine.

Take a look at the timestamp of /var/log/prelink/prelink.log.  If it's a match for the time window in question, then we have a suspect.

I've been a Linux sysadmin for even longer than I've been a DBA and yet somehow this is the first time I remember ever running across this, and frankly, on first reading, it made me nervous that an overnight process was actually poking at bits inside executables, but I guess it's not as problematic as it seems like it would be.

Perhaps it's never caught my attention because I never use the package manager to install MySQL (I see it as too important for anything to touch but me) and the "standard" path for installing official binary distributions is /usr/local/mysql[/bin], which isn't in the paths that prelink touches.

Prelink optimizes program execution and and provides some degree of immunity against exploits based on buffer overruns by making the addresses inside executables and shared libraries somewhat random on a system by system basis.  What you might find, depending on the mix of installed software on each machine, is that each machine's current copy of the mysqld binary is the same size but that they do not actually have the same md5sum or sha1sum checksums as each other even if they're identical versions.  Before today, that would have made no sense at all... but given what I found on prelink... it wouldn't surprise me at this point.

http://people.redhat.com/jakub/prelink/prelink.pdf

I think it seems very likely that this is what's happened.  If your servers are properly firewalled and nothing else seems amiss, it seems unlikely that an intruder would have deliberately and successfully compromised multiple machines in the same way.

If the above turns out to be a likely candidate, you should find, curiously enough, the you can still get to, and make copies of, those files that are deleted, as long as you don't shut down the running mysqld process.  I categorically do not recommend that you put them back but if you are curious, you might find the following informative.

Find the process ID of mysqld from lsof and cd into the /proc/nnnnn directory on your system, where nnnnn is the process id.

Inside this directory you'll find what appears to be a broken symlink called exe.  This is not exactly what it seems, because it actually still references the deleted file in a very real fashion.

I'll illustrate the rest of this with one of my deleted copies of "bash" from above.  The process ID was 31501.

root@host:/# cd /proc/31501
root@host:/proc/31501# ls -l exe
lrwxrwxrwx 1 root root 0 May  1 06:41 exe -> /bin/bash (deleted)

You can actually cp exe /some/new/place and salvage this deleted file, precisely because it is still open.  Again, I wouldn't recommend that you start using this copy, but this is the one that is on the disk that has been deleted, if you want to do any detailed byte by byte analysis.

For a coarser example, you could do a checksum of it.  Again, the example is from my system, with the deleted "bash" which is process 31501.

root@host:/proc/31501# md5sum exe
748c6ee926dd3cf55bcfb60ce928d367  exe

Not surprisingly, that's not the same checksum as the live copy of "bash" on my system.

root@host:/proc/31501# md5sum /bin/bash
3cac23b8442e3971f1d10660cc779b9f  /bin/bash

However, by lucky coincidence, I have another machine that has not been upgraded as recently as the one above.  You'll notice this copy of bash is exactly the same size as the deleted copy on my machine... and it even has the same checksum.

user@older:~$ ls -l /bin/bash
-rwxr-xr-x 1 root root 955024 Apr  3  2012 /bin/bash
user@older:~$ md5sum /bin/bash
748c6ee926dd3cf55bcfb60ce928d367  /bin/bash

You also could... but you really really really shouldn't... actually run the file represented by "exe" from inside the process directory of mysqld (mine is running on 21691).  Seriously, don't do it.  But here's what it will do, if you're curious.  The executable is using its named on disk to identify itself as ./exe ... but it's MySQL all right:

/proc/21691# ./exe --version
./exe  Ver 5.6.10 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))

How about that?


Running an installer without administrator privileges is seldom succesful.  Why don't you follow the steps outlined here:

https://stackoverflow.com/questions/26441873/starting-postgresql-and-pgadmin-in-windows-without-installation#26441939


configure sqlcmd to automatically run your script every time it starts?

You will need to set the SQLCMDINI variable in Windows.  For example:

C:\SET SQLCMDINI=c:\sqlscripts\init.sql

The init.sql (or whatever you name it) would contain the initial script.


Incremental extract strategy using SSIS and CDC

This appears to be your stumbling block in relation to most of the demos of SSIS CDC:

  My current extract consists of queries that join multiple tables
  together and then feeds that into the transform and load. Changes
  could happen in any of those tables, not just one.

As such you're on the right track with your first proposed option. For your join issues - it seems that you either have data that changes in tandem or a data set that shouldn't implement CDC. You should probably reduce the number of CDC tables and do a full load from others. At the least you could come up with a custom incremental that makes sense for your JOIN requirements while also reducing your data set size. Something like:

SELECT *
FROM InfoTable
WHERE Date >= '01-01-2016'

The typical pattern for data warehouse ETL is to move table by table from source to a Staging database, which replicates the table structure of the source systems nearly 1 to 1 (only for tables you actually use and skip things like IDENTITY which will fail your inserts/transfer). Following this pattern should solve at least part of the CDC issue.

From Staging to DataWarehouse you should have your joins, business logic, clean-up, etc. in the ETL. One of the benefits of this pattern, beside simplifying incremental source system pulls, is the reduced load on LoB/OLTP systems. You get in and out from the source system as fast as possible and leave the majority of the ETL burden on the SSIS/DW server(s).

You could setup a variety of incremental load patterns on all your tables. The importance of moving ETL logic/resource use out of source systems becomes even more important if you're running that frequently. If you're trying to meet an hour load window there may be other options beside just incremental loads... even if your time is consumed by data transfer (e.g. network settings).


SQL Clustering options with 2012 standard Ed

On a standard SQL Server license on SQL Server 2016 you can setup AlwaysOn Availability Groups on two hosts without the need for shared storage.

On SQL Server 2014 and 2012 the only high availability option you have with a standard license is to create Alwayson Failover Cluster Instances which depend on you using shared storage.

So using the standard edition the only option you have is to run SQL Server 2016 with AlwaysOn Availability Groups. Remember that a FCI or AG will only set you back a single SQL license if the secondary server is not queried. The setup is not that complicated but a bit out of scope, you can start here


Pull Subscription: process cannot read file due to OS error 5

OS error 5 means Access is denied.

Instead of using SQL Server Agent service account — use pass-through authentication.

Create a local Windows account on both Publisher and Subscriber that has the same username and password.  Add the Windows account to SQL Server logins on both Publisher and Subscriber.  Use this account for the Distribution Agent process account.

Ensure the account is a member of the db_owner fixed database role in the subscription database, a member of the PAL at the Publisher, is a login associated with a user in the publication and distribution databases, and has read permissions on the snapshot share.

Replication Agent Security Model


How to omit multiple columns where is not null?

The screenshot of your data and output clearly shows the problem. The concatenation operator automatically takes care of NULL and you don't need to do anyhting. But you don't have NULL in your tables, you have 'NULL', a string, in your tables.

SQL> select 'A' || ' ' || null || ' ' || 'B' from dual;

'A'|
----
A  B

SQL> select 'A' || ' ' || 'NULL' || ' ' || 'B' from dual;

'A'||''|
--------
A NULL B

If you really had NULL in your table, SQL Developer would display (null) in the data grid.

Clean your data, then try again.

update ap.vendors set vendor_address2 = null where vendor_address2 = 'NULL';

Alternatively, you can turn string 'NULL' to a real NULL on the fly:

SELECT vendor_name,
       vendor_address1 || ' ' || decode(vendor_address2, 'NULL', null, vendor_address2) || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
ORDER BY vendor_name;

But don't do that. Take care of your data.


mysqldump 5.5.25 use swap instead of memory

Your problem is, that mysqldump normally caches the results it produces.
You have to use the --quick option, to prevent this.

 --quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.


My cluster disappeared, but data is still there

Alright, I managed to solve it.

I tried to create the cluser

pg_createcluster 8.4 main

It complained that 8.4 was not an installed version. Alright then, this confirms my suspicion, that at some point postgres was upgraded to the latest version in the debian repos, and the data left behind.

So I went to reinstall 8.4 (Remember to backup your data dir, if you want to follow these steps. Don't hold me accountable for any dataloss)

sudo touch /etc/apt/sources.list.d/pgdg.list
sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-8.4
pg_lsclusters

The output of pg_lsclusters showed that the 8.4 main cluster was created during the installation. I was afraid that it would have nuked the data dir with a fresh installation, but it didn't. Running psql revealed all the old data was still there


There's a strong possibility that CU 4 for SP2 which was release in January of 2015 will resolve the issue you are experiencing: https://support.microsoft.com/en-us/help/3029825/fix-dbcc-checkdb-checktable-command-may-take-longer-in-sql-server-2012

That being said, you are running a SQL server that is out of support and SP2 CU 4 which should resolve the issue is out of support as well. You should go ahead and patch the server to the current SP4 CU 10 + the latest security updates.

It appears to me that you may also be running an old version of OLAs scripts based on the way you are calling them. They should be updated as well.


postgres - pg_dump and pg_restore without roles

The error occurs when pg_restore set the ACLs : you can use --no-acl to prevent GRANT commands.

With the -Ft option in pg_dump, you can skip roles and ACLs only in pg_restore. You can also edit the catalog with --list if you need more details.


Fixing a corrupt Log File in small downtime window

If your log file is corrupt, my concern is that a backup/restore would retain the corruption.  My approach (which would probably be faster to complete) would be:

Run a full backup.
Detach database.
Delete/rename the log file.
Attach the database and rebuild the log file.
Take a second full backup.

To attach the database and rebuild the log file, it's just additional syntax in the CREATE DATABASE statement:

CREATE DATABASE [foo]
ON (FILENAME=<<Path to data file>>)
FOR ATTACH_REBUILD_LOG;

This will force SQL Server to rebuild the log file when it attaches the database.

The risk in this is losing whatever transactions happen between the completion of the full backup and detaching the database, but it sounds like this will be a minimal risk.  If you can "freeze" Sharepoint at the beginning of the downtime window, this would help mitigate that risk.


Enumerating short versus long column names from INFORMATION_SCHEMA views on DB2 for IBM i

INFORMATION_SCHEMA does not contain the long column names.


How to determine CHECK or NO CHECK for foreign Key using SQL Server System Tables

You will need to query "sys.foreign_keys" system table.
Column "is_disabled" will provide you this info.
As far as I know this information is not available in INFORMATION SCHEMA.

You can use following query to get similar results:

SELECT
FK.name [constraint_name]
,T.name [referencing_table_name]
,TabC.name [referencing_column_name]
,RefT.name [referenced_table_name]
,RefC.name [referenced_column_name]
,FK.delete_referential_action_desc delete_referential_action_desc
,FK.update_referential_action_desc update_referential_action_desc
,FK.is_disabled
,FK.is_not_trusted
FROM   sys.foreign_keys AS FK
   INNER JOIN sys.foreign_key_columns FKC
     ON FK.object_id = FKC.constraint_object_id
   INNER JOIN sys.tables AS T
     ON T.object_id = FK.parent_object_id
   INNER JOIN sys.columns AS TabC
     ON TabC.column_id = FKC.constraint_column_id
     AND TabC.object_id = FKC.parent_object_id
   INNER JOIN sys.tables AS RefT
     ON RefT.object_id = FK.referenced_object_id
   INNER JOIN sys.columns AS RefC
     ON RefC.column_id = FKC.referenced_column_id
     AND RefC.object_id = FKC.referenced_object_id


Matching left and right single-quotes used as apostophes

The best way to handle your issue (and avoid SQL injection) is to pass in your user input as a variable.  Since you are using a LIKE you can do something like this:

CREATE TABLE #person (person_name nvarchar(50))
INSERT INTO #person VALUES (N'Bob'),(N'Bo''b'),(N'Bo‘b'),(N'Bo’b'),(N'Bo#b'),(N'Bo^b')

DECLARE @user_input nvarchar(50) = 'Bo’b'

SET @user_input = REPLACE(
                        REPLACE(
                                REPLACE(@user_input, N'‘', N''''),
                                N'’', N''''),
                                N'''', N'[‘’'']')

-- @user_input now == Bo[‘’']b

SELECT person_name
FROM #person
WHERE person_name LIKE @user_input

Basically this replaces all of the different 's with a single type (the ') and then puts []'s around all three so that they get used in the LIKE.


Postgres table size is not growing after adding row

PostgreSQL tables are stored in 8kB blocks. A new row will be added to the first best block that has enough free space. Only if there is no block with free space, the table will be extended.
Keep inserting more rows, and eventually the table will grow.


suppress header in output of sybase query in Linux

Use -b to suppress headers when you connect via isql.

isql -UmyUser -SMYSERVER -iscriptFile -b


Restore databases from prod to test server with data over X days

As already suggested, you cannot achieve this using backup/restore. You can also not achieve it using log shipping.

I would look at using transactional replication perhaps from views, which would define the 60 to 90 day window.

Be aware (or make your stakeholders aware) that replication is not free and comes with its own (usually small) performance overhead.


Allow a SQL Server job to run for specific time before killing it

I have been using the following stored proc on a SQL agent job:

    -- =============================================
    -- Author:        Devin Knight and Jorge Segarra
    -- Create date: 7/6/2012
    -- Description:    Monitors currently running SQL Agent jobs and
    -- alerts admins if runtime passes set threshold
    --
-- =============================================

/*
Change log:
 =============================================
7/11/2012 (v 1.01)
        Changed Method for capturing currently running jobs to use master.dbo.xp_sqlagent_enum_jobs 1, ''

7/12/2012 (v 1.03)
        Updated code to deal with “phantom” jobs that weren’t really running.
        Improved logic to handle this. Beware, uses undocumented stored procedure xp_sqlagent_enum_jobs

7/24/2012 (v. 1.16)
        Removed need to specify mail profile
        Fix for error sending notify email
        Added commented line for testing purposes (avg+1 minute for short tests)
 =============================================
 exec usp_LongRunningJobs
*/
ALTER PROCEDURE [dbo].[usp_LongRunningJobs]
                @jobname nvarchar(100)
AS

--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
    DECLARE @JobLimitPercentage FLOAT

    SET @JobLimitPercentage = 150 --Use whole percentages greater than 100

    -- Create intermediate work tables for currently running jobs
    DECLARE @currently_running_jobs TABLE
        (
          job_id UNIQUEIDENTIFIER NOT NULL ,
          last_run_date INT NOT NULL ,
          last_run_time INT NOT NULL ,
          next_run_date INT NOT NULL ,
          next_run_time INT NOT NULL ,
          next_run_schedule_id INT NOT NULL ,
          requested_to_run INT NOT NULL ,-- BOOL
          request_source INT NOT NULL ,
          request_source_id SYSNAME COLLATE database_default
                                    NULL ,
          running INT NOT NULL ,-- BOOL
          current_step INT NOT NULL ,
          current_retry_attempt INT NOT NULL ,
          job_state INT NOT NULL
        ) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions

--Capture Jobs currently working
    INSERT  INTO @currently_running_jobs
            EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ''

--Temp table exists check

    CREATE TABLE ##LRJobsStage
        (
          [JobID] [UNIQUEIDENTIFIER] NOT NULL ,
          [JobName] [sysname] NOT NULL ,
          [StartExecutionDate] [DATETIME] NOT NULL ,
          [AvgDurationMin] [INT] NULL ,
          [DurationLimit] [INT] NULL ,
          [CurrentDuration] [INT] NULL
        )

    INSERT  INTO ##LRJobsStage
            ( JobID ,
              JobName ,
              StartExecutionDate ,
              AvgDurationMin ,
              DurationLimit ,
              CurrentDuration
            )
            SELECT  jobs.Job_ID AS JobID ,
                    jobs.NAME AS JobName ,
                    act.start_execution_date AS StartExecutionDate ,
                    AVG(FLOOR(run_duration / 100)) AS AvgDurationMin ,
                    CASE
        --If job average less than 5 minutes then limit is avg+10 minutes
                         WHEN AVG(FLOOR(run_duration / 100)) <= 5
                         THEN ( AVG(FLOOR(run_duration / 100)) ) + 10
        --If job average greater than 5 minutes then limit is avg*limit percentage
                         ELSE ( AVG(FLOOR(run_duration / 100))
                                * ( @JobLimitPercentage / 100 ) )
                    END AS DurationLimit ,
                    DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
            FROM    @currently_running_jobs crj
                    INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
                    INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
                                                              AND act.stop_execution_date IS NULL
                                                              AND act.start_execution_date IS NOT NULL
                    INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
                                                              AND hist.step_id = 0
            WHERE   crj.job_state = 1
            and jobs.NAME=@jobname
            GROUP BY jobs.job_ID ,
                    jobs.NAME ,
                    act.start_execution_date ,
                    DATEDIFF(MI, act.start_execution_date, GETDATE())
            HAVING  CASE WHEN AVG(FLOOR(run_duration / 100)) <= 5
                              THEN (AVG(FLOOR(run_duration / 100))) + 10
                            --THEN ( AVG(FLOOR(run_duration / 100)) ) + 1  --Uncomment/Use for testing purposes only
                         ELSE ( AVG(FLOOR(run_duration / 100))
                                * ( @JobLimitPercentage / 100 ) )
                    END < DATEDIFF(MI, act.start_execution_date, GETDATE())

--Checks to see if a long running job has already been identified so you are not alerted multiple times
    IF EXISTS ( SELECT  RJ.*
                FROM    ##LRJobsStage RJ
                WHERE   CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                        SELECT  CHECKSUM(JobID, StartExecutionDate)
                        FROM    dbo.LongRunningJobs ) )
     BEGIN
--Send email with results of long-running jobs

    --Set Email Recipients
        DECLARE @MailRecipients VARCHAR(200)

    SET @MailRecipients = 'developer@adventureworks.com' --Uncomment/Use for testing purposes only

    EXEC msdb.dbo.sp_send_dbmail --@profile_name = @MailProfile
        @recipients = @MailRecipients,
        @query = 'USE DB_Maintenance; Select RJ.*
                From ##LRJobsStage RJ
                WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate)
                NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.LongRunningJobs) ',
        @body = 'View attachment to view long running jobs',
        @subject = 'Long Running SQL Agent Job Alert',
        @attach_query_result_as_file = 1;

--Populate LongRunningJobs table with jobs exceeding established limits
    INSERT  INTO DB_Maintenance.[dbo].[LongRunningJobs]
            ( [JobID] ,
              [JobName] ,
              [StartExecutionDate] ,
              [AvgDurationMin] ,
              [DurationLimit] ,
              [CurrentDuration]
            )
            ( SELECT    RJ.*
              FROM      ##LRJobsStage RJ
              WHERE     CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
                        SELECT  CHECKSUM(JobID, StartExecutionDate)
                        FROM    dbo.LongRunningJobs )
            )
    END

Edited to include table creation:

CREATE TABLE [dbo].[LongRunningJobs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [JobName] [sysname] NOT NULL,
    [JobID] [uniqueidentifier] NOT NULL,
    [StartExecutionDate] [datetime] NULL,
    [AvgDurationMin] [int] NULL,
    [DurationLimit] [int] NULL,
    [CurrentDuration] [int] NULL,
    [RowInsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LongRunningJobs] ADD  CONSTRAINT [DF_LongRunningJobs_Date]  DEFAULT (getdate()) FOR [RowInsertDate]
GO


Attempting to setup a new named instance but can't get past the Feature Selection Screen

You want to make sure you select install new instance

And then make sure you are choosing a new installation

Choose your features, and then under Instance Config you can name your instance


Error from Program Compatibility Assistant when installing SQL Server 2008

From Mark Sinkinson's enigmatic comment, you'll notice that that earlier versions are not compatible on Windows 8. 2008 & 2008R2 are supported, but you need to update them to their most recent service packs.

I agree though, Program Compatibility Assistant isn't the most assisting.

Basically, you just need go through the "Run the program without getting help" option, and then when the installation is finished apply the most recent Service Pack (2 for 2008R2, 3 for 2008).

This MSDN blog post goes into further details on this.

Not really your question but you might want to head over to Brent Ozar's website for a handy checklist/guide for SQL Server installations. I've done quite a few of them but still have that to hand whenever I do a new one.


Create t2 instance and connect via Wokrbench

customer VPC does not support DNS resolution and/or hostnames.

A quick search for the error message uncovers what it means: your VPC (Amazon Virtual Private Cloud) is not configured to support DNS resolution and/or hostnames, both of which are required to make an RDS instance inside VPC publicly accessible.

The solution appears to be to configure the VPC with these capabilities enabled, after, of course, considering why they might have been disabled to begin with.

http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/vpc-dns.html

https://forums.aws.amazon.com/thread.jspa?threadID=153914


Selecting the same column from Different rows Based on Different Criteria

Given this table and data:

CREATE TABLE #src(Number int, name varchar(32), seq bit);

INSERT #src(Number,name,seq) VALUES
(12345,'Mickey Mouse',0),
(12345,'Minnie Mouse',1),
(45678,'Donald Duck',0),
(45678,'Daphney Duck',1),
(245678,'Pluto Dog',0);

You can apply a simple PIVOT:

SELECT Number, Owner1 = [0], Owner2 = COALESCE([1],'')
  FROM #src AS c
  PIVOT (MAX(name) FOR seq IN ([0],[1])) AS p
  ORDER BY Number;

Results:

number   Owner1         Owner2
------   ------------   ------------
12345    Mickey Mouse   Minnie Mouse
45678    Donald Duck    Daphney Duck
245678   Pluto Dog

I would just ensure that Number, seq is enforced to be unique and that seq is either a bit or has a constraint so that it can only be 0 or 1.


how to get 'Queries per second avg' using SQL (not 'mysqladmin status')

For those running MariaDB and MySQL (If using 5.7, show_compatibility_56 enabled)

SELECT Q / T Queries_per_second_avg FROM
(SELECT variable_value Q FROM information_schema.global_status
WHERE variable_name = 'Questions') A,
(SELECT variable_value T FROM information_schema.global_status
WHERE variable_name = 'Uptime') B;

For those running MySQL 5.7, show_compatibility_56 disabled

SELECT Q / T Queries_per_second_avg FROM
(SELECT variable_value Q FROM performance_schema.global_status
WHERE variable_name = 'Questions') A,
(SELECT variable_value T FROM performance_schema.global_status
WHERE variable_name = 'Uptime') B;


Query with INNER JOIN takes long time than expected?

Try adding all columns that are not in a MIN, MAX or SUM to the GROUP BY


Why am I getting cardinality issues when querying a single table

I think your problem is the fact your WHERE clause has multiple predicates on the same table. This article by Paul White explains the issue.

It may be possible to get around this issue by having a computed column that does what your current WHERE clause does. You could then use this column on it's own but this would need a bit of testing to make sure it works OK.

The resolution:

James suggestion got me looking into multiple predicate issues. So I tested using single predicates and noticed that integers were being stored as varchar for the RateItemType field.

The problem was resolved by making the following change:

[iri].[RateItemType] IN (3,2)

to

[iri].[RateItemType] IN ('3','2')


If these are the only two ApprovalLevelName values that are possible, and ignoring for now what you want to do with comments (since both rows could have comments), here is an approach that doesn't use PIVOT explicitly but still gets the results you want.

Table and sample data:

CREATE TABLE #tr
(
    Code              char(4),
    Period            datetime,
    Taskname          varchar(32),
    ApprovalLevelName varchar(32),
    ApprovedBy        varchar(32),
    Status            varchar(32),
    ApprovedTime      datetime
);

INSERT #tr VALUES . -- please use unambiguous date formats
('RTX4','20180331','Test1','Analyst',    'user1','Approved','20180406'),
('RTX4','20180331','Test1','Supervisor', 'user2','Approved','20180406'),
('RTX4','20180331','Test2','Analyst',     NULL,   NULL,      NULL),
('RTX4','20180331','Test2','Supervisor',  NULL,   NULL,      NULL);

Query:

;WITH x AS
(
  SELECT *, dr = CASE ApprovalLevelName WHEN 'Analyst' THEN 1 ELSE 2 END
  FROM #tr WHERE ApprovalLevelName IN ('Analyst','Supervisor')
)
SELECT Code, Period, Taskname,
  Analyst    = MAX(CASE dr WHEN 1 THEN ApprovedBy END),
  Supervisor = MAX(CASE dr WHEN 2 THEN ApprovedBy END),
  AnalystStatus    = MAX(CASE dr WHEN 1 THEN Status END),
  SupervisorStatus = MAX(CASE dr WHEN 2 THEN Status END),
  AnalystApprovedTime    = MAX(CASE dr WHEN 1 THEN ApprovedTime END),
  SupervisorApprovedTime = MAX(CASE dr WHEN 2 THEN ApprovedTime END)
FROM x
GROUP BY Code, Period, Taskname
ORDER BY Code, Period, Taskname;

Some repetition there, which goes up if you have more levels. Still, while PIVOT might seem simpler as a high level concept, it's also designed for simpler cases and when all of the possible output values are known, and this solution is more scalable in that way (and performance should be similar).

Results of the query seem to match your requirements:

Don't forget to clean up:

DROP TABLE #tr;


;with [Users](Id,Name,Other_Columns)
AS(
    SELECT 1,'John','Blah' UNION ALL
    SELECT 2,'Ricky','Blah' UNION ALL
    SELECT 3,'Stella','Blah' UNION ALL
    SELECT 4, 'Bob','Blah')
,
Saldo (Id,[User_id],Saldo)
AS
    (SELECT 1,    3 ,       0.00 UNION ALL
    SELECT 2,    1,        9.00 UNION ALL
    SELECT 3,    2,         0.15 UNION ALL
    SELECT 4,    4,         3.50 )

,Payments(Id,[User_id],Amount,Paid_date)
AS
    (SELECT 1,2,10.00,'2014-09-01 08:10' UNION ALL
    SELECT 2,2,25.00,'2014-09-01 09:00' UNION ALL
    SELECT 3,4,100.00,'2014-05-10 12:47' UNION ALL
    SELECT 4,1,20.50,'2014-02-23 15:30')

SELECT
    ID
    ,Name
    ,Saldo
    ,[Last Payment]
FROM
(
    SELECT
        U.ID,U.Name,S.Saldo
        ,LEFT(COALESCE(P.Paid_date,'0000-00-00'),10)  + ' ' + CAST( COALESCE(Amount,0) AS VARCHAR(50))  [Last Payment]
        ,ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY COALESCE(P.Paid_date,'0000-00-00') DESC) AS RN
        FROM
            [Users] AS U
            INNER JOIN Saldo AS S
            ON U.ID= S.[User_id]
            left JOIN Payments AS P
            ON P.[User_id] = U.ID
)X
WHERE X.RN =1

output:

ID  Name    Saldo   Last Payment
1   John    9.00    2014-02-23 20.50
2   Ricky   0.15    2014-09-01 25.00
3   Stella  0.00    0000-00-00 0.00
4   Bob 3.50    2014-05-10 100.00


How we can do full text search and facets stuffs on BigQuery?

Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?

Correct, BigQuery does not provide search or faceting capabilities. BigQuery is a data warehouse, so it's good for running analytics over large amounts of data, but it does not provide indexing, search, or facets.

You may be able to use Google Cloud Datastore (document database) for this, since it provides secondary indexes, or you may want to do as you suggested, and use a combination of a SQL database such as MySQL or PostgreSQL together with a search product such as Elasticsearch or Solr.


Yes, as reflected in my comments, this capability still exists in SQL Server 2012. There is just an issue in Books Online currently that makes it appear that there isn't a 2012 version of this topic, if the current version of the topic you're in is not the 2012 version.


How do I login to a Postgres database without a password?

When pg_hba.conf is checked for an authentication request, the first match determines the rule.

Quote from the manual

  The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered

(Emphasis mine)

In your case the lines:

host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

are matched before the trust lines you have added, therefor Postgres requires a password. If you want to disable password authentication completely you have to disable the md5 authentication, e.g. by commenting those lines.


What is the cause of high index fragmentation in SQL server?

Things to consider are :

Fill factor
Sort order on the index/table. If table cluster index is not based on identity. When new record is being inserted, the new record could enter in the middle of table. It will screw up the cluster index
Update and delete operations
The last time you rebuilt the index


MS Access copy/paste of table fails with message about clipboard size

What's wrong its trying to copy such amount of data (straight from a table, mind you) to the Clipboard. But since this is Access we're talking it wouldn't shock me that's suggested over Exporting the data safely into a format like .CSV or Even better .SQL
Is funny that you mentioned the system architecture because on first sight I would check that as possible cause.

I would highly suggest changing the approach into something more around this lines: https://support.office.com/en-us/article/Introduction-to-importing-and-exporting-data-08422593-42dd-4e73-bdf1-4c21fc3aa1b0


Trigger Gives an Error on Multiple Rows SQL SERVER

since INSERTED can contain more than one row the update could be performed using a join and not fillin single valued variables:

UPDATE C
SET AccountTypeID = 8
FROM Customer as C on INSERTED as I on I.AccountNumber = C.AccountNumber
WHERE I.TotalSales between 0 and 5000


Execute a stored procedure via a Linked Server from SQL Server 2008 R2 to SQL Server 2014

As a test, I just ran the following from my SQL Server 2008 R2 instance through a linked server to my SQL Server 2012 instance:

SELECT LocalServerName = @@SERVERNAME
    , LocalServerVersion = SERVERPROPERTY('ProductVersion');

EXEC [REMOTESERVER].master.sys.sp_executesql
    N'SELECT RemoteServerName = @@SERVERNAME
        , RemoteServerVersion = SERVERPROPERTY(''ProductVersion'');';

This will show two result-sets, one for the local server, with server name, and version, and one for the remote server with the same details.

Executing this will prove that you can run stored procedures from down-level servers to up-level servers.


What is the Cassandra setting for coordinator timeouts for schema change queries?

According to a Datastax representative, the setting which affects schema changes is request_timeout_in_ms.

It should be noted that these timeout settings are not hard timeouts from the time your client application connects to the coordinator node. If the connection is lost, then the timeout counter restarts. Hence why I was seeing some CREATE TABLE commands taking 12 seconds even though I had request_timeout_in_ms set to 10000ms.


Safe to kill SPID as long as its a SELECT

Ultimately it will depend on whether your website is coded to handle this happening. I have seen blocking occur when a select statement is issued within the context of a transaction which then locks a considerable amount of rows until the transaction is complete. If your website is in the middle of placing an order or billing somebody's credit card and you kill it bad things could happen if the website is not coded to handle it properly. To be sure consult the developers of the website - they would be your best resource in understanding how it will cope in situations like this.


I'm assuming your DBA has not (and will not) give permissions for SQLServerAgent.
If so you will have to use another scheduler.

If you want to execute a stored procedure daily at a specific time, I'd suggest

Call the stored procedure from a page in your web application layer
Set up a scheduler to call the web page at a daily interval.

A free service like this could be set up to call your URL on a daily interval.
http://www.pingjs.com/


How to have MySQL Slave Replicate From a Master's Master

Step 01

On ServerB, run the following commands

STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
FLUSH TABLES;

Step 02

On ServerC, run SHOW SLAVE STATUS\G

Repeat running SHOW SLAVE STATUS\G until Seconds_Behind_Master is 0

Then, run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Step 03

On ServerB, run SHOW SLAVE STATUS\G

For the sake of example, let say SHOW SLAVE STATUS\G looks like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.64.68.253
                Master_User: replusername
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.003202
        Read_Master_Log_Pos: 577991837
             Relay_Log_File: relay-bin.010449
              Relay_Log_Pos: 306229695
      Relay_Master_Log_File: mysql-bin.003202
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 577991837
            Relay_Log_Space: 306229695
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0

Please note the following:

Master_Host (10.64.68.253)
Master_User (replusername)
Relay_Master_Log_File (mysql-bin.003202)
Exec_Master_Log_Pos (577991837)

Step 04

On ServerC, run the following:

STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages = 0;
FLUSH TABLES;
CHANGE MASTER TO
MASTER_HOST='10.64.68.253',
MASTER_PORT=3306,
MASTER_USER='replusername',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.003202',
MASTER_LOG_POS=577991837;
START SLAVE;

Step 05

On ServerC, run SHOW SLAVE STATUS\G

If Seconds_Behind_Master is a Number, CONGRATULATIONS !!!

Step 06

On ServerB, run SET GLOBAL innodb_max_dirty_pages_pct = 90;

On ServerC, run SET GLOBAL innodb_max_dirty_pages_pct = 90;

Give it a Try !!!

CAVEAT

If the majority of your data is MyISAM, ignore all commands that change innodb_max_dirty_pages_pct.


SQL Server TempDB behaviour in large memory environment

Do writes to TempDB always result in an actual physical write to disk,
  or are TempDB writes cached by SQL Server for delayed write like in
  the Windows file system cache?

Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical  mechanism. Reference here is What does checkpoint do for tempdb?.

In a "well behaved" system, writes to a user database file occur on checkpoint. In a badly behaved system, writes will also occur when the lazywriter needs to flush pages from the buffer pool to make room for other pages.

  A checkpoint is only done for tempdb when the tempdb log file reaches
  70% full - this is to prevent the tempdb log from growing if at all
  possible (note that a long-running transaction can still essentially
  hold the log hostage and prevent it from clearing, just like in a user
  database).

But there is no need to flush tempdb to disk, as crash recovery is never run on tempdb, it's always recreated on startup.

  Tempdb is not recovered in the event of a crash, and so there is no
  need to force dirty tempdb pages to disk, except in the case where the
  lazywriter process (part of the buffer pool) has to make space for
  pages from other databases.

This remarkably (it was a surprise to me) is the only mechanism by which tempdb pages will be written to disk. If there is buffer pool pressure, tempdb pages may be flushed to disk. If there isn't, it should not occur.

Edit: Debatable whether "badly behaved" is an appropriate description for a user database that is writing pages outside of checkpoint. Unusual, atypical, or just not ideal maybe?

Additional edit (following comments/chat with @PaulWhite):

The glaring omission above is that temporary tables are not sole source of tempdb traffic. Quoting from Understanding Hash, Sort and Exchange Spill Events:

  Certain SQL Server query execution operations are calibrated to
  perform best by using a (somewhat) large amount of memory as
  intermediate storage. The Query Optimizer will choose a plan and
  estimate the cost based on these operators using this memory
  scratch-pad. But this is, of course, only an estimate. At execution
  the estimates may prove wrong and the plan must continue despite not
  having enough memory. In such an event, these operators spill to disk.

I had incorrectly assumed that the mechanism behind a physical write for a spill operation were exactly the same as described earlier i.e. the lazywriter forcing tempdb pages to disk as a result of pressure on the buffer pool (caused by the spill).

@PaulWhite explained where I was wrong (thanks Paul!):

  I think you are asking why physical tempdb activity occurs when a
  query exceeds its workspace memory grant, rather than just using
  tempdb-in-memory If it did that, the query would be using more memory
  than its grant, defeating the point of restricting memory grants in
  the first place.

  Spills are indeed special in writing through to
  storage. Physical tempdb activity is seen on a spill, even in the face
  of oodles of free memory and zero pressure on tempdb.

Paul also pointed me to his blog post Advanced TSQL Tuning: Why Internals Knowledge Matters which includes example scripts for demonstrating spills, for those that want to delve deeper.


Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

The second plan has a lower estimated cost, so in that limited sense it is 'better'.

The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form of the query happens to find a plan using hash join and a table spool early on. The estimated cost of that plan is so low that the optimizer does not bother looking for anything better.

The second form of the query happens to find a plan using only nested loops outer joins early in the search process, and again the optimizer decides that plan is good enough. It so happens that this plan is estimated to be cheaper.

That said (as mentioned in the question comments) the two queries are not semantically identical. This may not be important to you if you can guarantee that the results will always be the same for all possible future states of your database, but the optimizer cannot make that assumption. It only ever produces plans that are guaranteed to produce the same results specified by the SQL, in all circumstances.

  I have realized that the nested syntax also modifies the behaviour of the query.

The 'nested syntax' is just one aspect of the whole ANSI join syntax specification. To enable a full logical specification for more complex join patterns, the specification allows (optional) parentheses, and FROM clause subqueries.

The query can be written using the same ANSI syntax using parentheses:

SELECT
    A.*,
    M.*,
    N.*
FROM dbo.Autos AS A
LEFT JOIN
(
    dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) ON M.ModelID = A.ModelID;

This form clearly shows that the logical requirement is to left join from Autos to the result of inner joining Manufacturers to Models. Omitting the optional parentheses gives the form you call 'nested':

SELECT
    A.*,
    M.*,
    N.*
FROM dbo.Autos AS A
LEFT JOIN dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
    ON M.ModelID = A.ModelID;

This is not a different syntax - it is just omitting optional parentheses and reformatting a bit.

As Martin mentioned, it is also possible in this case to express the logical requirement using inner joins followed by a right outer join:

SELECT
    A.*,
    M.*,
    N.*
FROM dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
RIGHT JOIN dbo.Autos AS A
    ON A.ModelID = M.ModelID;

All three query forms above use the same ANSI join syntax. All three also happen to produce the same physical execution plan with the data set provided:

As I mentioned in my answer to your previous question, queries that express exactly the same logical requirement will not always produce the same execution plan. Which logical query form you prefer to use is largely a question of style. There is no correlation between one particular style and 'better' query plans in general. I would generally advise against rewriting a query to get a particular plan if the new query is not genuinely logically identical to the original.

The SQL standard also allows FROM clause subqueries, so yet another way to write the same query specification is:

SELECT *
FROM dbo.Autos AS A
LEFT JOIN
(
    SELECT
        N.ManufacturerID,
        ManufacturerName = N.Name,
        M.ModelID,
        ModelName = M.Name
    FROM dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) AS R1
    ON R1.ModelID = A.ModelID;

  Using the traditional syntax, we have to change the join to `Manufacturers to an outer join, like so... but this changes the query plan.

This probably changes the meaning of the query, in which case it is technically not a valid alternative (but see ypercube's  comment on your question).

The (optional) parentheses in the ANSI join syntax are there precisely for more complex join requirements like this, so you should not be afraid to use them where necessary.


Grouping by overlapping arrays, transitively, without duplicates

One of the key parts of Jack Douglas's solution in Group by array overlapping is the | (pipe) operator used on arrays in the recursive part of the recursive t CTE like this:

...
select t.id, a.id, t.clst | a.clst
...

This operator concatenates two arrays suppressing duplicate items. The reason that answer cannot be directly applied to your setup is because apparently the | operator is defined for int arrays only, while you need a way to perform the same operation on inet arrays.

You can do that by treating the arrays as row sets. If you notice, what the | operator produces is effectively a union of two sets. Therefore, if you unnest both arrays, union them and aggregate the combined set back as an array, you will get the same result. So, this expression,

t.clst | a.clst

can be replaced with a correlated subquery:

(
  select
    array_agg(sub.n)
  from
    (
      select unnest(t.clst)
      union
      select unnest(a.clst)
    ) as sub (n)
)

Yes, the substitution is quite unwieldy in comparison, but it does the job, and that is something to start with.

Adapting the solution to your example (and adding a bit of white space to the original code), the complete query would look like this:

with recursive
  cte_a as
  (
    select
      myid,
      array_agg(distinct ip) as ip
    from
      a
    group by
      myid
  )
, cte_t (myid, pmyid, ip) as
  (
    select
      myid,
      myid,
      ip
    from
      cte_a

    union all

    select
      t.myid,
      a.myid,

      (  /* this is the replacement expression */
        select
          array_agg(sub.n)
        from
          (
            select unnest(t.ip)

            union

            select unnest(a.ip)
          ) as sub (n)
      )

    from
      cte_t as t
      join cte_a as a
        on a.myid <> t.pmyid and t.ip && a.ip and not t.ip @> a.ip
  )
, cte_d as
  (
    select distinct on (myid)
      myid,
      ip
    from
      cte_t
    order by
      myid,
      cardinality(ip) desc
  )
select
  array_agg(myid),
  ip
from
  cte_d
group by
  ip
;

You can test the query in this demo at db<>fiddle.uk.

Note also that Jack's word of caution probably applies to your situation as well:

  Bear in mind that this is unlikely to perform well on millions of rows.


Configure postgresql recovery again to be slave

Assuming you are using streaming replication, "no latency".
If you can stop writes or stay on the new master B, some steps below can be reduced

If you stop writes, before you failover back to old master A, it could be a master without any issues, most of the times.

But as you have mentioned that you have writes on the new master B after failing over to it, you will have to

resynchronize the old master A as a standby, using pg_start_backup-rsync-pg_stop_backup (and of course recovery.conf)
promote A using the trigger file, etc
restart B as a standby of A
if it doesn't start (due to continuous writes), you'll have to rsync it again as listed above

I would love to hear if there are other options :)


It can connect from SQL 1 server, which is the primary for AG.

By "connect", do you mean it can ping AG-LISTENER from SQL1?
It sounds like what your problem might be is with the port number you chose for your listener.  By choosing 5525, you are selecting a non-default port (1433 would be the default).
So when you try to connect to the listener, what does your connection string look like?  I'm guessing it looks something like this:
data source = ag-listener; initial catalog = ...

You have two options here.  You can either be explicit with your listener's port number:
data source = ag-listener,5525; initial catalog = ...

Likewise, if you're testing this out with SQL Server Management Studio (SSMS), then for the Connect to Server dialog box, instead of putting in ag-listener for the Server name text box, put in ag-listener,5525.
Or you can change the port that your listener is listening on to 1433 (read the below BOL reference before considering this change):
alter availability group YourAvailabilityGroupName
modify listener 'AG-LISTENER'
(
    port = 1433
);

It is worth noting when you can use the default port (1433).  Take a look at this reference on BOL explaining when you can and can't use 1433 for the listener (excerpt copy/pasted below for reference):

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.
(portions omitted for brevity)
If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

EDIT: If the above isn't your problem (as seen from your comment below) then, after you look through the logs, I'd say the next best course of action is to start looking at the network traffic to see what is (and isn't) happening.  You can use a network monitoring tool like netmon to accomplish this.
Another thing I'd do, and I realize you said the firewall isn't a problem, but I'd see if the port is actually listening (my favorite tool for this is portqry).


How to optimize indexes on MySQL query with various sorts

WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.


mysql upgrade from 5.6 to 5.7, how to config mysql backup

Auto-config requires a script that will edit backup files to change versions. You can use any command-line editor, like vi, to change these files to appropriate configurations. It is almost impossible to say what particular commands you must include or modify in these scripts, as it depends completely on your configurations. Still, the best method is to use third-party backup software, such as Handy Backup, which works equally with both MySQL 5.6 and 5.7, and with all other versions, too.


SSMS 2014 installation - "Invalid license data" when opening ASP.NET files

Visual Studio Shell is just the plumbing required to run the shared components within SSMS. If you want to manage .aspx files etc., you'll need to install the free Visual Studio Express (or a higher edition if Express doesn't have all the functionality you need).


PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

CHAR has a nasty side.  ANALYSE() predates character sets, and its code was probably not updated to take into account that English text in CHAR(...) utf8mb4 wastes 3/4 of the space!  Also, there was some utility of MyISAM row_format=fixed, but such is now useless in the default InnoDB.

Bottom line:  ignore its advice about CHAR unless you (1) really have fixed length strings and (2) have specified an appropriate CHARACTER SET.

I would not change TIMESTAMP to CHAR(19) because (1) the explosion of space due to utf8, and (2) you would lose the timezone adjustment that exists currently in the table, and (3) CHAR(19) is a lot bigger than the footprint of TIMESTAMP (4 bytes previously, now 5-8), and (4) ANALYSE() probably has not been updated to understand microseconds.

DATETIME, on the other hand does not have the timezone issue.  But points 1,3,4 still apply.


Item availability in row or another table

It's depend on what you want achieve. What your database will do? Do you want track who lend book and when? You can create table with transaction and based on return date set availability. Another question is how you want to implement this logic. It could be controlled by your front-end or you can create view, stored procedures or implement triggers. For simplicity you can add column IsAvailable as bit (1,0).
I don't now what database you are using.


How to minimize full ms-access window in vba?

Almost, but not quite.

The one you're looking for is DoCmd.RunCommand acCmdAppMinimize (minimize the Access window)

Also:

DoCmd.RunCommand acCmdAppMaximize

DoCmd.RunCommand acCmdAppRestore


Child records under the parent record in SQLite

The following query creates these rows:

mstr_id    party      billamount  sort1  sort2
 1         abc           500        1     -1     from bill_master
 2         def           600        2     -1
           lunch box     100        1      1     from bill_details
           water bag     400        1      2
           pencil boxes  300        2      1
           a4 papers     100        2      2
           staple pins   200        2      3

These sort* values cause the rows to be ordered correctly after sorting.

SELECT id AS mstr_id,
       party,
       bill_amount AS billamount,
       id AS sort1,
       -1 AS sort2
FROM BILL_MASTER
UNION_ALL
SELECT NULL,
       perticular,
       amount,
       mstr_id,
       sr_no
FROM BILL_DETAILS
ORDER BY sort1,
         sort2


Postgres 9.4 wal_level=logical increases disk space

This is an AWS DMS issue.

DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL logs which may result in storage full situations on the source. This heartbeat keeps restart_lsn moving and prevents storage full scenarios.

Login to DMS Console.
Stop all the tasks related to the source endpoint
Click on "Endpoints" from left side selection pane.
Select the source Endpoint which you are using, as per the task.
Click on "Modify" from the top of the screen.
Expand "Advanced".
Under extra connection attribute, please add this:

heartbeatenable=Y;heartbeatFrequency=1

HeartbeatEnable – set to true (default is false)
HeartbeatSchema – schema for heartbeat artifacts (default is public)
HeartbeatFrequency – heartbeat frequency in minutes (default is 5 and the minimum value is 1)

Click on "Modify". You will be automatically redirected to the Endpoints screen.
Select the endpoint again and click on "Test Connection".
It should automatically start the testing. Wait for it to show "Successful".

Stopping the task alone will not clear replication slot, so the storage usage will still increase when the task is in the stopped state, you will need to delete task to clear the slot.

To clear up the replication slots use the following commands -

SELECT * FROM pg_replication_slots ; --finds all replication slots
select pg_drop_replication_slot('NameOfSlot'); -- you will get the name of the slot from the first command

References:
[1] PostgreSQL Source WAL Heartbeat
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html#CHAP_ReleaseNotes.DMS230


Does RAM taken up Shared Memory connection counts against Max Server Memory?

The "max server memory" option only applies to the buffer pool, not to memory reserved for connections.


You could merge overlapping intervals and count the hours from there (untested):

select name, min(start), end
from (
    select x.name, x.start, min(y.end) as end
    from t as x
    join t as y
        on x.name = y.name
       and x.start <= y.end
       and not exists (
           select 1
           from t as z
           where y.name = z.name
             and y.end >= z.start
             and y.end < z.end
       )
    where not exists (
        select 1
        from t as u
        where x.name = u.name
          and x.start > u.start
          and x.start <= u.start
    )
    group by x.name, x.start
) as v group by name, end;


Enforce uniqueness across optional attributes for an entity

There isn't an easy way (except in SQL Server, see below) to enforce these constraints. And I say "these" and not "this" because they are indeed more than one.

You want to enforce uniqueness on (a,b,c,d) when all the attributes are not null. And uniqueness on (a,b,c) when d is null. And uniqueness on (a,b,d) when c is null. And uniqueness on (a,b) when both c and d are null.

You say that NULL value on c or d means that the value is known to be undefined, yet you allow both (10, 20, 30, 40) and (10, 20, NULL, NULL). Is the value defined or known to be undefined in this case?

Anyway, to actually enforce these rules, there are various options:

If you want to keep the data in a single table and you implement in a DBMS that have filtered/partial indexes (PostgreSQL, SQL Server), you can create 4 UNIQUE partial indexes:

UNIQUE (a, b, c, d) WHERE (c IS NOT NULL AND d IS NOT NULL)
UNIQUE (a, b, c)    WHERE (c IS NOT NULL AND d IS NULL)
UNIQUE (a, b, d)    WHERE (c IS NULL     AND d IS NOT NULL)
UNIQUE (a, b)       WHERE (c IS NULL     AND d IS NULL)

Some other DBMS (like Oracle and DB2) do not have partial indexes but they can be emulated, with different techniques (see the Use the Index Luke! website: DB2, "Emulating partial indexes is possible)

Specifically in SQL Server - and only in this DBMS, because it treats NULL values in regard to UNIQUE constraints differently than all the rest and not according to the standard - we don't really need all the 4 constraints and we can actually get your desired behaviour with what you already have, a simple UNIQUE constraint on (a,b,c,d).

It will do exactly what your rules want to enforce. This does not comply with the SQL standard and there is a small chance that the behaviour will change in future releases (unlikely I'd add, as DBMS try very hard for backwards compatibility) .

Another (similar to the partial unique index) option (kudos to @Erwin, see his answer) is to use a functional index. This could be used in PostgreSQL, Oracle and DB2 that have such indexes.

A variation is to use computed columns (for attributes c and d) and add a unique index based on the four columns (a, b, coalesced_c, coalesced_d). This feature is available in SQL Server but also in MariaDB 5.3+ (which is a MySQL variant) and in the latest MySQL version (5.7), too.

In older MySQL versions (which seems like the DBMS you use) and other ones (like SQLite) that do not have partial or functional indexes, if you want to keep only one table, I think the only option is to do what you considered:

Use a value (like 0 or -1 that can't appear in your data) as a substitute for "known to be undefined".

This would of course work in any DBMS.

And last, what you could also use in any DBMS (assuming it supports usual FOREIGN KEY and UNIQUE constraints) is to properly normalize the entity table into four ones, one for each case:

CREATE TABLE entity_ab (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  CONSTRAINT uq_attr_ab UNIQUE (attr_a_id, attr_b_id),
  CONSTRAINT fk_ab_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_ab_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id)
) ;

CREATE TABLE entity_abc (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  attr_c_id INT NOT NULL,
  CONSTRAINT uq_attr_abc UNIQUE (attr_a_id, attr_b_id, attr_c_id),
  CONSTRAINT fk_abc_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_abc_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
  CONSTRAINT fk_abc_attr_c_id FOREIGN KEY (attr_c_id) REFERENCES attr_c (attr_c_id)
) ;

CREATE TABLE entity_abd (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  attr_d_id INT NOT NULL,
  CONSTRAINT uq_attr_abd UNIQUE (attr_a_id, attr_b_id, attr_d_id),
  CONSTRAINT fk_abd_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_abd_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
  CONSTRAINT fk_abd_attr_d_id FOREIGN KEY (attr_d_id) REFERENCES attr_d (attr_d_id)
) ;

CREATE TABLE entity_abcd (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  attr_c_id INT NOT NULL,
  attr_d_id INT NOT NULL,
  CONSTRAINT uq_attr_abcd UNIQUE (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
  CONSTRAINT fk_abcd_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_abcd_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
  CONSTRAINT fk_abcd_attr_c_id FOREIGN KEY (attr_c_id) REFERENCES attr_c (attr_c_id),
  CONSTRAINT fk_abcd_attr_d_id FOREIGN KEY (attr_d_id) REFERENCES attr_d (attr_d_id)
) ;

then the entity table would become a VIEW:

CREATE VIEW entity AS
        SELECT attr_a_id, attr_b_id, attr_c_id, attr_d_id
        FROM entity_abcd
    UNION ALL
        SELECT attr_a_id, attr_b_id, attr_c_id, NULL
        FROM entity_abc
    UNION ALL
        SELECT attr_a_id, attr_b_id, NULL, attr_d_id
        FROM entity_abd
    UNION ALL
        SELECT attr_a_id, attr_b_id, NULL, NULL
        FROM entity_ab ;


How do I script SQL Server 2012 replication to turn it off during a deployment and then turn it back on after the deployment?

If you open the properties of the publication, then select the Subscription Options section, you will see a property named Replicate Schema Changes. Set that to true and any schema change will be replicated and display in the Distributor to Subscriber History in Replication Monitor as DDL change. No need to stop and restart any agents. I have found that you need to use alter and cannot use sp_rename to rename a column.

Of course, test in a non-production environment first.


MySQL cycling between periods of high I/O and high lock contention

Point 1 : Locking Management

You must check which processes are staying in locking state & what you can do it for locking i.e creating index for faster retrieval ...
You can check deadlock in show engine innodb status

Point 2: Redo Log Buffer & Files

Make sure the redo log buffer & files are big enough to capture many transaction.

Point 3: Flusing Method

Set innodb_flush_method=O_DIRECT for speedup with write operation.

Still this is not enough, you need to check Innodb buffer pool,OS level parameters(iostat,vmstat...)


How to keep a live backup of an SQL server in sync?

Have you looked at SQL Server mirroring? You can have high safety mirroring which means that a transaction at the client is not done until the transaction has been mirrored to your data center database. Mirroring is done at the database level and there are different types of mirroring depending on your needs.

Please look at the following to see a more extensive description of mirroring:
https://msdn.microsoft.com/en-us/library/ms189852.aspx


T-SQL Update Random String Value in Column

One possible approach is to reverse the full filenames and find the actual file names.

Table:

CREATE TABLE Data ([FileName] varchar(100))
INSERT INTO Data([FileName])
VALUES
    ('C:\ABC\Files\AA.dat'),
    ('C:\ABC\Files\AA.Unbound.qp'),
    ('C:\DEF\AA\GGG Build 1 Modules\Random123.qpp'),
    ('C:\DEF\AA\GGG Build 1 Modules\Random456.qpp'),
    ('C:\GH\DC.Random789.qpp'),
    ('C:\IJK\Random987.qpp'),
    ('Random000.qpp')

Statement:

UPDATE Data
SET [FileName] = CONCAT(
   'D:\FILES\',
   REVERSE(LEFT(REVERSE('\' + [FileName]), CHARINDEX('\', REVERSE('\' + [FileName])) - 1))
)

Result:

FileName
D:\FILES\AA.dat
D:\FILES\AA.Unbound.qp
D:\FILES\Random123.qpp
D:\FILES\Random456.qpp
D:\FILES\DC.Random789.qpp
D:\FILES\Random987.qpp
D:\FILES\Random000.qpp


Multi values for SSRS parameter with Like statement

You would need to change your query in an expression and concatenate the condition using the JOIN expression on your parameter values.

For instance, if you create a report based on the Adventureworks 2014 database you could add a parameter like this:

With some values that appear in the table:

If you then enter an expression for your dataset instead of a query like this:

="SELECT "
&     "[NationalIDNumber],"
&      "[JobTitle]"
&"FROM [AdventureWorks2014].[HumanResources].[Employee]"
&"WHERE [JobTitle] LIKE '%" & Join(Parameters!jobtitle.Label, "%' OR [JobTitle] LIKE '%") & "%'"

It would result in what you want when selecting a single value:

Because the concatenated expression is this:

SELECT [NationalIDNumber]
    ,[JobTitle]
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [JobTitle] LIKE '%Engineer%'

And it would also work when you select multiple values:

Because the expression would result in this:

SELECT [NationalIDNumber]
    ,[JobTitle]
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [JobTitle] LIKE '%Engineer%'
    OR [JobTitle] LIKE '%Marketing%'

Full .RDL file can be found on Github Gist and requires a local Adventureworks 2014 database.


Bent Ozar has a good article about why benchmarking is rarely seen based on Dr. David DeWitt.

  BENCHMARK TESTING. You must obtain Microsoft’s prior written approval
  to disclose to a third party the results of any benchmark test of the
  software.

But, one is free and one isn't so there is a big difference to consider among the laundry list of others (IDE, HA/DR, Auditing, Server operating systems, Supported laguages, etc).

There are also some older posts on Stackoverflow comparing the two, with many folks providing their opinions.

GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server?
DB2 vs PostgreSQL vs SQL Server
Brent Ozar: Two Important Differences Between SQL Server & PostgreSQL

Dr. Dewitt also has a great video about query optimization. which is slightly relevant but more so just a great watch.


How to use character classes in a Postgres regex select?

SELECT "buildings"."street_name"
FROM "buildings"
WHERE (street_name ~ '\m21\D*\M');

Explanation:

\m: beginning of word
21: the string to match
\D*: zero or more non-digit characters
\M: end of word
m21\D*\M: a word that starts with 21 followed by any number of non-digits up to the end of the word

If you wanted to limit the query to only ordinal street names (e.g. "21st") and avoid matching things like "21A", you could instead use \m14(st|nd|rd|th)?\M for the pattern.


How do I insert a row which contains a foreign key?

Your syntax is almost good, needs some parenthesis around the subqueries and it will work:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     (SELECT id from foo WHERE type='blue') ),
    ( 'another row', (SELECT id from foo WHERE type='red' ) );

Tested at DB-Fiddle

Another way, with shorter syntax if you have a lot of values to insert:

WITH ins (description, type) AS
( VALUES
    ( 'more testing',   'blue') ,
    ( 'yet another row', 'green' )
)
INSERT INTO bar
   (description, foo_id)
SELECT
    ins.description, foo.id
FROM
  foo JOIN ins
    ON ins.type = foo.type ;


My boss ask me for possibilities to do the encryption on content DB
  level, it backup. or on column level?

This is something you and You're boss need to sit and discuss on as what could be pros and cons of using TDE or Cell level encryption:

However Few points to note:

TDE does not offer the granularity of protection that cell-level encryption offers, it does provide a level of protection that cannot be achieved through cell-level encryption
TDE does not require any schema modifications and since Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.
In MS whitepaper titled "Database Encryption in SQL Server 2008 Enterprise Edition", Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%
Also, we have seen that the compress backup feature from SQL 2008 onwards which helps in saving the space without using any 3 rd party software seems to be of very less active means the compression it do with that feature on is quite less effective.
As far as i know i think Instant File initialization also does not work when TDE has been implemented.
Also, i am sure TDE also encrypts the backups starting from SQL server 2014, and don't think it does same for SQL server 2012

So there's few ups and downs on both TDE and Cell level, but you have to ultimately decide what suits and fits within you're system health as well as budget.

Many other points you would like to go and can be read here

Also,

  Implementing TDE in conjunction with cell-level encryption provides a
  layered approach to data security, which enhances its effectiveness.
  Another option for protecting sensitive data that is available is
  one-way encryption, also referred to as hashing. One-way encryption
  can be applied alongside TDE, cell-level encryption and other
  obfuscation methods


sp_rename or swapping schemas are the typical ways to accomplish this. Neither really saves you anything about permissions scripts because whether you renamed the new object or swapped it into the dbo schema, it still needs to have permissions applied to match the old version of the table.

For a long time I've preferred swapping schemas simply because I used to do this in an automated job and the "Caution: renaming objects blah blah" warning messages were filling the SQL Server Agent logs. That message is not suppressible but the ALTER SCHEMA...TRANSFER statement does not raise such a message. I have asked for a way to suppress this warning but it was rejected.

(Of course another avenue to consider is to archive the current data and alter the current table to meet the new requirements - but I don't know if that's something you haven't considered, or if it has and there's a reason you can't do that.)


Print Column Names for Synonym for Table in Another Database

I provided a similar answer here which utilizes sys.dm_exec_describe_first_result_set.  Here is an example of how you can achieve your goal.

--demo setup
SET NOCOUNT ON
USE [master]
GO
--Create two sample databases
DROP DATABASE IF EXISTS [DB1]
DROP DATABASE IF EXISTS [DB2]
CREATE DATABASE [DB1]
CREATE DATABASE [DB2]
GO

--Connect to [DB2]
USE [DB2]
GO
--Create and load a table
CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [PhoneNo] [int] NOT NULL,
    [StreetAddress] [varchar](40) NULL,
    [City] [varchar](20) NULL,
    [OrderID] [int] NOT NULL
)
GO

--Connect to [DB1] and create a synonyn that points to a table in [DB2]
USE [DB1]
GO
CREATE SYNONYM DB2_CUSTOMER FOR [DB2].dbo.CUSTOMER
GO

--The solution
--Use sys.dm_exec_describe_first_result_set to retrieve the metadata for the synonym
DECLARE @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM DB2_CUSTOMER', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @cols = replace(@cols,',',',' + char(10))
print @cols

Results:

CustomerID int,
FirstName varchar(20),
LastName varchar(20),
Email varchar(30),
PhoneNo int,
StreetAddress varchar(40),
City varchar(20),
OrderID int


You can record every connection using the general log.

In fact, you can record that information into a table.

In the mysql schema, there is a table called general_log. Here it is:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.01 sec)

mysql>

Of course, the table uses the CSV storage engine. You can change that to MyISAM and index it. Make a copy of the table beforehand:

use mysql
CREATE TABLE general_log_copy LIKE general_log;
ALTER TABLE general_log ENGINE=MyISAM;
ALTER TABLE general_log ADD INDEX (event_time);

To activate it, do this

1) Configure the general log to record to a table by adding these lines to my.cnf

[mysqld]
general-log
log-output=TABLE

2) Restart mysql

3) start mysql and user it for a day or two

4) Look for the dev user by running this query

SELECT * FROM mysql.general_log WHERE user_host LIKE 'dev %';

This will tell you what time and what IP dev is coming in

5) Hunt down the dev user in all your code

6) Truncate the general log

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
TRUNCATE TABLE mysql.general_log;
SET GLOBAL general_log = @old_log_state;

7) If you did find 'dev' users sneaking in, wait 24 hours and go back to step 4. Otherwise, you're done

CAVEAT

Please disable the general log when you are done by removing the lines in step 1. After that, just run

SET GLOBAL general_log = 'OFF';

That's all !!!

Please see my posts about the table version of the general log:

Jan 28, 2013 : mysql logging activity from specific user or ip
Feb 11, 2012 : MySQL general log
Nov 16, 2011 : Log MySQL DB changing queries and users

I also did this using the slow log instead : How to do MySQL User Accounting

Give it a Try !!!


PostgreSQL Binary Replication -do i really need to copy pg_xlog separated?

It works fine, until it doesn't.  Then you get a backup which won't start, will start but won't connect the master to stream new data, or perhaps one which will start but is silently corrupted (unlikely, but I wouldn't rule it out). Have you tested this method for a very large server under very heavy load?

If you use pg_basebackup, it will either take care of it for you, or will notify you that the backup failed before you discover that the hard way, or notify you that you need to have a separate WAL archive.  Depending on which version of PostgreSQL and which options you give to pg_basebackup.


Performance of query against column level dependencies much faster when using deprecated system view

I tried this rewrite that uses a temp table with the new DMF on AdventureWorks2014, and it runs around twice as fast as the version from your question.

SELECT t.[object_id], t.[name]
INTO #trigs
FROM sys.triggers t
WHERE
    t.parent_class = 1
    and t.is_ms_shipped = 0;

SELECT DISTINCT t.[name]
FROM #trigs t
CROSS APPLY sys.dm_sql_referenced_entities
(
    'Person.' + object_name(t.[object_id]),
    'object'
) deps
WHERE
    deps.referenced_entity_Name = 'Person'
    and deps.referenced_minor_name = 'BusinessEntityID';

Here's a screenshot of the Plan Explorer summary after running both queries in the same batch (plans here):

The original query (the first row) runs for about 8 ms, the temp table version (the second two rows) varies between 4 and 5 ms.  Of course, your database might have vastly more triggers, objects, and dependencies than AW2014.

As Dan Guzman mentioned, you may also get some improvements by updating system table stats.

FYI - those two queries are not exactly equivalent.  The second query will not show the same results if there is a trigger in a different (non-dbo) schema that references dbo.JobVoyage.JV_VoyageFlight.

In the example query from my test, there is a trigger in the Sales schema (iduSalesOrderDetail) that is returned by the deprecated view but not the DMF version, because the column I'm checking for is in the Person schema:

If all your objects are in "dbo" then this won't matter, but I thought it was worth pointing out.


Easiest way to find the larger version number?

A little easier on the eyes perhaps. It relies on the inferences I can make from your sample data (that three of the octets are < 100 and one is > 1000) - you may need to make adjustments to the multipliers to reflect any differences in real data. I doubt performance will be any different, but depending on how often you are running these comparisons, you may consider computed/persisted columns storing the calculations.

DECLARE @str1 nvarchar(max)='15.0.4000.8',
        @str2 nvarchar(max)='15.6.4000.8';

DECLARE @i1 BIGINT = 10000000000 * PARSENAME(@str1, 4)
    + 1000000 * PARSENAME(@str1, 3)
    + 10 * PARSENAME(@str1,2)
    + PARSENAME(@str1, 1),
  @i2 BIGINT = 10000000000 * PARSENAME(@str2, 4)
    + 1000000 * PARSENAME(@str2, 3)
    + 10 * PARSENAME(@str2,2)
    + PARSENAME(@str2, 1);

SELECT @i1, @i2, CASE WHEN @i1 > @i2 THEN @i1 ELSE @i2 END;

Results:

150000040008    150006040008    150006040008


Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

I saw an item on Pinal Dave's blog recently that may answer your question.  He suggests adding the line
127.0.0.1 localhost
to your hosts file

The full article can be found here

I do note, however, the situation he refers to is a machine that is not part of a domain - if your machine is, then this may not solve it


pg_stat_statements not found even with "shared_preload_libraries = 'pg_stat_statements'

Recent versions of Postgres install conf files in different locations than they used to be in.  There is sometimes a template .conf file that exists (but is not actually used), and then also an active .conf file (in a completely different location).  I discovered this by scouring through the /etc/init.d files.  It's possible that the .conf file you edited is not actually the one that is being used by the database.

You can find the location of the conf file used by your database by connecting to the database and querying SHOW config_file

Here is a link to your follow up question (in case anyone in the future finds it useful)


Add newest values until current date with the year and month separated as 2 INT columns

You could use WHERE NOT EXISTS to achieve what you need:

  INSERT INTO Table#1 (ID, col2, Year, Month)
  SELECT B.ID, B.col2, A.InvoiceYear, A.InvoiceMonth
    FROM Table#2 AS A
    JOIN Table#3 AS B ON B.ID = A.ID
   WHERE NOT EXISTS (SELECT *
                       FROM Table#1 AS C
                      WHERE C.Year = A.InvoiceYear
                        AND C.Month = A.InvoiceMonth)
ORDER BY A.InvoiceYear DESC, A.InvoiceMonth DESC;

You can find more information here: EXISTS (Transact-SQL)


MySQL percona 5.6 to MySQL 5.5 replication

It isn't clear what you're asking in scenario 1.

As for scenario 2, you can have a slave that's a higher version than the master, but not vice-versa. From the documentation:

  MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL 5.6 to a slave running MySQL 5.7, and so on.


Massive peer-to-peer replication topology?

For a meer 52 client sites replication will do just fine. Consider your local copy as the 'master' site and have the application work only on the local copy, always. Use replication to aggregate the data to the central repository for aggregated reporting. Index maintenance operations normally do not generate replicaiton traffic. Manage schema changes through application deployment migrations, ie. have upgrade scripts for each released version.

As the number of sites increases, managing replication becomes harder and harder. Also licensing would push toward deploying Express on periphery. And SQL Server upgrade is an absolute pain when replication is involved, as the order of upgrading the components (publisher, distributor, subscriber) is critical is but difficult to coordinate with many sites. Deployments that I know of with +1500 sites use customized data movement based on Service Broker. See Using Service Broker instead of Replication for an example. I've even seen designes that use Service Broker to push out to periphery new bits (application changes) which in turn deploy, when activated, migrations and schema changes, making the entire deployment operated from the center.


PostgreSQL insert not occurring serially

There is absolutely nothing wrong with Postgres to act like this and it has a reason. According to Postgres's documentation (in Notes section):

  sequence objects cannot be used if "gapless" assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

And again in another page it says:

  Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.


mysql trigger not working with if statement

You have to change DELIMITER to something else than ;

DELIMITER $$
CREATE TRIGGER updateNewEmp AFTER UPDATE ON BasicDetails FOR EACH ROW
BEGIN
  IF(NEW.MiddleName<>OLD.MiddleName)
  THEN
    INSERT INTO BasicDetails_audit(MiddleName,`State`,Country,Community)
         VALUES (NEW.MiddleName,NEW.`State`,NEW.Country,NEW.Community);
  END IF;
END$$
DELIMITER ;