Howto query all files, their fill level and additional drive info with one query? I need a query that puts together the following results:

one row per database file across all databases
additional columns with information about the locigal drive the file is on
information about the size and the usage of the file

So far I have put together the following query:

    SELECT
     GETDATE() as dt
     ,@@SERVERNAME as srv
     ,F.name
     ,F.physical_name
     ,Round(F.size * 8 / 1024, 2) as FileSizeMb
    , CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128 as FileUsedMB
    ,(F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) AS FileFreeMB
    ,Convert(decimal(18,2), (F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) / (F.size * 8 / 1024.1) * 100) as SpaceFreePerc
     ,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
     ,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes
 FROM sys.master_files F
 inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id
 CROSS APPLY sys.dm_os_volume_stats(F.database_id, F.FILE_ID) drv

Now I face two problems here:

The FILEPROPERTY SpaceUsed is only available for the database in current context - so I need a solution to collect all Fileproperties from all dbs / files.
CROSS APPLY dm_os_volume_stats does not work for dbs with compatibility level 80, but I have some of those in place.

Is there a way to reach this goal with the given conditions? The solution does not have to be a one-statement query, since it will anyways be part of a stored procedure in the end.

To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo.

First, create a #temp table to hold results:

CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname,
  physical_name sysname, FileSizeMb int, FileUsedMB int, FileFreeMB int,
  SpaceFreePerc decimal(18,2), SizeOnDiskMB int, volume_mount_point nvarchar(256),
  logical_volume_name nvarchar(256), available_bytes bigint, total_bytes bigint);

Now, some variables and a cursor. The only tricky part here really is the @exec variable, which allows each iteration of the dynamic SQL to be executed in the right database context.

DECLARE @database_id int, @file_id int, @logical_name sysname,
  @physical_name nvarchar(520), @size int, @sql nvarchar(max), @exec sysname;

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT database_id, [file_id], name, physical_name, size
  FROM sys.master_files;

SET @sql = N'SELECT
     GETDATE() as dt ,@@SERVERNAME as srv, @logical_name, @physical_name
     ,Round(@size * 8 / 1024, 2) as FileSizeMb
     , CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128 as FileUsedMB
     ,(@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) AS FileFreeMB
     ,Convert(decimal(18,2), (@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) / (@size * 8 / 1024.1) * 100) as SpaceFreePerc
     ,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
     ,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes
 FROM sys.master_files F
 inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id
 CROSS JOIN sys.dm_os_volume_stats(@database_id, @file_id) drv
 WHERE F.database_id = @database_id AND F.file_id = @file_id;';

OPEN c;
FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @exec = DB_NAME(@database_id) + N'.sys.sp_executesql ';
  INSERT #x EXEC @exec @sql, N'@database_id int, @file_id int,
    @logical_name sysname, @physical_name nvarchar(520), @size int',
    @database_id, @file_id, @logical_name, @physical_name, @size;

  FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;
END

SELECT * FROM #x;

CLOSE c; DEALLOCATE c;


Should log tables be "normalized" or store snapshots? Say we have the following structure:

USER
---
USERNAME

CONTACT
---
ID
FIRSTPHONE[FK TO PHONE.ID]
SECONDPHONE[FK TO PHONE.ID]

PHONE
---
ID
NUMBER

...Now, when creating an log table logging when a user calls a contact, should it store PHONENUMBER or NUMBER?
Option A:

LOG
---
USERUSERNAME
CONTACTID
PHONEID[FK TO PHONE.ID]

The argument against this is that if a change is made to PHONE.NUMBER, then the log itself will change; basically rewriting history.

Option B:

LOG
---
USERUSERNAME
CONTACTID
PHONENUMBER [Not a foreign key]

The argument against NUMBER this is that it's "denormalized".

So, which approach is better?

It's a business question of whether you want to know the phone number that was called or the phone ID that was called.

If, for example, you are using the log table to cross-reference with the billing from the phone company, you'd want to store the phone number that was called.  If, on the other hand, you are using the log table as a CRM tracking tool, it's much more useful to know that you dialed Walter's Widgets main line rather than the particular number that was stored in the system for that contact at that point in time.  Often, you're going to end up wanting to store both because you have both types of business requirements.

Realistically, I wouldn't give much thought to objections about denormalization of log tables.  Worry about denormalization on your core OLTP tables.  You're never going to be updating a log table, you're never going to use the log table as the source of truth for what a value is (as opposed to what the value was at a prior point in time) so there is little need to worry about denormalization.

The only real concern I'd have about denormalizing a log table is space-- if your log tables have large numbers of rows and you're storing relatively large string values that almost never change, you can end up wasting tens or hundreds of GB of space on those redundant strings and make queries against the log tables too slow to be useful.  You can mitigate those sorts of things, of course, with horizontal and vertical partitioning or by maintaining separate log/ history tables for different concepts.


How do I make PostgreSQL include all the months where I did not buy anything? I have this query:
SELECT avg(sum)
FROM
(
    SELECT date_trunc('month', "timestamp"), sum(amount) as sum
    FROM bookkeeping WHERE summary LIKE 'Bitcoin purchase:%'
    GROUP BY date_trunc('month', "timestamp")
) a

It gave me an impossible average cost of my Bitcoin purchases per month. I soon found out why: it ignores all the numerous months over the years where I did not buy Bitcoin at all, including only months when I did buy at least once. Since there aren't a ton of "empty" months included (with 0 for sum), it does the average of the sums for only the months where I bought at least once, so the average becomes completely wrong.
How do I make it "invent" rows for the months which aren't represented by at least one record, so that the avg(sum) will actually be meaningful?


How to show Large Objects (LOB) contents? How could I get to have a look into LOB contents?

\d ticketgrantingticket

              Table "public.ticketgrantingticket"
          Column           |          Type          | Modifiers
---------------------------+------------------------+-----------
id                         | character varying(765) | not null
number_of_times_used       | numeric(10,0)          |
creation_time              | numeric(19,0)          |
last_time_used             | numeric(19,0)          |
previous_last_time_used    | numeric(19,0)          |
ticketgrantingticket_id    | character varying(765) |
expiration_policy          | oid                    |
authentication             | oid                    |
services_granted_access_to | oid                    |
expired                    | boolean                |
proxied_by                 | character varying(1)   |
Indexes:
    "ticketgrantingticket_pkey" PRIMARY KEY, btree (id)

SELECT expiration_policy,
       authentication,
       services_granted_access_to
FROM ticketgrantingticket LIMIT 2;

 expiration_policy | authentication | services_granted_access_to
-------------------+----------------+----------------------------
           1165742 |        1165743 |                    1165744
           1165757 |        1165758 |                    1165759
(2 rows)

This shows those numbers by default.

I so far found this which I think is not working properly in my client somehow:

SELECT expiration_policy,
       encode(authentication::bytea, 'escape'),
       services_granted_access_to
FROM ticketgrantingticket LIMIT 2;

ERROR:  cannot cast type oid to bytea
LINE 1: SELECT expiration_policy, encode(authentication::bytea, 'esc...

I can see the same in pgAdmin III.

I am expecting blobs there because IINM oid can be used as a large object reference. We are using oid because not knowing what we were doing. I have so far assessed that one of those three columns will be safely and profitably converted to bytea, but am being unable to spy the other two columns' contents in the source (non postgres) system, so I decide to spy them in this (postgres) target system.

I ran

SELECT expiration_policy, lo_get(authentication), services_granted_access_to
FROM ticketgrantingticket
LIMIT 2;

after reading https://www.postgresql.org/docs/current/static/lo-funcs.html suggested by a now defunct comment. That worked.


Data Pump throws an error I am trying to do datapump.

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.open( operation => 'EXPORT', job_mode => 'TABLE', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'tab1.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.add_file( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''USERS'')');
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl,name => 'NAME_EXPR',value => 'IN (''CA_FILE'')');
DBMS_DATAPUMP.start_job(hdnl);
END;
/

But this throws an error:

Error report -
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3756
ORA-06512: at line 5
39001. 00000 -  "invalid argument value"
*Cause:    The user specified API parameters were of the wrong type or
           value range.  Subsequent messages supplied by
           DBMS_DATAPUMP.GET_STATUS will further describe the error.
*Action:   Correct the bad argument and retry the API.

Please help.

You got the error from line 5. This is line 5:

DBMS_DATAPUMP.ADD_FILE(
  handle => hdnl, filename => 'tab1.dmp',
  directory => 'DATA_PUMP_DIR',
  filetype => dbms_datapump.ku$_file_type_dump_file
);

If the specified dumpfile already exists, this line throws the above error. You can add reusefile => 1 to overwrite the existing file and avoid this error.

DBMS_DATAPUMP.ADD_FILE(
  handle => hdnl, filename => 'tab1.dmp',
  directory => 'DATA_PUMP_DIR',
  filetype => dbms_datapump.ku$_file_type_dump_file,
  reusefile => 1
);


Help me understand sp_server_diagnostics i have a SQl 2016 AlwaysOn cluster that has almost nightly issues. I've narrowed it down to it going down due to errors reported in sp_server_diagnostics.

This is a warning right before the cluster failed last nigh before there are health check timeouts and other messages saying always on is unhealthy

<system spinlockBackoffs="0" sickSpinlockType="none"
 sickSpinlockTypeAfterAv="none" latchWarnings="2"
 isAccessViolationOccurred="0" writeAccessViolationCount="0"
 totalDumpRequests="2" intervalDumpRequests="0" nonYieldingTasksReported="0"
 pageFaults="1882" systemCpuUtilization="35" sqlCpuUtilization="7"
 BadPagesDetected="0" BadPagesFixed="0" LastBadPageAddress="0x0"/>

Is there a specific entry I should look at first?

Ensure your servers follow VMware's Best Practices Guide for SQL Server.

Specifically, ensure the VMs running SQL Server have 100% of their memory and CPU reserved in VMware.  Ensure you're using the PVSCSI (para-virtualized SCSI) adapter for SQL Server RDMs or Virtual Disks.  Try to use as many PVSCSI adapaters as you can since that will increase the number of disk queues available to SQL Server.


MySQL: sysbench test - InnoDB vs Memory tables I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz)

I've created two tables, first one is using InnoDB storage engine and the second one is in Memory - heap table.

System details:

sysbench-0.4.12-5.el6.x86_64
CentOS release 6.4 (Final)

Prepare stage:

InnoDB

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-db=sbtest1 prepare

Memory (heap)

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --oltp-table-size=1000000 --mysql-db=sbtest2 prepare

Testing stage:

Sysbench – read only test – single table with 1 mln rows - data size 559MB (527MB data + 31MB indexes)

InnoDB

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --num-threads=128 --max-requests=100000 --oltp-read-only run

Total time: 16.3648s, TPS (transactions per second): 6111.40

Memory (heap)

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --mysql-engine-trx=no --num-threads=128 --max-requests=100000 --oltp-read-only run

This test is running much longer and I had to stop it as the load on the server was very high - even if this is in memory table!?.

I have addressed InnoDB vs MEMORY before

Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jan 17, 2012 : Mysql Memory table getting many locks

I also addressed the one headache MEMORY tables have: There is still disk I/O because of the .frm file (See my post I am using the MEMORY storage engine but MySQL still writes to my disk...Why?).

Also worth remembering is the fact that MEMORY tables do table-level locks.

You should just increase the InnoDB buffer pool and leave MEMORY tables behind


Multiple Update with Multiple Conditions We are told to watch out for things being set to null (we dont want) by using the IN clause in the following:

UPDATE Tests SET
     TestScore =
              CASE
                  WHEN TestId = 10 THEN 1000
                  WHEN TestId = 11 THEN 1100
              END,
     TestScore2 =
              CASE
                  WHEN TestId = 10 THEN 2000
                  WHEN TestId = 11 THEN 2100
              END
     WHERE TestId IN (10, 11)

But what happens when there two conditions required, namely the joint combo of (TestId, TestSubId)? Ie, what do I do for the IN clause, the ???, in the following to show it has to be in the combos (10,25) and (11,22):

UPDATE Tests SET
    TestScore = CASE
        WHEN (TestId = 10 AND TestSubId = 25) THEN 1000
        WHEN (TestId = 11 AND TestSubId = 22) THEN 1100
    END,
    TestScore2 = CASE
        WHEN (TestId = 10 AND TestSubId = 25) THEN 2000
        WHEN (TestId = 11 AND TestSubId = 22) THEN 2100
    END
    WHERE TestId, TestSubId IN ?????

In MySQL, you can use tuple comparison:

WHERE (TestId, TestSubId) IN ((10,25), (11,22))

That looks nice and succinct, although, as ypercubeᵀᴹ mentioned in a comment, it may not work well performance-wise.

However, given how the conditions are re-used in your UPDATE statement, you could also take a different approach altogether: represent the affected IDs and the new values as a derived table and use an update with a join:

UPDATE
    Tests AS old
    INNER JOIN
    (
        SELECT 10 AS TestId, 25 AS TestSubId, 1000 AS TestScore, 2000 AS TestScore2
        UNION ALL
        SELECT 11, 22, 1100, 2100
    ) AS new
    ON old.TestId = new.TestId AND old.TestSubId = new.TestSubId
SET
    old.TestScore  = new.TestScore,
    old.TestScore2 = new.TestScore2
;

This way the new derived table acts both as a filter for Tests and a supplier of new values for the update.


Which server to be master and which one slave database? I have an application for which I want to set up a master/slave mysql database, because the current db struggles under high load.

I have two different servers for db replication. Lets call them A and B.
A is the current db server.

Let's score the CPU power of A as 10 and B as 7.
B has half the memory of A. Both servers run on SSD.

The read/write ratio is about 9 to 1.

Now, my question is which server should be master? and why?

In general (until very recent versions), it is better to make the Slave the beefier machine.

Originally, you could easily run queries in parallel on the Master, but they would be run purely sequentially on the Slave.  If the system is busy enough, the Slave could get behind.  Later versions have changed to RBR and added some parallelism on the Slave.

Also, if the Slave is getting 9 times as many queries (assuming of similar length), then it would be good to have a beefier Slave for that reason.


Should I put multiple related items in one field in one record or store them separately? I'm developing a web app, something like an online dictionary.
One expression might have several different meanings, for example, the word "flight" has two different meanings: a plane trip, and the act of running away. I could use 2 entries (records) to represent them respectively.
One expression might also have several similar definitions(explanations), for example, the expression "be all about" is defined as "be focused on or interested in (a particular thing)" on lexico and is explained as "used to say what the most important aspect of something is
" on oxfordlearners
How do I represent this expression?
I can imagine two approaches, one is put those similar definitions in one entry (record), one field, something like {(definition_A, source), (definition_B, source), ...}, which seems ugly. possibly inefficient when indexing. The advantage is that multiple definitions are linked to one entry.
Another way is to use 2 entries, the question is, how do I link those two entries to one meaning.
Could someone give a hint?

The difference between my question and One field with multiple values? is every driver has a license could be used to identify them.
In the example of "square", I don't have an existing attribute to identify one meaning in "expression table" if I put its definition in another table.
Inside database, it can be done, in reality, it sometimes might not be plausible.
Even if I implement that way, bringing bad user experience by force them fill in a boring long form, I cannot import existing data, e.g. the two items above.
In other words, this method seems to need plenty of human laboring.

So the question LowlyDBA is likely to provide you some insight on how to implement a many-to-many relationship.  But I think there's a little more to your specific situation that should be expanded upon.
First how to model this.  Whenever you see " has zero/one or more ", this indicates a one-to-zero-to-one relationship.  The  doesn't necessarily need to be another entity as in the other question.
So you would have one table with your terms:
CREATE TABLE Term
(
  Term  VARCHAR(50)  NOT NULL  /* 50 is probably reasonable, but whatever works best for you */
 ,CONSTRAINT PK_Term PRIMARY KEY (Term)
 ,CONSTRAINT CK_Term_Length_IsValid CHECK (LENGTH(Term) >= 1) /* Or some other reasonable length */
 /* You can/should define additional constraints to keep invalid characters out of this column */
)
;

Next we need a table to store the definitions for each Term:
CREATE TABLE TermDefinition
(
  Term        VARCHAR(50)   NOT NULL
 ,Definition  VARCHAR(250)  NOT NULL /* Or some other reasonable maximum length */
 ,CONSTRAINT FK_TermDefinition_Definition_Of_Term FOREIGN KEY (Term) REFERENCES Term (Term)
 ,CONSTRAINT PK_TermDefinition PRIMARY KEY (Term, Definition)
 ,CONSTRAINT CK_TermDefinition_Definition_Length_IsValid CHECK (LENGTH(Definition) >= 1)
)
;

So you might ask why not replace the primary key with an integer row pointer like TermId and save some space?

In order for TermId to be a valid key, it has to replace an existing key which becomes an alternate key UNIQUE (Term).  This creates another copy of your data via an index.  Depending on the average number of definitions per term you might not really save a significant amount of space to begin with.
Any search for a definition now has to hit the index for Term (again, a copy of Term) to select the TermId before querying TermDefinition, versus just querying TermDefinition directly.  So extra code,  more index tuning/maintenance.
We should avoid creating meaningless objects when possible.

You might also ask why you would need a Term table in the first place, if all lookups would be performed against TermDefinition?

We may need a unqiue/distinct list of Terms in the future for other reasons, such as relating Terms to each other.
You may want to make it hard for people to add new Terms but not new TermDefinitions.

*Some may take umbrage with a VARCHAR primary key column, but the data has to stored somehow, the data has to be read somehow, and if that's a list of variable length characters, it is what it is.  We either create a primary key with varying characters forming the B-Tree, or an alternate key with varying characters forming another B-Tree.


SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a stored procedure no longer existing) First of all apologies for the novel but there's a lot to explain...

We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be exactly the same, but this is where over 20 years of product development gets you). For reasons I cannot say my task is to write a script which imports ALL data for a specific client from their DB. There can be many clients in a single DB so we have to detect if customerID is exists on each table and filter on that column if it does. I am using linked servers, a SQL Server Agent job, stored procedures, a cursor for all the tables and dynamic SQL to get as far as importing all of the data using select * into. The job simply drops / recreates the DB on the test server, sets compatibility to 80, alters the DB to match the settings of the source DB, creates the tables and then the stored procedures / triggers / functions / etc. This works perfectly up to creating the stored procedures, triggers, functions etc.

Basically there is one table, on the live server we're running this against in our tests, which used to have columns 'insertedpersonid' and 'updatedpersonid', but for reasons unknown these can now only be found on one server to my knowledge (and there may well be other tables where this is an issue on other servers, so simply creating the columns on the servers which don't have them is not an option). There is a stored procedure which references these columns, whether or not they exist, so when I try to create the stored procedure from a DB which does not have these columns the script just kinda falls over. I'm using try catch around every stored procedure to get it to go past the erroneous stored procedure, but I'm not happy with that as the erroneous stored procedure is not created and therefore the DB is not an exact copy of live. I could also be missing other errors in this way.

I have done a lot of searching on this topic, but cannot find any way to ignore object dependencies. Does anyone know of a way to do this?

Also if you can think of any better ways to do any or all of this then please feel free to say so. Note that this will be used by several teams around the world through a web interface (select the server > select the client > click import), so it can't be a simple backup / restore onto another server (firstly not all teams have access to the server and if we import 10 clients then in some cases the DB size will exceed 300GB, which simply won't fit onto the server we're importing to).

Lastly I am afraid I cannot give any detail on what version of SQL Server we're using as there are over 100 servers, some of which are on different versions of Windows Server. We're not happy that the testing server may not be on the same OS or SQL Server version, but it's the way it has to be. In the ideal world we'd have 1 server per SQL Server version per OS.

Thanks in advance.
Regards,
Richard

As others have said, you have problems which are goign to require serious re-evaluation of how you do business.  You can keep patching things with virtual duct-tape, or you can come up with a plan to fix the problem.

However, a duct-tape solution for this particular problem would be to check for the existance of those columns first (using information_schema.columns), and then create the appropriate version of the stored procedure (or not at all).  This will continue to create havoc, but it will at least buy you some time.


How to tell if a project needs a NoSQL database solution? How to tell if a project needs a NoSQL database solution?

I think that SQL limits define it. If the project needs bigger database or bigger fields then it needs NoSQL.

Am I right?

I looked into NoSQL database limits. They are below:

For an example I'm using MongoDB assuming that DynamoDB has similar numbers.

Mongo can be installed on a number of computers/nodes. PostgreSQL does not provide built-in tool for sharding, however citus is around.

MongoDB supports databases up to 64 terabytes and document size is 16 megabytes.

MySQL has a database limit of 256 terabytes, 64TB the maximum size for a table and record limit of 4 gigabytes.

PostgreSQL has no limit on database (4 terabytes does exist somewhere for testing) and it has a limit of 1 gigabytes for the size of any one field in a table and again 64TB the maximum size for a table.

Is there anything else that is relevant for decision making process?

How to tell if a project needs NoSQL database solution?

  I think that SQL limits define it, if project needs bigger database or bigger fields than it needs NoSQL.

  Am I right?

You are not right ;)

Choosing NoSQL over a relational database has a lot more to do with business reasons like:

do we have people on the team already familiar with NoSQL database?
will it be cheaper (in terms of hardware, licensing, support, etc)?
will it be faster (because of tooling, or the schema-less nature of document databases, etc)?

It also has a lot to do with the types of data you're storing, and your data modeling requirements.  For instance, if your application needs to store large amounts of XML or JSON data, or if every entity you save might be slightly different, you might be better off with NoSQL. But if you need to model relationships and enforce constraints, referential integrity, etc, then you would likely be better off with a relational DB.


Log backup vs differential backups We're trying to define a backup policy.
We were going to apply following:

Weekly full backup, retained for 30 days.
Nightly differential backups, retained for 7 days

But we're also exploring other option - Transaction Log backups, and Azure backup(which is offering a default of 2 hours)

Am I right in saying that that the differential backup would get us to the last differential backup, and then we can use log backups to get closer to where we want to recover from?

So, backups will be as below:

Sunday 1am- Full backup.
Monday 1am - differential
Tuesday 1am - differential
Wednesday 1am - differential

Every 2 hours since Wednesday Inc - Log Backups.

Lets say Disaster hits at Wednesday mid day!

Recover the differential , which gets us to Wednesday at 1am...
Then we use Log backups to get us to before the disaster struck.

Is that the correct distinction between the types?

We're trying to define a backup policy.

You are starting from the wrong end of things.

Backups are only the tools that you use to support a Recovery Strategy.

Start by working out - in conjunction with your Business partners; you cannot do this alone - how long you can take to get back a "broken" database (Recovery Time Objective) and how much data they're prepared to lose while you go about doing so (Recovery Point Objective).

Once you understand these two constraints, you'll have a better feeling of what you need your backup tools to do.


pg_dump issue while dumping single schema pg_dump does refuse to dump a given schema in my DB.

pg_dump -d <dbname> -s -n dm_geo -f <filename>

Result

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.4
-- Dumped by pg_dump version 9.5.4

SET statement_timeout = 0;
...
SET row_security =off;

--
-- PostgreSQL database dump complete
--

Seems your command is wrong, Follow the below command to dump dm_geo schema.

To dump without data

pg_dump dbname -s -n dm_geo -f backup.sql

To dump schema and data

pg_dump dbname -n dm_geo -f backup.sql


Splitting a deck of cards, and returning the results as if they were shuffled once Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a:

select top 26 * from DeckOfCards order by CardNumber desc
union all
select top 26 * from DeckOfCards order by CardNumber asc

It would be an even split.

But how could I have SQL Server intertwine the results returned, as if I had taken both portions of that union, one half in my left hand, and the other in my right, and shuffled them once like a deck of cards?

IE:
CardNumber 52, followed by 1, in the following sequence:
52, 1, 51, 2, 50, 3, 49, 4, etc...

This is not a homework question, just one of those things that passes through my mind when trying to get some shut eye. :)

I wanted to provide an alternative solution (using the sample data in Serpiton's answer - thanks!) that avoids sorts. This ought to be possible using ROW_NUMBER, but the query optimizer doesn't currently recognize its projection as unique. So:

DECLARE @TopHalf AS TABLE
(
    id integer IDENTITY (1, 1) PRIMARY KEY,
    CardID integer NOT NULL
);

DECLARE @BottomHalf AS TABLE
(
    id integer IDENTITY (0,1) PRIMARY KEY,
    CardID integer NOT NULL
);

INSERT TOP (26) @TopHalf (CardID)
SELECT D.id
FROM dbo.deck AS D
ORDER BY D.id ASC;

INSERT TOP (26) @BottomHalf (CardID)
SELECT D.id
FROM dbo.deck AS D
ORDER BY D.id DESC;

SELECT
    D.id,
    D.[card]
FROM
(
    SELECT id, CardID FROM @TopHalf AS TH
    UNION
    SELECT id, CardID FROM @BottomHalf AS BH
) AS Shuffled
JOIN dbo.deck AS D
    ON D.id = Shuffled.CardID
ORDER BY
    Shuffled.id,
    Shuffled.CardID;

SQLFiddle

Output:

Execution plans:


Sybase Central: How to set language for UI of Sybase Central How to set the language for UI of Sybase Central.. It appears it's taking the language settings from Windows, but having e.g. an English Sybase Central on a German Windows -- how to achieve this?

For database server and other tools it can be set either via SALANG environmental variable or via the tool dblang. On Linux you can just say somethng like

LANG=C scjview

but this seems not to work on Windows.

You can use the dblang command, but you need to disable fast launcher first.

See this for reference: Changing administration tool language settings


How to control Segmentation min/max data_id on a non-clustered ColumnStore index Given a simple row-based table without a PK but with a row-based clustered index like so:

create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value])

Then I wish to add a column store index that is segmented in the same order as the clustered index above:

create nonclustered columnstore index CS_IX_PropertyValue on dbo.PropertyValue(
    PropertyId, Value
)
with (drop_existing = on, maxdop = 1); -- maxdop=1 to preserve the order by property

MaxDop hint to preserve order came from: here

Then the following query was used to report the min/max data_id for the PropertyId column and it the full range was reported on each of the 7 segments:

create view [Common].[ColumnStoreSegmentationView]
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose: List ColumnStore table segment min/max of columns.

     Source: https://joyfulcraftsmen.com/blog/cci-how-to-load-data-for-better-columnstore-segment-elimination/
             https://dba.stackexchange.com/a/268329/9415

    Modified    By            Description
    ----------  ----------    -----------------------------------------------------------------------------------------
    2020.06.02  crokusek/inet Initial Version
  ---------------------------------------------------------------------------------------------------------------------*/
select --top 20000000000
       s.Name as SchemaName,
       t.Name as TableName,
       i.Name as IndexName,
       c.name as ColumnName,
       c.column_id as ColumnId,
       cs.segment_id as SegmentId,
       cs.min_data_id as MinValue,
       cs.max_data_id as MaxValue
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id
  join sys.partitions as p
    on p.object_id = t.object_id
  join sys.indexes as I
    on i.object_id = p.object_id
   and i.index_id = p.index_id
  join sys.index_columns as ic
    on ic.[object_id] = I.[object_id]
   and ic.index_id = I.index_id
  join sys.columns c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
  join sys.column_store_segments cs
    on cs.hobt_id = p.hobt_id
   and cs.column_id = ic.index_column_id
 --order by s.Name, t.Name, i.Name, c.Name, cs.Segment_Id
GO

I tried making the clustered index unique which did slightly affect the reported ranges but still was not monotonically increasing.

Any ideas?

Here is a Link that accomplished the segmentation in this manner but I don't see any difference.

Version:  Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

This isn't directly supported for nonclustered columnstore indexes.

It does work for clustered columnstore.

Azure Synapse Analytics has language support for doing it in one step e.g.:

CREATE CLUSTERED COLUMNSTORE INDEX <index_name>
ON dbo.PropertyValue
ORDER (PropertyId, Value);

This syntax has not yet made it to the SQL Server box product, though it is available under an undocumented feature flag so perhaps it isn't far away. It still won't work on a nonclustered columnstore index though.

General Workaround

The best you can do is to create the nonclustered rowstore index with MAXDOP = 1, then replace it with a nonclustered columnstore index with MAXDOP = 1 and DROP_EXISTING = ON.

This isn't guaranteed to preserve the ordering as you want, but it is highly likely:

CREATE NONCLUSTERED INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (MAXDOP = 1);

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (DROP_EXISTING = ON, MAXDOP = 1);

This will give you your best chance of achieving rowgroup elimination when filtering on PropertyId.

Special Case

When the desired ordering matches the rowstore clustered index (as appears to be the case in the question), there is no need to create a rowstore nonclustered index first. The documentation says:

  Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. In this case, the resultant nonclustered columnstore index will automatically be ordered.

So, in your case, it should be enough to run only:

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (MAXDOP = 1);

See this db<>fiddle demo.

Metadata

You can see the min and max values for each rowgroup and column using:

SELECT
    CSS.column_id,
    column_name = C.[name],
    rowgroup_id = CSS.segment_id,
    CSS.min_data_id,
    CSS.max_data_id,
    CSS.row_count
FROM sys.partitions AS P
JOIN sys.column_store_segments AS CSS
    ON CSS.hobt_id = P.hobt_id
JOIN sys.indexes AS I
    ON I.[object_id] = P.[object_id]
    AND I.index_id = P.index_id
JOIN sys.index_columns AS IC
    ON IC.[object_id] = I.[object_id]
    AND IC.index_id = I.index_id
    AND IC.index_column_id = CSS.column_id
JOIN sys.columns AS C
    ON C.[object_id] = P.[object_id]
    AND C.column_id = IC.column_id
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.PropertyValue', N'U')
ORDER BY
    C.column_id,
    CSS.segment_id;


MongoDB Ops manager backup issue I want to deploy backup features using mongodb ops manager version 1,8. I have already installed ops manager and it's running but while i am clicking on backup tab it's showing below message. Please suggest how can i fix it.

"Backup is an optional module, which has not been configured for Ops Manager your installation"

In the version 1.8, the backup was optional as the documentation says. That version is quite old, the current version is 3.4 where backup is included to ops manager package.


GROUPING SETS query with many-to-many relationships I'm writing a query that is mostly based on a GROUPING SET. A very simplified version of the query is the following:
SELECT GROUPING(a,b) as grouping, a, b  , COUNT(*)
FROM table
GROUP BY GROUPING SETS ((a,b), ())
ORDER BY count DESC

The real query has more groupings and a bit more stuff around it to translate e.g. one-to-many relations to something understandable. But the core is a bunch of aggregations performed at the same time on that table.
The result of that kind of query are several aggregations at once. And for multi-column grouping sets like in this query, it creates all combinations of those two aggregations.
The problem I have now is how to integrate aggregations that are not based on columns in this table, but on junction tables for many-to-many relationships with this table. For simple aggregations that are only based on a single column, I can do this with a separate query with a simple GROUP BY and UNION ALL that onto the GROUPING SETS query. It's a bit annoying, but that works.
But the problem are the more complex aggregations based on two or more columns. That's extremely easy to do with GROUPING SETS, but my workaround for many-to-many relationships doesn't really work then. I don't know how I'd write that kind of query if one of the columns of the grouping set is in the main table, and the other one in a many-to-many junction table. I also can't just join the junction table in this query, as this duplicates rows which means the aggregations are wrong.
Is there a reasonable way of doing this kind of GROUPING SET query with many-to-many relationships, and especially with combinations of columns in the main table and many-to-many relations?
Here is a minimal example of the kind of query I want to do. Keep in mind that the query I post here is obviously wrong because the join duplicates rows which get then counted wrong in the aggregations. The query I want to perform is essentially last one here, just with correct aggregations.
create table if not exists demo (
    id  serial primary key,
    a   text,
    b   text
);

create table if not exists stuff (
    id  serial primary key,
    c   text
);

create table if not exists demo_stuff (
    demo_id int references demo(id),
    stuff_id int references stuff(id)
);

insert into demo values(1,'x','y') on conflict do nothing;
insert into demo values(2,'x','z') on conflict do nothing;
insert into demo values(3,'x','z') on conflict do nothing;

insert into stuff values(1,'foo') on conflict do nothing;
insert into stuff values(2,'bar') on conflict do nothing;

truncate demo_stuff;
insert into demo_stuff values(1,1);
insert into demo_stuff values(1,2);

-- this query only on the demo table works fine
select grouping(a,b),a,b,count(*)
from demo
group by grouping sets ((a,b),());

-- this query joins the demo_stuff table to be able to
-- aggregate on many-to-many relationships for the demo table
select grouping(a,stuff_id),a,stuff_id,count(*)
from demo
left join demo_stuff on id = demo_id
group by grouping sets ((a,stuff_id),());

-- this query shows how the join leads to wrong aggregate
-- results, as it obviously duplicates rows
-- 'y' appears only once in the table, but is counted twice here
select grouping(b),b,count(*)
from demo
left join demo_stuff on id = demo_id
group by grouping sets ((b),());

Your problem query:
-- this query shows how the join leads to wrong aggregate
-- results, as it obviously duplicates rows
-- 'y' appears only once in the table, but is counted twice here
select grouping(b),b,count(*)
from demo
left join demo_stuff on id = demo_id
group by grouping sets ((b),());
Result:
grouping | b    | count
-------: | :--- | ----:
       0 | z    |     2
       0 | y    |     2
       1 | null |     4

Your comment is slightly inaccurate. The "table" is the derived table after joining demo and demo_stuff, and 'y' appears twice there. Either you want count(DISTINCT demo.id) in the result, or count(*). Those counts are different by nature and should go into separate result columns:

SELECT grouping(b), b
     , count(DISTINCT d.id) AS ct_distinct_demos
     , count(*)             AS ct_rows  -- your org count
FROM   demo d
LEFT   JOIN demo_stuff s ON d.id = s.demo_id
GROUP  BY GROUPING SETS ((b), ())
ORDER  BY 1;
Result:
grouping | b    | ct_distinct_demos | ct_rows
-------: | :--- | ----------------: | ------:
       0 | y    |                 1 |       2
       0 | z    |                 2 |       2
       1 | null |                 3 |       4

db<>fiddle here
You'll have to define precisely what to count for the correct solution. Might result in count(d.b) or count(*) or count (DISTINCT d.b) ...


Many-to-Too-Many Relationship I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question):
tblBook: ID, Title
tblPerson: ID, Name

Note that the tblPerson table contains authors, translators and editors (not only authors, that is).
Obviously, there is a many-to-many relationship between these two tables, so I also have the following junction tables:
tblBookAuthorJunction
tblBookTranslatorJunction
tblBookEditorJunction

But when it comes to joining the tables to select all the roles, I have a problem: how do I create a query to have a table that looks like:
BookTitle AuthorName TranslatorName EditorName

Is this at all possible? What should my query look like?
Edit: You can assume SQLite for the RDBMS.

Update
So far, there are two answers that contain queries. I would like to compare these answers for others who may find this information valuable... Note that the names of the junction tables are slightly different than the ones in the question. And, yes I know, the test data is boring.
Answer 1
The query in my own answer is as follows:
SELECT B.BookTitle, P1.PersonName, P2.PersonName, P3.PersonName
FROM tblBook B
LEFT JOIN tblBookAuthor A ON B.BookID = A.BookID
LEFT JOIN tblPerson P1 ON A.AuthorID = P1.PersonID
LEFT JOIN tblBookTranslator T ON B.BookID = T.BookID
LEFT JOIN tblPerson P2 ON T.TranslatorID = P2.PersonID
LEFT JOIN tblBookEditor E ON B.BookID = E.BookID
LEFT JOIN tblPerson P3 ON E.EditorID = P3.PersonID

And the output of this query looks like this:

Answer 2
The query in Erwin Brandstetter's answer is as follows (little bit modified to fit the test database):
SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors
FROM tblBook B
LEFT JOIN (
    SELECT  J.BookID, group_concat(P.PersonName, ', ') AS Authors
    FROM    tblBookAuthor J
    JOIN    tblPerson P ON J.AuthorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BA ON BA.BookID = B.BookID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.PersonName, ', ') AS Translators
    FROM    tblBookTranslator J
    JOIN    tblPerson P ON J.TranslatorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BT ON BT.BookID = B.BookID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.PersonName, ', ') AS Editors
    FROM    tblBookEditor J
    JOIN      tblPerson P ON J.EditorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BE ON BE.BookID = B.BookID

The output of this query looks like this:

If there can be more than one author / editor / translator per book - like it is in real life and like your relational design suggests, then the existing answer with plain LEFT JOINs will produce incorrect results. If any book could have at most one person for each of the roles, you could radically simplify your design: no junction tables needed, just a foreign key column in the table Books.

You need to aggregate authors, translators and editors. You could do that after joining all rows (and producing mulitple resulting rows), but it should be more efficient to aggregate in the subqueries and then join to the Books table

SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors
FROM   tblBook B
LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Authors
    FROM    tblBookAuthorJunction J
    JOIN    tblPerson P ON J.AuthorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BA ON BA.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Translators
    FROM    tblBookTranslatorJunction J
    JOIN    tblPerson P ON J.TranslatorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BT ON BT.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Editors
    FROM    tblBookEditorJunction J
    JOIN    tblPerson P ON J.EditorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BE ON BE.BookID = B.ID
WHERE  B.ID = 123

In most RDBMS it will be faster if you add WHERE BookID = 123 to the subqueries, too.


RMAN Restore control file and SP File I need to perform an incomplete recovery. We keep 7 days of backups, and I want to restore the database as it was 7 days ago. I've not done this before.
My question is: I can use the SET UNTIL TIME for the restore / recovery. But, with regards to restoring the control file and SPFile........do I need to restore those separately with SET UNTIL TIME, or will RMAN be smart enough to restore everything I need based on the timeframe I set?
Thank you.

In general there should be no need to restore controlfile or spfile. You say '7 days of backups' but you didn't specify what type of backups, nor what your retention policy is, nor what kind of housekeeping you do. If its a vanilla inc 0 and inc 1 backups with retention policy of recovery period of 7 days or more, a simple 'set until' followed by 'restore database' followed by 'recover database' should do it


Incremental encrypted per database backup and Partial restore policy for MySQL 5.7 in replication setup I will run a replication cluster using MySQL 5.7 and I would like to set up for each database a backup policy that has a full once per day + incremental every N hours with data encrypted (based on the full of the day). The backup will be done on the slave obviously.
I've looked to Percona xtrabackup documentation that cover each subject apart but not together.
For the backup steps I do it this way
Full backup command once a day
xtrabackup --backup --encrypt=AES256
  --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
  --compress
  --safe-slave-backup # in order to be able to restore on master \
  --slave-info \
  --databases foo
  --extra-lsndir=/data/backups/db_foo/20200201/full # to get clear text binlog info
  --target-dir=/data/backups/db_foo/20200201/full

Incremental backup every 2 hours
xtrabackup --backup --encrypt=AES256
  --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
  --compress
  --safe-slave-backup # in order to be able to restore on master \
  --slave-info
  --databases foo
  --incremental-basedir=/data/backups/db_foo/20200201/full # use full directory of the day
  --extra-lsndir=/data/backups/db_foo/20200201/incr_020000
  --target-dir=/data/backups/db_foo/20200201/incr_020000

You can see for the backup I pass option --extra-lsndir to get the LSN / checkpoint info in clear text alongside the encrypted ones so the incremental backup can be done. From my test it seems to work fine.
Layout of the full dump
|/data/backups/db_foo/20200201/full
├── backup-my.cnf.qp.xbcrypt
├── foo
│   ├── db.opt.qp.xbcrypt
│   ├── people.frm.qp.xbcrypt
│   └── people.ibd.qp.xbcrypt
├── ib_buffer_pool.qp.xbcrypt
├── ibdata1.qp.xbcrypt
├── xtrabackup_checkpoints
├── xtrabackup_checkpoints.xbcrypt
├── xtrabackup_info
├── xtrabackup_info.qp.xbcrypt
├── xtrabackup_logfile.qp.xbcrypt
└── xtrabackup_slave_info.qp.xbcrypt

However it's unclear to me the order of the steps necessary to restore the data with incremental and encrypted data together.
so my questions are:

are the backup commands correct ?
do you have some information how should I do the restoration? I'm confused about the order to run the decrypt/decompress/prepare, when to use --apply-log-only even after reading this resource and also how to restore only one database and not ALL the data.

Best.

After digging a bit and also based on this page I have a solution that seems to work fine.
Step 1: Decompress and decrypt backup files
xtrabackup --decompress --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
    --target-dir=/data/backups/db_xxx/full --remove-original
xtrabackup --decompress --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
    --target-dir=/data/backups/db_xxx/incr_1 --remove-original
xtrabackup --decompress --decrypt=AES256 --encrypt-key="GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" \
    --target-dir=/data/backups/db_xxx/incr_2 --remove-original

Step 2: Prepare the data

As we just want to restore one database and not the full mysql server data we pass --export to generate ready to copy files.
We pass also --apply-log to all backup except the last one (from the docs I linked in the question)

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db_xxx/full/ --export
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/db_xxx/full/ --incremental-dir=/data/backups/db_xxx/incr_1 --export
xtrabackup --prepare --target-dir=/data/backups/db_xxx/full/ --incremental-dir=/data/backups/db_xxx/incr_2 --export

Step 3: Restore the data
Now we need to replace the table files without deleting the database.
mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.' a.TABLE_NAME, ' DISCARD TABLESPACE;' ) AS 'DISCARD TABLESPACE'
    -> FROM information_schema.tables a WHERE a.TABLE_SCHEMA='foo';
+-----------------------------------------------+
| DISCARD TABLESPACE                            |
+-----------------------------------------------+
| ALTER TABLE foo.t1 DISCARD TABLESPACE;        |
| ALTER TABLE foo.t2 DISCARD TABLESPACE;        |
+-----------------------------------------------+
1 row in set (0.00 sec)

cp -a /data/backups/db_xxx/full/foo/* /var/lib/mysql/foo/
chown -R mysql:mysql /var/lib/mysql/foo/

mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.' a.TABLE_NAME, ' IMPORT TABLESPACE;' )
    -> FROM information_schema.tables a WHERE a.TABLE_SCHEMA='foo';
+-----------------------------------------------+
| IMPORT TABLESPACE                             |
+-----------------------------------------------+
| ALTER TABLE foo.t1 IMPORT TABLESPACE;         |
| ALTER TABLE foo.t2 IMPORT TABLESPACE;         |
+-----------------------------------------------+
1 row in set (0.00 sec)


Is it advantageous to scale out using multiple SQL Server instances on the same server? For a use-case of 9 databases spread across 3 time zones, is it advantagous to spread the databases onto multiple SQL Server instances on the same server?

What will I gain and what will I lose?  Tagged sql-server-2012 because it's the most pertinent, although generally the question is across SQL Server versions.

No, no and again no. One instance, one server, Always. The reasons have to do with the SQL Server memory manager and CPU scheduler architecture, it really works best if it has the whole box to itself and nothing else runs on the box. 'Partitioning' of resources (max server memory, affinity mask) solves some problems and introduces more.

If you want to consolidate, consolidate in one instance.

If you must keep separate instances then a far far far better option is Hyper-V and partition VMs. VMs have a much tighter control of resources and offer many other advantages. You need to be able to expose IO paths directly to the VM (no DBs on VHDs).


Mongodump affects app performance really bad We have quite a big mongo instance (150 GB) with no sharding, and our regular backup (mongodump) has a very significant effect on the app performance. Worse than that, due to the heavy use of mongo by the app, the backup lasts more than 10 hours.

I know that we need sharding, and we have plans to move to ElasticSearch, so I'm looking for some short-term solution.

Is there something I can do to improve this, like limiting number of queries per second for mongodump or anything?

We have a standalone mongo on a 32-core 190 GB RAM server that shares it with nginx, rabbitmq and some small things. Not the cleanest setup ever, I know :)

All data dumped via mongodump has to be read into memory by the MongoDB server. It's also worth noting that mongodump backs up data and index definitions; the time to restore can also be significantly longer as compared to other approaches since mongorestore will need to recreate any secondary indexes after the data is loaded.

As noted in the MongoDB documentation, mongodump is useful for backing up and restoring small deployments but is not ideal for capturing full backups of larger systems:

  When connected to a MongoDB instance, mongodump can adversely affect mongod performance. If your data is larger than system memory, the queries will push the working set out of memory, causing page faults.

A standalone server limits your backup options if you also want to keep your deployment available while taking a backup.

Here are a few suggested approaches in order of most to least recommended:

Approach #1: Use a cloud backup service

For the easiest short term solution, I would consider using a commercial cloud backup service like MongoDB Cloud Manager. MongoDB Cloud Manager provides continuous backup with scheduled snapshots and a retention policy (see Backup Preparations for more info). A cloud service also avoids you having to deploy any extra servers/infrastructure, so even if you plan to do so in future this is a helpful short-term solution.

The general approach would be:

Convert your standalone server into a single-node replica set (i.e. restart with the replSet parameter and run rs.initiate()).
Sign up for MongoDB Cloud Manager.
Download & install the Cloud Manager Backup Agent.

As an added benefit, Cloud Manager also includes a monitoring agent which can capture metrics history from your deployment and allow you to configure alerts.

Approach #2: Convert your deployment into a replica set and backup from a hidden secondary

This approach requires provisioning some extra infrastructure, but offloads the impact of backup from your primary server. Typically replica sets are provisioned with at least three members for high availability and automatic failover, but if your only goal is backup you can use a less ideal two server configuration.

The general approach would be:

Provision a second server which will be used for backup
Convert your standalone server into a replica set.
Add your backup server as a hidden secondary with a priority of 0 (it will never become primary) and 0 votes.
Use one of the supported backup methods to take backups on your hidden secondary. The backup methods are listed in general order of recommendation: filesystem snapshots (if supported by your configuration) or file copy (assuming you stop mongod) are preferable to mongodump.
(ideally) add another data-bearing secondary if you'd like the high availability & failover benefits of a replica set configuration.

Approach #3: Use filesystem snapshots (if available & appropriate)

A less impactful backup strategy than your current mongodump would be using filesystem snapshots, assuming you have a filesystem that supports snapshots (and all of your data and journal files are on a single volume so you can get a consistent snapshot of a running mongod). The upside of filesystem snapshots is that all data does not have to be read into memory by mongod, however snapshots can still have impact (particularly when creating the initial snapshot on a busy system). Successive snapshots are more efficient and less impactful, but are still not a complete backup solution as the snapshots are local to your server (and you only have a standalone at the moment).

Caveats

Approaches #1 and #2 both involve enabling replication to faciliate backups. Replication will add some additional local I/O on your primary server as all write operations are noted in a special capped collection called the oplog (operations log).
You've mentioned a likely need for sharding in future, but before doing so I would isolate your MongoDB workload from the other processes sharing the same server. If you can change your backup strategy to something more efficient than mongodump, remove resource contention, and capture some baseline metric history for review ... you may find that sharding is not required yet.


MySQL not reading my.cnf in MAMP Running MAMP (not PRO) 2.0.5, MySQL 5.5.9, locally on OS X and I cannot get MySQL to read a my.cnf file.

I've tried throwing it in the following directories with no results:

/Applications/MAMP/conf
/Applications/MAMP/Library
/Applications/MAMP/db/mysql

On each attempt, I make sure to shutdown the server, move the file and server is then restarted, after which a quick check of SHOW VARIABLES reveals no effect.

I'm using MAMPs my-small.cnf, renaming as my.cnf before copying over.

Any ideas as to what's going on?

Added skip-character-set-client-handshake to the [mysqld] group of the my.cnf file.

# The MySQL server
[mysqld]
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8

My-small.cnf as supplied in "support files" does not contain this, subsequently my connection was overriding the values, causing me to think the file was not being read... newbie mistake.

Solution mentioned on the MySQL manual here.


Unable to connect on premise sql server from Sql Azure db I have two SQL Server databases:

On-Premise : See the image below.

We have a static IP associated with this server in order to access remotely from other development machines. Machine is deployed in Location A and is accessible from Location B or C which are geographically separated.

To connect using SSMS, from Location B or C we are using IP Address/UserName/Password. Neither instance name is required nor FQDN. Only IP.
On Azure : Another database is on Azure. Hosted as a Service there. No VM. Its migrated from one of our On-Premise database, which was earlier accessible from #1. We are planned to move our all database to azure , but as of now, we need a cross communication for few months, until we migrate completely.

Problem

We are using a linked server in our queries, and they are failing as Azure does not support linked servers. So in order to access on premise from Azure db we are using External/Table and data-sources but still its giving me connectivity issues.

Error

  Msg 46823, Level 16, State 1, Line 1
  Error retrieving data from XXX.XXX.157.236.TableBackup.
  The underlying error message received was: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'.

Kindly help me fixing this issue.

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

Alternatively you can use replication as explained here.

Hope this helps.

Regards,

Alberto Morillo


SQL Group by column AND by date I am trying to write a query to pull group data by payment_method (one of columns in table) and by date. I can pull the query without grouping by date, but once I add the dates, I am unable to get the query to work. Does anyone have any idea how to fix my query?

Current query:

SELECT payment_method,SUM(net_amount)
DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0) as OrderDateByMonth
FROM Database
WHERE [criteria]
GROUP BY payment_method
AND DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0)
ORDER BY payment_method
AND OrderDateByMonth

You just need to use commas instead of AND:

SELECT payment_method,
       SUM(net_amount),
       DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0) AS OrderDateByMonth
FROM [Database]
WHERE [criteria]
GROUP BY payment_method,     -- <--- comma here
         DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0)
ORDER BY payment_method,     -- <--- and here
         OrderDateByMonth ;


Modeling an either/or relationship I've seen this post (How should I model an “either/or” relationship?) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship.
Say I have an entity MACHINE, and I want to creat a relationship to connect it with another entity OS, call it "installs" or whatsoever. And this OS has 2 subtypes: WINDOWS and MAC (Linux and Unix also work but just for demo purpose they are not included). Not considering virtual machine or double OS, I can only choose one of these 2 subtypes of OS, how should I model this in the entity-relationship model?

Should I

Create 1 relationship between MACHINE and OS. Or

Create 2 relationship between MACHINE and WINDOWS, MACHINE and MAC. Or

Create 1 ternary relationship between MACHINE, WINDOWS and MAC.

And should I add additional attributes to the entities or the relationship?
Thanks:D

The most powerful design tool is still natural language, predicates, and constraints. So, when in doubt use a plain text editor. Simply focus on logic, as opposed to jargon (this-that relationship ..). The following is one possible version of this story:
-- Machine type TYP exists.
--
mtyp {TYP}
  PK {TYP}

-- data sample
  (TYP)
-------------------
  ('dell alienware')
, ('macbook pro')
, ('thinkpad')
, ('hp spectre')

-- Operating system OS exists.
--
osys {OS}
  PK {OS}

-- data sample
  (OS)
-------------
  ('windows')
, ('mac os')
, ('linux')
, ('unix')

-- Operating system OS is available for machine type TYP.
--
mtyp_osys {TYP, OS}
       PK {TYP, OS}

FK1 {TYP} REFERENCES mtyp {TYP}
FK2 {OS}  REFERENCES osys {OS}

-- data sample
  (TYP, OS)
-------------------------------
  ('dell alienware', 'windows')
, ('macbook pro'   , 'mac os')
, ('thinkpad'      , 'linux')
, ('thinkpad'      , 'windows')
, ('hp spectre'    , 'windows')

-- Machine number MCH# is of machine type TYP.
--
machine {MCH#, TYP}
     PK {MCH#}
     SK {MCH#, TYP}

FK1 {TYP} REFERENCES mtyp {TYP}

-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware')
, (2, 'dell alienware')
, (3, 'dell alienware')
, (4, 'macbook pro')
, (5, 'thinkpad')
, (6, 'thinkpad')
, (7, 'hp spectre')

-- Operating system OS is installed
-- on machine number MCH#, machine type TYP.
--
install {MCH#, TYP, OS}
     PK {MCH#, TYP}

FK1 {MCH#, TYP} REFERENCES machine   {MCH#, TYP}
FK2 {TYP, OS}   REFERENCES mtyp_osys {TYP, OS}

-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware', 'windows')
, (2, 'dell alienware', 'windows')
, (3, 'dell alienware', 'windows')
, (4, 'macbook pro'   , 'mac os')
, (5, 'thinkpad'      , 'windows')
, (6, 'thinkpad'      , 'linux')

-- at this point nothing is yet installed on machine # 7

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename MCH# to MCH_NO.

Here is another example of design using natural language, predicates, and constraints to keep a DB in high NF.


How to fill a tabe automatically with a procedure in MySQL I have a very big table for users as below:

CREATE TABLE `karbar` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `password` char(41) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `token` char(40) NOT NULL,
 `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `totalspam` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `flname` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `activation_link` char(40) NOT NULL,
 `role` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `gender` char(1) NOT NULL DEFAULT 'u',
 `is_shop_active` char(0) DEFAULT '',
 `english_name` varchar(30) NOT NULL,
 `followings` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 `followers` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 PRIMARY KEY (`uid`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB

I want to fill this table with lots of data. Let's say millions of records. How to do this? I need to produce unique emails. Different data for emails. I know I can use CONCAT but don't know how to produce random data

Ok i got your answer !
Go to Talend download page and download the Talend Open Studio for Data Integration. Is free and uses Java to work.
After you downladed and installed talend go to this link to see how to generate what ever you need for free
 I am using Talend for stress testing and ETL process
You can throw the generated data mysql directly or into a CVS file and then load it into the mysql table !


Upload file to RDS Postgres Instance I want to create custom Full Text Search dictionary with special stop words from some_words.stop. How to upload this file to my RDS instance by FTP?


Select where combined with exists Table dating_users:
 user_id | username
---------+----------
      11 | taimoor
      13 | jacob
      14 | pi
(3 rows)

Table dating_likes:
 like_id | user_id | liked_user
---------+---------+------------
      54 |      11 |         13
      56 |      13 |         11
      57 |      11 |         14
(3 rows)

I want to find likes where a user has liked someone, and that someone has liked them. Here's my query:
select like_id from dating_likes where user_id = 11 and exists(select like_id from dating_likes where liked_user = 11);
Result:
 like_id
---------
      54
      57
(2 rows)

That should have resulted with only the first row, since 54 is a like where the user_id = 11 and there is a like 56 where liked_user = 11. Like 57 should not show up, since it's not mutual - all that's true is that the user_id is 11.

This is my solution
select t.*
from dating_likes t
where exists (select t1.like_id,t1.user_id
              from dating_likes t1
              where t.user_id = t1.liked_user and
                    t.liked_user = t1.user_id)

The output is this :
like_id  User_id   Liked_user
 56       13          11
 54       11          13

I don't know which solution(using Join or Exist) is better in terms of performance but I like to know if anyone wants to share their opinion.
Thanks


Temp Table Clustered Key Not Being Honored: Bug or Expected Functionality? As I was putting some test sets of data together, I noticed some funny behavior with temp tables.  When working with large sets of data in clustered temp tables that are populated via a parallel execution plan, the clustered key does not look to be honored when selecting data.  This issue also seems to affect all versions of SQL Server that I've tested (include vNext).

Here's a dbfiddle.uk example of the test.  You may have to execute it a couple of times to get the result I am finding, but it shouldn't take more than one or two executions to yield the same results.  Additionally, this is the local execution plan I'm getting on my environment which shows that the only difference between the large and small data sets is the way data is fed into the tables (e.g. parallel vs serial plan).

If you want to play-at-home, here's the test I'm running:

-- Large Data Set
CREATE TABLE #tmp
(
    ID  INT PRIMARY KEY CLUSTERED
)

INSERT INTO #tmp
-- Purposely insert in reverse order
SELECT TOP 100 PERCENT RN
FROM
(
    SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x
ORDER BY RN DESC

-- Smaller Data Set
CREATE TABLE #tmp2
(
    ID  INT PRIMARY KEY CLUSTERED
)

INSERT INTO #tmp2
-- Purposely insert in reverse order
SELECT TOP 100 PERCENT RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x
ORDER BY RN DESC

-- Large Record Set
-- Clustered Key Not Honored*
SELECT TOP 10 *
FROM #tmp

-- Small Record Set
-- Clustered Key Honored
SELECT TOP 10 *
FROM #tmp2

DROP TABLE #tmp
DROP TABLE #tmp2

I've not found any references indicating this is expected behavior, but before I submit a connect item, I first wanted to reach out and confirm this isn't a localized problem.  Can someone either point me to documentation identifying this is expected behavior or alternatively confirm this is, in-fact a bug?

EDIT: In response to the comments about not including an ORDER BY clause, I was always under the assumption the TOP keyword returned the data in the order in which it was inserted, which should, in this case, be the order dictated by the clustered key.  When running the same statement against a formal table, the expected behavior is returned:

-- Large Data Set with a Formal Data Table
CREATE TABLE tmp
(
    ID  INT PRIMARY KEY CLUSTERED
)

INSERT INTO tmp
-- Purposely insert in reverse order
SELECT TOP 100 PERCENT RN
FROM
(
    SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x
ORDER BY RN DESC

-- Large Record Set
-- Clustered Key Not Honored*
SELECT TOP 10 *
FROM tmp

DROP TABLE tmp

(6325225 row(s) affected)

(1 row(s) affected)
ID
-----------
1
2
3
4
5
6
7
8
9
10

(10 row(s) affected)

(1 row(s) affected)

Even the execution plans are the same, so why the different result sets between a temp table and a formally defined table?

Finally, a shout out to Joe Obbish as I gratuitously ripped off his CROSS JOIN approach to build large sets of test data as it's quite efficient!

There is no guarantee of ORDER without ORDER BY.

The execution plan for both has "Ordered = False".

This means you may get the results in key order but equally may not.

Specifically see When can allocation order scans be used?

  The only time such a scan will be used is when there’s no possibility
  of the data changing (e.g. when the TABLOCK hint is specified, or when
  the table is in a read-only database) or when its explicitly stated
  that we don’t care (e.g. when the NOLOCK hint is specifed or under
  READ UNCOMMITTED isolation level). As a further twist, there’s a
  trade-off with setup cost of the allocation order scan against the
  number of pages that will b read – an allocation order scan will only
  be used if there’s more than 64 pages to be read.

As the local temp table is not accessible to other connections you get this behaviour without explicitly taking a table lock however the comment about size of table still applies which is why you see the difference in your two cases.

If you need a specific order add an ORDER BY to get scan in key order (with "Ordered = True").


How could duplicate entry on unique key happen if only one master is active? Say, I we have master-master replication with only one master active at a time.

We have users table:

create table users (
 id int auto_increment,
 email varchar(255),

 primary key id(id),
 unique key email(email)
);

and web application cluster which inserts new record for new users.

Now we got error which stopped replication:

Error 'Duplicate entry 'something@example.com-' for key 'email'' on query. Default database: 'xxx'. Query: 'INSERT INTO users  ...

Question is: is that true that this error in this setup is impossible, and probable reason of error is that we had two masters active at same time?

Do not see the problem. When somebody enters the email address a second time then this happens. Nothing to do with replication. This is what the unique key is used for.

If this error would break the replication then I do not understand how replication could ever work. Every user could easily break the replication by, in this case, entering an email address 2 times.

I am not an MySQL expert but I think that the doc relates to errors other then maintaining data integrity. Perhaps somebody with more in-depth knowledge of clustering can help you on how to configure master-master clustering.


What are the events that EVMd process in Oracle RAC distributes across cluster? I am new to Oracle RAC. I was studying about Oracle RAC background processes, in which I came across EVMd process which distributes the event message across other nodes in the cluster.

Could you please make me understand, what are these event message? I worked on it but didn't get proper answer.

Thanks
Prabhakar

The Cluster Ready Services (CRS) process is the primary program for managing high availability operations(start, stop, monitor, and fail over) in a cluster.

The crsd process generates events when the status of a resource changes and EVMd publishes these events.

For example, if one of the cluster node goes down then the VIP running  should fail over to another node.
In this case crsd does the work of fail over and that changes the status of the resource-which is VIP in our example.

EVMd process distributes such events to all of the cluster members so that they are aware of the cluster changes.


Do I have to commit after an exception has been raised? My database generates a lot of orphan pages, and I'm trying to figure out why.

I often do this in my python code, using the fdb driver:

cursor = connection.cursor()
for item in list:
    try:
        cursor.execute(sql_insert_statement, (item,))
    except fdb.DatabaseError:
        log(f"Exception: duplicate found in database for {item}")
        continue

connection.commit()

I generate a lot of exceptions due to primary key unique constraint violations (which is fine), and I only commit the "main" transaction once, when all items in the list have each executed their INSERT statement.

Is this the right way of doing things? Or do I have to commit after each  exception that occurred instead (with a finally clause which would trigger after every try regardless of its outcome)?

At the very least, I doubt I need to commit after each cursor.execute, judging from the documentation.

You always need to either commit or rollback a transaction when the relevant unit of work is complete. Firebird transactions are not automatically aborted on errors (but usually the effects of the statement causing the error are undone). If you don't commit or rollback and the connections gets closed, open transactions will be rolled back eventually, but this is inefficient and can cause performance degradation (depending on how long that transaction remains open).

So, no, you don't have to commit (or rollback) immediately after an error, you commit when your work is complete, or rollback when your unit of work needs to be aborted.

However, very long running transactions can be problematic for other transactions, so make sure that your unit of work (transaction) is as short as possible (but not shorter than necessary for the work to be done).

However, failure to commit or rollback cannot cause errors about orphan pages. Orphan pages are a result of Firebird allocating database pages, but failing to add/register them to an object (table, index, etc), which means they exist and take space, but are unusable because nothing points to them.

This is usually the result of Firebird crashing or being killed in the middle of a write, or allocation bugs. You may want to check your logs for errors.


Commit truncation with rowcount I am new to SQL, I have a million rows in a table to delete. I wrote code to commit the transaction (delete the rows) every time the count reaches 50k and reset the counter again. My question is why I put a commit statement for the counter 50,000 not 10,000 or 1,000?

Is it OK to commit for every 1,000 deletes? What will happen and is it likely to affect performance?

It sounds like you are trying to delete everything in your table, is that correct? If so, I would check into a TRUNCATE syntax, this will cause the least amount of performance impact as it will not log the row by row delete. Additionally, since this is relatively low impact you shouldn't need to divide this up for only 1 million rows.

  In Oracle, truncating a table is a fast way to clear out records from
  a table if you don't need to worry about rolling back. One of the
  reasons is that when the table is truncated, it does not affect any of
  the table's indexes, triggers, or dependencies. Truncating a table is
  also a lot easier than dropping the table and recreating it.

Oracle / PLSQL: TRUNCATE TABLE Statement


Fast data loading with duplicate check We are developing an application that loads data from clients and then processes it in some predictive analytics tasks. Each client has different data structure: some clients will for instance have very detailed website activity logs, others will not. We try our best to map their data model to ours.

Currently, we modeled our problem by defining "domains". A domain can be "Transactions", "Customers", "Events"... Each domain has a minimum set of columns that is required to link it to other domains or simply to make sense (each transaction always must have a timestamp and a customer ID, for instance). It then has "tags", types of which can be dynamically added, that represent extra columns that that specific client has.

We load data from the clients in chunks of a few gigabytes. Because an extract can have overlapping data on what has been loaded before, or even updates of previously loaded data, we need to be able to quickly link a record to the existing record (upsert). Would not be a big problem if each client had a defined ID for each entity of each of our domains. But this is not the case: most clients are not capable of providing a sound ID in 1 value.

On these cases, however, multiple columns are enough for a primary key. What I do then is that I take a hash of those columns and use it as a primary key. The fact that keys are randomly distributed creates a performance problem in a clustering key.

What kind of storage engine could I use to makes this efficient, while maintaining my capability of nicely querying it? With our current solution (innoDB), it takes hours to load a million rows on a computer with a classical hard disk. As a startup, we would be interested in a free or cheap solution.

The options we are considering right now:

Simply have a very slow data ingestion process. Once historical data is loaded, the DB only needs to be fast enough to absorb the incoming data each night.
Use a noSQL solution that would absorb the data faster and that would be able to run on commodity hardware. This would require a lot of work because the data must be queryable in a lot of different ways.

There are many articles already on performance tuning of INSERT or `BULK INSERT' queries. Main points:

Optimize hardware: RAID/SSD/Fusion IO etc.
Tune source file system and network: copy files locally, use JUMBO packets if reading over network
Find optimal batch size, minimum logging
Insert into table with no indexes, triggers, constraints etc.
Apply all calculations, lookups, indexes, compression etc after load or in chunks
After all data is ready AND main table is stored on same disks, then switch partition - usually it will be almost instant as it will be a metadata only operation. However there cases when it is not.


What is back-end programming? I like to know what exactly is the back-end programming? Does it include offline databases? Everyone who designs and creates databases and tables is a back-end programmer? What do they mean exactly by server side programming? By which languages they do this and may someone gives some examples about some of those programs that are written for server and we call them back-end programs? Maybe its good to ask for which kind of projects/programs/applications we need back-end programming?

Sorry if this is duplicated, broad or boring, but I couldn't find my answers any other places!

Back-end and front-end are emerging separations of concerns in development, typically these days "web development". In some shops you have two types of developers, often on separate teams. These different groups of developers often specialize (intentionally or not) and this specialization creates a pretty useful mental model,

Back-end developers are responsible for things not pertaining to the user-interface. This is usually half the work. Their development stops at some fairly arbitrary hand-off point.
Traditionally back-end developers were the only kind of developers because the user-interface was massively simpler and the pool of people that had a complete understanding ("full stack" in modern parlance) was the norm. As the user-interface became more complex and a litany of vocational schools and camps emerged training people for a specific tract of programming there was a need to place those from this pigeonholed and often spoon-fed pool of the workforce. The work load was removed from back-end developers, and the front-end developer was born. Back-end developers today are likely to be skilled with the database, SQL, Perl, Python, C, C++, and will have a thorough understanding of Networking Administration, and System Administration (and may have themselves migrated from when those forms of Administration were more or less employable on their own merits).

Front-end developers are responsible for everything at the hand-off-point forward. Today, professional front-end developers are largely of two camps: Angular and React, previously they were oriented to jQuery, and Backbone like technologies. Today the front-end entails massive libraries, especially Angular, and they take a substantial investment in time to learn, and to stay up to date with. Typical front-end developers are skilled with Angular or React and have extensive knowledge of REST-clients, JavaScript, HTML, CSS, Reactive design, Translation and i18n, Fluid design (mobile vs desktop) and UX concepts.

Some languages straddle both camps pretty well and you'll find a lot of cross over, like Java/Dalvik on Android which creates pretty well rounded programmers, and also Node.JS which permits JavaScript front-end programmers to transition to and from the backend.
As far Database Administrators are concerned, you're likely a Back-End developer if you're here. There are databases (often hosted solutions) that cater to front-end developers which lack a firm understanding of how a database works. One such example is Google Firebase, but shy of being employed at Google you're likely not a Database Administrator in any useful sense for merely using the hosted Firebase service.


Where can I find a list of reserved logins? I have a web page where the user can configure a SQL Server login and password. I am trying to implement validation for this page.

I looked up the requirements for a SQL Server login, and found this Microsoft article. According to that article, SQL Server logins cannot be a reserved login name. "sa" and "public" are provided as examples. A full list of reserved login names is not provided, and I was not able to find one by searching.

Is there a list of reserved SQL Server login names? If so, where can I find it?

If I try the following:
create login [public] WITH PASSWORD = '';

I get this error message:

Msg 15025, Level 16, State 1, Line 12
The server principal 'public' already exists.

That makes me think that reserved logins are the server principals that exist on a fresh install of SQL Server. I can get a list of those with valid login names with this query:
SELECT [name]
FROM sys.server_principals
WHERE [name] NOT LIKE '%\%';

Here are the results in SQL Server 2016:
╔═════════════════════════════════════════╗
║                  name                   ║
╠═════════════════════════════════════════╣
║ sa                                      ║
║ public                                  ║
║ sysadmin                                ║
║ securityadmin                           ║
║ serveradmin                             ║
║ setupadmin                              ║
║ processadmin                            ║
║ diskadmin                               ║
║ dbcreator                               ║
║ bulkadmin                               ║
║ ##MS_SQLResourceSigningCertificate##    ║
║ ##MS_SQLReplicationSigningCertificate## ║
║ ##MS_SQLAuthenticatorCertificate##      ║
║ ##MS_PolicySigningCertificate##         ║
║ ##MS_SmoExtendedSigningCertificate##    ║
║ ##MS_PolicyTsqlExecutionLogin##         ║
║ ##MS_PolicyEventProcessingLogin##       ║
║ ##MS_AgentSigningCertificate##          ║
╚═════════════════════════════════════════╝

Both of the examples mentioned in the article are in the result set. Trying to create logins with any of those names seems to fail. For example, both of the following fail:
create login [diskadmin] WITH PASSWORD = '';
create login [##MS_SmoExtendedSigningCertificate##] WITH PASSWORD = '';

I can't say for sure that the above is the complete list but hopefully it helps.
Just for fun I tried creating all possible alphanumeric logins to see which ones would throw an error. With a single CPU I was only able to create and drop 55 logins per second. At that rate it would take about a year to test all possible six character combinations, so this doesn't seem to be a viable approach.


Connection Manager in SSIS 2017 Package NOT Saving the Password We're using Visual Studio 15.7.2 and using SSIS on SQL 2017 (14.0.3029.16).

We built a connection manager to a 3rd-party, vendor managed SQL Server 2008 R2 (10.50.6529.0).  Our only allowed connections to this server are via SQL Login, so Windows Authentication is a no-go.

We can run the package inside of Visual Studio/SSDT just fine, the package executes and completes successfully as we'd expect.

When we open the package in SSDT we're met with error immediately:

Upon looking at Connection Managers we're met with the following icon:

When we open the connection manager, this is what we see (password blank, should be *********):

The package is configured to run (via job) at 8:45 pm Eastern and it fails every time. We get a plethora of errors that basically state we can't connect to the 3rd-party server.  Investigation shows that the password isn't being saved so we get denied at the front door!

Has anyone ran into this before?  What options do I have to rectify?  I'm somewhat new to SSIS, so I'm learning a lot of hard lessons fast.

By default SSIS saves and encrypts sensitive data (passwords, etc.) with something called UserKey. This means that no-one else is able to use the connection with saved password other than the person who previously saved the SSIS-package.

This can be changed through the SSIS package properties -> ProtectionLevel. You should select "EncryptAllWithPassword" and set a password to "PackagePassword" property and save the package. Now when you open the package next time, SSDT will ask you to enter the password. When entered, connection should be working just fine.


XML queries with case insensitive tag- which is the best query I had asked question about XML queries with case insensitive tag earlier and i found a solution too. But i also found some other solution.
So table was something like

DECLARE @myTable TABLE ( yourXML XML )

INSERT INTO @myTable SELECT '<z>1<c>2</c></z>'
INSERT INTO @myTable SELECT '<Z>1<c>2</c></Z>'

And all the below solutions return what i wanted(case insensitive tag)

-----Solution 1----------

SELECT * FROM @myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = "z"]/*[lower-case(local-name(.)) = "a"] where  ( ($x/*[lower-case(local-name(.)) = "b"][1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 2----------

SELECT * FROM @myTable
WHERE
(CONVERT(XML,LOWER(CONVERT(VARCHAR(MAX),[yourXML]))).exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 3----------

SELECT * FROM @myTable WHERE
([yourXML].exist('for $x in (/Z/A,/z/a) where  ( ($x/b[1],$x/B[1]) = 1 )  return $x') > 0 )
-------------------------

Now i want to know which one is better to use by considering its all X-query commands(support for value(),exist(),count(),query() etc), Performance,Efficiency etc.

You can easily test the performance yourself.

Create a regular table that you can test your queries on.

create table myTable ( yourXML XML )

Add a couple of rows that will give you a match.

INSERT INTO myTable SELECT '<z>1<c>2</c></z>'
INSERT INTO myTable SELECT '<Z>1<c>2</c></Z>'
INSERT INTO myTable SELECT '<Z>1<c>2</c></Z>'

Add a whole bunch of rows that will not match in different parts of the XML.

insert into myTable
select top(10000) '<X>1<c>2</c></X>'
from sys.all_objects as o1, sys.all_objects as o2

insert into myTable
select top(10000) '<Z><X>1<c>2</c></X></Z>'
from sys.all_objects as o1, sys.all_objects as o2

insert into myTable
select top(10000) '<Z><X>1</X><c>2</c></Z>'
from sys.all_objects as o1, sys.all_objects as o2

Use SET STATISTICS IO (Transact-SQL) and SET STATISTICS TIME (Transact-SQL) and execute your queries in SQL Server Management Studio.

set statistics time on
set statistics io on

-----Solution 1----------

SELECT * FROM myTable WHERE ( [yourXML].exist('for $x in /*[lower-case(local-name(.)) = "z"]/*[lower-case(local-name(.)) = "a"] where  ( ($x/*[lower-case(local-name(.)) = "b"][1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 2----------

SELECT * FROM myTable
WHERE
(CONVERT(XML,LOWER(CONVERT(VARCHAR(MAX),[yourXML]))).exist('for $x in /z/a where  ( ($x/b[1]) = 1 )  return $x')>0 )

-------------------------
-----Solution 3----------

SELECT * FROM myTable WHERE
([yourXML].exist('for $x in (/Z/A,/z/a) where  ( ($x/b[1],$x/B[1]) = 1 )  return $x') > 0 )

-------------------------
-----Solution 4----------

select *
from myTable
where yourXML.exist('(Z/A,z/a)[(b,B)=1]') = 1

Switch to the message tab and evaluate execution time and required reads.

Presumably you have better data in your database to test on. The performance characteristic will change depending on the actual structure of the XML you have to deal with in the real world.

A side note is that your queries are not equivalent.
The first is easily adapted to longer element names.
The second query changes the content of the elements, not just the element names.
The third query does not really deal with case insensitive element names, it just enumerate all possible element names that in this case happens to be different in cases only. If you wanted to deal with lets say a 3 letter element name with your third solution you would have 8 (I think) different permutations to deal with.

I added a fourth solution,  mostly because it is short and pretty, that has the same limitations as your third solution. It is in my measurements slightly faster than using FLWOR.


How to use fsyncLock() safely in backing up a single mongod I read from mongodb document:

important:
db.fsyncLock() may block reads, including those necessary to verify authentication
warning:
When calling db.fsyncLock(), ensure that the connection is kept open to allow a subsequent call to db.fsyncUnlock(). Closing the connection may make it difficult to release the lock.

In worst case, I use fsyncLock() to block database and suddenly lost connection to database (because of temporary network failure). I cannot reconnect because fsyncLock() block reader. My database will be blocked forever.

I am considering to shutdown mongodb to backup.

What is the most safest backup solution for single mongod ?

You could use a filesystem based snapshot (like LVM, EBS or similar) - as long as the snapshot is point-in-time and includes the journal then you do not need to lock the database at all when taking a backup.  Once the snapshot is complete, simply archive it off to your backup drive/host/cloud and then remove the snapshot.

Other options would require this to be a replica set. With a single node replica set you could use MMS backup (no other hosts required, but you do have to have an oplog, and it is not free).

If you added a second node (and an arbiter to break ties in voting), then your secondary could function as your backup.  You could take it down to make a backup without impacting your original node (again, as long as you have an arbiter), or you could use the techniques above also


Store n:m data with most n:1 tuples I have these tables:

create table data (
    id int identity not null,
    ref int not null
);

create table reference (
    refid int not null
);

with ~2 million rows in data and ~200k rows in reference. I add ~5k rows to data each day, resulting in 500-5k additional rows in reference per day.

In most of the cases I need to save only one reference for one data column (multiple data entries can have the same reference) but in some cases (currently about 0.1%) I need to save more than one reference.

I probably could use a third table like:

create table data_reference (
     dataid int not null,
     refid int not null
);

to create a n:m mapping but for most of the values it will not have any gain here, it will just create another join...

I could also create a second column in each data row, because in most of the cases I have only one additional reference (but in theory there could be more) and save null if no other reference is there - which seems to me quite bad because there could be tuples with 3 or more references.

Is there an efficient and easy to maintain way to save such data?

Using an intermediate table is the proper way to do this.  With proper indexes and good maintenance it won't hurt performance much at all.

Avoid the multi-column solution (refid1, refid2 etc.) - that's repeating groups and not properly normalised.  Plus your queries will degenerate into a dog's breakfast of OR clauses and CASE statements, which will hurt performance.

You may get some benefit from subtyping your data rows so most use your current arrangement but those with multiple reference use a different one.  Your queries will UNION ALL the two sets to get the total.  I really doubt the overhead from this will compensate for the mostly-redundant join to data_reference.

The subtyped schema would look like this:

create table data (
    id int identity not null,
    ref int not null,       -- set to a standard value for rows with >1 reference.
    is_single_reference bit not null
);

create table reference (
    refid int not null
);

create table data_reference (
     dataid int not null,
     refid int not null
);

The query would be something like:

select
    <whatever>
from data as d
inner join reference as r
    on d.ref = r.refid
where d.is_single_reference = 1

union all

select
    <whatever>
from data as d
inner join data_reference as dr
    on dr.dataid= d.id
inner join reference as r
    on r.refid = dr.refid
where d.is_single_reference = 0;

This is fairly ugly.  You'd be lucky to avoid two scans of data.  Ideally you'd want filtered indexes but IIRC these aren't availble until SQL Server 2008.  The applicaiton would have to know how many reference rows there were per data, set flags accordingly and maintain those if the number changed.  Just use the n:m approach.


How to Backup Remote Database to FTP server I can connect to a remote SQL Server via Management Studio. But, when I create backups, they get created on the server's disk.

How can I transfer them to an FTP server?

I only have a login to the MS SQL Server & the FTP site where I need to save the backup file.

You have two processes here that are discrete,

Dumping the database.
Moving the dump to an FTP server.

There are numerous ways to accomplish both of these. You'll have to ask a more specific question about either one.

FTP is an old antiquated transfer protocol that's highly insecure and you should never use it unless you absolutely must. Instead, consider using something like rsync over SSH. Microsoft has jumped on SSH with the latest version of Windows 10 providing an install option from the Windows Store, and it's likely the most secure method of doing what you're after: if you have control over both the server and the client, I would be using that method for transfer.


Using IMPORT FOREIGN SCHEMA with Greenplum database I have setup a PostgreSQL 12 database and am trying to connect to a Greenplum database in order to create proxy tables.  I am able to connect to the Greenplum db, but I get an error when I try to use the IMPORT FOREIGN SCHEMA command.

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER "remote_server" INTO schema_test_1;

returns:

    ERROR:  Greenplum Database does not support REPEATABLE READ transactions. (variable.c:570)
CONTEXT:  remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
SQL state: XX000

I read that REPEATABLE READ is not supported in Greenplum and to use SERIALIZE instead.  Is there a way to edit the IMPORT FOREIGN SCHEMA command so that I can replace REPEATABLE READ with SERIALIZE?

I am using PGadmin 4.

Update:

I found that I can get commands to work if I write them as complete transactions and include the following before any commands:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Is there a way to set this as the default value for all transactions going through the Foreign Server?

No, that cannot be done. You have to use a less ancient fork of PostgreSQL that has heard of REPEATABLE READ.

To make that work, you'd have to modify the PostgreSQL source code, but if you do you should use SERIALIZABLE only with Greenplum.