Flash storage and auto-grow Our databases files  (data and log)  of (a lot of) SQL Servers are hosted on flash storage (SAN), what would you use as auto-grow property for databases having size round 500GB (data) and 150Gb (log)?
Knowing in the background that you cannot size 5 years ahead because of storage price.
Subsidiary question:
Is it still accurate to take care of file fragmentation on SSD SAN volumes?

In general, the type of storage and current size of database isn't particularly relevant to your auto-grow settings.  Ensuring that instant file initialization is enabled is far more important.

If instant file initialization is not enabled, then everything (more or less) comes to a halt while it is initializing the new part of the file.  With SSDs that time will be shorter, but it will still be a significant hiccup.  So If you don't have it enabled, you would want to have growth size set fairly small, or increase the size during maintenance windows.

If instant file initialization is enabled, then the file growth will only take a few milliseconds regardless of whether the storage is SSD or platters.  How much you grow it at once just depends on whether anything else will be using the disk space.  If it is one database, one file, etc., and nothing else will be using it, you could just grow it once to use almost all of the disk and be done with it.  But if there are multiple databases/files, then you'll just have to take all of those factors into consideration.

As to file fragmentation, the performance impact is all about disk read times, and since random disk seeks are far faster on SSDs, it's not near as much of an issue as it is with platters.  As Aaron mentioned in comments above, reads should mostly be out of the buffer, so if there is adequate memory for SQL Server it won't be reading much from disk anyway.

In general, you want to set it up so that it doesn't have to grow very often so that when there is a performance issue, you won't even be wondering if it's due to file fragmentation, etc.  It doesn't matter what your storage is, optimize the config for whatever resources you have.  Without knowing all of the details of the system, we can only answer questions about concepts, not specifics of what would be best for your particular situation.  Additionally, StackExchange is not really the place for getting custom specs--that's a consulting engagement.


mongod runs with command but not with config file I am trying to start mongod with a config file on MongoDB 3.2 on Ubuntu 14.04. I can launch mongod by issuing the command myself, but I can't get it to work with the config file.

Here's the command I'm issuing:

sudo mongod --replSet rs0 --logpath /var/log/mongodb/mongod.log --dbpath /data/db --storageEngine wiredTiger --fork

When I run this command, I can then run mongo to enter the mongo shell and successfully use the database.

I don't see a difference between the command I'm issuing and the config file, which I call with sudo mongod --config /etc/mongod.conf. When I run this, and try a command in the same mongo shell I had successfully used for the prior query, I get

2017-01-31T10:55:44.899-0800 E QUERY    [thread1] Error: error: { "ok" : 0, "errmsg" : "not master and slaveOk=false", "code" : 13435 } :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCommandCursor@src/mongo/shell/query.js:689:1
DBQuery.prototype._exec@src/mongo/shell/query.js:118:28
DBQuery.prototype.hasNext@src/mongo/shell/query.js:276:5
DBCollection.prototype.findOne@src/mongo/shell/collection.js:289:10
@(shell):1:1

and here is my /etc/mongod.conf. How are these different?:

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: "/data/db"
  journal:
    enabled: true
  engine: "wiredTiger"
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: "/var/log/mongodb/mongod.log"

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1

processManagement:
  fork: true

#security:

#operationProfiling:

replication:
  replSetName: rs0
#  oplogSizeMB: 1024

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

That error message "not master and slaveOk=false" is what you get when you try to query on a secondary server. If you're getting that message, that implies that:

The server has successfully started
The server has successfully joined the replica set
A different server is currently Primary

You should be able to check that by running rs.status() in the shell (assuming your user account has replicaset administration privileges).

If you want to run a query on this server to confirm that it is working (even though it is currently a secondary), you can use the rs.slaveOk call.


where can I find the DNS name associated to the listener of an Availability Group? I have an availability group that has a listener.

The listener is called SQLPROD as you can see on the picture below.
It uses port 1433

When I run the following script:

ALTER AVAILABILITY GROUP [SQLPROD_AG]
    ADD LISTENER 'SQLPROD' (WITH IP (('200.1.1.199', '255.255.254.0')));

I get this error message (when it already exists):

  Msg 19477, Level 16, State 48, Line 4 The availability group
  'SQLPROD_AG' already has a listener with DNS name
  'SQLPROD_AG_SQLPROD'.  Availability groups can have only one listener.
  Use the existing listener, or drop the existing listener and create a
  new one.

using the Always On Availability Groups Catalog Views I haven't found anything revealing:

select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_availability_group_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_name_id_map
select * from sys.dm_hadr_cluster
select * from sys.dm_tcp_listener_states
select * from sys.dm_hadr_cluster_members

select * from sys.availability_databases_cluster
select * from sys.availability_groups_cluster
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_read_only_routing_lists
select * from sys.availability_group_listeners
select * from sys.availability_replicas
select * from sys.availability_groups

All seems to be fine:

when I use powershell, the following commands:

Import-Module FailoverClusters
cls
Get-ClusterResource

Where can I find where the name SQLPROD_AG_SQLPROD is?
Can I change this name?

The message that is returned is not accurate as it is referring to the name that it will give the network name resource (SQLPROD_AG_SQLPROD), and it is labelled incorrectly as the DNS name.  You can see that the network name resource in your AG is named SQLPROD_AG_SQLPROD, and that is what it's referring to.

It really should say something like, "The availability group 'SQLPROD_AG' already has a listener with a network name resource of 'SQLPROD_AG_SQLPROD'."  When the listener is created, the associated network name resource is created with a name according to the following syntax:

<Availability Group Name>_<DNS name>

Since a resource with that name already exists, it can't create it.

Also, underscores aren't allowed in DNS names, so the name given in the error message is obviously not a DNS name.

To see the DNS name associated with the listener, you can use SQL Server Management Studio and view the properties of the listener, or use Failover Cluster Administrator and view the properties of the network name.

The following PowerShell command will return the names and DNS names of all network name resources that are not core cluster resources.

Get-ClusterResource | Where-Object {$_.isCoreResource -eq $false -and $_.ResourceType -eq "Network Name" } | Get-ClusterParameter | Where-Object { $_.Name -eq "DnsName" } | Select ClusterObject, Value

If you want to filter by a specific AG name, you would use Where-Object {$_.OwnerGroup -eq "SQLPROD_AG" -and $_.ResourceType -eq "Network Name' } for the first WHERE clause.

With T-SQL, you can get this from the AG-related views.  To list all:

select ag.name, agl.dns_name from sys.availability_group_listeners agl
join sys.availability_groups ag ON ag.group_id = agl.group_id

To list only the one for SQLPROD_AG, you would just need to add a WHERE clause:

WHERE ag.name = 'SQLPROD_AG'

If you're wanting to use a different DNS name for the listener, you have to drop the current listener and create a new one.

ALTER AVAILABILITY GROUP SQLPROD_AG REMOVE LISTENER 'SQLPROD'

See Remove an availability group listener.


What exactly is written to transaction log? Here's the scenario:

DB with Simple recovery model,
Roughly 14 GB of data (~80 million rows),
INSERT ... SELECT statement into empty table with 7 non-clustered, non-unique, single column indexes and one clustered single column primary key from table with no indexes,
Transaction logs set to have unlimited space with Autogrowth on by 2 GB

The query has been running for 3 hours now and transaction log files have grown to about 45 GB!

My question is simple - why? What is actually written to transaction log in this scenario? I read this article, this technet resource and this MSDN article but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...

The table is not replicated. I finally decided to cancel my query and instead create keys/indexes/constraints on new table, drop the old one and rename it. It should be faster, right?

but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...

No, in simple recovery mode, leaving Operations That Can be minimally Logged, all other operations are fully logged. In simple recovery model what is different is when the checkpoints are going to happen. Checkpoints are fired after transaction commits and when log file grows 70% of its size. For all other scenarios logging in simple recovery is same as full recovery.

Minimal Logging also requires Target Table to meet certain requirements

  Roughly 14 GB of data (~80 million rows),

Were you performing insert into select * from type of query ? In that case you were doing lot of data reading and movement so transaction log file will record each page allocated to new table and depending on query. Now transaction log also keeps space for rollback of all the changes done. Keeping all this in mind I guess the space required would be genuine. I am not sure what query you ran and your SQL Server configuration, depending on that, answer can change.

There was a similar question I answered about what events are recorded in transaction log. There is NO documented command to read it but you can use undocumented command to read transaction log, I must tell you it requires level of expertise to interpret the output. Please read Analyzing Transaction Log By Remus Rusanu.

There are 3rd party tools available by RedGate and Apex which helps you in reading transaction log. You can search about them online.


Count based on status on latest association I have two tables in PostgreSQL like this:
Process
- id : uuid

Log
 - created : date
 - status : string
 - parentProcess : uuid

A process can have several logs related to it, and their status can be 'SUCCESS', 'FAILED', 'PENDING'.
I would like to group all of my processes based on the latest associated Log status:

PENDING
FAILED
SUCCESS

7
4
23

For example, if a Process has 3 Logs with the following data
Process a {
   id: 1
}

Log 1 {
 - created : 2020-05-18 11:02:23.035+00
 - status: "PENDING"
 - parentProcess: 1
}

Log 2 {
 - created : 2020-05-18 17:57:44.002+00
 - status: "SUCCESS"
 - parentProcess: 1
}

Log 3 {
 - created : 2020-05-18 09:31:02.081+00
 - status: "FAILED"
 - parentProcess: 1
}

this process should be increase the 'SUCCESS' column because its latest log (Log 2) has 'SUCCESS' status.
How would I write the SELECT to be able to do this?

One way to accomplish this is with a window function and grouping applied using the GROUP BY clause and an aggregate function with a case statement like so:
WITH CTE_Log_Sorted AS -- Creates a CTE (can do this in a subquery too) to generate unique row numbers for each status within a partition of parentProcess starting with the latest status first (ordered by created descending).
(
    SELECT status, ROW_NUMBER() OVER (PARTITION BY parentProcess ORDER BY created DESC) AS SortId
    FROM Log
)

SELECT
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS PENDING, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS FAILED, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS -- Case statements to sum up only the status we want to count in this column
FROM CTE_Log_Sorted
WHERE SortId = 1 -- Filter out everything but the latest status of each parentProcess
GROUP BY status -- Group on each status so we can sum up the count of each in our SELECT clause


How do I remove duplicate records in a join table in PostgreSQL? I have a table that has a schema like this:

create_table "questions_tags", :id => false, :force => true do |t|
        t.integer "question_id"
        t.integer "tag_id"
      end

      add_index "questions_tags", ["question_id"], :name => "index_questions_tags_on_question_id"
      add_index "questions_tags", ["tag_id"], :name => "index_questions_tags_on_tag_id"

I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.

What does the SQL look like for that?

In my experience (and as shown in many tests) NOT IN as demonstrated by @gsiems is rather slow and scales terribly. The inverse IN is typically faster (where you can reformulate that way, like in this case), but this query with EXISTS (doing exactly what you asked) should be much faster yet - with big tables by orders of magnitude:
DELETE FROM questions_tags q
WHERE  EXISTS (
   SELECT FROM questions_tags q1
   WHERE  q1.ctid < q.ctid
   AND    q1.question_id = q.question_id
   AND    q1.tag_id = q.tag_id
   );

Deletes every row where another row with the same (tag_id, question_id) and a smaller ctid exists. (Effectively keeps the first instance according to the physical order of tuples.) Using ctid in the absence of a better alternative, your table does not seem to have a PK or any other unique (set of) column(s).
ctid is the internal tuple identifier present in every row and necessarily unique. Further reading:

How do I decompose ctid into page and row numbers?
How list all tables with data changes in the last 24 hours?
How do I (or can I) SELECT DISTINCT on multiple columns?

Test
I ran a test case with this table matched to your question and 100k rows:
CREATE TABLE questions_tags(
  question_id integer NOT NULL
, tag_id      integer NOT NULL
);

INSERT INTO questions_tags (question_id, tag_id)
SELECT (random()* 100)::int, (random()* 100)::int
FROM   generate_series(1, 100000);

ANALYZE questions_tags;

Indexes do not help in this case.
Results
NOT IN
The SQLfiddle times out.
Tried the same locally but I canceled it, too, after several minutes.
EXISTS
Finishes in half a second in this SQLfiddle.
Alternatives
If you are going to delete most of the rows, it will be faster to select the survivors into another table, drop the original and rename the survivor's table. Careful, this has implications if you have view or foreign keys (or other dependencies) defined on the original.
If you have dependencies and want to keep them, you could:

Drop all foreign keys and indexes - for performance.
SELECT survivors to a temporary table.
TRUNCATE the original.
Re-INSERT survivors.
Re-CREATE indexes and foreign keys.
Views can just stay, they have no impact on performance. More here or here.


Reviewing relational database tables I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong.

I have three tables, one stores students basic info ---{students_info}, the other stores their classes ---{students_class} and the other stores their positions ---{students_positions}.

students_info
 st_id | f_name | l_name
  1    | quinoo | mickel
  2    | nunoo  | gyan
  3    | kwanis | nnipa

students_class
 cl_id | class | academic_year |st_id
  13   |  A    | 2013          | 2
  32   |  B    | 2014          | 1
  73   |  C    | 2014          | 3

students_positions
 pos_id | positions | ushered_year | st_id
   54   | President |   2013       |  2
   34   | secretary |   2014       |  3

This is what I have done so far. But a friend saw my tables and its relationships and he was like, it would be best, if I created another table to contain all the ids column values in addition to the table I have. This is what he suggested:

students
 all_std | st_id | cl_id | pos_id
 01      |  1    |  32   |  null
 02      |  2    |  13   |  54
 03      |  3    |  73   |  34

UPDATE

My friend was suggesting the additional table above (of which i think is wrong) because I was querying data from all the three tables within just a query statement using JOIN, but the data appears in duplicate. Look at the one below

SELECT
    a.st_id
    b.class
    b.academic_year
    c.positions
FROM
    students_info a
      INNER JOIN
              students_class b
          ON
              a.st_id=b.st_id
      INNER JOIN
                students_positions c
          ON
               a.st_id=c.st_id
WHERE
      a.st_id=2;`

This is the answer i receive after executing the query

 st_id | class | academic_year | positions
   2   |  A    |    2013       | President
   2   |  A    |    2013       | President

On the contrary this is what I expect

 st_id | class | academic_year | positions
   2   |  A    |    2013       | President

You have 3 tables of which only first table is normalized, 2nd & 3rd are not.

Normalization works like this:

students_info
st_id | f_name | l_name | date_of_birth | address  | phone
  1   | quinoo | mickel |   1/1/1990    | nowhere1 | 1-800-1234
  2   | nunoo  | gyan   |   2/1/1990    | nowhere2 | 1-555-1234
  3   | kwanis | nnipa  |   3/1/1990    | nowhere3 | 1-555-4321

classes_info
cl_id | class | academic_year
 13   |  A    | 2014
 32   |  B    | 2014
 73   |  C    | 2014

students_classes
cl_id | st_id
13    |  2
13    |  1
32    |  1
32    |  2
73    |  3

Why you got it wrong? You set 3 students and 3 classes, and put each student to one class. If you have 3 students and 3 classes, and each student is going to two classes, in your example you would have duplication of rows in students_class table.

In basic tables, lets call them OBJECT tables (students_info, classes_info) you CAN NOT have duplication, as every object (student, class) is unique.
In relations tables you have relations and there you can have whatever relations you like, as there you store only id's of basic objects you relate to each other and not all other data pertaining to some object (which is stored in object table). So in students_classes table you have 5 rows, as students 1 and 2 go to two classes each, and 3rd student is going only to one class.
Having 300 students and 20 classes, tables for students and classes whould have 300 and 20 rows respectively, and relations table students_classes would have as many rows as needed, e.g. if every student must take 5 classes, and can take max 10 classes, students_classes table could have from 1500 to 3000 rows (for one academic year).


MySQL Trigger: Insert depending on NEW.column of type enum I am trying to add a trigger to one of my tables. Depending on a enum-value of the newly inserted row it should execute a different insert-query. I am using phpMyAdmin and am trying to execute my script in the SQL-Tab but it always get an error which tells me absolutely nothing...

Here's my SQL Script that i try to execute in the SQL Tab of phpMyAdmin:

CREATE TRIGGER insert_after_new_kpi_trig
    AFTER INSERT ON KPI
    FOR EACH ROW

    IF NEW.kpi_type = 3 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects;
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams;
    ELSE IF NEW.kpi_type = 1 THEN
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams;
    ELSE IF NEW.kpi_type = 2 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects;
    END IF;

And this is the error I'm getting:

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

So can someone tell me if something is wrong with my script or do I have to use the string-value of the enum for the comparison instead of the index that I'm currently using? Thank you for your help.

Edit:
Maybe I should also mention what the enum is its the following

enum('TEAM', 'PROJECT', 'BOTH')

So if I use 3 in the if condition it should be equal to BOTH

I had to use delimiter to make it work but because delimiter is a mysql-console command it didn't work by putting it in my script.

phpMyAdmin has a different way to set a delimiter. Below of the script-input there is another input to set a delimiter so at then and my script looked like this:

CREATE TRIGGER insert_after_new_kpi_trig
    AFTER INSERT ON KPI
    FOR EACH ROW
    BEGIN
    IF NEW.kpi_type = 3 THEN BEGIN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects p;
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams t;
        END;
    ELSEIF NEW.kpi_type = 1 THEN
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams t;
    ELSEIF NEW.kpi_type = 2 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projectsp;
    END IF;
    END $$ --Thats the delimiter I set.


Is is possible to manage SQL Server 2008 databases via R2 Management Studio? Is it possible to manage SQL Server 2008 databases using SQL Server 2008 R2 Management Studio?

Yes, it is possible to use the SSMS from R2 to connect to a database server from any of the previous versions (I'm not sure about version pre SQL 2000, but it's not your case anyways).

You won't have all possible features activated (like Utility Explorer), but that shouldn't stop you to use it properly.


Does missing logout event in SQL Server trace mean connection leak? (originally asked this question on StackOverflow was told that this community is a right one).

I am investigating possibility of unreleased SQL Server database connections. I ran SQL Server Profiler and collected event traces. Typically after an executed SQL command, I see "Audit Logout" event and often exec sp_reset_connection that indicates that connection is reused for a new command.

However, I track some SPIDs and see that after some SQL command (e.g. INSERT), there are no more events for the given SPID. Does this mean that connection is not released, stays open and can not be reused?

Great question! To find out if the connection is still open right now, look in sys.sysprocesses:

SELECT * FROM sys.sysprocesses;

Generally, as long as they're not doing anything, open connections aren't a problem for SQL Server - it can handle tens of thousands. Connection pooling - keeping a connection open and ready for reuse - is fine, and doesn't consume that many resources on a SQL Server.

I'm generally more worried about sleeping sessions - someone who started a request minutes ago and didn't finish - and you can find those with Adam Machanic's free sp_WhoIsActive:

sp_WhoIsActive @ShowSleepingSpids = 1


Do I face corruption issues? I have found out the msdb.dbo.suspect_pages has 15 entries. 14 of those are last updated in 2014 and of type 7 ("Deallocated").
One entry  is of type 1 (823 or 824 error) with a count of 1 and last udpated in June 2015.

Last DBCC CheckDB was ran on Nov. 9th using a maintenance plan. The only output I have is the history of this job saying it completed 100%:

  Microsoft (R) SQL Server Execute Package Utility  Version
  10.50.6000.34 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  7:54:37 PM  Progress: 2016-11-09
  19:54:44.66     Source: {84CA0A16-9095-4D9D-9CF8-4AF8A1144015}
  Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE
  msdb..sp...".: 100% complete  End Progress  Progress: 2016-11-09
  19:54:47.22     Source: Check Database Integrity      Executing query
  "USE [dbname]  ".: 50% complete  End Progress  Progress: 2016-11-09
  22:08:15.87     Source: Check Database Integrity      Executing query
  "DBCC CHECKDB(N'dbname')  WITH NO_INFOM...".: 100% complete  End
  Progress  DTExec: The package execution returned DTSER_SUCCESS (0).
  Started:  7:54:37 PM  Finished: 10:08:16 PM  Elapsed:  8018.92
  seconds.  The package executed successfully.  The step succeeded.

So there is not further detailed information about the possible corruption's source / table / index. I am not sure if we found any issues here or not jsut because it completed 100%...

I ran "sp_blitz" and "sp_blitzindex" but it did not return any corruption or something like that.

So far there were no suspect failures or anything that maybe caused by corruption. Since it is there for many months I'm not sure if any action must be taken. Of course there are no backup existing prior that date...

I wonder where to look at next?

Edit:

Another run of DBCC Checkdb as indicated by @Randolph returned the following:

Message
DBCC CHECKDB (myDBName) WITH all_errormsgs, no_infomsgs, data_purity executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 1 hours 23 minutes 27 seconds.  Internal database snapshot has split point LSN = 007c5657:00000372:000e and first LSN = 007c5657:00000353:0001.

Are we okay? Should I delete the entries from msdb.dbo.suspect_pages ?

If this returns nothing, then your database is fine.

DBCC CHECKDB (DatabaseName) WITH NO_INFOMSGS,
ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY


Differences between .out and .sql in pg_dump PostgreSQL I create a backup file of postgeSQL database:
>pg_dump --dbname=db_test --host=localhost --username=user -F c -f dump_test.out

In documentation for pg_dump is used extension .sql and for pg_dumpall is used .out

https://www.postgresql.org/docs/9.2/app-pgdump.html
https://www.postgresql.org/docs/9.2/app-pg-dumpall.html

What is the differences between the extensions of .out and .sql in pg_dump in PostgreSQL? what is the recommendation and why?

Actually in these examples the used extensions don't really matter: both exports are plain text (i.e SQL) scripts. You decide which extension to use: if you export in plain SQL it's better to use .sql extension but you can choose another one.

In general you decide which extension you use. Neither psql nor pg_restore have a mandatory extension for the export file. If you use another output file format (-Fc or -Ft) for pg_dump it's better to use another extension than .sql to remember which export format has been used.


When tables are locked does MySQL queue queries? I have a small production website, but it's live 24/7. When running the Optimize, Analyze or Repair commands, MySQL locks  the table it is working on.
QUESTIONS

How would this effect users trying to make changes from a Perl application?
Are queries queued for the time table is locked and then processed (assuming the application does not time out)?
Do queries simply not work?

Thanks!
/usman

Since OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are DDL, full table locks are required.
However, if all data is InnoDB, the latest version of MySQL is a little more lenient with DDL locks. Note the current MySQL Documentation on OPTIMIZE TABLE in InnoDB:

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL
(ALGORITHM=INPLACE). Consequently, concurrent DML (INSERT, UPDATE,
DELETE) is not permitted on a table while OPTIMIZE TABLE is running,
i.e. the table is locked. Also, secondary indexes are not created as
efficiently because keys are inserted in the order they appeared in
the primary key.
As of 5.6.17, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for
both regular and partitioned InnoDB tables. The table rebuild,
triggered by OPTIMIZE TABLE and performed under the cover by ALTER
TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE)
and only locks the table for a brief interval, which reduces downtime
for concurrent DML operations.

With ANALYZE TABLE

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

REPAIR TABLE makes no apologies. The documentation sheds no light on reads, so SELECTs would be outright blocked until the repair is completed.
All queries that are writes (INSERT, UPDATE, DELETE) would be queued until those full table locks are released. This is true regardless of the storage engine being InnoDB or MyISAM.
SUMMARY
Read locks mitigate SELECTs depending on the command

ANALYZE TABLE will permit SELECTs
OPTIMIZE TABLE will try to minimize lock time
REPAIR TABLE has nothing to offer queries but waiting


Can't write DBNull to SQL in Powershell In Powershell, I have some data that I am importing from a .csv, and some data points aren't always entered so I need to replace them with [DBNull]::Value.

The problem is that when I go to use Write-SqlTableData I get this error:

Write-SqlTableData : Invalid storage type: DBNull.

I'm using this code to detect blank spaces and replace them with DBNull's:

$DataOut | Foreach-Object {
    ForEach ($Property in $_.PSObject.Properties) {
        If([String]::IsNullOrEmpty($Property.Value)) {
            $_.($Property.Name) = [DBNull]::Value
        }
    }
}

What is the issue here? What is an alternative?

I am having the exactly the same issue and I come to this web page. But it really did not solve the issue other than what Shawn Metlton mentioned to use write-dbadatatable in the comment.

Since the original poster @discipline did not give the whole PS script, I cannot fix his issue. But I figure out how to do the work after one hour's try.

So here is how to repeat the same error message

We first create a table in [TempDB] on my local sql server instance

use tempdb
if object_id('dbo.t', 'U') is not null
    drop table dbo.t;
create table dbo.t (name varchar(100), gender varchar(10), id int)

Now we write a few lines of PS codes as follows:

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city='Toronto'}
#this can be successful
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $p;

After execution, we can check the sql table and see it is indeed successful as shown below

Now if we change the $p to have its [city] property to DBNull as the following

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city=[dbnull]::Value}
#this will fail
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $p;

we can see the following error

The alternative solution is to create a system.data.datatable and then pass the object value to this datatable and then pass datatable to write-sqltabledata as shown below

$dt = new-object 'System.Data.DataTable';
$dt.Columns.Add((new-object System.Data.DataColumn('name', [System.String])));
$dt.Columns.Add((new-object System.Data.DataColumn('gender', [System.String])));
$dt.Columns.Add((new-object System.Data.DataColumn('city', [System.String])));

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city=[dbnull]::Value}

$p | % {$r = $dt.NewRow(); $r.name=$_.name; $r.gender=$_.gender; $r.city=$_.city; $dt.rows.add($r);}
#this will succeed
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $dt;

Now if we query the sql table again, we will see it works

If you have other better ideas, please share for the commuity.


RAC, data guard, physical stand by and impdp We're setting up a RAC 2 nodes data guard 11g environment, with a remote physical standby (2 nodes also)

We receive the client's data he exports with expdp. We have imported his dump in the primary database a first time for our tests. The day we go to production we will have to import it again. We will probably have to drop the data before doing so, so will we have to disconnect the data guard environment before we proceed? What advice would you give me in this context?

Thank you,

S.

When you drop user on primary then this will be also reflected on secondary site. Also impdp will work as expected. But when you are about to import large amount of data MAA might slow down the whole procedure significantly.

If you have enough experience with RMAN you might try:

shutdown and disconnect the secondary
import data into master
backup master using RMAN
cross-site-restore RMAN backup of primary on secondary node, thus re-initialize the secondary.

Although this approach might seem to be complicated it might be faster in large configurations.


Sub-query in update function I have a really terrible set of data that I've inherited and part of my job is going through and cleaning it up to something that's actually usable.  Currently, I'm trying to update a column in my table based on the name of entry.

Table: (category is currently null for almost everything)

+-----------------------------------------------------+
| programID | programName | programVersion | category |
+-----------------------------------------------------+

My query:

update Programs
    set category="Adobe Suites"
    where programName
        in (
            select distinct programName
                from Programs
                where programName regexp '^adobe.*cs[1-9]$'
            );

The problem is, no matter whatever I try I keep getting the error:

  ERROR 1093 (HY000): You can't specify target table 'Programs' for update in FROM clause

I'm not sure what other way this could be done.  Any ideas on how to replace the from statement with something else?

SUGGESTION #1
Why use a subquery at all? Try it like this:
update Programs
set category="Adobe Suites"
where programName regexp '^adobe.*cs[1-9]$';

PROS AND CONS : Simpler query with no subquery, but a single-pass full table scan.
SUGGESTION #2
Put the distinct names in a separate table and then do an UPDATE JOIN:
create table ProgramNames
    select distinct programName
    from Programs
    where programName regexp '^adobe.*cs[1-9]$'
;
alter table ProgramNames add primary key (programName);
update Programs A INNER JOIN ProgramNames B USING (programName)
set A.category="Adobe Suites";
drop table ProgramNames;

PROS AND CONS : Clean UPDATE JOIN, but requires creating the temp table manually. This will be great if programName is indexed in the Programs table.
CAVEAT
There is great reason why you get that error message. It has to do with the MySQL Query Optimizer. Back on Feb 22, 2011, I wrote an answer to this post : Problem with MySQL subquery. I mentioned that there are times the MySQL Query Optimizer can make rows disappear intermittently in the optimization process. The join would not be clean internally. So, rather than rewrite it, MySQL simply kicks out an error so as not to have to rewrite. IMHO I think that maybe due to the way MySQL interacts with storage engines.
Give it a Try !!!


SQL Service Broker & Logical File Names A colleague was having trouble enabling the Service Broker for a particular database.  SQL Server kept on throwing an error stating that the Service Broker was already running. When checking the database, the option for Service Broker was disabled.

It turns out that the database was created using a backup of another database and the logical names had not been changed. The original database did indeed have the Service Broker enabled.

So in summary we have:

DB1 with logical names foo and foo_log with Service Broker enabled
DB2 with logical names foo and foo_log with Service Broker disabled

We were unable to start Service Broker on DB2 as SQL Server said it was already running. The databases were pointing to different .mdf & .ldf files.

Why does the Service Broker use the logical names and not the database names? I always thought that the scope of logical name was restricted to a single database?

EDIT

This is the SQL Statement that was used to enable Service Broker

ALTER DATABASE DB2 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

And the error is:

Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "DB2" cannot be enabled because there is already an enabled Service Broker with the same ID.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

There are only 4 steps to enable service broker for a Database and non of them use logical file name as parameter

Try this

USE MASTER
GO

IF EXISTS ( SELECT  is_broker_enabled, Service_Broker_Guid,*
            FROM    sys.databases
            WHERE   name = 'DB2'
                    AND is_broker_enabled = 0 )
BEGIN

    -- Step 1 ---  Rollback running transactions and brings the database in a single user mode.
    ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    -- Step 2 --- To avoid distributed conversation and OLD database GUID conflicts.
    ALTER DATABASE DB2 SET NEW_BROKER;

    -- Step 3 --- Set the is_broker_enabled flag to true
    ALTER DATABASE DB2 SET ENABLE_BROKER;

    -- Step 4 --- Brings the database back to Multi user mode and allow shared locks\connections.
    ALTER DATABASE DB2 SET MULTI_USER WITH ROLLBACK IMMEDIATE

END


Can’t Get My JOINs Right for this Query With the SQL Fiddle at http://sqlfiddle.com/#!18/fce3e/1, I’m trying to get the following results, but can’t wrap my head around it.  I don’t want to specify a specific category or personId values.

Thank you.

| id | message | category | personId | enabled |
|----|---------|----------|----------|---------|
|  1 |   Hello |        1 |        1 |       1 |
|  2 |      Hi |        1 |        1 |       1 |
|  3 |     Hey |        1 |        1 |       0 |
|  4 |    Argh |        2 |        1 |       0 |
|  5 |     Yar |        2 |        1 |       0 |
|  6 |  Hooray |        2 |        1 |       0 |
|  1 |   Hello |        1 |        2 |       0 |
|  2 |      Hi |        1 |        2 |       0 |
|  3 |     Hey |        1 |        2 |       0 |
|  4 |    Argh |        2 |        2 |       1 |
|  5 |     Yar |        2 |        2 |       1 |
|  6 |  Hooray |        2 |        2 |       0 |
|  1 |   Hello |        1 |        3 |       0 |
|  2 |      Hi |        1 |        3 |       0 |
|  3 |     Hey |        1 |        3 |       1 |
|  4 |    Argh |        2 |        3 |       0 |
|  5 |     Yar |        2 |        3 |       0 |
|  6 |  Hooray |        2 |        3 |       0 |

SELECT message.Id,
       message.message,
       message.category,
       person.Id personId,
       CASE WHEN personmessage.messageId IS NULL
            THEN 0
            ELSE 1
            END enabled
FROM message
CROSS JOIN person
LEFT JOIN personmessage ON personmessage.personId = person.Id
                       AND personmessage.messageId = message.Id
ORDER BY 4,1

fiddle


Is the everything-is-arithmetic MySQL specific? Today I learned that SELECT f1,EXISTS(SELECT ...),f2 is valid in MySQL and, reading from StackOverflow, apparently in PostgreSQL as well and the EXISTS part is 0 when the subquery result is empty and 1 when the result is not. Then I began to think and tested a few queries looking like SELECT * WHERE column1 - column2 and even this worked and it's the same as column1 <> column2. And I even tried WHERE (column1 > 0) - column2 and even this worked. Is this SQL standard or just MySQL is trigger happy about casting booleans and integers with wild abandon? Or perhaps booleans do not even exist in SQL and 0 / 1 represents them?

Or perhaps booleans do not even exist in SQL and 0 / 1 represents them

boolean do exists in the SQL standard but as "real" booleans.

So valid boolean literals are false or true.

  Is this SQL standard or just MySQL is trigger happy about casting booleans and integers with wild abandon

MySQL does not actually support boolean values. It simply treats anything that can't be converted to a non-zero number as "false".

E.g.

select *
from some_table
where 'xxxx';

is accepted as valid (even though it is invalid SQL) and is equivalent to where false because xxxx can't be converted to a number and thus is assumed to be 0 which is treated as false.

On the other hand

select *
from some_table
where '42xxxx';

is treated as where true because 42xxx will be (implicitly) converted to 42 which in turn is treated as "true".

The query:

SELECT *
WHERE column1 - column2

is invalid standard SQL because it yields a number (assuming column1 and column2 are numbers) and a number is not a boolean expression.

You can see this behavior when doing:

select 1 > 0;

MySQL returns the number 1 whereas a database supporting real booleans (e.g. Postgres) will return true. Oracle does not support real booleans so select 1 > 0 from dual is actually invalid there.


How do I set up active-passive database clustering between data centers? I am looking to setup an active/passive database cluster between data centers using mirroring and/or log-shipping across these two nodes. My challenge is understanding High Availability (HA) and getting the HA configuration correct.

Scenarios:

Hardware Failure.  I have no issues with this and understand that all traffic will be diverted to another data center or another node with in the same data center.
Data Center 1 Failure. Again, I have no issues here. All the traffic will be diverted to another data center.
Database Updates. I am facing an issue here.

During a patch upgrade on the Data Center 1 database, I want to divert all traffic to the Data Center 2 database.  After patching is complete, I want to revert it back to Data Center 1.  This may require breaking the mirroring/log-shipping configuration.  How would I then copy all the changes made on the Data Center 2 database back to Data Center 1?  Is there any feature I can use to get this incremental data while minimizing downtime?

So you've got a couple of options when it comes to doing this.

Option 1: Use a geographically dispersed cluster which is a single cluster deployed in both data centers.  This gives you one single cluster which exists in both sites.  You would end up having several nodes in each site so that when doing local site patching you don't have to fail over to the second site.  In the event of a site failure you would then fail the cluster over to the second site.  To change to the clients would be needed as they would just connect to the same virtual name which would then resolve to a new IP address.

Option 2: Database Mirroring between sites.  This is the preferred option as it's easier to setup, doesn't require expensive SAN replication software, and failover and back is all handled via SQL Server.  I'd recommend a two node cluster in each site for local HA, then mirroring between the sites for your DR setup.  Local HA covers server patching which the database mirroring covers you for DR.  If the sites are far from each other setup the system with asynchronous mirroring, if they are close to each other the you can use synchronous mirroring which will allow for no data loss.


InnoDB row level locks without SELECTs? I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:

Each client runs the following transactions repeatedly:

Start transaction
Insert one row into table A
for i = 1 to 22
  Do some time-consuming calculation (30 sec)
  Insert results into table B
end
Commit transaction

The lock wait timeouts happen when inserting into table A. Table B's primary key is the same as table A's augmented by another field. Table B has a foreign key into table A.

There are no SELECTs anywhere. No row is inserted twice. Transaction isolation level is REPEATABLE_READ.

SHOW ENGINE INNODB STATUS shows up to 36 row-level locks for some active transactions.

I do not understand why there are any locks. Can anyone give me some hints as to what's going on?

MySQL is taking too much of the innodb buffer pool size for your table locking.

Insert innodb_buffer_pool_size=2g in my.cnf file, which is most probably in /etc/mysql/


SQL server agent SSIS error I get the following error when I execute my package as a SQL server agent job.

It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.

I don't even know how to begin fixing this error.

Where should I check first?

Date        a value of time
Log     Job History (MyJob)

Step ID     1
Server      PCTSQL004
Job Name        MyJob
Step Name       Job_1
Duration        00:00:00
Sql Severity        0
Sql Message ID      0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted       0

Message
Executed as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client).  The step failed.

It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive.

You have to save the package with EncryptAllWithPassword using a password.

Then schedule it using SQL Agent Job as below :

"DriveLetter\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "SSIS_Package_Location\bin\Package.dtsx" /DECRYPT "PasswordStrong007" /CONFIGFILE "Location_to_Config_file_If_any\Config.dtsConfig" /CHECKPOINTING OFF /REPORTING E

Now the user account that you use should have permissions on the database server instance.

You can check the permissions using below T-SQL :

SELECT [Login Type] = CASE sp.type
        WHEN 'u'
            THEN 'WINDOWS Login'
        WHEN 's'
            THEN 'SQL Login'
        WHEN 'g'
            THEN 'GRP'
        END
    ,convert(CHAR(45), sp.NAME) AS srvLogin
    ,convert(CHAR(45), sp2.NAME) AS srvRole
    ,convert(CHAR(25), dbp.NAME) AS dbUser
    ,convert(CHAR(25), dbp2.NAME) AS dbRole
FROM sys.server_principals AS sp
JOIN sys.database_principals AS dbp ON sp.sid = dbp.sid
JOIN sys.database_role_members AS dbrm ON dbp.principal_Id = dbrm.member_principal_Id
JOIN sys.database_principals AS dbp2 ON dbrm.role_principal_id = dbp2.principal_id
LEFT JOIN sys.server_role_members AS srm ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals AS sp2 ON srm.role_principal_id = sp2.principal_id


SSIS Package Execution From Visual Studio Unit Test Hello I am attempting to run a test which contains the following code:

        string pkgLocation;
        Package pkg;
        Application app;
        DTSExecResult pkgResults;

        pkgLocation =
          @"C:\Users\mYCoMppp\Documents\Visual Studio 2012\Projects\packageOnExeCreatesTable" +
          @"\Integration Services Project8\Package.dtsx";
        app = new Application();
        pkg = app.LoadPackage(pkgLocation, null);
        pkgResults = pkg.Execute();

        Console.WriteLine(pkgResults.ToString());
        Console.ReadKey();

This is an attempt to populate a table which has had all of its records removed thanks to the test I ran before it. I am not getting any errors - however it does take a while to complete the test. I do not know exactly how long it takes, because the test is running as I type this.

So alas, my question. I would like to know if anybody out there can explain to me if this is a time consuming way of executing a package. And also if anybody knows of any other way to execute SSIS packages faster.

First thing write another package that does a simple file copy task for example and see if that takes a long time using your same call method. If it does then perhaps your call method could be improved. I suspect it will be quick and the package code is where the problem is, not how its called.


UPDATE column without FK fires other FK triggers constraint check I'm using PostgreSQL 9.6 and i have noticed that when i execute an UPDATE on a single column without foreign key it will fire all triggers for other columns that have a value and a foreign key.
I see this thanks to EXPLAIN ANALYZE that gives me "Trigger for constraint" for each one of those other columns.
With massive updates those triggers takes 50% of the execution time

EDIT:

CREATE TABLE parent (
    id INTEGER,
    PRIMARY KEY(id)
);

CREATE TABLE child (
    id INTEGER,
    is_enabled BOOLEAN DEFAULT FALSE,
    parent_id INTEGER references parent(id),
    PRIMARY KEY(id)
);

INSERT INTO parent (id) VALUES (1);
INSERT INTO child (id, is_enabled, parent_id) VALUES (1, FALSE, 1);

UPDATE child SET is_enabled = FALSE WHERE id = 1;
EXPLAIN ANALYZE
UPDATE child SET is_enabled = TRUE WHERE id = 1;

OUTPUT:

Update on child  (cost=0.16..8.17 rows=1 width=15) (actual time=0.017..0.017 rows=0 loops=1)
  ->  Index Scan using child_pkey on child  (cost=0.16..8.17 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=1)
        Index Cond: (id = 1)
Planning time: 0.033 ms
Trigger for constraint child_parent_id_fkey: time=0.168 calls=1
Execution time: 0.199 ms

Notice Trigger for constraint child_parent_id_fkey even if i haven't changed parent_id

EDIT 2:
It happens when issuing 2 update statements, same transaction, that covers the same row even if nothing is changed and no FK column is used.
Here is a dbfiddle by joanolo

Is this an expected behavior? Should i fill a bug report?
Now i'm trying to avoid to touch same rows twice in the same transaction

Tom Lane answered on the mailing list

This happens because an UDPATE invalidates the foreign key triggers for a previous INSERT in the same transaction, so postgres fires them when there is an old tuple version in the same transaction. It doesn't have the data to know that the previous change on that row was an update without fk changes.


Product Attribute System While Avoiding EAV I'm working on building an attribute system for products. The issue that I'm running into is that various products can have very different attribute requirements.

Some E-commerce websites such as Magento use an EAV system. That's something that I'd like to avoid due to performance issues, database cleanliness/complexity/control.

So far I'm leaning towards using a variety of tables. For example I might have a table of attributes that pertain to medical devices and then a different table that is for toys and games. Any attributes that are generic would just fall into the actual product table. If I do choose to go with this option, then I suppose my product table would have a column which represents which attribute table is used.

I'm not actually a Database Administrator, so I don't really know what's best. I hope that someone is able to give me some insights into a good implementation or acknowledge that my current thought process is in the right direction.

Thank you

Your question looks like an instance of a very widespread pattern.  This pattern goes by the name "generalization and specialization" in Extended ER modeling.  It also goes by the name "classes and subclasses" (or "types and subtypes") in object modeling.

When designing an object oriented data structure, the concept of inheritance plays a key role in the design.  When designing a relational data model, the designer is faced with the reality that the relational data model does not include the concept of inheritance.

So the designer is forced to implement inheritance through a workaraound, by designing tables that work more or less the way inheritance works.  This situation has been studied before, and some fairly good designs have been used with fairly good results.

Two of these designs go by the names "Single Table Inheritance" and "Class Table Inheritance".  These two designs are well presented by Martin Fowler in one of his books.  You can see a summary at these two web pages:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

You can choose which one depending on the specifics of your case.

In conjunction with class table inheritance, there is a technique known as Shared primary key. Shared primary key involves defining a key to the subclass tables that is both a primary key and also a foreign key that references a row in the class table.  This does two things:  it enforces the one-to-one nature of these relationships and it makes the relevant joins simple, easy, and fast.

You can see this technique illustrated over in the StackOverflow area:

https://stackoverflow.com/questions/tagged/shared-primary-key

These three designs or techniques are not perfect, and they require a little work on the part of the application programmer.  But they go a long way to meeting a very commonplace need.


Would it be correct to add extra columns to the junction table according to normalization rules? Hello I have many to many relationship. I learning normalization rules.
I add quantity because quantity column is used only for products associated with the invoice.
My question is: Can I add extra column in junction table? Is it OK to normalization rules or is there a better solution?

yes you can add columns to a bridge table, as long as they are attributes to that for that relationship.
Customers     CustomerProducts      Products

customerId    customerId            productId
              productId
              price
              quantity
              buyDate

The columns price, quantity and buy_date, are all specific to the that entry


EER Design specification I just started learning about Databases, and my first assignment has information about an Art Museum that we are supposed to use to define a DB for, demonstrated with an EER Diagram.

One specification is as follows:

"The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist (if known), a Year (when it was created, if known), a Title, and a Description. The art objects are categorized in several ways, as discussed below."

What role, if any, does "if known" play in terms of the diagram? I reviewed the instructors notes looking for something resembling such specification, but I couldn't find anything in the diagram components that would imply whether or not information is available. Thoughts?

In the logical model it means that the attribute is optional, which translates into a nullable column in the physical implementation.


Multitenant application: several tables or several IDs? We are designing a common product to serve several companies. The most convenient way to do this, technically speaking, is to deliver a database to each customer.
But we predict that, depending on the customer size, deliver a database to each customer can be prohibitive (because of the cost of a cloud-based database, as we do not work with local databases), but we would like to serve the smaller companies too. So we are discussing the use of just one database to several customers.
If we decides to do so, what would be the best approach?

Apply a "master identifier" to all tables.
All tables will have a column that we call, lets say "customer_id". So, each register recorded in the database will carry the customer ID, and we can differ between what belongs to which customer.

Prefix tables.
All tables will be replicated to each customer (in the same database), in which each one will receive a prefix, pointing to what customer the table belongs. In the backend, easily we add the "prefix" feature to differ between customers.

Of course, each approach can be applied to reach the same objective, but we would like to know "the best approach". What are the implications to each approach, covering questions like performance, scalability, development complexity, easiness of administration, etc.?

The best approach is a mixture of allowing multiple databases and having a master identifier within the table.

This allows you to host multiple smaller customers in one database, but also to use multiple databases (so you can scale). Because there may be a point where you want to have more data than you can handle in one database.


cnames for mongo config server I am seeing this line in many mongoDB links

  Use CNAMEs to identify your config servers to the cluster so that you
  can rename and renumber your config servers without downtime

I need some help in how set or add CNAMEs to my mongo config servers.

Can anyone help me with a brief explanation on how to implement that in a shard instance?

A Canonical Name record (abbreviated as CNAME record) is a type of resource record in the Domain Name System (DNS) used to specify that a domain name is an alias for another domain, the "canonical" domain.

So, you give a 'dnsname' to your config servers. For example mongoCfg1, mongoCfg2 and mongoCfg3.

Those dns you can register on a dns server, or in the hosts files of your mongo-servers:

mongos:~$ cat /etc/hosts
127.0.0.1 localhost
127.0.1.1   mongos1

10.0.0.10   mongoCfg1
10.0.0.11   mongoCfg2
10.0.0.12   mongoCfg3
10.0.0.13   mongoShard1Repl1
10.0.0.14   mongoShard1Repl2
...

In your mongos config file or startup parameters or even the replication/shard configuration in your mongodb, it is adviced to use those CNAMES.

Example of mongos with cname as configdb parameters:

mongos --configdb mongoCfg1:27019,mongoCfg2:27019,mongoCfg3:27019 --port 27017 --logpath /data/log/log.mongos

In case you need to replace a server, let's say mongoCfg2 needs to be replaced, then you can change the ip for mongoCfg2 in all the hosts files so they will refer to the new config server.


I've got 60 million sales of 800 different liquid products whose price change daily ..and I'm having a bit of a battle getting to customer stats like who's bought the most, and when was their last purchase.

I've no problem writing the query, it's just really slow

Sales
CustID, ProdID, Qty, SaleTimestamp
1,1,10,2000-01-01 01:01:01

ProductPrices
ProdID,Price,PriceDate
1,100,2000-01-01

Sales are recorded as an amount sold at a time/date. All products change price daily, so to find the total sales to all customers we need to join Sales and ProductPrices on productID and date of sale

SELECT
  CustID, SUM(Qty*Price), MAX(s.SaleTimestamp)
FROM
  Sales s
  INNER JOIN
  ProductPrices p
  ON s.ProdID = p.ProdID and CAST(s.SaleTimestamp as date) = p.PriceDate
GROUP BY
  CustID
HAVING SUM(Qty*Price) > 10000 --only big customers

It's a simple enough query, but it's been running for 45 minutes now. I've got 60 million rows in sales, and 800 products * 365 days = 292,000 prices. Sales:ProductPrices is Many:One

I've tried another way round to try and pre-reduce the dataset to just customer-product-day-totalqty:

WITH s AS (
 SELECT
  CustID, ProdId, CAST(SaleTimestamp as date) Dat, SUM(Qty) TotQty
 FROM
  Sales
 GROUP BY CustID, ProdId, CAST(SaleTimestamp as date)
)

SELECT CustId, SUM(s.TotQty * p.Price) Tot
  ProductPrices p
  INNER JOIN
  s
  ON
    p.PriceDate = s.Dat and p.ProdId = s.ProdId
GROUP BY CustID
HAVING SUM(Qty*Price) > 10000 --only big customers

But no luck. I've wrapped the entire thing in a count(*) to test whether it was completing the query quickly but taking ages to farm N million rows back to SSMS - the count is still slow too. I've swapped the cast to date out for a BETWEEN style on the non CTE query (either way round: saletimestamp between pricedate and pricedate+1day / pricedate between saledate-1day and saledate) but it appeared to make it worse, the most costly operation becoming a merge join on the tables, with SQLS stating that it was a many:many

I'm pretty sure the join is the problem, as I can do this:

WITH s AS (
 SELECT
  CustID, ProdId, CAST(SaleTimestamp as date) Dat, SUM(Qty) TotQty
 FROM
  Sales
 GROUP BY CustID, ProdId, CAST(SaleTimestamp as date)
)
SELECT COUNT(*) FROM s

And I get an answer of 13 million rows in 6 seconds.. If I do the same thing with products:

WITH p AS (
 SELECT
  ProdId, PriceDate, AVG(Price) price
 FROM
  ProductPrices
 GROUP BY ProdId, PriceDate
)
SELECT COUNT(*) FROM p

I get 292000 in less than 1 second. If I take these two queries and put them together for my answers:

WITH s AS (
 SELECT
  CustID, ProdId, CAST(SaleTimestamp as date) Dat, SUM(Qty) TotQty
 FROM
  Sales
 GROUP BY CustID, ProdId, CAST(SaleTimestamp as date)
),
p AS (
 SELECT
  ProdId, PriceDate Dat, AVG(Price) price
 FROM
  ProductPrices
 GROUP BY ProdId, PriceDate
)
SELECT COUNT(*) FROM(
  SELECT s.CustID, SUM(TotQty * Price)
  FROM p INNER JOIN s ON p.ProdId = s.ProdId and p.Dat = s.Dat
)

I'm back to 20+ minutes now and no result ..

I realise that it's probably hard to answer without seeing the plan - do I post the XML?

Server hardware is pretty immense - multiple 20 core Xeon Gold CPUs and 2TB ram

Without seeing the execution plan, it's impossible to say exactly what the problem was. However, the root cause seemed to be with the way the optimizer was handling joining the queries within your CTEs (or something along those lines).

In SQL Server CTEs aren't currently materialized or fenced off (unless you use TOP inside one). In many cases, breaking up queries (whether they use CTEs or not) can be helpful to the optimizer.

As of this answer, there are no hints to alter the way CTEs are handled.


MySQL log buffer parameters I have a 150 GB PROD MySQL Database (version 5.6.13).

What should be the values for below parameters:

innodb_log_buffer_size
innodb_log_file_size
innodb_buffer_pool_size (RAM - 20 GB)

Innodb_buffer_pool_size the maximum you can have. If this is a dedicated box to mysql something around 16Gb should be a good start.

innodb_log_file_size depends on the amount of write you have. Run the following commands in your mysql console while database is being used:

> pager grep sequence
> show engine innodb status\G select sleep(60); show engine innodb status\G

Output will be something like this:

Log sequence number 8622728172243
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 8622740654048
1 row in set (0.00 sec)

Now given the numbers run:

> nopager
> select (8622740654048 - 8622728172243) / 1024 / 1024 * 60;

That returns you the amount of MBs written to the log file in an hour which is the rule of thumb for smoothing out write IO efficiently.

To my.cnf:

innodb_log_files_in_group = 2
innodb_log_file_size = [the calculated amount / 2]  # Because you have two logfiles

innodb_log_buffer_size depends mostly on the size of your transactions. If you don't have big blobs in large transactions somewhere around 16M to 64M should be sufficient. See what works best for your workload. Start from a low size and keep an eye on innodb_log_waits status:

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

If that increases than increase the size of the buffer.


How to query/join tables where foreign key constraint is set in schema I'm relatively new to sqlite and databases in general.
I've successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table.  The schema also has the foreign key constraint set on the child keys.

Performing a query where every table is joined with the SELECT * FROM...JOIN...ON ......... works without issue, but it is laborious manually connecting the keys.

My question is: Is it possible to perform a query/join of the tables without the ON statement which links each parent/child key relationship?  Since this was already established in the schema definition, this seems to be redundant.  I'm trying to simplify the sql command and make use of the parent/child relationship already defined.  When I perform said query but omit the ON statements, the results are not what I expected.  Any guidance is appreciated.

A foreign key constraint is just a constraint, i.e., it prevents you from inserting data that would violate the constraint.

Foreign key constraints have no effect on queries; if you want to join tables through these values, you still have to write the join. (Why? Because the SQL standard has always said so, so changing it would break backwards compatibility.)

The only way to avoid the ON is by giving the columns the same name in the parent and child tables so that you can use the USING clause instead:

SELECT ... FROM ParentTable JOIN ChildTable USING (ParentID);

(And this is less typing than a hypothetical ... JOIN ... USING FOREIGN KEY ConstraintName.)


What is the equivalent of the SQL Server CHAR function? SQL Server has CHAR ( integer_expression ), so I can do

SELECT CHAR(65)

What is the equivalent on Postgresql? I have tried SELECT E'\0x65'; but I get

  ERROR:  invalid byte sequence for encoding "UTF8": 0x00

Have a look at the docs on PostgreSQL String Functions and Operators,

  chr(int) Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate an ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes.

Demonstrating chr(), its inverse, and both.,

# SELECT chr(65), ascii('A'), chr(ascii('A'));
 chr | ascii | chr
-----+-------+-----
 A   |    65 | A
(1 row)

In your question you use E'' (the "bytea Hex Format") which is totally possible too, though I wouldn't suggest it for simple problems like this. You can do that like this,

SELECT E'\x41'; -- 41 is hex for 65 (in base 10)
SELECT E'\101'; -- 101 is octal for 65 (in base 10)
 ?column?
----------
 A
(1 row)


Join SQL Server table in OPENQUERY on multiple keys I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys:

SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A
INNER JOIN SQL_TABLE B
ON A.KEY1 = B.KEY1 AND A.KEY2 = B.KEY2
INNER JOIN SQL_TABLE C
ON A.KEY3 = C.KEY1 AND A.KEY4 = C.KEY2;

But ORACLE_TABLE is very huge and this query takes too much time to run. Is there a way to pass SQL_TABLE to OPENQUERY and do the join on the Oracle server?

OK, then think the other way around.  Something like the general outline:

Create a Linked Server from Oracle to SQL Server and SQL Server to Oracle.
From Oracle select the 100 rows of SQL Server data and store in a temporary table in Oracle.
Run the query in Oracle to join/filter/whatever the 1 billion Oracle rows by the 100 SQL Server rows in the temporary table to create the result set.
Use OPENQUERY from SQL Server to bring the results back to SQL Server.

INSERT INTO SqlServerTable
  SELECT * FROM OPENQUERY (LinkedServerName, 'query text');

I likely missed some step, but this basic approach is to move the small amount of SQL Server data over to Oracle, run the query, and then read the result back to SQL Server.

EDIT:  At https://stackoverflow.com/questions/29200147/how-do-i-convert-an-oracle-timestamp-data-type-to-sql-server-datetime2-data-type Jon of All Trades showed how he converts an Oracle timestamp to a SQL Server datetime2.

(I have no Oracle server to test against, however.)


MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID I am using Running same source code and same database for WordPress, only the mysql version is different. It make my pagination post have duplication.
Here is what I found out. Any idea how to solve it in higher version?

This is for Page 2, and Page 3 query output. Left-Hand side is MySql 5.5, Right-Hand side is MySql 5.6.  up_posts.ID is unique.

SQL Statement:-

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 10, 10

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 20, 10

Page 2:- different is LIMIT 10,10

Page 3:- different is LIMIT 20,10

Seem like MySQL 5.5 will sort by the ID, but 5.6 above does not have this ability.

Added ORDER BY up_posts.post_date DESC, up_posts.ID DESC no more duplicate result. Seem like we need to manually add this to avoid an unnecessary problem.

FYI. It applied to MySQL 5.6 and 5.7.

See 8.2.1.16 LIMIT Query Optimization

If you want to order by primary key, you have to say so explicitly.


Does Postgresql apply the same type of locks on UPDATE with range condition and on UPDATE with in-set condition? Does Postgresql apply the same type of locks on UPDATE with a range condition and on UPDATE with in-set condition?

E.g. will the queries:

UPDATE table WHERE id IN (1,2,3,4,5,6,7,8,9,10);
UPDATE table WHERE id >= 1 AND id < 11;

Cause the same type of locks on table?

Yes, both statements will take a UPDATE lock on all the modified rows.

See the documentation on row locks:

  The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns.


Perform update and raise notice in Postgres Function I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened.
I don't know is "NOTICE" is the proper RAISE (Why not "INFO"?).
The central question is: how I can add the raise notice argument after the update?

The problem can be, that level for messages is too high
so try this appproach

CREATE TABLE id ( "id1" INT)

INSERT INTO id VALUES (1)

1 rows affected

do
$$
declare
  newnumer int = 90;
begin

  UPDATE id SET id1 =  newnumer WHERE id1 = 1;
SET client_min_messages TO INFO;
  raise info   '"NOTICE" is the proper RAISE (Why not "INFO"?)' ;
        -- catch notification

end;
$$
language plpgsql;

✓

SELECT * FROM id;

| id1 |
| --: |
|  90 |

db<>fiddle here


TSQL command to delete backup files older than N hours Can anyone please help me with a TSQL commmand to delete backup files older than N hours?

Remember that T-SQL should not be used to do file maintenance. Instead you should use Powershell to delete /move / copy etc files.

Tsql way:

use xp_cmdshell command along with the FORFILES command

e.g Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old.

EXEC xp_cmdshell 'FORFILES /p c:\BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'

Disclaimer: Enabling xp_cmdshell is a security risk and not many companies allow it.

Powershell Way --> This should be the right approach !!

A full script can be found at technet.

## Delete files older than 90 days

$a = Get-ChildItem C:\Scripts
foreach($x in $a)
    {
        $y = ((Get-Date) - $x.CreationTime).Days
        if ($y -gt 90 -and $x.PsISContainer -ne $True)
            {$x.Delete()}
    }


Recover MongoDB data without WiredTiger.wt file We have recovered a MongoDB database from filesystem, but the WiredTiger.wt file has changed since then (i.e. the collection has been dropped).

So is it possible to read data (documents) from some .wt files without having the corresponding WiredTiger.wt?

Or create a WiredTiger.wt file with our own .wt files?

If you perform the recovery by overwriting some collection-*.wt files inside your dbpath, then your database is in an unsupported state. Data loss is quite likely in this case.

If you want to restore the database, you would need to restore the dbpath as a whole and not as individual files. Please see MongoDB Backup Methods for instructions and information on supported backup and restore methods.

The WiredTiger.wt file is a crucial metadata file that keeps track of the state of the whole database and all the *.wt files. Hence, the content of this file is deployment specific, and not transferrable to other deployments. It is not possible to recreate the content of this file.

If you have a known good backup of the whole dbpath, you may be able to restore to a valid state of the database, as long as you restore the dbpath as a whole.


Next value for Sequence Function Why does this give me the same value, four 1?

USE [TSQL2012]
GO
IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL
    DROP SEQUENCE dbo.sqlsequence;
GO
CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH 1
MAXVALUE 8 CYCLE

SELECT NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq1] ,
    NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq2] ,
    NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq3] ,
    NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq4]

But this works fine as expected.  Gives me 1, 2, 3, 4

USE [TSQL2012]
GO
IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL
    DROP SEQUENCE dbo.sqlsequence;
GO
CREATE SEQUENCE [dbo].[SQLSequence] AS INT
START WITH 1
MAXVALUE 8 CYCLE

SELECT NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq1]
SELECT          NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq2]
SELECT          NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq3]
SELECT           NEXT VALUE FOR
        [dbo].[SQLSequence] AS [Seq4]

As per the documentation states:

  The NEXT VALUE FOR function is nondeterministic, and is only allowed in contexts where the number of generated sequence values is well defined. Below is the definition of how many values will be used for each referenced sequence object in a given statement:

  SELECT - For each referenced sequence object, a new value is generated once per row in the result of the statement.


Ensure same tenant in a Many to Many relationship I am trying to draft a very simple data model for a multi-tenant database.

So far I have only 3 tables:

company: pretty much my tenant
account: is part of one company and zero or more groups
account_group: same as account: one company and zero or more accounts

Here is the SQL:

CREATE TABLE company (
  name TEXT NOT NULL,

  PRIMARY KEY (name)
);

CREATE TABLE account (
  email_address TEXT NOT NULL,
  company_name TEXT NOT NULL,

  PRIMARY KEY (email_address),
  FOREIGN KEY (company_name) REFERENCES company (name)
);

CREATE TABLE account_group (
  name TEXT NOT NULL,
  company_name TEXT NOT NULL,

  PRIMARY KEY (name, company_name),
  FOREIGN KEY (company_name) REFERENCES company (name)
);

CREATE TABLE account_group_membership (
  account_group_name TEXT NOT NULL,
  account_group_company_name TEXT NOT NULL,
  account_email_address TEXT NOT NULL,

  PRIMARY KEY (account_group_name, account_group_company_name, account_email_address),
  FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name),
  FOREIGN KEY (account_email_address) REFERENCES account (email_address)
);

In this specific case, there is no constraint between the Account Company and Group Company.

After doing some search, I found this question and tried to add the following to my account_group_membership table;

CREATE TABLE account_group_membership (
  account_group_name TEXT NOT NULL,
  account_group_company_name TEXT NOT NULL,
  account_email_address TEXT NOT NULL,
  account_company_name TEXT NOT NULL,

  PRIMARY KEY (account_group_name, account_group_company_name, account_email_address),
  FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name),
  FOREIGN KEY (account_email_address, account_company_name) REFERENCES account (email_address, company_name),
  CONSTRAINT same_company CHECK (account_group_company_name == account_company_name)
);

Unfortunately this doesn't work since the company_name column of the account table is not unique.

I cannot:

Make the column company_name unique since multiple accounts can be from the company.
Use email_address and company_name as primary key for an account. The email_address should be unique in the whole table. Also account will mostly be queried by email_address and from time to time by company_name.
See any other solution actually....

So is there any simple way to do this (simple as in easy to understand and maintain) ?
Alternatively, does having this problem, mean that there is something wrong going on in the design ?

This is almost an anti pattern if you ask me.

All accounts have a company
All account_groups have a company
They're all linked through a linking table which also has a company.

So, everything has a company: except when it doesn't? And if a company has a subsidiary you're in a world of trouble. It may make sense. But, what you seem to be doing is creating buckets which you can relate to in order to emulate hierarchy; however, the hierarchy is easier to just have in all it's glory.

CREATE TABLE accounts (
  accountid             serial PRIMARY KEY,
  name                  text   NOT NULL,
  email                 text,
  contact_name_given    text,
  contact_name_surname  text,
  parent_accountid      int    REFERENCES accounts
);

INSERT INTO accounts (accountid,name,email,contact_name_given,contact_name_surname,parent_accountid) VALUES
  (1,'ACME', 'company@acme.com',null,null,null),
  (2,'ACME Sales', 'sales@acme.com', 'John D', 'Supervisor', 1),
  (3,'Evan Carroll', 'ecarroll.sales@acme.com', 'Evan', 'Carroll', 2);

Then get the list with a RECURSIVE CTE

WITH RECURSIVE t(accountid, name, email, contact_name_given, contact_name_surname, level)
AS (
  SELECT
    accountid,
    name,
    email,
    contact_name_given,
    contact_name_surname,
    0
  FROM accounts
  WHERE parent_accountid IS NULL
  UNION ALL
    SELECT
      a2.accountid,
      a2.name,
      a2.email,
      a2.contact_name_given,
      a2.contact_name_surname,
      t.level+1
    FROM t
    JOIN accounts AS a2
      ON ( t.accountid = a2.parent_accountid )
)
SELECT *
FROM t;

 accountid |     name     |          email          | contact_name_given | contact_name_surname | level
-----------+--------------+-------------------------+--------------------+----------------------+-------
         1 | ACME         | company@acme.com        |                    |                      |     0
         2 | ACME Sales   | sales@acme.com          | John D             | Supervisor           |     1
         3 | Evan Carroll | ecarroll.sales@acme.com | Evan               | Carroll              |     2
(3 rows)

Now you just need to give logical names to the level = 0,1,2 if that's something the task really requires. or make the schema more accommodating of different types of entities in the hierarchy.


Designing a table in SQL I have a table with 3 columns id,Item,parentid
when storing data the top most level will have a parentID of 0
while the second level will have ParentID of 2 (relative) which will be an id an item.

 ----------------------
 id | Items    | parentId
 ----------------------
  1 | Computers|    0
  2 | Dell     |    1
  3 | laptop   |    2

Is this the best design considering the levels might be endless.

The question of whether or not this is the "Best Design" is really more of a philosophical question that needs to have more information explained. If you are looking to merely manage a simple hierarchical set of items, then this is good design.

You're definitely going to have to be very mindful of referential integrity if items are being added deleted in the various layers of the parent-child relationship.


A few questions regarding Sql Server Utility I just stumbled across the Sql Server Utility (don't ask) and was just wondering what the overhead of the collect and upload process is like? How does this compare with other monitoring/health tools like Idera's Sql Diagnostic Manager?

Plucking an answer out of thin air..

I'd suggest the same load. At the end the day, the same dmvs will be queried by any monitoring tool. The same ones that I'd query adhoc or use in scheduled queries. SQL Server utility or a 3rd party tool just automate this for you.


Database Design challenge: One (Table)-to-Many (Tables) Relationship? I am rather new to DBs, and after days of being stuck, researching and trying different solutions, I turn to this community's expertise for help.

I am trying to have one Person table in my MSSQL DB, that would contain all our contacts. Keeping all in one table facilitates a lot of things for us downstream.
Challenge is we have to split the Company table into different tables, by categories, as we need different attributes for each - e.g. Fuel Supplier, Engine Manufacturer and Maintenance Company would each have their own table, listing companies in said category, with their own specific attributes.

Therefore, I would need a "one table to many tables" relationship where the various Company tables feed into only one attribute ("Company") in the Person table. Goal being that within a Person record, one could choose the Company that person works for - only one company, from across multiple tables.

So ideally something like that:

Is there a way to achieve this or any workaround?

Thanks for your help!

This seems like a case where you'd want to use inheritance (subtype/supertype), but it isn't.

A company that manufactures engines won't necessarily always be an engine manufacturer, and might not only be an engine manufacturer. It could change what it makes, or it could make multiple things

"Fuel Supplier" or "Engine Manufacturer" are roles played by companies. A company has many roles. So you have a company table, and you have a company_role table with a foreign key to company. If roles have different columns, then this would be a case where you can use inheritance

While you're at it though, you should know that it's useful to use inheritance to create a super type for individuals (people) and companies (or organizations). This would let you have say a sales order linking to the "customer" which could be a person or company


after executing a back up cmd (via query) how to kill it I have a big database (150 GB) that needs a back up and I don't have space in current machine, so I am pointing it to the different box which has space. After executing the cmd, it is executing the query but taking long time, not showing stats also.

Query used:

backup database [DBName] to
disk = '\\Servername\H:\Backup7-7-2017\dbname.bak'
with copy_only, stats = 1

go

It is running for more than 5 minutes and no change on stats, and in destination folder the file is not created yet, so I am planning to go for manual backup from GUI. Now I want to kill this running backup as it is in suspended mode, when I checked in activity monitor.

There are many ways to kill the backup - exactly the same as you would any query.

ALT+Break in SSMS
Right-click "kill process" from the activity monitor
CTRL+C in sqlcmd
and so on...(these seemed most appropriate given the implied knowledge of the asker)

It probably won't end immediately but just give it a little time. Also I'd say give it more time in general, a backup of a large DB can take some time and may not start producing stat updates immediately. Check out previous backup times as a guide if possible.


Hyper V replication - vs - sql replication solutions I need to establish an offsite copy of a number of production servers, including SQL server.

Hyper V replication has been chosen as a simple solution (all servers are virtual). This is to supplement offsite backup for disaster scenarios. We do not need instant failover, mirroring or any advanced setup, just a reasonably recent copy of the most critical data and systems.

Hyper V replication looks simple, set-and-forget (just make sure to put tempdbs on a non-replicated VHD, set flags to maintaing write order across VHDs). But I am still concerned of efficiency and robustness, and thinking that a simple log shipping setup might be better, easier on the bandwith (would not need to replicate anything but the logs)

Any opinions or insight on what (when) to choose one over the other would be welcome

I think the answer depends on the SQL skill level of the people who will ultimately be responsible (lose their job) if the data can't be recovered in a DR scenario.

Most of the time when DBAs are chit chatting you'll hear horror stories of vendor solutions that didn't work, or stopped working, or weren't monitored. And when the data is lost it's lost. I've seen all 3. It's all good to say some other team will be monitoring it with a fancy tool - until a few months later when they've learned to ignore the alarms, and simply don't have time to respond to your repeated emails that it's broken.

That's why DBAs like me are a bit anal and insist on ownership and responsibility wherever there's accountability. I keep backups in SQL because then I can verify they're working and monitor them as well. I keep it all as simple as possible because simple works.

On the other hand if someone hands you a server and says "this is a copy, confirm its a proper copy and working", there's not a whole lot you can do without SQL Data Compare and an outage window.

If a place doesn't have a DBA, or there are licensing restrictions or software installed on the database server, then you make do with what you can. And vendors and consultants will often play along as well because when the data is lost they've already been paid and moved on (again they're making do with what they've been told or provided).

But for me? For best practice and not even a difficult one at that? No vendors in my backups.

PS: The exception being underlying snapshots of the backup media like network drives, for long term or redundant storage, as long as it's clear whose responsible for validating that's working. However again it's a matter of buyer beware - when it runs out of snapshot space someone will inevitably delete all your old backups without consultation; I've seen it happen time and again. But in this case as long as it's clear X people are responsible for long term file storage there's not much you can do.


SQL Server: Using Dual Databases for Performance? We have a SQL database that stores application usage logs for about 3000 PCs. These PCs send their usage data to the SQL server around 10-20 times per day. We used to store only the most recent 60 days of application usage, but the customer asked us to no longer purge data. Now that we have about a year's worth of data (about 6,000,000 rows), the SQL database is suffering from some performance issues. Not significant, mind you, but far more than any other database we have. There are a significant number of records added each hour (application open records), and within a few hours at most that record will be updated just once with the associated application close. It is these updates that you can see via SQL Activity Monitor that are taking considerable time to complete.

That UPDATE query is simple:

SELECT TOP 1 f_ID
from tb_applicationusage
WHERE f_application = 'xxxxxxx' AND
      f_computername = 'xxxxxxxxx' AND
      f_endtime IS NULL
ORDER BY f_starttime DESC

Effectively, it finds the most recent matching application start for a specific machine that doesn't yet have an associated application close. I can't think of a more efficient way to run the query, so I'm considering the following alternative:

Move to two databases:

Working database with only the most recent 24 hours worth of records
Final database with all other records

I'm no SQL guru, so I'm probably missing some drawbacks of this method. The goal would be to just have a SQL Agent job move the completed records over to the final database every night. Then, when the customer wants to run their monthly reports, I can just have that report query only the final database and not the working database. With only maybe 10,000 records to query in the working database instead of 6,000,000 it would seem logical that it would work faster. But again, it seems so simple I'm probably missing something obvious.

Version: Microsoft SQL Server 2008 R2

Two things

You don't actually say you have an index on the table -- I expect this would just solve your problem.  An index on f_application, f_computername, f_endtime, f_starttime should make your update time tiny with only 6mill records.
If you want to split it up, don't do it the way you describe, create a table for open but not closed records that you use before your current table.  Then when something "updates" delete it from the that staging table and insert it in your big table.  Using a staging table in this way is considered a leading/best practice - having a table arbitrarily split in two is always a nightmare to work with


Problem with nested UPDATE queries I cannot make an update query work. As a first step, the following one works correctly:

UPDATE Tab1
SET Tab1.a = '3'
WHERE
    Tab1.id IN ( 123, 456 );

where id is the primary key of Tab1.
However when I add a select to get the set of values Tab1.id must be in, I get the following MySQL error:

  Error code: 1093
  You can't specify target table 'Tab1' for update in FROM clause.

This is the complete query:

UPDATE Tab1
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN (
            SELECT Tab1.id
            FROM Tab1, Tab2
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

I cannot see how to fix this error.
Am I forced to split this query in two?

Thank you!

Try

UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

Check the documentation for further information (look for 'multiple-table syntax').  An important point is:

  If you use a multiple-table UPDATE statement involving InnoDB tables
  for which there are foreign key constraints, the MySQL optimizer might
  process tables in an order that differs from that of their
  parent/child relationship. In this case, the statement fails and rolls
  back. Instead, update a single table and rely on the ON UPDATE
  capabilities that InnoDB provides to cause the other tables to be
  modified accordingly. See Section 14.3.5.4, “FOREIGN KEY Constraints”.

You may check a similar example on SQLFiddle.


SQL Server DB Property Issue After SQL Server 2016 (SP1-CU3) patching we are not able to open the user database properties. Whenever its opened am getting below error:

  Method not found: System.collections.specialized.stringCollection
  Microsoft.sqlserver.management.smo.server.getpropertynames(System.type)'.
  (SqlManagerUI)

This is a shot in the dark, but since I've seen a number of scenarios where this has fixed odd errors in SSMS 2016, close SSMS, clear out all temporary files using the Disk Cleanup utility (cleanmgr.exe), start up SSMS, and then try this operation again.

Worst case you're no better off than you are now, but you've got a little more free disk space.


Oracle SQL NOT IN and NOT EXISTS returning different values. Why? I have two tables both containing an ID column which is NUMBER(38).

Table A has ~200k rows which is ALL IDs in existence, while table B has ~15k rows. Table B is a subset of Table A, which means all IDs in B exist in A.

(This is just an example, table names and column names are different and not matching, but I'm trying to make this easier to explain)

I am trying to find all of the records in table A whose ID does not exist in table B:

select ID from tableA where ID not in (select ID from tableB);

Returns 0 rows. This doesn't make sense to me, as I am expecting to get back 85k rows.

When I reverse the statement, I get 15k rows returned which makes sense because B is a subset of A.

Alternatively, I tried using NOT EXISTS:

select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

And this result returns 85k records VS 0 from the example above.

I've never seen this behavior before, does anyone know what might be going on and why the NOT IN isn't returning the same results as NOT EXISTS? In this situation, NOT EXISTS is working as expected, but NOT IN is showing 0 records which is wrong.

That is normal behaviour. NOT IN and NOT EXISTS is not the same. They return same results only as long as nulls are not involved.

SQL> create table tablea (id number);

Table created.

SQL> create table tableb (id number);

Table created.

SQL> insert into tablea values(1);

1 row created.

SQL> insert into tablea values(2);

1 row created.

SQL> insert into tableb values(2);

1 row created.

SQL> insert into tableb values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select ID from tableA where ID not in (select ID from tableB);

        ID
----------
         1

SQL> select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

        ID
----------
         1

SQL> insert into tableb values (null);

1 row created.

SQL> commit;

Commit complete.

SQL> select ID from tableA where ID not in (select ID from tableB);

no rows selected

SQL> select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

        ID
----------
         1

Your subquery returns nulls.


Design keeping track of activity I want to keep track of different activities/actions made by members of a website. For example "post created", "successful login", "file uploaded" etc.

Is it OK to just have a table named for example "actions" and save timestamps , user ID etc and the primary key for the actions such as post_id (table Posts), file_id (table Files)

Is this a valid way of doing it, or is there a better way?

Your proposed design is completely viable.

Couple of things to consider:

the table most likely will be write (INSERT) heavy
will grow indefinitely unless archived
queries most likely fall under OLAP and not OLTP (aggregations over point queries)

Workload

Assuming InnoDB engine you want primary key to be a continuously incrementing id so INSERT won't be random causing too much page split and fragment the tablespace.

Benefits of AUTO_INCREMENT:

Always incrementing
Providing fast insert rate

Downsides of AUTO_INCREMENT:

One extra column
You probably will never query on PK

Timestamp would be a great candidate for PK if you can keep it UNIQUE since it's most likely will participate in most of your queries and most like as a range. (PK is at the end of every secondary key).

You could also experiment with the an id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY instead. Use whichever gives better performance.

Size

Try to keep the row size as small as possible to avoid sharding/partitioning/archiving as long as possible (eventually you will need to but MySQL is quite good with tens of millions of rows even).

To achieve this use only IDs in this table and have lookup tables where necessary. For example: instead of action VARCHAR(12) use action_id TINYINT UNSIGNED. TINYINT is 1 byte whereas action will be always at least 2 assuming ASCII, latin1 or similar small character set but longer the string becomes the more byte it will use obviously. UTF8 can use up to 4 bytes / character.

Only put index on columns where you really need to. Daily, monthly, etc aggregation tables will serve you better than heavy indexes on this table.

A possible schema

CREATE TABLE my_action_log (
    ts TIMESTAMP(3) PRIMARY KEY,
    action_id TINYINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    post_id INT UNSIGNED NOT NULL,
    ...
    KEY ... # Whatever index you need
) ENGINE=InnoDB