Use SHA1 as custom id in mongodb I have to save a lot of documents that I'll show on a web and I have a Mongo database to save them.

I know that Mongo can autogenerate a random ID for each element in a collection and I also know that you can create a hashed index then to increase the speed search.

I'll use the document SHA1 hash (or maybe other) as identifier in the web to each document, and I was wondering if it could be a good idea to use it as custom index on Mongo.

It doesn't matter if someone makes the SHA1 to a random document to search it on the web, I mean, the security it's not a problem because the documents will be public.

I saw that other people have used custom index and they say that it's useful

In my opinion, doing it would  have these advantages:

It's a faster way to find documents, because you can search by the documet hash and not by whole document.
The ID it's already an index so you will save one index.
You may save some storage because you don't need to save the hash separately.
The SHA1 size is smaller than default Mongo id, so you will be able to have more index in memory at same time.

And as disadvantages:

You have to manage the collisions manually. (I may use a "bigger" hash function)
You need to generate the hash by yourself.
it will be harder to short the documents and you may have extra keys.

But there are some disadvantages?

I know that Mongo can autogenerate a random ID for each element in a collection and I also know that you can create a hashed index then to increase the speed search.

Hashed indexes in MongoDB are typically used to create a suitable shard key for data distribution where the original field value is monotonically increasing (for example, default ObjectIDs). The underlying index format is still a B-Tree, so a hashed index does not change the speed or complexity to look up an index value.

  I'll use the document SHA1 hash (or maybe other) as identifier in the web to each document, and I was wondering if it could be a good idea to use it as custom index

ObjectIDs are pseudorandom identifiers designed to be independently generated and reasonably unique with roughly ascending order (based on a leading timestamp component); SHA1 hashes require unique input values and are unordered. ObjectIDs are generated by clients/drivers by default.

Using a custom _id format is fine, but you should consider desired attributes like uniqueness (required for a primary key) as well as ordering (depends on your use case) and independent generation (required for effective scaling of a distributed deployment).

  It's a faster way to find documents, because you can search by the ; hash and not by whole document.

As mentioned above, this is not a faster search solution. If you already know the primary key (_id value), looking it up as a hash value or as an ObjectID is equivalent.

  The ID it's already an index so you will save one index.

True.

  You may save some storage because you don't need to save the hash separately.

_id is already a unique reference, so you wouldn't necessarily need another unique value.

  The SHA1 size is smaller than default Mongo id, so you will be able to have more index in memory at same time.

A normal SHA-1 hash is 20 bytes, which is 8 bytes more than an ObjectID. Perhaps you are thinking of the truncated SHA-1 hashes used by git (typically around 7 bytes). I'm not sure what the relative probability of collisions would be, but smaller key spaces increase the possibility.

There are also factors to consider other than the size of the _id value. Indexes in MongoDB use prefix compression by default, which is helpful when you have a values like ObjectIDs which start with 4 byte timestamps. Indexes with random values may end up creating larger data structures.

For an example comparison of index prefix compression, this older blog post from John Page might be useful: How to reduce your MongoDB hosting costs.

The best way to test the impact of data model changes would be to generate some representative test data for your planned approach. There are mock data tools that can help with creation of plausible data (for example, mgeneratejs).


Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL) Loan table associated to due table.

also

Invoice table associated to due table.

In due table either one of them foreign key should present and other one should be null.

What kind of constraint will be perfect ?

Thanks.

As it sounds, you cannot solve this with one constraint, but nothing prevents you from defining more of them.  I believe something like this should work:

CREATE TABLE due (
    ...,
    d_invoice_id REFERENCES invoice (i_id),
    d_loan_id REFERENCES loan (l_id),
    ...,
    CHECK (   (d_invoice_id IS NULL AND d_loan_id IS NOT NULL)
           OR (d_invoice_id IS NOT NULL AND d_loan_id IS NULL))
);

A column that has a foreign key defined on does not have to be NOT NULL, unlike the column it references.  Apart from that, you may define checks of arbitrary complexity - it is advisable to keep complexity at its necessary minimum, though.


Grant access on Secondary but not on primary in SQL Server logshipping I have a strange situation wherein users need permission on log shipped DB  on secondary server but not on primary. The problem is, the login has access on many other DBs on primary server apart from log shipped DB and that needs to be maintained. My environment is SQL Server 2012 EE.

Is it possible to achieve this?

As a workaround what I have tried is granting temporary access on my primary DB just before the backup log-shipping starts and once the backup completes I have added a step to remove it. But the issue with this workaround is, the users can still access the primary log shipped DB when I try to grant them access just before the log backup. Any help would greatly be appreciated.

On Primary

Create a new Login [testLogin] and a new User [testUser] with the requiered rights and roles:

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'test123'
    , DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [Test]
GO
CREATE USER [testUser] FOR LOGIN [testLogin];
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testUser];
GO

These login and user must not exist and be different from your current login and user.

Once [testUser] is available on Secondary, remove [testLogin] from Primary:

USE [master]
GO
DROP LOGIN [testLogin];

  Other option on Primary

Remove the orignal Login

You must plan your update if you have to remove login for a short time and make sure you disconnect them first.

Create a new Login with the same name

CREATE LOGIN...

Link Orphan users to the newly created Login for all DBs except the log shipped DB:

USE [DB1]
GO
sp_change_users_login @Action='update_one'
    , @UserNamePattern='testUser'
    , @LoginName='testLogin';

  On Secondary [testLogin]:

Create a new login [testLogin]:

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'test123'
     , DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

Link the orphan user [testUser] (shipped from Primary) to the new login [testLogin]:

USE [Test]
GO
sp_change_users_login @Action='update_one'
    , @UserNamePattern='testUser'
    , @LoginName='testLogin';


Drawing an entity-relationship diagram for a movie sales business domain I want to keep track of movies that exist in different formats (for example, DVD, Bluray, etc.). The price for each format differs from the others as well as the quantity of each format, so I came up with the entity-relationship diagram (ERD) shown below:

Is this correct from a design perspective? Does this implies redundancy? I don't understand how will this be retained in a table when implemented in a relational database.

Would it be better to do it like in the diagram that follows:

Additional information

Here's more descriptive information about what I want to store at this point of the design:

I want to store information about sales.
For each movie that exists in the company I need to keep format, price and stock quantity.
I will also need to maintain customer information with a unique id, name, surname, address, movies that he/she has already bought and his/her credit card number.
Finally, I will have a basket that temporary keeps items (let's suppose that other items exist apart from movies) that the customer wants to buy.

I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.


Encryption with pgcrypto I am working on encrypting a column in a postgre table using pgcrypto. The postgre version is 9.5.1. I have figured out how to encrypt using pgp_sym_encrypt() and decrypt using pgp_sym_decrypt(). My problem is figuring out how to keep the key hidden.

I am trying to avoid having the application decrypt the data that is returned. The data in this table is only sensitive if someone can view the entire table (450,000 rows) and put the data together, so its ok for the data to return to the application decrypted.

In pgAdmin, I have this in my query

 SELECT pgp_sym_decrypt(column1,'password')

Obvisouly, I don't want the query template our application uses to have the password stored in it and then pass it in everytime a query is ran. Any ideas would be appreciated?

There is no way to avoid the drawbacks of symmetric encryption on the server.

PostgreSQL has to have access to the key and the encrypted data to decrypt it if you want to "avoid having the application decrypt". There are only two sources that can do it.

the consumer
the database (in which case it's also subject to being written in a log file)

Of course, if you have a single access point to the data like an webserver or the like, you could potentially decrypt there too and keep the keys off the server.


Deduplication/normalization of string column on insert I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text repeats exactly. The DB gets into several GBs size, so I think it's quite big for sqlite standard, I'm a noob here though.

Anyways, since strings in individual columns do repeat quite a lot, but combinations are original, I thought I can have tables for distinct original strings, and combinations just as tuples of foreign keys (I believe that's called normalization), then a VIEW, preserving all API, for reading.

The question is, can I implement deduplication mechanism on the DB side for insert?

I thought of something like INSERT on (text_column1, text_column2, text_column3) and then write some kind of INSTEAD OF INSERT trigger, that would split it into 3 INSERT IF NOT EXISTS commands + 1 insert into relation table. But I don't think it is even possible to have different "interface" and "storage" schema. I certainly failed to write it.

I have supplementary questions which tightly related (thus not worthy of separate entries, I believe):

Maybe sqlite3 already does string deduplication behind the scenes? (I doubt it, since mutable strings would complicate things a bit. Nothing undoable though.)
If it's hard then maybe it's a bad idea for some reason?

If it helps my data is read-only once inserted.

I've read:

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?
Implement a deduplication trigger in Oracle
Automatic deduplication (normalization) of strings in MySQL

But they regard different SQL systems, and don't really answer general question. I gather that this is not a popular problem and solution.

Here is a rough sketch. I could only test with SQLite 3.8 and it appears as if UPSERT is introduced in 2018-06-04 - Release 3.24.0. I.e. the following is untested, but hopefully you can make something out of it anyhow

create table Texts
( tid integer not null primary key AUTOINCREMENT
, textval varchar(20) not null unique);

create index x1 on texts (textval);

create table T
( x int not null
, tid int not null references texts (tid)
, primary key (x, tid) );

create view v as
   select t.x, texts.textval
   from t
   join texts
       on t.tid = texts.tid
;

CREATE TRIGGER trig1
INSTEAD OF INSERT ON V
BEGIN
        -- insert unless textval is already in place
        INSERT INTO texts (textval)
        VALUES (NEW.textval) ON CONFLICT (textval) DO NOTHING;

        -- lookup tid for textval
        insert into t (x, tid)
        select NEW.x, texts.tid
        from texts where texts.textval = NEW.textval;

END;

rowid appears to be recommended over autoincrement, but that is sort of beside the point so I used it anyhow.

For 3.8 I used this ugly workaround:

CREATE TRIGGER trig1
INSTEAD OF INSERT ON V
BEGIN
        -- insert unless textval is already in place
        INSERT INTO texts (textval)
        SELECT NEW.textval FROM (VALUES(1))
        WHERE NOT EXISTS (
            SELECT 1 FROM texts WHERE textval = NEW.textval
        );

        --lookup tid for textval
        insert into t (x, tid)
        select NEW.x, texts.tid
        from texts where texts.textval = NEW.textval;
END;

You can try it at DB<>Fiddle. Test:

insert into V (x,textval) values (1,'a');
insert into V (x,textval) values (5,'bb');
insert into V (x,textval) values (15,'a');

select * from v;
x   textval
1   a
5   bb
15  a

select * from texts;
tid textval
1   a
2   bb

select * from t;
x   tid
1   1
5   2
15  1


Is there an operator or an easy way to match an expression one or more times with the LIKE operator in SQL? For example if I run the following query, the expression in the WHERE clause will only match on the first letter before a number but is there a way to match on one or more letters first (e.g. in the case where there's a variable number of letters before the number in the field I'm filtering on)?

SELECT FilteredField
FROM Table
WHERE FilteredField LIKE '[a-zA-Z][0-9]'

Example data:

ABC9
DEF2
GH7
Z1
XYH2

Essentially I'm looking for the SQL Server 2016 equivalent of the RegEx + when using the LIKE operator.

If you are talking about solving this purely in Transact-SQL, you can cover all those cases in your example data with the following filter:

WHERE FilteredField LIKE '%[a-zA-Z][0-9]'

If you want to additionally stipulate that all the characters before the numeral must be Latin letters, you will need to get a little creative:

WHERE FilteredField     LIKE           '%[a-zA-Z][0-9]'
  AND FilteredField NOT LIKE '%[^a-zA-Z]%[a-zA-Z][0-9]'

Basically, you are saying:

  The value must end with a letter and a numeral, but whatever precedes the letter must not be a non-letter.

There is no way to specify this with a single condition using only built-in syntax, if that was what you were after.


What difference does it make if my COLLATION_CONNECTION is utf8mb4_0900_as_cs or utf8mb4_0900_ai_ci My default collation_connection was 'utf8_generic_ci' and it caused an issue today with a polish character not fitting, thowing an error (the table has utf8mb4_0900_as_ci)

So I changed the connectin to utf8mb4_0900_as_cs but I don't know if that can affect performance, matching or returned data beyond the utf8 encoding.
Is there any difference between ci and cs in the connection collation ?

I of course understand the difference in column/table collation.

Changing the collation of the connection will have little to no impact on performance, but also on what you probably want to achieve. From the manual:

  collation_connection is important for comparisons of literal strings.
  For comparisons of strings with column values, collation_connection
  does not matter because columns have their own collation, which has a
  higher collation precedence

If you want to make comparisons in with or without case sensitivity/accent sensitivity, you will have to update the actual column's collation, or force the collation on every comparison manually (e.g. WHERE ). The latter may have low impact if no index is required, or a huge impact if the usage of an index is prevented because the comparison and the column use different collation.

In practice:

mysql> SET NAMES utf8mb4; -- we will be using this charset to send literals
mysql> SELECT 'A' = 'a';
+-----------+
| 'A' = 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.000 sec)

mysql> SET SESSION collation_connection = 'utf8mb4_bin';
Query OK, 0 rows affected (0.000 sec)

mysql> SELECT 'A' = 'a'; -- it works on literals, but you will have to force it manually on columns
+-----------+
| 'A' = 'a' |
+-----------+
|         0 |
+-----------+
1 row in set (0.000 sec)

mysql> MariaDB [(none)]> SELECT 'A' = 'a' COLLATE utf8mb4_bin; -- may prevent index usage on columns
+-------------------------------+
| 'A' = 'a' COLLATE utf8mb4_bin |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.000 sec)

mysql> select 'A' = 'a' COLLATE utf8mb4_unicode_520_ci;  -- may prevent index usage on columns
+------------------------------------------+
| 'A' = 'a' COLLATE utf8mb4_unicode_520_ci |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set (0.004 sec)


Does the target database need to have the same structure of the source database before to use IMPDP? When I do a import from another database have I need the same estruture of the source database in the target database?
For example, if my source database use ASM and my target database no, what have I to do before to make the import?
In the last time I received an error during the import. The error was about the creation of a tablespace and the datafiles. The erro said that the path of the datafile does not exist. For this problem I used the remap_datafile. But I wonder if is there another way to do it without this parameters. Mainly if there are several datafiles
In the last time I didn't use ASM, it was file system on different machines
My scenery is a full export and import

The new database can have a different directory structure and even storage system, but you'd need some preparation before running the import. Especially, you'd need to create the tablespaces in advance with the correct (new) storage parameters (and of course the required size). Then impdp would not have to create them (an error might still be shown, but import should proceed).
Make sure to give the tablespaces the same names (or use the corresponding REMAP parameters on import). No need to apply quotas etc, that the import should take care of then (when creating the users).


Oracle JDBC TLS connection fails (handshake_failure) after OS upgrade I have an Oracle Database 12.1 server running on Oracle Linux 6.

Secure (TLS) connections were working fine until I decided to update the underlying operating system (yum update) and reboot the server. A total of 62 packages were updated (yum update log) including the kernel and OpenSSL.

Now, secure connections, from the JDBC clients and from SQL*Plus fails with handshake_failure or ORA-28860.

I have a verbose TLSv1.2 log from my JDBC client but I can't figure out what is wrong with the TLS connection.

listener.ora and sqlnet.ora

sqlnet.ora and listener.ora do not contain SSL_CIPHER_SUITES

I am sure that nothing was changed or updated on the client (either JDBC or SQL*Plus). Few minutes before the upgrade/reboot, there was no problem establishing the TLS connection.

The server runs Oracle 12.1.0.1. The TLS connection issue occurs with all tested clients which previously succeeded in establishing a secure connection:

ojdbc8.jar 12.2.0.1
ojdbc7.jar 12.1.0.2
SQL*Plus 12.1.0.2.0.

The SQL*Plus trace file contains an ORA-28862 ("peer closed the connection") which may be more specific than the previous ORA-28860. I will try to get the same trace file on the server side.

Restarting the listener solved the issue... I can't understand what happened, as the listener was already restarted during the server reboot.

I should have tried that before.

Thanks to everyone who has taken time to help.


Mysql find 10 nearest point from given lat/long per type? I have a database given with 3 different types of locations A, B and C. These points have latitude and longitude information with them.

Now I have a random lat-long given and I want to find the closest 10 points per type to that given location - how can I do that? Anyone has an idea?`

Thanks for any help

Searching for the nearest 10 points of interest gives, usually, near points. In that case the usual cartesian distance (SQRT((LAT1 - LAT2)^2 + (LONG1 - LONG2)^2)) can be used.

Given a generic table (for the definition of latitude and longitude I've used this pointer.)

CREATE TABLE POI (
   ID INT IDENTITY(1, 1)
 , `Name` Varchar(100)
 , `Type` CHAR(1)
 , `LAT`  Decimal(10, 8)
 , `LNG`  Decimal(11, 8)
)

The query would be something like

DECLARE myLAT Decimal(10, 8) DEFAULT 45.48561;
DECLARE myLNG Decimal(11, 8) DEFAULT 123.47981;

(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'A'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'B'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)
UNION ALL
(SELECT `Name`, `LAT`, `LNG`
 FROM   POI
 WHERE  `Type` = 'C'
 ORDER BY SQRT(POWER(myLAT - `LAT`), 2) + POWER(myLNG - `LNG`), 2)
 LIMIT 10)

In case the point of interest are few and far from the 'here' point and from themselves you have to use a different formula for the distance, to account that the earth is, more or less, a sphere.


"Filter:" instead of "Index cond:" in index production usage An index to optimize fetching multiple external identifiers and update underlying data was created. The index is a simple btree of the fields. The external identifier needs to be a string as it depends on the source the data.
create index reports_collector_existing_index
on reports(provider_id, connection_id, id_on_site);

The query is running very slow in production (PostgreSQL 13.2 in Amazon RDS), and the explain indicates that the index isn't fully being used with the identifiers field in it being ignored and instead it is going to the table via Filter:, as described below:
production=# explain analyse SELECT * FROM reports WHERE (("provider_id" = 38) AND ("connection_id" = 1175966)
AND ("id_on_site" IN ('104992038676612727711', '109990966674014073837', '105453161196787392789', '102255741242869923419', '104173931474885108430', '106078528214550610325', '115918069366580690259', '101145134508987068276', '115359007991887431873', '115909079192172351351', '109019484929659145996', '114223295457618192193', '100701806344027988654', '116172834178410664645', '109271042536972421195', '112763372050251071013', '114228853909813896127', '100378649534685813241', '109853822973690938225'));

 Index Scan using reports_collector_existing_index on reports  (cost=0.70..2.78 rows=1 width=695) (actual time=1588.672..78750.175 rows=50 loops=1)
   Index Cond: ((provider_id = 38) AND (connection_id = 1175966))
   Filter: (id_on_site = ANY ('{104992038676612727711,109990966674014073837,105453161196787392789,102255741242869923419,104173931474885108430,106078528214550610325,115918069366580690259,101145134508987068276,11535900799188743187>
   Rows Removed by Filter: 3675
 Planning Time: 2052.299 ms
 Execution Time: 78750.268 ms
(6 rows)

The same database dumped into a local environment (although excluding more than 1tb of tables' data for the sake of development) is used to run the same query. The explain shows then proper index usage:
development=# <same query from above>
 Sort  (cost=210.14..210.15 rows=6 width=615) (actual time=0.207..0.209 rows=50 loops=1)
   Sort Key: id_on_site
   Sort Method: quicksort  Memory: 43kB
   ->  Index Scan using reports_collector_existing_index on reports  (cost=0.41..210.06 rows=6 width=615) (actual time=0.038..0.177 rows=50 loops=1)
         Index Cond: ((provider_id = 38) AND (connection_id = 1175966) AND (id_on_site = ANY ('{104992038676612727711,109990966674014073837,105453161196787392789,102255741242869923419,104173931474885108430,106078528214550610325,115>
 Planning Time: 0.637 ms
 Execution Time: 0.243 ms

What could be causing the production environment to not fully use the index?
I have run ANALYZE on the table and also created statistics on the columns involved. I have tried recreating the index, using ASC, nulls last, and partial indexes. All result in the same slow Filter:. I have no idea why the sort node is only present on dev, though I would love the production had that sort too. Filtering on the first 2 integers is very fast.
On production it is going through all records in the provider_id/connection_id scope, that is why 3675 iterations.

The planner does weigh between the options and chooses the one that it thinks will be cheaper, of course based on the estimates it has at hand at the time.
When having the ANY in the "Index Cond", it needs to re-descend the index one time for each of the 19 members of your list.  Each time it re-descends, it thinks it will land on a different index leaf page costing one random_page_cost (a questionable assumption perhaps, but the alternative assumption would be just as questionable).  This leads to a high estimated cost.  It needs to pay this cost even for the members that don't end up matching to any index entry.
When the ANY is in the Filter, it thinks it will have to scan all rows meeting the provider_id = 38 AND connection_id = 1175966 condition, and apply the ANY to each one.  But the thing is, we have no way of knowing how many rows it thinks that that will be.  The EXPLAIN reports how many rows it thinks will survive the ANY filter, but doesn't report to us how many it thinks will be tested but fail (it has that estimate under the hood somewhere, it just doesn't make it visible to us).  EXPLAIN ANALYZE reports how many actually failed, but the planner can't plan retroactively based on that value of course.
It looks to me like your production server thinks only one row will have to be tested against the filter, which explains why it has such a low cost estimate and gets chosen.  Whereas your dev server has a more accurate estimate of how many rows need to be tested.  The way to test this hypothesis would be to run slightly different query and see what it gives for the row estimates on each machine.
explain SELECT * FROM reports WHERE "provider_id" = 38 AND "connection_id" = 1175966;

There is another possibility that the executor doesn't implement, which means the planner does not consider it.  It could scan the whole section of the index where "provider_id" = 38 AND "connection_id" = 1175966 and test the ANY against the id_on_site value stored in the index, rather than jumping to the table to test the id_on_site stored there.  I think the reason it does not do this is that it does not trust the ANY/IN test not to throw errors, so it is not going to risk testing it in the index and possibly throwing an error when that tuple in the table might be obsolete anyway.  No one would thank the PostgreSQL developers for making it throw errors based on the invisible detritus!  Now in this case it should be safe as it just a combination of = and OR, both of which are safe.  But I guess the system is not clever enough to know that.
So what can you do about it?  One thing would be fix the estimate of how many rows will match the provider_id = 38 AND connection_id = 1175966 condition on production (in which the first step would be finding out what that estimate is).  Another would be to get an index-only scan by adding more columns to the index or removing columns from the select list.  If you can get an index-only scan, it would test the ANY condition against the index value rather than the table value as long as the table page is marked all visible.  Another thing you could do is switch your RDS to a storage class which doesn't suck. It appears to be taking over 20ms per random page read, which as Erwin points out is horrible.


Out-of-Date MS SQL Server 2014 instance Could you give me some advice on the best practice to update a SQL Server 2014 instance that is on version 12.00.2000.00? Can I install the Service Packs or do I need all of the cumulative updates as well?

Install the most recent service pack, then install the latest cumulative update.

According to SQL Server Builds, the most recent Service Pack for SQL Server 2014 is currently SP2.

CU10 is the most recent Cumulative Update.

Ensure you take a backup of your databases onto physically different media, and do this in a non-production environment to test the change prior to doing this to your production SQL Server.


MySQL replication slave of slave not receiving updates I have 4 servers: A, B, C and D.

A and B are in a master-master setup.

C is a slave to B and D is a slave to C.

Changes on A or B get to all nodes other than D. I need them to get to D

Changes on C get to D but obviously they don't go to A or B which is fine.

After some reading I found that I needed the log-slave-updates option set on C, this way C would log the changes replicated to it and then D could read those and make the same updates.

I have set the option to on and D is waiting for it's master to send events but changes from A or B are still not getting to D.

With log-slave-updates on server C, only updates written directly on B will go to server D.
Server A and Server B require log-slave-updates, otherwise, none of their updates will reach C. In general, any Master that is also a Slave must have log-slave-updates in order to relay binlog events from distant servers.
I have discussed this before

MySQL Slave Relay Logging but not logging Binary Log
Multi-master + slaves replication in MySQL

Restarting mysqld is required on servers A and B for the setting to take effect
Let me explain further
Your Setup

A is Slave of B (log-slave-updates off)
B is Slave of A (log-slave-updates off)
C is Slave of B (log-slave-updates on)
D is Slave of C
A, B, and C are Masters

You run INSERT INTO tblname on Server A what happens ?
Since B is Slave of A

B's IO Thread reads A's binary logs
B's IO Thread gets INSERT INTO tblname
B's IO Thread records it in its relay logs
B's SQL thread reads relay logs
B's SQL thread gets INSERT INTO tblname
B's SQL thread executes INSERT INTO tblnameit
Since B does not have log-slave-updates defined, B does not record INSERT INTO tblname into its binary logs. C's IO Thread never sees INSERT INTO tblname coming from server A.

You run INSERT INTO tblname on Server B what happens ?
Since A and C Slaves of B and D is a Slave of C

A's IO Thread reads B's binary logs
A's IO Thread gets INSERT INTO tblname
A's IO Thread records it in its relay logs
A's SQL thread reads relay logs
A's SQL thread gets INSERT INTO tblname
A's SQL thread executes INSERT INTO tblnameit
C's IO Thread reads B's binary logs
C's IO Thread gets INSERT INTO tblname
C's IO Thread records it in its relay logs
C's SQL thread reads relay logs
C's SQL thread gets INSERT INTO tblname
C's SQL thread executes INSERT INTO tblnameit
Since C has log-slave-updates enabled, C records INSERT INTO tblname in its binary logs
D's IO Thread reads C's binary logs
D's IO Thread gets INSERT INTO tblname
D's IO Thread records it in its relay logs
D's SQL thread reads relay logs
D's SQL thread gets INSERT INTO tblname
D's SQL thread executes INSERT INTO tblnameit

Based on this description, if you had any slaves coming from A, those slaves would never see INSERT INTO tblname coming from B.
Therefore, you need log-slave-updates on A and B as well as C.


What are the disadvantages of using Couchbase's Global Secondary Indexes (GSI)? Couchbase's documentation on GSI states that Global Secondary Index is a powerful solution for secondary lookup queries that are required for interactive applications that require low latencies.

The documentation also mentions that GSI has good write performance, so I am wondering if there are use cases (besides requiring MapReduce aggregations) where Views would be preferred.

The other advantage views have theoretically is that with a large rate of mutations, you'd have a lot of index recalculations.  Views are local to each node, so you don't need to move the data across the physical network to index it.

This all depends on implementation details, the size/types of your documents/indexes, how your system resources are laid out, etc.  Testing should tell you which one works best and note that there is a lot of active development on GSI features such as plasma.


How to query MySql using 256 AES decrypt with key and IV How does one write a MySql query to do a search on (for example) a username, when the username has been AES encrypted with a key and an IV?

I can't find examples of how to write the query.

Of course one has to decrypt the entire dataset and then filter it. Doesn't this cause memory problems?

This is not advisable. Having to decrypt the whole data-set will be slow and memory expensive. You will want to find a reasonable way to encrypt the data without using a separate IV for each username, then encrypt the username you want to find and search for the cipher-text. This will allow you to use indexes and search for only the one username you are interested in.

Alternatively you may consider using hashing in addition to the sorting encrypted username. HMAC the username with a password only known to the server and store in the DB. Then when you want to find a user, hash his username as well and find using the hash. Admittedly this allows some information leakage, for example attacker will be able to see users with the same usernames (you rpobably don't want these anyway). However he should not be able to use any hashtables or even guessing without knowing the HMAC key.

PS: A way you may want to deal with the IV may be: During registration (or username change) choose IV common for the entire server. Encrypt using this IV. If the first block (16 bytes for AES) already are in the database, increment the IV by one and try again. Store the highest increment. During search, try all possible IVs (from the basic one up to the highest increment used).


Using Azure MSSQL "contained database" how do you force a user to reset their password? I'm setting up new users in an Azure MSSQL Contained Database.

I want new users to be forced to reset their password on the first login.

I see the ability to set a new password documented https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver15 but I do not see the ability to force the reset.

Any ideas?

This is currently not supported and you cannot use Must_Chance, Check_Policy or Check_expiration as of now.

  Keyword or statement option 'check_policy' is not supported in this
  version of SQL Server.

You can vote for this in the forum :

https://feedback.azure.com/forums/217321-sql-database/suggestions/10491111-azure-sql-database-password-force-change-on-first


Cannot connect to an Hana database via its ODBC driver I have not found any documentation about ODBC connection strings for Hana and am trying to connect using the examples in SAP HANA Database Client Interfaces.

An attempt to connect via ODBC with the connection string:

DRIVER=HDBODBC; SERVERNODE=192.168.0.213:30015; ID=SYSTEM; PWD=PASSWORD;

fails with this error:

ERROR [08S01] [SAP AG][LIBODBCHDB DLL][HDBODBC]
Communication link failure;
-10709 Connection failed (RTE:[0]  (192.168.0.213:30015))

whereas the corresponding ADO.NET connection works:

Server=192.168.0.213:30015;UserName=SYSTEM;Password=PASSWORD;

What may be wrong with the ODBC connection? I have the correct driver installed.

you might require to install SAP HANA Database Client for ODBC configuration

Please refer to above linked document for a sample case where HDBODBC is installed for connection to HANA database via ODBC

After HDBODBC configuration is created as illustrated in referenced tutorial, you can build your connection string as follows referencing the System DSN name with DSN argument

"DSN=KodyazHANADb;SERVERNODE=myhdb.kodyaz.sap.biz:30815;UID=A00011462;PWD=myPassword+;DATABASENAME=K0D"

You can find the database name in SAP HANA Studio connection properties or by querying M_Databases system view


Principle of least privilege for sql server agent jobs I've just read this article which advocates using the sa account for ownership of sql server agent jobs.

http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account

He has a couple of caveats but doesn't provide much detail on these.

He does say the following:

  ...in situations where security is a concern, and where least privilege
  makes sense (such as with jobs other than backups, consistency checks,
  index defrags, etc.) then you'll want to look at AND take advantage of
  SQL Server proxies.

My question is - why does least privilege not make sense with backups, consistency checks, index defrags, etc? What is the rule of thumb for when least privilege can be ignored?

Maybe the wording is unclear, but least privilege applies to all activities and should never be ignored.

The least privilege you need to perform database maintenance is sysadmin. Lower privileged users could perform those activities too (I'm thinking of db_owners), but the least privilege needed to perform any activity on a SQL Server instance is sysadmin, so if you have a job that performs administration tasks, it makes sense to have it running as a sysadmin.

For tasks other than database maintenance (tasks that implement some business logic), you want to impersonate users that have permissions to perform specific activities on the database.


How to better use CLR Function from performance point of view (repeat inside each DB or have general function)? I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and XML text and will make validation.

I will have 1 configuration database and many installations databases. From that prespective I wonder where to create that function - inside configuration database or inside each installation DB?

From support point of view it would be better to have only one CLR fuinction.

This appears to be a duplicate of this question:

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question.

There is no obvious choice here as to which location is better for SQLCLR objects in general since there can be constraints imposed by what is being done with the SQLCLR code. There are some uses that will require the Assembly to be in each individual database, and one use that would require the Assembly to be in a centralized database. It all depends on a few different aspects of what the code is doing. Hence we need to look at what those aspects are in order to determine if there is even a choice to begin with, and if so, what the pros and cons would be.

SQLCLR-specific functional aspects

User-Defined Types (UDTs): UDTs cannot be referenced across databases; they cannot be declared with 3-part names (i.e. DatabaseName.SchemaName.UserDefinedTypeName). If any UDTs are being used then the Assembly will need to be added to each database in which the UDT will be used. However, if other SQLCLR objects are being used, then assuming that there is the choice to place those objects in either a centralized DB or in each customer/application DB, then you could always place the UDTs in an Assembly that gets placed in each customer/application DB and another Assembly containing Functions / Stored Procedures / User-Defined Aggregates / Triggers.
Security:

How many Databases are affected: Is the CLR code doing anything that requires the Assembly to be marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE? If so, are you importing any DLLs that were signed outside of your control and cannot be resigned? Typically these would be unsupported .NET Framework libraries or 3rd party DLLs. When you have no control over the signing of Assemblies that need to be marked as either EXTERNAL_ACCESS or UNSAFE, then you might be forced to set the database containing the Assembly(ies) to TRUSTWORTHY ON. Since setting a database to TRUSTWORTHY ON is a security risk, it is preferable to minimize the number of databases you would need to do this to, in which case putting the code into a centralized database seems like a better approach. And if you already have a central DB for other code and want to truly minimize this type of security risk, you could have a second centralized DB for just this code.

If you do have control over the signing of the DLL(s), then you should definitely be creating a Certificate or Asymmetric Key in the master database based on the DLL, and then creating a Login based on that Certificate or Asymmetric Key, and then assigning either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission to that Login. Those few steps right there (and the only things being created are the Certificate or Key and the Login) will allow any Assembly signed with the same private key to be set to either EXTERNAL_ACCESS or UNSAFE (depending on which permission was granted to the Login), no matter what database(s) it is loaded into. And if you are able to do this, then you can place Assemblies set to either EXTERNAL_ACCESS or UNSAFE in all customer/application databases without any more security risk than you would have placing that same code into a centralized database **.
Different permissions for different clients/apps need: If, for any reason, some clients/apps might need to have a different PERMISSION_SET than others, then that would requiring loading the Assembly(ies) into each client/app database. This would allow you to have some databases using SAFE while others were using EXTERNAL_ACCESS. This goes beyond what can be done with object-level permissions. By setting an Assembly that has code to do file system functions to SAFE, you guarantee that the code can't work, even if someone does find a way to bypass your regular security and can still EXECUTE the SQLCLR Stored Procedure.

AppDomains: This aspect concerns memory/resource utilization and separation. This is probably the area that is the most impacting in terms of considerations, yet it is also probably the least understood. So, let's start by looking at how T-SQL objects would handle the same central DB vs each client/app DB question.

T-SQL Functions and Stored Procedures, upon being executed, store their execution plans in the plan cache (well, not Inline TVFs), which is in memory. Thinking in terms of just memory utilization, using a centralized DB has the advantage of storing a single plan rather than one plan per each client/app DB, especially if there are 100 or more DBs. However, having a cached plan invites the question of whether or not it is an optimal plan for subsequent executions. It is possible, with a potentially wide range of variation in how it is being executed across so many client/app DBs, that a single plan is great for some but also quite horrible for others. If you don't want the performance hit of specifying WITH RECOMPILE, then deploying that to each client/app DB would allow for more individualized optimization. In short: central DB is less memory used for plan cache, but potentially worse performance; separate DBs is more memory for plan cache, but fewer potential performance issues.

When it comes to SQLCLR objects, the same plan-caching pros and cons exist for each approach. But now that we are dealing with App Domains there are additional ramifications to consider. App Domains are memory spaces / sandboxes that .NET uses to run code in. Each App Domain is its own separate sandbox. In SQL Server, App Domains are created per each Database and Assembly Owner combination. So multiple Assemblies in the same DB owned by the same User will share an App Domain, but Assemblies in that same DB owned by another User will have a different App Domain, and Assemblies in other DBs will be in their own App Domains. With that in mind:

Memory consumption increases at a faster rate when deploying to individual client/app DBs since the Assemblies being used are loaded into the App Domains (but they are not loaded until they are first used). The App Domain also holds all of the variables, resource handles, etc (until those things are marked for Garbage Collection and GC decides that the moon and stars are aligned perfectly and takes that as a sign to run). So a 2 MB Assembly in one database using one AppDomain that has a certain amount of memory reserved for variables, etc can be quite different than loading that same Assembly into 100 DBs where it is now 200 MB (technically there is some portion of the DLL that is shared in memory across multiple instances of it, but I'm not sure how to measure that) plus 100 times the amount of space reserved for variables etc.

A related issue is if you are using Regular Expressions and making use of the RegEx option for Compiled which compiles the expression down to Intermediate Language (MSIL). This does speed things up for repeatedly used expressions, but once an expression is compiled, it cannot be garbage collected and will stay in the AppDomain until it is restarted. If there is a commonly used RegEx function that is using the Compiled option, the memory used to store it will be repeated per each DB if the Assembly is loaded into each DB. In this case it might make sense to put this code in a centralized DB.
Resource limitations can be an issue when using a centralized database. Depending on what classes you are using, you might unknowingly create a resource bottleneck. For example:

When using the static RegEx methods instead of the instance methods, the Regular Expressions that you use are cached. But the default cache size is only 15 expressions. If a great variety of expressions is being sent in from a large number of clients or apps, then expressions won't stay in the cache for very long. So if this is the only reason to consider loading the Assembly into each Database, then you could just increase the cache size. Please see the MSDN page for RegEx.CacheSize for details.
Similarly, if making WebRequests then there is a default maximum number of active connections that can be made to a particular URI. And that default is just 2. If you make more requests to that same URI (very easy to do if it is a static location and you are using a centralized DB for this code) then any requests above that maximum will simply wait in line for a current connection to close (i.e. blocking). So you would have to either load the Assembly into each client/app DB or increase the connections-per-URI limit. You can set the default maximum for all URIs in the current App Domain by setting ServicePointManager.DefaultConnectionLimit (this can be set once per start up of the App Domain, such as in a static class constructor), or can be set on a per-URI basis by creating a HttpWebRequest and then setting its .ServicePoint.ConnectionLimit property (this needs to be done each time the WebRequest is instantiated since the object has a maximum time to live and once garbage collected, the ConnectionLimit will revert to the ServicePointManager.DefaultConnectionLimit value, as noted above, when a new instance is created).

If you are using a static variable to cache certain values (shared memory -- rare but still a possibility), then you need to decide what the scope of sharing those values should be. If you want the sharing to be contained within each client/app DB, then load the Assembly into each client/app DB. But if you want to share those values across all DBs, then put the Assembly into a shared, centralized DB.

General functional aspects

Database access: Is the code referencing any database-specific objects? Keep in mind that executing SQL using the in-process / Context Connection = true; connection will execute initially with the "current" database being set to the database where the object exists, not necessarily where the object is being called from. Hence code running in a customer/application database and calling an object in a centralized database won't be able to reference objects by using only 2-part names. However, you can still use a centralized database for such code as long as you have an input parameter for @DatabaseName (use: [SqlFacet(MaxSize = 128)] SqlString DatabaseName) and then pass DB_NAME() into it. Then you can use DatabaseName.Value in the SQLCLR code for either a USE statement or to concatenate into the Dynamic SQL to create the appropriate fully-qualified object names (i.e. 3-part names).

This is probably not a determining factor if you are only referencing system-based objects (i.e. sys.databases) that return the same rows no matter what database you are in. Nor should it be an issue if you are making an external connection since you would already be passing in the database name for the connection string, or you will just be logging into the default database for the login making the connection.
Collation differences: If the Collations between the centralized DB and the client / app DBs are the same, then this is not a determining factor when deciding between these two models. But, if your system is going to support different collations, then you need to be aware of what you code is doing as it might be impacted by Collation Precedence. If you are sending in strings that will be compared to other strings, then the behavior might not be what is expected, even if there is no error produced. The collation used to compare local variables and string literals will be the default Collation where the object (i.e. Stored Procedure or Function) exists. If this Collation is different than the collation of the "current" database when that object is called (if passing in a literal or variable), or different than the field being passed in, then there can be a variety of differences in how that comparison is done. Hence, if supporting a variety of Collations, then string-based operations might be more stable/consistent when the code is deployed to each client/app DB.

Distractions

The following are reasons given in this Question, and in the duplicate question linked at the top of this answer, for preferring one method or the other, but that I feel are not truly relevant in deciding which method is a better fit:

Centralized DB is easier to support: How so? The code should only exist once in source control. And assuming that the code loaded into each client/app database is the same, then troubleshooting should be roughly the same in either case. If anything, placing the SQLCLR code in a common database, separate from the client/app DBs adds a layer of complication with cross-database calls that can technically be seen as a reason to not centralize this (or other) code.
Centralized DB is easier on deployments: If this is the case, then I would say that one needs to fix their deployment process. If you are working with a model in which you duplicate the client / app databases to have identical schema and just different data, then you already face this problem when deploying schema changes. Deploying SQLCLR is also just DDL statements. It might be more difficult to deploy SQLCLR code if attempting to load the Assembly from the DLL, but there is no reason to do that. Just make sure to have a self-contained SQL script that does a CREATE ASSEMBLY or ALTER ASSEMBLY using the hex bytes (i.e. FROM 0x4D5F000002C...).
Individual Client / Application DBs are better for backup/restore: The argument here is that if there is a problem and you need to restore, then having everything contained in the Client / App DB being restored is easiest. I would say that if you need to do a restore, then restoring 2 DBs (Client / App DB and common DB) is pretty much the same work, and restoring 101 DBs (100 Client / App DBs and 1 common DB) is still pretty much the same. And from a reliability stand-point, either your backup process is reliable and can be trusted to properly back up both Client / App DBs and common DB, or you need to fix your backup process ;-).
Individual Client / Application DBs are easier for testing variations: To a degree this is true if you need to test one version of the code without changing all references. Though usually this should be done by testing in a completely different environment to begin with. But if there is a customer who is going to beta-test some code change, for example, then this situation can be mitigated easily enough by using the model suggested by @AaronBertrand in his answer to this question which is very similar (it concerns mainly T-SQL objects and not specifically SQLCLR code): Create function in central database or repeat in each database? . The model discussed in that answer is to use a centralized DB for most/all cases, and to create Synonyms in each Client / App DB so that the local code can reference local names. Then, if a variation is needed, code can be placed in a particular Client / App DB and the Synonyms would then be removed. In that case, the local T-SQL code would still reference the same local names and not know the difference. Personally, I really like this approach and cannot think of any particular downside to it outside of needing to create / remove the Synonyms, which doesn't seem like a high price to pay for the flexibility gained.

Ergo: for the particular situation described in this Question (i.e. scalar function, no external resources, no database-object access, no resource limitations), it seems like using your single configuration database would be fine.

** If you find yourself thinking "but assemblies set to EXTERNAL_ACCESS or UNSAFE are security risks because of what they allow you to do": I was not saying that there is no risk at all with assemblies set to EXTERNAL_ACCESS or UNSAFE if you use the Certificate / Asymmetric Key -based method. What I am saying is, in that configuration, whatever risk exists is no different between placing the Assembly in a centralized database versus in each client/application database. This is because any potential security problems that could result from assemblies that are set to EXTERNAL_ACCESS or UNSAFE are not localized to the database in which those assemblies exist (unlike setting TRUSTWORTHY to ON). Any security issues are system wide. But, when setting databases to TRUSTWORTHY ON, then you have additional, per-database security issues.


What are good ways to store decorated objects in a relational database without adding a field for every possible property? Suppose I have an object, I use a decorator to add properties to this object, and then I want to store the object and its properties in a relational database, and be able to run queries on these stored properties.

For example, say I've got a database with kitchen equipment. Base properties would include things like make, model, and description. Of the pieces of kitchen equipment, some use electricity, some use gas, some use water, some need a drain, some need fire protection, some need routine maintenance, etc. These properties are generally not Boolean flags, but are more of a rich collection of classes themselves. So, if I want to keep all of my "equipment" in one table, I've got to find a way to store all of these properties.

I'm using a decorator to add properties to objects instead of using inheritance to reduce the number of classes, as I don't want to have 2^(number of properties) classes.

Two things I've come up with so far are table per property and field per property.

Table per property has many more joins to reconstruct the object and field per property has a lot of empty fields for many objects, and requires adding a field whenever I introduce a new unique property. Is there some (sane) way to address this other than to use one of the two methods mentioned above? I know that this can be done in some NoSQL databases, this question specifically concerns relational databases.

It is not clear in your question whether you need to use these dynamic properties in your database queries. I'll assume you won't.

With this assumption, I can actually point you to an implementation I've used in the past, although I'd add a disclaimer: in these days, you really shouldn't do this unless you're really stuck to your current DB and cannot, at all, move the persistence of this collection of objects to a NoSQL DB, which is where it seems to belong.

Well then, to the answer:
You can make use of an object serialization engine, such as the one in Java (maybe you're using another language, but I'm sure it has some way of doing the same thing), then create a database table where you store the serialized objects. Then, in order to be able to query this table, you have the following options:

If you have common properties between all objects, you can create a column for each property that is needed in queries, then populate them based on the object being serialized whenever you have an insert or update.
If you need to query by using some properties that only exists in a few objects, you can create another table with a foreign key to the unique ID of the object in the original table, then add the columns needed for querying.

You can mix the options above as you wish - in my case, I ended up with  one table just for storing the serialized objects, given unique IDs, then multiple tables for querying. There was some duplication of data, but it was done based on performance measurements of the many reports that had to run on these objects. Your mileage may vary.

In the end, whichever query structure you choose will give you a relatively good mix of the advantages of relational DB queries while retaining the object structure used by your program, since the deserialization process should recreate the object (or object graph, given the use of decorators), just as it was before being serialized.

However, I feel like I should stress this again: I do not believe that this should be done in this day and age, unless you have real economic/political/whatever reasons to do so.


Oracle: REGEXP_LIKE: Strings with angle brackets aren't found (whether they are escaped or not) The following question will answer my original question, plus many others. What would the correct syntax be to do a REGEXP_LIKE search (in Oracle Database 12c) for this string (which exists somewhere in a CLOB)? In other words, pretend like you want to find this exact string:

%.,"'?!:#$&)(*;+-/<>=@[]\^_{}|~

using:

"select * from my_table where regexp_like (some_column, '" + regexSearchString + "')"

Now, from everything I've read, you would need to transform the string by escaping the necessary characters  (\  ^  .  $  |  (  )  [  ] *  +  ?  {  }  ,) to get it to work, like this:

%\.\,"'\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~

Now I haven't tested every non-escaped character like &, _, or %, but I have found that searches with < or > are not found. Even though REGEXP_LIKE is not needed in the following example, say I wanted to find all occurrences of ><div class=, for whatever reason. Is there an additional thing that needs to be done to get it to correctly treat angle brackets just like any other letter?

select 1 from dual where
regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~',
            '%\.,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~');

This gives, as expeced, 1.

You must escape .?$)(*+[]\^{} and |

You do not need to escape , (but it works nonetheless):

select 1 from dual where
regexp_like('%.,"''?!:#$&)(*;+-/<>=@[]\^_{}|~',
            '%\.\,"''\?!:#\$&\)\(\*;\+-/<>=@\[\]\\\^_\{\}\|~');

This works:

select 1 from dual where
regexp_like('
Lets check for an inner tag :)
',
            '<(\w+)>[^<]*</\1>');

Gives 1. So does what you ask for:

select 1 from dual where
regexp_like('
Lets get this
example going :)

',
            '><div class=');

For which you don't even need regex, by the way.

EDIT:
By guess is, that you are using a programming language to build your SQL. In that case, it is highly likely that you are not properly escaping your . If you want the SQL to include '\.' (an escaped .), you need to escape it for your lanuage too: '\\.'
Likewise, if you want '\\\^' you'd need to write '\\\\\\^'


Call to default values (MySQL) in a procedure For the sake of an experiment, we're working with the following procedure (same DB, multiple tables):

delimiter //

CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hash_id int,
  _id_status bit(2), _id int, _criminal_status bit(1), _charge varchar(45), _plea bit(2), _sentence varchar(45), _dob int, _state_id int, _state_id_status bit(1))

begin

START TRANSACTION;

  INSERT INTO MYDB.person (`first_name`, `last_name`, `hashID`)
    VALUES (_first_name, _last_name, _hash_id);

  INSERT INTO MYDB.id_crimes (`id`, `id_status`, `criminal_status`, `charge`, `plea`, `sentence`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, LAST_INSERT_ID());

  INSERT INTO MYDB.dob_state (`state_ID`, `stateID_status`, `dob`, `hashID`)
    VALUES (_dob, _state_id, _state_id_status, LAST_INSERT_ID());

  INSERT INTO MYDB.timestamps (`create_time`, `update_time`, `hashID`)
    VALUES (_create_time, _update_time, LAST_INSERT_ID());

  COMMIT;

end//

delimiter ;

We're running into difficulties when trying to call the procedure, however. The DEFAULT and CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP values are throwing errors (call seen below). Any help would be appreciated!

call mysql.insertTables (`Debbie`, `Hopkins`, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  DEFAULT, 1, 123456789, 0, NULL, NULL, NULL, 12211985, 12345678, 1)

P.S. The hashID value is stored only as a foreign key in every table except person, so I'm not sure we need to insert it (as LAST_INSERT_ID()) in every table. The first issue mentioned is the biggest concern, though.
Thank you!

The point of "default" values is that they are the default values.  You Don't need to list them in the insert query at all.  They'll be inserted automatically.

To pass CURRENT_TIMESTAMP as a parameter, you probably need to use the equivalent pseudo-function, CURRENT_TIMESTAMP()... which is the same as NOW()

Stop and consider DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.  The default value here is only CURRENT_TIMESTAMP -- ON UPDATE ... is a completely unrelated directive, so here again, you need to just pass NOW() or CURRENT_TIMESTAMP() if you want to pass these into procedures... though that us unnecessary if you modify the inside queries not to specify the columns you are free to exclude, because they have default values.


Can anybody tell me what's wrong with this query? CREATE PROCEDURE Click(
    in  ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
IF ex = 0 THEN
    INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0);
END IF;
    UPDATE clickactivity SET Clicks = Clicks + 1;
END

It complains about line 4 like so:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

There are two things wrong

First, use DELIMITER $$ so semicolons are interpreted inside the stored procedure and not the command line. After defining the stored procedure, switch the delimiter back to semicolon (;).

Next, UPDATE clickactivity SET Clicks = Clicks + 1; will update every row and you don't want that.

SUGGESTION #1

Add WHERE IP = ipaddress; to the UPDATE clickactivity

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
    IF ex = 0 THEN
        INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 0);
    END IF;
    UPDATE clickactivity SET Clicks = Clicks + 1 WHERE IP = ipaddress;
END $$
DELIMITER ;

SUGGESTION #2

Why do two or three queries to register a single click ?

Change the code to do the query so that it INSERTs and UPDATEs at the same time

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
    ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;
END $$
DELIMITER ;

In that event, you don't need a stored procedure. Just do the INSERT

INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;

Of course, doing this works if ipaddress is the PRIMARY KEY or is a UNIQUE KEY in clickactivity.

GIVE IT A TRY !!!


Improving SQL Query joins performance We have been using SQL Server 2014 for our project as a backend source. We have been performing various joins on the tables. We came across a question in one to many and many to one relationship in joins.

Say for example: I have a table named Industry and another table called Products. Industry holds the list of Industry in market and Products holds that industry's multiple products.

Industry

Id
IndustryName
Owner

Products

Id
IndustryId
ProductId
ProductName

From the above tables, I am performing two queries as shown below:

1 to many

Select *
from Industry
inner join Products
    on Industry.Id = Product.IndustryId

many to 1

Select *
from Product
inner join Industry
    on Product.IndustryId = Industry.Id

Among the above which will be more efficient performance wise?

No difference. Only the order of the columns in the SELECT list changes.

See related questions:

Is it correct, order of where clause doesn't matter when it is used with join?
join syntax / style performance consideration
Is a join optimized to a where clause at runtime?

Changes like this will almost 100% produce the same execution plan.

Communtiy Wiki answer based on an original comment on the question by @ypercube


Rename foreign keys en masse on SQL Server 2017 I have a database that was recently migrated from Oracle to SQL Server 2017. Whoever initially setup the database set all the foreign keys to be system generated names.  In other words if I look at the foreign keys in a table they are named (SYS_C005214, SYS_C005271, SYS_C005272). I would prefer to have these all renamed to:

FK_ChildTable_childColumn_ParentTable_parentColumn

I would like to setup a script that will change all of the foreign keys to the approprite name. Is there a way to do this without having to go into each key to see it's definition and then renaming it by hand?

Note: I now realize after running Aaron Bertrand's script, I also need this for primary keys.

Stealing from myself (please see the caveats and comments on that article):

CREATE PROCEDURE dbo.ImplementNamingStandard
    @SELECT_Only        BIT = 1,
    @ForeignKeys        BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX), @cr CHAR(2);
    SELECT @sql = N'', @cr = CHAR(13) + CHAR(10);

    DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT;
    SELECT @TableLimit = 24, @ColumnLimit = 10;

    IF @ForeignKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N'''
          + QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
          + REPLACE(name, '''', '''''') + ''', @newname = N''FK_'
          + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit) + '_'
          + LEFT(REPLACE(OBJECT_NAME(referenced_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.foreign_keys
        WHERE is_ms_shipped = 0;
    END

    SELECT @sql;

    IF @SELECT_Only = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO


Identify duplicates and apply logic I have a largish 27mil row table.  The table contains a large number of duplicate string values due to case variance.

All rows have the string column and also one additional numerical column, and I am trying to identify duplicates, and then ignore the row with the numerically highest secondary value within each duplicate set and export the remaining rows to a new table.

I have tried a number of approaches, but performance has been so poor as to make them nonviable even as a one time event.

What is the most performant approach for this kind of duplicate search with logic?

Platform is MySQL.

I would try locating all duplicates and their respective highest secondary values like this:

SELECT
  StringColumn,
  MAX(SecondaryColumn) AS HighestValue
FROM
  atable
GROUP BY
  StringColumn
HAVING
  COUNT(*) > 1

and then using the results to produce the final set like this:

SELECT
  t.StringColumn,
  t.SecondaryColumn
FROM
  atable AS t
  INNER JOIN
  (
    SELECT
      StringColumn,
      MAX(SecondaryColumn) AS HighestValue
    FROM
      atable
    GROUP BY
      StringColumn
    HAVING
      COUNT(*) > 1
  ) AS s ON t.StringColumn = s.StringColumn
        AND t.SecondaryColumn <> s.HighestValue
;

I believe an index on (StringColumn, SecondaryColumn) should help the performance.


Optimizing bulk update performance in PostgreSQL Using PG 9.1 on Ubuntu 12.04.

It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:

UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid

(We're just overwriting fields of objects identified by ID.)  The values come from an external data source (not already in the DB in a table).

The tables have handfuls of indices each and no foreign key constraints.
No COMMIT is made till the end.

It takes 2h to import a pg_dump of the entire DB.  This seems like a
baseline we should reasonably target.

Short of producing a custom program that somehow reconstructs a data set
for PostgreSQL to re-import, is there anything we can do to bring the
bulk UPDATE performance closer to that of the import?  (This is an area
that we believe log-structured merge trees handle well, but we're
wondering if there's anything we can do within PostgreSQL.)

Some ideas:

dropping all non-ID indices and rebuilding afterward?
increasing checkpoint_segments, but does this actually help sustained
long-term throughput?
using the techniques mentioned here? (Load new data as table, then
"merge in" old data where ID is not found in new data)

Basically there's a bunch of things to try and we're not sure what the
most effective are or if we're overlooking other things. We'll be
spending the next few days experimenting, but we thought we'd ask here
as well.

I do have concurrent load on the table but it's read-only.

Assumptions

Since information is missing in the Q, I'll assume:

Your data comes from a file on the database server.
The data is formatted just like COPY output, with a unique id per row to match the the target table.
If not, format it properly first or use COPY options to deal with the format.
You are updating every single row in the target table or most of them.
You can afford to drop and recreate the target table.
That means no concurrent access. Else consider this related answer:

Best way to populate a new column in a large table?

There are no depending objects at all, except for indices.

Solution

I suggest you go with a similar approach as outlined at the link from your third bullet. With major optimizations.

To create the temporary table, there is a simpler and faster way:

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

A single big UPDATE from a temporary table inside the database will be faster than individual updates from outside the database by several orders of magnitude.

In PostgreSQL's MVCC model, an UPDATE means to create a new row version and mark the old one as deleted. That's about as expensive as an INSERT and a DELETE combined. Plus, it leaves you with a lot of dead tuples. Since you are updating the whole table anyway, it would be faster overall to just create a new table and drop the old one.

If you have enough RAM available, set temp_buffers (only for this session!) high enough to hold the temp table in RAM - before you do anything else.

To get an estimate how much RAM is needed, run a test with a small sample and use db object size functions:

SELECT pg_size_pretty(pg_relation_size('tmp_tbl'));  -- complete size of table
SELECT pg_column_size(t) FROM tmp_tbl t LIMIT 10;  -- size of sample rows

Complete script

SET temp_buffers = '1GB';        -- example value

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

COPY tmp_tbl FROM '/absolute/path/to/file';

CREATE TABLE tbl_new AS
SELECT t.col1, t.col2, u.field1, u.field2
FROM   tbl     t
JOIN   tmp_tbl u USING (id);

-- Create indexes like in original table
ALTER TABLE tbl_new ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl_new (...);
CREATE INDEX ... ON tbl_new (...);

-- exclusive lock on tbl for a very brief time window!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

DROP TABLE tmp_tbl; -- will also be dropped at end of session automatically

Concurrent load

Concurrent operations on the table (which I ruled out in the assumptions at the start) will wait, once the table is locked near the end and fail as soon as the transaction is committed, because the table name is resolved to its OID immediately, but the new table has a different OID. The table stays consistent, but concurrent operations may get an exception and have to be repeated. Details in this related answer:

Best way to populate a new column in a large table?

UPDATE route

If you (have to) go the UPDATE route, drop any index that is not needed during the update and recreate it afterwards. It is much cheaper to create an index in one piece than to update it for every individual row. This may also allow for HOT updates.

I outlined a similar procedure using UPDATE in this closely related answer on SO.


After fresh install getting `psql: command not found` I downloaded and installed PostgreSQL and PostGIS from KyngChaos for my OS X 10.10 install. When I run psql I get,

bash: psql: command not found

According to the site How to run psql on Mac OS X? it gets installed into /Library/PostgreSQL. There is no file there.

According to the README included in the dmg file, postgres binaries are installed in /usr/local/pgsql/bin/. You should add these lines in your ~/.bashrc :

export PSQL_DIR=/usr/local/pgsql/bin/
export PATH="$PSQL_DIR:$PATH"

Update your environment with source ~/.bashrc or run a new shell and psql should be available.


Corruption on MS SQL Database due to 1 failed disk on RAID 6 Why is that when a single disk fails from a RAID 6 setup MS SQL 2014 database corrupts with the following error:

DESCRIPTION:    SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xad191b0d). It occurred during a read of page (1:144366313) in database ID 19 at offset 0x0001135b5d2000 in file 'D:\MSSQL12.LIST133\MSSQL\DATA\test.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The RAID 6 status is healthy and hasn't change even with one disk failed (the issue should appear when 3 DISKs fail). How does the SQL knows what is going on with the DISK since it is not a software RAID 6? There is a seperate controller and a storage.
Also what is the point of having spare disks in the RAID  if the SQL is going to corrupt anyway? How can I avoid this?

Firstly you should change your database page verification setting from tornpage to checksum to have more reliable identification of database corruption.
secondly SQL Server has detected an I/O error - not surprising if an entire disk failed (or was removed while you tested your test db). - Anyway you won't know if the database is corrupt until you complete the recommended action - DBCC CHECKDB


Isolation Level for a Data Warehouse We are building out a data warehouse (and also some data marts as well) in our organization. My DBA skills have mostly been on OLTP type applications, but I am moving into supporting OLAP for our organization.

We ran into an issue recently where one of our ETL programs (in this case it happens to be a Message Driven Bean written in Java) is using the Repeatable Read (RR) isolation level. Because of this, they were running into locks. I obviously question why RR was being used. (EDIT: I originally thought RR on the DB2 side, but learned this was RR from the JDBC side, which maps to RS on the DB2 side. Since then we have also tried RC on the JDBC side, which is CS on the DB2 side. That appears to have resolved some of the locking issues.)

So I started to research what should be the suggested isolation levels for data warehousing, both from the feed/source side, as well as from the retrieval side.

I really can't find much. I have found a few articles that mentioned Uncommitted Read (UR) being good for the query/retrieval side, but nothing about the feeding side. I was thinking that Cursor Stability (CS) or at the highest Read Stability (RS) should be used. I would side with CS, but perhaps that is tainted by OLTP thinking?

In our case the main feed for our warehouse is right now done "real-time" using Java message queues. Retrieval from the warehouse will be done with ETL tool (DataStage in our case), probably kicked off as nightly jobs (some for daily reports, others for monthly, or quarterly reports). These reports will be based off of our marts. So while the warehouse might be fed "real-time", it won't be read from, or actively queried over (at least not right now).

Granted, we use DB2 9.7 FP 4 (and we are hoping to migrate to 10.1 sometime next year) and some of the specifics may differ per vendor, but what isolation levels do you warehouse DBA's recommend for feeds and reads and why?

Also, any input on isolation levels for marts would be appreciated to.

  EDIT: Just so it is easier for other vendor comparisons. Here is the table of DB2 isolation level names as mapped to the JDBC isolation
  levels. (This per the book Understanding DB2: Learning Visually By
  Examples and thus copyright IBM.)

+------------------------------+-----------------------+
|            JDBC              |          DB2          |
+------------------------------+-----------------------+
| TRANSACTION_READ_UNCOMMITTED | Uncommitted Read (UR) |
+------------------------------+-----------------------+
| TRANSACTION_READ_COMMITTED   | Cursor Stability (CS) |
+------------------------------+-----------------------+
| TRANSACTION_REPEATABLE_READ  | Read Stability   (RS) |
+------------------------------+-----------------------+
| TRANSACTION_SERIALIZABLE     | Repeatable Read  (RR) |
+------------------------------+-----------------------+

For most databases including Data Warehouses Read Committed is a sufficient isolation level.  I wouldn't use Read Uncommitted due to the possibility of incorrect answers.  On SQL Server we can also add Read Committed Snapshot Isolation.  This versions rows and avoids queries being blocked by updates.  Don't know if you have something similar on DB2.


Information_schema appearing in slow_log I am finding that my information_schema keeps running this query

SELECT engine, COUNT(*) as n FROM information_schema.tables WHERE engine is not null GROUP BY engine

Which seems to always appear in the slow_log, it seems to run every 4-5 seconds; with a 10 second query time.

I am using Google Cloud SQL.

Is there a way I can disable this from occurring - or rather, what keeps triggering it? I have read the MySQL Documentation on the information_Schema but could not figure out why this was being called.

Thank you

Found the issue, Google App Engine runs the query for monitoring


Maintenance Plan Wizard generates invalid script? I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:

  Executing the query "EXECUTE master.dbo.xp_delete_file
  0,N'',N'',N'2016..." failed with the following error: "Error executing
  extended stored procedure: Invalid Parameter". Possible failure
  reasons: Problems with the query, "ResultSet" property not set
  correctly, parameters not set correctly, or connection not established
  correctly.

Generated T-SQL is this (according to Log File Viewer):

EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

GO

Please note all single quotes are doubled.

Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?

Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:

EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

GO

The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:

File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files.  The path must end with a backslash "\".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:


What things SQL Server takes backup of when we run a full backup Like I am trying to ask that, suppose we run a full backup using GUI or T-SQL, which file SQL Server takes backup of i.e, only data file or log file as well.

I have read that it takes active transaction portion (Log File) of SQL Server as well. please confirm.

If yes, then what will happen to a transaction that was running at that time, and committed next, logs of the transaction will there on backup or not.

how will sql server revert back to it, if the database fails.

A Full Backup contains a copy of all the database pages, and all the log records from the LSN at which the first database page was copied through the LSN at which the last database page was copied.

On restore the database pages are copied into the new database files, then the log records are replayed to to bring the database up to the point-in-time at which the backup completed.

If you restore WITH RECOVERY, then any transactions that were open at the time the backup completed are rolled-back and the database is brought online.

If you restore WITH NORECOVERY, then you can restore additional Log Backups to restore the database to a later point-in-time.


Check if contiguous date interval exists I have a table in PostgreSQL which describes some events that have a start date and an end date:

CREATE TABLE my_table
(
  event_id serial NOT NULL,
  start_date timestamp without time zone NOT NULL,
  end_date timestamp without time zone NOT NULL
)

A single event may overlap the previous and the next one. In the following table the first 3 of 4 events form a contiguous time interval:

1   '2015-04-02 22:09:03'   '2015-04-02 22:19:05'
2   '2015-04-02 22:17:38'   '2015-04-02 22:27:38'
3   '2015-04-02 22:25:21'   '2015-04-02 22:36:23'
4   '2015-04-02 22:45:23'   '2015-04-02 22:55:23'

Is it possible to write a query that checks if a contiguous date interval between two given dates exists?

I would like to have something like:

select ...
from my_table
where start_date > '2015-04-02' and end_date < '2015-04-06'

First, we combine intervals that overlap to find all the contiguous "islands" of the intervals:

with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  )
select start_date,
       coalesce(lead(previous_max) over (order by start_date),
                (select max(end_date) from my_table)
               ) as end_date
from c
where previous_max < start_date
   or previous_max is null ;

After that, it's easy to check if a given interval in completely surrounded by one of the found contiguous islands.

   with c as
  ( select *, max(end_date) over (order by start_date
                                  rows between unbounded preceding
                                           and 1 preceding)
                as previous_max
    from my_table
  ) ,
cont as
  ( select start_date,
           coalesce(lead(previous_max) over (order by start_date),
                    (select max(end_date) from my_table)
                   ) as end_date
    from c
    where previous_max < start_date
       or previous_max is null
   )
select *
from cont
where tsrange(start_date, end_date)
       @>                             -- contains
      tsrange('2015-04-02 22:10:00', '2015-04-02 22:30:00')
limit 1 ;

Test at SQLfiddle


Always On Availability Groups on single VM Is there any way to implement Always On Availability Groups using SQL Server 2019 Enterprise Edition on a Single Virtual Server.
The goal is not to have any HA or DR capability, but merely Read-Scale as a method to distribute read operations to a second replica.  The thought is to have two or more instances on the same virtual server, with each instance having a portion of the RAM assigned based on load experienced for reading vs. writing.  The memory for each SQL instance would be less cluttered, and less locking etc would occur, and performance would be expected to improve even with a single server.
Attempting to do this with two instances on the same server, SQL complains in the AG Creation Wizard that the name of the replica node has the same base server name included, even though there are two separate instances.  Essentially, it is interpreting the base server name of the instance name as the 'node' (even though the setup for read-scaling does not require WSFC node setup), and thus the replica is not allowed to be created on the same node.
Is there a way around this?
Thanks!
Script I tried without success when instances were on same server:
CREATE AVAILABILITY GROUP [TEST_AG1]
WITH (
 AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
 DB_FAILOVER = ON,
 DTC_SUPPORT = NONE,
 CLUSTER_TYPE = NONE
)
FOR DATABASE [Northwind]
REPLICA ON N'ROBSWS2019\SQLA' WITH (
 ENDPOINT_URL = N'TCP://ROBSWS2019.AUSTINLANE.local:5022',
 FAILOVER_MODE = MANUAL,
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
 BACKUP_PRIORITY = 50,
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
 N'ROBSWS2020\SQLB' WITH (
 ENDPOINT_URL = N'TCP://ROBSWS2020.AUSTINLANE.local:5022',
 FAILOVER_MODE = MANUAL,
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
 BACKUP_PRIORITY = 50,
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);

GO

I definitely understand the comments from Lee and Rodrigo, and really appreciate you pointing me at those additional resources.  I think your observations are generally correct.
However, I have discovered an approach that may bear fruit on this endeavor.  Rafael Rodriguez has accomplished this through the use of Docker Containers.  He has an article about how he did this AOAG Read-Scale Clusterless.
He also has the entire project in a git repository.
I downloaded it and fired it up in Docker on my Win10 workstation.  I was able to connect to both instances from SSMS, add a table to the primary and then see it show up in the secondary replica!
I think this essentially answers this question.  My next step is to set up the particulars of my use case, and determine a testing strategy to get benchmarks and performance improvement data.  I will try to circle back and post any results.
Thanks to everyone for helping me move forward on this project!  Merry Christmas!!!


What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database? The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank.  They had no errors or anything to point them to a reason of why this happened.  Since then, the company has not tried mirroring since it is not great for high availability if it just kills the primary database in the mirror setup.  The mirror was on SQL-Server 2005 (most likely).  I don't have any other information other than that because of how long ago this problem occurred.

The current setup that we are going to possibly use for mirroring will consist of SQL servers 2008, 2008 r2, 2012.  Do problems like the one mentioned happen frequently or has Microsoft improved their logic enough to rule out these possibilities for the most part?  Do problems with erasing and corruption happen more frequently when the servers are on different version of SQL Server?  Has anyone else ever had a similar problem with a nailed down cause?

Any insight or expertise would be appreciated since I cannot locate anyone who has had a similar problem yet.

I appreciate that you are looking for anecdotes about the experience of using mirroring; however this site is not really the place for opinion.
Having said that, you may find the following excerpt from Microsoft.com regarding database mirroring helpful:
Database mirroring is a simple strategy that offers the following benefits:

Increases availability of a database.

In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (without data loss). In the other operating modes, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.

Increases data protection.

Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.
A database mirroring partner running on SQL Server 2008 Enterprise or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error. For more information, see Automatic Page Repair (Availability Groups/Database Mirroring).

Improves the availability of the production database during upgrades.

To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are hosting the failover partners. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade. For more information, see Install a Service Pack on a System with Minimal Downtime for Mirrored Databases.

Paul Randal's sqlskills.com blog offers an excellent set of articles about mirroring at http://www.sqlskills.com/blogs/paul/category/database-mirroring/
Glenn Berry offers up some pertinent tips for running mirroring at http://sqlserverperformance.wordpress.com/2012/03/12/sql-server-database-mirroring-tips-and-tricks-part-1/


Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs I have a DB table has more than 50 columns.

I am querying this table, it should return only one row any time. as below image from sqldeveloper.

here I need to construct query/pl-sql block, to find out table column as a key and query result as values.

Eg:     Key                         -  Value

      TASK_EVENT_ID   - 1765

      EVENT_TYPE        - ASR_UPDATE

       ...... etc upto all columns in my table.

In my procedure I am constructing as like below.

 Message_handle RAW(64);
Enqueue_options DBMS_AQ.enqueue_options_t;
Message_properties DBMS_AQ.message_properties_t;  msg
SYS.AQ$_JMS_BYTES_MESSAGE;
msg.set_string_property('TASK_EVENT_ID', v_TASK_EVENT_ID);
msg.set_string_property('EVENT_TYPE', v_EVENT_TYPE);

------------------------------------------------------------------------

According to your requirement you might be looking for UNPIVOT operator(If you are using 11g or later version). It converts column-based data into separate rows.

For example:

SQL> desc tbl1
 Name                      Null?    Type
 ----------------------------------------- -------- --------------
 ID                         NUMBER
 NAME                           VARCHAR2(20)

SQL> SELECT * FROM tbl1;

    ID NAME
---------- --------------------
     1 test name

WITH all_objects_data AS(
SELECT TO_CHAR(id) id, name FROM tbl1)
SELECT column_name, column_value FROM all_objects_data UNPIVOT(column_value FOR column_name IN (id,name));

COLU COLUMN_VALUE
---- ----------------------------------------
ID   1
NAME test name

For details: Pivot and Unpivot


Contained User within a Non-Contained Azure SQL Database While granting access to Azure SQL databases I have been creating Contained Users directly in the SQL database like so:-

CREATE USER [email@domain.com] FROM EXTERNAL PROVIDER;

As this has been working fine, and users need to add the database name to the connection, I thought azure would know this is now a contained database.

However, upon checking, I've found the database is not contained

select containment, containment_desc from sys.databases

containment /   containment_desc
0           /   NONE
0           /   NONE

Should I now convert the database to be contained?
Are there any implications for leaving the database in this state?
Should I revert back to creating the user at both the server and DB level?

Azure SQL Database allows database-level authentication regardless of whether the database is contained or not. There is no need to change the database containment for the purpose of database-level authentication alone.

The reason you might use a contained database is to ensure better portability. See the Contained Databases documentation for more info.


SQL Server Agent - Service Failed to Start (Access is Denied) I have a server running Server 2012 R2 with SQL Server 2012 installed. The SQL Server Agent works fine if it is running under Local System. I changed the SQL Agent service account to a domain account and the service will not start. Checking the event viewer the only error message available is the one below.

  The SQL Server Agent (NEPO) service failed to start due to the
  following error:  Access is denied.

If I add the domain account to the local admin group on the server the service starts normally. I don't want to leave that account as a local admin. I have also gone through and manually granted all the permissions Microsoft outlines in this article. Still getting the same error and not sure where to turn next.

I hope you are using Configuration Manager to change the Service account.Using the service.msc applet does not grant required permission

Refer the link : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/975c86b9-3622-418a-8e19-6720a51d7549/access-denied-starting-sql-server-agent?forum=sqlgetstarted

Other way could be to add the Service account in SQL Server as sysadmin and then configure it to run under the required service account.


UNION query question My query is:

SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM
"tema".MMT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL GROUP BY
BRAND,BRAND_GROUP,SUB_BRAND

UNION
SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM
"tema".BGT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL GROUP BY
BRAND,BRAND_GROUP,SUB_BRAND;

and my output is :

BRAND BRAND_GROUP SUB_BRAND TOTAL_INCOME
----- ----------- --------- ------------
GBS   NULL        NULL             10000
SWG   NULL        NULL             10000
GBS   NULL        NULL             20000
STG   NULL        NULL             20000
GTS   NULL        NULL             30000

The problem is that i have 2 categories of BRAND and I want to have just 1. Like this :

Brand       Brand_Group     Sub_brand       Total_Income
GBS                  -          -           30000
STG                  -          -           20000
GTS                  -          -           30000
SWG                  -          -           10000

Can someone help me with an ideea?

Sum after you did the union (hope DB2 does it this way):

SELECT  BRAND, BRAND_GROUP, SUB_BRAND, SUM(INCOME) AS TOTAL_INCOME
FROM    (SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME
         FROM   "tema".MMT
         WHERE  BRAND_GROUP IS NULL
         AND    SUB_BRAND IS NULL
         UNION ALL
         SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME
         FROM   "tema".BGT
         WHERE  BRAND_GROUP IS NULL
         AND    SUB_BRAND IS NULL) AS T
GROUP BY BRAND, BRAND_GROUP, SUB_BRAND;

Just a question. Why do you need the BRAND_GROUP and SUB_BRAND (since they are both NULL)?


How to get desired type of records for desired number of times in oracle I want to get get 6 "double_scoring" records and 4 "singe_scoring" records from my Profiles table based on SCORING_RULE column values. Can you please let me know how can I do this with SELECT query where I am just selecting 10 records with ROWNUM <= 10 limit.

Please try this query:

select * from PROFILES where
SCORING_RULE = 'double_scoring' and
ROWNUM <= 6
union
select * from PROFILES where
SCORING_RULE = 'single_scoring' and
ROWNUM <= 4

Check it here: http://sqlfiddle.com/#!4/7ecc95/1