Postgresql - Point in Time recovery using recovery.conf and trigger. How to minimize the time waiting for .history files Following http://www.postgresql.org/docs/9.1/static/continuous-archiving.html, I copy the WAL archives to another machine and apply them regularly. Recently due to pg server crash at my main server I tried to restore the database by pushing a trigger (an empty file, expecting smart failover) and the logs are given below.

prodrestore_error.log

WAL file not present yet. Checking for trigger file...
trigger file found: smart failover
Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 0000000100000001000000CC
WAL file path:        /backup/prod/db_backup/archive/0000000100000001000000CC
Restoring to:         pg_xlog/RECOVERYXLOG
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/0000000100000001000000CC" "pg_xlog/RECOVERYXLOG"
Keep archive history: 000000000000000000000000 and later
trigger file found: smart failover
running restore:      OK

Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 00000002.history
WAL file path:        /backup/prod/db_backup/archive/00000002.history
Restoring to:         pg_xlog/RECOVERYHISTORY
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/00000002.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history: 000000000000000000000000 and later
running restore:      cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
not restored
history file not found
Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 00000001.history
WAL file path:        /backup/prod/db_backup/archive/00000001.history
Restoring to:         pg_xlog/RECOVERYHISTORY
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/00000001.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history: 000000000000000000000000 and later
running restore:      cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
not restored
history file not found

postgresql-9.1-main.log

2015-08-03 08:08:44 IST::@:[5542]:LOG:  restored log file "0000000100000001000000CC" from archive
2015-08-03 08:11:44 IST::@:[5542]:LOG:  could not open file "pg_xlog/0000000100000001000000CD" (log file 1, segment 205): No such file or directory
2015-08-03 08:11:44 IST::@:[5542]:LOG:  redo done at 1/CC001E10
2015-08-03 08:11:44 IST::@:[5542]:LOG:  last completed transaction was at log time 2015-08-03 07:59:58.121908+05:30
2015-08-03 08:11:44 IST::@:[5542]:LOG:  restored log file "0000000100000001000000CC" from archive
2015-08-03 08:17:44 IST::@:[5542]:LOG:  selected new timeline ID: 2
2015-08-03 08:23:45 IST::@:[5542]:LOG:  archive recovery complete
2015-08-03 08:23:51 IST::@:[6088]:LOG:  autovacuum launcher started
2015-08-03 08:23:51 IST::@:[5541]:LOG:  database system is ready to accept connections

The whole process of looking for two history files 00000002.history and 00000001.history, 4 times and finally start the database took approximately 12 minutes. Then each repeat iteration by restoring a base backup fresh took more time as it started looking for more .history files.

How I can make this as fast to look for history files and if not present proceed quickly to start database in read/write mode ?  Else, not to look for history files at all.

That's not the fault of the history files.

Quoting from the documentation link you referenced:

  Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time (or as close as possible given the available WAL segments). Therefore, a normal recovery will end with a "file not found" message, the exact text of the error message depending upon your choice of restore_command. You may also see an error message at the start of recovery for a file named something like 00000001.history. This is also normal and does not indicate a problem in simple recovery situations; see Section 24.3.4 for discussion

Since it looks like you're using pg_standby, you have a few options.

Adjust -r maxretries and -s sleeptime.

By default -r maxretries is 3, and since it looks like you have the -s sleeptime setting set to 60, it will wait 60 seconds on the first retry, then 120 seconds on the second retry, then 180 seconds on the third and final retry of the copy command before it gives up and brings the replica server up into standalone mode.

So, in addition to replaying all the logs, at the end when the command is trying to copy the .history files, you're waiting a grand total of 360 seconds, or six minutes, plus the time it takes to restore all your WAL segments under smart mode.
Do a fast failover instead of a smart failover.

If you're desperate to fail over quickly, and can stand the loss of any unapplied WAL segments, you can do a fast failover instead of the smart failover you're triggering, as referenced in the pg_standby documentation here.

The merits of smart failover versus fast failover depend on how fast you want to get your replica server going and how much tolerance you have for lost transactions.

Fast failover ignores unapplied WAL segments and as such, the transactions are lost. Smart failover replays all the WAL segments it possibly can before bringing up the replica in standalone mode. This can take a long time depending on how many WAL segments you need to replay.

Finally, since you're on 9.1, you could use streaming replication along with a restore_command in your recovery.conf to keep the server even more up to date than the standard log shipping can, by streaming individual WAL records as they are produced.

Additionally, if the replica can't keep up with the streaming load (due to network issues or any number of things), it can alternately use a restore_command to grab WAL segments from the archive you already have set up.

You can't use pg_standby for the restore_command though.

In theory, since there is little if no delay restoring WAL records if all is going well, instead of restoring full WAL segments, your replica should come up in standalone mode much faster.

An example of streaming replication plus archiving in action can be found here:

http://evol-monkey.blogspot.com/2014/09/offsite-replication-problems-and-how-to.html

It is also useful to monitor your replication lag to make sure that everything is as up to date as your requirements dictate. Examples of that are here:

http://www.keithf4.com/monitoring_streaming_slave_lag/

Hope that helps. =)


DateTime value has additional hour after insert I developed a Windows Service (with a fixed culture of "de-DE") in C#. This service is getting data from an API and copies it over to a Navision Database.

My problem is following: After I've copied the data, every DateTime value got an additional hour. So if the DateTime value from API was 13:25:00, in the Navision database it is now 14:25:00.

When retrieving the DateTime value from API it is of kind "Unspecified".

To insert it into the Navision database I'm doing a simple
INSERT INTO [TABLE] (..., [Date_Time], ...) VALUES (..., [DATETIME VALUE], ...).

This just happens on customer side. In my test environment I can confirm that the same DateTime value from the source is inserted into the database.

I used the Database Creation Script provided by the customer to create the database in my test environment.

I cannot see all sources of failure, because I'm not yet so deep into this.

This is an application issue more than a database issue.

Navision treats datetime entries as UTC, and is adding the hour in its display (assuming your local timezone is UTC+1).

I have run into this myself since I work with Navision. If you have a client application, you can look at displayed datetime for a record and compare it to what you see by querying the table.


Scheduled backup task doesn't always backup all databases despite always saying job successful I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.

It quits with success every day but some days it quits with success only after backing up a few of databases. It can be different number of databases each time. Most days it successfully backups all databases but sometimes 2 backup successfully, sometimes 5, etc.

I don't see any errors in job history, event viewer or sql server log.

Backups are taking place to a local disk, although the folder is a "junction" to a folder on an expandable storage volume.

OS is Windows 2003 64bit running Sql Server 2008 web edition 64 bit as a virtual machine running on Vmware ESXi 5 host.

Stored Procedure:

ALTER PROCEDURE [dbo].[backup_all_databases]
@path VARCHAR(255)='c:\backups\'

AS

DECLARE @name VARCHAR(50) -- database name
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @dbIsReadOnly sql_variant -- is database read_only?
DECLARE @dbIsOffline sql_variant -- is database offline?

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
AND version > 0 AND version IS NOT NULL

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.bak'

SET @dbIsReadOnly = (SELECT DATABASEPROPERTY(@name, 'IsReadOnly')) -- 1 = Read Only
SET @dbIsOffline = (SELECT DATABASEPROPERTY(@name, 'IsOffline')) -- 1 = Offline

IF (@dbIsReadOnly = 0 OR @dbIsReadOnly IS NULL) AND @dbIsOffline =0
BEGIN
    BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
    WAITFOR DELAY '00:00:20'
END

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Any suggestions please?

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR FOR
    SELECT name, state, user_access
    FROM sys.databases
    WHERE name NOT IN ('tempdb')

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues

        FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH


MYSQL ways to represent '1970-1-1 0:0:0' Out of interest, are there any other ways of representing the timestamp '1970-1-1 0:0:0' other than from_unixtime(0)?

In PostgreSQL there is 'epoch'::timestamp. Would there be an equivalent in MySQL which is as concise?


How to return different statements in where clause ? (oracle) I mean:

CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER) RETURN NUMBER
BEGIN
SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1
WHERE COLUMN1<= Y AND (CASE WHEN ID < X THEN COLUMN1<COLUMN2 ELSE COLUMN2>COLUMN3 END),
AND COLUMN 3 = XYZ;

RETURN SOMETHING.

END;

I know case statement does not return an expression but a value. How can i use some kind of "conditional statements" or something to return expressions ? I use to do it easily in mysql with "IF conditional" but i don't know how to do it in Oracle 11g.
EDIT: Does it work the same, not only for where statemen but for select ?

Just use grouped WHERE conditions:

WHERE COLUMN1<= Y
   AND ((ID < X AND COLUMN1<COLUMN2)
        OR(ID >= X AND COLUMN2>COLUMN3))
AND COLUMN 3 = XYZ;

Remember you really just want to get some boolean evaluations back here - as long as you can reduce your requirements to distinct sets of conditions you can put it into WHERE which will be plenty fast.

The time to use CASE is if you have overlapping criteria that you need to short circuit.


Data replication between SQL Server 2016 and SQL Server 2008 R2 is valid? We have two servers with two SQL Server instance. we have plan to make first one SQL Server 2016 for production database and the second one SQL Sever 2008 R2 for the same database as reporting side.

Can we create a replication solution between SQL Server 2016 & 2008 R2? If not: is there another solution to migrate updated data between these two instances?

I'm afraid Replication between 2016 and 2008 is deprecated per Microsoft.
MSDN Link

  Replication is supported if each SQL Server endpoint is within two
  major versions of the current version of SQL Server. Consequently, SQL
  Server 2016 does not support replication to or from SQL Server 2008 or
  SQL Server 2008 R2.


Hierarchical structure with limited number of records per user I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem.
Any user can refer only 3 other users at max.
So for example I have this relation of users:

CREATE TABLE users
(
  id serial NOT NULL,
  referred_by integer NOT NULL,
  created_at time without time zone NOT NULL,
  updated_at time without time zone NOT NULL,
  CONSTRAINT primary_key PRIMARY KEY (id),
  CONSTRAINT referred_foreign FOREIGN KEY (referred_by)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

I can use connectby function to query any hierarchy. But when it comes to inserting related models I have to check if a user has not already referred 3 other users before inserting any new user under him. If he already have 3 users then it need to cascade down the tree and put it as a leaf node of tree which is further a heavy query to run on larger hierarchies.

For cascading new users to leaf node Is it possible to determine the nearest node where new user can be put ? for example If this is the data in table:

                    ____________ A ___________
                   /             |            \
                __ B __       __ C __     ____ D ____
               /   |   \     /   |   \   /     |     \
              E    F    G    H   I   J   K     L

And A refers one more user, Now, since A's downline is fully saturated any new user under him should cascade down the tree, and there are 9 places where the new user can be put.

E    F    G    H    I    J    K    L    D

out of which the nearest node is D, so incoming node should go under D instead of E,F..K,L

I have 3 questions:

Most important, Is it possible to restrict the number of records under each user ? I dont want to rely on triggers to check for number of records and cascade down if required. I can change the design if necessary.
How can I 'efficiently' fetch the nearest leaf node of any tree after determining that the root node is saturated ? By efficiently I mean that the same query might need to run a couple hundred times per minute and the data set has no depth limit. Again, I am open for design suggestions if any.
Is it possible to create partial index for referred_foreign so that the first user who will not be referred by anyone can still satisfy the foreign integrity ?

Is there any better design or something that I am missing ? (I know I am missing a lot just dont know what it is)

The following requirement can be easily implemented with a constraint: any user can refer only 3 other users at max.

    CREATE TABLE users
    (
      id serial NOT NULL,
      reference_number SMALLINT NOT NULL,
      CONSTRAINT CHK_reference_number CHECK(reference_number BETWEEN 1 AND 3),
      CONSTRAINT UNQ_reference_number UNIQUE(id, reference_number),
      referred_by integer NOT NULL,
(snip)

Note that your design does not prevent cycles. We can easily enforce that via constraints as well. I can elaborate if you are interested.

Regarding the efficiency of finding qualifying descendants, we can add some redundant data and get much better speed. For example, E is a descendant of A, but not a direct one - B is between them. We can store the following rows:

Ancestor: A
AncestorLevel: 1
Descendant: B
DescendantLevel: 2

Ancestor: B
AncestorLevel: 2
Descendant: E
DescendantLevel: 3

Ancestor: A
AncestorLevel: 1
Descendant: E
DescendantLevel: 3

Once we have this redundant data, finding descendants is easy and fast - just one simple query without recursion. Naturally, with this approach we need substantially more storage.

Of course, with redundant data there is always the risk that it is inconsistent. We can use constraints to enforce the integrity of redundant data. This is complex but doable.


Single MySQL DB contains both push and pull data. Recommended path to replication? We have a single deployed (MySQL) database that contains both user tables and product tables (currently in the same DB).

The product tables are "pushed to production" data -- they are generated in our internal development environment and need to make their way to the production database. We'd like to set up replication because our product DB is growing quite large (>40M items). Its no longer feasible to push snapshots of this data - we want incremental updates.
The user tables are "pulled from production" data -- they generated in our production system and copies of this data need to make their way back into our development. Our devs want near real-time analytics and don't want to run these queries on the production environment.
There are (currently) some join queries between these set of tables (e.g. lookup user's cart product). However its not particularly problematic separate these as separate queries to independent stores.

I'd like to set up replication of both sets of data. Pull the user data, push the product data.

What would be the best practice to set up this kind of bi-directional way replication?

Is it possible to setup replication on a table basis? Or should the data be split into separate databases, or even separate servers?

You may set up a Master-Master-Replication in which both servers are slave of each other and one can write to both servers.

If you always make changes to just one subset of the tables on each of the servers, you do not run into problems concerning auto increment values (you might want to set auto_increment_increment and auto_increment_offset anyhow) or simultaneous writes to the same data on different servers.

But please note that a Master-Master-Replication can result in severe chaos, if one does something wrong.

Actually I do not understand why you do not fill the product tables in the production directly and not in the development database? You then could use one single master for writes and just keep a slave for backups and statistics / analytics, which want current data, but do not need the latest at all time. Makes configuration, maintenance and failure resistance a lot easier.
That is the solution I would prefer in this case.


Delete rows on a table with cascading foreign keys I want to delete rows on Table A but it has foreign keys on Table B and Table D. And Table B has foreign keys in Table E and Table D has foreign key in table F. Can I delete all the cascading records from Table A to F in a simple script without making multiple delete scripts per table? In reality, Table A has around 20 table referencing to it.
Table A

Table B

Table E

Table C
Table D

Table F

PostgreSQL version: 9.2.24

First, it depends on how your foreign keys are declared. Assuming tables like:
CREATE TABLE parent
( pid ... not null primary key
, ...
);

CREATE TABLE child
( ...
, pid ... not null
    references parent (pid)
        on delete <action>
        on update ...
...
);

action can be any of:

NO ACTION
Produce an error indicating that the deletion or update would create a foreign
key constraint violation. If the constraint is deferred, this error will be
produced at constraint check time if there still exist any referencing rows.
This is the default action.

RESTRICT
Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

CASCADE
Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.

SET NULL
Set the referencing column(s) to null.

SET DEFAULT
Set the referencing column(s) to their default values.

See https://www.postgresql.org/docs/9.2/sql-createtable.html
If your foreign keys are declared as "on delete cascade" it is - in theory - sufficient to delete the root node. In practice, there may be physical limitations that restrict the total number of rows that can be deleted in one transaction.
If you want to experiment with the different actions you can use Fiddle. 9.5 is the oldest one available. If you are still on 9.2, consider upgrading to something more modern.


Availability Group how to determine last Failover time I'd just like to know if there is any way to query when the AG group failed over.

eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday?  How can i find when the failover took place

is there something specific in the Logs I should be looking for, or is there a tsql script to use?

You can use following PowerShell script to get last failover time

    Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap


How is IF NOT EXISTS SELECT THEN INSERT faster than UNIQUE index? In SQL Server how is...

Sp:

CREATE PROCEDURE insertToTable
    @field1 VARCHAR(256), @field2 varchar(256), @field3 varchar(256)
AS
BEGIN
    SET NOCOUNT ON

    IF NOT EXISTS (SELECT * FROM my_table WHERE field1 = @field1)
      INSERT INTO my_table
        (field1, field2, field3)
      VALUES (@field1, @field2, @field3);
    ELSE
      THROW 50000, 'xxxxxx', 1;
    END
GO

Table:

CREATE TABLE my_table (
    field1 VARCHAR(256) NOT NULL,
    field2 VARCHAR(256) NOT NULL,
    field3 VARCHAR(256) NOT NULL
);
CREATE INDEX idx_field1 ON my_table(field1);

the above faster than the below?

Sp:

CREATE PROCEDURE insertToTable
    @field1 VARCHAR(256), @field2 varchar(256), @field3 varchar(256)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO my_table
        (field1, field2, field3)
    VALUES (@field1, @field2, @field3);
GO

Table:

CREATE TABLE my_table (
    field1 VARCHAR(256) NOT NULL,
    field2 VARCHAR(256) NOT NULL,
    field3 VARCHAR(256) NOT NULL
);
CREATE UNIQUE INDEX idx_field1 ON my_table(field1);

Sample input:

field1: F56yCgZ9AEm9aFpTyjwhERtqNeglYEow

field2: BD84CE2A514316164B7448C804B178AD8F6F597E8EC6F25F4D6E36287259C65F67E7206E82A4F8EFD2389C0821C0C70E8278DC5F166D220356B5A15A091A6C17$0

field3: A18E9049117A77E6A4D41C6CA3FFDEA65D842BF1F57705405B4E66969531D93D

The input is generated on the fly by the web application and using prepared statements. I use Jmeter to generate requests to my web app.

With UNIQUE index, insert performance degrades after 100K inserts and gets worse.

With NON UNIQUE index and a manual check with IF NOT EXISTS SELECT, performance is constant even with millions of records inserted.

The values are unique enough that there never is a duplicate generated. Even after a few million values inserted.

FINAL UPDATE:

It's the INSERT that's really slowing things down.

When a Unique index is in place, with every new record you add SQL has to check if the value already exists. As the table grows, the amount of cross-referencing increases. A non-unique index will require no cross-referencing so performance is constant.

Unique indexes are usually faster for SELECT statements but that comes at a cost when updating a table.

Below is why the SELECT can sometimes be slower on a Unique Index

I've partially recreated your situation to the point where I think it is down to combination of Parameter sniffing and SQL prefers using the NON-UNIQUE index on a HEAP.

Set up 2 test tables, one of them is a heap (just like your table).

CREATE TABLE dbo.TEST1(ID VARCHAR(255) NOT NULL,TXT1 VARCHAR(255) NOT NULL,TXT2 VARCHAR(255) NOT NULL)
CREATE TABLE dbo.TEST2(ID VARCHAR(255) NOT NULL,TXT1 VARCHAR(255) NOT NULL,TXT2 VARCHAR(255) NOT NULL)
GO
INSERT INTO dbo.TEST1 VALUES(NEWID(),NEWID(),NEWID())
GO 30000

INSERT INTO dbo.TEST2
SELECT * FROM dbo.TEST1
GO

CREATE CLUSTERED INDEX cidx ON dbo.TEST1 (ID)
CREATE INDEX idx_nu ON dbo.TEST1 (ID)
CREATE UNIQUE INDEX idx_u ON dbo.TEST1 (ID)

CREATE INDEX idx_nu ON dbo.TEST2 (ID)
CREATE UNIQUE INDEX idx_u ON dbo.TEST2 (ID)

Review the footprint of the Indexes, on the HEAP the UNIQUE index has a smaller footprint than the NON-UNIQUE index. (perhaps the pages of the NON-UNIQUE index contain extra - possibly useful - information)(note: after running the above code multiple times the page count doesn't differ, probably due to caching, alter the "GO 30000" to rectify the issue.)

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    p.row_count,
    SUM (p.used_page_count) as used_pages_count,
    SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM
    sys.dm_db_partition_stats  AS p
        JOIN sys.tables AS t
            ON
            p.object_id = t.object_id
        JOIN sys.indexes AS i
            ON
            i.[object_id] = t.[object_id]
            AND
            p.index_id = i.index_id
        JOIN sys.schemas AS s
            ON
            t.schema_id = s.schema_id
WHERE
    t.name IN ('TEST1','TEST2')
GROUP BY
    s.name
    ,t.name
    ,i.name
    ,p.row_count

Now query the tables with literals and variables.

--SCAN of the UNIQUE index
DECLARE @account_id VARCHAR(255) = (SELECT TOP 1 ID FROM dbo.TEST2 WHERE ID like '%A%')

--Parameter Sniffing kicks in --The optimiser doesn't know the value of @account_id

--SEEK of the CLUSTERED index
DECLARE @ID1 VARCHAR(255)  = (SELECT TOP 1 ID FROM dbo.TEST1 WHERE ID = @account_id)

--SEEK of the NON UNIQUE index
DECLARE @ID2 VARCHAR(255)  = (SELECT TOP 1 ID FROM dbo.TEST2 WHERE ID = @account_id)

For some reason SQL prefers the NON UNIQUE index on a HEAP when performing SEEK operations.

Here's what I think is going on. When the Non-Unique index has more Pages then the corresponding Histogram in the STATS has more STEPS, run below code.

DBCC SHOW_STATISTICS ( 'TEST2' , 'idx_nu' )
DBCC SHOW_STATISTICS ( 'TEST2' , 'idx_u' )

The additional STEPS create a more granular view of the underlying Index, so the Optimiser (knowing that the EQ_ROWS is always 1) is getting a better Cardinality Estimate from the Non-Unique index.


will a record set derived from jsonb_to_recordset always maintain the order of the items in a JSON array I want to be able to retrieve a jsonb array of objects as a record set. Maintaining the order of objects is critical. Will jsonb_to_recordset always return the record set in the order of the array of objects?

Here's another solution using another of Postgres' json functions: jsonb_array_elements.

  SELECT id, (value->>'benefit')::varchar as benefit, ordinality as rank
  FROM products
  CROSS JOIN LATERAL jsonb_array_elements(benefits) with ordinality

"with ordinality" gives you the order of each array item and allows you to do an explicit order by on that field to ensure the order remains the same.

This stackoverflow post was very helpful


SQL Server 2008R2 - Why is my index not used I have a table defined in the following way:

CREATE TABLE [dbo].[MyTable]
(
   [MyTable_ID] [int] IDENTITY(1,1) NOT NULL,
   [COLUMN_WITH_DATA] [varchar](128) NOT NULL,
   [COLUMN_A] [varchar](128) NULL,
   [COLUMN_B] [varchar](128) NULL,
   [COLUMN_C] [bit] NOT NULL
)

And an index created like that:

CREATE INDEX [MyTable_Index_ABC] ON [dbo].[MyTable]
(
    [COLUMN_A],
    [COLUMN_B],
    [COLUMN_C]
)
ON [PRIMARY]

And I run the following query:

SELECT TOP 1 [MyTable].*
    FROM [MyTable](UPDLOCK)
    WHERE
        [MyTable].[COLUMN_A] IS NULL
    AND [MyTable].[COLUMN_B] IS NULL
    AND [MyTable].[COLUMN_C] = 0

The goal behind this is to get COLUMN_WITH_DATA vaule of first not yet used record, and then update its' COLUMN_A and COLUMN_B with not null values, to mark it as consumed.

I have a couple of millions of records in MyTable, and about half of them has both:
COULMN_A and COLIMN_B that are not NULL (indicating the data was already consumed).

In this situation the query runs really slow, and the execution plan shows that the index:

MyTable_Index_ABC

is not used unless I use a query hint, in which case the query runs much faster.
On the other hand if I update all not yet consumed rows, that is having:

COLUMN_A IS NULL AND COLUMN_B IS NULL

so that COLUMN_A and COLUMN_B contain empty string: '' instead of NULL, the index is used and the query runs much faster again.

Two questions would be:

why null values make my index discarded
is it possible to instruct the database to always use the index without having to use query hints on possibly multiple different queries
??

Thanks,

I like providing direct answers to questions; however, this topic can go deeper and longer, therefore I am adding a few articles at the bottom that expand on these details for all to learn from.

In summary ...

A few things that can affect using an index (mind you there are a lot of reasons too as you'll see in the articles posted below). The main priority of the engine is to predict how to get the data off the disk as fast as possible in the most effective/efficient way (use of statistics). What becomes the primary choice of the engine is to perform a table scan or index seek/scan. (There can be more depth to this conversation, but I will keep it to the context of your question).

Use less fields in the SELECT that are more cohesive with the INDEX KEY and INDEX INCLUDES.
More rows in the table will associate with the use of indexes
The more the rows are selective (unique) the more likely the use of indexes, thus many NULL values will cause the engine to avoid the index

In detail ...

What are some causes a table scan or an index scan/seek is used. These are also general helpers to determine index creation and use for most general situations. Follow these few steps and you will achieve big benefits right from the get-go.

One reason, as it is mentioned in the comments above, is the use of SELECT * FROM WHERE . SELECT * is a sure-fire way for the engine to decide to avoid using indexes. It's faster to get all fields from the table (Clustered Index/Heap) by scanning/retrieving from the table itself, bypassing any indexes. First human choice is to minimize your select fields to those in the INDEX KEY and in the INDEX INCLUDES.

The second reason is to how many records are in the table. The fewer the records, the easier it is for the engine to simply scan the table. The statistics can have a part in this. Because the engine will use the statistics to predict where the data is on the pages/disk, it can be said that there is not enough rows/distribution to use the index.

And thirdly, having values in your table that are less selective (less unique), like Male and Female, the less likely the index will be used. A table that will use indexes more in queries will be for fields that are highly selective (more unique), like zip codes in an address table (so long as your list of address are NOT all of your neighbors with the same zip code).

There are many techniques and strategies. But one piece of advice, become knowledgeable in reading and experience with indexes/statistics and how/where to put them on DISKS/LUNS and you'll go far as a DBA and offering huge performance gains.

Understanding SQL Server Statistics
Stairway to SQL Server Indexes


Bulk changing SSRS schedules I have around 100 SQL Server Reporting Services subscriptions that I need to change the schedule on.  The schedules are all standalone, not shared.

Is there a way that I can easily bulk change the report schedules?

Moving them all to handful of common shared schedules would also be acceptable, but again I'd rather not have to do it manually.

I've been experimenting with Powershell to talk to the Reporting Services web service, but it seems far from straightforward to edit schedules, so I'm hoping that there's an easier way.

This is an old question that I actually landed on in my quest trying to accomplish the same task recently.  I wanted to follow up with my solution in case someone else lands here as well.

I actually wrote a few functions in powershell to get the job done.
You can see the code and process here


DB2 Transaction Logs - can they be COPY-PASTE on another Disaster Recovery Server? We have Server1 with Online Backup1, Archive Logs 1,2,3 for example and Transaction Logs from  1 to 10.
We are interested in next scenario:
If Server1 fails completely and we want to move everything on second server I suppose it is not the problem to move and restore Backup1 and Archive logs 1,2,3 on Server2.
But can Transaction Logs (their snapshot in some specific time) be also copied to the Server2? Because Transaction Logs are probably the most problematic part for this Disaster Recovery Scenario. Can there be some problem with the fact that transaction logs are written and populated all the time?

ADDITION: DISK option is used when configuring Archive Mode

Perhaps you wish to look into HADR on DB2. It actually does log shipping to the standby, so if there is a fail over you can either manually take over on the standby, or if you install TSA (Tivoli Systems Automation), then you can have it detect a failure and automatically fail over to the standby.

If you don't want HADR, then yes, you could copy logs. Or even use the alternate/mirror log/archive parameters to get DB2 to copy the logs (possibly to a shared mount).


Would a NoSQL solution be relevant for a real-time mobile game? Developing an  iOS/Android/WP real time game.
Server side stuff will be done in Javascript using Node.js.
It will be a trivia with 8-players rooms + an admin site made in Python with Flask (for stats, account management, adding questions... It will also provide an API to get user profiles).

Basically I will have to fetch around ~10 questions/game (I will be checking if users already answered them or not so I can avoid redundancies too).
I was wondering if my project would really benefit from a nosql db.
I have never used one but wouldn't mind learning if it is relevant.

Player data : classic account infos, score, answered questions...
Questions data : questions + translation, answers, stats (times asked, correct/wrong answers count)...

RDMS choice would be psql.

Any thoughts?
Thank you

The short answer is that NoSQL, NewSQL, OODB or just plain SQL are all options, but SQL is probably the most viable.  Many NoSQL implementations can be configured for ACID compliance at the cost of performance/scalability, but they will most likely still scale beyond most reasonable SQL implementations.  Another option is to use application sharded SQL to get scalable SQL with minimal effort/cost, and in many cases is very viable. Generally the architecture and testing is more critical than the data store, the most scalable data store may fall flat on its rear with the wrong architecture.

For a startup, getting the product out is often the primary consideration, and SQL is generally the quickest to market due to it being a known entity and experienced developers have the most experience with it.  The biggest problem is usually your available resources, do they have the knowledge and experience with NoSQL? Or do you have the time to test, configure, test again?

pSQL is relatively known, its limitations are known.  NoSQL has 10s of viable options, and 100s in some state of development.  Finding the right one and understanding its limitations is a pretty significant project by itself.  NewSQL is very new, in 3-5 years it may very well be the defacto DB for large scale projects.  Any small shop should probably stay away until it gets more significant production usage by larger companies that have the resources to diagnose and work around the inevitable problems.


Retrieve Indices of Associative Array as Collection In PL/SQL, suppose I have some associative array defined as follows:

declare
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       a_arr a_arr_t;

I then, sparsely, populate the array as follows:

begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;
end;

Is there some operator or function that can give me the indices of the array, (1,10,100) as a collection of some sort, something like indices of in a forall statement?

Is there some operator or function that can give me the indices of the array

No, you have to loop through the associative array:

declare
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       type keys_t is table of PLS_INTEGER;
       a_arr a_arr_t;
       keys_ keys_t := keys_t();
       l_index integer;
begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;

      l_index := a_arr.first;
      while (l_index is not null)
      loop
          keys_.extend();
          keys_(keys_.count):=l_index;
          dbms_output.put_line(keys_(keys_.count));
          l_index := a_arr.next(l_index);
      end loop;
end;
/

/*
1
10
100
*/


psql, getting a list of databases to be parsed by a script I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them:

psql -l -A -t

but the output shows an obvious issue: the records are separated by newlines, but also contain newlines.

$ psql -l -A -t
postgres|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|
template0|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres
postgres=CTc/postgres
template1|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres
postgres=CTc/postgres

Using the -R option I can change the record separator, but it seems like no matter what I change it to, there's the risk of that string appearing in the data. Is it possible to instead tell psql to replace the newlines in the data with something else? (and then what if that string also appears in the data?)

I'd also tried to set the record separator to a null character with such sequences as -R '\000' and -R "\0", but it doesn't seem to interpret escape sequences in the parameter at all, and just uses the literal string \000 instead.

The other option I know of to list all databases is:

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

but that requires me to give the password for the postgres user, so it's not desirable. Perhaps there's another way to get a list of the names of all databases?

I don't see how your two solutions are different...  Since it still has to actually CONNECT to a database instance to see what databases are there, I'm willing to bet the only different is HOW you are connecting.

psql -l -A -t

vs

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

Try instead

psql -q -A -t -c "SELECT datname FROM pg_database"

This should use the default connection settings (database and login as local account, local connection, 5432 port).  If you really need to, then specify the user and database with -U postgres -d postgres .. but otherwise just leave the host and port unspecified.

I'm willing to bet the only reason psql -l -A -t isn't asking for a password is because you have that information in your .pgpass file.


MySQL InnoDB transactions with lot of delete instructions We have a process that runs frequently and does the following steps on a mysql database of type innodb.

SET autocommit = 0;
DELETE FROM table WHERE category='something';
-- every 300 rows or whatever is left in case of last round
INSERT IGNORE INTO table (fields) VALUES (row1),(row2),..,(row300);
    -- check affected rows == number of rows given if not, ROLLBACK;
-- at end of all rows
COMMIT;

This process for some reason seems to expand the ibd file into way more than the actual size. Previously we had one huge ibdata1 that went to about 60gb size, but then we dropped everything, changed the my.cnf to store data into separate files for each table, and still the table on which these transactions are happening just blew up again.

Is there some way to see what the ibd file consists of? Or if we could name the transaction to say transaction_2014_06_30 and then at the end of the erase all information related to this transaction from the idb file?

Also, will using a temporary table help in this process, because I've read that temporary tables use a different ibtmp1 file to store data and are actually lot faster in the recent versions of mysql.

this file ibdata1 also known as system tablespace, which consistent of undo log even if you have seperated tables into different files;

from your process I found it is a really huge transaction, which will produce drampatic quantitiy of undo logs, till MySQL 5.5, undo log space can not be shrink or adjust automatically or manually.

so there is a simple way to prevent you from the huge undo log:

commit your transaction every 300 delete statements.

each next transaction will recycle the undo log space.


SQL Server 2016 full backup and log shipping and newbie questions As most likely in a lot of places, I find myself in the roll of "DBA" by default rather than by skill.

I currently am setting up new SQL Servers using SQL Server 2016 Standard Edition. I have been able to successfully set up the log shipping agents and schedules to where it took a full back up at start, and now does log shipping every minute, and does a restore on the secondary every 5 minutes. I have verified its working as expected by altering the primary database and, after the 5 minute boundary has passed, I can see the changes in the secondary database.

First question I have is, once the restore on the secondary has been done, can the transaction log files be deleted?

Next question is, if I wanted to combine the transaction log shipping with a full/differential backup plan, is there anything out there that explains better about how to set this up. I have read multiple articles, but each seems to be missing one component or the other and its making it difficult for me to understand how these differential backup components work together.

once the restore on the secondary has been done, can the trn files be
  deleted?

Yes you can but you want to consider keeping files for certain period of time depending on your recovery point objective.

  if I wanted to combine the transaction log shipping with a
  full/differential backup plan

Full and differential backup will not break your log chain.

Make sure you do not take any log backup outside log shipping. All log backups must be done through the log shipping. Because that will break your log chain and hence log shipping will fail.


One Materialized View in Two Refresh Groups I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to create the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".

You can have a materialized view in only one refresh group?

What options do I have to refresh it in different intervals?

I believe that such query will return the next run date you require:

select least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'),
  next_day(sysdate, 'Sunday')) as answer from dual;

It calculates dates of next Sunday and 1st of next month and then returns the one which will be sooner.

Just 'Sunday' in next_day is NLS dependent.

That should give your required interval in call to DBMS_REFRESH.MAKE procedure:

interval=>'least(TRUNC(ADD_MONTHS(SYSDATE,1), 'MONTH'), next_day(sysdate, 'Sunday'))'


Guideline for MySQL free space What is the guideline for how much free space to keep available on a MySQL AWS RDS instance (SSD)? In the past for an on-premises SQL Server install we used to use something like 2x the largest table in the database as this would provide enough space for maintenance tasks etc. and plenty of reaction time for unexpected growth. Are there any such guidelines for a MySQL RDS install? (We were recently caught running far too lean and a few poorly constructed queries led to an insufficient space message).

A really poorly constructed query can take a huge amount of space -- can't really protect from that.

Your maintenance?  Or AWS's maintenance?

If you can 'quickly' upgrade to a bigger instance, would that be an acceptable fix for nearly running out of space?

Would you like a review of the schema for the largest table?  Maybe some datatypes could be shrunk.  Maybe some normalization is feasible.  Maybe unhexing uuids, etc, is advisable.  Etc.

Otherwise, stick with your old rule of thumb.


Syncing Database to Application (polling) I have created a windows application that uses a MS SQL 2008 database server.

There are two types of the application:

1) Client Application
2) Host Application

The client application is able to send commands to the database that the host application polls and performs the command,
e.g. restart host server, restart host appliation, host check for application updates, host show debugs etc.

Once the host application picks up the command from the database the command is removed. The client application has a command task list that shows the outstanding tasks. Currently to keep this task list up-to-date (e.g. to remove tasks that have been completed) I have to query/poll the database every 3 seconds to get a refreshed dataset for the client application.

Is there a better way to poll the database, I am not sure querying the databse every 3 seconds is a good idea even if the query is very small.

Is there a way for the database to tell the application to refresh if a table is updated?

Thanks, Greg

Polling might be an appropriate design for what you're doing here. It lets you control and tell the user exactly how fresh/stale the state is though 3 seconds is probably too frequent. 15 seconds seems to be a common threshold used by a lot of commercial applications and with Microsoft's tools (E.g. performance monitor). Some of SQL Server's system threads waked up every 15 seconds also.

That said, you can consider using query notifications (https://technet.microsoft.com/en-us/library/ms175110(v=sql.100).aspx) if you want a way to notify the app when the data you care about has changed. Essentially, you tell SQL Server the query you want to track and when the results for that query changes, your app gets a message. It is a bit of work to implement but the concept and coding are relatively simple.


Problem to access the SQL Server I have SQL Server 2005 which is installed on server machine.

I was working fine on SQL server before I change the server machine IP. I changed server machine IP to host the Web Service on it.

But now when I enter the Login and Password information SQL Server displays this error message:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

I also tried to remove Server machine IP, Still I can't able to access the SQL Server.
My all database stucked in it. Or can I recover my SQL server database?

Make sure SQL Server Service is running
If a named instance, make sure SQL Server browser service is running
Make sure SQL Server is configured to allow remote connections
Examine the SQL Server error log for messages confirming that SQL is listening on the  expected network interfaces and ports
Test server connectivity with PING from the client machine
Test port connectivity using TELNET or PowerShell to the server and port (from step 4) from the client machine.  For example

a. TELNET  1433

b. PowerShell:

1433 | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }

Check firewall settings if step 5 or 6 connectivity test fails

For further read below forum discussing the same which would be helpful to you for troubleshooting :

https://stackoverflow.com/questions/18060667/cannot-connect-to-server-a-network-related-or-instance-specific-error


Can I restore a database, from the same version SQL server, with different updates? The main server ( the one I backed up the database ) is :

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on
Windows NT 6.3  (Build 9600: )
(Hypervisor)

I'm trying to restore the database in this server:

Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)
May 27 2016 15:06:08  Copyright (c) Microsoft Corporation     Enterprise
Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build
10586: )

But the restore never completes. using SP_WHOISACTIVE the complete_percentage is null ( for like, 10 minutes ).
In my own machine, I could restore the database, this is my server:

Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64)
May 27 2016 15:33:17    Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
 (Build 10586: ) (Hypervisor)

What can cause this problem? the database is 140GB. Is there a place where I should verify?
With verifyonly the file is valid.
I'm using T-SQL to the restore:
restore database Database
    from disk ='F:\Database.BKP'
        with
            move 'Database' to 'path\database.mdf',
            move 'Database2' to 'path\database.ndf',
            move 'Database3' to 'path\database_FULLTEXT',
            move 'Databaselog' to 'path\database_log',stats=1

One thing that I noticed was the wait type Async_io_
Is there a way to verify if the disk has a problem, but via SQL SERVER?
Oh, and when I pause the script, I got this message:

Msg 3122, Level 16, State 1, Line 4 File initialization failed.

RESTORE cannot continue. Msg 3204, Level 16, State 1, Line 4 The

backup or restore was aborted. Msg 3013, Level 16, State 1, Line 4

RESTORE DATABASE is terminating abnormally. Query was cancelled by

user.

But the restore never completes. using SP_WHOISACTIVE the complete_percentage is null ( for like, 10 minutes ).

10 minutes is not nearly enough time to qualify for using the word "never"!

  What can cause this problem? the database is 140GB.

If you don't have instant file initialisation (see here and here amongst other places) turned on then it will be trying to zero 140Gb+ of disk space ready to restore the data - this could easily take longer than ten minutes[*] and during that part of the process the percent done will not count (this only counts as the pages are restored from the backup file to the data files, not while the data files are being created).

  [*]: ~14Gb in ~10 minutes is about 240MByte/second - how fast can your I/O subsystem write and what other activity is going on at the same time?

To answer the question in the title:

  Can I restore a database, from the same version SQL server, with different updates?

Yes, you should be able to, even if the updates that differ are service packs, though I generally recommended that you only restore upwards where possible just in case.


sql server returns 0 in the output To getting the disk space details I am using the following query.

E.g.
SELECT distinct(volume_mount_point),
 total_bytes/1048576 as Size_in_MB,
 available_bytes/1048576 as Free_in_MB,
 (total_bytes/1048576)-(available_bytes/1048576) as Used_in_MB,
 (((total_bytes/1048576)-(available_bytes/1048576))/(total_bytes/1048576))*100 as Used_in_Percentage
FROM sys.master_files AS f CROSS APPLY
 sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
 available_bytes/1048576 order by 1

But the above query returns '0' in percentage column.How to fix this error?

Change some of the 1048576's to 1048576.0.

SELECT distinct(volume_mount_point),
 total_bytes/1048576 as Size_in_MB,
 available_bytes/1048576.0 as Free_in_MB,
 (total_bytes/1048576)-(available_bytes/1048576.0) as Used_in_MB,
 (((total_bytes/1048576)-(available_bytes/1048576.0))/(total_bytes/1048576))*100 as Used_in_Percentage
FROM sys.master_files AS f CROSS APPLY
 sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
 available_bytes/1048576.0 order by 1

It's an awful query though.


What is the best way to store optional values in table? I have a large table that will be partitioned (about 300 million rows). There are currently 57 columns in the table. At the moment we also have an 'Option' column, which is to hold values that would be null for most records. The column type is varchar (max). In other words, this column is to store values that are certain business characteristics of the row. In the extreme case, when all these values occur, the length of this column will exceed 8000 characters. There may also be 0 characters.
There will be around 300 inserts per second and around 200-250 updates on this table (sometimes update which is also updating Option column (for example add new value).
The question is whether it is a better approach to create new columns for these values (then need to create an additional 63 columns) that would mostly be null for a single row. Or is it just a better idea to keep everything in varchar (max)?

Per your comment:

There will be around 300 inserts per second and around 200-250 updates
on this table (sometimes update which is also updating Option column
(for example add new value).

This leaves us with two options:

Store the optional columns in their own table(s) and create a view to stick everything together.  In this the PK of the optional table(s) will be the primary key of the main table, migrated through a FK constraint.
Just deal with the 1 bit penalty per column for the other rows.  Depends on how often the optional 63 columns are populated versus the other rows and what the actual differential in storage would be.

With respect to explicit columns vs VARCHAR(MAX), it is definitely worth defining/creating the columns, if for nothing else the sanity of your end users.  You can certainly group related columns together (those that are/will be NULL together) in their own tables if the data size is relatively large, or just deal with having one (potentially) wide table with very few records in it.
Do not use EAV.  There are plenty of SO questions and articles you can find outlining why and I won't repeat them here.

Ok so the option with VARCHAR(max) is the worst option ? Why new
partitioned table with only optional columns will be better solution ?

I'd say the worst option would still be EAV, but using a VARCHAR column to store multiple values violates 1st normal form (which exists for a very good reason), but from a practical standpoint here are the drawbacks:

Can't ensure the datatypes are consistent for the data stored
Trying to make sense of the data requires someone knows how the data is encoded, then they have to try and extract the data.  Things like JSON and XML can reduce that somewhat, but they have storage overhead and are still not as easy to work with as just writing a simple SELECT statement.

Relevant example from earlier in my career:
There was a need to store derived characteristics for each customer to be calculate a price.  These varied based on what price was being determined and there were over 500 characteristics total.  Someone "solved" the problem by concatenating all of the characteristics into one string and loaded that into a VARCHAR column.
This had the following disadvantages:

There had to be a huge amount of effort put into ensuring the resulting string was valid, complicating business rules and making changes difficult (a lot of testing since changing one thing could break another)
Getting the data parsed was intensive and required operations that couldn't be performed easily in the database.  So there was a whole other program that would pull the data out, parse the 500 some odd columns, then return it.  Slow, and the typical developer/user had no clue how anything worked.  And if you only wanted "Characteristic 26B" or whatever, you had to process every row, whether or not "26B" made any sense at all for that particular row.

Currently, I deal with a lot of aggregated data that is ridiculously wide (usually 100+ columns, most NULLable).  Given the type of workload (ad hoc queries selecting anywhere between 5-20% of the columns), I will usually create a table for each NULLable column or subject area, then combine everything into a single view.  SQL Server is great at join elimination, so we've been able to cut the amount of data read for a given query substantially. This results in faster performance and we don't have to rebuild the table every time a new data element is added (just create a new table, load the data, then alter the view).
If your situation was closer to that of a DWH (a lot of inserts and relatively few updates, a lot of ad-hoc queries), then Clustered Columnstore Index would be a good option, but that doesn't seem to be the case here.


You want a pivot table (which isn't a inbuilt function in MySQL, but there are workarounds)

A rough example (I cant test this here):

select ID
   sum(if( sec = 'A', 1, 0)
       * points) as Sec A,
   sum(if( sec = 'O', 1, 0)
       * points) as Sec O,
   sum(if( sec = 'U', 1, 0)
       * points) as Sec U,
   sum(if( sec = 'B', 1, 0)
       * points) as Sec B,
   sum(if( sec = 'S', 1, 0)
       * points) as Sec S,
   sum(if( sec = 'T', 1, 0)
       * points) as Sec T,
   sum(if( sec = 'Y', 1, 0)
       * points) as Sec Y,
   sum(if( sec = 'Z', 1, 0)
       * points) as Sec Z
  from [schema].[table]
 group by id
;

NOTE: I've put section Y and Z as your additional sections as they don't appear in your example with what they are

If that doesn't work directly here's some external links with more detailed examples:

https://www.toadworld.com/platforms/mysql/w/wiki/6349.pivoting
http://modern-sql.com/use-case/pivot


Database restore on new server and upgrade to SQL Server 2012 I have to move a 5 TB SQL Server 2008 database that has mirroring setup to a new server that will have SQL Server 2012 and always on AG setup.

I am wondering if detach/attach would be faster or backup/restore. The compressed split backup is about 1.2 TB.

I understand that detach/attach means database will not be available on the source.

Any advise on matters to consider, as this will be a first SQL Server upgrade for me.

Thanks as always to the DBA community.

First you need to run upgrade advisor to be able to confirm that there won't be any errors during the upgrade from 2008 to 2012.

If you want to minimize downtime, then setup mirroring from SQL 2008 to 2012 (yes mirroring is still supported but declared deprecated). Then during the day of cutover (migration), change the mode of mirroring to SYNCHRONOUS and failover. This will require couple of seconds to bring the mirror server DB online.

this way, you can prepare in advance and minimize the downtime.

once the 2012 server db is online, you can get that database in AG. Make sure to remove mirroring once you failover.

I have put in detailed steps for migration at : Upgrade SQL Server 2000 database to 2008 R2 and enable new features

Don't go for detach/attach as it is risky as if something goes wrong, you will end up without backup. Backup restore is the best method.


ON DELETE CASCADE not deleting entry for foreign key I am running the below query in ideone

CREATE TABLE AUTHOR (
AUTHOR_ID NUMBER(5) PRIMARY KEY,
AUTHOR_NAME VARCHAR2(18)
);

INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM');
INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');

---------------------------------
CREATE TABLE BOOK
(
BOOK_ID NUMBER(5) PRIMARY KEY,
BOOK_NAME VARCHAR2(15),
AUTHOR_ID NUMBER(5),
CONSTRAINT author_fk
FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ON DELETE CASCADE
);

INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10002, 'HARRY POTTER', 1235);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10003, 'TO BE OR NOT TO BE', 1237);
INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)
    VALUES (10004, 'PROJECT MANAGEMENT', 1234);

After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:

DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;

Select * from book ;

10001|SOFTWARE DEVELOPEMENT|1234
10002|HARRY POTTER         |1235
10003|TO BE OR NOT TO BE   |1237
10004|PROJECT MANAGEMENT   |1234

and

Select * from author ;

1235|J K ROLLING
1236|WILLAM
1237|SHAKESPEAR

the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table).
Can someone help me what I am doing wrong.

Thanks,

Aftee searching the Ideone FAQ, it seems that it uses Sqlite3 for the SQL language.
Sqlite needs PRAGMA foreign_keys = ON; to enable foreign keys checks. As they say in their docs: SQLite Foreign Key Support :

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:
sqlite> PRAGMA foreign_keys = ON;

Tested at: Ideone


Deciding when to extend to child tables I have a table of entities. For example purposes, lets call them Vehicles: Car, Boat, Motercycle, etc.

I'd ideally like to place all entities in a single Vehicles table because a lot of related tables (VehicleRatings, VehicleComments, VehicleHistory, etc) are going to apply to any entity, not just one of them.

But some of the entities have a few individual properties that aren't shared with the other entity types. Right now, there is only 1 extra property for one entity, and 2 for another. I expect there might be a few more that get identified later on, but not many overall.

How do I know when it's better to create a child table for each entity type to store these individual properties, as opposed to just adding an extra column in the parent Vehicles table? Is there any questions I can ask myself to help determine this answer?

I am looking to optimize for query performance primarily, followed by ease of maintenance.

From a logical ERD perspective, the appropriate design is clearly the entity supertype/subtype pattern.  What you're wrestling with is how to implement this from a physical perspective.

Like a lot of data modeling issues, there is no hard and fast rule.  You are looking at a compromise.  Do you want a fair bit more complexity in your application logic and queries (supertype/subtype) or do you want the risk that your application logic might not properly enforce your constraints and that a Car record in your consolidated vehicle table might get a non-null value in the anchor_weight column?

The kinds of things that you should consider in deciding how to trade off these alternatives are:

How many subtype dependent columns are there?
How likely is it that new subtypes will be defined in the future - or that new subtype dependent columns will be needed?
How serious would it be if inappropriate data (column values) were recorded for some records?
Where do you stand philosophically? (e.g. "Null columns are evil", "Application-enforced constraints are evil", "1:1 relationships are evil", "Inner joins are evil", etc.)
How many queries do I have which target only one subtype?
Do I have any programming environment constraints which make supertype/subtype physical models harder to work with (e.g. heavy use of ORMs or simplistic tools like Access).

At the end of the day, you are going to make a practical decision based on what is most important to you.  Whatever you decide will have pros and cons - but they will be your pros and cons.


Restrict access to Edition- or instance-specific databases I have the following issue with SQL Server.

On my Windows Sever, SQL Server 2008 Express Edition is running. Now I have installed SQL Server 2008 Web Edition on the same server.

My problem is, when I log in using SQL Server Management Studio of the Web Edition instance, I am able to see, and access, all the databases there, including those created in the Express Edition, which is not what I want.

Is it possible to restrict visibility/accessibility of databases to just those related to the Web Edition when I log in using the Web Edition, and similar for the Express Edition?

There is no way to prevent a client software from accessing a SQL Server instance when correct credentials are supplied.

SQL Server Management Studio is the same product for all editions (actually, starting from SQL Server 2016, it is a separate product which ships separately from the engine).

Your only option to prevent users from accessing one of the instances in your server is granting permissions only to the people entitled to access the data.


Table partitioning for archiving data Scenario:

two databases: DB_A and DB_Archive  with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A  and moved
to DB_Archive mainly to leave thing "separated" because tableA is heavily queried on DB_A for records of the past 2 months.

I want to get rid of this process because it is slow and consumes a lot of resources.
I'm thinking of implementing table partitioning on DB_A with a partition function on a date column and storing all records < 2 month on one partition and all records > 2 months on another partition. My questions:

is this scenario going to behave like if I had 2 different databases? If I
query my tableA for records > getdate() - 30, is it going to read the archiving partition?
I supposed I have to partition the indexes as well, right?
How do I deal with the fact that tomorrow my partition function will
"change", I mean, if I create the function today (2nd of July, its
range will be 2nd of May, but tomorrow would be the 3rd of May). Can
I create a dynamic partition function?

With partitioning you would have to do a partition per day, which puts the Pre-SQL 2012  limit of 1000 paritions in a new perspective as it would only allow for 3 years archive. With SQL Server 2012 you get 15000 partitions which is plenty for 1 partition per day.

Every day you would add a new partition. If you want to move the 61st past day partition you can do it efficiently, but is still an offline operation. See Move a Partition to a Different File Group Efficiently.

All your indexes would have to be aligned, see Special Guidelines for Partitioned Indexes.

Buying into partitioning is not an easy decission and it may be quite a big bite to chew... see How To Decide if You Should Use Table Partitioning. Specifically you should not expect performance improvements from partitioning. You should approach performance problems on time seriest by clustering by datetime.


Using NEW on tables with generated columns I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially this:
BEFORE UPDATE
ON public.some_table
FOR EACH ROW
WHEN (NEW IS DISTINCT FROM OLD)
EXECUTE PROCEDURE public.update_modified_column();

However, this code despite being correct does not work with generated columns, giving the following error:

BEFORE trigger's WHEN condition cannot reference NEW generated columns

So my question is this: Is there any way to use NEW with a table that has generated columns without having to specify every single column of that table that isn't generated?
It can be something that requires specifying the columns that were generated to be excluded, just not the other way around.
Also, I know a potential alternative to generated tables is views (which are not the same thing but close enough for my use case), but I can't use views, since I need something that keeps track if the columns allows or doesn't allow for null values, which unfortunately views do not do that.
Using normal columns with a before insert/update trigger to calculate the columns, along with setting the columns as NOT NULL with a Default (for my use case ofc), has essentially fixed the issue; however, I will keep the question open to see if there is any solution to the generated columns specifically, since they are a bit more of an elegant solution.

That is simple: use an AFTER trigger instead. The column value will have been computed then.
On the other hand, your answers reveals that you need a BEFORE trigger because you want to modify NEW.
So probably the best solution is to use a BEFORE trigger with a WHEN condition that excludes the generated column:
WHEN ((NEW.col1, NEW.col2, ...)
      IS DISTINCT FROM
      (OLD.col1, OLD.col2, ...))


Minimizing and Handling Deadlocks Strategies I want to discuss deadlock minimizing strategies. From isolation level to retries to different insert strategies.

Lets say we have table A:

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT NULL,
  `changing_data_a` VARCHAR(100) DEFAULT NULL,
  `changing_data_b` VARCHAR(100) DEFAULT NULL,
  UNIQUE KEY `name` (`name`),
  PRIMARY KEY (`id`),
) ENGINE=INNODB

And lets say we get data from multiple providers(100k+ new/updated rows per day), name is unique and changing_data_a and changing_data_b data can be updated from time to time.

Main queries to run against this table:

The instrets of course.
Selects by id
Selects by name
Updating changing_data columns by name

What would be your inserting strategy?

IODKU small batches from any number or threads(same as number of providers) with isolation level Read Committed and 3 retries in case of a deadlock.
Caching the data in memory of the application, inserting from a single thread every x minutes.
Caching the data in a help table without unique key, summarizing the data every x minutes

Any other strategy?

Also would like to add to this post the MySQL documentation regarding deadlocks handling: How to Minimize and Handle Deadlocks

This is a "normalization" table?  That is, it is a mapping from name to a shorter id?

If so, simply remove it from the transaction.  You don't really need to rollback if there is a failure.  At worst, you might have a few extra rows in the table that are never used.  Outside the main transaction, have autocommit=ON.  And, there is probably no need for IODKU, so simply use INSERT IGNORE.

Change from

UNIQUE KEY `name` (`name`),
PRIMARY KEY (`id`),

to

PRIMARY KEY(name),
INDEX(id)   -- sufficient for auto_increment

Rationale:  Multiple unique indexes slows down INSERTs.

Caveat:  Under what conditions do you look at the "changing_date" columns?  If you are coming from id, then your indexes are better due to clustering.

Another thing to note:  All(?) forms of INSERT, including IODKU, "burn" ids.  That is, you will find lots of gaps in the id values.

Since you are inserting at a mere 1+/second, I don't think you need any kind okf batching.  But if they do come to you in clumps, then here is a discussion of how to do that efficiently: http://mysql.rjweb.org/doc.php/staging_table#normalization


Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification I currently have a table which looks like this:

CREATE TABLE "PDPC".collection
(
    col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass),
    q1 character varying(10000) COLLATE pg_catalog."default",
    q2 character varying(10000) COLLATE pg_catalog."default",
    q3 character varying(10000) COLLATE pg_catalog."default",
    q4 character varying(10000) COLLATE pg_catalog."default",
    dg_fkey bigint,
    CONSTRAINT collection_pkey PRIMARY KEY (col_no),
    CONSTRAINT collection_dg_fkey_fkey FOREIGN KEY (dg_fkey)
        REFERENCES "PDPC".datagroup (dg_no) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE "PDPC".collection
    OWNER to postgres;

I am trying to execute an UPSERT statement in PHP using postgresql, but i received

  Fatal error: Uncaught PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in C:\Apache24\htdocs\consideration.php:77 Stack trace: #0 C:\Apache24\htdocs\consideration.php(77): PDOStatement->execute() #1 {main} thrown in C:\Apache24\htdocs\consideration.php on line 77

My web page currently has a form that takes in a user input of answers to four questions, and these questions will go into "PDPC".collection table. I want to run INSERT or UPDATE according to the dm_fkey, which is the foreign key I have set for this table.

This is the UPSERT statement that I used.

INSERT INTO "PDPC".collection (q1, q2, q3, q4, dg_fkey)
      VALUES (:q1, :q2, :q3, :q4, :dg_no)
      ON CONFLICT(dg_fkey) DO UPDATE
      SET q1=:q1, q2=:q2, q3=:q3, q4=:q4

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in

This because dg_fkey is a column referenced in a FOREIGN KEY CONSTRAINT and not an index. In fact, if you want that to be faster, you may consider additionally adding an index. From the docs on ON CONFLICT

You probably want something like this,

CREATE UNIQUE INDEX asdf ON pdpc.collection(dg_fkey);

Or add it the the ddl,

CREATE TABLE pdpc.collection
(
    dg_fkey bigint UNIQUE

Then your upsert will work.

Also never use double-quotes on identifiers, that's a horrible practice in Pg


Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server Q.--> An alternative of OpenRowSet in SQL Server

Description:

I have created a procedure which uses Dynamic PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 Feb-2014 Mar-2014 etc...

Using dynamic query in PIVOT, I am able to get the desired output by passing columns from  table. The @Columns variable is set by using Stuff function with FOR XML Path.
By passing this @Columns variable into dynamic sql query for PIVOT I am able to achieve the following:

Result table:

Col1   | Jan-2014  | Feb-2014 | ...............
-------------------------------------------------------
ABC    | 123       |11231     | ...............
ASDF   |123123     |123123    | ...............

Now I have to create a physical table which will be having a result from the above procedure as shown in the table. As I mentioned the procedure is containing dynamic query, it is having EXECUTE. Because of which I am not able to create a table inside the procedure.

I tried to create a view as well but that also didn't worked as my procedure contains variable declaration.

To achieve this I used OpenRowSet in a script that runs on a daily basis.

My problem is: I cannot use OpenRowSet, and I am not able to create a physical table from a stored procedure. The OpenRowSet contains connection string that is why I cannot use it.

Please suggest...

EDIT 1:

My procedure for creating dynamic PIVOT table:

DECLARE
        @Columns AS NVARCHAR(MAX)
        ,@Query AS NVARCHAR(MAX)

SELECT @Columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(DateRange)
                         FROM View
                         ORDER BY ',' + QUOTENAME(DateRange)
                         FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '')

SET @Query = 'SELECT Col1, Col2, ' + @Columns +
             ' FROM (SELECT Col1, Col2, [DateRange], Col3 FROM View) A
               PIVOT
                 (SUM(Col3) FOR DateRange IN (' + @Columns + ') ) S '

Execute (@Query)

and script for creating a physical table

SELECT *
INTO Physicaltable
FROM
    OPENROWSET('SQLOLEDB', 'ConnectionString',
               'SET FMTONLY OFF;SET NOCOUNT ON;EXECUTE (AboveProcedure)')

If you cannot use OPENROWSET, you could create a loopback linked server and use OPENQUERY or EXECUTE AT.

DECLARE @srv nvarchar(4000);
SET @srv = @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server     = N'LOOPBACK',
@srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
@provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
@datasrc    = @srv;

-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname  = N'LOOPBACK',
@useself     = N'True',
@locallogin  = NULL,
@rmtuser     = NULL,
@rmtpassword = NULL;

I blogged about it some years ago.

Another option is using some kind of CLR trickery to interpret the result set definition and create a target table based on the type and length of the columns.

If you're not allowed to use OPENROWSET, I highly doubt you will be allowed to use CLR, though. In this case, a third option could be using sp_describe_first_result_set and code your table creation script in T-SQL. It requires SQL Server 2012 or newer.


SQL Server Populate replication server from DPM I've inherited an old SQL Server database with 4 machines: production_server(2008.r2), DPM_server(DPM 2007), and two replication_server1(2008.r2) replication_server2(2012). SQL Server had to be reinstalled on replication_server2, and so all data on that machine was lost.

I need to get transaction replication from production_server to this server back online without hammering the production server to create the initial snapshot.

Is it possible to use my DPM server to provide a recent copy of the data, Either via a Backup or snapshot?

Or, Is it possible to have replication_server1 provide the initial snapshot, and then point it to the production_server publication once everything has caught up?

I haven't been able to find clear answers in the documentation, thanks in advance for your time.

This steps will implement your second option.

  Or, Is it possible to have replication_server1 provide the initial
  snapshot, and then point it to the production_server publication once
  everything has caught up?

You will setup replication normally between repl1 and repl2.  Use the same distribution server that you will use when you setup prod-->repl2 server.

Once downtime start (confirm app servers are not making any new connection/writing to Production publishing articles):

Continue running the distribution agent jobs till undelivered command count is zero on both prod-->repl1 and repl1-->repl2. This is absolutely critical for this approach to work.
Stop the distribution agent job/jobs.
Remove replication (publisher and subscriber only) but keep the distributor as is.
Reinstall replication between prod-->repl2 with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

  replication support only  Provides automatic generation at the
  Subscriber of article custom stored procedures and triggers that
  support updating subscriptions, if appropriate. Assumes that the
  Subscriber already has the schema and initial data for published
  tables. When configuring a peer-to-peer transactional replication
  topology, ensure that the data at all nodes in the topology is
  identical. For more information, see Peer-to-Peer Transactional
  Replication.

  Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher,
       [name]     AS Agent,
       publication,
       a.publisher_db,
       subscriber_db,
       art.article,
       art.destination_object,
       art.source_object,
       undelivcmdsindistdb,
       delivcmdsindistdb,
       s.article_id,
       agent_id,
       a.publisher_id,
       subscriber_id
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock)
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
                    ON s.agent_id = a.id
       JOIN master.dbo.sysservers AS ss WITH (nolock)
         ON a.publisher_id = ss.srvid
       JOIN distribution.dbo.msarticles AS art WITH (nolock)
         ON s.article_id = art.article_id
WHERE  subscriber_db <> 'virtual'
       AND subscriber_db = 'SubscriberDBName'
ORDER  BY undelivcmdsindistdb DESC
COMPUTE sum(undelivcmdsindistdb)


Join two group_concat result this is the query:

$sql = "select pn.id, pn.purchased_number, pn.plan, pn.ring_to_group,
        count(rtg.id) as totalGroups,
        concat_ws(',', group_concat(rtg.group_name), group_concat(rtg.id)) as groupList
           from tbl_phone_numbers as pn
           left join tbl_rings_to_groups as rtg on rtg.user_id=pn.user_id
           where pn.user_id = ? and pn.status = ?";
$query = $this->db->query($sql, array($this->userId, 'Y'));

that returns the result of groupList like default,demo,1,2 but i want to get result something like :

default:1,demo:2

in MYSQL any suggestion??

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id)
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList


What would be the best way to model my simple table? What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?

Or, searching for the right row through a table with only 1 column with those 17 numbers concatenated together as a single bigint?

The numbers when combined are all unique from one another (no row has the same values/value as any other). I'm new to indexes and what not, so I have no idea how I should model my table/server logic.

If we're talking about something where you may want to query on one or more of the attributes my suggestion would be multiple columns, indexed appropriately (for a small, infrequently-changing data set, index all the columns).
This works best for the case where you may want to run queries like
SELECT * FROM table WHERE attribute1=1 AND attribute3=2
since you will have several indexed columns and searches on one or more of them will be relatively efficient.

If we're talking about something where you only ever want to query on the aggregate (all attributes match) a single column will be more efficient in terms of storage, and likely in terms of indexing as well.
This works best for cases where the previous example would always devolve into
SELECT * FROM table WHERE attribute1=1 AND … AND attribute17=17
as rather than having to cross-check across multiple columns it will simply be
SELECT * FROM table WHERE attributeColumn=1234567  -- Rather than having to scan 17 indexes (or examine the contents of 17 columns in every row) you will just have the single index scan.


Workload group "Importance" vs. Resource pool IOPS throttling Our SQL 2014 server has a blazing-fast tempdb drive (2800 IOPS) but a much slower data drive (500 IOPS). Our application runs a few long-running reporting queries that are I/O-intensive and we'd like to avoid them starving our server for I/O capacity when they run.

Ideally we'd be able to limit these queries to 50% of available I/O capacity. Unfortunately SQL Server Resource Pool's IOPS throttling is not percentage-baed nor volume-specific.  If I limit to 250 IOPS, then it will unnecessarily slow down performance of queries that make heavy demands on tempdb. Slowing down these long-running queries if the server is busy is OK, but slowing them down by 10x+ if they need lots of tempdb access is not OK.

So we're looking for workarounds that will defend other queries from these lower-priority, long-running queries, but without unnecessarily hurting performance of these long-running queries if they happen to use lots of tempdb.  It's not practical to change the queries themselves to reduce tempDB usage-- these queries are generated by a custom reporting feature that may sometimes generate really complex query plans that spill results to tempdb.

So far the best idea I have is to remove IOPS throttling and instead use the "Importance" of a workload group to defend the rest of the server's I/O capacity from these queries. Is this a good solution to the problem I'm trying to solve?  What are the pros and cons of using Importance?

Or is there a better way to achieve our goals?

Ideally we'd be able to limit these queries to 50% of available I/O capacity. Unfortunately SQL Server Resource Pool's IOPS throttling is not percentage-baed nor volume-specific.

Correct. Not to delve too deep into this since it isn't configurable - but let's take a quick second and think about that sentence and what you're really talking about here.

How do you know what your IO capacity is? Let me take your information:

  Our SQL 2014 server has a blazing-fast tempdb drive (2800 IOPS) but a much slower data drive (500 IOPS).

Is that 2800 IOPs @ 4k? 8k? 256k? Is that at outstanding IO of 1? 4? 8? 24? 128? Should SQL Server now monitor the entire server for everything IO related? What if it's in a hyper-visor and can't "see" the base infrastructure... now what? Is that 2800 IOPs with 99% IOs within 10 ms? 20 ms? 100 ms?

This isn't as easy as saying "50%" as there are too many variables of what 50% means... Not nit picking, just not as easy of a subject as one may think.

  So far the best idea I have is to remove IOPS throttling and instead use the "Importance" of a workload group to defend the rest of the server's I/O capacity from these queries.

Importance doesn't touch IO, it deal with scheduling. High runs more often than normal which runs more often than low. It doesn't mean it won't eat up your CPU and IO, it just tries to be more fair with scheduling based on your interpretation of the workload (i.e. those marked high are more important). This most likely will not help what you're attempting to solve.

  Or is there a better way to achieve our goals?

Yes :) Scale out the reporting workload to something such as a readable secondary in an availability group, transactional replication subscriber, read-only copy of the database (depending on data freshness required), CDC/CT to another server with SSIS, etc. It'll also isolate your storage subsystems (unless it's on the same SAN/NAS/etc.) from each other.


User can see Oracle tables via ODBC that her account does not have access to We have a user whose Oracle account, according to ALL_TAB_PRIVS, has access to tables A, B, and C. However, when the user creates an ODBC and connects to Oracle via Microsoft Access (using her Oracle credentials in the ODBC), she can also view data from D, E, and F. We know that she isn't sharing someone else's credentials because we had her create a new ODBC and a new MS Access database, and the issue is still happening. One of our DBA's ran a query (I don't know which system views or tables) that shows her username as having used MSACCESS.EXE and odbcad32.exe to connect to Oracle, but nothing more specific than that.

This is worrisome. How is ODBC apparently bypassing Oracle security?

EDIT: To clarify, the tables in question are not in the user's schema. We keep many business tables in a particular schema S and then grant users access to just those tables in that schema. Consequently, most of the end users' schemas are empty. Our workflow generally goes like this:
1. User X requests access to tables on S schema.
2. Oracle account X is created on the server.
3. X is granted access to specific S tables.

It turns out the user had been granted other privileges by a different account as well as the one we use, so we were unable to see those privileges in ALL_TAB_PRIVS. We had to look in DBA_TAB_PRIVS, and sure enough, there were the other permissions. Thanks to all for chiming in!


Yum update mysql from 5.1 to 5.5 I was just in the process of 'yum updating' a server. One of the updates its suggesting to carry out is an upgrade from mysql 5.1 to 5.5.

The mysql server operates in a master-master replication setup with another 5.1 mysql instance. I would also update the other master to 5.5, but want to keep one online at all times. This master also has a number of 5.1 mysql slaves replicating from it.

Does anyone know if:

yum update is safe from 5.1 to 5.5 without huge data rebuilds
if the above replication setup will be a problem.

Edit:
This is a centos 5.4 install. However the updates are coming in from the non-standard remi repo.

The replication setup you have cannot be maintained when upgrding to MySQL 5.5

You will have to go with Master/Slave. Here is the reason:

Someone asked a question about gibberish appearing in a binary log and I answered this question

In my answer, I explained that binary logs start at different positions in different versions of MySQL

Here are those positions:

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

With replication, these numbers apply to the relay logs as well. The net effect is that a Slave can replicate from an older version of MySQL, but not the other way around.

You are better off doing the following:

Convert Master/Master to Master/Slave
Upgrade to MySQL 5.5 on the Slave
Point all apps at the Slave (now called NewMaster)
service mysql stop on the Master
Upgrade to MySQL 5.5 on the Master (now called NewSlave) but do not start mysql
service mysql stop on NewMaster
rsync NewMaster's /var/lib/mysql to NewSlaves's /var/lib/mysql
rm /var/log/mysql/master.info on NewMaster
rm /var/log/mysql/master.info on NewSlave
service mysql start --skip-networking on NewMaster
run RESET MASTER on NewMaster
service mysql restart on NewMaster
service mysql start on NewSlave
run RESET MASTER on NewSlave
setup Master/Master Replication using position 107 as start master_log_pos

With regard to using yum to upgrade, I run in panic and scream in horror at the thought of it.

Here is my more conservative approach:

I would mysqldump everything EXCEPT the mysql schema.
Run this to generate SQL for all mysql user grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql
Uninstall MySQL 5.1
Install MySQL 5.5 from RPMs by hand.
Load /root/MySQLGrants.sql into MySQL 5.5
Load the mysqldump'd data back into MySQL 5.5


Does postgres single-user mode use postgresql.conf settings? Does running postgres in single-user mode (--single argument) cause postgres to start and use the settings present in the postgresql.conf file in the data directory defined by the -D option?

I am doing some maintenance with postgres in single user mode. I want to ensure that while running in single user mode that it has the correct resource allocation available. I have already set these resources in postgresql.conf and the work as expected when running in normal (not single-user) mode. Is the same config file used when running in single-user mode as long as its present in the defined data directory?

My specific goal is to run a VACUUM FREEZE on a 1.6TB table to cope with a multixact wraparound issue. My postgresql.conf is loaded up with higher than normal (for us) maintenance_work_mem settings. If these settings are not read when running in single-user mode I need to know so I can apply them manually using the -c flag I think. I am specifically seeking an answer as to if the config is loaded, Q&A about my FREEZE or specific tuning should happen elsewhere.

I think I could verify this with pg_settings however I am now 3 hours in to a long running maintenance operation. I would prefer not to stop and restart that maintenance if another user or a manpage will confirm that my postgresql.conf settings are applied.

I am running PG 9.4.5

Yes, postgresql.conf and postgresql.auto.conf will be read and used in single-user mode as usual.

Parameters you specify with -c options will override the settings from the configuration files.

You are doing the right thing.