Difference installing SQL Server 2014 on stand alone vs. VM Cluster

edited answer based on the comments.

For a basic setup here are a few things that I do before getting to the actual SQL install

Have two additional IPs / Host Names in mind for the Windows Cluster and the SQL Server VIP
Have two Domain service accounts setup for the Engine and the Agent (Something like DOMAIN\sql.eng.svc and DOMAIN\sql.agt.svc)
Enable the following Roles and Features (File Server, .Net Framework 3.5, Failover Clustering, Telnet Client)
In Local Security Policy grant the DOMAIN\sql.eng.svc account "Perform Volume Maintenance Tasks" and "Lock Pages in Memory"

Disks

This is totally dependant on your environment and everyone will have their own preferences but in an ideal world I tend to go for:

One LUN for SQL Data (D:) Formatted with an Allocation Unit of 64K
One LUN for SQL Logs (E:) Formatted with an Allocation Unit of 64K
One LUN for SQL Backups (G:)
One LUN for SQL TempDB (I:) Formatted with an Allocation Unit of 64K
One LUN for Qourum (Q:)

Format the disks and bring them online on Node1.

Windows Cluster

Open Failover Cluster Manager and Validate the cluster with (Node1 and Node2). Once you are happy and there are no errors create the Cluster using the Windows Cluster IP / Host Name you set aside earlier.

As for Installing SQL Server run the installer on Node 1 choosing (New SQL Server failover cluster installation), once complete run the installer on the second node choosing the (Add node to a SQL Server failover cluster) install option.

Here is a decent enough guide on the process from a SQL perspective with screenshots - link.

One thing not covered in that guide is once done I would also enable Kerberos Authentication by running the below Set SPN commands

setspn -A MSSQLSvc/SQLCLUSTERNAME.DOMAINNAME:1433 DOMAIN\sql.eng.svc

setspn -A MSSQLSvc/SQLCLUSTERNAME.DOMAINNAME DOMAIN\sql.eng.svc

More info on SQL and SPN's here link.

It goes without saying that the failover process should be tested, make sure all resources come up on each node. Do this a few times to get the hang of it.

Hopefully this at least gets you started.


SQL Server 2008 Agent keep stopping for unknown reason

Installation is on RTM of SQL Server 2008 R2. Try upgrading to currently supported Service Pack 3 https://www.microsoft.com/en-ca/download/details.aspx?id=44271


Powershell - SQL Server - connectionstring in loop for multiple Instances

The return value of -1 makes sense when you consider what ExecuteNonQuery() does. It returns the number of rows affected or -1 in case of set nocount on or non-updating queries.

As per documentation,

  For UPDATE, INSERT, and DELETE statements, the return value is the
  number of rows affected by the command. ... For
  all other types of statements, the return value is -1. If a rollback
  occurs, the return value is also -1.

Try .ExecuteReader() instead.


Procedure code

DELIMITER @;

DROP PROCEDURE IF EXISTS GetBranch@;
CREATE PROCEDURE GetBranch()
BEGIN
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (id INT, parent_id INT, UNIQUE (id, parent_id)) ENGINE=Memory;
INSERT INTO temp (id, parent_id)
    SELECT id, COALESCE(parent_id, id) FROM test;
INSERT IGNORE INTO temp (id, parent_id)
    SELECT parent_id, id FROM temp;
REPEAT
    SELECT COUNT(*) INTO @count1
        FROM temp;
    INSERT IGNORE INTO temp
        SELECT t1.id, t2.parent_id
        FROM temp t1, temp t2
        WHERE t2.id = t1.parent_id;
    SELECT COUNT(*) INTO @count2
        FROM temp;
UNTIL @count1 = @count2 END REPEAT;
UPDATE temp
    SET parent_id = NULL
    WHERE id = parent_id;
DELETE t1.*
    FROM temp t1, temp t2
    WHERE t1.id = t2.id
      AND t1.parent_id IS NULL
      AND t2.parent_id IS NOT NULL;
SELECT id, GROUP_CONCAT(parent_id ORDER BY parent_id) branch
    FROM temp
    GROUP BY id
    ORDER BY id;
DROP TABLE temp;
END;
@;

DELIMITER ;

Test code

DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT, name CHAR(1), parent_id INT);
INSERT INTO test VALUES
    (1,'A',NULL),
    (2,'B',1),
    (3,'C',1),
    (4,'D',3),
    (5,'E',4),
    (6,'F',NULL),
    (7,'G',6),
    (8,'H',NULL);

CALL GetBranch;

DROP TABLE test;

Test output

mysql> CALL GetBranch;
+------+---------+
| id   | branch  |
+------+---------+
|    1 | 2,3,4,5 |
|    2 | 1,3,4,5 |
|    3 | 1,2,4,5 |
|    4 | 1,2,3,5 |
|    5 | 1,2,3,4 |
|    6 | 7       |
|    7 | 6       |
|    8 | NULL    |
+------+---------+
8 rows in set (0.31 sec)


How can I use MySQL variables in subqueries?

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows.

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

is translated to

SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\'');

MySQL variable cannot hold table resultset so this is not directly possible. You would have to create a subquery to extract separate values to rows by joining some counter table and parsing the string. Or you may use dynamic sql which allows to take a string and parse it - that way you would define new variable containing entire select with the IN list filled by your current variable, and then prepare  a statement from that string representation (sort of SQL eval). But probably best would be to just use the query direcly as a subquery instead of filling the variable. Newer version should materialize such independent subquery automatically. For good performance on older versions, you might want to create a temporary table instead and use JOIN and not IN.

Just a note - GROUP_CONCAT() is not very safe as there is a limit (variable) to how long string it can return and some results may be lost (with a warning) when the limit is exceeded.


If you are asking whether it is more efficient to use SQL 92 join syntax

SELECT empno, ename, dname
  FROM emp
       JOIN dept USING (deptno)

or to use the older join syntax

SELECT empno, ename, dname
  FROM emp  e,
       dept d
 WHERE e.deptno = d.deptno

the answer is that it doesn't matter.  The optimizer is automatically going to do an internal transformation from the SQL 92 syntax to the older syntax anyway.

If you have performance problems, it is highly unlikely that they are related to your choice of join syntax (though when Oracle first added support for SQL 92 join syntax, there were bugs relating to its ability to transform the SQL into the older style).


List of role permissions including other roles - how can I find out the roles that belong to other roles?

;WITH theRoles (member_principal_id, role_principal_id)
AS
(
  SELECT
   r.member_principal_id,
   r.role_principal_id
  FROM sys.database_role_members r
   UNION ALL
  SELECT
   tr.member_principal_id,
   rm.role_principal_id
  FROM sys.database_role_members rm
            INNER JOIN theRoles tr
                    ON rm.member_principal_id = tr.role_principal_id
)

select  the_role=A.name
       ,A.is_fixed_role
       ,role_member=B.name
       ,B.type_desc
from theRoles tr
        INNER JOIN sys.database_principals A
                ON tr.role_principal_id = A.principal_id
        INNER JOIN sys.database_principals B
                ON tr.member_principal_id = B.principal_id
GROUP BY A.name
       ,A.is_fixed_role
       ,B.name
       ,B.type_desc
order by a.is_fixed_role DESC
        ,a.name

and for testing the script above:

USE [DBA]
GO
CREATE ROLE [role_1]
GO
CREATE ROLE [role_2]
GO
CREATE ROLE [role_3]
GO
CREATE ROLE [role_10]
GO
CREATE ROLE [role_101]
GO
CREATE ROLE [role_1010]
GO

sp_addrolemember @rolename='db_datareader',@membername='role_1'
go
sp_addrolemember @rolename='db_datareader',@membername='role_2'
go
sp_addrolemember @rolename='db_datareader',@membername='role_3'
go
sp_addrolemember @rolename='role_1',@membername='role_10'
go
sp_addrolemember @rolename='role_10',@membername='role_101'
go
sp_addrolemember @rolename='role_10',@membername='role_1010'
go
sp_addrolemember @rolename='role_101',@membername='role_1010'
go


How to convert innodb engine tables to tokudb engine

Instead of using sed, why not let mysql write the conversion script for you?

echo "SET SQL_LOG_BIN = 0;" > Convert_InnoDB_to_TokuDB.sql
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_USERPASS="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_PORT=3306
MYSQL_CONN="-h127.0.0.1 -P${MYSQL_PORT} ${MYSQL_USERPASS}"
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',"
SQLSTMT="${SQLSTMT} table_name,' ENGINE=TokuDB;') InnoDBConversionSQL"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='InnoDB'"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
MYSQL_OPTIONS="--skip-column-names -AN"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" >> Convert_InnoDB_to_TokuDB.sql
less Convert_InnoDB_to_TokuDB.sql

When you are satisfied with the conversion script, then run it

mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < Convert_InnoDB_to_TokuDB.sql

Give it a Try !!!


Do I really Need to Upgrade to SQL Server 2014 SP2 ? -

Your manager will be concerned about possible regressions and new bugs in the service pack (rare, but it has happened in the past) or one of your applications relying on an undefined behaviour that is changed by SP2. These are valid concerns and it is not recommended that you upgrade in production without first at least reading all the official change documentation to make sure none of your applications are likely to be affected. Fully testing your application(s) against the upgrade in another environment is strongly recommended too.

If you have third party applications you might not have the product knowledge to perform the above checks/test, in which case you will need to discus the matter with the application vendor. Hopefully they can give you definitive word on whether they support their application(s) when used with SQL Server 2014sp2.

Do note that without SP2 SQL 2014 becomes officially unsupported in October according to the official lifecycle (https://support.microsoft.com/en-gb/lifecycle/search?alpha=sql%202014) so you are going to want to install SP2 (or upgrade further) before then, meaning you can't avoid this effort completely.

The chance of significant problems between service packs is much lower than between full releases so many people fly by the seat of their pants and just upgrade to the latest service pack without testing. This attitude is somewhat too risky IMO.


How to reduce foreign key performance impact in PostgreSQL

If you are a superuser, you can temporarily disable the system triggers that implement the foreign key checks:

BEGIN;
ALTER TABLE example_2 DISABLE TRIGGER ALL;
INSERT INTO example_2
   SELECT g FROM generate_series(1, 1000) g,
                 generate_series(1, 10000);
ALTER TABLE example_2 ENABLE TRIGGER ALL;
COMMIT;

Dire warning: Do this only if you are 100% certain that your data modifications will not produce inconsistencies. This is dangerous, which is also the reason why only superusers can do it.


How to normalize my db schema for a quiz application?

Instead of option I would use another table option_type with a PK and a name field and use a fk-relationship in challenge_options. You can easily add new kinds of challenges.

I am not sure if I would use is_true / is_correct within challenge_options. I would use a second table with user_id, challenge_options_id and a boolean correct/false.


Since you said you are beginner and is in learning phase I would give you lot of good articles to learn about SQL Server backup.

To start with main question as already mentioned in my comment

  So can I restore 4th one with 1st one. With out the 3rd ?

Differential backup only relates to previous full backup you have taken so NO you cannot restore 4th differential backup if 3rd full backup is lost. Also since as you said the database is in simple recovery model there is no chance of taking transaction log backup.

  [To understand] Whether there any way if it is not simple ?

If your database were in full recovery you could have taken Transaction log backup. The good thing with transaction log backup is that Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.

So you could have restored first full backup and all log backups, in serial order, taken after the full backup. This would hold true even if 3rd full backup is lost.

You must read Understanding Backups in SQL Server there is a Backup myth buster series from PR related to backups you must also read that.

Feel free to ask if any doubts I would add the comments in this answer


That will work fine - however you need to add a delimiter to your trigger:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `admin`.`company_AFTER_UPDATE` AFTER UPDATE ON `company` FOR EACH ROW
 BEGIN
  INSERT INTO AuditCompany
   select *, now() from Company where id = NEW.id;
 END $$
DELIMITER ;

You might gain some performance benefit if you kept the fields in your audit table to a minimum. You would also save space, although these days, that's less of an issue.

And don't forget to have an ON_INSERT and an ON_DELETE trigger also.

[EDIT].

Two further issues have occurred to me:

1) You say that "We wanted to avoid the 'insert into XXX values (NEW.id,....", but you're going to have to keep track of maintenance of the AuditCompany table anyway, which is perhaps a reason to be selective about the fields that you audit.

2) There are Open Source audit plugins in which you might be interested - Percona and McAfee. MariaDB do one - but I don't think that the source is available. Perhaps you can automate some of this process which appears to be what you would like to do?


Need to understand parallel query execution error

Few months ago, I faced similar situation wherein the MAXDOP setting was default and a run away query exhausted all the worker threads.

As Remus pointed out this is called worker thread starvation.

There will be a memory dump created on your server when this condition occured.

If you are on 2008R2+SP1 and up then sys.dm_server_memory_dumps will give you the dump file location as well.

Now back to the problem :

There is 1 scheduler monitor thread per NUMA node and since you have 2 NUMA nodes there will be 2 scheduler monitor threads which are responsible for health checking of all the schedulers every 60 secs for that particular NUMA node while making sure that the scheduler is stuck or not.

Each time a new work request is pulled from the schedulers worker queue, the work processes counter is incremented. So if the scheduler has work request queued and has not processes one of the work requests in 60 sec the scheduler is consider stuck.

Due to a run-away query or extensive parallelism, there arises a condition of worker threads begin exhausted as all the threads are occupied by that single run-away query or excessive prolonged blocking and no work can be done unless that offending process gets killed.

Your best bet is to first tune your Max Degree of Parallelism setting. Default of 0  means SQL Server can use all available CPU’s for parallel processing and there by exhausting all the worker threads.

There are many reasons that can lead to exhaustion of worker threads :

Extensive long blocking chains causing SQL Server to run out of worker threads
Extensive parallelism also leading to exhaustion of worker threads
Extensive wait for any type of "lock" - spinlocks, latches. An orphaned spinlock is an example.

Refer to my answer here that will show you how you can calculate MAXDOP value for your server instance.

Also, highly recommend you to start collecting Wait stats information about your database server instance.


Why can't I view the plan for view creation...?

EXPLAIN shows an execution plan for SELECT queries. In version 5.6, it was extended to include plans for INSERT, UPDATE, DELETE and REPLACE statements.

No version of MySQL (not even the still in development, 5.7) has EXPLAIN for CREATE TABLE or CREATE VIEW.

You can of course run the explain for the select (that defines the view):

EXPLAIN EXTENDED
    select intercom1,phone from fisgen where fid='f105';

or as @jynus suggested (thank you), the more useful:

EXPLAIN EXTENDED
    select * from fisgen_temptable;


Can I Create a Database and Tables in One SQL Command From PHP?

You cannot execute multiple MySQL statements from PHP with mysqli_query, but you can with mysqli_multi_query!


How to find "outer join in the where" syntax in SQL 2005 using cmptlevel 80?

A quick search of StackOverflow would show you use sys.sql_modules or OBJECT_DEFINITION to search for code on SQL Server 2005+. You can simply restore onto SQL Server 2005+ but not run to allow this.

That's if you want fool proof T-SQL. You could concatenate the syscomments rows together if you wish though in SQL Server 2000. I wouldn't personally.

Alternatively, use the SQL Server 2008 upgrade advisor to pick out most (maybe all) possible issues (such as ambiguous ORDER BY clauses which caught me out).

Note the Red Gate SQL Search sensibly only supports SQL Server 2005+


I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages

No there is none. Atleast I dont know as of now :-)

  if there's any other actions that can assist with this

From Paul Randal's blog - One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task).

select * from [your_problem_table] with (index = Index_that_has_large_Deletes)

Is there a possibility for you to partition the table and purge the old partition instead of doing deletes ? FYI .. SQL Server 2016 and up allows you to truncate individual partitions as well.

Also, you can (TEST and implement) - disable ghost clean-up (trace flag 661) and then rebuild index WITH ONLINE = ON option since you are using Enterprise edition.

If  you are using AlwaysON (with async) with replication, make sure to enable Trace flag 1448 - allows the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change.

Make sure to read Deletes that Split Pages and Forwarded Ghosts from Paul White to see if your tables have triggers or LOB columns that would slow down.


You answered your own question, by importing it into a temporary or staging table.

CREATE TABLE #TempTable(
    job_id   INT NOT NULL
    ,part_id  INT NOT NULL
    ,quantity INT NOT NULL)

BULK INSERT #TempTable
FROM 'C:\example.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n') --any other options like FIRSTROW, etc

Then, just use the SUM() aggregate when inserting into your actual table

INSERT INTO MyTable
SELECT
   job_id
   ,part_id
   ,quantity = SUM(quantity)
FROM #TempTable
GROUP BY
   job_id
   ,part_id


What is the y-axis of this Oracle ASH Viewer?

Since the link you included says the data is taken from V$ACTIVE_SESSION_HISTORY, presumably these are the wait event classes. In other words, the diagram shows how many sessions are waiting on each event class or actively executing ("CPU used") at any given time.


postgresql configuring wal_archive directory

"pg_xlog" is the working directory the WAL files are stored in while PostgreSQL itself still might need the WAL files, i.e. to recovery from a power outage or other "soft" crash.  They are removed from there only once PostgreSQL is sure they are no longer needed for that purpose, and once "archive_command" has successfully completed.

We can't tell what happened to your "/wal_archive/", but here is a plausible story:

You had some kind of network filesystem (NFS, CIFS, etc.) mounted using "/wal_archive" as a mount point.  That mount failed and never got re-established.  That means the original filesystem containing your WAL files is still floating out there somewhere, but the computer no longer know how to access it. You see the former /wal_archive mount point as an empty directory because that is how mounts work on Linux.  The user running your postgres server probably can't write to this residual directory, because when the mount was lost so were the permissions.  So the archive_command has been failing with permissions errors, but you never noticed that because you weren't looking in the server log.  It repeatedly tried to archive the WAL file out of pg_xlog, but repeated failed.  Since it failed, it could never remove the file from pg_xlog, until eventually it filled up.  In a panic, you removed some of the files from pg_xlog.  Now it is still trying to archive that one file, but it is now failing for a different reason, the file is no longer in pg_xlog, as demonstrated by the error message "cp: cannot stat 'pg_xlog/000000010000033C00000081'".

Now that you have it working again, either files should be showing up in the /wal_archive/, or there should be errors in the server log file telling you that the archive_command failed.

Note that the archive_command you are using is not perfectly safe.  It does not fsync the file contents just copied into /wal_archive/ before it reports success, so a poorly-timed crash could mean that the just-archived file doesn't actually exist anywhere.  You might want to switch to a canned solutions like "pgbackrest" or "barman", or using streaming replication, to avoid that kind of pitfall.

Also note that any backups you think you have are likely to be invalid, unless you are also taking logical backups (like with pg_dump).  You better take new ones and test them.


The post you found is from 2007. Rather start with the current manual:

  When the PREPARE statement is executed, the specified statement is
  parsed, analyzed, and rewritten. When an EXECUTE command is
  subsequently issued, the prepared statement is planned and executed.
  This division of labor avoids repetitive parse analysis work, while
  allowing the execution plan to depend on the specific parameter values supplied.

And:

  If a prepared statement is executed enough times, the server may
  eventually decide to save and re-use a generic plan rather than
  re-planning each time.

Bold emphasis mine. Read more details in the manual.

I wouldn't know of projects trying to convert prepared statements into executables.


How does the table scan in an UPDATE WHERE predicated on an unindexed column work?

Order of table scan is never guaranteed. Order in which rows are locked is not guaranteed either. In addition, SQL Server has lock escalation, so you can't really say what the engine decides to lock, row, page, or table itself.

Thus, deadlock may happen even if values of @id are different in concurrent sessions, but rows of interest happened to reside on the same pages. In SQL Server 2008 and higher you can add hint ROWLOCK which reduces chances of lock escalation (but again it's not guaranteed). If UPDATE can't acquire lock it waits (LOCK_TIMEOUT specifies how long it's gonna wait ).


SQLite - Get all other rows with any duplicate column value

sqlite> select
   ...>    Username,
   ...>    Field1,
   ...>    Field2,
   ...>    Field3,
   ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field1=u1.field1)  as F1check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field2=u1.field2)  as F2check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field3=u1.field3)  as F2check
   ...> from User u1;
Username    field1      field2      field3      F1check     F2check     F2check
----------  ----------  ----------  ----------  ----------  ----------  ----------
UserNum1    AAABBB      CCCDDD      EEEFFF      UserNum2    UserNum3
UserNum2    AAABBB      IIIJJJ      KKKFFF      UserNum1
UserNum3    LLLMMM      CCCDDD      PPPQQQ                  UserNum1
UserNum4    RRRSSS      TTTUUU      VVVWWW
sqlite>

NOTE: if you have more values that are double, only the first one is show, i.e. if you have also this record:

insert into User values ('Usernum5','AAABBB','XXXYYY','XXXYYY')

then the output will show:

sqlite> select
   ...>    Username,
   ...>    Field1,
   ...>    Field2,
   ...>    Field3,
   ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field1=u1.field1)  as F1check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field2=u1.field2)  as F2check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field3=u1.field3)  as F2check
   ...> from User u1;
Username    field1      field2      field3      F1check     F2check     F2check
----------  ----------  ----------  ----------  ----------  ----------  ----------
UserNum1    AAABBB      CCCDDD      EEEFFF      UserNum2    UserNum3
UserNum2    AAABBB      IIIJJJ      KKKFFF      UserNum1
UserNum3    LLLMMM      CCCDDD      PPPQQQ                  UserNum1
UserNum4    RRRSSS      TTTUUU      VVVWWW
Usernum5    AAABBB      XXXYYY      XXXYYY      UserNum1
sqlite>

Where you see (on the line 'Usernum5') only a reference to Usernum1, and not to Usernum2.


You might need an intermediate table to store this relationship, as it's possible that a student has more than one parent/gaurdian (a mother and a father) and it's possible that a parent/gaurdian has more than on student.

student
-------
  id

gaurdian
--------
  id

student_gaurdian
----------------
  student_id
  gaurdian_id

The table student_gaurdian can store all the relations between students and gaurdians, and allows many-to-many relations.

RE: your new question

Having two tables with keys to each other looks a little weird to me, but if you really are having performance problems and this relieves them, then it's fine.


Status of PostgreSQL replication after pause and resume

Unless I misunderstood your question, SELECT extract(epoch from (now() - pg_last_xact_replay_timestamp())) AS time_lag; should give you what you are looking for - the lag in seconds, not seconds portion of the lag as your original query returns.  You can achieve the same with a number of extract (seconds ...) + extract(minutes ...)*60 + extract (hours ...) *60*24 +...


How can streaming replication be kept lagged by a certain 'delay' time?

Streaming replication cannot do this (but see Craig Ringer's comment above about a possibly upcoming feature).

The good news is, if you make the standby getting the WAL files and apply them, you can apply an arbitrary delay. We push the WALs to a NFS volume and the restore command copies them from there to the local WAL dir, checking if the files are old enough.  Then the normal restore process applies them as usual.

Of course, the delay will match the preset value only approximately, as a single WAL file may contain changes from quite different time intervals, depending on the master's workload.

What you propose with pausing the restore process depending on the value returned by pg_last_xact_replay_timestamp() may work if run from a scheduled job (probably you want to schedule it to run quite frequently).  So far I haven't seen such a setup and am wondering if it can be a production-grade solution.


It's an internal method, presumably - from the name - used to lock an object based on its object_id and ensure that the executing user has the rights to make further changes to the object. This locking is required to do things like adding/dropping properties, hidden columns, etc., and to ensure that the object can't be dropped or altered by others in the meantime (mere mortals tend to do this by starting a transaction and locking the table through some indirect method, since there is no LOCK TABLE command - but clearly there is internal plumbing to help with system operations).

If you look at any of the procedures where this is used (there is a large list):

SELECT N'EXEC sp_helptext ''sys.' + QUOTENAME(name) + N''';'
FROM sys.all_objects
WHERE [schema_id] = 4
  AND LOWER(OBJECT_DEFINITION([object_id])) LIKE N'%lockmatchid%';

You'll see preceding comments like:

-- Lock the table schema and check permissions

-- Share lock default so it cannot be dropped

-- Since binding a default is a schema change, update schema count
--  for the object in the sysobjects table.

-- Ex-lock and check permission

-- Share Lock rule so that it cannot be dropped

-- Check that table and column exist

-- RESOLVE OBJECT NAME

-- Get exclusive object lock upfront so subsequent %%ColumnEx can proceed

-- Acquire sch-M lock up-front on the published object

-- LOCK PROC & CHECK PERMISSION --

-- LOCK, CHECK PERMISSION, BUMP SCHEMA FOR RECOMPILE  --

-- obtain owner-qual object name

-- Re-acquire schema mod lock to make the code more resilient to changes
-- although this must have been done inside sp_Mrepl_schema

-- LOCK TABLE, CHECK STANDARD TABLE-DDL PERMISSIONS

-- Since binding a default is a schema change,
--  update schema count for the object in the sysobjects table.

-- Update schema count for the object in the sysobjects table.

-- Verify that table exists

(And people say commenting code is a waste of time.)

There also seems to be LockExclusiveMatchID method, though the Exclusive argument seems to have later been added to LockMatchID, which made the exclusive method obsolete.

Not sure how much more specific you need to this to be - if you want to track down where this a high occurrence of these, you need to look at the outer calls, not this specific statement. For example, do you have an application that is renaming objects a lot, or calling sp_autostats directly, changing replication, or anything else you can infer from the list generated above? You'll have to figure out which procedures are getting called to help determine the why. And what you'll do about it (if you can even determine that there is a "problem") won't have anything to do with LockMatchID specifically.


How do triggers and check constraints relate to maitaining database rules

You haven't specified a tag in your question so I'll assume that you're asking a generic question despite the SQLite reference (if you're asking a SQLite-specific question, please update your tags).

Generally, I'd suggest using a trigger when you can't use a check constraint.  Different database engines have different levels of support for what you can do in a check constraint.  I don't know of any database engine, for example, that would currently allow you to define a check constraint that references data in a different table or data in a different row of the same table.

If your database engine allows you to define the email format rule in a check constraint, I would use a check constraint.  If your email validation rules were more complex, however, or you were going to have the same validation rules in many places, you may want to encapsulate them in a function.  Once you do that, most database engines are going to require you to use a trigger.  For example, you might want to perform more detailed validations that involve checking to see if the domain is valid, whether the email domain on the row in the leads table was not already in the customer table, etc.  At that point, you're doing something more complicated than what a check constraint can manage.


OK we found why there is a discrepancy.
We were able to list the collections on the secondary node with
db.getMongo().setSlaveOk()
show collections

From there, I realized the extra collection on the primary was system.profile (I'd forgotten I'd turned on the profiler) and it's 517 documents accounted for the different object counts from db.stats()


How to stop decrypting data after opening master key in SQL Server?

The command you're looking for is CLOSE MASTER KEY;

From docs.microsoft.com:

  This statement reverses the operation performed by OPEN MASTER KEY. CLOSE MASTER KEY only succeeds when the database master key was opened in the current session by using the OPEN MASTER KEY statement.


How do I update a table with the aggregated values (min, max) of a related table, fast?

MySQL syntax:

UPDATE
      w
  JOIN
      ( SELECT
              n_in_w.w_id,
              MIN(n.xyz) AS min_n_xyz,
              MAX(n.xyz) AS max_n_xyz,
              MIN(n.abc) AS min_n_abc,
              MAX(n.abc) AS max_n_abc
        FROM
              n_in_w
          JOIN
              n
            ON
              n.id = n_in_w.n_id
        GROUP BY
              n_in_w.w_id
      ) nw
    ON
      nw.w_id = w.id
SET
      w.min_xyz = nw.min_n_xyz,
      w.max_xyz = nw.max_n_xyz,
      w.min_abc = nw.min_n_abc,
      w.max_abc = nw.max_n_abc;


Try adding index on combincation of tableB (col2, ,col1) because selection is happening on col2. This should work.


The severity value for a unique key violation error is 14, not 16. You need to set your alert to fire on severity 14 instead. You can see the severity returned using the below code:

CREATE TABLE Test123 (Num INT)
GO

INSERT INTO Test123 (Num)
VALUES (1)
GO

CREATE UNIQUE INDEX UQ123 ON Test123(Num)
GO

BEGIN TRY
    INSERT INTO Test123 (Num)
    VALUES (1)
    DROP TABLE Test123
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS Message
    DROP TABLE Test123
END CATCH
GO

Alternatively, change your alert to fire on error number 2601 (the error number for duplicate key value errors).

EDIT: The above is valid for a unique index. For a unique constraint, the severity is still 14, but the error number is 2627.


Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows

As there's no information available about the cause and cure, sadly this will be a closed thread with no resolution.

Sadness all around.


We noticed that there were large number of inserts into the error log table coupled with info logging. This put a lot of stress on the database and resulted in significant performance issues. We disabled info logging at the application level which eliminated the performance bottleneck.


Consider batching your commits.
A batch size of 1024 is a good starting size.
Change batch sizes until you reach your optimum throughput.


Yes It's possible. As per MySQL documentation here
After connecting to the server as root, you can add new accounts. The following example uses CREATE USER and GRANT statements to set up four accounts:

For example

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by those statements have the following properties:

Two accounts have a user name of finley. Both are superuser accounts
with full privileges to do anything. The 'finley'@'localhost'
account can be used only when connecting from the local host. The
'finley'@'%' account uses the '%' wildcard for the host part, so it
can be used to connect from any host.

The 'finley'@'localhost' account is necessary if there is an
anonymous-user account for localhost. Without the
'finley'@'localhost' account, that anonymous-user account takes
precedence when finley connects from the local host and finley is
treated as an anonymous user. The reason for this is that the
anonymous-user account has a more specific Host column value than the
'finley'@'%' account and thus comes earlier in the user table sort
order. (user table sorting is discussed in
here.)
The 'admin'@'localhost' account can be used only by admin to connect
from the local host. It is granted the RELOAD and PROCESS
administrative privileges. These privileges enable the admin user to
execute the mysqladmin reload, mysqladmin refresh, and mysqladmin
flush-xxx commands, as well as mysqladmin processlist . No privileges
are granted for accessing any databases. You could add such
privileges using GRANT statements.
The 'dummy'@'localhost' account has no password (which is insecure
and not recommended). This account can be used only to connect from
the local host. No privileges are granted. It is assumed that you
will grant specific privileges to the account using GRANT statements.


How to read/write from replicated secondary in case primary went down in SQL Server?

Transactional replication is not the correct technology for the requirement you've described. What you're after is High Availability/Disaster Recovery (HADR) protection for the SQL Server, and transactional replication is not suitable or intended for this.

Brent Ozar has a High Availability Planning Worksheet in his First Responder toolkit. I would recommend you take a look at that to help you determine your requirements and design an appropriate architecture.

To keep it simple, below are your main options:

AlwaysOn Availability Groups
SQL Failover Cluster Instance
Log Shipping
Database Mirroring
VM/SAN replication

Which option you choose depends on your current environment (version and edition of SQL Server are important), HADR requirements, licensing, budget and in-house skills to design, implement and manage the chosen solution.

For example, if you are running SQL Server 2014 or earlier, you need Enterprise Edition to run Availability Groups, but you can use Database Mirroring (now deprecated).

Running Availability Groups or Failover Cluster Instances requires a Windows Server Failover Cluster installed on the SQL Server nodes to provide HADR - do you have skills in-house to build and manage the cluster?

If your RPO and RTO requirements are not substantial, or you're running an older version of SQL Server, then Log Shipping might be a suitable solution. You can, with some effort, implement a solution to allow log shipping to 'write-back' from the secondary to the principal server after failing over.

Lastly, if you do the discovery work and you don't have the budget for your required RPO/RTO, your infrastructure team might be able to leverage SAN replication and VM DR technology (VMWare's SRM, Azure ASR, Hyper-V Replication etc) to provide protection for your SQL Server environment.

If you can, I would suggest engaging a skilled consultant who can perform the assessment and discovery work to provide an appropriate architecture meeting all your constraints and will be able to help build and deploy the solution. You should also look at training options to get up to speed on HADR technologies so you can adequately manage the system.

Some links to help you:

AlwaysOn Availability Groups
Failover Cluster Instances
Log Shipping
Database Mirroring


Are SQL Server's Date values stored as a single int?

Just for fun, let's see what's on the page.

CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.experiment(d DATE NOT NULL);
CREATE CLUSTERED INDEX x ON dbo.experiment(d);
INSERT dbo.experiment SELECT SYSDATETIME();

DBCC IND('floob', 'dbo.experiment', 1);

Partial results (will differ on your system a bit):

----
13

PagePID iam_chain_type PageType
------- -------------- --------
229     In-row data    10
228     In-row data    1

So let's look at page 228:

DBCC TRACEON(3604, -1);
DBCC PAGE(13, 1, 228, 3);

Partial results. We see that the value for d occupies 3 bytes. If we look at the memory dump for the row, we see a hex value for the internal storage component: c8350b02 - what could that be?

SELECT DATEDIFF(DAY, CONVERT(DATE, '0001-01-01'), CONVERT(DATE, '2012-06-11'));

Result:

734664

So:

SELECT CONVERT(VARBINARY(8), 734664);

Result:

0x000B35C8

(Looks amazingly similar to what's on the page, just with bits flipped in the opposite order?)

Then:

SELECT DATEADD(DAY, CONVERT(INT, 0x000B35C8), CONVERT(DATE, '0001-01-01'))

Result:

2012-06-11


MAX worker thread in SQL server 2012/14/16

why max worker threads showing 2994 when i have 80 logical processors […]

It seems there is a documentation error when the item was copied over from MSDN to Docs. What you see in Dorr's post is correct and is applicable back to 2012 SP4 + latest CUs. I'll make the required documentation changes. The documentation has been updated.

  Coming back to that system health event status shows WARNING for below no […]

The reason you have a warning is because you have pendingTasks="1". This could indicate an issue with a scheduler and should be investigated.

  I see pending task count varying sometimes 2, 4 , 8 or even higher.

In the same Docs article that you linked, there is a query that can be used to track tasks and their status. It'd be interesting to see if it's only tasks on a specific scheduler, only login tasks, etc.

SELECT  s.session_id, r.command, r.status,
   r.wait_type, r.scheduler_id, w.worker_address,
   w.is_preemptive, w.state, t.task_state,
   t.session_id, t.exec_context_id, t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
   ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
   ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
   ON t.worker_address = w.worker_address
WHERE t.task_state = N'PENDING'


global unique identifier for 2 tables in relational sql

If your requirement is to have global Unique identified for across Entities, you can go for UUID. For more details refer here


I have a bytea field with the value \x11AA22BB. The comparison to a
  byte value of AA should be true

It seems you're looking for the position function, knowing that it will return 0 if the byte is not contained in the string:

select position('\xAA'::bytea in '\x11AA22BB') > 0 AS contained;

 contained
-----------
 t

If the byte value is stored in an integer, set_byte can be used to place it into a bytea:

select position(set_byte('\x00', 0, 170) in '\x11AA22BB') > 0;


Which columns to place the clustered index

Here's where the trickiness comes in. If the EventType is 'Dual', then uniqueness >is by Season, EventDate, SchoolId, OpponentId. If the EventType is 'Tournament', >Then uniqueness is on Season, EventDate, SchoolId, EventName (OpponentId will be >null).

I'd look at what's common between these two queries to determine what columns to use in the Clustered Index. Remember, the Clustered index is effectively how the data is arranged on the disk, so it should have a structure similar to the hierarchy that queries against the table will reference. If every query, or the most critical ones, all look at Season, SchoolId and EventDate regardless of EventType, then I'd use those in the Clustered index. Additional Non-clustered covering indexes can be created for specific queries (around EventType or OpponentId, for example) which INCLUDE the columns returned to prevent key lookups.

Clustered indexes do not need to be unique, they need to specify how the data should be arranged so that queries can quickly seek to the desired location. The primary key should be unique, but it doesn't have to be a clustered index. If it's just a reference for a row value that doesn't have any useful information that queries will use (i.e. you never join on it), then it should not be part of the clustered index.

For the second example, I would probably use Season, Week, and WrestlerId if those are most queried. The order should be in the logical heirarchy that the data is queried in...which brings me to my next point:

All this advice is contingent on the execution plans of your queries.

Always, ALWAYS, use actual execution information to determine what the best indexes for your queries are. Got some table scan issues? Implement a clustered index. Got a Clustered index scan slowing you down? Look into implementing a non-clustered covering index for that query based on the columns it's looking up (often suggested by the DB engine itself). Got key lookup? Try using an INCLUDES on the affected index with the looked-up columns. Indexing hypothetically, before you're super familiar with the DB engine, often leads to wasted time and rework. I'm not saying to shut your brain off and just blindly follow the query plan's suggested indexes, but instead use the query plan to build your indexing strategy rather than trying to preempt it, or second guess it.


Firebird database recovery and protection

Incremental backups - using nback utility in FB 2.x

Back-up is very fast (just dumping of changing pages), can use cascading (database -> monthly large snapshots -> daily deltas from last monthly -> hourls delta form last daily).

However it does not optimize (by recreating) the database. And it does not detect database errors other than top-level, errors of pages allocation (like "orphane page")

OTOH the pages snapshotted are mostly intact, so in case of partially corrupt database they might still have a manually salvageable data. If the corruption would be noticed quickly.

In a sense, that amounts to safe and incremental copying of he database file(s), wit hall the cons and pros.

Usual backup via "gbak"

Reads data as a usual SNAPSHOT transaction, thus db errors would effect it. Some db erors would manifest it in read errors (like if DBA changed column type in a way incompatible with data), but other might result in some data being "invisible" and skipped.

Backup file is stripped of fast-access metadata and geta a lot smaller, which is good for archives (example: 1.1GB of raw database -> 380 MB FBK -> ~700 MB after restore).

In FB 2.x GBak is known to work considerably slower via TCP/IP than via Firebird Service Manager connection. It is told to be amended in forecoming FB 3.

Restore is basically recreating database, so it is slow. But it optimizes the database internal layout and saves some space (no more half-empty pages in the middle of the file).

Due to Firebird being very liberal in online (during active operations of users) scheme change (the safe approach was enforced in 2.0 but undone in 2.1 after uproar), the traditional backup might be "unrestorable", so the attempt at restoring a FBK file into a spare free disk is a must. Until you proven you can restore that backup, you may consider you don't have it.

Tangential idea is Garbage Collection. Usually database have "peak hours" for example heavily used in day and almost unused at night. Sometimes DBA turn off garbage collection and reserve a lot of free space so during the day the database might grow almost uncontrollably. Then at night gbak is run not only to make a copy, but to enforce garbage collection at last.
There also is a built-in redundancy: SHADOW SERVERS implemented back in Borland days. It does not protect from malicious users, virus, program or server bugs, etc. But in case one server crashes like OS or hardware fault, another one is ready to take his mission instantly.

OTOH is the main and shadow servers were in different networks (offices, cities, countries) and the link disappears between them, then one of the networks would see it as crash of the main server and another would see it as a crash of shadow server. When the link would be repaired, the databases would have new different conflicting data entered by users.

This moves us to yet another idea of database replication. IOW the system in general might be designed to the idea tat there is more than single database/server, but they still have the same scheme and should periodically exchange data changes. That is a whole different topic, but it might be to some extent seen as a somewhat partial substitute to backups or crash defense.

PS. additionally you have to learn the difference between SuperServer (targeted at small installations) and Classic/SuperClassic servers. For running 24/7 the second options would be preferable, since frequently the server instances would be shut down after user disconnects. So while "server" as a concept keeps running 24/7, the actual executable programs of it get closed and restarted, easening at potential problems like memory leaks in server or UDFs. OTOH Classic server is more vulnerable to cache synchronization issues like in case of crash during Garbage Collection or attempts at metadata (scheme) changes while users are working.

In FB 3.x they promise to integrate those two approaches to make it a kind of sliding scale options in firebird.conf


Yes you can do that. Before hand make the log reader job disabled. This will make sure the jobs are not looking for the database.

For more clarity look at the below link :

MSDN : Replication and Single user Mode


MSSQL Agent Task Failing - Stored Procedure

I have put in "Advanced", "Run as user" as SQLUserName

This is not the option to use in your case.

Using this option you can specify the user, not login, to run your sp. However, when you connect to linked server, it's not a database user, but server login that is used, it's "login's current security context" that will be used.

So in your case all depends on the job's owner. If it's sysadmin, the job is executed under Windows account of your SQL Server Agent, and if it's not sysadmin, the job is executing using job's owner's login.

From your error it seems that your job's owner is sysadmin, as domain\servename$ is used. This is because your Agent is running under Local System or Network Service that when going on the network use your pc's account, domain\PCname$.

To fix you problem you can either change job owner to some login that has access to linked server or change SQL Server Agent's service account to domain account that should be mapped on linked server.


SQL Server - "The connection must be in an opened state."

This is a very interesting error. Please note there is no error code of any kind associated with it. This means it has to do with SSMS rather than SQL Server.

And this specific error is telling you that the SSMS no longer has active connection to the server. It is possible that this might be the case of faulty switch or similar, but I doubt it.

My recommendation is to check settings pertaining to power saving options of the computer you use in general or your NIC in particular.

In other words - Windows powers off you network adapter while on idle to save power. Might want to check adaper settings as well - my had all sorts of options like "EEE" or "Green Gigabit" enabled...

Addendum

Having similar issues lately when working via wireless connection we - my IT department and I - tracked the issue down to wireless adapter on my laptop and speculated that reason might be some undocumented or inaccessible setting linked to power consumption. It improved only after relentlessly updating drivers to latest possible version using both Intel and Dell updating tools.

Until then same symptoms: connection says it's live, but running queries randomly generated above error. Windows says connection is live, SSMS concurs, no - literally: zero - entries in Windows logs even remotely touching on networking. All is well when using wired LAN.


in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?

As for now
We plan to also add CACHE INDEX for Aria tables if there is a need for this.

And as you can see that it is written in https://mariadb.com/kb/en/cache-index/ so that it would be more like the MyISAM, but as development and theory grows in time, we will see and wait, what they actually want to do qand implement.


Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

This is the answer:

  There are a lot (thousands) of purchases per customer.

DISTINCT ON is fast for few purchases per customer. See:

Select first row in each GROUP BY group?

This should be much faster:

SELECT c.id AS customer_id, p.id AS purchase_id
FROM   customers c
LEFT   JOIN LATERAL (
   SELECT p.id
   FROM   purchases p
   WHERE  p.customer_id = c.id
   ORDER  BY p.id DESC
   LIMIT  1
   ) p ON true;

Subtle difference: every customer is in the result, even with no purchase at all.

Your index "purchases_customer_id_id" btree (customer_id, id) is good for this. An index on (customer_id, id DESC) would be even slightly better.

See:

Optimize GROUP BY query to retrieve latest row per user

Aside 1:

The 1st plan shows rows=823, the 2nd rows=755. Suggests you have purchases.customer_id that have no match in table customers, which typically shouldn't be. Add a FK constraint from purchases.customer_id to customers.id and make purchases.customer_id NOT NULL to enforce referential integrity.

Aside 2:

Lots of Heap Fetches at the end of each query plan. Are you vacuuming enough. See:

How does PostgreSQL perform ORDER BY with a b-tree index on the field?


How to see resumable timeout of another session in oracle?

DBA_RESUMABLE

  ...

  TIMEOUT | NUMBER | Timeout of the resumable statement

  ...

Session 1:

SQL> select sys_context('userenv', 'sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
53

SQL> alter session enable resumable timeout 600 name 'hello world';

Session altered.

Session 2:

SQL> select session_id, timeout, name from dba_resumable;

SESSION_ID    TIMEOUT NAME
---------- ---------- ------------------------------
        53        600 hello world


archive command repeatly failing for one particular file

If the file is not in pg_xlog, but there is a .ready file in archive_status, then somebody must have removed that file, which is why archival is failing.

Temporarily set archive_command = '/bin/true' until the view pg_stat_archiver indicates that PostgreSQL is happy again, then set the parameter back to its original value.

Perform a new base backup immediately after that.

You won't be able to recover across the point where you have no WAL archives.

Try to find out which of your administrators is stupid enough to delete files from the data directory and send him or her to a PostgreSQL course.


If the data is currently being updated on the replica, it could be due to a trigger (show triggers from [database];) or a replication rewrite flag (look at the my.cnf for master and slave).


Performance of single vs multiple column unique constraint

Your combinations in order of typical performance:

1. > 2. > 4. ( > 3.)

3. is invalid. If rows are only unique per (code, class_id), the lookup by code alone can return multiple rows and is different from the rest.

2. is pointless. If code is unique, there is no point in adding another predicate on class_id - except to verify that a given code actually belongs to a given class_id (and get no row otherwise).

Only 1. and 4. make sense and I would go with 1., of course. Unless you have additional requirements for the values of code, it's much more efficient to have one unique column. You could also make it the PK. Queries are simpler (one predicate instead of two), the (automatically created) unique index is potentially smaller (the most important factor here!), the lookup is typically slightly faster.

UPDATEs are also potentially more expensive for 2., where more columns trigger index updates. An UPDATE changing only code_id is cheaper for 1..

Your test result for 1. is counter-intuitive, maybe an artifact of your specific setup. Maybe you didn't prewarm the cache? Or some other random factor. It's pretty obvious from the EXPLAIN output: the only difference between 1. and 2. is the additional Filter: (class_id = 1) step. Nothing to gain here, you can only lose (even if very little in this case). 2. is typically a bit slower than 1. And 4. is also typically a bit slower than 1.


Why not just use one of the scripts which are out there and available for free.  The current reigning version of the one posted on sqlfool.com.


PSQL not executing SQL command - Powershell

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

You invoke psql twice here. The first enters interctive mode as no stdin was supplied, no -f filename parameter was supplied, and there's no -c command string.

So the script never gets to the second psql invocation. Put the parameters on one line.

Additionally:

You want WITH (CSV, HEADER) not CSV DELIMITER ','
{'SELECT * FROM myTable';} is complete nonsense. Simply use COPY mytable TO ... or if you need a subquery, use COPY (SELECT * FROM mytable) TO ...
If you're trying to do a line continuation, use  a backtick ` at the end of the line.
Unless you intend to use server-side paths, with the file permissions of the PostgreSQL server, you probably want \copy not COPY

(I'm assuming that --% stops Powershell from looking for Powershell flags and options in the rest of the command; I haven't seen it before).

I think you're looking for something more like:

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB -c "\copy (SELECT * FROM myTable) TO    'C:\Users\e\Desktop\test1.csv' WITH (FORMAT CSV, HEADER);"


Setting max server memory in SQL Server 2014

Aye aye. You are confusing megabytes and gigabytes (MB is even in the configuration name!).

EXEC sys.sp_configure N'max server memory (MB)', N'96' --<-- this is 96 MB
GO

EXEC sys.sp_configure N'max server memory (MB)', N'98304' --<-- this is ~96 GB
GO


SQL Server benchmarking HammerDb TPM = 0

The error is in the output as "SQL Server returned non-numeric transaction count data".  TPM is the same as the transaction count in the SSMS activity monitor ie "Batches/sec". NOPM is queried directly from the schema under test. (TPM is a database specific metric NOPM is database independent). It appears that workload is running OK but SQL Server is not returning transaction counter data. The first thing to check is whether SQL Server has been started with the "-X" option. This disables all performance statistics and would result in this effect. Secondly run the following SQL:

select cntr_value from sys.dm_os_performance_counters where counter_name = 'Batch Requests/sec'

This is what HammerDB runs (you can see it i nthe source code) - when this query works HammerDB should be able to monitor and report transactions.

For saving details more information is required.


Converting Non Unicode string to Unicode string SSIS

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.

Usage

In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.

Instead of

SELECT
    E.BusinessEntityID
,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
FROM
    HumanResources.Employee AS E

use a query such as

SELECT
    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
FROM
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.

Benefits

Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...


Will these queries return the same results?

No, you don't have to run the first query, get the results, build the second one dynamically and then run it.

And you don't need this complex second query either. The simple one will suffice:

SELECT
    friend_id, user_id, approved
FROM
    default_friend
WHERE
    user_id = 1
  AND
    approved = 0 ;

And you don't really need the user_id and the approved in the SELECT list either. They are going to be 1 and 0 in all rows, aren't they?

So, the question is. Why don't you just keep the original, 1st query, adding only the additional condition (approved = 0) ?:

SELECT
    friend_id
FROM
    default_friend
WHERE
    user_id = 1
  AND
    approved = 0 ;


Shrink transaction log while transaction in progress

You do not have to shrink the log (ie. make the file smaller) but instead you have to truncate the log (ie. allow the file to be reused internally, so it doesn't grow to start with). How to achieve this depends on your database recovery model. Under SIMPLE recovery model the truncation occurs automatically. Under other models (FULL or BULK-LOGGED) the truncation occurs basically when you successfully run a BACKUP LOG statement (I'm omitting some details). So, first, figure out what is the recovery model of your target database, see View or Change the Recovery Model of a Database.

For an in depth look, see How to shrink the SQL Server log. The article explains the circular nature of the log and why often simply issuing DBCC SHRINKFILE results in no reduction of the file (read the article to understand why).


Postgres: Performance Issue: Query on enormous data fails to use index

Your costs are on your WHERE predicate for ts.status.
You can see in the explain it's doing a Seq Scan for 400,951 rows with a cost of 10,628,176.10.
While having an index that is based on the ORDER BY fields in a query can help performance with the sorting, generally you should focus more on indexing based on your predicates (JOIN, WHERE, and HAVING clauses) because it won't have to do a Sequential Scan rather it can use the index to scan or seek even.
In this case if you had an index on the status column instead, your performance would likely be better (regardless sorting on your ORDER BY clause).
The difference in performance you're currently seeing is probably a difference in query plan between when you use and remove the ORDER BY clause that happens to be more efficient altogether. If you ran an explain for the query without the ORDER BY clause, I'm sure you'd see different operations occuring. But again, proper indexing on the status field should give you consistency in performance, either way.


Retrieving 10 records from multiple tables

With a slight waste of fetched rows, you can:

SELECT * FROM (
  SELECT * FROM table2 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 3
  UNION ALL
  SELECT * FROM table1 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 10
) select_both
LIMIT 10
;

At the worst case we will be reading 13 rows, then filtering them down to 10.


Query: How to get both Question and multi Answers in one query

You could make it with a simple JOIN, the question is: Are you going to have others tables?. That could make that you have to redo your design. I suggest EAV if you're going to use more tables with some configuration between Questions and Answers.

With your actual design the query would be like:

mysql> SELECT
    -> q.QuestionID,
    -> q.QuestionContent,
    ->     a.AnswerID,
    ->     a.ReplyContent
    -> FROM test.Question AS q
    -> JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
    -> WHERE q.QuestionID=1;
+------------+------------------------------+----------+--------------+
| QuestionID | QuestionContent              | AnswerID | ReplyContent |
+------------+------------------------------+----------+--------------+
|          1 | How many indexes may I have? |        1 | Use 6        |
|          1 | How many indexes may I have? |        2 | Use 1        |
|          1 | How many indexes may I have? |        3 | A or B       |
+------------+------------------------------+----------+--------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT
    -> q.QuestionID,
    -> q.QuestionContent,
    ->     a.AnswerID,
    ->     a.ReplyContent
    -> FROM test.Question AS q
    -> JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
    -> WHERE q.QuestionID=1;
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys           | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | q     | const | PRIMARY                 | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | a     | ALL   | fk_Answer_Question1_idx | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql>

Related:

To EAV or not to EAV?
Efficient query for an advanced EAV model
Eav MySQL Search


Found the problem.

The view already existed in both the original library and the target library, but with different system names.

Adding this line fixed the problem:

SYSTEM_NAME_OPTION => '0',


How to create a new table from other tables that are related to each other?

A sample:

USE test

-- Create source tables ...
CREATE TABLE table_1 (id INT, field_1 INT);
CREATE TABLE table_2 (id INT, field_2 INT);
CREATE TABLE table_3 (field_1 INT, field_3 INT);
CREATE TABLE table_4 (field_1 INT, field_4 INT);

-- ... and fill some sample data
INSERT INTO table_1 VALUES (1,1), (2,2);
INSERT INTO table_2 VALUES (1,11), (2,22);
INSERT INTO table_3 VALUES (1,111), (2,222);
INSERT INTO table_4 VALUES (1,1111), (2,2222);

-- Build SQL statement text ...
SELECT CONCAT('CREATE TABLE IF NOT EXISTS new_table AS',
              ' SELECT ', GROUP_CONCAT(fields),
              ' FROM table_1',
              ' NATURAL JOIN table_2',
              ' NATURAL JOIN table_3',
              ' NATURAL JOIN table_4;') INTO @sql
FROM ( SELECT CONCAT(MIN(table_name),'.',column_name) fields
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'test'
       AND table_name IN ('table_1', 'table_2', 'table_3', 'table_4')
       GROUP BY column_name ) fields;

-- ... and execute it ...
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

-- ... then check the result
SELECT * FROM new_table;

    mysql> SELECT * FROM new_table;
    +------+---------+---------+---------+---------+
    | id   | field_1 | field_2 | field_3 | field_4 |
    +------+---------+---------+---------+---------+
    |    1 |       1 |      11 |     111 |    1111 |
    |    2 |       2 |      22 |     222 |    2222 |
    +------+---------+---------+---------+---------+
    2 rows in set (0.00 sec)

-- Drop sample tables
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
DROP TABLE new_table;


Find the second highest value from a subquery or derived table

Write your join properly and just limit the result set after sorting if it's enough for you to get the second value, no matter if there are two or more courses with the same sum of coursefees. If you need to consider this, it becomes more complicated. Just let me know...

SELECT SUM(coursefees) AS sum_coursefees
FROM enrollment e INNER JOIN batch b ON e.batchid = b.batchid
INNER JOIN course c ON b.courseid = c.courseid
GROUP BY c.courseid
ORDER BY sum_coursefees DESC
LIMIT 1 OFFSET 1;

UPDATE:

That should do it:

SELECT * FROM (
  SELECT
  c.courseid,
  @rank := IF(@prev = SUM(coursefees), @rank, @rank + 1) AS rank,
  @prev := SUM(coursefees) AS sum_coursefees
  FROM enrollment e
  INNER JOIN batch b ON e.batchid = b.batchid
  INNER JOIN course c ON b.courseid = c.courseid
  , (SELECT @rank:=0, @prev:=NULL) var_init
  GROUP BY c.courseid
  ORDER BY sum_coursefees DESC
) sq
WHERE rank = 2;

Note though, that the order in the select clause is important. Do not switch columns.


How to run MongoDB as a non root user in Linux?

You mention that you used the rpm installer for installing MongoDB on the Amazon Linux AMI. When you do this, the mongod user is created on the system and all of the appropriate files are created and owned by the mongod user. When starting MongoDB installed by the rpm, the command must either be run by root or run using sudo. This is because the init script must execute a change user command so the mongod process can be forked as a child of the mongod user.

Failure to use root or sudo to start the service results in the errors you quoted above.

/etc/init.d/mongod: line 58: ulimit: open files: cannot modify limit: Operation not permitted
/etc/init.d/mongod: line 60: ulimit: max user processes: cannot modify limit: Operation not permitted
Starting mongod: runuser: may not be used by non-root users

So, in order to run as a service, you must use sudo or root. There is no simple way around this except granting the mongod user a shell, assigning it a password, logging in as that user, then running the service. This will still result in errors attempting to modify the ulimits and is generally not recommended.

To start MongoDB without running it as a service, since you used the rpm, the mongod binary should be in your path, so the following should work

mongod --dbpath /path/to/data --logpath /path/to/mongod.log --fork

Note that doing this will run it as the currently logged in user, that means all of the data files as well as mongod.log must be writable by that user. Ideally, these files should be owned by that user.


Multiline UDF or Stored Procedure for SSIS Data Source

It will always depend on the logic being implemented but I'd consider at least the following items when choosing between the two object types as a SSIS data source...

Are you planning to modify data in a permanent table as part of the logic? You may be restricted to using a stored procedure if you need to perform interim DML when preparing the data source.
Will your final result come from a SELECT from a temp table? It becomes more tedious to use a stored procedure as a data source as the temp table isn't created until sproc runtime; SSIS therefore can't see the table structure for column mappings in the data flow. Workarounds include using retaining connections at package level, utilizing global temp tables and delaying/bypassing validation of the package and affected data flows. It's a bit of headache compared to a table-valued UDF where you can define the output table structure up-front.


PostgreSQL works differently. As soon as the data are committed on the single primary database, the write is effective. If you use synchronous replication, that commit waits until the required standby servers (configured by synchronous_standby_names) have received the information.


Transaction Identifier - possible with MySQL?

I doubt if there is a transaction id.  But here are some "good practices":

Turn off auto-reconnect.  (This is a parameter on certain APIs when establishing the connection.)  A disconnect terminates transactions and rolls them back.  With auto-reconnect OFF, you will get an error on whatever happens next.
Check for errors after ever SQL statement.
Avoid spaghetti code.  Arrange the code so that it is obvious that there is no way to sneak out and do a COMMIT or ROLLBACK.  I like to have the START TRANSACTION at the beginning of a subroutine and the COMMIT and ROLLBACK near the end.  Then make sure that any subroutines it calls assume that they are inside a transaction.

This might help you discover if you have started another transaction in your connection:

SHOW SESSION STATUS LIKE 'Com_begin';

or

SELECT  Variable_Value
    FROM  information_schema.SESSION_STATUS
    WHERE  Variable_name = 'Com_begin';

Collect that right after doing the BEGIN or START TRANSACTION; collect it again just before your COMMIT to see if it has changed.  ("Changed" == something is wrong.)  Note:  I am using SESSION, not GLOBAL.


Oracle remove and place (re-install) database on 10g

The best way to drop database is to use DBCA graphic tool. Or you can use this sequence of commands:

shutdown immediate
startup exclusive mount
alter system enable restricted session;
drop database;

But instead I suggest using RMAN backup and restore.


Altering View in MS SQL Server by Adding Col based on Join

As far as some cars has not Year, you should use a LEFT JOIN on this way:

ALTER VIEW corvettes
AS
SELECT    a.carID, a.carName, b.carYear
FROM      Sheet1$ a
LEFT JOIN Sheet2$ b
ON        a.carID = b.carID
WHERE     a.carName = 'corvette'

Have a look at Using OUTER JOINS at Microsoft Docs.


In Postgres (tested with v9.3) you can use the dedicated inet data type, to store IPv4 addresses with only 7 bytes (or IPv6 with 19 bytes) and with automatic integrity checks and dedicated functions and type casts etc.
Schema
The translated schema could look like this:
CREATE TABLE log (
  id serial PRIMARY KEY
, dst_port int
, src_ip inet
, dst_ip inet
);
CREATE INDEX ON log (dst_port);
CREATE INDEX ON log (src_ip);

I moved to dst_port int to the 2nd position to optimize alignment / padding:

Configuring PostgreSQL for read performance

Now we can use standard window functions (not possible in MySQL).
Step 1: Fold groups of consecutive dst_ip for same (dst_port)
One special difficulty: The aggregate function min() / max() are not yet implemented for inet in Postgres 9.4. Both are in the upcoming Postgres 9.5!
So I substituted with DISTINCT ON in the first step:

Select first row in each GROUP BY group?

SELECT DISTINCT ON (dst_port, ip_grp)
       dst_ip, count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct, dst_port
FROM  (
   SELECT dst_ip, dst_port, dst_ip - row_number() OVER (PARTITION BY dst_port
                                                        ORDER BY dst_ip) AS ip_grp
   FROM   log
   ORDER  BY dst_port, dst_ip
   ) sub
ORDER  BY dst_port, ip_grp, dst_ip;

Result as desired - with a count of rows (could be upper IP as well).
You can subtract/add integer from/to the inet type. By subtracting the row_number() all consecutive rows get the same grp - the value of grp is irrelevant, just the fast that it's the same per partition (dst_port).
Then we can GROUP BY ... - or in this special case DISTINCT ON dst_port, ip_grp. I use another window function to get the count ip_ct in the same step: count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct.
Note that consecutive IPs can cross byte boundaries (see my comment to question).
Detailed explanation for this technique:

Select longest continuous sequence

Step 2: Fold groups of consecutive dst_port for same (dst_ip, ip_ct)
SELECT dst_ip, ip_ct, min(dst_port) AS dst_port, count(*) AS port_ct
FROM  (
   SELECT *, dst_port - row_number() OVER (PARTITION BY dst_ip, ip_ct
                                           ORDER BY dst_port) AS port_grp
   FROM  (
      SELECT DISTINCT ON (dst_port, ip_grp)
             dst_ip, count(*) OVER (PARTITION BY dst_port, ip_grp) AS ip_ct, dst_port
      FROM  (
         SELECT dst_ip, dst_port, dst_ip - row_number() OVER (PARTITION BY dst_port
                                                              ORDER BY dst_ip) AS ip_grp
         FROM   log
         ORDER  BY dst_port, dst_ip
         ) sub1
      ORDER  BY dst_port, ip_grp, dst_ip
      ) sub2
   ) sub3
GROUP  BY 1, 2, port_grp
ORDER  BY 1, 3, 2;

Basically, repeat the same logic like in the first step, applied to the result of the first step.
But now you have to group on ip_ct additionally. And this time, you can use the simpler min(dst_port), since the port number is a plain integer.
SQL Fiddle demonstrating all.


MySQL Fulltext match with forward slashes

You are looking for Adding a Collation for Full-Text Indexing

The example explains how to treat the hyphen character ('-') as a word character.


If you have a standalone MongoDB server and can tolerate downtime, the most direct approach would be to copy the contents of your current dbPath to a directory on the new disk:

Stop mongod
Copy the contents of your dbPath to a directory on the new disk
Update dbPath in your configuration file to point to this new directory
Finally, restart mongod


Im sorry so long not update this question. I solved this case 2 months after this question.

After few times trial and errors, my biggest impact in performance is using separated harddisk between Operation System and Data.

I try to bought new harddisk and look like changing storage system from RAID to AHCI give quite difference in performance for data processing. Well, we do 1 hour backup in Maintenance Job for safety to online cloud service.

I do some changes in BIOS settings which also gives many impacts to performance descried in this pdf http://h20195.www2.hp.com/v2/GetPDF.aspx%2F4AA3-6555ENW.pdf

Im also upgrading memory to 4x8GB

Comparing to my old server its 8 times faster now.

Thank you for helps.


MySQL Workbench Unable to Connect to Azure Databases for MySQL

This article hints at the issue, though it is slightly different. After updating the SQL Connector from version 6.x to 8.0.11 and workbench itself to 6.3.10 it has worked perfectly.


Too many Partitions, removing older years

Once the partition is empty and no other partitioned table on a partition scheme using the same partition function has data in the corresponding partition, just use ALTER PARTITION FUNCTION to MERGE away boundary point in the empty partition.


InnoDB ibdata1 corrupted, innodb_force_recover not working

MDEV-13939 Page directory corruption: infimum not pointed appears to be a bug upstream that has some tips on recovery.


Improving query time for auto complete box with millions of record

Have you thought about restricting the search in the UI?  Don't start the autocomplete query until after they've typed 3 or 4 characters. Then instead of looking for 6000 A*, the first jump would be a fraction of that number still.

Also, @Jonathan Fite 's comment about removing the leading % is also a good way to reduce time.  Unless, your users start looking for ew Yor, or shington...


Get a real query plan for EXPLAIN DELETE

You need to run EXPLAIN (ANALYZE, BUFFERS).
Beware that that actually executes the DELETE, so run it in a transaction and ROLLBACK afterwards.
That will show you information about foreign key constraints unless they are deferred, in which case they are executed at commit time.

I'm still curious if it's possible to get costs of a failed query.

In this case, the costs will be somewhere between the costs of the index scan (after which the trigger for the foreign key starts) and the total costs. It depends if the conflicting row is found early or late.


FullTextSearch indexing/searching words with special characters

Hmm I'm afraid '#' counts as punctuation and SQL Server Full-Text Indexing was invented before Twitter.  There are however a couple of approaches:

1) Pre-processing

Use the full-text functions to fetch most of the data then refine it with Like, eg

SELECT Id
INTO #tmp
FROM dbo.Users
WHERE CONTAINS ( Bio, '#promoter' )

SELECT *
FROM dbo.Users u
WHERE u.Bio Like '%#promoter%'
AND EXISTS ( SELECT * FROM #tmp t WHERE u.Id = t.Id )

Whilst you could use CONTAINSTABLE to do the same thing in one query, you can't necessarily guarantee what order the plan will take - and why
would you, T-SQL is after all declarative.  I prefer to be explicit, but if you find performance acceptable and/or data volumes are low, combine the two, eg

SELECT *
FROM dbo.Users u
    INNER JOIN CONTAINSTABLE ( Users, Bio, '"#promoter"' ) ct ON u.Id = ct.[key]
WHERE u.Bio Like '%#promoter%'

I did some simple tests on a 1 million row table and it seemed to perform ok.  YMMV.

2) Tokenised version of column

Store an alternate version, eg a computed column where '#' is replaced with the literal text 'hashtag', something like this:

-- Add computed column
ALTER TABLE dbo.Users ADD cleanBio AS REPLACE ( Bio, '#', 'hashtag' )

-- Add the computed column to full-text index
ALTER FULLTEXT INDEX ON dbo.Users ADD ( cleanBio )
GO

SELECT * FROM dbo.Users
WHERE CONTAINS ( cleanBio, 'hashtagpromoter' )

Obviously there is some storage overhead here.

Consider trying these approaches with your data and let me know how you get on.


IBM Bluemix SQL Database service data store management

Unfortunately, schemas have no clue about their physical background such as tablespaces; they mean only logical namespaces.
But it does not mean you cannot do the schema->tablespace relation yourselves by using the IN tspacename clause in create commands provided that the referred tablespaces already exist.


Answered by @Ziggy Crueltyfree Zeitgeister 's comment.

  A table owner can do with the tables as they please: drop, grant, revoke, etc. What you could do for altering tables is doing so via executing any required ALTER TABLE command from within a function with SECURITY DEFINER.


MS Access Unable to Select Number Field Size

As of 7/27/16 I ran an update for Office and the issue appears resolved.
To run the update in MS Office 2016 go to any MS Office application and go to File -> Account -> Update Options -> Update Now.


Should I repeat UNIQUE constraint on similar multi-index?

Case 1  If you need to avoid both (1,2) and (2,1), then nothing in the Question will prevent that form of "dup".

To do that, always store the LESSER() value in id1 and the GREATER() in id2.  Then UNIQUE(id1, id2) prevents dups.

Meanwhile, do a similar sort of the two values when doing a lookup.

Then you have only the one UNIQUE key -- which may as well be the PRIMARY KEY.  Now you have

Uniqueness constraint that works
Fewer indexes
Only need to do one lookup, not two

etc

Case 2  If (1,2) and (2,1) can coexist, then the order of the ids presented to the table is important.  Probably all you need is

PRIMARY KEY(id1, id2)

If you have something like WHERE id1 BETWEEN .. AND .. AND id2 = 7, then you would also need

INDEX(id2, id1)

My Cookbook covers most of these simply possibilities.


How to accomplish local joins between facts and dimensions?

Your distribution approach by the composite primary key will create a distribution with low or no skew. That's great but, as you point out, it comes with some costs in other areas. In practice even distribution is often a secondary concern. Distribution tends to be by the largest commonly used join or the most common aggregation. In the case you describe this sounds to be the security descriptor dimension.

Data distribution in a MPP database aims to optimise two metrics: minimising the movement of data and making the use of the all the available hardware performance in the cluster. Minimising data motion is achieved by co-locating data for large join and aggregate operations. Using all the hardware to get the best performance for a query requires that the data is evenly distributed and that typical queries will not be working on data on a single node. So when picking a distribution key to minimise data motion distribution it's not only important to minimise skew but also to avoid distributing by a field which is common used in query predicates.


how to upgrade mongodb 2.2 to 2.4 properly

You have to go to their repo for downloads that old:
http://downloads-distro.mongodb.org/repo/

Pick your distro (for example, if Ubuntu pick Debian), drill in to the arch (x86/x64) and pick your download.

example:
Ubuntu 64 bit 2.4 download:
http://downloads-distro.mongodb.org/repo/debian-sysvinit/dists/dist/10gen/binary-amd64/mongodb-10gen_2.4.14_amd64.deb

Try to always go for the latest release of any numbered version (the link is for 2.4.14) and avoid .0 versions (such as 2.4.0).


Which NoSQL database(s) should be used for advertising platform?

To be very fast, the database should only use memory and not disc since disc operations usually takes much longer time. But then your data aren't persistent in case of a crash. What you could do is assynchronous disk operations, by that most of your writes will be persistent but it's not guaranteed that the last few writes are persistent. If this is okey for you, you can use Redis. See also Redis - The Hacker's Database.

If you really care for persistent data, you should use a database that have been tested and used for years. Then PostgreSQL is a good choice.

To achive high availability, you need a distributed system with multiple nodes, so they can tolerate that at least one node goes down. Then I would use Riak.

The question is why do you need NoSQL? That word means almost nothing. I would recommend you to go with a traditional RDBMS like PostgreSQL until that can't handle your problem. It will be much easier to handle for you and it's much more flexible.

What you can do to improve the speed is to use caching. Then you can use Varnish Cash infront of your server, that will improve your speed a lot.

See also What are the differences between NoSQL and a traditional RDBMS?


Time Series Table ( Many Columns ) Design Recommendations

(type of option 2 is not represented in option 1. Makes the comparison slightly unfair, but hardly matters.)

The major difference between the two options is storage size. Assuming datatype real for your measurements and bigint for IDs (for lack of info), the row size of option 1 is:

8 + 8 + 100 x 4 + 24 + 4 = 444 bytes.

While the same occupies with option 2:

8 + 8 + 24 + 4 = 44 bytes in table measurement. Plus ...
(8 + 8 + 13 + 4 + 4 + 24 + 4) x 100 = 6500 bytes in measurement_data.
So 6544 bytes (!) total in option 2 for one row in option 1.

Plus you need more indexes in option 2, half of which are bigger by the same factor than in option 1 as well. Seems hopeless. Size matters.

OTOH the query for option 2 retrieves only rows with 65 bytes per hit in measurement_data, but it adds the overhead of at least an additional index-only scan on measurement. Still might prevail for a large number of hits and clustered (more considerations!) data. And updates should be cheaper, producing less table bloat - but such a design typically does not see many updates. Many related considerations depending on the complete picture.

In any case, ditch option 2 which stores some attributes redundantly over and over. Consider option 3 instead, a classical m:n relationship:

TABLE measurement (measurement_id bigint PK, ts timestamp)
TABLE measurement_kind (measurement_kind_id int PK, type int?, description text)
TABLE measurement_data (measurement_id bigint, measurement_kind_id int, value real, PK (measurement_id, measurement_kind_id))

Still 44 bytes for measurement. Plus:
(8 + 4 + 4 + 24 + 4) * 100 = 4400 bytes in measurement_data
So 4444 bytes total in option 3 for one row in option 1.

(And a negligible 100 x (4 + 4 + 13 + 3 + 24 + 4) = 5200 bytes once --> 8k mini-table for measurement_kind.)

The equivalent query to go with it:

SELECT md.value
FROM   measurement m
CROSS  JOIN (
   SELECT measurement_kind_id
   FROM   measurement_kind
   WHERE  description = 'measurementA'
   ) mk
JOIN   measurement_data md USING (measurement_id, measurement_kind_id)
WHERE  m.ts > X
AND    m.ts < Y;

Resumé: Maybe option 1, maybe option 3, never option 2.

Related (suggested!):

How to implement a many-to-many relationship in PostgreSQL?
Configuring PostgreSQL for read performance


Error log flooding with aborted connections (Aurora 2.03 - MySQL 5.7.12)

The aborted connections log messages are controlled by the log_warnings setting. In MySQL 5.6 it is set to 1 by default whereas in 5.7 the default is 2 (which is more verbose and shows connection messages)

You can compare the description of the settings for 5.6 vs 5.7

https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-warnings

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-warnings

  Print out warnings such as Aborted connection... to the error log. This option is enabled (1) by default. To disable it, use --log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See Section B.5.2.11, “Communication Errors and Aborted Connections”.

As long as the defaults weren't changed I wouldn't expect to see the connection errors on the 5.6 server. You could try setting it to 2 and checking the log again: set global log_warnings=2;


Is it possible to restore only one table?

No.

the destination database changed because there were some users added, and some tables

If the additions can't be added with a simple script you run after the restore, they should be in a separate database.  You can use Views or Synonyms to reference tables across databases.


Problem with Error: InnoDB: page ... log sequence ... number is in the future!

Nobody seems to have a real solution for this issue, at least i was not able to dig anything useful up. But apparently it helps, to just let it run and cleanup the logs on a regular basis, so your server does not get filled up with logs ... at least, since some days i do not have anymore log entries ...

this is all very odd -- in my humble opinion.


how to take backup of a child table with foreign key dependency in mysql using bash

Assuming MySQL 5.x

mysqldump has a --where option that can be used to limit/select rows to be backed up.

There isn't an option to join two tables using --where but you can identify which t_id values you want to backup and then run two mysqldumps as follows:

mysqldump -u... -p... --where="t_id in (10,100,200,300,.....)" database_name parent_table > parent.sql

mysqldump -u... -p... --where="t_id in (10,100,200,300,.....)" database_name child_table > child.sql

Once you have the parent.sql and child.sql dump files, load them into the target database and you should be good to go.

Also, note that I am assuming t_id column is indexed on both tables otherwise it may take a long time to dump the rows if the two tables have large number of rows.


SQL Server 2008 - performing nightly full backups & transaction log shipping, but log file keeps growing

On top of the full backup, I added a transaction log backup just to see if this made a difference

This is a big no no.  By doing a manual transaction log backup, you can break the LSN of the log shipping secondary and cause the restores to fail, unless you used the copy_only option, which doesnt allow for truncation anyway.

There are only a few things that can cause a log not to truncate/clear, an active transaction, the recovery model set to full w/ no t-log backups, or a feature that is not working properly (mirroring, replication etc...)

You can check for open transactions by running DBCC OPENTRAN in the context of the said database.  Also, I know it may seem like a waste, but I would also confirm that your log backups are actually succeeding.  It could be a situation where the backup drive is out of space and the backups are failing.

Edit:
I forgot to add, when your log does truncate, you will want to shrink your log file as the number of VLFs will probably quite large and could degrade performance.  You can use dbcc shrinkfile


That is just the way that SQL Server work. If you were able to exclude read-write filegroups in simple recovery, then you wouldn't be able to restore them. A restore need log backups to apply modifications to the data you just restored. Since you can't do log backup in simple mode, then those filegroups in your backup would be "doomed" - for even inaccessible (I believe that the state is "defunct" in SQL Server). And you cannot have a doomed primary filegroup. If you have data which is irrelevant for you,then consider truncating those table to get rid of the data or set them to read-only and exclude them.


If I understood you don't need to do it online.

In this case, how about a CREATE TABLE AS SELECT using a select which is focusing on the subset of data?

Then you just do a quick rename, recreate the indexes and done.


Array of template type in PL/pgSQL function using %TYPE

The %TYPE construct can only copy the exact type. But there is a simple workaround to register the according array type: Create a (temporary) table or view with the desired array type.
Template table:
CREATE TABLE foo (i int);

(Temporary) view to register the array type, optionally empty (LIMIT 0):
CREATE TEMP VIEW v_foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Or a (temporary) table:
CREATE TEMP TABLE foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Now your function works:
CREATE OR REPLACE FUNCTION f_arr_test(v_foo_i_arr.i_arr%TYPE)
  RETURNS foo.i%TYPE AS  -- example: return element type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;
You might as well make that a temporary view or table, since the type in the function signature is converted to the underlying type immediately. No persisted connection to the used template. So it's no problem that temp objects are dropped at the end of the session.
Polymorphic type
You might be better off with a polymorphic type to begin with. This works for any array type:
CREATE OR REPLACE FUNCTION f_arr_test_polymorphic(ANYARRAY)
  RETURNS ANYELEMENT AS  -- derived from input type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;
SQL Fiddle.
Note that using a %TYPE declaration inside the function body actually works dynamically in that it looks up the type in the system catalog at the first call of every session.
Related question on SO:

PL/pgSQL - %TYPE and ARRAY


Hash join and hash aggregate both use the same operator code internally, though a hash aggregate uses only a single (build) input. The basic operation of hash aggregate is described by Craig Freedman:

As with hash join, the hash aggregate requires memory.  Before executing a query with a hash aggregate, SQL Server uses cardinality estimates to estimate how much memory we need to execute the query.  With a hash join, we store each build row, so the total memory requirement is proportional to the number and size of the build rows.  The number of rows that join and the output cardinality of the join have no effect on the memory requirement of the join.  With a hash aggregate, we store one row for each group, so the total memory requirement is actually proportional to the number and size of the output groups or rows.  If we have fewer unique values of the group by column(s) and fewer groups, we need less memory.  If we have more unique values of the group by column(s) and more groups, we need more memory.

He goes on to talk about hash recursion:

So, what happens if we run out of memory?  Again, like hash join, if we run out of memory, we must begin spilling rows to tempdb.  We spill one or more buckets or partitions including any partially aggregated results along with any additional new rows that hash to the spilled buckets or partitions.  Although we do not attempt to aggregate the spilled new rows, we do hash them and divide them up into several buckets or partitions.  Once we’ve finished processing all input groups, we output the completed in-memory groups and repeat the algorithm by reading back and aggregating one spilled partition at a time.  By dividing the spilled rows into multiple partitions, we reduce the size of each partition and, thus, reduce the risk that the algorithm will need to repeat many times.

Bailout
Hash bailout is lightly documented, but mentioned by Nacho Alonso Portillo in What’s the maximum level of recursion for the hash iterator before forcing bail-out?

The value is a constant, hard coded in the product, and its value is five (5). This means that before the hash scan operator resorts to a sort based algorithm for any given subpartition that doesn’t fit into the granted memory from the workspace, five previous attempts to subdivide the original partition into smaller partitions must have happened.

The "hash scan operator" mentioned there is a reference to the internal class CQScanHash in sqlmin.dll. This class heads the implementation of the hash operator (in all its forms, including partial aggregates and flow distinct) we see in execution plans.
Bailout algorithm
This brings us to the heart of your questions - what exactly does the bailout algorithm do? Is it "sort based" or based on "a sort of nested loops thing"?
It is arguably both, depending on your point of view. When hash recursion reaches level 5, the in-memory hash partition changes from being a hash table to an initially empty b-tree index on the hash values. Each row from a single previously-spilled hash partition is looked up in the b-tree index and inserted (new group) or updated (maintaining aggregates) as appropriate.
This series of unordered inserts to a b-tree can equally well be seen as an insertion sort or as an indexed nested loops lookup.
In any case, this fallback algorithm is guaranteed to complete eventually without allocating more memory. It may require multiple passes if the space available for the b-tree is not sufficient to hold all the grouping keys and aggregates from the overflow partition.
Once the memory available to hold the b-tree index is exhausted, any further rows (from the current spilled partition) are sent to a single new tempdb partition (which is guaranteed to be smaller) and the process repeats as necessary. The spill level remains at 5 because hash recursion has ended. Some processing details can be observed with undocumented trace flag 7357.


DROP DATABASE statement cannot be used inside a user transaction

Create a separate initializer that drops the database if it exists, then creates the database outside of any transaction handling code.  If this fails for any reason, you could (probably) just fail the test right then and investigate why it failed.

From MSDN:

  The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

If you want, you could do this for each individual test:

DROP DATABASE if it exists
CREATE DATABASE
 Create necessary DDL
 Run DML unit test
DROP DATABASE

However, that would be overkill.  I'd simply create the database at the start of all tests, and drop it at the end.  Each unit test could be wrapped in a transaction which is either rolled-back or committed, depending on the requirements of further tests.

I looked for a resource that explains why CREATE DATABASE cannot be used inside a transaction, but I couldn't find one. Kenneth Fisher provided the following TechNet link, which shows all commands that won't work in a transaction:

Transact-SQL Statements Allowed in Transactions

Our guess is that a lot of them aren't allowed because they affect the file system, which is not transactional.