3 Databases, Best Replication Setup? We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of the master/multiple-slaves kind i.e. db1 as the master, and db2 and drdb as the slaves. drdb is activated only when both db1 and db2 are down i.e. disaster recovery, business cont. sort of thing. Our application also has a rudimentary failover 'feature' in which it automatically connects to the next available db server in case the default active db server i.e. db1-to-db2-drdb, is inaccessible.

I am currently considering the idea of applying a master-master replication between db1 and db2, and making drdb as a slave of db1. In this setup, I'l make db1 as the default active db server (performs all writes and read operations) and db2 the passive one (backup db server). When db1 goes down, db2 becomes the active db server, and dynamically change the master-host of drdb to db2. And so on.

Is this a good idea? What is a better replication setup for this kind of environment? Please give some suggestions.

UPDATE 1:

I also would like to add that these databases are already existent, and I've read somewhere the possibility of key-collisions/error in this kind of setup, what modifications in our current tables/databases configuration should we made before we setup this kind of replication?

Thank you very much!

I wrote up an interesting layout last year which features DRBD pairs in two data centers (DC1,DC2) with as follows

DRBD Pair in DC1 (db1 and db2)
DBVIP for Primary of DRBD Pair1 is 10.1.2.30
DRBD Pair in DC2 (db3 and db4)
DBVIP for Primary of DRBD Pair2 is 10.1.2.40
Have MySQL Circular Replication Between DRBD Primaries
Have the 10.1.2.40 as Master_Host for DBRD Pair 1
Have the 10.1.2.30 as Master_Host for DBRD Pair 2

MySQL high availability, failover and replication with Latency

MySQL Replication : 1 Slave / Multiple Masters

Here is why I suggested this: Using two data centers, you setup automatic failover for DRBD Pair in one data center. Let the other DRBD Pair in the second data center be for DB disaser site with it own local redundancy and failover. Should you ever loses one data center, the other data center is fully read with it own local failover setup. Your app would just have to use the DBVIP of the other database center in such a catastrophic case.

Please keep in mind that using DRBD in conjunction with MySQL is only beneficial if all of your data uses the InnoDB Storage Engine. Hard failovers in DRBD could easily result in crashed MyISAM tables.

Here is another setup to consider:

As with DRBD setups, any DRBD Secondary would provide just a Disk-Level copy of your MySQL Folder. It is available as a warm standby. MySQL is not being run on the DRBD Secondary. If you want the third DB server to become hot standby, ditch DRBD altogether and use pure MySQL Replication. With three DB servers, using db3 at a remote site, simply setup the following:

+--> db1 --> db2 --> db3 -->+
^                           |
|                           V
+<--------------------------+

Using your rudimentary failover, now you have two hot standby servers. You just have to make sure each DB server has a unique server_id value. I also recommend using MySQL 5.5 because it uses SemiSync Replication which is more sensitive to communication failures and stop Replication better. You will have to setup the appropriate heartbeats and timeouts.


Options for dumping Oracle queries to text files with no database server filesystem access I've an application currently producing reports using UTL_FILE and DIRECTORY. We are migrating this application to an environment in which there will be no filesystem access on the database server (including mounts).

One option is to use SQL*Plus on the client to produce the file.

Any suggestions for a low-key reporting tool designed for producing text-file output?

Thanks for any help.

You could email them with UTL_MAIL or POST them to another server with UTL_HTTP

Alternatively, save the complete reports as a clob in a table and query from any client - this is a little more work as you'll have to rewrite your reporting procedures, but is still easier than starting from scratch on the client.


How to store a version sequence per product I need to create a table like the following one:

+----+---------+---------+
| Id | Product | Version |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       2 |       1 |
|  4 |       1 |       3 |
|  5 |       2 |       2 |
+----+---------+---------+

Where ID is an Identity(1,1) and I need the Version to be auto-populated depending on being inserted Product.

A similar effect could be achieved upon SELECT by using ROW_NUMBER() with PARTITION like this:

SELECT ID, Product,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY ID) AS Version
FROM Products

What are my options?

If you can trust the IDENTITY column to be ever-increasing, I see no reason to store this information. It becomes redundant because it's information you can obtain from data already stored in the table (as you have already shown). Storing it is wasteful in terms of:

additional disk space
additional memory, since the data will hopefully be in memory most of the time
calculating the version # on every insert
re-calculating the version # for all related rows whenever any row is deleted

Now, the trade-off is measurable, since it is possible that the data is updated very infrequently, yet queried millions of times between updates. In that case, it may make sense to pre-calculate the data and store it. But don't prematurely optimize for that; make that decision when you can demonstrate that calculating it at runtime is adding measurable impact to your workload.

If you can't trust the IDENTITY column to be ever-increasing (after all, someone could go back and fill gaps left by deletes using IDENTITY_INSERT), then you could add a SMALLDATETIME column as NOT NULL with a default of CURRENT_TIMESTAMP, at a cost of 4 bytes per row (or DATETIME2 at varying cost depending on precision, if you need more granularity than minute). Then you should still calculate the version # at query runtime, IMHO, using a similar query to the one you proposed:

SELECT Id, Product,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY inserted_date)
FROM dbo.Products; --*

* Please always use the schema prefix

If you really, really, really, really, really think this is a good idea, then you can achieve this with an after trigger. Note that even with a very granular datetime with a default, you can't rely on just that for order-of-insert semantics (think multi-row inserts like below). Again, assuming you can rely on the IDENTITY column to be ever-increasing and never back-filled, you can use that column to break any ties.

USE tempdb;
GO

CREATE TABLE dbo.Products
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  Product INT,
  inserted_date DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [Version] INT
);
GO

CREATE TRIGGER dbo.AddRedundantInfoToProducts
ON dbo.Products
AFTER INSERT, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH src AS
  (
    SELECT Id, [Version], rn = ROW_NUMBER() OVER
      (PARTITION BY Product ORDER BY inserted_date, Id)
    FROM dbo.Products
    WHERE Product IN
    (
      SELECT Product FROM inserted
      UNION ALL
      SELECT Product FROM deleted
    )
  )
  UPDATE src SET [Version] = rn;
END
GO

Now we can test it out:

INSERT dbo.Products(Product) VALUES(1),(1),(2),(1),(2);

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

Results:

1   1   2015-05-27 16:18:19.723 1
2   1   2015-05-27 16:18:19.723 2
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 3
5   2   2015-05-27 16:18:19.723 2

Now let's delete a couple of rows:

DELETE dbo.Products WHERE Id = 2;

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

Results:

1   1   2015-05-27 16:18:19.723 1
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 2
5   2   2015-05-27 16:18:19.723 2

Now we'll insert a few new rows:

INSERT dbo.Products(Product) VALUES(2),(2),(3);

SELECT Id, Product, inserted_date, Version FROM dbo.Products;

Results:

1   1   2015-05-27 16:18:19.723 1
3   2   2015-05-27 16:18:19.723 1
4   1   2015-05-27 16:18:19.723 2
5   2   2015-05-27 16:18:19.723 2
6   2   2015-05-27 16:19:00.773 3
7   3   2015-05-27 16:19:00.773 1
8   2   2015-05-27 16:19:00.773 4

Clean up:

DROP TABLE dbo.Products;

Of course this can be easier if you control DML to the table through a stored procedure; you could calculate the new version(s) without the need for a trigger. However, I feel this is a waste of effort to even show you; I wouldn't recommend this approach at all because you can't ever really ensure all data manipulation will go through stored procedures, which means your data could easily get out of sync anyway (whereas the rank calculated at query runtime is always guaranteed to be current). Given the same table above:

CREATE PROCEDURE dbo.InsertProductRow
  @Product INT
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;
  DECLARE @rn INT;
  SELECT @rn = COALESCE(MAX([Version]),0) + 1
    FROM dbo.Products
    WHERE Product = @Product;
  INSERT dbo.Products(Product, [Version]) VALUES(@Product,@rn);
  COMMIT TRANSACTION;
END
GO

CREATE PROCEDURE dbo.DeleteProductRow
  @Id INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Product INT;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;
  SELECT @Product = Product FROM dbo.Products WHERE Id = @Id;
  DELETE dbo.Products WHERE Id = @Id;
  ;WITH src AS
  (
    SELECT Id, [Version], rn = ROW_NUMBER() OVER
      (ORDER BY inserted_date, Id)
    FROM dbo.Products
    WHERE Product = @Product
  )
  UPDATE src SET [Version] = rn;
  COMMIT TRANSACTION;
END
GO

The following batch will yield the same results at each step as above:

EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 2;
EXEC dbo.InsertProductRow @Product = 1;
EXEC dbo.InsertProductRow @Product = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

EXEC dbo.DeleteProductRow @Id = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

EXEC dbo.InsertProductRow @Product = 2;
EXEC dbo.InsertProductRow @Product = 3;
EXEC dbo.InsertProductRow @Product = 2;

SELECT Id, Product, inserted_date, [Version] FROM dbo.Products;

Don't forget to clean up:

DROP TABLE dbo.Products;
DROP PROCEDURE dbo.InsertProductRow, dbo.DeleteProductRow;


The most generic e-shop DB schema Let's say I create a schema for an e-shop what potentially sells everything: laptops, phones, tires, snacks etc.
Considering this, I cannot create a new table/entity for each good category the shop sells and describe the good's properties by new attributes.
So, I went to more generic model to describe each product

To me, it seems fine but as soon as I've tried to implement this solution, it's appeared to impossible to execute any query like

select names and prices of all laptops where ssd capacity >= 512 and weight < 2kg and price <= 2000 money

How is possible to change this schema and keep the ability to store information about any product?

Your query would look like this
SELECT Name
FROM ARTICLE
WHERE Id_Article_CATEGORY IN (SELECT ID FROM Article_category WHERE Name = 'Laptop')
AND ID IN (SELECT id_ARTiLCE FROM Article_Property WHERE id_PROPERTY IN (SELECT ID FROM Property WHERE (Name =  'SSD' AND Unit  >= 512) OR (Name = 'weight ' AND Unit < 2)))
AND price <= 2000;

article_property is a bridge table, you need there two columns atleast id-article and id_property to link both tables. when you have a property you need to get its id and see if there are articles that have this property.
that is valid for all bridge tables in your eer.
at this point you start with a schema and change it when the need arises. Your structure is flexible yur programming too, you you can realize almost anything.
if you find a weak pint you can change the structure by adding columns, removing is more complicated, because tigger functions and so on have to be checked.
you should still go back and examine existing e-commerce solutions, for your basic needs there a some tutorials to implement it, also they have a gui in php or python with a functioning framework and even when you want to implement your own look for laravel and other frameworks,that help you with a lot of functions, there is no need to invent the wheel all over again


How to remotely run commands for a DB2 instance, preferably using ibm_db? My objective is to see the size of Audit Buffer size, normally I would use below command on my Db2 instance

db2 get database manager configuration | grep AUDIT_BUF_SZ

But now I need to do it remotely over an Ubuntu 18 system which has my Db2 instance. For SQL query execution I used ibm_db library, an example of which is below

select="select * from department"
stmt_select = ibm_db.exec_immediate(ibm_db_conn, select)
cols = ibm_db.fetch_tuple( stmt_select )
print(cols[0])

So I need to know is there any way of getting the output of my command that can be implemented through Python, else any other library that can come in handy for this.

Use the SYSIBMADM.DBMCFG  administrative view for this.


SQL Query returning same value while using where 1 condition I have created a temp table and inserted the values as given below.

create table #temp( val int );

insert into #temp values(333);
insert into #temp values(222);
insert into #temp values(111);

On querying the below select statement I got 333 as the answer.

Select *
from #temp a
Where 1 =(
    Select COUNT(VAL)
    from #temp b
    where a.val <= b.val
);

Result:

val

333

Can you please help me understand how SQL server came to this solution.

Rewrite your query such a way:

SELECT a.*, x.cnt
FROM #temp a
CROSS APPLY (
    SELECT COUNT(VAL) AS cnt
    FROM #temp b
    WHERE a.val <= b.val
) x
--WHERE x.cnt = 1

If you uncomment the where clause you would get 333 | 1 as a result. You request a row from the outer table which doesn't have duplicates or bigger values.


Automated regular tests of your backups I was doing some investigation on how other DBAs go about performing automated regular tests of their database backups to ensure recoverability in case of disaster and I'm quite disappointed at what sparse topics I found on the topic.
I'll try to describe the solution that I came up with for my company in the comments, but how do you guys ensure that your backups are actually recoverable?

Do you recover your backups to test servers on regular basis? Using scripts?
Third-party software with this capability?
Running verify-only on the backups?
Letting your junior DBA do recovery tests?

Let me know if I should rephrase or add anything to the question.

UPDATE

After some time and polishing, i decided to publish my script if anyone's interested. https://github.com/curiebabz/SSARS

Provided that you have alerts set up on the jobs, Log Shipping is a feature that is available on all editions, doesn't require a whole lot of effort to set up, and I believe fulfills all of your requirements.

I would avoid any wizards that you might stumble upon in Management Studio. Essentially you need three jobs for the following tasks:

Initialize the secondaries (restore the latest full backup with NORECOVERY)
Take full and log backups
Restore log backups with STANDBY, NORECOVERY

Essentially, testing the restores of your full and database backups will be verifying them (and you can add other tasks to this process, such as bringing a restored database into recovery and checking data, running CHECKDB, etc)

I go into great detail about how I have implemented log shipping (different purpose, but generally the same approach):

Readable Secondaries on a Budget


Linux command to kill all MySQL sessions from a specific User? How can i kill all sessions from a user in mysql using a linux command line command?
 I have a user that is using all on my database connections and insted of killing one by one i want to make a script to kill them all !

Try this :

mysql -uuser -ppassword -e 'show processlist' | grep USER_NAME | awk {'print "kill "$1";"'}| mysql -uuser -ppassword

The command will list the processes running in the database and return a kill 'query id'; command and pipe it into mysql again.
 Be carefull with this command as there is no rollback form it :)


SQL SERVER - "ApplicationIntent=ReadOnly"Listener redirect to fully qualified domain name Hi all my DBA has setup a primary myNodeA and secondary node myNodeB and I'm trying to access the secondary node using "ApplicationIntent=ReadOnly" in JDBC connection string.

The problem we are facing is it redirects to just a server name and not a fully qualified domain name.

is there a setting or configuration in SQL Server to achieve this?

For example using that parameter redirects to  myNodeB server name only

This needs to redirect to myNodeB.myDomain.com

My initial suspicion is that the initial setup didn't include the fully qualified name, but check for certain with the following statement:

SELECT ag.NAME AS "Availability Group"
    ,ar.replica_server_name AS "When Primary Replica Is"
    ,rl.routing_priority AS "Routing Priority"
    ,ar2.replica_server_name AS "RO Routed To"
    ,ar.secondary_role_allow_connections_desc
    ,ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
INNER JOIN sys.availability_replicas ar ON rl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas ar2 ON rl.read_only_replica_id = ar2.replica_id
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY ag.NAME
    ,ar.replica_server_name
    ,rl.routing_priority

The query was gratuitously lifted from this MSDN blog: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

In addition, make sure your JDBC driver supports the ApplicationIntent=ReadOnly parameter.


Is there a way to configure a distributed environment with mongodb? Can I configure a distributed environment system with mongodb? (For example, master and slave structures)

My job is to insert the data into the master and read the data,
Slave nodes read data by dividing it, send it to the master node,
It is a method to combine the data received from the slave nodes in the master node.

I have now set up the slave and master. (mongod --master, mongod --slave)

Nowadays, master - slave system is not used anymore. You create replica set (RS) where you have one PRIMARY and at least one SECONDARY (plus arbiter), but minimum two SECONDARY are preferred.

In the RS, write is happening ALWAYS to current PRIMARY and preferred read is also from PRIMARY, but you can configure reading happening from SECONDARY too, but.. Information is not guaranteed to be up-to-date (until SECONDARY has replicated it OR write concern is so strict that OK is not returned until all nodes have it; what is not recommended).


Create Oracle ASM Disks Without Installing Oracle I have been asked to setup workstations (VMs) for an instructor's Oracle 11g R2 Workshop. I am finding the documentation that was downloaded from the Oracle Academy site extremely underwhelming. I am supposed to be configuring the environment with a set of ASM disks as LO devices that the students will then use to create an ASM disk group when they install Oracle Grid and as disks for the Fast Recovery Area.

My understanding is that the student will be installing Oracle Grid, Oracle Database, then creating a database, and that I do not need to have Oracle installed beforehand. However, the documentation makes use of the oracleasm command which comes with the Oracle installation.

Is there a way to set this up without installing Oracle? Has anyone ever setup/taught these workshops? I tried downloading the Oracle ASMLib tool here but it says it needs oracleasm as a dependency.

Mat is right. ASMlib is only one way to created candidate disks for ASM. I would recommend you use udev as detailed here.


MySQL: self-administration for multiple users My MySQL users have rights to administer their own databases, including create/drop database. I do not have a shared phpMyAdmin installation, however I do let users install it themselves.  Most of the users do not need it and they end up mailing me to create databases for them.  That is not a problem for me but I want to give the users more control and at the same time keep things simple.

Users have access to a simple web based control panel and I want to add a GUI where they can create, drop, rename their databases and do one or two more basic MySQL things like view stats.  Most of the users do not need the capabilities of phpMyAdmin, just the ability to create and drop databases.

Which MySQL user should do these actions like create/drop etc?
Should I:

Create an additional admin user which has grants to create/drop databases for all users and set right grants on them.  I would not let users create/drop databases by themselves although allowing create/drop would probably not break anything.
Let the user do it with his own MySQL user?  That would mean the user will need to enter his MySQL password every time he wants to create/drop/whatever.  I could save his password  as plain text in his account table (arghhhh... no way).

I like the idea of letting the user do everything with his own MySQL user and password because he can only break his own things and it makes my application more secure at the same time.

However, I do not want to make it complicated by forcing users to remember their MySQL password.  On the other hand I do not want to save clear text passwords for their MySQL account.  This approach is broken because user could change his password with SET PASSWORD = PASSWORD(?) and my clear text password would not be up-to-date and web GUI would no longer work.

The first solution is surely the way to go but I still have a feeling there is perhaps a third solution I have missed.  Is there something similar used by some big webserver adminstration tools? Or are they also using a dedicated admin user for this stuff?

I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.


How does InnoDB store tables in ibdata file? There are two tables and their default engine is InnoDB.

I want know the following

What's inside the ibdata file ?
How does InnoDB store these two tables ?

InnoDB Architecture

Percona created this picture a long time ago. As you can see, ibdata1 contains many classes of data structures. There are as follows:

Table Data Pages (if innodb_file_per_table disabled)
Table Index Pages (if innodb_file_per_table disabled)
Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables)
Double Write Buffer (Handles Data Redundancy to Support Crash Recovery)
Insert Buffer (Handles Updates to Secondary Indexes)
Rollback Segments (Manages Reverse Engineering of Uncommitted Transactions)
Undo Space (Containers of Information for Rollback Operations)

BTREEs in InnoDB
If you are concerned how indexes are stored, please read my earlier posts about InnoDB indexes

Apr 04, 2012 : How does innodb_stats_on_metadata relate to query execution plan stability?
Jun 28, 2012 : Benefits of BTREE in MySQL
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Jan 09, 2013 : Is ANALYZE useful immediately after creating an InnoDB index?
Mar 27, 2013 : Are there implicit indexes in InnoDB like MyISAM?
Jun 14, 2013 : Does the size of the primary key contribute to table size?


How to increase Max_used_connections? I have a registration application. 10000 people want to access the server but only 3000 quotas available. 3000 users registered successfully online within 3 minutes on the registration day. However, some users encounter 504 error page (Gateway time out). I checked the max_used_connections variable, the value is 10 only. The max_connections value is 151.
The max_used_connections is defined as

the maximum number of connections that have been in use simultaneously since the server started.

I wonder why the value is only 10. Is it too small compared with 3000 users registered successfully online within 3 minutes?

Change the configuration file to increase max_connections.  But be aware that that can increase RAM usage.  How much RAM do you have?  What is the value of innodb_buffer_pool_size?
You must streamline the "registration process" as much as possible.  Please provide all the SQL that happens for registration so we can help optimize it.
Max_used_connections is a "high water mark".  You never had more than 10 connections at the same time, at least not since the last restart.  10 is fine.  And it cannot be changed.  Since it is much smaller than Max_connections (151), there does not seem to be an issue.  10 connections at a time is fine.
Look at the STATUS Connections.  That is a "counter" that has been running since restart.  It will probably say more than 3000.  3000 in 3 minutes is only 17/second, not difficult if the registration process is simple.
The timeout probably indicates something else -- such as too much data flowing across the network.  Does registration involve showing some biggish images on the registration page?
Instrument your registration process to see how many milliseconds the whole process takes, and how long each SQL statement takes.
What was the value of max_user_connections?


Multiple junction tables? I am working on a system where you have users, and you have Fields of Study or 'fos' for short.  Each user can have many fos and each fos can have many users.  So we are dealing with a many to many scenario.  Here is the rub, each user can have many fos they act as an instructor for, and can also have many fos they act as a student for.  How is this relationship defined?  Would you have two junction tables one like user_instructor_fos and one like user_student_fos?

You can simply add an "is_student" bit column to the relation "UserToFOS", and create a unique key on user_id, fos_id and is_student.


Query to check if job already belongs to jobserver in MSSQL I am working with a legacy migration script that was setup to add a job (With a number of steps) to a MSSQL server. I am trying to build in some semblance of re-runable without it completely blowing up.

However I am now down to this line :

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

If the job already belongs to the local server, then this line will error. It's very feasible that this might be run several times.

Is there a query that I could run to check whether a job already belongs to local (Or any other servername?)

Found my solution.

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @myJobId AND server_id = 0))


what is a good use case for splitting user table by role I have a requirement where there are primarily 5 roles in the system. Each of these roles increase in hierarchy from junior to senior most role. Each junior role is a subset of its immediate next senior role in terms of access privileges. The roles and their hierarchy in increasing order would be J1-J2-S1-S2-SuperAdmin.

Each role needs to store additional information about the user that is only specific to that role for eg. J1 needs to store grade and location, whereas J2 will only need to store grade. S1 and S2 that manages J1 &J2 needs to store neither.

In a nutshell, its all about separating the admin role users from the non admin role users. The admin roles have a different set of fields which need to be mandatory and the general users who are not admin need to have a separate set of fields that need to be mandatory. For eg. First name, last name are mandatory for all users. Whereas location is only mandatory for non admin users. IF I keep all users in one table, then I cant enforce allowing nulls in certain fields for a certain role while disallowing it for other roles. Also due to admin users being far fewer than non admin users, the data access for admin users at login time could be faster for users belonging to admin roles? There is also the possibility of feilds being added or removed changes being made as which fields are mandatory or non mandatory for non admin users.

I am seeking opinions if anyone has encountered such a situation before and if anyone recommends splitting up tables by roles for such reasons. In such a scenario as mentioned above, what are the arguments that favor storing each role's users in a separate user table? Wont I need to scan each table and find which table the user is in before I authenticate the user? Would there be any specific problem if all such users are stored in the same table? What are the pros and cons of either approach? if I seem to be favoring such a design as mentioned above its only because I'm playing the devil's advocate.

What you are describing is an example of the general situation entity sub-typing.

There are two basic ways to handle entity sub-typing:

One method places common attributes in a table and distinct attributes in separate tables, linked using 1:1 relationships to the common table.  Some other considerations:

The common table may have a partitioning attribute, which is a field that indicates which sub-type of thing each record is.
The primary key of each sub-type table is a foreign key to the common table.
You can create views which merge the columns from the common table and each sub-type table, so that for querying purposes you appear to have a "one stop shop" for each sub-type of thing.  This can simplify your queries since it leaves you one less join to write out each time.
This approach makes it easy to apply referential integrity if you have child tables that apply to only a single sub-type (or all sub-types).

Another method is to place all attributes in a single table, and then use declarative constraints and/or triggers and/or application logic to enforce sub-type specific business rules.  In this case:

A partitioning attribute must be used to indicate which sub-type of thing each record is.
This makes queries easier, but it spreads your business logic out in a way that many people will find confusing and undesirable.
This approach can lead to many nullable columns, which makes some people itchy.
This approach doesn't help you if you have child tables that apply to individual sub-types, but it is the only sane approach if you have child tables that apply to multiple, but not all sub-types.

In your case you could pick either approach.  Which might be better depends on your preferences and on exactly how many distinct columns there are per sub-type.


Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need to migrate our database to its own server for one of our systems. I have a choice between two solutions: use Amazon RDS, or just launch a new Amazon EC2 box and install MySQL on it.

RDS, being a dedicated database service provided by the same company as EC2, seems like it ought to be the obviously better option. However, when I look at the pricing for the two options (see http://aws.amazon.com/ec2/pricing and http://aws.amazon.com/rds/pricing) it seems that an RDS server costs almost twice as much as an EC2 server for a box with the same specs.

Given that I'm capable of handling backups myself and that EC2 offers the same ability to scale up the instance as required that RDS does, I can't see any reason at all to use RDS instead of EC2. It seems like I'm probably missing something big, though, because if I were right, nobody would use RDS. What exactly am I missing, and what are the advantages of RDS over installing your own database on an EC2 instance?

I'm a big AWS fan in general... but RDS, not so much.

@RolandoMySQLDBA has pointed out are some pretty good points against it.

The only advantage I see in RDS compared to MySQL on EC2 are the ability to do point and click snapshots, clones, and point-in-time recovery, but these are not nearly sufficient to make up for the loss of control and flexibility and they most certainly don't justify the price being higher.  RDS is sexy in some ways, but you can't ultimately trust what you can't ultimately fix, because you can't get to all the moving parts.

I don't like not having the SUPER privilege.  I don't like not being able to tail the error log.  I don't like not being able to run "top" or "iostat" on my database server to see how the cores and drives are enjoying the load.  I don't like not having access to the federated storage engine.  I don't like the thought of paying for a hot standyby (multi-AZ) backup master machine that I can't even leverage as a read replica.  Sure, there are perfectly reasonable explanations why all of these constraints have to be in place for MySQL to be successfully packaged and sold as RDBMS-in-a-box.  The only thing is, RDBMS-in-a-box "solves" a whole series of problems I don't have... and gets in my way, causing new problems.

But the absolute deal-breaker for me with RDS is the complete lack of access to the binary logs and replication.  Binlogs, especially row-based, are a fantastic recovery tool for minor disasters, but they are of no help to you if you can't access them.  Want to configure an on-premise server at your office as a read-replica of your production database in RDS?  Something to take local backups from, do reporting, have on hand for disaster recovery should something unthinkable happen to your data that lives in RDS?  That's an idea that is simultaneously obvious and brilliant.

Oops, sorry, direct access to replication is not available.  Sure, you can pay for read replicas... but only as other RDS instances.  Not a value proposition in my book.

Update: One Significant Change in RDS for MySQL 5.6

I still prefer running my own server (even in EC2) as opposed to running RDS for a number of reasons, including the lack of support for User-Defined Functions, the inability to use the Federated Storage Engine, and the inability to have the one extra connection available for emergency access... however...

Amazon has made a significant change in MySQL 5.6 for RDS that eliminates one of my major objections -- perhaps my largest objection: the binary logs are now accessible and you can run a non-RDS instance as a slave, or connect other utilities to the server that read the binlog stream.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

Officially, the documentation indicates that they are exposing this so that you can set up a slave for the purpose of doing a live migration -- you synchronize the foreign future master server from the existing RDS instance using mysqldump, then connect it to RDS as a slave to get a live feed of updates via the replication stream until your application is migrated to the new master -- but unofficially, you can do this on an ongoing basis as long as you don't expect them to support you... which, to me, seems reasonable.

This was confirmed in a recent Webinar, in a conversation that begins at around 56:45:

  "You can keep it in a replicated state indefinitely...

  "...as long as you take the responsibility to maintain the replication..."

  "We are not preventing you from doing ongoing replication if that's what you want."

This new capability was enough for me to drop my blanket objection to using RDS in our public-facing website-backing MySQL instances, where we don't use FEDERATED or some of the other things as much or at all.

So I'm still not in favor of it, but I'm no longer against it, since having a live stream of the binary logs puts me ultimately back in control of the data in real time and the responsibility for ensuring that no transactions are lost in a catastrophic outage is back with me, because I, as the DBA, am back in control -- which is exactly how I want it.  Having a third party vendor to point fingers at, or file a lawsuit against, or whatever, doesn't get your lost data back if it disappears down a black hole inside a black box.

Management seems to like the "idea" of RDS and doesn't object to the cost difference, so we are now launching all new web sites with RDS behind them.

The point and click point-in-time recovery, I admit, is a nice feature in RDS... it doesn't alter or disrupt your existing machine -- instead, it fires up an entirely new instance, using the backup that was closest-in-time to the selected point in time, and then applies the necessary binlogs to bring that new machine forward to the point in time that you've specified.

Related to this, but in the other direction, it's also possible, now, to use a similar strategy to migrate a live MySQL database into RDS... you can connect an RDS master (presumably, typically, this would be a newly-deployed instance) as a slave of an existing system so that the RDS instance has the live version of the data at the time you migrate into it.  Unlike access to the RDS binlogs for outward replication, which only works in 5.6, the inward replication is supported in RDS beginning with 5.5.33 and 5.6.13.


How to index "almost" unique columns? I have a table that saves the needed information to send notifications to users. now the UID columns in this table is almost unique because mostly each user subscribes with one device.
but it is possible to have multiple rows with same UID so unique index is out of picture and because of almost unique nature of data i don't think usual index be useful either as it's going to take almost same time to search index as whole table.
Currently the solution that comes to my mind is saving primary code of each row of notifications table in a column in Users table in comma separated format.

An index doesn't need to be unique to be efficient, and in fact I've rarely found making my indexes unique made much of a difference in terms of performance, in my experience. (It does in certain cases, data size dependent, but not enough to be measurably important to me so far.) On that note, my experience is within Microsoft SQL Server, so knowing which database system, version, and implementation (when applicable) would be helpful here.
That said, in most modern Relational Database Management Systems (RDBMS), the standard index is a B-Tree index. That means your data is stored in a B-Tree logical data structure which is much more efficient for searching than the entire unindexed Table itself. It doesn't matter if your data is "almost" unique vs perfectly unique. If your data was completely redundant (every value for the column(s) you anticipate indexing are exactly the same) then indexing probably won't help you much, but in your case a regular B-Tree index sounds like it would work very good.


Dimensional model or Relational model for data warehouse? I have a pretty trivial questions in data warehouse,

I know we use Dimensional data model to construct data marts and use Relational data model in staging area.

What data model technique we use to store data in central data warehouse?
Can we use both? Which is most commonly used?
We have RDBMS for relational database model, what is the database used for dimensional modelling?

The point of a data mart / warehouse is to facilitate analysis for non-DBAs. The relational model is wonderful for transactional databases to speed writes integrally, but is harder to understand for non-DBAs.

A data warehouse is a collection of data marts, or can act as a central data mart. If you are going to use dimensional modelling in your data marts (as you should), then you should use it in your warehouse as well.

You will find that most companies use regular RDBMSs (say, Oracle, SQL Server, Postgres) for their data warehouse, though you probably want one that supports materialized views and easy table partitioning. Some companies use columnar databases (say, MonetDB) or NoSQL databases (say, HBase) as well.


SQL Server: can dynamic data masking be safe when providing a database backup? I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:

email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL

I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe.
Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment.

I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this).

CREATE USER user_name WITH PASSWORD = 'strong_password';

Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?

No, I'm sorry. Your partner has a sysadmin login for their SQL Server, and a sysadmin can do everything with everything inside a SQL Server instance - including the databases.

I suggest that you develop a routine to clear out the sensitive data. I.e., perform the backup. Restore it to a different database name, clean it. And now produce a new backup.


Mssql subquery inside a substring I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.
The subselect collects all rows and converts them into a single string by appending them with a separator ";".
This is what I need:
(SUBSTRING(
    (SELECT
                    d1.name +'; '
    FROM
                    data1 d1
                    inner join data2 d2
                    on d1.id = d2.id
    WHERE
                    d1.id = dOfOutterSelect.id
    ORDER BY
                    d1.CreatedOn FOR XML PATH('')

    ),
LENGTH(),1))

As "Row name",

Use the STUFF(...) T-SQL function to strip the last character off.  First REVERSE the string from the query results, then use STUFF, then REVERSE the result again.
Like this:
SELECT REVERSE(STUFF(REVERSE('this is my string'), 1, 1, ''))

The result looks like:

Results

this is my strin

Here's the Microsoft Docs pages for STUFF and REVERSE.
Actually, looking at your query more closely, you can refine it slightly to eliminate the use of the REVERSE function.  Take the example below, where I'm concatenating the names of all the columns in the database with a semi-colon separator.
SELECT STUFF((
    SELECT N'; ' + c.name
    FROM sys.columns c
    FOR XML PATH (N'')
    ), 1, 2, N'');

I add the semi-colon at the start of the string, then simply use STUFF to replace the first 2 characters with "nothing".
So the results look like:

Results

Column1; Column2; Column3

Newer versions of SQL Server (2017+) support the use of the STRING_AGG aggregate which can significantly simplify this kind of query.  To get the same output as my query above, using STRING_AGG, you'd write it like this:
SELECT STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ')
FROM sys.columns c;

There is CONVERT(nvarchar(max), ...) wrapped around the c.name column to coerce the STRING_AGG function into using a string of sufficient length to contain all the column names in my database.  If you leave that out, SQL Server reports an error:

Msg 9829, Level 16, State 1, Line 10
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.


Best way to create relationship between multiple tables I have a question about how to best handle a table that would be shared among multiple relationships, but shouldn't really be joined with all these tables. For example, suppose I have a table that has US States in it. Then, in this example, I have maybe three or four other tables that have a relationship with the States table, like: a Users table (where the user is from), a Car registration table, an Email table (maybe on this system we track sent and received emails and where they originate from) and some other table. If I joined all of these tables to the State table, it kind of makes it look like all of these table should have some sort of relationship with one-another. Maybe this is so for a few tables (Like User and Car registration) but not necessarily between all the tables (User and Email table for example). Or another example might be that we have two tables, Employees and Work_Office. Each has there own state. And each is joined to one-another, creating a loop (Work_Office has many Employees. Employee belongs to State and Work_Office belongs to State). Now we have a loop.

So, is it better to have multiple "State" tables? It doesn't seem like it would be. But at the same time, I don't want to create relationships that might not really exist? Or is it better to just have the tables but not actually create any sort of relationship?

No, you should not have multiple State tables.  Having a single table with relationships to the others is the right way to approach this.  The perception that this somehow implies that the other tables are linked, other than they share a State, is purely in your head - such an interpretation would not be common practice.

You should distinguish between your logical data model and your physical database design.  I would recommend adding the relationships to State into the logical model.  You may choose to omit the foreign key constraints from your database for performance or other reasons.

There's a post on Dell's Toad site which I can't find just now.  It's about a 200 table DB, every one of which has a UpdateUserId column and corresponding FK relationship to the User table.  The plan for a DELETE User.. statement is a thing of beauty - a perfect left-deep triangle of 200 joins!  They would have been better without the constraint.

Edit: here's the link.  I mis-remembered the details but the principle's the same.


By using SQL Window Functions, how can I get the lead of the last row in a partition? Considering the following simple example:

select lead(c) over (partition by a order by a)
from (values
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) t(a,b,c)

The first two rows are records with the same ID (column a) and "date1" (column c). The ID duplication exists due to an unnest operation made in an array column that might have different sizes or number of elements. The unnest operation results in column b.

Now, I want to get the lead date of the next ID, "date2". The query I wrote outputs "date 1" for the first record, but I actually want to get "date 2". I'm not interested in the static solution to this problem, which would be lead(c,2) over (order by a), but on how to get the lead of a dynamic sized window.

Expected output:

date2
date2
NULL

You can specify a window that excludes the current group:

SELECT first_value(c) OVER w
FROM (VALUES
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) AS t(a,b,c)
WINDOW w AS (
   ORDER BY a
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP
);

 first_value
-------------
 date2
 date2

(3 rows)


Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id).

Clients compute new intervals for one variable and store them in that table. To preserve consistency (for a variable_id I don't want intervals to overlap), clients first check (select) what intervals we have and then insert, delete and/or update accordingly (or do nothing).

If two clients do that simultaneously there is of course a risk that I insert overlapping intervals, a thing I don't want.

Well actually my question is simple: what should I do?

It seems that a SELECT .. FOR UPDATE doesn't work, because this locks existing rows, and that's not enough for me, as rows may be inserted. I'd rather need the result of "SELECT * FROM intervals WHERE variable_id = 1234" to be "safe" in some way.

I have implemented a solution that uses SERIALIZABLE transactions. The result is that if two clients enter this select/update-delete-insert transaction, one will fail (could not serialize access due to read/write dependencies among transactions or could not serialize access due to concurrent update), in which case I just have to run again my select/update-delete-insert logic to try to insert more appropriate intervals.

The issue here is that the serializable transaction seems to lock the entire table. But if a client wants to update variable 1234's intervals, while another one wants to update variables 4321's, there should be no problem, both transactions should be able to run happily.

I'm no SQL pro and can't even decide whether this is a simple classic sql question or a difficult issue.

Some remarks :

PostgreSQL 9.1.1
There's actually a lot more going in the transaction. Not only I update the intervals but I also insert corresponding variables' values in another table. Intervals in the intervals table are the intervals on which we have the variable's values. However I don't think this interferes with the issue described above.

Regarding the SERIALIZABLE transactions, be sure to pay attention to the "for optimal performance" tips near the bottom of this section: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE  These can reduce your rollback rates quite significantly.

That said, and not to discourage use of the SERIALIZABLE transaction isolation level, what you describe sounds like exactly the sort of problem that EXCLUSION CONSTRAINTs were designed to solve.  Where there is a declarative constraint to enforce a business rule, that is almost always better than using imperative coding (in triggers or application code).

http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

To use this feature with dates you would currently need to use the TEMPORAL module:

http://pgfoundry.org/projects/temporal/

You will probably want to migrate TEMPORAL columns to a RANGE when you move to PostgreSQL version 9.2 (expected to be released this summer).  The 9.2 RANGE feature, the TEMPORAL data type, and the EXCLUSION CONSTRAINT feature were all the work Jeff Davis to address problems like that posed in the question.  They were designed to work together.

Full disclosure: The SERIALIZABLE transaction behavior in 9.1 was developed by myself and Dan R.K. Ports of MIT.


Availability Group - how much data was lost after forced failover Let's consider a SQL Server AlwaysOn cluster with two nodes in asynchronous replication.
Is there any way to calculate how much data was lost after forced failover?
I mean in terms of time, to be able to know "I lost 1 hour of data or 1 minute".
I have considered checking LSNs but I don't know how to translate them into datetime.

Go to SSMS
Object Explorer
Expand Always On High Availability
Expand Availability Groups
Right click your AG
Select Show Dashboard

In the dashboard, above the list of AG databases and their status, there is Add/Remove Columns button
Pick Estimated Data Loss (time) and add this column to dashboard
You will be able to see the estimated data loss time for each DB
You can check this dashboard right before you are going to do forced failover, to get the idea of how much data can be lost


A database differential backup without a full backup At 10 pm, I scheduled a SQL Agent job to do a daily backups with the schedule:

Daily Full: 22:00
Differential: Every 4 hours
Log: Every 30 minutes

Now when I check my backup set(let's say at 22:30), I see that a differential backup has happened without any full backup!!.

I was not expecting to see the backup job up and running before 16:00 tomorrow when the first full backup will take place, however, I have a differential backup without a full backup? How is that possible?

It is not possible to perform a differential backup of a database if no previous backup was performed. A DIFF (differential) backup relies on the previous FULL backup.

  A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

Reference: Differential Backups (SQL Server) (Microsoft Docs)

Proving the Thesis

You can check this with the following steps:

Create a New Database

USE [master]
GO
CREATE DATABASE [Q209724_Database]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Q209724_Database', FILENAME = N'C:\SQL\SQL_DATA\Q209724_Database.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Q209724_Database_log', FILENAME = N'C:\SQL\SQL_LOGS\Q209724_Database_log.ldf' , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Q209724_Database] SET COMPATIBILITY_LEVEL = 130
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Q209724_Database].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

The database is created and has not yet been backed up.

Perform a DIFF Backup of The New Database

BACKUP DATABASE [Q209724_Database]
TO  DISK = 'C:\SQL\BACKUP\Q209724_Database.bak'
WITH DIFFERENTIAL,
     NOFORMAT,
     NOINIT,
     NAME = N'Q209724_Database-Full Database Backup',
     SKIP,
     NOREWIND,
     NOUNLOAD,
     STATS = 10
GO

Output

Msg 3035, Level 16, State 1, Line 91
Cannot perform a differential backup for database "Q209724_Database", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 91
BACKUP DATABASE is terminating abnormally.

So if you haven't previously performed a FULL backup, you will be unable to perform a DIFF backup.

Conclusion

After your database was created somebody or a backup solution has performed a backup of your database.

Checking Backup History

You can change the database name in the following script to the name of your database and check the backup history for a backup of the database:

SELECT
   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   -- msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   -- msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name,
   -- msdb.dbo.backupset.description,
   -- msdb.dbo.backupset.is_copy_only,
   -- msdb.dbo.backupset.is_snapshot,
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   'EOR'

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE 1 = 1

AND     database_name IN ('Q209724_Database')  -- database names
ORDER BY
           2 desc,3 desc

You should see an entry containing a Full remark for the backup_type column in the result set.

You can then analyse when and possibly who/what performed the FULL database backup.


Multiple SQL Server instances with FileStream enabled Is it possible to have two SQL Server instances, one 2008 R2 and another 2012, to run on the same machine with both having filestream enabled?

I tried to setup this in local dev machine and I can get the services running. But when I try to read a filestream from the 2008 R2 instance, I get a ArgumentException.

The path in both instance looks alright.

SQL Server 2008 R2

\\DEVMC\MSSQLSERVER\v1\TESTDB\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960

SQL Server 2012

\\DEVMC\EXPRESS2012\v1\TESTDB12\dbo\coreFileStore\fileData\525F2031-8D8C-45FF-8386-E1DD5F11C960

Is there any magical setting which could make it work in both instances or is just simply not do-able?

Here is the exception stacktrace when trying to access the file using C# test code.

System.ArgumentException : An invalid parameter was passed to the function.
   at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access)
   at SimpleTests.FileStreamTest.ReadFilestream(String connectionstring, String fileid) in FileStreamTest.cs: line 47

And here is the NUnit test method I used to verify the issue. FileStore access to TESTDB fails, but TESTDB12 works fine.

[TestCase(new object[] { "Data Source=localhost;Database=TESTDB;Integrated Security=True;Connect Timeout=120;", "525F2031-8D8C-45FF-8386-E1DD5F11C960" })]
[TestCase(new object[] { "Data Source=localhost\\express2012;Database=TESTDB12;Integrated Security=True;Connect Timeout=120;", "9AC28DC0-B927-4075-8D31-00E343E15663" })]
public void ReadFilestream(string connectionstring, string fileid)
{
    const string _selectSql =
        @"SELECT fileData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
  FROM coreFileStore
  WHERE fileStoreId = @FileStoreId";

    using (SqlConnection connection = new SqlConnection(connectionstring))
    {
        connection.Open();
        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            string filepath;
            byte[] txnInfo;
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = _selectSql;
                command.Parameters.AddWithValue("FileStoreId", new Guid(fileid));
                command.Transaction = transaction;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    filepath = reader.GetString(0);
                    txnInfo = reader.GetValue(1) as byte[];
                }
            }

            Console.WriteLine(filepath);

            using (SqlFileStream sqlFileStream = new SqlFileStream(filepath, txnInfo, FileAccess.Read))
            {
                using (StreamReader reader = new StreamReader(sqlFileStream))
                {
                    string x = reader.ReadToEnd();
                    Console.WriteLine(x);
                }
            }
        }
    }
}

Did found the answer in SQL Server support site.
https://connect.microsoft.com/SQLServer/feedback/details/793017/filestream-problem-when-installing-sql-server-2012-sp1-side-by-side-with-sql-server-2008-r2
It's a bug in SQL Server 2012. Hear is the copy of workaround suggested.

A workaround is to put the FILESTREAM containers of either a SQL Server 2012 instance or a prior version instance in a different volume by relocating or recreating them.
This will make the SQL Server 2012 RsFx driver (RsFx020x) attached to a different volume, so it won't interfere with the requests to the driver of a prior version.
For more info on relocating filestream dirs, please see http://msdn.microsoft.com/en-us/library/ms345483.aspx.
After relocation or recreation (it might need to restart the SQL Server), "fltmc instances" command will indicate if the RsFx drivers are attached to different volumes. For example, the result should look like this (excluding unrelated lines):
Filter                Volume Name                             Altitude        Instance Name
RsFx0201             C:                                     41002.01    RsFx0201 MiniFilter Instance     0
RsFx0105             D:                                     41001.05    RsFx0105 MiniFilter Instance     0


Add windows user to a particular database in SQL Server I have a windows user who I want to add to just one database SalesDeals on the server. I added them in SalesDeals-> Security -> Users section of the Database and gave them db_datareader permissions on the database. I cannot add them in the Top level Security section since I don't have permissions for that.

The issue is when the user tries to connect to the server CS01\CS01 they get login failed for the user error.

Do I need to get them added to the top level Server -> Security -> Users section for them to be able to connect to the Database.

Thanks

Yes, you must first create a Login on the SQL Server instance, then create a User in the database which maps to that Login.

Without a Login, the user cannot be authenticated to SQL Server (unless you're using a Contained Database, which is not a feature of your version of SQL Server), so they won't be able to access the database.


Checking for fields not on SET clause on before update trigger on MySQL I want to set a before update trigger that sets a column to a given value if an update query is done and that column has no new value on the set clause.

The table I'm using. (Sort of)

DROP TABLE IF EXISTS 'hex';
CREATE TABLE 'hex' (
    'id'    INT(11) NOT NULL AUTO_INCREMENT,
    'chunk' VARCHAR(45),
    'flag'  TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id')
);

So, what I want to do is when hex.chunk gets a new value and flag doesn't, flag is set to 0.

e.g.

Current Dataset

id   chunk           flag
1    "Antelope"      0
2    "Barracuda"     1
3    "Capybara"      0
4    "Dolphin"       1

Operations and Expected Results

Case 1 UPDATE hex SET chunk = "Antelope" WHERE id = 1; doesn't update the flag since chunk doesn't get a new value.

Case 2 UPDATE hex SET chunk = "Elephant" WHERE id = 2; updates the flag to 0 since chunk changes its value and no data for flag is passed.

Case 3 UPDATE hex SET chunk = "Capybara", flag = '1' WHERE id = 3; chunk and flag updates as queried.

Case 4 UPDATE hex SET chunk = "Ferrovax", flag = '0' WHERE id = 3 Same as case 3.

So, I've tried using this trigger but it doesn't seem to work the way I want it to work.

CREATE TRIGGER 'flag_the_hex'
BEFORE UPDATE ON 'hex'
FOR EACH ROW
BEGIN
    IF (NEW.flag IS NULL AND NEW.chunk != OLD.chunk)
    THEN
        SET NEW.flag = 0;
    END IF;
END

I think my main confusion here is how NEW values in the columns not updated are represented in triggers. If I update only the chunk column, how is the NEW value for flag represented? Null? Or does it just not exist yet?

Please keep in mind that there may be more columns in the table I'm using.

Thanks for reading.

EDIT: I asked this question over at StackOverflow and thought that may not be the appropriate place to ask this question. So, I asked this here and deleted it over there.

flag will be what it had been before the UPDATE started.  Maybe it would feel 'right' to use OLD.flag instead of NEW.flag, at least in the if?

Addenda

NEW.flag contains the new value. NEW.flag <=> OLD.flag checks to see if it changed. I doubt if there is any way to see if it was set but not changed. (Note that I used the "NULL-safe equal" operator.)


Why is a query with a join and limit clause running so slow, even using a covering index? I've got two tables that are a 1 to 1 relation.

Table A has an id column, and table B includes foreign key A_id to table A, and a nullable value column. I have a composite index on [value, A_id] on table B, and I'm running the following query:

SELECT B.A_id FROM B JOIN A ON B.A_id = A.id WHERE B.value IS NULL LIMIT 1000;

First of all, I understand the JOIN doesn't seem to serve a purpose here, I'm just illustrating the problem. In reality I need to select one other column from table A. If I get rid of the join, the query is instant.

There are 10 million rows in table B. This query takes more than 50 seconds to run. Yet the explain plan shows that table B is being accessed first, and is using the composite index. "Extra" shows "Using where; Using index". "Rows" shows almost 5 million rows.

What's going on here, and why doesn't the LIMIT cause this to run as nearly as quickly as without the join?

Even if both tables have the appropriate indexes on A.id and A_id you still may need an index on B.value to get decent performance.  I've reformatted your query for readability.

SELECT  B.A_id
FROM    B
JOIN    A
ON      B.A_id = A.id
WHERE   B.value IS NULL
LIMIT   1000;

It appears that A_id is a foreign key for a many to one relationship from B to A.  If that is the case we don't need to reference table A as A_id will be NOT NULL if the tables would join.

SELECT  B.A_id
FROM    B
WHERE   B.value IS NULL
AND     A_id IS NOT NULL
LIMIT   1000;

If B.value is rarely NULL you may not 1000 records in B that where B.value is NULL.  You can count the records, but not get a distribution of the records with the query.

SELECT  COUNT(1)
FROM    B
WHERE   B.value IS NULL;

An index on B.value would likely speed up the query.  A limit of 1000 is significantly larger than I have used on a production system.   Try the query with smaller limits like 1, 2, 5, 10, 20, 50 or 100.

Problems with query performance are best investigated with an EXPLAIN PLAN of the query.  This will tell you how the database is executing the query.  I've seen the optimizer choose rather strange plans, usually providing better performance than the plan I expected.  Rarely, the optimizer will choose a really bad plan.  Without the right indexes and statistics, the optimizer may not be able to generate a plan the performs well.


XE_FILE_TARGET_TVF WaitType in Azure SQL Recently I started exploring Azure Sql and came across waittype XE_FILE_TARGET_TVF. I tried searching online to get more info but didn't get enough information.

I am just curious to know:

What does this waittype mean ?
Is it only specific to Azure SQL ?
How does it affect sql query performance ?

You can find the answer here: https://www.sqlskills.com/help/waits/xe_file_target_tvf/

  This wait type is when a thread is waiting for data to be returned when using the sys.fn_xe_file_target_read_file table-value function to read event data from a file target.

this has no affect on SQL server performance and also applied to SQL on premise and Azure SQL.


How to: Real Time deletion of relevant records from resulting mysql results? I have about 100 MySQL tables and many SQL queries which take about 1-3 hours to run each and they return about 1 to 2 billions of records each.
However, every couple of minutes some records are inserted and deleted from any of the 100 tables.
Is there a way to not execute these SQL queries on the whole database and instead know the entries that were deleted? E.g. suppose one entry was deleted
from one of the 100 tables, I should be able to remove the corresponding entry from my previous query result.
My idea:
For inserts I can look at the time the new record was inserted and insert the latest.
For deletes I should be able to find out which entry was deleted from the logs (debezium). But how do I delete the record from my resulting query?

Create for every table an AFTER DELETE TRIGGER, so you are sure you catch every deleted entry and can remove them from your result set.
The same goes of course for the inserts, then you can also use a TRIGGER to add rows to your result set.
Your query is probably slow and you hope that you don't need to run it again, when you simply add and remove all new entries since the query last ran.


Backup / Restore of DB on MSSQL 2019 to MSSQL 2014 I have a production database running on MSSQL 2019 on a hosted platform.
I am wanting to create a sandbox copy of the db with all of the data in a local MSSQL server running 2014.

Everything I've tried  (backup / restore , generating scripts, export data) has error-ed out.

Does anyone have an easy button I am overlooking?

The harsh and direct answer would be no, you cannot downgrade.

You said scripting out and exporting data doesn't work, did you try to set the database compatibility mode to 2014. Then script out the database and create it on 2014 server. Then export the data?


Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2? I am encrypting a VARCHAR(50) NULL column as randomized. When doing this, the collation is changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2.

I cannot find any resources explaining why this happens, and I want to understand what the purpose of the collation change is.

SQL Server 2017 documentation:
Always Encrypted is not supported for the columns with the below characteristics:
(...)
String (varchar, char, etc.) columns with non-bin2 collations
(...)

Source: Always Encrypted

Looks like it is changing in SQL Server 2019:

Since its initial release, Always Encrypted has had a restriction regarding the use of collations: non-BIN2 collations are not allowed for character string columns encrypted using deterministic encryption. This restriction also applies to enclave-enabled string columns.

The use of non-BIN2 collations is permitted for character string columns encrypted with randomized encryption and enclave-enabled column encryption keys. However, the only new functionality that is enabled for such columns is in-place encryption. To enable rich computations (pattern matching, comparison operations), you must ensure the column uses a BIN2 collation.

Configure Always Encrypted with secure enclaves


MySQL silently replaces UTF chars with literal question marks I'm experiencing a situation similar to this SO question i.e. I'm working with a legacy database which has UTF8 content in latin1 tables (pretty ugly I know).

Now I'm getting new data from a new application which is completely utf8 and works with its database. To support other legacy system the application also writes a copy of its utf8 data in legacy tables. As far as I know it should be possible to write utf8 stuff in latin1 tables as long as you read it back and show it those data as UTF8. There are a lot of tutorials explaining how to fix this situation for the long term but I'd prefer to not apply them unless absolutely necessary (legacy system will be dismissed soon and I don't want to have downtimes to fix this if possible)

Here is a minimal SQL script which reproduces my issue:

CREATE TABLE `articles` (
  `content` mediumtext NOT NULL,
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET NAMES utf8;
SET CHARACTER SET utf8;
-- Turkish word for Croatia, second char is \xC4\xB1
INSERT INTO `articles` (`content`) VALUES ('Hırvatistan');

In my system I get no errors from MySQL but after the INSERT statement second char of the word is silently dropped and replaced by a literal ? ('\x3F').

mysql> SELECT content, HEX(content), HEX('Hırvatistan') FROM articles;
+-------------+------------------------+--------------------------+
| content     | HEX(content)           | HEX('Hırvatistan')       |
+-------------+------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E | 48C4B172766174697374616E |
+-------------+------------------------+--------------------------+

However if I paste the same script on http://sqlfiddle.com/ I get an error when I press 'build schema' which states:

Incorrect string value: '\xC4\xB1rvat...' for column 'content' at row 1

Why on my system the invalid utf8 char is simply dropped and I get no errors? Is there any mysql config value to enable in order to avoid this?

Which is the simplest way to allow any kind of char inside my current latin1 (with utf8 content) table? I have a lot of content and I'd prefer to avoid solutions like dump the content and reimport with other charset

I made some attempts to dig into this issue, here are the results.

When you set a connection charset (i.e. SET NAMES utf8) MySQL transparently handle encoding conversion for you. For instance if I insert a à (\xE0 in latin1 \xC3A0 in utf8) in a latin1 table using a UTF8 connection it reads the UTF 8 value and store it in table as \xE0

mysql> SELECT HEX('à');
+-----------+
| HEX('à')  |
+-----------+
| C3A0      |
+-----------+

mysql> INSERT INTO articles VALUES(50001, 'à');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT content, HEX(content) FROM articles WHERE id_p = 50001;
+---------+--------------+
| content | HEX(content) |
+---------+--------------+
| à       | E0           |
+---------+--------------+
1 row in set (0,00 sec)

When I insert invalid utf8 chars into a latin1 it replaces them with question marks as I shown in the original question.

In order to fix my issue I had to run this command on the original table (actually I tried it on a small copy of it). It takes care of changing charset, collation and also convert existing data. I took a record with a char which is different in latin1 and utf8 encoding

mysql> select HEX(BINARY SUBSTRING(content, 17, 1)), SUBSTRING(content, 17, 1) from articles where id_p = 40\G
*************************** 1. row ***************************
HEX(BINARY SUBSTRING(content, 17, 1)): 93
            SUBSTRING(content, 17, 1): “
1 row in set (0,00 sec)

mysql> ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 34905 rows affected (1 min 10,73 sec)
Records: 34905  Duplicates: 0  Warnings: 0

mysql> select HEX(BINARY SUBSTRING(content, 17, 3)), SUBSTRING(content, 17, 3) from articles where id_p = 40\G
*************************** 1. row ***************************
HEX(BINARY SUBSTRING(content, 17, 1)): E2809C
            SUBSTRING(content, 17, 1): “
1 row in set (0,00 sec)

After the conversion the “ char was replaced by its utf8 encoding in the content and all data are still readable. The conversion also changed content column type from MEDIUMTEXT to LONGTEXt because latin1 uses 1 byte per char and utf8 up to 3 bytes per char to avoid data truncation.

Now I'm experimenting with inserting invalid utf8 chars into the converted table and I get a different result for that. It seems that invalid (or not supported 4 bytes) utf chars are simply dropped from the stored value with a warning (shown only if warning enabled)

$ mysql --show-warnings

mysql> INSERT INTO articles VALUES(90000, 0xC328);
Query OK, 1 row affected, 1 warning (0,00 sec)

Warning (Code 1366): Incorrect string value: '\xC3(' for column 'content' at row 1
mysql> SELECT 0xf09f8eb6;
+------------+
| 0xf09f8eb6 |
+------------+
|            |
+------------+
1 row in set (0,00 sec)

mysql> INSERT INTO articles VALUES(90001, 0xf09f8eb6);
Query OK, 1 row affected, 1 warning (0,00 sec)

Warning (Code 1366): Incorrect string value: '\xF0\x9F\x8E\xB6' for column 'content' at row 1

After this I found that also in my original example a warning is shown if they are enabled:

-- With warnings enabled
mysql> INSERT INTO `articles` VALUES (50000, 'Hırvatistan');
Query OK, 1 row affected, 1 warning (0,00 sec)

Warning (Code 1366): Incorrect string value: '\xC4\xB1rvat...' for column 'content' at row 1

Finally to trigger an error instead of just a warning (to avoid data loss) just change SQL mode for session or globally (at server level)

mysql> SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO `articles` VALUES (50000, 'Hırvatistan');
ERROR 1366 (HY000): Incorrect string value: '\xC4\xB1rvat...' for column 'content' at row 1


Get The Most Recent Record In A Table By Date And User I have a table as below:

SELECT [EffectiveDate]
      ,[Rate]
      [ModUser]
      FROM [Vision]

there are many rows with the ModUsers name. I need to provide the ModUsers name and get the latest rates.

How do I do that?

There are many ways to do this. Here are some of them:

common table expression with row_number() version:

with cte as (
  select  *
      , rn = row_number() over (
              partition by ModUser
              order by EffectiveDate desc
            )

    from Vision
)
select ModUser, EffectiveDate, Rate
  from cte
  where rn = 1;

cross apply version:

 select distinct
     t.ModUser
   , x.EffectiveDate
   , x.Rate

  from Vision t
    cross apply (
      select top 1
            ModUser
          , EffectiveDate
          , Rate
        from Vision i
        where i.ModUser = t.ModUser
        order by i.EffectiveDate desc
       ) as x;

top with ties version:

select top 1 with ties
    *
  from Vision
  where
  order by
    row_number() over (
      partition by ModUser
          order by EffectiveDate desc
      );

inner join version:

select
      v.ModUser
    , v.EffectiveDate
    , v.Rate
  from Vision v
    inner join (
      select
          ModUser
        , MaxEffectiveDate = max(i.EffectiveDate)
        from Vision i
        group by ModUser
       ) as x on x.ModUser          = v.ModUser
             and x.MaxEffectiveDate = v.EffectiveDate;


Make "NOT NULL" default in Postgresql 9.2 I use NOT NULL a lot when creating columns.

Is there a way to make this default when defining columns?  In other words, make column is_nullable default to NO. (I realize this would make it harder for me to set it to YES later one.)

YOU have to define NOT NULL when creating a table as described in documentation :

NOT NULL ==>
The column is not allowed to contain null values.

Examples :

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);


How to query two values from the same table from different records combined with fields from other tables? I have a query similar to the one below which is getting data from a couple of tables and works as expected. The table eventsink.RedEvent has another column called Finished. I want to add the Finished column to the result of my query, however the value I want for Finished will be from another record than the record that Created is queried from. I need the MIN() of Finished that also matches DeviceId and also has an Id = '1'

Thanks for any help you can provide. I am pretty new to such complex SQL queries.

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

Try this:

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

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


Aggregate ranges by merging There aren't any Aggregate functions for Range types in PostgreSQL.

How to aggregate ranges using a merge operation?

This solution works but does not look optimal:

CREATE TEMPORARY TABLE test_ranges (r int4range);
INSERT INTO test_ranges VALUES ('[4, 8)'), ('[12, 45)'), (NULL);

SELECT int4range(min(lower(r)), max(upper(r))) FROM test_ranges;
=> '[4,45)'

Create an aggregate using the range_merge(anyrange, anyrange) function.

CREATE AGGREGATE range_merge(anyrange)
(
  sfunc = range_merge,
  stype = anyrange
);

SELECT range_merge(r) FROM test_ranges;
=> '[4,45)'


Copy Azure premium storage to standard storage for redundancy For our SQL servers we run backups onto a shared premium storage disk. Premium storage is locally redundant and I would like to copy these backups to a standard storage disk in order to take advantage of it's Geo-redundancy. What is the best way to do this?

The only way that I'm aware of would be to just start copying, robocopy is likely the most efficient method.

However, if you are aware writing backups to local disk uses up your alloted IOPS. It is best practice, and recommended by Microsoft, to use the backup to URL for database backups. Doing this does not count against your IOPS for the disk or VM.


CRON and RMAN Backups We run RMAN backups via CRON jobs, but find the out-of-the-box emails with CRON to almost do what we want.  I know we can channel the RMAN output to /dev/null so no email is sent, which for any successful run of RMAN is the desired outcome, but if the RMAN job runs into an error, I would like the entirety of the standard output sent via email to the DBA team for review.  I suspect I can wrap my RMAN scripts with a bash shell script that will pipe the standard output to cron only in the event of a failure, but is there a better way to do this?

To summarize:

Can I pipe standard output to an email in the event of an error (and
ignore the standard output if no error exists) so we can use the
default mail functionality within cron?
If not, should I just wrap my RMAN backups scripts with logic so
that it will only return output from the shell script if an error is
encountered?
Is there another approach that I'm not seeing or able to find online
that accomplishes this?

My apologies if this has already been asked.  I've found a number of one-off questions and scenarios, but nothing that I've felt match up exactly with my desired outcome.

Thanks!

I write a bash script to perform the backups (RMAN, datapump and also xtrabackup and dump for MySQL in a similar way) and then call that from cron.  That way you can handle the email as you want.

So a simple crontab line is like this

00 18 * * 0-6 /dba/bin/rman.sh > /tmp/dba_rman_SID.log 2>&1

Then everything is done in the script.

So define some common variables including RMAN e.g.

export RMAN="${ORACLE_HOME}/bin/rman target /"
...
etc etc

Then import a list of emails you want (one per line) from a local file

declare -a EMAILS
readarray -t EMAILS < .emails.dat

Then define a couple of handy procedures that you can use later on for error handling and email

abort_with_error()
{
        echo
        echo "Error :" $1
        echo
        echo "Error Code :" ${ERR}
        echo

        export SUBJECT="Error - ${SUBJECT}"

        send_email

        exit 1
}

send_email()
{
        echo
        echo "Sending logfile via email to the following :"
        echo

        for EMAIL in ${EMAILS[@]}
        do
                echo ${EMAIL}
                echo
        done

        for EMAIL in ${EMAILS[@]}
        do
                case $EMAIL in
                        \#*) ;;
                        *) ${MAILX} -s "${SUBJECT}" ${EMAIL} < ${LOG_FILE} ;;
                esac
        done
}

now the main process which is wrapped up to output it all to a log file.  This is a simple example but shows the basic method.

{

    //add what you want your script to do here - can be many things for example:

        ${RMAN}<<EOF
        delete noprompt force obsolete;
        exit;
EOF

        ERR=$?
        if [ ${ERR} -ne 0 ];
        then
                abort_with_error "The RMAN delete obsolete backup step failed"
        fi

        echo "${JOB_NAME} ended at : $(date)"

        send_email

} > ${LOG_FILE} 2>&1

Adjust the abort_with_error and send_email procedures to suit your environment and needs.

This separates the scheduler from the process so no need to change cron if you update your backup process or get a new team member who needs the emails.

This way you get an email if it works and if it doesn't but with Error in the subject so an simple email rule can hide away non-interesting successful backups and highlight failures and a simple count up each morning shows if something didn't email and needs a look.  Since the log is held on the server you can find it there (add something to the job to clean the old ones down after a couple of weeks).

Obviously there are a bunch of ways you can implement something like this and you'll need to vary it to suit (and I've cut out * a lot * of stuff from the script for clarity).


Couldn't connect to databasee I have a dedicated webserver and a dedicated database server.

Often, I get errors connecting to the database server

couldn't connect to database

Here is my current /etc/my.cnf configuration file

[mysqld]
local-infile=0
innodb_file_per_table=1
max_allowed_packet = 100M

log-slow-queries=/var/log/slow.log

open_files_limit=7306
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -10

How can I increase the timeout connect for the database server?

Actually I do not think this is something you would want to change under nearly any circumstances. You should not fix the symptom, but should find out the problem.

Does the mysql log contains information about aborted connections? Does the syslog of that server contain information which might be relevant? Do you run a more or less decent version of mysql? (At least the most current minor version.) Have you checked if the database server runs out of resources (e.g. RAM, because swapping is really bad for any DB server)? Have you compared the number of connections your webserver maximally issues simultaneously to the db server and the number of connections your db server is processing in parallel from its configuration file?

Have you tried pinging the database server from the web server for a long period of time? Does the ping slows down (or gets lost) too, when the error message pops up in your application?

You should check all that before you change anything in timeouts.


How do i prevent executing os command in oracle 11g I have Oracle 11g installed on a linux serveur and i'm looking for a way to prevent sysdba from executing system command through oracle(Using java for example).

There is no reasonable way how to do that. For example till 11gR1 SELinux was not certified to be used with Oracle database. Since 11gR2 this is at least not recommended.

Let's assume that you and Oracle DBA are adult, where is the problem then?
Oracle is not running with root privs. On the other hand Oracle requires many kernel tunables(sysctl) to be set according to Oracle's manuals, and also Oracle monitoring might require execution of some OS commands, so you should cooperate with DBAs anyway.


Which method is better to drop all user connections before a restore Change to single user mode or Take offline the database? When we restore a database from prod to dev which method is better to disconnect other connection accessing the database? I am on SQL Server 2008r2

1.    Change to Single user mode

use master

Go

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Go

RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\DB.bak'
WITH  FILE = 1,
MOVE N'DB'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',
MOVE DB_log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10

GO

ALTER DATABASE MyDB SET MULTI_USER

Go

2.    Take offline the database and restore database

USE master

GO

ALTER DATABASE MyDB
SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\DB.bak'
WITH  FILE = 1,
MOVE N'DB'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB.mdf',
MOVE DB_log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyDB_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10
GO

ALTER DATABASE MyDB
SET OFFLINE

GO

Which method would assure I will be the only user so I can do the restoration without a problem?

I would set it offline. I say this because SINGLE_USER mode is generally used to allow an administrator to perform some kind on maintenance on a database without users attempting to access and possibly interfering with the maintenance.

It doesn't really matter either way for a restore as your users will experience the same thing which is they wont be able to connect. One possible annoyance with the SINGLE_USER approach would be if a user stole the single session before you and blocked you from performing your restore.


How to convert data from XML format into Excel workbook or database table I have some data that is given in a consistent XML format (which I can't change). I would like to import it into a database so that I can work with it, and then search, identify and export certain records into an Excel workbook, as I don't think it can be imported to Excel directly.

I have no experience working with databases so I'm not sure if this is possible or for the best approach. My question is: Can this be done with a database tool/function? It's not important what the database system is, but I would prefer MS-Access or SQL-Server Express.

Excel is capable of importing XML directly.

See this link for details.


You can get it by using SUM(CASE...) statement.

SELECT   PROJECT_ID,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME NOT IN ('A','B') THEN AMOUNT END), 0) OTHER_TOTAL,
         COALESCE(SUM(AMOUNT), 0) AS TOTAL,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID, YEAR;

PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR
---------: | ------: | ------: | ----------: | ----: | ---:
         1 |       0 |      70 |           1 |    71 | 2016
         1 |      10 |      20 |          50 |    80 | 2017

dbfiddle here

As @JackDouglas has suggested, let me add his new solution that could be slightly quicker.

SELECT PROJECT_ID
     , A_TOTAL
     , B_TOTAL
     , nvl(TOTAL,0)-nvl(A_TOTAL,0)-nvl(B_TOTAL,0) OTHER_TOTAL
     , TOTAL
     , YEAR
FROM( SELECT   PROJECT_ID,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
               COALESCE(SUM(AMOUNT), 0) AS TOTAL,
               YEAR
      FROM     FINANCE_SOURCE
      GROUP BY PROJECT_ID, YEAR );

dbfiddle here
(Execution plan included.)


Best option to back up mysql innodb partition We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link http://www.percona.com/doc/percona-xtrabackup/howtos/recipes_ibkx_partition.html. Can any one suggest is the best mechanism or there other option such as mysqldump etc?

The Percona xtrabackup solution that you linked in your question is a good solution. It is a hot backup and doesn't lock innodb tables. I would prefer that to mysqldump in most cases.

Did you try xtrabackup? Have questions about using it?


Custom Deletion for Ola Hallengren Scripts I am trying to find a way to delete backups being taken by Ola's native SQL backups.
I need to keep only 2 weeks of transaction logs, 5 weeks of differentials, 14 weeks of full backups, 13 monthly full backups, and 3 beginning-of-the-year full backups.
I already asked the question and can do the first three with changing the @CleanupTime parameter in each of the respective jobs, however I don't know how to stop the Ola scripts from deleting the monthly or beginning-of-the-year backups.

If this is your required backup retention policy, then could you not have an extra backup job that runs once a month, and another running at once a year. These two new jobs would point to their own directory and have their own retention policy.

The downside is once a year you'll do 3  at once, daily, monthly and yearly one. But this is a rare event.

I would question if all this backups are needed, would the data be stale and no good by then? Could you lose a year's worth of data if you needed to restore it. If you want it for looking back at the data, is a DWH more suited?

However I don't no the reasons why this is required, there is probably a good reason for your approach. Such as cost saving.


MySQL DBs taking up way too much disk space I'm on Ubuntu Server 18.04 running MySQL server 5.7.27 with InnoDB. I confirmed that InnoDB file-per-table is On.
I have a mysqldump containing all of my DBs that takes up about 1.3 GB disk space.

After I imported the backup with sudo mysql -u root < myfile.sql, I noticed that there's significantly less space left on my drive. I checked the disk space usage of the newly created DBs in /var/lib/mysql and their combined size is about 14 GB.

I ran sudo mysqlcheck -o --all-databases -u root to optimize the tables, but it only reduced the size to 11 GB:

root@ip-x-x-x-x:/var/lib/mysql# du -sm * | sort -nr
1468    dbvwgcayak57wx
1468    dbmuj7j8scnzt6
1468    dbmmqtf5sss9pk
1468    dbf9ynyscjzw83
1468    dbdr5hk7kvsh73
1468    db3mj8b7b5ezuw
1468    db22z4jcfbf9yn
544 db7b5ezuwb8ca4
76  ibdata1
48  ib_logfile1
48  ib_logfile0
35  dbbz9e6hcmqbtb
23  db688zwgk7uvdh
19  mysql
12  ibtmp1
3   phpmyadmin
2   performance_schema
1   sys
1   ib_buffer_pool
1   dbzqcfb9scuv3h
1   dbscjzw83rv253
1   auto.cnf
0   debian-5.7.flag

All my large DBs contain several hundred tables that are small in size (less than 2 MB according to phpmyadmin), but they take up a lot more actual disk space (about 8 MB).

What else can I do to reduce disk space?

I solved this issue by setting innodb_file_per_table = 0 in my MySQL config (/etc/mysql/mysql.conf.d/mysqld.cnf) and re-importing the mysqldump after dropping all databases. The ibdata1 file that contains all DB data is just about 2.4 GB.

root@ip-x-x-x-x:/var/lib/mysql# du -sm * | sort -nr | head -15
2445    ibdata1
48  ib_logfile1
48  ib_logfile0
12  ibtmp1
11  dbvwgcayak57wx
11  dbmuj7j8scnzt6
11  dbmmqtf5sss9pk
11  dbf9ynyscjzw83
11  dbdr5hk7kvsh73
11  db3mj8b7b5ezuw
11  db22z4jcfbf9yn
4   db7b5ezuwb8ca4
1   phpmyadmin
1   mysql
1   ib_buffer_pool

Seems like having a *.ibd file for every single table can actually be disadvantageous if your DB contains a lot of small tables.


ReFS vs NTFS - which is better for SQL Server 2019 on Windows Server 2019 We want to install SQL Server 2019 on Windows Server 2019; currently Admin has formatted Nimble Storage with ReFS. Is it really better than NFTS? I have seen other posts wherein people have mentioned about downside of ReFS. Any suggestions \ help is appreciated.

So I'm not familiar with Nimble Storage or ReFS but it looks like the manufacturer (HPE) recommends against using ReFS with Nimble Storage, as a quick Google search turns up on their documentation here.
That being said, what is your Server Admin trying to achieve with Nimble Storage and/or ReFS?...this is definitely a very edge case setup as far as SQL Server goes (the majority of instances in the wild are likely running on NTFS).
In terms of reliability (which it seems like the point of ReFS is), from a database perspective, having a data redundancy, backup, and recovery plan that keeps RTO (Recovery Time Objectives) and RPO (Recovery Point Objectives) in mind is what would be recommended. Whether those objectives are accomplished through appropriately scheduled Full, Differential, and Transaction Log backups, implementing Availability Groups, or any other related means, none of them will be dependent on nor recommend a specific file system for your server's disk.


What does horizontal scaling mean? In database context, I have come across horizontal scalability as one of the advantages of the NOSQL databases. What does the term mean?

CouchDB on Wikipedia
NoSQL on Wikipedia

How would it compare to vertical scaling?

Horizontal Scaling
Horizontal Scaling is essentially building out instead of up. You don't go and buy a bigger beefier server and move all of your load onto it, instead you buy 1+ additional servers and distribute your load across them.

Horizontal scaling is used when you have the ability to run multiple instances on servers simultaneously.  Typically it is much harder to go from 1 server to 2 servers then it is to go from 2 to 5, 10, 50, etc.

Once you've addressed the issues of running parallel instances, you can take great advantage of environments like Amazon EC2, Rackspace's Cloud Service, GoGrid, etc as you can bring instances up and down based on demand, reducing the need to pay for server power you aren't using just to cover those peak loads.

Relational Databases are one of the more difficult items to run full read/write in parallel.

I saw Damien Katz speaking about CouchDB at StackOverflow DevDays in Austin and one of his primary focuses for its creation was these parallel instances.  As this has been a focus of it since day one, it would be much more capable of taking advantage of horizontal scaling.

Vertical Scaling
Vertical Scaling is the opposite, build up instead of out. Go and buy the strongest piece of hardware you can afford and put your application, database, etc on it.

Real World
Of course, both of these have their advantages and disadvantages. Often times a combination of these two are used for a final solution.

You may have your primary database where everyone writes to and reads real time data on a large piece of hardware. Then have distributed read only copies of the database for heavier data analysis and reporting where being up to the minute doesn't matter as much.  Then the front end web application may be running on multiple web servers behind a load balancer.