How to take SQL Server database under this scenario? I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB.

How can I take a full backup in this scenario? How can I split full backup into different files in this ratio?

Please suggest a method to do this.

If you have access to another server on your network with larger disks, you can do database backup to a network share:

backup database YOURDATABASE
to disk = '\\NETWORKSERVER\SHARENAME\YOURDATABASE_yyyymmdd.bak'
with init

For this to work, the share would need to have Write permissions from the domain account under which your SQL Server is running.

If you don't have access to another server with enough disk space, you may want to get an inexpensive Network Attached Storage type of device (e.g. synology) and perform your backups to it.


Query a database specific configuration parameter I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this:

ALTER DATABASE mydatabase SET default_transaction_read_only = false;

Which works great. However, occasionally I get into a state where I'm not sure which databases are read only and which aren't. How do I query this parameter? I know how to query it server-wide:

SHOW default_transaction_read_only;

But not at a database specific level.

These settings are stored in the pg_catalog.pg_db_role_setting system catalog.  You can search for the databases that have the above setting like

SELECT datname
  FROM (SELECT datname, unnest(setconfig) AS setting
          FROM pg_db_role_setting
          JOIN pg_database AS d ON setdatabase = d.oid) AS t
 WHERE setting = 'default_transaction_read_only=false';


How to optimize mysql table of 2 billions rows? We have a table for logging actions. The volume added up pretty fast. After about 3-4 months, we got 600M rows, we need to store the data for 1 year at least, so it is expected to host 2-3 billions rows.

The structure is not very complicated:

    CREATE TABLE `site_statistic` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `entityType` varchar(50) NOT NULL,
     `entityId` int(10) unsigned NOT NULL,
     `entityCount` int(10) unsigned NOT NULL DEFAULT '1',
     `timeStamp` int(10) unsigned NOT NULL,
     PRIMARY KEY (`id`),
     KEY `entity` (`entityType`,`timeStamp`,`entityId`)
    ) ENGINE=MyISAM CHARSET=utf8

Sample data:

when a user logs in:
entityType = "user.login" , entityId = that user Id , entityCount= 1 most of the time
when a product is added:
entityType = "product.add" , entityId = that productId , entityCount= 1
when a product is clicked:
entityType = "product.clicked" , entityId = that productId , entityCount= 1
when an album is liked:
entityType = "album .liked" , entityId = that album id..
so on and so forth.

Currently, data insert is not a problem (yet). But reading the data is very slow. It took 90+ seconds to query product activities for a given period of time:

   SELECT
            `entityType`,
            DATE_FORMAT(FROM_UNIXTIME(`timeStamp`), "%e") AS `category`,
            SUM(`entityCount`) as `count`
        FROM
            `site_statistic`
        WHERE
            `entityType` IN ('product.displayed','product.clicked','product.add')
                AND
            `timeStamp` >= 1527267600
                AND
            `timeStamp` <= 1527872399 AND entityId = 12807
        GROUP BY
            `entityType`,
            `category`;

Mysql crashed (or rather not responding) from time to time when many statistic reports are required at the same time.

Deleting the data (related to a product for example) is also very slow. We need to delete to save space...

What can we  do to improve it, apart from disable real time report and send later by email?

P.s.: Our client can be considered small (few guys hosting a website with modest profit) not some crazy enterprise solution.

Use InnoDB.
Normalize entityType or turn it into an ENUM.
Change INT UNSIGNED (always 4 bytes) into a suitable sized int)
If the 3-column key is unique, then make it the PRIMARY KEY and toss id
id, if kept, will need to be BIGINT UNSIGNED -- you are getting too close to the 4 billion limit of INT UNSIGNED.
The optimal order for the index:  (entityId, entityType, timeStamp), or possibly (entityId, timeStamp)
Minimize the number of indexes on a DataWarehouse table.
For purging data after a year, DELETE will be painful.  Instead PARTITION BY RANGE and use months:  http://mysql.rjweb.org/doc.php/partitionmaint
If you get in trouble with the ingestion rate: http://mysql.rjweb.org/doc.php/staging_table
If you keep id, then do

this trick:

PRIMARY KEY (entityId, timeStamp, id)
INDEX(id)

This, together with InnoDB, makes the data for that SELECT mostly 'clustered', hence faster to fetch.

But the big fix to that query, and similar "report" queries in a Data Warehouse (which what you have), comes from building and maintaining Summary table(s):  http://mysql.rjweb.org/doc.php/summarytables

The summary table to support that would look something like:

CREATE TABLE summary (
    entityType VARCHAR... -- denormalized
    dy  -- timestamp truncated to the day
    entityId
    sumCount  -- SUM(entityCount); use SUM(sumCount) to get weekly total (etc)
    PRIMARY KEY(entityid, entityType, dy)

See the link for various ways to keep the summary table updated.

addenda

Munch on what I said.  Then think about how it splitting into multiple similar tables based on entityType.

One simplicity -- count is always "1" for some Types, so you don't need a count column (except in the Summary table for that Type).


How to Remedy Inconsistent MySQL Replication SYSDATE Value When using master-master/master-slave replication in a MySQL environment, there's a possibility of creating inconsistent data when SQL statements use the sysdate instead of a hardcoded time in their SQL statements.

The issue that could potentially crop up is that if replication is halted for whatever reason (network blip, rebooting one of the hosts, replication thread halted, etc.) is that the SQL that involves sysdate will be turned into a real time at the time of the SQL being applied to the database. If replication is halted for meaningful time (1 hour to 1 day), the 2 databases will no longer be entirely synchronous. A row with a timestamp may begin to escape application workflows based on date-time boundaries.

What potential workarounds/remedies are there for finding such rows and updating them accordingly as a post-step to restoring replication between the databases? I'm almost certain we could use Veridata but that would require quite a bit of overhead, and I'm looking for a more elegant/simple solution to the issue.

Virtually everything that is replicated is frozen before replication.  For example, RAND, NOW, UNIX_TIMESTAMP are all passed through the replication stream so that the Slave will have exactly what the Master has.

But... SYSDATE is an exception.  You should not use it if you want identical data.

Or...  "sysdate-is-now" is an option (either on the server commandline or via some setting).  This makes SYSDATE act like NOW.

What version are you running?  See http://bugs.mysql.com/bug.php?id=15101


How to change SQL Server Service Account using PS SQL Server runs under AD Account SQLAccount1. How to make it run under another account, SQLAccount2? How to do it un Powershell?

The GUI way to do this is to go to SQL Server Configuration Manager.  Go to the properties of the SQL Server and change the account name on the Log On tab.  Click the Browse button.

Than enter the account that you want to SQL Server Log on as.  Click Check Names, to make sure the account exists.  Than click OK on select user or group and OK on SQL Server Properties.

Than restart.

2.This blog Hey, Scripting Guy! Blog, the author Aaron Nelson - SQLvariant shows how to do this in PowerShell.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "WIN7NetBook" #Suck in the server you want

#These just act as some queries about the SQL Services on the machine you specified.
$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-Table

#Same information just pivot the data
$SMOWmiserver.Services | select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters | Format-List

#Specify the "Name" (from the query above) of the one service whose Service Account you want to change.

$ChangeService=$SMOWmiserver.Services | where {$_.name -eq "MSSQLSERVER"} #Make sure this is what you want changed!

#Check which service you have loaded first

$ChangeService

$UName="DomainName\UserName"

$PWord="YourPassword"

$ChangeService.SetServiceAccount($UName, $PWord)

#Now take a look at it afterwards

$ChangeService


Is there a standard approach to rolling out database schema changes? A developer said I should simply wrap all my database upgrade scripts in a transaction.  If it fails, just roll back all changes.  All my instincts tell me this is wrong, especially when it comes to dealing with large volumes of data and/or procedures and functions.

I typically hand-hold the upgrade process on single instance databases as follows:

Negotiate a maintenance window
Prepare upgrade scripts
Put database into restricted user mode
Disable scheduled jobs/processes that would normally hit the database
during this window
Perform a full backup
Apply the upgrade scripts
Have developer or test team confirm the app operates as expected
Put database back into multi-user mode
Release the database for normal use

When it comes to rolling out changes to several hundred instances, however, I have changed my process as follows:

I make the upgrade scripts much more robust: they can be run multiple times on the same server without harm, database version numbers are repected, scripts will terminate if they are run against the run version, etc.
spawn a process for each server (using powershell, osql, etc)
run the appropriate upgrade script
report success or failure

There's no standard process because every system is different.  About the last thing that I would do if just wrap everything in a single transaction.  What happens if I need to move 500 Gigs of data around?  That's one massive transaction.

Recently I've been using database snapshots as my rollback.

Basically take a snapshot, make the changes.  Delete the snapshot after signoff.  If upgrade failed roll back the snapshot and then try again.

It's a lot quicker to roll back a snapshot than to restore the database (assuming that the database is large).


Updating table with many rows I have 2 tables:

CREATE TABLE `users_ids_mapping` (
 `current_user_id` bigint(20) NOT NULL,
 `new_user_id` bigint(20) NOT NULL AUTO_INCREMENT,
 UNIQUE KEY `current_user_id` (`current_user_id`),
 KEY `new_user_id` (`new_user_id`)
) ENGINE=INNODB

CREATE TABLE `remote_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL DEFAULT -1,
    ..
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
) ENGINE=InnoDB

The first table holds old user_id values and new ones, what I want to do is to update the second table remote_users user_id with the new values.

I do it by running 2 queries:

SELECT id FROM remote_users
ORDER BY id ASC
LIMIT {limit}
OFFSET {offset}

And when having the next ids, I run:

UPDATE remote_users r
JOIN users_ids_mapping m ON m.current_user_id = r.user_id
SET r.user_id = m.new_user_id
WHERE r.id IN({string.Join(",", ids)})

users_ids_mapping can reach to around 100k rows.

I am using batches of 10k rows. This worked for costumers with remote_users table of 10-100m rows, but now i ran it on a costumer with around 200m+ rows and its simply takes too much time.

I can see that in the beginning its pretty fast:

running batch number:1 with offset:10000 found:10000 rows, took:76ms

But when the offset is high it takes almost 3 mins to update 10k rows:

running batch number:13678 with offset:136780000 found:10000 rows, took:185949ms

Any suggestions on how I can speed up the process?

too many rows for one update

If so use simple

UPDATE users_ids_mapping m,
       remote_users u
SET u.user_id = m.new_user_id
WHERE u.user_id = m.current_user_id
LIMIT 1000

Replace 1000 with a chunk size which is safe for you - both for resource consumption and for progress displaying.

Repeat until "Affected rows = 0". Set approximate upper limit of the progressbar with = 1 + (count of records in users_ids_mapping) / (chunk size)

UPDATE

  For multiple-table syntax, ORDER BY and LIMIT cannot be used.

UPDATE remote_users u
SET user_id = ( SELECT new_user_id
                  FROM users_ids_mapping
                  WHERE u.user_id = current_user_id )
WHERE EXISTS ( SELECT new_user_id
               FROM users_ids_mapping
               WHERE u.user_id = current_user_id )
LIMIT ??? ;

fiddle


Restart ORACLE OHAS without restarting databases looks like ohasd is getting to much memory (memory leak or something) that cause heavy swapping, is it possible to restart OHAS (till patch is applied) without bouncing databases?
"crsctl stop has" puts all resources down :(

No, because the Grid Infrastructure generally controls networking and storage access. If you can't start the DB without starting GI first, then you can't restart GI without restarting the database too.


Different sort despite same collation in Postgres I am getting different sort results for items with diacritics even though the collation being used is apparently the same.

So, after installing Postgress.app in my mac box for development I create the following table:

create table names (
    id int primary key,
    name varchar
)

and then insert a few values

insert into names values (1, "Francisco");
insert into names values (2, "Ana");
insert into names values (3, "Ágata");

and then query them like so:

select name from names order by name;

I get a wrong sorting order like so:

Ana
Francisco
Ágata

I can confirm with

select datname,
       datcollate
from pg_database;

that the datcollate value for all databases is en_US.UTF-8.

To help me debug this problem, I quickly spinned up an RDS instance, with same data, query and collation, but this time I get the proper expected sort order of:

Ágata
Ana
Francisco

What am I missing here, and how can I configuration postgres in my dev box to behave as expected?

PostgreSQL uses the operating system for its locales (at least with libc as the collation provider, otherwise icu may be used), and unfortunately, different operating systems give inconsistent sort results for the same UTF-8 locale names.

The fact that UTF-8 collations with Postgres.app doesn't sort as expected is an old open item on their issue tracker: Problems with sort order (UTF8 locales don't work). It can't really be fixed unless Apple fixes macOS, and so far it's not happening. See Ordering differences between Postgres instances on different machines (same locale) for a 2012 question on SO mentioning the problem.

  What am I missing here, and how can I configuration postgres in my dev
  box to behave as expected?

Your options:

use the same operating system for dev and production, possibly virtualized.
use ICU collations. They sort the same across all operating systems, but as of PG version 12, they can be used only with explicit COLLATE clauses at the column or expression level (not at the database level).
use the "C" or "C.UTF-8" collations, which sort binary-wise by codepoints. They don't provide a linguistically-aware sort order, but they're faster, immutable and consistent across operating systems.


Find all tables in a snapshot replication that have primary keys I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication.

I've tried to break it down into two steps, finding all tables in a snapshot replication, and then checking to see if those tables have a primary key.

I've tried to piece together a few different code samples, but I may need to start over, here's what I've got so far.

--=============================================================================================

SELECT  DB_NAME ()                  PublisherDB
  , sp.name                     AS PublisherName
  , sa.name                     AS TableName
  , UPPER (srv.srvname)         AS SubscriberServerName
  ,*
FROM    dbo.syspublications   sp
JOIN    dbo.sysarticles       sa ON sp.pubid = sa.pubid
JOIN    dbo.syssubscriptions  s ON sa.artid = s.artid
JOIN    master.dbo.sysservers srv ON s.srvid = srv.srvid;

--=============================================================================================
SELECT          DB_NAME ()                    AS db
              , SCHEMA_NAME (o.schema_id)     AS [Schema]
              , so.name                       AS table_name
              , so.type
              , CASE WHEN TABLE_NAME IN (
                              SELECT    TABLE_NAME
                              FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                              WHERE     CONSTRAINT_TYPE = 'PRIMARY KEY'
                          ) THEN 1 ELSE 0 END AS HasPrimaryKey
--INTO            #t2
FROM            sys.objects o WITH (NOLOCK)
INNER JOIN      sysobjects                                      so WITH (NOLOCK)
--INNER JOIN #t1 ON t1.
LEFT            OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t2 ON t2.TABLE_NAME = so.name ON so.id = o.object_id
WHERE           (
        (so.xtype = 'U') -- user table   xtype: docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15
    OR  (so.xtype = 'V') -- view
    OR  (so.xtype = 'P') -- stored procedure
)
                AND so.category <> 2
                AND so.name IN (
                        SELECT  DISTINCT OBJECT_NAME (objid) FROM  dbo.sysarticles
                    )
ORDER BY        so.name
              , so.type;

--=============================================================================================

DECLARE @jobId UNIQUEIDENTIFIER;
DECLARE @jobName sysname;

SELECT  @jobId   = jobs.job_id
      , @jobName = jobs.name
FROM    msdb.dbo.sysjobs       jobs (NOLOCK)
JOIN    msdb.dbo.syscategories categories (NOLOCK) ON jobs.category_id = categories.category_id
WHERE   categories.name = 'REPL-Snapshot'
        AND jobs.name LIKE '%db-name%';

SELECT  @jobId
      , @jobName;

EXEC sp_start_job @job_id = @jobId;

This is what I ended up going with.

--====================================================================================================================================================
--  Get tables in Snapshot replication for the selected DB.
--====================================================================================================================================================

IF OBJECT_ID ('tempdb..#t1') IS NOT NULL DROP TABLE #t1;

SELECT      pub.name                                                                AS [Publication]
          , CASE WHEN pub.name LIKE '%Snapshot%' THEN 'SnapShot'
                WHEN pub.name LIKE '%Transaction%' THEN 'Transaction' ELSE NULL END AS ReplicationType
          , art.name                                                                AS [Article]
          , serv.name                                                               AS [Subsriber]
          , sub.dest_db                                                             AS [DestinationDB]
          , obj.object_id
          , CASE WHEN obj.type = 'U' THEN 'Table'
                WHEN obj.type = 'V' THEN 'View'
                WHEN obj.type = 'P' THEN 'SP' ELSE NULL END                         AS ObjectType
INTO        #t1
FROM        dbo.syssubscriptions sub
INNER JOIN  sys.servers          serv ON serv.server_id = sub.srvid
INNER JOIN  dbo.sysarticles      art ON art.artid = sub.artid
INNER JOIN  dbo.syspublications  pub ON pub.pubid = art.pubid
INNER JOIN  sys.objects          obj ON obj.object_id = art.objid
WHERE       CASE WHEN pub.name LIKE '%Snapshot%' THEN 'SnapShot'
                WHEN pub.name LIKE '%Transaction%' THEN 'Transaction' ELSE NULL END = 'Snapshot';

--====================================================================================================================================================
--  Check for primary keys on the above tables
--====================================================================================================================================================
SELECT          SCHEMA_NAME (tab.schema_id)                         AS [schema_name]
              , tab.[name]                                          AS table_name
              , pk.[name]                                           AS pk_name
              , SUBSTRING (column_names, 1, LEN (column_names) - 1) AS [columns]
FROM            sys.tables  tab
LEFT OUTER JOIN sys.indexes pk ON tab.object_id = pk.object_id
                                  AND   pk.is_primary_key = 1
CROSS APPLY     (
    SELECT      col.[name] + ', '
    FROM        sys.index_columns ic
    INNER JOIN  sys.columns       col ON ic.object_id = col.object_id
                                         AND  ic.column_id = col.column_id
    WHERE       ic.object_id = tab.object_id
                AND ic.index_id = pk.index_id
    ORDER BY    col.column_id
    FOR XML PATH ('')
)                           D(column_names)
WHERE           pk.object_id IN (
                    SELECT  object_id FROM  #t1
                )
ORDER BY        SCHEMA_NAME (tab.schema_id)
              , tab.[name];


RAM usage is increasing in database server with time RAM usage is increasing in database server with time. When we restart the SQL services it take around 4 GB but after day or two it increases to 11-12 GB and even further.
Application is hosted on separate server. Please suggest what could be the reason.

SQL Server will try and use all the RAM it can get hold of to reduce execution times on queries.

As you have had it running for a day or two, with the increased amount of queries ran it will be using as much RAM that it has available to it.


Is Service Broker the best choice for auditing data changes on SQL Server Express? My project is to audit 5 to 10 existing tables in our system without prolonging transactions. Whatever method is used, it has to work on SQL Server Express 2005 to (eventually) 2016.

I've done research on Change Data Capture (CDC) and Change Tracking. Change Tracking doesn't capture the specific changes, and CDC is only available in Enterprise Edition.

Then I stumbled upon Service Broker. I was intrigued by Service Broker, so I started creating a prototype. Service Broker is working fine, but answers received in two of my other posts lead me to believe that this might not be the right way to go. Too complicated for nothing. I'm still in the analysis stage and trying different things as part of my analysis.

Right now the results of the service broker are not convincing... a bulk update of 105000 items to a price table, takes 38 seconds while the processing of the Queue (the auditing part) takes 17 seconds...  but the 38 seconds includes the double processing of inserting into 2 #temp tables that are then used to insert into the TMPins and TMPdel.  So I figure that I can cut that in half... I'm now questioning the use of the service broker... logically the trigger would probably take the same amount of time just by inserting the info straight into the audit table...

To clarify, when I say Bulk insert, It Isn't the "Bulk Insert" Function.  I'm talking about a big chunk of data that is inserted or updated at once.  when updating 105000 items in the price table, I want to audit the changes that happened. When I say changes that happen, I decided to insert the new values in the audit table (if it's an insert or update) or insert the primary key with all other fields null  (for records that have been deleted)... So yes! it can be after the data is loaded in but I don't want to lose any audit (I don't want a transactions to pass out of order)

The two other posts will help get the context of what I'm trying to do and what I've tried:

Trigger to create a variable Table to be sent to service broker
triggers - using the inserted / deleted tables in Dynamic SQL

I value every idea.

I am guessing that the following post is the basis of what you are currently using: Centralized Asynchronous Auditing with Service Broker.

While I really like Service Broker, I don't feel that it is the best fit to address this particular situation. The main concerns that I have with Service Broker, at least in this particular scenario, are:

It is probably over-complicated if used mainly to separate the DML event from the audit event. If not moving the data to another system, it doesn't seem to offer much benefit since you will still need to persist the INSERTED and DELETED tables in the DML trigger.
It is event-based, and events vary greatly in their size and frequency. You could have a million 1-record operations, or one or two 1 million record operations. And since each event is each individual DML operation, you don't have any opportunity to remove duplicate and/or negating entries across several DML operations.

My preference is for dumping the changes into a queue table, and then in a separate process that is scheduled to run every X minutes, read Y number of rows and process them.

Create a queue table to hold the audit data for a particular table (not for each individual DML operation). The table should have:

the LOCK_ESCALATION option set to DISABLE (via ALTER TABLE {name} SET (LOCK_ESCALATION = DISABLE) ) to avoid conflict between the logging of new data by the trigger and the removal of data from the audit processing. This option was introduced in SQL Server 2008, so it cannot be use on 2005 instances, but no reason to not use it in the 2008 and newer instances since it doesn't alter the functionality in either case.
an AuditID PK that is one of the following:

an INT IDENTITY starting at -2147483648
a BIGINT IDENTITY
an INT with its value coming from a SEQUENCE that is set to CYCLE

only the fields that you are tracking changes for, if not all of them
fields for both "old" and "new" if you need to keep track of the before and after values each time you process. Depending on how you are auditing changes, if you have an archive of prior values, then you should only need the "new" values from the INSERTED table since the values in the DELETED table should already be in your prior audit data.

Create a trigger that simply inserts into the queue table. If you need to pass in both "old" and "new" values, JOIN the INSERTED and DELETED tables here rather than trying to maintain two separate queue tables, one for each of those pseudo tables. JOINing them at this point and inserting both the "old" and "new" values into a single row is a slight performance hit, but will guarantee that each operation stays together and in chronological order (via the incrementing PK).

If you are not tracking changes on all fields, then use either UPDATE() or COLUMNS_UPDATED() to determine if the columns being audited have indeed been updated (for UPDATE operations; these functions return true for all columns in INSERT operations). Please keep in mind that the UPDATE() and COLUMNS_UPDATED() functions do not determine if a change has been made in the value of the column(s)!! They only report if the columns were present in the SET clause of the UPDATE statement. The only way to determine if the value(s) actually changed is to JOIN the INSERTED and DELETED tables. But if not tracking all columns, those functions are great for exiting the Trigger without doing any work if no tracked columns have changed. Meaning, at the beginning of the Trigger, you would do:

IF (NOT UPDATE(TrackedColumn1) AND NOT UPDATE(TrackedColumn2))
BEGIN
  RETURN; -- no changes so just exit
END;

If you are not capturing both "old" and "new" values into the queue table, then this is the only opportunity to eliminate "updated" records where no columns actually changed. When inserting into the queue table, you just filter on WHERE IntColOld <> IntColNew OR StringFieldOld <> StringFieldNew COLLATE Latin1_General_BIN2 OR ISNULL(DateFieldOld, '1900-01-01') <> ISNULL(DateFieldNew, '1900-01-01') OR .... It is important to use a _BIN2 collation on string fields to ensure that the two fields are in fact identical. And you need to use INSULL for just the nullable fields so that they can equate if both are NULL.
Create a Stored Procedure that will run for X seconds, and in that time will process sets of TOP(@BatchSize) rows, using ORDER BY AuditID ASC. You do this by a WHILE loop that checks GETDATE() against @StartTime which was set at the beginning of the stored procedure. Depending on what processing you need to do, it is sometimes easier to create a local temporary table to either INSERT INTO #TempTable SELECT... the working set (then you will have to DELETE those rows at the end of each loop) or DELETE FROM using OUTPUT INTO #TempTable.

Here you can remove duplicate modifications. If you are tracking both "old" and "new" values per each row, you should also eliminate rows where none of the columns actually changed. You can do that by testing for WHERE IntColOld = IntColNew AND StringFieldOld = StringFieldNew COLLATE Latin1_General_BIN2 AND .... It is important to use a _BIN2 collation on string fields to ensure that the two fields are in fact identical. Non-binary collations, even if case-sensitive and accent-sensitive, etc, still allow for linguistic normalizations that should compare the same for regular comparisons, but not when auditing. Don't use _BIN collations as they have been deprecated since SQL Server 2005, which is when the _BIN2 collations came out.

Everything done inside of the loop needs to be within an explicit BEGIN TRAN / COMMIT / ROLLBACK. Use a TRY / CATCH block to manage that. This will prevent either losing some records or processing some twice.
Schedule the stored procedure to run every X minutes. Typically this is done via a SQL Server Agent job, but SQL Server Agent is not available for the Express editions. In that case, you can either use the Windows Task Scheduler or get something like Quartz.NET.

Setting up a process in this fashion allows you to have a more consistent, and tuneable, process that should steadily process X records every Y minutes. So it doesn't matter if you have 1 million single-row DML operations or a single 1 million row DML operation; this process will just keep chuggin' along, doin' what it does.


Grouping results with left and inner join with zero counts I have a query like

SELECT
    `campaign_question_options`.`text`,
    COUNT(`campaign_submission_answers`.`answer`) as `count`
    FROM `campaign_questions`
    INNER JOIN `campaign_question_options` ON `campaign_question_options`.`campaign_question_id` = `campaign_questions`.`id`
    LEFT JOIN `campaign_submission_answers` ON `campaign_submission_answers`.`answer` = `campaign_question_options`.`text` AND `campaign_submission_answers`.`campaign_question_id` = 1
    LEFT JOIN `campaign_submissions` ON `campaign_submissions`.`id` = `campaign_submission_answers`.`campaign_submission_id`
    LEFT JOIN `participants` ON `participants`.`id` = `campaign_submissions`.`participant_id`
WHERE
    `campaign_questions`.`id` = 1
GROUP BY `campaign_submission_answers`.`answer`
ORDER BY `campaign_question_options`.`index`;

This gives me a result set like

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |   114 |
| 2            |    48 |
| 3 (neutral)  |    34 |
| 4            |     6 |
| 5 (negative) |     0 |
+--------------+-------+

So the problem is that I then need to filter the results further on the participants.appraisee_id column. However if I add this to the where clause I lose my zero result (since the left join returns a null row).

SELECT
    `campaign_question_options`.`text`,
    COUNT(`campaign_submission_answers`.`answer`) as `count`
FROM `campaign_questions`
INNER JOIN `campaign_question_options` ON `campaign_question_options`.`campaign_question_id` = `campaign_questions`.`id`
LEFT JOIN `campaign_submission_answers` ON `campaign_submission_answers`.`answer` = `campaign_question_options`.`text` AND `campaign_submission_answers`.`campaign_question_id` = 1
LEFT JOIN `campaign_submissions` ON `campaign_submissions`.`id` = `campaign_submission_answers`.`campaign_submission_id`
LEFT JOIN `participants` ON `participants`.`id` = `campaign_submissions`.`participant_id`
WHERE
    `campaign_questions`.`id` = 1 AND `participants`.`appraisee_id` = 1
GROUP BY `campaign_submission_answers`.`answer`
ORDER BY `campaign_question_options`.`index`;

Which returns

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |    16 |
| 2            |     1 |
+--------------+-------+

When in fact I wish for

+--------------+-------+
|     text     | count |
+--------------+-------+
| 1 (positive) |    16 |
| 2            |     1 |
| 3 (neutral)  |     0 |
| 4            |     0 |
| 5 (negative) |     0 |
+--------------+-------+

Can anyone help me improve this query?

Thanks

Build a table with n=1..5 and the comments (positive, etc); lets call
it labels
Get the counts (5 or fewer)
Put together:

SELECT labels.n, labels.comment, IFNULL(x.ct, 0) AS "count"
    FROM labels
    LEFT JOIN ( your big query to get the counts ) x
        ON x.n = labels.n
    ORDER BY labels.n

The principle is "FROM (stuff you want all of) LEFT JOIN (stuff that may be missing some)"


Which MySQL table to use for read-only, bulk-loaded tables? So I have a workload on 2 key tables in my application with the following characteristics:

READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, there are no deletes nor updates.  I am using  INSERT IGNORE ...
Very Large Tables
I am using temporal partitioning, but even with that my expected worse case on a single paritition is 10M-100M records per paritition (older partitions are eventually deleted).
No need for FK, transactions, consistent reads
Theses table are used to seed very targeted time-sensitive queries and reporting views.  There is no need for enforced FK constraints (there are no explicit FKs) and certainly no transactions are needed.  There is also no need for clients issuing selects to have a fully consistent view of the data -- so if I am in the middle of a bulkload and they only see 1/2 of the in-progress updates, that's totally cool.

I initially was leaning towards InnoDB and then went back to MyISAM after reading about concurrent inserts, which sounds like a perfect description of my use case (high number of inserts with no intervening updates) I am thinking of going back to MyISAM esp given how many other features that I just don't care about (e.g. Foreign Keys, transactions).

Could someone with real-world experience using MySQL for similar workloads offer a suggestion on what storage engine is the right choice here.  Plan on doing some serious testing early next week but just would like to get a rough idea of what to expect/look out for here.

This is definitely a loaded question. Why ???

InnoDB caches data and index pages
InnoDB may lock Clustered Indexes during Inserts/Updates
InnoDB is multithreaded and can be tuned for accessing CPUs/Cores
MyISAM only caches index pages
MyISAM must always be read data from disk
MyISAM can have dedicated caches per tables

If you have plenty of RAM and intend to read the same data throughout a business day, some would go with InnoDB. If you are looking for high-speed reads from varying ranges of data, some would go with MyISAM. Even still, there are simply too many mitigating factors to pick one fully over the other such as

VARCHAR field processing
Row Format
Foreign Keys
Index Cardinality
Physical Disk (SSD, SATA, SAN, NAS)
Application Processing Needs

Here are some posts that compare and contrast MyISAM and InnoDB

Sep 20, 2011 : Best of MyISAM and InnoDB
April 12, 2012 : Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)
May 02, 2012 : Which mysql storage engine to choose?
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Aug 17, 2012 : When to switch from MyISAM to InnoDB?

Please read these before deciding ...


Tool for quickly backup and restore a table Is there a tool for quickly backup and restore a table's data, preferably to a csv file?

It would be best if I could do it using batch. Run a .cmd file and data is backed up into a file with datetime name. And anytime needed I can just run another .cmd file to restore a file into the table.

BCP is the tool you're looking for.
 Here's some links that should put you in the right direction:

https://msdn.microsoft.com/en-us/library/ms162802.aspx

https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/


Reliability of Scimore embedded database I currently use SQLite for some applications that need a light embedded database. These apps use multiple processes that access the same database. For concurrent write access this is cumbersome. We have done some work-arounds to overcome this to some degree, but clearly SQLite has it limits.

So, we looked around and found an interesting, but very unknown, database: Scimore. It's quite capable; it's embedded version uses an out-of-process technique that allows multi-concurrency read and write access. Besides this, it's free. It's almost too good to be true.

From our first test results it seems to be very good and fast, but then why is it so unknown?

Therefore my question: Do you know this database; if so what are your experiences regarding reliability?

Scimore website

NB: I'm only asking for real usage experiences; not opinions.

I've worked a few with it, and until now, it works like a charm, no concurrency problems, fast and depending on clients, lite as you want it to be.
I do not know if it would hace issues at electrical shutdowns while saving data
but i'm using it for technical data (Engineering data)

Cheers!


Is there any performance difference between UNIQUE INDEX and INDEX with the same cardinality on MySQL? I have a table that has id and created_at.
Column     | Non Unique | Cardinality
-------------------------------------
id         | 0          | 1000
created_at | 1          | 1000

id is unique, and created_at is non unique.
I want to know if there's any performance improvement if I convert the created_at index from non unique to unique.

There will be no difference for read operations, once written the entries in the index will be the same.
In theory there is a slight difference in update performance as the engine needs to enforce uniqueness in a unique index, but in reality this is one going to be at most a few CPU cycles per row difference so will be unnoticeable.
Only create a unique index for values that you know should be unique. It is quite conceivable that two items could be created at the same time (or at least within the granularity of the data type you are using for that column).


You can add the number of gifts with either:

SELECT ...,
    COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) AS number_of_gifts

or the similar (works in MySQL only):

SELECT ...,
    SUM(GiftOrder='gift') AS number_of_gifts

If you want a simple (yes/no) as in your question, if the customer has ever done one or more gifts vs. none at all, then:

SELECT ...,
    CASE WHEN COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) > 0
        THEN 'Gift'
        ELSE 'Not Gift'
    END AS gifts


MAT over a calculated measure We have an OLAP SSAS cube with budget data on the fact table, and budget cathegory (sales, costs, salaries, ...) on a dimension. We have a bunch of formulas for our users but we are not able to create a MAT (moving annual total) on an already calculated measure.

create MEMBER CURRENTCUBE.[Measures].[Sales budget]
as ([Dim budget].[Level_2].[Sales],[Measures].[Budget]);
-- Budget accounting only the values from the fact table with value "Sales" on the appropiate field of the cathegory

create MEMBER CURRENTCUBE.[Measures].[Sales budget MAT]
as Aggregate(LastPeriods(12,[Dim time].[Months].CurrentMember), [Measures].
[Sales budget]);
-- A MAT over a calculated measure doesn't work, we obtain a #VALUE! error

create MEMBER CURRENTCUBE.[Measures].[Budget MAT]
as Aggregate(LastPeriods(12,[Dim time].[Months].CurrentMember), [Measures].[Budget]);
-- A MAT over an original measure works as expected.

Any idea what are we doing wrong? How can we calculate this MAT?

I had a blonde moment! Just using SUM instead of AGGREGATE and everything is ok as expected :(


Count duplicate versions of databases (SQL Server 2008 R2) I wish to pull statistics on the number of duplicate database versions we have running in our environment - most databases run with version control in the database name, so I built this query:

SELECT 'Production' as Class, getdate() as DateRecorded, left(d.name, CHARINDEX('_v',d.name)-1) as [DBGroup], d.name as [DatabaseName], ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
ON D.DATABASE_ID = MF.DATABASE_ID
where d.name like '%[_]p[_]%'
and d.name not like '%import'
and d.name like '%[_]v[0-9]%'
GROUP BY left(d.name, CHARINDEX('_v',d.name)-1),d.name
order by coalesce(left(d.name, CHARINDEX('_v',d.name)-1), d.name)

which is great; however this excludes any databases which do not contain any version control ('%[_]v[0-9]%').  When I remove the respective like statement, it crashes with the following:

Invalid length parameter passed to the LEFT or SUBSTRING function.

based on how it can't parse the charindex parameter on databases which do not contain any version control, so I built a secondary query to hoover everything else up:

SELECT 'Production' as Class, getdate() as DateRecorded, NULL as [DBGroup], d.name as [DatabaseName], ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
ON D.DATABASE_ID = MF.DATABASE_ID
where d.name like '%[_]p[_]%'
and d.name not like '%import'
and d.name not like '%[_]v[0-9]%'
GROUP By d.name

So the purpose of this question is to request how to compile a query capable of running both queries as one single query?

You can use CASE...WHEN logic to only apply the CHARINDEX function where appropriate:

SELECT      'Production' as Class,
            getdate() as DateRecorded,

            CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1)
            ELSE        d.name
            END         as [DBGroup],

            d.name as [DatabaseName],
            ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB

FROM        SYS.MASTER_FILES MF
            INNER JOIN SYS.DATABASES D ON D.DATABASE_ID = MF.DATABASE_ID

GROUP BY    CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1)
            ELSE        d.name
            END,
            d.name

ORDER BY    CASE WHEN   d.name like '%[_]p[_]%' and d.name not like '%import' and d.name like '%[_]v[0-9]%'
            THEN        left(d.name, CHARINDEX('_v',d.name)-1)
            ELSE        d.name
            END,
            d.name


PostgreSQL : send all rows of a table to a function I have a table with one column (double precision numbers). I also have made a function (with double precision numbers as input and output), and I want to send every value of my table to the function, and store the output in a second (new) column.

What is the best way to do this? Make a void function with a loop in it? Or are there better ways?

Thannks

UPDATE mytbl
SET col2=myfn(col1)

should do the trick


Unable to restore database in Oracle 10g I created a database backup using the expdp command in Oracle 10g.  The backup appears to be created successfully.  However, while restoring using impdp command I get the following error message.

ORA-39083: Object type JOB failed to create with error: ORA-00001:
unique constraint (SYS.I_JOB_JOB) violated Failing sql is:  BEGIN
DBMS_JOB.ISUBMIT( JOB=> 4002, NEXT_DATE=> TO_DATE('2012-07-13
14:29:31', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'sysdate + 10/1440',
WHAT=>
 'wwv_flow_mail.push_queue(wwv_flow_platform.get_preference(''SMTP_HOST_ADDRESS''),wwv_flow_platform.get_preference(''SMTP_HOST_PORT''));',
 NO_PARSE=> TRUE); END;

 Processing object type
 DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Job
 "TMSMV"."SYS_IMPORT_FULL_01" completed with 1255 error(s) at 12:24:24

I used following commands:

expdp tmsmv/password full=y dumpfile=tmsdump20120113-558.dmp logfile=tmslog20120713-558.log

impdp tmsmv/password full=y dumpfile=tmsdump20120113-558.dmp logfile=tmslog20120713-558.log

How can I resolve this issue?

Looks like there is already a job on the imported database with that job number.  Just recreate the job on the import database.

The job looks to be a queue so you could, depending on your business procedures, probably run the new job to test that everything is working.


Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint How come that statement don't copy constraints if I explicitly ask for it?

CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS);
INSERT INTO backup_schema.permissions
    SELECT *
    FROM public.permissions
    WHERE community_id=123;

verifying with pgadmin3 I discovered that constraints on the new table(eg.:backup_schema) was:

but in the original table in public schema was like this:

The documentation says about INCLUDING CONSTRAINTS (emphasis mine):

  Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. Also, no distinction is made between column constraints and table constraints — when constraints are requested, all check constraints are copied.

Foreign keys obviously fall into this 'other constraints' group, this is why the two FKs of yours are not copied over.

Interestingly, the documentation for version 9.3 has a different wording, not stating the above so clearly.


Availability Group: ReadIntent connection failing I have two nodes configured in an AG group in a dev domain:

node1.DevDomain.com
node2.DevDomain.com

My own workstation is on our production domain:

MyMachine.ProdDomain.com

When I test ApplicationIntent=ReadOnly connections from a machine within DevDomain I am correctly directed to the readable secondary. Note, the connection string specifies the Listener name as I am in the same domain.

However, when I test ApplicationIntent=ReadOnly connections from my own workstation (different domain) I get the connectivity error below. Note, in this case I need to specifiy the Listener IP since the I am outside the domain and the name will not resolve.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is conf
igured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.).Exception.Message

Server: Windows 2012, SQL 2012. My machine: Windows 7.

I believe I've got the Routing Lists and URLs correct (and again, this works fine from within the domain).

I know the nodes of the cluster need to be in the same domain, but I would think I'd be able to connect from outside the domain by specifying the Listener by IP, but maybe not?

I thought perhaps I had a DNS issue in the DevDomain, but everything seems to resolve fine inside that domain.

Can I do this / what do I need to be looking at to resolve this?

Script to create read only rounting:

/*

set up read-only intent routing

Test it:

sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername" -Kreadonly
sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername"

*/

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com \instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));-- not read_only

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node1.DevDomain.com:49598'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node2.DevDomain.com\instance:49877'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node1.DevDomain.com\instance'
                ,'node2.DevDomain.com\instance'
                ))
        );

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instanced'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node2.DevDomain.com\instancee'
                ,'node1.DevDomain.com\instance'
                ))
        );
go


Incremental MySQL restore using binlogs I've tried to restore a MySQL database using incremental binlogs and mysqlbinlog tool but I received the following message:

ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

BINLOG_FORMAT parameter in my database is set to ROW, but is there a way to restore binglog with this parameter in ROW?

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

I believe you will find the server will tell you that it is configured for STATEMENT since there's not another good reason why you'd get this error.

The error message is referring to the server where you're trying to restore the files as currently configured, not the format the server may have been in when the log was created.

MySQL has two logging formats for replication, ROW, which logs the actual changes made to individual rows, and STATEMENT, which logs the queries that made the changes.  The MIXED format isn't a different format, but instead is a configuration setting that allows the server to select the format for each query based on internal rules.

When a server has binary logging enabled and executing queries from an incoming binlog, it has to write the queries to its own binlog, but it doesn't just copy the incoming log entry into the outgoing log... if the server is configured for ROW, it will always translate incoming STATEMENT events into ROW events.  If it is configured for MIXED, then it may log incoming STATEMENT events as outgoing STATEMENT events or it may log them as ROW... but if it is configured for STATEMENT then it is unable to log incoming ROW events, and the error you're seeing would be what is returned.

 -- server receives  -- server global   -- server writes
 -- incoming event   -- binlog_format   -- outgoing event

    STATEMENT           STATEMENT          STATEMENT
    STATEMENT           MIXED              STATEMENT or ROW
    STATEMENT           ROW                ROW
    ROW                 STATEMENT          XXX NOT SUPPORTED XXX
    ROW                 MIXED              ROW
    ROW                 ROW                ROW

If SHOW GLOBAL VARIABLES LIKE 'binlog_format'; really does say ROW I'll be surprised, but if not, then your fix should be this:

SET GLOBAL BINLOG_FORMAT = 'ROW';


How to Total all Records in a Field? this is probably very basic but I can't figure it out.
I'm trying to total two records to give the output: £2,100 in the Total field but they keep coming out separate.

Any help? Thanks.

The quick and dirty way to show totals is to add a Totals row to any Datasheet View of a table or query.  First open the Datasheet, then on the Home ribbon (i.e. toolbar tab) click the Totals button (with the sigma sign).  A Total row with empty cells appears at the bottom of the data rows .  Clicking on a cell reveals a drop-down list with options for various aggregate functions, include sum.  See this documentation page.

A more advanced way to get totals is to create an aggregate query.  Within the standard Query Design View, click the Totals button on the query Design ribbon.  This enables and shows a Total row in the design grid.  For each column of the query, Totals options can be selected including "Group By" and various aggregate functions.  The same query can also be edited in the SQL view which will show a GROUP BY clause and various aggregate functions in the column SELECT list.  See this documentation page for further details.

Asking questions here is fine, but especially for beginning topics as this, the web contains many tutorial pages with much more detail, just as I linked to.  All I did was search for "Access sum fields" and "Access query total" to find those pages.  I suggest doing those same searches so that you can see various other resources and tutorials.


MYSql Full Text Boolean search and relevancy score improvements I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant.

Query:

SELECT phppos_items.name,MATCH (phppos_items.name)
AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) as rel
FROM `phppos_items`
WHERE MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE)
ORDER BY `rel` DESC LIMIT 20;

The search is Samsung S4 LCD* IN BOOLEAN MODE and I would think "Samsung S4 LCD/Digi Black" and "Samsung S4 LCD/Digi White" would have a higher relevancy score; but it does NOT (Shown below).

Many of the items in the database contain 2 characters or less to search.

So I changed ft_min_word_len to 2 and restarted server and then dropped and created database again. I thought this would help; but it did not.

Do you have any idea on how to fix this? I need to use boolean mode as Natural Language search has the 50% rule and becoming a STOP word.

Example queries/results

mysql> show global variables WHERE Variable_name = 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 2     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> use pos;
Database changed
mysql> SELECT phppos_items.name,MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) as rel FROM `phppos_items` WHERE MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) ORDER BY `rel` DESC LIMIT 20;
+----------------------------------+--------------------+
| name                             | rel                |
+----------------------------------+--------------------+
| Samsung Tab 3 8.0" T310 LCD/Digi | 1.9734089374542236 |
| Samsung Note 10.1" LCD/Digi      | 1.9734089374542236 |
| Samsung Tab 3 Lite 7" T110 LCD   | 1.9734089374542236 |
| Samsung Tab 4 7" T230 LCD        | 1.9734089374542236 |
| Samsung Tablet P600 LCD Black    | 1.9734089374542236 |
| Samsung T800/805 LCD/Digi        | 1.9734089374542236 |
| Samsung S3 LCD/Digi Blue         | 1.9734089374542236 |
| Samsung S3 LCD/Digi White        | 1.9734089374542236 |
| Samsung S4 LCD/Digi Black        | 1.9734089374542236 |
| Samsung S4 LCD/Digi White        | 1.9734089374542236 |
| Samsung S5 LCD/Digi Black        | 1.9734089374542236 |
| Samsung S5 LCD/Digi White        | 1.9734089374542236 |
| Samsung S4 Mini LCD/Digi Black   | 1.9734089374542236 |
| Samsung S4 Mini LCD/Digi White   | 1.9734089374542236 |
| Samsung i9000 LCD/Digi           | 1.9734089374542236 |
| Samsung Note 2 LCD/Digi Black    | 1.9734089374542236 |
| Samsung Note 2 LCD/Digi White    | 1.9734089374542236 |
| Samsung Note 3 LCD/Digi Black    | 1.9734089374542236 |
| Samsung Note 3 LCD/Digi White    | 1.9734089374542236 |
| Samsung Ace 2X LCD               | 1.9734089374542236 |
+----------------------------------+--------------------+
20 rows in set (0.00 sec)

mysql> SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 5.6.26    |
+-----------+
1 row in set (0.00 sec)

The problem was:
ft_min_word_len is only for MyIsam and the correct variable is innodb_ft_min_token_size


How to handle TimeZone properly in SQL Server? My local development server is in the Middle East, but my production server is in the UK.

I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I need to show the time according to Saudi Arabia format.

Should I create a new database table called TimeZone and save the Time in UTC?

There's no quick fix for this, unfortunately. Internationalization of an application should be part of the very first design discussions, as it really goes to the core of a lot of different areas, including date/time comparisons and output formatting.

Anyways, to get on the track of Doing It Right, it's essential to store the time zone information with the time. In other words, realizing that the date/time 20130407 14:50 is meaningless without either (a) including the time zone's then-current UTC offset(note 1), or (b) ensuring that all logic inserting these values first converts to a certain fixed offset (most likely 0). Without either of those things, two given time values are incomparable, and the data is corrupt. (The latter method is playing with fire(note 2), by the way; don't do that.)

In SQL Server 2008+, you can store the offset with the time directly by using the datetimeoffset data type. (For completeness, in 2005 and before, I would add a second column to store the then-current UTC offset value (in minutes).)

This makes it easy for a desktop-type application, as these platforms normally have mechanisms to automatically convert a date/time + time zone to a local time and then format for output, all based on the user's regional settings.

For the web, which is an inherently disconnected architecture, even with the back-end data set up properly, it's more complex because you need information about the client to be able to do the conversion and/or formatting. This is usually done via user preference settings (the application converts/formats things before output), or simply showing things with the same fixed format and time zone offset for everyone (which is what the Stack Exchange platform currently does).

You can see how if the back-end data is not set up properly, very quickly it's going to get complicated and hacky. I would not recommend going down any of those paths because you'll just end up with more problems down the line.

Note 1:

A timezone's UTC offset is not fixed: consider daylight savings where a zone's UTC offset varies by plus or minus an hour. Also zones' daylight savings dates vary on a regular basis. So using datetimeoffset (or a composite of local time and UTC offset at that time) results in maximum information recovery.

Note 2:

It's about controlling data inputs. While there's no fool-proof way to validate incoming values, it's better to enforce a simple standard that doesn't involve computations. If a public API expects a data type that includes an offset, that requirement will be clear to the caller.

If that wasn't the case, the caller has to rely on documentation (if they read it), or the computation is done incorrectly, etc. There are fewer failure/bug modes when requiring an offset, in particular for a distributed system (or even just web/database on separate servers as the case here).

Storing the offset anyway kills two birds with one stone; and even if that isn't required now, it makes the possibility available later on if necessary. True it takes up more storage, but I think it's worth the trade-off because the data is lost if it's never recorded in the first place.


Permutation and Combination of words Follow up of question

I have the following data:

I have the following sample data for permutations and combination.

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');
insert into tbltest values('Nasir Ahmed Amir Ahmed Mehendy','UAE High Street');

For an example expected result:

name
----------------
John Mak Sam
John Sam Mak
Mak John Sam
Mak Sam John
Sam John Mak
Sam Mak John
....
....

Tried the solution from previous question:

--To store Words
CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

--Table variable to identity column for each words
DECLARE @Inter TABLE (id int identity(1,1), names varchar(max));

--Insert into table variable
INSERT INTO @Inter(names) SELECT name from TblTest;

--Query to insert into NameParts table
;WITH splitdata
AS
(
    SELECT id,splitname
    FROM
    (
        SELECT *,Cast('<X>' + Replace(F.Names, ' ', '</X><X>') + '</X>' AS XML) AS  xmlfilter
        FROM   @Inter F
    )F1
    CROSS apply
    (
        SELECT fdata.d.value('.', 'varchar(50)') AS splitName
        FROM   f1.xmlfilter.nodes('X') AS fdata(d)
    ) O
)
INSERT INTO dbo.NameParts
SELECT *
FROM splitdata;

Query for Permutation and Combination:

--Query for Permutation and Combination
;WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  FullNameVariation
FROM
  permutations
WHERE
  Level = 1
ORDER BY
  ID,
  FullNameVariation
OPTION (MAXRECURSION 5100);

Note: Above query works fine but unable to get following record in the result set:

Nasir Ahmed Amir Ahmed Mehendy

and that is because of this condition:

' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'

The word Ahmed is repeated and the above condition not met.

Extending the original answer by Andriy M we can uniqify the strings at the insert phase with a dummy character (in my case I used ~), and remove that character later.

In this case I chose to use REPLICATE passing in the dummy character '~' and the ROW_NUMBER function for the number of instances to replicate. This should ensure that all strings are unique.

dbFiddle

Full code

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');
insert into tbltest values('Nasir Ahmed Amir Ahmed Mehendy','UAE High Street');
GO

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

--Table variable to identity column for each words
DECLARE @Inter TABLE (id int identity(1,1), names varchar(max));

--Insert into table variable
INSERT INTO @Inter(names) SELECT name from TblTest;

--Query to insert into NameParts table
;WITH splitdata
AS
(
    SELECT id,splitname
    FROM
    (
        SELECT *,Cast('<X>' + Replace(F.Names, ' ', '</X><X>') + '</X>' AS XML) AS  xmlfilter
        FROM   @Inter F
    )F1
    CROSS apply
    (
        SELECT fdata.d.value('.', 'varchar(50)') AS splitName
        FROM   f1.xmlfilter.nodes('X') AS fdata(d)
    ) O
)
INSERT INTO dbo.NameParts
SELECT id,[splitdata].[splitName] + REPLICATE('~',(ROW_NUMBER() OVER (PARTITION BY [splitdata].[id] ORDER BY [splitdata].[splitName] ASC))
FROM splitdata;
GO

;WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  REPLACE(FullNameVariation,'~','')
FROM
  permutations
WHERE 1=1 AND
  Level = 1
ORDER BY
  ID,
  FullNameVariation
OPTION (MAXRECURSION 5100);

DROP TABLE [dbo].[tbltest]
DROP TABLE [dbo].[NameParts]


Many:Many with Shared Relation I'm modelling data with multiplicity like this:

Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I model this?

Here is one possible representation with nearly enforced consistency (it doesn't enforce the 1+ Composition : 0+ Anthology multiplicity). However it duplicates FK_Composer lots of places (which as a side annoyance breaks some of my Entity Framework features).

Composer    Composition    junction          Anthology
--------    -----------    --------------    ---------
                           FK_Anthology   -> PK
PK       <- FK_Composer <- FK_Composer    -> FK_Composer
            PK          <- FK_Composition

Note: I'm also trying to hack this out at the business logic and ORM layers and have hit obstacles there as well.

https://stackoverflow.com/questions/16273702/model-entity-framework-many-many-plus-shared-relation
https://stackoverflow.com/questions/16312984/custom-entity-framework-many-to-many-navigation-property

The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system?  There are all kinds of incidental relationships between tangible things that are recorded in any system.  However, only certain of these are important for the purposes of the system itself.  These are the ones that belong in a relational schema.

If anthologies will always be made up of compositions, and compositions will always have composers, then you can always derive the relationship between anthologies and composers using a query.  If you did it that way there would be no risk of inconsistent relationships.

This model would look like this:

You would have table definitions something like this:

create table Composer
( composer_id int IDENTITY
, composer_name nvarchar(50)
, biography nvarchar(1000) null
, other_composer_info nvarchar(1000) null
, constraint pk_composer primary key(composer_id)
);

create table Composition
( composition_id int IDENTITY
, composer_id int
, composition_name nvarchar(50)
, composed_on datetime
, constraint pk_composition primary key(composition_id)
, constraint fk_composition_composer
    foreign key (composer_id) references Composer(composer_id)
);

create table Anthology
( anthology_id int IDENTITY
, anthology_name nvarchar(50)
, constraint pk_anthology primary key (anthology_id)
);

create table AnthologyItem
( anthology_id int
, composition_id int
, constraint pk_anthology_item primary key
   (anthology_id, composition_id)
, constraint fk_item_anthology (anthology_id)
   references Anthology(anthology_id)
, constraint fk_item_composition (composition_id)
   references Composition(composition_id)
);

The good thing about this is that it has no redundant data to get out of synch.  The problem is that it doesn't quite fit Shannon's requirement that an anthology be about one composer and that all of the compositions in that anthology must be from the same composer.

Unfortunately, this is not an easy problem to solve with declarative referential constraints.  Declarative constraints are great for making sure that everything within a row makes sense.  What they aren't built to do is enforce rules between rows.

There is a declarative way to solve this problem, but it involves a trade-off that many people wouldn't like, because it smells an awful lot like violating normalization.  Some people would argue (Mike Sherril comes to mind) that this solution doesn't literally violate normalization rules, but people who are less well attuned to the actual rules of normalization will probably look at this solution with skepticism.

So what is this controversial solution?  It looks like this:

Note that the primary keys of some of these tables have been modified.  Here is the SQL DDL for the solution: (You need to scroll it to the bottom to see the magic.)

create table Composer
( composer_id int IDENTITY
, composer_name nvarchar(50)
, biography nvarchar(1000) null
, other_composer_info nvarchar(1000) null
, constraint pk_composer primary key(composer_id)
);

create table Composition
( composition_id int IDENTITY
, composer_id int
, composition_name nvarchar(50)
, composed_on datetime
, constraint pk_composition
    primary key(composer_id, composition_id) -- NOTE CHANGE!
, constraint fk_composition_composer
    foreign key (composer_id) references Composer(composer_id)
);

create table Anthology
( anthology_id int IDENTITY
, composer_id int                            -- THIS IS NEW!
, anthology_name nvarchar(50)
, constraint pk_anthology
    primary key (composer_id, anthology_id)  -- THIS IS DIFFERENT
);

create table AnthologyItem
( composer_id int                            -- THIS IS NEW!
, anthology_id int
, composition_id int
, constraint pk_anthology_item primary key   -- THIS HAS CHANGED.
   (composer_id, anthology_id, composition_id)
, constraint fk_item_anthology
    foreign key (composer_id, anthology_id)
    references Anthology(composer_id, anthology_id)
, constraint fk_item_composition
    foreign key (composer_id, composition_id)
    references Composition(composer_id, composition_id)
);

Note that the way this works is you have to impose a single composer on an anthology by making the composer part of the anthology's primary key.  You do the same thing with composition.  Then, when you create an intersection between composition and anthology, you have the composer ID twice.  You can then use a check constraint to declaratively enforce that compositions and anthologies have not only a single composer, but the same one.

NOTE: I'm not saying you should do this, I'm just saying you could do this. YMMV etc.


postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (using pg_notify) is sent. A nodejs client listens for the notification and handles it.

For testing, we added PERFORM pg_sleep(5) right after pg_notify call so we can see the progress percent slowly increment.

The problem is the notifications aren't sent immediately to listeners when PERFORM pg_notify is called but instead ALL notifications are sent AFTER the stored procedure has completed running.

Has anyone encountered this problem and found any solution (aside from dblink)?

We've tried the following inside the Stored Procedure:
- moved pg_sleep at top of loop
- adding commit; after pg_notify
- use notify channel, 'payload instead of pg_notify

We then tried using triggers + pg_notify. When that didn't work we also tried using execute format('notify channel, ''%s''', payload). The same result.

Some sample codes:

Node.js

const pg = require('pg');
let client = new pg.Client({user: '', database: '', host: '', password: '', port: 5432});
client.connect();
client.query('LISTEN progress_updates');
client.on('notification', function(data) {
   console.log('notification: ', data);
});

Backend:

-- table
drop table if exists operations;
create table progress ( id serial primary key, percent int default 0 );

-- stored procedure
CREATE OR REPLACE PROCEDURE my_stored_proc() language plpgsql
AS $$
DECLARE
progress_id int;
BEGIN
insert into progress (percent) values (0) returning id into progress_id;
commit;

update progress set percent= 10 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 10}');
-- notify progress_updates, '{"percent": 10}'
perform pg_sleep(5);

update progress set percent= 30 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 30}');
perform pg_sleep(5);

update progress set percent= 70 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 70}');
perform pg_sleep(5);

update progress set percent= 100 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 100}');
END;
$$;

-- trigger
create or replace function notify_progress_trigger() returns trigger
  language plpgsql
as
$$
DECLARE
  payload TEXT;
BEGIN
  raise notice 'TRIGGERED ON %', NOW();
  payload := '{"percent": ' || NEW.percent || '}';
  perform pg_notify('progress_updates', payload);
  -- execute format('notify progress_updates, ''%s''', payload);
  return NEW;
END;
$$;
alter function notify_progress_trigger() owner to postgres;

create trigger watched_progress_trigger
  after insert or update
  on operations
execute procedure notify_progress_trigger();

-- Call stored proc manually
call my_stored_proc();

We expected the notifications will be received one by one by the listener after each batch gets updated while the stored procedure is still running, but instead we receive all of the notifications in quick succession after the stored procedure completes.

As far as I'm aware what you're trying to do, by issuing a commit inside a procedure, is use an autonomous transaction. Postgres does not support them inside stored procedures/functions, and the behaviour you are seeing is correct - the (implicit) transaction does not complete until the procedure completes.

This page discusses the issue in detail along with potential workarounds.


SQL CASE query prioritization I'm doing a pokemon sql table and created a new column named capacity_difference to determine the collectability of each pokemon in the table. However, when I want to use CASE query to categorize them, the outcomes only show the ELSE condition for every log. Can someone please tell me how to fix the query?
My queries are as follow:
ALTER TABLE pokemon add capacity_difference INTEGER;

SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference from pokemon;

SELECT name, type_1, type_2, HP, (attack - defense) as capacity_difference,
  CASE
    when capacity_difference > 90 then "collect asap"
    when  capacity_difference < 90 and capacity_difference > 50 then "good"
    when capacity_difference > 0 and capacity_difference < 50 then "okay"
    when capacity_difference > -10 and capacity_difference < 0 then "bad"
  ELSE "worse"
  END AS Collectability
FROM pokemon;

The problem is that renaming ( (attack - defense) as capacity_difference ) is not known at the time when your case expression is evaluated. I.e. Your case expression is evaluating the column capacity_difference which you have not assigned a value. I would suggest that you get rid of that column, and do:
SELECT name
     , type_1
     , type_2
     , HP
     , CASE when capacity_difference > 90
            then "collect asap"
            when capacity_difference < 90 and capacity_difference > 50
            then "good"
            when capacity_difference > 0 and capacity_difference < 50
            then "okay"
            when capacity_difference > -10 and capacity_difference < 0
            then "bad"
            ELSE "worse"
       END AS Collectability
FROM (
    SELECT (attack - defense) as capacity_difference
         ,  name
         , type_1
         , type_2
         , HP
    FROM pokemon
) AS t;


MySQL NDB Cluster settings to prefer data node on localhost than remote My current MySQL NDB Cluster configuration:

Server 1 - management node
Server 2 - huge machine running both ndbd and mysqld
Server 3 - huge machine running both ndbd and mysqld
NoOfReplicas = 1

So server 2 has exact copy of server 3 data

During setup evaluation I discovered that mysqld on server 2 is sending about 50% queries to ndbd running on server 3.

With greater number of nodes this would be OK to provide load-balancing, but with my specific configuration (both nodes have same dataset) I'd like to get max performance by telling mysqld on each server to PREFER connecting to ndbd process running on localhost, and only connect ndbd on remote host only if local is not available (ie restart)

I've read something about server access cost table that should contain information for query optimizer about latency between nodes, but I can't find this blog post right now.

Any help?

I would recommend that you upgrade to MySQL NDB Cluster 7.5. Then you can take advantage of a new relevant feature:
https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-system-variables.html#sysvar_ndb_read_backup

With the read_backup feature used in a setup like yours--with only one node group (shard/partition)--all reads can be done locally at the given data node and no cross data node work is ever required.

Another related new aspect of this feature is that when it's used, the API node (the mysqld process) will automatically select/use a data node available on localhost as the transaction coordinator.

So this should give you exactly what you're looking for here! I expect the "read backup" feature to be extremely popular with many MySQL NDB Cluster users, especially those with setups similar to yours. :)

For some additional information:

https://blogs.oracle.com/MySQL/entry/mysql_cluster_7_5_is
http://mikaelronstrom.blogspot.co.uk/2016/10/read-any-replica-in-mysql-cluster-75.html


How to insert in table from remote stored procedure without creating a distributed transaction? How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an insert into a local table.

For SQL Server 2008 and up, you can disable promotions for the linked server.  It's on the Advanced property tab, or you can script it like:

EXEC master.dbo.sp_serveroption
    @server=N'YourLinkedServer',
    @optname=N'remote proc transaction promotion',
    @optvalue=N'false'

For SQL Server 2005, you can add an OLE DB linked server. That allows you to enter detailed options in the connection string.

Here's how to configure such a linked server.  Click Server Objects, then right click Linked Servers and choose New Linked Server....  Configure the server as follows:

Tab "General":

Linked server: enter name of linked server
Check Other data source
Select provider Microsoft OLE DB Provider for SQL Server
Product name: Enter anything here, like ZzzZZzzzz
Enter provider string
Data Source=<server or ip>,1433;Network Library=DBMSSOCN;Initial Catalog=<database name>;Enlist=false;

Tab "Security":

Choose Be made using this security context
Enter username and password

The Enlist=false in the connection string should prevent distributed transactions.


SQL Server 2008 R2 service pack steps .....on cluster I have a sp3 to apply on SQL Server 2008 R2 on windows 2008 R2 cluster with four nodes and 6 instances. Node 4 do not have any resources but is possible owner for all the instances.

Steps I am planing

remove node 4 as possible owner for all SQL related resources
reboot node 4, apply sp3, reboot node 4
put back node 4 as possible owner for all node 1 SQL resources
failover node 1 resources to node 4
repeat step 2 for node 1
repeat step 3 for node 1
failover node 4 resources back to node 1
put back node 4as possible owner for all node 2 SQL resources
repeat step 4 for node 2
repeat steps 5,6,7 for node 2
repeat steps 4,5,6,7 for node

Please verify and confirm or correct my steps.

I guess sp3 + cumulative update for 6 instances per node around 1.5 hrs

Thanks in advance

I would make a slight modification to the procedure to reduce the number of failovers:

Remove Nodes 3 and 4 from the list of possible owners for each SQL Server Cluster Instance, leaving Nodes 1 and 2 as possible owners to preserve failover capability while doing the rolling upgrade.
Apply the Service Pack and latest Cumulative Update to nodes 3 and 4.  Reboot as necessary.
Make nodes 3 and 4 possible owners for all clustered instances.
Failover the clustered instances to either node 3 or 4.
Remove nodes 1 and 2 from the list of possible owners.
Apply the Service Pack and latest Cumulative Update to nodes 1 and 2.  Reboot as necessary.
Add nodes 1 and 2 back in as possible owners.
Leave the instances on node 3 or 4, or fail them back to node 1, if desired.

Microsoft has documentation providing more details if required.


How to make trigger code execute only for a specific application? I was wondering if it is possible to enable a trigger just for my current application and not for any sql executed against the table.

The situation:

There are two applications working on the same database. App1 and App2.

I have a trigger on 'MyTable' which should only be fired when App1 is executing a query, but not when App2 is doing so.

While not a good design, it is doable. You'll need to put logic in the trigger so it causes the code to only be executed when the correct application is connected.

Create trigger...
As
    If app_name() = 'something'
    begin
        put code here
    end


Can I cluster PostgreSQL by sharing a data directory over NFS? I created a PostgreSQL cluster in one host and shared it over NFS share to another host. Even though i can access the cluster using second host but can not use or log in to databases. Is it possible to use a single cluster by multiple hosts? so i can introduce it to clients as a single host and in case the first host that was used to create the cluster is down, the other host keeps the cluster running so clients would not face any connectivity or accessibility issue to that cluster.

Use your PostgreSQL database with a shared disk it's a bad idea at all. Your database probably will crash and you WILL lose data.

If you need HA a solution based on shared disk(Like the RedHat Cluster Suite) do that by your storage device, creating a LUN and mapping it on both servers.

Take a look at this doc from EDB's Website:

http://www.enterprisedb.com/services/packaged-services/high-availability

Provide more details for a better solution.


Why are queries causing spill to tempdb? Background

I am in the process of migrating a 160gb database from MSSQL 2008 (standard) on a Win 2008 server with 48gb RAM to a new server running MSSQL 2012 (64-bit web edition) on Win 2012 with 64gb of RAM. The old server is live and under load; the new server is not in production. The new server has 8 tempdb files (4GB each).

Problem

In testing on the new server I am seeing steps in numerous queries cause alerts mentioning "operator used tempdb to spill data during execution". I have been able to avoid sorts by rewriting some of the queries, but this is not really addressing the issue. The same queries on the old server do not cause spills. I have read that spills happen when MSSQL can't complete an operation in memory and has to spill/page into tempdb. Should I be concerned about spills?

Examples

I have run sp_updatestats on the database, so statistics should be up to date, but you will note there are some discrepancies between the estimated and actual numbers of rows.

Memory concern

I have set a max memory setting for MSSQL of 58 of the 64gb. Currently MSSQL has consumed around 35gb of this memory, but has a working set of only 682mb. The old server (albeit in production, handling load) has 44gb of memory committed to MSSQL of which 43.5gb is in its working set.

I do not know if the spills might be related to a memory setting - anyone have any ideas? MSSQL currently has acres of RAM to spare, so why is it spilling into tempdb for some sorts and hash matches?

There's several different questions in here:

Q: Why weren't the queries spilling before?

They were, but SQL Server Management Studio didn't surface this as a clear error prior to SQL 2012. It's a great example of why when you're doing performance tuning, you have to go deeper than the graphical execution plan.

Q: Why do queries spill to disk?

Because SQL Server didn't grant them enough memory to complete their operations. Perhaps the execution plan underestimated the amount of memory required, or perhaps the box is under memory pressure, or they're just big queries. (Remember, SQL Server uses memory for three things - caching raw data pages, caching execution plans, and workspace for queries. That workspace memory ends up being fairly small.)

Q: How can I reduce spills?

By writing sargable T-SQL statements, having up-to-date statistics, putting enough memory in the server, building the right indexes, and interpreting the execution plans when things don't work out the way you expected. Check out Grant Fritchey's book SQL Server Query Performance Tuning for detailed explanations of all of those.


I have a bunch of SQL databases. How should I put these into Azure? I have a bunch of on-premise SQL databases that contain data related to HR. One database could be pure employee data, such as staff records, leaver records or recruitment records. Another database could be from a external provider of services, which would tell me what benefits my employees are utilising. A final example could be salary review data, where data is generated by the business each year by line managers submitting employee performance scores.
I'm currently exploring Azure as I want a cloud solution for my data. I need to store all of these datasets in a central location, with some standard governance applied. I also need to have this data available to applications such as Power BI for BI requirements and I also need machine learning capabilities for data science projects.
I currently use SSIS to get my data in SQL Server, however my research has suggested using Azure Data Factory, Azure Data Lake and Azure Synapse to manage my data.
I don't really know where to start, any guidance would be great! Thank you.

the first obvious solution could be the adoption of a SQL Server VM hosted on azure.
You can move there databases and ssis packages in any traditional ways.
This is an Azure Iaas solution and usually is the most expensive. You can reduce cost moving your on prem sql license wiht the "Bring Your own license" solution coming from software assurance license mobility option.
But you can also adopt Azure Paas solution using:

Azure SQL databases (https://docs.microsoft.com/en-us/azure/azure-sql/database/)
Azure SQL managed instances (https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview)

I suggest you the second one because it is almost equal to the a on prem sql server instance. But both laks of ssis solution.
You pointed right to Azure Data Factory to relace dtsx packages but you can also run the same dtsx packages inside azure data factory:
https://docs.microsoft.com/it-it/azure/data-factory/create-azure-ssis-integration-runtime
In the meantime you redisign the ssis packages in ADF you can eventually run them temporarly in a Azure VM.
For moving database to an Azure SQL Server Managed instance i suggest you to use DMA:
https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
You can do a online or offline migration. Look at this:
https://azure.microsoft.com/en-us/resources/videos/data-migration-demo-using-azure-migrate-dma-and-dms/
Here you can find a free lab to test the procedure:
https://github.com/microsoft/MCW-Migrating-SQL-databases-to-Azure


In SQL Server would misc pages be pushed out when an object grows within a mixed extent? In SQL Server 2008-2012, objects (including tables) are first put in a mixed extent. An extent can accommodate up to 8 pages and accommodates more than one type of page (meaning: pages from more than one object). Say ExtentA has pages from objects A(2), B(2) and C(4) - numbers in parentheses indicate how many pages they own within that extent. Object B grows, and my question is: Would SQL Server start kicking out A and C's pages before assigning B a new, uniform extent (assuming it eventually outgrows extentA) OR it just jumps to a new extent given extentA is already full.

This question was moved from Stack Overflow to DBA. Comments on the original question:

  Valid question, but why do you ask? Seems to be totally unimportant knowledge. –  usr

  Hi, Thanks for the reply. I'm actually in a very difficult database optimization situation in which I have to crack down on unnecessary spaces being allocated for numerous tables (1000+ tables). So I'm starting from the basics... I couldn't figure out this question because I had no means of tracking it.. I can see that a new extent is being assigned, but I cannot find out whether the previous extent was fully used... –  LearnByReading

It just jumps to a new, uniform extent.  There is no mechanism to try to convert the first mixed extent into individual uniform extents.

If I understand correctly, when the mixed extent has no data in it any longer, it will switch that extent to being unused.  Then it could be used for either a mixed extent or a uniform extent.


drop table from outside? A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running.  The script created a new table and then inserted at least 70 million rows of data into the new table.  Now service.msc in windows 7 will not start MySQL as a windows service.  It gives error 1053.

How can I get MySQL to launch as a windows service?

Here is what I have done do far:

Deleted the ib_logfile* files
Deleted the myservername.err file
Changed the following settings in my.ini:
    innodb_additional_mem_pool_size = 100M
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 50M
Restarted the computer a few times, trying to start the MySQL windows service a few times

I notice the size of the ibdata1 file is 76,812,288 KB

I have thought to just delete the newly created table, which would reduce the size of the database by a huge factor and get things back to what they were before the bad script ran.  It would be nice if I could just drop the table without opening the mysql windows service, or somehow bring the database back to the state it was in just before the script started running, and then just restart the windows service without the damage that is causing it not to start now.  But I do not know how.  Can anyone show me how to get windows to start the MySQL service again?

I uploaded the myservername.err file to a file sharing site.  You can read it by clicking on this link.  Please understand that this error file continues to grow into hundreds of megabytes in size.  The file sharing site just has the first few megabytes.  I imagine it gets repetitive after that.

Your data is corrupt. You might attempt starting the server iterating through the http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html documentation. If it starts you can attempt to save the large table (mysqldump/mydumper export) or drop it if it's not needed.


Can MySQL use an index to calculate SUM? The following query is run in a MySQL database (all InnoDB tables, Percona 5.5), and is slow for some parameter values (i.e. for some large customers).  It returns the total size of all files uploaded by a given customer:

SELECT SUM(document.size)
  FROM document
       INNER JOIN post ON post.id = document.id
       INNER JOIN page ON page.id = post.page_id
 WHERE page.customer_id=1234;

I tried denormalization -- added document.customer_id column and index on document(customer_id, size) -- and got much better query times.  But I'd like to avoid denormalization if possible.  I also tried adding just an index on document(size), but couldn't get MySQL to use it.  The documentation states that every index in an InnoDB table also contains references to the primary key, so why did MySQL not use this new index when joining, and save itself the need to load document row data?

The schema looks like:

CREATE TABLE page (
  INT id NOT NULL,
  INT customer_id NOT NULL,
  ...
  PRIMARY KEY (id),
  INDEX ix_page_customer (customer_id)
  FOREIGN KEY (customer_id) REFERENCES customer (id)
)

CREATE TABLE post (
  INT id NOT NULL,
  ...
  PRIMARY KEY (id),
  INDEX ix_post_page (page_id),
  FOREIGN KEY (page_id) REFERENCES page (id)
)

CREATE TABLE document (
  INT id NOT NULL,
  INT size NOT NULL,
  ...
  PRIMARY KEY (id),
  FOREIGN KEY (id) REFERENCES post (id)
)

A customer contains many pages, a page contains many posts.  Posts can be of different types (e.g. message, document).  There are tables for each of these post subtypes, of which document is one.  One of the larger customers has 435K posts (of which 8K are documents) spread over 25K pages.

The query is fairly fast for smaller customers (i.e lower pages*posts), but slow for larger customers.  Here's EXPLAIN output for the query for a large customer:

+----+-------------+-----------+--------+--------------------------+------------------+---------+-----------------------+-------+-------------+
| id | select_type | table     | type   | possible_keys            | key              | key_len | ref                   | rows  | Extra       |
+----+-------------+-----------+--------+--------------------------+------------------+---------+-----------------------+-------+-------------+
|  1 | SIMPLE      | page      | ref    | PRIMARY,ix_page_customer | ix_page_customer | 4       | const                 | 51200 | Using index |
|  1 | SIMPLE      | post      | ref    | PRIMARY,ix_post_page     | ix_post_page     | 4       | database.page.page_id |     2 | Using index |
|  1 | SIMPLE      | document  | eq_ref | PRIMARY                  | PRIMARY          | 4       | database.post.post_id |     1 |             |
+----+-------------+-----------+--------+--------------------------+------------------+---------+-----------------------+-------+-------------+

Please note that Extra: Using Index doesn't mean that an index is used- you know that because of the columns type:eq_ref and key:PRIMARY. Using index in reality means that you are using the Covering index optimisation to access data.

In your case, you are using and index for all 3 table accesses. If you wanted also the Covering index optimisation, you could create an index on (document.id, document.size), but unless that table is very large and this query is very frequent, you would not see a big difference (in cases where you are disk bound). The index on (document.size), effectively, contains the primary key, but it cannot be used, because it is hidden after the size column. In order to be used for filtering, it must in the first place. Also, in MySQL/Percona 5.5, the hidden primary key can only be used for certain sorting and covering index operations, not for filtering. This last limitation is solved in MySQL/Percona 5.6.

You biggest problem is that your query has to read 50k rows and add them (an index won't solve that). With the above tricks you can make the calculations mostly in memory (assuming a big enough buffer pool), but you still have to sum every single value. You could maintain a duplicated customer_id on document -if that makes sense (that is denormalizing, too)- to avoid 2 table accesses.

But your initial idea of denormalizing, maintaining the value precalculated -can be maintained with a trigger-, or using any other kind of cache -query cache, with all its problems, external cache like memcache, etc.- is probably the way to go if you cannot handle such latencies.


Code required to make column not nullable I have a table where I mistakenly allowed NULL values in a column:

CREATE TABLE EXAMPLE (
    EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL,
    FOO_ID INT,
    CREATED_ON DATETIME DEFAULT CURRENT_TIMESTAMP, -- Oops!
    CODE VARCHAR(50),
    QUOTE DECIMAL(18,2),
    BAR_ID TINYINT NOT NULL DEFAULT 1,

    CONSTRAINT EXAMPLE_PK PRIMARY KEY (EXAMPLE_ID),

    CONSTRAINT EXAMPLE_FK1 FOREIGN KEY (FOO_ID)
    REFERENCES FOO (FOO_ID)
    ON DELETE CASCADE,

    CONSTRAINT EXAMPLE_FK2 FOREIGN KEY (BAR_ID)
    REFERENCES BAR (BAR_ID)
);

CREATE INDEX EXAMPLE_IDX1 ON EXAMPLE (FOO_ID);
CREATE INDEX EXAMPLE_IDX2 ON EXAMPLE (BAR_ID);

It's a fairly unimportant column (not indexed, not foreign key, not used in triggers...) and this is how I'd normally fix it:

ALTER TABLE EXAMPLE
ALTER COLUMN CREATED_ON DATETIME NOT NULL;
-- Runs in 1 second

However, I decided to use a GUI tool (SQL Server Management Studio, but all other tools I've tried do a similar thing) and found the whole process took like 20 minutes. The reason? The automatically generated change code is a 3351-line script that apparently tries to edit every single table on database that has a foreign key pointing to EXAMPLE.EXAMPLE_ID!

ALTER TABLE dbo.SOME_OTHER_TABLE
DROP CONSTRAINT SOME_OTHER_TABLE_FK
GO

BEGIN TRANSACTION
GO
ALTER TABLE dbo.SOME_OTHER_TABLE ADD CONSTRAINT
    SOME_OTHER_TABLE_FK FOREIGN KEY
    (
        EXAMPLE_ID
    ) REFERENCES dbo.EXAMPLE
    (
        EXAMPLE_ID
    ) ON UPDATE  NO ACTION
    ON DELETE  CASCADE

GO
ALTER TABLE dbo.SOME_OTHER_TABLE SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Further digging reveals that the script creates a new Tmp_EXAMPLE table with the final definition and copies all data from EXAMPLE.

Is this complex process necessary? What's the exact problem with plain ALTER TABLE?

Just take care of current NULL values by updating them to a default value.
Then do your ALTER TABLE

Example for an integer

UPDATE [MyTable]
SET [MyColumn] = 0
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] INTEGER NOT NULL;

Example for a varchar

UPDATE [MyTable]
SET [MyColumn] = ''
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] VARCHAR(max) NOT NULL;

Altering a column: null to not null

Security Measure

Before, make sure you backup your database in case of you screw it all

To make short

This whole complex scenario made by SSMS is not necessary there is no problem doing a plain ALTER TABLE