Is it best practice to revoke server permissions to the server "public" role on SQL Server instances?

Yes, you should fix this. If you have multiple DBs in the server, a legitimate user in one DB might have nothing to do in another DB and will not be a user in that and hence 'public'. Why would you give pubic user permission to view/access objects in a DB they are not entitled to?

Make sure to test the application after you fix these. Sometimes even a legitimate user might be missing appropriate grant permission and might have been working based on public Access, which won't work after you fix the 'public' role access. They needs to be granted explicit access through grant stmt to specific objects or via database roles or via user roles.


You have two options really:
You can create a delimited file with your data, like the following:

row1col1data,row1col2data,row1col3data,row1col4data
row2col1data,row2col2data,row2col3data,row2col4data
row3col1data,row3col2data,row3col3data,row3col4data

You can then load that into Vertica using the COPY command and vsql. If you have database superuser rights, you can load the file directly, using:

vsql -U <username> -w <password> YOURDATABASENAME -c "COPY yourtablename (col1name, col2name, col3name, col4name) FROM 'yourdelimitedfile' DELIMITER ','"

Omit -w  if you want to enter keep the password out of your bash history and enter it when the program starts. If you don't have database superuser rights, you can still cat the file into vsql and get the data from STDIN - quite why Vertica prevents you from loading data from a file without superuser rights, but will let any user pipe it in via cat, I have no idea, but you can do it as follows:

cat yourdelimitedfile | vsql -U <username> -w <password> YOURDATABASENAME -c "COPY yourtablename (col1name, col2name, col3name, col4name) FROM STDIN DELIMITER ','"

Or, if you have a bunch of individual insert statements in a file, separated by semicolons, you can just run the file through vsql, as follows:

vsql -U <username> -w <password> YOURDATABASENAME -f pathtoyoursqlfile

But if you're doing more than 1000 rows, bulk loading using a delimited file is supposed to be much faster.

Hope that helps!


Write a slow query to test slow query logging?

A simple query would be:

SELECT SLEEP(2);

You want to iterate it?

DELIMITER $$
DROP FUNCTION IF EXISTS `iterateSleep` $$
CREATE FUNCTION `iterateSleep` (iterations INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE remainder INT;

    SET remainder = iterations;

    read_loop: LOOP
        IF remainder=0 THEN
            LEAVE read_loop;
        END IF;

        SELECT SLEEP(2) INTO @test;
        SET remainder = remainder - 1;
    END LOOP;

    RETURN iterations;
END $$
DELIMITER ;

-- TO TEST IT OUT
mysql> SELECT iterateSleep(2);
+-----------------+
| iterateSleep(2) |
+-----------------+
|               2 |
+-----------------+
1 row in set (4.01 sec)

Alternatively if you just want to test your slow_query_log, change 'long_query_time' to 0 (to log all queries):

SET long_query_time=0;


How to create a server alias in SQL Server 2012

Set one of your instances to use a different port. The alias will allow the different port to be used without the client having to worry about it.


How do I grant Read Access to FILESTREAM data in a table but not grant SELECT access?

There's no way to restrict SELECT access to a set number of rows.

If the users don't need access to the entire table, but just their "own" rows, you could configure row level security (SQL 2016 and up) to restrict access to only what they need. This will not prevent the user from performing a SELECT *, but it will restrict the volume of data returned if they do run one.

Ultimately, this is the kind of situation which would be best solved by not allowing users to have any direct access to the table, and instead have the data returned via app code which can be controlled to not run a SELECT *.


SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade

I would slipstream Service Pack 2 (and perhaps Cumulative Update 2 as well) into the R2 installer prior to running the upgrade, especially if you have enabled the extended partition support. This guide walks you through slipstreaming (it talks about a different service pack and CU, but the process is the same). Slipstreaming will save you a bunch of time and service restarts, since you should be installing the latest service pack at least and not be leaving your new instance at RTM (it is no longer a maintained branch).

You shouldn't have to change SSIS packages.

If you can afford a side-by-side upgrade (e.g. you have another machine or adequate resources for another instance on the current machine, and can re-point your apps later), it will be slightly safer and require less downtime than an in-place upgrade (though it will be more work). You can install another instance, get your logins and jobs set up, then mirror or log ship your user databases to the new instances. When you've ready, you can fail over, take the old databases offline, then the new instance is the primary and you just need to re-point your apps.

Personally, I haven't had any problems with in-place upgrades, but I would probably opt against them if possible in a mission critical environment. The main point is if you do side-by-side and something goes wrong, you can take the new instance down and rebuild from scratch, and it doesn't affect your production system. If something goes wrong in the middle of an in-place upgrade, then you'll be in scramble mode... do we have current backups? Where do we restore them?

In either case, you'll want to make sure you take proper backups before the upgrade.

You will want to update statistics once the upgrade is complete, and if you're on Enterprise Edition and using data compression on tables with nvarchar columns, you may want to rebuild those indexes, since one of 2008 R2's primary advantages over 2008 in the OLTP world is Unicode compression. I wrote a few blog posts about this:

https://sqlblog.org/2009/08/11/sql-server-2008-r2-a-quick-experiment-in-unicode-compression
https://sqlblog.org/2009/08/23/sql-server-2008-r2-digging-deeper-into-unicode-compression
https://sqlblog.org/2009/10/04/more-testing-of-unicode-compression-in-sql-server-2008-r2


Getting error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

The values '0000-00-00' are causing this error. You could for example use NULL instead.


You could try something like this to reduce the ORs. This was validated on SQL Server so you may need to tweak a bit:

SELECT * FROM users
WHERE active=1
AND first_name  IN ('Bill Gates', 'Bill', 'Gates')
OR last_name    IN ('Bill Gates', 'Bill', 'Gates')
OR workplace    IN ('Bill Gates', 'Bill', 'Gates')

Seems like some data validation is called for overall though.


Is the default clustered index file physically the same file as the file containing the data records?

You refer to "data file" as like the data (actual rows) for a table is in a file separate from other tables and indexes. That is not the case. I just like to point that out, since that might be a contributing reason for the confusion.

There is so much info available out there on physical structures for SQL server, so this will be super-brief:

Data (loosely speaking) is stored in pages (8KB) blocks, in a database data file. There are different type of pages.

The clustered index is the data. I.e., the leaf level of that index are the actual rows. It contains all columns, and the sort for that index is of course based on the index key for that clustered index. There's nowhere else/further to "point". When you navigated the tree, and are now in the leaf for the cl ix, you are indeed at the data row.

Also note that a data file (.mdf, .ndf) for a database (in contrast to a transaction log file, .ldf) can contain all types of pages. That include pages for non-clustered index, pages for clustered indexes, pages for heaps (data pages for tables that don't have a clustered index), etc.

So, at the file level, there is no separation between "data" and "indexes". You can achieve that, to some degree, using filegroups, but that is a separate topic.

Also note that I carefully didn't confuse the clustered index with the primary key. It just happens that the default index type for the PK is a clustered index. But that is just a default.


Is index on foreign key used? (No difference in EXPLAIN.)

Very quick answer: MyISAM does not support FOREIGN KEYs at all... Any FOREIGN KEY you define on a MYISAM table is silenty thrown away.

When you later switch to InnoDB (i.e. ALTER TABLE my_table ENGINE=InnoDB) the FOREIGN KEY is not there either; it was thrown away when the table used to be MyISAM.


The actual value to use for your MAXDOP is going to vary by your workload.  If it's truly pure OLTP then you should probably get away with setting it to 1 and be done.
HOWEVER
We (the royal we) almost never get away with a pure OLTP workload, there are always going to be reports driven off of it, or screens in the app that look at lots of data at once, or you have to SHARE the server with BI.  So a more reasonable number is usually selected.
We have a 24 core server (for the memory, SQL Managed Instance) and have set our MAXDOP to 8 and our CTFP to 50.  I am currently toying with the idea of increasing CTFP as many of the times when we have problems it is because a largish query has gone parallel and has tied up resources doing so.  No benefit to speed, but large increase in resources required. In fact, many of our larger queries have MAXDOP hints limiting them to single thread.
For an 8 core server, I would set MAXDOP to 4 and leave CTFP where it is now (50).  For larger number of cores, I would set MAXDOP to 8 and start with CTFP at 50.  Then look at your workload, especially for queries with high CX waits.  If the majority of your workload is still going parallel and shouldn't, tweak the numbers.  Otherwise, tune the queries that are going parallel that shouldn't.


Why --audit commands does not work in Mongo Enterprise

--auditDestination is parameter of mongoD and mongoS, if you try to use it with mongo command, you get error message.


Get minimal logging when loading data into temporary tables

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging.

No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table (dbo.temp) created in tempdb, because these can be accessed from multiple sessions.

Note that even where the full minimal logging optimizations are not applied, tables created in tempdb benefit from other optimizations like not needing to log REDO information. You can test to see whether rows or pages are being logged using the undocumented sys.fn_dblog. Regular logging will feature row-logged records like LOP_INSERT_ROWS.

Note that adding TABLOCK to a local temporary table is required to get parallel execution with INSERT...SELECT queries in SQL Server 2016, see the Microsoft Knowledge Base article:

Poor performance when you run INSERT.. SELECT operations in SQL Server 2016


MySQL: Keep the 10 most recent rows for each user

I found figured out an answer that works pretty well, though if you have a lot of users it may take some time to execute. I have yet to do any time testing with it. It also assumes you have an id field that uniquely identifies each score.

The way it works is in the inner query it finds up to the 10 most recent scores. Then deletes everything not returned by the inner query.

DELETE FROM score
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT *
    FROM score s1
    WHERE (
        SELECT COUNT(*)
        FROM score s2
        WHERE s1.userid = s2.userid
            AND s1.timestamp <= s2.timestamp
    ) <= 10 --Keep this many records
  ) foo
);


How to query two values from the same table from different records combined with fields from other tables?

Try this:

SELECT eventsink.RedEvent.DeviceId,
           MAX(eventsink.RedEvent.Created),
           BranchNumber,
           CountryCode
    FROM eventsink.RedEvent
    JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId
    JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId
    JOIN (SELECT MIN(Finished), DeviceId FROM eventsink.RedEvent WHERE Id = '1' GROUP BY DeviceId) t
         ON eventsink.RedEvent.DeviceId = t.DeviceId
    WHERE (Id = '9'
           AND SoftwareVersion LIKE '[1][.]%')
    GROUP BY eventsink.RedEvent.DeviceId,
             BranchNumber,
             CountryCode

Alternatively, you can put the derived table (aliased as t) into a CTE. They are the same thing really.


Looks like you're using a tool not capable of identifying benign waits. See Wait statistics, or please tell me where it hurts. There is no impact, just bad monitoring.

I recommend you read Filtering out benign waits. The engine has certain background tasks and when these background tasks don't have any work they simply sit idle, waiting for work. while waiting for work, the wait times are aggregated by the wait stats but unfortunately there is no way to distinguish between 'wait for a resource to continue the work' or simply 'wait for something to do to start the work'. The later case is a 'benign wait'. The less work these tasks actually do, the more wait stats they aggregate. These tasks never shut down and there is no session to close. You have embarked on a snipe hunt.

For more details read Understanding how SQL Server executes a query and How to analyse SQL Server performance.


Pivot a table to repeat the first column for each following column

This will work wit Access and SQL-Server

SELECT ID, A AS Fieldx
FROM tbl
UNION ALL
SELECT ID, B
FROM tbl
UNION ALL
SELECT ID, C
FROM tbl
UNION ALL
SELECT ID, D
FROM tbl;

specific for SQL-Server:

SELECT ID, FieldX FROM
   (SELECT ID,A,B,C,D   FROM tbl) p
UNPIVOT
   (FieldX for dummyID in (A,B,C,D)) as dummyField

Additional information can be found here


Can I use Windows XP 32 bit? (because I already have a VM) If not then
  can I use Windows 7 or 10?

You have the list of supported platforms here:
http://www.mysql.com/support/supportedplatforms/cluster.html

From there I can see there are 32-bit binaries, and several windows versions, but check your specific combination. I would suggest not using 32-bit binaries/OS when possible for MySQL due to memory limitations, but you say it is

  for testing

So I assume that for a small (< 2GB data) test that is ok?

  Do I only need one VM where both nodes will be installed (I don't know if MySQL supports multiple instances or something like that) or do I need at least two VMs?

For production, 4 nodes are recommended. You can technically run it on 2 nodes -yes, you can run multiple instances of MySQL (and ndbd) on the same physical server, but again, you are doing a test, so depending which part you care to test more or less realistically it will work for you or not-. Network partitions and latency issues, and memory pressure will not show on a single (or just a few), small, machine test. Even for different VMs on the same physical server, you will not get a realistic scenario. But maybe you are not interested on that, and only on learning how to set it up and/or pure controlled switchover tasks.

  I read that NDB cluster require load balancer. Can I do it without load balancer? I mean I will manually run statements (Insert/Update/Delete) on server1 to check if it goes to server2 or not instead of using some PHP application.

Yes, you can do that, but what is the point then, on having a cluster? You want 4 nodes at least (2 SQL and 2 data nodes you can crash at any time and have transparent failover). Why not set up a single Master-slave replication topology (Or master-master, with read only secondary master) in your case?

  Do I need free version of MySQL or some enterprise version?

MySQL has a dual-license model. There is a community version of the NDB Cluster. It is free (no cost) but licensed under GPL, which means it has restrictions on how to use it (linked application must also be GPL). If you are an ISV or a developer shop that has some deliverables to clients, you may need to purchase an Enterprise licence. For testing, where you are not going to distribute the server or the application to anyone, you can use the Community edition. I am not a lawyer, read the license or hire one to read it for you.

  Do I only need MySQL or some other software also needed to setup cluster?

Aside from really considering a load balancing solution (or integrate it on your application), for a production deployment I would say it is a must to have: a monitoring solution (providing trends and alerts), administration tools that make your life easier. Probably the most important part is someone to adapt your application- MySQL cluster is very different from a typical MySQL/InnoDB setup, and in most cases migrating to that is not transparent for how the application access it (it is more memory and key-value focused).

Here you have summary of differences between MySQL NDB Cluster and other similar concepts:

What's the difference between MySQL Fabric and MySQL Cluster


Backup to local and MIRROR TO Azure Files returns ERROR

I can't see that you are using backup to URL. It looks like a local drive and a file share.

If you read about mirrored backups you see that there are some special requirements:

"Mirroring applies both to disk and tape (disks do not support continuation tapes). All backup devices for a single backup or restore operation must be of the same type, disk or tape."

"Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent, such as, only drives with the same model number from the same manufacturer."

https://msdn.microsoft.com/en-us/library/ms175053.aspx


Aggregating nested set models with field constraints

These are the steps to obtain the desired result:

A cross self-join to obtain every single combination of primary keys possible (this squares the number of records);
Apply the nested set filter (commented below) to remove all combinations of nodes that are not related (e.g., pk 1 and 15);
Assert that only combinations of the same nature are being fetched and aggregated, by equalling the idx from both node and crosst;
Since we are aggregating from crosst.idx, that means it will sum the "balance" for all nodes, including those that are negative (and thus orphans); the third filter ensures we are only taking the total balance starting from original positive parents.

SELECT
    crosst.pk AS prnt_pk,
    crosst.idx,
    SUM(node.value) AS aggr
FROM
    nsm_table AS node,
    nsm_table AS crosst
WHERE 1=1
    AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers
    AND node.idx = crosst.idx
    AND crosst.value > 0
GROUP BY crosst.pk, crosst.idx
HAVING SUM(node.value) >= 0

And the output is exactly as the desired one:
prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000


MYSQL Update data based on top 10 records of each user

I may have a solution, but first please see if this computes the avg_scores correctly:

SELECT
        user_id, AVG(score) AS avg_score
    FROM
      ( SELECT  @prev := 0, @n := 0 ) init
    JOIN
      ( SELECT  @n := if(user_id != @prev, 1, @n + 1) AS n,
                @prev := user_id,
                user_id, score, id
            FROM  user_scores
            ORDER BY
                user_id  DESC,
                id  DESC
      ) x
    WHERE  n <= 10
    GROUP BY user_id;

Recommend INDEX(user_id, id, score) on user_scores .

If that works, then a multi-table UPDATE may finish the task:

UPDATE   user
    JOIN ( the above ) AS y  USING(user_id)
    SET  user.user_score = y.avg_score;


How join two databases data, thats contains auto increment ids, into a new database

Non trivial.  And, if you have FOREIGN KEYS, it will be worse than that.

Plan A:

Do this to every table in one of the databases that has a potentially conflicting id:

UPDATE tbl SET foo_id = foo_id + 1000

(Pick the number based on avoiding the MAX(foo_id) in the other database.)

Then proceed to merge the matching tables.

Plan B:  This has the advantage of not destroying any tables until you have checked the result.

Create a third database and tables.  Write a bunch of SQL statements to carefully copy all the rows from one db, then the other db, to the new db.  Be sure to copy tables in the correct order.  Hopefully, the lets you create new ids for everything.  (I have not thought through the details.)


How to make subtraction without repeating the subqueries?

You can use CTEs when you have repeating subqueries. Although it's not necessarily better for performance, in Postgres CTEs are an optimization fence, in other words they are evaluated once (or not at all).

Your query can probably be written more simply though:

SELECT player
FROM played_world AS pw
WHERE NOT EXISTS
      ( SELECT 1
        FROM world AS w
             JOIN player AS p
             ON w.winner = p.country_player
        WHERE w.year = pw.world_year
          AND p.player_id = pw.player
      )
GROUP BY player
HAVING count(*) > 1 ;

or with EXCEPT:

SELECT player
FROM played_world
GROUP BY player
HAVING count(*) > 1

EXCEPT

SELECT p.player_id
FROM world AS w
     JOIN player AS p
     ON w.winner = p.country_player ;


How to change default mongo port on a bitnami instance

I am running a bitnami instance of mongodb for our pre-production. The
  default port for mongo is 27017. But for security reasons i want to
  change it but it is not possible.

As you mention error code below

  Error: couldn't connect to server
  /opt/bitnami/mongodb/tmp/mongodb-27017.sock:27017, connection attempt
  failed : connect@src/mongo/shell/mongo.js:237:13 @(connect):1:6
  exception: connect failed

As per your error code location (/opt/bitnami/mongodb/tmp/mongodb-27017.sock) mongod tring to connect with port 27017. May be your mongod.conf data files and log files has not configure properly or may be you don't have a authentication permission of that folder where is mongod.conf .

As Bitnami MongoDB documentation here On Unix, the MongoDB clients can connect to the server using a Unix socket file at

installdir/mongodb/tmp/mongodb.sock

The default port in which MongoDB listens is 27017

The main MongoDB log file is at installdir/mongodb/log/mongodb.log

The MongoDB configuration file is located at installdir/mongodb/mongodb.conf

The official MongoDB documentation has more details about how configure the MongoDB database.

For your further ref here , here and here


How to manage users and collections in MongoDB?

At the time of writing this (2.4 is the stable release) you would have to split up these collections into three databases instead.  The ability to do collection level permissions is not available, so you need to do it at the database level.  If you alter your model to have 3 databases (Books, Students, Professors), then you can add the relevant users to each database using db.addUser as you require.

However, if that is not possible for some reason, it is worth noting that collection level access control is slated to be a feature in 2.6 (the next major release) which is due soon (tm).  The usual caveats apply, nothing is truly in a release until you see the actual binary and confirm it, but this feature (as part of SERVER-1105) is already marked as committed and closed.  It is available for testing in the development releases right now.  The documentation (once complete) will be available at:

http://docs.mongodb.org/master/release-notes/2.6/#collection-level-access-control


Database Schema -Images in separate tables?

I would go with the first option.

Create two columns, one for profile image, one for header image.
Store the path

You shouldn't be creating new tables for 1:1 relations. For both header and profile you want to store at most one image. That's what columns are for.

Even better. Normalize them all to the same format and appropriate sizes and save them in  pkid/header.png, pkid/profile.png and then just make boolean flags has_image_header has_image_profile If you don't want to hardcode that path in the app, and you'd rather hardcode it in the database do it with a function,

CREATE FUNCTION get_path(pkid int, imgtype text)
RETURNS text
AS $$
  SELECT '/' || pkid::text || '/' || imgtype || '.png';
$$ LANGUAGE sql
IMMUTABLE;

SELECT get_path(432, 'header');
    get_path
-----------------
 /432/header.png
(1 row)


How to prevent users other than DBAs from creating database links

I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there.

If you check the SQL reference you will see that users require the CREATE DATABASE LINK privilege to create those links. You can revoke the privilege from some users, but that would not prevent them from connecting directly to the remote database and running their queries there.


You must use the RMAN executable that is in the classpath that is used to run (or how would you say that) your database. That is the one from 11g. Just do . oraenv <database> (note space between . and oraenv) to set the correct path and then execute rman.


How to find total used and remaining space in Azure SQL mi

sp_databases doesn't seem to be applies to Azure SQL Database (as per MS Official Documentation) which is the same case for sys.dm_os_volume_stats and sys.master_files.

However, you may want try with following query, this basically gives break-down of utilized and available space with all mounted drives that are hosting database related files.

SELECT  SERVERPROPERTY('MachineName') as HostName,
        volume_mount_point,
        max(total_bytes / 1048576.0) / 1024 as TotalSize_Gb,
        max(available_bytes / 1048576.0) / 1024 as Avalable_Size_Gb
FROM sys.master_files AS f
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
Group by volume_mount_point
go

select DB_NAME(database_id) as DBName,
        SUM (CASE WHEN type_desc = 'ROWS' THEN ((size * 8) / 1024.0)/1024 end) as Data_Size_GB,
        SUM (CASE WHEN type_desc = 'LOG' THEN ((size * 8) / 1024.0)/1024 end) as Log_Size_GB,
        SUM (CASE WHEN type_desc = 'ROWS' THEN ((size * 8) / 1024.0)/1024 end) +
        SUM (CASE WHEN type_desc = 'LOG' THEN ((size * 8) / 1024.0)/1024 end) as Total_Size_GB
from sys.master_files
group by database_id
go


SSRS- database and Report Server - can they be different?

Yes, the report server can be installed on another machine than the server where the databases reside, there can even be more than one report server instances connected to the same reportserver database (this is called scale out deployment).

  Two or more report server instances sharing a single report server database.

You can run

exec ListInstallations

In the reportserver database to list the Report servers connected to this database.


Achieving "sequence number", rather than dateTime, for updates; postgres specifically

As a_horse_with_no_name originally suggested in question comments:

--assuming we have tables: a, b, ...
CREATE TABLE a
(
  a_id integer NOT NULL PRIMARY KEY,
  a_name text
) ;

CREATE TABLE b
(
  b_id integer NOT NULL PRIMARY KEY,
  b_name text
) ;

First we create a global sequence and add a column in each table:

-- create one global sequence to be used by all tables
CREATE SEQUENCE global_sequence_number ;

-- add a column in each table, that uses the same sequence
ALTER TABLE a
  ADD COLUMN global_sequence_id INT NOT NULL
    DEFAULT nextval('global_sequence_number'::regclass) ;

ALTER TABLE b
  ADD COLUMN global_sequence_id INT NOT NULL
    DEFAULT nextval('global_sequence_number'::regclass) ;

Create trigger function (one) and UPDATE triggers (one in each table):

-- create a trigger function to update the column
CREATE OR REPLACE FUNCTION update_global_sequence_id()
RETURNS TRIGGER AS
$$
BEGIN
   NEW.global_sequence_id = nextval('global_sequence_number'::regclass);
   RETURN NEW;
END;
$$ language 'plpgsql';

-- add a trigger, in each table
CREATE TRIGGER update_a_global_sequence_id
  BEFORE UPDATE ON a FOR EACH ROW
    EXECUTE PROCEDURE update_global_sequence_id();

CREATE TRIGGER update_b_global_sequence_id
  BEFORE UPDATE ON b FOR EACH ROW
    EXECUTE PROCEDURE  update_global_sequence_id();

And profit:

 -- example of use
INSERT INTO a (a_id, a_name)
VALUES
  (1, 'a'),  (2, 'b') ;

INSERT INTO b (b_id, b_name)
VALUES
  (3, 'c'),  (4, 'd'),  (5, 'e') ;

SELECT *
FROM a FULL JOIN b ON FALSE
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  1   a  1
  2   b  2
             3   c  3
             4   d  4
             5   e  5

And after some updates, to check that the triggers work:

-- lets do some updates:
UPDATE a SET a_name = 'aa' WHERE a_id = 1 ;
UPDATE b SET b_name = 'cc' WHERE b_id = 3 ;

-- and see what happened
SELECT *
FROM a FULL JOIN b ON FALSE
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  2   b  2
             4   d  4
             5   e  5
  1  aa  6                   -- sequence updated to 6
             3  cc  7        -- sequence updated to 7


UPDATE table1
SET value=CONCAT(value, ',test_data')
WHERE field1='abc'
  AND NOT INSTR(CONCAT(',',value,','), ',test_data,');

But I'd recommend to normalize your data structure... CSV data field is not safe.


Simplify query

Remove useless user_id from subquery.
Remove coalesce around count(). I quote the manual on aggregate functions:

  It should be noted that except for count, these functions return a
  null value when no rows are selected.

Meaning, count() never returns NULL.
Remove the redundant LEFT JOIN from the subquery (Update: not if you want to set columns to 0 where no rows are found in t2).

UPDATE t1
SET    col1 = t2.col1_count
      ,col2 = t2.col2_sum
      ,col3 = t2.col3_sum
FROM  (
   SELECT t1_id
         ,count(*) AS col1_count  -- if id is NOT NULL, count(*) is a bit faster
         ,sum(CASE WHEN col5 = true  THEN col2 ELSE 0 END) AS col2_sum -- might be simpler
         ,sum(CASE WHEN col5 = false THEN col3 ELSE 0 END) AS col3_sum -- missing info
   FROM   t2
   WHERE  user_id = 1
   GROUP  BY t1_id
   ) t2
WHERE  t2.t1_id = t1.id;

To reset rows in t1 without any matches in t2:

UPDATE t1
SET    col1 = 0, col2 = 0, col3 = 0
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.t1_id = t1.id);

To do both at once, the version with LEFT JOIN the subquery like you had is probably faster, depends on your data distribution.

Avoid empty updates

If there is a chance that values in t1 are already up to date, add conditions to the WHERE clause that prevent empty updates (applies to both queries):

...
AND (col1 IS DISTINCT FROM t2.col1_count OR -- again: might be simpler
     col2 IS DISTINCT FROM t2.col2_sum   OR -- missing info
     col3 IS DISTINCT FROM t2.col3_sum)

For columns defined NOT NULL you can use <> instead of IS DISTINCT FROM.

This can make a big difference, updates are expensive.

Index

The only index you need for this (in addition to the primary key on t1.id) is:

CREATE INDEX ix_t2_user_id ON t2(user_id);


You need to grant the create session privilege in order for the user to be able to connect. Then grant the select privilege on every table the user needs to access. To grant the user the privilege to read all tables in the entire database you can use the select any table privilege.

I think the PUBLIC role has the execute privilege for the DBMS_METADATA package so the user should be able to extract the DDL of the tables automatically.


This is exactly what the LAG() window function does, available since version 8.4:

SELECT id, name, date,
       LAG(name) OVER (PARTITION BY id ORDER BY date) AS previous_name,
       LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date
FROM table_name ;

There is also the option of using a LATERAL join (available from 9.3). Might be better if you want all the columns (of the previous row). This assumes that there is a UNIQUE constraint on (id, date):

SELECT t.id, t.name, t.date,
       p.name AS previous_name, p.date AS previous_date
FROM table_name AS t
  LEFT JOIN LATERAL
    ( SELECT p.*
      FROM table_name AS p
      WHERE p.id = t.id
        AND p.date < t.date
      ORDER BY p.date DESC
      LIMIT 1
    ) AS p ON TRUE ;

An index on (id, date) would be helpful for both queries.


Is there a way to see if database has been accessed through SQL Injection attack?

Since you log all queries against the database, look for queries that don't belong.

Start with queries that failed. Assuming your app isn't riddle with bugs, there should be very few queries submitted to the DB that fail. Attackers typically have a set of queries they execute once they find a vulnerable app. If you apply the principle of least privileges on accounts used to access the DB, you should find a bunch of failed queries. There will also be queries that fail because a less sophisticated attacker might have a bunch of queries that are not compatible with your database edition, version or vendor.

Next, look for queries/commands that could not have been generated by your application. For instance, if the application should never accesses system tables, system functions, information schema, etc... but you see them in your log, that's a good indicator something might be amiss and warrants deeper investigation. Looking at your OS logs might be helpful here in case they tried or succeeded in planting a backdoor/rootkit.

Once you're done with that, look for queries with unusual/unexpected behavior. For instance, queries that return significantly more data than normal. If you see queries that access all tables around the same time or ran show tables then accessed each table in order, there's a good chance you have an attacker that tried (or succeeded) to exfiltrate data. Even something seemingly innocuous like SELECT * without a WHERE clause is suspicious (assuming your app doesn't do this). Note that this applies to any possible query, not just SELECT. The attacker might be some disgruntled employee and decided to randomly DELETE or UPDATE data to get you in trouble with your auditors or worse.

Looking at IP addresses is often not helpful even if you're looking at the web server logs unless you have a narrow and/or well known band of IP addresses your users connect from. Few attackers do it from their home PC anymore. Bots and proxies are found all over the world plus IPs are easily spoofed. Unless you get your ISP or Telco provider involved, it's unlikely you'll get very far looking at your own logs. One better indicator is your corporate firewall or network logs to find unusual outbound IP targets. An attacker that exfiltrates data has to send it to some real endpoint. That is usually to a place that isn't cooperative and/or moves slowly when foreign companies or law enforcement comes knocking.


pgAgent not working on linux (Ubuntu 14.04)

Late answer but...

  What is causing the above messages and what needs to be done to get pgAgent to run scheduled tasks?

Seems that, as stated in comments, this boils down to why the .pgpass file is being ignored.

Reading the documentation I found the following relevant details on this page, emphasis mine:

  On Unix systems, the permissions on a password file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.

So, if your .pgpass file does not have such permissions it will be ignored. This could be what is causing the error you see.


Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

InnoDB has Limitations on row lengths and BLOBs. According to the MySQL Documentation:

  The maximum row length, except for variable-length columns (VARBINARY,
  VARCHAR, BLOB and TEXT), is slightly less than half of a database
  page. That is, the maximum row length is about 8000 bytes. LONGBLOB
  and LONGTEXT columns must be less than 4GB, and the total row length,
  including BLOB and TEXT columns, must be less than 4GB.

  If a row is less than half a page long, all of it is stored locally
  within the page. If it exceeds half a page, variable-length columns
  are chosen for external off-page storage until the row fits within
  half a page, as described in Section 14.3.12.2, “File Space Management”.

  Although InnoDB supports row sizes larger than 65,535 bytes
  internally, MySQL itself imposes a row-size limit of 65,535 for the
  combined size of all columns:

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB; ERROR 1118 (42000):
Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to
TEXT or BLOBs

  See Section E.10.4, “Limits on Table Column Count and Row Size”.

You are going to have to recreate the mysqldump so that the Storage Engine is Specified. Perhaps just drop the --compatible=mssql from the mysqldump command. The end result is the that the table will remain a MyISAM table when being imported into MariaDB.

This is just a guess but look at the error message you posted. If a BLOB prefix is 768 bytes and you have 10 BLOBs, that 7680 bytes. That leaves you with 320 bytes. If the remaining datatypes exceed 320 bytes, then it is impossible to convert to InnoDB.

Since a TEXT column is the variant of a BLOB, converting to TEXT does nothing since the storage requirements for TEXT and BLOB field are identical.


Create index on very large table with many shared values

For starters gid should probably be a numeric type. integer should be good enough or bigint if the key space shouldn't be big enough. Much smaller footprint, faster processing than with character data, faster and smaller indexes.

More importantly, to improve performance I suggest database normalization.

Quote:

  There is a fairly regular pattern where each word appears about 1000 times.

Create a separate table for unique words:

CREATE TABLE word (
   word_id serial
 , word    text
);

Fill it with unique instances of word in your big_tbl:

INSERT INTO word (word)
SELECT DISTINCT word
FROM   big_tbl
ORDER  BY word;

ORDER BY is optional, not needed for query at hand. But it speeds up index creation and might be cheaper overall.

The table should be small in comparison: only ~ 50k rows for 50M rows in your big table.
Add indexes after filling the table:

ALTER TABLE word
    ADD CONSTRAINT word_word_uni UNIQUE (word) -- essential
  , ADD CONSTRAINT word_word_id_pkey PRIMARY KEY (word_id);  -- expendable?

If those are read-only tables, you can do without the pk. It's not relevant to the operations at hand.

Replace your big table with a much smaller new table. You may have to lock the big table to avoid concurrent writes. Concurrent reads are not a problem.

CREATE TABLE big_tbl_new AS
SELECT b.gid      -- or the suggested smaller, faster numeric replacement
     , w.word_id, b.stat
FROM   big_tbl b
JOIN   word w USING (word)
ORDER  BY word;   -- sorting by word helps query at hand

ORDER BY clusters the data (once) making the query at hand faster, because far fewer blocks have to be read (unless your data is clustered mostly already). The sort carries a cost, weigh cost and benefit once more.

DROP big_tbl;     -- make sure your new table has all data!
ALTER big_tbl_new RENAME TO big_tbl;

Recreate indexes:

ALTER TABLE big_tbl ADD CONSTRAINT big_tbl_gid_pkey PRIMARY KEY (gid);  -- expendable?
CREATE INDEX big_tbl_word_id_idx ON big_tbl (word_id);  -- essential

Your query looks like this now and should be faster:

SELECT b.*
FROM   word w
JOIN   big_tbl b USING (word_id)
WHERE  w.word = 'something';

Reorganization is meant to be a one-time operation to re-organize your data. Keep the new form and also consider keeping indexes permanently.

All of this together (including new indexes) should occupy about half of what you had before on disk, also cutting the time for creation in half (at least). Index creation should be considerably faster, the query as well. If RAM is a limiting factor, these modification pay double.

If you have to write to the table as well, it becomes more expensive (but you did not mention anything about that). You'd need to adjust your logic for DELETE / UPDATE / INSERT:
Example for INSERT: Fetch word_id for existing words or insert a new row in word returning the new word_id. Details for this:
How do I insert a row which contains a foreign key?


Auto_Increment After deleted data from the table

ALTER TABLE tablename AUTO_INCREMENT = 1;

See also the same question on stackoverflow.


Dealing with measure groups of different granularity

If you want to see measures from two different fact tables side by side but different granularity, it's possible but not perfect. For example, the measure for purchase amount in FactPurchase has no monthly equivalent.

To do this, it helps to have as many related dimensions between both fact tables. So if you have a dimension such as property type that is related to FactPurchase, I would make that a dimension on FactMonthlyStatus as well. This can be done by adding these to the actual FactMonthlyStatus table and figuring them out in your ETL process or by creating a view that sits on top of FactMonthlyStatus that does a join to lookup these dimensions as they are loaded into your cube. However, you are still going to have dimensions such as month that are at a lower level of granularity than FactPurchase.

The main thing to figure out is how your measures in both fact tables relate to each other. Are you attempting to keep a running total for rent paid and see how it relates to what you paid for the property? If that's the case, it might make sense to create a cube based off a single view that combined the FactPurchase with an aggregate of FactMonthlyStatus to see how they compare.


Why is random password generation the same when it's inside while loop? TSQL

You need to clear the value of @r for each loop.

declare @r varchar(6);
set @r = ''; -- Clear previous value
select @r = coalesce(@r, '') + char ...

I can see that you thought declare @r varchar(6); would do that for you but it does not.

Have a look at Variables scope which are defined within a while block in stored procedures - SQl Server for more fun with variable declarations.


Insert into value as generate row with different minutes

I am not entirely sure what your question is, but if you are confused that all rows have the same timestamp value, this is because now() and current_timestamp return the time at the start of the transaction.

Quote from the manual

  Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp

And then:

  clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

So if I understood your question correctly, you probably want to use clock_timestamp() rather than now()

Another option is to use generate_series() to generate timestamps:

INSERT INTO indexing_table(created_at)
SELECT *
FROM generate_series(timestamp '2019-01-01 00:00:00',
                     timestamp '2019-03-31 00:00:00',
                     interval '1 minute') ;


Transactional versus Merge Replication (Restore backup after downtime)

for backup purposes I would implement database mirroring. if read access is required then using database snapshots could help.

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

i believe that merge replication would work to eliminate the need to backup/restore from B to A, but the performance penalty would be considerable. if the base is small then it would be fine.


Why am I not seeing any benefits of parallel execution when parallel clause is used?

You're not seeing the benefits of parallel execution because both insert methods use single-row inserts, hence parallel does not kick in !

Parallel execution is only available for bulk operations. Lots of tiny operations doesn't qualify as a set operation. You're inserting rows one by one here, you should try it with a bulk operation (INSERT /*+APPEND*/ INTO SELECT, CREATE TABLE AS SELECT ...) and see if you can achieve some performance improvement.

You can tell when Oracle does a parallel execution because the explain plan will display parallel operations.

For further reading:

Parallel introduction in the concepts guide
A short introduction about parallel execution by Tom Kyte


How to create a mysql stored procedure through linux terminal

There is no way around it. You must use the DELIMITER command. Why ?

If you ever perform a mysqldump of the stored procedures, each stored procedure begins with

DELIMITER ;;

and ends with

DELIMITER ;

Here is a post where I mentioned this before : DROP PROCEDURE IF EXISTS not included in mysqldump

Try dumping one stored procedure with mysqldump and see for yourself

I also wrote some code to do this:

how to dump a single stored procedure from a database
Dump only the Stored Procedures in MySQL

As for the answer posted by @altmannmarcelo, it directly answers your question (+1 for his answer). Otherwise, mysqldumps could never restore stored procedures.

There are two things you can do to accommodate a new DELIMITER:

TRY THIS #1

Give the delimiter on the command line itself

mysql -u root -pmypass --delimiter="//" test < myproc.sql

It is a command line option for the mysql client program

[root@****]# mysql --help | grep -i delimiter
  --delimiter=name    Delimiter to be used.
delimiter                         ;

TRY THIS #2

You can write the code into a text file and execute against the text file as @altmannmarcelo suggested

echo "DELIMITER //" > myproc.sql
echo "create procedure test_pro()" >> myproc.sql
echo "begin" >> myproc.sql
echo "select 'hello pro';" >> myproc.sql
echo "end" >> myproc.sql
echo "//" >> myproc.sql
mysql -u root -pmypass  test < myproc.sql

GIVE IT A TRY !!!


How to handle connection pooling for massive multi-tenancy/multi-schema environment

You have 2000 schemas spread across 50 servers...I don't think there's a connection pooler that exists to handle that type of situation.  You're going to have to roll your own in application code, I believe.

I'd really step back and take a look at your architecture.  What happens if you double the number of customers?  Triple it?  This type of design seems seriously unmanageable and difficult to scale to new levels of business.

HTH,
Dave Sisk


Increasing mysql ram usage to decrease disk activity

Disk activity is either lots of stuff being read in or lots of stuff being written out because there isn't enough room in memory to hold it.
Increasing memory might reduce your disk activity, but it might not improve your overall performance.
Left to its own devices, your DBMS will not do lots of disk I/O for itself.  It only does so in response to queries that are sent to it.
You need to look at the workload (queries) that the server is running.  The chances are there are some "chunky" ones in there that are trawling through a lot of data.  Find them, tune them, get rid of them.  That will improve your server's overall performance.


Table-Valued Parameter as Output parameter for stored procedure

No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.


How to send+update on duplicate a big time series to SQL server

If the data is in memory, you can use SQLBulkCOpy in .net or similar to send data to SQL Server. No need to instantiate a file.

And load a staging table first in SQL Server. Then use MERGE from this staging table to the actual table

If you don't want a persistent staging table, create a #temp table and use that in the subsequent MERGE. I'm not sure about the usefulness of Table Valued Parameters here

If you want to load the final table directly, then you can MERGE directly from a file using OPENROWSET(BULK…). But you don't want to instantiate a file.

In summary: there is no direct "in client memory" to final table solution: you need an intermediate table or file


In addition to what Md Haidar Ali Khan said, ...
The "default storage engine" does not mean that you created all the tables in that engine.  You should verify with SHOW TABLE STATUS; for you database(s).  That will say, among other things, what Engine is being used.

innodb_buffer_pool_size, for an InnoDB-only system, should be set to about 70% of available RAM (if RAM is at least 4GB).  And set key_buffer_size to some small value, say, 20M because of system tables that are in MyISAM.  These system tables probably caused the spikes you saw.

More discussion of memory -- including what to do if you are using MyISAM.


Column must appear in the GROUP BY clause error even though it DOES appear there

I figured it out. It's the damn parentheses around the columns in the GROUP BY clause. The correct query is:

select order_number,key_a,key_b,key_c,sum(quantity) from my_table
      group by order_number,key_a,key_b,key_c;


Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL

It's possible, but it will take some work.  "Unpivoting" sounds like part of the task.

I would dump everything in .csv files, then LOAD DATA them into MySQL into tables that mimic the spreadsheet sheets.

Then, I would construct the tables that make more sense in the database.  An example is the unpivoted version of the data that is spread across 90 columns.

Then I would write various INSERT ... SELECT ... statements to copy the data from the Excel-centric layout to the MySQL-centric schema.  It is the sort of thing where errors are quickly rectified by DROPping the new tables and rerunning the conversion queries.  Plan on doing that a lot until you get the tables looking good.

Then, write and run queries to use the data in the database-centric format.  You should try writing the queries earlier to get a feel for what you will be getting into.


What's the difference between master and slave nodes in MySQL Cluster?

The data node marked as 'Master' in the output from the ndb_mgm show command is the coordinator of some cluster internal management tasks.  For example, it coordinates changes to the distributed dictionary due to DDL commands, and manages node join and leave transactions.  The 'Master' role is dynamic, and is generally assigned to the longest running data node in a cluster, only moving when that node fails.

The 'Master' role is completely unrelated to the 'Master/Slave' concept in MySQL replication, and also has no bearing on the replication of data between nodes within a cluster.  Perhaps it would be better if it were called 'Coordinator' or 'Leader'.  As you say, you do not need to worry about which data nodes are used by MySQLD, all are equivalent for most operations and it is handled automatically.


Snapshot Replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019 not working

The replication version compatibility rules are documented here: Replication Backward Compatibility (snapshot falls under Transactional Replication).

SQL Server 2008 is out of support, and replication between SQL 2008 and SQL 2019 is unsupported. So probably not.


Enforcing minimum cardinality in One To many design?

Declare a constraint on Players, something like

check (10 < (select count(*) from p_id where t_id = @t_id))

and ensure that your application inserts at least 11 players the first time.

It's not clear this is a good idea, though.  It's one thing to say every player belongs to a team.  It's something else to say all teams have 11 players.  In the first case, you're enforcing referential integrity: what would teamless player be?  (Well, you might have worked with him.  I have.)  In the second case, you're imposing a business rule: does a team cease to exist if player #11 quits?  Is it the case that when player #11 quits, the whole team must be deleted?

There might be such a rule, but it seems more likely (from this distance) that the team continues to exist but becomes ineligible to play.  In that case you might want a view

create view EligibleTeams as
select * from Teams as t where 10 <
    (select count (t_id) from Players where t_id = t.t_id)

Also, is it clear you need t_id and p_id?  Not to me.  I think your keys are the names of the players and the teams.  Definitely you don't want two Ravens or whatever, and the odds that you'll have two players named Roger Staubach on one team are longer than the Cowboys winning the World Series.


Internal MariaDB error code: 1927 ('Connection was killed')

I understand from the comments that your application is writing to more than one DB node. This is known for causing deadlocks, and this appears to be the underlying problem in your first crash log that you uploaded to the MariaDB Jira. If you're using a DB proxy (like MaxScale or ProxySQL), then make sure to use a read-write splitter so you write to only one node, but read from all.


1 time shrink of abused databse or restore from backup

Restoring your backup will in fact bring you right back to where you were when the backup was taken.  This is actually one of the few cases where a shrink is appropriate.  Fortunately it's a background process and shouldn't affect your system to much unless you are running a really heavy OLTP system.  Once the shrink is done, and if I was you I should shrink down about 80GB and keep the other 20 for growth, you will almost certainly have to reindex.  The reindex (and the fragmentation that requires it) will most likely cause you performance problems so you should probably consider doing the whole process during a maintenance window.  If you don't have a maintenance window then try a slow period.  Either way if you want to get rid of that extra space shrink is the only option you have that I know of.


It really depends on how old the snapshot is.  After the snapshot is applied, the agent will need to also send all of the changes that have occurred since the snapshot was taken.  Depending on how many changes have occurred, this may or may not affect other existing subscribers.

I tend to take a new snapshot if possible before reinitializing a subscriber although sometimes that is not an option.

I wouldn't worry too much about it impacting other subscribers.


"Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio

IntelliSense Refresh local Cache should fix it


A few db's that come close:

1:  Postgres-XC, meets most areas except that sharding takes some design effort

2:  VoltDB, meets most areas except for data larger than memory

The key question though is why do you need the autosharding?  Anything automatic adds complexity and if you are big enough to require sharding, you can probably afford to have the experts look carefully at how best to do this (i.e. via Postgres-XC).

Honestly, I think the best option for most people in your position is to start with PostgreSQL and plan on scaling up and out with bigger servers and Postgres-XC when you get to the point that you need this.  But there are a lot of big systems out there that run on PostgreSQL, including Skype and Instagram, so it certainly can do web scale even without sharding.


Slow query - big difference between estimated and actual rows

Row goal
The issue with the estimates is most likely due to the row goal introduced by the TOP (500). See:
How (and why) does TOP impact an execution plan?
If you find the estimates are much better without the TOP, or by adding OPTION (QUERYTRACEON 4138) to the query, that would confirm the hypothesis.
Trace flag 4138 is documented in KB 2667211:
A query may take a long time to run if the query optimizer uses the Top operator in SQL Server 2008 R2 or in SQL Server 2012.
You should also enable optimizer fixes with trace flag 4199.
Indexing
Consider creating an index on table 39, keyed on:
Id_Obj_To, Id_ObjType_To, Id_ObjType_From, Id_Obj_From

Ensure Id_Obj_From has a suitable data type (not e.g. a max type).
Predicte ordering
The optimizer does not reorder NOT EXISTS clauses well in your version of SQL Server. If you expect the NOT EXISTS clause to be more selective than the Gid_LineSet predicate, reorder those two clauses in the WHERE part of the query.


Galera Cluster Setup - Primary and Secondary Site Scenario

"Weighting" was added late in the game, when they realized that a 2-datacenter setup was too vulnerable.  (3 datacenters is resilient, and can use garbd in one of them.)  The example you quote is resilient to any single server, datacenter, or network outage.

As I read the last sentence of the quote, node1 or node2 died but the other three nodes are alive and talking to each other.  That is, there is a Quorum, and the system is still reliable.

However, I agree that the sentence is ambiguous -- It can be read that after the network died, node1 or node 2 died.  This leaves three clumps:  (node1), (node2), (node3,node4), each with a weight of 2.  None should be considered "primary" because none has Quorum.

You bring up garbd, yet it is not in the example??  And where would you put it?

You should not be changing the configuration while the system is hobbled -- you should be fixing the broken components.

The main goal of is to allow a single point of failure -- a single node, the network, a data center.  It would take a really large and complex system to survive two failures.  For example, I think it would require 5 datacenters to survive 2 network failures.

So, focus on a single point of failure.


SQL Server: Join all tables from a table listing their names

Assuming the list of Metrics.ID is static:

WITH cte AS ( SELECT DateTime FROM History_t1
              UNION
              SELECT DateTime FROM History_t2
              UNION
              ...
              UNION
              SELECT DateTime FROM History_t75
            )
SELECT cte.Datetime,
       History_t1.value History_t1_value,
       History_t2.value History_t2_value,
       ...
       History_t75.value History_t75_value
FROM cte
LEFT JOIN History_t1 ON cte.Datetime = History_t1.datetime
LEFT JOIN History_t2 ON cte.Datetime = History_t2.datetime
...
LEFT JOIN History_t75 ON cte.Datetime = History_t75.datetime

If, as you claim, "Assuming that the timestamps across all of the history tables align" you can avoid of CTE:

SELECT History_t1.Datetime,
       History_t1.value History_t1_value,
       History_t2.value History_t2_value,
       ...
       History_t75.value History_t75_value
FROM History_t1
LEFT JOIN History_t2 ON History_t1.Datetime = History_t2.datetime
...
LEFT JOIN History_t75 ON History_t1.Datetime = History_t75.datetime

The Metrics table not needed at all.

If the list of Metrics.ID is dynamic, use dynamic SQL to build the query text similar to above.


Disclosure: I am a MySQL employee, working on MySQL Cluster.

I would say that MySQL Cluster could achieve higher throughput / host than sharded MySQL+InnoDB provided that :

Queries are simple
All data fits in-memory

In terms of latency, MySQL Cluster should have more stable latency than sharded MySQL. Actual latency for purely in-memory data could be similar.

As queries become more complex, and data is stored on disk, the performance comparison becomes more confusing.  To get a more specific answer, you need to describe more about your application and the queries you perform, as well as the number of hosts and volume of data.  MySQL Cluster has recently gained parallel localised query execution (AQL) which means it can be competitive with standalone MySQLD despite having data distributed across multiple hosts.

MySQL Cluster is currently limited to 'sharding' over 48 hosts.  Sharded MySQL in theory has no limit.  However, for a given target throughput, fewer MySQL Cluster hosts may be needed than sharded MySQL hosts.

More interesting differences are when you look at areas other than performance :

MySQL Cluster supports arbitrary queries across all shards
MySQL Cluster supports arbitrary transactions across all shards
MySQL Cluster supports synchronous replication of shards with automatic failover and recovery
MySQL Cluster supports online add node (cluster expansion)
Sharded MySQL is more 'roll your own'

Having sharding built into your application gives you maximum scaling potential, but adds complexity and limits your flexibility in terms of cross-shard queries and operations.  If your sharding is premature then it may be the root of some problems for you.  MySQL Cluster lets you get some of the benefits of sharding without having to constrain your application to be single-shard only.

Regarding the previous answer, a few clarifications :

  "Although MySQL Cluster is ACID-complaint, it does not provide a suitable storage engine for data with compound keys."

MySQL Cluster supports compound primary and secondary keys.  Not sure what's not 'suitable' about it.  Perhaps the previous poster can explain?

  "In order to have data with the same key characteristics stored in a particular set of data nodes, you could doing the following:

  Take all data nodes offline, leaving only those data nodes you want to house data with the same key characteristics.
  Load your data into the MySQL Cluster, which populates only your select data nodes
  Bring all data nodes back online"

This is incorrect.  Data distribution is independent of which nodes happen to be online at any time.  MySQL Cluster supports various data distribution schemes to support the optimisations you describe.  I describe data distribution in MySQL Cluster in a blog post here : Data distribution in MySQL Cluster


MyISAM ROW FORMAT
If the table is a lot of VARCHAR fields, I would love to suggest the following
For every MyISAM table you load, do this beforehand
ALTER TABLE mytable ROW_FORMAT=Fixed;

This will increase query performance up to 15-30% without changing anything else. The side effect of doing this is having bigger tables and bigger indexes.
I have earlier posts on this:

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure? (Par 3)
Sep 20, 2011 : Best of MyISAM and InnoDB
Jan 03, 2012 : Optimized my.cnf for high-end and busy server (Under the heading Replication)

INCREASED RAM
If you have many indexes, you may want to increase RAM on the DB Server and then attempt to preload all MyISAM index pages into the key buffer.
First compute how big a key buffer you would like (in GigaBytes)
SELECT CEILIING(SUM(index_length)/POWER(1024,3)) SuggestedKeyBufferSize
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT
IN ('information_schema','performance_schema','mysql');

If SuggestedKeyBufferSize can be set up for the DB Server, then do the following (example 4G)
STEP 01) Set this in /etc/my.cnf
[mysqld]
key_buffer_size=4G

STEP 02) preload all MyISAM indexes
See my post on how to do this : MySQL warm procedure


I have a surprise for you.

This command

grant all privileges on mydb.* to 'mydb'@'%' identified by 'password';

does not give away the SUPER privilege. How do I know this ?

SUPER is a global grant privilege. Global grants exist in the table mysql.user

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

Notice it has a column called super_priv.

If you run the command SHOW GRANTS FOR mydb.'%';, it will echo two lines. The first line will say GRANT USAGE .... When you see USAGE, this tells you that all global privileges in the mysql.user table from that user are 'N'. To prove this, run this command:

SELECT * FROM mysql.user WHERE user='mydb' AND host='%'\G

That being said, you are probably wondering, what privileges are granted for grant all privileges on mydb.* to 'mydb'@'%' identified by 'password'; ?

Look carefully at the command. You are granting all privileges to the user for the mydb database. Where are database-level grants stored ? That's right, you guessed it. It's in the table mysql.db, which looks like this:

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

Please notice that the column super_priv does not exist in mysql.db

Now, run this command

SELECT * FROM mysql.db WHERE user='mydb' AND host='%'\G

and see for yourself.

Now, do you have any global users ?

SELECT user,host FROM mysql.user WHERE super_priv='Y';

This will give you a list of users that currently have the SUPER privilege.

If you want to manually revoke that privilege from every user except root, login as root and do this:

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

SUMMARY

Do not worry about any database-level users. They do not possess the SUPER privilege and never will.


How to make it impossible for a postgres user to delete databases?

In PostgreSQL, only the owner of a database can drop a database. (Superusers can drop databases, but that's a different issue.) So changing the owner is the most direct way to prevent user1 from dropping any databases.

Fix that with ALTER DATABASE.

ALTER DATABASE name OWNER TO new_owner

  To alter the owner, you must own the database and also be a direct or
  indirect member of the new owning role, and you must have the CREATEDB
  privilege. (Note that superusers have all these privileges
  automatically.)

To give specific permissions back to user1, use GRANT.  You're probably looking for something like

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO user1;

But read the documentation for GRANT first. You might need WITH GRANT OPTION, permissions on sequences, something less than ALL PRIVILEGES, and so on.


can you setup replication on mirrored SQL database

Mirroring has been deprecated for a while - I wouldn't build out things with it at this point.
I think you may be better off spending time preventing issues that cause you to have to re-do your replication, but aside from that note I'd recommend looking at using Availability Groups instead of, or with, transactional replication.
For using with transactional replication:

When a published database is aware of Always On availability groups, the distributor that provides agent access to the publishing database is configured with redirected_publishers entries. These entries redirect the originally configured publisher/database pair, making use of an availability group listener name to connect to the publisher and publishing database. Established connections through the availability group listener name will fail on failover. When the replication agent restarts after failover, the connection will automatically be redirected to the new primary.

and

When an Always On availability group containing a database that is a replication subscriber fails over, the replication subscription might fail. For transactional replication push subscribers, the distribution agent will continue to replicate automatically after a failover if the subscription was created using the AG listener name.

are relevant.
But you could also potentially just use an asynchronous replica in an Availability Group to offer an up to date copy to your BI users as well.
You have a lot of options here and without knowing more details, I'm hesitant to recommend anything specific. But the documentation here is good and should be able to help you devise where to go next.


SQL Server - How to restore the database except specific tables

To exclude certain tables from your refresh of the integration environment, with a backup from production, you will need to make use of a staging\data cleansing environment.

The plan would be:

Backup production
Restore backup in staging
Drop the unwanted tables
Backup staging
Restore backup in integration

This plan could easily be implemented with PowerShell (DBATools is well worth a look) or SSIS.

If you like coding, go for PowerShell. If you like dragging icons around the screen, go for SSIS. Both can get the job done.


How to migrate external procedure from Oracle Listener to Postgres

You can rewrite it as a normal C or PL/Java function in PostgreSQL.

If it is important for you to run the function in a separate process, give PL/Container a try.


Items from last month AND if not possible, items from this month

Use LEFT JOIN LATERAL ... ON TRUE to preserve all rows of the right table:

SELECT i.article
     , i.price AS price_new
     , ROUND((i.price - l.price)::numeric, 2) AS price_difference
     , l.price AS price_old
FROM   items i
LEFT   JOIN LATERAL (
   SELECT price, date
   FROM   log
   WHERE  article = i.article
   AND    date < (i.date - 25)  -- make expression sargable, plus fix off-by-1 error
   ORDER  BY date DESC          -- to make sure ...
   LIMIT  1                     -- ... we pick at most 1 row
   ) l ON TRUE
ORDER  BY i.article, l.date;

Returns all rows in items, and you get NULL for price_difference where no previous older than 25 days is found in log.

What is the difference between LATERAL and a subquery in PostgreSQL?

I added ORDER BY ... LIMIT 1 to make sure we only pick the one latest entry. If data in table log can be trusted to never return more than one row, you can skip the added lines.

Or, if the query does not actually depend on items.date, a single subquery depending on the current date using DISTINCT ON can be cheaper (with the right indexes):

SELECT i.article
     , i.price AS price_new
     , ROUND((i.price - l.price)::numeric, 2) AS price_difference
     , l.price AS price_old
FROM   items i
LEFT   JOIN (
   SELECT DISTINCT ON (article)
          article, date, price
   FROM   log
   WHERE  date < (CURRENT_DATE - 25)
   ORDER  BY article, date DESC
   ) l USING (article)
ORDER  BY i.article, l.date;

About DISTINCT ON:

Select first row in each GROUP BY group?


Vertica INSERT speed highly depends on server load

You shouldn't be surprised that a load takes longer when there's more resource usage. However, there's probably a simple explanation for your poor performance. I would examine the following:

The INSERT with UNION ALLs is highly suspicious. Is the load more than 100MB? If yes, use COPY.
Are the projections optimized for load?
Is the query profiled and no locks being used on the target table?
Most importantly, is the load local? From a client across a wide network? Test a load from a local file as a benchmark.

Your "1000 rows" isn't helpful because your row can be 10MB wide, or you could be loading JSON, loading LONG VARCHAR, etc. An actual load size is more valuable. On a typical DL380 Gen2, you should see around 350GB/HR/node.


You can use a filtered unique index for this:

CREATE UNIQUE INDEX LinkedID_Unique_except_Zeroes_or_Null
    ON dbo.oldAccessTable (LinkedID)
    WHERE LinkedID <> '00000000' ;

The condition (LinkedID <> '00000000') takes care of both requirements, i.e. rows with '00000000' or NULL are ignored and not stored in the index, so they are not checked for uniqueness.


As Joe suggested, you should use the LAG() function to compare values to the previous row. It could be implemented like this:

With LaggedLogTime As (
    Select ID, Log_Time,
        PreviousTime = Lag(Log_Time, 1) Over (Partition By ID Order By Log_Time)
      From #log)
Select ID,
    SessionCount = Sum(iif(DateDiff(Minute, PreviousTime, Log_Time) > 60 Or PreviousTime Is Null, 1, 0))
  From LaggedLogTime
  Group By ID;


Following your comment, I'm not entirely sure this question is a good fit for this site but:

VMWare vCloud Director for self-service VM creation.
If you're SQL2008R2+ you can SYSPREP a SQL installation. Alternatively, the standard install process shouldn't be a challenge for a SQL literate developer or sysadmin.
As for creating a database... anyone that can't do so probably shouldn't be coding against SQL Server.

If you're a Microsoft shop and your developers are MSDN licensed, you might want to expand the scope of this idea and take a look at Visual Studio 2010 Lab Management.

  Visual Studio Lab Management is an extension of Microsoft Test Manager
  that helps you to optimize the use of Microsoft Hyper-V technology to
  manage and use virtual machines in testing, building, and developing
  applications in Visual Studio 2010. Visual Studio Lab Management is
  integrated with System Center Virtual Machine Manager (SCVMM) to
  enable you manage multiple physical computers that host virtual
  machines and to manage the storage of virtual machines, virtual
  machine templates, and other configuration files in SCVMM library
  servers.


No.  In order for a log to be restored to a database, the log sequence number (LSN) of the log has to be the correct number to match what the database requires for the next log to be restored.  If you could somehow get the LSNs to match, restoring a log from a different database would corrupt it as some operations contain page IDs, and the page IDs are not going to be the same on both databases.

There are probably quite a lot of other reasons why this won't work, but it really isn't necessary to document all the reasons why as it is something that is simply not designed or expected to be able to work that way.


Copy my data from a existing db to a new db

Have a look at the mysql_dump dump utility (MySQL docu).

Alternatively you can use the Export function of PhpMyAdmin to generate a SQL file containing all you structure and data.


Don't grant UPDATE rights on the view.  Grant update rights on specific column within the view.  Only grant the update rights on the columns which you want the user / application to be able to update.


Installing Full Text Search to SQL Server 2008 R2

Its a two step process.

In the installer select Maintenance/Edition Upgrade to upgrade your existing instance.
Run the installer again and select Installation/...or add features to an existing installation/Add features to an existing instance.


SQL Concurrent Inserts and Automatically Find Active Flag Record

Based on the question 'What is the proper database design':

Having a 'CurrentFlag' column 'de-normalises' the schema design a little, for the sake of identifying the current order for a customer quickly.

I've worked on databases that use this method, but it is a performance trade-off that requires 'code' (e.g. triggers) to maintain the column.
This is where concurrency issues can creep in because of the extra work being done upon insert.

However, the same result can be achieved without changing the schema, by adding a descending order covering index which 'includes' the 'CustomerOrderID' in the index itself.

Testing is required for this index-only solution to ensure it is performant under the '100 million inserts in week time span' load, especially when querying the data (see end of the code sample).

Covering index example code:

--Create a test table for the example based on given schema
USE [testDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CustomerOrder](
    [CustomerOrderId] [bigint] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [FileLastModified] [datetime] NOT NULL,
    [Productsku] [varchar](25) NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_CustomerOrder] PRIMARY KEY CLUSTERED
(
    [CustomerOrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Create the covering index
CREATE NONCLUSTERED INDEX IX_ActiveCustomerOrder
    ON dbo.CustomerOrder (CustomerID ASC, FileLastModified DESC) INCLUDE (CustomerOrderID);

-- Insert dummy data (with dates out of order)
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '17-Oct-2017', 'Prod1',20);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '15-Oct-2017', 'Prod1',30);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '18-Oct-2017', 'Prod1',41);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '14-Oct-2017', 'Prod1',42);
INSERT INTO [dbo].[CustomerOrder] ([CustomerId] ,[FileLastModified] ,[Productsku] ,[Quantity])
       VALUES (100, '13-Oct-2017', 'Prod1',43);

-- Fetch the active order for a single customer using an index hint
SELECT TOP(1) CustomerID, CustomerOrderId, [FileLastModified]
FROM dbo.CustomerOrder WITH (INDEX=IX_ActiveCustomerOrder)
    WHERE CustomerID = 100;

-- Fetch active orders for all customers
SELECT x.CustomerID, x.CustomerOrderId, x.[FileLastModified]
FROM (
SELECT CustomerID,
       CustomerOrderId,
       FileLastModified,
      (ROW_NUMBER() OVER(PARTITION BY CustomerID
                    ORDER BY FileLastModified DESC)) AS RowID
  FROM dbo.CustomerOrder WITH (INDEX=IX_ActiveCustomerOrder)
) x
WHERE x.rowid = 1;


How to measure a perfomance of SQL Server database?

I/O (hardware) bottlenecks are absent since the SQL Server runs on a virtual rack having
  plenty of physical resources under it.

Let's get this straight:

Most virtualization is pathetic - not even low, pathetic - on the IO side.
"having plenty" means nothing unless you qantify there is no IO problem.

At the end, come up with the metcis you need to measure, and find the bottlenexks. But starting with "virtual rack has no IO issues" will give you smiles from most people using virtualization.


You need to restore the data from master.

Take a dump of missing tables from master using
mysqldump  --singl-transaction>missing_tables.sql

Stop the slave.

Restore the dump file in slave using
mysql -uroot -p<missing_tables.sql

Start the replication slave will catchup.


If you are trying to delete a large number of rows in a single statement, then it is likely you are waiting on log activity. So you can:

Make sure your log is adequately sized so that growth events don't slow you down. With the defaults your log is probably starting at 1MB with 10% growth. Growth events are expensive, and if you are logging even 10 GB of deletes, this will destroy performance not just now but also in the future (due to what this does to VLFs).
If you are deleting the whole table, use TRUNCATE or DROP/CREATE.
If you are deleting most of the table, use SELECT INTO to put the data you want to keep into another table, then TRUNCATE, then move the small portion back. (Or just drop the old table, rename the new, and re-apply constraints / permissions etc.)
Minimize the impact of logging in the first place by deleting the data in chunks instead of all at once. See this article. You can also consider switching to simple recovery temporarily, so that you only have to CHECKPOINT to clear the log instead of take log backups, but you need to be sure to set it back and to take a new full backup to re-initiate the log chain.


MongoDB How to query data in object with random names?

If I want to search all news with "oid" : "421", what command should I
  use?

As per MongoDB BOL $match (aggregation) Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.

The $match stage has the following prototype form:

{ $match: { <query> } }

So, in your case the query will be like that

   db.locations.aggregate({$match: {'4210003170944.oid': '421'}}).pretty()

Let's from starting onward i am going to show through mongo shell .
First i am going to create a collection name as locations. which is a collection name in your query. Then i will insert the data , which you have provided through the code and the do the desire query with $match (aggregation).

> db.createCollection("locations")
{ "ok" : 1 }
> db.locations.insert({
... "_id" : ObjectId("5a6d068b0088c92a34324d8e"),
... "4210003170944" :
...     {
...     "date" : "20180126",
...     "sectionId" : "100",
...     "rank" : "1",
...     "newsTitle" : "Foo",
...     "oid" : "421",
...     "aid" : "0003170944"
...     },
... "2140000800903" :
...     {
...     "date" : "20180126",
...     "sectionId" : "100",
...     "rank" : "2",
...     "newsTitle" : "Bar",
...     "oid" : "214",
...     "aid" : "0000800903"
...     }
... }
... )
WriteResult({ "nInserted" : 1 })
>
> db.locations.aggregate({$match: {'4210003170944.oid': '421'}}).pretty()
{
        "_id" : ObjectId("5a6d068b0088c92a34324d8e"),
        "4210003170944" : {
                "date" : "20180126",
                "sectionId" : "100",
                "rank" : "1",
                "newsTitle" : "Foo",
                "oid" : "421",
                "aid" : "0003170944"
        },
        "2140000800903" : {
                "date" : "20180126",
                "sectionId" : "100",
                "rank" : "2",
                "newsTitle" : "Bar",
                "oid" : "214",
                "aid" : "0000800903"
        }
}

Hope this will help out to you.


In SQL Server, you can create file groups that consist of 1 or more files.  You can then migrate data into those file groups individually by rebuilding the clustered index for each table on that file group.

You can also do the same for indexes by rebuilding the index by specifying the desired file group.  You can use file size caps to ensure the files do not grow larger than 512 GB.

You will want to do this prior to migrating to the new drive array so that you can do a backup/restore or a detach/attach to the new array with no issues.  You will also want to shrink the PRIMARY file group after you're finished migrating data to the new file group(s). (I'm assuming everything is in PRIMARY currently).

This will be your easiest solution that will not require any coding changes but it will incur some significant performance hits while the data is being moved.

Your performance will likely go up markedly by splitting the data into multiple files and moving to SSDs. I would suggest doing a backup first, and don't do detach-attach if you need to physically move files around - backup/restore is FAR safer.

Multiple filegroups are superior to multiple PRIMARY files as they allow piece-meal restores, which can help get the database up and running far more quickly in a DR event.


What's default path for "db2move export" command's output

You can specify the path(s) for lob and xml columns with the -l parameter of the db2move utility only.
Other files will be created in the current directory and this can't be changed.


Commonly Used Acronyms by Database Administrators

ACID – Atomicity, Consistency, Isolation, Durability

AIO Asynchronous I/O

BASE - Basically Available, Soft-State, Eventually Consistent...essentially a counterpart to (though not really "opposite" of) ACID, this is the core principle of most NoSQL implementations.

BI - Business Intelligence

BLOB – Binary Large OBject

CAP – Consistency, Availability, Partition tolerance...  The three requirements of a distributed system (as they apply to a database) according to Eric Brewer's CAP Theorem.

CDM – Copy Data Management

CI – Clustered Index

CK – Candidate Key

CLOB – Character Large OBject

CRUD – Create, Read, Update, and Delete

CS – Cursor Stability - an isolation level supported by different database management systems.

CTE – Common Table Expression

DB – Database

DBA – Database Administrator

DBMS – Database Management System

DCL – Data Control Language

DDL – Data Definition Language

DML – Data Manipulation Language

DMV - Dynamic Management Views

DR - Disaster Recovery

DRBD - Distributed Replicated Block Device

DRDA – Distributed Relational Database Architecture

DRI - Declarative Referential Integrity

DSS - Decision Support Systems

DTD – Document Type Definition

DW or DWH – Database Warehouse

EAV – Entity-Attribute-Value (aka. the archenemy)

ERD - Entity Relationship Diagram

ETL – Extract, Transform, Load

FDW - Foreign Data Wrapper (PostgreSQL)

FK – Foreign Key

FLWOR – For, Let, Where, Order, Return - an expression form used within XQuery to query XML within a database (not sure if DB2 only)

FS – Filesystem

FTS – Fulltext Search

GBP – Group Buffer Pool

HA – High Availability

HADR – High Availability Disaster Recovery

HDD – Hard Disk Drive

ICP – Index Condition Pushdown (MySQL)

IOPS – IO Per Second

IOT – Index Organized Table (Oracle)

ISAM - Indexed Sequential Access Method

I/O – Input/Output

JDBC – Java Database Connectivity

KV – Key/Value

LAMP - Linux, Apache, MySQL and PHP

LBAC - Label Based Access Control

LOB – Large OBject

LPAR – Logical Partition

LRU – Last Recently Used (algorithm)

LUN – Logical Unit Number

MDC – Multidimensional Clustering Table

MDM – Master Data Management

MDX – Multidimensional Expressions

MED – Management of External Data

MQT – Materialized Query Table (IBM DB2)

MV – Materialized View

MVCC – Multiversion Concurrency Control

NAS - Network Attached Storage

NCI – Non-clustered Index

NF - Normal Form (ie: 1NF, first normal form)

ODBC – Open Database Connectivity

ODS - Operational Data Store

OLAP – Online Analytical Processing

OLTP – Online Transaction Processing

OODBMS – Object-Oriented Database Management System

OOM – Out Of Memory

ORM – Object-Relational Mapping

OS – Operating System

PK – Primary Key

PL/pgSQL – Procedural Language/SQL (PostgreSQL) used for writing stored procedures. Similar to PL/SQL.

PL/SQL – Procedural Language/SQL (Oracle) used for writing stored procedures. Also see SQL PL.

QPS – Queries Per Second

RAC – Real Application Clusters (Oracle)

RAID – Redundant Array of Independent Disks

RBAR – Row By Agonizing Row

RDBMS – Relational Database Management System

RBR – Row-Based Replication (MySQL)

RPO - Recovery Point Objective - how much data you can afford to lose. If your server went down, this is the point at which you'd be able to recover the data.

RR – Repeatable Read - an isolation level supported by different database management systems.

RS

– Read Stability - an isolation level supported by different database management systems.

– Replica Set - multiple physical nodes forming a logical node with redundant data. Most commonly used in the MongoDB ecosystem

RTO - Recovery Time Objective - how much time it would take you to recover the data to the RPO

SAN – Storage Area Network

SBR – Statement-Based Replication (MySQL)

SCD – Slowly Changing Dimension

SE – Storage Engine (MySQL and forks)

SEQUEL – Structured English QUEry Language, which was IBM's precursor to SQL, which is why SQL is sometimes (often?) pronounced SEQUEL and not S.Q.L.

SP – Stored Procedure

SQL – Structured Query Language

SQL PL – SQL Procedure Language used for writing stored procedures. Also see PL/SQL.

SQL/XML – an extension of the SQL language used for querying XML.

SSD – Solid State Drive

TPS* - Transactions Per Second, a measurement of database performance.

UAT - User Acceptance Testing

UDF – User Defined Function

UDT – User Defined Type

UR – Uncommitted Read - an isolation level supported by different database management systems.

URLT - Update Resume; Leave Town - For those DBAs that don't bother putting together a proper recovery strategy

XML – eXtensible Markup Language

XSD – XML Schema Definition

XSLT – XML Stylesheet Transformation


I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?

Your kernel parameters need modifying.

Edit /etc/sysctl.conf and ensure the following lines are present:

kernel.shmall = 18350080
kernel.shmmax = 75161927680
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

Then reboot the machine.


SQL Server Deadlock between two selects and one update on one table

When looking at the deadlock graph:

Update query - SPID 75

The update query locking (spid 75) is pretty straightforward, an X (exclusive) lock is requested on the key/row value, while that row is currently locked by the select query.

The update query also holds an IX (Intent exclusive) lock on a page, as is expected when taking an exclusive lock on the row  that belongs to the page. The IX lock is compatible with the IS (Intent shared) lock issued by the select query (as a result of the S lock on the row).

See: lock compatibility matrix

Select query - SPID 103

The unusual part of your deadlock is that the select query (spid 103) wants a S (shared) lock on both the row & the page that has the row data (and possibly other row data). Especially since lock escalation from row to page is not possible (row to table & page to table). A previous transaction holding the lock is also ruled out.

The explanation seems to be in the double access to the dbo.Appointments table.
Locks are taken twice and one of these table accesses will want the page lock, while the other one already aquired the rowlock.

The update fires in between these shared locks being aquired.

An example of locks requested / acquired in order

A S Lock on the key/row is taken by the first read access on the dbo.Appointments table as part of the select query
An IX Lock is taken on the page that has the row data by the update query
X lock is requested on the row by the update query
The S lock on the page that also has the row data is requested
by the second access to dbo.Appointments as part of the select query

All this means that this part of the select query is your deadlock problem:

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate;

  How would I try to prevent deadlocks from happening in this scenario?

Reduce

To reduce the probability of the deadlock happening you could look into optimizing the select query by rewriting or adding indexes.

Remove

To remove the possibility of deadlocks between these two queries entirely you could split the self join in two parts by using a temp table, or take a shared page or table lock on the Appointments table by using WTIH(PAGLOCK) / WITH(TABLOCK). Remember that this impacts concurrency.

  Also, does anyone know why the first statement with two selects would
  acquire a U lock on the selected table's PK as in Example 3? I don't
  think that it matters, but it seems strange.

The owner (select query) holds the shared lock on the key.

An U (update) lock (coming from the update query) is compatible with a shared lock see: Lock Compatibility Matrix.

The update query tries to convert the U lock to an exclusive lock:

<waiter id="process24b7826bc28" mode="X" requestType="convert" />.

The info is correct when opening the XML with SentryOne Plan Explorer (free tool).


Is there a way to install both tabular and multidimensional modes?

You answered your own question. You simply install another instance of SSAS.