You can add WAITFOR DELAY between executions.

UPDATE que SET status = 0 WHERE doc = '001'; EXECUTE [things] '001';
WAITFOR DELAY '00:00:10';
UPDATE que SET status = 0 WHERE doc = '002'; EXECUTE [things] '002';
WAITFOR DELAY '00:00:10';
UPDATE que SET status = 0 WHERE doc = '003'; EXECUTE [things] '003';
WAITFOR DELAY '00:00:10';

Might be a good idea to add some (error) logging as to know where it failed if an error occured.

If you don't want to write 50 extra lines, you could use search and replace in ssms (CTRL+F) and replace 'UPDATE' with 'WAITFOR DELAY '00:00:10';  UPDATE'

Make sure you are on current document and then press replace all:

Result:


How to grant permission to execute stored procedure in master database from another database

What am I missing?

The DOMAIN\user login should be able to run  dbo.sp_HelloWorld with it's corresponding user in the MyDB context.

The EXECUTE AS USER command is the reason for the error being returned.

Execute as user documentation

  Specifies the context to be impersonated is a user in the current
  database. The scope of impersonation is restricted to the current
  database. A context switch to a database user does not inherit the
  server-level permissions of that user.

Important part: the scope of impersonation is restricted to the current database.

Which is why the permissions from other databases such as master are not applied when using EXECUTE AS USER.

You need to use EXECUTE AS LOGIN  if you want the correct login / user mapping

Execute as login documentation

  Specifies the execution context to be impersonated is a login. The
  scope of impersonation is at the server level.

USE MyDB
GO
EXECUTE AS LOGIN ='DOMAIN\user'
EXEC dbo.sp_HelloWorld
REVERT

Result

Hello World!


How to create SQL Server Agent Queued jobs?

The SQL Server Agent has a maximum worker thread setting that limits the total number of threads that can run jobs at any given time. When you hit this limit, you will get a warning message that the thread limit has been hit and the job queued for execution. When this occurs, that counter increments to record the fact that there is a queued job.

This is also true for job steps, which have their own worker thread limit for each subsystem and a performance counter under SQLAgent:JobSteps to record the queued steps.

This queueing mechanism is not something you can manually initiate or control, it is an in-built queueing mechanism to manage the worker threads used by SQL Agent.

See here for more info. The max worker thread count is # of CPUS*100. Run sp_enum_sqlagent_subsystems to see the max worker threads for individual subsystems.

You can test this out yourself, though it is easier to test the subsystem queue behaviour than the SQL Agent job queue behaviour.

Create a number (6 or more, depends on the limit for the PowerShell subsystem) of one-step SQL Agent jobs to run a PowerShell command (Start-Sleep -Seconds 60) and execute them simultaneously. Job Activity Monitor will show something similar to below

And if you're monitoring the performance counter SQLAgent:JobSteps - Queued Steps, you will see it increase:


How should I organize my mdf, log and tempdb on a 3 physical disk server

It really depends on your workload but with only two drives you're going to have to make some concessions.  Ideally you want to have tempdb, data and log files all on their own drives.  There's a lot of nasty things going on in tempDB and you don't want that affecting your production databases.

As I said, you should consider your workload, if tempDB is made to work particularly hard or you've got a database that has few write operations then you might find that you're better off putting tempDB on it's own drive and bundling the data and log files together.  In any other situation I'd put tempDB on the same disk as the data files.


Timeout for Always On Synchronous Commit Mode

how can you configure the timeout for Synchronous Commit mode?

You can do it using SSMS, TSQL and powershell. Microsoft has documented this. Please see Modify the session-timeout period for an availability group replica. For example TSQL would look like
ALTER AVAILABILITY GROUP AccountsAG
   MODIFY REPLICA ON 'INSTANCE09' WITH (SESSION_TIMEOUT = 15);

Just note that the timeout value you put is the value which AG will take into consideration making the replica Async in functionality after primary looses connection to secondary replica. Means it will start committing data on primary without waiting for commit on secondary. BUT this does not means that log file will not grow on primary, it will, and primary will store all the log records so that when the secondary comes online it can send records to secondary and sync. If your secondary will be down for long time and you worry about logs being accumulated take the secondary out from the AG and this will allow committing of transaction logs on the primary.

Can it work, for example, between continents

Yes it will, but the timeout value you have to find out which best suits your environment. I have seen such configuration where value was 25 seconds due to network latency


Do you have to immediately index those columns even before you notice performance issues?

Short answer - Yes.

Where you have joins, you should also have Primary and Foreign Key Constraints and most DBMSs will insist that each of these is supported by an Index (even if it has to create one for you).

The vast majority of traffic in most databases is SELECTs, so putting off the creation of Indexes until performance becomes a problem is just setting yourself up to fail.  You know you're going to need them, so save yourself the grief and put them in to start with.
(And, anyway, it won't be you that notices the "Performance Problem" - it will be your unhappy Users).


Proper Guidelines for new Database setup in SQL Server

How to define initial size to create Database

Well I believe you are asking what value to select for initial size ?. If so, I am sorry to say that it depends on your environment. But please do not leave it to default of 2 MB (for data file) and 1 MB (for log file). I wish MS had better value set as default but this is AFAIK from SQL Server 2005 and has not been changed yet. If you ask me, I would suggest to keep initial size to as much as possible to avoid autogrowth. Suppose you know your database would be 500 GB in year keep it to 300 GB and forget the hassle of autogrowth and slowness due to it, there is no harm in giving as much space to data file as it needs this is only going to benefit the queries. This size if for data file, for log file you would have to figure out yourself. SQL Server Database growth and Autogrowth Setting article would help you more.

  Should i use different file and filegroups

Well again the answer is "it depends", file and filegroups are more for making DBA's management job easier and segregating things. If you have large database where you want to offload non critical objects which are accessed less on slow drive and move more accessed objects on faster drive you can do it via filegroups. You can also move one BIG table to altogether different filegroup residing on fast drive to make queries faster. Please read below 2 discussions.

Benefit to use multiple filegroups
Benefit of filegroups and setting filegroups to read-only

  What type of Collation should i select

That definitely I cannot tell and you should ask the respective teams.

  Any tips for tempdb configuration

A lot, please refer to below links. Since you have not added what version of SQL Server you are talking I would only suggest very basic things. Putting tempdb on faster drive makes queries using temp tables fast. Make sure you correctly add temdb data files and keep initial size and autogrowth same for all the data files. For SQL Server 2014 and before enabling TF 1117 and 1118 is also recommended. For SQL Server 2016 and above TF's are not required. More details Here

Correctly adding Tempdb Files
Cheat Sheet How to Configure Tempdb

  How to defined how much resource needed and how to allocate them

That needs lot of information from your side. There is no direct formula for it.

  How to handle Server Memory Setup (Minimum server memory, Maximum server memory, Index creation memory, Minimum memory per query)

To configure max server memory please refer What is a deterministic method for evaluating a sensible buffer pool size?. Don't touch index creation memory and minimum memory per query. If you are having just one instance of SQL server leave min server memory to default.

Ignore points 7  should not touch those settings unless you are expert. The default just works fine. You should let SQL Server decide worker threads it does the job beautifully.

  How to calculate/estimated Maximum number of concurrent connections

Unless you start putting some load on server there is hardly any point in getting concurrent connection. For getting max connections on SQL Server hitting see Stackexchange thread Max Number of connection

  How to configure SQL Server Parallelism (Cost Threshold for Parallelism, Locks, Max Degree, Query Wait)

You just need to setup proper max degree of parallelism rest you should leave to default. For maxdop setting refer MAXDOP setting algorithm for SQL Server


Incremental backups using MySQL Workbench

To be frank, if you are using a GUI tool as your backup strategy, you are doing it wrong.  Backups need to be scripted, automated, and regularly tested or else you might as well not have them.  If your database is very small (a few gigabytes), there is no point in doing incremental backups: backup the entire database with mysqldump and use binary logs in statement format for point in time recovery, or using a slave, shut down the slave an archive the binary files (cold backup).

If you are using InnoDB and really need incrementals, there is Xtrabackup from Percona or innodbhotbackup.

Xtrabackup is a great tool which I highly recommend.  It will perform true incremental backups with minimal impact to the server.


How to query all entities associated with all children (recursive) of a node in SQL?

I assume the row (5,5) is a typo, so I used:

create table tree (node_id int not null primary key, parent_id int);
insert into tree (node_id, parent_id)
values (1,null),(2,(null),(3,1),(4,1),(5,4),(7,2),(8,5);

as a side note, which representation would you have preferred if you were about to start answering a question, the ascii art in your question, or create and insert statements like above?

To recursively traverse the tree you can use a recursive common table expression (CTE):

with rec (node_id, ancestor_id) as (
    select t.node_id, t.parent_id
    from tree t
    union all
    select rec.node_id, t.parent_id
    from rec, tree t
    where rec.ancestor_id = t.node_id
) select * from rec

I much prefer explicit joins, but the DBMS I'm using does not support them in CTE:

with rec (node_id, ancestor_id) as (
    select t.node_id, t.parent_id
    from tree t
    union all
    select rec.node_id, t.parent_id
    from rec
    join tree t
        on rec.ancestor_id = t.node_id
) select * from rec

Now it's just a matter of joining this result with your entities table:

with rec (node_id, ancestor_id) as (
    select t.node_id, t.parent_id
    from tree t
    union all
    select rec.node_id, t.parent_id
    from rec
    join tree t
        on rec.ancestor_id = t.node_id
) select rec.ancestor_id, e,entity_id
  from rec
  join entities e
      on e.node_id = rec.node_id

This assumes that your DBMS version supports recursive CTE:s. The latest versions of MySQL / MariaDB does.

Another side note, you may want to normalize the tree relation, and keep the structure in a separate relation:

create table treenode (node_id int not null primary key, ...)
create table tree (node_id int not null primary key
                  ,parent_id int not null);

root node(s) are the nodes that are absent in tree

If you are on an older version, or have huge amounts of data and your performance needs to be improved you can add additional information to your model. The most common ones are:

Nested Sets, all nodes have an interval of what nodes they sub-sume
Materialized Path, all nodes have an aggregated string which represents ancestor nodes
A transitive closure table, I have some notes and a few examples at tree:


A table with many null values VS many tables

As I understand you now, a "water" is kind of a recipe.

For such a recipe you want to record a) what additives where added and b) what the outcome was.

That's two entities, recipe and additives. Both have a many to many relationship with a relationship attribute, which is the amount. The outcome of a recipe is of course an attribute of the recipe and not one of a relation between a recipe and an additive. It's the whole mixture that counts.

A typical and normalized solution are three tables.

One for the additives.

CREATE TABLE additive
             (id serial,
              name text,
              PRIMARY KEY (id));

Another one for the recipe.

CREATE TABLE recipe
             (id serial,
              name text,
              drinkable boolean,
              dangerous boolean,
              PRIMARY KEY (id),
              CHECK (NOT drinkable
                      OR NOT dangerous));

Note: I added a check constraint, that a receipt deemed dangerous cannot be marked as drinkable.

And a table linking the additives for a recipe.

CREATE TABLE recipe_additive
             (recipe integer,
              additive integer,
              amount integer NOT NULL,
              PRIMARY KEY (recipe,
                           additive),
              FOREIGN KEY (recipe)
                          REFERENCES recipe
                                     (id),
              FOREIGN KEY (additive)
                          REFERENCES additive
                                     (id));


MySQL JOIN two tables and get latest result

I stated in the above comments that this seems to be a case of a supertype-subtype relationship but, since changing your database structure may be a decision that lies outside of the scope of this question, I am going to focus on offering a solution for your current situation.

Then, after a couple of edits, I decided to include some DDL statements and a brief description of my assumptions about your database structure, hoping that in this way my answer and its involved queries will be more easily comprehended.

Understanding of the situation and hypothetical DDL

As I percive the scenario, type_a and type_b may be two different kinds of reading or measurement that you are collecting for each device.

This way, although I am not completely sure of the meaning of the type_a.type_a_id and type_b.type_b_id columns, I assume that they are some sort of sequential_number or row_number or record_identifier in each respective table. Likewise, the columns type_a.stats_time and type_b.stats_time are the exact points in time when a device posts some sort of reading.

I have also assumed that type_a.device_id and type_b.device_id are FOREIGN KEYS that make reference to the device table that, in turn, has the column device.device_id being used as some kind of sequential_number or row_number or record_identifier that you have defined as PRIMARY KEY.

Having described my understanding of the state of affairs, please note that I am not suggesting that this is the optimal structure (since, naturally, I am not familiar with the real scenario, it may lack normalization, integrity, etc.), I am just making some assumptions based on the data samples and queries that you have presented, in order to deliver a possible solution for your specific situation. So, here is the speculative DDL:

CREATE TABLE device
(
    device_id INT      NOT NULL AUTO_INCREMENT,
    name      CHAR(30) NOT NULL,
    type      CHAR(1)  NOT NULL,
    PRIMARY KEY (device_id),
    UNIQUE INDEX uix_name (name)
);

CREATE TABLE type_a
(
    type_a_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL ,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    noise      INT      NOT NULL,
   PRIMARY KEY (type_a_id),
   CONSTRAINT FOREIGN KEY fk_type_a_device (device_id)
   REFERENCES device(device_id),
   UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

CREATE TABLE type_b
(
    type_b_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    `signal`   INT      NOT NULL,
    PRIMARY KEY (type_b_id),
    CONSTRAINT FOREIGN KEY fk_type_b_device (device_id)
    REFERENCES device(device_id),
    UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

-- Some ‘device’ INSERTS...

INSERT INTO device (name, type) VALUES ('First device', 'A');
INSERT INTO device (name, type) VALUES ('Second device', 'A');

-- ... And then, some ‘type_a’ and ‘type_b’ INSERTS
-- in order to have some sample data for retrieving.

INSERT INTO type_a (device_id, stats_time, `status`, noise)
VALUES (1, STR_TO_DATE('06/01/2015 08:10:01 AM', '%c/%e/%Y %r'), 'Foo', 123);

INSERT INTO type_a (device_id, stats_time, `status`, noise)
VALUES (1, STR_TO_DATE('04/04/2015 03:07:34 PM', '%c/%e/%Y %r'), 'Bar', 456);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`)
VALUES (2, STR_TO_DATE('03/04/2015 02:08:15 PM', '%c/%e/%Y %r'), 'Boo', 789);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`)
VALUES (2, STR_TO_DATE('05/07/2015 04:03:12 PM', '%c/%e/%Y %r'), 'Far', 852);

Initial proposal

And then, here is my first submited query which, while keeping the original idea, has been reformatted and adapted to the DDL structure that was added later:

SELECT DE.device_id,
       COALESCE(TA.type_a_id, TB.type_b_id)   AS type_id,
       DE.name,
       DE.type,
       COALESCE(TA.stats_time, TB.stats_time) AS stats_time,
       COALESCE(TA.status, TB.status)         AS `status`,
       COALESCE(TA.noise, 0)                  AS noise,
       COALESCE(TB.signal, 0)                 AS `signal`
  FROM device            DE
  LEFT OUTER JOIN type_a TA
    ON TA.device_id    = DE.device_id
  LEFT OUTER JOIN type_b TB
    ON TB.device_id    = DE.device_id
ORDER BY stats_time DESC;

As you can see, I am using the COALESCE() function that only if the column type_a.stats_time contains a NULL value, then ‘prints’ the value of the column type_b.stats_time, and works in a similar way for the type_a.status and type_b.status columns.

Subsequent proposal

First method

Now, having reread your question and after some comment interactions, I understand that what you actually want is to obtain a single result set containing only two rows —one row including columns corresponding to the latest type_a.stats_time value and one row holding columns related to the latest type_b.stats_time value—. Therefore I suggest you the following query that also retrieves data from the DDL proposed above:

(SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id  = DE.device_id
  WHERE TA.stats_time = (SELECT MAX(stats_time)
                           FROM type_a))

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id  = DE.device_id
  WHERE TB.stats_time = (SELECT MAX(stats_time)
                           FROM type_b))

ORDER BY stats_time DESC;

Note the use of the UNION operator, which in serves the purpose of combining in one single result set the latest row of type_a (based on the newest stats_time column value, obtained via the MAX() function within a subquery in the WHERE clause) with the latest row in type_b (likewise, based on the newest stats_time column value, also obtained through the MAX() function within a subquery in the WHERE clause).

Second method

You can also try with this alternate query, which sorts each one of the combined result sets based on the stats_time column in each respective SELECT statement using the ORDER BY and LIMIT clauses.

(SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id = DE.device_id
  ORDER BY TA.stats_time DESC LIMIT 1)

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id = DE.device_id
  ORDER BY TB.stats_time DESC LIMIT 1)

ORDER BY stats_time DESC;

Once you compare the performance of all the suggested methods, it will be easy to define which one is the best for your needs. Also, if one of them solves your problem, then you can set it up as a VIEW, this way future data retrieval will be easier to obtain.

About the speed performance aspect of your queries, you may start refining such matters by paying special attention to the indexes definition in the apropiate columns, e.g. type_a.stats_time and type_b.stats_time.


Using Identity Insert on a linked server

Linked servers aren't usually a good idea from a security perspective. See distribution statistics section here

I think SSIS would provide a better way of doing this.


I did a presentation about alternative ways of defining "extensible" relational database schema at the Percona Live MySQL Conference & Expo in April 2013.  Here's a link to my slides:

http://www.slideshare.net/billkarwin/extensible-data-modeling


Why does InnoDB allow a 772 byte unique constraint?

Revised math:

8 bytes for BIGINT -- Do you really need more than 4 billion values?  Consider INT UNSIGNED at only 4 bytes.
2 byte length of VARCHAR
764 bytes for 191 characters (potentially 4 bytes per utf8mb4 character)

2+764 < 767, so the VARCHAR column passes that test
8+2+764 < 3KB, so the entire index passes another test.  (As would a pair of 191-char VARCHAR)

Edit

The reference manual has some complicated limits on indexes.  Generally you will be safe with a column taking no more than 767 bytes, and the entire index taking no more than 3072 bytes.  Keep in mind that one character in utf8mb4 counts (for these limits) as 4 bytes.  I have a compilation of limits.


How to turn off autocommit in phpMyAdmin

You can do this in 3 different ways:

The best answer to this question depends on the context of your request.

If you are doing this from time to time on a dev/test machine (and I certainly hope you're not doing it on an important/production one!), you can then afford to make global changes and you could:

Edit the database interface file inside the "libraries" folder as described here and put the line SET AUTOCOMMIT = 1; in the postConnect function. This will affect every connection made from that instance of phpMyAdmin - you may or may not want this, depending on whether you also use that machine's phpMyAdmin for running stuff on prod?
Put the line autocommit = 0 to set the variable in your my.cnf/my.ini. This now means that every connection to that server will automatically commit (described here). Again, you may or may not want to do this.
Finally, if this is something that you'll want to do from time to time - i.e. experiment at lunchtime/whatever, you can issue a BEGIN [WORK] before you issue your SQL statements! (ibid - aliases of START TRANSACTION - see here).
This has the advantage of not affecting the configuration of either phyMyAdmin or the MySQL server, BUT has the disadvantage of the dev (i.e. you) having to remember to put it in each time. NOT advisable on a production machine, but means that you can use your db tool on both work systems and your experimental ones without worry!

The choice is yours!


postgres database with multiple database object owners and pg_dump

If you grant SELECT privileges to all tables to the database owner you should be able to export at least the tables. If not, please give full error message(s).


Changing the datatype of the primary key columns of partitioned tables

First before I provide a solution, I want to confirm your assumption:

  As far as I can see there is a schema bound dependency of the partition scheme (and function) to the table preventing the datatype modification.

You are correct; the fact that you have a partition function defined for a datatype of INT is preventing you from altering the data type of your column...  Sadly, you're kind of stuck with the current table because there's no way, to my knowledge, to adjust the datatype of a partition function without dropping/recreating it.... which you can't do because you have tables dependent upon it.  So basically this is the chicken-and-egg scenario.

  I'm not seeing a solution to updating the column type on this database, let alone a production database. We can afford some maintenance downtime, say +- 60 minutes, but no longer. What are my options?

One possible solution to your issue is to take advantage of Partitioned Views.  This functionality has been around forever and is often overlooked, but a Partitioned View will provide you with a way to sidestep your INT data limit by adding new data to a different table where the ID column is a BIGINT datatype.  This new table should also have a better partition function/scheme underlying it and hopefully will be maintained a little better going forward.  Any queries that are pointing to the old table would then be pointed to the Partitioned View and you'll not run into a data limit issue.

Let me explain with an example.  The following is a trivial recreation of your current partitioned table, consisting of one partition, as follows:

-- Create your very frustraiting parition scheme
CREATE PARTITION FUNCTION dear_god_why_this_logic (INT)
AS RANGE RIGHT FOR VALUES (0);

-- Scheme to store data on PartitionedData Filegroup
CREATE PARTITION SCHEME dear_god_why_this_scheme
AS PARTITION dear_god_why_this_logic ALL TO ([PRIMARY]);

-- Create Partitioned Table
CREATE TABLE dbo.TestPartitioned
(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    VAL CHAR(1)
) ON dear_god_why_this_scheme (ID);

--Populate Table with Data
INSERT INTO dbo.TestPartitioned WITH (TABLOCK) (VAL)
SELECT CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 128)
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;

Let's say for argument's sake that I'm getting close to the INT limit (even though I'm obviously not).  Because I don't want run out of valid INT values in the ID column, I'm going to create a similar table, but use BIGINT for the ID column instead.  This table will be defined as follows:

CREATE PARTITION FUNCTION lets_maintain_this_going_forward (BIGINT)
AS RANGE RIGHT FOR VALUES (0, 500000, 1000000, 1500000, 2000000);

-- Scheme to store data on PartitionedData Filegroup
CREATE PARTITION SCHEME lets_maintain_this_going_forward_scheme
AS PARTITION lets_maintain_this_going_forward ALL TO ([PartitionedData]);

-- Table for New Data going forward with new ID datatype of BIGINT
CREATE TABLE dbo.TestPartitioned_BIGINT
(
    ID BIGINT IDENTITY(500000,1) PRIMARY KEY CLUSTERED,
    VAL CHAR(1)
) ON lets_maintain_this_going_forward_scheme (ID);

-- Add check constraint to be used by Partitioned View
ALTER TABLE dbo.TestPartitioned_BIGINT ADD CONSTRAINT CK_ID_BIGINT CHECK (ID > CAST(499999 AS BIGINT));

A few notes here:

Table Partitioning on the New Table

This new table is also going to be partitioned based on your requirements.  It's a good idea to think about future maintenance needs, so create some new file groups, define a better partition alignment strategy, etc.
 My example is keeping it simple, so I'm throwing all of my partitions into one Filegroup. Don't do that in production, instead follow Table Partitioning Best Practices, courtesy of Brent Ozar et. al.

Check Constraint

Because I want to take advantage of Partitioned Views, I need to add a CHECK CONSTRAINT to this new table.  I know that my insert statement generated ~440k records, so to be safe, I'm going to start my IDENTITY seed at 500k and create a CONSTRAINT defining this as well.
 The constraint will be used by the optimizer when evaluating which tables can be eliminated when the eventual Partitioned View is called.

Now to Mash the Two Tables Together

Partitioned Views don't particularly do well when you throw mixed datatypes at them when it comes to the partition column in the underlying tables.  To get around this, we have to persist the current ID column in your current table as a BIGINT value.  We're going to do that by adding a Persisted Computed Column, as follows:

-- Append Calculated Columns of New Datatype to Old Table
-- WARNING: This will take a while against a large data set and will likely lock the table
ALTER TABLE dbo.TestPartitioned ADD ID_BIG AS (CAST(ID AS BIGINT)) PERSISTED
GO

-- Add Constraints on Calculated Column
-- WARNING: This will likely lock the table
ALTER TABLE dbo.TestPartitioned ADD CONSTRAINT CK_ID_TestPartitioned_BIGINT CHECK(ID_BIG <= CAST(499999 AS BIGINT));
GO

-- Create a new Nonclustered index on ID_BIG to act as new "pkey"
CREATE NONCLUSTERED INDEX IX_TestPartitioned__BIG_ID__VAL ON dbo.TestPartitioned (ID_BIG) INCLUDE (VAL);

I've also added another CHECK CONSTRAINT on the old table (to aid with Partition Elimination from our eventual Partitioned View) and a new Non-Clustered Index that is going to act like a primary key index (because look ups originating from the Partitioned View are going to be occurring against ID_BIG instead of ID).

With the new Computed Column and Check Constraint in place, I can finally define the Partitioned View, as follows:

-- Build a Partitioned View on Top of the old and new tables
CREATE VIEW dbo.vw_TableAll
WITH SCHEMABINDING
AS
SELECT ID_BIG AS ID, VAL FROM dbo.TestPartitioned
UNION ALL
SELECT ID, VAL FROM dbo.TestPartitioned_BIGINT
GO

Running a quick query against this view will confirm we have partition elimination working (as you don't see any lookups occurring against the new table we've created):

SELECT *
FROM dbo.vw_TableAll
WHERE ID < CAST(500 AS BIGINT);

Execution Plan:

At this stage, you'll need to make a few changes to your application:

Stop inserting new records into the old table, and instead insert them into the new table.  We're not allowed to insert records into the Partitioned View because we're using IDENTITY values in the underlying tables.  Partitioned Views don't allow for this, so you have to insert records directly into the new table in this scenario.
Adjust any SELECT queries (that point to the old table) to point to the Partitioned View.  Alternatively, you can rename the old table (e.g. TableName_Old) and create the view as the old tables' name (e.g. TableName); how fancy you want to get here is up to you.

New Records to the New Table

At this point, new records should be getting inserted into the new table.  I'll simulate this by running the following:

--Populate Table with Data
INSERT INTO dbo.TestPartitioned_BIGINT WITH (TABLOCK) (VAL)
SELECT CHAR(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 128)
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;
GO

Again, my Identity Seed is configured so I won't have any ID conflicts between the two tables.  The CHECK CONSTRAINTS on both tables should also enforce this.  Let's confirm that Partition Elimination is still occurring:

SELECT *
FROM dbo.vw_TableAll
WHERE ID > CAST(300000 AS BIGINT)
    AND ID < CAST(300500 AS BIGINT);

SELECT *
FROM dbo.vw_TableAll
WHERE ID > CAST(500000 AS BIGINT)
    AND ID < CAST(500500 AS BIGINT);

Execution Plans:

Do take note that most queries will likely span both tables.  While we won't be able to take advantage of partition elimination in these scenarios, the query plans should remain as optimal as possible with the benefit that you don't have to rewrite your underlying queries (if you decided to name the view the same as your old table).

What to do now?

Well this is wholly dependent upon you.  If the current records are never going to disappear and you're happy with performance from the Partitioned View, pour a celebratory beer because you're done.  Huzzah!

If you want to consolidate the old table into the new table, you're going to have to pick maintenance windows within which to do the next series of operations. Basically, you're going to drop the constraints on the tables (which will break the partition elimination component of the Partitioned View), copy your most recent records over from the old table to the new table, purge these records from the old table, and then update constraints (so the Partitioned View is able to take advantage of partition elimination once again).  Because of the volume of data you have in the existing tables, you may have to go through a few rounds of this process to consolidate everything.  Those steps are summarized as follows:

-- During a maintenance window, transfer old records to new table if you so choose
-- Drop Check Constraint while transferring over records
ALTER TABLE dbo.TestPartitioned_BIGINT DROP CONSTRAINT CK_ID_BIGINT;

-- Retain Identity Values
SET IDENTITY_INSERT dbo.TestPartitioned_BIGINT ON

-- Copy records into the new table
INSERT INTO dbo.TestPartitioned_BIGINT (ID, VAL)
SELECT ID_BIG, VAL
FROM dbo.TestPartitioned
WHERE ID > 300000

SET IDENTITY_INSERT dbo.TestPartitioned_BIGINT OFF

-- Enable Check Constraint after transferred records are complete, ensuring the Check Constraint is defined on the proper ID value
ALTER TABLE dbo.TestPartitioned_BIGINT ADD CONSTRAINT CK_ID_BIGINT CHECK (ID > CAST(300000 AS BIGINT));
GO

-- Purge records from original table
DELETE
FROM dbo.TestPartitioned
WHERE ID > 300000

-- Recreate Check Constraint on original table, ensuring the Check Constraint is defined on the proper ID value
ALTER TABLE dbo.TestPartitioned DROP CONSTRAINT CK_ID_TestPartitioned_BIGINT;
GO
ALTER TABLE dbo.TestPartitioned ADD CONSTRAINT CK_ID_TestPartitioned_BIGINT CHECK(ID_BIG <= CAST(300000 AS BIGINT));
GO

If possible, test this out in a non production environment.  I don't condone testing in production.  Hopefully this answer helps, and if you have any questions, feel free to post a comment and I'll do my best to get back to you quickly.


PostgreSQL exits the shell when importing large data

Is your physical machine using autologout?

$ echo $TMOUT

When there exists a value above zero, you should unset that environment variable.

$ unset TMOUT

http://www.cyberciti.biz/faq/unset-tmout-under-unix-appleosx-bash-ksh-tcsh-shell/


Unable to shrink log file due to transactional replication

In case the solution i gave in comment doesn't works try this :

As you said :
Log_reuse_wait_desc on ServerB.DBB always shows 'REPLICATION'

This means Replication is causing the log not shrinking problem.

DBCC OPENTRAN gives you the traction that is blocking as output which in your case is following :

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

To see which database still needs to be replicated use :

DBCC loginfo

If you are sure everything is replicated to all subscribers run the following query on the each database where the replication is completed.I will repeat make sure everything is replicated before executing this query as it marks all replications as successful and your replication queue will be empty.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Back up the log file and then try shrinking it now it should free up the space.

Source:
https://stackoverflow.com/questions/1238491/how-to-shrink-transaction-log-in-sql-server-database-in-replication


Understanding IAM Pages: extent intervals

You can see by the red arrow what seems to be 16 pages related to the same extent. How is that possible?

The red arrow points to an entry showing that extents starting at 1:176 and 1:184 are allocated to the current entity (both 8-page uniform extents are in use) within the GAM interval.

  The other question I have is related to the pages intervals. Why aren't they contiguous? Take for example the last extent, it will cover pages with id 336 to 22642, or the preceding one, 296 to 328.

They are not extents, they are extent ranges allocated to the current entity (e.g. table, index) within the 4GB GAM interval the current IAM page covers.

The extent ranges shown as NOT ALLOCATED may be in use by other entities.

For more information, see Inside the Storage Engine: IAM pages, IAM chains, and allocation units by Paul Randal.


Consultants using old version of Hibernate ORM for new application - should I be concerned?

There could be several reason they continue to use an older version. It may be they've run into issues with newer versions and know to stay away from them. It could also be that they know the version they're on and can be much more efficient working in it which translates in to dollars.

I see later versions lost support for sharding and had an overhaul to services management. These may be features they lost that they like to use.

I would ask them to explain their reasoning for using the older version and then decide on what to do.

This question might get better answers on Stack Overflow since ORMs are used by software developers more than DBAs.


Rebuild master database only in sqlserver 2012

Well, you cant. So your only option is to backup msdb, model and distribution rebuild the system databases from setup and then restore the backups.

If that is not an option, i.e. the sql server does not start you can rebuild the system databases, then install all patches until your server is on the same build level as before, turn off the sql server service and copy the database files for the other system databases over the current ones, remembering to take a copy of them first.


The size of the tablespace doesn't change when I import a smaller dump or deleting old data

Tablespace size is not changed by creating, dropping or importing tables (if we forget autoextend option). If your tablespace has no data after droping tables, it is easy to resize datafiles or recreate tablespace. If not empty tablespace, after tables drop, you can try to resize datafiles ALTER DATABASE DATAFILE 'file1' RESIZE 20000m.  Note that resizing may not always be done if your tablespace still contain some tables/indexes. In this case you will need to do some other actions before resizing... (alter table move or drop+import or...)


Port SQL Server data to MySQL without Workbench

In retrospect it is probably nicer to just roll my own converter since MySQL uses snake case instead of title case like SQL Server.

So, what I'm going to do is use a PowerShell script like so:

$tables = "FirstTable", "SecondTable"
function script {
    param(
        [System.String] $table
    )
    "set nocount on; select * from dbo."+$table
}

$tables |
% {
   $path = "C:\Data\"+$_+".txt"
   $script = script $_
   sqlcmd -S MyServer -d MyDatabase -W -h -1 -E -Q $script -s `"`t`" -o $path
}

This will export all the data from my SQL Server database and put them in files and then I will import those files into MySQL. Which I haven't quite figured out yet. I let the MySQL Workbench do the initial CREATE TABLE statements and used Vim to make everything snake case.


How To insert records in SqlExpress Database tables through sqlscript while installing setup?

If you have a script that generates your database and all of the objects, you can just append to the end whatever DML you are looking to accomplish.  For instance, if you generated a script for your entire database that contains all of the DDL, just open up that file and at the end of it put something like this:

insert into States
(
   Col1,
   Col2,
   Col3,
   -- etc...
   ColN
)
values
(
   "Col1Val",
   "Col2Val",
   "Col3Val",
   "ColNVal"
),
(
   "Col1Val2",
   "Col2Val2",
   "Col3Val2",
   "ColNVal2"
)

insert into City
(
   -- your column list
)
values
(
   -- your values
)

Likewise, you can just have a separate script file and use SQLCMD to run your DDL script and then have a separate script containing your INSERTs and run that script afterwards.  Here is a link on how to do this with SQLCMD.


This is documented, although not very clearly, in MySQL docs: InnoDB restrictions:

  Cascaded foreign key actions do not activate triggers.


I can't find time zone tables in sys database of MySQL

I used to face the same issue as you and I resolved it by following these quick steps.

Please follow below:

Environment:

MySQL Version: 5.7
OS: Windows 10 (64 bit)

Steps:

Download the package that contains the data files of pre-built time zone tables. You can download here https://dev.mysql.com/downloads/timezones.html

timezone_2018e_posix_sql.zip - POSIX standard for MySQL version 5.7

Extract the downloaded package, you will then have a file named timezone_posix.sql
Open a command line in the root directory of MySQL. Ex: On my
computer this is "C:\Program Files\MySQL\MySQL Server 5.7\bin"
Run the command mysql -u root -p to login to MySQL with root access and then enter password.
Run the command show databases; to view all database. You will see a database named mysql. You need to insert data into the mysql database

Run the command use mysql;

Finally, you can insert data by issuing the command source *path timezone_posix.sql*.

After inserting the timezone data, you can test by issuing the following query:

 SELECT CONVERT_TZ('2018-07-02 14:46:46','utc','America/Los_Angeles')

If the result is not null, you're successful!!!


Fast insert from temp table to main table - advice needed, match index order?

I think that you may need to look at redesigning the clustered index on your main table.  Best practices for a clustered index is to make it unique and ever increasing, but you have the first column being sensorid.  Which means that (assuming I have inferred correctly) that you have a relatively stable set of sensors with lots of date/time and measurements.

This will lead to lots of page forwards and an extremely fragmented, which increases your maintenance duration as your index defragmentation process works.  Without knowing your actual query load, I can only make broad suggestions.

Change your clustered index to be Date/Time, SensorID, pkID
Add a non-clustered index on SensorID, Date/Time

With that in place, your inserts won't fragment the clustered index as badly.  BulkInserts won't cause blocking to read workloads.  Having a clustered index on the stage table that matches it's target will help.


Does tempDB disk perf matter if you have plenty of unused memory?

Even if you have plenty of DRAM, tempdb may still be used.

This happens in a few situations:

Snapshot isolation: Using this feature can create a lot of tempdb activity.

Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is allocated. If it turns out (for example, due to bad statistics) that the allocated memory is too small, then SQL Server will spill operators like hash tables and sort buffers to tempdb, EVEN if there is enough memory after the query has started to satisfy the requirement.

Or course, if your queries are perfectly tuned and you run without snapshot isolation, you can avoid these effects. But in most situations, some amount of tempdb activity is to be expected, even on a large DRAM system.


A selective index will not be used when using query to retrieve the data.

From Selective XML Indexes (SXI) - Supported XML Features

  Selective XML indexes support the XQuery supported by SQL Server
  inside the exist(), value() and nodes() methods.

  For the query() and modify() methods, selective XML indexes may be
  used for node filtering only.

  For the query() method, selective XML indexes are not used to retrieve
  results.

However testing shows that the index is used when you rewrite the query using nodes. These queries returns the same but the second is faster because it uses the the selective XML index.

select data.query('/Log/CallStack/ExceptionThread/Line')
from LogData

select T.X.query('.')
from LogData
  cross apply data.nodes('/Log/CallStack/ExceptionThread/Line') as T(X)

The selective XML index used in the second query is defined as..

[Line] = '/Log/CallStack/ExceptionThread/Line' as XQUERY 'node()',

I used a table of my own just to be able to show what the difference in this case is.

(67992 row(s) affected)
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 1840865, lob physical reads 313514, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 129792 ms,  elapsed time = 180087 ms.

(1850106 row(s) affected)
Table 'xml_sxi_table_622625261_256000'. Scan count 1, logical reads 8416, physical reads 3, read-ahead reads 8403, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 927882, lob physical reads 0, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 65271 ms,  elapsed time = 67636 ms.


Removing Archive Logs after PostgreSQL PITR online-recovery

The documentation on PITR recovery that comes with pgpool is really incomplete, and downright misleading at points.  It suggests keeping all the archive files on the master than then having a recovery_command that uses scp to copy from there to each standby.  That's worthless.  Each standby needs to have its own copy of the archive, or there's no real redundancy there.  If the master fails in that situation, you have nothing useful.  The archive_command on the master should distribute files to the standby systems instead.  There is no need to save a copy of the archive on the master at all if you do that.

For cleaning up the archives on each standby, the poor recommendation for the recovery_command given there shouldn't be followed either.  Instead pg_standby should be called as the recovery command.  See the real documentation on Warm Standby Servers for more details.  If you're using pg_standby, one of the things it should gets passed is the "%r" parameter, which is the information needed to remove old archive logs.  Use pg_standby that way and it will clean up the archives for you.

The main non-trivial part here is writing an archive_command script that properly copies data to both standby servers.  I don't have a good example to point at, but what you need to do is check for errors after each copy, and provide a success return code only if both copies happen.


Is it possible to search within a MySQL JSON array in a case insensitive manner?

Other case, when You can not change data and must have proper result:

select
    id
from test
where json_search(UPPER(tags), 'one', UPPER('lorem%')) is not null

will be slower of course, so need test on real data-size


MySQL - How to check whether all values of a given SELECTed row are equals?

SELECT
   column_a
  ,column_b
  ,column_c
  ,column_d
FROM mytable
WHERE column_a = column_b
AND   column_a = column_c
AND   column_a = column_d

ETC.

SQL Fiddle Link

My Notes

CREATE TABLE mytable
(
column_a int,
column_b int,
column_c int,
column_d int
);

INSERT INTO mytable VALUES (1,2,4,7);
INSERT INTO mytable VALUES (6,3,5,6);
INSERT INTO mytable VALUES (5,5,5,5);
INSERT INTO mytable VALUES (7,1,3,4);
INSERT INTO mytable VALUES (3,3,3,3);


PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

The simple checks work as desired:

CHECK ( lower(period) > '-infinity' )

and

CHECK ( upper(period) < 'infinity' )

Therefore, you can have:

CREATE TABLE bill
(
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  period tstzrange NOT NULL,
  -- other columns
  CONSTRAINT bill_pk PRIMARY KEY (id),
  CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
  CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
  CONSTRAINT bill_period_lower_not_infinity_ck
    CHECK ( lower(period) > '-infinity' ),
  CONSTRAINT bill_period_upper_not_infinity_ck
    CHECK ( upper(period) < 'infinity' )
);


SQL Server database backup(2012) restore on lower version(2008)

You CANNOT do this - there is no downgrade procedure - you just cannot attach/detach or backup/restore a database from a newer version (2012) of SQL Server down to an older version (2008) - the internal file structures are just too different to support backwards compatibility.

You can either get around this problem by

using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances
otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool
or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.


Why is this faster and is it safe to use? (WHERE first letter is in the alphabet)

It depends on the data in your tables, your indexes, .... Hard to say without being able to compare the execution plans / the io + time statistics.

The difference I would expect is the extra filtering happening before the JOIN between the two tables.
In my example, I changed the updates to selects to reuse my tables.

The execution plan with "the optimization"

Execution Plan

You clearly see a filter operation happening, in my test data no records where filtered out and as a result no improvements where made.

The execution plan, without "the optimization"

Execution Plan

The filter is gone, which means that we will have to rely on the join to filter out unneeded records.

Other reason(s)
Another reason / consequence of changing the query could be, that a new execution plan was created when changing the query, which happens to be faster.
An example of this is the engine choosing a different Join operator, but that is just guessing at this point.

EDIT:

Clarifying after getting the two query plans:

The query is reading 550M Rows from the big table, and filtering them out.

Meaning that the predicate is the one doing most of the filtering, not the seek predicate. Resulting in the data being read, but way less being returned.

Making sql server use a different index  (query plan) / adding an index could resolve this.

So why doesn't the optimize query have this same issue?

Because a different query plan is used, with a scan instead of a seek.

Without doing any seeks, but only returning 4M rows to work with.

Next difference

Disregarding the update difference ( nothing is being updated on the optimized query)
a hash match is used on the optimized query:

Instead of a nested loop join on the non-optimized:

A nested loop is best when one table is small and the other one big. Since they are both close to the same size I would argue that the hash match is the better choice in this case.

Overview

The optimized query

The Optimized query's plan has parallellism, uses a hash match join, and needs to do less residual IO filtering. It also uses a bitmap to eliminate key values that cannot produce any join rows . (Also nothing is being updated)

The non-optimized query

The non-Optimized query's plan has no parallellism, uses a nested loop join, and needs to do residual IO filtering on 550M records. (Also the update is happening)

What could you do to improve the non-optimized query?

Changing the index to have first_name & last_name in the key column
list:

CREATE INDEX IX_largeTableOfPeople_birth_date_first_name_last_name
on dbo.largeTableOfPeople(birth_date,first_name,last_name)
include(id)

But due to the use of functions and this table being big this might not be the optimal solution.

Updating statistics, using recompile to try and get the better plan.
Adding OPTION(HASH JOIN, MERGE JOIN) to the query
...

Test data + Queries used

CREATE TABLE #smallTableOfPeople(importantValue int, birthDate datetime2, first_name varchar(50),last_name varchar(50));
CREATE TABLE #largeTableOfPeople(importantValue int, birth_date datetime2, first_name varchar(50),last_name varchar(50));

set nocount on;
DECLARE @i int = 1
WHILE @i <= 1000
BEGIN
insert into #smallTableOfPeople (importantValue,birthDate,first_name,last_name)
VALUES(NULL, dateadd(mi,@i,'2018-01-18 11:05:29.067'),'Frodo','Baggins');

set @i += 1;
END

set nocount on;
DECLARE @j int = 1
WHILE @j <= 20000
BEGIN
insert into #largeTableOfPeople (importantValue,birth_Date,first_name,last_name)
VALUES(@j, dateadd(mi,@j,'2018-01-18 11:05:29.067'),'Frodo','Baggins');

set @j += 1;
END

SET STATISTICS IO, TIME ON;

SELECT  smallTbl.importantValue , largeTbl.importantValue
FROM #smallTableOfPeople smallTbl
JOIN #largeTableOfPeople largeTbl
    ON largeTbl.birth_date = smallTbl.birthDate
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.last_name)),RTRIM(LTRIM(largeTbl.last_name))) = 4
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.first_name)),RTRIM(LTRIM(largeTbl.first_name))) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
AND LEFT(RTRIM(LTRIM(largeTbl.last_name)), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å');

SELECT  smallTbl.importantValue , largeTbl.importantValue
FROM #smallTableOfPeople smallTbl
JOIN #largeTableOfPeople largeTbl
    ON largeTbl.birth_date = smallTbl.birthDate
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.last_name)),RTRIM(LTRIM(largeTbl.last_name))) = 4
    AND DIFFERENCE(RTRIM(LTRIM(smallTbl.first_name)),RTRIM(LTRIM(largeTbl.first_name))) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
--AND LEFT(RTRIM(LTRIM(largeTbl.last_name)), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å')

drop table #largeTableOfPeople;
drop table #smallTableOfPeople;


Oracle sql plan for a select with procedure in terms section

Yes, there can be changes in the plan, and they can be quite drastic in terms of performance.

To be sure in your specific case, there's only one way to find out: look at the plans and check (i.e. measure with real data and up-to-date statistics) the performance.

The potential problem with your query with the function is that it will prevent the optimizer from using an index on atbl.fld if there is one – this can result in drastic performance differences.
If there isn't an index, you'll probably get the same plan.

(And if  checkme is deterministic you can create a function-based index to optimize that query if necessary.)


How can I set a default session sql_mode for a given user?

As long as the account in question doesn't have the SUPER privilege, you could accomplish this with the global server variable init_connect, which is...

  A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters.

This is part of the global server configuration and gets executed right before processing of the first query sent by each new client connection (except for users with SUPER, because if you broke this, you couldn't log in to fix it otherwise).

The init_connect contents are executed by the server for all non-SUPER users... however, we could construct a CASE expression that uses the identity of the logged-in user via the CURRENT_USER() function and evaluates to the appropriate sql_mode for that user, and otherwise use the default.

The expression might be something like this:

mysql> SET @@sql_mode = CASE CURRENT_USER()
                        WHEN 'usera@%' THEN 'TRADITIONAL'  # this user gets 'TRADITIONAL'
                        WHEN 'userb@localhost' THEN 'ANSI' # this user gets 'ANSI'
                        ELSE @@sql_mode                    # everybody else gets the default value,
                        END;                               # from global sql_mode

Once you have the expression you want, wrap it up in string and assign it to the global variable init_connect.

mysql> SET GLOBAL init_connect =
           "SET @@sql_mode = CASE CURRENT_USER()
                             WHEN 'usera@%' THEN 'TRADITIONAL'
                             WHEN 'userb@localhost' THEN 'ANSI'
                             ELSE @@sql_mode
                             END;";

The same thing could then be added to the [mysqld] section of my.cnf to make your configuration persist across restarts, although you'd almost certainly have to remove the whitespace I added for readability and possibly adjust the quoting.

Tested on MySQL Server 5.5.30.  I attempted to benchmark whether this has any significant impact on initial connection time or first query execution time and my conclusion is that if there is an impact, it is most definitely in the sub-millisecond range and was too short to identify on the system under test.


Access denied for user 'root'@'localhost' What proces is causing this?

One way to do this might be by finding out what local processes have the MySQL socket file open. First, determine where your socket file is. Not sure where it is on Debian, try /tmp/mysql.sock or /var/lib/mysql/mysql.sock, or check my.cnf for the location.

Next, since the failing connection comes and goes, you will need to "freeze" it temporarily in order to catch it in the act. You can do it by stopping the mysqld process. For example, run pgrep -l mysqld to determine its process ID, then sudo kill -STOP <PID> (replace <PID> with the actual ID, obviously).

Now you can run sudo lsof | grep mysql.sock (or whatever your socket file happens to be) to see every process that has the file open.

To "un-freeze" mysqld, run sudo kill -CONT <mysqld PID>


Safe delete Transaction Log in SQL Mirroring scenario

Transaction log is the most important element in SQL Server. It is like a sequential journal that logs all the changes made to the database and contains enough information to undo / redo the changes in the event of crash recovery to keep the database in a consistent state.

Highly recommend to read : Understanding Logging and Recovery in SQL Server

All your question are just because you dont have proper understanding of how transaction log works.

To keep transaction log in shape, you should take regular log backups (more frequently) on the principal server. Check - How do we handle transaction log maintenance for a mirrored database?

To answer your questions :

  Would it be safe taking full backup of Principal Server and then delete the transaction log completely?

No this is not possible and not advisable to delete T-Log.

  Does it have to do something with Full Recovery/ Simple Recovery Mode?

Mirroring uses T-log to replay everything from principal server to mirror server. So Full recovery is only possible. check : recovery models.

  What would happen when the transaction log on the Principal Server is deleted and the after sometime Mirror Server will take the place of the Principal?

Not feasible. Please read the links that I have suggested.


It would appear neo4J (well, the web site) was just totally broken for some days there (around Aug 7 2017).

As reported by wBob, it does seem to be working now!

It's worth noting that the basic problem (reported in the question above) seems to be resolved.  However, after you "create a sandbox" the system appears to fail when you "go to the data browser" in that sandbox ... but it would appear that it simply works after, say, a few minutes (there's no warning while it is getting going).

Once you get past all that it does seem to work, eg the "movie" sandbox:


Sort operator positioning in an execution plan

It looks like you have a classic parameter sniffing issue. As far as I can tell your queries are identical but both of them use different cached plans. The cached plan that you get depends on the input parameters for the first run. I assume that there's a plan that works well enough for all inputs and you want that plan. However, depending on parameter values you might get unlucky and end up with a plan that works better for some inputs but poorly for others.

If possible I would add a RECOMPILE hint to the query. This will prevent the query optimizer from using a cached plan for the query but it will see the values of the variables when making the plan. As a general rule of thumb you'll get better performance if you can give more information to the optimizer. The downside of this approach is that the query plan will be recompiled each time, but this is only a three table query. I wouldn't worry about it unless the query runs hundreds of times per second.

Another option is to use the OPTIMIZE FOR query hint. This will give you a cached plan that isn't based on input values, so the key is getting a query plan that works well enough for all possible input values. In your other question you implied that you weren't satisfied with the default guess of 100 rows for TOP so you can force the query optimizer to optimize for a specific TOP value like follows:

OPTION (OPTIMIZE FOR UNKNOWN, OPTIMIZE FOR (@top = 5000));

None of the input variables will be sniffed to form the plan. That syntax may look odd but it's allowed:

  If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.

If neither one of those options works and you're confident that you have a query plan which works well you could look into using plan guides.


PostgreSQL: How do I stop PostgreSQL from generating logs for PL/pgSQL functions?

The presence of CONTEXT depends on log_error_verbosity.
Another setting you might want to tune down is log_min_error_statement.
From the documentation:
log_min_error_statement (enum)

Controls which SQL statements that cause an error condition are
recorded in the server log. The current SQL statement is included in
the log entry for any message of the specified severity or higher.
Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE,
WARNING, ERROR, LOG, FATAL, and PANIC. The default is ERROR, which
means statements causing errors, log messages, fatal errors, or panics
will be logged. To effectively turn off logging of failing statements,
set this parameter to PANIC. Only superusers can change this setting.

log_error_verbosity (enum)

Controls the amount of detail written in the server log for each
message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE,
each adding more fields to displayed messages. TERSE excludes the
logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE
output includes the SQLSTATE error code (see also Appendix A) and the
source code file name, function name, and line number that generated
the error. Only superusers can change this setting.


I have experience this exact same thing in the past.  It usually happens because you have linked psql against an inferior readline-like library.  If you are using libedit, use libreadline instead.  If you are using libreadline, use a different one.  Details depend on your operating system and method of installation.


This applies more to other trace flags (eg 610 for minimal logging) that affect the optimiser and important behaviours/optimisations

You should be OK with the deadlock trace flags 1204, 1205 and 1222


Better execution plan if parameter is stored in local variable

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available.

From the execution plan provided, this is exactly what happened in your case.

When a local variable is used, the value in the variable cannot be sniffed:

Notice the blank "Compiled Value". The query optimizer estimates a higher number of rows based on the average distribution of values in the Date column, (or possibly a complete guess) leading to the parallel plan.

When the stored procedure parameter is used directly, the value of @week is sniffed:

The optimizer estimates the number of rows that will match the query predicates using the value '2016-02-08', plugged into:

and cast(@week as datetime2(3)) <= [Date]
and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))

It comes out with an estimate of one row, leading to the choice of a serial plan with the key lookup. The predicates above are not very friendly for cardinality estimation, so the 1-row estimate may not be very accurate. You could try enabling trace flag 4199 but there's no guarantee the estimate will improve.

For more details please see:

Parameter Sniffing, Embedding and the RECOMPILE Options

In general, it is also possible that the initial run of the stored procedure occurs with a very selective value for @week, with only a small number of rows expected. Another possible cause of problems occurs when a very recent value of @week is used on the initial call, before statistics had been updated to cover this range of values (this is the Ascending Key Problem).

A very selective sniffed value for @week may cause the query optimizer to choose a non-parallel plan with an index seek and a key lookup. This plan will be cached for reuse for future executions of the procedure with different parameter values. Where a later execution (with a different value for @week) selects many more rows than originally, the plan is likely to perform poorly, because seek + key lookup is no longer a good strategy.


How to persist messages in Postgresql's LISTEN/NOTIFY?

You can't replay them. If no one is listening, they're gone. Start up your own communication daemon to catch them. Then you can do whatever you want.


MySql sort conditional based on column values

I think this would work - although it's rather obfuscated:

ORDER BY
    price = 0,
    LEAST(stock, 100) DESC,
    price ;

More clearly:

ORDER BY
    CASE WHEN price > 0 THEN 1 ELSE 2 END,
    CASE WHEN stock >= 100 THEN 100 ELSE stock END DESC,
    price ;


Restarting SQL Server's services remotely programmatically

Please see http://technet.microsoft.com/en-us/library/hh403394.aspx - you missed for example PowerShell.

Pay extra attention if you're using a clustered instance.


Failed to import a mongodb database with duplicate fields

I have successfully upgraded a database from 2.4 to 4.0 using the following 3 step method:

use 2.4 mongobackup on the 2.4 database
delete all .json files from the outputted dump directory
use 4.0 mongorestore on the remaining .bson files


How to retrieve master log position and master log file in shell script

There is a cleaner approach

SMS=/tmp/show_master_status.txt
mysql -ANe "SHOW MASTER STATUS" > ${SMS}
CURRENT_LOG=`cat ${SMS} | awk '{print $1}'`
CURRENT_POS=`cat ${SMS} | awk '{print $2}'`
echo ${CURRENT_LOG} ${CURRENT_POS}

This logs into mysql only once.


MySQL Master-Slave Replication Cross Database

I addressed something like back on June 14, 2012 : Configured MySQL replication but its not working
The basic problem may be the queries you are using
If you have a query that looks like this
INSERT INTO db1.tb SELECT * FROM db2.tb;

You could be potentially blocking queries of this nature
Queries like this:
USE db1
INSERT INTO tb SELECT * FROM db2.tb;

should work if you have replicate-do-db=db1 and Replicate-wild-do-table=db.%
To be sure, check out the replication filtering rules to make sure your queries are allowing your current settings to work on the Slave.
According to the replication filtering rules

To make it easier to determine what effect an option set will have, it
is recommended that you avoid mixing “do” and “ignore” options, or
wildcard and nonwildcard options. An example of the latter that may
have unintended effects is the use of --replicate-do-db and
--replicate-wild-do-table together, where --replicate-wild-do-table uses a pattern for the database name that matches the name given for
--replicate-do-db. Suppose a replication slave is started with --replicate-do-db=dbx --replicate-wild-do-table=db%.t1. Then, suppose that on the master, you issue the statement CREATE DATABASE dbx.
Although you might expect it, this statement is not replicated because
it does not reference a table named t1.

Perhaps you need to get rid of one of those rules on the Slave and restart mysql on the Slave.


Pros and cons of row level security Microsoft SQL Server

Pros

Row level security (RLS) reduces your application development complexity. Without RLS, you'd generally create filters with your queries or through ORM, or pass on filtering criteria to your procs. Once you have RLS in place, user1 sees what you desire user1 to see without changing your application much.

With RLS, security is at the DB level. If another application was reading data in a different language from a different platform, RLS still allows user1 to see what you want user1 to see. If someone is trying to run/test SQL queries to SSMS, RLS shows data according to your setup.

Your DB backup and restore will keep RLS intact.

Cons

The disadvantage is more DB-centric work and small amount of performance penalty. If you have web developers who aren't strong on the DB side, troubleshooting RLS might take them a bit of time. On the contrary, if application layer was filtering information through an ORM, you could debug and write auditing on the application or middle-ware side.

As far as the overheads of row level security go, here's an article that details some challenges: https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ and https://www.mssqltips.com/sqlservertip/4778/performance-impact-of-sql-server-2016-rowlevel-security/.

To use RLS or not?

If you are comfortable with RLS, I'd recommend you try it out. Microsoft has listed some RLS-related best practices here: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017.

If your DB is well protected and access to data is through application layer only, you can opt to build filtering within your application layer. It's not a lot of work to do security checks at the application layer AND use RLS. There isn't a one size fits all answer. For most simpler applications, I tend to use the application layer for filtering since it has served me well with auditing/logging/debugging.


Would adding an INDEX on varchar column in Mysql increase performance?

Answer is yes, indexing varchar data field increases the performance but it depends on the query you performed against that particular table. Lets consider the following two entities,

Card

Card Id (PK)
Card Type
Card Name

Registration

Reg Id (PK)
Card Id (FK)
Number of Registration

Lets consider a query, what are the total number of registration by card type?

Here, the card type is not frequently updated, it is either Credit or Debit Card. Therefore index on the card type data field is a good choice. Card type contains the values Credit card and Debit Card only.

If your non-key data field have such data and you need to perform the query in such a way mentioned above, you can create a non-unique key index for performance.

I hope this answer will help you. Thank you.


You can use either a Profiler trace or sp_whoisactive to get the number of reads per session.  Reads are the number of logical disk reads in 8KB pages, so if the number of reads is 2, for example, that might equate to 16KB of disk I/O.


Check back on my comments to your three previous questions on designing an accounting application schema (avoid cross and or repeat-posting BTW).

Schema  design  is an iterative process. There must be a billion issue tracker programmes out there - search for "open source issue/bug/ticket/problem tracker" and you'll get loads of schemas with which to experiment.

Personal recommendation on this one, at least for initial study: D. Richard Hipp's bug tracker called Fossil - he uses an SQLite database to store the schema. From the Fossil site "Fossil is a simple, high-reliability, distributed software configuration management system" and the software that Hipp produces is stellar!

Look at those and then design your own schema. I don't know what development environment you're using, but take your first pass at a schema, and then run some code against it. Then fix the bugs (there will be bugs) and do the same thing again. Rinse and repeat until you get a programme that works to your own satisfaction.

If this is for some commercial need, then I suggest that you use an already available F/LOSS programme, unless your company has some compelling requirement that can only be fulfilled by a COTS product. You haven't mentioned any particular database server - that could be a factor in any choice of COTS and indeed F/LOSS. Some companies offer free products - Oracle has a bug tracker written in Apex for which the source is available and it's interface is very slick - your choices are virtually limitless in this domain.

If this is a school/uni project and your professor seems to enjoy handing out schema design projects (IMHO, a bookkeeping system is a bit much for a first project), then you should a) tell us this up front and b) show us that you have made some effort - which you have - your ERD above shows that you've at least thought about the problem.


Getting "BadImageFormatException" when attempting to connect through Visual Studio to Oracle

I discovered the solution myself. I found that apparently when I had installed Oracle Client, it had installed on my external drive E the first time and became Oracle Home #1 in the Windows registry. I don't believe it was "looking" at the Oracle client I had installed on the C drive even though it was first in the path. I uninstalled the Oracle clients on both the C and E drive and reinstalled the client on the C drive. I am now able to connect to Oracle with Visual Studio.


Remove duplicate records from table, leaving one copy

Not sure if this would work in mysql since i am a MSSQL-dba but you could add an identity column -> auto_increment.
This would make all rows unique.
Then you could do a group by on duplicate records and select the highest ID for example. This way you will only delete one row.
Repeat this until no more duplicates.


Error when sending email with attachment using SQL Server login

As per books online:

  Database Mail uses the Microsoft Windows security context of the
  current user to control access to files. Therefore, users who are
  authenticated with SQL Server Authentication cannot attach files using
  @file_attachments. Windows does not allow SQL Server to provide
  credentials from a remote computer to another remote computer.
  Therefore, Database Mail may not be able to attach files from a
  network share in cases where the command is run from a computer other
  than the computer that SQL Server runs on.

I was able to reproduce this using my AD account.  While running following code remotely I get the error.  Running the same code by logging into the host was successful.

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'taiob.ali@company.com',
@file_attachments = 'd:\FailedLogins_0_131397600848650000.xel',
@subject = 'testing file attachment'

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 [Batch Start Line 0]
    The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Now if that is not your case (meaning the file is on a network share) CREDENTIAL should work.

According to this CREDENTIAL did not work. But the 2nd answer did work though it poses security risk.

There is a connect item about this and here is a comment by Aaron Bertrand which supports the answer I refered above.

  If it is indeed a case of reduced privileges, you can work around this
  by routing mail through a wrapper procedure that uses EXECUTE AS (or
  is signed). Personally, I think that all of your send mail calls
  should go through a single wrapper procedure anyway (makes
  modifications, profile changes, etc. much easier to propagate).


How to check innodb history list length?

I can't find documentation confirming it anywhere, but I think it's:

select count from information_schema.innodb_metrics where name = 'trx_rseg_history_len';

The closest thing to a confirmation is a bug report referring to this counter as the history list length. Also, the current, min and max values match the logs I have recorded. It would be nice if someone familiar with the MySQL source code could confirm it.


generate array of unique uuid in postgreSQL

Assuming you don't actually want them in an array, since you are unnesting it right away, here's how to generate a table of any amount of UUIDs:

WITH RECURSIVE genx(n, uu) AS (
  VALUES (1, uuid_generate_v4())
  UNION ALL
  SELECT n + 1, uuid_generate_v4()
  FROM genx WHERE n < 30
) SELECT uu FROM genx;

Change the 30 to as many as you want!


One way of doing this is to have a temporal table for business time (sometimes called valid time) as history. Basically what you have is a copy of your products table with additional attributes for begin_time and end_time:

CREATE TABLE PRODUCT_HISTORY
( ...
, BEGIN_TIME TIMESTAMP NOT NULL
, END_TIME TIMESTAMP
,    PRIMARY KEY (<key>, BEGIN_TIME) );

When you add a product you add a row to the history with BEGIN=now and END=null.

When a product changes you update the row where END=null to END=now, and add a new row as for insert.

When a product is removed update the row where END=null to END=now

All these manipulations of the history table are easily implemented via triggers.


what are the checklist when looking at MySQL database for first time?

This question is very general and I'd suggest clarifying it with more context, but I'll give it a shot.

I would look at:

security.  What are the users that have privileges on that database?  What are the privileges (i.e. read-only on specific tables, or do they have grant option permissions)?
entities.  What are the major entities and their relationships?
performance.  What queries are resource hogs?  Are appropriate indexes in place?
denormalization.  Is the schema purposely denormalized?  If so, why and is it appropriately denormalized?
foreign keys.  Are they enforced (do the tables use InnoDB)?
size.  What are the biggest tables?  Which tables are expected to grow the fastest?


Please provide SHOW CREATE TABLE.

Indexes needed:

users_interest:  (user_to, new)
users_view:  (user_to, new)
mail_msg:  (user_to, folder, new)
users_block:  (user_from) and (user_to, user_from)
user:  (activated, online) and (activated, last_visit)

The construct NOT IN ( SELECT ... ) performs poorly; change it to a LEFT JOIN ... WHERE ... IS NULL.  For example:

SELECT ... FROM user
    WHERE user_id NOT IN ( SELECT user_from
                         FROM users_block WHERE user_to=1 )
      AND other_stuff

-->

SELECT ... FROM user AS u
    LEFT JOIN user_from AS ub  ON u.user_id = ub.user_from
    WHERE ub.user_from IS NULL
      AND other_stuff

Or (I don't know whether this is better):

SELECT ... FROM user AS u
    WHERE NOT EXISTS ( SELECT * FROM users_block
                     WHERE user_to=1 AND users_from = u.user_id )
      AND other_stuff

A suggestion:  Instead of having 5 rows of unlabeled counts, do something like

( SELECT 'hidden count' AS total, COUNT(*) AS ct ... )
UNION ALL
( SELECT 'blocked to  ' AS total, COUNT(*) AS ct ... )
...

so that each count is labeled.


What does this Oracle TO_CHAR format do: "FM99990.90"

Of course it is listed/documented.

Format Model Modifiers

  FM

  Fill mode. Oracle uses trailing blank characters and leading zeroes to
  fill format elements to a constant width. The width is equal to the
  display width of the largest element for the relevant format model:

  Numeric elements are padded with leading zeros to the width of the
  maximum value allowed for the element. For example, the YYYY element
  is padded to four digits (the length of '9999'), HH24 to two digits
  (the length of '23'), and DDD to three digits (the length of '366').

  ...

Example:

SQL> select to_char(1.5, 'FM99990.90') from dual;

TO_CHAR(1
---------
1.50

SQL> select to_char(1.5, '99990.90') from dual;

TO_CHAR(1
---------
     1.50

SQL>


Here's what I did

Open phpmyadmin directory
You will see config.sample.inc php file
Clone it, but remove the sample from the title
After cloning, open the file config.inc and look for $cfg['Servers'][$i]['AllowNoPassword'] = false;
Set it to true

By doing this, we are allowing the phpMyAdmin to submit an empty password.

Now, try root as a username and leave the password blank.

Hope this will work for you as well.


Your script is correct, and if you get the error

  The object 'postTB' does not exist in database 'cwpros' or is invalid
  for this operation.

this means some of the following:

you created your table in other db than cwpros
someone dropped your table
your default schema is not dbo and you have anothe object with the
same name in your schema, but it's not a table

So first of all make sure that the object does exist in your database:

select *
from sys.objects
where name = 'postTB'


Can't optimize a nested query using indexes

Your best simple index is probably going to be on (client_id, task_date, status).  client_id should go first, because it is the only one used with an equality operator.  Including status will only help for index-only scans, so it depends on how well vacuumed task is and your version of PostgreSQL.

The partial index will not lead to incorrect results.  Queries that cannot correctly use the partial index, will not use it.  So if WHERE status != 2 is the most commonly queried case, or the most time-sensitive one, go ahead and make the partial index.


What is the recommended memory_target and memory_max_target for ORACLE 12 DB running on a dedicated server with 32GB RAM?

Forget memory_target and memory_max_target. AMM has always been a feature that worked on paper, but failed in practice. Oracle finally admitted it semi-officially as of the release of 12.2:

When I have no information about earlier memory usage, I just go with a 1:4 ratio for PGA:SGA target.

sga_target=20G
pga_aggregate_target=5G

Which implicitly sets:

sga_max_size=20G
pga_aggregate_limit=10G

Then later you can tune if needed, after observing the workload and memory usage.


Currently you have:

from
(
   'Select * from #info Where name = ''' +@name + ''' '
) as d

This isn't valid, because you can't say FROM ('some query'), that's like saying FROM ('something that is not a query') because it is just a string. Try:

from
(
  Select * from #info Where name = @name
) as d

...in other words, why all the string wrapping? I did not parse the code, so there may be other problems, too. But that's the immediate one.


calling a function with parameters in insert

You can build a 'table' using the VALUES clause and build the function call on top of it:

INSERT INTO wgs84 (wkb_geometry, wkb_geography)
SELECT st_transform(st_setsrid(st_makepoint(col1, col2), col3), col4)
  FROM (
        VALUES (694274.6998, 5341745.2959, 25832, 4326),
               ( 69425.6998, 5341754.23,   25834, 4327)
       ) t (col1, col2, col3, col4);

This is easier to read, to write, and to modify than the repeated function calls.  You can even align your data which sometimes makes finding errors easier.

A version of this can be putting the VALUES into a WITH query.  In some cases it is better to put (preferably using the COPY command) the data into a temporary table and selecting from there in the INSERT statement - this comes handy when the input size gets bigger.


MySQL high availability, failover and replication with Latency

For the sake of simplicity, I recommend MySQL Circular Replication only. Here is why:

There are many technologies and topologies that are far superior to MySQL Circular Replication. My favorite, hands down, is DRBD (Distributed Replicated Block Device). However, DRBD works great when the Server Pair is in the same bulding, data center, and rack. It's even better when using a crossover cable in the 192.168.x.x. subnet between the DRBD Primary and DRBD Secondary. Unfortunately, DRBD has horrible performance over a distance between two locations, although DRBD can still work. There are no network topologies around to give you the satisfactory DRBD performance needed between two datacenters.

Once you setup MySQL Circular Replication between the two DB servers in two different data centers, the only tuning needed is for the network. In essence, the replication performance is a function of network settings (speed/latency of binary log transmission in MySQL Replication Setup) and disk I/O (DRBD).

An alternative you may want for better redundancy is the following for the sake of example:

Setup a DRBD Pair in both locations
DRBD Pair in Site #1 with VIP 111.111.111.111
DRBD Pair in Site #2 with VIP 222.222.222.222

Setup MySQL Circular Replication between the DRBD Primary Servers under these conditions:
For site #1, use 222.222.222.222 as the Master_Host in MySQL
For site #2, use 111.111.111.111 as the Master_Host in MySQL

Although introducing a level of complexity, now you have two levels of redundancy: DRBD within each site and MySQL Circular Replication between sites. You have the additional benefits of running backups via mysqldump on the DRBD Primary of the hot standby server.

As for failover, DRBD provides automatic failover at any one site.

Only in the event that a datacenter is totally unavailble would you using the DB VIP at the hot standby site.

UPDATE

I just did a double take and noticed that you are using Drupal6. I am glad you will be converting all the drupal tables to InnoDB. This will remove any chance of MyISAM table updates causing table locks to freeze DB Connections that are simply reading MyISAM tables. Any DML update (INSERTs, UPDATEs, DELETEs) against a MyISAM table WILL ALWAYS DO A FULL TABLE LOCK !!! Using InnoDB will introduce row-level locking, which eliminates full table locks.

In addition, DRBD becomes your friend when everything is InnoDB because crash recovery will be consistent between the DRBD Pair. Contrawise, DRBD with MyISAM buys you nothing because a crashed MyISAM table on the DRBD Primary is simply duplicated to the DRBD Secondary as, you guessed it, a crashed MyISAM table.

UPDATE #2

You should use two levels of redundancy

Level 1 : At each database center, use DRBD.
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd.html

Set up a pair of DB Servers
Startup DRBD
Startup MySQL on the DRBD Primary

This creates redundant data at the disk level.

Level 2 : You should setup MySQL Circular Replication between
the DRBD Primary of DataCenter#1 and the DRBD Primary of DataCenter#2

Each DRBD Primary will be running MySQL and will act
as both Master and Slave to Each Other

I have setup for clients topologies like this and I consider it quite stable.


Why is my database still fragmented after I rebuilt and reindexed everything?

The tables are tiny. The page counts in your tables are:

11, 8, 6, 5, 13, 8, 10

They occupy 480kb in total. There is quite literally nothing to defrag.

Edit: This warrants a little more explanation.

A new table or index is usually allocated it's first 8 pages from a mixed, rather than uniform extent. So, it's possible for each of the first 8 pages to be allocated from different mixed extents. A table or index consuming 8 pages could therefore have 8 fragments, 1 on each of 8 different mixed extents.

The more widely used defrag scripts (a couple of examples linked below) tend to exclude small tables because of this. IIRC, <500 pages is in one or both of them. At these sizes, there is very little benefit to defragmenting and the fragmentation figures are potentially skewed by the mixed extent allocations.

Ola Hallengren - SQL Server Maintenance Solution
SQL Fool - Index Defrag Script


If I can remember correctly, I fixed it by setting the innodb_force_recovorey level or I just purged the mariadb package and restored from .sql files


Remove field from composite key and collate duplicate data

If the four records dictate a unique and you want the first one by date, then why not insert into another table, truncate, then insert back once you finish the table reconfigure (or create a new table with the right columns, insert into, then rename after dropping?) I don't think this can be improved upon, unless you want to delete from where not equal to, but the insert into should be faster (needs to be tested to prove for sure)


Multi Select Statement With inner join multiple table

Here is the answer

SELECT U.*,
       GROUP_CONCAT(DISTINCT S.`name`) AS subject,
       GROUP_CONCAT(DISTINCT C.`name`) AS class,
       GROUP_CONCAT(DISTINCT L.`name`) AS location
FROM `user` U INNER JOIN
     `user_to_subject` UTS
     ON U.`u_id` = UTS.`u_id` INNER JOIN
     `subjects` S
     ON S.`s_id` = UTS.`s_id` INNER JOIN
     `user_to_class` UTC
     ON U.`u_id` = UTC.`u_id` INNER JOIN
     `class` C
     ON C.`c_id` = UTC.`c_id` INNER JOIN
     `user_to_location` UTL
     ON U.`u_id` = UTL.`u_id` INNER JOIN
     `location` L
     ON L.`l_id`=UTL.`l_id`
WHERE U.`u_id` = 913;

Find the fiddle here. http://sqlfiddle.com/#!9/cbbbda/1


Count numbers in scientific notation (E-notation)

This would do it:
SELECT count(*) AS overall
     , count(*) FILTER (WHERE partnumber ~* '^[-+]?\d*\.?\d+e[-+]?\d+$') AS matches
FROM mytable
-- WHERE .. -- no outer WHERE

~* is the case-insensitive regular expression match operator.
The pattern'^[-+]?\d*\.?\d+e[-+]?\d+$' only applies to numbers in E-notation (without blanks).
Details in the manual.


Two MySQL requests at the same time - Performance issue

It looks like 'page swapping' is the problem here.

When you deal with one table at a time, its indexes and parts of the table are loaded into the RAM. If you keep dealing with the same table, it will be fast as the table already exists in the ram.

On the other hand, if you keep changing the table that you are dealing with, then the data in RAM has to be written to the disk, and the second table's info will be loaded to RAM. This is one of the MOST EXPENSIVE operations.

Solutions!! Not straight forward though: You may distribute your DB, or run your scripts sequentially. Hardware upgrade may make the process faster.


How we can design best optimized solution: Generate huge random numbers and check in database table whether this numbers are there or not

Put the million numbers in a table.  Then JOIN the tables together to see which ones exist.  Or LEFT JOIN ... IS NULL to see which are missing.


SQL Server 2008 R2 database mirroring - is it possible to have a secondary server for multiple primary servers?

A SQL Server instance can host multiple mirroring partnerships. Mirroring is between databases and databases are hosted on SQL Server instances, so choosing a name like 'DBx' for servers is a rather poor choice. Lets call the instances SRV1, SRV2 and SRV3 and the databases DB1 and DB2. What is possible is:

to have the database DB1 mirrored by SRV1 (primary) and SRV2 (secondary)
to have the database DB2 mirrored by SRV3 (primary) and SRV2 (secondary)

But the question is What do you use mirroring for? Primarily it is a High Availability feature. The question is: will the SRV2 instance be able to take over the primary role for both DB1 and DB2 in case of failover? Nothing prevents both SRV1 and SRV3 from suffering a problem at the same time. Also, if SRV2 suffers a disaster, you will have to come up with a backup plan to restore the HA for both DB1 and DB2. Are you prepared to handle all these cases?

  Would this configuration be possible using separate instances on DB2?

Yes, but is not necessary.


What is the Difference between IN and Not IN performance wise?


Dynamically records not getting to cursor in trigger

Don't use a CURSOR in a Trigger.

Do use INSERT ... ON DUPLICATE KEY UPDATE ... instead of your select+insert/update.

IODKU can handle multiple rows at once, but I don't see why that would happen.  Perhaps Cursor's SELECT belongs there?

A typical pattern is:

INSERT INTO tbl (...)
    SELECT ...
    ON DUPLICATE KEY UPDATE
        a = VALUES(a), ...


Sql Server Index Data File Drive Location

It sounds like the vendor's snapshotting functionality is putting some pretty serious limitations on your flexibility for database layouts.  If you have performance issues (and it would be a very long way from the first time someone had database performance issues on a SAN) then you have very little flexibility to spread the database across multiple physical volumes.

Does the vendor insist you also put the log files on the same volume as well?  If so, then your log files will be on a busy shared volume with no write buffering from the SAN's cache due to Microsoft's policy I/O reliability certification programme for SQL Server.  MS requires SAN vendors to honour FILE_FLAG_WRITETHROUGH, which SQL Server opens its files with.  If your SAN vendor is going to force ALL files for a database to live on the SAME volume you could have performance issues.

I'd look into alternatives to the snapshot manager for DR before signing up to that.  What did you do on your old system?

EDIT Per the chat - if you can use mount points to organise your database files then one set of LUNs per database is probably better than forcing all the databases into the same format.


See this answer. No, the order you put the conditions in the WHERE clause doesn't matter: In MySQL, does the order of the columns in a WHERE clause affect query performance?

If id is not nullable, you can replace the count(id) with count(*) and  try adding an index on (_campaign, session, timeCreated).

ALTER TABLE mDelivered
  ADD INDEX campaign_session_timeCreated_IX        -- choose a name for the index
    (_campaign, session, timeCreated) ;

And you don't need the GROUP BY since you are already restricting the rows to fixed _campaign and session values. Try this:

SELECT count(*) cnt
FROM mDelivered
WHERE _campaign = 2
  AND `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
  AND timeCreated > NOW() - INTERVAL 1 HOUR ;


First, you can set up Canary table and other detailed monitoring of transactional replication per Kendra Little'ss article Monitoring SQL Server Transactional Replication.  Canary tables, the easiest step in the article, are described as:

  It’s easy to build your own system for tracking replication latency for each publication. Here are the ingredients for the simplest version:

  Add a table named dbo.Canary_PubName to each publication
  dbo.Canary_PubName has a single row with a datetime column in it
  A SQL Server Agent job on the publisher updates the datetime to the current timestamp every minute
  A SQL Server Agent job on the subscriber checks dbo.Canary_PubName every minute and alerts if the difference between the current time and the timestamp is greater than N minutes

Then take a look at the activity happening on the tables being replicated.  It sounds like most of your problems are related to very high activity - your log size moving from 3GB with only 13-20% used, to 3.5GB (meaning all 3GB was used) is an indicator of big transactions, which may be saturating your replication bandwidth.

Also, if they take 20-30 minutes to replicate when reinitialized, that's a combination of their size and your free bandwidth.

So, the general fixes are:

Increase the bandwidth
Reduce the amount of data

Use the smallest integer based keys you can, and simply replicate lookup tables with descriptions, rather than have descriptions in your tables.

BIT when possible, then TINYINT, then SMALLINT, then INT, then BIGINT where you're forced to.

Replicate indexed views instead of actual tables (publisher = indexed view, subscriber = table) - send over only the required columns, and only the required rows.
Don't use NCHAR/NVARCHAR unless you need to - use CHAR/VARCHAR when you know you only have, or perhaps even should have, no UCS-2 "Unicode" data.

Use CHAR when the average size of data is within 2 bytes of the maximum field size, since VARCHAR has a 2 byte overhead to store length

Reduce the number of transactions on the replicated data

Whatever you've got happening that causes all that activity on the replicated tables, analyze it.  Is it required?  Could it be re-written to minimize the amount of data being replicated?

Don't touch the same row more than once, i.e. instead of INSERT, UPDATE, UPDATE, UPDATE, just a single INSERT to the final table - perhaps work in #temp tables before then?  And so on and so forth.

If they don't absolutely need up to the minute data on any given table, then don't replicate your the core tables that get all this activity.

For each non-realtime-required table, create reporting tables on the publisher that get updated only every so often (once a week, once a day, once an hour, whatever), and replicate those.
And make sure to carefully use only the UPDATEs, INSERTs, and DELETEs that are required - the goal is to minimize replication load.
Don't use MERGE; it has too many issues.

Split the tables up into multiple publications, so you aren't taking a snapshot of, and reinitializing, every table every time you have to add or remove one.

I.e. If you add/drop/alter an article in Publication C, you need to reinitialize and snapshot that publication, but publications A, B, D, E, and F aren't affected by this.

And thus it moves less data, which in turn takes less time.
And the subscriber retains access to all the articles in all the other publications
So split them up along functional usage lines; if tables A, E, and G are used together most of the time, while C and D aren't involved in most of those queries, that's a natural split that will leave your client happier; at least some functions continue unobstructed during re-initialization of either set.

Increase the bandwidth


Is it possible to replicate a collection in MongoDB

Depending on what is your MongoDB server version... Solution is use method "Change Streams" or "Oplog tailing"
Where you are "listening" changes of source DB and replicating those commands to destination DB (at other MongoDB installation)