Collect disk space metrics for whole SQL Server instance

To monitor disk space usage and database file size I use this script that you can find on my blog:

-- create a temporary table to hold data from sys.master_files
IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
    DROP TABLE #masterfiles;

CREATE TABLE #masterfiles (
    database_id int,
    type_desc varchar(10),
    name sysname,
    physical_name varchar(255),
    size_mb int,
    max_size_mb int,
    growth int,
    is_percent_growth bit,
    data_space_id int,
    data_space_name nvarchar(128) NULL,
    drive nvarchar(512),
    mbfree int
);

-- extract file information from sys.master_files
-- and correlate each file to its logical volume
INSERT INTO #masterfiles
SELECT
     mf.database_id
    ,type_desc
    ,name
    ,physical_name
    ,size_mb = size / 128
    ,max_size_mb =
        CASE
            WHEN max_size = 268435456 AND type_desc = 'LOG' THEN -1
            ELSE
                CASE
                    WHEN max_size = -1 THEN -1
                    ELSE max_size / 128
                END
        END
    ,mf.growth
    ,mf.is_percent_growth
    ,mf.data_space_id
    ,NULL
    ,d.volume_mount_point
    ,d.available_bytes / 1024 / 1024
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) AS d;

-- add an "emptyspace" column to hold empty space for each file
ALTER TABLE #masterfiles ADD emptyspace_mb int NULL;

-- iterate through all databases to calculate empty space for its files
DECLARE @name sysname;

DECLARE c CURSOR FORWARD_ONLY READ_ONLY STATIC LOCAL
FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'

OPEN c
FETCH NEXT FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @sql nvarchar(max)
    DECLARE @statement nvarchar(max)
    SET @sql = '
        UPDATE mf
        SET emptyspace_mb = size_mb - FILEPROPERTY(name,''SpaceUsed'') / 128,
            data_space_name =
                ISNULL(
                    (SELECT name FROM sys.data_spaces WHERE data_space_id = mf.data_space_id),
                    ''LOG''
                )
        FROM #masterfiles AS mf
        WHERE database_id = DB_ID();
    '
    SET @statement = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql'
    EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql

    FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- create a scalar function to simulate the growth of the database in the drive's available space
IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
    EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

EXEC tempdb.sys.sp_executesql N'
CREATE FUNCTION calculateAvailableSpace(
    @diskFreeSpaceMB float,
    @currentSizeMB float,
    @growth float,
    @is_percent_growth bit
)
RETURNS int
AS
BEGIN
    IF @currentSizeMB = 0
        SET @currentSizeMB = 1
    DECLARE @returnValue int = 0
    IF @is_percent_growth = 0
    BEGIN
        SET @returnValue = (@growth /128) * CAST((@diskFreeSpaceMB / (@growth / 128)) AS int)
    END
    ELSE
    BEGIN
        DECLARE @prevsize AS float = 0
        DECLARE @calcsize AS float = @currentSizeMB
        WHILE @calcsize < @diskFreeSpaceMB
        BEGIN
            SET @prevsize = @calcsize
            SET @calcsize = @calcsize + @calcsize * @growth / 100.0
        END
        SET @returnValue = @prevsize - @currentSizeMB
        IF @returnValue < 0
            SET @returnValue = 0
    END

    RETURN @returnValue
END
'

-- report database filegroups with less than 20% available space
;WITH masterfiles AS (
    SELECT *
        ,available_space =
            CASE mf.max_size_mb
                WHEN -1 THEN tempdb.dbo.calculateAvailableSpace(mbfree, size_mb, growth, is_percent_growth)
                ELSE max_size_mb - size_mb
            END
            + emptyspace_mb
    FROM #masterfiles AS mf
),
spaces AS (
    SELECT
         DB_NAME(database_id) AS database_name
        ,data_space_name
        ,type_desc
        ,SUM(size_mb) AS size_mb
        ,SUM(available_space) AS available_space_mb
        ,SUM(available_space) * 100 /
            CASE SUM(size_mb)
                WHEN 0 THEN 1
                ELSE SUM(size_mb)
            END AS available_space_percent
    FROM masterfiles
    GROUP BY DB_NAME(database_id)
        ,data_space_name
        ,type_desc
)
SELECT *
FROM spaces
WHERE available_space_percent < 20
ORDER BY available_space_percent ASC

IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
    DROP TABLE #masterfiles;

IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
    EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

Basically, it uses sys.master_files to query the size of all the database files and sys.dm_os_volume_stats to query the drive info. FILEPROPERTY returns information on the available space inside the file.

The script there was meant for alerting when available free space falls under a threshold (say 20%), but it can be modified to perform regular monitoring and record the information in a centralized table.

Don't overlook what the Data Collector feature can record in the Management Data Warehouse database. The feature is available in all editions of SQL Server (express excluded for the lack of an agent to schedule the collection). If the reports you have there are not enough, you could query the tables directly (you just need to query [snapshots].[disk_usage] in the MDW database).

In particular, if you're on Enterprise Edition, the Utility Control Point feature focuses on resource utilization with a "holistic" approach. Again, it provides some reports and a dashboard to see resource utilization across the whole server infrastructure or the single server.

In my experience, Data Collector and Utility Control Point are the most underused features in SQL Server.


How to force SQL developer to use imp/exp command while import/export

Not going to happen

The Original exp utilitie became DESUPPORTED as of 11.1.  Additionally, the 11.1 version of the database is no longer supported either.  SQL Developer isn't going to be "patched" to support unsupported Database version or unsupported database features.

For more information you can read the Compatibility Matrix for exp/imp which has Oracle Doc ID 132904.1 NOTE : You must have a valid support contract (and account) to access the Document. (copy+pasting information is against Oracle's term of use)

The imp utility seems to still be around for importing old exports.

Workaround

Learn to use the command line.


What are the benefits of using Manual Failover Mode in an AlwaysOn Availability Group instance as opposed to Automatic Failover Mode?

If you have synchronous replicas you typically have one with automatic failover.  The most common reason not to is when the primary is hosted on a Failover Cluster Instance, and it's a rule that only the FCI can failover automatically in that configuration.

But you could also have a scenario where you would prefer an outage over a failover, for instance if the secondary replica is not accessible from the application.


Fastest way to count how many dates' ranges cover each date from series

And how to include pairs date-kind with 0 count?

Build a grid of all combinations then LATERAL join to your table, like this:

SELECT k.kind, d.as_of_date, c.n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS  JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
CROSS  JOIN LATERAL (
   SELECT count(*)::int AS n
   FROM   dates_ranges
   WHERE  kind = k.kind
   AND    d.as_of_date BETWEEN start_date AND end_date
   ) c
ORDER  BY k.kind, d.as_of_date;

Should also be as fast as possible.

I had LEFT JOIN LATERAL ... on true at first, but there is an aggregate in the subquery c, so we always get a row and can use CROSS JOIN as well. No difference in performance.

If you have a table holding all relevant kinds, use that instead of generating the list with subquery k.

The cast to integer is optional. Else you get bigint.

Indexes would help, especially a multicolumn index on (kind, start_date, end_date). Since you are building on a subquery, this may or may not be possible to achieve.

Using set-returning functions like generate_series() in the SELECT list is generally not advisable in Postgres versions before 10 (unless you know exactly what you are doing). See:

What is the expected behaviour for multiple set-returning functions in select clause?

If you have lots of combinations with few or no rows, this equivalent form may be faster:

SELECT k.kind, d.as_of_date, count(dr.kind)::int AS n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
LEFT   JOIN dates_ranges dr ON dr.kind = k.kind
                           AND d.as_of_date BETWEEN dr.start_date AND dr.end_date
GROUP  BY 1, 2
ORDER  BY 1, 2;


The PointsForPushups table needs to be normalized first.  I suggest breaking it into the two following tables:

tblPointsForPushups
----------
reps | ageGroup | pushups
1 | A | 6
1 | B | 7
1 | C | 8
2 | A | 7
2 | B | 9
2 | C | 9
3 | A | 9
3 | B | 11
3 | C | 12

tblAgeGroups
----------
ageGroup | ageMin | ageMax
A | 17 | 21
B | 22 | 26
C | 27 | 31

With these tables in place then DateDiff and Between can easily be used in a query to find the required information.


You use mytop and mysqltuner to analyze using mysql resources (RAM and CPU).

Too enable the option to log slow queries:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

And check out this post about ntpd service:

https://stackoverflow.com/questions/1282232/mysql-high-cpu-usage

I have a dedicated server for a high rate of transactions:

max_allowed_packet=16M
key_buffer_size=8M
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=512M
join_buffer_size=40M
table_open_cache=1024
query_cache_size=40M
table_definition_cache=256
innodb_additional_mem_pool_size=10M
key_buffer_size=16M
max_allowed_packet=32M
max_connections = 300
query_cache_limit = 10M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

Regards.


A workaround for restoring HierarchyId in SQL server 2014

I am looking for a workaround.

For me, based on conversations I've seen about this elsewhere, you have four options:

Perform a system restore to a point prior to the Visual Studio CTP installation.
Wait for Connect item #1063935 to be addressed - it may be in a future Cumulative Update or Service Pack for SQL Server, or it may be in a patch or some kind of update for the Visual Studio preview. It is not addressed explicitly in Cumulative Update #6, but applying that can't hurt to try.
Back up your databases somewhere, and remove everything Visual Studio, .net & SQL Server related (the hard way, not through the Control Panel), and install SQL Server again.
If 3. doesn't work, back up your databases somewhere, and nuke & pave your OS.

If 1. is not possible, then you need to take further action in the future to prevent having to do these other much harder things:

Either don't install Visual Studio, SQL Server etc. CTP or preview versions on your critical / primary workstation (virtual machines are a much better target for this sort of thing); or,
start taking better system backups so that if you do decide to install such things you can recover.

This can be an expensive lesson that, sadly, most people learn the hard way.


Unexpected results with soundex() function when combined st_distance()

The condition:

AND    A.name = B.name

only allows identical names, while you obviously want to match names with the same soundex value. So make that:

AND    soundex(A.name) = soundex(B.name)

If gid is the primary key, you can also simplify to:

GROUP BY a.gid, b.gid

However, since there obviously is no aggregation at all, you can simply remove the GROUP BY clause.

As suggested by PostGis, consider ST_DWithin() instead of ST_Distance() in the JOIN clause. The latter cannot utilize a spatial index.


The OLE DB provider is usually specified via a connection string for database applications. Connection strings are usually stored in an external application configuration file so that the driver and other attributes (server, database, etc.) can be changed without code changes. Below is are SQL Server 2008 and 2012 Native Client examples, with SQLNCLI11 being the 2012 version:
Provider=SQLNCLI10;Integrated Security=SSPI;Initial Catalog=YourDatabase;Data Source=YourServer

Provider=SQLNCLI11;Integrated Security=SSPI;Initial Catalog=YourDatabase;Data Source=YourServer

Applications can also configure connection string properties programmatically or even have values hard-coded. Consult your CRM vendor documentation to determine specifics of configuration file locations or other configuration means, and importantly, verify the software supports SQL Server 2012 native client.


AlwaysOn: Can reads on secondary block DDL on primary

Could someone explain, what the actual consequences are, if any?

Sure.

  On larger tables schema changes seem to cause a lot of blocking, which lead us to not applying some of our changes.

This is happening on the primary (if it truly is blocking), you'll need to find what sessions are blocking it at why (on the primary for the primary).

  We got the advice to disable ReadOnly connections to the secondary replicas for the schema migrations.

This won't help the blocking, it could lessen it but only if the secondary is in synchronous commit mode and only if the workload on the secondary is causing extra HADR_SYNC_COMMIT waits. If it isn't, then it's not going to help the primary for blocking.

What turning off readable seconaries will do, is stop the workload on the secondary allowing it to have the most speed in keeping up by not having any other workloads to contend with. It may also help with redo blocking on the secondary since you're running DML and could lessen the send queue/redo queue. If you had synchronous replicas you may want to look at making them asynchronous for the time being depending on if the schema changes are size of data operations or not.

  From what I have read, I would guess that it doesn't, because reads at the secondary should not block dml queries on the primary.

Correct.


The table may have a million or a billion or a trillion rows. The answer is the same:

Add an appropriate index on the columns used in the WHERE (and other clauses).

In this case, a simple index on (name) would be enough. Assuming that the number of rows that match the condition name = 'John' is small, say 0.1%, then the optimizer will use the index to find the matching rows and then read only those rows from the table (this is done with some additional lookups), to find the lastname values.

With a very large table, this may not be fast enough. If the percent of matching rows is 0.1% with a billion rows, that means a million rows with name = 'John' and a million lookups on the table. We can then have instead a "covering" index - on (name, lastname) - so we don't have to do the additional lookups on the table. Only the index will be used and the million values will be found with a simple index seek (and partial scan).

Response to your comment

  This is, as far as I can see, as simple as altering the table structure and just setting the name column to be an index. Correct?

Basically yes, with either ALTER TABLE ... ADD INDEX ... or CREATE INDEX ... The table structure doesn't really change, the index is an additional (physical) structure.


SQL Server - DELETE from subquery/derived table

Convert your select statement into a CTE, and DELETE FROM the CTE, as in:

;WITH del AS
(
SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1
)
DELETE FROM del;

Standard warning: You should test this in a non-production environment.

You can simplify your query quite a bit, and likely get better performance by using the below query, which does not make use of the intermediate table, DUPS_Claims, since it is absolutely unnecessary:

;WITH cte AS
(
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
)
DELETE
FROM cte
WHERE rn > 1;

I created a non-clustered, non-unique index on both tables, then looked at the execution plans for both variations.

The first variant:

The second variant:

The first variant scans the index twice, whereas the second variant clearly only needs to scan the index a single time, and doesn't require a relatively expensive merge join in my somewhat contrived example.  My sample ETL.Stage_Claims table contains 89 unique ID values, each duplicated 89 times, for a total of 7921 rows.

In case CTEs aren't your thing, you can use this approach to delete from a derived table, instead:

DELETE c
FROM (
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
) c
WHERE rn > 1;

The query plan for the above DELETE from the derived table:


Query from 1st domain and insert in 2nd domain

Your GRANT statement seems incorrect.

GRANT select ON `database-name.tableA`.* TO 'userB'@'localhost';

This grants the SELECT privilege on all tables (*) in a database called "database-name.tableA".

The correct statement would be this:

GRANT select ON `database-name`.`tableA` TO 'userB'@'localhost';

Since "database-name.tableA" isn't a valid database, userA can't possibly have permission to access it.  When a user tries to grant a permission to another use, the first user must have the privilege they are trying to give to another user, and the first user must have the GRANT OPTION.

You may be asking, "why doesn't the error message tell me there's no such database?"

The reason is because of security.  If you don't have permission to access an object, then you also, in most cases, don't have permission to know whether that object exists.  If the error message told you the object didn't exist, it would be giving you information that you aren't entitled to know.


This would work, although you might want to consider a physical backup solution with percona-xtrabackup (innobackupex) or the new mariabackup. Depending on the load on your cluster, you can even do these physical backups directly on one of the cluster nodes. (Though in my experience, xtrabackup does lock (FLUSH TABLES WITH READ LOCK) for about 15-20 seconds during backup of our 30G database.)
I don't have experience with this, but it sounds like it should work, obviously provided that when you restore from this backup you bootstrap the cluster from this node.


Second Normal Form (2NF) Normalisation Exercise

You have the right idea and have just gone a little wrong with the year column.
Background
The table given shows that Company Id + Year is the key to the table - meaning that each row must have a unique combination of the value of these two columns.  This is indicated by the solid line under these two columns.  A table is normalized when that table is a faithful representation of a relation.  To approximate a relation, the table must:

Have unique unordered rows
Have uniquely labeled unordered columns
Have a single value of whatever domain the column is defined upon in cell

In looking at the table in the exercise these conditions are met.  Once a table is normalized, it can be further normalized as a way to eliminate certain redundancies which occur due to functional dependencies between the columns.  By functional dependency we simply mean that the value of one column always determines the value of the second.  This is based on the mathematics of algebraic functions.  For example, the value 2, when plugged into the algebraic function 2x + 3 will always yield 7.  2NF is defined to mean that each non-key column in the table is fully dependent on the entire key - thus no partial key functional dependencies.
The Exercise
When inspecting the example table we can easily see Company Nameis dependent only on Company Id and not on Year.  Each time for example we see a value of 295 for Company Id we see a value of Miracle Holidays.  The repair is exactly as you have done - split Company Id and Company Name out into their own table where there can be a single unique row for each Company Id with the corresponding Company Name.  Now Company Name is fully dependent on the key -  Company Id.  Year simply stays in the original table and there is no need to redundantly split it out into a third table.
A Caution
The exercise is asking you to infer the functional dependency of Company Name on Company Id based solely on inspection of the data.  While in this simple example it is obvious, in the real world you cannot simply assume a functional dependency exists based on the existing data.  What appears to be a functional dependency might turn out to instead be coincidental.  Because of this, in the real world you would always ask the expert in the domain of knowledge the table represents what the functional dependency should be.
Getting More Information
Normalization is a very complex topic and I have glossed over many important concepts.  CJ Date has written an entire book with respect to it called Database Design and Relational Theory:  Normal Forms and all that Jazz.  While definitive, it is hard to grasp all the formalisms.  An excellent reference that presents the formalisms in language more easily understood by common practitioners is Fabian Pascal's Practical Database Foundation Series.  Studying both of these references - first Fabian's and then Date's - will give you all the information you need to master normalization as a repair procedure.


Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

The short version is that you should expect MIN(val) = MAX(val) to be better for rowstore queries in all cases and COUNT(DISTINCT val) = 1 to be better for columnstore queries when val is a string column.

I put 6.4 million rows into a table to test with. The data has a roughly similar data distribution to your sample data:

DROP TABLE IF EXISTS dbo.t224998_2;

CREATE TABLE dbo.t224998_2 (
gid INT,
val VARCHAR(20)
);

INSERT INTO dbo.t224998_2 WITH (TABLOCK)
SELECT
CASE WHEN q.RN % 7 <= 2 THEN q.RN - q.RN % 7
WHEN q.RN % 7 >= 4 THEN q.RN - q.RN % 7 + 4
ELSE q.RN END
, CHAR(65 + q.RN % 7)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

Here are the first 14 rows in the table:

Here are the query plans when running your pair of queries without any indexes:

The query with MIN and MAX only has a single hash aggregate operator. The COUNT(DISTINCT) query has two. For the second query, the rightmost operator only keeps the distinct rows and the leftmost operator performs the count. Not surprisingly, the DISTINCT query is about twice as slow.

Creating the following index makes the two queries more competitive:

CREATE INDEX IX1 ON dbo.t224998_2 (gid, val);

Now the plans look like this:

Now the distinct query is about 25% slower. It should be stressed here that for none of these plans are the MIN and MAX values "looked up". You're querying the table without a filter. SQL Server is going to scan all of the rows of the index or table. The index is useful because it can be scanned in key order and that allows the aggregate to be calculated more efficiently. For the MIN(val) = MAX(val) query, the stream aggregate reads the ordered rows and keeps track of the min and max value seen for each unique value of gid. It passes the row along to the next operator when it finds a new value for gid. At no point is an index seek performed to get the minimum or maximum value. You can write a query to do that but it's somewhat convoluted.

The COUNT(DISTINCT) query again splits the work up into two aggregates. Both aggregates take advantage of the ordering of the index. The rightmost one removes duplicate rows and the leftmost one performs the count.

If I change the table to a columnstore one without any nonclustered indexes the second query becomes the winner. Here are the plans:

The MIN(val) = MAX(val) performs all of the aggregate work in row mode. Aggregates that returns string columns are not supported by batch mode. This restriction is documented by Microsoft. COUNT(DISTINCT val) is supported by batch mode and as a result all of the aggregate work is performed in batch mode. That query is over twice as fast as the MIN(val) = MAX(val) option.


Can Postgres synchronous replication stall a cluster if secondary/standby crashes?

In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master?

Yes, by design. See the manual:

synchronous_standby_names
synchronous replication

  will the master simply stop functioning if the secondary/standby is inaccessible?

It'll still serve read-only queries, but commits will block and not return until the replica comes back up. If you can accept some transactions not being synchronously replicated and have SET LOCAL synchronous_commit = off in those transactions they'll commit without blocking.

  How does Postgres deal with this case?

As documented above you can configure multiple synchronous replicas to mitigate potential production impact. PostgreSQL does what you tell it to, and doesn't confirm commits until they're replicated.

If you don't want that, use asynchronous replication. The idea of "synchronous replication" where the master can keep on working while the replica is offline is nonsensical.


Optimize IN clause in where query with order by - MySQL

I was looking at a very similar problem today. After doing a ton of searching online, I found this great article by Percona.

Using "UNION ALL", you can join together a list of queries, like so:

(SELECT * FROM `test` WHERE user_id = 9898 AND status = 1 ORDER BY id LIMIT 30)
UNION ALL
(SELECT * FROM `test` WHERE user_id = 9898 AND status = 3 ORDER BY id LIMIT 30)
UNION ALL
(SELECT * FROM `test` WHERE user_id = 9898 AND status = 4 ORDER BY id LIMIT 30)
ORDER BY id LIMIT 30

It looks gnarly compared to select ... status IN (1,3,4), but it's effective in avoiding the filesort.

As long as the inner "SELECT..." statements are efficient, the performance is good.


App Engine Standard connection to Cloud SQL Latency Randomly

It's hard to tell where exactly the problem is because there isn't enough information. I'm not sure how your application implements connecting, so it's hard to judge if that's the problem or not. Additionally, it's impossible to separate network latency vs query latency without more information.

Here some advice that might potentially help:

Make sure you are using the /cloudsql/<INSTANCE_CONNECTION_NAME> unix socket to connect. You can follow the instructions here. This will likely provide the lowest latency.
Make sure you are opening/closing connections correctly. You should be opening a connection, performing your queries, then closing the connection. Look up how best to do this in PHP given your stack.
It looks like you are using a db-g1-small instance. This is a shared-core instance, and isn't recommended for production use (this is a particularly strange decision since you are also using a failover). Try upgrading to a machine size that is covered under the SLA.


PostGIS error building with `make` in ./extensions: make[1]: *** No rule to make target

You must first run,

make;

in root, then you can run make in ./extensions


SQL Server Disk I/O Latency During Specific Timeframe

This is too long for a comment, but there won't be enough in here to provide the answer as to WHAT is causing this, just HOW to work out what IT is.

Firstly: What applications are connecting to databases on that server, and are they running batch processes against databases that have grown faster than you've kept track of, thus extending the time required to complete?

Are any backups being taken at this time? Context switching is really high, so there could be backups conflicting with batch processes running at the same time.

Here's an example of how you could capture the job/query causing your issues:

Download the WhoIsActive stored proc by Adam Machanic from http://whoisactive.com/. Run it against an Administration database you might have on the server, or in the master database.
Create a table that can capture the output of WhoIsActive to a table (http://whoisactive.com/docs/06_options/)
Create a SQL Agent job that runs executes sp_WhoIsActive every 15 seconds (this is my preference, change to suit yours) and log the data to the table you created.
Create a set of PerfMon counters (using https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/) on the SQL Server and log the data to CSV.
If the server is a VM, ask your VM Admin for server stats for the given period. vRealize Operations contains a lot of useful reports for proving a point.
Get some reports from your SAN Admin showing SAN activity during the problem period.


Is using DISTINCT as a hint in subqueries useful?

When wondering about things like this you should compare the execution plans for your queries.

The shape of the execution plan for your query will of course differ depending on how many rows you have in your tables and what indexes is defined.
One scenario that shows there is no difference in performance is when there are substantially more rows in A than there are in B. The optimizer will then choose B as the driving table in a nested loop join against A. In order to get a correct result back it has to use a Stream Aggregate on table B in both queries to get only the distinct rows from B. So in this case the distinct keyword has no impact on performance.

The execution plan for two other obvious cases to test, more rows in B than A and equal number of rows in the tables, also shows the exact same execution plan for the queries.

Update

Before query optimization takes place the query goes through a simplification phase. You can see what the logical tree looks like using the trace flag 8606.

The Input Tree for the queries are clearly different but after simplification they are the same.

Ref: More Undocumented Query Optimizer Trace Flags and Query Optimizer Deep Dive – Part 2

Input tree and simplified tree for query using distinct:

*** Input Tree: ***
        LogOp_Project QCOL: [xx].[dbo].[A].SomeColumn
            LogOp_Select
                LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
                ScaOp_SomeComp 2
                    ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                    LogOp_GbAgg OUT(QCOL: [xx].[dbo].[B].SomeColumn,) BY(QCOL: [xx].[dbo].[B].SomeColumn,)
                        LogOp_Project
                            LogOp_Project
                                LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
                                AncOp_PrjList
                            AncOp_PrjList
                        AncOp_PrjList
            AncOp_PrjList
*******************
*** Simplified Tree: ***
        LogOp_LeftSemiJoin
            LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
            LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                ScaOp_Identifier QCOL: [xx].[dbo].[B].SomeColumn
*******************

Input tree and simplified tree for query not using distinct:

*** Input Tree: ***
        LogOp_Project QCOL: [xx].[dbo].[A].SomeColumn
            LogOp_Select
                LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
                ScaOp_SomeComp 2
                    ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                    LogOp_Project
                        LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
                        AncOp_PrjList
            AncOp_PrjList
*******************
*** Simplified Tree: ***
        LogOp_LeftSemiJoin
            LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
            LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                ScaOp_Identifier QCOL: [xx].[dbo].[B].SomeColumn
*******************


Use a Derived Table/CTE to get a single ID for "approved"/"not approved" and LEFT JOIN it two times to Employees:

SELECT TaskID
   ,MAX(CASE WHEN IsApprove = 1 THEN EmployeePermission end) AS Approved_ID
   ,MIN(CASE WHEN IsApprove = 0 THEN EmployeePermission end) AS Not_Approved_ID
FROM "Employee Approval"
GROUP BY TaskID


Optimized query to fetch a record from 10 million entries

The query to accomplish this would be of the form:

SELECT t.UniqueKey
  FROM mytable t
 WHERE t.UniqueKey = ?
   AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE
   AND t.timeStamp <= NOW()
  LIMIT 1

(This query assumes that the timeStamp column is defined as datatype TIMESTAMP.)

The query will either return one or zero rows, which will indicate either that the specified UniqueKey does "exist" in the past 5 minutes, or that it "doesn't exist".

That query cries out for an index:

... ON mytable (UniqueKey,timeStamp)

If UniqueKey is truly UNIQUE within the table, then an index on just UniqueKey is satisfactory. Can't really determine what's best without knowledge for the column datatypes, storage engine, existing keys/indexes, data distribution, et al.

UPDATE

(the answer above was copied from StackOverflow, where the question first appeared.)

OP has updated question with sample data, illustrating that the timeStamp column is probably NOT a MySQL TIMESTAMP, but appears to be an integer value.

If we assume that it's stored as an integer datatype (e.g. INT, BIGINT, etc.), and that the integer value that is ascending in datetime order... then the most efficient query would be of the form:

SELECT t.UniqueKey
  FROM mytable t
 WHERE t.UniqueKey = ?
   AND t.timeStamp >= ?
   AND t.timeStamp <= ?
  LIMIT 1

The values are supplied in the predicates (to compare to timeStamp are assumed to be integer values. That's just an assumption, we don't have knowledge here that they actually ARE integer values, and we don't have knowledge of how the datetime is encoded.

It's very likely that timeStamp value represents the integer number of seconds since the beginning of the epoch (midnight Jan 1, 1970 UTC). That's a widely adopted convention, but it's still just a convention, not a "rule" that all columns with a name of timeStamp have values that are encoded this way.

So, I hesitate to make any further recommendations based on assumptions. MySQL does provide functions that make conversions possible, some very convenient. But without knowledge of what values are actually being stored in the timeStamp column. Those recommendations would be based on assumptions.


"Print" specific columns through foreign keys

Table Users has the id column, that column is related on
  Users_request.id finally Users_request.id is related with
  Users_applications.id

So Users.id = Users_request.id and Users_request.id = Users_applications.id which means that Users.id = Users_applications.id

Then you can do:

SELECT Users.id, Users_applications.name
FROM Users INNER JOIN Users_applications  ON Users.id = Users_applications.id
WHERE Users.id = 'User1';


If you must poll, see this article by a former colleague of mine.

  Most places I’ve worked at have had some sort of requirement for
  picking up new or changed rows from a table in the database. It’s only
  recently that I’ve found, what I would consider to be, a clean way of
  doing this.

He is particularly interested in capturing a stream of changes, but your use case sounds like you want to do a form of cross-language IPC via the database. I personally do not like polling; it's inelegant. There are a few approaches for this:

AQ. I link to the 9i docs so you can see that this is a well-established feature of Oracle. This is my own favourite technique for this kind of application. One interesting thing about AQ is that you might not even need to tell your app to wake up and fetch the data; you could send the data in the payload!
DCN. This is very straightforward to use from OCI, but I have never tried it with Java so can't comment.
DBMS_ALERT, as Leigh mentions
DBMS_PIPE a similar mechanism
Direct calls from PL/SQL into C using EXTPROC. From C you can call any other language, including Java. From that link there is some sample code on Github for doing this; it's actually quite straightforward.
If you want to get really exotic, you could do this with Oracle Coherence - I do this via C in my example, there is also a Java binding.


SQL Server mirrored backup using cluster shared volumes

I hope this is Partial answer and this might helpful to you.

Clustered Storage: clustered file system is a file system which is shared by being simultaneously mounted on multiple servers. There are several approaches to clustering, most of which do not employ a clustered file system (only direct attached storage for each node).

Local Drive Z: Microsoft says "drive", what they actually mean is a partition - a formatted block of space on an existing physical drive. In your case, the "Z" partition is most likely the EFI System Partition (ESP) which holds the boot information for the OS.

Cluster Storage and Local Drives are different from each other.

Hardware Requirements for Backup Mirrors:

Multiple-storage servers working together to respond to multiple read and write requests. clustered file system is a file system which is shared by being simultaneously mounted on multiple servers.

Mirroring applies both to disk and Storage file system (disks do not support continuation file system). As in earlier versions of SQL Server, all backup devices for a single backup or restore operation must be of the same type, disk or tape.

Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent,such as only drives with the same model number from the same manufacturer.
or use same Cluster storage file system for Backup and Mirror paths with different folders.

This might work:

BACKUP DATABASE [msdb]
TO DISK = N'C:\ClusterStorage\BackupSQL\\some_path_and_name.bak'
MIRROR
TO DISK = N'C:\ClusterStorage\MirrorBackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT


Test if a string is a palindrome using T-SQL

If you are using SQL Server you can use the REVERSE() function to check?

SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome;

Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function:

SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


Generally speaking, adding AD groups to database roles and granting the permissions to the roles gives you some separation of purpose. In that case you would use them as:

AD Group = a set of logins that share the same database access needs.
Role = a set of permissions that define a set of rights to be shared by logins.

The advantage is that the role can have more that one AD group added to it.  Likewise, if the AD Group is removed, the permission sets remain in place.  That way the original AD Group, a new AD Group or groups, could be added without needing to redefine the rights.

And, of course, more than one AD Group can use a role and an AD Group could use several roles.  It all depends on how you want to use and reuse permissions.

If the rights are granted directly to an AD Group, then dropping the group also drops the rights that the group had.  Which would mean recreating those rights if you need them elsewhere.


Your model completely ignores the fact that books come in multiple copies.

Each copy of a book can only be in one place at one time, but each place could have multiple copies of any given book.

Since each copy of a book will have exactly the same ISBN, the answer to your first question is: "No, ISBN is not a sufficient primary key for Books."

What you need to account for is a Title entity.  This is going to be a combination of what you're thinking about for your Books entity.  A Title is an abstract object.  It will have a title, authors, publication date, and so forth.  You might even want to consider making a distinction between Title and Edition, since a book can be republished many times with variations each time.

Whether or not you want to distinguish between editions of a title, you must at least distinguish between Title and Books.  The answer to your second question is that each physical book will be in one place (either at a Library or on loan to a Member - or in transit between branches, or lost, etc. - depending on how sophisticated you want your model to be).

On the other hand, Titles, (or Editions) will be many-to-many with locations.


"Prelogin failure" when attempting to run stored procedure across linked server

I've seen it happen twice after SQL Server service packs were applied - service packs that took a really long time (hours) to finish the upgrade, or broke as they were trying to apply changes to the system databases.

The fact that you mentioned there was a scheduled restart clues me in, too - that means someone had probably staged updates to happen on restart, and they did - but then they caused an outage.

As a result of this, I'm never a big fan of automatically applying updates (Windows or SQL) on SQL Servers.


Sudden Query Performance Degradation On A Linked Server

If I'm reading the plan and operations correctly, it looks like those nested loops joins with the inner remote operations are the problem.

They were estimated to execute ~750 times apiece, but actually executed almost 6,000 times each. Normally that might not be such an issue, but if network latency is involved with every call, the query becomes extremely sensitive to network conditions. This would certainly explain the ASYNC_NETWORK_IO waits you're seeing.

In any event, I would stick with your workaround -- it's probably better to protect the tables behind a view anyway instead of trying to do all the joins remotely.


Creating an index in both asc and desc directions

I'd be surprised to hear of a modern database that can't do reverse order index scans.

SQL Server certainly does and the backward scan is indicated in the execution plan.
Firebird (search for backward in the page text).
MySQL


Ah, after you updated your comment I realized that you were asking about how to start another process after all databases have been restored/loaded. You can configure a stored procedure to be called using the sp_procoption. I am pretty sure that runs after all databases are ready. Next, you just need to invoke your external process via sql server.

"...Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup...."


Looking for ideas on maintaining data warehouse in close to real-time - ETL tools, etc

If you have the Enterprise version of SQL Server, then I would recommend doing this using Change Data Capture (CDC). This is much easier and cleaner than setting up triggers to watch for changed data. Plus it reads the transaction log for changes and then logs those changes in system tables.

You can use SSIS for your ETL. If you have 2012 installed, there are some very handy CDC components available. However you could also use regular components like the OLE DB source and query the built in CDC functions that SQL Server installs when CDC is enabled.

I have used CDC and SSIS to build an integration that is keeping 2 systems in sync while we are transitioning from one to another and it runs every 10 minutes without issue. The key is to keep your SSIS packages as light weight as possible. This means moving lookups away from full cache and other things like that. Ideally, the more often the job runs, the less data you are transmitting each run.

One other thing to consider if you are worried about putting stain on your source system would be look into seeing up an Availability Group read only replica for your source in asynchronous mode. This would allow you to offload your reads from your OLTP database. This does require Enterprise Edition and 2012 or higher though.


SQL Injection is hard to track from SQL Server side. If you want to find out who change what, then some sort of tracing should be running on your server.

If no tracing (custom one implemented by your company DBA or sysadmin) is running, then you can check

windows event logs
sql server error logs
Default Trace for objects modified.
Successful and Failed logins (if you have enabled on sql server side).

Above all are few areas to look into, but it will be difficult for you to corelate and find a pattern.

Best is to scan your web-server IIS error logs using tools like LogParser.

Additional References :

SQL Injection: Defense in Depth
How to Secure Microsoft SQL Server


No there isn't anything similar to what you are asking built into the product.

Standard SQL has the concept of a NATURAL JOIN that joins on common column names but SQL Server does not implement this and there would be no guarantee that any such names would correspond with the PK anyway.

It would be possible in theory for you to write some sort of script pre-processor or dynamic SQL code generator that did this if it was a massively important need for you.


50% CPU usage on MySQL RDS instance (db.m3.medium) with 0 connections

A possible reason:

The "Change buffer" queues up index changes for eventual writing to disk.  If you did a lot of INSERTs/UPDATEs/DELETEs on table(s) with non-unique indexes, the CPU could have been busy for some time after the queries "finished".

The reboot may have only coincidentally cleared up the CPU.  The change-buffer work must be done eventually, even after a reboot.


I do not see SQL Server instance name under roles section of Fail over cluster manager screen! How to setup?

You have to actually create the Availability Groups first, then it will show up in FCM. You have basically finished the prerequisite steps to allow an Availability Group to be created...so let's go create one!

Sounds like you have an instance on each VM, create or put a database on one of the instances
Open SSMS and connect to the instance with the database
Expand AlwaysOn High Availability and right click Availability Groups
Start the New Availability Group Wizard
Give your new AG a name
Pick a Database to join to the AG...it needs to be in full recovery mode, and need to have had an initial backup (the wizard will identify these for you under status)
Pick how you want the other node to be joined to the AG (the wizard will backup, restore in norecovery mode, and then join on the other node or you can do it manually, or do it later)
Let the wizard create the AG

This is the very general outline, go to Brent Ozar's site and download the AlwaysOn Availability Groups Setup Checklist to really do things right!


First, make sure the MongoDB service is running in the remote machine.

Next, check if the MongoDB port is open to the outside network or not.

The following will help you check if the 27017 is accessible from an outside machine.

nmap -Pn 13.56.140.11

If the port is not enabled, use the following command to expose the port.

sudo iptables -I INPUT -p tcp -s 0.0.0.0/0 --dport 27017 -j ACCEPT


Regular expression in a SQL case statement

May I suggest doing it in a couple of passes:

SQL> select * from records;

SUBJ_NO
--------------------------------------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

SQL> l
  1  with strip as(
  2  select translate(subj_no, '/-', '  ') subj_no
  3    from records
  4  )
  5  , reg as (
  6  select regexp_substr(subj_no,'^[A-Z]{2,3}') col_2
  7   , trim(regexp_substr(subj_no,'[0-9 ]+')) col_3
  8    from strip
  9  )
 10  select col_2||col_3 col_1
 11   , col_2
 12   , col_3
 13*   from reg
SQL> /

COL_1                COL_2      COL_3
-------------------- ---------- --------------------
CBS405 505           CBS        405 505
BR404 504 604        BR         404 504 604
AP490                AP         490
APR501               APR        501
MP300                MP         300
ACX501               ACX        501
ACX400               ACX        400

7 rows selected.

Strip replaces the slashes and dashes with spaces so they don't get in the way later.

Reg rips off your regexp and adds another one that finds the first group of digits and spaces, trimmed for neatness.

Last one takes the results and formats them as you requested.

Edit:  If I've understood your comment below then you have all those values in a single row.  Slice it up with a pipelined function.

SQL> select * from records;

SUBJ_NO
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

1 row selected.

SQL> create or replace function get_records return tt_subj_no pipelined as
  lv_recs  records.subj_no%TYPE;
  lv_subj  varchar2(100);
  lv_start number := 0;
  lv_end   number := 10;
begin
  select *
    into lv_recs
    from records;

  lv_end := instr(lv_recs, chr(10));
  while lv_end > 0
  loop
    pipe row(substr(lv_recs, 1, lv_end));
    lv_recs := substr(lv_recs, lv_end + 1);
    lv_end := instr(lv_recs, chr(10));
  end loop;
  pipe row(lv_recs);
end;
/

Function created.

SQL> select * from table(get_records);

COLUMN_VALUE
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

Stick that in the original query and you should be good to go.


Aside from invisible characters, the possibility of an OS patch changing the results of such an query exists through a change in locales.

An index involving text types is only valid if the underlying collation never changes. Postgres relies on the OS for comparing strings, through the libc, so a change in an OS collation may have the effect of an index becoming unusable, as if it was corrupted.

If there is an index on the column, you can test this hypothesis by issuing SET enable_indexscan TO off; before the offending query and see if it gives the same results. This check may also reveal an index corruption independantly of any problem with the collation.


How to use Join for two tables that have related data but in different column names

You have to specifically list the columns you are interested in, rather than simply stating *

The first table can remain like this:
SELECT PROJECT_FORM.*, …
then you list the columns you require from the other table one by one.

SELECT PROJECT_FORM.*, ASSIGNMENT.HSID
FROM PROJECT_FORM
JOIN ASSIGNMENT ON PROJECT_FORM.FORMID=ASSIGNMENT.FORMID


You can get an idea of what backups have been taken, and when, by looking at the result of this query:

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType =
        CASE bs.type
            WHEN 'D' THEN 'Database'
            WHEN 'L' THEN 'Log'
            ELSE '[unknown]'
        END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = @DBName
    OR @DBName IS NULL
ORDER BY bs.backup_start_date DESC;

By default, this will show you all backup activity that has occurred on the current instance since the last time msdb was created/restored, assuming backup history has not been deleted.


Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

I have mentioned ALGORITHM=INPLACE in two other posts

Mar 27, 2014 : When tables are locked does MySQL queue queries?
Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity

In the second post, I named some caveats on using online DDL

  The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to /tmp and the table you are changing is 7GB, there has to be more than 7GB free in /tmp. If /tmp is not large enough, please consider mounting /tmp on a large volume.

What I have mentioned my seem like overkill to discuss. Notwithstanding, the point I am making, to answer your question, is yes you can do INSERTs and SELECTs

CAUTION: You should test adding VARCHAR that has a default of NULL, an INT with a default and without a default in a Staging server just to see if you can live with any slippage in performance during INSERTs and SELECTs.

This new feature of online DDL is Oracle's answer to Percona's pt-online-schema-change.


Foreign key in main table or supplementary table?

Suggested that previous comment be posted as 'answer'.  Here 'tis.

Even if a justifiable one-to-one, I'd still treat the "primary" table as if it were a parent, and the "detail" as if it were one of  potentially multiple children. Thus the FK would go in the 'details' table. More appropriately named 'additional_student_details'.


MySQL used all drive space running an ALTER and now won't start

Find the file #sql-8aa_25 and remove it.  It is probably in the same directory where the database is.

If you did a kill -9, mysqld did not get a chance to remove the tmp file.


Remove a period if it is the first character in a varchar column

One option is using a CASE statement, such as this one:

CASE WHEN LEFT(MyColumn,1)='.' THEN RIGHT(MyColumn, LEN(MyColumn)-1) ELSE MyColumn END


How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I think this is what you want.  I had to make a few assumptions though.  First that a given item is identified by product and lot.  So I added a join between ShippingArchive and OrderItems based on these columns.   Next that in your report you only needed to see the product and lot information once and needed the requested, shipped and missing information for qty only.  I also made the assumption that missing meant requested-shipped.

Edited to handling multiple shipping for the same order.

WITH Shipped AS (SELECT Shipping.OrderReference, ShippingArchive.Product, ShippingArchive.lot,
                        SUM(ShippingArchive.qty) AS Qty
                FROM Shipping
                JOIN ShippingArchive
                    ON Shipping.ShippingId = ShippingArchive.ShippingId
                GROUP BY Shipping.OrderReference, ShippingArchive.Product, ShippingArchive.lot
                )
SELECT  [Order].OrderReference, OrderItems.Product AS Requested_Product, OrderItems.lot AS Requested_Lot, OrderItems.qty AS Requested_Qty,
    ISNULL(Shipped.qty,0) AS Shipped_Qty, OrderItems.qty - ISNULL(Shipped.qty,0) AS Missing_Qty
FROM [Order]
JOIN OrderItems
    ON [Order].OrderId = OrderItems.OrderId
LEFT OUTER JOIN Shipped
    ON [Order].OrderReference = Shipped.OrderReference
    AND OrderItems.Product = Shipped.Product
    AND OrderItems.Lot = Shipped.Lot


Use BCP utility

bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

The -c argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but -t , changes the field terminator to commas.  -T specifies Windows authentication ("trusted connection"), otherwise use -U MyUserName -P MyPassword.

This doesn't export column headers by default. You need to use a UNION ALL for headers

OR

Use SQLCMD

SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable"
-s "," -o "D:\MyData.csv"

OR

Use Powershell

Here is a link to an article. If you end up using this, you might want to append -notype after Export-Csv $extractFile to get rid of the unnecessary column on the output file.


Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I would recommend you to run the following

mysqlcheck -u ROOTUSERNAME -p --auto-repair --optimize DBNAME_HERE

This will prompt for MySQL Root Password, type in and it will  reclaim the disk space. Time taken for this process will depend on the size of the database. You may try this first in your test env and then proceed.


Transform/map json object array to primitive for GIN indexing

I wouldn't store the data like that in the first place, if I could help it.  Assuming that that ship has sailed, I would at least transform it to a native PostgreSQL array, not a JSON(B) array, as that would give you better statistics estimates and so probably better execution plans, as well as just plain faster execution.

Due to some arbitrary-seeming restrictions on the composition of set-returning functions with aggregations and expression indexes, I couldn't find a way to do this without the help of some helper function (which is probably a good idea anyway).

CREATE OR REPLACE FUNCTION public.helper(jsonb)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$
select array_agg(x) from
   (select jsonb_array_elements($1->'records')->>'tag') as f(x);
$function$

Followed by:

CREATE INDEX ON t1 USING GIN (public.helper(jsonb_colname));

And then:

select * from t1 where public.helper(x) && ARRAY['receipt'];

But taking a step back, you could do this directly using the built-in jsonb containment operators with no transformations or expressions at all (although the index will probably larger and slower than a tailored one):

create index on t1 using gin (jsonb_colname);
select * from t1 where jsonb_colname @> '{"records":[{"tag":"receipt"}]}';


mariadb: replication master and slave have equal MySQL server ids

Use RESET SLAVE ALL on the master to forget about is slave replication setting to itself.


Design for CE Events / Training Database

My wife is a nurse educator who had no even management platform. I developed one in a VERY similar fashion that you outlined here. The logic and thought process was the same.

You should be safe with this overall schema.


How to make IntelliSense understand temp tables, linked servers and synonyms?

In short, Intellisense isn't as intelligent as its name might suggest.

Temp Tables

If you're working with temp tables across different databases or separate query windows, Intellisense doesn't think the table exists, because, well, it doesn't as far as it knows. There are usually two scenarios this occurs:

At run time, we know that a first command will execute and by the time we get to running an additional command that the table will exist. But asking Intellisense to be aware of things that haven't happened yet is impossible. This usually involves the temp table being created by a different process, query window, etc. It generally only knows the scope of its own query window and objects in non-tempdb databases.
If it does already exist, being aware of everything already in the tempdb is probably a performance concern (just conjecture) because things move around in there fast, from moment to moment, and it'd be hard/unlikely to get a reliable enumeration of objects that was valid for more than a few seconds. Regardless, it just doesn't know about much outside of that single query window with regards to tempdb objects.

Linked Servers

This one sort of makes sense. Do you really want Intellisense going and querying your linked servers every time you are writing queries that involve remote objects? It just doesn't know and so assumes it doesn't exist.

Synonyms

If the synonyms are referring to linked server objects, then they'll suffer from the issue described above. On their own, however, they should work fine with local database objects.

Bottom Line

Learn to ignore it, or use a different IDE if possible (one that doesn't use Intellisense). Most people seem to like Intellisense despite its flaws, but you could always turn it off completely as well.

There is a hacky work-around for temp tables at this SO question but I personally would never do that, as you're increasing the complexity and reducing readability of your code for...a red squiggly line.


Backing up multiple instance System Databases with distributed Maintenance Plan

I am not that huge fan of Maintenance plans to use such strategy for Backups.

What suits the best to our environment is using ola.hallengren Backup scripts.

It backups by taking the initial folder as the location define by the user and later it itself identifies the instance that it is backing up and thereafter creates the folder respectively:

Give it a try , as its simple to execute and easy to maintain.


Sum of previous n number of columns based on some category

On SQL Server 2014 and 2016 1 you can use a WINDOW function (i.e. an OVER clause) to perform what you want:

SELECT
     category, year, week, value,
     sum(value) OVER (PARTITION BY category
                          ORDER BY year, week
                              ROWS 2 PRECEDING) AS retention_value_3_weeks
FROM
     t
ORDER BY
    category, year, week ;

And this is the result you'll get:

category | year | week | value | retention_value_3_weeks
:------- | ---: | ---: | ----: | ----------------------:
a        | 2016 |    1 |     5 |                       5
a        | 2016 |    2 |     7 |                      12
a        | 2016 |    3 |     8 |                      20
b        | 2016 |    3 |     6 |                       6
b        | 2016 |    4 |    15 |                      21
b        | 2016 |    5 |    25 |                      46
c        | 2016 |    3 |    25 |                      25
c        | 2016 |    4 |     2 |                      27
c        | 2016 |    5 |    21 |                      48
c        | 2016 |    6 |    26 |                      49

NOTE that the x = 3 of your example gets translated to (the current row and the 2 preceding ones).

If, for some reason, you can't use the OVER clause, you can still compute the same results using some (quite convoluted) subqueries:

SELECT
    category, year, week, value,
    (SELECT
        sum(value)
     FROM
        (SELECT TOP 3                    /* total number of rows to consider */
             value
        FROM
           t t2
        WHERE
               t2.category = t.category  /* partition by category */
           AND t2.week <= t.week         /* current and preceding rows */
        ORDER BY
           year DESC, week DESC          /* order by criteria */
        ) AS q
     ) AS retention_value_3_weeks
FROM
    t
ORDER BY
    category, year, week ;

Check it all at dbfiddle here

If you want to use @x instead of 3, you can do so:

DECLARE @x AS INTEGER = 3;

SELECT
    category, year, week, value,
    (SELECT
        sum(value)
     FROM
        (SELECT TOP (@x)                  /* total number of rows to consider */
             value
        FROM
           t t2
        WHERE
               t2.category = t.category  /* partition by category */
           AND t2.week <= t.week         /* current and preceding rows */
        ORDER BY
           year DESC, week DESC          /* order by criteria */
        ) AS q
     ) AS retention_value_3_weeks
FROM
    t
ORDER BY
    category, year, week ;;

dbfiddle here

1) Can't test with SQL Server 2012 because I don't have one. The documentation from MS SQL Server indicate it should be available since version 2008.


protecting network from external attack (SQL server 2012 bruteforce)

You can disable SA account and wait until attack ends. This is not a solution for the problem, but can buy some time to reconfigure your network.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
 GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
 GO


Effect of logical fragmentation of range scan in SQL Server Table

Can someone tell me why? Why is the physical order important at all?

Please note, I am speculating as to why the decisions were made given the best possible information I have available to me.
AFAIK it's due to the optimization of IO using a specific API which in this case is called ReadFileScatter.
ReadFileScatter requires a starting offset and a number of bytes to read from that point. Once read the number of contiguous on disk bytes are transferred into non-contiguous memory locations (aka buffers).
Because Read-Ahead, depending on version and edition, can read different amounts of data it ends up being a single large IO instead of a bunch of tiny ones which makes the throughput of the overall storage system more albeit at a cost to latency.
/Start of pure speculation/
Please keep in mind that when these APIs and choices were being made, rotational media (aka spinning rust) was the main source of storage and SSDs did not exist. Thus, having all of the items contiguous on disk means we don't have to reposition the track heads between reads which makes the overall single contiguous read faster than many smaller disjointed ones.
References and further reading:

Reading Pages (Books Online)
Sequential Read Ahead by Craig Freedman
How It Works: Bob Dorr’s SQL Server I/O Presentation
What do I need to know about SQL Server database engine I/O?

The first reference includes a diagram you may find helpful:


Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?

ANSWER #1
This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
echo %SQLSTMT%
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" > C:\windows\InnoDBCheck.sql

After you run this script, add this to my.ini
[mysqld]
init-file=C:\windows\InnoDBCheck.sql

Then, restart mysql
net stop mysql
met start mysql

during the startup, just after crash recovery, the
Forgive me for such a crude solution since I have very little dealing with MySQL for Windows (that's not sarcasm, I am serious).
ANSWER #2
Here is something more serious
I just checked my MySQL 5.6.10 no-install ZIP file. It has innochecksum.
C:\>dir \MySQL_5.6.10\bin\i*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.6.10\bin

01/22/2013  07:05 PM         4,065,792 innochecksum.exe
01/22/2013  07:05 PM         3,706,880 innochecksum.pdb
               2 File(s)      7,772,672 bytes
               0 Dir(s)  160,596,770,816 bytes free

C:\>

Download that ZIP and try running that .
You could then collect all the .ibd files with something like this:
cd "C:\Program Files\MySQL\MySQL 5.5\data"
dir *.ibd /s/b > C:\ibdfiles.bat

You can then edit C:\ibdfiles.bat and prepend innochecksum against every filename.
Sorry this is not a full answer, but at least you can a hold of innochecksum.exe


Can't get my SQL query to filter by dates like I want

!= doesn't support a list of items like that.

Try one of these (note the commas between items):

WHERE Format([Start Date], 'MM/yyyy') NOT IN ('07/2016','08/2016','09/2016',...)

or even better (and faster), without converting to text (if your date range is truly consecutive like your example):

WHERE [START Date] NOT BETWEEN '2016-07-01' AND '2017-07-31 23:59:59'

Since between is inclusive of the endpoints, that is equivalent to:

WHERE [START Date] >= '2016-07-01'
AND   [START Date] <  '2017-08-01'


Sql Server's Buffer Pool is a wonderful thing.  It's smart enough to handle all sorts of situations in a fairly intelligent way.  Here are a couple examples showing how at first glance the buffer pool behavior seems strange, but is actually fairly clever.

Consider a 400 GB clustered index on a server with 64 GB of Memory available for the buffer pool.  If a query scans the entire 400 GB index, how much buffer pool should the index use after the query is complete?

Probably not very much.  If Sql Server aggressively cached all the data it accessed maybe the last 50GB that it read during the scan is in memory. However, the next time you run the same query, that 50GB is useless because Sql will have to read the first 350GB from disk before it gets to the last 50GB.  This is a common enough scenario that Sql handles it intelligently.  Instead it is more likely that all of the intermediate level index pages will be cached along with a few of the leaf pages.

I've got a server with 512GB of memory, but it never seems to use all of it, or it uses it..strangely?

Sql Server is NUMA aware.  On NUMA (Non-Uniform Memory Access) systems, some memory is closer/faster to a CPU than other memory.  For example, a 4 socket system might have 4 memory controllers (1 per socket).  It is faster for a CPU to access memory on the controller attached to it vs accessing memory through some other cpu's controller. (I've seen 1.4-1.7 times slower access).

Sql Server knows this, and will prefer to allocate local node memory before remote node memory. Since requests can run on any cpu (they don't typically jump between them) and hence allocate memory on any node, the first request to access an index will cache it's pages in local memory.  In most cases, you'll end up with a fairly equal distribution of cached pages, however, each node is managed individually by sql server, so effectively, your 512 GB is really four 128GB buffer pools.  You could have the situation where node 1 is using all 128GB, node 2 is using 80GB, and pages are getting discarded from node 1 to make room while node 2 has lots of free space.

Why would my heavily read from indexes be cached less than indexes only receiving writes?

One possible reason is checkpoints.  Depending on your recovery interval, it may be several minutes after a data change occurs before a checkpoint writes the page to the data file (it's already in the log file on disk).  During this time, the page can't be discarded to make room in the buffer pool.

Besides setting the size, are there other ways to change how the buffer pool behaves?

There isn't a whole lot of control over the buffer pool and the decisions it makes. That's fine because you typically don't need to tweak it.  Watching perfmon counters for "SQLServer:Buffer Manager" and "SQLServer:Buffer Node" can provide insight into what the buffer pool is doing, whether it is sufficient in size, whether your IO subsystem can keep up with the checkpoints.

Regarding a couple of items you mentioned:

Fragmentation on disk doesn't affect memory usage directly. However, if an index has 50% free space on the pages due to deletes, it will take up twice a much room on disk and in memory, but that's related to free space, not fragmentation.
PAGE_IO_LATCH Waits measure the time it takes for the IO subsystem to load pages into memory.  They are an indicator of disk performance and not memory.
You are correct, high page lock waits are due to the writes. The writes make pages dirty, and dirty pages have to stick around in the buffer pool until they get checkpointed to disk.


DATE(added_time) = CURDATE()

is the root of some of the evil

Do not "hide" a column (added_time) inside a function (DATE) if want an index to be used.

If added_time is a DATE, then you can simply do

added_time = CURDATE()

If it is a DATETIME or TIMESTAMP, then do

added_time >= CURDATE() AND
added_time  < CURDATE() + INTERVAL 1 DAY

Then your INDEX(added_time) can be used.


Generated bug data script how not to set the IDENTITY in the insert statements?

bcp is probably a better way to achieve this and works well with Azure SQL Database.  It's a command-line tool that enables you to extract and  load data easily.  For example:

bcp "dbo.yourTable" out c:\temp\temp.bcp -S "yourSourceServer" -d "yourSourceDatabase" -T -n

bcp "dbo.yourTable" in c:\temp\temp.bcp -S "yourTargeteServer" -d "yourTargetDatabase" -U someSQLLogin -P somePassword -E -n

Specify the -E switch in the bcp in statement to keep your IDENTITY values.  Replace the table, server and database names in my template above and see if you can get the first statement to run.  Note in the first example I am using Windows Authentication (-T) but SQL Authentication in the second one (-U for User and -P for password).  The main MSDN page for bcp provides some good examples:

https://msdn.microsoft.com/en-us/library/ms162802.aspx

If your table already has some data in it, consider inserting your data into a staging table first, then INSERT to your main table, eg

SET IDENTITY_INSERT yourTargetTable ON
GO

INSERT INTO yourTargetTable ( columnList )
SELECT columnList
FROM yourStagingTable s
WHERE NOT EXISTS
    (
    SELECT *
    FROM yourTargetTable t
    WHERE s.yourPrimaryKey = t.yourPrimaryKey
    )
GO

SET IDENTITY_INSERT yourTargetTable OFF
GO

There are other methods of achieving this.

For example, you could accomplish the same thing using SQL Server Integration Services (SSIS) but there is some overhead to creating an SSIS project.
For example, if your table is not too big, create a copy of your table in tempdb using something like SELECT [all columns except the identity column] INTO tempdb.dbo.scratchTable FROM yourMainTable.  Run the Generate Scripts option against this copy then delete your scratch table.


It seems you are using Xtrabackup-v2 method for SST.

So if you are using that, you should set RELOAD, LOCK TABLES, REPLICATION CLIENT and SUPER privileges to work correctly.

If you do not want set directly user pass in my.cnf you cant start mysql server with this command:

service mysql start --wsrep-sst-auth=username:password


Store table names to reference other tables

Storing table names in other (user) tables is an instance of storing structural metadata and user data side by side.  This has a variety of interesting effects, and some of those effects make it attractive to designers.  The down side is less apparent.

The down side is this:  you end up doing a lot of data management "by hand" in the SQL that a different design might have allowed the DBMS to do for you.  This makes your code harder to maintain and slower to run.

Having said that, I'll admit to having pulled this stunt several times, and the results were usually good enough so that I didn't regret the choice.

There's a second thing going on in your case.  Types 1 and 2 of both widgets and products are cases of class/subclass modeling (or, if you prefer type/subtype modeling).  This kind of thing is simple and straightforward in object modeling, because inheritance takes care of most of the difficulties for you.  Not so in relational modeling.  Relational modeling, as such, has no mechanism for inheritance.  Some variants of SQL have extensions that make inheritance easier to model and to implement.

There are many case of questions here in the DBA area that boil down to the question of how to implement subclasses (or subtypes) in SQL tables.  Some of those questions are grouped under this tag:  subtypes.  Over in StackOverflow, there are even more such questions, and there are three tags that relate to three design techniques that might help:  single-table-inheritance, class-table-inheritance, and shared-primary-key.

Your design resembles a class table inheritance design, except that you use embedded table names instead of shared primary key to implement the IS-A relationships between subclasses and classes.  You might want to explore using shared primary key, and then creating views that collect all the data for each subclass by joining the superclass table with each subclass.  I'm not sure you want to go this way.  It could get awfully unwieldy once you have hundreds of different product types.


How would you store permutations of a set of simulation parameters, optimizing for storage and efficiency of parsing?

Why do you think that obscuring real data into a bit-masked field will be faster? You are only talking about 1 million rows and 24 bytes per row. That really isn't much, especially since you are only doing singleton / seek operations.

A better optimization would be to use TINYINT fields (range of 0 - 255 ; byte in .NET). Using TINYINT instead of INT (and assuming a FILLFACTOR** of 100) would result in:

the table would effectively be 10 MB instead of 28 MB (1 byte per each of 6 fields + 4 bytes for the INT [ID] field = 10 bytes per row).
you would get 806 rows per 8060-byte data page instead of 287 with six INT fields

The main efficiencies gained here are:

quicker initial lookup time for 2244 records. How so? With 1 million rows, 287 rows per 8k data page equates to 3484 pages, while 806 rows per 8k data page equates to only 1240 pages. Once a page is read to get the first row's values, it should stay in the buffer pool (i.e. memory) for subsequent lookups (i.e. the other rows on that same data page). More rows per page means fewer pages to read from disk--2244 fewer pages in this case. But once all of the pages are in the buffer pool, the lookups should be the same speed whether it was 806 rows or 287 rows per page. So, 2244 lookup operations out of the 1 million are slightly faster. Certainly not bad since there was no cost associated with this optimization, but just wanted it to be put into perspective.
less memory required to hold the entire table in memory. this will make it more likely to remain in memory.

Lastly, it is definitely possible to squeeze this data into an even smaller representation. But at this point it is 10 bytes per row with no complication added to the processing / coding and all of the values are human readable. There isn't a lot of room to go below 10 bytes per row, but even if it got down to 7 bytes per row (INT IDENTITY + 3 bytes for Parameters, which should be the minimum required to handle these ranges), I am not sure the added complexity would be worth it (i.e. diseconomies of scale).

CREATE TABLE dbo.Experiment
(
  ExperimentID INT NOT NULL IDENTITY(1, 1),
  Parameter1  TINYINT NOT NULL,
  Parameter2  TINYINT NOT NULL,
  Parameter3  TINYINT NOT NULL,
  Parameter4  TINYINT NOT NULL,
  Parameter5  TINYINT NOT NULL,
  Parameter6  TINYINT NOT NULL,
  CONSTRAINT [PK_Experiment] PRIMARY KEY (ExperimentID) WITH (FILLFACTOR = 100) ON [Tables]
)
ON [Tables];

And you can populate it using the following (which assumes two parameters of 0 - 4, two parameters of 0 - 7, and two parameters of 0 - 9 ; based on comments on the Question):

;WITH Five(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
), Eight(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
), Ten(num) AS
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
  UNION ALL SELECT 9
)
--INSERT INTO dbo.Experiment
--     (Parameter1, Parameter2, Parameter3, Parameter4, Parameter5, Parameter6)
SELECT f1.[num] AS [Parameter1],
       f2.[num] AS [Parameter2],
       e1.[num] AS [Parameter3],
       e2.[num] AS [Parameter4],
       t1.[num] AS [Parameter5],
       t2.[num] AS [Parameter6]
FROM   Five f1
CROSS JOIN Five f2
CROSS JOIN Eight e1
CROSS JOIN Eight e2
CROSS JOIN Ten t1
CROSS JOIN Ten t2;

The query above only generates 160k permutations, not 1 million, again based on comments on the Question. But it should be should be fairly easy to figure out how to adjust it for different ranges of values for any combination of the six parameters.

** FILLFACTOR: Rows are stored on data pages that are roughly 8060 bytes. Smaller rows = more rows that fit into that fixed size container. FILLFACTOR is how much space to reserve (when creating / rebuilding an index) for new rows or updates that take up more space due to variable length fields requiring more bytes for the new version of the row. A value of 100 means that there is no reserved space, that 100% of the 8060 should be taken up by current rows. In this scenario there are only fixed length fields and no out of sequence inserts.


Listener is not starting in my Standby database

Can you try the below,

run the cmd as administartor and verify the ORACLE_HOME and PATH variable and try lsnrctl start
If it still fails to start, provide listener.ora file


If they are Windows domain accounts/groups, you shouldn't have to worry about it as they should be identical on both nodes.

The orphaned user issue and SID syncing across both nodes is only applicable to SQL authorization logins.  With SQL authentication, you will have to sync the SIDs as, after a failover, the SIDs wont line up between the server level SID and db level SID.


Sometimes you overlook the basic tools; Microsoft Access Imported the file straight up.

Opened a new Blank Database in Access

External Data -> XML File -> Select the file.

Then I pointed my Access database to an SQL Server backend and just used SSMS to generate the DDL Statement to make the schema portable.

Thanks for the help!


Enabling TDE on SQL Server AOAG 2014 Databases - Guidance

1 Now the Databases are TDE free ,what will be the new changes or
  effects which I will be seeing post enablement of TDE in my PROD site.

The data at rest is encrypted, the data in memory not.
The encryption might take a while, depending on your database size, you can follow this up with:

SELECT DB_Name(database_id) AS DB, encryption_state
FROM sys.dm_database_encryption_keys;

Your backups will go slower (10-20 %) and use more cpu
If you are going to disk a lot (not enough memory, bad queries , ...) then I would not enable TDE before fixing this.

If any filegroups are read only, then enabling TDE will fail.
There are other restrictions, found here:
https://docs.microsoft.com/nl-nl/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

I would also advise to test TDE on a DEV server first.

  2 What is the performance impact on my existing PRODUCTION database
  accessibility.

  &

  3 Will there be any performance impact at all ? What is the range of
  impact after enabling TDE on my DBs ?

The data at rest is encrypted, the data in memory is not.

Your backups will go slower (10-20 %) and use more cpu.

When modifying data you will see some overhead, since it will have to be encrypted on disk.

If your disk access is low, then the overhead will be lower
and TDE will have a lower impact on performance.

  4 What will be the effect on the AOAG component Failover part ? Will I
  face issues while the Databases are with TDE enabled ?

On the secondary, do the following:

1 Make backups of the master key and the certificates

2) Create a master key

3) Restore the key and Certificate from the primary on the secondary

4) Restore the database on the secondary

5) add the database to the AG

(more info here: https://blogs.msdn.microsoft.com/sqlhenryweng/2017/11/08/configure-tde-encrypted-database-in-sql-server-alwayson-availability-group/)

  5 What will be the affect while Backing up or Restoring the Databases to
  different SQL Server ?

To do restores from the backups you need to have the same certificate that was used for encryption. This certificate is protected by the master key.

To accomplish this:

1 Make backups of the master key and the certificates
2 Restore the key and certificates on the new principal and mirror pairs

Extra checklist to migrate to a new server:

There's a database master key in the master database of the new server.
The certificate used to encrypt the database is restored along with its private key.
Restore the database

  6 If I enable TDE on Primary Replica and all secondaries will I be able
  to add the DBs to AOAG 2014 using the Wizard.

See Question 4 for more info on this.


What is a Data Source and where can I find it in SSMS?

Yes, Initial Catalog refers to the database name and Data Source refers to your server name.

See more on SQL Server connection strings


postgres: do I really need to use Collate and Ctype when restoring database?

The main consideration is that if you use ORDER BY with a column of a string data type, the result will be sorted according to the database collation unless you specify an explicit COLLATE clause for the column definition or with the ORDER BY. If your users expect the results sorted according to Hebrew sorting rules, they might be unhappy.
To get a Hebrew collation on Unix, use the locale he_IL.utf8.


Why can't I restore the DB from an SQL dump in PostgreSQL 9.4 (problem with dblink)?

The comment from @a_horse_with_no_name helped me solve the issue i.e., the extension was not installed in the right schema when attempting to restore the DB. When moved to the right schema the restore finished with no errors.


Please provide further details about what happened previously. Did a hardware failure occur? Also, refer to the .trc files that generally follows ORA-00600 errors in alert.log. If you're on linux and made a default installation, alert log will probably be located at $ORACLE_BASE/diag/rdbms/sid/sid/trace. It contains the main DB events, and it's the first approach while diagnosing Oracle Failures.


CPU Usage Incorrect in sys.dm_os_ring_buffers

Microsoft says that DMV (ring buffers) doesn't work on SQL Server 2017, only 2019:

  Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL
  Server by built-in tools as well as third party monitoring utilities.
  When SQL Server 2017 was released on Linux, unfortunately this DMV did
  not return correct CPU usage information by SQL Server process. SQL
  Server team is glad to announce that the starting with SQL Server 2019
  release candidate, the sys.dm_os_ring_buffers DMV returns SQL CPU
  utilization correctly.


How do I (safely) kill long running operations in MongoDB?

This can be a little tricky, but the fact that the MongoDB shell is basically a Javascript interpreter gives us decent options in terms of filtering.  Here is the function I use to accomplish this:

// kills long running ops in MongoDB (taking seconds as an arg to define "long")
// attempts to be a bit safer than killing all by excluding replication related operations
// and only targeting queries as opposed to commands etc.
killLongRunningOps = function(maxSecsRunning) {
    currOp = db.currentOp();
    for (oper in currOp.inprog) {
        op = currOp.inprog[oper-0];
        if (op.secs_running > maxSecsRunning && op.op == "query" && !op.ns.startsWith("local")) {
            print("Killing opId: " + op.opid
            + " running over for secs: "
            + op.secs_running);
            db.killOp(op.opid);
        }
    }
};

This will only kill queries above the maxSecsRunning threshold and will not touch anything running against the local database, which is where the oplog lives (and hence is the database that is involved in the long running replication ops.  It is relatively easy to add criteria to the inner if conditional to more precisely target operations as needed based on specific needs.

The code is also available as a gist (where I will remember to update it on an ongoing basis).


Transfer Data from One Table to Another or else Update the Data

You can use INSERT ON DUPLICATE KEY UPDATE. For this it is necessary to have a unique index on GeneralInventory.ItemCode, so that duplicates can be detected!

Your statement would look like the following:

INSERT INTO GeneralInventory (ItemCode, Qty)
SELECT RE.ItemCode, RE.ReceivedQty
FROM Receiving RE
WHERE RE.RINo = 'Data of RINo'
ON DUPLICATE KEY UPDATE
SET Qty = Qty + VALUES(Qty);

A quote from the above linked manual page to explain the VALUES() function:

  You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.


Oracle Fine-Grained Access (FGA) auditing excluding one source

I helped my self with this:
BEGIN
    dbms_fga.add_policy(
      ... ,
      audit_condition => 'SYS_CONTEXT(''USERENV'',''OS_USER'') <> ''tomcat''',
      ...);
END;
/


postgres partition trigger and checking for child table

I would advise to use always quote_ident when generating dynamic sentences, to make sure your idents don't have upper/lowercase problems, or that you can use identifiers with spaces or special chars, and that you avoid SQL injection if your idents are parameters out of your control.

That is, your trigger function should look like:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
  start_ts TIMESTAMP ;
  end_ts TIMESTAMP ;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;
  start_ts := partition_date::timestamp ;
  end_ts := start_ts + interval '1 day' ;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE '    || quote_ident(partition)
        || ' (check (updatetimestamp >= ''' || start_ts || ''' '
        || '     AND updatetimestamp <  ''' || end_ts || ''')) '
        || ' INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %', partition;
  END IF;
  EXECUTE 'INSERT INTO ' || quote_ident(partition) || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Check a usage example for quote_literal on the PostgreSQL documentation.

As a matter of preference, I prefer to have the names of all tables lowercase and with no special chars (so that I don't have "Table Names with Double Quotes", but table_names_without_quotes), because I think the code is less noisy and more readable. But, again, this is a matter of taste.


Can you create a clustered DB in Azure using Windows 2003 and SQL Server 2005?

Shared Disks are currently in preview. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disks-shared

But if you have no choice but to run old, unsupported software, you shouldn't attempt anything as complicated to implement and operate as cluster.  Just install the software on a single VM with premium disks, and live with the 99.9% uptime SLA for a single Azure VM.  And with a single stand-alone VM, you may have an easier time supporting a more current OS or SQL Server version.

Put another way, a failover cluster on with Windows Server 2003 and SQL Server 2005 will definitely be more expensive, and will probably have worse uptime than a single standalone VM.  With a cluster you have to properly configure and be able to troubleshoot the quorum model, the Azure load balancer that supports the floating IP, and you must have a domain with highly-available domain controllers.   The likelihood that you get all this right, and have the ability to troubleshoot and repair it if it gets messed up is just too low.  And if you can't figure something out, you won't be able to get help.


Your choice does not have to either/or - you can have both.

At a "physical level" is generally better to use horizontal designs for performance, maintenance and reliability reasons. But the greater rule is: "Don't mix data and metadata". There is nothing intrinsically wrong with building an application that knows how to do CREATE TABLE and ALTER TABLE Foo ADD Col1 by reading metadata table and then allow the app itself to modify the data model. That way, you get the best of both worlds. Think of adding tables and columns the same way you think of DML. Of course, if you do this, you need to make sure you reconfigure the replication when the schema is changed.

Note that the above design guidance doesn't rule out having tables containing the metadata describing what the other tables do (so the app can configure itself to work with it). When building a data warehouse, this is typically an excellent idea. The metadata can even be used to generate and compile new application code.


Don't use a transaction for Stored Procedure

I think there may be some confusion here about a batch vs a transaction.

A transaction is a statement or set of statements that will either succeed or fail as a unit.  All DDL statements are in transactions themselves (i.e. if you update 100 rows but row 98 throws an error, none of the rows are updated).  You can wrap a series of statements in a transaction as well using BEGIN TRANSACTION and then either COMMIT or ROLLBACK.

A batch is a series of statements that are executed together.  A stored procedure is an example of a batch.  In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.

I suspect your issue is the batch is getting cancelled when an error occurs because either the stored proc itself or an outer scope (like the application or stored proc that calls this procedure) has error trapping in it.  If that is the case this is trickier to resolve since you need to adjust how you handle errors at whatever scope is trapping them.


Disabling protocols using SQL Server Configuration Manager or ENDPOINTs

If you disable the endpoint another endpoint of that connection type could be created.  If you disable the protocol in the config manager no endpoints for that connection type will ever work.

There's no security issue as normal authentication is required for all connection types.


If you have minimum of three active nodes in your cluster, you don't need to add garbd.

You should use a garbd with the following conditions:

When you have an even number of nodes and you can't add new nodes (because of cost or ...) to avoid split-brain situations.
when you want to backup data (SST method) to a server.

More information:
http://galeracluster.com/documentation-webpages/arbitrator.html


Difference between SQL node and Data Node

Management Node: is the Node that process reading the cluster configuration file and distributes this information to all nodes in the cluster that request it. It also maintains a log of cluster activities. Management clients can connect to the management server and check the cluster's status.

Data Node: This type of node stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you need four data nodes. One replica is sufficient for data storage, but provides no redundancy; therefore, it is recommended to have 2 (or more) replicas to provide redundancy, and thus high availability.

SQL Node: This is a node that accesses the cluster data. In the case of MySQL Cluster, an SQL node is a traditional MySQL server that uses the NDBCLUSTER storage engine. An SQL node is a mysqld process started with the --ndbcluster and --ndb-connectstring options, which are explained elsewhere in this chapter, possibly with additional MySQL server options as well.

Note: SQL Node is the specialized type of API node.

Please refer to MySQL webpage for more info:

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-basics.html


How to remove duplicates in col1 and get group MAX in col2? / mysql

SELECT col1, MAX(col2) FROM mytable GROUP BY col1


Retrieve linked servers out of system database files?

The answer placed here for future reference if needed. It looks as though this data may be in a hidden table in the master database sys schema named sysxsrvs.

According to what I could find this may be available to be queried if connected through the DAC.

It also uses sysobjvalues (contained in all databases). I believe to populate the srvproduct/providername fields.

reference: https://msdn.microsoft.com/en-us/library/ms179503.aspx


Distribution clean up job in transactional replication removed records but not files

the "immediate sync" option is the problem here. Unless you are regularly adding new subscribers every few hours, then this can be set to false and the snapshots are then cleared out after being applied.

exec sp_changepublication
          @publication=N'<publication_name>',
          @property=N'allow_anonymous',
          @value='false';
go

exec sp_changepublication
          @publication=N'<publication_name>',
          @property=N'immediate_sync',
          @value='false';
go

There is a lot of really good information about this here: http://www.replicationanswers.com/TransactionalOptimisation.asp


SSRS is missing after failover to other node

Reporting services is not cluster-aware. If you want high-availability you need to set up a scale-out deployment where multiple Report servers share the same back-end database.

From the documentation: High availability in SQL Server Reporting Services

  A scale-out deployment provides a way to share a database. If one
  report server goes down, other servers in the same deployment will
  continue to work.

  Reporting Services is not cluster-aware. By itself, a scale-out
  deployment does not provide load balancing

So you would have needed to install SSRS on more than one machine and set it up like that before the failover happened.