Risks associated with automatically shrinking TempDB as a SQL agent job?

There are a lot of common misconceptions when it comes to tempdb and shrinking databases.

For starters, if tempdb grows beyond 350 GB, it's because it needs to. With that kind of size growth, I'd say there's an ETL job or manual batches that aren't behaving well. You could fix that in a number of ways, the best of which are (matter of my opinion)

Fix the queries that are eating up your tempdb: look for huge Hash Match or Sort operations without proper indexes or even row versioning if you're doing Read Committed Snapshot Isolation.
Add more disk space. In most environments, this is just a matter of giving your SAN admin a call.

Normally, I would recommend putting tempdb on its own disk(s) and size the database from the start, so you'll avoid the autogrow thing entirely. Autogrowing by 10 percent is often really bad practice for two reasons:

When the database is small, autogrowth can be very frequent, and also not aligned with your physical storage sectors
With larger databases, the files will grow by quite a lot once they do.

Another issue with shrinking tempdb is that SQL Server keeps a lot of stuff in there, preventing it from shrinking. You'll find that even if you try to shrink tempdb, it just won't, unless you pretty much restart the server (either literally or by clearing all sorts of buffers, both of which can have a dramatic impact on your production environment).

In summary

Don't autoshrink, make sure there's enough space from the start.
Consider sizing tempdb from the start so it won't have to autogrow.
See if you can tune those queries that are filling your tempdb. They'll probably also be the ones that take the most time to run.


Identical SQL Server databases hosted in the same physical DB server

If these are quite large databases I would ask if they are on different physical disks, with one disk significantly slower or busier than the other? (i.e. one is SSD, and the other is a standard HDD).

For smaller databases though, the data will quickly be cached, and so speed of the disk would stop being relevant at that point.

I'd suggest turning on Client Statistics and Show Actual Execution Plan to narrow down where the queries against the two databases are not aligned.


SQL Server 2012 Standard server with 50 instances won't uninstall instances

It will likely be because the setup GUI is trying to enumerate all the instances installed  and failing miserably, running the uninstall from the command line should do it

Running the below command with admin privileges will uninstall the SQL server instance with no setup GUI displayed:

Setup.exe /Action=Uninstall /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /Q

I assume that you only have the SQL Server feature installed, if you have others you want to remove, you can just add them in a comma separated list of values, such as /FEATURES=SQL,RS,Tools

For different instances, just replace MSSQLSERVER with the name of the instance you want to remove.

Command line reference for setup.exe is available here:

Install SQL Server 2012 from the Command Prompt


Need to run select statement across all databases

Here's the "do something on each db" template I use, slightly adapted to your example:

--Create a table to hold the query results
CREATE TABLE #licenselist
    (dbid smallint, dbname sysname, system_license varchar(99));

DECLARE @fixedname nvarchar(150);

--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
    SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
    FROM master.sys.databases
    WHERE state_desc = 'ONLINE'
    AND HAS_DBACCESS(name) = 1                  --Added at @SolomonRutzky's suggestion
    AND name NOT IN ('master','msdb','model','tempdb');

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @fixedname;

WHILE @@FETCH_STATUS = 0
BEGIN
       EXEC ('USE ' + @fixedname + ';' +
        'INSERT INTO #licenselist
         SELECT DB_ID() as dbid, DB_NAME() as dbname, ParamValue as system_license
         FROM Table1
         WHERE paramname = ''SYSTEM_LICENSE'';');

       FETCH NEXT FROM dbCursor INTO @fixedname;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #licenselist

DROP TABLE #licenselist

EDIT: streamlined a bit, removed the raw @dbname variable and instead fixed it in the original query.


Cannot fix ERROR:ORA-01031: insufficient privileges

I assume that you run this on the server that hosts the database and that you have set the ORACLE_SID to the one of the database that you want to access. You must run the orapwd application. This generates a file that holds the password. Check the documentation for the exact syntax. It depends a bit on the OS on which you host the database.


Restoring an entire SQL Server database instance

Personally I advocate quite strongly against the detach / attach method. You're already taking proper backups, right? Why not continue that plan and use backup / restore?

Detach / attach is dangerous, for one simple reason: if something happens during the detach or during the copy, you now have ZERO copies of your database.

With RESTOREs you will be able to proceed even if your new server gets hosed and you need to use a different server, or you're not able to re-use the same server name, or any other host of things could go wrong.


READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express

Yes from that link it supports it. A Database Snapshot is a read only "frozen in time" (when the snapshot was taken) view of data. This is a tool useful in some  recovery, reporting and rollback preparation scenarios.

The isolation levels you are asking about are all covered by the line talking about Snapshot Isolation Level (Row-level Versioning).

I would question what sort of workload you are putting against SQL Server Express that needs a snapshot isolation level as there are other restrictions on SQL Server Express that tend to make it not a great fit for busier, high concurrency apps (Most notably database size and memory restrictions) but this is certainly supported from that edition features matrix you linked.


Weed out certain rows where timestamp is within interval


Depending in your perspective, the preferable approach would be to continue doing it the way you're doing it, but to use SSL to encrypt communications between the application server and the database -- because if you don't, then not only are you sending the encryption key in the clear, you're also sending the decrypted data in the clear.

It seems a little bit pointless to protect the encryption key from packet sniffer snooping, while continuing to transmit the data you're trying to protect, over the same channel, also in the clear.

Granted, the exposure would theoretically be less, since you wouldn't be giving away the key, itself, but you're still exposing the "secure" data.

You could avoid passing the key over the wire by declaring a stored function that returns the key, and using a call to the stored function in place of the constant in the queries, but that creates what is arguably a worse scenario, because a breach of your database's security exposes both the encrypted data and the keys necessary for decryption.  But, this would avoid the specific issue that you're intending to avoid.

mysql> CREATE FUNCTION aes_key () RETURNS TINYTEXT DETERMINISTIC NO SQL RETURN 'usa2010';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT aes_key();
+-----------+
| aes_key() |
+-----------+
| usa2010   |
+-----------+

Now, your queries look like AES_ENCRYPT('nebraska', aes_key()).  As I say, this does accomplish the direct purpose you intend, and the permissions on the stored function would serve to keep honest people honest, but putting it right out there on the server along with the data seems like a particularly bad idea.

Perhaps slightly more orthodox would be to compile a simple User Defined Function (UDF) in C, which would return a constant string -- the decryption key -- when called, and reference that function in the queries.  The only advantage of this approach is that the UDF wouldn't be returned if an attacker did a full database dump to extract the data.  One potentially significant disadvantage is that user-defined functions are global and are not controlled by permissions -- they are accessible to any user who can connect to the database server.

And, of course, either of these alternatives, like the mechanism you proposed in the question, involves storing the encryption key on the hard drive of the database server, where it is more vulnerable if the database server is breached.  If the key is stored with the application code, then both platforms have to be breached for your sensitive data to be effectively stolen.

So, no... I would suggest that the appropriate place for storing the encryption key would be somewhere other than the database server itself, specifically in the application server, and using SSL to connect from the application to MySQL if data on the wire between the two systems is a security concern.


I wrote up an interesting layout last year which features DRBD pairs in two data centers (DC1,DC2) with as follows

DRBD Pair in DC1 (db1 and db2)
DBVIP for Primary of DRBD Pair1 is 10.1.2.30
DRBD Pair in DC2 (db3 and db4)
DBVIP for Primary of DRBD Pair2 is 10.1.2.40
Have MySQL Circular Replication Between DRBD Primaries
Have the 10.1.2.40 as Master_Host for DBRD Pair 1
Have the 10.1.2.30 as Master_Host for DBRD Pair 2

MySQL high availability, failover and replication with Latency

MySQL Replication : 1 Slave / Multiple Masters

Here is why I suggested this: Using two data centers, you setup automatic failover for DRBD Pair in one data center. Let the other DRBD Pair in the second data center be for DB disaser site with it own local redundancy and failover. Should you ever loses one data center, the other data center is fully read with it own local failover setup. Your app would just have to use the DBVIP of the other database center in such a catastrophic case.

Please keep in mind that using DRBD in conjunction with MySQL is only beneficial if all of your data uses the InnoDB Storage Engine. Hard failovers in DRBD could easily result in crashed MyISAM tables.

Here is another setup to consider:

As with DRBD setups, any DRBD Secondary would provide just a Disk-Level copy of your MySQL Folder. It is available as a warm standby. MySQL is not being run on the DRBD Secondary. If you want the third DB server to become hot standby, ditch DRBD altogether and use pure MySQL Replication. With three DB servers, using db3 at a remote site, simply setup the following:

+--> db1 --> db2 --> db3 -->+
^                           |
|                           V
+<--------------------------+

Using your rudimentary failover, now you have two hot standby servers. You just have to make sure each DB server has a unique server_id value. I also recommend using MySQL 5.5 because it uses SemiSync Replication which is more sensitive to communication failures and stop Replication better. You will have to setup the appropriate heartbeats and timeouts.


What are the low-level storage requirements?

You didn't mention the Database platform, I can give insight on SQL Server.

SQL Server ensures durability with a concept called WAL. Write ahead logging. This means that all changes are first written to log before the are applied tot the data files.

When a row needs to be altered. The corresponding data (and possibly index) pages get fetched from disk into the buffer pool (in memory).

In memory, the page(s) get updated, the page is then marked as dirty. Done. But what happens when you have a power outage? The memory is cleared right?

Why isn't the page then written to disk immediately? Because this could give major disk contention. Imagine that you have a page with a couple of hundred rows on it. It would create a lot of disk IOs if for every row update the page is written to disk.

Therefore a different mechanism is used. A mechanism where it's no longer necessary to write data pages to disk every time a single update has taken place on that page.

Whenever a row is updated, the change along with the information to undo the change is first written to a log file. Once that is done, the client that issued the change is given an acknowledgement that the change is in fact written to disk (hardened) and that the client can continue with the next statement.

This way, when you have a power outage, after which the database server starts again, all finished (committed) changes can be read from the log file and still be applied and all unfinished or rolled back changes can be undone.

Eventually it would be nice if all those data pages that are dirty and hanging in memory are applied to the data file right? For starters you don’t want days changes in log files that need re-applying after a server restart.  Therefore the dirty data pages are occasionally flushed to the data file(s). This is called a checkpoint. The interval on which checkpoints occur is based on the time it would take to recover the database after a restart. (The time it would take to read all the changes in the log file and apply them to the data file (s).
But key to understand is that data pages and whether or not they are written to disk are not important for durability.

Back to the log file. To make sure that once SQL Server gives a log file write IO request to the OS and the OS gives back acknowledgement that the IO is handled it is in fact really hardened to disk and not hanging around in OS cache, the log file is created with the following flag: FILE_FLAG_WRITE_THROUGH. This tells the OS NOT to cache any write IOs that are going to this file.

-- update

Atomicity is achieved by the fact that your changes are all wrapped in transactions. (Either implicitly or explicitly). So when you update a record you have a minimum of 3 log records:

BEGIN TRAN
UPDATE RECORD X
COMMIT TRAN

Let’s for simplicity sake assume that these 3 records are send to disk in 3 separate IOs.
The durability rule dictates that the client can only get a acknowledgment if 1, 2 and 3 are hardened to disk.

Atomicity rules dictates that either the complete transaction is applied or none of the steps of the transaction.
So in the above example, if you have a failure after IO #2 has been hardened to disk. After a reboot the database server will have a look in the transaction log.
All transaction records that are in the transaction log and have not been applied to the data file AND have a final COMMIT TRAN record will be applied to the data file.
However, for the above transaction, the database server will not find a COMMIT TRAN record in the log file.  You could have to scenario’s now.

SQL Server looks in the data file to find out if UPDATE RECORD X has
been applied to the data file. (1 possibility could be that you had
a checkpoint right after log record 2 was generated and just before
your crash.) In this case, since the database server can’t find a
commit record, it assumes that the whole transaction most be rolled
back. It will undo the change. Since your client application never
got a acknowledgement that the transaction was committed. Your now
back in a consistent state. The application must retry the
transaction.
SQL Server looks in the data file and find out that nothing of this
transaction was every applied to the data file yet. It will just
disregard these log records.

--END Update

Most modern hardware that handles IOs have mechanisms that handle power outage. For example controller card cache is backed up with batteries, SANs have the same principles. The only think to keep in mind is that even if they do have these mechanisms, if any of your hardware allows you to continue operation even if the battery is not present or is failing that you implement proper error event monitoring and take action accordingly.

The transaction Log manager sends the IOs to the OS in guaranteed order. Once a hardware appliance tells the OS that the IO is taken care of, it in fact means that the IO from that moment on is either in battery backed cache or is in fact on disk. In other words: the IO is reproducable after a power outage.  The IO hardware appliance might write IOs to physical disk in a different order for optimization purposes. But this is no longer important for us.

IO ordering is something of concern when you implement SAN level mirroring. But that is another topic.

What do you mean with your own DB? You are going to develop your own RDBMS? At a minimum you need these 3 core concepts:

Only acknowledge to the client application that the transaction is in fact commited when either a log records is written to disk that can fully reproduce the change or the change itself is written to disk. (If you don't want to implement something like WAL)
For hardening written to files, implement FILE_FLAG_WRITE_THROUGH flag on file initialization. To make sure the windows OS doesn’t cache.
Use hardware that has battery backed cache. And monitor on battery
problems

Detailed SQL Server information can be found in the SQL Server I/O White Papers.

More information about ACID. Atomocity, Consistency, Isolation and Durability here


Asuming this is to transport data to an other system. It that case this will work:

set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off

If you don't want a header line, change to heading off

If this is to analyze the data in excel, please wake up and learn sql. SQL, especially Oracle, has many great tools to help analyzing your data. By the time the data is transported into the csv file, Oracle analytics has already done some great analysis.Data Warehousing and Business Intelligence but for many tasks plain SQL can be good enough. Start reading about Oracle Analytics.


Your data is corrupt. You might attempt starting the server iterating through the http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html documentation. If it starts you can attempt to save the large table (mysqldump/mydumper export) or drop it if it's not needed.


You should use smart index maintenance scripts as opposed to maintenance task.

  Should I run the job in primary or in replica?

Yes, as they get replicated on secondary replicas.

  If I run it only on secondary then what benefits do I get?

you cannot run on secondary replica.

Index maintenance generate a lot of transaction log activity that affects your RTO and RPO. Also, it increases the redo backlog on secondary replicas.

You should follow Recommendations for Index Maintenance with AlwaysOn Availability Groups :

Find a quiet time or during planned maintenance time run your index maintenance.
If possible switch from Synchronous mode to Async mode. Once index maintenance is done, switch it back to what it was.
Take frequent log backups so that the log file does not grow out of control.
Since you are on sql server 2014, you should use WAIT_AT_LOW_PRIORITY, MAX_DURATION, and ABORT_AFTER_WAIT
If you are using online index rebuilds, then use MAXDOP = 1

Also, note that a Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

  Is corruption likely to happen if I use SP1 and index rebuild via maintenance plans?

Since you are on SP1 (12.0.2370 or greater) , you should be fine.


Is there a max allowance for entries in a table in mysql?

I took a look at MySQL documentation and there seems to be no maximum number for records in a table, but taking a look at MySQL features page:

  Scalability and Limits:

  Support for large databases. We use MySQL Server with databases that
  contain 50 million records. We also know of users who use MySQL Server
  with 200,000 tables and about 5,000,000,000 rows.

  Support for up to 64 indexes per table (32 before MySQL 4.1.2). Each
  index may consist of 1 to 16 columns or parts of columns. The maximum
  index width is 1000 bytes (767 for InnoDB); before MySQL 4.1.2, the
  limit is 500 bytes. An index may use a prefix of a column for CHAR,
  VARCHAR, BLOB, or TEXT column types.

According to this information, the recommended maximum number of records may vary a lot depending on your hardware limitations but would probably be around the boundaries of 5,000,000,000 rows.

EXTRA: if you plan to store really large numbers of records in a table, care to use the MAX_ROWS parameter in your CREATE TABLE data definition statements.


How can I use LOAD DATA LOCAL INFILE for specific columns?

Mysql can do following with user defined variables
 LOAD DATA LOCAL INFILE '/your_path/yourfule.csv'
 INTO TABLE texts
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 ( @ID
 , @Text
 )
 SET `ID`   = @ID
   , `Added` = CURDATE()
   , `Text`     = @Text
 ;

Please check, if MySQL allows from that path to insert data.
And also you may have to change the options for INFILE


executing a stored procedure on a monthly schedule

Use Oracle's DBMS_SCHEDULER to schedule a job to run monthly.


A flag (indicator) that shows for each row which conditions was used to select it

If your conditions are mutually exclusive (you you only care about matching the first of an order you control) then you could get away with a single column:
SELECT *,
  CASE
    WHEN name = 'meatfeast' THEN 'name'
    WHEN diet = 'vegan' THEN 'diet'
  END as reason_for_appearing
FROM
  pizza
WHERE
  name = 'meatfeast' OR diet = 'vegan'

You repeat your where conditions as individual clauses of a case when, (in the order you care about them)

If they aren't mutually exclusive and you want to know all the reasons why it might have matched you'll either need multiple columns ..
SELECT *,
  CASE WHEN name = 'john' THEN 'yes' END as because_of_name,
  CASE WHEN city = 'Chicago' THEN 'yes' END as because_of_city
FROM
  person
WHERE
  name = 'john' OR city = 'chicago'

.. unless you want to get really involved with a huge case when of all the conditions combinations (which is 2^N, as I'm sure you can imagine)
Or you might alternatively do some kind of concat you can pick apart programmatically or the user can read
SELECT *,
  CONCAT(
    CASE WHEN name = 'john' THEN 'name' END,
    CASE WHEN city = 'Chicago' THEN ' city' END
  ) as because_of
FROM
  person
WHERE
  name = 'john' OR city = 'chicago'

I suppose it rather depends on what you want to do with it after


The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string with 'single quotes', you need to prefix them with an N''. N stands for national, not nvarchar (admittedly, a U prefix - for Unicode -  would have made more sense).

Compare:

SELECT Prefix = N'', NoPrefix = '';

Results:

Prefix      NoPrefix
------      --------
           ??


adding fields to 2LIS_12_VCITM datasource in ECC

There are several different ways to add fields to a logistics datasource like 2LIS_12_VCITM. If it is a pool field (a field already available in a source table) you can go to t-code LBWE and simply add it to the comm structure of the datasource. Note that before doing this you will need to drain the V3 and V2 update jobs for the datasource.

If the field you want to add is not a pool field then custom code needs to be written to append the field to the datasource or add an exit routine that populates the field when the datasource data is being transferred to BW. An exit routine is less problematic than adding an append structure for the datasource.


PostgreSQL: Query for location of global tablespace?

pg_default and pg_global locations are "hardcoded".

pg_default lives in:

select setting||'/base' from pg_settings where name='data_directory';

and pg_global lives in:

select setting||'/global' from pg_settings where name='data_directory';

src/backend/commands/tablespace.c says so:

 * There are two tablespaces created at initdb time: pg_global (for shared
 * tables) and pg_default (for everything else).  For backwards compatibility
 * and to remain functional on platforms without symlinks, these tablespaces
 * are accessed specially: they are respectively
 *          $PGDATA/global/relfilenode
 *          $PGDATA/base/dboid/relfilenode

Please also note, that exposing location of data directory is a - not so terrible but still - security hole.

app01@postgres=> show data_directory;
ERROR:  must be superuser to examine "data_directory"


Mongodb _id Value and Indexes Are Lost During Mongorestore

Can anyone share their restore experiences that restored both indexes and mongodb_id?

As per MongoDB documentation here By default, mongorestore looks for a database backup in the dump/ directory.

New in version 3.6:

All MongoDB collections have UUIDs by default. When MongoDB restores collections, the restored collections retain their original UUIDs. When restoring a collection where no UUID was present, MongoDB generates a UUID for the restored collection.

UUID() has the following syntax:

UUID(<string>)

Parameter   Type    Description
hex        string
                    Optional. Specify a 36 character string to convert to a UUID BSON object. If not provided, MongoDB generates a random UUID in RFC 4122 v4 format.

Changed in version 3.6: In earlier versions of the mongo shell, UUID required a hexadecimal string argument. See the 3.4 manual.

It's returns A BSON UUID object.

  I noticed the indexes are lost and _id has new values after
  mongorestore.

@Will, As JJussi has already said that mongorestore should recreate all indexes what mongodump has recorded.

For Example

I have mongodump in my C:\data\dump folder. Which contains the database  folder 100YWeatherSmall,citibike,city,ships,video with their (.bson & .json) file. These database I had already restored in MongoDB 3.6.

Here again i am going to mongorestore to these databases dump file in MongoDB 3.6.

As you can see that mention all databases has already in MongoDB.

Note: Here I have highlighted the some database name due to security reason.

Here I am going to mongorestore through the below command.

C:\Program Files\MongoDB\Server\3.6\bin>mongorestore --verbose C:\data\dump

  Note:  Run mongorestore from the system command line, not the mongo shell.

video.movies database dump restore report status

- E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef0') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef1') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef2') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef3') }
  - E11000 duplicate key error collection: video.movies index: _id_ dup key: { : ObjectId('58c59c6b99d4ee0af9e0eef4') }

100YWeatherSmall.data database dump restore report status

- E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1cc8') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1cca') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1ccc') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1ccd') }
  - E11000 duplicate key error collection: 100YWeatherSmall.data index: _id_ dup key: { : ObjectId('5553a98ee4b02cf7150e1cd0') }

citibike.trips database dump restore report status

 - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b258e') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b25ad') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b2608') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b26c5') }
  - E11000 duplicate key error collection: citibike.trips index: _id_ dup key: { : ObjectId('572bb8232b288919b68b26da') }

Here you can noticed that all during mongorestore it's showing  index: _id_ dup key: because that index: id is already exist in that database & collection .

That means whatever mongodump has created the index:id & ObjectId that is same index:id & ObjectId is same mongorestore in MongoDB.

Here the below mention reports is the citibike.trips mongorestore complete mongodump restore reports.

2018-05-30T10:56:31.962+0300    [########################]  citibike.trips  835MB/835MB  (100.0%)
2018-05-30T10:56:31.962+0300    restoring indexes for collection citibike.trips from metadata
2018-05-30T10:56:32.106+0300    finished restoring citibike.trips (1990273 documents)
2018-05-30T10:56:32.126+0300    done

For example here I have only mention the citibite.trips restore complete reports. The message is showing restoring indexes for collection citibike.trips from metadata.

So, as per above conclusion we can say that mongorestore, store the same index:_id_ & ObjectId,  which mongodump has created.

For your further ref here , here and here


How to query time series with gaps between a given date range in PostgreSQL?

WITH cte AS (
SELECT Dates."Date",
       test.DeviceID,
       test.State,
       ROW_NUMBER() OVER (PARTITION BY test.DeviceID,
                                       Dates."Date"
                          ORDER BY test."Date" DESC) rn
FROM generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates ("Date")
JOIN test ON Dates."Date" >= test."Date"::date
)
SELECT DeviceID, State, "Date"
FROM cte
WHERE rn = 1
ORDER BY 1, 3

fiddle


No way that I have seen or found.  I also pinged Jeff Smith and Kris Rice (Product Owner and Developer for the platform) on Twitter and they confirmed as well.  (see https://twitter.com/thatjeffsmith/status/1125396802423992322)

On the plus side:  You have located the configuration file.  And, while not ideal, at least you can make your configurations somewhat portable.  :-)


Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2

According to the general consensus that answers shouldn't be posted as comments, following my short answer.
You have set a timeout of 7 minutes and your database instance is aborting the connection after 7 minutes. Works as designed.
General information is logged in the General query log and contains:

Established client connections and statements received from clients

The Error log contains:

Problems encountered starting, running, or stopping mysqld

Reference: 5.4 MySQL Server Logs (MySQL Documentation)
So if your 10.1 installation is not displaying any errors, then you possibly have a larger timeout value set.


Unsurprisingly, the manual is right. But there is more to it.

For one, size on disk (in any table, even when not actually stored on disk) can be different from size in memory. On disk, the overhead for short varchar values up to 126 bytes is reduced to a 1 byte as stated in the manual. But the overhead in memory is always 4 bytes (once individual values are extracted).

The same is true for text, varchar, varchar(n) or char(n) - except that char(n) is blank-padded to n characters and you normally don't want to use it. Its effective size can still vary in multi-byte encodings because n denotes a maximum of characters, not bytes:

  strings up to n characters (not bytes) in length.

All of them use varlena internally.
"char" (with double-quotes) is a different creature and always occupies a single byte.
Untyped string literals ('foo') have a single byte overhead. Not to be confused with typed values!

Test with pg_column_size().

CREATE TEMP TABLE t (id int, v_small varchar, v_big varchar);
INSERT INTO t VALUES (1, 'foo', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

SELECT pg_column_size(id)        AS id
     , pg_column_size(v_small)   AS v_small
     , pg_column_size(v_big)     AS v_big
     , pg_column_size(t)         AS t
FROM   t
UNION ALL  -- 2nd row measuring values in RAM
SELECT pg_column_size(1)
     , pg_column_size('foo'::varchar)
     , pg_column_size('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'::varchar)
     , pg_column_size(ROW(1, 'foo'::varchar, '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'::varchar));

 id | v_small | v_big |  t
----+---------+-------+-----
  4 |       4 |   144 | 176
  4 |       7 |   144 | 176

As you can see:

The 3-byte string 'foo' occupies 4 bytes on disk and 7 bytes in RAM (so 1 byte vs. 4 bytes of overhead).
The 140-byte string '123...' occupies 144 bytes both on disk and in RAM (so always 4 bytes of overhead).
Storage of integer has no overhead (but it has alignment requirements that can impose padding).
The row has an additional overhead of 24 bytes for the tuple header (plus an additional 4 bytes per tuple for the item identifier in the page header).
And last but not least: The overhead of the small varchar is still just 1 byte while it has not been extracted from the row - as can be seen from the row size. (That's why it's sometimes a bit faster to select whole rows.)

Related:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
Detailed optimal column order with large text and bytea
Measure the size of a PostgreSQL table row
Configuring PostgreSQL for read performance


Repairing corrupt SQL Server database backup?

I do know a consultant who does this kind of work, but it's nowhere near cheap - think mid-five-figures to start the project, paid in advance.  It's not for the faint of heart and you only want to go that route as a last resort.


Merge table from master slave replication

You can use pt-table-sync with --verbose to generate the differential sql and verify the data from there. Whichever version you feel is correct you may choose  to keep.

I'm not aware of any tool which will merge, as such what will be the merge condition upon conflicts?

Writing on both nodes in master-master can cause bad bad data! I'd try to avoid that.


Clarification about master slave configuration in mysql

Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave

For this Example, let's assume

IP of the Master is 10.1.1.20
IP of the Slave is 10.1.1.30

Here is what you do

STEP01 : Activate Binary Logging on the Old Server

Step01-a) Add this to /etc/my.cnf on the Master

[mysqld]
server-id=101120
log-bin=mysql-bin

Step01-b) # service mysql restart

After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql

STEP02 : Perform mysqldump

On the Master, you can mysqldump the data and record from what point in time it happened.

# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart

What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:

# head -22 MySQLData.sql | tail -1

STEP03 : Load the mysqldump into the Slave

Execute the mysql client loading the mysqldump into the Slave's mysql instance

# mysql -h10.1.1.30 -uroot -p < MySQLData.sql

STEP04 : Create MySQL Replication User on the Master

# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"

STEP05 : Setup the Slave with a Separate Server ID

Add this to /etc/my.cnf on the Slave

[mysqld]
server-id=101130

and # service mysql restart

STEP06 : Setup the Replication on the Slave

Goto the mysql client and run the following command

mysql> CHANGE MASTER TO
    MASTER_HOST='10.1.1.20',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1;

STEP07 : Setup the point-in-time Master Log and Position

Back in STEP02, I mentioned viewing the point-in-time position using

# head -22 MySQLData.sql | tail -1

You should see something like this:

#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Run it as command in the mysql client on the Slave

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;

Afterwards, run this command

mysql> SHOW SLAVE STATUS\G

You should see something like this:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.113.232
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 122957100
               Relay_Log_File: relay-bin.003666
                Relay_Log_Pos: 122957100
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

STEP08 : Launch Replication

Start up replication with this:

mysql> START SLAVE;

Afterwards, run this command again

mysql> SHOW SLAVE STATUS\G

If you see this

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

CONGRATULATIONS, MySQL Replication is Working !!!


CPU column 5x higher on one machine vs another?

Over-committing CPU and memory on a VM can certainly give you false positives. The true performance bottlenecks are "hidden" from the OS, so you may not see it as easily as you would on a normal windows box.  I have seen this countless times before, so we always recommend reserves on assigned resources, and where possible, dedicated LUNs for the SQL Server.

Also, if your client is using "older" (for e.g. pre-Nehalem) processors, the actual virtualisation may be impacted by shortcomings of the hardware.  I'd never recommend putting a SQL Server VM (especially not a high-IO one) on a host that has older hardware.  Nehalem family introduced VT-d (if memory serves), which (similarly to how VT-x attaches a VM ID tag onto the TLBs, enabling the hardware to know which VM a processor call belongs to) means your hardware is optimised for directed I/O.  All of these help in freeing up the hypervisor from having to keep track of these when emulating processor, memory and I/O calls.

Also, other things that may impact performance at a VM level is DRS, and VMotion, but the newer the hardware, the less of a concern they become, especially when you have VT-d, VT-c and VT-n enabled processors and chipset.

Pending feedback from you, I will be extending this answer, so please provide comments...


Attach 110(2012) from SQL Server 2014 Instance to a (110) SQL Server 2012 INSTANCE

You can't do this. Compatibility mode makes no difference here. You simply can't go back to 2012 from 2014 via backup/restore or detach/attach.

Your options are limited to either generating scripts to recreate the schema and objects or using a third party tool. Red gate have one but I've never used it.


MongoDB - changing data directory name changes data

As per MongoDB documentation here To use an alternate dbpath, specify the path in the configuration file (e.g. C:\mongodb\mongod.cfg) or on the command line with the --dbpath option.

  Default: /data/db on Linux and macOS, \data\db on Windows

The directory where the mongod instance stores its data.

Changed in version 3.0: The files in --dbpath must correspond to the storage engine specified in --storageEngine. If the data files do not correspond to --storageEngine, mongod will refuse to start.

--directoryperdb

Uses a separate directory to store data for each database. The directories are under the --dbpath directory, and each subdirectory name corresponds to the database name.

Changed in version 3.0: To change the --directoryperdb option for existing deployments, you must restart the mongod instances with the new --directoryperdb  value and a new data directory (--dbpath ), and then repopulate the data.

For standalone instances, you can use mongodump
on the existing instance, stop the instance, restart with the new --directoryperdb
value and a new data directory, and use
mongorestore to populate the new data directory.
For replica sets, you can update in a rolling manner by stopping a
secondary member, restart with the new --directoryperdb
value and a new data directory, and use initial
sync to populate the new data directory. To update all members,
start with the secondary members first. Then step down the primary,
and update the stepped-down member.


Rerunning query in the same connection takes significantly longer

With that many rows, moving the results from the server to the client will be a significant part of the elapsed time.  Eliminate variablity in that part by wrapping your query in some way such as:

select
    count(*)
from (
    {your query here}
) as xx

This way the network transfer will be a minimal part of the elapsed time and you can eliminate it from your enquiries.


Eliminate database fragmentation (export, drop and re-import tables)

Instead of export/import, you should use

alter table t shrink space;

which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s):

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/


SQL Server - How to solve FIFO Cost of OHB... function, cursor,?

Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/


comparing ip and subnet (ipv4 and ipv6) - mysql

I assume your IPv6 is in BINARY(16)?  Build a 128-row table of the BINARY(16) values for the subnet masks.  (/128 is all 1's.)  JOIN to the table and do masking on each half of the address --

WHERE (LEFT(ip, 8)  & LEFT(mask, 8))  == LEFT(ip, 8)
  AND (RIGHT(ip, 8) & RIGHT(mask, 8)) == RIGHT(ip, 8)

(No shifting.)


I really wonder if you got a solid business case for worrying about performance issues in rounding. Unless you are doing thousands of conversions per second, this really is non-issue.

The standard approach is to measure the performance differences. First you got to make sure that your ROUND() and CONVERT() actually do the same thing. Are you sure about that? After you are comfortable with the idea that both algorithms return same result, run your test cases.

On my test system, looping the round/convert 10 million times each, measuring the duration in milliseconds and running the test set five times provides results as such:

convert     round       delta
----------- ----------- -----------
7883        7546        337
7770        7510        260
7733        7563        170
7750        7466        284
7780        7513        267

By this test, it looks like ROUND() performs slightly better on my system. Your mileage is quite likely to vary. For ten million iterations, ROUND() saves on the average 250 milliseconds. That is, ROUND() performs some 250 nanoseconds faster than CONVERT(). I highly doubt if this is relevant to most real-world issues - unless one is developing something like high-frequency trading algorithm or the like.


Why does mongodb require three config servers

Config servers are a special case, you can use one (for testing), or three and that's it.  They are not a replica set, at least not yet, that might change in the future, see SERVER-1448.
Go and (re)read the first two paragraphs here - note the piece about them not being a replica set is explicitly mentioned.  I will pick out some other relevant points:

Config servers use a two-phase commit to ensure immediate consistency
and reliability
to ensure redundancy and safety in production, you should always use
three

The first part gives you a hint about why 3 are needed, the second part is telling, particularly in terms of the safety aspect (redundancy is obvious - you have 3 copies of the data).
With how the 2 phase commit is implemented, all three config servers must receive (and acknowledge) a change for it to be considered successful.  That's the safety piece - all three must be in a quorum and have the same data (there are regular checks), and that is why it is three config servers required exactly (for now).  When you run with just one for testing, this can't happen, and your cluster meta data is essentially at risk, hence using just one is for testing purposes only.


Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog?

Both of those changes improved throughput, but decreased robustness.

innodb_flush_log_at_trx_commit=1 says to flush stuff to disk at every COMMIT, thereby making your database fully crash-proof.  =2 risks one second's worth of COMMITs.

If you have the binlog turned on, sync_binlog=1 says to flush to disk at every COMMIT.  =0 allows delaying (buffering) the writes.  This variable is somewhat important if you have Slaves and/or do incremental backups.


You need to assign the RECORD type before sending it as parameter, and a very simple way to do that in your case would be simple assigning it to NEW or OLD even when it wouldn't be necessary, like:

CASE TG_OP
    WHEN 'INSERT' THEN
        old_v = NEW;
        new_v = NEW;
    WHEN 'UPDATE' THEN
        old_v = OLD;
        new_v = NEW;
    WHEN 'DELETE' THEN
        old_v = OLD;
        new_v = OLD;
END CASE;

Notice that now it is very important to use TG_OP and control which variables you must read/use.


SQL Memory Usage Cumulative for each Database

I don't believe there is any cumulative / average metric for this. Nor do I think it makes sense to look at it this way. I don't think you can derive any useful conclusion from such a view of things. This information is too volatile to use in that way. Some stuff ages out faster than others. It greatly depends on which pages are being accessed frequently, how much memory is available on the system, etc. Also, the Buffer Pool is not the only use of memory. There is also permissions cache, plan cache, DB Metadata cache, and others. Hence, such an approach will most likely be misleading.

In addition to looking in sys.dm_os_buffer_descriptors, there are also the following resources to check:

DBCC MEMORYSTATUS;
SELECT * FROM sys.dm_os_memory_clerks;
SELECT * FROM sys.dm_os_performance_counters;

Regarding those three resources, please note:

there is some duplication between the DBCC and the others (and even some other DMVs not listed here)
in each of those resources, there are some entries that cover all databases and cannot easily be broken down, such as Connection Pool cache, plan cache, etc


How to model optional product delivery companies

Assuming tables like:

CREATE TABLE PRODUCTS
( PRODUCT_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE DELIVERY_COMPANIES
( COMPANY_ID ... NOT NULL PRIMARY KEY
, ...
,
);

I assume what you mean by ups;dhl;fedex is a table like:

CREATE TABLE PRODUCT_DELIVERY_COMPANIES
( PRODUCT_ID ... NOT NULL
, COMPANY_ID ... NOT NULL
, ...
,    CONSTRAINT ... FOREIGN KEY (PRODUCT_ID)
     REFERENCES PRODUCTS (PRODUCT_ID)
,    CONSTRAINT ... FOREIGN KEY (COMPANY_ID)
     REFERENCES DELIVERY_COMPANIES (COMPANY_ID)
,    CONSTRAINT ... PRIMARY KEY (PRODUCT_ID, COMPANY_ID)
);

and that seems about right.


Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created.

You can check whether your database is using circular logging by issuing:

./db2 get db cfg for dbemp | grep LOGARCH

If both LOGARCHMETH options are switched off...

First log archive method       (LOGARCHMETH1) = OFF
Second log archive method      (LOGARCHMETH2) = OFF

... then you'll need to change your database configuration to use archive logging before backups can be taken. I'm not 100% sure of this but I believe you'll need to restart your database and take an offline backup before you can start taking online backups.

There's an overview of how to do this in IBM's "configuring database logging options" documentation.

Note that if you change from CIRCULAR LOGGING to ARCHIVE LOGGING, you'll need to figure out what to do with your archived log files. Storing them on SAN/NAS/Tape to allow roll-forward recovery can be a really good idea if this data is important to you.


sqlplus works specifying the parameters but fails when using tnsnames.ora

I could fix the problem. I changed my TNS_NAMES.ORA removing ADDRESS_LIST and leaving like this:

ORCL=
(DESCRIPTION =
  (ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.ccccc.us-east-1.rds.amazonaws.com)(PORT=1521))
  (CONNECT_DATA=
  (SID=ORCL)
  )
)

After I created a new file sqlnet.ora and added this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

I also (just in case), renamed both files to lowercase:

[franklin@ip-10-148-17-253 admin]$ ls -ltr
total 8
-rw-r--r-- 1 root root 151 Aug  5 13:08 tnsnames.ora
-rw-r--r-- 1 root root  34 Aug  5 13:08 sqlnet.ora

So now I can connect just doing this:

sqlplus myuser/mypassword@ORCL


How to attach an .mdf file that was not properly detached?

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

on your 2008 instance, create a database with a data file of the
exact same size (I wouldn't bother trying to put a 2000 instance
anywhere)
shutdown SQL Server
rename the new database's mdf file, and paste the old one in its place
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?


Hi folks, In one of my Agent Job is running by every minute

Depending on how you have configured the history for the Sql Agent, you may have lost that information.  From this answer:

  The SQL Server Job system limits the total number of job history
  entries both per job and over the whole system. This information is
  stored in the MSDB database.

  Obviously you won't be able to go back and see information that has
  been since discarded, but you can change the SQL Server Agent
  properties and increase the number of entries that will be recorded
  from now on.

  In the SQL Server Agent Properties:

Select the History page
Modify the 'Maximum job history log size (rows)' and 'Maximum job
history rows per job' to suit, or change how historical job data is
deleted based on its age.

  It won't give you back your history, but it'll help with your future
  queries!

You might be able to tweak the following query to give you the information you're looking for (if it still exists).  I got the query from here.

;WITH jobListCTE as
(
SELECT j.name as job_name,
msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
message
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.step_name = '(Job outcome)'
)
SELECT job_name as [JobStep],
run_datetime as [StartDateTime],
SUBSTRING(run_duration, 1, 2) + ':' +
SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) as [Duration],
message
FROM jobListCTE
ORDER BY run_datetime DESC, job_name;


What's the impact of running sp_updatestats in production server?

That's a big 'it depends.'  Depending on how your statistics have been maintained and the options you specify you could end up running full table/index scans and thrashing your I/O and buffer pool. Depending on the characteristics of your hardware and databases that could be very bad.

Also, rebuilding statistics invalidates execution plans, which means you could see a CPU spike and slower performance while SQL Server re-compiles queries.

Best practices dictate updating statistics during off-peak hours to minimize impact.  Otherwise, take due precautions to minimize load on the system such as rebuilding statistics on only the tables that require it over a period of time.

Check books online for more information:

http://technet.microsoft.com/en-us/library/ms173804(v=sql.110).aspx

http://technet.microsoft.com/en-us/library/ms187348.aspx


Mysql synchronization databases on one machine

Why not Enable / configure mySQL Replication Service? I am not pro at it but I know you can achieve that by using mySQL Replication. See the link for details.
http://dev.mysql.com/doc/refman/5.0/en/replication.html

I am updating my answer here try to guide you to the proper sources or techniques. Try enabling clustering. and try to go through this link to load balance your mySql instances.
https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial

one machine running mySql instance and the same one doing the backup will not solve your issue but can be achieved through virtualization. Consider vmWare for example or try Virtualbox too.. but again if your server fails you will not going have nay means to take advantage of such design. However what will be good you go for another box and deploy the sql node there and then enable/ configure clustering


Accessing Service Broker from Different Databases

Consider the Asynchronous Procedure Execution example. One approach is to simply have one DB hosting all the SSB objects involved (the services, the procedure, the activation queue etc) and simply call the invocation procedure usp_AsyncExecInvoke across DBs, eg. EXEC SSBExecutionDB.dbo.usp_AsyncExecInvoke .... The SSB activated procedure could in turn run the code in the DB you invoke it from, by simply passing in a fully qualified 3 part name to execute:

EXEC SSBExecutionDB.dbo.usp_AsyncExecInvoke 'MyDb.dbo.MyProcedure';

Note that your procedure will execute under activation EXECUTE AS context and will thus be subject to the EXECUTE AS sandboxing. You must either mark the SSBExecutionDB as TRUSTWORTHY, or use code signing, see Signing an Activated Procedure.

This approach keeps all the SSB infrastructure in one place (in one DB) so it may be feasible for you. The disadvantage is that it does not leverage the loosely coupled SSB model which would allow you to schedule the async procedure even when the executionDB is not available. It also introduces a tight coupling between your DBs and the execution SSB DB, which creates problems vis-a-vis restores and failover (mirroring).


What does that "LIMIT 100" do in a query? What might its effect be for "heavy users" (who have lots of matching rows in the database?), and what might its effect be for "non-heavy users" (who will not have that many matching rows in the database?)? How many rows will need to be inspected by the system before the 100 LIMIT is reached for "heavy users", and how many rows will need to be inspected by the system before the 100 LIMIT is reached for "non-heavy users"? How many rows will have to be inspected by the system if a user doesn't even have the full 100 matching rows in the database?

"The question, then, is how to speed up the worst case scenario, right?"

The answer to that is that you either have to try to make more "intelligent" estimates of your hardcoded number 100, or you could try to rewrite your OR predicates as UNION queries, hoping that it's your engine's poor optimizer who doesn't do that automagically (and provided that all individual parts of those UNION queries can be served rapidly by existing indexes - if your predicates are generated dynamically, then you'll have to index as good as every column that could ever possibly appear in your WHERE clause).  (Disclaimer : I don't know about what optimizations your engine does and does not do.)


Is it possible to create a PostgreSQL RLS Policy that depends on values from the table the policy applied on?

You could create a function that avoids the endless loop:
CREATE FUNCTION is_ok(project_user) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   /* avoid recursion if the "user_id" is correct */
   IF ($1).user_id = current_setting('app.current_user_id')::bigint THEN
      RETURN TRUE;
   END IF;
   /* otherwise, recurse */
   RETURN EXISTS (SELECT 1 FROM project_user AS pu
                  WHERE ($1).project_id = pu.project_id
                    AND pu.user_id = current_setting('app.current_user_id')::bigint);
END;$$;

Then create the policy like this:
CREATE POLICY project_user_read ON project_user FOR SELECT TO PUBLIC
   USING (is_ok(project_user));


What is the smallest and fastest possible query in MySQL?

@Kondybas is correct; Fastest query is SELECT 1.

@a-horse-with-no-name is also correct "If your server can't cope with a statement like that once a minute, something is seriously wrong."

Percona Monitoring and Management monitors MySQL servers, in some cases, once per second, without "performance issues".


For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1?

With hot_standby_feedback on, vacuum can still be done, but it will be less effective as some tuples that would otherwise be vacuumed now have to be delayed to a later vacuum.  The only real downside that I know of is the increased bloat. How much of a downside this is depends entirely on your usage.  The worst case is if your database has small, intensively updated tables, like a work-queue table.  Those could get extremely bloated.  If you don't have that kind of table, you probably won't have a problem.

The problems with max_standby_*_delay are that other queries running on the standby are also having their horizon held back by potentially large amounts, and that if you hold back the stand-by long enough accumulated un-replayed WAL files will fill up your hard drive and you will lose the stand-by.


How can one drop all default values and allow NULL for all columns of a table?

You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE
You would have to issue this query
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will retrieve every column in the PRIMARY KEY.
You could then make this query generate the ALTER TABLE clauses for you as follows:
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will produce a comma-separated list of MODIFY COLUMN clauses you need to apply to the column after dropping the PRIMARY KEY. If you want to drop any of these columns, you have to change MODIFY COLUMN  to DROP COLUMN for the columns that need to be dropped.
Give it a Try !!!
UPDATE 2014-12-09 14:00 EST
Your comment

I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results

To give you a general idea of how query can help you, here is a table in MySQL on my laptop
mysql> show create table weird.vinner\G
*************************** 1. row ***************************
       Table: vinner
Create Table: CREATE TABLE `vinner` (
  `startnr` int(11) NOT NULL,
  `alder` varchar(25) NOT NULL,
  `kjonn` varchar(25) NOT NULL,
  `ovelseid` varchar(25) NOT NULL,
  `slutttid` int(11) NOT NULL,
  PRIMARY KEY (`startnr`,`slutttid`),
  KEY `slutttid` (`slutttid`),
  CONSTRAINT `vinner_ibfk_1` FOREIGN KEY (`slutttid`) REFERENCES `passering` (`slutttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

See the PRIMARY KEY. It has two columns: startnr and slutttid.
Now, if I run my code on that tabl, I get this list
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
    -> FROM information_schema.key_column_usage
    -> WHERE table_schema='weird'
    -> AND table_name='vinner'
    -> AND constraint_name='PRIMARY';
+------------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))     |
+------------------------------------------------------------------------+
| MODIFY COLUMN startnr DEFAULT NULL,MODIFY COLUMN slutttid DEFAULT NULL |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The output creates a MODIFY COLUMN clause for both columns.
If you were dropping the slutttid column, you would use PHP to change MODIFY to DROP
MODIFY COLUMN startnr DEFAULT NULL,DROP COLUMN slutttid

You would be doing this kind of code to generate the clauses needed


Many databases including Oracle have a COALESCE function that behaves like NVL, but takes two or more values.  This is useful to return a value when there are multiple columns that could have the desired value and/or to provide a default value.

If all columns in an index are NULL, Oracle does not index the row, so it will require a table scan to retrieve rows when you have a NULL value in the match condition.  In other cases, you have values that are not NULL, to match on and only one column that could be NULL.  This is common in OUTER JOIN when you want to select only one value in the outer table.

In any case, unless you have in index where the NULL value is indexed, a search of all candidate records will be required.  If you have an index that can be used to reduce the number of possible matches to less than 4% of the the records, then the candidate set may consist of those rows.  Oracle will generally use a table scan for small tables, or if there is no index that can limit the candidate rows sufficiently.  Above 4% (possibly lower) the preference will be for a table scan.

You have three options for the conditional:

NVL(column, value) = value
COALESCE(column, value) = value
column = value OR column IS NULL

In my experience, all three are equivalent.  You can verify for yourself, by running an explain plan on the query.

It is possible to influence the plan with hints.  However, this can easily result in sub-optimal plans.


There is a problem with the way you are doing this now, the huge amount of data just seems like a waste. Tons of logging and its just a nightmare for i.os. I would personally look into merge statements instead.

This performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

But if you are going with this solution, I would believe the better option is just to disable the indexes on the table. Then truncate the table and then load. This should allow faster inserts. You will need to reenable the indexes though and then rebuild them.


Are implicit transactions per batch or per session in SQL Server?

What exactly do you mean by "implicit transactions"? Do you mean that you have the session property of IMPLICIT_TRANSACTIONS enabled and are using that feature? Or do you mean that you just aren't specifying BEGIN TRANSACTION? (i.e. creating an "explicit" transaction)?

If you are meaning the IMPLICIT_TRANSACTIONS feature, that simply issues the BEGIN TRAN and nothing more. So whether or not you are using IMPLICIT_TRANSACTIONS to issue the BEGIN TRAN or if you are doing that explicitly yourself, you are still responsible for issuing the COMMIT or ROLLBACK. And yes, Transactions can span batches, whether you are using GO to separate batches or executing multiple queries by hitting F5 at different times.

In either case -- IMPLICIT_TRANSACTIONS set to ON or an explicit BEGIN  TRAN statement -- nothing is automatically committed. The only thing that might be automatic in these two cases is a ROLLBACK if you close the connection without issuing the COMMIT or ROLLBACK first.

Without either IMPLICIT_TRANSACTIONS set to ON or an explicit BEGIN  TRAN statement, then each individual query is its own transaction, that is "auto-committed" and you need not worry about it. Meaning, 2 separate INSERT statements are two completely separate transactions.


Sort order specified in primary key, yet sorting is executed on SELECT

For a non partitioned table I get the following plan

There is a single seek predicate on Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010.

Meaning that SQL Server can perform an equality seek on the first two columns and then begin a range seek starting at 1339225010 and ordered FORWARD (as the index is defined with [Date] DESC)

The TOP operator will stop requesting more rows from the seek after the first row is emitted.

When I create the partition scheme and function

CREATE PARTITION FUNCTION PF (int)
AS RANGE LEFT FOR VALUES (1000, 1339225009 ,1339225010 , 1339225011);
GO
CREATE PARTITION SCHEME [MyPartitioningScheme]
AS PARTITION PF
ALL TO ([PRIMARY] );

And populate the table with the following data

INSERT INTO [dbo].[SensorValues]
/*500 rows matching date and SensorId, DeviceId predicate*/
SELECT TOP (500) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
UNION ALL
/*700 rows matching date but not SensorId, DeviceId predicate*/
SELECT TOP (700) 3819,52,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
UNION ALL
/*1100 rows matching SensorId, DeviceId predicate but not date */
SELECT TOP (1100) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 1339225011
FROM master..spt_values

The plan on SQL Server 2008 looks as follows.

The actual number of rows emitted from the seek is 500. The plan shows seek predicates

Seek Keys[1]: Start: PtnId1000 <= 2, End: PtnId1000 >= 1,
Seek Keys[2]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010

Indicating it is using the skip scan approach described here

  the query optimizer is extended so that a seek or scan operation with
  one condition can be done on PartitionID (as the logical leading
  column) and possibly other index key columns, and then a second-level
  seek, with a different condition, can be done on one or more
  additional columns, for each distinct value that meets the
  qualification for the first-level seek operation.

This plan is a serial plan and so for the specific query you have it seems that if SQL Server ensured that it processed the partitions in descending order of date that the original plan with the TOP would still work and it could stop processing after the first matching row was found rather than continuing on and outputting the remaining 499 matches.

In fact the plan on 2005 looks like it does take that approach

I'm not sure if it is straight forward to get the same plan on 2008 or maybe it would need an OUTER APPLY on sys.partition_range_values to simulate it.


Creating a numeric user ID from distinct combinations of IP and user agent from a server log

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ip_address VARCHAR(39) CHARACTER SET ascii NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
    UNIQUE(ip_address, user_agent)
) ENGINE=InnoDB;

INSERT INTO users (ip_address, user_agent)
    SELECT DISTINCT ip_address, user_agent
    FROM server_requests;

Notes:

39 is the max length for IPv6; don't say 255 (to avoid indexing problems).
The extra index solves the performance problem by having the PK needed to optimize LEFT JOIN users u  ON r.ip_adress=u.ip_adress  AND r.user_agent=u.user_agent


You almost had the correct syntax.  This is exactly what you want:

SELECT * FROM rails_db WHERE username NOT LIKE ALL(ARRAY[my values]);

This is a very nice syntax, but it won't necessarily be fast, especially not if the array is large.  However, what you want is hard to optimize for performance, and there is no reason to think uglier syntaxes will be any faster.


How does an index speed up insertion in a table

An index like that with an INSERT like this only makes the INSERT slower.

An index could help if you performed an INSERT .. SELECT .. FROM ... and the index would speed up the SELECT part.


Variable to select database in SQL query

Per the comment by Tibor Karaszi the change only applies "to the EXEC scope"  This is validated by the Microsoft statement below.

In SQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.
Changes in database context last only until the end of the EXECUTE statement.  Source

The easiest work around is probably, the final example in the question. Adding a couple of lines at the beginning and end of your code can change the context for just a single execution. The rest of your code stays on whatever the context was before the `EXEC'
DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN  +' -- Add this
Select * from sysjobs_view
-- Up to 2GB of code here
' --And add this

EXEC (@Sql)

There are limitations to using this dynamic SQL solution  If there are single quotes in your 2GB of code, they have to be escaped by doubling the single quote.  This can be a huge pain if there is a lot of code to review and test.


Replication - Binary log parsing(Tableid generation, ON DELETE CASCADE handling, Changed columns for update event)

These are interesting questions with answers that are not too easy to come by.  The internal structures are sometimes not very well documented or the documentation is difficult to find and more than just a little bit frustrating.

Part 1: table_id

The table_id in a row-based replication TABLE_MAP_EVENT is not a value ordinarily exposed to the user, though you'll see it in the output of mysqlbinlog with --verbose and --base64-output=decode-rows.

#120820 13:42:23 server id 10  end_log_pos 681420677    Table_map: `xifc`.`action_log` mapped to number 4025219

This value comes from an incrementing global counter in sql/sql_base.cc.  It increments every time a table is added  to the cache of open tables that is limited in size by the global variable open_table_cache... so if that value is smaller than the number of tables being periodically accessed, you should see it increment more frequently.  All of the tables will have a new id on the next generated binlog event after a FLUSH TABLES.

Since a table appears to be closed and opened every time it is altered, the table_id apparently has no choice but to increment every time a table is altered, but this does not mean than an increment in the table_id for a given table necessarily means that the structure has changed.  My testing reveals that the table_id changes even when a column is renamed, even though all of the data types for the table are identical.

In summary: the table_id for a table always changes when the table is altered, and sometimes changes even when it has not been altered.  The counter resets if the server is restarted.  The TABLE_MAP event contains the table_schema and table_name.

Part 2: ON DELETE CASCADE ...not in the binlog?

I was surprised to find that, unlike row changes caused by triggers, cascaded deletes in child tables caused by foreign key constraints don't seem to show up in the binary log.  Then, I found this explanation and a moment's reflection makes it apparent why this might be true.

I'll give you a hint: it's the same reason MyISAM doesn't do foreign key constraints... namely, that's because MySQL itself doesn't do foreign key constraints... InnoDB does.  They're handled internal to InnoDB, apparently outside of the visibility of that part of MySQL server that handles binary logging, and rely on the slave to have the same constraints defined.  When the rows are deleted on the slave, InnoDB on the slave handles it, so from a replication perspective, they aren't "needed" in the binary log.

Part 3: Row Changes in UPDATE events and identifying columns

When a row is updated, an UPDATE_ROWS_EVENT is written, containing before and after images of the row in question.  (Inserts only contain an after image, deletes only contain a before image).  Prior to MySQL 5.6.2, each event contains the full before and after row image, which is what I like.  Arguably, the space savings for only logging what is needed for replication into the binlog doesn't justify the loss of potential recovery data, but a new variable binlog_row_image was introduced in 5.6.2 to allow you to change how many columns' worth of information are stored in the log.

Assuming this is set full, which would be the same as the old behavior, you get column values in the row events from every column that isn't null, in the row image (and null flags row columns that are null), and you get column type definitions in the table_map events that is sent at the beginning of every transaction for each of the tables that will be involved.  From this, you can extract the column values, ordered by their ordinal position within the table, but you don't get column names.  They are not in the binary log.

I have been working on a project that generates live events for background processes by emulating a slave server, connecting to the master (or a downstream slave with log_slave_updates enabled), requesting a binlog stream, decoding it, and generating a JSON representation of the event to be sent into a message queue (SQS or Stomp) or topic (SNS or Stomp).  I had to make an architectural decision: provide an array of the column values by ordinal position in the table and require the processes consuming the events to sort them out... or try to figure out the column names for inclusion in the event as an associative array (hash).

I decided to go with the column names, and I accomplished this by asking the server -- on a second connection that I keep open for the purpose -- to DESCRIBE table_schema.table_name every time I see a table_id that I had not seen before, and I cache it in memory until I see the same table mapped to a different id.  I also clear my cache if I see a ROTATE_EVENT in the binlog, since MySQL rotates the binlog if the server crashes or shuts down, and the table_id values restart.

The answer here ties back in with the answer on item #1.  Every time a row event occurs after a table is altered, the table shows up with a new ID, so if you fetch the table definition each time you see a new table_id, but cache the definition against the old id until it changes, you're pretty safe, though not precisely so: if a table is altered more than once in rapid succession and rows are changed between the alter events, it's possible for you to see a table definition that is newer than the one that was active when the binlog event was generated.  This becomes "more true" if your parser can't keep up with the rate at which the server is generating events.  Of course, in among the binlog events, you'll also see the ALTER TABLE event get replicated as a query event, but it is replicated exactly as it was entered by the client, so you'd have to parse, tokenize, and understand it, in order to make use of it... but this condition is very difficult to actually produce and unlikely to be a major concern.  I my implementation, I handle it by using the lesser of the number of columns in the binlog or the number of columns in the table definition, with the assumption that the primary keys are the most interesting columns, and usually at the left, and unlikely to be encoded incorrectly even if this condition is encountered.  A single alter on an active table works perfectly because I've already cached the definition under the table_id and am still using it to decode/encode row events until I see the new table_map event, which prompts me to fetch a new definition.

While working on my project -- which I am using on the fringes of production and plan to release under an open source license if it's ever truly "finished" -- something I found very helpful in figuring out some of the internals (even though I don't know Ruby) was the source code to Jeremy Cole's mysql_binlog.  Especially decoding of packed decimals. :(


Implement check constraint for varchar (7) to enforce YYYY-MM

You could try concatenating a valid 'day' on to the YYYY-MM values and use a CASE expression and the ISDATE function.

IF EXISTS (
        SELECT *
        FROM dbo.TEST
        )
    DROP TABLE dbo.TEST
go
CREATE TABLE Test (
    ID INTEGER
    ,YYYY_MM_CHAR CHAR(7) NOT NULL
    ,CONSTRAINT chk_YYYY_MM_CHAR CHECK (
        CASE
            WHEN ISDATE(YYYY_MM_CHAR + '-01') = 1
                THEN 1
            ELSE 0
            END = 1
        )
    );

INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-01')
INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-13')

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 9
The INSERT statement conflicted with the CHECK constraint "chk_YYYY_MM_CHAR". The conflict occurred in database "master", table "dbo.Test", column 'YYYY_MM_CHAR'.
The statement has been terminated.


Oracle TRANSLATE Not Returning Expected Result

Phil is right, "No, there isn't a limit", "it /should/ just work", and " this will be difficult to answer without copies of the data or another way to reproduce".  Here is a demonstration:

/*
drop table table_a;
drop table temp_table;
create table table_a as
    (select level id, to_char(level) column_to_update from dual connect by level <=200);
create table temp_table as (select level id from dual connect by level <= 200);
update table_a set column_to_update='2ñ' where column_to_update='2';
*/

SELECT * FROM table_a;

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

SELECT * FROM table_a;

You could try adding a WHERE clause to verify that TRANSLATE is actually changing rows.

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE)
AND column_to_update <> TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') ;

If this returns 200 rows then you might not be looking at the correct rows.  If it returns zero rows then the TRANSLATE function is not changing the data.


Does AT TIME ZONE automatically take care of DST conversion?

Yes, it accounts for it:

  AT TIME ZONE applies specific rules for converting input values in
  smalldatetime, datetime and datetime2 data types, that fall into an
  interval that is affected by the DST change

As per the MSDN article on the expression. The sys.time_zone_info column supplies information on if the zone currently is in DST, but the article above indicates that the rules take into account DST given a specific date time value.

Example:

/*------------------------
SELECT SYSUTCDATETIME() AT TIME ZONE 'Eastern Standard Time';
------------------------*/

----------------------------------
2017-01-18 22:18:12.3789253 -05:00

And when we try using a date with a different DST offset:

/*------------------------
SELECT CAST('2017-07-18' AS DATETIME) AT TIME ZONE 'Eastern Standard Time';
------------------------*/

----------------------------------
2017-07-18 00:00:00.000 -04:00

It assigns a different DST offset value, being aware of whether or not the date falls into DST.


How to filter a value in json field type on Postgres 9.2?

You would need the ->> operator instead:

select * from integrations.accounts where field ->> 'id' = 'xxxxxx';

But these operators were added with Postgres 9.3. Postgres 9.2 is just too old (and unsupported by now). It had barely more than the data type json itself. There seems to be a third-party extension "Json Enhancements for PostgreSQL 9.2":

https://clarkdave.net/2013/06/adding-json-enhancements-to-postgresql-9-2/
https://www.pgxn.org/dist/json_enhancements/doc/json_enhancements.html#Json.Enhancements.for.PostgreSQL.9.2

But I have never tried it and would much rather upgrade to a current, supported version of Postgres instead. See:

https://www.postgresql.org/support/versioning/


Insert a 'NULL' string in a table in PostgreSQL

'NULL' is not the same as NULL, the first one is an string just like any other string, so your constraint (NOT NULL) won't work.

I think you are working with a programming language (like PHP or Java) that you put your value inside a string with a variable like

INSERT INTO originators(originator, id, regexp)
VALUES ('$variable', $id, $regexp);

so in this case you have to correct your code to prevent the quotes, or if you can't do that you constraint should be CHECK(upper(originator) != 'NULL').


How to get average according to last update time?

Assuming your table and sample data look like this:

CREATE TABLE dbo.Item
(
    Item        char(1) NOT NULL,
    RecordTime  datetime2(0) NOT NULL,
    Value       decimal(5,3) NOT NULL,

    CONSTRAINT PK_dbo_Item_Item_RecordTime
        PRIMARY KEY (Item, RecordTime)
);

INSERT dbo.Item
    (Item, RecordTime, Value)
VALUES
    ('A', '2014-12-29T10:13:00', 0.139),
    ('A', '2014-12-29T10:12:00', 0.150),
    ('A', '2014-12-29T10:11:00', 0.113),
    ('B', '2014-12-29T10:10:00', 0.139),
    ('B', '2014-12-29T10:09:00', 0.120),
    ('B', '2014-12-29T10:08:00', 0.084),
    ('C', '2014-12-29T09:55:00', 0.101),
    ('C', '2014-12-29T09:54:00', 0.095),
    ('C', '2014-12-29T09:53:00', 0.129);

One way to write the query is:

SELECT
    MaxTime.Item,
    Average.Value
FROM
(
    -- Highest record time per item
    SELECT
        I.Item,
        RecordTime = MAX(I.RecordTime)
    FROM dbo.Item AS I
    GROUP BY
        I.Item
) AS MaxTime
CROSS APPLY
(
    -- Average value within 15 minutes
    SELECT
        Value = CONVERT(decimal(5,3), AVG(I2.Value))
    FROM dbo.Item AS I2
    WHERE
        I2.Item = MaxTime.Item
        AND I2.RecordTime >= DATEADD(MINUTE, -15, MaxTime.RecordTime)
) AS Average
ORDER BY
    MaxTime.Item;

SQLFiddle Demo


Connecting to a local MS SQL Server from a GUI client

If you are running the GUI on the same machine as the mssql-server service, then you can use localhost for the address (127.0.0.1).  If you are running the GUI from a different workstation, you'll need the IPv4 address of the machine where the mssql-server service is running.  You can obtain that by running ifconfig from a Linux terminal prompt.  Sample output from ifconfig:
eth0: flags=4163  mtu 1500
        ether 00:15:5d:89:45:01  txqueuelen 1000  (Ethernet)
        RX packets 423  bytes 137827 (134.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

eth1: flags=4163  mtu 1500
        inet 192.168.200.11  netmask 255.255.255.0  broadcast 192.168.200.255
        inet6 fe80::2f70:9d15:8e7d:16cb  prefixlen 64  scopeid 0x20
        ether 00:15:5d:89:45:04  txqueuelen 1000  (Ethernet)
        RX packets 20138  bytes 2006000 (1.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 19756  bytes 30125657 (28.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 1  (Local Loopback)
        RX packets 3239  bytes 361340 (352.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3239  bytes 361340 (352.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

In the output above, the bit that is important is the "inet" address, which in my case is 192.168.200.11.
The default port for SQL Server is 1433 - unless you chose a different port in mssql-conf use that port number.  Ensure the firewall on the Linux box is allowing outside connections via 1433, if you intend to connect to SQL Server via the network.
Use sa as the login, and the password you specified during SQL Server setup via the sudo /opt/mssql/bin/mssql-conf setup command.
I would leave the domain and unix socket path blank.
Once you have connected to the instance, you may want to configure a non-sa account.  Do that with the CREATE LOGIN statement.
Just an FYI, you can use SQL Server Management Studio to connect to SQL Server on Linux, if that's your desire.  Alternately, you can download Microsoft's native GUI client for Linux (and Windows & Mac), Azure Data Studio, here.


Can a trigger create a TABLE when inserting data?

You can use INFORMATION_SCHEMA.TABLES to check if dev_hist table exists, if not you can create it in your trigger:

CREATE TABLE T (id int, name text);

CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$
    DECLARE action_name text;
    BEGIN

        -- add your catalog information.
        CREATE TABLE IF NOT EXISTS dev_hist
        (
            action text
        );

        IF TG_OP = 'DELETE' THEN
            action_name := 'deleted';
            INSERT INTO dev_hist SELECT action_name;
        ELSE
            IF (TG_OP = 'INSERT') THEN
                action_name := 'added';
            ELSIF (TG_OP = 'UPDATE') THEN
                action_name := 'modified';
            END IF;
            INSERT INTO dev_hist SELECT action_name;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$dev_hist$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS dev_hist ON T;
CREATE TRIGGER dev_hist AFTER INSERT OR UPDATE OR DELETE ON T
    FOR EACH ROW EXECUTE PROCEDURE process_dev_hist();

INSERT INTO T VALUES (1);
DELETE FROM T WHERE id = 1;

1 rows affected

1 rows affected

SELECT * FROM dev_hist;

| action  |
| :------ |
| added   |
| deleted |

db<>fiddle here


What is the "proper" way to design a database architecture when entry counts belogning to different users may vary wildly?

No, if the items share common characteristics you should put them in the same table and define an index over the UserID.


Is there anybody using the SQL Server feature to create groups of stored procedures differentiated by number?

Numbered stored procedures are deprecated.

  msdn
  Numbered procedures are deprecated.
  Use of numbered procedures is
  discouraged. A
  DEPRECATION_ANNOUNCEMENT event is
  fired when a query that uses this
  catalog view is compiled.

My team encountered this in a maintenance project. We couldn't figure it out at first. Then, we did some research and found out that it is deprecated. We had to rebuild it to normal stored procs.

Numbered stored procedures won't show up in SSMS's Object Explorer tree.

Numbered Stored Procedures


Insert into table with identity insert taking almost a second

If I am not wrong this behaviors cannot be blocking as its an insert
  and unless something is blocking the whole table which is not the
  case.

If there are bigger queries reading data from table or table2, they could take a table-level shared lock, resulting in blocking.

You could test with read commited snapshot isolation level and use row versioning if blocking is the case.

You need to know the implications (version store will grow, long running open transactions can be problematic for your tempdb, row versioning is on query level, not transaction level.)

To enable RSSI:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Look into what your app supports, an example of when not to use RCSI is when application code is counting on pessimistic locking. More on that here

Autogrowth

Another reason could be an autogrowth event triggering as a result of the insert.
The time this takes depends on the autogrow settings, hardware, instant file initilization, .... this could take longer than expected, and be a blocking factor for your inserts as they have to wait for these new pages to be available.

Trace them with an extended event. This event should also provide you the query that made the autogrowth occur.

Pregrowing your files on idle moments could work to solve this.


Does it make sense to store a couple of Boolean values as array?

TL;DR: Do not use an array. Use individual boolean columns.

Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres.

Do nullable columns occupy additional space in PostgreSQL?

A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns hardly matter for the row size at all - even if defined NOT NULL. 1 byte each, 5 bytes and no alignment restrictions. Hardly relevant. There are typically much more substantial things you can do to your row. (You should have provided your actual table definition.)

Configuring PostgreSQL for read performance

Arrays have an overhead of 24 bytes. Plus actual data. And handling is much less convenient and much more expensive:

Calculating and saving space in PostgreSQL

This related answer has a detailed assessment for three relevant options:

separate boolean columns
an integer to encode up to 32 boolean values
a bitstring (bit(n) or bit varying(n))

Is there any difference between integer and bit(n) data types for a bitmask?


You're not using WITH ROLLUP correctly. WTIH ROLLUP creates a subtotal and/or total based upon what you tell it. You didn't specify what to call the subtotal and/or total so it is calling it NULL.

This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Output:

Item                 Color                QtySum
-------------------- -------------------- --------------------------
Chair                Blue                 101.00
Chair                Red                  210.00
Chair                ALL                  311.00
Table                Blue                 124.00
Table                Red                  223.00
Table                ALL                  347.00
ALL                  ALL                  658.00


Untested, but I believe your query should look like:

SELECT salesman
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2015'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice
           end) As [Q1 2015]
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2016'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice
           end) As [Q1 2016]
FROM Barnacles
WHERE DATEPART(Year, warrantypurchasedate) in ('2015', '2016')
  AND DATEPART(Quarter, warrantypurchasedate) = '1'
GROUP BY salesman

I moved  sum outside of case, and added a WHERE clause to filter out relevent rows. I also removed date from the GROUP BY clause, otherwise you will get 1 row per date.


Best approach to find missing logins and missing jobs between principal and mirror server

Using Power Shell worked for me. I have this as part of bigger script, I am only providing sections of it. Hopefully you can put it together.

FUNCTION report-status-primary
    {
        PARAM (
                $Check,
                $Variable,
                $Primary,
                $Secondary
              )

        WRITE-HOST "Checking $Check on $Primary..." -ForegroundColor Yellow
        IF ($Variable)

            {

                WRITE-HOST "$Check on $primary not on $secondary" -ForegroundColor Red
                $Variable
            }
        ELSE
            {
                WRITE-HOST "Match" -ForegroundColor Green
            }
        WRITE-HOST "------------------------------------------------------------" -ForegroundColor Yellow
    }

  FUNCTION report-status-secondary
    {
        PARAM (
                $Check,
                $Variable,
                $Primary,
                $Secondary
              )

        WRITE-HOST "Checking $Check on $Secondary..." -ForegroundColor Yellow
        IF ($Variable)

            {

                WRITE-HOST "$Check on $Secondary not on $primary" -ForegroundColor Red
                $Variable
            }
        ELSE
            {
                WRITE-HOST "Match" -ForegroundColor Green
            }
        WRITE-HOST "------------------------------------------------------------" -ForegroundColor Yellow
    }

Variable:

$SourceSQLServer = 'Primary Server'
$TargetSQLServer = 'Secondary Server'

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$SourceServer = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($SourceSQLServer)
$TargetServer  = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ($TargetSQLServer)

# -- Jobs
$JobsSNT = $SourceServer.JobServer.Jobs.name | where {($TargetServer.JobServer.Jobs.name -notcontains $_) -and $_ -notlike "*]__*" }
$JobsTNS = $TargetServer.JobServer.Jobs.name | where {($SourceServer.JobServer.Jobs.name -notcontains $_) -and $_ -notlike "*]__*" }

# -- Logins
$LoginsSNT = $SourceServer.Logins.name | where {$TargetServer.Logins.name -notcontains $_}
$LoginsTNS = $TargetServer.Logins.name | where {$SourceServer.Logins.name -notcontains $_}

Reporting:

report-status-primary -check 'Jobs' -variable $JobsSNT -primary $SourceServer -secondary $TargetServer
report-status-secondary -check 'Jobs' -variable $JobsTNS -primary $SourceServer -secondary $TargetServer

report-status-primary -check 'Logins' -variable $LoginsSNT -primary $SourceServer -secondary $TargetServer
report-status-secondary -check 'Logins' -variable $LoginsTNS -primary $SourceServer -secondary $TargetServer

Here is a sample output I get running this script.


Is there a way to ensure that a SQL Server trigger will be executed?

Yes.  Triggers occur as an implicit nested transaction within the scope of the caller's transaction.  The trigger will commit or roll back as part of this transaction.

See:

Nesting Transactions
Rollbacks and Commits in Stored Procedures and Triggers
ROLLBACK TRANSACTION (Transact-SQL)


Can I run concurrent backups of multiple read only filegroups?

Yes you can do multiple full file-group backups concurrently. The primary concern I would have would be for disk I/O and network I/O throughput doing so many at once. They'll still complete, just all slowly. Unless you have the I/O to handle 20TB at once.