Monitor and alerting tool for specific queries

Nagios is an open source solution for Unix/Linux based systems.  It operates by scripts so it can do what you are requesting and more.  You can write your own script tailored to your specific alert (and you can set the warning and critical criteria).

Here is a link to MySQL specific plugins freely available for your use:
https://exchange.nagios.org/directory/Plugins/Databases/MySQL

And a link to other similar opensource alerting products:
https://serverfault.com/questions/83963/alternatives-to-nagios


Query uses Primary Key Index instead of Nonclustered index defined

The table only has 2 rows. The cost difference between a table scan and an NC seek is negligible. SQL Server is pretty good about finding the cheapest way to getting things done. I'd expect that when the table gets bigger, the NC index will start getting used.

To compare performance, you can force the index to be used with an index hint:

SELECT lop.RecordID ProspectusID,
    c.c_code CourseCode,
    c.c_desc CourseDescription,
    COALESCE(c.c_major, '') MajorName,
    lop.EffectiveYear EffectiveSchoolYear,
    COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount
FROM dbo.ListOfProspectus AS lop
    INNER JOIN dbo.courselist AS c ON lop.CourseCode = c.c_code
    LEFT JOIN dbo.ProspectusContent AS pc WITH (INDEX(idx_ProspectusContent_prospectusid)) ON lop.RecordID = pc.ProspectusID
WHERE lop.CourseCode IN ('BSIT')
GROUP BY lop.RecordID, c.c_code, c.c_desc, c.c_major, lop.EffectiveYear
ORDER BY CourseCode, EffectiveSchoolYear

In production, I would use the hint as a last resort though. Given proper indexes and statistics, the query optimizer can typically build the cheapest plan on its' own.

Read more about hints: http://technet.microsoft.com/en-us/library/ms181714.aspx


Why would increase in innodb_buffer_pool_size slow down MySQL?

From what you gave me thus far

The CPU has the illustrious task of accessing all 32GB RAM.
InnoDB's Buffer Pool is 8GB (524288 * 16384)

524288 (Innodb_buffer_pool_pages_total)
16384 (Innodb_page_size))

InnoDB Architecture

CONJECTURE #1

Since InnoDB allocates its buffer pool contiguously, one could only imagine if there is any form of fragmentation of data and index pages among other things in the Buffer Pool. What other things? There are also updates for secondary non-unique indexes posted first inside the Buffer Pool before they are merged into the System Tablespace file (better known as ibdata1). Please notice the Insert Buffer within the InnoDB Architecture.

CONJECTURE #2

While a larger buffer pool can reduce disk I/O, a buffer pool that's too large may have issues, such as the fragmentation issue I just mentioned.

Perhaps sparse memory, though contiguous, is not a good thing either. I discussed something of that nature back on Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

I noticed the miscellaneous pages (Innodb_buffer_pool_pages_misc) within the buffer pool. That takes up 235 MB (16384 * 15022). Just some overhead for indexing and row locks. Notwithstanding, this can be victimized by a large buffer pool with lots of sizable empty gaps.

CONJECTURE #3

Data and index pages are not the only things that occupy the Buffer Pool. An annoying neighbor they must share space with is the Insert Buffer. That section of the Buffer Pool contains the changes for all non-unique indexes. Depending on the number of indexes each InnoDB table has, up to half of the Buffer Pool could be used. This should be a good reminder to look over all InnoDB table and remove redundant indexes

Here are other posts that describes how to hunt down redundant indexes and why to get rid of them:

Jan 26, 2012 : MySql - find redundant indexes
Dec 10, 2011 : Do I have duplicate key indexes?
Sep 07, 2011 : MySQL CPU Usage
May 17, 2011 : Do I need to add a new single column index to a table if a multi-column index on that field already exists?

Think about it: If a table has redundant indexes, INSERTs and UPDATEs to a table will results in rebalancing tree pages 45% of of the time (in the worst case scenario). Such tree rebalancing with happen multiple times for a table with a lot of indexes. Removing redundant indexes cuts down the time spent writing those changes to the Insert Buffer and in turn, to the Index Pages of each InnoDB table.

Increasing the Buffer Pool Size leaves room for the Insert Buffer to run amok if you have InnoDB tables with too many indexes, especially redundant indexes. Another telltale sign that you need to look for such tables is to run this query:

SELECT table_schema,table_name,data_length,index_length
FROM information_schema.tables WHERE engine='InnoDB'
AND data_length/(data_length+index_length) < 0.8;

This will give you a general idea which tables need to have the indexes looked over for redundancy of leading columns.

EPILOGUE

Keep in mind that the Buffer Pool holds

Data Pages
Index Pages
Insert Buffer for Index Changes
Adaptive Hash Index
Opened tables info
Miscellaneous Overhead
Refer to the InnoDB Architecture

Wasted space in a Buffer Pool could take more time to sift through, especially with a single core that is solely responsible for all of RAM.

My advice would be to keep the innodb_buffer_pool_size at 10G.


With SSMS on my local machine, is it possible to login to a remote SQL server on my VPN network via my Active Directory credentials?

As I said in my comment, I do not fully understand what your issues are.

But I think the solution is to open SSMS as another user.

Personally I have the SSMS icon in my task bar:.

Shit + R click
Select "Run as a different user"
Enter the account and password you would normally use on your office machine.

SSMS will connect to instances as if you where on your office machine and had opened SSMS.

Note I assume you are working remote for possibly the first time.  There are a number of security barriers, that your organization may have in place to prevent you from connecting from home.  I can't address those, and this is probably not the site to address them.


How do I set up privileges for different schemas?

You can always have root as user t access when all fails, so don't touch the user and give a string password.
Besides that you can always recover whwn you log in as su or administrator see manual
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hp. * TO 'dba '@'localhost';
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hpc. * TO 'dba '@'localhost';
CREATE USER 'hp_dbo '@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hp. * TO 'hp_dbo'@'localhost';
CREATE USER 'hp_application_user  '@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT,CREATE,DROP,EXECUTE  ON hp. * TO 'hp_application_user '@'localhost';
CREATE USER 'hpc_dbo '@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON hpc.* TO 'hpc_dbo'@'localhost';
CREATE USER 'hpc_application_user'@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT,CREATE,DROP,EXECUTE  ON hp. * TO 'hpc_application_user`@'localhost';

If you need more privileges for the user hpc_application_user or hpc_application_user
you can use the same command with the privilege you want to add or revoke
If you have multiple user with the same privileges you can define a role


calculating % difference for every row with the same ID

I wrote out an answer that works in SQL Server before noticing you have this question tagged as MySQL.  For context, if you weren't in MySQL you'd use the LAG function like this:

SELECT
    s.P_date,
    s.Company,
    s.Price,
    s.Price/s.Last_price as Percent_diff
FROM
(   SELECT
        t.P_date,
        t.Company,
        t.Price,
        LAG(t.Price) OVER (PARTITION BY t.Company ORDER BY t.Company, t.P_Date)  Last_price
    FROM YourTable as t
) as s

The LAG line selects the Price value from the last record for the same company, allowing the outer query to do the percent calculation.

But MySQL doesn't support LAG.  Luckily there's an answer on Stack Overflow that shows how to simulate LAG in MySQL.  It even uses an example table almost identical to yours:

https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql

I suggest you do this as a view, not a table.  That way the calculation will only run when you select from the view, leaving you free to update your main table without worrying about those million rows bogging you down.


Upgrading from MySQL 5.1.37 to MySQL 5.1.73

I upgraded an old server from 5.1.34sp1 to 5.1.67 some time back... that was about the same amount of "jump" in minor versions as you are contemplating.  In this case, the system worked without incident.
Within the same major release of MySQL (e.g. 5.1.x to 5.1.y), assuming the versions involved are after the GA milestone, upgrading should be relatively straightforward.

For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture.
— http://dev.mysql.com/doc/refman/5.1/en/upgrading.html

Nevertheless, a complete backup is essential.
Importantly, if you haven't tried restoring your backup on a different server, then for all practical purposes, you don't actually have a backup.
Giving you the "ssh commands" (actually, shell commands) to do the upgrade isn't possible, since there are variations in installation that make it impossible to provide a list of "type these things and it will work" commands.  Assuming your current installation is using an Oracle tarball binary distribution, you'll need to download and extract the binaries into a new directory, stop the server, move or relink the data files where the new copy of MySQL can see them, and rename a couple of directories or change a couple of symlinks.  There's not really a concept of "uninstalling" the old version, since the old version can live harmlessly on your server in a different directory.  You just move it out of the way, and if the upgraded version doesn't start up properly, will often still be able to start the old version back up, if needed.  Eventually, you can remove the old directory, with caution, to avoid removing the wrong things.
You'll need to read the documentation.
http://dev.mysql.com/doc/refman/5.1/en/upgrading.html
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
After you do the upgrade, don't forget to run the mysql_upgrade utility.  This utility does not upgrade the version of your server; instead, it upgrades your data files to be fully compatible with the new version you've just installed.  You run this after you do the upgrade, and after you restart the MySQL server daemon.
http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html
Depending on how well the server was maintained in the past, if it was upgraded in the past, then it's possible that this was not done when it was upgraded previously.  If so, it would actually be a good idea to run it before doing the upgrade, so that all of your structures are all consistent.  Two important points, here:  The version of the mysql_upgrade utility that you run needs to be the version bundled with the currently running version of the server, and you need to make your backup before you run this.  If you have some serious latent structural corruption that can't be automatically fixed, this can be theoretically be uncovered by mysql_upgrade and the server may shut down to prevent the detected corruption from causing even more serious problems.  This is because mysql_upgrade uses CHECK TABLE as part of its work, and:

If CHECK TABLE finds a problem for an InnoDB table, the server may shut down to prevent error propagation. Details of the error will be written to the error log.
— http://dev.mysql.com/doc/refman/5.1/en/check-table.html

This is not a likely scenario, but it's a documented possibility of which you need to be aware.
As a general rule, the slave server should be upgraded before the master, not after.  This is somewhat less critical when remaining within 5.1 (if you were upgrading to 5.5, you absolutely need to upgrade the slaves first) but upgrading the master last is most definitely the most correct approach.
Upgrading the slave first will also give you a better idea of what to expect, and if the slave's data is consistent with the master, you can always promote it to master if your upgrade of the actual master fails.


If the intention is to build a relational database, then the objective should be to reflect the informational characteristics of the relevant business environment in the database layout, so the short answer is:
A database designer must define one or more composite KEYs whenever each row of the table under consideration has to be uniquely differentiated by the values of one or more combinations of columns.
In this way, a database designer is responsible for (1) the identification of that business context requirement and for (2) the declaration of the corresponding multi-column KEY constraint(s).
As you know, it is his or her duty to (i) identify and (ii) declare single-column KEYs too, whenever necessary.
Therefore, the general procedure should be to always analyze very carefully each particular situation, inspecting the meaning ascribed to every individual table and column, taking into account all their interconnections regarding the entire database layout.
Extra column for system-controlled surrogates
By analyzing very carefully each particular situation, one can as well determine the quite specific cases when, apart from the declaration of one or more single- or multi-column natural KEYs (as demanded), a table may benefit from the inclusion of an extra column to retain system-controlled surrogatesa (surrogates for brevity, which are implemented, e.g., via an IDENTITY property in Microsoft SQL Server or an AUTO_INCREMENT attribute in MySQL), as such an artefact is at all times an attached element that entails supplementary administration tasks (e.g., it regularly needs a accompanying INDEX at the physical level, with all its implications).
The point brought up above is pertinent because, according to my interpretation of the following question excerpt “I usually use a primary key (usually INT AI id)”, what you are describing is, precisely, a column that retains surrogates.
In this respect, since optimizing the usage of the database platform resources (the database management system, the hardware, etc.) is paramount, it must be mentioned that a database practitioner should employ any kind of technical additions only when it is justified by the features of the business context represented in the database of significance.
A column holding surrogates that somewhat work as “substitutes” for the respective natural KEY values can sometimes be of help when, e.g., it is utilized in tables that:

Represent entity types that are at the top layer of the relevant conceptual data hierarchy.
Have natural KEYs that are wide and/or heavy, so it may be useful to prevent referencing them from other tables via FOREIGN KEY (FK) constraints.

In this manner, FK references are made to a column containing values that are physically lighter in terms of bytes (when compared with references made to a relatively large combination of columns and/or references made to columns set up with heavy types and sizes) which has impacts on, e.g., disk space and memory consumption due to the reduction of the physical-level scaffoldings that support the concerning logical-level definitions.
General advantages of multi-column keys
Data manipulation
In cases of tables that stand for (conceptual) entity types that belong in the lower layers of the data hierarchy of the business context of interest, composite PRIMARY KEYs (PKs) tend to be more effective since they commonly are called for in (logical) SELECT operations that include columns that point to PKs of tables at higher layers of the aforementioned hierarchy, while columns with surrogate values are typically not involved.
Along these lines, the use of composite PKs is generally convenient to avoid needless JOINs (which does not imply in any way that the JOIN operator is a bad tool, quite the opposite, it is an essential, elegant and powerful instrument with regard to data derivation that functions very fast in the right settings), and in agreement with the optimization of platform resources previously brought up, one should employ and supply mechanisms for data manipulation that are as efficient as possible.
For instance, with a hypothetical logical-level SQL-DDL layout like the one that follows:
--
-- Let us suposse that we are building a database in which:

-- a. A row of Foo may be associated with zero-one-or-many rows of Bar.

-- b. A row of Bar can be associated with zero-one-or-many rows of Baz.

-- c. A row of Foo is identified by the values of its own columns
--    (Qux, Corge, Grault, Garply, Etcetera) alone.

-- d. A row of Bar is identified by its BarDateTime value of creation along with
--    the FooId value of the Foo row with which it is associated.

-- e. A row of Baz is identified by its BazNumber value along with
--    the FooId and BarDateTime values of the Bar row with which it is associated.

-- 1. The representation of the ‘top layer’
--    of the hypothetical conceptual data hierarchy:
CREATE TABLE Foo (
    FooId       INT         NOT NULL, -- Column for surrogates.
    Qux         CHAR(60)    NOT NULL,
    Corge       CHAR(80)    NOT NULL,
    Grault      VARCHAR(90) NOT NULL,
    Garply      CHAR(75)    NOT NULL,
    Etcetera    CHAR(48)    NOT NULL,
    CreatedDate DATE        NOT NULL,
    --
    CONSTRAINT Foo_PK PRIMARY KEY (FooId),
    CONSTRAINT Foo_AK UNIQUE      (Qux, Corge, Grault, Garply, Etcetera) -- ‘Voluminous’ composite ALTERNATE KEY.
);

-- 2. Conveying the ‘middle layer’ of the
--    conceptual data hierarchy:
CREATE TABLE Bar (
    FooId       INT      NOT NULL,
    BarDateTime DATETIME NOT NULL,
    Waldo       CHAR(30) NOT NULL,
    Etcetera    CHAR(62) NOT NULL,
    --
    CONSTRAINT Bar_PK        PRIMARY KEY (FooId, BarDateTime), -- Composite PK.
    CONSTRAINT Bar_to_Foo_FK FOREIGN KEY (FooId)
    REFERENCES Foo (FooId)
);

-- 3. Portraying the ‘bottom layer’
--    of the conceptual data hierarchy.
CREATE TABLE Baz (
    FooId       INT      NOT NULL, -- This component of the PK provides ease of ‘navigation’.
    BarDateTime DATETIME NOT NULL, -- This component of the PK also provides ease of ‘navigation’.
    BazNumber   INT      NOT NULL,
    Fred        CHAR(28) NOT NULL,
    Etcetera    CHAR(57) NOT NULL,
    CreatedDate DATE     NOT NULL,
    --
    CONSTRAINT Baz_PK        PRIMARY KEY (FooId, BarDateTime, BazNumber), -- Meaningful composite PK.
    CONSTRAINT Baz_to_Bar_FK FOREIGN KEY (FooId, BarDateTime)             -- Composite FK.
    REFERENCES Bar (FooId, BarDateTime)
);

--

…it is possible to JOIN (a) columns FROM table Foo with (b) columns FROM table Baz without having to “pass” first “through” (c) table Bar, making use of Foo.FooId and Baz.FooId as the only necessary conditions. To this extent, when one can save one or more “manoeuvres” in a certain data manipulation operation, more platform resources will be available for other concurrent processes.
In this series of posts you can see a more “concrete” example of a conceptual schema and its respective logical representation where the configuration of composite PKs offers multiple advantages.
Tendencies
Nevertheless, these are tendencies, hence, as noted before, you should always inspect every exact case (considering its effects on the overall scenario) to define whether or not appending a column for surrogates is beneficial.
Data constraints
On the other hand, there is a quite relevant detail that you refer to in the paragraph cited below:

When creating a database structure, I tend to create unique composite keys for every group of data that need to be unique. Beside them I usually use a primary key (usually INT AI id), unless a composite key is really enough to identify the records.

It is really important to take that detail into account because a surrogate value does not carry business context meaning, thus it does not uniquely identify the real data elements of a row, and that aspect is exclusively guaranteed, at all times, by meaningful natural (be it single- or multi-column, either PRIMARY or ALTERNATE) KEY values.
Apart from that, it is as well opportune to remember that composite KEYs, working in conjunction with FKs, are of great value to establish declarative restrictions that ensure that the pertinent rows comply with the cardinality ratios of the conceptual relationships/associations that exist between the entity types represented by the tables of a database.
As you are well aware, the task of constraining the data properly is of enormous significance in the field of database administration, for it ensures that the information managed is consistent with its conceptualization in the real world (i.e., the applicable business environment). In this manner, having consistent data, the involved manipulation operations yield reliable results, and the end users of a database (and/or one or more application programms sharing access to it) can use it as a valuable instrument to make well-informed decisions.
Relevant scenario
Some of these topics and other logical, physical and practical factors are discussed in my answer to the question entitled

“Third Normal Form: Composite PRIMARY KEY vs System-Assigned Surrogate (IDENTITY)”,

so you might find those posts of help.
Terminology
As for terminology concerns, you may also find of interest my deliberations contained in the meta post entitled

“Issues about the [unique-key] (sic) tag and its synonyms”.

Endnote
a System-controlled surrogates (i.e., those generated and assigned by the database management system) were assessed in the 1979 paper named Extending the Database Relational Model to Capture More Meaning by Dr. Edgar Frank Codd, the creator of the relational paradigm. In such paper, it is prescribed that, among other points, system-controlled surrogate values must never be displayed to database users, although in most databases I have observed, that rule is not complied with (perhaps it should be enforced by the database management system itself, automatically).
 This answer is based on the supposition that, in the scenario at hand, system-controlled surrogates are shown just like normal business context values. It is worth to point out that system-controlled surrogates are not part of the original relational model, as published in the 1970 paper entitled A Relational Model of Data for Large Shared Data Banks, also written by Dr. Codd, of course.


All SQL Server aliases linked to default instance instead of instance I assigned

To correctly connect to SQL Server instance the network library has to know 2 things: IP address and port number. These two are necessary and sufficient.

The "name" of a named instance make no difference to the library, it was invented when named instances were introduced and it is used just to determine the port number.

When the port number is missing in the connection string, the network library asks for it SQL Server Browser (on UDP 1434) sending it the instance name. As the responce it receives port number.

When there is port number in the connection string, instance name is just ignored.

You can test it by yourself by changing instance name to bla-bla-bla:
if you create an alias or just type in SSMS connection dialog .\bla-bla-bla,1433
you'll be connected to the default instance.

Vice versa, if you omit instance name of the named instance but indicate the correct port where your  named instance is listening on, for example .,8852 where 8852 is the port of named instance you'll be connected to this named instance even if it's name was not specified.


Running Oracle Export Utility within a Shell script

Try changing the tables parameter to this.
You only need parentheses when you have more than one table.

TABLES='SYSTEM_PARAMETERS' \

$ exp tables=system.plan_table file=plan_table.dmp statistics=none

Export: Release 11.2.0.2.0 - Production on Tue Feb 17 08:59:05 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     PLAN_TABLE          4 rows exported
Export terminated successfully without warnings.


How to change Oracle user password without showing the actual password on screen?

You can do this with the SQL*Plus password command.

To change the current user's password, just type password and you'll get the usual prompt.

$ sqlplus foo/foo123
...
SQL> password
Changing password for FOO
Old password:
New password:
Retype new password:
Password changed

You can also use password username to change another user's password (if you're logged on with sufficient privileges - you need to be dba I believe).

SQL Developer also allows you to do that. Just type password and execute, and you'll get a password change popup. Or expand the "Other users" branch, right-click on the account that needs a change and select the "Edit user" menu.


SQL: get product stock of all product types in a single query

You have effectively have these 3 queries:

simple

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN product_stocks AS s
  ON p.product_id = s.product_id AND
     p.type = 'simple'
GROUP BY p.product_id

complex 1:

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN complex1_childs AS c
  ON p.product_id = c.parent_id AND
     p.type = 'complex_1'
JOIN products AS cp
  ON c.child_id = cp.product_id
JOIN product_stocks AS s
  ON cp.product_id = s.product_id
GROUP BY p.product_id

complex 2 is the same as above changing the table and the p.type join criteria.

You can UNION these together into a single query.


SQL to Find Partial Duplicates of a Field

You can identify the repeated 1->8 substrings using this:

SELECT LEFT(basic.MEMBNO,8)
FROM dbo.basic
GROUP BY LEFT(basic.MEMBNO,8)
HAVING COUNT(*) > 1;

So you can join with that to get the individual values:

;WITH x(m) AS
(
  SELECT LEFT(basic.MEMBNO,8)
  FROM dbo.basic
  GROUP BY LEFT(basic.MEMBNO,8)
  HAVING COUNT(*) > 1
)
SELECT b.MEMBNO
  FROM dbo.basic AS b
  INNER JOIN x
  ON x.m = LEFT(b.MEMBNO, 8)
  ORDER BY b.MEMBNO;

You can also do it this way (I just wasn't sure when windowed COUNT was introduced):

;WITH x AS
(
  SELECT MEMBNO, c = COUNT(*) OVER (PARTITION BY LEFT(MEMBNO, 8))
  FROM dbo.basic
)
SELECT MEMBNO FROM x WHERE c > 1
ORDER BY MEMBNO;


How to find out what caused the database(DB2) in a locking state

List the current database activities:

db2top -d [dbname]

This command will show you database locks:

db2pd -d [dbname] -lock wait


Insert Into Existing Table Using OpenQuery

Provided that AURORA is a linked server, maybe you could simplify the operation by eliminating the OPENQUERY construct?

INSERT INTO AURORA.testdb.some_schema.address_info (firstname, lastname, address, city, state)
VALUES ('jo', 'bob', '11111 no where drive', 'city1', 'state1');

You might have to set/change some of the connection options on the linked server for this to work properly.

Note that you seem to be missing the schema name or the database name; when you're referencing a linked server, the four-part naming is server.database.schema.object, where you can choose to leave the schema blank to use the default schema (like dbo).


Is there any correct way to convert file system from ext4 to xfs in centos?

create partition where enough space, format it to xfs
mount that partition to some directory
stop mongod process
rsync -aP src dst files from original mongod dbpath to this xfs mount
unmount original dbpath partition mount AND this xfs partition
mount xfs partition to path where mongod dbpath points
start mongod process

So, basically when mongod process is stopped, copy all dbpath files to new location/partition and then point mongod process to use that location...


what are the implications of setting up a subscriber as a publisher?

This is known as the republishing model.  It is usually used for balancing distribution loads over slow or expensive links although I have seen it used in the scenario you described as well.  The republishing topology you propose is supported which can be found near the bottom of the page Republish Data, Transactional publication -> Transactional subscription/transactional publication -> Transactional subscription.

If I recall correctly, the challenge with this topology occurs if you ever have to reinitialize COPY_DB, you will also have to reinitialize NEW_DB.


Updating column having index causing Deadlock

What would be the best way to resolve the issue

You should create a compound index (unique if appropriate) on (pgmNumber, state). The index only on state is useless because of the low cardinality of that domain.

Generally only one index can be used for executing statements like yours, and in your scenario: "Both state and pgmNumber columns has individual index" it causes locks on more rows than necessary, thus leading to deadlocks.


Refer to https://mariadb.com/kb/en/user-statistics/
An optional feature of MariaDB collects statistics on usage of indexes (and also tables, users, and clients).
If you enable the userstat plugin (see the documentation link), it records a counter of the rows that were read via each given index. If that number remains 0 for a given index, then it was not used by any query.
Keep in mind these counters are reset to 0 when the MariaDB server restarts, so you don't know if the index was used prior to that.


How do I grant all privileges to local db2admin with DB2 9.7 on Windows?

Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.

So I did the following:

Opened the DB2 GUI (was easiest to do this way).
Connected to the desired database as db2admin.
Added my logon to the database as a user.
GRANT all authorities to that id (my id).
Disconnect.
Connect to the desired database as my id.
GRANT all authorities to db2admin.
Disconnect.

Voila! The db2admin logon now as all authorities.

  EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the
  DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the
  instance owner (in my case the db2admin id) have all the authority
  needed to interact with the database. I could have actually just
  commented out those grant lines above in the script. Those were left
  over from a script which ran against an older version of DB2. I have
  also found if I need to have the instance owner DBADM after doing a
  restore to database A from database B, it is easiest just to set the
  registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available
  in Fix Pack 2 and above). Then I don't have to try to grant instance
  owner DBADM. It automatically is granted that to any database restored
  into the instance. If you are not at Fix Pack 5, you have to bounce
  the instance for this to take affect.


Can't get databases or its objects with powershell

I found the answer in serverfault:

https://serverfault.com/questions/355014/sql-server-powershell-cannot-find-path-error

I have to run a script to properly load the Snapins in PowerShell:

#
# Add the SQL Server provider.
#

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Set mandatory variables for the SQL Server rovider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

Once loaded I can list every object in that database instance


pt-heartbeat showing "Host is blocked because of many connection errors"...but connections are well under limit

If 'host_name' is blocked because of many connection errors.
then to Unblock only options mysqladmin flush-hosts

unblock documentation link


Compare possible null values in one query

Your 2nd query seems correct and does what you want.

Another option would be to use the TOP .. WITH TIES but this works only because NULL values are treated as the lowest in SQL Server.

SELECT TOP (1) WITH TIES t.*
FROM cte AS t
ORDER BY t.NodeId DESC ;

If you had MIN() instead of MAX() and you tried the TIES with ASC instead of DESC, the above would fail miserably.


Declarative partitioning with Postgres for integer column

It should be done manually.  From the doc page (already mentioned in the question)
 - https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) -
under

  5.10.2.1 Example  To use declarative partitioning in this case, use the following steps: ....

  Create partitions. Each partition's definition must specify the bounds that correspond to the partitioning method and partition key of
  the parent. Note that specifying bounds such that the new partition's
  values will overlap with those in one or more existing partitions will
  cause an error. Inserting data into the parent table that does not map
  to one of the existing partitions will cause an error; an appropriate
  partition must be added manually."


The error message in the log was a red herring, and due to the configuration for Unix sockets (when I only needed TCP). Disabling Unix sockets by setting the unix_socket_dir to an empty string (as per the documentation) made those errors disappear from the logs.

The connection problem was because I needed to specify the password in the pgbouncer.ini file.

The line:

* = host=myremoteIP port=5432 user=postgres

Needed to be:

* = host=myremoteIP port=5432 user=postgres password=mypassword

I can now connect using psql.


Can jsonb do fast search by *value* or even a partial value? Other types?

I suggest a MATERIALIZED VIEW with unnested values and a trigram index as search tools.

hstore

If you don't need nested values, hstore may be even better for you. Use the function svals(hstore) to unnest hstore values.

You need to install the additional module hstore once per database:

CREATE EXTENSION hstore;

Key value pair in PostgreSQL

Table

CREATE TABLE person AS
SELECT * FROM (
   VALUES
     (1, 'Joe Doe', hstore  'toy=>car, color=>red')
   , (2, 'Jane Doe', 'food=>hamburguer, color=>blue')
   ) t(person_id, name, preferences);

Materialized view

CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference        -- just pref
FROM   person p, svals(p.preferences) j(preference);

This is an implicit CROSS JOIN LATERAL to the set-returning function svals().

Trigram Index

You need to install the additional module pg_trgm once per database:

CREATE EXTENSION pg_trgm;

Then:

CREATE INDEX person_pref_j_trgm_idx ON person_pref_j
USING gin (preference gin_trgm_ops);

Details:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Query

SELECT *
FROM   person p
WHERE  EXISTS (
   SELECT 1
   FROM   person_pref pp
   WHERE  pp.person_id = p.person_id
   AND    pp.preference ILIKE '%burg%'
   );

Be aware that this is pretty fast.

jsonb

If you have nested values or numeric or boolean values, jsonb may be more efficient. You can do the same as above with jsonb_each_text(jsonb):

CREATE TABLE person AS
SELECT * FROM (
   VALUES
     (1, 'Joe Doe', jsonb  '{"toy": "car", "color": "red"}')
   , (2, 'Jane Doe', '{"food": "hamburguer", "color": "blue"}')
   ) t(person_id, name, preferences);

CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.key, j.preference   -- incl. key or just pref?
FROM  person_j p, jsonb_each_text(p.preferences) j(key, preference);

Same index, same query. You might want to add the key to the MV and search for that, too:

CREATE INDEX person_pref_trgm_idx ON person_pref_j
USING GIN (key gin_trgm_ops, preference gin_trgm_ops);


Does an excessive table violate normalization rules?

By changing to your proposed solution you lose information from the database. The existing solution says what clubs can exist in a particular school irrespective of anyone actually being in that club at any point in time. The proposed solution requires someone to join the club before the club comes into existence (i.e. before a row is written to the database).

As a practical implication think of sign-up sheets. It's the day before term starts. The principal wants a sign-up sheet on the noticeboard for each club so students can join. It would be wasteful to print a sheet for all club types and let students join clubs which will never exists in this school. Today, before term starts, there are no students so your proposed solution will not work. The existing solution, however, allows the principal to offer, say, a soccer club but not a water polo club.

I realise you're using the analogy of school/ club as a proxy for your real situation, and you're going to have to translate all I say to the actual problem and that the comments I'm about to make may not apply. That's the price you pay for analogies. If your actual "school" can choose from all "club type" all the time then your proposed solution is adequate.

  Or is it just a case of poor design?

It is not poor design. Neither is it good design. It is a design which implements some affordances but precludes others. It was written for reasons to which we do not have access. It was written with the knowledge available at the time. Likely it passed a great many tests and active, production usage.

Now, the world may have moved on since then. The business rules may have changed; the implementation team's understanding may have improved. That design may have a performance characteristic which is not acceptable on your hardware with your workload given your data. It may be appropriate to change that design.

Normalization is about how non-key columns depend on key columns within a single table. It shows how you can change the schema so changing a single value in the real world will update a single column in a single row within the database. It has nothing to say about implementing scenarios from the real-world problem at hand.

I understand your current Student table to mean "a person as a member of a club". For that meaning the primary key will be {student id, club id}. In your current implementation the table is not normalized because Name depends only on student id and not on club id. The normalized solution would be to change the semantics of table Student to "A person" (columns student id, name) and create a new table ClubMember with columns {student id, club id}.

  The relationship from Club to ClubType is 1 to 1.

I doubt it. What are possible values of ClubType? Maybe "soccer" or "yoga"? I should think a great many schools would like to have a soccer club. Perhaps

Each Club     is-this-schools exactly one   ClubType
Each ClubType is-offered-in   zero or more  Club

As an ERD:

ClubType --< Club >-- School


Why doesn't join elimination work with sys.query_store_plan?

The documentation is a little misleading. The DMV is a non-materialized view, and does not have a primary key as such. The underlying definitions are a little complex but a simplified definition of sys.query_store_plan is:

CREATE VIEW sys.query_store_plan AS
SELECT
    PPM.plan_id
    -- various other attributes
FROM sys.plan_persist_plan_merged AS PPM
LEFT JOIN sys.syspalvalues AS P
    ON P.class = 'PFT'
    AND P.[value] = plan_forcing_type;

Further, sys.plan_persist_plan_merged is also a view, though one needs to connect via the Dedicated Administrator Connection to see its definition. Again, simplified:

CREATE VIEW sys.plan_persist_plan_merged AS
SELECT
    P.plan_id as plan_id,
    -- various other attributes
FROM sys.plan_persist_plan P
    -- NOTE - in order to prevent potential deadlock
    -- between QDS_STATEMENT_STABILITY LOCK and index locks
    WITH (NOLOCK)
LEFT JOIN sys.plan_persist_plan_in_memory PM
    ON P.plan_id = PM.plan_id;

The indexes on sys.plan_persist_plan are:

╔════════════════════════╦══════════════════════════════════════╦═════════════╗
║       index_name       ║          index_description           ║ index_keys  ║
╠════════════════════════╬══════════════════════════════════════╬═════════════╣
║ plan_persist_plan_cidx ║ clustered, unique located on PRIMARY ║ plan_id     ║
║ plan_persist_plan_idx1 ║ nonclustered located on PRIMARY      ║ query_id(-) ║
╚════════════════════════╩══════════════════════════════════════╩═════════════╝

So plan_id is constrained to be unique on sys.plan_persist_plan.

Now, sys.plan_persist_plan_in_memory is a streaming table-valued function, presenting a tabular view of data only held in internal memory structures. As such, it does not have any unique constraints.

At heart, the query being executed is therefore equivalent to:

DECLARE @t1 table (plan_id integer NOT NULL);
DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT
    T1.plan_id
FROM @t1 AS T1
LEFT JOIN
(
    SELECT
        T2.plan_id
    FROM @t2 AS T2
    LEFT JOIN @t3 AS T3
        ON T3.plan_id = T2.plan_id
) AS Q1
    ON Q1.plan_id = T1.plan_id;

...which does not produce join elimination:

Getting right to the core of the issue, the problem is the inner query:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3
    ON T3.plan_id = T2.plan_id;

...clearly the left join might result in rows from @t2 being duplicated because @t3 has no uniqueness constraint on plan_id. Therefore, the join cannot be eliminated:

To workaround this, we can explicitly tell the optimizer that we do not require any duplicate plan_id values:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT DISTINCT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3
    ON T3.plan_id = T2.plan_id;

The outer join to @t3 can now be eliminated:

Applying that to the real query:

SELECT DISTINCT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

Equally, we could add GROUP BY T.plan_id instead of the DISTINCT. Anyway, the optimizer can now correctly reason about the plan_id attribute all the way down through the nested views, and eliminate both outer joins as desired:

Note that making plan_id unique in the temporary table would not be sufficient to obtain join elimination, since it would not preclude incorrect results. We must explicitly reject duplicate plan_id values from the final result to allow the optimizer to work its magic here.


Unable to connect on premise sql server from Sql Azure db

My suggestion is to gradually migrate databases from SQL Server on-premises to SQL Azure but keeping the original databases on-premises and have them synchronize with Azure using SQL Data Sync as explained here. You then will be able to access your databases on premises if you need without the latency of linked server or you can access them on SQL Azure if you want.

Alternatively you can use replication as explained here.

Hope this helps.

Regards,

Alberto Morillo


Oracle database 18c – ORA-12541: TNS:no listener

The error message clearly says there is no listener listening on the requested address.

Check if it is running (lsnrctl status), and start it if necessary (lsnrctl start). On Windows you will need to do this in a command prompt started with administrator privileges, because it also start a Windows service. Or you can start the Windows service called something similar to OracleOraDB18Home1TNSListener.


In general, a single instance is more efficient, but has inferior performance isolation between databases.

If you want maximum throughput and best resource utilisation, put them all in a single instance. One busy DB will be able to affect other DBs more easily this way, especially when it comes to things like high rates of small write transactions. Features like async commit, commit delay, etc, help. If you group them all on one instance you'll also have to do any physical backups and streaming replication / PITR for the whole set of DBs, you can't isolate just one DB.

If you want to reduce the impact one busy/overloaded DB has on another DB, isolate them into their own instances and put each on a separate file system, perferably on separate underlying storage. You'll pay a price in wasted shared_buffers space, extra fsync()s, etc, and you'll find things like user management a major pain, but you'll be able to do streaming replication / physical backups of just one DB and you'll be more able to manage their relative resource allocations. If they're separate instances you can use things like nice, ionice, etc more effectively and can manage storage quotas somewhat better.


If you built your database at C:\Daten\db\db2\IBM\DB2, then that should be your path for your database directory. (ie, it should be one higher than where your database resides).

Also, just wondering what command you used when you built your database? Where did you specify that it lives?


How encryption in SQL login authentication works

how are the password encrypted and how does sql server decrypt it? What kind of encryption is involved in it?

Super high level, if you have encryption setup for the instance then the certificate that is configured can be used. If you don't have it enabled, the self-signed certificate created on SQL Server startup will be used.

Regardless of which is used, the SSL/TLS setup for this is done during the pre-login negotation phase. If, however, encryption is either not set or enforced, the TLS/SSL portion will only be active for the length of the credential transfer and will immediately go back to plain text (no SSL/TLS) once this is completed for the life of the connection.

SQL Server itself doesn't encrypt or decrypt the data, it's encrypted and decrypted in transit only. Depending on your client driver and application, this may or may not exist in plain text in your process memory space. Additionally, SQL Server uses hashes to check the passwords and doesn't store the original plain text password for SQL logins (unless you want to be pedantic about proxy accounts and linked servers).


How to use the below expression in my SQL Query

No idea what the is missing is supposed to mean (is it the same as is null?). If yes, the CASE expression and the ORDER BY can be written like this:

ORDER BY
  CASE
    WHEN Sec.ea_end_of_activity IS NOT NULL
      THEN 2
    WHEN First.aco_ac_ea_id IS NULL
      THEN 1
    ELSE 0
  END,
  Sec.aco_acc_short_name ;

The First. and Sec. can be omitted (at least in other DBMS, not 100% sure about Sybase), since they are in the SELECT list.


select newest update record among similar record

I'm not super familiar with Postgres, but based on some Internet searches, it seems that your version of Postgres supports 'window functions'.  It also seems to support 'common table expressions'.  If those statements are true, you should be able to wrap you main query into a common table expression and utilize the ROW_NUMBER window function to assign a row number to each 'grouping' (partition by person) and order by seg.updated desc.  Something like the following

;
With BaseData as
(
SELECT seg.summ
    ,su.channel
    ,seg.block_id
    ,seg.person
    ,seg.updated_at AS segupdate
    ,ROW_NUMBER() OVER(PARTITION BY seg.person ORDER BY seg.updated_at desc) as rn
FROM segments seg
LEFT JOIN summaries su
    ON seg.summ = su.id
WHERE su.created_at BETWEEN '2018-06-06'
        AND '2018-07-13'
ORDER BY summ
    ,block_id
    ,seg.updated_at DESC;
)
SELECT * FROM BaseData where rn = 1


MySQL can handle it, but not without some care.

12,000 tables is a red flag.  Each table is one (or more) OS file.  This large a number, especially if in a single database, can cause sluggishness.

Are the records merely written to the tables?  Or are they used to "update" existing rows?

I would load the 500K rows into a table(s), cleanup the data, then copy it into the real tables.

Generally, I would do the work 1000 rows at a time.  But this might vary depending on whether you have Replication, which Engine you are using, etc.

7B in 150GB smells like MyISAM; is it?

If you are "never" reading it, why put it in a database?  Why not in flat files?

What does the incoming data look like?  CSV?  mysqldump?  Other?

Simple-minded code should be able to load a nightly dump in an hour or two.  More advanced coding might get it down to less than an hour.


Backup and restore of RDBMS(Oracle, DB2, SQL Server) from Perl

Backing up in DB2 is pretty simple. You use the DB2 Backup Database command (command line). You can invoke this via scripting language if you wish (I highly recommend it). We run AIX at our shop, so we use ksh scripts that are called by cron jobs to make sure we run backups of the database.

Restores we do "manually". We still have them scripted, but we invoke them only when needed.


save 14 columns in one table or different table?

Two tables that are in 1:1 relationship should, in almost all cases, be combined into a single table.  Here are some exceptions of where it may be beneficial to have multiple, parallel, vertically partitioned, tables.  Note: you probably do not have any of these cases.

MyISAM table with lots of rows and one or more big TEXT or BLOB.  The layout of MyISAM leads to clumsy access for queries that don't need the bulky columns.  (This does not apply for InnoDB.)  Even so, there is a workaround ("lazy eval").
You have a very rapidly changing column, such as "Likes".  Separate it into a separate table (plus an id for JOINing) so that the frequent UPDATEs are not competing with the more mundane operations.
Optional columns.  If many of the rows do not have a certain column(s), move them to another table and use LEFT JOIN to reconstruct.  This is almost not worth mentioning, since NULL is likely to be better for an optional column.
A new column being added to a huge table.  If you already have a table with millions of rows, and it is very actively used, and you don't want the downtime to add a new column, put it in another table.


MySQL - INSERT INTO ... SELECT ... UNION

You cannot use the INSERT INTO tblname (...) VALUES ... syntax

You must use the INSERT INTO tblname (...) SELECT ... syntax

Try the Following:

INSERT INTO my_backup_table (account_id)
SELECT id FROM
(
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%gmail.com')
    UNION DISTINCT
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
    (SELECT email FROM inactive_clients))
) A;


Best database replication technique for a very unstable (power wise) network

If MySQL isn't a hard requirement, consider FoundationDB Key-Value Store + SQL Layer.

FoundationDB is a distributed Key-Value database with ACID guarantees and strong fault-tolerance. You can setup a cluster spread across the different municipalities, and if a server in the cluster goes down, the rest of the cluster continues to operate and accept writes. Changes are automatically synced to the downed node once it comes back online.

Here's a video where you can see this in action: Dave is killing the power to different nodes in the cluster, and demonstrating the cluster remains up and accepts writes. This blog post walks through what happens to the database in the case of a network partition and adds more technical context.

SQL Layer is a process that maps SQL statements to the Key-Value Store. It which presents a full, ANSI-compliant SQL API to your application.

Full disclosure: I'm an engineer at FoundationDB


I think there's no way to indicate to \copy that the file is relative to the location of the script, but you may use \cd to change the current directory before \copy.

\cd interpolates variables so the directory can be passed on the command line with -v:

psql -vscriptdir="c:\path\to\script" -f c:\path\to\script\script1.sql

Before including anything, say there's this command in the top-level script:

\cd :scriptdir

(except if the -f argument is simply a file without a path:  in this case it's not necessary since the current directory is already set where the script is)

Then every inclusion of a script in a subdirectory should be:

\cd dir2
\i script2.sql
\cd ..

(as opposed to \ir dir2/script2.sql)

When script2.sql will invoke \copy with a file relative to it, the data file will be found since it's in the current directory.

That should work recursively, assuming scripts in subdirectories also follow that convention, and that they don't do an unrelated \cd that would interfere with the \cd .. waiting to happen on the way back to the top.


If the account id is always at the third position use split_part()

select *
from gorfs.seg
WHERE split_part(full_path, '/', 4) = '4865'

Note that due to the leading / the actual index is 4, not 3.

The following query:

with seg (id, full_path) as (
  values
      (1, '/userfiles/account/4556/attachment/1234'),
      (2, '/userfiles/account/123/attachment/1234'),
      (3, '/userfiles/account/7890/attachment/1234'),
      (4, '/userfiles/account/4556/attachment/56789')
)
select *
from seg
where split_part(full_path, '/', 4) = '4556'

returns

id | full_path
---+-----------------------------------------
 1 | /userfiles/account/4556/attachment/1234
 4 | /userfiles/account/4556/attachment/56789

If you want to put that into a view, you can use:

create or replace view v_seg
as
select st_ino,
       segment_index,
       st_ino_target,
       full_path,
       split_part(full_path, '/', 4) as account_id
from gorfs.seg;


Appropriate Action for Dead Locks / Blocking

To piggy back off @AaronBertrand, killing a process could cause transaction inconsistency and that would mean you are throwing out ACID compliance.  Why not first record all queries that take over 45 seconds and then change your code design based off that?

There's a lot you can do to resolve locking/blocking issues from changing the isolation levels such as READ UNCOMMITED or using snapshot isolation (snapshot isolation is preferred as there's lots of caveats with READ UNCOMMITED, but it won't help in the case of writers blocking writers).

check out:

MCM Snapshot Isolation Intro Video

Good Start On Troubleshooting Locking And Blocking


Open your job in the management studio and go to the backup step properties and check whether it looks like this

I suspect "On failure action" Is not setup properly.


SQL Server Agent will schedule a job and cause it to be run by the SQL Server service - the database engine. The account which is trying to create this snapshot for your CHECKDB to be run concurrently with users in it is not the Agent service, but the SQL Server service. So look to see who your SQL Server service is running as and what permissions that account has.


How do I determine the time period to which the SQL Server i/o warning is referring?

Are warnings collected and expressed on reaching a request threshold or on schedules? Can anyone explain the rules behind this warning log?

Let me start by saying that this is mostly not a SQL Server issue, unless you have totally flawed system. The message means that SQL Server waited more than 15 seconds for one of the I/O requests to complete. For example when SQL Server wants a data page into memory from disk it would initiate an I/O request, if that request takes longer than 15 seconds such messages are logged into errorlog. Normally, on a good system, an I/O response should complete within milliseconds but if this warning is coming this mostly indicates

The SQL Server is producing more I/O for a storage subsystem to handle and thus overwhelming it with I/O responses
The underlying disk subsystem is slow, outdated, not on updated firmware and so cannot cope up with SQL Server I/O requests.

Quoting from Blogs.msdn

  The message indicates that SQL Server has been waiting on at least one
  I/O for 15 seconds or longer. The exact number of times you have
  exceeded this time for the specified file since the last message is
  included in the message. The messages will not be written more than
  once every five minutes. Keep in mind that read IOs on an average
  system should take no more than 10-20ms and writes should take no more
  than 3-5ms (the exact acceptable values vary depending on your
  business needs and technical configuration). So anything measured in
  seconds indicates a serious performance problem. The problem is NOT
  within SQL Server, this message indicates SQL has sent off an IO
  request and has waited more than 15 seconds for a response. The
  problem is somewhere in the disk IO subsystem. For example, the disk
  IO subsystem may have more load than it is designed to handle, there
  is a "bad" hardware or firmware somewhere along the path, filter
  drivers such as anti-virus software are interfering, your file layout
  is not optimal, or some IO subsystem setting such as HBA queue depth
  is not set optimally.

There two phases of analyzing this message by SQL Server, recording and reporting. More details in This support Article

  Recording There are two moments when a record action occurs in SQL Server. The first is when the I/O operation actually finishes. If an
  I/O request takes more than 15 seconds to finish, a record operation
  occurs. The second moment is when the lazy writer runs. When the lazy
  writer runs, the lazy writer checks all the pending data and all the
  pending log file I/O requests. If the 15-second threshold has been
  exceeded, a record operation occurs.

  Reporting

  Reporting occurs in intervals that are 5 minutes or more apart.
  Reporting occurs when the next I/O request is made on the file. If a
  record action has occurred and 5 minutes or more have passed since the
  last report occurred, the informational message that is mentioned in
  the "Summary" section is written to the SQL Server error log.

  The 15-second threshold is not adjustable. However, you can disable
  stalled or stuck I/O detection by using trace flag 830, although we do
  not recommend that you do this.

Troubleshooting

Make sure SQL Server data and log files are distributed on different drive this will give some I/O relief
Make sure drive on which SQL Server files reside are not compressed
Get your storage checked from storage team/vendor.
If the message says I/O request taking longer for tempdb make sure you have configured tempdb properly.  Make sure trace flag 1117,1118 is enabled. This support article should help you.

To find out whether disk is slow or SQL Server is spawning more I/O's which disk could handle you would have to take help of perfmon counters and DMV Sys.dm_io_virtual_file_stats. For perfmon counters and how to gauge whether disk is issue or it is SQL Server please refer to blog by Karthik PK.

For DMV, Paul Randal owner of SQLSkills.com has Caputing IO latencies for period of time. This will let you capture I/O latency for period of time and analysze. If you have HDD or flash drives and see latency in seconds that is pointing to storage slowness issue.

Also read Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data


From what you've described a self referencing table is the way to go.  This only works if you can get all the data into a single schema (which you probably can).  It'll make the management a piece of cake and you can have changes on infinite length at this point.


FULL OUTER JOIN With MySQL, MySQL don't support ist what is the solution for more than 2 Tables?

Build a VIEW that simulates a FULL OUTER JOIN of A and B.  (Use the template given by some of the links.)
Build a VIEW that simulates a FULL OUTER JOIN of that VIEW and C.  (Use the template given by some of the links.)
Etc.

Eventually, you will have a FULL OUTER JOIN of all the tables without the ugly mess of compounding the number of joins needed.


SQL Server 2012 - How to view the locks' log?

If you haven't already turned on the blocked process report, then it's not possible to jump back in time to view past blocked processes.

However, I find that where there's blocking, these two already-on things help:

First, SQL Server tracks which indexes are involved in blocking. It doesn't tell you which queries are involved, nor is it permanently reliable (the data disappears at odd times), but it's a start. You can query sys.dm_db_index_operational_stats to learn more, or use the open source sp_BlitzIndex to see it. sp_BlitzIndex identifies the indexes involved in blocking as "Aggressive Indexes" - that can be a little confusing, so here's a post on how to interpret those warnings.

Second, if there's blocking, there's sometimes also deadlocking. And good news! SQL Server tracks deadlocks by default in the system health session, and you can indeed query those with the open source sp_BlitzLock. It doesn't hurt to give that a shot - it may identify which queries are causing you problems.

(Disclaimer: I wrote some code in those open source procs, and I wrote the blog post referenced in here.)


I know that ApexSQL Refactor ( https://www.apexsql.com/sql_tools_refactor.aspx ) will cover the aliases as preset option, not sure about AS without doing some digging.


USING FILE clause is used to load data from a file. The column list shows two columns to be imported from the .csv file.

LOAD TABLE clientAddress.table (client.id, address.info)
USING CLIENT FILE 'file.csv'


SQL Server - failed to update records when RAM is fully occupied

As suggested set the “Maximum Server Memory”.

https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

Additionally, please note that your error is more likely related to locking or timeouts rather than “running out memory”. An extensive exclusive lock on the target table combined with missing indexes for the update statement can result in what you are experiencing.

1)  Tune your report query and ensure that its not having redundant excessive reads. Verify that hash and sort operators in your execution plan are not spilling to disk.

2)  Make sure that your update statement has logical supporting indexes.

Tuning both process might enhance concurrency of your application.

Good luck :)


Always On Synchronous Commit Mode With Large Delays

The answer you're looking for is in the docs

⚠ Note
If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

When this happens, a synchronous secondary will fall back to be asynchronous (what some folks call "pseudo synchronous" because you set it to synchronous, but it's actually async). This is not specific to network latency, or physical distance, but can happen under a number of scenarios. As an example, if you restart a synchronous replica and that server is offline, your AG will fall back to asynch and continue to process transactions.
Synchronous AGs only pay a performance penalty on writing to the transaction log (which needs to write to the secondary replica). Write activity is often a minority of the database activity, with the majority of traffic being read operations, which are not affected by synch/async.
Running an AG in sync commit mode between US & EU is totally possible. Write transactions will be slower, as they need to write to the log on the other side of the ocean. Generally, a better design is to keep synch replicas closer to avoid the network latency. However, depending on the IO patterns of your database, your business needs, and your network backbone between geographies, it's possible. On the systems I support, we can tolerate synchronous commit latency across hundreds of miles--but when the distance gets longer than that the performance impact becomes too great. It's not that SQL Server can't support it--it's that the users will not tolerate that slowness.
Generally, I find most folks end up running synchronous commit within a data center/geography for high availability, and asynchronous commit between geographies/data centers for disaster recovery. For a planned failover across geographies, you can temporarily switch the remote replica to synchronous mode to ensure no data loss during the failover.
The easiest way to find out the actual impact on your application is to test it.


can my SQL Server application code using temporary tables be used for Postgres?

Postgres supports temporary tables but it does not support the #tablename syntax.  You can keep the structure but you will have to modify the code to the new syntax.  Unless you want to download the code for Postgres and modify that but that seems like unrecommended overkill.

http://www.sqlines.com/postgresql/statements/create_temporary_table

  CREATE TEMPORARY TABLE statement creates a temporary table that is
  automatically dropped at the end of a session, or the current
  transaction (ON COMMIT DROP option).

  Quick Example:

   -- Create a temporary table
   CREATE TEMPORARY TABLE temp_location
   (
      city VARCHAR(80),
      street VARCHAR(80)
   )
   ON COMMIT DELETE ROWS;


It is always tricky when you have Identity column and that is a Primary Key for the table.

Based on my experience dealing with T-Rep and Merge, I would recommend to use NOT FOR REPLICATION to NO. By default it is set to YES. You can do it on sql 2005 and up dynamically (without affecting replication) using sys.sp_identitycolumnforreplication.

If you are not updating the data on subscriber side, you should be fine.

To answer your question :

  Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data on Server B?

Considering, you are not updating the data on subscriber, when you want to reseed, you should always take the max value from the identity column and then reseed the value accordingly.

You can use below sql to find that out :

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName,
        c.name AS ColumnName,
        IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
        IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + t.name) as Identity_increment
    FROM    sys.columns AS c
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
  -- write here the table name if you want to filter for specific table
    and t.name like '%TABLE_NAME%'

for example consider below customer table :

TableName     ColumnName    CurrentIdentityValue    Identity_increment
[dbo].[customer]    id         7                    1

Now if you want to somehow reseed the identity value, you can run

dbcc checkident ('customer',reseed,8 )

This means that it will set the current Identity value to 8, so next time any row is inserted into the table, it will have current identity value as 8 (CurrentIdentityValue) + 1(Identity_increment) = 9.

You have to be careful doing this as this can cause identity gaps.

Other things you can do are :

Partitioning
Manual identity range management solutions using sp_addaritcle with parameters like @identity_range ,@pub_identity_range and @threshold

Read up on :

The identity crisis in replication - by Hilary Cotter
All about “Identity Range Management” - by Chris Skorlinski


From a general perspective (not platform-specific), here's what I'd recommend mastering for data warehouse projects:

Know how to load data fast.  BI projects usually involve nightly loads of large amounts of data.  The ETL guys need to shove data in fast with a minimum of concurrency issues.  This means knowing when to disable indexes, when to perform tasks in temporary staging databases rather than the production environment, and how to offload processing to the ETL server instead of the database server.

Know how to handle large table scans. BI environments usually have multiple terabytes of data, beyond what can fit in memory.  Index tuning can only take you so far.  You need to know how to get as much throughput out of the SAN as possible.

Know how to segment archive data. BI environments usually have a small percentage of live, changing data and a much larger percentage of read-only (or read-biased) archive data.  You have to know how to recognize those patterns and how to separate that data out into different tables or different storage targets with a minimum of work required by your ETL people.

Know how to handle maintenance tasks.  Defragmenting or rebuilding indexes is easy on a 100GB database, but not so easy on a 1TB or larger database.  Maintenance windows have to be carefully planned.  Backups are another story altogether.

Know when to design reporting tables. If your users constantly access the same aggregate data (like grouping sales data by month or by salesperson, or constantly recalculate a profit percentage) then you need to recognize those trends in the end user queries, design a pre-calculated set of reporting tables, and train the users to access the data that way.


Why does an index rebuild requires a Sch-M lock?

The documentation for How Online Index Operations Work says:

SCH-M (Schema Modification) if any source structure (index or table) is dropped.*
* The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.

That is specifically for online operations. If you're asking about offline operations, the logic is the same: nothing can be accessing a structure that is about to be dropped, not even under Sch-S only, or for internal use inside a system transaction. Also note the "(index or table)" there, so this applies to all indexes (including clustered) and heap tables.
Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).
Also related from the documentation:

ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change...

From the same link, SQL Server 2012 introduced the ability to add a NOT NULL column without taking Sch-M so long as the default value is a runtime constant. I mention this because it shows how SQL Server has evolved to reduce unnecessarily restrictive locking where possible. If you find a particular case where you feel Sch-M is genuinely unnecessary, you could make the suggestion on the Microsoft Feedback site.
In addition, in Unicorns, rainbows, and online index operations, Paul Randal says:

When then index operation has completed, the new and old indexes are in lock-step as far as updates are concerned. A schema-modification lock (SCH_M lock mode) is required to complete the operation. You can think of this as a super-table-X lock – it's required to bump the major version number of the table – no operations can be running on the table, and no plans can be compiling while the lock is held.

Related links:

The Sch-M lock is Evil by Michael J Swart


Writing a query to find all employee salaries that are higher than their managers

By way of an example that addresses all the items @ypercube brought up in his comments on your question, you could redesign your table structure like:

CREATE TABLE Workers
(
    WorkerID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1)
    , ManagedByWorkerID INT NULL CONSTRAINT FK_Workers_WorkerID REFERENCES Workers(WorkerID)
    , Salary NUMERIC(10,2)
);

To add some sample data for demo purposes:

INSERT INTO Workers VALUES (NULL, 1000000);
INSERT INTO Workers VALUES (NULL, 50000);
INSERT INTO Workers VALUES (1, 40000);
INSERT INTO Workers VALUES (1, 1000001);
INSERT INTO Workers VALUES (3, 40002);
INSERT INTO Workers VALUES (2, 29999);
INSERT INTO Workers VALUES (2, 60000);
INSERT INTO Workers VALUES (5, 60000);

And finally, the query to return workers who make more than their boss:

SELECT *
FROM dbo.Workers;

SELECT W.WorkerID
    , W.Salary
    , Bosses.WorkerID AS BossID
    , Bosses.Salary AS BossSalary
FROM dbo.Workers W
    INNER JOIN dbo.Workers Bosses ON W.ManagedByWorkerID = Bosses.WorkerID
        AND Bosses.Salary < W.Salary;

The results:


How much work actually happens on SSMS and how much on the server itself?

The SQL Server Management Studio only sends the query (or T-SQL prodcedure) to the server and waits for the result.

There's a difference between running it from LAN or directly on the server ONLY because results has to be transferred on your PC (so the difference is only the time to transfer data on LAN).
(Also, in the first case, your PC has to be turned on for the entire duration of the query.)


Is it possible to make one column readonly?

There is no built in declarative support for non updatable columns (except for specific predefined cases such as IDENTITY)

This Connect item requested it but was rejected. Add DRI to enforce immutable column values

An UPDATE trigger would probably be the most robust way of achieving this. It could check IF UPDATE(CreatedByUser) and raise an error and rollback the transaction if true.


You need to place init_connect in /etc/my.cnf
[mysqld]
init_connect='SET CHARACTER SET=utf-8';

This will set the variable for you after the next mysql restart.
If your username has the SUPER privilege, you need not set the variable again dynamically since you already set globally earlier with SET GLOBAL init_connect='SET CHARACTER SET=utf-8';
As for 'MySQL server has gone away', the most common cause is timing out. Please set wait_timeout and interactive_timeout to something sufficiently large (default 28800 sec [8 hours]). Other reasons are specified in the MySQL Documentation.
UPDATE 2012-04-22 01:24 EDT
Since you are changing the character set to process multibyte character strings, you may need to make sure the MySQL Packet is bigger (at least triple whatever the configuration is now). I would bump it up to 256M
[mysqld]
init_connect='SET CHARACTER SET=utf-8';
max_allowed_packet=256M

I recommend this because sometimes Error 2006 : 'MySQL server has gone away' is caused by the MySQL Packet not being big enough. I wrote posts about this earlier:

MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob (Nov 22, 2011)
Fixing MySQL errors recorded in error logs (Sep 25, 2011)
MySQL server has gone away obstructing import of large dumps (Sep 01, 2011)


how does multiple column index work in details

This is not necessarily the case. Oracle, for example, has an access path known as an "index skip scan". See http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553

Basically, if there are few distinct values in column A, and the query restricts on column B (and optionally, column C), the executor will substitute each of the distinct values of column A in turn and probe the index for the supplied value of column B (and optionally, column C).

Postgresql can do something similar, but it is still labelled as an "index scan", see Working of indexes in PostgreSQL


SQL Server Baseline and Performance Optimization

Here are some good articles with some practical examples that you can find here:

How to detect SQL Server performance issues using baselines – Part 1 – Introduction

How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting

How to detect SQL Server performance issues using baselines – Part 3

While Part 1 will provide you some basic knowledge about what the baseline is, in Part 2 you can find info how to do that on your own using "poor man" method

Part 3 provides some examples on how you can establish baselines and how to use baselines in troubleshooting some issues via ApexSQL Monitor


What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7?

Set it to 0 and let InnoDB pick the concurrency.

20 cores with currency=8 means that at least 12 cores will always be idle.

What is the value of Max_used_connections?  If that is not very high, the "concurrency" is not your main problem.

Meanwhile, let's see one of the 'slow' queries, together with SHOW CREATE TABLE; there may be ways to speed it up, possibly decreasing the I/O.

For more analysis of your VARIABLES and GLOBAL STATUS, let's see them.  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


SQL Server 2008 r2: What events/queries hit tempdb

To summarize Tempdb is used to store 3 types of objects:

user objects

local temporary table (seen by one session)
global temporary table (seen by all sessions)
Table variables

internal objects used temporarily by SQL server during Query processing : operations such as sort , join, hash and cursors all require space in tempdb. see the sys.dm_db_session_space_usage DMV.
the version store :
The following features make use of the version store:

Triggers
Snapshot isolation or Read-Commited Snapshot isolation
Online index operations
Multiple Active Result Sets (MARS)

Please note that using the sys.dm_tran_version_store can be ressource intensive.

May I suggest that you check Troubleshooting insufficient disk space in tempdb for a few examples of how to use the DMV.


Fastest way to get the length of the intersection of two text arrays in PostgreSQL

You say you're looking for "Jaccard Similarity between two arrays"

You may consider MadLib which provides this as dist_jaccard()

  Jaccard distance between two varchar vectors treated as sets.

You may want to try it without STRICT. That may be slowing it down.
You may want to use INTERSECT ALL Especially if you know you don't have dupes in $1.
As Craig Ringer said in the comments you may want out intarray and consider using &.

To give a more accurate answer here,

What are the datatypes?
How many elements are we talking about on each array?
How much exclusion does the INTERSECT provide?


Is processing required dimensions enough or to process the entire cube?

I think you are confusing things.

First of all, processing the cube and processing the dimensions is sort of separate. I think you mean "processing the entire database".

If you process only dimensions, and not the cube, the measures will never be updated.

You could Process Update the dimensions which wouldn't unprocess the cube but would drop the indexes and flexible aggregations so you would need to Process Index in the same transaction (to avoid the indexes being created on data that may have changed since the process dimension).

Also if you fullprocess dimensions, the cubes where the dimensions are linked will be in an "unprocessed" state.

On the other hand. You don't necessarily need to process the entire database, If you are absolutely sure nothing in one of the underlying dimension tables changes you can get away with just processing some of the dimensions and the cube without processing the entire database.

On the other hand, what makes you ask this question? It's certainly not something I would recommend as there are probably better solutions, such as processing incremental which won't do a lot if nothing has changed.


Yes, increase table_open_cache.

Are you using Windows?  Or *nix?  (You are in an area where the OS matters.)

Keep in mind that each MyISAM table involves 3 files.

What is Max_used_connections?  That should give you a clue of what max_connections can be lowered to.


Why is SQL Server 2012 memory usage limited to 4 GB

Short update: I could solve the problem myself.
Problem was actually not related to the memory. We had high traffic with many very CPU expensive queries which created a constantly high CPU load, which were the reasons for RESOURCE_SEMAPHOREs.

After reducing the quantitiy of queries significantly and tuning the queries itself to be less expensive, the load decreased and there were no more RESOURCE_SEMAPHOREs.

Memory usage is still only 4 GB, but obviously system doesn't need to use more!


Storing prices in SQLite, what data-type to use?

Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100.

In my experience it's standard practice.


For each row, find the next closest value in another table (Teradata)

select CHAS_INIT, CHAS_NR, Ingate_TIME,
   -- find the previous IN-time
   last_value(case when inout = 'i' then Ingate_TIME end ignore nulls)
   over (partition by CHAS_INIT, CHAS_NR -- does this determine a truck?
         order by Ingate_TIME)
from
 (  -- combine both in & out into a single result
    select 'i' as inout, CHAS_INIT, CHAS_NR, Ingate_TIME
    from session.Ingate
    union all
    select 'o' as inout, CHAS_INIT, CHAS_NR, Ingate_TIME
    from session.Outgate
 ) as dt
-- only return OUT rows
qualify inout = 'o'


MongoDB service not working after upgrade to Ubuntu 15.04

Since your error is:

Failed to restart mongod.service: Unit mongod.service failed to load: No such file or directory.

This is pretty much saying that your startup path is not in the expected location or else the Ubuntu install has led to problems. See: http://docs.mongodb.org/manual/tutorial/manage-mongodb-processes/

EDIT: Updated with link to what appears to be the answer, namely something is wrong with Ubuntu and mongo.

https://stackoverflow.com/questions/29879231/upgrade-to-ubuntu-15-04-from-14-10-breaks-mongo-how-to-fix

Joel Parke has defined the problem and pointed to a ticket on the issue. https://jira.mongodb.org/browse/SERVER-17742

That ticket reportedly explains how to "cleanly install current versions of mongodb on Ubuntu 15.04 by replacing the apt source."


Version store blowing up, but responsible sessions have no open transactions

This issue was caused by an orphaned distributed transaction.  Since the transaction is orphaned, the session ID is invalid by the time you see it.  To clear the condition you find the oldest transaction with an active snapshot, lookup of the unit of work (UOW), and then kill the UOW.

--find the transaction with the oldest snapshot version
select top 1 transaction_id, elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions a
order by a.elapsed_time_seconds desc

--using the transaction ID from the query above, find it in the active
--transactions DMV.  You should see the begin time as many minutes/hours ago
select transaction_id, transaction_begin_time, transaction_uow
from sys.dm_tran_active_transactions where transaction_id in(<transaction_id>)

--using the transaction_uow from the query above, kill the unit of work
kill '<transaction_uow>'

After it is killed, run the first query again to see if there are any more that need to be cleared.

In some cases you may need to find the UOW by first finding orphaned transactions, which are supposed to have a session ID of -2.

select * from sys.dm_tran_locks where request_session_id = -2

See Kill (Transact-SQL) for more.


List all column names for multi-joined table

I'd like to somehow get the name of all columns in this new table.

You can create a temporary view, which registers the row type. Then query either the information schema like you tried or the system catalog pg_attribute:

CREATE TEMP VIEW tmp_v1 AS SELECT ...;  -- your query here

SELECT attname
FROM   pg_attribute
WHERE  attrelid = 'pg_temp.tmp_v1'::regclass;

Returns the desired list of column names.

Catalog tables or information schema? See:

Query to return output column names and data types of a query, table or view

The temporary view is only visible inside your current session and dropped at its end. (Or you can drop it manually.)

In psql you could use the meta-command \gdesc:

test=# SELECT 1 AS foo \gdesc
 Column |  Type
--------+---------
 foo    | integer

The manual:

  \gdesc

  Shows the description (that is, the column names and data types) of
  the result of the current query buffer. The query is not actually
  executed; however, if it contains some type of syntax error, that
  error will be reported in the normal way.

  If the current query buffer is empty, the most recently sent query is described instead.

So you can even execute a bare \gdesc after running the query to get a "table description" for the executed query.


How secure is a linked server in SQL Server

I want to log all logons using a logon trigger from server A in server B.

Ideally, if you are using Enterprise edition, then as Shawn mentioned, you can use SQL Server Audit.

You can even set up a light weight server side trace and then define a WMI event notification - TRACE_FILE_CLOSE to automatically load the trace data into a table when it rollsover.

Another option is - you can use the script for Logon Trigger posted by me. Note that it wont work for the existing connections and it a limitation of Logon triggers. All new connections will go through the Logon Trigger.

  it is important that my database be able to insert the log into A, but it should not have delete or update access on B.

You can use transactional replication by just replicating the LOGON Table to server B (in your case). Make sure you choose - Do not replicate DDL changes option.

  when I want to monitor the logons on A the only possible solution should be doing it on B.

You can create a view using Linked server and just give select permission to the view. This way you can control who can access the info.

As a side note, make sure you do not return a full data set in the view, rather just limit it with last 10 -15 days of login audit, so when a user queries the data, it will return a small amount of data only.


Extract rows of a table, that include less than x NULLs

Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:

CREATE TABLE #test123(
    c1 float,
    c2 float,
    c3 float,
    c4 float,
    c5 float
);

INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
       (2,NULL,NULL,1,2),
       (2,3,NULL,NULL,2),
       (NULL,3,NULL,1,NULL),
       (2,3,NULL,1,2);

To see why ISNULL isn't effective here, run this query:

SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
  FROM #test123;

You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.

It's more code but a simple way to address this is:

SELECT * FROM #test123
  WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;


High ASYNC_NETWORK_IO when user locks computer with Excel open

ASYNC_NETWORK_IO can cause database contention issues if the results being sent to the client never spool anywhere (e.g., via an ORDER BY clause forcing a sort in tempdb).
If this issue is happening with Excel, then the problem is likely due to an Excel Database Connection refreshing on the background thread and the user simultaneously locking their computer during the background refresh.  When the background thread is complete, the computer is locked.  The issue is that in Win32, a locked computer means the user session object is not available.  When the user session object is not available, there is nowhere for the background thread to return results to, and it just hangs, waiting for Excel.exe to read the results.  Because the results never spool anywhere, the ASYNC_NETWORK_IO lock causes a livelock on any previously acquired locks.  In itself, ASYNC_NETWORK_IO is harmless.  It's the lock chain that is doing the damage.
To sum it up, this problem can happen under the following two conditions:

ASYNC_NETWORK_IO is present due to a slow reader (in my case, the slow reader is livelocked due to Win32 user session object unavailable)
The results are never spooled to some intermediary object, implicitly (e.g. ORDER BY triggering a sort in tempdb on underlying table, because the data is too big to sort completely in memory) or explicitly (INSERT results into a tempdb table, do SELECT on the tempdb table, which releases the livelock).

Two options:

If you don't have control over the client's slowness?

a. Write the query results to a temp table. Return the temp table to the user.
b. Also, don't forget to SET NOCOUNT ON at the top of the stored procedure, to avoid roundtrip delays due to DONE_IN_PROC TDS messages being sent to the client.

If you do have control over the client's slowness?

a. Check if the NIC is misconfigured or if the client is using a VM and the VM's NIC is miconfigured. See: https://www.reddit.com/r/sysadmin/comments/2k7jn5/after_2_years_i_have_finally_solved_my_slow/
b. Measure using Perfmon. Microsoft uses a heuristic of 10 for PerfMon Network Output Queue Length. See: https://technet.microsoft.com/en-us/library/aa997365(v=exchg.80).aspx
c. Add Query BeforeRefresh and AfterRefresh event handlers to instrument logging clues in the Worksheet to determine what the user is doing. In particular, see "XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events": https://support.microsoft.com/en-us/help/213187/xl2000-how-to-use-the-query-beforerefresh-and-afterrefresh-events
d. Write an Excel add-in that caches their SQL calls.
UPDATE 1/19/2018
I'm still dealing with high ASYNC_NETWORK_IO issues with Excel users.  I believe I have found more potential causes of this, but these are just theories at this point:

If you have multiple tables in the same spreadsheet, and the last table to the right has more rows than the tables to the left, this seems to cause Excel to "blink" a lot.  Correspondingly, in SolarWinds Database Performance Analyzer, queries on this spreadsheet that return the most rows and columns also have the highest ASYNC_NETWORK_IO problems. (Next steps for me are to reproduce this issue, but I'm not sure it's worth my time as I persuaded one user that they didn't need that huge result set at all for now.)

I found a comment on Experts Exchange ( https://www.experts-exchange.com/questions/25049176/Slow-queries-excuted-throug-network-ASYNC-NETWORK-IO.html ) where someone said 5,000 row results or higher are notorious for causing problems.  This appears to actually be a recommendation raised internally by Microsoft Dynamics NAV application, which is notorious for causing SQL Server instability due to high ASYNC_NETWORK_IO waits.  The error message the application raises is, "The number of records in the table exceeds the maximum number of 5000. Set a filter to decrease the number of records in the table. Exporting too many records at once can affect system performance."  But this is just a guess.

For SSIS Excel Destination tasks, if the user does not have permission to write to Temporary Internet Files folder, then it might just hang: https://stackoverflow.com/a/23523954/1040437  (Again, just a theory as I use this to try to collate all possible theories into one place.)


Limiting number of results in a Partition using OVER(PARTITION BY)

The column "row_num" doesn't exist because the logical order of processing requires the dbms to apply the WHERE clause before it evaluates the SELECT clause. The windowing function is part of the SELECT clause, so its alias isn't accessible in the same statement's WHERE clause.

The FROM clause is the very first part of the statement to be evaluated. That's why the  aliases you declare in the FROM clause are accessible in the WHERE clause of the same statement.

Search this page for "logical processing order". Although this links to SQL Server documentation, the logical order of processing is the same for every dbms that complies with SQL standards.


MySQL server installation file download for windows

There is a "portable" ZIP package of version 5.6.27 (not 25) for download:
http://dev.mysql.com/downloads/mysql/5.6.html#downloads

For completeness, to install and run the server using an unprivileged account:

unzip
rename my-default.ini to my.ini
update base and data directory path in my.ini (data directory must exist)
Open a command window in the installation path.
run the server once with ".\bin>mysqld.exe --log_syslog=0 --console --standalone --initialize" to set-up a fresh database (note: --initialize might be --bootstrap in that version)
run the server with "mysqld.exe --log_syslog=0 --console  --standalone" (--console only if you want the output in the terminal window)
enjoy :)

"mysqld" can be installed as a background service (d for daemon), but you cannot do this without admin rights.


SQL Server 2014 - Add Node inherits wrong configuration

It appears to be a bug/feature. It was discussed previously on SQL Server Central http://www.sqlservercentral.com/Forums/Topic1578488-2799-1.aspx where Ed Vassie said:

  Put it another way... On an ADDNODE install you cannot specify the
  location for the SQL binaries, they always go to the system drive.

Ed Vassie is a reliable source as the original developer of FineBuild, a tool specifically for rolling out repeatable installs of SQL Server including in clustering mode. He lives and breathes this part of SQL Server.


Storing companies' working hours in Postgres

I would store the opening hours per weekday/per company. For storing the actual hours, a timerange seems suitable.

create table opening_hours
(
  company_id int not null references company,
  day_of_week int not null, -- 1 = Monday
  hours timerange not null
);

I would use 1 for Monday because that's the ISO definition and most of Postgres' date functions work like that, so you don't need to do conversion from and to a different base number for Monday.

If there is a noon break, it would mean that the day_of_week gets two entries, e.g.

insert into opening_hours (company_id, day_of_week, hours)
values
(1, 1, timerange('09:00', '12:00')),
(1, 1, timerange('13:00', '18:00'));

To find the companies that are opened at a specific day and time you can use something like this:

select *
from company c
where exists (select *
              from opening_hours oh
              where oh.company_id = c.id
                and oh.hours @> time '17:35'
                and oh.day_of_week = 2)

An exclusion constraint can be used to avoid overlapping opening hours for the same day and company.


You could do log shipping with the standby option rather than norecovery.  The database would be read-only, and the users would get kicked off during each restore, but it might suit your needs better.