After spending a couple of days. Finally, find the solution. The solution is You have to whitelist IP of a primary and secondary node's in the /etc/hosts file in the primary Node. And Paste this below configuration in the /etc/mysql/my.cnf config file in all secondary node's. Please increment the server_id in all nodes.
etc/hosts file
# Your system has configured 'manage_etc_hosts' as True.
# As a result, if you wish for changes to this file to persist
# then you will need to either
# a.) make changes to the master file in /etc/cloud/templates/hosts.debian.tmpl
# b.) change or remove the value of 'manage_etc_hosts' in
#     /etc/cloud/cloud.cfg or cloud-config from user-data
#
127.0.0.1 .localdomain MYSQL-CLUSTER
127.0.0.1 localhost

123.22.33.44 test1
22.33.44.55  test2
33.44.55.66  test3

#
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

Primary Node
# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
#loose-group_replication_group_name = ""
loose-group_replication_ip_whitelist = "123.22.33.44,22.33.44.55,33.44.55.66"
loose-group_replication_group_seeds = "123.22.33.44:33061,22.33.44.55:33061,33.44.55.66:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 1
bind-address = 123.22.33.44
report_host = 123.22.33.44
loose-group_replication_local_address = 123.22.33.44:33061

Secondary Node
# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
#loose-group_replication_group_name = ""
loose-group_replication_ip_whitelist = "22.33.44.55,123.22.33.44,33.44.55.66"
loose-group_replication_group_seeds = "22.33.44.55:33061,123.22.33.44:33061, 33.44.55.66:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 2
bind-address = 22.33.44.55
report_host = 22.33.44.55
loose-group_replication_local_address = 22.33.44.55:33061

Third Node
# General replication settings
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_bootstrap_group = OFF
loose-group_replication_start_on_boot = OFF
loose-group_replication_ssl_mode = REQUIRED
loose-group_replication_recovery_use_ssl = 1

# Shared replication group configuration
#loose-group_replication_group_name = ""
loose-group_replication_ip_whitelist = "22.33.44.55,123.22.33.44,33.44.55.66"
loose-group_replication_group_seeds = "22.33.44.55:33061,123.22.33.44:33061, 33.44.55.66:33061"

# Single or Multi-primary mode? Uncomment these two lines
# for multi-primary mode, where any host can accept writes
#loose-group_replication_single_primary_mode = OFF
loose-group_replication_enforce_update_everywhere_checks = ON

# Host specific replication configuration
server_id = 3
bind-address = 33.44.55.66
report_host = 33.44.55.66
loose-group_replication_local_address = 33.44.55.66:33061

I followed this tutorial https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04


How to restore replication after server crash

In the end I simply did what the server wanted, i.e. created all the "publication" tables (*dbo.MSpeer_** and *dbo.sys**) in the problematic database (the one that was the publisher), through Script Table As -> CREATE To dialog. Thanks to this server allowed me to disable publishing and distribution. Then I only needed to reconfigure replication.

Hope, next time it crashes (actually I hope it won't), we'll be wise enough to backup tables involved in replication thoroughly.

Additional information by Zane, originally left in a comment:

The tables dbo.MSpeer_* and dbo.sys* can also be created by configuring the distributor/publisher on a different server with SQL Server installed. This will create these tables in the database that you've set for publishing. See -> Tables - >System Tables in SSMS.

Then, generate the schemas for all these tables and then create these tables in the problematic database which will allow you to disable distribution and publishing and reconfigure the distributor.


How to Disable and Drop all Temporal Tables from a database

I believe that's what you need.

SET NOCOUNT ON ;
DECLARE @cmd NVARCHAR (MAX);

DECLARE
    @SchemaName VARCHAR(100),
    @TableName VARCHAR(100),
    @HistorySchemaName VARCHAR(100),
    @HistoryTableName VARCHAR(100);

SET @cmd = '' ;

SELECT TOP ( 1 )
       @SchemaName = SCHEMA_NAME(T1.schema_id ),
       @TableName = T1 .name,
       @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
       @HistoryTableName = T2 .name
FROM sys .tables T1
LEFT JOIN sys .tables T2
    ON T1.history_table_id = T2 .object_id
WHERE T1. temporal_type = 2
ORDER BY T1. name;

WHILE @@ROWCOUNT = 1
    BEGIN
        SET @cmd = 'ALTER TABLE ' + QUOTENAME (@SchemaName ) + '. ' + QUOTENAME(@TableName) + ' SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE '       + QUOTENAME (@HistorySchemaName ) + '.' + QUOTENAME(@HistoryTableName );
        EXEC sp_executesql @cmd

        SELECT TOP ( 1 )
               @SchemaName = SCHEMA_NAME(T1.schema_id ),
               @TableName = T1 .name,
               @HistorySchemaName = SCHEMA_NAME(T2.schema_id ),
               @HistoryTableName = T2 .name
        FROM sys.tables T1
        LEFT JOIN sys.tables T2
            ON T1 .history_table_id = T2 .object_id
        WHERE
            T1 .temporal_type = 2
            AND T1 .name > @TableName
        ORDER BY T1 .name;
    END;


How to record prices for the product sales?

What you really should do (and is how a lot of similar type systems architect their schema) is have two normalized tables that store sales information.
One table is the SalesHeader (or sometimes just called SalesOrder) and the other table is the SalesLine. SalesHeader has a one-to-many foreign key relationship to SalesLine on the OrderId.
The SalesHeader stores basic information like OrderId (as the primary key) and SaleDate, where the SalesLine table has a new record for each ProductId with Quantity and Price columns, and its own primary key column called LineId.
This allows you the flexibility to have the same product sold on the same date at different prices, or even multiple products on the same date at different prices, and even batch different orders up (if you wanted). You can adjust the SalesHeader and SalesLine tables as what's more relevant to your use case, but that's the general solution. (Also please don't mind that I use PascalCasing when naming my obejcts. :)


In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement?

It makes a difference if this is going to be a set-based trigger, or if you are designing it to handle only one row at a time, but this should get you pointed in the right direction.

IF
UPDATE(col1)
AND UPDATE(col2)
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3 AND col2 = 1.1)
BEGIN
    DO STUFF
END

IF
UPDATE(col1)
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3)
BEGIN
    DO STUFF
END


Thoughts about Oracle backup procedure for 11.2 (beginner question)

first think about what it costs if the database gets lost. For this there are several levels.

lost completely and forever because of lack of backup
lost production because you perform a restore without being able to recover last TX.
lost production because the site was unavailable

For all scenarios there are solutions. Normally I work for sites that won't allow the loss of any data. Most of the times they also want protection against unplanned downtime. This is the ultimate protection. As most things, it also has a price. Most protection has the highest cost. But if you need this, there will be a business case for it. That is why you should start with the question what dataloss costs your company. This will give you a budget.

For documentation start with DBA Essentials
A very nice third party solution for DR can be found here dbvisit standby

Hiring support from a good dba can make a huge difference for your company. A good dba has your business in mind when he/she proposes solutions. Your question a asking a bit much. You need dba support. For an overview pick the Oracle concepts guide. Read it and use it to help you hire a good dba.


Do I need to rebuild heap table after rebuilding a non-clustered index?

Do I need to rebuild a heap table after rebuilding a non-clustered index?

Rebuilding a heap automatically rebuilds any non-clustered indexes on the table (because the pointers to where the rows are changes during this process).

I would not rebuild a heap after rebuilding the NC index, because this would mean the NC index is rebuilt twice (which is just wasteful).

Rebuilding the NC index won't fix fragmentation in the heap.  So if you need to fix the heap fragmentation, rebuild that first (or put a clustered index on it), and both will be fixed.


Oracle Enterprise Manager - Where do I find the error logs?

ORACLE_HOME/HOSTNAME_SID/sysman/log

... is where I finally found them. I don't know if this is system dependent; this was on a linux box.


Notification on long-running query or deadlock in SQL Server 2008 R2?

You can configure alerts for both of these with SQL Agent.  Create a new alert and select type "SQL Server performance condition alert"

For long running queries, choose Object "MSSQL$InstanceName:Transactions" and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options, and you're good to go.

For deadlocks, the Object is "MSSQL$InstanceName:Locks" and the Counter is "Number of Deadlocks/sec"

If you want more fine grained control of the deadlock notification, check this out:
http://www.sqlservercentral.com/articles/Administration/3243/


transaction log growing out of control during maintenance

You should be able to do batch updates to this table without causing the log to grow uncontrollably.

Here is a simple testbed to show how I'd approach this:

USE tempdb;

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , COL1 nvarchar(150) NOT NULL
    , COL2 nvarchar(150) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.Table1 (COL1, COl2)
SELECT N'     ' + sc1.name + N'     '
    , N'     ' + sc2.name + N'     '
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

GO

This actually removes the leading and trailing spaces, 4,000 rows per batch:

DECLARE @LogFreePercent DECIMAL(5,2);

/* this temp table will hold all the ID values for
   rows we need to update
*/
IF OBJECT_ID(N'tempdb..#ids', N'U') IS NOT NULL
DROP TABLE #ids;
CREATE TABLE #ids
(
    id int NOT NULL
) ON [PRIMARY];
CREATE CLUSTERED INDEX #ids_cx ON #ids(id);

/* this temp table will hold all the ID values for
   rows we've already updated
*/
IF OBJECT_ID(N'tempdb..#fixed_ids', N'U') IS NOT NULL
DROP TABLE #fixed_ids;
CREATE TABLE #fixed_ids
(
    id int NOT NULL
) ON [PRIMARY];
CREATE CLUSTERED INDEX #fixed_ids_cx ON #fixed_ids(id);

/* Insert the ID values for rows with leading or trailing
   spaces.  This is not efficient, but should do the trick,
   and ensures we only update rows that actually have leading
   or trailing spaces.
*/
INSERT INTO #ids (id)
SELECT id
FROM dbo.Table1 WITH (NOLOCK)
WHERE COL1 LIKE ' %'
    OR COl1 LIKE '% '
    OR COL2 LIKE ' %'
    OR COl2 LIKE '% ';

/* Run a loop while we still have IDs that need processing
*/
WHILE EXISTS (SELECT TOP(1) 1 FROM #ids)
BEGIN
    /* Use a CTE to select 4000 rows to update from the
       temp table, outputing the IDs we've fixed into
       the #fixed_ids table.
    */
    ;WITH src AS (
        SELECT TOP(4000) Table1.*
        FROM dbo.Table1
            INNER JOIN #ids i ON i.id = Table1.id
    )
    UPDATE src
    SET COL1 = LTRIM(RTRIM(COL1))
        , COL2 = LTRIM(RTRIM(COL2))
    OUTPUT inserted.id
    INTO #fixed_ids;

    /* Show some status details
    */
    PRINT (CONVERT(nvarchar(30), GETDATE(), 120) + N' - updated '
        + CONVERT(nvarchar(20), @@ROWCOUNT) + ' rows.')
    SELECT @LogFreePercent = 100 - su.used_log_space_in_percent
    FROM sys.dm_db_log_space_usage su;
    PRINT (N'Log free percent: ' + CONVERT(nvarchar(10), @LogFreePercent));

    /* remove the processed rows from the temp tables
    */
    DELETE FROM #ids
    WHERE EXISTS (SELECT 1 FROM #fixed_ids fi WHERE fi.id = #ids.id);
    DELETE FROM #fixed_ids;

    /* checkpoint the database to allow log records to be truncated
    */
    CHECKPOINT 1;
END

Run this afterwards to see if any rows have leading or trailing spaces:

SELECT [1] = '|' + t1.COL1 + '|'
    , [2] = '|' + t1.COL2 + '|'
FROM dbo.Table1 t1;

You may notice I've got the dreaded WITH (NOLOCK) in the query that reads from the source table, dbo.Table.  This is to help prevent our process from blocking other ongoing transactions.  Since we're only using this to create a list of IDs we want to operate on, this is an acceptable use of READ UNCOMMITTED.

In my slow dev database, this processed 564,000 rows in just under three minutes, and the log never dipped below 97% free.


SQL 2005 table data type "text" to nvarchar

If you want to know what data is there, then:

Don't use LTRIM or RTRIM
Use DATALENGTH() (for NTEXT add / 2 to the end of that) instead of LEN

Now, converting from either TEXT or NTEXT to NVARCHAR won't result in data loss if the length is the same or larger than what was calculated assuming what is mentioned above.

HOWEVER, the real issue isn't what data is there, it's what does the app allow for. If the app allows for more than 200 characters, then 200 isn't a safe choice. Make sure that all sources that can insert or update data in this column enforce a limit of 200 characters. If even one does not, then you need to work with the devs to make sure any sources of input are updated to reflect a limit of 200, or whatever you agree on (just because nobody has yet used even 200 characters doesn't necessarily imply that there isn't a valid case for going above that limit, if the app allows for it).

I just had the thought that there could be a slight issue with maximum row size since the TEXT, NTEXT, and IMAGE datatypes, by default, are a 16 byte pointer in the row with the actual data off row. Since you only get approximately 8060 bytes per row, if you were less than 400 bytes from that limit, then changing TEXT (requiring 16 bytes) to NVARCHAR(200) (requiring 400 bytes) could potentially result in an error. However, I just tried it and doesn't error; it allows for going above the 8060 byte limit (keep in mind that the max row size is still approx. 8060, but any extra data will be placed on ROW_OVERFLOW pages).

USE [tempdb];

-- DROP TABLE dbo.AlterColumnTest;
CREATE TABLE dbo.AlterColumnTest
(
  [ID] INT NOT NULL PRIMARY KEY,
  [EnglishSource] VARCHAR(50) COLLATE Latin1_General_CI_AS,
  [EnglishText] TEXT COLLATE Latin1_General_CI_AS,
  [HebrewSource] VARCHAR(50) COLLATE Hebrew_CI_AS,
  [HebrewText] TEXT COLLATE Hebrew_CI_AS,
  [Spacer] CHAR(7500) NOT NULL DEFAULT ('')
);

DECLARE @TestString VARBINARY(100);
SET @TestString = 0x4120746573743A20C0C620CBE0E2E8EC20F0F4F92021;
SELECT CONVERT(VARCHAR(50), @TestString); -- A test: ÀÆ Ëàâèì ðôù !

INSERT INTO dbo.AlterColumnTest ([ID], [EnglishSource], [HebrewSource])
VALUES (1, @TestString, @TestString);

SELECT * FROM dbo.AlterColumnTest;

UPDATE dbo.AlterColumnTest
SET    [EnglishText] = [EnglishSource],
       [HebrewText] = [HebrewSource];

SELECT * FROM dbo.AlterColumnTest;

ALTER TABLE dbo.AlterColumnTest
  ALTER COLUMN [EnglishText] NVARCHAR(200) COLLATE Latin1_General_CI_AS;

ALTER TABLE dbo.AlterColumnTest
  ALTER COLUMN [HebrewText] NVARCHAR(200) COLLATE Hebrew_CI_AS;

SELECT * FROM dbo.AlterColumnTest;

SELECT *
FROM   sys.columns sc
WHERE  sc.[object_id] = OBJECT_ID(N'dbo.AlterColumnTest');

Values for @TestString taken from:

Code Page 1252 Windows Latin 1 (ANSI)
Code Page 1255 Windows Hebrew


Removing the uses of MySQL temporary tables

Sometimes i use temporary tables to create complex reports. A relatively simple (and meaningless) example could be something like

drop temporary table if exists some_temporary_table1;
create temporary table some_temporary_table1
    select t1.someColumn, max(t2.someOtherColumn) as someMax
    from some_table1 t1
    join some_table2 t2 using(someJoinColumn)
    where t1.someConditionColumn = 'someConditionValue'
    group by t1.someColumn
;

drop temporary table if exists some_temporary_table2;
create temporary table some_temporary_table2
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from some_temporary_table1 tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax
;

select *
from some_temporary_table2 tmp2
join some_table3 t3 using(someDifferentJoinColumn)
where t3.someFancyConditionColumn = 'someFancyConditionValue'

Same result can be achieved without temporary tables, eliminating them step by step.

Step 1 - eliminate some_temporary_table1, replacing its occurrence with its creating code:

drop temporary table if exists some_temporary_table2;
create temporary table some_temporary_table2
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from (
        select t1.someColumn, max(t2.someOtherColumn) as someMax
        from some_table1 t1
        join some_table2 t2 using(someJoinColumn)
        where t1.someConditionColumn = 'someConditionValue'
        group by t1.someColumn
    ) tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax

Step 2 - eliminate some_temporary_table2

select *
from (
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from (
        select t1.someColumn, max(t2.someOtherColumn) as someMax
        from some_table1 t1
        join some_table2 t2 using(someJoinColumn)
        where t1.someConditionColumn = 'someConditionValue'
        group by t1.someColumn
    ) tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax
) tmp2
join some_table3 t3 using(someDifferentJoinColumn)
where t3.someFancyConditionColumn = 'someFancyConditionValue'

If more temporary tables are involved, this can end up in a highly unreadable (and unmaintainable) code. To keep your code readable you can build the query in PHP using string variables for subqueries:

$subQuery1 = "
    select t1.someColumn, max(t2.someOtherColumn) as someMax
    from some_table1 t1
    join some_table2 t2 using(someJoinColumn)
    where t1.someConditionColumn = 'someConditionValue'
    group by t1.someColumn
";

$subQuery2 = "
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from ( {$subQuery1} ) tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax
";

$mainQuery = "
    select *
    from ( {$subQuery2} ) tmp2
    join some_table3 t3 using(someDifferentJoinColumn)
    where t3.someFancyConditionColumn = 'someFancyConditionValue'
";

which is similar to the original temporary table solution.

In terms of performance - it usually doesn't make much difference.
Sometimes the temporary-tables-solution is faster.
Sometimes it's the one-statement-solution. But mostly it's not more than 10%.
In an old example i just tested, the one-statement-solution was a bit was faster,
even though i gave some hints like to use smaller data types (smallint instead of bigint),
creating indexes on temp tables to use for the next join and using a temp table twice.
In another example i remember from work, the temporary-tables-solution has been faster,
even though i didn't use any hints.


Archive backups with a PowerShell script launched by SQL Server Agent

I believe the problem you're having has to do with SQL Server Agent Tokens as referred to by this Stack Exchange post SQL Agent - PowerShell step “syntax error”

Excerpting from that post

  This is a not very intuitive and I was never able to find anything
  concrete on the explanation [e.g. no exact BOL or white paper was
  found]. The syntax error in the SQL Agent job is a T-SQL syntax error
  based on User Tokens. So that basically means that a PowerShell
  Sub-Expression Operator is treated as a Token to SQL Server Agent. So
  in PowerShell this $( ) appears to be treated as reserved character
  sequence for SQL Server Agent.

In your example, the problem line is this

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"

Notice the $( which is a Powershell sub-expression.  This part is attempting to be interpreted by the SQL Server Agent as a token.

My suggestion would be to separate out the sub-expression into another Powershell variable ($DAILYDIRDAY) and then use that to finish building the $DAILYDIR variable.

Give this a try:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"
$DAILYDIRDAY = $DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__]
$DAILYDIR = "$ArchivesQPath\$DAILYDIRDAY"

and follow up with the rest of the Powershell script


Combine results of two queries, where the second relies on the first

I would use a CTE for the first query and reuse it for the second query:

WITH cte AS (
   SELECT co.name, sum(c.total) AS total, c.created_at
   FROM   c_report c
   JOIN   contact  co ON co.id = c.contact_id
   GROUP  BY co.name, c.created_at
   )
SELECT * FROM cte
UNION ALL
SELECT po.name, sum(p.total), p.created_at
FROM   p_report p
JOIN   person   po ON po.id = p.person_id
WHERE  EXISTS (SELECT 1 FROM cte WHERE created_at = p.created_at)
GROUP  BY po.name, p.created_at;

Be sure to use UNION ALL, not UNION. You don't want to remove duplicates, and it's faster that way.


This would depend on which one of the repeat you want to keep. For this example, I will dedup the table based on url and title and keeping the first occurrence.

First thing to do: run these queries

SELECT COUNT(1) FROM RSS_items;

SELECT COUNT(1) dupcount_two,url,title FROM RSS_items
GROUP BY url,title HAVING COUNT(1) = 2 WITH ROLLUP;

SELECT COUNT(1) dupcount_morethantwo,url,title FROM RSS_items
GROUP BY url,title HAVING COUNT(1) > 2 WITH ROLLUP;

This will show total table count and how all (url,title) combinations that repeat twice and more than twice with the total for each at the bottom. If the sum of the repeats is more than 5% of the total:

CREATE TABLE RSS_items_URLTitle LIKE RSS_items;
INSERT INTO RSS_items_URLTitle (id,url,title)
    SELECT MIN(id),url,title
    FROM RSS_items GROUP BY url,title
    HAVING
;
DELETE B.*
    FROM RSS_items_URLTitle A
    INNER JOIN RSS_items B
    USING (url,title) WHERE A.id <> B.id
;
DROP TABLE RSS_items_URLTitle;

This method made be very slow if you do not have an index on (url,title)

Otherwise, run this

CREATE TABLE RSS_items_New LIKE RSS_items;
CREATE TABLE RSS_items_URLTitle LIKE RSS_items;
INSERT INTO RSS_items_URLTitle (id,url,title)
    SELECT MIN(id),url,title
    FROM RSS_items GROUP BY url,title
;
INSERT INTO RSS_items_New
    SELECT B.*
    FROM RSS_items_URLTitle A
    INNER JOIN RSS_items USING (id)
;
ALTER TABLE RSS_items RENAME RSS_items_Zap;
ALTER TABLE RSS_items_New RENAME RSS_items;
DROP TABLE RSS_items_URLTitle;
DROP TABLE RSS_items_Zap;

This method may be better since it only operates on the table id.

Please look over both methods. Try testing it on test databases with copies of the data before running anything.

If you want to keep the last occurrence of duplicates, replace MIN(id) with MAX(id).

Give it a Try !!!


How to partition an Oracle table with 65 million rows?

Partitioning is a licensable option for the Enterprise Edition.  This makes it expensive.  Unless you already have purchased it this is not the way to go.  And even it you have purchased it, it might not be the right approach.

Why not start with more straightforward tuning?  Obviously you have neglected to provide even cursory information regard data distribution (how many distinct values of FIELD_ID?) or database version, let alone something as useful as an explain plan, so the following are guesses.

  "Show me the components where field id 5 = '03530'"

That sort of query would most likely benefit from a compound index on:

tblValues(field_id, comp_id)

  dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Is the sort of thing which is bound to suck.  You show those sorts of queries are common.  In which case you need to use Oracle Text.  That is designed for indexing and searching large free text fields.   Find out more.

In your case I think you need a CTXCAT index; in Oracle 11g we can build Composite Domain Indexes which mix CTXCAT indexes with regular columns.  You might get some good results from creating a CTXCAT index on value ad creating sub-indexes on (field_id) and perhaps (field_id, comp_id).  Find out more.


Typically using a NOT EXISTS condition is the most efficient way. But you also need to generate the combination of all possible countries and dates through a cross join before finding those that don't exist.
Without a lookup table, this would be something like:
with countries as (
  select distinct country
  from units_sold
)
select c.country, d.year, d.month, d.week
from countries c
  cross join dates d
where not exists (select *
                  from units_sold u
                  where (u.year, u.month, u.week) = (d.year, d.month, d.week))
order by c.country, d.year, d.month, d.week;

If you do have a lookup table with the countries, you can remove the CTE (with countries ...)  and replace it with the real table.


Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

I would use countryID, CustomerID as your unique key as you have stated that you will have a unique customer id for each country. I would start with countryID as that will be unique in the system and you can add in customerID in order to get a unique key for each customer.


Get value from one field, and paste it in to a new column

Edited OP code:

Function Macro1()
'' dim variables
Dim tmp As String, temp() As String
Dim rs As Recordset
'' open source for reading
Open "J:\80_Applications\SAPGuiScripting\01_TXT_Files\CM01_Total.csv" For Input As #1
'' attach recordset to table
Set rs = CurrentDb.OpenRecordset("CM01_Total_1")
'' loop source - read line-by-line until its end
Do Until EOF(1)
  '' read one line
  Line Input #1, tmp
  '' look if it is a header, if yes, extract and store the number
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Trim(Mid(tmp, 17,8))
  '' look if it is a data line
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    '' divide the line to separate fields
    temp = Split(tmp, "|")
    With rs
      '' add new, empty, record
      .AddNew
      '' set values into fields
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      ![freie_Kap] = temp(5)
      !Einh = temp(6)
      '' save editions
      .Update
    End With
  End If
'' loop to reading the next line
Loop
'' close source file and recordset
Close #1
rs.Close
Set rs = Nothing
End Function

PS. Error handler must be added.


How to uninstall SQL server 2012 not showing in control panel's "Programs and features"

My guess is since you have remamants of failed installation still present in your system that is why you cannot install the new instance. This can be fixed by deleting few registry keys related to SQL server. Follow below method , this method was given by Microsoft forum team and is recommended way to remove have helped me and forum users many time . If you have multiple instances below suggesstion  would not work so dont use it.

First please refer to below Microsoft support link for what actions to follow before removing SQL Server instance. Its is for 2008 r2 but would be applicable to 2012 as well.

http://support.microsoft.com/kb/955499/en-gb

If still you face problem you might as well try below suggesstion which involves removing registry entries.

Note: Removing necessary registry entries might cause inconsistency so backup registry before doing changes.

•1. Uninstall the existing SQL Server  and all the components from the add remove program.

•2. Backup the registry  . Please read in details the MS link before proceeding.

•3. Delete the following keys using regedit:

◦--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

◦--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

•4. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall and delete all the sub-keys referencing SQL Server.

•5. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete all the keys referencing SQL Server.

•6. Rename all the SQL Server folders in the computer.

•7. Reboot the machine.

If you dont find this useful or have confusion dont proceed. Open a case with Microsoft and allow them to have a look at your system. with my experience with many failed installation registry keys are left in corrupted state and only solution would be to remove them.But you need expert to fiddle with registry

PS: Above method was suggested in SQL server forum and works well.

Of course if you can please revert it would be helpful


mongodb - different keyfiles for different replica sets?

The keyfile is used to secure intra-cluster communication, so all components of a sharded cluster (mongod and mongos) must use the same keyfile.

The documentation you referenced is for a single replica set deployment. For a sharded cluster see: Enforce Keyfile Access Control in Sharded Cluster.


Sample table and data

CREATE TABLE dbo.Table1
(
    RowID integer IDENTITY PRIMARY KEY,
    Col2 integer NULL,
    Col3 integer NULL,
    Col4 integer NULL,
    Col5 integer NULL
);

INSERT dbo.Table1
    (Col2, Col3, Col4, Col5)
VALUES
    (1, 1, 1, 1),
    (1, 3, 2, 6),
    (1, 1, 1, 1),
    (1, 1, 1, 1),
    (1, 1, 1, 1),
    (1, 1, 1, 1);

Trigger

CREATE TRIGGER Table1_AU
ON dbo.Table1
AFTER UPDATE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    SELECT
        I.RowID,
        Changed.Name,
        Changed.Old,
        Changed.New
    FROM Inserted AS I
    JOIN Deleted AS D
        ON D.RowID = I.RowID
    CROSS APPLY
    (
        -- Unpivot only changed columns
        SELECT 'Col2', D.Col2, I.Col2
        WHERE NOT EXISTS (SELECT D.Col2 INTERSECT SELECT I.Col2)
        UNION ALL
        SELECT 'Col3', D.Col3, I.Col3
        WHERE NOT EXISTS (SELECT D.Col3 INTERSECT SELECT I.Col3)
        UNION ALL
        SELECT 'Col4', D.Col4, I.Col4
        WHERE NOT EXISTS (SELECT D.Col4 INTERSECT SELECT I.Col4)
        UNION ALL
        SELECT 'Col5', D.Col5, I.Col5
        WHERE NOT EXISTS (SELECT D.Col5 INTERSECT SELECT I.Col5)
    ) AS Changed (Name, Old, New)
    WHERE NOT EXISTS
    (
        -- Only changed rows
        SELECT I.Col2, I.Col3, I.Col4, I.Col5
        INTERSECT
        SELECT D.Col2, D.Col3, D.Col4, D.Col5
    );
END;

Update statement

UPDATE dbo.Table1
SET Col3 = 0,
    Col4 = 4,
    Col5 = 5
WHERE
    RowID = 2;

Output

Note the returning results from triggers is deprecated, but presumably the question is a simplified version of the actual requirement. This code assumes the RowID column will never change (and cannot, in this example due to the identity property).


How to define User/Permission relationship as M:M?

Just an idea - should there be a terenary relationship in there?  There seems to be a three way relationship between the resource, permission and the user.


SQL Server 2017 Availability Groups - Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?

Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?

Only if it blocks the REDO thread(s) which would require the use of DDL on the primary and a workload that is blocking said DDL from acquiring the needed Sch-M lock on the same object(s).

Long running transactions don't "stop" you from seeing updates but a plethora of other items will, such as:

Slow Redo
Slow network
Slow disk
CPU/Memory pressure
Synchronous/Asynchronous
Readable secondary replicas use SNAPSHOT isolation under the covers

  Has SQL Server 2017 (or 2014/2016) removed this limitation?

AFAIK there was never a limitation except that your log is going to grow which could cause other issues. It could also present issues with readable secondary replicas if this impacts or blocks ghost cleanup - that's generally what I've witnessed and in those cases the transactions were open on an extremely busy system for 24-48 hours. Since Snapshot isolation is used, your tempdb is going to take a hit due to version store and there is a limit. I'm not exactly sure what the quoted text you have is referring to as it doesn't explain the scenario(s).


First, examine what the distribution agent saved about the error by executing select * from [dbo].[MSrepl_errors] order by id desc in the distribution database.

When it comes to poor performance in the distribution database, I've experienced several problems. Besides excessive blocking, which I'm sure you are watching, there are several maintenance related issues. First, I've had virtual log file issues slow my replication in the past. When this happened, there was a noticable impact on performance. To address this run dbcc loginfo on the distribution database. If you get over 100 records returned, it's a slight concern. If you get thousands of records returned, then it's a real problem. Fixing it includes changing the autogrowth settings for the log file to a reasonable value, shrinking the log file, then re-initializing it back to its original size. Unfortunately, the default autogrowth size for data files is 1MB, which should be increased as well.

If you don't have this database included in the index and statistics maintenance, you could experience query timeout issues. I once had a specific table in a user database used by a procedure with a recursive function and if this table got fragmented slightly, the procedure would run very long.

It is also possible to use Profiler or Extended Events to trace all of the commands that the problematic distributor executes to determine which command times out. Locate the SQL Agent job that runs the distribution agent with the issue and filter by program name using that job name.

I'm also not averse to creating new indexes in the distribution database, if necessary. I use the query below to help with this, but don't just create everything without reviewing them to see if you can consolidate.

    USE distribution

    SELECT so.create_date
,dm_mid.database_id AS DatabaseID
,dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) Avg_Estimated_Impact
,dm_migs.last_user_seek AS Last_User_Seek
,OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE
    WHEN dm_mid.equality_columns IS NOT NULL
        AND dm_mid.inequality_columns IS NOT NULL
        THEN '_'
    ELSE ''
    END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE
    WHEN dm_mid.equality_columns IS NOT NULL
        AND dm_mid.inequality_columns IS NOT NULL
        THEN ','
    ELSE ''
    END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
,dm_migs.user_seeks
,dm_migs.user_scans
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON
dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
ON
dm_mig.index_handle = dm_mid.index_handle
    JOIN sys.objects so
ON
so.object_id = dm_mid.OBJECT_ID
    WHERE
dm_mid.database_ID = DB_ID()
    ORDER BY
Avg_Estimated_Impact DESC

If all of this still does not resolve the issue, then locate the job for the distribution agent and add the parameter -ErrorFile (path and filename local to the server). Monitor the growth of this file because it might grow faster than you expect, but it will contain more details about the problem.


Occasional error when running sp_BlitzFirst

You'll get that error when one of the columns in the select won't fit into the destination table.

Tracking it down can take some time, but it's just like kindergaten - try and fit the shapes in the holes.

If you compare #Variable_info to #stored_proc_info, you will probably find that one of the columns in #variable_info is larger than its corresponding column in #stored_proc_info.

I can't find #variable_info in my version of the sp_blitzcache, so I can't tell which columns don't match.

Often, I track it down by running a query like this:

SELECT len(vi.SPID) as spid, len(vi.SqlHandle) as handle, len(vi.QueryHash as hash), len(vi.variable_name) as variable_name,
len(vi.variable_datatype) as data_type, len(vi.compile_time_value) as compile_time, len(vi.proc_name) as proc_name
FROM #variable_info AS vi;

and looking for values that are too big.


For a relatively immutable tracking of history, System Versioned Temporal Tables may be the way to go as you are using a recent enough edition of SQL Server (similar support is available in some other databases). They have some gotchas that mean they are not suitable for all circumstances but if they fit your model they'll make life easier by implementing chunks of it for you "for free" and allowing you to use some useful time-travelling syntax such as SELECT <stuff> FROM <table> AS OF <date_time> and BETWEEN <date_time> AND <date_time> instead of more manually querying the history.

For an audit you need extra though, as well as what happened when you need to track who made the change. One option would be to add a user identifier column to each table being tracked, which would be included in the history too. If you are tracking users that all have their own SQL Server login then you can populate that by trigger, reading the value from SYSTEM_USER/SUSER_NAME(), though in the more common case of tracking users of an application that always logs into SQL Server using the same application account you will need to implement the population that column in your application logic.

  Is one design better than the other

Your second suggestion is essentially an extended property bag (where each property is actually property+time) which makes me wary of it. It is potentially flexible, but has its own significant issues which, for instance, make the model inefficient to report from. Lookup "Entity Attribute Value model" (the more technical term that "property bag" is usually a synonym for), on DBA.SE and elsewhere, for detailed discussions as to the pros and cons of this. It is commonly considered to be an anti-pattern.

isCurrent

If you do roll your own history structure instead of using the built-in support (because the built-ins are unsuitable for some reason, or you need to implement something more cross-platform compatible, for instance) then I would recommend against keeping all the data in your core tables and instead keeping current data on its own and a separate history. This will make querying current data, likely the most common thing you application will do, less error prone. If you keep all versions1 in your history structure you may still have an isCurrent flag in those structures.

1 SQL Server's temporal tables don't keep the latest version of a row in the history table (unless it has been deleted, depending on if you conceptually consider the last existing version to be the latest or not) only copying the previous version in as it is modified. Some history/audit implementations do hold the latest version of each row as well as the past ones so that the history table contains everything.


the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

If you have the ability to create a foreign key constraint, you are actually getting permission to check for the existence/non-existence of a certain value in a certain column (or set of columns) of a table. You may not have the privilege to SELECT the whole list of values in this column, but you can already know something about them. You could try to insert values on the referencing table. The ones that are allowed, you already know exist in the referenced table.

If you should know absolutely nothing about those values, you cannot be granted the privilege to make a foreign key constraint on the column.

Add to that the considerations pointed out by @Erwin, WRT to being able to prevent deletes from the referenced tables.


You will need a locally installed PostgreSQL of the same major as the data directory archive. For example, if the archive came from 9.1.5 you would need some 9.1.x on the local machine, it doesn't matter if it's 9.1.0 or 9.1.9, but it must be 9.1 not 9.2 or 9.0.

If you don't have 9.1, you can install it from packages or just download the source and compile it locally.

Then, if you just want to dump the data to reload into an existing local PostgreSQL install:

Unpack the archive
If necessary set your PATH so that PostgreSQL 9.1's bin directory is first on the PATH
PGPORT=5435 pg_ctl -D /path/to/unpacked/archive start
PGPORT=5435 pg_dumpall --globals-only
Dump each database with pg_dump

If you instead want to restore the database so it runs instead of whatever's locally running, you must:

Ensure the current install is of the same major version
Stop the current PostgreSQL: service postgresql stop
Rename the data directory. On CentOS it'll be in /var/lib/pgsql/9.1/data/, so something like sudo mv /var/lib/pgsql/9.1/data /var/lib/pgsql/9.1/data.old. Do not delete it; if it turns out there is data in there you forgot you needed, this way you can still get it back.
Move the unpacked archive to the old data directory location, e.g sudo mv /path/to/unpacked/archive/folder /var/lib/pgsql/9.1/data
Set the permissions: sudo chown postgres:postgres /var/lib/pgsql/9.1/data; sudo chmod 700 /var/lib/pgsql/9.1/data
Edit postgresql.conf to reflect any required changes for the local install
Start PostgreSQL: service postgresql start

Some details like service name, path, etc will vary depending on whether you're running the original Red Hat packages of PostgreSQL, or whether you installed from http://yum.postgresql.org/ .


Use Powershell to query SSAS connection string

Woohoo!!!

# Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

## Connect and get the edition of the local server
$connection = “localhost”
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

foreach ($d in $server.Databases )
{

    Write-Output ( “Database: {0}, String {1}:” -f $d.Name, $d.DataSources.ConnectionString)

} # Databases

returns:

Database: EnvironmentalAnalysisService, String Provider=SQLNCLI11.1;Data Source=LAP123;Integrated Security=SSPI;Initial Catalog=EnvironmentalDataMart:
Database: PerformanceAnalysisService, String Provider=SQLNCLI11.1;Data Source=DEV-EDW;Integrated Security=SSPI;Initial Catalog=PerformanceDataMart:

PS SQLSERVER:\SQLAS>


How to prevent Dropping of tables but allow Creating of them, within a Schema

Expanding on the suggestion of @Dominique Boucher.

You can do it by making a procedure that acts as proxy for the create statement and specifying an execute as clause in it. If you then only allow execute permissions to the users that you want, they can only create tables, not edit them. This way don't become owners (the execute as user becomes owner) and they can't edit the procedure itself.

GRANT EXECUTE ON [dbo].[TableBuilder] TO [Plebby]

Below an example, with some checking/error handling, but you would need to expand on it to suit your needs. You would create this procedure on a highly priviliged user.

CREATE PROCEDURE TableBuilder
@schemaName sysname,
@tableName sysname,
@tableScript nvarchar(max)
WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);

IF SCHEMA_ID(@schemaName) IS NULL
BEGIN
    SET @ErrorMessage = 'Schema with name ' + @schemaName + ' does not exist.';
    RAISERROR(@ErrorMessage, 16, 0);
    RETURN
END

IF OBJECT_ID(@schemaName + '.' + @tableName, 'U') IS NOT NULL
BEGIN
    SET @ErrorMessage = 'Defined table ' + @schemaName + '.' + @tableName + ' already exists';
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

IF (@schemaName + '.' + @tableName <> (SELECT SUBSTRING(@tableScript, (CHARINDEX('TABLE', @tableScript) + 6), (LEN(@schemaName) + LEN(@tableName) + 1))))
BEGIN
    SET @ErrorMessage = 'Schema and/or tablenames in creation script do not match with ' + @schemaName + '.' + @tableName;
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

EXEC(@tableScript)

RETURN 0
END

A user with execution rights on the above procedure can use it to execute a valid CREATE TABLE statement. And you can add more checks/constraints (i.e. the allowed schemas). And should do so since the execution is dynamic SQL. Depending on the user rights of your:

  WITH EXECUTE AS 'user'

You can get some nasty rights escalation if you don't control your input.
I've tested the above procedure on SQL Server 2008, 2012 and 2017. I don't have an AzureDB instance available for testing though.


mongoimport error - Failed: error connecting to db server: no reachable servers

I have gone through the error and checked out, the problem is resist with mongoimport syntax. The mongoimport shell is unable to find the location of (.json) file.

And also there is mistake like --jsonArray.

As per MongoDB BOL --jsonArray Modifies the output of mongoexport to write the entire contents of the export as a single JSON array. By default mongoexport writes data using one JSON document for every MongoDB document.

  Note:- Accepts the import of data expressed with multiple MongoDB documents within a single JSON array. Limited to imports
  of 16 MB or smaller.

For Simple mongoimport syntax will be like that

mongoimport -d databasename -c collectionname (File Location of json file.json)

For Example

mongoimport -d test -c zips C:\MongoDBDBA\zips.json

2018-02-07T08:32:31.084+0300    connected to: localhost
2018-02-07T08:32:31.532+0300    imported 29353 documents

IN the above mongoimport syntax the database name is test and collection name is zips and C:\MongoDBDBA\zips.json is the (.json) file location.

For Example with localhost and port number 27017

If you want to use localhost and by default port number 27017 in mongoimport then simply the syntax will be like that

mongoimport --host localhost --port 27017 -d blog -c posts C:\MongoDBDBA\posts.json

2018-02-07T10:48:54.340+0300    connected to: localhost:27017
2018-02-07T10:48:56.333+0300    [########################] blog.posts   33.9MB/33.9MB (100.0%)
2018-02-07T10:48:56.345+0300    [########################] blog.posts   33.9MB/33.9MB (100.0%)
2018-02-07T10:48:56.346+0300    imported 1000 documents

Here in the above syntax blog is the database name and collection name is posts.

  Most Important Note:- Run mongoimport from the system command line, not the mongo shell.

For your further ref Here


SQL Server Designers, Failed Saves, and Generated Scripts

I do not use the designer for making changes, so I have no particular experience with script problems.  I did some years ago find that the designers were prone to errors, so I am not surprised that you have some problems.  Nonetheless, you find the tools valuable.

From your description, you wind up with two scripts, but they are not identical.  So the problem is how to treat the scripts.  Or how to avoid any need to deal with the issue.

Database backups or database snapshots are a couple of tools that can help.

If you are using SQL Server Enterprise or Developer editions, you have the ability to create database snapshots. So this solution depends on your SQL Server edition and you rights on that server and how many people are sharing that database.

Scenario using a snapshot database and assuming that you have full rights to do whatever you want to with the design database.

First, create a SNAPSHOT database (examples from the SQL Server documenation):

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'D:\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;

Once you have the snapshot created, make your modifications in the designer and then save your scripts.  If all is well, you get rid of the snapshot:

DROP DATABASE AdventureWorks_dbss1800;

However, if the designer glitched you would then use the snapshot to revert the database back to the point in time of the CREATE...SNAPSHOT.

USE master;
-- Reverting AdventureWorks to AdventureWorks_dbss1800
RESTORE DATABASE AdventureWorks from
DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

Because you reverted, you can repeat the designer steps with the database in exactly the same state as the first effort.

If you are not using a version that supports SNAPSHOT databases, you can do the same thing (perhaps more slowly) using BACKUP DATABASE and RESTORE DATABASE.


Oracle MV requires object type to be defined as FINAL?


What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data?

I finally went ahead and got a test database loaded with data. Table structure is:

CREATE TABLE [dbo].[ImageTable](
    [CompositeID] [bigint] NOT NULL,
    [ImageID] [tinyint] NOT NULL,
    [FullImage] [varbinary](max) NULL,
    [ThumbnailImage] [varbinary](max) NULL,
    CONSTRAINT [XPK_ImageTable] PRIMARY KEY CLUSTERED ([CompositeID] ASC,[ImageID] ASC)
)

The table was loaded with approximately 2.8M records. The FullImage value was set, and the ThumbnailImage value was left as NULL for each record.

The database itself is 154GB in size.
The uncompressed backup is 154GB in size.
The compressed backup is 134GB in size.

Given that the two PK fields account for approximately 24GB (2.8M records * 9 bytes/record), it looks like the only savings from compression are being realized by compressing the PK fields.


How to organize the tables of a MySQL database with several statistics data of several years?

Well , i would add a new column on the Statistics_Entry as your year column ! Next depending on the volume of your data i would partition that table using this column(this will speedup your query when using the year column as your predicate).
 This lookup table can be a view !
I would not create a new table just for the year column, this would just make thing complicated ! in my opinion !


SQL SERVER Database IO performance impact of splitting database tables into FileGroups and multiple Files per filegroup

Talk with your SAN administrator. That's the source that should be able to explain you with detailed information what kind of SAN you've got and what it is capable of. Have you got MPIO? What's the structure of your fabric?

Sometimes it makes sense to use multiple LUNs for different files. Behind the scenes, there might be different types of storage available.

A SAN with some SSD capacity might offer tiering. That is, the hottest data is elevated to SSD -backed part of the system whilst colder data is persisted on HDDs. Some models provide autotiering, in which the system decides what's hot and what's not. Some models require administrative action.

HP EVAs provide VRAID levels 0, 1 and 5. Using those will spread a LUN on all the disks in an array. This increases reliability, but the cost with VRAID 5 is increased controller CPU usage. The SAN admin needs to balance increased storage costs against increased CPU usage.

Aligning NTFS blocks with storage is dependent upon the SAN. If you are in addition using virtualization (Hyper-V, VMWare, whatever), that's additional complexity introducec. Your storage vendor is likely to offer white papers, best practices and consulting services about how to configure the storage for optimal performance.

Network packet size is relevant - if you use iSCSI. For FibreChannel, IP settings are not relevant.


Is there any benefit of a primary key that comprises all columns of the table?

In your case these fields are natural key.

  Surrogate Key:

  Surrogate keys are keys that have no
  “business” meaning and are solely used
  to identify a record in the table.
  Such keys are either database
  generated (example: Identity in SQL
  Server, Sequence in Oracle,
  Sequence/Identity in DB2 UDB etc.) or
  system generated values (like
  generated via a table in the schema).

  Natural Key:

  Keys are natural if the attribute it
  represents is used for identification
  independently of the database schema.
  What this basically means is that the
  keys are natural if people use them
  example: Invoice-Numbers, Tax-Ids, SSN
  etc.

Surrogate Keys vs Natural Keys for Primary Key

I prefer to add surrogate key to separate business and database model management.
Other question is using clustered and nonclustered index on primary key..
If you table changes (non static table, it has high intensive inserts or update), you will get problem with performance in case of using clustered index on non-monotonic increased key.


An INCREMENTAL LEVEL 0 is a full backup of all used blocks in the database.
An INCREMENTAL LEVEL 1 CUMULATIVE is a backup of only the blocks that have changed since the last incremental backup.

... the definitions above paraphrased from the Glossary of Oracle Database Backup and Recovery User's Guide 11g Release 2.


AWS doesn't run PostgreSQL.

They run Amazon Relational Database Service (RDS), based on PostgreSQL
You can run PostgreSQL yourself on EC2, using something like the Ubuntu Cloud images.

  What's the maximum value for the process pid? Im looking into an issue around the use of pg_backend_pid in a trigger.

I assume the maximum value for process pid is the maximum pid value for Linux.

But, for PostgreSQL it's stored in an interger.

select pg_typeof(pg_backend_pid());
 pg_typeof
-----------
 integer
(1 row)

Which means the max value is pow(2,31)-1


PRIMARY index not used if too few entries

It seems you can force MySQL to use an index with force index, documented here.

First, make sure your statistics are up to date.

Then try something like this (untested)

SELECT cmd.cmd_date, liv.liv_nom
FROM cmd
    LEFT JOIN liv force index (primary) ON cmd.liv_id = liv.liv_id
WHERE 1
ORDER BY cmd_date DESC

Different index hints might work better. Expect different behavior when liv contains only 5 or 6 rows total rather than 5 or 6 matching rows out of thousands or millions of rows.


MySQL (ndb) Cluster and BLOB Datatype access issue

Following further testing it seems my problem wont be performance, but simply creating the table, as that many BLOB fields exceeds the maximum row size, and so I can't even create the table!!

I got to about 47 BLOB fields before it gave up.


MySQL encryption for 'database at rest' on cloud services

Encryption at rest can't address server breaches, it is only intended to mitigate cases of

physical data storage theft, and
data access by unauthorised people, e.g. cloud technicians.

MariaDB encryption wouldn't be able effectively deal with either, because stealing the drives would effectively provide root access to the system, including access to the encryption keys and complete unrestricted access to the database. This is also the case with cloud technicians, who can access backup copies of the system or peer into the filesystem by analysing the underlying datastore. MariaDB encryption can minimise the risk of intrusion, however in order to access the drive or mysql files, the attacker would have to have either root or at least mysql user privileges, and would access not only the files but also the encryption keys in that case.

A good way to ensure encryption at rest is using dm-crypt (or equivalent) to have the OS encrypting the partition where the database files are located, and entering the password manually by an authorised operator at system or RDBMS startup time (i.e. no keys stored in the system at all). This system is not particularly effective against server breaches, but it makes the data completely secure in case of physical storage theft, or access to system backups or underlying datastores.


I noticed you did not specify the MASTER_LOG_POS in the CHANGE MASTER TO command.

Another problem could be this:

When you set up MySQL Replication, you have to explicitly set this in /etc/my.cnf

on the master:

[mysqld]
server-id=1

and this on the Slave

[mysqld]
server-id=2

Then CHANGE MASTER TO can work

Since the Master is MySQL 5.0.67 and you loaded the same data into the Slave, here is the CHANGE MASTER TO command you need:

change master to
MASTER_HOST='192.168.1.15',
MASTER_USER='replication1',
MASTER_PASSWORD='*******',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=98;

Here is something you need to know fot setting up other slaves

The Master must be the same version as the Slave or older.
The starting position for any empty Master is

107 for MySQL 5.5
106 for MySQL 5.1
98 before MySQL 5.1

Now you have replication working, take note of the following

Relay_Log_Space is 399
Exec_Master_Log_Pos is 98

Do the following:

Run this command on the Master: CREATE DATABASE rolando;
Do SHOW SLAVE STATUS\G on the Slave
Do SHOW DATABASES; on the Slave

What results should you have?

Relay_Log_Space should be greater than 399
Exec_Master_Log_Pos should be greater than 98
The database rolando should appear in the SHOW DATABASES; on the Slave

If this happens, congratulations !!! Replication is working !!!

From here, you just have to monitor

Slave_IO_Running (make sure it is Yes)
Slave_SQL_Running (make sure it is Yes)
Seconds_Behind_Master (should be zero, long running queries make it increase until all queries are processed)


Object-oriented programming CRUD design pattern

CREATE - When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.

Do all inserts for each "person" in a single transaction based on updatable views, not on base tables. Every dbms exposes a function that will return the last inserted ID number. (For PostgreSQL, this SO answer.)

  READ - To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)

In the query you posted, you don't do that at all. Instead, you retrieve all the data for every type.

Instead, use two views (see above), one for legal persons and one for natural persons. Resist the urge to join every possible table in the views.

  UPDATE - When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An
  INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.

In the real world, you don't "upgrade" a natural person to be a corporation. (A person who "is" a corporation is legally two different things. That's why there are corporations.) In a database, if you run into such a situation, you have to take one of three paths.

All the original data is wrong, because you entered data for a person who is not really a person. Delete the bad data, and enter new data for the corporation.
All the original data is right; that person is actually a natural person. Reject all the updates that would make that natural person a corporation.
All the original data is right, and that person is both a natural person and a corporation. Redesign your schema to accommodate this possibility.


Is there a way to minimally log insert under Always on?

No.  Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery.

Quote from the Data Loading Performance Guide:

  Minimally logged operations are available only if your database is in
  bulk-logged or simple recovery mode.

Re trace flag 610, what that added was the ability to do minimally logged inserts to tables with clustered indexes ( and/or non-clustered indexes ) with data already in them.  This was not previously available.

The thing to bear in mind about minimally logged transactions as that as soon as a transaction log backup contains one of these, you lose the ability to do point-in-time restore with that backup.  So hopefully now you understand why these are not available in Full recovery model


The error indicates that you have invalid references in the parentId column.

This error shows when you are trying to insert a row with an invalid reference, update an existing row with an invalid reference, or delete a row that is referenced. You also get it when you are trying to create a foreign key constraint on a column that is already populated and contains references to non-existing rows, which is what happens in your case.

Solution: check the contents of the parentId column and correct/remove the invalid references.


how to get weekly unbound retention from bigquery?

This should be a relatively straightforward aggregation by Week, but you will need to join all records greater than or equal to the current week and take your distinct count from there.
However, that's going to be a lot of rows for Bigquery to work through, so this will probably work better for the platform:
SELECT
  TransactedWeek AS Week
 ,SUM(SUM(LastWeekInd))
    OVER
      (
        PARTITION BY
          1
        ORDER BY
          TransactedWeek
        ROWS BETWEEN
          CURRENT ROW
            AND
          UNBOUNDED FOLLOWING
      ) AS active_users_count
FROM
  (
    SELECT DISTINCT
      UserId
     ,TransactedWeek
     ,CASE
        WHEN TransactedWeek = MAX(TransactedWeek) OVER (PARTITION BY UserId) THEN 1
        ELSE 0
      END AS LastWeekInd
    FROM
      <Your Table>
  ) x
GROUP BY
  TransactedWeek

You can add logic to limit the ranges in the inner query.
Note: if you have any weeks missing from your data, they will be missing in your final result (although the counts will still be accurate).  If you have weeks with no transactions, you may need to generate a list of weeks and left join these results to that.


Is there a need to index the short string code for a faster query?

Use the DBMS for its intended purpose; that is create a constraint on the table so that it fits the business rules.

Designing your table so it looks like:

USE tempdb;
CREATE TABLE dbo.MyTable
(
    [Key] int NOT NULL
    , Code varchar(10) NOT NULL
         CONSTRAINT uq_MyTable_Code
         UNIQUE
);

This will ensure every row inserted into MyTable has a unique Code column.  Now you can add a try ... catch statement around  your inserts in .Net code to inform the user when a duplicate is detected.

The SQL Server engine will automatically create a unique index on the table to support the constraint.  You could just manually create a unique index on the table to satisfy the same requirements; however adding it as a unique constraint in the way detailed above self-documents the business rule.


Data warehouse: using day number in Epoch in a date dimension

Epoch is also a synonym for a date or time. Occasionally used in database models as an alternative to the ubiquitous 'timestamp' as a column name, the two are essentially interchangeable.

Edit: Looking at your list with fresh eyes (and without getting the book from the cupboard it's hiding in) I expect an epoch in this instance would be a period such as a financial quarter. For example, if a the quarter ran from 1st April to 30th June, 'Day number in Epoch' for 1st May would be 32.


Find out MySQL index size for a concrete index

There appears to be no way to get the answer out of mysql directly; however you can do a pretty good job of estimating it:

For innodb, PRIMARY index is part of the data itself and shows up in data_length.  If a table has no other indexes index_length is 0.

For every row in your table, every non-PRIMARY (UNIQUE or INDEX) index stores

6 bytes overhead
every column indexed
every column in PRIMARY not already in the index
1 bit per possible NULL column, rounded up to the byte

The size of a column in an index is the space the actual data takes up plus 1 or 2 bytes of length per VARCHAR/TEXT/BLOB.  NULLs take no space.

There is an additional overhead of about 1.4x to 2.8x for the B-tree pages and non-full leaf pages.  In my databases it looks to be around 2x usually.

FKs should not have any space overhead beyond the index they require.

mysql docs - source


SQL to find nearest location in one table from locations in another

construct a sql select that for every row in SourceData find an entry(ies) in NewLocations that is within a few metres

Something like:

select *
from NewLocations n
cross join SourceData s
where originalLocation.STDistance(NewLocation) < 5


MySQL - Backup new and modified entries only

We have a similar process in place, where we run a script to

TRUNCATE TABLE backupTable;
INSERT INTO backupTable SELECT * FROM mainTable WHERE datefield >= xxxx

WHERE x is the time the backup ran the day before. Then we run MySQLDump against backupTable.

Alternatively you could do something similar and set a 'backedup' flag in the main table, and then just

INSERT INTO backupTable SELECT * FROM mainTable WHERE 'backedUp' = 0;

and then update the backedUp flag in the main table.


sys.system_internals_partition_columns view on Azure SQL

Not sure exactly what you want to grab out of the referenced system view. Much of the data you care about is likely still exposed to you, but through the Database related DMVs which have more or less been around in some fashion since SQL Server 2005 with additions in versions since.

You might have some luck with querying a few of these DMVs. You can explore with SELECT * FROM

sys.dm_db_partition_stats
sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) (specifying the parameters required for your usage)
sys.dm_db_index_usage_stats

etc.

There are quite a few database level and connection information level Dynamic Management Views exposed to you in Azure SQL DB.


How to find equivalent of Oracle's privileges in SQL Server?

In SQL Server the right to "CONNECT" is given at the instance level.  You'll find this right assigned to the login.  The ability to view the database catalog is given at the database level by assigning the "VIEW DEFINITION" right to the database or the schema.


while restoring mysql database, views changed into tables

In the backup script the views are first created as tables which are then dropped at the end of the script as each view is being created, so it seems that an error occurs while creating the views at the end of the script.

However when a view is created there is a user who is used in the DEFINER clause of the view who may not exist in your database, e.g., DEFINER=user123@192.168.1.%

I usually remove that clause in the backup script and it tends to work.

To verify add the -v option when importing the database


How do I store more than 15,000 Japanese characters in a column?

In this case only NVARCHAR(MAX) should be used. It's able to store up to 2^31-1 bytes of Unicode data (about 2 GB).


Unable to attach SQL Server database with shared folder in docker on Linux

I figured out the problem with shares.

One thing I did was remove the share in VMWare because for some reason, the mount with vmhgfs did not work well with SQL Server. It just could not get the right privileges even if I tried setting them with chmod.

Instead I installed CIFS and mounted the share as

sudo mount.cifs //10.100.1.1/dbaclone/clone /data/dbaclone -o credentials=/home/sander/.dbaclonecredentials,dir_mode=0777,file_mode=0777

This mounted the share as needed.

SQL Server was able to connect to it without any permissions issues.


Actually, pg_dump backs up all the data, permissions, indexes and whatnot that constitute the database. What is not backed up are the PostgreSQL server's configuration files, globals (roles and tablespaces), and its actual on-disk files and layout.

If you want to back up everything, you should use pg_dump to dump each of your databases (I recommend the custom format), and then grab the config files in your $PGDATA directory: postgresql.conf, pg_hba.conf and pg_ident.conf. If you use tablespaces, you'll need to also keep track of where they link to in the pg_tblspc subdirectory of $PGDATA. I would not use pg_dumpall, it's harder to restore from. The rest of the files in $PGDATA don't need to concern you, since a hot copy will probably look defective to PostgreSQL anyway.

Amendment: you should also do pg_dumpall -g to get your roles and tablespaces backed up as well. Your restore strategy is then as follows:

initdb -D <new data directory>
start up PostgreSQL
psql < (file with output of pg_dumpall -g)
createdb for each database
pg_restore -d <database> <pg_dump file> for each database

Why would you rather use pg_dump than pg_dumpall for everything? For two basic reasons:

pg_restore has features that depend on the dump being in the custom format. The main one right now is parallel data restoration, which can significantly improve restore speed. pg_dumpall can only produce one big SQL script.
The script produced by pg_dumpall cannot be used to selectively restore databases. The script contains instructions to create this database, change to it, load the data, then create the next database. If you have several databases in your cluster it will be difficult to restore them individually from a script generated from pg_dumpall.

That said, if you have only one database in your cluster the above steps may be too much overhead for the amount you gain. Even a simple backup made with pg_dumpall is better than no backups at all. Just remember to get all the other stuff on disk mentioned above as well (postgresql.conf, pg_hba.conf and whatever changes to the layout you made for tablespaces).


Limiting user access to tables based on a ROLE

If I had to guess you have them added to the db_owner role as well.  Either that or sysadmin (although less likely).  Administrative roles, specifically DBO and SYSADMIN can not be blocked from access to anything they have control over (dbo - database level, sysadmin - instance level).  I would go back and check first the role that you created and make sure that it is not a member of any other roles that might cause an issue.  db_datareader for instance shouldn't be a big deal.  Second I would check public and make sure IT isn't a member of any other roles.  Then I would check any other roles that the users might be a member of.

All that being said it's also possible you made a mistake when you set up your role and revoked instead of denyed (I've done that type of thing before).

Last but not least, you should change how you are doing this anyway.  Remove all access from public.  Create a role that has just the access you want them to have and add them to that role.  It is generally considered a bad idea to grant access to public.  Or for that matter to grant lots of access and then deny it to certain people.  It's far better to accidently forget to grant someone access to something, than to forget to deny it.


By using a case a statement you can turn your magic strings into numeric values. These can be summed and a group statement defines how to organize the summation.

Edit: I've added a sort as it is likely you are interested in the "winners".

SELECT
    SUM (
        CASE WHEN vote = 'Yes' THEN 1
        WHEN vote = 'No' THEN -1
        ELSE 0 END) BalancedVotes,
    Name
FROM
    myTable
GROUP BY
    name
ORDER BY
    BalancedVotes DESC


A different number of AFTER INSERT trigger alarms in SQLite and SQL Server

In SQL, triggers can be fired "per row" or "per statement".

SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing).

Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which are specified by the phrases FOR EACH ROW or FOR EACH STATEMENT in the trigger's definition (though amusingly both have different defaults: DB2 defaults to per-row, PostgreSQL to per-statement). I vaguely recall Oracle supports both types too but there was something weird about its "per statement" variant.

Typically per-row triggers have access to the before/after state of the row they are triggered by, while per-statement triggers have access to a pseudo-table of the rows that the triggering statement affects.


How to achieve Data synchronization in Active/Active clustering using SQL Server 2012 Standard Edition

There is no such thing in SQL 2012.

There are Availability Groups which don't use shared storage. It it's Enterprise edition only (or SQL 2016).


Oracle Database Migration using Oracle SQL Developer

why do you need windows at all? you even do not need sql developer.

shutdown the source database
scp datafiles(.dbf .ctl)
scp files in ORACLE_HOME/dbs (like initSID.ora, spfileSID.ora, and also password file)
you should also create dirs for diagnostic_dest (where alert.log is placed)
amend /etc/oratab
set the right SID
start the database

PS: this assumes that the Oracle version (including fixpack) is exactly the same.


LRM-00101: unknown parameter name '_first_spare_parameter'

From the spfile create a pfile, which can be edited directly:

sqlplus / as sysdba
create pfile='/tmp/pfile.ora' from spfile;
exit

Edit /tmp/pfile.ora, remove _first_spare_parameter. Then create the spfile again:

sqlplus / as sysdba
create spfile from pfile='/tmp/pfile.ora';

Now you can try starting your instance.

Above should work in a standard single instance environment, in a customized or clustered environment you may need extra steps.


SQL How to copy text from one table (from one column) to another table (to one column), preferably with IF the target column is empty

Although previous answer was correct, and I am thankful to @McNets, I found an easier way to do it.

UPDATE info i, articles a
SET i.Description = a.Article
WHERE i.Codename = a.Codename
AND i.Description IS NULL;


SQL which removes entries with duplicate emails

Use multiple-table DELETE syntax:
DELETE t1.*
FROM Person t1
JOIN Person t2 USING (Email)
WHERE t1.id > t2.id


Restore database without overriding Auto growth

I just tested this in SQL 2016 (13.0.4001.0). Restore retain the auto-growth setting of the database (for which backup was taken).  Here is the code I tested with.
Create database

CREATE DATABASE [AutoGrowTest]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'AutoGrowTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'AutoGrowTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

Change auto-growth

USE [master]
GO
ALTER DATABASE [AutoGrowTest] MODIFY FILE ( NAME = N'AutoGrowTest', SIZE = 262144KB , FILEGROWTH = 131072KB )
GO

Backup database

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

Restore with diff name

USE [master]

RESTORE DATABASE [AutoGrowTest_restore] FROM DISK = N'C:\AutoGrowthTestFull.bak'
WITH FILE = 1, move N'AutoGrowTest' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore.mdf'
, move N'AutoGrowTest_log' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore_log.ldf'
, nounload, stats = 5

GO

It retained auto-growth setting of original database.

Please note auto-growth setting changed in SQL 2016 than what is mentioned in some comments and answers.


Importing a small quantity of data like 400M shouldn't take long. Other questions have suggestions on how to do this quickly.

Because these are SQL dumps for a single database, they could be imported into a different database name without a problem. You could use a database name per date, import the data and perform the operations in parallel.


Can SQL Server prevent the pagefile from being moved?

I'd love to see a screenshot of the Virtual Memory applet, here's the one from my notebook:

SQL Server does not interact directly with the pagefile.  Only the operating system itself is capable of doing that since the O/S holds an exclusive lock on the file while it is using it.

If SQL Server is the only load running on the machine (it should be for all but the simplest installations), and is correctly configured, the page file should be for most purposes irrelevant.

The SQL Server database engine has some very sophisticated memory management techniques, which can be managed by settings such as lock pages in memory, LargePageAllocator (see http://support.microsoft.com/kb/920093 for details), max server memory and min server memory, etc.

In my opinion, for a machine dedicated to SQL Server, I typically set the page file to a low amount, such as 4GB.  I set max server memory to an amount close to the amount of physical ram in the machine (less than 94% of memory, leaving at least 2GB free), and set min server memory to the same amount.  I then watch performance counters related to the page file, such as pages/sec etc, and SQL Server's Page Life Expectancy (see http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx) to make sure SQL Server is not paging memory to disk.  If SQL Server is paging to disk, or the Page Life Expectancy is low (this is the number of seconds a page is retained in the buffer pool on average) I will request that physical memory be increased in the machine.

For instance, on the last production machine I worked on, we had 192GB of RAM, and had the page file set to 4GB.  The default setting Windows "suggested" would have been 288GB, a clearly ridiculous amount.

For further info on this topic, see:

https://dba.stackexchange.com/a/20492/10832

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

https://dba.stackexchange.com/a/10201/10832


Question comment pasted below:

  io_stall by itself doesn't mean too much. If in 10 seconds you had 1000 operations stalling for 1 second each, you'll have 1000 seconds of stalls. That would be 16+ minutes of stalls in 10 seconds. You need to correlate this with IO operations...

The above is a pretty good example of how you can see monumental and seemingly exaggerated numbers.  By itself, io_stall doesn't really mean anything.  You need to know the scale of I/O operations for that cumulative stall.

Instead of having this:

SELECT MAX([IO2].[IO_STALL] - [IO1].[IO_STALL])
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
...

You need to divide the stall by I/O operations to get the average stall per I/O (or per read, or write, or whatever granularity you're looking for).  In other words, my recommendation would be to modify your query to look something like this:

SELECT
    MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES))
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)

And then you need to have an extra predicate clause to ensure you're not dividing by zero:

...
WHERE IO1.NAME = IO2.NAME
and (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0
AND IO1.Disk = @Disk

What this basically does is calculate the average io_stall per I/O operation.  By itself, a high io_stall could simply mean a higher workload and not necessarily the sign of a problem.


Why is index not being used? Can I replicate plan?

This was totally a workaround, but it worked for me.  I had 4 servers running the same database with different data sets.  Three of them would chose an optimal plan, but the fourth would never choose that plan, and was consistently taking significantly longer.  In my case I could see the good plans seeking one of the tables for a handful of rows out of around 100,000 first, then doing the other joins.  The bad plan would do that seek last and would seek that table 500,000 plus times.  I also tried the query hints and re-writing the query in different ways with no luck.
So my work around was to manually query that table for those handful of rows first into a temp table, then join to that instead of the full table.
So in your case could you run something like this first into a temp table and then join against it instead of the whole DWH.dbo.tbl_dim_date table:
select  d.dim_date_id
   ,d.date
   ,d.flag_future_day
from    DWH.dbo.tbl_dim_date d
where   (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y'
          or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y'))
   and d.flag_future_day = 'N'
   and d.date >= '14/07/2020'

Not sure in your case if the above query would be selective enough to be really helpful and I realize that it is a workaround. But sometimes workarounds work!
Another thought, are you able to modify that NCI you included? It might help if you added the date column to the indexed columns instead of the INCLUDE columns. There are a lot of filters done in that query based on that date column.


Even when a machine is fixed in a given timezone, storing timezone-dependent date/time information is not very safe. What do you do during Daylight Saving Time? You have the same problem - you skip ahead or repeat an hour. As the others suggested, you should always store your data using UTC, then it is easy to convert to any time zone you want, without having to know what time zone the server was in when the data was stored (though you can optionally store that information separately, or you can use DATETIMEOFFSET - but I haven't found that very useful since it, also, doesn't account for DST).

For easy situations (e.g. no DST involved), you can write a simple helper function to convert UTC to whatever time zone is appropriate. Even better if you just know the offset (e.g. +5 hours or -4 hours). This is a simple DATEADD operation.

For situations where DST is involved, I've always used a calendar table. It is very easy to populate a table with 30 years of data (one row for each day) and also store the offset, in minutes, for each day (given your time zone and whether the date falls in DST or not). Yes these can change for future dates, but the only time this can become tricky is if you're booking events farther into the future than the advance warning you get when the government decides to change the policy (as they did a few years ago). Converting UTC data on a given date to the time zone of your choice is still a simple DATEADD operation, except now it involves looking up the actual difference for that date in a table.

For data you're entering now, you can simply use GETUTCDATE() instead of GETDATE()/CURRENT_TIMESTAMP. This returns UTC time regardless of your time zone, and whether or not the machine is set up to observe DST.


Should log tables be "normalized" or store snapshots?

It's a business question of whether you want to know the phone number that was called or the phone ID that was called.

If, for example, you are using the log table to cross-reference with the billing from the phone company, you'd want to store the phone number that was called.  If, on the other hand, you are using the log table as a CRM tracking tool, it's much more useful to know that you dialed Walter's Widgets main line rather than the particular number that was stored in the system for that contact at that point in time.  Often, you're going to end up wanting to store both because you have both types of business requirements.

Realistically, I wouldn't give much thought to objections about denormalization of log tables.  Worry about denormalization on your core OLTP tables.  You're never going to be updating a log table, you're never going to use the log table as the source of truth for what a value is (as opposed to what the value was at a prior point in time) so there is little need to worry about denormalization.

The only real concern I'd have about denormalizing a log table is space-- if your log tables have large numbers of rows and you're storing relatively large string values that almost never change, you can end up wasting tens or hundreds of GB of space on those redundant strings and make queries against the log tables too slow to be useful.  You can mitigate those sorts of things, of course, with horizontal and vertical partitioning or by maintaining separate log/ history tables for different concepts.


Grant Select on some columns for specific user

Yes, you give permissions only required users in SQL Server using GRANT like this

GRANT SELECT ON <schema>.<tablename>(<Col1>,<Col2>) TO [Domain\User]

For more info, refer Grant


Record not getting filtered using like clause

Your search pattern contains a wildcard character [,
you should enclose it in brackets or use ESCAPE character like this:

declare @SearchRecord table (searchkeyword1 varchar(100));
insert into @SearchRecord values
('"Affiliate" means'),
('"Agent''''s Rate of Exchange" means'),
('["Aggregate Total Commitments" means'),
('["Agreed Security Principles" means');

select *
from @SearchRecord
where searchkeyword1  like  '![%' ESCAPE '!';

select *
from @SearchRecord
where searchkeyword1  like  '[[]%';

All this is described here:
LIKE (Transact-SQL)


Regenerating a cube relational schema in Visual Studio

That's not how it works. A .dsv (data source view) is generated by defining which tables/queries you want to use in your cube.

The flow is:

create one or more .ds data sources by defining how to connect to the source databases
create a dsv (data source view) by adding tables and named queries defining how to get data from your .ds
create dimensions and cubes by defining how the data from your .dsv needs to look in the multidimensional model.
process your dimensions and cubes to load them with data from the source system.

Technically I suppose you could recreate the source systems schema by analyzing the xml in the .dsv file as it contains the data types and table names/queries, and you could theoretically map those back and recreate the source database(s), but that would require a lot of manual work, or you writing something to parse that out of the xml.
If your DSV contains named queries you would still need to analyze those sql queries and reconstruct the underlying table.
Depending on how complex your named queries are this could become near impossible.

After all that work you would end up with a schema and no data, so your developers would maybe be able to modify the visual studio solution, but have no way to load it with data to test their modifications.


Removing/ Undoing Database addition to Alwayson Groups

Yes it is possible to do so. However, there are some possible issues you have to consider or expect.

You will have at some down time in you application or web (whatever accessing the database).
You need to change the connection string. AOA group connection string uses the listener or AoAgroup. When you switch back, you need to use regular connection string FQDN of your server or IP of your address.
You might also need to remove the server from the cluster.
When you failover databases under your secondary server remain under no recovery state. Databases in your primary server will be online but sometimes (especially if you have really big database) if there is some transaction log left and during the failover, your database will hang up under suspect mode.


How do I give root@localhost permission to grant privileges in MySQL?

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

you should have gotten 42. That's how many columns MySQL 5.5 has for mysql.user. Since you got 39, that means you must have upgraded from MySQL 5.1. That has 39 columns.

I wrote an earlier post about the number of columns in mysql.user in different versions : MySQL service stops after trying to grant privileges to a user

Here is the post where I dealt with this : mysql: Restore All privileges to admin user

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

to realign mysql.user and have it autofill missing permissions with Y.

Give it a Try !!!

If you want to try to fix the mysql.user manually, here are the steps:

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user
    ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N'
    AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That's it !!!


Would appreciate some advice on using external data with SQL Server 2005

You can import your data into a staging table then update the product reference data from that.  The product table can hold your 'available' flag.  Unfortunately you will have to import the XML data into SQL Server to load it, but SSIS has an XML reader, so you can use that to do the imports.


Geospatial join using ST_Contains or ST_Within seems slow, with costly query and bad estimate

PostgreSQL chooses the optimal plan and uses your index.
The time is probably spent in the 72382 executions of st_within that are required to verify that the rows found by the index actually satisfy the condition (the index operates with bounding boxes and consequently can return false positives, in your case 4884 of them).
The bitmap heap scan has to read 6 million 8kB blocks, which can also be an explanation for the long execution time. I don't know why, because points shouldn't be large. One possible explanation might be lots of dead tuples - does VACUUM temp_locations; help?


How to use GROUP BY after the Having clause Mysql

Move the GROUP BY to between the WHERE and the HAVING.

SELECT
    a.`id` AS aid,
    ap.`pid`,
    p.`products_name`,
    a.accounts_account_number,
    c.clients_name,
    a.accounts_product_type AS pptype,
    c.`clients_last_name`,
    a.`accounts_account_name`,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
FROM `bf_clients` c
    INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
    INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
    INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
    INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
GROUP BY a.`id`
HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' )
    AND `Disable` !='1'
ORDER BY a.`id`

Also, move anything that needs to be checked BEFORE the GROUP BY, into the WHERE

Since you are using the HAVING to filter on an aliased subquery, you have should either (option A) add the whole subqueries into the where clause OR (option B?) why don't you just join the table used in the subquery?

Option A Example WHERE clause:

WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
    AND (
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != ''
        )
    and (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != '1'
GROUP BY a.`id`
-- REMOVED THE HAVING
ORDER BY a.`id`

Option B Revised with a LEFT JOIN:

NOTE: This option below has just been roughly put together without any tables / data, so may need some adjustment.  Further suggestion to improve this are welcome!

SELECT
    a.`id` AS aid,
    ap.`pid`,
    p.`products_name`,
    a.accounts_account_number,
    c.clients_name,
    a.accounts_product_type AS pptype,
    c.`clients_last_name`,
    a.`accounts_account_name`,
    case when perms.`permission` = 'Readonly' then perms.status end as ReadOnly,
    case when perms.`permission` = 'Buy' then perms.status end as Buy,
    case when perms.`permission` = 'Sell' then perms.status end as Sell,
    case when perms.`permission` = 'Disable' then perms.status end as `Disable`
FROM `bf_clients` c
    INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
    INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
    INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
    INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
    LEFT JOIN `bf_account_permissions` perms on (a.id = perms.id)
        and (perms.pid = ap.`pid`)
        and (perms.uid=2)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
    and (perms.status !='' or (perms.`permission` = 'Disable' and perms.status !='1'))
GROUP BY a.`id`
ORDER BY a.`id`


How to get the second string in the ORA-02296 error to show something meaningful

Provide a name for the constraint:

SQL> create table monkey(tail_count number);

Table created.

SQL> insert into monkey values (null);

1 row created.

SQL> alter table monkey modify tail_count not null;
alter table monkey modify tail_count not null
*
ERROR at line 1:
ORA-02296: cannot enable (ANIMAL.) - null values found

SQL> alter table monkey modify tail_count constraint tail_count_not_null not null;
alter table monkey modify tail_count constraint tail_count_not_null not null
                                                *
ERROR at line 1:
ORA-02296: cannot enable (ANIMAL.TAIL_COUNT_NOT_NULL) - null values found


Are AD Groups the exact same as individual windows logins with explicit permissions?

Microsoft's doc about Connecting Through Windows Authentication says:

  By using Windows Authentication, Windows groups can be created at the
  domain level, and a login can be created on SQL Server for the entire
  group. Managing access from at the domain level can simplify account
  administration.

There are no remarks about restrictions for apps when using a group instead of a login. Also, John F. Tamburo' article says:

  The group membership is used as a path to access; all activity is
  logged against the actual user's account.  sp_who2 and every
  auditing tool will show logins based on the actual end user; and
  DDL/DML changes will be audited to that user.  No more guessing who
  used the application's SQL login - It's gone!  For applications that
  run as Windows services, the service account under which the
  application runs is made a member of the necessary groups. (emphasis added)

So, as long as the group mapped as the SQL Server login keeps the needed privileges for the applications, no problem would be found on that change.