How to use LOW_PRIORITY for heavy queries? I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources.

I do not want them to stop end users from basic tasks when performing such heavy UPDATE tasks.

Is LOW_PRIORITY for this purpose? or I should follow another strategy?

UPDATE LOW_PRIORITY ....

Note that my engine is innoDB.

LOW_PRIORITY doesn't work for two reasons.

It works only with MyISAM engine.
It would just cause to wait until there are no more normal or HIGH_PRIORITY read requests. If your server happens to be under constant load, it can even happen, that the update doesn't get executed at all.

What you can do, is, to check with a SELECT first (I mean rewriting your UPDATE to a SELECT), that your query is performant, that it uses indexes to find the rows to update (check with EXPLAIN) and add appropriate indexes if necessary.

Then when you execute your UPDATE statements, pack them in a transaction.
The InnoDB engine logs information about currernt transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction it results in writes that, with a larger buffer, would not need to have been made at all. To set the size of the log buffer, adjust the value (or add it, if it's not set yet) in your my.cnf (Linux) or my.ini (Windows)

[mysqld]
innodb_log_buffer_size = 8M

The default value is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.

If this isn't enough, you can update rows in batches using LIMIT and spread the update statements over time, so that the row-locks aren't held for too long.

Good luck.


Set database offline with rollback immediate takes an hour to complete Every night we take our production database offline, detach it, and then re-attach it to sever any active connections before start of our backup procedures. The first command is:

USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE

This command used to take 20 seconds to two minutes earlier to complete which was acceptable. From past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.

As far as I know, we have not changed any settings that would cause this. My understanding was WITH ROLLBACK IMMEDIATE severed all connections and the database goes offline immediately. I have tried investigating the following:

recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
Statistics do not update asynchronously
no large queries are running just before the database goes offline
checking sp_who2 while the database going offline shows no users connected to it
the database's log file is very small, ~30 MB

We are currently running MSSQL Server 2012(Standard Edition) in simple recovery mode with no mirroring. The database is about 300 GB in size.

Any help is appreciated, thank you!

I believe what you are seeing is completely normal.

  Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures

Why do you do this ?. This is not the correct thing to do always and this is precisely why you are facing the issue. You can take backup of the database when it is online and this is how you should backup SQL Server database. The full backup which you will take when database is online would include all the committed changes done to database when backup finishes. Keeping in single_user and restricted_user mode before the activity may not solve your problem completely.

  This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete.

You also said that there is no long running query, for that I would say triple check. Alter database requires exclusive access on the database and it may be blocked by some other query, have you checked that.

Did yo checked for any transaction running with begin transaction and may be have missed commit. Run DBCC OPENTRAN() to see for any open transactions. Errorlog would have more information on this

The other thing I see is sometimes when you run alter command there are hardly any queries running which needs rollback and so the command finishes quickly while other days there are some command running which needs to be completely rolled back before database can be made offline. Depending on amount of rollback that needs to be done the time varies.

I would not suggest you to put database in offline mode, I do not see any use in this.


How to deploy MongoDB config servers to achieve maximum availability during network partitions? What's the recommended way to deploy config servers?

Assume, we have two shards, each one is a 3-nodes replica set.
If I deploy all config servers together with one of these shards, a single network partition can make the second shard completely not functional.

If I deploy 3 config servers in a distributed fashion, then after a network partition both shards will be available, but in read-only mode.

If I deploy config servers to a separate datacenter, it will just move the point of failure from shard-to-shard network part to shard-to-datacenter network part.

Any better options?

If you lose one of the three config servers, you just halt migrations, so in a balanced system where you're not exceeding 80% capacity, you should have hours and hours of time to page the duty DBA and bring back that one server.

You could virtualize them or place them on management band / highly reliable servers if you really can't afford to have down time for the cluster to perform chunk migration over a long weekend or other situation where you're not attentive to the config servers.

Most of your planning will be to make sure you understand the network partition scenarios and that the primary stays running and you have enough secondaries to serve read loads as needed. It's hard to generalize other than:

Add arbiters to keep the voting members an odd number.
Test your outage scenarios
Scale the system so you can actually perform rolling outages and verify that things work well without killing your application in practice. You might not test at peak load, but you should be able to take rolling outages routinely several times a week until you're confident in your tools, scripts and configuration.


Searching through dates query not working? I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute.

I checked my DataBase where date is saved in 1446976737 format which seems unix timestamp. BUT in my filters user is entering human readable dates such as 2018-11-11. Now by executing the below query i'm not getting result. Is there anything wrong with my query?

SELECT  `e`.`id` AS `id`, `e`.`first_name` AS `first_name`,
        `e`.`gender` AS `gender`,
        `e`.`email` AS `email`, `e`.`phone` AS `phone`, `e`.`age` AS `age`,
        `e`.`version` AS `version`, `e`.`evaluation_status` AS `evaluation_status`,
        `e`.`ip_address` AS `ip_address`, `e`.`date_created` AS `date_created`,
        `e`.`date_updated` AS `date_updated`
    FROM  `evaluation_client` AS `e`
    WHERE
       AND  `e`.`date_created` >= 2018-11-11
       AND  `e`.`date_created` <= 2018-11-18;

From the answers and comment i also tried the below query but still it's taking 3 minutes to load the data is it normal? How can i make it faster?

SELECT  `e`.`id` AS `id`, `e`.`first_name` AS `first_name`,
        `e`.`gender` AS `gender`,
        `e`.`email` AS `email`, `e`.`phone` AS `phone`, `e`.`age` AS `age`,
        `e`.`version` AS `version`, `e`.`evaluation_status` AS `evaluation_status`,
        `e`.`ip_address` AS `ip_address`, `e`.`date_created` AS `date_created`,
        `e`.`date_updated` AS `date_updated`
    FROM  `evaluation_client` AS `e`
    WHERE
        `e`.`date_created` >= unix_timestamp("2015-11-11")
       AND  `e`.`date_created` <= unix_timestamp("2015-11-19")

    ORDER BY
       `e`.`date_created` DESC;

(This is why we asked for the datatype of date_created!)

Assuming date_created is a TIMESTAMP:

2018-11-11 is an arithmetic expression that evaluates to 1996; certainly not what you wanted.

"2018-11-11" (with quotes) will compare correctly to 1446976737.

UNIX_TIMESTAMP(ts_column) will generate something like 1446976737, so it should work correctly.  But don't "hide a column in a function".

As for

`e`.`date_created` AS `date_created`

Since the alias (AS...) does not do anything extra, leave it off.  I am worried that

AND  `e`.`date_created`

will look at the alias, not the value.  It needs to look at the value to use this:

INDEX(date_created)

Do you have that index?

By using >= and <= you are including midnight from both ends; did you intend to do that?

WHERE AND is syntactically incorrect.  Did you leave some stuff out?  It may be important.  If you have

WHERE x = 1
  AND date_created ...

then a better index would be

INDEX(x, date_created)

1446976737 dates back a few years:

mysql> SELECT FROM_UNIXTIME(1446976737), UNIX_TIMESTAMP("2018-11-11");
+---------------------------+------------------------------+
| FROM_UNIXTIME(1446976737) | UNIX_TIMESTAMP("2018-11-11") |
+---------------------------+------------------------------+
| 2015-11-08 01:58:57       |                   1541923200 |
+---------------------------+------------------------------+

When you can't figure out what to do, here's a hint:  See what you get from SELECT * FROM tbl.  Then put quotes around something in that format.  In the case of a TIMESTAMP column, you will get something like

2015-11-08 01:58:57

So, you need date_created = "2015-11-08 01:58:57"

Assuming date_created is INT:

Either of these is a valid way to compare:

date_created >= 1446976737
date_created >= UNIX_TIMESTAMP("2015-11-08 01:58:57")

It is still valid, and wise, to have the index(es) mentioned above.


SQL Backup tuning large databases I am tuning SQL backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have SQL instance with a 4 TB database and several smaller databases from a few MB to 150 GB.

My question is can I start tuning with the 150 GB DB and then apply those settings to the 4 TB backup with minimal changes? Or does the 4 TB require it's own "tuning session" from scratch? Since it usually takes several backup attempts to dial in the settings I'm trying to get a jump-start on tuning the 4 TB database if possible.

Backing up the entire database to a single file would be much slower. You can consider below points

Stripe your backups across different spindles on a SAN.
Use backup compression (costs you more CPU) but it results in fewer overall I/O operations during backup and restore operations.
you can use the trace flags 3605 and 3213 to find out the default BUFFERCOUNT value used in your backup and then tune accordingly.
Set your max memory away from default as when you increase BUFFERCOUNT and MAXTRANSFERSIZE, they require additional memory.
For your 4TB database, assuming you have different filegroups for putting recent and historical data, you can mark the historical data as READ_ONLY and then just back it up once. You should perform regular backups of the read/write filegroups.
Use BACKUP ... WITH CHECKSUM  as it places a checksum on every page in the backup file which helps in detection of corrupt pages in the backup file when doing restores.
Best is to backup locally on the server (provided you have enough disk space) and then transfer the backup file to a remote backup location -- or -- if you are backing up directly to a network share, then best is to use Multiple NICs and/or just use differnt logical subnets in the network.

Below is from the Whitepaper -  A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network :

As a side note, its more important to test your restore strategy as a backup is ONLY GOOD if it can be restored without any issues. Enabling Instant File Initialization will considerably cut down the restore time.

Be careful when you are striping your backups as there are chances that a stripe might get corrupted or might become missing (deleted) due to machine or human error :-)

References :

Consider efficient backup strategies
Backup More Than 1GB per Second Using SQL2008 Backup Compression
How to increase SQL Database Full Backup speed using compression and Solid State Disks


SQL Developer code 'Advanced Format' versus 'Custom Format' Preferences -> Code Editor -> Format -> Advanced Format
versus
Preferences -> Code Editor -> Format -> Advanced Format -> Custom Format

As I understand, both are alternative options to (re)format code in SQL Developer, where Advanced Format bases on .xml and Custom Format bases on .prg.

It seems, that the Advanced Format is the default, which is used when pushing Ctrl+F7.

How can I deactivate Advanced Format and activate Custom Format now?

The custom format allows you to augment what's available with GUI controls in the advanced formatter preferences page.

It uses arbori, a parse tree query language.

The developer, Vadim, discusses this in his blog here.

So you don't pick one or the other - both are in play when using the formatter.


sp_primary_keys_rowset is consuming a lot of CPU I manage a database server that supports a C#/VB.NET application and we've had a lot of CPU usage problems.  I am using the following dmv query from Glenn Berry at SQLSkills:

SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.min_elapsed_time AS [Min Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],
qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time]
--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

and by a wide margin, the top 5 queries are all this sys.sp_primary_key_rowset procedure.  It looks like it's not documented in SQL Server's documentation, but some research tells me that it might have something to do with ADO and the application needing to find out more info about the table before running a query?  I can't seem to find any good info on this proc.

What causes this, how can I determine what queries are causing it, and most importantly, can we make it run less often?  It doesn't look like it's that expensive when it runs, only 100ms on average, but it's running 120+ times a minute.

sys.sp_primary_keys_rowset returns details about the primary key columns present in the table name that is passed-in via the @table_name and @table_schema parameters.

The stored procedure has the following definition:

create procedure sys.sp_primary_keys_rowset
(
    @table_name         sysname,
    @table_schema       sysname = null
)
as
    select
        TABLE_CATALOG   = s_pkv.TABLE_CATALOG,
        TABLE_SCHEMA    = s_pkv.TABLE_SCHEMA,
        TABLE_NAME      = s_pkv.TABLE_NAME,
        COLUMN_NAME     = s_pkv.COLUMN_NAME,
        COLUMN_GUID     = s_pkv.COLUMN_GUID,
        COLUMN_PROPID   = s_pkv.COLUMN_PROPID,
        ORDINAL         = s_pkv.ORDINAL,
        PK_NAME         = s_pkv.PK_NAME
    from
        sys.spt_primary_keys_view s_pkv
    where
        (@table_schema is null and s_pkv.TABLE_NAME = @table_name) or
        s_pkv.object_id = object_id(quotename(@table_schema) + '.' + quotename(@table_name))
    order by 2, 3

Although we can't directly see the source of the sys.spt_primary_keys_view view referenced in the above code, we can indirectly see that the view gathers data from various system objects, including:

syssingleobjrefs
sysidxstats
sysobjvalues
sysschobjs
syscolpars

This is what part of an "actual" execution plan looks like when I run the store procedure on my test SQL Server 2016 instance:

As you can see, it's fairly involved.  Having said that, I wouldn't expect this stored procedure to be negatively affecting performance unless it is running against a database containing a very large number of objects.  Glenn's query is returning the top 50 rows ordered by total_worker_time, which is the total amount of CPU time, in microseconds, consumed by executions of this plan since it was compiled.  It's quite possible this procedure is showing up at the top of the list as a red-herring; if the number of executions is extremely high it may be consuming a lot of CPU time without actually having a seriously negative impact on performance.   For example, if the procedure runs for 100ms at the start of a batch that takes 3 seconds, it's responsible for 1/30th of the run time.  If you change the ORDER BY clause to return results in order of average run time, or perhaps total execution count, you may see something more meaningful.

I wrote about how to look at wait stats to see what, if anything, is slowing SQL Server down.


Transaction Log Shipping Removal, Backup and Drop Database I have  databases (about 15 different) Production Databases set for Log_Shipping and I would like to take a full backup of it and need to drop from production later on because we have already move it to new environment.

Should I, just end log shipping and back up database?
Should I, just take backup and keep log shipping on until I, drop it from current environment?
How should I remove log shipping from current environment at the time of drop of databases.

Thank You for help and time.

You should first remove logshipping using below procedure. You can use both TSQL and SSMS for this
To Remove Log Shipping
On the log shipping primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.
On the log shipping secondary server, execute sp_delete_log_shipping_secondary_database to delete the secondary database.

Note
If there are no other secondary databases with the same secondary ID, sp_delete_log_shipping_secondary_primary is invoked from sp_delete_log_shipping_secondary_database and deletes the entry for the secondary ID and the copy and restore jobs.

On the log shipping primary server, execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This also deletes the backup job.
On the log shipping primary server, disable the backup job.
On the log shipping secondary server, disable the copy and restore jobs.
Optionally, if you are no longer using the log shipping secondary database, you can delete it from the secondary server.
To remove Log Shipping using SSMS
Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
Under Select a page, click Transaction Log Shipping.
Clear the Enable this as a primary database in a log shipping configuration check box.
Click OK to remove log shipping from this primary database.
After logshipping is removed drop databases.


SQL Server LaunchPad "The service did not respond in a timely fashion" + No Log in ExtensibilityLog I'm trying to start LaunchPad service but it fails to start with:

Error 1053: "The service did not respond in a timely fashion"

I've been reading this was common in RC versions but I'm running SQL Server 2016 SP1 + CU3.

I've tried to add the SQLRUserGroup to the LogOnLocally user right assignment. The service still not run.

Also, there is no log in folder:
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ExtensibilityLog

I've tried to change security of log folder including the NT Service\MSSQLLaunchpad and the SQLRUserGroup entries.

Any suggestion is appreciated

UPDATE: It was fixed reinstalling the SP1!

UPDATE2: We reproduced the issue simply by following these steps: install SQL 2016, Update it with SP1, Add R Services using the installation (no SP1).

I has this issue, this tutorial did the trick for me.

https://www.mssqltips.com/sqlservertip/4558/sql-server-launchpad-service-cannot-be-started/

Should be noted an easy work around for me was just to change the account to NETWORK SERVICE in config manager, if you're not fussed about using a dedicated account for security reasons it will likely work for you also.


Reviewing the design of a database about animals of two distinct species I am creating a small database using SQL Server Express 2017.

There are two animals (Foxes and Elephants).
The Foxes table will have the columns: Identifier,
Species, BodyLength, and FurColour.
The Elephant table will have: Identifier, Species,
ElephantWeightKG, and TuskLengthCM.
Each animal will have a list of "Items", I have an Items table from which Foxes and Elephants both share.
Currently, there is no overlap between the Fox and Elephant identifiers. But there is the
possibility in the future that the format of the Identifiers change
(worst case scenario there may be a bit of overlap). The database
should be designed to deal with this.

After a bit of trial and error, and research, this is what I have come up with:

create table Foxes
(
    Identifier varchar(50) not null constraint PK_FoxIdentifier primary key,
    Species varchar(50),
    BodyLength int,
    FurColour varchar(50),
);

create table Elephants
(
    Identifier varchar(50) not null constraint PK_ElephantsIdentifier primary key,
    Species varchar(50),
    ElephantWeightKG int,
    TuskLengthCM int,
);

create table Items
(
    ItemSeq int identity(1,1),
    ItemID as isnull(FoxID, ElephantID) + '-' + cast(ItemSeq as varchar) persisted primary key,
    FoxID varchar(50),
    ElephantID varchar(50),
    ItemDescription varchar(500),

    foreign key (FoxID) references Foxes(Identifier) on delete cascade on update cascade,
    foreign key (ElephantID) references Elephants(Identifier) on delete cascade on update cascade,

    constraint CK_FoxIDElephantsItemIdentifier check
    (
        case when FoxID is null then 0 else 1 end +
        case when ElephantID is null then 0 else 1 end = 1
    )
);

Is this an acceptable design? What would you do differently?

Thanks.

Analyzing the scenario from a pure conceptual perspective, an important entity type that has been missrepresented so far is Animal, which would help to (a) convey all the relevant associations more accurately and to (b) delineate the whole database more clearly at the logical level.
In this way:

Fox and Elephant would then be entity subtypes of Animal, which would in turn be their entity supertype.

Since each particular Animal instance is either a Fox or an Elephant —never both— it means that the subtype instances are mutually exclusive.

Any given Animal occurrence —be it a Fox or an Elephant— may be linked to zero, one, or more Item occurrences, while an Item instance will be linked to exactly one Animal counterpart —be it a Fox or an Elephant—.

Note: Supertype-subtype associations are referred to as superclass/subclass relationships in some conceptual modelling methodologies.

Expository logical DDL design
Consequently, I would set up the corresponding logical-level design along the lines of:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns depending
-- on the applicable business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient physical-level index strategies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Species (
    SpeciesCode CHAR(1)  NOT NULL,
    Name        CHAR(30) NOT NULL,
    --
    CONSTRAINT Species_PK PRIMARY KEY (SpeciesCode),
    CONSTRAINT Species_AK UNIQUE      (Name)
);

-- “Populating” the Species table:
INSERT INTO Species
    (SpeciesCode, Name)
VALUES
    ('F', 'Fox'),
    ('E', 'Elephant');

CREATE TABLE Animal ( -- Represents the supertype.
    AnimalId        INT       NOT NULL IDENTITY (1,1), -- Column meant to enclosed the Identifiers as established in the business context of relevance.
    SpeciesCode     CHAR(1)   NOT NULL, --Stands for the discriminator property.
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Animal_PK            PRIMARY KEY (AnimalId),
    CONSTRAINT Animal_to_Species_FK FOREIGN KEY (SpeciesCode)
        REFERENCES Species (SpeciesCode)
);

CREATE TABLE Fox ( -- Conveys one of the subtypes.
    FoxId      INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column.
    BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
    BodyLength INT         NOT NULL,
    FurColour  VARCHAR     NOT NULL,
    --
    CONSTRAINT Fox_PK           PRIMARY KEY (FoxId),
    CONSTRAINT Fox_AK           UNIQUE      (BusinessId),
    CONSTRAINT Fox_to_Animal_FK FOREIGN KEY (FoxId)
        REFERENCES Animal (AnimalId)
);

CREATE TABLE Elephant ( -- Represents the other subtype.
    ElephantId INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column either.
    BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
    Weight     INT         NOT NULL,
    TuskLength INT         NOT NULL,
    --
    CONSTRAINT Elephant_PK           PRIMARY KEY (ElephantId),
    CONSTRAINT Elephant_AK           UNIQUE      (BusinessId),
    CONSTRAINT Elephant_to_Animal_FK FOREIGN KEY (ElephantId)
        REFERENCES Animal (AnimalId)
);

CREATE TABLE Item (
    ItemSeq         INT          NOT NULL IDENTITY (1,1),
    AnimalId        INT          NOT NULL,
    Description     VARCHAR(500) NOT NULL,
    CreatedDateTime DATETIME     NOT NULL,
    --
    CONSTRAINT Item_PK           PRIMARY KEY (ItemSeq),
    CONSTRAINT Item_to_Animal_FK FOREIGN KEY (AnimalId)
        REFERENCES Animal (AnimalId)
);

This db<>fiddle contains the DDL layout shown above so that you can see it in action.

The columns FoxId and ElephantId, constrained as the PKs of the appropriate tables, help in representing the conceptual-level one-to-one association by way of FK constraints that point to the AnimalId column, which is constrained as the PK of the Animal table. This implies that, in an actual “pair”, both the supertype and the subtype rows are identified by the same PK value; thus, it is more than opportune to mention that (a) attaching an extra column to hold system-controlled surrogate values to (b) the tables standing for the subtypes is (c) entirely superfluous.
As you can see I added the Animal.CreatedDateTime because I esteem that it is important to know the exact point in time when a certain Animal row was INSERTed into the database.
In this manner, the association between the Item rows and the Fox or Elephant rows is established “indirectly” via the corresponding Animal rows and the suitable constraints.
The Item.AnimalId column prevents having (1) one column for The Items.FoxId and (2) one column for The Items.ElephantId, with all the ambiguities and needless complexity they involve (e.g., accepting NULL marks with the repercussions they have on data manipulation, adding a CHECK constraint like the one you added to your Items table, etc.).
“Look-up” table for the FurColour data
I am not familiar with FurColours of Foxes (e.g., I do not know how many FurColours are presented by Fox specimens, or how the FurColours are composed, etc.), but you may like to evaluate incorporating a table that serves a “look-up” role for this sort of information, which can in turn be referenced to from a FOREIGN KEY constraint in the Fox table.
Integrity and consistency considerations
It is paramount to mention that, in your business context, you have to (1) ensure that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart, and, in turn, (2) guarantee that said “subtype” row is compatible with the value contained in the “discriminator” column of the “supertype” row.
It would be very elegant to enforce such circumstances in a declarative manner by virtue of ASSERTIONs but, unfortunately, none of the major SQL platforms has suitable support for these powerful instruments (which are the proper tools for this type of job). Therefore, resorting to procedural code within ACID TRANSACTIONs is quite convenient so that these conditions are always met in your database. Other option would be employing TRIGGERs (procedural as well), but they tend to make things untidy.
Creating views to fix useful derivable tables
It seems that one of the purposes served by the columns FoxID and ElephantID of your Items table is that they help to determine the type of Animal row that an Item row is linked to, but that need is better coped with by means of a view (i.e., a derived table) as exemplified as follows:
CREATE VIEW ItemWithAnimal AS
    SELECT I.ItemSeq,
           A.AnimalId,
           A.SpeciesCode,
           I.Description,
           I.CreatedDateTime AS ItemCreatedDateTime,
           A.CreatedDateTime AS AnimalCreatedDateTime
        FROM Item   I
        JOIN Animal A
          ON I.AnimalId = A.AnimalId;

…where the SpeciesCode column values indicate the exact species of the AnimalId value of concern, so the people interpreting the data can see if the Item under consideration is linked to a Fox or an Animal. So when you want to retrieve Item information, you can SELECT directly FROM this derived table or view, instead of the Item base table.
Having views that fetch the “full” Fox or Elephant information seems to be useful too, e.g.:
CREATE VIEW FullFox AS
    SELECT A.AnimalId,
           F.BusinessId,
           F.BodyLength,
           F.FurColor,
           A.CreatedDateTime
        FROM Fox    F
        JOIN Animal A
          ON F.FoxId = A.AnimalId;

Evidently, this view would be more benefitial if, say, the Animal table was comprised of more columns meant to be “shared” by the Fox or Elephant tables, but it is worth to illustrate the benefit of views.

The views discussed above are also included in the db<>fiddle previously linked to.

Review of your Items.ItemID, Items.FoxID and Items.ElephantID columns
It seems that your Items.ItemID column is some sort of derivable column, i.e., the values it contains are calculated in terms of the values contained in two other columns of your table, i.e., Items.FoxID or Items.ElephanID along with Items.ItemSeq (which is unnecessarily involved). I highly advise that you avoid resorting to that kind of course of action for any kind of column, but since Items.ItemID is constrained as the PRIMARY KEY (PK) I put more emphasis to my advise.
In this way, if in your business domain an Item is uniquely diferrentiated by the combination of its ItemSeq and the Identifier of the Animal of relevance (be it a Fox or an Elephant), you can then adapt the logical arrangement I expounded for the Item table by declaring a two-column composite primary key on (AnimalId, ItemSeq) and avoid the work arround with the derivable PK.
Other scenarios involving supertype-subtype associations
In case that you want to see more examples of supertype-subtype relationships, which are typical data structures that come about in business environments of very different natures, you may find of interest, e.g., my answers to the questions entitled:

“Database schema for entities with two possible owner/parent types?”
“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”
“How to model an entity type that can have different sets of attributes?”

You may like to take a look at the rest of posts grouped by the subtypes tag too.


Why is an additional filtered statistic being ignored (EAV schema)? I'm trying to improve a row estimate for this sub-query (of a larger query).  The estimate is showing 1266 rows.  The actual is 117k rows.  This particular property (EAV schema) only has two values defined for it (2 and 3):

declare @pPropVal smallint = 2;

select Value, ObjectId
  from Oav.ValueArray PropName
 where PropName.PropertyId = 897
   and PropName.Value  = @pPropVal
option (recompile)

The query plan shows the proper seek predicate on index IX_ValueArray_PropValObj on PropertyId and Value as expected.

(A) As an attempt to improve row estimates, an additional statistic was added which brought the row estimate up slightly to 3041:

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](PropertyId, Value, ObjectId)
 where
     (
             PropertyId = 897
         and [Value] is not null
     )
  with fullscan

The histogram shows a single row.  The HI key is just the PropertyId (the first column) which is not that useful so as I understand it, it is using the density information.

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
897             0           196026   0                    1

All density Average Length  Columns
1           4               PropertyId
0.5         8               PropertyId, Value

Name    Updated Rows    Rows Sampled    Steps   Density Average key length  String Index    Filter Expression   Unfiltered Rows
ST_SomePropertyName May 20 2014  2:01PM 196026  196026  1   0   8   NO  ([PropertyId]=(897) AND [Value] IS NOT NULL)    9317055

(B) Since there is a filter on PropertyId = 897, I thought I could re-create the statistic like this:

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](Value, ObjectId)
where
    (
       PropertyId = 897
       and [Value] is not null
    )
 with fullscan

The histogram looks useful to my eyes but the estimator appears to be ignoring it because it reverts to the original estimate of 1266.

RANGE_HI_KEY  RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS   AVG_RANGE_ROWS
2             0           117760   0                     1
3             0           78266    0                     1

All density   Average Length  Columns
0.5           4               Value
5.101364E-06  12              Value, ObjectId

Name    Updated Rows    Rows Sampled    Steps   Density Average key length  String Index    Filter Expression   Unfiltered Rows
ST_SomePropertyName May 20 2014  2:04PM 196026  196026  2   0   12  NO  ([PropertyId]=(897) AND [Value] IS NOT NULL)    9317055

(C) It does work to filter to a fixed value (and not even need the 2nd two columns) but that is not a very practical solution.  This gave the exact estimate 117k.

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](PropertyId)
 where
     (
             PropertyId = 897
         and [Value] = 2
     )
  with fullscan

histogram:

RANGE_HI_KEY   RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
897            0           117760   0                    1

(D) (Added to original question) An approach of limiting the value to a smaller range helps.  But if the range values are not uniform or the value was a string based field or not even known this may not be a good workaround in general:

CREATE STATISTICS [ST_ListUnderBrand_897] ON [Oav].[ValueArray](PropertyId, Value)
WHERE
  (
      PropertyId = 897
      and [Value] >= 1 and [Value] <= 20
  )
  with fullscan

This gives estimates of about 16k.  Changing the [1,20] to the exact [2,3] gives estimates of ~80k.  It seems obvious that the true range of Values from the table data are not really used (since it is 2nd column) and this is some estimate based mostly on the filter range.

Please note the Value field is a sql_variant but I don't think that is related as the query plan does not show any implicit conversions.

Why doesn't SQL Server use the statistics from B?   Should it?

Are there other options available to fix this?

Filtered indexes and statistics won't come into play when you're using local variables, unless you use the OPTION (RECOMPILE) query hint, and are running SQL Server 2008 R2 or later.

Tim Chapman's MSDN blog post explains with examples.


How do you run T-SQL commands on multiple servers from one place? I have some daily and weekly tasks where I run a script to get the required information.  What is the way to running this code once and hitting all the servers without having to log into each one of them separately?

There are a couple of ways to go about this, but natively it sounds like Central Management Servers is a pretty good fit for you.

If it is just "one" script and "one" functionality to pull the information, consider writing a quick PowerShell script that loops through all of your servers (the list of servers can be contained in a text file or a database table, etc.) and executes the script.  This is quite simple and painless.

It all depends on how often your script(s) will change and what your comfort level is with each of the different approaches.


Extra fields in user_role / roles tables or a new table I have a user_roles table which I use to manage permissions and user's access to certain things. For example, a team manager is a role. Since a team manager belongs to a specific team, the way I handle that is by having extra fields in these tables for example:

roles_table
  * has_record
  * record_str

user_roles_table
  * record_id

So if I need to get the team of a team manager, I would have to check the roles record to get the record_str, in this case 'Team' and instantiate an object using that string and the record id in user_roles.

I did it this way because it was a generic way to apply it across all roles in which case I can continue to add roles without changes. Its also nice for adding roles to users. If I am assigning roles, and it has a record, I can then query for the records using the str and display the options in a drop down.

My concern with this is the performance. Do you guys think it will last at a high scale?

The other option I was considering was to just create a table for each user role. This is less modular since if I want to create a role I need add a table, but it provides a more direct way of getting from the user_role to the record without all the joins in between.

I would use your current design over creating a table for each user role. Why create a new role, when your code may have to cycle through several tables to get an answer that could have come from one place.  (Opinion:  You would just be making more work for yourself.)

Of course, a Team, a TeamManager, and a TeamMember may all have some attributes that are related only to their individual roles.

If you are facing that challenge, then a supertype-subtype design can work well for this issue.  All the common data is in the supertype table, but each subtype contains data unique to that role/task/etc.  (Whatever you are trying to manage.)

A Microsoft Tutorial give a basic description at Lesson 5: Supertypes and Subtypes at https://msdn.microsoft.com/en-us/library/cc505839.aspx


Mistake during Oracle 11g PITR I tried using set time until.. and mis-typed the date.  Can anyone help me understand how to get my backups into a manageable state?

After the accidental recover, most of my backupset disappeared.

I recovered them and used 'catalog recovery area' .. and they're listed in 'list backupset'.  But something still isn't right.

When I do a PITR now, I get messages that my dbf files aren't available

and... the 'list backupset' seems to show backupsets.  But they are listed differently than the files which weren't included in the 'bad' recovery.

Gists with the error and the list of backupsets are here https://gist.github.com/akinsgre/5561254

So after working through the info that Phil gave me.. and doing some research after the fact..

It appeared that the Set Until with a future date screwed things up by "removing, or de-cataloging my backups".  Even if that's possible (I don't know enough about Oracle to say) it wasn't the case.

What had happened was that my initial Full Backups were gone due to some poor data-retention.  So when I put a future date, it appeared like my PITR would work because it had a Full backup from a day ago.  However, I never did have a Full backup from a week ago.     I had taken one a couple months ago. but it had been deleted.

I'm using EM Console for my backups and haven't yet figured out how to tell it to do incrementals every day and fulls once a week.  I'll get there though.

Thanks for all the help.


PostgreSQL for high volume transactions and for Data warehousing Am quite new to PostgreSQL, I have never done a large deployment using it before. But, I have good experience in enterprise solutions and I want to try and apply some of what I learned using PostgreSQL.

I have site which is sized to handle large number of data and traffic. The infrastructure will be built using on amazon (AWS) using EC2 instances and EBS volumes.

The design should have two databases, a main transactional database and a data warehouse to handle analysis and reporting.

Main transactional database

will be used for live website, the site is built on multiple nodes to scale up concurrent users. Mainly we require the database for this case to be extremely fast in reading operations, we expect >100GB data with 30% annual growth. At this point, we are planning to use two EC2 servers (and add more later as we need).

my question, what is the recommended setup for the above requirements? Plus, is there a way to manage the table and volume partitioning? is there recommendations for using AWS setup?

Data warehouse database

Will be used mainly for capturing all the data from main transactional database in time dimension. so, even deleted records from main database will be captured in the DWH. Therefore, the data will be very large and growth will be even bigger. We will also use couple EC2 instances or more if required.

What is the recommended setup in this case? this will require fast writing operation because of the constant writing (ETL). Can we build OLAP cubes in PostgreSQL? if yes, has anyone out there gave it a try?

Connecting to database

The web servers will be connecting to the main database to query and write. We are currently developing an application using django which uses native library for connecting. Is it recommended to use the same basic method? or should we configure pgpool?

Data warehouse (ETL)

What is recommended way for building ETL processes to read from main and load to data warehouse? Any tools? methodology to follow? does PostgreSQL offer any helpful functions/tools in building ETL processes?

Infrastructure/Database Services

You should probably read this for an overview of a high-volume site that runs on AWS with EBS. They've moved to Ephemeral storage but have had to create some redundancy in being able to (re)store the data.

http://blog.reddit.com/2012/01/january-2012-state-of-servers.html

Data Warehouse/ETL

I've used Pentaho in the past. Not directly with postgres, but I've found it to be a good solution for both OLAP (Mondrian) and ETL (Kettle)

http://www.pentaho.com/

edit: "Community Editions" can be found here

http://mondrian.pentaho.com/

http://kettle.pentaho.com/

Connection

These folks seem to really like pgbouncer. https://stackoverflow.com/questions/1125504/django-persistent-database-connection

I have no experience with it, though. Apparently, Disqus uses it.


Is there a way to search the earliest or latest postgres partition for table? BACKGROUND

I am working on a web application that has date pickers for searching. Those searches query a partitioned table. So far, so good.
However, based on the data I'm querying, we don't have a limit on the early date picker. I could impose an artificial, arbitrary value, but I'd rather have a quick query that fetches the earliest found partition table, possibly the latest as well, without knowing the date ranges of those partitions so we can get the earliest found date.

Partitions are created using a function that I believe is called in a cron job every month that creates a month+1 partition. They are named after timestamps, but only use year and month.

PROBLEM

Is there something that allows me do so this (pseudocode):

select min(date_column) from parted_table.earliest_partition;

For example, if a monthly table partition is created every new month starting from 2018, I would expect earliest_partition to be table_2018_01

select min(date_column) from table_2018_01

How would I get table_2018_01 without knowing that 2018_01 was the earliest created partition?

Disclosure: I'm working with someone else's code so I am not fully familiar with the system.

postgres 9.4

tl;dr I guess what I'm looking for is a query that searches table names and returns the earliest one. Should be doable through some built in pg_ table?

First off, many thanks to those who commented and helped me get to this answer. I was not as detailed as I should have been in posting the question. I believe this is the answer I seek:

select table_name from information_schema.tables where table_name like 'table_%' order by table_name limit 1;

It should return the earliest table partition name given the naming conventions we use on them.


Multiple Selects With One Column Encrypted I am trying to do two select statements on the same table. However I get errors. The reason for this query is one of the columns is 256 bit CRC encrypted. Here is my query:

SELECT (select COLUMNA from TABLE) AS 'NumberA',
(select CAST(aes_decrypt(COLUMNB,'key','IV') AS CHAR) from TABLE) AS 'Number2';

This query throws this error:

Error Code: 1242. Subquery returns more than 1 row

Both of these selects by themselves work fine. However I need to figure out how to select all of the columns and use the encrypted column in the query as well.

Also, if you can't answer the question, don't put it on hold. Just state you don't know the answer

I don't believe the issue is concerning the encryption. You are doing two sub-queries either of which could contain multiple rows, hence the error. If both source tables are the same, you can re-write as: -

SELECT COLUMNA AS 'NumberA',
CAST(aes_decrypt(COLUMNB,'key','IV') AS CHAR) AS 'Number2'
FROM TABLE


RAID0 instead of RAID1 or 5, is this crazy? I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or other documentation you can point me to on this topic, that would be great.

We have 3 servers in 2 datacenters that are part of an SQL cluster. They are all running SQL Server in an Availability Group. The primary has a replica sitting right next to it and another in the other datacenter. They are running synchronous replication with automatic failover. All drives are enterprise class SSDs. They will be running SQL Server 2017 or 2019.

I'm thinking that there would be multiple benefits to running them on RAID0 arrays over other methods with few, if any, real drawbacks. The only negative I'm currently seeing is lack of redundancy on the primary server, so it failing increases. As pros:

If a drive fails, rather than run in a slowed, degraded state until someone receives a notice an manually acts on it, the server will immediately fail to a secondary maintaining full operational capability. This will have an added benefit of notifying us of a failover, so we can investigate the cause sooner.
It reduces the chance of failure overall per TB capacity. Since we don't need parity or mirror drives, we reduce the number of drives per array. With fewer drives there is less total chance of a drive failure.
It is cheaper. Needing fewer drives for our required capacity obviously costs less.

I know this isn't the conventional business thinking, but is there something I'm not considering? I'd love any input either pro or con.

I'm not trying to do this for query performance gains, though if there are meaningful ones feel free to point them out. My primary concern is failing to consider or address a reliability or redundancy issue that I haven't thought of.

The OS is on a separate mirrored drive, so the server itself should stay up. One of those drives can be replaced and again mirrored. It is small and there aren't any database files other than system DB's on it. I can't imagine it taking more than minutes. If one of the data arrays fails, we replace the drive, rebuild the array, restore and resync with the AG. In my personal experience, restoring has been MUCH faster than a RAID5 drive rebuild. I haven't ever had a RAID1 failure, so I don't know whether that rebuild would be faster or not. The restores would be coming from a backup and rolled forward to match the primary, so the load increase on the primary server should be very minimal only syncing the last few minutes of logs with the recovered replica.

There's one very important aspect I think you are missing in your assessment:

How do you plan to recover?

When raid5 loses a drive, it will run in a degraded state until it has recovered automatically. (At least if you have a hot spare at hand.)

When a raid0 loses a drive, it cannot ever recover at all. This means you have lost redundancy, and to recover it, you need to rebuild your raid0, and copy all the data (not just the data on the broken drive) back from the secondary that is now under production load. That is, instead of the single degraded raid5 array, it's now your entire production setup that gets the performance hit.

If the raid5 (or raid6) degraded state performance penalty isn't something you can cope with, you should probably do raid 1+0 instead. Yes, it costs more, but disk prices being what they are, it's going to be money well spent.

Maybe "actively monitor the raid5 state, and transfer the load off the primary when a drive fails" is the solution that gives you most of the benefits without any drawbacks? (Apart from losing the coolness factor of running without any local redundancy, of course.) If your raid5 drive recovery is taking a lot longer than a complete database data sync, either your raid software is acting strangely, or you have seriously oversized disks, I'd think.


Sql Statement to display multiple true statement I have a sql statement that checks for certain special characters in a string and returns that.

SELECT

CASE
WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], '
WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], '
WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN '[DSC1 - STX (start of text)], '

END  [Special Character]

The issue with below is that if the string has more than one special character, it just lists the first one and not the other as I guess the case statement breaks as soon as it finds the first match.

How do write that it lists all that it finds. e.g. if the string under DSC1 has both char(1) and char(2), then it will return

  Special Character

  [DSC1 - NUL (null)], [DSC1 - SOH (start of heading)],

You'll need separate CASE expressions since evaluation stops after the first condition is satisfied.

You could use CONCAT to concatenate the values of all expression results into a single column. If the result of a CASE is NULL (WHEN predicate is false or unknown), CONCAT will return an empty string for the value.

SELECT
    CONCAT(
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], ' END,
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], ' END,
        CASE WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN '[DSC1 - STX (start of text)], ' END
        ) AS [Special Character]


Backup/restore check data is the same SQL Server I'm a trainee SQL DBA. I'm using SSMS 2008r2.
I've backed up a database and restored this as a new database on a new instance.
I want to check to see if the data is the same on both databases. Is there a way I can do this without running select commands on both databases and comparing?

Thanks.
Ryan.

SQL Server Data Tools offers a Data/Schema comparison option. You need to install it, previous download of it, and then do the comparison.

More information on how to compare in How to: Compare and Synchronize the Data of Two Databases.

To download the free tool, please visit https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx.


how do I execute a void-returning procedure in pgsql 9.1? If I execute the following in a file with the pgsql command, it complains that the procedure needs to be called with 'perform'. But when I try to use perform, it tells me that perform is not defined. What's the solution?

create or replace function waitris() returns void as
$$
declare
    cnt integer;
begin
    loop
        select count(*) into cnt from taskflag where taskid = 'rdfdb' and state != 2;
        if cnt = 0 then
            exit;
        end if;
        select pg_sleep(1);
    end loop;
end;
$$
language plpgsql;

select waitris();

There is no problem with how you call your function.  The error you get comes from executing it if and only if cnt is greater than zero.  The error you get is

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "waitris" line 9 at SQL statement

and it clearly states that the problem lies in the SELECT pg_sleep(1); line.  That is the place you should replace SELECT with PERFORM.

Otherwise your loop is futile without a CONTINUE.  And even if you add that, behaviour of the loop is hard to follow.  You'd better use a WHILE loop instead.  And still one more: as you see, I changed the variable name count to cnt for better readability.


Mysql same DB, same query, different users - different error messages On my AWS RDS Mysql 8.0.16 server I have one DB (my-db) and 2 users: the master user (created automatically by RDS) and an app user created by me:
create user if not exists `appuser`@`%` identified by 's3cr3t';
grant all on `my-db`.* to `appuser`@`%`;

When I run the delete from Things where id = 123 query on behalf of the appuser, the error message I get is:

[23000][1217] Cannot delete or update a parent row: a foreign key constraint fails

(1217 and no constraint details)
When I run the same query as a master user, the error message is:

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (my-db.Sandwiches, CONSTRAINT SandwichesThingIdFK FOREIGN KEY (thingId) REFERENCES Things (id))

(1451 and there are constraint details)
How do I make appuser get 1451 and all the details?

It looks like this issue is specific to Mysql 8.0.16. I've upgraded to 8.0.21 and I now get 1451 as expected.
Longer answer: 1217 (ER_ROW_IS_REFERENCED) and 1451 (ER_ROW_IS_REFERENCED_2) both describe the same situation. As far as I understand, Mysql will use 1451 if user has a REFERENCES privilege for the table of interest, and 1217 otherwise. And it's the same story about 1216 (ER_NO_REFERENCED_ROW) vs 1452 (ER_NO_REFERENCED_ROW_2).


NTLM vs Kerberos authentication - questions We have 4 servers, all are members of the same Domain:

1. SQL 2012 on Windows Server 2016
2. SQL 2012 on Windows Server 2012
3. SQL 2017 on Windows Server 2016
4. SQL 2017 on Windows Server 2016

I noticed that on first two servers, domain users are connecting using NTLM only (sys.dm_exec_connections DMV, auth_scheme column)

Researching how to change auth_scheme to Kerberos, learned that "Write ServicePrincipalName" privilege must be granted to SQL Server Service Account in the Active Directory Service Interfaces (ADSI Edit)

Right now privilege "Write ServicePrincipalName" is NOT granted to the service account

Before granting that privilege to SQL Service Account, I decided to check auth_scheme on the 3rd and 4th servers, and found out that domain users are actually connecting using Kerberos auth_scheme to those servers

Question is - how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth to servers 3 and 4, and as NTLM to servers 1 and 2 at the same time ?

Is there some setting somewhere, that makes the difference between servers 1,2 and 3,4 and how users connect to them ?

how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth

That privilege is not required for Kerberos to work, it only enables the service account to register its own Service Principal Names (SPNs).

See Register a Service Principal Name for Kerberos Connections


DBCC CHECKDB WITH PHYSICAL_ONLY option runs slower compared to a Full DBCC CHECKDB? Is it possible for DBCC CHECKDB WITH PHYSICAL_ONLY to run slower compared to a full DBCC CHECKDB? The platform is SQL Server 2014 SP2.

According to Microsoft, no.

DBCC CHECKDB Documentation

  PHYSICAL_ONLY
  Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.
  A full run of DBCC CHECKDB may take considerably longer to complete than earlier versions.

I confirmed this by running DBCC CHECKDB against the same 50GB database twice, once with PHYSICAL_ONLY specified, and the other without any options set. The results:

 With physical_only:
 SQL Server Execution Times:
 CPU time = 46894 ms,  elapsed time = 61546 ms.

 All checks:
 SQL Server Execution Times:
 CPU time = 247964 ms,  elapsed time = 263678 ms.

Having said all that, do you see that PHYSICAL_ONLY takes longer than a full check?

EDIT: Here's the results for a 320GB database:

With physical_only:
SQL Server Execution Times:
CPU time = 552805 ms,  elapsed time = 1218867 ms.

All checks:
SQL Server Execution Times:
CPU time = 2074875 ms,  elapsed time = 3007516 ms.

So, no - PHYSICAL_ONLY on databases larger than 200GB is not slower than a full check.


Test SQL Server connection with sqlcmd using machine name How can I test a connection via machine name as the SQL logon name (ie mydomain\server01$ - as an IIS AppPool would connect) using sqlcmd?

I'm having trouble figuring out an application's SQL Server connection issue.

You would generally not find an Application pool using the computer account to connect to SQL Server. At most I would not want to allow that type of configuration.

You should be able to accomplish it by running the process as the local system account,  NT AUTHORITY\SYSTEM. You can do this by using psexec. You would just specify -s parameter to execute it as the system account.

I think something like this should work:

psexec -s "sqlcmd -S myserver -E"


ORA-12546: TNS:permission denied, on Windows through Task Scheduler I am trying to export all objects of specific oracle user by executing a batch file through Windows Task Scheduler:

exp.exe myuser/pass@dbname owner=myuser log=mylog.txt

The Oracle 11.2.0.4 x64 and the database are on that same Windows7 Pro machine

The task starts OK. That is to mean that no wrong user or password issues are there. But when task is started by schedule, it says in the log:

ORA-12546: TNS:permission denied

When task is started manually, under same user as the scheduled user (this user is member of Administrators group and ora_dba group, by the way), it works fine and export is done. The task is scheduled with an "Execute with highest privileges" check-box in task properties, so I don't see why there is difference between the "by schedule" and "manual" task start.

I've read a lot on "ORA-12546: TNS:permission denied" on stackoverflow site, but all topics are concerning Linux. I tried to set read and execute permissions on c:\app\oracle, where my Oracle is installed. Actually, Administrators already by default had all permissions on that directory, so it did not make any difference

I noticed that export of tables, however, works fine even in the task started by schedule:

exp.exe myuser/pass@dbname tables=mytable1,mytable2 log=mylog.txt

At any rate, the oracle user “myuser” has oracle rights to export entire database

Problem:
Only-tables are exported fine, but with «owner=myuser» export key I get «TNS:permission denied», and only if Windows scheduled task is started by schedule, while manually started task does everything OK. How do I make it export all, and by schedule?

The problem lies somewhere deep in windows permissions. Those are messing somehow with Oracle TNS system. I still do not know the right way and what exactly those permissions are, but the problem is solved for now, by means of bypassing TNS, as self-suggested previously:

set ORACLE_SID=dbname
exp.exe myuser/pass owner=myuser log=mylog.txt

This works, no error


What's the term that describes filtering query that makes use of indexes I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about that, but I can't find it). It has (AFAIK) two or three syllables.

It basically denotes the type of query that makes use of indexes, and constitues of best practices to use when querying the database.

Some of the best practices are these:

do not search by perform functions or computations on column names
do use LIKE operator with trailing wildcards, not leading
compare column value to fixed value (constant),

Eg.

SELECT column, date FROM table (..)

(..) WHERE column LIKE 'A%' -- OK, because looks up the column
(..) WHERE column LIKE '%A%' -- INCORRECT, expensive
(..) WHERE SUBSTRING(column, 1, 3) = 'abc' -- INCORRECT

Do you know what's the term describing these types of queries?

Thanks.

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different platforms, and even different versions and editions of the same platform.

References:

Wikipedia
https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable
A question on SARGability


Mongodb - Copy files for mongorestore I've got 3 nodes in a replica set.

Each time when performing a mongorestore from the Master, the secondaires go into a RECOVERING state. The oplog cannot keep up. unfortunately I cannot increase the oplog size because lack of disk space.

Now, I want to perform a manual restore by copying the files.
https://docs.mongodb.com/manual/tutorial/resync-replica-set-member/

Here is a snippet of the files which I got:

meee@mongo-p01:~$ sudo ls -la /var/lib/mongodb
[sudo] password for meee:
total 44052516
drwxr-x---   4 mongodb root          4096 Feb 22 17:54 .
drwxr-xr-x. 32 root    root          4096 Feb 23 01:59 ..
-rw-------   1 mongodb mongodb   67108864 Feb 22 17:54 mymongobase.0
-rw-------   1 mongodb mongodb  134217728 Feb 22 17:08 mymongobase.1
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.10
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:12 mymongobase.11
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.12
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.13
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.14
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.15
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:14 mymongobase.16
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:15 mymongobase.17
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:21 mymongobase.18
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:51 mymongobase.19
-rw-------   1 mongodb mongodb  268435456 Feb 22 17:08 mymongobase.2
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:51 mymongobase.20
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.21
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.22
-rw-------   1 mongodb mongodb  536870912 Feb 22 17:08 mymongobase.3
-rw-------   1 mongodb mongodb 1073741824 Feb 22 17:08 mymongobase.4
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:11 mymongobase.5
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:53 mymongobase.6
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.7
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 mymongobase.8
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:53 mymongobase.9
-rw-------   1 mongodb mongodb   16777216 Feb 22 17:54 mymongobase.ns
drwxr-xr-x   2 mongodb mongodb         40 Feb 22 17:54 journal
-rw-------   1 mongodb mongodb   67108864 Feb 22 17:07 local.0
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 local.1
-rw-------   1 mongodb mongodb 2146435072 Feb 22 17:54 local.2
-rw-------   1 mongodb mongodb   16777216 Feb 22 17:54 local.ns
-rwxr-xr-x   1 mongodb mongodb          6 Feb 22 17:06 mongod.lock
drwxr-xr-x   2 mongodb mongodb          6 Feb 22 17:54 _tmp

What are the correct steps I should take?

Reset all mongos by reïnstalling

Perform a mongorestore on the master

Right after the restore, stop all mongod

Copy all local.x and mymongobase.x files to other nodes

Start mongod on all nodes?

I'm a bit lost here.

Ensure you have enough disk space!
Shutdown mongos on all 3 nodes
From Master node; tar all local* and mymongobase* files
SCP files to 2 secondaries
Unpack files in mongo data location (/var/lib/mongodb)
Start mongod on all nodes

Works for me


Django postgres multiple schema I'm trying to develop a django interface for an existing postgresql db, the db makes use of various schemas, looking at the literature the following example should work, but it only returns the schema defined in the default database when I run python manaage.py inspectdb. Also when this works, how do I define which schema to use when defining the django model?

DATABASES = {

'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS' : {
            'options': '-c search_path=public'
        },
    'NAME': 'gygaia',
    'USER':'postgres',
    'PASSWORD':'abc',
    'HOST':'localhost',
    'PORT':'5432',
},

'samples': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'OPTIONS' : {
            'options': '-c search_path=samples'
        },
    'NAME': 'gygaia',
    'USER':'postgres',
    'PASSWORD':'abc',
    'HOST':'localhost',
    'PORT':'5432',
},
    'excavation': {
            'ENGINE': 'django.db.backends.postgresql_psycopg2',
            'OPTIONS' : {
                'options': '-c search_path=excavation'
            },
        'NAME': 'gygaia',
        'USER':'postgres',
        'PASSWORD':'abc',
        'HOST':'localhost',
        'PORT':'5432',
    },
}

Here is a super easy project example with 2  apps and  3 schemes that cross each other with ForeignKey

https://github.com/mullerivan/DjangomultipleSchema
The idea is to define each model with the schema and  table  that need to use

class Meta:
    db_table = u'"app1\".\"on_app_one"'


Is it possible to backup users from MongoDB admin database? The title sums it up nicely.
Essentially what I am asking is, if its possible to backup (use mongodump perhaps?) only users (accounts created for human users)?

So after digging around further i found how to do that.

In short i needed to take the system.users collection or rather use a query to get only users from that collection that follow a certain pattern. for me it was user@some.domain, without the query i would have gotten all service users also which i did not want or need.

The tricky part was mongorestore which did not work if i wanted to restore only the system.users collection

The error i got was:

2020-01-28T12:44:49.902+0200    assuming users in the dump directory are from <= 2.4 (auth version 1)
2020-01-28T12:44:49.960+0200    Failed: the users and roles collections in the dump have an incompatible auth version with target server: cannot restore users of auth version 1 to a server of auth version 5
2020-01-28T12:44:49.960+0200    0 document(s) restored successfully. 0 document(s) failed to restore.

I got the error because mongorestore is unable to determine the version of the dump mongo if you dont give it system.version collection also.


How do I use 'N' in my prepared SELECT statement I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.

I'm trying to run the following query which searches the database for Chinese characters:

SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)

        $text = "%简况%";
            $query = $this->DBH->prepare($sql);
            $query->bindParam(':term1', $text, PDO::PARAM_STR );
        $query->execute();

This query returns results with random characters so it looks like the Character encoding is somehow incorrect.

I was able to return the same results by running this query directly on the DB using Heidi:

SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')

Changing the query to the following by adding an 'N' before the string returned the correct results:

SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')

But when I try to do the same with my prepared statement as shown below I get a syntax error:

SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)

        $text = "%简况%";
            $query = $this->DBH->prepare($sql);
            $query->bindParam(':term1', $text, PDO::PARAM_STR );
        $query->execute();

Here's the error:

  Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.

So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!

This returns the expected result:

$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);

But using the PARAM_STR_NATL constant doesn't return anything:

$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);

You should bind them using the (relatively new) PDO_PARAM_STR_NATL constant. This constant is available in PHP 7.2 and up. It will cause the desired N prefix to be added.

$query->bindParam(':term1', $text, PDO::PDO_PARAM_STR_NATL);

In case you're interested, some history can be found here:
https://bugs.php.net/bug.php?id=60818

The Github commit that is linked there shows how N is added (lines 178-184):
https://github.com/php/php-src/commit/4afce8ec8c6660ebd9f9eb174d2614361d1c6129


Which changes to a table in SSMS cause a DROP/CREATE and can this always be avoided with TSQL? I'm aware that there are some circumstances where making certain changes to a table (for example, changing the type of a column) through the GUI of SSMS will cause the table to be dropped and re-created.

My question is, does a complete definition exist of exactly what changes cause this behaviour? And can this behaviour always be avoided by running an ALTER TABLE instead of using the SSMS GUI (and if not, which can be avoided and which cannot)?

I doubt Microsoft has anywhere published the logic that is used to determine how SSMS will script a table change. It should be noted that the way SSMS has handled these has evolved over the years - I swear in earlier versions every single change would drop & re-create. Now it definitely tries to make much targeted adjustments.

I can't think of many instances where the only option is to DROP and CREATE. There are many scenarios where it would be the easiest for a human or SSMS to script, but most any situation can be solved by judicious uses of ALTER or dropping and re-creating constraints only. The one absolute exception to this is changing the order of columns - that does require dropping and re-creating the table.

One way to always be sure of what your change is going to do is, in the table designer, once you've made your changes, but before you've saved them, go to the "Table Designer" menu, and select "Generate Change Script." This will tell you exactly what SSMS is going to execute and can allow you to decide before you hit "Save" whether or not that's how you want it done.


ANALYZE command in Redshift fails I created a table in AWS Redshift (4-node dc1 cluster) in the following manner.

CREATE TABLE events
(
    event_id int,
    event_date int ,
    event_month_year int ,
    event_year int ,
    event_fraction_date numeric(10,4) ,
    event_actor1_code varchar(100) ,
    event_actor1_name varchar(100) ,
    event_actor1_country_code varchar(100) ,
    event_actor1_known_group_code varchar(100) ,
    event_actor1_ethnic_code varchar(100) ,
    event_actor1_religion1_code varchar(100) ,
    event_actor1_religion2_code varchar(100) ,
    event_actor1_type1_code varchar(100) ,
    event_actor1_type2_code varchar(100) ,
    event_actor1_type3_code varchar(100) ,
    event_actor2_code varchar(100) ,
    event_actor2_name varchar(100) ,
    event_actor2_country_code varchar(100) ,
    event_actor2_known_group_code varchar(100) ,
    event_actor2_ethnic_code varchar(100) ,
    event_actor2_religion1_code varchar(100) ,
    event_actor2_religion2_code varchar(100) ,
    event_actor2_type1_code varchar(100) ,
    event_actor2_type2_code varchar(100) ,
    event_actor2_type3_code varchar(100),
    is_root_event varchar(100) ,
    event_code varchar(100) ,
    event_base_code varchar(100) ,
    event_root_code varchar(100) ,
    event_quad_class int ,
    event_goldstein_scale float ,
    event_num_mentions int ,
    event_num_sources int ,
    event_num_articles int ,
    event_avg_tone float ,
    event_actor1_geo_type int ,
    event_Actor1_Geo_Full_Name varchar(500) encode lzo,
    event_Actor1_Geo_Country_Code varchar(100) ,
    event_Actor1_Geo_ADM1_Code varchar(100) ,
    event_Actor1_Geo_Lat float ,
    event_Actor1_Geo_Long float ,
    event_Actor1_Geo_FeatureID varchar(100) ,
    event_Actor2_Geo_Type int ,
    event_Actor2_Geo_Full_Name varchar(500) encode lzo,
    event_Actor2_Geo_Country_Code varchar(100) ,
    event_Actor2_Geo_ADM1_Code varchar(100) ,
    event_Actor2_Geo_Lat float ,
    event_Actor2_Geo_Long float ,
    event_Actor2_Geo_FeatureID varchar(100) ,
    event_Action_Geo_Type int ,
    event_Action_Geo_Full_Name varchar(500) encode lzo,
    event_Action_Geo_Country_Code varchar(100) ,
    event_Action_Geo_ADM1_Code varchar(100) ,
    event_Action_Geo_Lat float ,
    event_Action_Geo_Long float ,
    event_Action_Geo_FeatureID varchar(100) ,
    event_date_added int ,
    event_source_url varchar(500) encode lzo
)
diststyle key
distkey(event_id);

I performed a COPY of around 60k records.

Post that, I performed a VACUUM and ANALYZE. It fails at the ANALYZE command with the following error:

[Amazon](500310) Invalid operation: index "pg_toast_16408_index" is not a btree;

I tried dropping and re-creating. But I get the same error. But once I change the diststyle to ALL, then the error disappears.

Any help on this would be very much appreciated.

A restore of the snapshot database resolved the error. Looks like the index was corrupted.


Create Linked Server using Active Directory account I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my personal account and the service account, from MSSQL and from .Net exe.

Now I need to configure a Linked Server on another instance to this one, using this AD service account.

For creating a Linked Server with local MSSQL account, I just open New Linked Server dialog, provide MSSQL host, set server type to MSSQL, in Security set Be made using this security context, provide username and password, and in Server Options set true to RPC and RPC Out.

But when I try to do that with an AD account, I get an error saying that login failed.

Any idea how to configure a Linked Server using AD account?

The two most common scenarios for security via a linked-server are:

Create a SQL login on the target SQL Server, and use that login in the definition of the linked server.  You then GRANT and DENY access to that login on the target server objects as necessary, using the principle of least privilege.

Define the SQL Server to allow "pass through" authentication by configuring security to "be made using the login's current security context".   This allows security to vary amongst users and is far more flexible.  From MSDN:

Instead of having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query to connect to a linked server when all the following conditions exist:

A user is connected to SQL Server by using Windows Authentication Mode.

Security account delegation is available on the client and sending server.

The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.

In order to use option #2 above, in a multi-hop authentication scenario, you'll need to enable several things to support Kerberos Authentication.  MSDN has a great blog on the requirements, available here
You may also want to look at this question.


Storing ~700 Tb in SQL Db I am a newbie to Db's on a whole, and I came across a tutorial, wherein we needed to store ~700 Tb of data over a few years. It was mentioned that scaling SQL for such limits of data is not the best approach, and hence in the tutorial they went ahead with NoSQL. I have a few doubts regarding this:

Is it possible to store such Tb's in a single machine, as per my understanding, horizontal scaling is not possible in SQL. Please correct this if I am wrong.
What will be the latency comparison of SQL vs NoSQL. Because of horizontal scaling, will a noSQL db like DynamoDB give lower latency than a SQL Db.

Is it possible to store such Tb's in a single machine

Definitely. Though that machine becomes a large single-point-of-failure.
Building a petabyte scale storage system that is efficient and fault tolerant can itself be a challenge, doing it with cost efficiency too even more so. I assume that being a "newbie as DBs" also means you are not particularly experienced with storage solutions either, so you don't want to be trying to put together a custom build.
A not un-common arrangement is to have a couple of clustered machines running your chosen DBMS (SQL Server etc.) using an "off the shelf" SAN based storage system. I did a quick calc with the first search result for "petabyte SAN" and a ~700Tb arrangement came in at around £40,000 and that is just the storage unit and drives to populate it: add onto that racks to hold it all, decent spec machines for the clustered database servers, the enterprise licensing that is likely to be needed, ... This level of storage is never going to be cheap. You also need to factor in the ongoing costs: electricity to power it all and the required air conditioning too, replacing drives when they fail, and also the man-power to monitor and maintain it all!
You would almost certainly be better off looking at managed solutions, perhaps with one of the bigger cloud providers, and let them worry about much of the scaling, redundancy, reliability, power, etc. problems for you. For this scale you are unlikely to find an "off the shelf" price so you'll need to talk to them directly. Be warned: this amount of storage, particularly for rapid access, with good resiliency (in terms of both data security and service availability) is not going to be remotely cheap, there is no way it can be.

as per my understanding, horizontal scaling is not possible in SQL. Please correct this if I am wrong.

It is possible, though whether it is practical compared to other options depends on your data and access patterns.

What will be the latency comparison of SQL vs NoSQL.
Because of horizontal scaling, will a noSQL db like DynamoDB give lower latency than a SQL Db.

All of the above can only really be answered with "it depends" without a lot more information about the data your are needing to store, and at least a vague idea of expected retrieval patterns and other requirements.
Just knowing the size and expected growth of the data is not nearly enough for a more detailed discussion.


Database subsetting a viable method of slicing a database? Interested in getting a smaller copy of a database for testing purposes as restoring the full database takes hours, that would include all rows from tables referenced. Trying to do this manually would seem impossible.

I've seen a few tools out there that do this, but not sure which one would work with SQL Server? And perhaps I'm going about this all wrong and should be trying to do something different.

Any experience using data subsetting or anything else I should be doing instead?

Thanks for your help in advance.

Database subsetting is a great substitute for testing when you're dealing with a very large database or you want to limit the exposure of a production dataset. I would not recommend trying to do this manually, as you point out, unless you're dealing with a very simple schema (1-3 tables). Most commercial databases have tools for subsetting (e.g. Oracle does) although MS SQL Server surprisingly doesn't. There are a number of decent open source projects out there (e.g. Jailer, Condenser, RDBMS-Subsetter) that are good, but not quite out-of-the box.

A few additional caveats to keep in mind:

If you select too small of a subset, you may miss outliers and important edge cases. If you know of a particular set of records that you want in your test set, you should explicitly include these as part of subset.
You want to select an unbiased subset. Don't pick a geographic region or a fixed attribute to segment your subset population. I recommend you pick a primary table (e.g. users) and a randomly selected percentage of that table.
Scale testing does not always scale linearly. Don't assume that you can simply 100x the performance you measure on 1% of the original database.

(note: I'm a contributor to an open source database subsetting tool, you can find out more about it here: https://www.tonic.ai/blog/condenser-a-database-subsetting-tool)


How to access different MySQL databases in a same instance in Amazon RDS? In my Amazon RDS Dashboard, the configuration details of my DB instance (running MySQL) mentions that my DB name is ebdb, which is the default one, I haven't changed it. It's the same screen as here, instead that I don't want to change the instance name.

I've managed to connect to this DB using MySQL Workbench and I've imported a schema carpool which now lies alongside the existing ebdb one:

In the config of my app I've specified this DB url: jdbc:mysql://${DB_HOST}:3306/carpool, DB_HOST being the endpoint specified in the RDS Dashboard.

If my app tries to access the DB it throws an error because it can't find the table. I assume that's because it looks in the wrong database.

In the RDS Dashboard, placing the cursor on the label DB Name gives some info, including An Amazon DB instance can host multiple databases. Then why is the name of the database hard-coded ebdb ?

How can I access another database in my RDS instance ?

Well, I was living in a lie.

The simple reason why I couldn't access my table on RDS was because RDS runs on Linux and MySQL table names are case-sensitive on Linux, while my local DB runs on Windows where MySQL table names are not case-sensitive.

About multiple databases in a single RDS instance: RDS does accept multiple databases. It's just that, for an unknown reason, only the initial database of the instance is always displayed in the dashboard. This still doesn't make sense to me because this database has nothing more than other ones, apart being the first one.


PowerShell command 'New-Item' fails when invoked through SQL Server Agent job I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing.

The problem is the script works fine when I manually execute. It also executes fine when I execute it as SQL Server Agent service account. But the same script fails when I try to execute as part of SQL Server Agent job. Am I missing something here?

$path = "\\server\shared\path\01222020"
New-Item -path $path -ItemType Directory -Force

Exception:
Date        1/22/2020 5:25:10 PM
Log     Job History (test)

Step ID     1
Server      serverA
Job Name        Test
Step Name       Test
Duration        00:00:01
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: domain\agent_service_account. A job step received an error at
line 9 in a PowerShell script. The corresponding line is 'New-Item -path $path
-ItemType Directory '. Correct the script and reschedule the job. The error
information returned by PowerShell is: 'Invalid Path:
'\\server\shared\path\01222020'.  '.  Process Exit Code -1.  The step failed.

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation.

Alternatively, you can change directory beforehand to a local drive (cd C:) and it should then work without prefixing the provider name, but you may want to stay in the default SQLSERVER:\SQL\SERVERNAME\INSTANCENAME path depending on what your script requires.

More info:

https://dbatools.io/agent/

https://social.technet.microsoft.com/Forums/windowsserver/en-US/ec7f3ae8-d196-459e-b9dc-e6ed0df93004/running-powershell-from-sql-server-using-unc-paths


Want to get vat-amounts in invoice table I have three tables:

  tblInvoices: fldInvoiceID, fldInvouceDate, fldInvoiceCustomer

  tblInvoiceItems: fldInvoiceID, fldItemID, fldItemQty, fldItemProdID, fldVatID

  tblVatRates: fldVatRateID, fldVatRate

with the following records:

1, 0;
2, 6;
3, 12;
4, 21;

Now I want to print at the bottom of my invoice a list of total vat-amounts per fldVatRate.
Example:
0 % - 12.00 €
6 % - 25.65 €
12 % - 0.00 €
21 % - 5.19 €
I've try a lot of t-sql statements but never get the real thing. The last t-sql give me the desired outpu BUT the 12% is not mentionde while it is 0. How can I get the wanted output ?
The sql untel now:

SELECT [HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblInvoiceDetail.fldItemQty,
   [HJ-Admin].tblInvoiceDetail.fldItemUnitPrice, [HJ-Admin].tblInvoiceDetail.fldLineTotal,
   [HJ-Admin].tblVatRates.fldVatRate, [HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100 AS fldVatAmount,
   [HJ-Admin].tblInvoiceDetail.fldVatRateID

FROM   [HJ-Admin].tblVatRates LEFT OUTER JOIN
       [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID

Result:

As you can see the Vat-rate 6% (ID = 2) is mentioned twice while it shoulde be once with the total of the two records (= 1.725000). The NULL-values are just fine to see the amount is NULL.

I would like an output like this:

It should be fairly simple then:

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate

or with a total rows as well:

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
WITH ROLLUP

Or with the grouping that you are after (only an overall total row, no totals per grouping level):

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY GROUPING SETS(
        ([HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblVatRates.fldVatRate)
        , ()
    )

With InvoiceID:

SELECT
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
WHERE [HJ-Admin].tblInvoiceDetail.fldInvoiceID = @intInvoiceID
GROUP BY GROUPING SETS(
            ([HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblVatRates.fldVatRate)
            , ()
        )

Try to put the InvoiceID into the where, will be more performant.

If you want to also include all non matching rows from the tblVatRate table:

WITH Inv AS (
    SELECT
        r.fldVatID
        , r.fldVatRate
        , SUM(i.fldLineTotal * r.fldVatRate / 100) AS fldVatTotalAmount
    FROM [HJ-Admin].tblVatRates r
    INNER JOIN [HJ-Admin].tblInvoiceDetail i ON r.fldVatID = i.fldVatRateID
    WHERE i.fldInvoiceID = @intInvoiceID
    GROUP BY GROUPING SETS(
                (r.fldVatID, r.fldVatRate)
                , ()
            )
)
SELECT ISNULL(i.fldVatID,@intInvoiceID) AS 'fldVatRateID', r.fldVatRate, SUM(ISNULL(i.fldVatTotalAmount,0)) AS 'fldTotalVat'
FROM [HJ-Admin].tblVatRates r
LEFT OUTER JOIN Inv i ON r.fldVatRate = i.fldVatRate
GROUP BY GROUPING SETS(
                (r.fldVatID, r.fldVatRate, i.fldVatID)
                , ()
            )


TSQL change column constraint between null and not null I am doing a exercise to change a column's constraint between null to not null.
Table name: Production.categoriesTest. Please review my question and give me suggestion
on what I did wrong and how to fix the issue. Thanks!

when table was created, the description column is set to NOT NULL
I used the update clause to set description column to NULL
When using alter table command to set the column description back to NOT NULL, I received an error message.

Error message:

  Msg 515, Level 16, State 2, Line 98
  Cannot insert the value NULL into column 'description', table 'TSQL2012.Production.categoriesTest'; column does not allow nulls. UPDATE fails.

Create table command:

 create table Production.categoriesTest
    categoryid INT not null identity,
    categoryname nvarchar(15) not null,
    description nvarchar(200) not null,
    constraint PK_Categories1 Primary Key (categoryid)

--update clause make all description column becomes 'NULL"
  update Production.categoriesTest
  set description = NULL
  where categoryid = 8;

The description column became 'NULL'

I did insert a value for description column in the able after the column set to Null.

When trying to return the column back to NOT NULL SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL.

For an existing column, you have to update the table to remove any NULL values before you can set it as NOT NULL.


How to fix indexes to reduce the fragmentation percentage I need your guidance and help to understand a SQL topic that I've never considered before but now I'm trying to build tables thinking on performance, one of the topics is Fragmentation but after reading some website data I need more knowledge.
The code below will create a new table , that table will have a clustered index as the primary key  but also it will have a non clustered index for a column called HosttTimeStamp which is basically a DATETIME that storage the transaction date . The problem is that both indexes are using 98% of fragmentation and I'm looking a way to reduce that when new data gets inserted
USE [DB1]

IF OBJECT_ID('dbo.ProductionTable', 'U') IS NULL BEGIN
CREATE TABLE dbo.ProductionTable
(
    [Tier1CustomerAccountID] INT NOT NULL,
    [Tier1CustomerBusinessName] VARCHAR(255) NULL,
    [Businessname]NVARCHAR(50) NULL,
    [AccountID] INT NOT NULL,
    [RetailerAcc]VARCHAR(10) NULL,
    [Product] VARCHAR(10) NULL,
    [Title] NVARCHAR(50) NOT NULL,
    [PlanName] VARCHAR(200) NULL,
    [PlanValue] NUMERIC(18,3) NOT NULL,
    [Hosttimestamp] DATETIME NOT NULL,
    [TerminalID] INT NOT NULL,
    [TxnIDType] CHAR(3) NOT NULL,
    [Region] VARCHAR(255) NULL,
    [State] VARCHAR(255) NULL,
    [RetailerAccTxnID] BIGINT NOT NULL,
    [TxnType] CHAR(3),
    [FirstTxnDate] DATETIME NULL,
    [Classification] VARCHAR(50) NULL
PRIMARY KEY CLUSTERED
(
    [RetailerAccTxnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

DECLARE @tableName varchar(max) = 'dbo.ProductionTable';
DECLARE @indexName varchar(max) = 'IX_ATTActivationsData_Hosttimestamp';
DECLARE @indexDefn varchar(max) = '([Hosttimestamp] ASC)';
IF NOT EXISTS(
    SELECT * FROM sys.indexes
    WHERE Name=@indexName AND OBJECT_ID = OBJECT_ID(@tableName) ) BEGIN

    EXEC('CREATE NONCLUSTERED INDEX ' + @indexName + ' ON ' + @tableName + @indexDefn)

END;

I'm new on the index topic so I was thinking that maybe my non cluster index is wrong, that instead of being " on dbo.ProductionTable (HostTimeStamp) " must be something like  "on dbo.productionTable (RetailerAccTxnID) INCLUDE (HostTimeStamp). The reason is because if I'm not wrong the word "ON" is 100% more for table joins and there will be multiple queries pointing to this table using the HosttimeStamp for the "WHERE" conditions

Check out the IndexOptimize job from Ola Hallengren
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics.
Example (Able to run once you've ran MaintenanceSolution.sql from Ola Hallengren)
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30


Hypothetical: Security Intrusion Had a random thought I was curious about.  In the case of a security breach where a user gains access to the DB server through legitimate credentials (social engineering, phishing, fired employee, etc) what are some quick and effective methods of locking down access?

Is it as simple as removing 'Connect SQL' from the securables for the login in question?

Simply "disable" the login:

ALTER LOGIN [xyz] DISABLE;

Then, if the login is a SQL Server login, you can change the password with:

ALTER LOGIN [xyz] WITH PASSWORD = '$ome$ecurePa$$w0rd';

Then, re-enable the login:

ALTER LOGIN [xyz] ENABLE;

If the login is from Active Directory, you can still disable the account (and re-enable later), but you'll need to have the password changed in Active Directory.

Note that disabling the login does not disconnect anyone currently connected as that login. In order to do that, you'd need to kill the spids associated with the login.


postgres \dt functionality change from v9.4 to 9.6? I have a postgres 9.4 database and when I use the:

\dt

command, tables in the 'public' schema, and other user-created schemas are shown.

I have the exact same database on a Postgres 9.6 server and when I use:

\dt

only the tables in the 'public' schema are shown, and in order to see tables in the other user-created schema, i have to use:

\dt schemaname.*

to see those tables. Is this a difference between 9.4 and 9.6, or any i missing something else. permissions?

Check the search_path for each user you're trying to check on.

SHOW search_path;

If they're different you can set them with SET search_path TO mySchema, public;