Why is database ownership by groups not allowed? Which user should own a database? Per microsoft "ALTER AUTHORIZATION for databases for SQL Server":

  Requirements for the new owner:

  The new owner principal must be one of the following:

  A SQL Server authentication login.
  A Windows authentication login representing a Windows user (not a group).
  A Windows user that authenticates through a Windows authentication login representing a Windows group.

Apparently a group cannot own a database.  Indeed invoking

alter authorization on database::MyDatabase to [domainname\SQLServerAdminGroup]

results in the message

  An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or assymetric keys.

Having a single member of, say, a Windows security group of SQL administrators be a database owner seems vulnerable to that account becoming disused.  That seems to be what happened when the owning employee left the company in this question.  This answer summarizes the problem of database ownership being an NT primary principal as follows:

  Having the database ownership default to the NT primary principal creates a containment issue (the owner is an NT SID managed by AD and does not travel with the database files, the NT account can be thumbstoned etc etc etc).

I'm new to SQL Server administration and the restriction that only primary principals can be owners stands out compared with ownership of, for example, files.  Per microsoft "How Owners are Assigned and Changed":

  By default, a new object's owner is the security principal identified as the default owner in the access token attached to the creating process. ... The only exceptions occur when the user is a member of either the Administrators group or the Domain Admins group. In both cases, the Owner field in the user's access token contains the SID for the group, not the SID for the individual user account. The assumption is that administrative accounts are used only to administer the system and not for any individual purpose. As a result, objects created by one administrator can be managed by other administrators in the same group.

In other words,

for files created by an administrator the default owner of the file is the group, while
for databases the owner cannot be a group.

This leaves me with the following questions:

Is there an underlying reason why databases cannot be owned by a secondary principal?
Which principal should own a database?  Is there a best practice?  If so, what is the reasoning behind that best practice?

Honestly, sa or a disabled SQL Server account with absolutely minimum permissions is the best choice.  As to why?  Well, I could write out three or four paragraphs on that, or I can share my favorite article on What account should own the databases and why. A very thorough explanation covering all the bases. Highly recommended.
Here's a core excerpt: (Edited to add this 1/9/2020)

All right, well how about sa? It can already do anything it wants so it’s not like we are granting additional permissions, it can’t quit or be fired, and no one should be using it anyway. Heck, you probably have sa disabled, right? You know what? That’s actually perfectly reasonable! In fact sa is a really common id to use as the database owner.
The only risk is if the database becomes TRUSTWORTHY. Then you can create stored procedures that can act as sa. It’s honestly not a huge risk even then since you have to have impersonate permissions to create a procedure with the EXECUTE AS clause. Meaning that in order to create that stored procedure you have been explicitly granted the ability to impersonate a member of the db_owner role (please, please, please don’t do this) or be a member of db_owner yourself. That does mean that someone with db_owner can become a sysadmin but again, fairly minor risk. TRUSTWORTHY isn’t that common, and hopefully, if you are using it you understand the risks and are avoiding dangerous permissions.
But, a risk is a risk. So if we are feeling particularly paranoid and/or have a very sensitive system the easiest solution is to create a SQL login (with a stupidly complex password since no one is ever going to log in as it), disable it, and then use that as the owner.


MongoDB Replica Set Failure Some background

I had an issue with my replica set the last 24 hours, where the members could not connect to each other, and I got errors such as; could not connect to .... the machine actively refused it; read error; connect error etc.

Things I did to try and resolve it:

I rebooted my PC
I restarted the servers a bunch of times

But nothing seemed to work. I could connect to each member via the shell, and each member status was SECONDARY.

Why I did this

On one of my members a kept getting a message that there was a problem or failure with my shutdown, and that there was some issue with the mongod.lock file (I can't remember the exact message...).

My resolution

Finally, I deleted the mongod.lock file in each member directory, boot up the members, and a connection was established and a vote took place.

My Question

Was this a wise move? What would be the preferred method?

Thank you for your input and help!

As per my original question, my solution was as follows:

My resolution

I deleted the mongod.lock file in each member directory, boot up the members, and a connection was established and a vote took place.

Thank you to Dmytro Shevchenko for assisting with clarification via his link, explaining that deleting the mongod.lock files are OK.


Error:Must declare the scalar variable "@SumSalary" create procedure pro3(@deptid int, @SumSalary int out) as
begin
    select @SumSalary = sum(e.salary)
    from emp e
    inner join dept d
        on e.deptid = d.deptid
    where d.deptid=@deptid;
end

declare @SumSalary int

exec pro3 @deptid = 10, @SumSalary = @SumSalary out --Error:Must declare the scalar variable "@SumSalary".

I got the below error

Must declare the scalar variable "@SumSalary".

Please help me

It showing error because you might running your SP and execute statement in same SSMS window. It will work if you run the below query in separate SSMS window and it will not show the error.
declare @SumSalary int
exec pro3 @deptid = 3, @SumSalary = @SumSalary out
OR
You just change the local Variable name as below and you can run the query in same window -
create procedure pro3(@deptid int, @SumSalary int out) as
begin
select @SumSalary = sum(e.salary)
from emp e
inner join dept d
on e.deptid = d.deptid
where d.deptid=@deptid;
end
declare @SumSalaryOutput int
exec pro3 @deptid = 3, @SumSalary = @SumSalaryOutput out


How to change dbid in oracle to specific value? I want to change dbid of oracle 11g database.
Current dbid = 2241883xxx   And
want to set dbid = 1380515xxx

You would be better off using the nid command to change the dbid to a new value. then register your database with the rman catalog, if you use one.

nid help=y

DBNEWID: Release 12.1.0.2.0 - Production on Wed Oct 7 13:56:42 2015

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

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO


What is the CURRENT_DATE or current date value function for SQL Server? What is the SQL Server functionality that allows you to get the current date as a DATE type like the SQL Standard's CURRENT_DATE feature. What is the <current date value function> for SQL Server?

Using PostgreSQL, I'm looking for something like,

SELECT CURRENT_DATE;
 current_date
--------------
 2018-06-27
(1 row)

You can use either GETDATE (return type datetime) or SYSDATETIME (return type datetime2), with the difference being the precision up to nanoseconds for SYSDATETIME().

Example:

SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime

Results:

fn_GetDate                 fn_SysDateTime
2018-06-27 10:31:18.963    2018-06-27 10:31:18.9659170

See Date and Time Data Types and Functions (Transact-SQL) in the product documentation.

For completeness, SQL Server also recognises CURRENT_DATE as mentioned in the question, as an ODBC scalar function:

SELECT {fn CURRENT_DATE()};

This returns varchar(10), so would need an explicit cast or convert to the date data type:

SELECT CONVERT(date, {fn CURRENT_DATE()});

The built-in functions are recommended over ODBC scalar functions.


Importing plain (raw) files into a postgres table (bytea and large objects) What is a good way to insert/copy plain files into a postgres table, preferrably using the psql command-line?

In my case the files are a bunch of eMails from Maildir archives, so I tried to use COPY:

psql -c "COPY emails (data) FROM '/tmp/emailfile' WITH (FORMAT text);" emails

which I would use in a for-loop shell script (for file in $(ls dir); do psql ...; done).

However, I have trouble finding a good "delimiter" that couldn't possibly be in the file,
and I'm getting these errors: ERROR:  extra data after last expected column.

So I considered using the COPY ... FORMAT binary version and a BYTEA field in the db (then convert the column to TEXT inside the db),
but that requires a file header and trailer, which I have no easy way of building on-the-fly.

Is there an easy way to do this from the commandline, or do I need to write a python script for this?

Server-side files

Assuming  the account has the pg_read_server_files role (or is superuser), if the files are accessible on a filesystem mounted on the server, and their paths have been collected in the table, this will grab the contents efficiently:

UPDATE emails SET mail_data = pg_read_binary_file(emails.fullpath);

It is more efficient than using large objects as an intermediate storage area.

Client-side files

When the files are not accessible server-side or the account does not have elevated permissions, with psql a more generic solution may be, for each file:

\set clientpath '/path/to/file'
-- assume clean paths (without any character that would be special to the shell)
\set contents `base64 :clientpath`

insert into email_data([other columns...], mail_data)
 values ( [other columns values...], decode(:'contents','base64'));

A base64 intermediate representation is used because psql doesn't support parameters in binary form. The :'contents' syntax instructs psql to inject the variable in text form into the query.


Oracle Password File Version When trying to perform certain user security actions, I get the following error:

ORA-46953: The password file is not in the 12.2 format.

How do I migrate a 12.1 password file to 12.2? Or, is this even necessary?

You can migrate the password file with the input_file option of orapwd.

Example:

[oracle@o71 ~]$ orapwd file=test format=12

Enter password for SYS:
[oracle@o71 ~]$ orapwd describe file=test
Password file Description : format=12
[oracle@o71 ~]$ orapwd file=test2 input_file=test format=12.2
[oracle@o71 ~]$ orapwd describe file=test2
Password file Description : format=12.2


How to migrate Oracle database to MySQL server? Currently our application is running using Oracle 11g database. We are developing newer version of the application which runs on MySQL cluster. We need to migrate the data from Oracle database to MySQL database. This is not a direct copy of database objects. In newer version of the application schema(table structure and relationships) is changed. And audit tables also needs to be copied. Is there any way to map tables, columns from old Oracle schema and new MySQL schema and apply copy so that the data will be copied?

We are thinking doing this activity in two steps.
1. Convert Oracle database to MySQL database.
2. Writing stored procedure to copy table by table.

Is there any best practices while performing this kind of database migration from one RDBMS vendor to another? Is there any open source or commercial tools available for this?

I recommend to do this migration in two steps.

Use MySQL Workbench to migrate most of the db objects and data to MySQL (table data will be copied by this process too). See Using the MySQL Workbench Migration Wizard for details. This will leave you with manual work for specific objects like stored procedures. No tool can do an automatic migration as stored routines differ vastly between Oracle and MySQL.
Use MySQL Workbench to reverse engineer the migrated db into a model.
Use this model to adjust objects to your new schema structure.
Synchronize the model to your server to apply the changes. Depending on what changes you made this might be non-destructive, but of course, having a backup is always a good idea.

You can repeat steps 3 and 4 as many times as you wish to incrementally adjust your schema. Synchronization will also take over any changes made in schema by others (two way sync).

Migration from Oracle to MySQL is not supported by MySQL Workbench. All my suggestions above are just that, ideas one can try out to get it at least partially working. Nothing of that is being tested, however, because, as I said, Oracle to MySQL migration is not supported.


Adding foreign key that references a column which is not Primary or Unique key I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column.

Problem is table B's relevant column isn't a foreign key.
And if I will do that I guess that the correct state where I can have the same values multiple time for different columns values will be impossible- and this is not the behavior I look for.

So what can be done?

So table2.columnB must contain a value from table1.columnA but there could be duplicate values in table1.columnA so you can't declare it unique? Apparently InnoDB does allow this, see https://stackoverflow.com/questions/2362163/mysql-foreign-key-by-non-unique-key-how-is-that-possible for discussion, but standard SQL (and therefore most databases) explicitly does not (it renders some operations like ON {DELETE|UPDATE} CASCADE potentially inconsistent) so I would avoid the option.

If there is a fixed set of valid values you could keep them in their own table with a unique/primary key defined upon the column, then both table2.columnB and  table1.columnA can be foreign keys referencing the new table.column.

A less clean option would be to write triggers to maintain the foreign key manually, as mentioned in http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/ amongst other places you should find easily via your preferred search engine. You need to be careful to consider all possibilities logically and efficiently, so I would recommend the "new entity type and foreign key" method instead - that will be less bug prone, probably more efficient, and might better model your data anyway (perhaps the properties represented by those columns should be entities in their own right).


Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012) I have a database that was a mirrored partner and went into suspended state. (Mirror Server was shut down).

I took the principal database out of mirroring using SSMS. But now I am unable to restore the mirror database so that I can set up the mirroring again.

When I run ALTER DATABASE X SET PARTNER OFF on the mirror, SQL Server logs

  Error 3456 (Could not redo log record)

When I run RESTORE on this database from a backup - SQL Server tells me I cannot because the database is involved in mirroring.

Is there any other way to drop this database safely? I cannot take it offline and cannot delete it for the same reasons. It seems I cannot remove mirroring on the Mirror, for some reason. Thank you

Edit:

I should probably give more background. The situation came about because I shut down the OS before stopping SQL Server & SQL Agent services. (Assuming that the OS would trigger a safe shut down of these services, like others, not wise).

This probably caused some sort of corruption which makes the DB un-editable, even to remove it from mirroring. Aside from shutting down SQL Server and moving the .mdf / .ldf files then restarting it - are there other options? Can I actually try editing the .ldf file to remove the offending record, or something like that?

First shutting down Windows without stopping SQL Server first didn't cause this.  That won't do anything bad to the database as all services are cleanly shutdown when Windows is told to turn off.

I assume that the principle is offline and can't be brought online?

Try forcing the database to come online.

ALTER DATABASE X SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

That should bring the database online which should then allow you to remove mirroring and drop the database (or do whatever else you need to do with it).


Data Modeling Problem (Tasks / Timerecords / Users) It's late here an I'm having troubles wrapping my head around it.

I have 3 tables: Tasks, Timerecords, Users

A task is assigned to a user and always has an "estimated duration".
Timerecords are created by users and always link to a task. They always have a "logged duration".

I now want to get a list of all users with their sum of estimated duration (over all tasks) and their sum of logged duration (over all tasks), but I can't really figure out how I can make this possible with these 3 tables and avoiding circular relationships. But it wouldn't be a problem if I'd have to copy a table (it's for a data analysis project).

This should be close enough.

-- User USR exists.
--
user {USR}
  PK {USR}

-- Task TSK exists.
--
task {TSK}
  PK {TSK}

-- Task TSK is assigned to user USR,
-- with time estimate of EST hours.
--
task_user {TSK, USR, EST}
       PK {TSK, USR}

FK1 {TSK} REFERENCES task {TSK}
FK2 {USR} REFERENCES user {USR}

-- On date DTE user USR spent HRW hours
-- working on task TSK.
--
time_record {TSK, USR, DTE, HRW}
         PK {TSK, USR, DTE}

FK {TSK, USR} REFERENCES task_user {TSK, USR}

-- PostgreSQL, SQL Server
--
WITH
q_1 AS (
    SELECT USR
         , sum(EST) AS total_est
    FROM task_user
    GROUP BY USR
),
q_2 AS (
    SELECT USR
         , sum(HRW) AS total_log
    FROM time_record
    GROUP BY USR
)
SELECT a.USR
     , coalesce(b.total_est, 0) AS total_estimate
     , coalesce(c.total_log, 0) AS total_logged
FROM      user AS a
LEFT JOIN q_1  AS b ON b.USR = a.USR
LEFT JOIN q_2  AS c ON c.USR = a.USR ;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key


Non-standard number data types in Oracle I'm not sure that this question belongs to this StackExchange.
I've asked this question on StackOverflow:

Besides the "usual" number data type where precision is greater then scale there are many "non-standard" number data types where scale greater then precision or where scale is negative.
For example
NUMBER(2, 5) means that there are 5 digits in the fractional part, 3
of them is obligatory zeros. NUMBER(2,-6) Here the scale is -6, which
means the value is rounded to millions and the precision is 2, so 2
significant digits can be stored. Can somebody provide examples of
using such data types in practice?

Some clarifications:
I am very interested in the motivation for using such data types (NUMBER(2, 5) and NUMBER(2,-6)).
What tasks can such a table design solve?

It prevent Aoplicatuions from inserting invalid data. P.For example for many currecncies it maks no senes to have more than 2 digits right from the decimal point.


SQL Server 2008 Timeout expired rebuild and reorganize index solve the issue I have a large database with a file stream located on a different disk the server that hosts this database has a large number of another none related databases but the specifications of this server are great, my database throws a timeout exception for some queries from time to time,

every time I rebuild and reorganize the indexes the queries back to work normally, I have created a maintenance plan that do Rebuild, Reorganize and update statistics every 3 hours to avoid such issues, the fill factor for the indexes is 90%,

What are the reasons for this? and how I can avoid this issue without the maintenance plan as it adds an extra overhead on the CPU and the RAM.

You have a performance issue at your hands. Investigate it appropriately, using a performance investigation methodology like Waits and Queues. USE is also a great performance troubleshooting framework.

Stop doing random changes in hope something improves. Measure, identify, then act appropriately. Establish baselines. Read the entire Monitor and Tune for Performance chapter on MSDN.


Which databases support parallel processing across multiple servers? I need a database engine that can utilize multiple servers for processing a single SQL query in parallel. So far I know that this is possible with the some engines, though none of them are feasible for me either because of pricing or missing features. The engines currently known to me are:

MS SQL (enterprise)
DB2 (enterprise)
Oracle (enterprise)
GridSQL
Greenplum

Which other engines have this feature? Do you have any experience with using this feature?
Edit: I have now proposed a method for creating one myself. Any input is welcome.
Edit2: I have found another one: Informix Extended Parallel Server
Edit3: Stado is a new one.
Edit4: Maybe also pgpool-II, they write:

Parallel Query
Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time.

Look into Hive:

http://wiki.apache.org/hadoop/Hive

It's free and scales very well.

A new presentation was posted recently with updated numbers:

http://www.slideshare.net/ragho/hive-icde-2010

also see

http://wiki.apache.org/hadoop/Hive/Presentations


Error when passing variable to function I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function:
CREATE OR REPLACE FUNCTION public.intercept_ddl()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
            SELECT current_query() into _qry;
            PERFORM pglogical.replicate_ddl_command('_qry', '{default}'), _qry;

 end if;
END;
$function$
;

When I tried it, it threw an error:

ERROR:  syntax error at or near "_qry"
LINE 1: SELECT pglogical.replicate_ddl_command('_qry', '{default}')
        ^
QUERY:  SELECT pglogical.replicate_ddl_command('_qry', '{default}')
CONTEXT:  during execution of queued SQL statement: _qry
PL/pgSQL function intercept_ddl() line 6 at PERFORM

How to pass _qry variable so we can use it replicate_ddl_command()?

After fixing single quotes as instructed by Laurenz, I tried again with the command:
alter table test alter COLUMN description type text;

And got another error message:

ERROR: cannot drop active portal "pglogical"
CONTEXT: during execution of queued SQL statement: alter table test alter COLUMN description type text; SQL statement "SELECT pglogical.replicate_ddl_command( _qry, '{default}')" PL/pgSQL function intercept_ddl() line 7 at PERFORM

In addition to fixing single quotes as instructed by Laurenz, you might have to use the (default!) replication set '{ddl_sql}' for the command replicate_ddl_command(). The error message

cannot drop active portal "pglogical"

indicates an open portal with a prepared statement that would conflict with the DDL command.
CREATE OR REPLACE FUNCTION public.intercept_ddl()
  RETURNS event_trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   PERFORM pglogical.replicate_ddl_command(current_query(), '{ddl_sql}');  -- !!
END
$func$;

You did not disclose the actual event trigger, but could look like this
CREATE EVENT TRIGGER foo
ON ddl_command_start
WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE', 'DROP TABLE')
EXECUTE FUNCTION public.intercept_ddl();

Filter command tags in the trigger itself, obviates the need to do so in the function. That's more efficient as the function is only called for applicable tags to begin with.
Quoting the manual of pglogical:

There are three preexisting replication sets named “default”,
“default_insert_only” and “ddl_sql”. [...] The “ddl_sql” replication
set is defined to replicate schema changes specified by
pglogical.replicate_ddl_command

And:

pglogical.replicate_ddl_command(command text, replication_sets text[]) Execute locally and then send the specified command to the
replication queue for execution on subscribers which are subscribed to
one of the specified replication_sets.Parameters:

command – DDL query to execute
replication_sets – array of replication sets which this command should be associated with, default “{ddl_sql}”


sqlpackage.exe fails on SignalR objects Issue:
When running sqlpackage.exe to apply a sql dacpac, it occasionally fails on broker/SignalR related objects, which breaks the build:
*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot drop the service 'Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver', because it does not exist or you do not have permission.
Error SQL72045: Script execution error.  The executed script:
DROP SERVICE [Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver];

This is our sqlpackage.exe cmd line parameters:
/p:TreatVerificationErrorsAsWarnings=True /p:DoNotAlterChangeDataCaptureObjects=True /p:DropIndexesNotInSource=True /p:DropObjectsNotInSource=True /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;LinkedServerLogins;LinkedServers /p:BlockOnPossibleDataLoss=False

Again, the failure rate is like 1 out of every 10 runs, and no one is manually removing these objects, thus I have no idea why it would not be able to drop the object if it knows about it.  Any ideas on a good solution to get it to apply consistently ?

Again, the failure rate is like 1 out of every 10 runs, and no one is
manually removing these objects

Although no one is manually dropping these objects, it seems SqlDependency is used to detect database changes. SqlDependency creates/drops ephemeral objects so, if the application is running during publish with the with the /p:DropObjectsNotInSource=True option, the object might longer exist when the deployment script is executed by sqlpackage.
One option is to stop the application while the dacpac is published. You could also exclude the problem objects during publish (e.g. using the DoNotDropObjectTypes option).


Difference in duration of SP:Completed and sum(SP:StmtCompleted) I have a stored procedure, which sometimes displays a big difference in duration for the overall execution time and the execution time for each of its statements. The procedure consist of 4 statements, each with a maximum run-time of 4ms. But some procedure has Sp:Completed duration of around 1000 ms or more.
The procedure is called very often (at least once every second, or more).
My first idea was about recompile time, but this procedure is rather simple and doesn't need to be recompiled. I run trace to find recompile events relating this procedure and can't catch any record in it.
I want to use MS SQL 2017 if it may help. Every duration which I mention here was obtained from the SQL Server Profiler.
What is the reason for the long procedure execution time?

It's just in how and what they measure. The statement complete measures only that statement. It doesn't measure any wait time while that statement didn't start, etc. The procedure complete measures all sorts of extraneous management, blocks, waits, etc. There's just not going to be a perfect correlation between the two.


How do I identify all the students who have passed? Suppose I have a Table student and a Table exam which tracks all the exams a student has taken, how do I find all the students who have passed or not taken any exams similar to a white-list? In this example that means students 1 and 2.

PS: pass flag U means that the status is unknown, active flag N means the exam was removed, rest of the flags are self explanatory.

Just to be very clear, the real use case is actually assessments, I need to figure out people who have no pending assessments to participate in, so that they can proceed with next set of procedures. Others would need to finish the pending items.

+-------------+------+
| student_idn | name |
+-------------+------+
| 1           | Mark |
| 2           | Jack |
| 3           | Jane |
| 4           | Jill |
+-------------+------+

+----------+-------------+------+--------+
| exam_idn | student_idn | pass | active |
+----------+-------------+------+--------+
| 1        | 1           | Y    | Y      |
| 2        | 1           | Y    | Y      |
| 3        | 1           | Y    | Y      |
| 4        | 2           | Y    | Y      |
| 5        | 2           | N    | N      |
| 6        | 3           | Y    | Y      |
| 7        | 3           | Y    | Y      |
| 8        | 3           | Y    | Y      |
| 9        | 3           | N    | Y      |
| 10       | 4           | U    | Y      |
+----------+-------------+------+--------+

I came up with this below query but is there a better way to do this? I feel that this is O(n) or O(nlogn) depending on index etc not sure (anyone knows the best possible for this) hence should be close to optimal?

SELECT
  *
FROM student
WHERE NOT EXISTS (SELECT
  *
FROM exam
WHERE exam.student_idn = student.student_idn
AND pass <> 'Y'
AND active = 'Y')

The problem you describe is known as relational division. There are basically two ways to approach it:

Transform for all x : p(x) to  not exists x :  not p(x)

In other words, transform students who have passed all test to students where it does not exist a test that the student did not pass. Your query in the question is an example of this.

This transformation is often referred to as De Morgan's laws for quantifiers. See for example:

https://philosophy.stackexchange.com/questions/28324/proving-de-morgans-laws-for-quantifiers

Students who have passed the same amount of tests as there are tests. Something like:

 SELECT student_id
 FROM tests
 WHERE passed = 'y'
 GROUP BY student_id
 HAVING COUNT(1) = (SELECT COUNT(DISTINCT test_id)
                    FROM tests)

I'm answering from a mobile so I can't see the question while answering, so the details of my answer is likely wrong according to your question, but hopefully you get the idea.


PostgreSQL Scheduled Database Backup For scheduling a daily back up on a postgres database we execute the following command:

0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \
          > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql \
          2>> $HOME/db_backups/cron.log

After the code executes, the log says "invalid password". Is there a way to specify a password or avoid the requirement when creating a dump?

Also we  would like to retrieve the dump and pop it on separate Windows server  using pgadmin3. When restoring using pgadmin dbase -> database restore the 'OK' button is disabled and we are unable to restore the database.

Backup without password

If you run the script as OS user postgres it should not request a password in a standard setup, because password-less peer access (or ident on older versions) is enabled in pg_hba.conf for the postgres user.

So make it a cronjob of postgres if you can. Or enable password-less access for the OS user running the job. You can do that in pg_hba.conf but, as @Richard already pointed out, the password file .pgpass file may be a more elegant solution.

More on how to connect without password in this related question.

Restore in pgAdmin

I quote the pgAdmin3 FAQ:

  pgAdmin III uses PostgreSQL's pg_restore tool, which supports only the
  COMRESS and TAR options of pg_dump which is used for backup creation.
  The PLAIN format can't be interpreted by pgAdmin III and pg_restore
  (it can be edited manually, and executed with psql and pgAdmin III's
  query tool in many cases), and thus isn't accepted as valid file.

  We recommend using the COMPRESS format for daily backup tasks. The
  PLAIN format is for advanced manual processing before executing as SQL
  script, and has some restrictions (no blobs) which makes it less
  usable for standard backup tasks.

To restore a plain SQL backup execute it with psql similar to this:

psql mydb -p 5432 -U myuser -f backup.sql

For a single database backup you have to connect to the right database. For a cluster-backup with pg_dumpall, you might as well connect to the maintenance db "postgres".

You could also load and run such a backup with the pgAdmin query tool. It's plain SQL.


Oracle 19c Installer Can't Select National Character Set I am trying to install a new 19c instance of Oracle database in order to migrate a 12c database there. The 12c database uses a database character set of WE8ISO8859P1 and a national character set of AL16UTF16, both configured on installation.

I have found during the 19c installation, however, that there is no option to set the national character set. How can I be sure that this is configured to the correct AL16UTF16?

Just install the DB software then use DBCA to create the database.
There you can choose the national characterset.

Typically noone uses the installer to create a database. Some others (including me) avoid even DBCA.


SQL Server storage limits on Azure Looking at SQL Server maximum capacity limits on here: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.120).aspx

Database size can be as big as 524,272 terabytes.

Here, you could see VM size limits on Azure: http://msdn.microsoft.com/en-us/library/windowsazure/dn197896.aspx

If you look at the table on the second page, the maximum number of data disks that could be attached is 16 (1 TB each). Does that mean on Azure, SQL Server cannot house more than 16 TBs and anything more than that would require a new VM?

Yes, all the storage that you can attach to a single Azure VM is 16TB so that's the practical limit on storage for a single server.

Now that said, there isn't a problem that can't be worked around.

If you needed say a 100TB database, you could spin up 7 VMs, attach 16TB of storage to each one.  Then spin up an 8th VM which has SQL Server on it, then create the database using SMB network shares on the 7 other machines storing the database on the 7 remote machines.


mongodb - what roles the user to grant access to all databases I have created mongouser as testuser with below roles. I want to give access for new databases for this user. what roles the user to grant access to all databases.

db.createUser({ user: "testuser" , pwd: "testuser", roles: ["dbAdminAnyDatabase", "readWriteAnyDatabase", "enableSharding"]});

I have created mongouser as testuser with below roles. I want to give
  access for new databases for this user. what roles the user to grant
  access to all databases.

  db.createUser({ user: "testuser" , pwd: "testuser", roles:
  ["dbAdminAnyDatabase", "readWriteAnyDatabase", "enableSharding"]});

As per MongoDB documentation here MongoDB grants access to data and commands through role-based authorization and provides built-in roles that provide the different levels of access commonly needed in a database system. You can additionally create User-defined roles.

A role grants privileges to perform sets of actions on defined  resources. A given role applies to the database on which it is defined and can grant access down to a collection level of granularity.

dbAdminAnyDatabase

Provides the same privileges as dbAdmin on all databases except local and config.
The role also provides the listDatabases action on the cluster as a whole.

Changed in version 3.4: Prior to 3.4,  dbAdminAnyDatabase includes local and config databases. To provide dbAdmin privileges on the local database, create a user in the admin database with dbAdmin role in the local database. See also  clusterManager and clusterMonitor role for access to the config and local databases.

For further your ref here, here and here


Looping through MySQL Query progressively gets slower I have a MySQL Query, which as part of a batch job, loops through about 4,000 records - running the same queries against each of the 4,000 records.

As it moves through the records, it gets progressively slower.

By the time it's at the 4,000th, it's taking up to 20 times longer than when it did for the first loop.

There's actually 10 queries which run in each loop (SELECT), but the overall trend is all getting slower as the batch job progresses.

Also interesting is comparing how long each of the 10 individual queries takes in the loop.

At the start of the batch it's uneven (e.g. one SELECT takes 1% of loop time, whilst another takes 35% of loop time for example). This is as expected, as some queries are bigger than others.

But as it progresses, these 10 queries all start to take a much more similar time, all close to 10% of loop time -- suggesting perhaps there is a delay building up and affecting statements before they execute?

On my dev environment, MySQL is running locally - Running MySQL v5.7.24 on Ubuntu 16 on AWS EC2.

On my live environment,  I use AWS RDS. Running Aurora MySQL v5.6.10 on Ubuntu 16 on AWS EC2. I'm currently using Aurora Serverless, so have limited config control, but can move to RDS MySQL instance if there's config I need to access which would help.

The issue occurs in both environments.

It feels like previously executed statements are hanging around in the memory and not full freeing up resources for the next loop. But I don't know where to start looking to rectify.

EDIT:

On my dev environment (with local MySQL server), the load gets pretty high during the process (close to 100%), with RAM and SWAP not really changing (and well within limits).

On my live environment (RDS MySQL), the load and RAM are consistent, with no real change during the process (and well within limits), but the RDS  hits around 60% CPU utilization

So I'm obviously peaking out the CPU - which then, in turn, results in a lag with flows on?

EDIT:

I just doubled my dev environment EC2 (t2.small to t2.medium), which doubles RAM and 1CPU -> 2CPU. It's made no difference, and I notice running htop that only one of the CPUs is maxing out -- the other is almost idle - and I've just read that the MySQL Process won't (can't) be shared across multiple cores... so that doesn't help :-)

Welcome any thoughts and advice,

Thanks

SOLVED:

Found one statement involving a table which was missing a crucial INDEX, and it was bogging down there.

Adding the INDEX and each loop is now pretty consistent - no progressive lag.

Re query "can't you do all the work in a single JOIN between the two tables, rather than 1+4000 queries" - there's a heap more going on in each loop (sending messages etc) so it's more than just a MySQL Statement.

Cheers


Does partitioning a MySQL table also increase index creation performance? This is something I was wondering since I'm planning to partition a aggregation table with 10 mil records where rebuilding indexes cause table locks for some time.

Because partitions are bluntly put just multiple tables that have their own index and our system only inserts new records into a single partition that is kept small (Only data from the current day is added).

So when rebuilding or adding indexes will the table lock occur on all partitions or only the partitions it is rebuilding at that moment?

Also because you have to add your partition key to the primary key are there other troubles that I might run into when partitioning a existing table?

Partitioning usually rather decreases ALTER TABLE speed. Index and data is stored in separate files for each partition (a separate .ibd file for INNODB if innodb_file_per_table=1), so that you might make alter in parallel. But partitioning add an extra processing step to choose the partition for storing data/index, thus the whole operation is made slower.

mysql> create table alter_test (id int primary key auto_increment, v   char(20));
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table alter_test (id int primary key auto_increment, v  char(20)) partition by key(id) partitions 10;
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.83 sec)

As for the shared lock for ALTER TABLE, all partitions are locked in the same time, so partitioning does not help.

Hints:

Upgrade to  MySQL 5.6, since it supports online alter table.
Use scripts such as oak-online-alter-table or pt-online-schema-change as a workaround for MySQL version < 5.6


Oracle: Can a user log in without a password? I'm writing a sqplus-like program.  I assume the connect string contains the password if it contains a slash, e.g. scott/tiger or scott/tiger@orcl.

if connect string does not contain "/":
    prompt for password entry

Is there ever a case where a user could have a connect string scott or scott@orcl and not need to be prompted for a password?

Possible solutions:

Use a Secure External Password Store (Oracle Wallet): http://docs.oracle.com/database/121/DBSEG/authentication.htm#CHDHGAIJ

You can use the stored credentials without specifying a username or password. Connection string: /@orcl.

REMOTE_OS_AUTHENT: http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams208.htm

Not recommended, insecure, mentioned just for completeness, do not use it.

Kerberos Authentication: http://docs.oracle.com/database/121/DBSEG/asokerb.htm

You can use your directory credentials for logging in without specifying your username or password. Connection string: /@orcl.

SSL Authentication: http://docs.oracle.com/database/121/DBSEG/asossl.htm

You own a certificate and you will be authenticated by that, without specifying a username or password. Connection string: /@orcl.

Operating system authentication based on group membership. Works only locally, and for privileged users. Connection string: / as sysdba, / as sysoper, / as sysbackup, / as sysdg, / as syskb.


how to decide whether table segregation is benificial or not I have an input data feed that is significant in size. Presently, I have one table with all entries input in it.

I want to find out how to decide whether splitting the table into smaller tables would be beneficial or just an overhead. I need some insight on how to do this analysis, and if I perform a test for small data how can I scale the results?

The thing with segregation is that query time will surely reduce but at the same time there will be an increase in data input time and memory allocation will increase as well so I don't know whether it's worth it or not.

Some of your premises are not necessarily true therefore could lead to false conclusion.

  The thing with segregation is that query time will surely reduce

Properly sized and configured servers with the right indexes can still serve queries from tables in the TB scale in the milliseconds range. If you do point queries (single row lookups with primary key or fully covering secondary keys) then you will find that the significant time is spent on parsing the query string and authenticate/authorise the user not the actual data fetching.

  memory allocation will increase as well

By having more tables you're certainly not going to increase the memory allocation for MySQL. Number of tables has no influence on memory usage. Discussing memory allocation is out of scope now but this may be useful to take a look: MySQL memory calculator.

  there will be an increase in data input time

The insert time could only decrease not increase by having more tables.

Insert (and update) time can be improved by having more smaller table because the B+Trees become smaller. Given which storage engine you use it also matters if you insert in primary key order or random order. In case of InnoDB if you insert in random order there is a performance penalty of constant B+Tree rebalancing. In that case smaller tables could help.

However it does involve more logic on the application side and I don't know how much overhead that would be for your developers. Table partitions could be a middle ground here.

Do you delete from these table frequently?

If you do purging whole tables (or partitions) is much easier and can be done without affecting your production queries and replication (if you have) while DELETE FROM ... WHERE ... has to lookup the rows, it involves some locking (level is dependent on storage engine dependent), etc.

In this case splitting is certainly beneficial.

Do you want to archive the data?

MyISAM tables can just be simply copied over to another mysql server and it will just work. This is a very common and easy way to archive old data to a different server.

Splitting to tables by date work quite well in this case too.

Do you change your table structure frequently?

ALTERing many smaller table is more manageable than one huge.

These are the most common situations for splitting I can think of right now.

The question is do you experience any issues right now? Is there an explicit need to fix something? Can it be done by other ways of optimization? Configuration, indexes, query tuning, etc?

Testing

You can test your query time by replaying queries from tcpdump or slowlog.

You can also enable binary logs and replay insert/updates from ther eand see how fast they finish.

This is going to be a balance between many things including operability of the system so find what's work best for your use case.

I hope this helped.


How to convert an XML deadlock file into a graph or something readable? I'm looking for a easy or manageable way to read an XML file, specifically the XML output for SQL Server deadlocks.  I have some servers that are not being monitored for various reasons.

I found the following code on the interwebz, but its output is nothing but XML.

CREATE TABLE #errorlog (
                        LogDate DATETIME
                        , ProcessInfo VARCHAR(100)
                        , [Text] VARCHAR(MAX)
                        );

DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);

INSERT INTO #errorlog EXEC sp_readerrorlog;

SELECT @tag = text
FROM #errorlog
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime')
    AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

Is there a way to take the raw XML output from this T-SQL code and display it as a deadlock graph or more easily readable text?

You can bcp out and save it as .xdl. Many ways you can do it .. e.g. BCP OUT (I am using xp_cmdshell but you can use powershell or any other method).

Borrowing code from my answer (You need to adjust the code as per your requirement)  :

  -- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!

exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!


Unique key based on many-to-many pivot table I have to manage artists and albums tables:

| artists      |  | albums       |  | album_artist |
+--------------+  +--------------+  +--------------+
| id           |  | id           |  | id           |
| artist       |  | album        |  | album_id     |
| created_at   |  | created_at   |  | artist_id    |
| updated_at   |  | updated_at   |  +--------------+
+--------------+  +--------------+

Keeping in mind that this is a many-to-many relation, I do need to find a way to get the album-artist(s) pair unique, since albums may have the same name yet belonging to different artists (f.e. "Greatest Hits" album of 2Pac and "Greatest Hits" of Notorious BIG).

Is there a known way/pattern to address this problem?

The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key.

There are two variations of this approach. First, you can keep the current structure of the album_artist table and simply add a unique constraint on the said two columns:

ALTER TABLE album_artist
ADD CONSTRAINT uq_album_artist
  UNIQUE (album_id, artist_id);

There are more options to adding a UNIQUE constraint, which can be found in the linked manual.

The second variation is to get rid of the id column and declare (album_id, artist_id) to be the primary key of the table, as Rick James has suggested in a comment:

ALTER TABLE album_artist
DROP PRIMARY KEY;  /* assuming id has actually been declared
                      as the PK; if not, omit this step */

ALTER TABLE album_artist
DROP id;

ALTER TABLE album_artist
ADD CONSTRAINT pk_album_artist
  PRIMARY KEY (album_id, artist_id);

A junction table like your album_artist table, which stores no additional information beyond the table references, usually does not need to be referenced by other tables – at least, not often enough to justify the need for a dedicated ID column. The second variation, therefore, may be more appropriate. Rick's other useful thoughts on implementation of a junction table can be found in his blog.

If another table needs to reference an album and an artist as a valid combination (i.e. one that exists in the junction table), it can simply use a composite foreign key to reference album_artist:

FOREIGN KEY (album_id, artist_id) REFERENCES album_artist (album_id, artist_id)

It is only when you need to reference the junction table often (that is, from many tables) that I would argue the first approach, which allows you to keep a dedicated PK column in the junction table, would be more useful.


How to access the data of collections from the .ns and other core storage files in mongodb? My database in MongoDB was not accessible after the journal file became of the size > 1 GB . So I restarted the mongod process with a different data path to store the mongodb files in my machine.
Now I want to recover my old data from the old path but I am unable to do so. Can anyone help?

The DB is on a 64 bit Ubuntu box.

Check this guide: http://docs.mongodb.org/manual/tutorial/backup-with-mongodump/
Create Backups Without a Running mongod Instance

If your MongoDB instance is not running, you can use the --dbpath option to specify the location to your MongoDB instance’s database files. mongodump reads from the data files directly with this operation. This locks the data directory to prevent conflicting writes. The mongod process must not be running or attached to these data files when you run mongodump in this configuration.

mongodump --dbpath /old_data -o dataout

Then restore to a new data-directory and access the databases.


Facebook generates a Post record when I rename a group's name or just an ActivityItem? My question is more about relational database scheme.

I have in mind two tables for it:

Posts
Activities
Where when I post a a status message it probably creates a record for a Post and an Activity representing this (for build the feed and represent notifications).

Well, in case of renaming the name of a facebook's group I started to have this question.

Is created ("simulated") a Post record for a name changes or just and activity record?

While I can't comment how Facebook works internally, in terms of relational design, a normalized schema would have these two actions in separate tables.

This would be done because the other bits of information associated with each action would be very different.

Changing a group's name would probably involve inserting a history record with the old name, and updating the current record with the new name. Notifications could have other things associated, such as comments and likes.


mysqlbackup mysql enterprise utility issue I recent took a backup using mysqlbackup.

While restoring it, I noticed that the files that were copied into datadir are with root:root (user:group) instead of mysql:mysql.

Is anything wrong with taking backup or what?....

Since it is a backup, you are right to be concerned with whether it can be restored or not.

To be on the safe side, you must run

chown -R mysql:mysql /var/lib/mysql

immediately after the backup so mysql doesn't barf when restoring it.

Otherwise, you may get an OS error concerning file permission during the restoration or mysql startup

[root@******* 20130101_000]# perror 13
OS error code  13:  Permission denied


How do you encrypt data while performing a mysqldump? My OS is windows 7.

How can I encrypt data while performing a mysqldump?
From the command prompt, can I take a mysqldump with encrypted varchar and texts values?

Please help me.

Thanks

You can use ccrypt. Example:

Creating the key file:

echo 'mySecretKey123' > ~/.backup.key
chmod 600 ~/.backup.key

Encrypting the backup:

mysqldump databasename | ccrypt -k ~/.backup.key | bzip -c > ~/backup-mysql.sql.bz2.cpt

To decrypt the backup:

cat ~/backup-mysql.sql.bz2.cpt | ccat -k ~/.backup.key | bunzip2 -c | less


Unable to connect to SQL Server from one machine; "an existing connection was forcably closed by the remote host" Connecting to the SQL Server (2008 R2 SP3 - KB 4057113) with SSMS version 18 yields:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

Error Number: 10054
Severity: 20
State: 0

Connection failure logging is enabled on the server but nothing gets logged. Please note the for help link is dead. I've tried various database applications. Nothing gets through. The problem appears to be on the server, not the workstation, but other workstations can connect to the server. This workstation can also connect to other servers.

I've got a good guess this is somehow TLS related, because that's what it was last time (server didn't support TLS 1.2 and needed a patch). This time I can't find the problem though.

Found the problem. SQL Server 2008 R2 really wants to use a cert signed with SHA1. If SHA1 certs are disabled, you can't connect.


SQL Server Service does not start I'm using Windows 8.1. I have successfully installed SQL Server Management Studio, but services can't be started in "Sql configuration Manager". I search on google about these errors,and try all possible solutions but I am not able to resolve my error. Here is my error when I open services.msc and try to start services then this error generate

I can't start my services my sql configuration manager.Here is error image when i start services from configuration manager

When I check parameters path then I can't find MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf in C:\program File\Microsoft SQL Server folder. Microsoft SQLfolder only contains two folders: 90 and 100.

you need to find the corresponding error in event viewer in windows log\application find the error, one that the source is MSSQLServer and has happened, at the time you started the service.
detail of this error will help you resolve the issue.
(in the image below there is no error in it, it's just an information message, but in your case you may notice an error)

you may also want to check in system logs in event viewer, in the service Control manager to find a related error.


Localhost exception in MongoDB I have a MongoDB database installed and configured, but I have a very big problem regarding security.

I have enabled authentication and created a bunch of users, each with its own permissions, everything ok there, now my problem is that if a bad guy gets access to my server, he can simply restart the service without specifying the authentication parameter, and access all my databases just by logging in with the localhost.

In the documentation it says:

  The localhost exception applies only when there are no users created
  in the MongoDB instance.

Well, I have already created users, and I am still able to access my database using the localhost exception. Am I doing anything wrong? Or is the documentation wrong? If so, this seems to be a major security problem.

The Mongo version I am using is 3.4.

These are the steps I followed to enable authentication:

Joined Mongo without the --auth parameter
Created an Admin User

 use admin

 var user = {
   "user" : "Admin",
   "pwd" : "1234",
   roles : [
     {
       "role" : "root",
       "db" : "admin"
     }
   ]}

 db.createUser(user);

Restarted Mongo with the --auth parameter
Created a database and some collections

use TestDB
db.createCollection( "TestCollection1" );
db.createCollection( "TestCollection2" );

Created a custom role:

use TestDB

db.runCommand({ createRole:"TestUserRole",
  privileges : [
     { "resource": {"db" : "TestDB", "collection" : "TestCollection1"}, "actions": ["insert", "update"] },
     { "resource": {"db" : "TestDB", "collection" : ""}, "actions": ["insert"] }
   ],
   "roles" : [ { role: "read", db: "TestDB" } ]
 })

Created a regular user

use TestDB

var user = {
  "user" : "TestUser",
  "pwd" : "1234",
   roles : [
      {
          "role" : "TestUserRole",
          "db" : "TestDB"
      }
]}

db.createUser(user);

After this, I can access Mongo with both users, with the Admin account I can find, insert, update and delete in both collections of the database TestDB. With the user TestUser, I can only see the database TestDB (can't see the System collections) and can only find, insert and update TestCollection1, and can only find and insert in TestCollection2.

This is the behaviour I want, all working fine. Now, if I restart Mongo without the --auth parameter, I shouldn't be able to connect to Mongo using the localhost exception because users are already created. Yet I am able to connect.

Authentication only controls access to clients connecting to a MongoDB deployment. Securing remote access to your server or encrypting your data files are separate security measures.

If an attacker is able to gain access to a shell on your server and run privileged commands (like restarting the mongod service), access control is irrelevant. Similarly, even if your data files were encrypted an attacker with privileged access will be a major security problem.

As the administrator of a server, it is your responsibility to have appropriate access control, firewalls, auditing, and other security measures in place. Security involves multiple layers of defense, and access control is only a starting point. At a minimum you should also enable network encryption (TLS/SSL) and appropriately limit network exposure.

For a list of recommended security measures, see the MongoDB Security Checklist.

  Now, if I restart Mongo without the --auth parameter, I shouldn't be able to connect to Mongo using the localhost exception.

If you restart MongoDB without the --auth parameter (or equivalent configuration file directive), you are explicitly disabling access control and allowing clients to connect without requiring authentication. This may be useful for administrative intervention, such as resetting a forgotten admin password.


Partial Data Export - MS SQL In SQL Server Management Studio 2014, How to apply a WHERE condition during Generating Script (Data only) for a particular Table?

I'm using Generate Scripts method as I dont have Remote Server access to download Database Backup. However, as the table rows are around 15k and each row is heavy, the script is not generating for the particular table.

You can't directly filter SSMS's commands, but here's an easy workaround:

Create another table with the subset of data you want to export:

USE TemporaryStagingDatabase;
GO
SELECT *
  INTO dbo.MyTemporaryDestinationTable
  FROM SourceDatabase.dbo.MySourceTable
  WHERE (my filtering conditions);

Ideally, you do this in a different database so you don't bloat the data & log files with your temporary export work.

Then, use SSMS's magical wizards to export the data from your staging table. (If you have to do this a lot, though, check out SSIS.)


Are databases only growing in size? Databases use the file system to store the data.
As far as I know it is not possible to delete a record from a random access file.
So does that mean that when we do a DELETE FROM TABLE the size of the table i.e. the file that stores the table never decreases?
So databases essentially keep growing and never reduce in size?

It is certainly possible to delete data from a random access file.

Realistically, though, virtually any database will do a soft delete and mark data as deleted rather than physically deleting data.  At some point, something else will then reuse the space that the deleted row had been using (what operations reuse the space will depend on the database among other things).  And different databases may provide tools to automatically or manually reclaim that space.

Practically, this is basically the same thing that the operating system does when you delete a file.  The operating system removes the file system's pointer to the file just like the database removes the reference to the row from the index.  The actual data for the file still exists on the drive.  At some point, some other file system operation will eventually reuse that space.  Some operating systems provide a "defrag" utility that will go through and make all the free space contiguous and move the allocated data to the "beginning" of the drive.


Mysql: Unable to drop a columns NULL default Running mariadb 10.3

I have a bunch of columns that were created by default as NULL defaults. I was asked by a third party integrator to remove the NULL defaults, but so far have been unsuccessful.

I first do:

mysql> Describe Tablename;

I see my columns with Null defaults. e.g:

+--------------------------+---------------------+------+-----+---------+-------------------+
| Field                    | Type                | Null | Key | Default | Extra             |
+--------------------------+---------------------+------+-----+---------+-------------------+
| Tech ID                  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment    |
| State                    | tinyint(1) unsigned | NO   |     | 1       |                   |
| Ref ProdID               | int(10) unsigned    | NO   | MUL | NULL    |                   |
| Prenom                   | varchar(50)         | YES  |     | NULL    |                   |

Then I do:

mysql>ALTER TABLE Tablename ALTER COLUMN Prenom DROP DEFAULT;
Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

Describe again shows that the Null default has not been removed from column Prenom.

+--------------------------+---------------------+------+-----+---------+-------------------+
| Tech ID                  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment    |
| State                    | tinyint(1) unsigned | NO   |     | 1       |                   |
| Ref ProdID               | int(10) unsigned    | NO   | MUL | NULL    |                   |
| Prenom                   | varchar(50)         | YES  |     | NULL    |                   |

Is it even possible to do this?

I suspect your integrator was asking to remove the NULL from the column.

First check there are no NULL columns:

SELECT * FROM TableName WHERE Prenom IS NULL

Or to set to empty string:

UPDATE TableName SET Prenom = "" WHERE Prenom IS NULL

Then NULL for the column can removed with:

select version();

| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |

create table TableName ( Prenom  varchar(50) )

✓

show create table TableName

Table     | Create Table
:-------- | :----------------------------------------------------------------------------------------------------------
TableName | CREATE TABLE `TableName` (`Prenom` varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

ALTER TABLE TableName MODIFY COLUMN Prenom varchar(50) NOT NULL

✓

show create table TableName

Table     | Create Table
:-------- | :------------------------------------------------------------------------------------------------------
TableName | CREATE TABLE `TableName` (`Prenom` varchar(50) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1

db<>fiddle here


Ola Hallengren backup failed I have been using on my SQL Server 2008R2 server Ola Hallengren solutions for almost two years.  Last night backups failed, both System databases and user databases with error below.

Example of system databases error:

  Date and time: 2016-05-31 20:00:01 Command: DECLARE @ReturnCode int
  EXECUTE @ReturnCode = [master].dbo.xp_create_subdir
  N'E:\SQLBackups\Server1\master\FULL' IF @ReturnCode <> 0
  RAISERROR('Error creating directory.', 16, 1) HResult 0x5620, Level
  16, State 1 xp_create_subdir() returned error 183, 'Cannot create a
  file when that file already exists.' Msg 50000, Level 16, State 1,
  Server Server1, Line 1 Error creating directory. Outcome: Failed
  Duration: 00:00:00 Date and time: 2016-05-31 20:00:01

User Databases error Example:

  Date and time: 2016-05-31 20:00:12 Database: [Dummy_1] Status: ONLINE
  Standby: No Updateability: READ_WRITE User access: MULTI_USER Is
  accessible: Yes Recovery model: SIMPLE Differential base LSN:
  423000000014100036 Last log backup LSN: 342000000037500001   Date and
  time: 2016-05-31 20:00:12 Command: DECLARE @ReturnCode int EXECUTE
  @ReturnCode = [master].dbo.xp_create_subdir
  N'E:\SQLBackups\Server1\Dummy_1\FULL' IF @ReturnCode <> 0
  RAISERROR('Error creating directory.', 16, 1) HResult 0x5620, Level
  16, State 1 xp_create_subdir() returned error 183, 'Cannot create a
  file when that file already exists.' Msg 50000, Level 16, State 1,
  Server Server1, Line 1 Error creating directory. Outcome: Failed
  Duration: 00:00:00 Date and time: 2016-05-31 20:00:12

It failed when I run the job on right click too

Does anyone know how to solve the problem?

You need to ensure the SQL Server Service Account has the required Access Control for the paths used for storing your backups.

Check the SQL Server Accounts and Permissions page for details about what ACLs are required.  See Configure File System Permissions for database engine access for details about how to change permissions.


What is the best design for storing courses subscription data? Building an app that will manage lessons, students, courses. Each lesson belongs to a certain course. A student can subscribe to a single lesson or to a whole course.
So it seems straightforward that I need these tables(only essential columns):

Students
--------
Id[PK]

Lessons
-------
Id[PK]
CourseId[FK, NOT NULL]

Courses
-----------
Id[PK]

But how should I store subscriptions? Should I store them in one table that has two columns LessonId and CourseId only one of which will be populated for each subscription, or should I create two similar tables managing lessons and courses individually?

Subscriptions
-------------
Id[PK]
StudentId[FK, NOT NULL]
LessonId[FK, NULL]
CourseId[FK, NULL]

vs

CourseSubscriptions
-------------------
Id[PK]
StudentId[FK, NOT NULL]
CourseId[FK, NOT NULL]

LessonSubscriptions
-------------------
Id[PK]
StudentId[FK, NOT NULL]
LessonId[FK, NOT NULL]

Or may be you can advise another more suitable approach?

Additional info

Table(s) of subscriptions can be thought of as an intent of some person(student) to participate in a specific lesson(since he has an interest in subject) or in whole bunch of lessons that are grouped under the hood of a course.
A student can subscribe to as many lessons and courses as he likes, I think that each subscription should have a record of its own, so in case of a shared table there wont be any data overlapping.

Both are OK designs, so now the problem is deciding between the two.  Here are some things that I think you should consider as you decide which one is best.

How similar are the non-essential columns in the Course Subscription and Lesson Subscription tables?  If they are very similar then records which contain only a course subscription in the Subscriptions table would not have a bunch of null columns to skip which apply only to lesson subscriptions and vice versa.  The more similar the columns are, the more you should lean towards using the Subscriptions design.
Can a student subscribe to both a course and a lesson at the same time? If the student does that, will the data for any shared columns between a course and lesson be different?  If the data is different, then you'll definitely need the CourseSubscriptions and LessonSubscriptions tables.  Otherwise, you should be fine with just a Subscriptions table.

Finally, if you go with one table... Think through how your output would handle a combined course and lesson subscription.  Is that two subscriptions or one?  Conversely, if you go with two tables and one action results in two entries, is that one or two?


Replicating a postgres database for testing I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better way to replicate live server database to dev server, It need not to be real time but at least daily.

Unless the DB is huge, your best bet will be to clone it periodically.

Here's what I'd do if you're on PostgreSQL 9.3, which supports cascading replication:

On the live server, add a streaming replica that keeps an up to date copy on the dev server. You don't use this DB directly, it's just there so you can copy the DB without disrupting the master.
Use pg_basebackup -X stream to take a copy of the replica when you want to refresh the dev db. You can do this while still using the dev db so long as you send the base backup to a separate location.
Stop the dev db, delete its data directory, move the data directory you just copied with pg_basebackup to replace the old dev db, and start the dev db again. This ensures minimal downtime for the dev db.

The downside of this approach is that your dev server needs space for three copies of the master db - the streaming replica, the base backup you're copying, and the current dev db.

You can reduce that to two copies by deleting the dev db before doing the base backup, in exchange for a longer dev outage. Or by stopping dev and the replica, rsync'ing them, then starting them back up, instead of doing a basebackup and directory swap.

You can reduce that to one copy by doing away with the streaming replica and doing the base backup direct from live after deleting the dev db. Or you could stop the dev db, pg_start_backup() the master, rsync master to dev, and pg_stop_backup() on master. However, these approaches impose greater disk I/O on live while the base backup runs.

There are many possible variants; for example, you can stop the streaming replica and copy it at the file system level rather than using pg_basebackup.

One alternative I'd be tempted to use if my dev db was quite big would be to make a file-system or drive-level snapshot of the streaming replica while it's running, then mount that snapshot copy-on-write on a different directory and start it as the dev DB. That way you only have to actually copy data that changes. This approach is supported by LVM and by AWS EC2 EBS at the volume level, or by zfs and btrfs at the file system level.

Do not attempt to "sync" the master to the replica in real time. Not only will it impose an unreasonable load on the master and possibly cause issues on the master,


Oracle: Decreasing PCTFREE on large table Question about Oracle Database Enterprise Edition version 19.10.0.0.0 running as a standalone instance.
I have a table with billions of rows. The table has always had PCTFREE set to 10.
It turns out that we only ever perform inserts on this table, no updates, no deletions. I would like to save disk space by lowering PCTFREE to 0.
As I understand it, if I just do ALTER TABLE x PCTFREE 0 then in our case, only future allocated blocks are affected. But all the millions of existing blocks will never be filled above 90% because we never update or delete those rows.
Is there a way to get all the old blocks "re-evaluated" and put back on the freelist after I lower PCTFREE?
I would prefer not to move all of the data (e.g. use CTAS, or expdp+impdp, or delete+insert every row, or dbms_redefinition) because the table is so big that this would take forever and/or use enormous amounts of intermediate space. I just need the database to know that it's now okay to put more rows in the old blocks. Is this possible?

My question was answered on another message board. The answer is to use DBMS_REPAIR.SEGMENT_FIX_STATUS after the change in PCTFREE.
On my platform (sparc64) it's quite buggy, though, with lots of ORA-600 when repairing individual blocks. But repairing all blocks seems to work better for some reason.


Best design to reference multiple tables from single column? Proposed schema

First and foremost, here is an example of my proposed schema to reference throughout my post:

Clothes
----------
ClothesID (PK) INT NOT NULL
Name VARCHAR(50) NOT NULL
Color VARCHAR(50) NOT NULL
Price DECIMAL(5,2) NOT NULL
BrandID INT NOT NULL
...

Brand_1
--------
ClothesID (FK/PK) int NOT NULL
ViewingUrl VARCHAR(50) NOT NULL
SomeOtherBrand1SpecificAttr VARCHAR(50) NOT NULL

Brand_2
--------
ClothesID (FK/PK) int NOT NULL
PhotoUrl VARCHAR(50) NOT NULL
SomeOtherBrand2SpecificAttr VARCHAR(50) NOT NULL

Brand_X
--------
ClothesID (FK/PK) int NOT NULL
SomeOtherBrandXSpecificAttr VARCHAR(50) NOT NULL

Problem statement

I have a clothes table which has columns like name, color, price, brandid and so on to describe the attributes for a particular item of clothing.

  Here's my problem: different brands of clothing require differing
  information. What is the best practice for dealing with a problem like
  this?

Note that for my purposes, it is necessary to find brand-specific information starting FROM a clothes entry. This is because I first display the information from a clothes entry to the user, after which I must use its brand-specific information to purchase the item. In summary, there has to be a directional relationship between clothes (from) and the brand_x tables.

Proposed/current solution

To cope with this, I have thought of the following design scheme:

The clothes table will have a brand column which may have id values ranging from 1 to x, where a particular id corresponds to a brand-specific table. For example, id value 1 will correspond to table brand_1 (which might have a url column), id 2 will correspond to brand_2 (which might have a supplier column), etc.

Thus to associate a particular clothes entry with its brand-specific information, I imagine the logic at the application-level will look something like this:

clothesId = <some value>
brand = query("SELECT brand FROM clothes WHERE id = clothesId")

if (brand == 1) {
    // get brand_1 attributes for given clothesId
} else if (brand == 2) {
    // get brand_2 attributes for given clothesId
} ... etc.

Other comments & thoughts

I'm attempting to normalize my entire database in BCNF, and although this is what I came up with, the resulting application code makes me feel very anxious. There is no way to enforce relations except at the application level, and thus the design feels very hacky and, I anticipate, very error-prone.

Research

I made sure to look through previous entries before making a post. Here's a post with a near-identical problem that I managed to find. I made this post anyway because it seems like the only answer provided does not have a SQL or design-based solution (i.e. it mentions OOP, inheritance, and interfaces).

I'm also kind of a novice when it comes to database design, and so I'd appreciate any insights.

It appears there are more helpful responses on Stack Overflow:

Here
And here
Aaaand here (key concept being: class table inheritance)

I have referred to the solutions there and suggest others finding my question do so as well.

Despite the above-provided links, I am still on the lookout for responses here and would appreciate any solutions provided!

I am using PostgreSQL.

I personally don't like to use a multi-table schema for this purpose.

It's hard to ensure integrity.
It's hard to maintain.
It's difficult to filter results.

I've set a dbfiddle sample.

My proposed table schema:

CREATE TABLE #Brands
(
BrandId int NOT NULL PRIMARY KEY,
BrandName nvarchar(100) NOT NULL
);

CREATE TABLE #Clothes
(
ClothesId int NOT NULL PRIMARY KEY,
ClothesName nvarchar(100) NOT NULL
);

-- Lookup table for known attributes
--
CREATE TABLE #Attributes
(
AttrId int NOT NULL PRIMARY KEY,
AttrName nvarchar(100) NOT NULL
);

-- holds common propeties, url, price, etc.
--
CREATE TABLE #BrandsClothes
(
BrandId int NOT NULL REFERENCES #Brands(BrandId),
ClothesId int NOT NULL REFERENCES #Clothes(ClothesId),
VievingUrl nvarchar(300) NOT NULL,
Price money NOT NULL,
PRIMARY KEY CLUSTERED (BrandId, ClothesId),
INDEX IX_BrandsClothes NONCLUSTERED (ClothesId, BrandId)
);

-- holds specific and unlimited attributes
--
CREATE TABLE #BCAttributes
(
BrandId int NOT NULL REFERENCES #Brands(BrandId),
ClothesId int NOT NULL REFERENCES #Clothes(ClothesId),
AttrId int NOT NULL REFERENCES #Attributes(AttrId),
AttrValue nvarchar(300) NOT NULL,
PRIMARY KEY CLUSTERED (BrandId, ClothesId, AttrId),
INDEX IX_BCAttributes NONCLUSTERED (ClothesId, BrandId, AttrId)
);

Let me insert some data:

INSERT INTO #Brands VALUES
(1, 'Brand1'), (2, 'Brand2');

INSERT INTO #Clothes VALUES
(1, 'Pants'), (2, 'T-Shirt');

INSERT INTO #Attributes VALUES
(1, 'Color'), (2, 'Size'), (3, 'Shape'), (4, 'Provider'), (0, 'Custom');

INSERT INTO #BrandsClothes VALUES
(1, 1, 'http://mysite.com?B=1&C=1', 123.99),
(1, 2, 'http://mysite.com?B=1&C=2', 110.99),
(2, 1, 'http://mysite.com?B=2&C=1', 75.99),
(2, 2, 'http://mysite.com?B=2&C=2', 85.99);

INSERT INTO #BCAttributes VALUES
(1, 1, 1, 'Blue, Red, White'),
(1, 1, 2, '32, 33, 34'),
(1, 2, 1, 'Pearl, Black widow'),
(1, 2, 2, 'M, L, XL'),
(2, 1, 4, 'Levis, G-Star, Armani'),
(2, 1, 3, 'Slim fit, Regular fit, Custom fit'),
(2, 2, 4, 'G-Star, Armani'),
(2, 2, 3, 'Slim fit, Regular fit'),
(2, 2, 0, '15% Discount');

If you need to fetch common attributes:

SELECT     b.BrandName, c.ClothesName, bc.VievingUrl, bc.Price
FROM       #BrandsClothes bc
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
ORDER BY   bc.BrandId, bc.ClothesId;

BrandName   ClothesName   VievingUrl                  Price
---------   -----------   -------------------------   ------
Brand1      Pants         http://mysite.com?B=1&C=1   123.99
Brand1      T-Shirt       http://mysite.com?B=1&C=2   110.99
Brand2      Pants         http://mysite.com?B=2&C=1    75.99
Brand2      T-Shirt       http://mysite.com?B=2&C=2    85.99

Or you can easily get Clothes by Brand:

  Give me all clothes of Brand2

SELECT     c.ClothesName, b.BrandName, a.AttrName, bca.AttrValue
FROM       #BCAttributes bca
INNER JOIN #BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN #Attributes a
ON         a.AttrId = bca.AttrId
WHERE      bca.ClothesId = 2
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

ClothesName   BrandName   AttrName   AttrValue
-----------   ---------   --------   ---------------------
T-Shirt       Brand1      Color      Pearl, Black widow
T-Shirt       Brand1      Size       M, L, XL
T-Shirt       Brand2      Custom     15% Discount
T-Shirt       Brand2      Shape      Slim fit, Regular fit
T-Shirt       Brand2      Provider   G-Star, Armani

But for me, one of the best of this schema is that you can filter by Attibutes:

  Give me all Clothes that has the attribute: Size

SELECT     c.ClothesName, b.BrandName, a.AttrName, bca.AttrValue
FROM       #BCAttributes bca
INNER JOIN #BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN #Attributes a
ON         a.AttrId = bca.AttrId
WHERE      bca.AttrId = 2
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

ClothesName   BrandName   AttrName   AttrValue
-----------   ---------   --------   ----------
Pants         Brand1      Size       32, 33, 34
T-Shirt       Brand1      Size       M, L, XL

Using a multi-table schema whatever of the previous queries will require to deal with an unlimited number of tables, or with XML or JSON fields.

Another option with this schema, is that you can define templates, for example, you could add a new table BrandAttrTemplates. Every time you add a new record you can use a trigger or a SP to generate a set of a predefined attributes for this Branch.

I'm sorry, I'd like to extend my explanations by I think it is more clear than my English.

Update

My current answer should works on no matter which RDBMS. According to your comments, if you need to filter attributes values I'd suggest small changes.

As far as MS-Sql doesn't allow arrays, I've set up a new sample mantaining same table schema, but changing AttrValue to an ARRAY field type.

In fact, using POSTGRES, you can take advantatge of this array using a GIN index.

(Let me say that @EvanCarrol has a good knowledge about Postgres, certainly better than me. But let me add my bit.)

CREATE TABLE BCAttributes
(
BrandId int NOT NULL REFERENCES Brands(BrandId),
ClothesId int NOT NULL REFERENCES Clothes(ClothesId),
AttrId int NOT NULL REFERENCES Attrib(AttrId),
AttrValue text[],
PRIMARY KEY (BrandId, ClothesId, AttrId)
);

CREATE INDEX ix_attributes on BCAttributes(ClothesId, BrandId, AttrId);
CREATE INDEX ix_gin_attributes on BCAttributes using GIN (AttrValue);

INSERT INTO BCAttributes VALUES
(1, 1, 1, '{Blue, Red, White}'),
(1, 1, 2, '{32, 33, 34}'),
(1, 2, 1, '{Pearl, Black widow}'),
(1, 2, 2, '{M, L, XL}'),
(2, 1, 4, '{Levis, G-Star, Armani}'),
(2, 1, 3, '{Slim fit, Regular fit, Custom fit}'),
(2, 2, 4, '{G-Star, Armani}'),
(2, 2, 3, '{Slim fit, Regular fit}'),
(2, 2, 0, '{15% Discount}');

Now, you can additionally query using individual attributes values like:

  Give me a list of all pants Size:33

AttribId = 2 AND ARRAY['33'] && bca.AttrValue

SELECT     c.ClothesName, b.BrandName, a.AttrName, array_to_string(bca.AttrValue, ', ')
FROM       BCAttributes bca
INNER JOIN BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN Attrib a
ON         a.AttrId = bca.AttrId
WHERE      bca.AttrId = 2
AND        ARRAY['33'] && bca.AttrValue
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

This is the result:

clothes name | brand name | attribute | values
------------- ------------ ----------  ----------------
Pants          Brand1       Size        32, 33, 34


SQL Server Cluster and Windows Server Storage Replication I am currently investigating on how to setup a SQL Server stretch cluster in combination with storage replication for which I want to use Windows Server Storage Replication. However, I am no sure on how to configure the storage replication exactly and maybe you can help. :-)
I do not want to use Always On Availability Groups. In the end I want to use DTC and that does not seem to be supported in combination with Always On or SQL Server mirroring. The purpose of my setup is to have SQL Server highly available on site A and have a DR copy on site B, which also needs to be highly available if that is made active in case of a fail over. I configured a stretch cluster and that works fine, but I am struggling on how to configure the storage replication in combination with the CSVs.
I cannot find any documentation on the complete setup. I can find docs (MS and community) on how to configure Storage Replication in combination with Scale Out File Server or Hyper-V, but that does not talk on how configure the CSVs exactly and how to fail over to the other site. Every document says regarding the storage something like 'You need manual intervention to fail over'. I have read some documentation from Dell and HPE on how they use their storage replication solution, but that does not give me those details. How and what exactly you need to do on the Windows cluster side is not described. Secondly, they use their own mechanism to make sure the storage blocks are synced in the correct order.
I have 2 scenario's I tried, but these fail. I use my home lab here, so all servers are VMs and therefore use VHDs themselves. So the physical disk in scenario A, for example, is a VHD of a file server VM. All servers use Windows Server 2019.
Scenario A:

Configure Storage Replication between a physical disk on iSCSI target
server A and B
Create 2 iSCSI disks on that physical disk and make
them CSV in the cluster

In this scenario the physical disk will replicate the blocks of the iSCSI disks to the other site. This works fine, but there are several issues if I understand correctly:

The iSCSI disks on Site B cannot be added to the cluster, because they are read-only (not even visible) due to the Storage Replication configured.
In order to fail over, I cannot stop the cluster, switch the replication direction, add the iSCSI disks on Site B as CSV to the cluster. It seems that I need to remove the LUNS first and delete the iSCSI CSV disks from Site A from the cluster? Then add them again at Site B? This seems very cumbersome to me, but maybe this is how this should be done in order to fail over? Or am I missing something here?

Scenario B:

Configure Storage Replication between CSVs

In this scenario I create some iSCSI disks and add them as CSV to the cluster. That works fine. Then I wanted to configure replication via the Failover Cluster Manager. First add them as CSV, then right click on one and select 'Replication'.
This scenario fails, because it tells me that the disks are not of the same size. However, they are exactly the same size (I use the same Powershell command to create them) and are formatted with the same NTFS Allocation Unit size. Maybe I am missing something here?

So basically my question is how I can get Windows Server Storage Replication to work correctly in combination with the cluster configuration I am using?

I finally managed to figure it out. I think information is missing, hard to find and no described in much detail. So for me I was a bit in the dark when figuring this out. Also, I think I had some tunnel vision at the start thinking that storage replica works in a certain way. MS has a different way of configuring it and the way it works/ you are supposed to use it.
Scenario A
Possibly an option, but is cumbersome and I don't think MS will support this. I also have not tested this. In order to fail over you probably need to:

Stop the cluster
Switch the replication direction
Add the iSCSI disks to the iSCSI target server at site B as an available iSCSI LUN
Add the LUNs to the SQL Servers again
Add the LUNs to the cluster as CSV, so they will be new as far as the cluster is concerned
Potentially detach and re-attach the databases, because the path could have changed if you have not renamed your volume folder names in C:\ClusterStorage\ when intially setting up the cluster

If someone could comment on this who has more experience on this, that would be great.
Scenario B:
This is the way this is intended to be used. In order to correctly setup the configuration you need to:

Create a multi-site cluster and make sure Windows Server Datacenter version is used for all nodes. Else it will not support replication.

Configure the storage. What is not in the mentioned documentation is that (per LUN you want to replicate) you need to have the data source disk configured as CSV and the other disks configured as Available Storage. Move all the available storage to the node that is the owner of the source disk on Site A (so that will include the available storage in the other site, Site B. This will bring some disks offline, no worries the Storage Replica Wizard in Failover Cluster Manager you will use later on uses logic that will detect which node has access to what storage

Configure fault domains, which is mentioned in the doc also mentioned by stephenmorris

I think that the fault domains in relation to Storage Replica, are used to determine which nodes are at what site and therefore what storage is accessible at what site. This is because you see some storage tests going on in the background during the Storage Replication wizard. However, I am not sure of this.
New-ClusterFaultDomain -Name SiteA -Type Site -Description "Primary" -Location "Site A"
New-ClusterFaultDomain -Name SiteB -Type Site -Description "Secondary" -Location "Site B"

Set-ClusterFaultDomain -Name Node1 -Parent SiteA
Set-ClusterFaultDomain -Name Node2 -Parent SiteA
Set-ClusterFaultDomain -Name Node3 -Parent SiteB
Set-ClusterFaultDomain -Name Node4 -Parent SiteB

(Get-Cluster).PreferredSite="Seattle"

What they probably forgot in this code is to turn the feature on, which you do by using:
#Enable fault domain awareness
(Get-Cluster).AutoAssignNodeSite=1

Reading the document again at a later time, I am not really sure if this is a requirement, since the only role for the Fault Domains in relation to Storage Replica seems to be to determine what storage is located at what site. It is not mandatory to have this awareness during normal operation? You would need to enable the auto behaviour if you have nodes and disks spread across racks on a single site for example and you want to use Storage Spaces Direct for your storage fault tolerance? Maybe someone could comment on that as well.

Configure Storage Replica

Now what is also not described in the document is that you need to do this via Failover Cluster Manager. Right click on the source disk -> 'Enable Replication'. In the documentation they use Powershell. This works as well, but you need to use the name of the cluster as the source AND destination computer. Not the nodes themselves. Also, for SQL Server you need to add the storage volume to a Consistency Group. You will be given this question in the GUI. The logic used by the wizard will automatically detect what volumes are accessible by which nodes. You will see this happening in the background.
Somewhere in the aforementioned document is a link, which is not easy to find, to a video that has more details: https://channel9.msdn.com/Events/Ignite/2015/BRK3487
Something regarding the consistency groups is not clear to me by the way. I don't see any way how I can add 2 source volumes to 1 consistency group. Or am I just no understanding the concept correctly?


Database finding data I have a table

  PUR# EID PID  CID         QTY  PTIME     TOTAL_PRICE
---------- --- -----       ---- --------- -----------
100001 e01 p002 c001          1 12-AUG-17      211.65
100002 e01 p003 c001          1 20-SEP-17       118.4
100003 e02 p004 c002          5 08-OCT-17        4.95
100005 e04 p007 c004          1 15-OCT-17       119.2
100006 e03 p008 c001          1 12-OCT-17       349.3
100007 e03 p006 c003          2 10-SEP-17       35.91
100008 e03 p006 c005          1 16-AUG-17       17.96
100009 e03 p001 c007          1 12-OCT-17        8.99
100010 e04 p002 c006          1 19-SEP-17      211.65
100011 e02 p004 c006         10 16-OCT-17         9.9
100012 e02 p008 c003          2 18-SEP-17       698.6

I want to find

its pid and cid as well as the number of different customers (header: “number of customers”) who have purchased it in October 2017. If a product has not been purchased by anyone in October 2017, number 0 should be shown. Use column format to change the header to “number of customers”.

P.S: It is not a homework and I am working on a project so finding it difficult

I think you want something like this,

SELECT pid, count(DISTINCT cid) AS "number_of_customers"
FROM table
WHERE PTIME >= DATE '2017-10-01'
  AND PTIME < DATE '2017-11-01'
GROUP BY pid
HAVING count(DISTINCT cid) > 0;