"Corrupted key" error when creating/updating SQL Server 2012 credential for proxy account

I am going to attempt to answer my own question (as my experience may help others). Firstly, regarding the issue I experienced, I found this blog helpful (as were the comments from Sean Gallardy above).

As I had no Service Master Key (SMK) backup to restore from, my intention was to regenerate a new SMK using the ALTER SERVICE MASTER KEY REGENERATE with the FORCE option (causing loss of encrypted entities). I was prepared to have to recreate my credential secrets and Linked Server passwords. This instance had no Database Master Keys but, had it done so, I could have opened them using their password and regenerated them by the SMK (thus avoiding data loss).

However, I decided first to see if a simple restart of the SQL Server instance would resolve the issue - and it did! Upon restart, the Service Master Key verified successfully (and I confirmed this by creating a new test credential successfully).

My best guess is that whatever issue prevented the SMK verification previously (causing the "Service Master Key could not be decrypted using one of its encryptions" error log entry) had disappeared. I know that the SMK always has two encryptions: the machine key encryption and the service account encryption (and these provide an either-or decryption method in the case of cluster failover or service account change).

As the host WINTEL server was having issues, I imagine that invalidated the machine key encryption. I can't imagine what might have been invalidating the service account encryption (that account did not change) but whatever it was, it seemed to be a temporary problem. So my best guess is that, upon restart, the SMK was able to self-verify using the service account encryption (and, at that point, create a new machine key encryption).


enable log_bin_use_v1_row_events in mysql 5.6

There is one thing that cannot work with MySQL Replication from 5.5 to 5.6 : DATETIME. Why ?

MySQL 5.6 DATETIME has a very different byte format because it accommodates microseconds. When the binlog_format is ROW, or when MIXED switches from STATEMENT to ROW, only a 5.6 Slave can unpack the row change from the relay logs. A slave running MySQL 5.5 cannot handle that.

I have two recent posts where I mentioned this

Jan 02, 2015 : mysql replication master 5.5 slave 5.1 error on create database
Jun 18, 2015 : mysql master slave replication scenario

The workaround I learned from my boss back in January was to switch binlog_format to STATEMENT and restart mysqld.

If you enable log_bin_use_v1_row_events on a MySQL 5.6 Master to let a MySQL 5.5 slave read it, that sounds like it could work. I still worry about the DATETIME representation. If you set the option log_bin_use_v1_row_events to true and it stops MySQL 5.6 from writing microseconds with binlog_format ROW or MIXED, then you I think have something. Based on my two posts, I cannot guarantee other statements would be immune.

I would normally say "GIVE IT A TRY !!!" but I have never tried that log_bin_use_v1_row_events. So, go ahead and try and let us know.


SQL Server split mdf into multiple files

Who ever created the database did this on purpose.  Assuming that all the database files are part of the same file group (the database properties will tell you this) then all you need to do is do a DBCC SHRINKFILE any use the EMPTYFILE parameter.

DBCC SHRINKFILE (Example_1, EMPTYFILE)
GO
ALTER DATABASE MyDatabase REMOVE FILE Example_1
GO

Do that for each of the files and all the data will be put back into a single file.  You can then delete the extra files.

Now odds are this isn't hurting your performance any, so you'll need to continue investigating why you are having performance problems.


Splitting a table based on business logic

I cannot imagine any data that does not have a value to the business. If it doesn't have value why store it? Even technical logs have business value ("If we don't have these logs it is 50% more expensive to fix bug").

There are a few valid reasons to split a table:

Partition hot and cold data for cache efficiency
Too many columns (>1000 or so)
Many columns almost always null

Not sure what you gain by splitting off data by layering concerns, though. Everything gets harder to understand and to maintain (not easier).

My recommendation clearly is to have only one table. Don't artificially split the data.


How to uninstall Oracle 11G software that are installed in Windows 8?

Oracle has a tool called Deinstall that will remove an Oracle database as well as the Oracle software. Either GUI or command line. Check here on how to use it.

https://docs.oracle.com/database/122/HPCLI/oracle-deinstallation-tool-deinstall.htm


Is there a way to improve performance of this simple SELECT query?

525K rows takes time to shovel.  It takes time to read from disk (unless it happens to be cached in RAM.  It takes time to send that many rows across the wire to the client.  And the client has to receive them.

The index is optimal; it has the desired columns in the desired order.  But it is suspicious.  Keep in mind that a PRIMARY KEY is, by definition, unique.  It is usually not reasonable to have a "value" in the PK.  And there is no difference if you leave it out.

One thing that will make a small improvement is to shrink the datatypes where practical.  Almost no application need BIGINT, but you have 3.  BIGINT is 8 bytes.  INT is only 4 bytes and can hold a timestamp (a la epoch).  Surely you don't have billions of 'Instruments'.  PhenomenonName could be normalized or perhaps turned into a 1-byte ENUM.


Use surrogate or natural PK if a row is uniquely identified by two or more columns?

That is part of one of computing's big philosophical debates. Sometimes it gets as heated amongst information theorists as the vi/emacs thing does amongst Linux developers...

I always avoid using a value that might change as a key or part of a key, in this case player name or server name unless your system has an explicit rule that those properties can not change and must be unique. While you can in some databases use ON UPDATE CASCADE to remove problems due to the value of a PK needing to be updated, this feels unclean to me. If the player name must be unique then you do not really need a separate player ID as playername+servername is a candidate key in the player table too.

BTW: Your player table feels incorrect to me.I would keep servers as a separate entity so the same player record can be linked to multiple servers, so one player who plays in many places does not have to have different records for each server. Something like:

    Player             Ranking               Server
    --------------     -----------------     --------------
    PlayerID (PK)  <-- PlayerID (FK, PK)     ServerName
    PlayerName         ServerID (FK, PK) --> ServerID (PK)
    {more columns}     PlayerRank            {more columns}

or

    Player             Ranking           Server
    --------------     -------------     --------------
    PlayerID (PK)  <-- PlayerID (FK)     ServerName
    PlayerName         ServerID (FK) --> ServerID (PK)
    {more columns}     PlayerRank        {more columns}
                       RankID (PK)

(probably the former as PlayerID+ServerID is a candidate key and not having the extra ID saves space in both not having the extra data and needing an extra index)

One further point: PlayerRank can not be a unique index on its own as you will have a "rank 1" player on each server that has at least one player, so the unique index will need to be over ServerID+PlayerRank.


What are the 'free to use' workload generators available for Oracle?

Lazer,

Here are what I have found: Load and Performance Tools. This is a huge list of tools, you can find out what product is better for you, but I could not see any tool that has been created specially for Oracle databases (on my first sight). For Sql Server

An information resource for software testers

I hope it will help you.

ADDED:

The best generator that you made yourself.


MySQL 5.6 - Creating an event inside a stored procedure

It seems there is a conflict in MySQL documentation.
I think CREATE EVENT Syntax section in MySQL documentation is not correctly updated in later versions. Although "CREATE EVENT Syntax" sections says

  You can create an event as part of a stored routine, but an event
  cannot be created by another event.

In Event Scheduler Restrictions section in mysql documentation,
It says

  An event may not be created, altered, or dropped by a stored routine,
  trigger, or another event. An event also may not create, alter, or
  drop stored routines or triggers. (Bug #16409, Bug #18896)


In general, you want to utilize as much RAM as possible (leaving enough for the O/S, etc), since that means data is in-memory instead of being on disk.

For a CRM system serving 100 people, you may find 16GB of RAM dedicated to the server is not enough.  My phone has 8GB of RAM, for instance, and it's just a phone.

In-memory access times for data is a couple of orders of magnitude faster than accessing data on disk.


You need to take another full backup from the production database and restore it to the log shipping server in NORECOVERY mode.  Then the log shipping should be able to restart.  You may need to remove the log shipping and put it back by going through the wizard depending on how you set it up.


"Credential retrieval failed" during recovery of an Oracle database

I've just solved the same error on Oracle12c under Windows on my test enviroment.
The Oracle service was running under oracle user local account but I was trying the PITR with my domain user. Even if my domain user was member of ORADBA group I've got the same nasty ORA-12638 error.

I've changed the user account of the Oracle Service and got it finally working.
I guess if you use SYSTEM account it will work as well.

Regards,
Daniel.


New to databases - does my ERD reflect good database design?

I recommend using surrogate primary keys instead of artist or album names.  Doing this will improve the database design because currently a change in an artist's name would require updating each entry in your audio_files table for that particular artist.

You and I likely agree it would be strange for an artist to change their name, however they have a penchant for doing this.  Even beyond the infrequent artist name change, you are much more likely to encounter artist name changes due to user error.

Also, I noticed that you use the path_of_file as the primary key of the audio_files table.  Keep in mind that files may move for a variety of reasons, some of which may be out of the domain of control for your app.  You should consider how your database handles that.

To answer your follow up:

If you go with the unique key approach I recommend, updating the artist name would be straight forward:

UPDATE Artists
SET Artists.Artist_Name = 'New Name'
WHERE Artists.Artist_ID =

Updating the artist name in the design you propose would involve inserting a new record in the artist table, creating a subset of file paths with the old artist name, then updating each file path record to the new artist record, and finally deleting the old one.  Yuck!


Linux SQL Standard Edition being killed by OOM killer

Are there are other applications / workloads running on the server when SQL Server gets killed?

If Linux detects memory pressure, SQL Server will be the easy target - even if it's staying with the 18 GB limit you set.  Imagine SQL Server using 18GB, and 3 other applications using 6 GB each.  The Linux kernel will target the SQL Server process to free up RAM, because it is using the most of any single process.

You should monitor memory usage by all apps on the box leading up to the crash.  If other apps are using a lot of RAM, you should consider either:

removing them, or
further reducing the memory available to SQL Server

If SQL Server is really exceeding its limits, your best bet is likely to contact Microsoft support.

For a real life example, see this article from SQL Server support for recommendations on avoiding the OOM Killer:

How to safeguard SQL Server on Linux from OOM-Killer


SQL Azure "hot" table query slowdown over time

The problem might be cardinality estimation, index fragmentation and stale statistics causing performance issues that increase over time. If you're not rebuilding your indexes and updating your statistics, this can be affecting the optimiser's plan choices and causing performance degradation over time. It might also explain why TRUNCATE seems to correct the issue (see Kendra Little's article, basically the optimiser tracks TRUNCATE to ensure it makes sensible decisions on empty tables even without statistics updates).

You need to do a couple of things to track down the root cause:

Enable Query Store to identify regressions in query performance over time. If a new plan is being selected due to cardinality issues or stale statistics, Query Store will highlight them.
Implement regular index and statistics maintenance tasks if you haven't already. If you have stats or index frag issues, this should help alleviate them.
Track your wait statistics over the one-two week period to see if a particular wait is increasing over time causing the degradation.
Next time it occurs, don't truncate to fix - you need to collect as much data as you can to try and determine the root cause. Get the statistics details for the table, get the index frag details for the table, get the current execution plan details, collect BlitzCache details, these will all help you identify the root cause.


Postgres 9.6 - Taking differenctial backup

Please don't re-invent the wheel! There are so many traps in backup up and recovering the probability you fall into them is pretty high!

  Directly Rsync the Postgres data directory.

Please don't! You'll copy unconsistent data...

I think you should consider one of these 3 tools :

barman
backrest
probackup

Barman and probackup have real differential backups and will check backup consistency before restoring a backup. That's why I prefer one of those tools.

You can find a complete comparison in Anastasia Lubennikova's excellent conf talk Advanced PostgreSQL backup and recovery methods slides.


Robust way to build a DWH without downtime

For reference, here's the solution that I came up with:

Create a new schema dwh
Build the new version of the DWH in that shema
Drop the old public schema
Rename the new dwh schema to public

(this assumes that your main schema is named public, of course)

This method works quite reliably and without interrupting/killing existing database connections, so I prefer the solution over my initial "create and rename database" approach. Here's the SQL that I use for building the DWH (it assumes that you're connected to the DWH database):

--we drop the temporary schema if it already exists
DROP SCHEMA IF EXISTS dwh CASCADE;
--we create the temporary schema
CREATE SCHEMA dwh;

--we modify the search path for this session
SET search_path to dwh;

--create your DWH here...

--now we commit the new version

--make sure you're connected to the correct database when executing this ;)
DROP SCHEMA IF EXISTS public CASCADE;

--we rename the DWH schema to public
ALTER SCHEMA dwh RENAME TO public;

--you might need to restore permissions on the new public schema...
GRANT USAGE ON SCHEMA public TO dwh_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dwh_user;

Apart from needing to manually restore permissions and having to recreate extensions in the new schema, I found this solution to be quite optimal as it does not require superuser privileges for the DWH user, and works without creating/dropping any databases. I have not yet figured out a way to copy permissions from one schema to another, which is my only minor issue with this approach.


Its a physical server on windows 2012 R2 standard. It has 4 sockets with 10 cores per socket and 20 logical procesors per socket total 80 from error log what i see. Its SQL2012 trying to move to 2017

Ok.  The memory on this box is seperated into 4 seperate nodes, each attached to a single socket.  SQL Server will use memory across all the sockets, and since much of SQL Server memory is used for caching database pages and query plans, there will be significant amounts of cross-node memory access.  And that's OK.  Query memory grants will be allocated on the local node for the worker running the task.

SQL Server has several resources it allocates per NUMA node, and for SQL Server 2016+ will automatically subdivide large NUMA nodes with the Automatic Soft-NUMA feature.

So on the one hand there's nothing you can do about the physical configuration of the server memory, and on the other there's nothing you need to do as SQL Server will automatically configure itself to use the NUMA nodes most effectively.

  Is there a way we can measure if 2 nodes might work better than 4 nodes for future design ?

Basically fewer nodes is better, but there's a limit to how many cores and how much RAM you can put on a single NUMA node.

Most of the cross-NUMA node memory access should be in the Buffer Pool, which you can track with a performance counter:

SQL Server: Buffer Node Remote Note page lookups/sec
    Indicates the number of lookup requests from this node which were satisfied from other nodes.


MySQL - Information_schema Events - How persistent or retention is managed

In MySQL, an "event" is not a periodic data element. It's more like a cron job (sorry— an "at" job in Windows). It's a routine of code with a schedule.
The content of information_schema.events is metadata about the schedule job. For instance, the code body, the schedule, etc. This information should be unchanging no matter how many times you the event runs.
It does not store any history of executions of that job, nor results or data produced by that job.


How do I add a MySQL linked server to SQL Server?

We have a linked server on SQL pointing to a MySQL server.
Creating the linked server is a pretty straightforward thing.

The tricky part is getting the permissions sorted out on MySQL side.

If memory serves my right you need to create a local account on the SQL server and create an login on the MySQL server for the full computer name where the linked server will run.

On the SQL server I have a login for Sqltest. This is a SQL account

On the linked server properties I have "Security" > Be made using this security context > MySQL login username and password

On the MySQL box I have a login for Sqltest@myserver.com. This is where my linked server failed for quite a long time.


SQL column=ALL(query): Equivalent meaning

If not mistaken you can use a not exists predicate and negate the operator for ALL in the subquery. Something like:

SELECT ...
FROM T
WHERE T.x = ALL ( select y from U )

corresponds to

SELECT ...
FROM T
WHERE NOT EXISTS ( select 1 from U
                   WHERE NOT T.x = U.y )

I.e.

WHERE NOT EXISTS ( select 1 from U
                   WHERE T.x <> U.y )

In other words, for the predicate = ALL to hold there must NOT EXISTS a tuple that does satisfy NOT =

Note that this might not hold if nulls are taken into consideration. Example:

with t (n) as ( values (1),(1),(2) )
select * from t as t1
where not exists (select n from t as t2 where t1.n <> t2.n )

and:

with t (n) as ( values (1),(1),(2) )
select * from t where n = all (select n from t)

Both return 0 rows. However:

with t (n) as ( values (1),(1),(2),(null) )
select * from t where n = all (select n from t)

return 0 rows whereas:

with t (n) as ( values (1),(1),(2),(null) )
select * from t as t1 where not exists (select n from t as t2 where t1.n <> t2.n )

return 1 row.

Three-valid logic makes things a whole lot more complicated, and one needs to be even more careful than usual when doing query rewrites.

FWIW, there is an really interesting project that applies Homotopy Type Theory (HoTT) to pseudo SQL:

http://cosette.cs.washington.edu/

The idea is to be able to prove whether two queries are equvalent, and if not provide an example of situations where they are not. Any vendor that adopts this to there dialect of SQL will have an killer app for sure.


Install Full Text search on existing instance

The setup program restarts the target instance when installing full text search.

However, there's an easy way to find out: test it! Don't trust what I say: test it in your test environment.


This is how I solved my problem:

SELECT
    descricao [nome],
    JSON_QUERY(endereco) [endereco]
FROM teste2
WHERE id=2
FOR JSON PATH, ROOT('clientes')

The "magic" was performed by JSON_QUERY (expression, path ) as documented here:

JSON_QUERY (Transact-SQL)

See also:

Frequently Asked Questions about JSON in SQL Server


Execution plan for staging In-Memory tables estimate row count =1

Are you using memory-optimized tables, or memory-optimized table variables? Memory-optimized table variables suffer from the same issues as "regular" table variables: the cardinality is assumed to be very low.

The other potential issue with using memory-optimized tables as a destination for staging ETL, is that all writes to memory-optimized tables are serial. So you will have to "roll your own" parallelism.


DBCC CHECKDB uses tempdb and can stress the I/O subsystem heavily when it runs.  It uses tempdb to store facts during the checks it is running.  Paul explains some of the details of this in his blog post (http://sqlskills.com/BLOGS/PAUL/post/How-does-DBCC-CHECKDB-WITH-ESTIMATEONLY-work.aspx) If you are getting latch contention on the system tables in tempdb, the only thing you can really do is reduce your usage of tempdb.  Adding additional files only reduces latch contention on the PFS, GAM and SGAM pages.

Have you configured the SQL instance following Microsofts recommendations for Sharepoint, which includes setting 'max degree of parallelism' to 1 for the instance? (http://technet.microsoft.com/en-us/library/hh292622.aspx)


See content of temporary Table in Oracles' EXPLAIN

As @pmdba mentioned, no way to peek at that table, and if you could, it would likely be raw data in some strange format.  But if you want more detail, find out the SQL_ID for the statement in question (via v$session and v$sql for example), and then query the v$sql_plan table for the column OTHER_XML.  For running in SQL*Plus, you'll want to include the noted set commands to see the output.
set long 65000
set linesize 200

select sql_id, other_xml from v$sql_plan where sql_id='26upzh3jd5fd7'
and other_xml is not null;

and you will see all sorts of interesting things, if you are patient in reformatting it into something readable.  Note that information is basically a hint used in invoking the SQL; what really happens may or may not match that.
Now if you want to get into what actually happens, including what "guesses" the Oracle optimizer does before actually running a query, you need to (assuming you have DBA privileges), turn on SQL trace for the session, run your SQL, then turn off SQL trace after.  Then go to your diagnostics directory, and find the *.trc file associated with your session.  And make sure you have some food nearby.  A lot of data there, but it has helped me several times to fix long-running queries.  Brief example:
execute DBMS_SESSION.SESSION_TRACE_ENABLE(binds=>true);
-- invoke your SQL after a brief SQL to help you find it later in the trace
select 'HEY my sql start' from dual;
-- your select query goes here
select 'HEY my sql stop' from dual;
execute DBMS_SESSION.SESSION_TRACE_DISABLE;

The trace file will be in a directory that in Linux is likely named something like
/u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace


Windows Service Management Node Won't Start

DISCLAIMER : Not a CMCDBA, Not a MySQL Cluster Expert
According to the Book

Page 175 describes ndb_mgmd as follows:

The ndb_mgmd binary represents the management server. This program reads in the cluster configuration file (config.ini) and then sends the configuration to any of the later starting nodes.It is also used for management (for example, restarting nodes, starting backups) and monitoring the health of the cluster. It also creates the cluster log, which is a central location for all cluster activities to be logged to.

Just from that brief description, you need to do the following:

Make sure there are no Storage Nodes and SQL Nodes running before starting ndb_mgmd
You need to locate the cluster log and find any vital error messages
Check to make sure config.ini has this:

config.ini
[NDB_MGMD]
id=(some number)
HostName=(DNS Name or IP Address)

Check to make sure config.ini can see every other node

Sorry, this is not really an answer. This is what I would do in your situation.
Here are the MySQL Cluster Experts in the DBA StackExchange:

Frazer Clement
Mat Keep
Andrew Morgan

Until someone can fully address this, I would defer to them to come up with an answer.


Why does this tsvector update use so much space?

Databases cannot simply overwrite data "in place".  If they did, they wouldn't be able to have atomicity or isolation, and probably not much concurrency and durability either.

When you do a bulk update of every row in a table, in PostgreSQL, the resulting table has a copy of both the old data and the new data. You can rid of the copy of the old data by running VACUUM FULL <table name>.  Or you can just do a regular VACUUM, but if you do that it will not shrink the amount of space it takes up, it will just mark the space as being re-usable for future work on that table.


What are the practical limitations on a column family in Cassandra?

I'm not a fan.  It's about as good an idea as creating a relational table named OrdersOrCustomers with columns defined for both.  The storage-engine penalty is slightly lower in Cassandra because of the sparse-cell storage under the hood, but it's still bad practice.

This bites you later when you want to map/reduce over your data; each task will have to scan over all your data, and filter out the rows that don't match what you're actually interested in (e.g., customers).  And good luck making sense of the statistics that Cassandra tracks per-CF.  ("Is this CF the source of 80% of my application reads because of the order data? Or because of the customer sessions it's combined with? Or the other five data types I threw in?")

If you absolutely positively need tens or hundreds of thousands of CFs?  Even then I'd rather run Cassandra without arena allocation, than mutilate my data model like this.


Do I need a composite primary key in a junction table?

You should use the combination of EntityId and TagId as composite primary key, here is why:

This will enforce uniqueness, that you will have to do even if you add a surrogate key.
These columns are what you will base most if not all of your queries on this table, so they should be indexed anyway
Adding a surrogate key will not have any benefit from the performance perspective.

btw, I don't know about MySql, but in Sql Server, a primary key is not necessarily the clustered index. It is set as the clustered index by default, but you can change the clustered index to any other index if you need.


Should I save "document ID" as a String or Number in MongoDB

One note, somewhat unrelated it's not an object hash, but rather a BSON ObjectID which is somewhat more predictable than a hash.  It's a small point, but it can be an important one.

There are a couple of things which will usually swing you one way or another:

Are they all guaranteed to be base 10 numbers? If so, then you should be save storing them as such, and it will usually be more efficient to do so in terms of things like index size (and usually object size and storage, but that depends on the string).
If there are hex digits or similar in there, or if there is any doubt about the content, then you will want to go with a string instead

If you do go with a string type, be aware of the slight quirks involved with Lexicagraphical ordering which will be used for any sorting and to satisfy range based queries on the field.  Again, these are not usually show stopping problems, but they can cause some interesting results if you are not aware.

Finally, make sure you pick a type and stick to it if this field is going to be indexed.  You are allowed to mix types in a field in MongoDB - having a flexible schema is one of its major strengths, but if you index such a mixed field you will only generally get results for the type you query on.  For example, querying an indexed field using an numeric type when it contains a mix of strings and numbers will only yield results from the number type and vice versa.


Single query to get only newest entry of grouped redundant entries

The best index for this query would be on (vendor_id, created, file_name), with this specific order on the indexed columns.

There are various other methods to rewrite this type of queries, without using GROUP_CONCAT(). There is even a tag, at SO and this site: greatest-n-per-group.

One way to rewrite (needs the same index as above). It assumes that (vendor_id, created) is UNIQUE (ie. there are no two rows with same vendor and created) or it isn't unique and you want all the duplicate/tied results:

SELECT
    v.vendor_id,
    f.file_name AS newest_file,
    f.created                    -- you can have this in the results as well
FROM
    ( SELECT vendor_id           -- you can replace the "v" subquery with
      FROM files                 -- a single "vendors" table,
      GROUP BY vendor_id         -- if there is one
    ) AS v
  JOIN files AS f
  ON  f.vendor_id = v.vendor_id
  AND f.created =
      ( SELECT fi.created
        FROM files AS fi
        WHERE fi.vendor_id = v.vendor_id
        ORDER BY fi.created DESC
        LIMIT 1
      ) ;

If (vendor_id, created) is not UNIQUE and/or you want just one row of the duplicates, change the joining condition to:

--  the query as it is
--
  JOIN files AS f
  ON  f.id =
      ( SELECT fi.id
        FROM files AS fi
--
--  the query as it is


The table belongs to the extension pglogical, so pg_dump doesn't dump it.

Objects that belong to an extension don't get included in the dump, they are subsumed in the CREATE EXTENSION command from a database dump, because that object will be created by the CREATE EXTENSION command. If the object itself got dumped too, restoring the dump would cause an error, because the object already exists after CREATE EXTENSION.

You could use psql and do something like

\copy (SELECT * FROM pglogical.replication_set ORDER BY ...) TO 'some.file' (FORMAT 'csv')


How do I ensure only one instance of a job is running?

The way SQL Server Agent works is exactly how you want already - if you schedule the job for every 10 minutes, and at 11:00 it runs for 12 minutes, it won't run again until 11:20 (the 11:10 instance won't start, because an instance of the job was already running).


Replication consistently fails to bring new rows to subscriber

I believe you are running into the issue described in Subqueries that you define on merge articles are not re-evaluated.

From the article the cause is:

  This behavior occurs because the query is not re-evaluated and the row
  is not propagated as part of replication when you update a row in a
  table that is referenced by a subquery. Although you can place a
  subquery in a row filter, it is not a join filter. Although you can
  define a subquery that is based on data from another table, this too
  can cause unexpected results at the Subscriber.

And the resolution is:

  To resolve this behavior, use a join filter when you want the filter
  definition to be re-evaluated during every merge synchronization
  process.


pg_upgrade unrecognized configuration parameter "unix_socket_directory"

I hacked the problem by running (as root):
mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> \
     /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Run pg_upgrade as intended, then undo the hack:
    mv -f /usr/bin/pg_ctl{-orig,}

The problem is that pg_upgrade executes the program pg_ctrl with arguments that specify files in the old "unix_socket_directory" rather than the new "unix_socket_directories" (note the second is plural). This hack renames the original /usr/bin/pg_ctl to /usr/bin/pg_ctl-orig, and then creates a shell script in its place that simply calls the original pg_ctl program, passing all arguments with any strings "unix_socket_directory" changed to "unix_socket_directories".
In bash, one can change a portion of a string, say from bar to baz in a variable $foo, by using ${foo/bar/baz} (note this does not change the variable, but rather returns the variable's modified contents). Arrays can also be used with ${x/y/z} to retrieve an array with all of its contents replaced, all at once. The variable $@ is an array that contains all arguments passed to the program/script/function, so the new pg_ctl script executes the old one with all arguments changed from the old directory name to the new one.


Why you want to avoid Dynamic SQL in stored procedure?

There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance.

I don't think there is a concrete example as per se but I would say this: Try to achieve what you are after using regular queries and statements first - only then once you have exhausted all other avenues do it dynamically. Just remember that executing a dynamic SQL string is done in a separate user session to the module that is calling it - so you may encounter permissions issues where you are not expecting any.

If your worried about performance; test it. If your worried about security; validate your input. There is no right or wrong - only that you use your best judgement based on the information and tools you have available to you at the time.


The outer select needs to find all rows in your table that match the where clause created_date is not null. That column isn't in that index, so the index (statistically) doesn't help – doing a full table scan is usually much faster than accessing all rows via an index.

If you had a separate index that could be used for that clause, it still might not be if it is not selective enough (i.e. if you don't have a vast majority of rows with null create dates).

If you can extend that index, try adding trunc(created_date, 'MONTH') to it and changing the (outer) where clause to use that function too (the null will "propagate" through the function).
Then you can try adding the status column too to avoid the table completely.

Indexing the function doesn't appear to work here, but indexing the date should help for the outer query.

But looking at the query more closely, I don't understand the meaning of the sum(select count(1) ...) in the select list, which is the most costly part. I don't believe this will return anything meaningful (count of rows per group times number of rows with status='SOLVED' per group).
If you want the number of rows that have status_alarm = 'SOLVED' within each group (this makes sense to me), you could rather:

sum(case when status_alarm = 'SOLVED' then 1 else 0 end) as total_solved

With that and a covering index, the query is answered with a single index fast full scan.

Test setup

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>
SQL> drop table foo;

Table dropped.

SQL> create table foo(
  2      cd date
  3    , p1 number not null
  4    , p2 number not null
  5    , p3 number not null
  6    , st number
  7    , stuff char(250));

Table created.

SQL>
SQL> insert /*+ append */ into foo
  2    select
  3     case when mod(rownum,20) = 0 then null else sysdate-(rownum/10000) end td
  4    ,round(dbms_random.value(0, 5)) p1
  5    ,round(dbms_random.value(0, 5)) p2
  6    ,round(dbms_random.value(0, 5)) p3
  7    ,round(dbms_random.value(0, 4)) st
  8    ,'lets imagine there are lots more columns in the table'
  9    from dual connect by level <= 500000;

500000 rows created.

SQL> commit;

Commit complete.

SQL> create index foo_ix1 on foo(p1, p2, p3);

Index created.

SQL> create index foo_ix2 on foo(cd, p1, p2, p3, st);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

Query structured the same as yours

SQL>
SQL> set timing on
SQL> set autotrace traceonly
SQL> select
  2    trunc(cd, 'MONTH') cm
  3    ,p1
  4    ,p2
  5    ,p3
  6    ,count(1) as oc
  7    ,sum(
  8      (select count(1)
  9       from foo b
 10       where b.st = 0
 11       and trunc(b.cd, 'MONTH') = trunc(a.cd, 'MONTH')
 12       and b.p1 = a.p1
 13       and b.p2 = a.p2
 14       and b.p3 = b.p3)
 15    ) as st
 16  from
 17    foo a
 18  where
 19    trunc(cd, 'MONTH') is not null
 20  group by
 21    trunc(cd, 'MONTH')
 22    ,p1
 23    ,p2
 24    ,p3
 25  order by
 26    trunc(cd, 'MONTH');

648 rows selected.

Elapsed: 00:04:15.83

Execution Plan
----------------------------------------------------------
Plan hash value: 1720639959

------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   475K|  7885K|   |  5901   (3)| 00:01:11 |
|   1 |  SORT AGGREGATE        |     |     1 |    20 |   |        |      |
|*  2 |   INDEX FAST FULL SCAN | FOO_IX2 |    28 |   560 |   |   583   (2)| 00:00:07 |
|   3 |  SORT ORDER BY         |     |   475K|  7885K|    14M|  5901   (3)| 00:01:11 |
|   4 |   HASH GROUP BY        |     |   475K|  7885K|    14M|  5901   (3)| 00:01:11 |
|*  5 |    INDEX FAST FULL SCAN| FOO_IX2 |   475K|  7885K|   |   614   (7)| 00:00:08 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."P1"=:B1 AND "B"."P2"=:B2 AND "B"."ST"=0 AND
          TRUNC(INTERNAL_FUNCTION("B"."CD"),'fmmonth')=:B3)
   5 - filter(TRUNC(INTERNAL_FUNCTION("CD"),'fmmonth') IS NOT NULL)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   22699250  consistent gets
      3  physical reads
      0  redo size
      22461  bytes sent via SQL*Net to client
    996  bytes received via SQL*Net from client
     45  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    648  rows processed

That's a lot of gets.

Modified query

SQL>
SQL> select
  2    trunc(cd, 'MONTH') cm
  3    ,p1
  4    ,p2
  5    ,p3
  6    ,count(1) as oc
  7    ,sum(case when st = 0 then 1 else 0 end) as sc
  8  from
  9    foo a
 10  where
 11    trunc(cd, 'MONTH') is not null
 12  group by
 13    trunc(cd, 'MONTH')
 14    ,p1
 15    ,p2
 16    ,p3
 17  order by
 18    trunc(cd, 'MONTH');

648 rows selected.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 4221217160

------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   475K|  9277K|   |  6438   (2)| 00:01:18 |
|   1 |  SORT ORDER BY         |     |   475K|  9277K|    16M|  6438   (2)| 00:01:18 |
|   2 |   HASH GROUP BY        |     |   475K|  9277K|    16M|  6438   (2)| 00:01:18 |
|*  3 |    INDEX FAST FULL SCAN| FOO_IX2 |   475K|  9277K|   |   614   (7)| 00:00:08 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TRUNC(INTERNAL_FUNCTION("CD"),'fmmonth') IS NOT NULL)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
       2125  consistent gets
      0  physical reads
      0  redo size
      21304  bytes sent via SQL*Net to client
    996  bytes received via SQL*Net from client
     45  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    648  rows processed

(Note that even without any index, this version is orders of magnitude more efficient than the first one.)


There must be a lock on a system catalog preventing the update. This is a pretty unusual occurrence.

Check pg_stat_activity for long running transactions that are not in state idle. Cancel those.
Look for prepared transactions in pg_prepared_statements and roll them back.

If both don't help, have a look at pg_locks while your CREATE USER hangs. It will show you for which transaction the statement is waiting.


How to repair, or drop/create a corrupted table in mysql?

This situation sounds a lot like a post I answered two years ago (InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage))
First, here is the InnoDB Architecture

In that post, I basically point out that the metadata of the table is still present inside the system tablespace file ibdata1.
When you ran SHOW TABLES;, all mysqld did was report the .frm files. It is possible to create the table as MyISAM because it has no attachments to ibdata1 or any other metadata. Yet, you cannot convert it to InnoDB because the metadata somehow imagines that the .frm and some tablespace_id is attached to the table name. Based on my old post, I would just mysqldump all the other tables and call you table a lost cause. However, Percona has the Data Recovery Toolkit for just such an occasion.
In the webpage about the Data Recovery Toolkit says

The Percona Data Recovery Tool for InnoDB is a free toolkit for recovering lost or corrupted data from InnoDB tables. It provides a way to extract InnoDB's data from files or raw devices in a tab-separated format when standard InnoDB recovery cannot be used for some reason such as a dropped table or severe corruption that prevents InnoDB from recognizing the data. It can be used for MySQL recovery to retrieve deleted tables, recover previous versions of updated rows, or fix InnoDB file corruption after a hardware failure such as a memory fault or bad cabling.
Percona has used these tools to perform MySQL data recovery for many customers, including extremely large datasets. Although the tools are powerful, performing InnoDB data recovery correctly requires expert knowledge of the InnoDB storage format and source code plus experience, and intuition. Percona can help with our data recovery service.

I have not used it, but if Percona made it, you should give it a try.
If you have a backup of that lost table (in the form of a mysqldump), you should carry out the steps from my old post and then load the backup of the lost table.
SUGGESTION
Once you get this situation squared away, you should tune InnoDB to flush changes to disk.
[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

That way, when you shutdown mysql, InnoDB can more thoroughly flush changes.
If you want even better assurance of InnoDB being flushed to disk, you should use MySQL in Linux.


It looks like replication is what you need here. There are different flavors of replication (merge, transactional and snapshot) that are suitable in different situations. In this case, it looks like transactional replication is what you need, unless changes on the secondary copy have to be propagated to the original copy of the data.

I fail to understand the role of the second schema. If you need both schemas at the primary database, then trigger could be a way to keep the two schemas in sync. If you need the second copy of the tables only at the second server, you can replicate the tables from sch1 to sch2 without issues.


All the select .. union .. select .. bits count as a single statement. I can't find an article in the documentation to justify this but I can offer supporting evidence.

First, the semicolon is the Transact-SQL statement terminator. There is only one place in your query it will be accepted and that's at the very end of the very last SELECT. Second, the execution plan consists of a single tree, indicative of a single statement. In contrast two queries submitted as a single batch would cause two disjoint execution trees.

If there is an explicit transaction in place the select..union becomes part of that and the locks are held until the COMMIT or ROLLBACK. If you have an implicit transaction, by which I mean autocommit, not SET IMPLICIT_TRANSACTIONS, it spans all of the selects. Locks will be held for however long they would be held absent the UNION, governed by isolation level, query options (TABLOCKX etc.), lock escalation, trace flags .. and all the other things that affect this.


My instinct is to (side by side) migrate as is. Then once live and stable, separately, add always-on ag's.

That is perfectly reasonable and supported.

However.

Are you sure you really want to perform the cluster creation, configuration and AG provisioning and testing on a live (post-migration) system?

If you do the AGs first, you can perform all that configuration and testing as many times as you want before the migration.


Data Flow Transform Not Automatically Mapping Columns

Copying and pasting SSIS packages can lead to a few issues. It sounds like you have simple packages - a dataflow with a source to destination. Generally, those don't have the hangup I've run into but I have seen situations where people were inconsistent in their definition of data types. Email - varchar(40), varchar(80), varchar(120), nvarchar(256) all in the same database, just in different tables. What can happen is that you first build your package using the 80 size. When you copy/paste and fix your source table, the editor may not pick up on the data size change when you goto the 40 table because it fits within the old sizing. But I've also seen the reverse - it might not pick up the increased size. Resolution isn't horrific, just change your query to select 1 AS x, let it set the metadata and then fix your query back to the proper select.

As Dave suggested, this task is ideal for an entry level Biml task. In fact, as along as you don't mind not having your explicit source query, my Biml replicate-o-matic is pretty much exactly what you're describing. Earlier incarnation is on SO and I'm linking to it to hedge against link rot.

If you like your approach, you can keep your approach. But, save yourself the clicking and dragging in your destination and replace it with six clicks: three  right, three left. In your mappings tab, right click to bring up this context sensitive menu

Left click "Select All Mappings"

Right click and the left click "Delete Selected Mappings"

Right click and then Left click "Map Items by Matching Names"


Will this index end up lopsided?
No. The way the BTree algorithm works keeps it balanced. (One interpretation of the "B" in BTree is for "balanced".) As leaf pages fill they cause parent pages to split, with half the rows going to each new parent. Further rows cause further splits, which cascade up the tree until the root has to split, at which point a new root page is created. At every stage all paths from root to leaf have the same length and hence "balanced".

Should it be reindexed regularly?
Yes. Each DBMS has its own idiosynchracies but likely regular re-build of indexes will be recommended. If for no other reason, the statistics will get out of date eventually which will result in less efficient query plans.

Should I use a low, normal, or high fill factor?
As all writes will be on the right-most pages leaving free space anywhere else will be a waste. There will never be any writes to use up that free space. Use a fill factor of 100%.

Should we try to find something else to put into the index
Indexes aren't (generally speaking) created for entertainment purposes. They're there to improve query performance or support constraint enforcement. If your workload would benefit from a <something> + date index then create that. If not, don't. Indexes require extra work during writes and consume space and maintenance time. Create the ones you need and no more. And no less. Keep in mind filtered indexes and included columns. Bear in mind that optimiser math may mean your indexes aren't used anyway.

Will there be issues with hotspots or index page contention .. What can I do to mitigate that?
Well, yes, in theory. First test and prove you'll have a problem meeting your expectied transaction-per-second count. If you can't, are you sure it's this index that's the limiting factor? If it is, in-memory tables and snapshot isolation may implement concurrency mechanisms different to the "base" system's, depending on DBMS. There may be alternative storage engines whose characteristics differ. Try those to see if the pain receeds. You can always post a follow-up question here with quantatative observations!

I understand that for an ascending surrogate key..
Surrogate keys are made-up values that replace human-intelligible natural keys. They're used for reasons releated to DBMS implementation peculiarities and are not inherent to the relational model. Your date is not a surrogate key - it is business data.

a reverse key index might be better.
Hmmm .. OK, so instead of writing all today's rows to key value 20161021 you'd write them to .. 12016102? How does that help? Hashing won't help either: to use the hashed values for lookup each date would have to produce the same hash so there's still a hotspot.

Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?
Probably. The vast majority of indexes on most systems are like this. By-and-large they work just fine and the DBMS is able to process significant workloads on modest hardware. Indexing is one of those things you can (and should) tweak ad nauseam after go-live. Concentrate on delivering a normalised, feature-rich, debugged system. Tune it afterwards.


Can data changes become visible between a subquery and an outer statement in PostgreSQL?

Is it possible for the DELETE to see rows that weren't previously committed and visible to the SELECT subquery?

No, that's not possible.

Postgres always operates at "read committed" isolation level (or higher) and every statement sees a consistent snapshot of the database from the time the statement started. And in this case the statement defining the snapshot is the DELETE statement. The select from the sub-query sees the same snapshot


Will a SELECT . . . INTO use short-circuit evaluation for a COALESCE?

Yes, per documentation:

Database SQL Language Reference (Oracle 11g): COALESCE()

  Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

Note (having been burned by this myself): be wary of relying on proof-of-concept to determine order-of-operations in SQL language.  SQL is declarative, and the engine is often given the flexibility to execute portions of a statement in arbitrary orders.  So just because one ad-hoc example shows the order of execution of operations A, B, C to be A-then-B-then-C, it may be the case that in different circumstances, engine will opt for alternate execution order, e.g. B-then-A-then-C.


How to removing or reduce the size of redo logs in MySQL cluster

You can decrease the size of redo log files by decreasing parameter NoOfFragmentLogFiles which defines no. of fragment files or by reducing size of each fragment file by parameter FragmentLogFileSize. By default  NoOfFragmentLogFiles=16 and FragmentLogFileSize=16M. Hence total redo log files size will be 256MB and there are 4 of these if you will look into your file directory system; D8 D9 D10 D11; If your NoOfReplicas parameter is 1 then only one redo log file will take upto size 256MB and rest will have an apparent size of 256MB not actual.You can see the redo usage by executing command all dump 2398; on management node and look into node's ndb_"NODEID"_out.log. You will see redo files usage.


How to set a specific directory location for individual MySQL database?

You would have play games with symlinks.

WARNING : The following only works with innodb_file_per_table enabled

For example, suppose your datadir was /opt/mysql/data. Each database woudl reside under that folder. If you have three databases (db1, db2, db3), then the folders are:

/opt/mysql/data/db1
/opt/mysql/data/db2
/opt/mysql/data/db3

Before doing anything run this query:

mysql> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');

Let's start by creating temp databases

mysql> create database tmpdb1;
mysql> create database tmpdb2;
mysql> create database tmpdb3;

Next, let's move every table you have db1 to tmpdb1, db2 to tmpdb2, db3 to tmpdb3

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME tmp',db,'.',tb,';') FROM information_schema.tables WHERE table_schema in ('db1','db2','db3')" > /root/MoveTables1.sql
mysql -uroot < /root/MoveTables1.sql

OK, we moved every .ibd file sideways to tmp databases

Go into mysql and make sure all the tables have been moved. Databases db1-db3 should be empty

mysql> use db1
mysql> show tables;
mysql> use db2
mysql> show tables;
mysql> use db3
mysql> show tables;
mysql> use tmpdb1
mysql> show tables;
mysql> use tmpdb2
mysql> show tables;
mysql> use tmpdb3
mysql> show tables;

Next, drop the original databases

mysql> drop database db1;
mysql> drop database db2;
mysql> drop database db3;

Go into the OS and create symlinks

ln -s /home/user2/mysql /opt/mysql/data/db1
ln -s /home/user2/mysql /opt/mysql/data/db2
ln -s /home/user3/mysql /opt/mysql/data/db3
chown -R mysql:mysql /opt/mysql/data/db1
chown -R mysql:mysql /opt/mysql/data/db2
chown -R mysql:mysql /opt/mysql/data/db3

Go into mysql and make sure the databases are visible

mysql> show databases;

Next, move the tables into the symlinked databases

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME ',SUBSTR(db,4),'.',tb,';') FROM information_schema.tables WHERE table_schema in ('tmpdb1','tmpdb2','tmpdb3')" > /root/MoveTables2.sql
mysql -uroot < /root/MoveTables2.sql

Finally, run the first query you started with

mysql> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');

If the same display comes up, you did it successfully.

UPDATE

@AaronBrown was kind enough to remind me that spreading .ibd files does not buy you anything except for spread-out files. No performance gains whatsoever. Thanks Aaron.

https://dba.stackexchange.com/a/4425/877
http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/


Automating MySQL DB Creation with Specific date

This script will dynamically create the database prepended with the date format you want

SET @dbname = 'rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Just set the @dbname in the script.

If you want it as a Stored Procedure, here you go:

DELIMITER $$

DROP PROCEDURE IF EXISTS `MakeRestoreDB` $$
CREATE PROCEDURE `MakeRestoreDB`(GivenDBName VARCHAR(20))
BEGIN

    SET @dbname = GivenDBName ;
    SET @today = DATE(NOW());
    SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
    SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
    SET @ut2 = UNIX_TIMESTAMP(@today);
    SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
    SET @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

END $$

DELIMITER ;

Then, just call MakeRestoreDB('rolando');, and you are good to go.

Give it a Try !!!

Hmmmmm ... some of these statements seem familiar :-)


Why are column store databases the new thing now?

By and large, I think the increase in the popularity of data warehousing is prompting the move to Columnstore indexes.

Columnstore indexes are geared toward increasing query performance for workloads that involve large amounts of data, typically found in data warehouse fact tables.

Columnstore indexes store data by column rather than by row. By storing data in columns rather than rows, the database can more precisely access the data it needs to answer a query rather than scanning and discarding unwanted data in rows.

For wide tables, such as those commonly found in data warehouses, Column Store Indexes come in handy as you essentially reduce the amount and size of data needed to be accessed for any given query.

Check out some of these links for more information and examples:

Understanding the SQL Server Columnstore Index

Column-oriented DBMS

Columnstore Indexes in SQL Server 2012


PostgreSQL: Copy Table Functions to another database schema

You can't tell pg_dump to dump functions only.  You can exclude tables, but you still have to deal with other objects (like a_horse_with_no_name said).

However, you can make a dump without data (-s) and filter it upon restoring.  Note the -Fc part: this will produce a file suitable for pg_restore, which is able to consume a custom-made table of contents.

First take the dump:

pg_dump -U username -Fc -s -n public -f dump_test your_database

Then create a list of the functions:

pg_restore -l dump_test | grep FUNCTION > function_list

And finally restore them (-L specifies the list file created above):

pg_restore -U username -d your_other_database -L function_list dump_test

Note that this is a nearly verbatim copy of my earlier answer on stackoverflow.


The easy way to disable replication is to run this on the RDS slave

CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;

If Slave_IO_Running is Connecting, then there is one or both of the issues

The grants for the repl2 user is wrong
firewall issue on port 3306.

If the issue is the grants, drop the repl2 and add it back with

DROP USER 'repl2'@'%';
CREATE USER 'repl2'@'%' IDENTIFIED BY '111111';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl2'@'%';

I'll leave the FW/SecurityGroup stuff to you.

Once these are cleared up, run the CALL mysql.rds_set_external_master with the proper private IP. Please don't use 192.168.x.x.


Running multiple instances on the same host

To answer your immediate question on how to set it up, I and Randy Melder addressed this question May 31, 2011.

DTest and Laurynas Biveinis answered this more recently (Sep 20, 2011).

Some swear by its usage these days.

Years ago, mysql used to provide a script called safe_mysqld which made it simple to have multiple instances of mysql. All you did was create a my.cnf for the what instance on whatever port you wanted. Then called:

safe_mysqld custom_my.cnf

MySQL stopped distributing safe_mysqld in favor of mysqld_multi.

However, I have learned how to use mysqld_safe. Believe it or not, because of understanding mysqld_safe, I actually wrote my own mysql multi-instance engine back in Feb 2011. It is in production use right now with many of my employer's clients. Here is how I did it:

First, make this service engine called /etc/init.d/mysqlservice

#!/bin/bash

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

WHICH=/usr/bin/which
ECHO=`${WHICH} echo`
GREP=`${WHICH} grep`

#
#  Check for a four digit number greater than 3306
#  Make sure the my.cnf for the Port Number Exists
#
P1=${1}
if [ "${P1}" == "" ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^[Hh][Ee][Ll][Pp]$"`
if [ ${X} -eq 1 ]
then
  ${ECHO} "Usage: service mysqlservice <port-number [3307-3399]> {start|stop|restart|status|mycnf}"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^33[0-9][0-9]$"`
if [ ${X} -eq 0 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYSQLD_PORT=${P1}
if [ ${MYSQLD_PORT} -lt 3307 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYCNF=/etc/my${MYSQLD_PORT}.cnf
if [ ! -f ${MYCNF}  ]
then
  ${ECHO} "${MYCNF} Does Not Exist"
  exit
fi
MYCNF_BACKUP=${MYCNF}_backup

NOHUP=`${WHICH} nohup`
SLEEP=`${WHICH} sleep`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
CAT=`${WHICH} cat`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
MYSQLD_SAFE=`${WHICH} mysqld_safe`
MYSQLADMIN=`${WHICH} mysqladmin`
MYSQL=`${WHICH} mysql`
MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1"
MYSQL_PING_FILE=/tmp/MySQL${MYSQLD_PORT}Ping.txt
MYSQL_STAT_FILE=/tmp/MySQL${MYSQLD_PORT}Status.txt
MYSQL_ERROR_LOG=/var/log/mysqld${MYSQLD_PORT}.log
MYSQLD_START="${MYSQLD_SAFE} --defaults-file=${MYCNF} --port=${MYSQLD_PORT}"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"
MYSQLD_PING="${MYSQLADMIN} ${MYSQL_CONN} ping"
PROGNAME="MySQL (Port ${MYSQLD_PORT})"

#
#       This service will use mysqld_safe to run mysql server instances on other ports
#

start() {
  cd /tmp
  ${ECHO} -n $"Starting ${PROGNAME}"
  ${NOHUP} ${MYSQLD_START} 2>/dev/null 1>/dev/null &
  ATTEMPTS=0
  STARTING_MYSQLD=1
  MINUTES_TO_TRY=3
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STARTING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    READY_FOR_CONNECTIONS=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "ready for connections\.$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STARTING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STARTING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STARTING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Started ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 0 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQL_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

# See how we were called.

${MYSQLD_PING} 2>/dev/null > ${MYSQL_PING_FILE}
MYSQL_RUNNING=`grep -c "mysqld is alive" ${MYSQL_PING_FILE}`
${RM} ${MYSQL_PING_FILE}

case "$2" in
  start)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      start
    else
      ${ECHO} ${PROGNAME} is Running
    fi
    ;;
  stop)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      ${ECHO} ${PROGNAME} is Not Running
    else
      stop
    fi
    ;;
  restart)
    stop
    ${SLEEP} 1
    start
    ;;
  status)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      ${ECHO} ${PROGNAME} is Not Running
    else
      ${MYSQL} ${MYSQL_CONN} -A -e"status" > ${MYSQL_PING_FILE}
      ${GREP} "Server version:" < ${MYSQL_PING_FILE}  > ${MYSQL_STAT_FILE}
      ${GREP} "UNIX socket:"    < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${GREP} "Uptime:"   < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${GREP} "Threads:"  < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${CAT} ${MYSQL_STAT_FILE}
    fi
    ;;
  mycnf)
    less ${MYCNF}
    ;;
  edit)
    cp ${MYCNF} ${MYCNF_BACKUP}
    vi ${MYCNF}
    ;;
  *)
    ${ECHO} $"Usage: $0 {start|stop|restart|status|mycnf}"
    ${ECHO} $"Usage: $0 help"
    exit 1
esac

exit

Next, create mysql instance file for a specfic port.

For example, here is the service mysql3307

#!/bin/sh
#
# readahead:    Prereads programs required for startup into memory
#
# chkconfig: 2345 4 99
# description:  This service causes the programs used during startup \
#               to be loaded into memory before they are needed,\
#               thus improving startup performance
#
#

# Sanity checks.
[ -x /usr/sbin/readahead ] || exit 0

# Check for > 384 MB
#free -m | gawk '/Mem:/ {exit ($2 >= 384)?0:1}' || exit 0

# Source function library.
#. /etc/rc.d/init.d/functions

WHICH=/usr/bin/which
SERVICE=`${WHICH} service`

MYSQL=`${WHICH} mysql`
PORT_NUMBER=3307

# See how we were called.
case "$1" in
    start)      ${SERVICE} mysqlservice ${PORT_NUMBER} start    ;;
    stop)       ${SERVICE} mysqlservice ${PORT_NUMBER} stop     ;;
    status)     ${SERVICE} mysqlservice ${PORT_NUMBER} status   ;;
    restart)    ${SERVICE} mysqlservice ${PORT_NUMBER} restart  ;;
    mycnf)      ${SERVICE} mysqlservice ${PORT_NUMBER} mycnf    ;;
    edit)       ${SERVICE} mysqlservice ${PORT_NUMBER} edit     ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status|mycnf}"
        ;;
esac

Make sure that the mysql instance file has the port number defined in PORT_NUMBER between 3307 and 3399.

As mysql instance file calls mysqlservice, please note that mysqlservice will check for the config file for the specfic port.

In the case of mysql3307, mysqlservice will look for /etc/my3307.cnf.

Make sure you have the following explicitly defined in /etc/my3307.cnf

[mysqld]
datadir=/var/lib/mysql3307
socket=/var/lib/mysql3307/mysql.sock
port=3307

[mysqld_safe]
log-error=/var/log/mysqld3307.log

The datadir and socket can be at places of your choosing.

To create mysql3308 and other services, following the same paradigm but use the exactly port number defined throughout.

Give it a Try !!!


Is a SQL query still called a "query" when doing a deletion, update or insertion?

I typically call them DML statements or just statements. Query is commonly used for convenience.

After all, you're issuing a query against the database - if you say...

DELETE dbo.table WHERE id = 5;

...isn't at least part of that a query? What about...

INSERT dbo.table1(col) SELECT col FROM dbo.table2 WHERE ...

?

And no, I would not call them "scripts" - that implies something else altogether (usually a collection of statements, in a wide variety of languages, and commonly saved to a file).


Problems with connecting to a remote Oracle 12c database

The problem was down to the TNSNAMES.ORA referring to the SERVICE rather than the SID.


Best way to choose which columns to index to improve performance

Determining indexes for tables is not easy. I can offer some general guidelines that will help, but the specifics are going to be up to you to determine through testing on your systems with your queries.
The first, and most important, index you set is your clustered index. You only get one, so you want to make it right. Further, the key on your clustered index is also going to be the key for looking up all your non-clustered indexes. So, generally, you want to try satisfy three general criteria for your clustered index. First, the cluster should go on the column, or columns, that define the most commonly used path to the data. You don't say how the data is most commonly filtered above, so I can't make a recommendation based on that. Second, because the clustered index key is used for lookups in other indexes, as much as you can, you want it to be as small a data type as possible. You'll frequently see an ID column like you have because it satisfies this. However, don't get hung up on this. If a 'Name' column is the most frequently used path to the data, even though it's a wider key than an int, it can still be the clustered index. Further, the clustered index has to be unique. That's not to say the column must be unique. SQL Server can make the cluster unique internally if it has to. Yes, this adds overhead, so unique data is preferred, but again, driven by reality, not arbitrary rules.
Nonclustered indexes. Use as few as you can. However, create them as you need them. From the sounds of things, sorting on timestamp frequently, you might want an index there. However, this is where things get fun, that doesn't mean it will get used to sort the data. It depends on the other filtering criteria and how the data is being retrieved, which columns, the exact WHERE clause, etc. Same thing goes for creating a nonclustered index on Name. It could be a good choice. However, what other columns are being retrieved?
As was already mentioned, if you add a nonclustered index, but it only satisfies the filter criteria for the query, you'll still have to go to the clustered index to look up the rest of the columns. This is fixed by either adding additional columns to the nonclustered key (although, only do that if they're also used for filtering), or by using the INCLUDE key word.
All of this has to be tested by looking at the execution plans for your query. That will show you which indexes are being used and how they are used. So plan on pulling that information out.
Yeah, this is an enormous topic and I've only scratched the surface here. I have a 900 page book on query tuning and a 600 page book on execution plans. There's that much to talk about.


Create MySQL trigger that updates position of each row when sorted by a specific field

You (as in, the user) won't generally sort data within a table.  You (as in, a user) will sort the output from queries.

The order that the data is stored is generally irrelevant to you, and the database will store the data in a way that makes sense for it.

If you want/need the data ordered when you run a query, put the order by statement in the query...

select x,y,z
from table1
order by x asc, y desc


As ypercube pointed out, you want to use COUNT(DISTINCT t.contests_id) here to get the unique count of contests per users. All credit goes to ypercube on this one, but for a readable version of the example query on how to do this:
SELECT u.*, COUNT(DISTINCT t.contests_id) AS contest_total
FROM users AS u
LEFT JOIN trials_has_users AS tu
    ON tu.users_id = u.id
LEFT JOIN trials AS t
    ON t.id = tu.trials_id
WHERE u.id = 1 AND tu.users_id = 1
GROUP BY u.id;


How to order the where clause and join columns in a covering index?

If all your filters are equality matches (such as =, rather than >, LIKE, !=, etc), then it's going to useful to list them first. Then the data that matches those three conditions will be easily locatable, and in CustomerID order, which gives good options for being used in a join. Remember that your matching on CustomerID needs to join on several things.

Oh, and if your CustomerID is non-nullable and has a FK, then the Query Optimizer might not even be looking at t2.


There are many general solutions to the problem. Here are three that quickly come to mind.

SSAS
VoltDB
Map Reduce Framework

What you're describing is what would historically fall under an OLAP solution. If you can handle some lag time in your data then perhaps a solution such as SQL Server Analytic Services (SSAS) could provide you a solution. You could read data from these tables, perhaps via some secondary reporting server to not affect your operational database, and then perform these aggregations and processing of data you described.

However, this problem also has some newer solutions we see in the Big Data realm with a specific focus on "Velocity" not necessarily "Volume" of data. Velocity can mean either the processing of relatively static data quickly or the processing of dynamically changing data that is actively being ingested by the DB.

A second option is to allow a contemporary "NewSQL" database engine, such as VoltDB, to do this for you. VoltDB handles a lot of this "indexing" as you referred to it and it does so in real-time as opposed to per-aggregated data that may not be current. There are always performance tradeoffs when performing aggregations across many tables as opposed to a specific table but in my experience an in-memory DB engine, such as Volt, can beat traditional OLAP solutions when considering many metrics including processing speed and management.

Another option is to use the Map Reduce framework many DB engines support today. This implies that you're happy with off-line processing of a problem\query as opposed to real-time processing.

It's not an exhaustive list but hopefully that gives you some other ideas. I know that the triggers you are speaking of can easily spiral out of control due to hotspots in your tables especially if your SQL is not optimized. The one recommendation I will strongly make is to definitely get this processing off of your operational server and do this on a secondary replica\reports server.


You can save on the delete and vacuum if you will use smart partitioning!
Make sure your design is compatible with dropping partitions instead of actual DELETE.


Judging from the comments and your update to the question, it looks like the case where the data being loaded from legacy system is taking too long to be applied to the SLAVE.

Without the information on what your binlog_format is set to, I can only assume that it is set to ROW. You can try setting this to MIXED and then the SLAVE updates should run a lot quicker and not lag behind the master unless there are other factors affecting the SLAVE being slow.

When logged in as a privileged user, run the following from MySQL client:

SET GLOBAL binlog_format='MIXED';

If the above resolves your problem, then add this to your /etc/my.cnf to make it permanent across MySQL restarts.


Which of these build numbers will tell me what version of SQL Server is installed on a server?

Yes, you can query the database to find out the version via sqlcmd.

The actual SQL you'll want to use is just SELECT @@VERSION but to do it via sqlcmd, you can use:

sqlcmd -S[server] -U[user] -P[password] -q "SELECT @@VERSION"

Also check the MS sqlcmd reference for further info on flags you can use with sqlcmd.


Distinct on two columns, how to rid from 'duplicates'

in this simple example you can use the least and greatest functions to make the distinct on condition match in both cases.

select distinct on ( least(msg_from, msg_to),greatest(msg_from, msg_to))
array[msg_from, msg_to] as participants from mockdata_messages
order by least(msg_from, msg_to),greatest(msg_from, msg_to)


How to get last and first occurrence of items into separated columns?

Test:

SELECT
  t_min_max.name,
  t_min.value AS value_1st,
  CASE WHEN min_id <> max_id THEN t_min.value END AS value_last
FROM (
  SELECT name, min(id) AS min_id, max(id) AS max_id
  FROM table
  GROUP BY name
) AS t_min_max
JOIN table AS t_min
  ON t_min_max.min_id = t_min.id
JOIN table AS t_max
  ON t_min_max.max_id = t_max.id;

With complex queries, you should always work your way from the inside out,

SELECT name, min(id) AS min_id, max(id) AS max_id
FROM table
GROUP BY name;

You can see here that we're grabbing the range of IDs for each user,

id name     value
1  Daniel   3      <- this is a min(id)
2  Daniel   7
3  Daniel   2      <- this is a max(id)
4  Carol    9      <- this is a min(id)
5  Carol    4
6  Carol    9      <- this is a max(id)
7  Ray      5      <- this is both min(id) and max(id)
8  Charles  1      <- this is both min(id) and max(id)

After we do that we have a table like,

         min(id) | max(id)
Daniel | 1       | 3
Carol  | 4       | 6
Ray    | 7       | 7
Charles| 8       | 8

We then self-join that back to the original table in order to get the values.


You cannot update an IDENTITY field, so you have to insert new records into the table if you want to manipulate the IDENTITY value.  You are able to adjust the IDENTITY SEED throughout the process though to fit your needs or outright ignore it (so long as you do not insert duplicate values) with SET IDENTITY_INSERT [tableName] ON.  Here's an example script that hopefully illustrates what you can do better than my explanation:

CREATE TABLE #tmp
(
      ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , MyValue VARCHAR(255)
)

-- Insert Test Data
INSERT INTO #tmp (MyValue)
VALUES ('one')
      ,('two')
      ,('four')
      ,('three')
      ,('five')
      ,('del')
      ,('seven')
      ,('del')
      ,('del')
      ,('del')
      ,('del')
      ,('del')
      ,('thirteen')

-- Purge some test data to make a pocket of missing IDENTITY values
DELETE FROM #tmp
WHERE MyValue = 'del'

-- Check Current IDENTITY value
DBCC CHECKIDENT(#tmp)

SELECT * FROM #tmp

-- purge ID 3 & 4 to reinsert/fix their IDs
DELETE FROM #tmp WHERE ID IN (3, 4)

SET IDENTITY_INSERT #tmp ON

--Re-Insert 'three' and 'four' records with matching IDs
INSERT INTO #tmp (ID, MyValue)
VALUES (3, 'three')
      ,(4, 'four')
-- Add Values Outside the Range
      ,(21, 'twenty one')
      ,(101, 'one hundred one')
SET IDENTITY_INSERT #tmp OFF

SELECT * FROM #tmp

-- As you can see adding values outside the expected range auto-advances the IDENTITY
DBCC CHECKIDENT(#tmp)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred two')

SELECT * FROM #tmp

DBCC CHECKIDENT(#tmp)

--Advance the Identity to 130
DBCC CHECKIDENT(#tmp, RESEED, 130)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred thirty one')

SELECT * FROM #tmp

--decriment the Identity
DBCC CHECKIDENT(#tmp, RESEED, 5)
INSERT INTO #tmp (MyValue)
VALUES ('six')

SELECT * FROM #tmp

-- caution this will fail as IDENTITY 7 already exists
BEGIN TRY
INSERT INTO #tmp (MyValue)
VALUES ('seven')
END TRY
BEGIN CATCH
    DECLARE @myMessage NVARCHAR(4000)

    SELECT @myMessage = ERROR_MESSAGE()

    RAISERROR(@myMessage, 0, 1) WITH NOWAIT
END CATCH

-- caution this will fail as IDENTITY 3 already exists
BEGIN TRY
SET IDENTITY_INSERT #tmp ON

INSERT INTO #tmp (ID, MyValue)
VALUES (3, 'three')
END TRY
BEGIN CATCH

    SELECT @myMessage = ERROR_MESSAGE()

    RAISERROR(@myMessage, 0, 1) WITH NOWAIT
END CATCH

SET IDENTITY_INSERT #tmp OFF

-- Be sure to Reset IDENTITY back to max value otherwise you risk an IDENTITY value collision
DECLARE @maxID INT
SELECT @maxID = MAX(ID) FROM #tmp

DBCC CHECKIDENT(#tmp, RESEED, @maxID)

INSERT INTO #tmp (MyValue)
VALUES ('one hundred thirty two')

SELECT * FROM #tmp

-- Cleanup
DROP TABLE #tmp

Some notes, DBCC CHECKIDENT requires db_ddladmin privileges or higher to use.  Because of this, I couldn't include a DB Fiddle example.  Also, and this may be something you want to dig into as well, but I believe that pushing data from a "test" environment qualifies said environment as "production worthy" which may affect your licensing.  Licensing is out of scope, but this could bite you depending on how you have your server licensing setup, so just be wary of promoting data up your environment stack.


Which authentication is better for MS-SQL Server 2019 : integrated security=SSPI vs uid=sa;pwd=xx | windows auth vs sa auth

Security Difference
You can find that on the Connecting Through Windows Authentication doc:

Windows Authentication is the default authentication mode, and is much
more secure than SQL Server Authentication. Windows Authentication
uses Kerberos security protocol, provides password policy enforcement
with regard to complexity validation for strong passwords, provides
support for account lockout, and supports password expiration. A
connection made using Windows Authentication is sometimes called a
trusted connection, because SQL Server trusts the credentials provided
by Windows.

Performance Difference
The existing performance difference between these two methods isn't enough to advise you to choose SQL Server Authentication over Windows Authentication to gain any performance improvement in detriment of security. Therefore, if you have the option to use Windows Authentication, use it.
As J.D.'s comment says, storing credentials is not the safest option and even the performance disadvantage of Windows authentication was mitigated with SQL Server connection pooling as mentioned by Dan Guzman. He also added:

I tested 10K connection open/close requests in my test lab on bare
metal. The average milliseconds per connection were: SQL auth with
polling: 0.10838721, Windows auth with pooling: 0.12424151, SQL auth
with no pooling: 2.66011692, Windows auth without pooling: 3.2432628.
Consider that query execution rather than connections will likely be
the long pole in the tent.


Postgres streaming replication between hosts with different timezone

what will be the effect of this on fields of type "timestamp with timezone"?

They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will be no effect.

One notable exception to this is where the timestamp is truncated to a date. The timestamp may fall on a different day in one time zone to in another time zone. Truncation of a timestamp with time zone to a date discards time zone information. So be cautious with date_trunc, casts from timestamp with time zone to date, calls to extract or date_part, etc.

  what will be the effect of this on fields of type "timestamp without timezone"?

Nothing.

  what will happen to replication if we change the slave timezone?

Nothing. Replication is block level. It doesn't care about the timezone setting in the slightest.

This is just the same as changing TimeZone in the master with a SET TimeZone at the SQL level, or in the config file. So you can just try it with a standalone DB.

e.g.

test=> SHOW TimeZone;
    TimeZone
----------------
 Australia/West
(1 row)

test=> CREATE TABLE tztz(tstz timestamptz, ts timestamp);
CREATE TABLE
test=> INSERT INTO tztz(tstz, ts) values (current_timestamp, current_timestamp);
INSERT 0 1
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-02 20:48:56.664932+08 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

test=> SET TimeZone = UTC;
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-02 12:48:56.664932+00 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

test=> SET TimeZone = 'UTC-4';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-02 16:48:56.664932+04 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

Note that in both cases the timestamp is the same, because 16:48 at UTC+04 is the same as 12:48 at UTC or 20:48 at UTC+8 (Australia/West).

However, when the timezone shift causes the timestamp to be in the prior or next day in that time zone...:

test=> SET TimeZone = 'UTC-12';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts
-------------------------------+------------+----------------------------+------------
 2015-08-03 00:48:56.664932+12 | 2015-08-03 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

it's all fine with the timestamps still, but the timestamp with time zone truncated to date has moved to a different day.

If you're confused by the offsets seeming backwards that's because PostgreSQL respects the POSIX standard for time zone offsets which are, infuriatingly, backwards to the offsets most people use day to day.


Slow query that times-out my application, any advice?

The most likely case is either parameter sniffing or, less likely, bad statistics, with a very unlikely side of index split pages (thousands of rows is pretty small for that).
To prove parameter sniffing:

Turn on profiler or a trace for your particular Session_ID; watch Reads, Writes, CPU, Duration for events SQL:BatchCompleted and RPC:Completed

Invalidate the query cache for your particular query, which you can do by either DBCC FREEPROCCACHE  sql_handle
a. Note that if you do DBCC FREEPROCCACHE without any arguments, it frees the entire procedure cache, which has serious though short lived bad performance impacts on production boxes, since EVERY query plan now needs to be rebuilt.

Run your query for the most common set of parameters

Run your query for the most common set of parameters (again; ignore the results from #2, since they may include caching data)

Run your query for the least common, most unusual (index statistics wise) set of parameters.

Run your query for the least common, most unusual (index statistics wise) set of parameters (again; ignore the results from #4, since they may include caching data)

Do steps 1-5 again, EXCEPT do 1,2,5,6,3,4 - i.e. do the least common first, and the most common second

If your results on the second run of each set are significantly different on reads, writes, and/or CPU, you've proved parameter sniffing!  If not, you may still have parameter sniffing issues on other combinations, or you may not.

If it takes arguments that vary from division to division, it's probably parameter sniffing.  Read Erland Sommarskog's article Dynamic Search Conditions in T‑SQL which covers several ways of handling the various execution plans required.  WITH RECOMPILE is the quick band-aid, multiple stored procedures is the cadillac solution, and very careful dynamic SQL is another option (NEVER concatenating parameters into the SQL statement, only using them as actual parameters and as part of IF statements that put blocks of code in).
You can ALTER INDEX ALL on  REBUILD and it'll also update statistics, and see if that makes a difference.  Use ONLINE=ON if you're on Enterprise edition and need to, otherwise it's going to lock those tables.
Oh - make sure all tables have high cardinality clustered indexes first!


SQL Server 2017 Availability group not routing to secondary server

2 more things to be noted here while you are trying to connect to the Listener via the Management Studio for ReadOnly routing:

Make sure that you are also providing the database Name along with "ApplicationIntent=ReadOnly" in the Additional Connection parameters section of SSMS
SSMS sometimes has sticky connections so it could be possible that it is still connecting to an existing connection that was made via SSMS before providing the parameters "ApplicationIntent=ReadOnly". Try restarting the SSMS.


Deadlock on insert for a table with an insert trigger that updates column in same triggered table

I have to say that I don't quite agree with some of the comments that state that what you're seeing is necessarily a framework problem.  The Linq to SQL framework most certainly does allow you to specify a stored procedure as the means of input for rows.

That being said, I'd highly recommend you use that mechanism instead of a trigger if at all possible.  You'll be able to add your locking hints inside the stored procedure and your inserts will work fine.

Your spec, however seems highly suspicious to me, and IMO the problems you seem to be running into right now are good counterexamples as to why one might not want to pursue a design such as this.  So my (purely rhetorical) question is for the designer.  What is that problematic sequential number supposed to represent?  If it's a sequence in time, then why not just use a time and use the window functions to order the rows in something like a view instead (you can point Linq to SQL to views as well)?  Is the intent to have some sort of gapless sequential number?  Are you signed on to all of the complexity and locking you're going to have to deal with just to maintain that number?

Unfortunately, I think there is no real answer -- no magic combination of hints which will work across your frameworks and design -- which will solve your problem.  IMO my answer is take a step back and look critically at your design.


How to get around not being able to use Temp Tables in Functions?

Your best bet is to rewrite the function to remove the need for the temp table at all.  This will benefit you in other ways as your current row by row approach will not scale well.

The example below shows one way of getting the same results without using a temp table.  I had to make assumptions about the data types since you didn't include the function header.

DECLARE @EmpID INT
DECLARE @StartDate DATE
DECLARE @EndDate DATE

;WITH CTE_HR
    (
    SELECT HRM.*
        , IsRangeBetween = General.IsRangeBetweenDateRange(HRM.StartDate, HRM.EndDate, @StartDate, @EndDate)
    FROM Human_Resources.v_GetemployeeManning(@EmpID) AS HRM
    )
SELECT * FROM CTE_HR
WHERE IsRangeBetween = 1


You should never stop a vacuum process, ever.

A vacuum full will completely recreate the table, so it should take roughly as long to run as it would to create and populate a brand new table and index it with all 490_000_000 records.


Batch Requests/sec reported by DMV millions of times larger than Activity Monitor

From the documentation:

  Note: For per-second counters, this value is cumulative. The rate
  value must be calculated by sampling the value at discrete time
  intervals. The difference between any two successive sample values is
  equal to the rate for the time interval used.

If you'd like something that already does interval sampling, sp_BlitzFirst is pretty neat, and free, and full disclosure: I am a contributor, etc.


Move database to another drive in same server

It is easy and well documented to move data files (including master, model, tempdb,...) to other locations in MS-SQL Server. There is no supported approach to move the instalation (SQL Server binaries) to another drive / folder. I think even with manual manipulation of registry entries the SQL Server wouldn't come up again.

You should consider uninstall and new installation.

backup all databases including system databases as master, model ( SSISDB)
uninstall SQL Server using SQL Server installation center
uninstall SQL Server 2008 Browser component by using the Add or Remove Programs
install SQL Server with same instance name using SQL Server installation center; select new locations for binaries and data
restore backups

This article describes how to uninstall a stand-alone instance of Microsoft SQL Server 2008. When you follow the steps in this article, you also prepare the system so that you can reinstall SQL Server 2008


MongoDB merge two collections and drop documents with same value in field

There might be more elegent solutions for this, but let me answer how I merged a new collection into an original collection without importing the duplicates.

Compare userid fields in both collections and take the difference.
Use mongos $out operator to save documents to a difference collection. This new collection has all documents of the new collection, except of the duplicates.
mongodump the difference collection and mongorestore it into the original collection.

from pymongo import MongoClient
import subprocess

# collection names
db_name = 'db'
col_original = 'col_original'
col_new = 'col_new'
field = 'userid'

# Get ids from collection
def get_ids(db, col):
    docs = db[col].aggregate([
        {'$project':
         {'_id': 0,
          'id': '$' + field}}
    ])
    docs = list(docs)
    ids = [x['id'] for x in docs]
    return ids

# Connect to MongoDB
client = MongoClient('mongodb://localhost')
db = client[db_name]

# Get difference in ids
ids_new = get_ids(db, col_new)
ids_original = get_ids(db, col_original)
ids_diff = list(set(ids_new).difference(ids_original))

# Get all documents with userid that are in col_new, but not in
# col_original. Hence, all duplicates are skipped.
db[col_new].aggregate([
    {'$match': {
        field: {'$in': ids_diff}}},
    {'$out': 'col_diff'}])

# Use mongodump to save col_diff
subprocess.check_output(['mongodump',
                         '-d',
                         db_name,
                         '-c',
                         'col_diff'])

# Merge col_diff into col_original
subprocess.check_output(['mongorestore',
                         '-d',
                         db_name,
                         '-c',
                         col_original,
                         'dump/' + db_name + '/col_diff.bson'])


join to other table multiplied by loop foreign key in mysql

The issue of the amounts that are multiplied is easy to solve. You just have to use a derived table that does the calculation (group by) first and then join that (derived table) to the other one, that stores the hierarchical structure.

The derived table to use:

( SELECT userid,
         SUM(amount) AS sele_descendant_amount
  FROM webineh_user_buys
  GROUP BY userid
  HAVING SUM(amount) >= 1000
) AS buys

Then we can join it to the other table. You also need to join it twice and that's another issue that may lead to a less efficient query. Unfortunately MySQL doesn't yet have CTEs, so the derived table code has to be duplicated. But you'll get at least a start with this code.

Tested at SQLfiddle:

SELECT
    buys_d.userid,
    buys_d.sele_descendant_amount,
    COUNT(*) AS level
FROM
    ( SELECT userid,
             SUM(amount) AS sele_descendant_amount
      FROM webineh_user_buys
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_d
  JOIN
    webineh_prefix_nodes_paths AS users
      ON  users.descendant_id = buys_d.userid
  JOIN
    ( SELECT userid,
             SUM(amount) AS sele_descendant_amount
      FROM webineh_user_buys
      GROUP BY userid
      HAVING SUM(amount) >= 1000
    ) AS buys_a
      ON  users.ancestor_id = buys_a.userid
GROUP BY
    buys_d.userid, buys_d.sele_descendant_amount ;


In SQL Server 2008, is possible to one database subscribe 2 publications for the same database? (merge replication)

What you are asking is not possible.  Do not use SQL replication for high availability.  It is an exotic and soon-to-be deprecated feature set.


Read inside node value of Xml using Sql Server

CREATE TABLE test(data xml);
insert into test values
('
<ROOT>
  <Customer>
    <Order>Order 1</Order>
  </Customer>
  <Customer>
    <Order>Order 2</Order>
  </Customer>
</ROOT>');
GO

1 rows affected

If you need first <Order> element of your XML data you can use the next sentence.

SELECT data.value('(ROOT/Customer/Order)[1]', 'varchar(100)')
FROM   test;
GO

| (No column name) |
| :--------------- |
| Order 1          |

If you need to select all <Order> elements you can use a CROSS APPLY solution with .nodes()

SELECT T2.lin.value('(Order)[1]', 'varchar(100)')
FROM   test
CROSS APPLY data.nodes('/ROOT/Customer') as T2(lin)
GO

| (No column name) |
| :--------------- |
| Order 1          |
| Order 2          |

dbfiddle here


Sort Warnings, finding out the query behind them

You can tweak your query slightly to get a count of 'SpillToTempDb' i.e. Sort warnings, which would show you if any of the queries you're looking at could be an issue.

WITH
XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
p.name,
p.object_id,
p.create_date,
p.modify_date,
s.last_elapsed_time,
s.last_worker_time,
(s.last_elapsed_time - s.last_worker_time) as time_difference,
s.execution_count,
last_execution_time,
Query_Plan.value('count(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/Warnings/SpillToTempDb)', 'int') TempDbSpillWarnings
FROM SYS.dm_exec_procedure_stats as s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS deqp
INNER JOIN sys.procedures as p on p.object_id = s.object_id
where database_id = 11 and s.last_worker_time < s.last_elapsed_time
order by time_difference DESC

Otherwise, since you are on SQL Server 2012, I would recommend running an Extended Event for an hour or so. There's a good guide on doing so half way down this tutorial.


Creating Stored Procedure in local db that references tables in linked server

You can't parameterize entity names into a T-SQL statement. In order to do this you need to (a) create the #temp table first, and (b) use dynamic SQL. Here is one approach:

CREATE PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @exec nvarchar(256),
    @linkedserv nvarchar(150),
    @linkeddb   nvarchar(150),
    @sql        nvarchar(max);

  SELECT @linkedserv = N'HOSTNAME\SERVER',
         @linkeddb   = N'remotedb';

  SELECT @exec = QUOTENAME(@linkedserv) + N'.'
               + QUOTENAME(@linkeddb) + N'.sys.sp_executesql';

  CREATE TABLE #TempTable([Options] varchar(64));

  SELECT @sql = N'SELECT (CASE LTRIM(RTRIM(ot.[COLUMN1]))
        WHEN ''''     THEN ''''
        WHEN ''THIS'' THEN ''THAT''
        WHEN ''NOW''  THEN ''NEVER''
        ELSE ''OTHER'' END) AS [Options]
    FROM dbo.TableOnLinkedServer AS t
    FULL OUTER JOIN dbo.OtherTableOnLinkedServer AS ot
      ON t.COLUMN1 = ot.COLUMN0;';

  INSERT #TempTable([Options]) EXEC @exec @sql;

  SELECT * FROM #TempTable;
END
GO

You could do it all inside dynamic SQL, so if knowing the columns up front is a challenge, there is a way. But it's messy, assuming you actually need a #temp table, because then everything you do with #TempTable has to be done inside the dynamic SQL.

CREATE PROCEDURE [dbo].[mystoredprocedure]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @exec nvarchar(256),
    @linkedserv nvarchar(150),
    @linkeddb   nvarchar(150),
    @sql        nvarchar(max);

  SELECT @linkedserv = N'HOSTNAME\SERVER',
         @linkeddb   = N'remotedb';

  SELECT @exec = QUOTENAME(@linkedserv) + N'.'
               + QUOTENAME(@linkeddb) + N'.sys.sp_executesql';

  SELECT @sql = N'SELECT (CASE LTRIM(RTRIM(ot.[COLUMN1]))
        WHEN ''''     THEN ''''
        WHEN ''THIS'' THEN ''THAT''
        WHEN ''NOW''  THEN ''NEVER''
        ELSE ''OTHER'' END) AS [Options]
    INTO #TempTable
    FROM dbo.TableOnLinkedServer AS t
    FULL OUTER JOIN dbo.OtherTableOnLinkedServer AS ot
      ON t.COLUMN1 = ot.COLUMN0;

  SELECT * FROM #TempTable;';

  EXEC @exec @sql;
END
GO

Please look at the obligatory Erland Sommarskog article on dynamic T-SQL to ensure you don't end up creating an embarrassing SQL Injection vulnerability.