Move database physical location using a loop for database names. MSSQL I have a main database and several other databases that store documents in them.
I am trying to figure out a way to write a loop in my database move script that dynamically loops through the document databases and include them in my script however the amount of databases aren't fixed. For example I could have one documents database called AppDocuments1 or multiple document databases e.g. AppDocuments1, AppDocuments2, AppDocuments3 and so on.
Could someone help me with creating a loop that automatically loops the all databases so I can include them in my database script.
I have access to the number of databases there are in a field in the main product database. e.g. NoOfDocumentDatabases : value = 6 would indication 6 document databases all named as above.

This is a script I developed in the past for helping me migrate lots of databases from one drive to another. It only works on one drive at a time (figured it was safer and easier to manager that way) but you can easily change that.
SET NOCOUNT ON;

DECLARE @OldDriveLetter VARCHAR(2) = 'D:';
DECLARE @NewDriveLetter VARCHAR(2) = 'N:';

DECLARE @Script_SetDatabaseOffline AS NVARCHAR(MAX) = 'USE master;' + CHAR(13)
DECLARE @Script_ModifyMDFLocation AS NVARCHAR(MAX) = ''
DECLARE @Script_ModifyLDFLocation AS NVARCHAR(MAX) = ''
DECLARE @Script_SetDatabaseOnline AS NVARCHAR(MAX) = '';

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- SET DATABASES OFFLINE
SELECT @Script_SetDatabaseOffline = @Script_SetDatabaseOffline +
'
    ALTER DATABASE [' + D.[name] + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[file_id] = 1; -- This ensures we de-dupe the result set so we don't have multiple dupe "ALTER SameDatabase SET OFFLINE"

--PRINT @Script_SetDatabaseOffline

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- MODIFY MDF FILE LOCATION
SELECT @Script_ModifyMDFLocation = @Script_ModifyMDFLocation +
'
    ALTER DATABASE [' + D.[name] + ']
    MODIFY FILE (name=''' + MF.[name] + ''', filename=''' + REPLACE(MF.physical_name, 'D:', @NewDriveLetter) + ''');
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[type] = 0 -- MDF (ROWS)

--PRINT @Script_ModifyMDFLocation

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- MODIFY LDF LOCATION
SELECT @Script_ModifyLDFLocation = @Script_ModifyLDFLocation +
'
    ALTER DATABASE [' + D.[name] + ']
    MODIFY FILE (name=''' + MF.[name] + ''', filename=''' + REPLACE(MF.physical_name, 'D:', @NewDriveLetter) + ''');
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[type] = 1; -- LDF (LOGS)

--PRINT @Script_ModifyLDFLocation

------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- SET DATABASES ONLINE
SELECT @Script_SetDatabaseOnline = @Script_SetDatabaseOnline +
'
    ALTER DATABASE [' + D.[name] + '] SET ONLINE;
'
FROM sys.Databases AS D
INNER JOIN sys.master_files AS MF
    ON D.database_id = MF.database_id
WHERE D.database_id > 4 -- Not "master", "temptb", "model", or "msdb" (system DBs)
    AND MF.physical_name LIKE @OldDriveLetter + '%' -- One drive at a time
    AND MF.[file_id] = 1; -- This ensures we de-dupe the result set so we don't have multiple dupe "ALTER SameDatabase SET OFFLINE"

--PRINT @Script_SetDatabaseOnline

/*
SELECT @Script_SetDatabaseOffline
SELECT @Script_ModifyMDFLocation
SELECT @Script_ModifyLDFLocation
SELECT @Script_SetDatabaseOnline;
*/

SELECT @Script_SetDatabaseOffline + @Script_ModifyMDFLocation + @Script_ModifyLDFLocation + @Script_SetDatabaseOnline;

The comments explain what's going on in details but in short it's basically using dynamic SQL and the sys schema objects to build out strings of the queries needed to reconfigure the locations of the MDFs and LDFs of all user databases. (You can add your own additional logic if you want to exclude certain user databases too.)
The steps it basically does are:

Sets the databases offline
Modifies the MDF locations
Modifies the LDF locations

*You'll want to move the physical files between step 3 and 4 (because the databases have to be offline before you can move them).

Sets the databases back online


MySql DB from Ubuntu to RHEL I would like to migrate my Mysql databases from Ubuntu to RHEL..what is the best way to proceed? I'm on community edition. I will be moving to entreprise edition of MySql on RHEL.

Depending on the hardware you're moving from/to (or not moving), you could just take the data & config files, and copy them across to the new system.  If the new system is set up the same for storage/memory, this might just work, but may need some tweaking of config to make it happen.

If you're changing to hardware where you can't just copy the files, then your only realistic option is a mysqldump export.


Automatic recovery on a 3 node AlwaysOn Multisite Cluster? We are looking to build a 3-node AlwaysOn Windows 2012 R2 failover cluster with 2 nodes in our primary datacenter for HA, and a 3rd node replica in our DR datacenter for reporting.

Given this is a multisite cluster, is there any way to configure this in a way which automatic failover will occur if the primary and DR sites lose connectivity or if the primary site goes down entirely?  Or would we have to add a 3rd site with a file share witness to the mix to make that possible?

The simplest answer is, no, you won't be able to facilitate this with a 3 node cluster in the manner described.

The reason is due to quorum. Assuming the 3 nodes, 2 at Primary and 1 at DR with Windows Server 2012R2. Dynamic quorum is on by default, this will automatically adjust node weights in case of a node failure. Dynamic witness is also on by default which will change the witness vote to keep the number of total votes odd.

The thing is that dynamic quorum only works if less than half of the nodes go down simultaneously. If 50% or more voting nodes go down at once there won't be enough voters left to keep quorum or for dynamic quorum to decide that this isn't a split brain scenario.

How could you potentially achieve this?

If it would be possible to put 2 nodes at the Primary site, 2 nodes at the DR site, and a witness at a 3rd site then it should do what you're looking for.

  is there any way to configure this in a way which automatic failover will occur if the primary and DR sites lose connectivity or if the primary site goes down entirely?

These look the same from the perspective of the DR site. Whether it loses connection with the servers at the primary site or whether the primary site goes down doesn't look any different. In each case they no longer can "see" the other nodes, only the local ones. This results in the race to acquire the lock on the witness. Whichever side attains the lock first, wins.

There is an additional setting in Windows Server 2012R2 called LowerQuorumPriorityNodeID which can be used to weight one side or the other when these types of situations happen.


MySQL - Indexing Partitioned Column I have a table with no primary key defined, partitioned on a column, using List Partitioning method. It has huge data in one partition. Though partitioned column is used in WHERE clause while querying against this table, performance is very slow.

Is it suggestible to create an index that includes the partitioned column? Here's my sample table structure,

CREATE TABLE inventory
(
   AccountId BIGINT,
   AccountNumber VARCHAR(250),
   DeviceId BIGINT,
   DeviceNumber VARCHAR(250),
   ClientId SMALLINT
   CreatedDate DATETIME,
   CreatedUser VARCHAR(50)
)
PARTITION BY LIST (ClientId)
 (PARTITION p_1 VALUES IN (1),
  PARTITION p_2 VALUES IN (2),
  PARTITION p_3 VALUES IN (3),
  PARTITION p_4 VALUES IN (4),
  PARTITION p_5 VALUES IN (5),
  PARTITION p_6 VALUES IN (6),
  PARTITION p_7 VALUES IN (7),
  PARTITION p_8 VALUES IN (8),
  PARTITION p_9 VALUES IN (9),
  PARTITION p_10 VALUES IN (10),
  PARTITION p_11 VALUES IN (11),
  PARTITION p_12 VALUES IN (12),
  PARTITION p_13 VALUES IN (13),
  PARTITION p_14 VALUES IN (14),
  PARTITION p_15 VALUES IN (15));

Table is partitioned by "ClientId" and I had to make 1024 partitions to this table.

1024 partitions means 1024 "sub-tables".  Certain operations will open all 1024.
A table (or partition) is implemented using files in the OS.  You are at the mercy of the OS speed or sluggishness.
If you have WHERE ClientId = 123 that should lead to "partition pruning", which leads to opening the one partition, then scanning the entire partition.
INDEX(ClientId), without partitioning, would be almost as good.

This would be better:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ClientId, id),
INDEX(id)

Now the data is clustered on ClientId, there is no extra thing to "open", and the performance will be better.

Please provide the main queries you have so that I can check whether I have messed up other cases.

Other issues...

AccountId BIGINT,
AccountNumber VARCHAR(250),

sounds grossly redundant.  Is there a 1:1 mapping between that Id and that Number?  Then the pairs should be in another table, and only the Id should be in this table.  Ditto for Devices.

Making that change would significantly shrink this table, thereby making operations somewhat faster.

Meanwhile, learn about JOIN in order to get the ...Number from the ...Id.

More on partitioning.


SQL Server Trigger When New Value  Old Value I need a trigger that update to Null a column everytime another column is changed.

This is what I have now:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE (NAME)
BEGIN
    UPDATE contact
    SET ERROR = Null
    FROM contact c2 INNER JOIN Inserted I ON c2.ID = I.ID
    INNER JOIN Deleted D ON d.ID = c2.ID

    WHERE c2.NAME <> I.NAME
    AND I.NAME <> D.NAME

END
END

I'm testing like this:

UPDATE contact SET NAME = 'John' WHERE NAME = 'Paul'

But its not working and I'm running out of ideas. Any help?

There are a few things that need to be fixed in your trigger:

The WHERE condition of c2.NAME <> I.NAME will always be false since the inserted pseudo-table is the current definition of those same rows in the real table (at least in an AFTER Trigger). This one line was preventing anything else from happening.
If it weren't for issue #1, you would be seeing "odd" behavior regarding what rows in the contact table were being updated by this Trigger. The issue here is that you have two independent references to the same table in the main UPDATE clause and the FROM clause. You have UPDATE contact (notice there is no table alias) and then FROM contact c2 (notice the c2 alias, making this a second reference to the same table. You can either remove the c2 alias from the FROM clause (such that both references are just contact), OR, what I recommend for readability is to keep the alias and use it in place of the table name in the UPDATE clause, such that it reads: UPDATE c2 ....
Finally, when comparing strings to determine if there are any changes, you really need to use a binary collation (one ending in _BIN2 if available, else one ending in _BIN). This will prevent any "insensitive" options (such as case and/or accent) from masking changes, and it will also prevent string normalization from masking any changes.

Updated code:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE (NAME))
  BEGIN
    UPDATE con
    SET    con.ERROR = NULL
    FROM   contact con
    INNER JOIN DELETED del
            ON del.ID = con.ID
    WHERE  ins.NAME <> del.NAME COLLATE Latin1_General_100_BIN2
  END;
END;


How do I modify a stored procedure in IBM DB2? I have a DB2 database and I'm using Control Center. I've found a procedure in the 'Stored Procedures' folder, which I need to modify. In MS SQL Server and Oracle you can accomplish this right from the DBMS.

Is there a way I can modify this procedure with an editor in DB2 (9.7.0), similar to SQL Server and Oracle?

I believe you can do so through IBM Data Studio. I would use that tool anyway as that is now the approved IBM tool for interacting with DB2 and Control Center is deprecated.


Upgrading SQL Server 2008 R2 Standard edition to SQL Server 2008 R2 Enterprise edition what is the process and the impact? We are using SQL Server 2008 R2 Standard edition with Visual Studio 2010 (Web API). Also we have created SSIS packages using business intelligence. If we decide later on to upgrade to SQL Server 2008 R2 Enterprise edition, what is the process and the impact on the .NET environment?

The process is simply to launch SQL Server setup from your DVD or ISO, go to the Maintenance tab, and perform an "Edition Upgrade" (please ignore the fact that this screen shot is from SQL Server 2014):

You'll need to enter a different product key.

Please also make sure you install the latest service pack (SP3; 10.50.6000).

As for impact, you shouldn't expect any negative impact at all, except that perhaps you will need to bounce the service as a part of installation.

More info on edition upgrades.

If this doesn't work out, you could always install a brand new instance of SQL Server with a different instance name, then backup and restore your user databases (and copy linked servers, job, and logins). But that really should be a last resort - if you have an issue with an edition upgrade, come back with a more specific question.


MongoDB find() not getting anything I have a collection as follows:

{
    "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
    "date" : "20180126",
    "oid" : "000",
    "aid" : "0000000000",
    "currentavalible" : "7",
    "deletedbyuser" : "0",
    "reply" : [
        {
            "oidaid" : "000000",
            "nickname" : "****",
            "date" : "2018-01-26 07:32:45",
            "recomm" : "13",
            "unrecomm" : "0",
            "contents" : "aaaaaaaaaaaa"
        },
        {
            "oidaid" : "000088",
            "nickname" : "para****",
            "date" : "2018-01-26 08:29:54",
            "recomm" : "12",
            "unrecomm" : "0",
            "contents" : "bbbbbb"
        }
    ]
}

To find in reply that  "recomm" is "13" i tried:

db.comments.find( { 'reply.recomm' : '13' })
db.comments.find( { 'reply' :{ 'recomm' : '13'}  })
db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' })
db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply' : { 'recomm' : '12'} })

And all fetched me nothing.

What seems to be the error in my syntax?

db.comments.find( { 'reply.recomm' : '13' })
db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' })

Both above query is looks fine and will be fetch the documents of records inside the comments collections.

I am sure that you are doing mistake something like not in the tree of particular database  where your comments collection reside.

Let's from beginning see here,

First i have create the StackExchange database.

>Use StackExchange

Then i have created the comments collection inside the StackExchange database.

> use StackExchange
switched to db StackExchange
>

> db.createCollection("comments")
{ "ok" : 1 }

And inside of comments collections i have insert the documents, which you have provided in souce code.

> db.comments.insert({
...     "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
...     "date" : "20180126",
...     "oid" : "000",
...     "aid" : "0000000000",
...     "currentavalible" : "7",
...     "deletedbyuser" : "0",
...     "reply" : [
...         {
...             "oidaid" : "000000",
...             "nickname" : "****",
...             "date" : "2018-01-26 07:32:45",
...             "recomm" : "13",
...             "unrecomm" : "0",
...             "contents" : "aaaaaaaaaaaa"
...         },
...         {
...             "oidaid" : "000088",
...             "nickname" : "para****",
...             "date" : "2018-01-26 08:29:54",
...             "recomm" : "12",
...             "unrecomm" : "0",
...             "contents" : "bbbbbb"
...         }
...     ]
... })
WriteResult({ "nInserted" : 1 })

Now i want to fetch comments documents records from the comments collection, which is in StackExchange database.
First check through mongo shell, what is your current database like that

> db
StackExchange
>

Here it mongo shell the current database is StackExchange.

Note:- I have create comments collection in StackExchange database. But for your confirmation , you must always checks before queering the query in mongo shell like

  show collections
  comments

Then try to find the documents inside the collection through the db.collection.find().

  Note:- Here collection is the collection name. In your case it's
  comments.

From the first query db.comments.find( { 'reply.recomm' : '13' })

> db.comments.find( { 'reply.recomm' : '13' }).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "date" : "20180126",
        "oid" : "000",
        "aid" : "0000000000",
        "currentavalible" : "7",
        "deletedbyuser" : "0",
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                },
                {
                        "oidaid" : "000088",
                        "nickname" : "para****",
                        "date" : "2018-01-26 08:29:54",
                        "recomm" : "12",
                        "unrecomm" : "0",
                        "contents" : "bbbbbb"
                }
        ]
}

and from the query
 db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' })

> db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply.recomm' : '12' }).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "date" : "20180126",
        "oid" : "000",
        "aid" : "0000000000",
        "currentavalible" : "7",
        "deletedbyuser" : "0",
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                },
                {
                        "oidaid" : "000088",
                        "nickname" : "para****",
                        "date" : "2018-01-26 08:29:54",
                        "recomm" : "12",
                        "unrecomm" : "0",
                        "contents" : "bbbbbb"
                }
        ]
}
>

But from the mention below both query you shall not fetch the documents records from the comments collection due to their improper fetching of Array records.

db.comments.find( { 'reply' :{ 'recomm' : '13'}  })

db.comments.find( {'_id' :  ObjectId("5a6d9a1ee183f06dfd91dbf0"), 'reply' : { 'recomm' : '12'} })

As per MongoDB 3.6 Documented Here . If you want to fetch the array documents then you can use $ (projection), Or you can use Aggregation Pipeline Stages.

As you said only you want to find the records in reply that  "recomm" is 13.
so, you have to use  $elemMatch (projection)operator and your query will be as mention below

> db.comments.find({"_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0")},{reply: {$elemMatch: {recomm: "13"}}}).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                }
        ]
}
>

After the OP requirement to edit the code and execute the query to find out OP desire results.

Here i want to insert the second documents which having different id field records in comments documents.

> db.comments.insert({
... ...     "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf1"),
... ...     "date" : "20180126",
... ...     "oid" : "000",
... ...     "aid" : "0000000000",
... ...     "currentavalible" : "7",
... ...     "deletedbyuser" : "0",
... ...     "reply" : [
... ...         {
... ...             "oidaid" : "000000",
... ...             "nickname" : "****",
... ...             "date" : "2018-01-26 07:32:45",
... ...             "recomm" : "13",
... ...             "unrecomm" : "0",
... ...             "contents" : "aaaaaaaaaaaa"
... ...         },
... ...         {
... ...             "oidaid" : "000088",
... ...             "nickname" : "para****",
... ...             "date" : "2018-01-26 08:29:54",
... ...             "recomm" : "12",
... ...             "unrecomm" : "0",
... ...             "contents" : "bbbbbb"
... ...         }
... ...     ]
... ... })
WriteResult({ "nInserted" : 1 })

Now i only find out the comments collection documents , which having the "recomm":"13" records.

> db.comments.find({},{reply: {$elemMatch: {recomm: "13"}}}).pretty()
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf0"),
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                }
        ]
}
{
        "_id" : ObjectId("5a6d9a1ee183f06dfd91dbf1"),
        "reply" : [
                {
                        "oidaid" : "000000",
                        "nickname" : "****",
                        "date" : "2018-01-26 07:32:45",
                        "recomm" : "13",
                        "unrecomm" : "0",
                        "contents" : "aaaaaaaaaaaa"
                }
        ]
}


Merge date ranges on updates Given a table of date ranges, e.g. Start Date, End Date, I've seen a few examples online of how to merge ranges on the fly, but I'm able to merge them as the user makes updates.

So for example, if 01/01 to 01/02 exists and a user inserts 15/01 to 15/02, then the original range can be updated to 01/01 to 15/02.

Is there a well known way for dealing with this, or do I need to work it all out and put it in a stored procedure?

The table looks like this...

CREATE TABLE [dbo].[dateRange](
   [Id] [int] NOT NULL,
   [Start] [datetime] NOT NULL,
   [Finish] [datetime] NOT NULL
)

Given two ranges...

INSERT INTO dateRange (Id, Start, Finish) VALUES (1, '01-01-17', '01-02-17');
INSERT INTO dateRange (Id, Start, Finish) VALUES (2, '03-01-17', '01-04-17');

The user can delete, update and insert new ranges. Deletes are simple. I think there are three cases for inserts (updates have similar possibilities)...

New range covers an existing range...

INSERT INTO dateRange (Id, Start, Finish) VALUES (3, '15-12-16', '15-02-17');

Results in deletion of 1.
New range is covered by existing range. So...

INSERT INTO dateRange (Id, Start, Finish) VALUES (4, '15-01-17', '16-01-17');

.. is ignored.
New range partially overlaps a new range.

INSERT INTO dateRange (Id, Start, Finish) VALUES (5, '15-12-16', '15-01-17');

Insert, again, is ignored and the overlapped row is extended. It's more complicated if both ends of the new range are overlapped because one of the overlapped ranges needs to be deleted.

The window functions sound cool but I'm thinking of doing it in C#.

For every user, I want zero overlapping date ranges. I'm using SQL Server 2014.

In one of my comments to you

  I was thinking about solving your problem with a DML trigger. That
  would allow the work to be done in the database engine without having
  to return all of the data to C# for evaluation - are you open to a
  solution that blows away the current data and lays out a new set of
  rows with the proper start/finish dates based on the recent DML
  action? It would not be as elegant as updating existing rows.

You said (paraphrasing)

  I think it's worth a look but I tend to avoid blowing data in that
  manner... could you give at least a brief account of your solution?

Well, it's a slow work day today and since no one else has come on stage with an answer yet, I'll step up to the mic and prepare for the hecklers.  This is a prototype without any performance considerations (indexes, etc).  Even with proper indexing, it may not suit your needs.  Naturally, if I have misunderstood your question (or worse, have a fatal flaw), go easy on the downvotes ;)

I tried to come at this problem with the KISS principle.  My approach relies on a table trigger which deletes existing data and lays out a new timeline when inserts, updates and deletes are executed.  It relies heavily on logic I got from Itzik Ben-Gan's post entitled New Solution to the Packing Intervals Problem.  You should definitely read Itzik's post to fully understand the logic in my trigger.

Your original question indicated that "For every user, I want zero overlapping date ranges.".  I took the liberty of adding a UserID column to your table.  My table also uses an IDENTITY for the Id column.  Since my solution blows away existing data and inserts a new timeline, I don't specify the Id column on my inserts.

IF OBJECT_ID('dbo.DateRange') IS NOT NULL
    DROP TABLE dbo.DateRange

CREATE TABLE [dbo].[DateRange](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [UserId] VARCHAR(10) NOT NULL,
   [Start] [datetime] NOT NULL,
   [Finish] [datetime] NOT NULL
)
go

Here's the trigger where the real fun happens

CREATE TRIGGER dbo.DateRange_Merge_Ranges
   ON  dbo.DateRange
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    --Dropping temp tables used in the trigger
    IF OBJECT_ID('tempdb..DataToInsert') IS NOT NULL
        DROP TABLE #DataToInsert;
    IF OBJECT_ID('tempdb..UniqueUserIdsAffected') IS NOT NULL
        DROP TABLE #UniqueUserIdsAffected;

    --Get all UserId's affected by the Insert, Update, Delete
    SELECT * INTO #UniqueUserIdsAffected FROM
    (
    select UserId from inserted
    union
    select UserId from deleted
    ) a

    --Calculate a new column called prvend which contains the 'previous' rows Finish date
;   WITH C1 AS
    (
        SELECT a.*,
            MAX(Finish) OVER(PARTITION BY a.UserId
                            ORDER BY a.start, a.finish
                            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prvend
        FROM dbo.DateRange a join
        #UniqueUserIdsAffected b on
            b.UserId = a.UserId
    ),

    --Determine whether each row starts a new 'packed-interval' - call the column grp
    C2 AS
    (
    SELECT *,
        SUM(isstart) OVER(PARTITION BY C1.UserId
                        ORDER BY C1.Start, C1.Finish
                        ROWS UNBOUNDED PRECEDING) AS grp
    FROM C1
    CROSS APPLY ( VALUES(CASE WHEN Start <= prvend THEN 0 ELSE 1 END) ) AS A(isstart)
    )

    --Insert the results into a local temp table because we're going to delete
    --all rows fromthe real table matching the UserId's affected in the Insert, Update, Delete
    SELECT C2.UserId, MIN(C2.Start) AS Start, MAX(C2.Finish) AS Finish into #DataToInsert
    FROM C2
    group by C2.UserId, C2.grp;

    --Delete all rows fromthe real table matching the UserId's affected in the Insert, Update, Delete
    DELETE a from
        dbo.DateRange a join
        #UniqueUserIdsAffected b on b.UserId = a.UserId

    --Insert into the real table the newly calculated time slices
    INSERT INTO dbo.DateRange(UserId, Start, Finish)
        SELECT UserId, Start, Finish from #DataToInsert

END
GO

Now that we have our table with the trigger defined, let's insert some rows
I'm inserting rows for TOM and JOE.  My DML examples deal with TOM, but I wanted you to see that there was no magic up my sleeve in keeping updates isolated to whom they belong.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-01-01', '2017-02-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-03-01', '2017-04-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('JOE', '2017-01-01', '2017-02-01');
INSERT INTO DateRange (UserId, Start, Finish) VALUES ('JOE', '2017-03-01', '2017-04-01');
select * from DateRange order by UserId, Start, Finish

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Each of the following cases build on the previous case (if one exists)

Case 1: New range covers an existing range...

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2016-12-15', '2017-02-15');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 2: New range is covered by existing range. So...

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-01-15', '2017-01-16');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 3: New range partially overlaps a new range.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2016-12-15', '2017-01-15');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-02-15 00:00:00.000 |
| TOM    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 4: Update an existing row

UPDATE DateRange SET Finish = '2017-03-02' WHERE UserId = 'TOM' and Start = '2016-12-15';

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-04-01 00:00:00.000 |

Case 5: Insert a new range

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-06-01', '2017-07-01');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-06-01 00:00:00.000 | 2017-07-01 00:00:00.000 |

Case 6: New range partially overlaps a new range.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-04-01', '2017-05-01');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-05-01 00:00:00.000 |
| TOM    | 2017-06-01 00:00:00.000 | 2017-07-01 00:00:00.000 |

Case 7: New range partially overlaps a new range.

INSERT INTO DateRange (UserId, Start, Finish) VALUES ('TOM', '2017-05-05', '2017-06-05');

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2016-12-15 00:00:00.000 | 2017-05-01 00:00:00.000 |
| TOM    | 2017-05-05 00:00:00.000 | 2017-07-01 00:00:00.000 |

Case 8: Delete an existing row

delete from DateRange WHERE UserId = 'TOM' and Start = '2016-12-15';

| UserId | Start                   | Finish                  |
|--------|-------------------------|-------------------------|
| JOE    | 2017-01-01 00:00:00.000 | 2017-02-01 00:00:00.000 |
| JOE    | 2017-03-01 00:00:00.000 | 2017-04-01 00:00:00.000 |
| TOM    | 2017-05-05 00:00:00.000 | 2017-07-01 00:00:00.000 |

If you can achieve the same results in C# in an easier way, you should stick with that.  Of course, a 'smartie' on this site may have a better solution.


DBCC SHRINKFILE works with file_id but not with logical name I am trying to shrink a database file but am running into errors.

Using the file_id from sys.database_files works, but using the logical file name yields an error.

The logical file name is the same in both statements, so that can't be the problem. Also, the database that is being connected to, is the same. The following works as expected:

declare @fileId as int = (select file_id from sys.database_files where name = 'XY')
DBCC SHRINKFILE (@fileId, 0, TRUNCATEONLY)

However the following...

DBCC SHRINKFILE ('XY' , 0, TRUNCATEONLY)

...will result in error 8985:

  Msg 8985, Level 16, State 1, Line 1
  Could not locate file 'XY' for database '<my database>' in sys.database_files. The file either does not exist, or was dropped.

Revised my answer, testing did not show as a valid reason for it not to work.

Couple of things to check

Are there leading or trailing spaces in the file name?
Did you recently upgrade to SP3 by any chance?

Try switching the recovery model from full, to simple, back to full again. There is an apparent bug when updating to SP3. Changing the recovery model will reset the log sequence number and for whatever reason that clears the issue, at least temporarily.

social.msdn conversation about this


How to backup and restore a database to a different location and name via command line only I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only.
The name of the database on the new location should not be identical to the original name.

I've tried the following commands, but failed when I tried to restore the database:

Backup:

sqlcmd -E -S MyUser\SQLEXPRESS -Q "BACKUP DATABASE original_db TO DISK='C:\tmp\backups\base_db.bak'"

Restore:

sqlcmd -E -S MyUser\SQLEXPRESS -Q "RESTORE DATABASE new_test_db FROM DISK='C:\tmp\backups\base_db.bak'
with move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.mdf',
    move 'original_db' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\new_test_db.ldf'"

When I try to restore the database I get the following error:
"The backup set holds a backup of a database other than the existing 'new_test_db' database."

(When my database didn't exist I got the following error on restore - "Logical file 'original_db' is not part of database 'new_test_db'. Use RESTORE FILELISTONLY to list the logical file names.")

Any ideas how to do it properly?

Thanks for the help.

The WITH MOVE 'logical_file_name_in_backup' refers to the internal name of that file, not the database name.

Run this to see what I mean. You're looking at the name column

SELECT * FROM sys.database_files

Edit:

Need WITH REPLACE now... see first link above and look for

  The database name is different from the database name recorded in the backup set.


Best practice around non-production databases refreshes and TDE Say we have a production system with several databases encrypted using TDE and a self-generated certificate.

We regularly need to take this database and refresh our non-production systems. This process involves backing up the source database, restoring it to pre-production, obfuscating personal data and a number of other items.

The key fact here is that the pre-production system has a copy of the encryption certificate from production

I am looking at ways to make this process more secure - I'm not happy with the certificate sat on the pre-production environment. Is there another way that will:

Never have an unencrypted backup saved anywhere
Not allow propagation of personal data from production into non-production systems?

In a perfect world, if you’re storing personally identifiable data in production, you never restore the database to a development environment.

You can’t rely on obfuscation alone. For example, sooner or later, someone’s going to make a backup copy of a table in production. They’re going to select the dbo.Customers table into a new table for safekeeping before they make a change to it, and they’re going to forget to drop it. Your obfuscation code won’t be expecting new random tables with personally identifiable data, and so after the obfuscation process, the data will still be there.

Instead, when you need development data, generate it from scratch.

(Remember: you asked for the best practice, not a compromise. If your response is, “but we want production data in development,” then you’re already missing the best practice.)


How to change default port for T-SQL Debugger The default port for T-SQL Debugger is 135, it is possible to change this port ? In my company this port is on special list of banned ports, therefore we need to open that service on another port number.

I am following this article:

Transact-SQL Debugger for SQL Server 2008 – Part 2 by Bill Ramos

I don't think it's possible to change the TCP port used by T-SQL Debugger.
You can use an application such as RelayTCP and port forwarding rules on your router to achieve what you want. It's difficult to give specific recommendations without knowing more about your network topology.

It may be easier to set up a VPN to your site and then run T-SQL Debugger over VPN (I assume that LAN-local connections to TCP 135 will not be blocked).


If database is compromised and downloaded, does it matter if user passwords are hashed? I'm currently implementing as much security into a web app as possible, but I can't stop thinking about the database itself. For all the security I implement, such as salting and hashing passwords in the users table, if hackers manage to download the database entirely (by getting access to the server for example), they'll be able to see all the data in the entire database?

I've been reading about all the database breaches of well-known brands within the past couple of years, and that these databases are for sale on the dark web.

I understand hashing passwords will slow down / stop hackers having access to plain text login credentials that may be the same for other websites the user has signed up to, but my question is, what about the personal information stored in the database that isn't hashed. Like which products you've bought over the past couple of years, or your employment history, or your contacts, or something really embarrassing such as which adult websites you've visited and/or bookmarked.

Considering you can't hash a user's email address if it's being used as part of the login credentials, some hacker could query the database using the userID for example, get personal information from other tables, and then send a blackmail email to that user.

The two potential solutions I can think of are:

Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.
Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.

So, 1 isn't much of a solution because I can't encrypt everything. And would 2 actually work i.e. would a web app using django for example be able to write to the database if its root file on the server was an encrypted file? I know MS SQL Server has an always-encrypted feature, but I'm using MySQL.

Sorry for the essay-length question - my head is spinning with all sorts of possibilities, and it's driving me nuts!

Thank you!

There are two concepts at play here:

Data at rest
Data in motion

Data at rest

As you correctly noted, if someone gets access to your database and the data itself is not encrypted, then hashing and salting passwords makes no difference.

  Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.

You should enable on-disk encryption in some way or form. SQL Server offers Transparent Data Encryption (TDE), where as soon as data leaves memory and is persisted to storage, it is encrypted. This is called "encryption at rest".

This feature may not be available in your RDBMS, so you might rely on the storage vendor's on-disk encryption. Otherwise, you can investigate file-system level encryption.

The important thing is to keep the database administrators away from the encryption keys, and to ensure that database backups are handled with the same security considerations as the database server.

Data in motion

As for your question around applications having access to encrypted data, this is handled differently. I'll return again to SQL Server because that's what I know.

  Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.

The application uses a certificate to connect to the server, so that only it can see the data it has access to. It's called Always Encrypted, and unless someone has that certificate, the data remains inaccessible.

The database server you end up using should take these two concepts into account. Here's the documentation for SQL Server, but the other more established players should have similar features.


A connection cannot be made. Ensure that the server is running I have 2 PCs at home: Win10 with SQL Server 2016 and Win7 with SQL Server 2014.

Both servers are installed in Windows Authentication mode. I have set up users with the same name on both PCs.

I can connect to Database Engine from Win10 to Win7 and vice versa, but when I try to connect to SSAS, I get following error:

  Cannot connect to XPSWin10.

  ADDITIONAL INFORMATION:

  A connection cannot be made. Ensure that the server is running.
  (Microsoft.AnalysisServices.AppLocal.AdomdClient)

  Unable to read data from the transport connection: An existing
  connection was forcibly closed by the remote host. (System)

  An existing connection was forcibly closed by the remote host (System)

I was able to connect to SSAS before I reinstalled my Windows 10 PC.

I should say, I have 2 users in each PC (User10 and User7). On Win10 I login with User10 and on Win7 I login with User7. Both users on both PC have the same password.

The service is running - I can connect locally.

I disabled the firewall and still I have the issue. The only difference is it shows me the error after a minute not immediately.

After spending lot of time, I found the solution!

As I mentioned, I had to log into Win10 with User10 and at the same time log into Win7, using User7.

Looks like connection was successful when I log into both PC with the same username.

So, I ended up to run SSMS as different user (in each PC) to be able connect to the other one.

On Win10 PC, I created a shortcut to run SSMA as User7, and this way I was able to connect to WIn7 PC and also created the shortcut in Win7 PC to run the SSMS as User10 to be able to connect to Win10 PC.

This link (FIX Connection) also might be helpful, it did not help me though.

This is the link to create shortcut to run as different user


looking for missing indexes in the cached execution plans I have the following query that shows me what is cached in sql server:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 1=1
  --AND OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
  AND query_plan IS NOT NULL
ORDER BY ExecutionCount DESC

Is there a way I could query the queryPlan field looking for missing indexes?

You can try to execute below script and find for missing indexes in cached execution plans from here

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU
, total_elapsed_time/execution_count AS AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

It has sort of worked for me in sql server 2008 and 2012, but still you will need to check if that works the way you looking in sql server 2014.

Also,

In addition to above i would suggest you read Finding what queries in the plan cache use a specific index , a great script from Jonathan, which really helps in understanding the usage of those indexes getting used for the current execution plans in cache.


Does dropping a Primary Key constraint from a Partitioned Table break the partition? We have a table partitioned on a date column. The Primary Key for the table is a composite key of this date column and 4 other columns.

We are thinking about dropping the Primary Key constraint from this table and simply creating a new non-clustered index.

Is the partitioning affected if we drop the Primary Key constraint since the partitioning column is part of the Primary Key?

It shouldn't affect the table's partitioning, as the table was created on the partition scheme with the partitioning column specified.

Each partition has it's own HOBT (Heap or B-Tree). So essentially you have a HOBT for each Date in your table.

If the primary key is also a clustered index, you will be converting each partitions' B-Tree to a Heap. Depending upon the size of the table, moving the data from the B-Tree to the Heap could take a considerable amount of time and resources. It's recommended to drop all nonclustereds before attempting to drop the clustered index, then recreating them after (or disable / rebuild). Here's a detailed explanation from Microsoft:

"When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last."

http://msdn.microsoft.com/en-us/library/ms190691%28v=SQL.90%29.aspx

I won't comment on query performance implications of doing this, as I'm not familiar with how the table is queried.

As with any major change, make sure to try this in the test environment before attempting to make this change.


Two-way replication between SQL Server 2008 R2 and SQL Server 2008 R2 SP3 I know that in SQL Server you can't have two-way replication between different versions.

I wanted to know if SQL Server 2008 R2 and SQL Server 2008 R2 SP3 are considered "different versions"?

Did anyone try to build a replication between those 2 versions?

Those aren't different versions just different service packs. Replicating between 2008 and 2008 R2 would better fit that definition of replication between two specific versions.

So The way I understand the MSDN article on the subject here is this

  When you replicate between or among different versions of SQL Server,
  you are usually limited to the functionality of the earliest version
  used. For example, if you upgrade a Distributor to an instance of SQL
  Server 2008, but you have a Publisher that is running an instance of
  SQL Server 2005 and a Subscriber that is running an instance of SQL
  Server 2000, you are limited to the general functionality and
  replication functionality of SQL Server 2000.

And here is something more specific to merge replication

  For merge replication, a Subscriber to a merge publication can be any
  version no later than the Publisher version. For more information
  about compatibility for earlier versions, see "Compatibility Level for
  Merge Publications"

In reality though, even if you can replicate between different versions, that can't be ideal. Replication is already complex enough by itself without adding in yet another layer of complexity.

So if you can apply those service packs to the other servers, you should in my opinion.


Is the Memory engine adequate for a read intensive/lookup table? I have an InnoDB table with just two columns, a VARCHAR(20) and a DATETIME, and around 4M rows. This table serves as a blacklist for other tables, and is truncated and recreated from a csv file from time to time. There are no writes involved besides that, it's only used for a SELECT checking if a key exists, which always hits the index for the VARCHAR column.

The problem is, this table has to be consulted for every single operation in all my systems, all the time, because if there's a match, which is very rare, the operation must be aborted immediately. From my application profiling we spend around 10% of the database time reading from it.

I'm considering using the Memory engine for this table. The idea is to create a base table with the CSV engine that just loads the csv file instead of the whole data importing operation, and a init script to populate the Memory table. I'm assuming the HASH index on the VARCHAR column would be faster for simple lookups, but I'm not sure if it performs well with the almost 100% miss ratio I have.

Is this a good idea to improve my lookup speed?

I would not recommend the MEMORY storage engine
REASON #1 : No Redundancy
Whether you have a server crash or a normal system shutdown, all the data in the MEMORY table are lost. All you would have is the table structure.
REASON #2 : Mild Disk I/O
No matter what Storage Engine you choose, the .frm of a table is always accessed to check for table existence and availability. This will incur some disk I/O for this check.
Please read past posts on the pros and cons of the MEMORY Storage Engine

May 22, 2011 : I am using the MEMORY storage engine but MySQL still writes to my disk...Why?
Sep 26, 2011 : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
Jan 17, 2012 : Mysql Memory table getting many locks
Jan 20, 2012 : How much memory will a MEMORY table take up?

RECOMMENDATION
Given the two reasons for not using the MEMORY Storage Engine, I would recommend the MyISAM Storage Engine over using MEMORY or InnoDB. Why?
Looking back at Reason #1, you can have everything in RAM and have data redundancy on disk if you create the table as follows:
STEP 01) Create the table like this:
CREATE TABLE blacklist
(
    url VARCHAR(20),
    dt DATETIME,
    PRIMARY KEY (url)
) ENGINE=MyISAM ROW_FORMAT=Fixed;

STEP 02) Create a dedicated 16MB MyISAM cache for that table:
cd /var/lib/mysql
echo "SET GLOBAL blacklist_keycache.key_buffer_size = 1024 * 1024 * 16;" > init-file.sql
echo "CACHE INDEX blacklist IN blacklist_keycache; >> init-file.sql
echo "LOAD INDEX INTO CACHE blacklist; >> init-file.sql

STEP 03) Add this to /etc/my.cnf
[mysqld]
init-file=/var/lib/mysql/init-file/sql

STEP 04) Restart MySQL
service mysql restart

That's it.
Going forward, every reload of the table will populate the dedicated key cache. Please note the ROW_FORMAT=Fixed clause. What that does is speed up character search 20-25% (I wrote about this before).
Why not use InnoDB?

The data and index pages would have the data twice in RAM.
Accessing an InnoDB table introduces additional mild disk I/O via data dictionary checking (See pictorial representation of ibdata1)
Index pages to rotate out is the InnoDB Buffer Pool is too small. Contrariwise, an InnoDB Buffer Pool too big wastes RAM.

Using MyISAM, the data remains on disk but is exclusively accessed from the dedicate key cache.


'You cannot add or change a record because a related record is required in table 'Booking'.' error when removing a PKFK value in a SQL database in C# System.Data.OleDb.OleDbException: 'You cannot add or change a record because a related record is required in table 'Booking'.'

My sub-routine to get rid of the booking with a given BookingID in the Textbox 'TxbID' is given by:
Cmd.CommandText = "SELECT BookingID, Occupied FROM Room WHERE Room.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "DELETE FROM Booking WHERE Booking.BookingID = '" + TxbID.Text + "'";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "DELETE FROM Occupant WHERE Occupant.CustomerID = '" + CID + "'";

My table creation code is:
Cmd.CommandText = "CREATE TABLE Occupant(CustomerName VARCHAR, CustomerNumber CHAR(11), CustomerID VARCHAR, PRIMARY KEY (CustomerID))";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "CREATE TABLE Booking(BookingID VARCHAR, DateIn DATE, DateOut DATE, TotalPrice FLOAT, CustomerName VARCHAR, CustomerID VARCHAR, FOREIGN KEY(CustomerID) REFERENCES Occupant(CustomerID), PRIMARY KEY (BookingID))";
Cmd.ExecuteNonQuery();
Cmd.CommandText = "CREATE TABLE Room(BookingID VARCHAR, RoomNumber VARCHAR, RoomSize VARCHAR, RoomPrice FLOAT, Occupied BIT, PRIMARY KEY (RoomNumber), FOREIGN KEY(BookingID) REFERENCES Booking(BookingID))";
Cmd.ExecuteNonQuery();

I'm working on my computer science A-Level project so help is very appreciated.
Edit:
This function only allows number 0-9 to be input so injection shouldn't be an issue here? (The booking ID is an integer)
private void TxbID_TextChanged(object sender, EventArgs e)
{
     if (System.Text.RegularExpressions.Regex.IsMatch(TxbID.Text, "[^0-9]"))
     {
          MessageBox.Show("Please enter only numbers.");
          TxbID.Text = TxbID.Text.Remove(TxbID.Text.Length - 1);
     }
 }

And UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "' is where my error occurs.
My goal is to be able to delete a booking from the database by deleting the row from Occupant and Booking. My Room table is filled with the information for each room along with a BookingID which I wanted to set blank when the booking is deleted. How do I go about that?

"UPDATE Room SET BookingID = '', Occupied = False WHERE Room.BookingID = '" + TxbID.Text + "'";

You don't have a Booking record with a BookingID of ''. I suspect you have your relationship the wrong way round; a Booking should have a RoomID rather than a room having a BookingID, or if you want to persist with the current architecture you should perhaps be setting Room.BookingID null
Please read this website, not because it solves your problem, but because you're making the most grave mistake anyone can make when writing database software, and you need to kick the habit now. (If you did it while working for my company, they'd just fire you)


How to find the latest SQL statements within the database? I like to get the latest executed statements within my database, along with performance indicators.

As such, I like to know, which SQL statements were most CPU/DISK intensive.

Here is the SQL to do the job. Open for trial.

Step 1: Determine the installatin IDs & user IDs.

SELECT inst_id,sid FROM gv$session WHERE username='<ENTER-USERNAME>';

Step 2:

SELECT
      s.sid
     ,s.CLIENT_INFO
     ,s.MACHINE
     ,s.PROGRAM
     ,s.TYPE
     ,s.logon_time
     ,s.osuser
     ,sq.sorts
     ,sq.DISK_READS
     ,sq.BUFFER_GETS
     ,sq.ROWS_PROCESSED
     ,sq.SQLTYPE
     ,sq.SQL_TEXT
 FROM gv$session s
    , gv$sql sq
WHERE s.SQL_HASH_VALUE = sq.HASH_VALUE
  AND s.inst_id = :inst_id -- replace with instID from above
  AND s.sid = :sid -- replace with ID from above
  AND sq.inst_id = s.inst_id

There might be multiple Ids & instance Ids returned. So it's up to a users' choice on how to use this data in a web interface etc.


Uniquely Append to Array on Conflict PostgreSQL I've got two columns in PostgreSQL, hostname and ip. Hostname is the primary key and ip is an array of IPs.
I want to be able to insert IPs for a give hostname, on conflict I want to append to the array with the data I'm trying to insert and the output data should be unique.

e.g

hostname - ip

test.com   {1.1.1.1,2.2.2.2}

Input

I attempt to insert...

test.com {1.1.1.1, 3.3.3.3, 4.4.4.4}

Output

test.com {1.1.1.1, 2.2.2.2 ,3.3.3.3, 4.4.4.4}

insert into auto_scope_pre_proc (hostname,ip) values ('test.com', '{10.10.10.10, 10.10.10.22}'::inet[])
on conflict (hostname) do update
SET ip = array_append(auto_scope_pre_proc.ip,ip);

The function array_append does not filter duplicates entries.
Here is a possible solution:

drop table auto_scope_pre_proc;
DROP TABLE
create table auto_scope_pre_proc
(
  hostname varchar(20) primary key,
  ip inet[]
);
CREATE TABLE
insert into auto_scope_pre_proc (hostname,ip) values ('test.com', '{1.1.1.1, 2.2.2.2}'::inet[])
on conflict (hostname) do update
set ip = array( select distinct unnest(auto_scope_pre_proc.ip || excluded.ip));
INSERT 0 1
select * from auto_scope_pre_proc;
 hostname |        ip
----------+-------------------
 test.com | {1.1.1.1,2.2.2.2}
(1 row)

insert into auto_scope_pre_proc (hostname,ip) values ('test.com', '{1.1.1.1, 3.3.3.3, 4.4.4.4}'::inet[])
on conflict (hostname) do update
set ip = array (select distinct unnest(auto_scope_pre_proc.ip || excluded.ip));
INSERT 0 1
select * from auto_scope_pre_proc;
 hostname |                ip
----------+-----------------------------------
 test.com | {4.4.4.4,2.2.2.2,3.3.3.3,1.1.1.1}
(1 row)


Reindex or Analyze Tables on PostgreSQL after import I used PostgreSQL 9.5 on a server and I ended up migrating the data to a new server using PosgreSQL 12.
Should I perform a REINDEX for all tables or just ANALYZE for all tables?

If you use pg_dump/pg_restore, then all indexes are created during pg_restore (after the data was loaded), so there is no reason to run reindex.
pg_restore however, does not import any statistics, so running analyze directly after running pg_restore is a very good idea.


mysql high cpu usage (no have slow queries) I have a problem with mysql. MYSQL High CPU Usage (300%-400%). But I cannot find solution to handle it. I optimized config mysql.
I think maybe my server has a problem, but What do I need to do to find the problem and how to fix it.
Infomation:

Server not have slow queries
SHOW FULL PROCESSLIST; => Only 7 connection (not lock).
show engine innodb status => ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
5 read views open inside InnoDB
Process ID=25898, Main thread ID=140213805094656, state: sleeping
Number of rows inserted 16646, updated 89843, deleted 48, read 45381116563
0.50 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2140759.81 reads/s

mysqld.log have so many line as (it happens continuously once every second):

Access denied for user 'test'@'139.198.168.221' (using password: YES)
Access denied for user 'root'@'139.198.168.221' (using password: YES)
Access denied for user 'ODBC'@'218.59.174.83' (using password: YES)
Access denied for user 'mysqld'@'218.59.174.83' (using password: NO)
Access denied for user 'root'@'180.215.129.37' (using password: NO)
Access denied for user 'server'@'112.121.189.218' (using password: NO)
[Warning] Host name 'zg-0416b-112.stretchoid.com' could not be resolved: Name or service not known
[Warning] Host name 'zg-0416a-245.stretchoid.com' could not be resolved: Name or service not known

Somebody is trying to hack your server using bots (all these log entries are login attempts). To be honest I see the same atempts in my logs all the time. To check if this is the source of your server overload (I am not sure...) try to restrict network connections to mySQL: uncomment this line in your mysqld.cnf
bind-address    = 127.0.0.1

Then restart your mySQL and check the load.


Default SQL Server 2008 locking behaviour I am using SQL Server 2008.

What is the default lock behaviour with UPDATE?

NOLOCK?
ROWLOCK?
PAGLOCK?

How can I tell what the current level of lock is for a table?

Thanks,
Bruce

It really depends on how much you're updating. Locks will escalate as the size of the query increases. If many rows within the same page will be modified, SQL Server will escalate to a PAGLOCK. If many pages will be modified, it will escalate to a TABLOCK.


CTE placement of Where clause Is there a difference performance wise between these 2 examples:
WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1
      WHERE Col3 = '1'
    )

select * from CTE1

WITH CTE1
    AS (
      SELECT
        Col1,
        Col2,
        Col3
      FROM dbo.Table1
    )

select * from CTE1
  WHERE Col3 = '1'

We're using a CTE as a subselect within some dynamic sql and can't apply the where clause until the 'select from CTE' happens.  Our actual query is much more complicated than this.  But, I'm wondering if it will be more performant to not use a CTE.  Will the second example actually pull all of the rows from the table and then apply filtering?  Or are these functionally equivalent?  We use SQL Server.

Both queries have the same execution plan. You can check that in SQL Server Management Studio by typing:
WITH CTE1 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
WHERE Col3 = '1' )
SELECT * from CTE1

;WITH CTE2 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
)
SELECT * from CTE2 WHERE Col3 = '1'

Regarding the second part of your question: it's quite hard to say without looking into actual code and understanding what you are trying to achieve, however you may find that using temporary table may be way more efficient than CTE.


Is (and if how) it possible in Oracle to "insert into  values " if  has virtual columns Here's a table

create table tq84_virtual_test_without (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 number
);

with the rule that col_5's value is the sum of the other four columns.

So the table is filled accordingly:

insert into tq84_virtual_test_without values( 1, 2, 3, 4, 10);
insert into tq84_virtual_test_without values( 3, 8, 7, 5, 23);

commit;

Now, say, that there is a need to copy one row and change just one column's value. This can of course be done quite elegantly (imho, that is) with a rowtype-variable, like so

declare
  r tq84_virtual_test_without%rowtype;
begin

  select * into r from tq84_virtual_test_without where col_2 = 8;

  r.col_4 := r.col_4 - 2;
  r.col_5 := r.col_5 - 2;

  insert into tq84_virtual_test_without values r;

end;
/

This is elegant because it doesn't clutter the source code with insert into ... (col_1, col_2...) values (.., ..) statements and I'd like to keep this feature, if possible.

On the other hand, col_5 is a perfact candidate for a virtual column. So, here's almost the same thing, but with col_5 being a virtual column:

create table tq84_virtual_test_with (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 as (col_1 + col_2 + col_3 + col_4) virtual
);

insert into tq84_virtual_test_with values( 1, 2, 3, 4, default);
insert into tq84_virtual_test_with values( 3, 8, 7, 5, default);

commit;

Now, and this is unfortunate, the following construct doesn't work anymore:

declare
  r tq84_virtual_test_with%rowtype;
begin

  select * into r from tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

--
--  ORA-54013: INSERT operation disallowed on virtual columns
--
  insert into tq84_virtual_test_with values r;

end;
/

So, is this still somehow possible (and if so, how) to use this rowtype-variable along with virtual columns?

Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works:

create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );

declare
  r v_tq84_virtual_test_with%rowtype;
begin

  select * into r from v_tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

  insert into v_tq84_virtual_test_with values r;

end;
/

As far as I can tell, this is the only way to workaround your requirement to use %rowtype.


How should I update ID field values for all records based on previous values? I need a SQL query that can update ID fields for all records in a table from their previous values to a new value based on that previous value.

For example, if the ID is 1, I want to change it to 9991 - prepending '999' to every ID in the table.

What is the most efficient means of doing this?

A couple options come to mind.  If you have a charish data type:

update table set column=concat('999',column);

If you have an integer type find the max value, let's say it's under 10,000 then

update table set column=9990000+column


Size of the ldf file increasing Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

I have some sql server database for which recovery model is set to full.Now for some databases the ldf size has become large.I tried taking log backup regularly,but i didn’t find decrease in physical size of ldf. I followed what stan20 has told in this link http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/704bcfbc-9251-4f4e-9a80-04bc7fe9ae78
But it only reduced logical file size. So how i can mange size of ldf file?

Backing up the transaction log will not shrink the physical file.  It will only mark transactions as inactive and potentially mark virtual log file(s) for reuse.  What you're looking to do is actually shrink the file system file, and this can be done through DBCC SHRINKFILE.

Note: shrinking the transaction log, or any other database file for that matter, should not be a routine database task.  Proper sizing will mitigate the necessity for tasks like this.


Backup strategies for zero data loss I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state.

I've gone through two options:

1) Mirroring (to be deprecated in future versions)

2) AlwaysOn Availability Groups

Neither looks like a perfect fit in my scenario because mirroring would be obsolete soon and Availability Groups are only available in Enterprise Edition of SQL server. We are only having BI edition.

Are there any good options apart from these? We are ready to spend money even for 3rd party tools, but can't afford Enterprise Edition at this moment.

There is always a balance. You can get close to zero data loss with the right restore strategy and none of this additional technology - which are more geared to HA than DR. I've stopped calling this a backup strategy, because the key takeaway from any such plan is that you need to test that you can restore your backups, not just that they're running successfully.

Since you can afford a day of downtime, you can take your time retrieving your full and log backups from wherever you store them and restoring them on a new server. With FULL recovery, and taking a log backup every 5 minutes, you should be able to guarantee that you will never lose more than 5 minutes of data (unless, of course, you backup to the local disk and the disk explodes - best practice is to get your backups as far away from the primary server as possible, as soon as possible). You can tighten that window up to three minutes, two minutes, one minute, etc., but backing up the log too frequently can cause a few ripple effects - most notably on your I/O subsystem, your SQL Server Agent history, and of course the complexity of restoring to point-in-time.

I also wouldn't be afraid to use mirroring in addition to a proper backup/restore methodology (but certainly not instead of), to get you as close to zero data loss as possible. Just because it's deprecated doesn't mean you have to worry about it being unavailable "soon." Deprecation announcements are typically three releases out... plenty of time in 6-9 years to come up with an alternative, even if you are staying on top of every new release, which most people don't, and even if this application is still in service, which it may not be. There are a lot of folks still running 2000 and 2005; do you think they're worried about deprecated or discontinued features in current versions?

Besides, Microsoft will most certainly have had to come up with an alternative for Standard / BI Edition customers by the time they actually remove mirroring from the product. I know they have been told loud and clear, from the moment they announced this deprecation, that they need to have a contingency plan for non-Enterprise customers. My guess, with absolutely no internal knowledge on the subject, is that at some point they will introduce some extremely limited form of Availability Groups to Standard Edition, much like they did with backup compression in 2008 R2, which was previously only available in Enterprise.


Fetch rows which belongs to a certain geo location I've a table (locations) which contains records as given below:

id |     lat   |     lng   | radius_in_km
-----------------------------------------
1  | -18.17943 | 83.968051 |     5
2  | 44.5796   | 7.069637  |     10

Now, I've got a point (lat, lng) from Google and I need to fetch the records from locations table if the given lat, lng falls into radius of those rows. Please notice, the radius is dynamic/different for each row so I need to match the records according to their radius with the given point.

I've searched the stackoverflow network but couldn't find anything that works for me, any hints would be fine even not the code.

Keeping lat long like that isn't spatial. You need to make a point

Point(long,lat)

I would store the result of the above on the row, rather than individual lat/long cords. Regardless of what you store on the row, that's what you have to work with. Not storing that on the row will just be slower, as you'll have to calculate it every time.

Next what you want is the Bounding Box test. In MySQL they're designated with an MBR prefix.

SELECT *
FROM table
WHERE ST_Contains(
  ST_Buffer( Point(lng,lat), DISTANCE_IN_DEGREES ),
  Point(google.long, google.lat)
);

You'll have to calculate DISTANCE_IN_DEGREES yourself. MySQL doesn't have a geography type, so you can't just use a fixed distance like meters, miles, or feet. You have to do the math yourself. This is a lot of work in itself.

ST_Contains doesn't use an index. For that you'll need MBRContains. Or you could just use PostgreSQL, for that it'll look like this,

SELECT *
FROM table
WHERE ST_DWithin(
  ST_Buffer( ST_MakePoint(lng,lat)::geography, DISTANCE_IN_METERS ),
  ST_MakePoint(google.long, google.lat)::geography
);

Not just is that shorter, but from the docs

  This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

So you don't have to use two functions like you would in MySQL MBRcontains (bounding box w/ index), and ST_Contains.


Effect of Oracle on MySQL While the future of MySQL is unknown, I haven't had time to keep up with the known effects that Oracle has had on MySQL since it was bought from SUN.

Have there been changes of note, either by Oracle (or the MySQL community) since the deal became public?

Obligatory disclosure: I have worked for MySQL AB/Sun Microsystems & Percona (I never worked for Oracle).  I'm now independent, and have no shares/remaining stake-holdings in any. I do however acknowledge that I have a stake holding in MySQL succeeding in general, as I have invested time in a specific skill-set.

It used to upset me how features were decided at MySQL...  How was it decided that partitioning was a critical feature for 5.1, but backup totally missed the radar?

There seemed to be a bunch of low hanging fruit (years old bugs) that were not being addressed, and I was always cynical that unless it could check off a box on a features grid, it would never be handled.

There was a bit of talk, but no indication it was any better under Sun's management.  However, now that Oracle is in control, several years old bugs are being addressed, performance has become a feature, and I actually find really compelling reasons to upgrade to 5.5 and 5.6.

I feel awkward having to defend one of the world's biggest software companies, but they're really not getting enough praise.  Instead everyone is making claims they are somehow screwing the project.  Most of the projects they 'screwed' made no commercial sense to them... however they make a non trivial amount of money on OEM licenses and subscriptions/professional services for MySQL.


How can I correctly use the timezone format specifier in a TO_DATE call in Oracle I am trying to obtain a DATE object with the following statement:

TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD HH24:MI:SS TZR')

and

TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD TZH:TZM:SS TZR')

But in both cases, it is telling me: ORA-01821: date format not recognized.

I got the TZR format specifier from https://www.techonthenet.com/oracle/functions/to_date.php, namely:

TZD     Daylight savings information. For example, 'PST'
TZH     Time zone hour.
TZM     Time zone minute.
TZR     Time zone region.

What is the correct way to specify a date/time with a time-zone?

Datetime Format Models

  TZR - Time zone region information. The value must be one of the time
  zone region names supported in the database. Valid in timestamp and
  interval formats, but not in DATE formats.

select to_timestamp_tz
  (
    '1970-01-01 00:00:00 UTC',
    'YYYY-MM-DD HH24:MI:SS TZR'
  )
  as result
from dual;

RESULT
----------------------------------------
01-JAN-70 12.00.00.000000000 AM UTC

If you want a DATE type, you need to convert it:

select cast(to_timestamp_tz
  (
    '1970-01-01 00:00:00 UTC',
    'YYYY-MM-DD HH24:MI:SS TZR'
  ) as date)
  as result
from dual;

RESULT
----------------------------------------
01-JAN-70


Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade I (successfully) followed the upgrade procedure in  §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old tablepaces (some of which are user-defined tablespaces) and other legacy files to reclaim space not needed from the old cluster.

There are two approaches I’ve considered:

Connect to the old database cluster as postgres and run queries against the pg_catalog system tables to DROP tables, tablespaces, databases, etc.
Run find -L /path/to/tablespace/root -type d -user postgres -iname “PG_10_*” exec rm -rf {} \; to remove directories in the tablespace locations (note the new tablespaces share the same root directory as the old ones)

After either (1) or (2), I’d remove the old PGDATA directory.

Is there a preferred approach (including any methods not mentioned)?

Note, this is distinct from the following post: How do I uninstall the old version of Postgres on CentOS 7

One thing to be very careful about here is whether you’ve done a pg_dump+pg_restore or a pg_upgrade.

It sounds like you’ve done a pg_dump+pg_restore based on the docs section you’ve linked to.

In that case it should be safe to completely remove the old data directory, including table spaces, as you have a full copy of the data in the data directory of the new cluster.

If you have used pg_upgrade that is not safe to do (neither are the two methods you’ve described), because some of the files are hard-linked from the old to the new cluster.

Edit: As pointed out in a comment, the latter is only true if you have used the “--link” option for pg_upgrade.


Setting up AG Groups with Current Failover Cluster Environment

Windows: 2012 R2 Standard
SQL: SQL 2014 Enterprise Edition
Setup: Two Node Windows Failover cluster with SAN

Changes:

We want to introduce Availability Group to the instance by adding a second SQL instance running at a DR site. It will be Virtual server with attached drive. For Clarity, we will call SQL instance running on two node cluster with SAN as S1 and second SQL instance at DR site as S2

Questions:

Can we create AG groups after Adding S2 to S1 cluster? In the event of S1 failure and it failsover to Node 2, would AGs failover to S2 instance?
Start fresh by creating two instances on new servers setup with AG and then migrate DBs to them?
Licensing - Do we have to pay licensing for both Primary and Secondary instance even though everything will be running on Primary and will failover in the event if Primary goes downs?

Thanks!

For your first question, yes you can use the same environment (Windows: 2012 R2 Standard and SQL 2014 Enterprise Edition) but the configuration should be changed. During failover clustering, primary and secondary servers shares the same resources or drives, this didn’t work for Always on configuration, and both primary and secondary replicas has to have their own drives.

For your second question, it depends on your preference and available resources, if you think that it’s easy just to reset drives on both servers and change configuration you could do that. If you have sufficient resources, space in SAN and feels more confident to start a fresh install you might chose the second option.

Concerning about the licensing. Assuming you are planning to use secondary node only for disaster recovery or short maintenance time, you need licensing only for a primary node. However, if you are planning to query from secondary node, you are also required a license for the secondary node too.


Can a sublass have two parents (two entity types that are connected with it?) I'm kind of new to ERDs and stuff, just wondering if I'm correct when my (conceptual) entity relationship diagram has two entity types connected to one subclass (so the subclass inherits data from both of it).

My question here would be: For each entity type connecting to this sublass, does it have its own instance of it? So: Will my room entity type record if its a standard or executive room, and will the booking entity type be able to record if that specific booking was to have decorations or not?

Sorry, I'm struggling to word this question and I'm sure a few will be confused, but I appreciate any help on the matter.

"Can a subclass have 2 parents(2 entities that connect to it?)".  Yes.  However: Avoid redundant relationships in your ERDs.  For your particular scenario: if the BOOKING entity is connected to STANDARD/EXECUTIVE via ROOM, then you will later be able to write a query whose output tells the user what type of room has been booked.

Regarding your questions:
"For each entity connecting to this sublass, does it have its own instance of it?  So: Will my room entity record if its a standard or executive room, and will the booking entity be able to record if that specific booking was to have decorations or not?"
ANSWER: Yes, a ROOM can have attributes that determine whether it is a "standard" or "executive" (see the model below).  A booking does not change a room's "decorations".

It would be sufficient to model ROOM -< BOOKING (each room may have one or more bookings, same as you have done).  As for the "room types", I would use an ARC ie an exclusive OR (XOR).  The reasoning behind it: each room is either equipped as STANDARD room (with all the necessary attributes) or it has the EXECUTIVE "package" (including all attributes that you need).

There is NO need to draw (a) relationship(s) between STANDARD/EXECUTIVE and BOOKING.
A ROOM gets booked (and each room is either a STANDARD or an EXECUTIVE, but this gets recorded in the respective attribute/column -> see the diagrams (ERD above, relational model below).

If it is possible that a single (unique) booking ID can be used to book several rooms, then you'd have a M:M relationship between ROOM and BOOKING.  (Which would require an intersection.)

(As for the implementation of the XOR - which comes much much later: you can use a CHECK constraint in the ROOMS table in order to force the room to be either "standard" or "executive".)


ORDER placement for trigger in t-sql I'm using Sybase ASE version 16.0.0.1915, (follows t-sql syntax) and trying to create a new trigger. This new trigger emulates another existing trigger, however is slightly modified. It would be a whole lot harder to modify the existing trigger to address both "workflows", so copying into a new trigger with modified details and an order of "2" would be my preferred solution.
I'm reading in the docs, that in order to create a new trigger based on the same action, I need to set the order for the triggers to fire off. If no order is specified, it is assigned order of 0 (first).
Docs
So here is my trigger, the existing similar trigger does not have an order set. When I place "ORDER 2" into the create statement (after the name, before "on" in row 1), I keep getting syntax error -131.
I've also tried placing the "ORDER 2" after the table name, before "for", and after "insert", before "as". All producing the same error -131.
So what am I doing wrong? Is there some other problem with this trigger preventing me to use the ORDER clause?
create trigger "DBA"."WKM_autoFillCL143" on
"DBA"."case_checklist" for insert
as
if((select top 1 "inserted"."code" from "inserted") in( '143' ) )
  begin
    declare @parentRef integer,@desc varchar(255),@desc1 varchar(255),@checkID integer
    set @parentRef = (select top 1 "parent_ref" from "inserted")
    if(@parentRef <> '0')
      begin
        set @desc = (select "description" from "case_checklist" where "checklist_id" = @parentRef)
        if(@desc is not null)
          begin
            set @checkID = (select top 1 "checklist_id" from "inserted")
            update "WKM_RecordChecklistMapping" set "c143" = @checkID where "c142" = @parentRef
            declare @tabid integer
            set @tabid = (select top 1 "tab_id" from "WKM_recordChecklistMapping" where "c142" = @parentRef)
            set @tabid = (select top 1 "tab_id" from "user_tab2_data" where "tab_id" = @tabid)
            if(@tabid is not null)
              begin
                declare @recProvider varchar(255),@recsRequested varchar(255),@dateFrom "datetime",@dateTo "datetime"
                set @recProvider = (select top 1 "Provider_Name" from "user_tab2_data" where "tab_id" = @tabid)
                set @recsRequested = (select top 1 "Records_Requested" from "user_tab2_data" where "tab_id" = @tabid)
                set @dateFrom = (select top 1 "For_Dates_From" from "user_tab2_data" where "tab_id" = @tabid)
                set @dateTo = (select top 1 "Through" from "user_tab2_data" where "tab_id" = @tabid)
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+"coalesce"(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+"coalesce"(convert(varchar(255),@dateTo,1),'00/00/00')
                set @checkID = (select top 1 "checklist_id" from "inserted")
                update "case_checklist" set "description" = @desc1,"staff_assigned" = 'ZKS',"due_date" = ("today"()+7) where "checklist_id" = @checkID
              end
          end
      end
  end

There are 4x different RDBMS products under the Sybase name ... Adaptiver Server Enterprise (ASE), SQLAnywhere, IQ and Advantage.  The 4x products do not share a common SQL language/syntax.
The question references a link for the create trigger syntax in ASE.
The sample code in the question appears to also be for ASE.
However, from the comments we've discovered the OP is actually using the SQLAnywhere product (version 16.0.0.1915).  And while SQLAnywhere does provide some half-workable support for ASE's T-SQL dialect, it does not include a common syntax for the create trigger command.
From the comments: OP has located the documentation for SQLAnyhere's create trigger command.
Using one of the examples from that link (scroll down to find CREATE TRIGGER myTrig), and modifying OP's current trigger code to match the documentation, I'm thinking OP's looking for something like:
create trigger "DBA"."WKM_autoFillCL143"
after insert order 4 on "DBA"."case_checklist"
REFERENCING NEW AS inserted
FOR EACH STATEMENT
BEGIN
....

NOTES:

I work primarily on ASE, and my SQLAnywhere experience is rather limited, so OP may need to tweak the above to work correctly in SQLAnywhere; also ...
OP should review the various options available via SQLAnywhere's create trigger command (eg, should the trigger fire before or after the insert?)
OP will need to review the rest of the current (ASE/T-SQL) trigger code to see which parts can be used 'as is' in SQLAnywhere and which parts will need to be rewritten to match SQLAnywhere's syntax
OP will also want to verify SQLAnywhere's support for quoted identifiers (ie, does the current use of double-quoted identifiers meet SQLAnywhere's syntax?)


MySQL Slaves lag behind master I have one master and four slaves. Sometimes all my slaves lag behind the master. I have implemented the heartbeat for monitoring replication lag. Now I am trying to find why the slaves are lagging behind the master.

I saw the slow queries (for today) on the master and I found that the slowest query (DML) was taking 138 seconds. But the slaves were lagging about 1400 seconds and there were also no slow queries on the slaves for DML (update, delete, insert, etc.).

Points to be taken into consideration:

All tables are InnoDB.
68 GB of RAM (Master as well as slaves).
Data size about 1 TB.
Master and slave are running from a long.

What may be the reason for lag?

The Problem

This problem normally  happens because on master, you allow a poll of connections(threads) which run commands at same time (max_connections), but on the slave (on versions before 5.6) you have just 1 thread executing commands from binlog, lets say you have a big table, and you run an optimize or alter table and this command took 3 minutes on master, after execute it on master, it will be logged on binary log and relay to slave, when the slave pick this command from his log, it will start to run this command and any other query from master will need to wait until this command.

Solution 1

when you start to see the seconds behind master increase, try to look in the SHOW PROCESSLIST on the slave serve to see which query is been executed at that time

Solution 2

when you start to see the seconds behind master increase, try to look in the SHOW SLAVE STATUS which position of the binary log is been executed, and use the mysqlbinlog to find the command.

Solution 3

Update to MySQL 5.6 and start to make use of multi-threaded slave
a good start is to read this article: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html


TLS1.0 disabled and 3rd party OLE DB use I'm provisioning a new server as part of which we have disabled TLS 1.0 and installed SQL Server Express 2016. I've then attempted to install a 3rd party application that is going to install a website and supporting DB etc. The first thing it does is test the connection to SQL and it returns the following error:

  Connection Problem: [DBNETLIB][ConnectionOpen
  (SECDoClientHandshake()).]SSL Security error

To attempt to work out the issue I created a UDL file on my desktop and reproduced this error when using the Provider Microsoft OLE DB Provider for SQL Server.  The test worked fine for provider SQL Server Native Client 11.0 and I can also connect with SSMS.

Is there anyway I can force the 3rd party installer to use a different provider?
Is there anyway I can log what type of connection it is attempting?

See Does Microsoft OLE DB Provider for SQL Server support TLS 1.2 and the authoritative answer from Dan Guzman.  He references a newer version of OLEDB that supports TLS 1.2, which can be downloaded at Microsoft® OLE DB Driver 18 for SQL Server®

And to specifically answer your questions, there is no way for us to know if the installer can be forced to use a different provider.  It's certainly possible if it uses a connection string template that can be modified, but based on my experience I would say it's highly unlikely you'll be able to modify the installer to use a different provider.

As for logging what type of connection--see if the SQL Server error log contains any information when the failure occurs.  You can also set up an extended event in SQL Server to capture information about logins (there is "Connection Tracking" template).  The client_app_name field collected by "Connection Tracking" template will include the information that the client passes about the provider.  This can sometimes be blank, though, as it depends on the client to set that information.  Typically a provider will have that information included automatically unless the developer does something to override it.


MySQL table relations, inheritance or not? Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping.

I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content stored in database. Blog page would have page_url, but no page_content. Blog would have Posts, Categories...

Lets say I have route like this:

/{pageurl}

This is page, with page url that can be home, or news, or blog... That page can be either Static page, and then I would joust print page_content. But it can also be Blog Page, and then I would print latest posts as content.

How should I relate these Static Page and Blog Page tables? Is this inheritance, since both are pages, with their URL, but they have different content? Should I use inheritance, so that both Static and Blog page extends Page that would have page_url? Or should I made another table page_types and there store information about available page types?

I would recommend that you have a Pages table that contains both Static, Blog and any new pages you may have. The Pages table would have the columns page_url, page_content and pagetype (a tiny integer) which will help you differentiate between different pages. Later u may end up with other page types which have content hence leaving page content as a field in the pages table and may also allow you to have custom PHP or markup content in a blog page for customization (thinking aloud)

You can then have a BlogPages table which has the information for blog pages linking to the categories and posts.


Oracle not trusting statistics after an import? We are seing some strange behavior in the way Oracle is handling statistics of an imported schema.
We build a schema on the dev system, analyze if and import into the production with the stats.
For the most part everything works fine, but some query seems to select different plans than in dev.  After re-analyzing them in production, the plan becomes the same as on dev.  The schema is not changed on prod (pretty much read-only).  So there must be some kind of difference between how Oracle handles stats built locally vs imported?

A number of factors can change on import.

Fill factor for data blocks is likely more optimal.  Partially filled data blocks from the development system will be filled on import. This can change over time if data is updated and added, but does not appear to be a factor in this case.
Changes in block placement of rows may alter the number of block required to retrieve by index.  This can have positive or negative impacts on the data.
Cost parameter for data and or index blocks may be different on the two databases resulting in different cost weights for plans.
Block sizes may be different on the two databases resulting in different costs.
Newer versions of Oracle will recalculate statistics automatically as data changes.  The exported statistics are normally a subset of the full statistics.  There is a DBA_STATS package which can be used to export and import the full set of statistics.
Histogram sizes may be different on the two systems resulting in differences in level of detail.  (Its been a while since I tuned this kind of thing.)  If the histograms are missing, Oracle will sample the data and estimate.  Different sample sizes can cause very different behavior.
Oracle may decide to use bitmap indexes in some cases.  This can require regenerating the indexes on each query.  You can add bitmap indexes for the required columns.  This option can be disabled at the database level (it may be an undocumented setting).

Try generating cost statistics for the queries you are using on both the Development and Production systems.  You may also want to examine the Statistics on both systems.

You may get more consistent results using transportable tablespaces for the tables and indexes.  This will eliminate the import/export step.

EDIT: Added bitmap, statistics, and histogram info above.

It is possible to just run an explain plan without running the queries.  There are various tools which assist in this.  I have used Aqua Data Studio, but SQL Developer should work as well.  For parsed queries (running or not) there is a view available from which the plan can be extracted.

There are available views which can be used to view the current statistics for a table.  Comparing used blocks on both systems will give you a quick idea how much statistics should have changed.  Histogram data is can result in different plans.