Prevent read access on primary AG replica, but allow it on the readable secondary We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We have setup read-only routing to accomplish this. This is also all on SQL 2016.

My initial thought was to create the login on both the primary and secondary replicas and grant read access to the database in question. Then we'd DENY CONNECT or disable the login on the current primary replica. In SSMS, the users could then connect to the Listener with ApplicationIntent=ReadOnly and be routed to the secondary replica without ever touching the primary.

We'd setup a simple job on both the primary and secondary replica servers with basic logic: IF current server = primary then disable login; if current server = secondary then enable login.

The problem is that I'm getting login failures when connecting to the listener with readonly intent when the login is disabled on the primary server. When I re-enable the login on the primary replica, it works just fine and the connection is properly routed to the readable secondary.

I setup a trace on the primary server and sure enough, I can see the login connect and run some system type queries in both master and msdb on the primary replica - even though I'm connecting with ApplicationIntent=ReadOnly in SSMS. I'm not sure if this is something that SSMS does behind the scenes or if it is the default behavior of a login going through the read-only routing process.

Here are the queries I captured with the quick profiler trace on the primary:

--msdb query

    select
        case
            when object_id('dbo.sysdac_instances') is not null then 1
        else
            0
        end

--master query

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

Has anyone had to deal with this situation before? It seems we basically need to allow a login connect permission on the primary replica while denying it read access to the database in the AG on the primary, but give that login permission to read the database on the readable secondary replica.

The other alternative is to create a DNS entry that points directly to the secondary replica, but we can't guarantee that replica will ALWAYS be the secondary as a failover could happen.

It seems we basically need to allow a login connect permission on the primary replica [...]

That's exactly right.  The login being used to connect has to be able to connect to the primary.

When you using ApplicationIntent=ReadOnly in a connection string that's pointed at an AG listener, the driver initially connects to the primary instance.  This is so that SQL Server can review the list of replicas and see if there are any available, online readable replicas to send you to.  This is documented here:

Read-Only Application Intent and Read-Only Routing

  The primary database of the availability group processes the incoming read-only routing request and attempts to locate an online, read-only replica that is joined to the primary replica and is configured for read-only routing. The client receives back connection information from the primary replica server and connects to the identified read-only replica.

There's also a good summary of the process laid out by Top Microsoft Man Sean Gallardy on the PFE blog here:

Finding Which Connections Have Been Read Only Routed

  We create a connection through the driver
  The driver connects to SQL Server through the listener and passes the relevant information
  SQL Server notices that we have read only routing setup and checks the configuration
  SQL server reports back to the driver that we have a read only secondary that can be used
  SQL Server sends the new secondary information to the driver
  The driver creates a new connection to the secondary with the given information

Regarding the other item you noted in the question:

  [...] while denying it read access to the database in the AG on the primary, but give that login permission to read the database on the readable secondary replica.

Database level permissions are going to be replicated across the AG - so if you remove the user's read-access on the primary, they will lose read-access on the secondary as well.

I'm a little confused about the goal of such an endeavor, since the data should, in theory, be the same (or close to the same).  Maybe you just don't want certain logins running queries on the primary at all for performance reasons.  Expressing Read-Only intent and connecting to the listener is really the most straightforward approach to solving this.

Other options would involve separate hardware, or custom coding in your app that deals with connecting directly to the right replica (and retry logic if a failover has occurred, and scheduled jobs on each replica to disable / enable the logins in the event of a failover, etc).


Sql Server string concat with order by and subquery While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a sql variable to eventually call EXEC(@sql). One thing we noticed was that despite our string concat and our COALESCE checks, only the last record was populating the variable.

The below code is a simplified version to reproduce the results.

IF OBJECT_ID('tempdb..#test') IS NOT NULL
BEGIN
  DROP TABLE #test
END

CREATE TABLE #test
(
    tmp_id INT IDENTITY(1,1),
    error VARCHAR(MAX),
    error2 VARCHAR(MAX),
    object_id INT
)

DECLARE @sql VARCHAR(MAX) = ''

INSERT INTO #test(error, error2, object_id)
SELECT TOP 100 CONVERT(VARCHAR(MAX), name), CONVERT(VARCHAR(MAX), name), object_id
FROM sys.columns

    SELECT @sql +=
      ' | '+(SELECT xC.name
        FROM sys.columns xC
        WHERE xC.name  = T.error
        AND xC.object_id = T.object_id) + ' | '
            + CAST(T.error2 AS VARCHAR(MAX))
FROM #test T
ORDER BY T.tmp_id

SELECT @sql

The strange thing is there seems to be a combination of factors at play here. If you remove the subquery sys.columns, or the order by, or the error2 column then it works as expected. You can also change the order by to be ordered by error instead of tmp_id and it will also still work.

I'm not looking for a solution as I've already rewritten the production code but I'm very interested in the "why" this is happening. I've tested on sql server 2008, 2016, 2017, and 2019 server.

Anyone have any ideas as to the cause?

String concatenation in that form combined with ORDER BY is undefined (and unstable and unreliable). I have seen the same symptom where you only get one result. If you want all the rows, drop the ORDER BY (ordering won't be guaranteed, but maybe that's ok, since your TOP 100 doesn't have an ORDER BY, so that's not guaranteed to be deterministic either). If you need ORDER BY, use XML PATH or STRING_AGG() instead, depending on version.

SELECT @sql = STUFF((
  SELECT ',' + t.error + CONVERT(varchar(12), xC.[object_id]) + ','
    + t.error + ',' + t.error + ',' + t.error + ','
    + t.error2 + ',' -- carriage returns are good for the soul!
    + t.error + ',' + t.error + ',' + t.error + ','
FROM #test AS t
INNER JOIN sys.columns AS xC
  ON t.object_id = xC.object_id
ORDER BY t.tmp_id -- this is the key to ordering
FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');

On 2017+ you can use:

SELECT @sql = STRING_AGG(t.error + CONVERT(varchar(11), xC.[object_id]) + ','
    + t.error + ',' + t.error + ',' + t.error + ','
    + t.error2 + ','
    + t.error + ',' + t.error + ',' + t.error, ',')
  WITHIN GROUP (ORDER BY t.tmp_id)
FROM #test AS t
INNER JOIN sys.columns AS xC
  ON t.object_id = xC.object_id;

For more info, see Order by 1 shortens the result set to one row and the links therein.


T-SQL: If parameter1 is null, use parameter2 This is a simple one but I can't seem to figure it out.

I have two parameters, 1 and 2. If one parameter is passed a null value, use the other parameter in there WHERE clause.

Ex:

SELECT ...
 WHERE CASE
    WHEN @Parameter1 IS NULL
    THEN Field2 = @Parameter2
    WHEN @Parameter2 IS NULL
    THEN Field1 = @Parameter1
  END

I know this is something simple or maybe I'm using my logic wrong. Any help or direction is appreciated.

The simplest answer to your question involves telling SQL Server what to look for by placing brackets around grouped requirements.  So, for instance, if we only want to check Field2 when @parameter1 IS NULL, we could do this:

WHERE (@parameter1 IS NULL AND Field2 = @parameter2)
    OR (Field1 = @parameter1)

SQL Server will evaluate Field2 = @parameter2 when @parameter1 IS NULL.  When '@Parameter1` is not null SQL Server will only return rows that match it.

If you have a lot of rows in the source table, you may want to use a stored procedure to help optimize the possible choices.  The following example creates a table, populates a couple of rows, and creates a procedure to query the table.

I'm doing this in tempdb since this is just an example:

USE tempdb;

Create the table, and populate it with a couple of rows:

IF OBJECT_ID('dbo.SomeTable') IS NOT NULL
DROP TABLE dbo.SomeTable;
GO
CREATE TABLE dbo.SomeTable
(
    SomeID INT NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Column1 NVARCHAR(30) NULL
    , Column2 NVARCHAR(30) NULL
    , Column3 NVARCHAR(200) NOT NULL
);
INSERT INTO dbo.SomeTable (Column1, Column2, Column3)
VALUES (NULL, 'col2_value', 'some data for row 1')
    , ('col1_value', NULL, 'some data for row 2');

Since I know the precise patterns my queries against this table will take, I can create a couple of simple covering indexes that will allow SQL Server to very quickly find the relevant rows:

CREATE INDEX IX_SomeTable_c1 ON dbo.SomeTable(Column1) INCLUDE (Column2, Column3);
CREATE INDEX IX_SomeTable_c2 ON dbo.SomeTable(Column2) INCLUDE (Column1, Column3);
GO

This will add an extra 10,000 rows to the table just to give us enough data for the indexes to be considered by the query optimizer:

INSERT INTO dbo.SomeTable (Column1, Column2, Column3)
SELECT TOP(10000) LEFT(o1.name, 30), RIGHT(o2.name, 30), o3.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3

Here, I'm creating a procedure that will search our table using the contents of either Column1 or Column2 depending on the parameters passed in.

IF OBJECT_ID('dbo.MySearch') IS NOT NULL
DROP PROCEDURE dbo.MySearch;
GO
CREATE PROCEDURE dbo.MySearch
(
    @parameter1 NVARCHAR(30) = NULL
    , @parameter2 NVARCHAR(30) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @where NVARCHAR(100);
    DECLARE @params NVARCHAR(100);
    SET @sql = 'SELECT t.Column1
    , t.Column2
    , t.Column3
FROM dbo.SomeTable t
';
    IF @parameter1 IS NULL
    BEGIN
        SET @params = '@p2 NVARCHAR(30)';
        SET @where = 'WHERE t.Column2 = @p2';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p2 = @parameter2;
    END
    IF @parameter2 IS NULL
    BEGIN
        SET @params = '@p1 NVARCHAR(30)';
        SET @where = 'WHERE t.Column1 = @p1';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p1 = @parameter1;
    END
END;
GO

If you pass in values for both @parameter1 and @parameter2, the stored procedure will return no rows.  You may want to add a third IF statement to the stored procedure to handle that situation, something like:

    IF @parameter1 IS NOT NULL AND @parameter2 IS NOT NULL
    BEGIN
        SET @params = '@p1 NVARCHAR(30), @p2 NVARCHAR(30)';
        SET @where = 'WHERE t.Column1 = @p1 AND t.Column2 = @p2';
        SET @sql = @sql + @where;
        EXEC sys.sp_executesql @sql, @params, @p1 = @parameter1, @p2 = @parameter2;
    END

Without knowing your exact requirements, it's difficult to say if you need that.

Two samples that show the usage of the stored proc, along with the execution plans:

EXEC dbo.MySearch @parameter1 = 'col1_value';

EXEC dbo.MySearch @parameter2 = 'col2_value';

Using a stored procedure in combination with dynamic SQL allows the query designer (us) to help SQL Server consistently choose the best plan for the job by providing two independent cachable plans that are optimized for each scenario.  Depending on the cardinality of each column involved, the complexity of the actual query design, along with the presence of desirable indexes, SQL Server may decide to create vastly different plans for the two queries, which can be a Very Good Thing™.

Just for fun, using the "simple" WHERE clause I show at the top of my answer like this:

DECLARE @parameter1 NVARCHAR(30) = 'col1_value';
DECLARE @parameter2 NVARCHAR(30);
SELECT t.Column1
    , t.Column2
    , t.Column3
FROM dbo.SomeTable t
WHERE (@parameter1 IS NULL AND t.Column2 = @parameter2)
    OR (t.Column1 = @parameter1);

Results in a plan like this:

This clearly shows SQL Server searching both indexes for Column1 and Column2.  This is rather obviously not optimal, and would certainly not be the preferred route for larger sets of data.


Percona PAM with AD authentication using SSSD I have installed percona PAm plugin on my Percona server as shown below:

mysql> show plugins;
...
| auth_pam                      | ACTIVE   | AUTHENTICATION     | auth_pam.so        | GPL     |
| auth_pam_compat               | ACTIVE   | AUTHENTICATION     | auth_pam_compat.so | GPL     |
+-------------------------------+----------+--------------------+--------------------+---------+

And also have this configured:

cat /etc/pam.d/mysqld
auth required pam_sss.so
account required pam_sss.so

I have a group on the AD server called "dba", and added an AD user 'john.d' in this group. So i would like to log into MySQL using Ad users e.g., john.d, who should also inherit all privileges granted to the "dba" group. Below is how this AD group, "dba", is a setup to allow its users access the Percona server:

CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysqld,dba=dbarole';
CREATE USER 'dbarole'@'%' IDENTIFIED BY 'dbapass';
GRANT ALL PRIVILEGES ON *.* TO 'dbarole'@'%';
GRANT PROXY ON 'dbarole'@'%' TO ''@'';

When i log in into mysql as dbarole, everything works well with all granted privileges. But when i login as john.d, one of the AD users included into the "dba" AD group, this user does not inherit the privileges (ALL) granted to its group, but only has the USAGE privilege as shown below:

mysql> show grants;
+-----------------------------------+
| Grants for @                      |
+-----------------------------------+
| GRANT USAGE ON *.* TO ''@''       |
| GRANT PROXY ON 'dba'@'%' TO ''@'' |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

My Question is, how can i get an AD user inherit privileges granted to their group in MySQL?

All i had to do is add the dba group using the following realm command:

realm permit -g dba

Doing so will automatically change the /etc/sssd/sssd.conf file and add the following line(s):

access_provider = simple
simple_allow_groups = dba

Before running real, access_provider was setup as "ad", and was automatically changed to "simple" as shoen above.


How to select a subset from a query result I have a query which calculates the Time Difference and Distance Between the Next and Previous Row:

SELECT  * FROM (
            SELECT
                iVehicleMonitoringId AS PreviousId,
                LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentId,
                iGeofenceId AS GeofenceId,
                eEventCode as 'EventCode',
                iAssetId AS AssetId,
                LEAD(iAssetId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousAsset,
                sDigitalInputValue AS PreviousIgnition,
                LEAD(sDigitalInputValue, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentIgnition,
                dtUTCDateTime AS StartDate,
                LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS EndDate,
                DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
                sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

                FROM VehicleMonitoringLog
                    WHERE dtUTCDateTime > @utcStartDate AND dtUTCDateTime < @utcEndDate
                            AND iAssetId in (Select iAssetID From #tblAssets)
        ) AS dt
        ORDER BY dt.AssetId, dt.StartDate

Which returns the following result

Which is fine - Now I need to calculate the Total Distance and Time in between the selected section (Highlighted in Blue).

So basically it is the  Total Distance and Total Time From eEventCode = 6 To eEventCode = 7. There can be more than one set with start event = 6 and end event = 7.

SQL Fiddle

How do I perform this operation?

EDIT
Okay this is the new fiddle with a dataset: http://sqlfiddle.com/#!6/40a9e1

Now this data has two datasets where start eventcode = 6 and end eventcode = 7

Now if I execute this query - to get distance between two adjacent points:

SELECT
    iVehicleMonitoringId AS PreviousId,
    LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS CurrentId,
    iGeofenceId AS GeofenceId,
    iAssetId,
    dtUTCDateTime,
    eEventCode as 'EventCode',
    DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
    sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

    FROM VehicleMonitoringLog
        WHERE
        iAssetId = 6024 AND
        dtUTCDateTime > N'2016-01-26 04:44:00' AND dtUTCDateTime <  N'2016-01-26 12:39:45'
        Order by dtUTCDateTime

The result should add all the Distances columns for each set.

This query SUM all the distances between 6 and 7:

SELECT AssetId, [Event], GeofenceId
    , DistanceCoveredK = SUM(DistanceCoveredK)
    , TimeSpentDuringVisitSeconds
FROM (
    SELECT AssetId = vm.iAssetId
        , [Event] = c.eEventCode
        , DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
            LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY vm.iAssetId, vM.dtUTCDateTime ORDER BY c.dtUTCDateTime)
        )
        , GeofenceId = vm.iGeofenceId
        , TimeSpentDuringVisitSeconds = DATEDIFF(SECOND, vm.dtUTCDateTime, c.dtUTCDateTime)
    FROM VehicleMonitoringLog vm
    CROSS APPLY (
        SELECT top(1) eEventCode, dtUTCDateTime, sptGeoLocaitonPoint
        FROM VehicleMonitoringLog WHERE iAssetId = vm.iAssetId AND dtUTCDateTime > vm.dtUTCDateTime AND eEventCode = 7
        ORDER by dtUTCDateTime
    ) c
    INNER JOIN VehicleMonitoringLog p ON p.iAssetId = vm.iAssetId AND p.dtUTCDateTime >= vm.dtUTCDateTime AND p.dtUTCDateTime <= c.dtUTCDateTime
    WHERE vm.eEventCode = 6
) g
GROUP BY AssetId, [Event], GeofenceId, TimeSpentDuringVisitSeconds

Output:

AssetId Event   GeofenceId  DistanceCoveredK    TimeSpentDuringVisitSeconds
6024    7       1015        172,642601234594    100
6024    7       1015        1210,11662866215    28393

I changed the datatype of sptGeoLocaitonPoint in your SQL Fiddle from int to geography. New SQL Fiddle is here


Can someone give me feedback on my ER diagram? I am new to creating ER diagrams and was wondering if anyone could review one that I constructed for an assignment. I used min, max notation in my design but I am not entirely sure if I did everything correctly.

Directions:

I would like to create a simple database that tracks my job history.

The database tracks my employee information: name, email, phone number, hire date (if known), and current salary. For each job it tracks the department I worked at, including the department name, manager and location.

In some cases I worked remotely, in those cases the database indicates that I did
work remotely and lists the nearest office location. Each job has a title, starting salary and an ending salary.

Because I could have multiple positions at the same company the job history portion of the database is uniquely identified by my employee id and start date. The history portion also has my end date for a role.

Assume I can only work for one department at a time and that the database tracks only one job at a time. Also assume that I want to track the information of a few of my team mates (not their salaries of course).

A single employee can have multiple job histories. A single employee can have one or more jobs. Job histories are associated with a single department.

What other assumptions might you need to simplify this example? Remember submitted ER models
should be in the standard or the (min, max) notations.

Draw an ER model that represents the above requirements description. What assumptions do you
need to make, if any, to complete the diagram? If needed make assumptions to simplify the final ER.
Bear in mind that not every app view or field discussed above is required to be represented on the ER
diagram. Additionally, in some cases the requirements may list irrelevant details that do not need to be
represented.

Here is the diagram I constructed.

Any feedback would be greatly appreciated.

1) There are 2 independent relations: from Employee to Job and from Employee to Job History. This means, that potentially Employee can have Jobs that are not contained in the Job History, and vice versa, there can be Job History elements for which there is no element in the Job relation.

2) Wording is misleading. Based on relation from Job to Job History one can suppose that StartDate and EndDate mean period where this Job existed. To different times different Employees could have been assigned to this Job. But based on the relation from Employee to Job History one can suppose that StartDate and EndDate means dates of assignment of particular employee to this Job. Which of these you mean is not clear.

3) I would expect that Job is related to Department, not the Job History.


How to determine database read/write ratio on MongoDB MMS? By looking at MongoDB MMS charts, is there a way to calculate a database read/write ratio, or roughly determine whether a database is read-heavy or write-heavy?

Assume I do not have contact with developer and therefore has no idea on database's schema or use case.

Edit: I understand MMS metrics are based on entire databases on the single mongod instance. However, assume currently there is only one database exist.

Example MMS chart:

There are two way.

opcounters

Build as sum of the opcounters which are write operations, build a sum of the opcounters which are read operations set them into relation. To do this reliably for a sharded cluster, you have to create a dashboard which groups the opcounters for the shards.

network in/out ratio

This is self explanatory. But please keep in mind that you have to divide the out ratio by the number of replica set members to get a rough idea. For a sharded cluster, you need this grouped by shards in a dashboard, too.

What really comes to my mind: you should actually know if you have a rather read or rather write heavy application. Your use case, your business model (if applicable), your implementation, your integration tests: all these should give you an idea.


Sql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'? One transaction log file is growing and growing for a couple of days and I can't find out why. Now I figured out that either the way the activities are stored in the sql logfile OR the behaviour of sql server/backup software changed.

The log files have been backed up every 15 minutes by a third party software.

Until a specific point some days ago, the log backup event is visible every 15 mins as it always used to in the log with the following entries (newer first):

  BackupLog: SafeCheckpoint done

  Backup: Media open

  BackupLog: Work estimate done

  BackupLog: Log files done

  BackupLog: Writing history records

  Log was backed up. Database: xy_db, creation date(time):
  2014/01/10(03:31:14), first LSN: .............. This is an
  informational message only. No user action is required.

  BackupLog: Writing history records

  BackupLog: Finished

After that specific day, there is no more such entry visible.
But instead I can see the following log entries indicating something like a log backup as well (newer first):

  BackupDatabase: Log files done

  Database differential changes were backed up....

  Completed file yyy.mdf

  Started file xxx.mdf

  BackupDatabase: Log files done

  BackupDatabase: Database xy_db

I wonder if or why the presentation of the log backups may have changed and if the new (bolded) entries indicate the same action? I have checked

 DBCC TRACESTATUS (3226);

that tells me status = 0 but I can't find any hints in the log that this ever has been changed these days what also makes me unsure if those Backup Log" entries I was used to ever depended on this trace flag?

EDIT:
I have checked the trace flags for different servers. the trace flag 3226 is turned off but I can see the Log entries I am used to in the sql server logs. So this seems not to be involved here.

Where are those different / new log entries possibly coming from?

The final reason for this effect was that the Backupexec process responsible to take regular log backups was still present and running (so IT monitoring did not alert anything), but it was kind of internally crashed, so it did not do it's job properly.
In order to address this kind of event I decided to add the log file events and it's metadata details to the SQL Server monitoring. Once every day those information is captured and added to the monitoring information repository, where I can run reports and queries against to see progress and find out abnormal behavior.
This ended in a report about the log files looking similar to this sample. It shows the file size over time of each TL and also the percentage of usage at each point. If point size increases, the file contents grow. If they grow too much, the file size increases. Dramatically file size decrease was caused by manual shrink operations (followed by rolling eyes after it grew and grew again...). Basically log file usage and backups should be aligned in a way it almost never grows.

This is very useful to keep an eye on and eventually could also be used to alert by using this report as a data driven subscription (which I have not tried yet).


I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB? FYI: I don't believe this is a duplicate question, I've looked and most are general questions with vague answers. See more specifics below:

Currently a database I am in charge of locks up every once in awhile to a state that becomes unresponsive, and I need to manually restart the MySQL server to make it become responsive again (Yuck). I believe the culprit is an UPDATE query that is being run, which is being called every 1 minute for each active user in the system.

The query is something like this:

UPDATE logging_table SET log_time = log_time + 60 WHERE id = X;

The table is not small (approx 0.5 million records) and is both updated and selected from quite often; it is a "Main operations" type table that is related to quite a few other tables (No FK's, just relations in the PHP framework we are using). The monitoring app I use shows the above query consuming about 40% of the overall database time in our app, followed by a SELECT query that gets information from that table.

Currently the table is MyISAM as it worked fine for a lower number of users, but as the active users participate during peak times of the day, the database locks up.

Does this sound like a table I should convert over to InnoDB (The rest of the tables were setup as MyISAM by default)? I should note that it is not feasible at this point to split this table so that frequent updates can happen on another table.

Thanks for any suggestions.

In almost every use case, InnoDB is preferred over MyISAM.  So, yes.

To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed.  Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased.

In MyISAM, an UPDATE blocks all other operations on the table.  In InnoDB, it blocks only the one row (assuming the WHERE clause restricts the scope to a single row).

It is ok to have a mixture of MyISAM tables and InnoDB tables.


SELECT SUM and remove similar rows SELECT
SUM(coins.available_supply * hist_all.price) AS market_cap_usd_all
FROM coins
    LEFT JOIN hist_all ON coins.id = hist_all.coins_id
    WHERE hist_all.price != 0
AND (hist_all.second_coin = 'USD' or hist_all.second_coin = 'USDT');

I try get sum market_cap_usd_all, but in my table are rows with USD and USDT:

My query should like this:

if exists row second_coin = USD, DON'T GET this same row with 'USDT', but if don't exists row with second_coin = USD check if it exists row with second_coin = USDT and get him. So the table should like this:

I don't know how write query with WHERE IF I tried this:

... WHERE IF (hist_all.second_coin = 'USD', hist_all.second_coin = 'USD', hist_all.second_coin = 'USDT')

but it still sums all rows, both from USD and USDT.

You need a join or a correlated subquery (with `EXISTS), in order to compare rows:

SELECT
    SUM(c.available_supply * h.price) AS market_cap_usd_all
FROM coins AS c
    JOIN hist_all AS h
    ON c.id = h.coins_id
WHERE h.price <> 0
  AND ( h.second_coin = 'USD'
     OR
        h.second_coin = 'USDT'
        AND NOT EXISTS
            ( SELECT 1
              FROM hist_all AS hi
              WHERE hi.first_coin = h.first_coin
                AND hi.second_coin = 'USD'
            )
      ) ;


DB2 - Archive Mode - on what values to set LOGPRIMARY,LOGSECOND,LOGFILSIZ? we must set our database from CIRCULAR to ARCHIVE mode in order to enable ONLINE backups.
Backups will be done every 1 day.
Database is now almost 30GB.
Database grows approximately  1GB per month.
For online backup we will use Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option.
Time to restore is not so important parameter, it is important to everything restore from previous day when everything was OK.

Can you please assist me to set those three parameters on appropriate value? This is production database with important data.

LOGPRIMARY and LOGSECOND are set to values 13 and 15 by default but I think it is not enough? Is it to large if I set them to 50 for example? What can be negative effect?

Current values are: 13, 15, 4096.
I run Configuration Advisor and it gives me those values: 27, 7, 1024.
But for many wizard questions I used default values because I do not know exact answers (for example Transactions per minute,or Average number of connected remote applications..)
Why it put the lower value for LOGFILSIZ?

Can someone additionally suggest me are those good values or not ?
Thank you!!!!

LOGPRIMARY, LOGSECOND and LOGFILSIZ combine to define the maximum size of the transaction log on your server using the following formula:

Total Size = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096 bytes

In your case, this results in (13 + 15) * 4096 * 4096 = 469762048 bytes.

The maximum transaction log size limits 2 things:

1) the absolute size of a single transaction
2) the "timespan" between the oldest active transaction and the newest transaction.

You need to make it large enough to handle both scenarios, although if you're running into problems relating to #2 then you really have a problem with a poorly-behaved app that isn't committing.

Choosing an appropriate value for LOGFILSIZ should be based on how regularly you want to archive log files, as this will have an effect on your recoverability in the event of a problem.  If your database archives log files only once every hour (or less), you will potentially lose much more data in the event of a serious problem than if your database archives a log file every 2-3 minutes (because every time a log file is archived it should be copied to NetBackup).


Microsoft SQL Server SQL Injection Through .NET Even With Quote Escaping I've been reading up on SQL Injection as part of a security audit for a fairly large web service.  I've been googling and read all the posts I could find here and on SO and have a fairly solid understanding of how dynamic queries really should be constructed and some of the differences between MySQL and MSSQL.  I eventually came across this page published earlier this year by Microsoft titled SQL Injection.  In the section titled 'Filtering Input', it states:

  Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}

Writing Secure Code Second Edition also discusses this on page 401-402, but it looks like the author didn't actually try the C# examples (or maybe the SQL version he used back in 2003 doesn't handle the escaped strings correctly).  It states that the string input will lead to an invalid query (in reality the escaping just queries for a value with a single quote in it), and then states that it could also be attacked with using the value for an unquoted numeric field (though it can't because the field in the example comes through a local variable of type int, which can't possibly contain the information needed for an injection).

I don't doubt that there is a possible attack vector here, but I haven't been able to find any explanations of how this might be accomplished.  From my other research, it looks like one possible way might involve strange byte sequences in non-Unicode strings.  In my case, I'm working with .NET, so there is no possibility of such sequences, and I would think that either ADO.NET, SQL Server, or both would properly handle Unicode strings with any other types of characters inside the single-quotes.  (Though interestingly the sample code appears to be in C#).

I'm also well aware of the other methods of SQL injection listed on Microsoft's page, and for the purposes of this question, I'm not worried about those.  Also, I'm only concerned about properly typed string literal inputs here.  This code always parses the incoming types into their appropriate runtime type before composing the SQL queries, so the quote escaping only applies when the data is literal string data.  So in my case, the function actually looks more like:

private string SafeSqlStringLiteral(string inputString)
{
  return "'" + inputString.Replace("'", "''") + "'";
}

This thing that concerns me most is the following phrase in the documentation:

  However, because of the large number of characters that may pose problems, this is not a reliable defense.

Is this just referring to strange single-byte encodings, or is there a Unicode character that this applies to (I haven't been able to find one programmatically)?

The code I'm reviewing is extensive, and uses this type of escaping in many places (and in a way that's not trivial to fix). I'm not asking for an exploit directly (that's frowned upon IIRC), but I'd like to get some more-expert-than-me clarification on this so that I might be able to convince myself that this isn't an issue, or convince the team leader that it is an issue so that we can budget the significant amount of time necessary to fix these issues.

So, my question is, assuming we're not playing any games with PInvoke or unsafe code to create single-byte strings and call SQL Server with those, is there any way using .NET to inject SQL through this type of string literal escaping (as in SafeSqlStringLiteral above) when the string literals are only used in comparisons to string columns and don't involve LIKE clauses, making queries against Microsoft SQL Server 2012+?

Single-quote escaping can be circumvented if there is a numeric field input that is not checked for only numeric data (and, as always, if the field isn't length limited.) Here's the basic method:

Enter a valid number, followed by a semicolon to terminate the existing command. Then enter something like below, after the semicolon - this will execute the command SELECT * FROM users;

DECLARE @o varchar(max);SELECT @o= CHAR(83)+CHAR(69)+CHAR(76)+CHAR(69)+CHAR(67)+CHAR(84)+CHAR(32)+CHAR(42)+ CHAR(32)+CHAR(70)+CHAR(82)+CHAR(79)+CHAR(77)+CHAR(32)+CHAR(117)+CHAR(115)+ CHAR(101)+CHAR(114)+CHAR(115)+CHAR(59);EXEC(@o);

Nowadays, having code that would allow this through your input field would be pretty unlikely - a simple IsNumeric() check would blow this right out of the water, and a rational fieldsize limit on the numeric input leaves you trying to execute DECLAR or some such, which will of course blow up. But this is theoretically possible.


Clustered index creation fail on table creation We've got an error when running the following script;

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Table_Name')
BEGIN
CREATE TABLE Table_Name
(
    Field_Name_1 binary(32) NOT NULL CONSTRAINT PK_Name_Goes_Here PRIMARY KEY NONCLUSTERED
    , Field_Name_2 int NOT NULL
    , Field_Name_3 datetime NOT NULL INDEX IX_Name_Goes_Here CLUSTERED
)
END

In particular it's the clustered index creation throwing the following error:

  Msg 1018, Level 15, State 1, Line 15
  Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

This is odd as it works on all of our servers except for a particular QA server. The fix we've put in place is to create the clustered index outside of the table creation statement but I'd be interested if anybody has come across this problem before?

The syntax for inline index declaration was added in SQL Server 2014, though that is was absolutely unclear in the official CREATE TABLE documentation. After speaking to the documentation owners, that topic now accurately reflects that inline index syntax is only valid starting with SQL Server 2014 (and some variations in 2016):

The other instances, where this syntax is working for you, must be on SQL Server 2014 or newer.

On 2012, regardless of compatibility level, you'll need to create the index separately.


mongodump replicaset - 10 hours data loss acceptable - do we need --oplog (oplog.bson) option? We have a 3 node replica set. We take backups from secondary with mongodump. Our customer said that it's ok too loose 10 hours of data in worst case.

So we decided we don't need point in time recovery --oplog (oplog.bson).

Is this correct in our use case with 10 hours of data loss acceptable?

Is oplog the same as MySQL binary logs?

Yes. OPlog is kind of the same as MySQL binary logs as both are used to sync data to secondary and point in time recovery.

You can use mongodump without --oplog option. You will only loose current writes in your backup. As you have a 10 hour window it wouldn't be an issue.

In this scenario I recommend you to use one of your replica set with slaveDelay.


How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value? I want to set addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed:

mysql> alter table mytable change c1 c2 timestamp null default addtime(current_timestamp, '15:00:00');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'addtime(current_timestamp, '15:00:00')' at line 1

How to do this? Thanks.

MySQL does not support functions in column default values. CURRENT_TIMESTAMP is the closest thing you'll find to an exception:

  The DEFAULT clause specifies a default value for a column. With one
  exception, the default value must be a constant; it cannot be a
  function or an expression. This means, for example, that you cannot
  set the default for a date column to be the value of a function such
  as NOW() or CURRENT_DATE. The exception is that you can specify
  CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

http://dev.mysql.com/doc/refman/5.5/en/create-table.html#id850368


Extract part of string based on nth instance of character I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character.

\\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS
\\fileServerA\d$\LiteSpeed\Wednesday\ServerB\Tlog\model_TLOG_20120321_1038.SLS
\\fileServerA\d$\LiteSpeed\Saturday\ServerA\TLog\DBA_2_TLOG_20120811_1538.SLS
\\fileServerA\d$\LiteSpeed\Friday\ServerB\Tlog\DB3_TLOG_20120914_2330.BAK
\\fileServerA\d$\LiteSpeed\Wednesday\ServerC\Tlog\DB44_TLOG_20120815_1445.BAK

Desired results:

ServerA
ServerB
ServerA
ServerB
ServerC

Well, one way would be to use an ordered split function:

CREATE FUNCTION dbo.SplitStringsOrdered
(
    @List       NVARCHAR(2000),
    @Delimiter  NVARCHAR(32)
)
RETURNS TABLE
AS
    RETURN
    (
      SELECT rn = ROW_NUMBER() OVER (ORDER BY Number), Item
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
          CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_objects) AS n(Number)
        WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
      ) AS y);

Sample usage:

DECLARE @x TABLE(filepath NVARCHAR(2000));

INSERT @x VALUES
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS'),
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerB\Tlog\model_TLOG_20120321_1038.SLS'),
('\\fileServerA\d$\LiteSpeed\Saturday\ServerA\TLog\DBA_2_TLOG_20120811_1538.SLS'),
('\\fileServerA\d$\LiteSpeed\Friday\ServerB\Tlog\DB3_TLOG_20120914_2330.BAK'),
('\\fileServerA\d$\LiteSpeed\Wednesday\ServerC\Tlog\DB44_TLOG_20120815_1445.BAK');

SELECT x.filepath, s.Item FROM @x AS x
  CROSS APPLY dbo.SplitStringsOrdered(x.filepath, '\') AS s
  WHERE s.rn = 7;

Results:

filepath                                     Item
-------------------------------------------  -------
\\file...eed\Wednesday\ServerA\Tlog\DBA1...  ServerA
\\file...eed\Wednesday\ServerB\Tlog\mode...  ServerB
\\file...eed\Saturday\ServerA\TLog\DBA_2...  ServerA
\\file...eed\Friday\ServerB\Tlog\DB3_TLO...  ServerB
\\file...eed\Wednesday\ServerC\Tlog\DB44...  ServerC


MySQL Workbench 5.2 edition - Unable to establish the connection I have installed MySQL (mysql-workbench-gpl-5.2.37-win32.msi ) which is a Windows Installation Workbench.

Couple of times, I have done re-installation on my computer. Now, I am not able to establish the connection.

Following error message is thrown, while i try to connect to the database from Workbench

Check that mysql is running on server 127.0.0.1
Check that mysql is running on port 3306 ( note : 3306 is the default, but this can be changed )
Check the root has rights to connect to 127.0.0.1 from your address (mysql rights define what clients can connect  to the server and from which machines)
Make sure you are both  providing a password if needed and using the correct password for 127.0.0.1 connecting from the host address you’re connecting from

I hope your problem might have been already resolved. However, I recently had a similar experience and I had to do following to resolve it.
1. Launch MySQL Workbench (Mine was 5.2.36 CE Revision 8542)
2. Double click on a server instance to manage the server instance
3. Goto Users and Privileges
4. Select root or other user you wish to check from the list in "Server Access Management" tab
5. Change the "Limit connectivity to hosts matching:" to IP that you are using to connect OR just change it to "%" to remove the IP restriction.<6>
6. In my case I had to change it to "%" to make it work.

Hope this will be helpful to someone, someday!
Cheers
Manish.


Error on Oracle calling external procedures I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it.

the C source(libcprog.c) is like this:

int exec_hmmftg ( char *p1 , char *p2, char *p3 ){}

the library in oracle database is:

CREATE LIBRARY CPROGLIB UNTRUSTED AS '/libcprog.so';

the created pl/sql function is:

CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1    IN     VARCHAR2,
                                          P2    IN     VARCHAR2,
                                          P3    IN OUT VARCHAR2)
    RETURN BINARY_INTEGER
    IS EXTERNAL NAME "exec_hmmftg"
    LIBRARY CPROGLIB
    LANGUAGE C
    PARAMETERS(P1 STRING, P2, P3 BY REFERENCE STRING, RETURN INT);

When I tried to call CALL_C_PROGRAM I catch:

ORA-01405: fetched column value is NULL

How can I know where is the problem?

New

I changed library definition to address somewhere wrong and I still catch the same exception!

Is there a parameter or something which I have to set before calling the function?

The following configuration works for me on Oracle 11.2.0.2:

C prog:

[oracle@node1 ~]$ cat exec_hmmftg.c
#include <stdio.h>

int exec_hmmftg ( char *p1 , char *p2, char *p3 )
{
  return 5;
}
[oracle@node1 ~]$

Compile it, and make sure it's placed in $ORACLE_HOME/bin or $ORACLE_HOME/lib (configuration can be changed to allow other directories) - compiler flags will differ for AIX too.

gcc -shared -o $ORACLE_HOME/lib/exec_hmmftg.so exec_hmmftg.c

Configure EXTPROC in the listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.6)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PHIL112)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = PHIL112)
    )
  )

Restart the listener with lsnrctl stop followed by lsnrctl start.

Create the reference to the external library & create the function to interface between PL/SQL and the C prog:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 14 17:51:21 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE LIBRARY CPROGLIB UNTRUSTED AS '/u01/app/oracle/product/11.2.0/dbhome_1/lib/exec_hmmftg.so            ';
  2  /

Library created.

SQL> CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1 IN VARCHAR2, P2 IN VARCHAR2, P3 IN VARCHAR2)
  2      RETURN BINARY_INTEGER
  3      IS EXTERNAL NAME "exec_hmmftg"
  4      LIBRARY CPROGLIB
  5      LANGUAGE C
  6      PARAMETERS(P1 STRING, P2 STRING, P3 STRING, RETURN INT);
  7  /

Function created.

SQL>

Test the function:

SQL> select call_c_program('a','b','c') from dual;

CALL_C_PROGRAM('A','B','C')
---------------------------
                          5

SQL>

Not quite sure what you're doing wrong without seeing the full C you've written. Try and setup this simple case I've made for you, then add functionality bit by bit from there.


How to connect to SQL Server Analysis Server via Management Studio command line I can connect SQL Server Management Studio to a SQL DB by passing command line parameters like this:

"C:\Program Files (x86)\....\Ssms.exe" -E -S SERVERNAME -d DB_NAME -nosplash

(Obviously -E is for window Authentication)

This connects to the SQL Server. But I often find I want to connect to an Analysis Server instance instead. Can anyone tell me the command line option please?

There isn't one.

But... why open and close Management Studio all the time? Leave it open and use the Registered Servers list (View | Registered Servers).


Does oracle database suffer from parameter sniffing issue Does oracle database suffer from parameter sniffing issue in stored procedures?

Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a feature.  Also considering with Sql Server 2017 features like automatic query tuning, I was wondering is SQL Server late in fixing these estimates/plan caching issues compared to other Database management system?

Yes it does.
Starting with version 11.1, we have Adaptive Cursor Sharing with bind sensitivity and bind awareness, which allows the optimizer to produce multiple optimal execution plans for the same SQL statement, depending on the bind values.

Purpose of Adaptive Cursor Sharing
With bind peeking, the optimizer
peeks at the values of user-defined bind variables on the first
invocation of a cursor. The optimizer determines the cardinality of
any WHERE clause condition as if literals had been used instead of
bind variables. If a column in a WHERE clause has skewed data,
however, then a histogram may exist on this column. When the optimizer
peeks at the value of the user-defined bind variable and chooses a
plan, this plan may not be good for all values.
In adaptive cursor sharing, the database monitors data accessed over
time for different bind values, ensuring the optimal choice of cursor
for a specific bind value. For example, the optimizer might choose one
plan for bind value 10 and a different plan for bind value 50. Cursor
sharing is "adaptive" because the cursor adapts its behavior so that
the optimizer does not always choose the same plan for each execution
or bind variable value. Thus, the optimizer automatically detects when
different execution of a statement would benefit from different
execution plans.

This does wonders on paper and in presentations, but in the real world, there is still room for improvement.
Shortly: the problem with ACS is that it does not work immediately for new values. It uses the already existing execution plan optimal for other values, and after that, on a repeated attempt, it may choose a different plan. We may not have time to wait for the first sub-optimal attempt for the new bind value to finish, as it may take orders of magnitude longer depending on the actual statement and data quantity/distribution.
https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1


Mongodb replica set primary and secondary node conf fail when one slave is down In my env, I had created MongoDB replica set conf for high availability so one server fails should be handled by other also in my conf I have 3 DB server and 1 arbiter too but,

my slave's server fails due to disk utilization by this, my master node also fails to provide data to api's which create disrupts my whole system.

So, is this event occurs due to configuration fail or its mongo property? Or some conf other than replica set creation is required to prevent this??

currently, I am using MongoDB shell version v4.0.3

Followed this doc some of this command used in the replica set setup

https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

In my env, I had created MongoDB master-slave conf for high
  availability so one server fails should be handled by other but, my
  slave's server fails due to disk utilization by this, my master node
  also fails to provide data to api's which create disrupts my whole
  system.

As per MongoDB documentation here to providing all the functionality of master-slave deployments, replica sets are also more robust for production use. Master-slave replication preceded replica sets and made it possible to have a large number of non-master (i.e. slave) nodes, as well as to restrict replicated operations to only a single database; however, master-slave replication provides less redundancy and does not automate failover.

Master instances store operations in an oplog which is a capped collection. As a result, if a slave falls too far behind the state of the master, it cannot “catchup” and must re-sync from scratch. Slave may become out of sync with a master if:

The slave falls far behind the data updates available from that
master.
The slave stops (i.e. shuts down) and restarts later after the master
has overwritten the relevant operations from the master.

When slaves are out of sync, replication stops. Administrators must intervene manually to restart replication. Use the resync command. Alternatively, the --autoresync allows a slave to restart replication automatically, after ten second pause, when the slave falls out of sync with the master. With --autoresync specified, the slave will only attempt to re-sync once in a ten minute period.

To prevent these situations you should specify a larger oplog when you start the master instance, by adding the --oplogSize option when starting mongod. If you do not specify --oplogSize, mongod will allocate 5% of available disk space on start up to the oplog, with a minimum of 1 GB for 64-bit machines and 50 MB for 32-bit machines.

  So, is this event occurs due to configuration fail or its mongo
  property? Or some conf other than master-slave creation is required to
  prevent this?

As MongoDB documented here from MongoDB 4.0 removes support for master-slave replication. Before you can upgrade to MongoDB 4.0, if your deployment uses master-slave replication, you must upgrade to a replica set.

Warning :
Deprecated since version 3.2: MongoDB 3.2 deprecates the use of
master-slave replication for components of sharded clusters.

Important:
Replica sets replace master-slave replication for most use cases. If possible, use replica sets rather than master-slave replication for all new production deployments. This documentation remains to support legacy deployments and for archival purposes only.

After modification the question of OP

As  here MongoDB provides two options for performing an initial sync:

Restart the mongod with an empty data directory and let MongoDB’s normal initial syncing feature restore the data. This is the more simple option but may take longer to replace the data.

See Automatically Sync a Member.

Restart the machine with a copy of a recent data directory from another member in the replica set. This procedure can replace the data more quickly but requires more manual steps.

See Sync by Copying Data Files from Another Member.

  WARNING: Avoid reconfiguring replica sets that contain members of different MongoDB versions as validation rules may differ across
  MongoDB versions.

Reconfigures an existing replica set, overwriting the existing replica set configuration. To run the method, you must connect to the primary of the replica set.

For Example

rs.reconfig(configuration, force)

To reconfigure an existing replica set, first retrieve the current configuration with  rs.conf(), modify the configuration document as needed, and then pass the modified document to rs.reconfig().

rs.reconfig() provides a wrapper around the replSetReconfig command.

The force parameter allows a reconfiguration command to be issued to a non-primary node.

For further your ref here, here and here


SQL update multiple rows in destination table with same id but different values from other table creditNote table

id      invoice_id     generated_at(timestamp)     status
10101      111          2018-02-28 14:42:39.247         generated
23982      111          2018-03-30 11:11:11.247          generated

paymentModeAmount table

id      invoice_id     paid_on(timestamp)     payment_mode
98236      111                                 creditNote
63725      111                                 creditNote

After execution of below query OUTPUT is

paymentModeAmount table

id        invoice_id     paid_on(timestamp)       payment_mode
98236        111         2018-02-28 14:42:39.247   creditNote
63725        111         2018-02-28 14:42:39.247   creditNote

Here PaidOn Column has same data, but we need to get two different timestamps

Expected

id      invoice_id     paid_on(timestamp)       payment_mode
98236      111         2018-02-28 14:42:39.247   creditNote
63725      111         2018-03-30 11:11:11.247   creditNote

paidOn timestamp's should be different as expected

PL/SQL Function

CREATE OR REPLACE FUNCTION updatePaidOn()
RETURNS TABLE(
invoiceid BIGINT,
generatedat TIMESTAMP)
AS $$
BEGIN
RETURN QUERY SELECT
generated_invoice_id as invoiceid, generated_at as generatedat
FROM
credit_note
WHERE
status='generated';
END; $$

LANGUAGE 'plpgsql';

Execute the below function

UPDATE payment_mode_amount pm
SET paid_on = sq.generatedat
from updatePaidOn() as sq
where pm.invoice_id = sq.invoiceid
and pm.paid_on is null
and pm.payment_mode='creditNote';

Update accordingly timestamps..

If you don't care which timestamp is used for which target row, you can match the two tables by joining them on a generated "row number" using e.g. this:

with cn as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from creditnote
  where status = 'generated'
), pnr as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from paymentmodeamount
  where payment_mode = 'creditNote'
)
select *
from pnr
  join cn on cn.rn = pnr.rn and cn.invoice_id = pnr.invoice_id;

This joins the rows based on the ordering of the ID values in the tables creditnote and paymentmodeamount. If you want you can also order the creditnote table by generated_at.

Given your sample data this would return something like this (this is only to demonstrate what the above join does):

id    | invoice_id | generated_at | rn | id    | invoice_id | generated_at        | rn
------+------------+--------------+----+-------+------------+---------------------+---
63725 |        111 |              |  1 | 10101 |        111 | 2018-02-28 14:42:39 |  1
98236 |        111 |              |  2 | 23982 |        111 | 2018-03-30 11:11:11 |  2

Now the above query can be used as part of an UPDATE statement to bring the rows together.

with cn as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from creditnote
  where status = 'generated'
), pnr as (
  select id, invoice_id, generated_at, row_number() over (order by id) as rn
  from paymentmodeamount
  where payment_mode = 'creditNote'
)
update paymentmodeamount p
   set generated_at = cn.generated_at
from pnr
  join cn on cn.rn = pnr.rn and cn.invoice_id = pnr.invoice_id
where p.id = pnr.id
  and p.generated_at is null;

This assumes that paymentmodeamount.id is the primary key of the table.

Note that this won't be very fast for large tables.

Online example: https://rextester.com/YXAK25669


Getting the "Database does not exist" error message Hoping someone can help out here. I've searched for an answer on my own as much as I could in the areas that I understand regarding SQL Server administration. Something which I'm fairly new at. ( Under 2 years experience )

  Msg 911, Level 16, State 1, Line 1
  Database 'dbname' does not exist. Make sure that the name is entered correctly.

The issue is when running this script (error above) ( script posted below ) it stops and says it's not able to hit a particular database as if its still connected. However, this database which was from a migration from old server to new and was renamed (all areas including files) no longer exists anywhere. I do not see it in any T-SQL outputs or any reports and the MDF / LDF files are long gone. However, the script still hangs and thinks this DB should be part of this instance.

In searching, I've come across various posts stating a possible permission issue and to check the master DB for residual information all of which have not panned out with any solution. I have full access under my active directory login for this instance and have the SA codes as well. Same error when running as that.

Any help would be appreciated, hopefully, its something simple and I'm just missing something obvious.

Thank you all for your help, it's appreciated.

DECLARE @CurrentDB sysname
DECLARE curDatabase CURSOR FAST_FORWARD FOR
SELECT name FROM master.sys.databases
--SELECT * FROM master.sys.databases
WHERE name not in ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @CurrentDB
WHILE ( @@FETCH_STATUS = 0)
BEGIN
--print @CurrentDB
--
declare @sql1 varchar(100)
select @sql1 = 'USE ' + @CurrentDB + ';' + '

'

--EXEC sp_sqlexec @sql1
--print @sql1

declare @sql2 varchar(max)
select @sql2 =
-- Ensure a USE <databasename> statement has been executed first.
'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'

declare @sql varchar(max)
select @sql = @sql1 + @sql2

EXEC sp_sqlexec @Sql

--print @sql

FETCH NEXT FROM curDatabase INTO @CurrentDB
END -- curDatabase WHILE loop
CLOSE curDatabase
DEALLOCATE curDatabase

Using your script, I hit the same error with a database called ಠ_ಠ.

I've made some changes to handle Unicode strings properly, and quote any odd characters in database names.

Ultimately, the problem was with your use of sp_sqlexec. When I changed it to sp_executesql, the script worked fine.

DECLARE @CurrentDB NVARCHAR(128);
DECLARE curDatabase CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   master.sys.databases
    --SELECT * FROM master.sys.databases
    WHERE  name NOT IN ( 'master', 'tempdb', 'msdb', 'model', 'AdventureWorks', 'AdventureWorksDW' );
OPEN curDatabase;
FETCH NEXT FROM curDatabase
INTO @CurrentDB;
WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        --print @CurrentDB
        --
        DECLARE @sql1 NVARCHAR(MAX) = N'';
        SELECT @sql1 = @sql1 + N'USE ' + QUOTENAME(@CurrentDB) + N';

'       ;

        --EXEC sp_sqlexec @sql1
        --print @sql1

        DECLARE @sql2 NVARCHAR(MAX) = N'';
        SET @sql2
            = @sql2
              +
        -- Ensure a USE <databasename> statement has been executed first.
        N'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE 1 = 0;

select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid
from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'       ;

        --PRINT @sql2

        DECLARE @sql NVARCHAR(MAX) = N'';
        SET @sql = @sql + @sql1 + @sql2;

        PRINT @sql;
        EXEC sys.sp_executesql @sql;

        SET @sql = N'';
        SET @sql1 = N'';
        SET @sql2 = N'';

        FETCH NEXT FROM curDatabase
        INTO @CurrentDB;
    END; -- curDatabase WHILE loop
CLOSE curDatabase;
DEALLOCATE curDatabase;

Also, I'd strongly recommend against writing maintenance scripts like this from scratch. Ola Hallengren writes amazing free ones.


Normalisation to 3NF I've been learning how to normalise data into a normalisation table and I'm struggling to get my head around this example question.

I have this 'clinical record' card:

Would anyone able to explain to me as to how to convert this into UNF and then to 3NF?

The card is UNF. Everything is one row.
3NF is when you make a list of the things, and each list of things has a unique item list, and you link them using thing-item-id.

[list] Form [items] FormID, FormName, FormTemplate (definition of    report)
[list] Address [items] AddressID, StreetNumber, StreetName...    (address data)
[list] Vet [items] VetID, VetName
[list] Species    [items] SpeciesID, SpeciesName
[list] ExaminationType [items]    ExaminationTypeID, ExaminationTypeName, Cost
[list] Patient [items]    PatientID, PatientName, PatientSpeciesID REFERENCES Species (SpeciesID)
[list] Appointment [items] PatientID REFERENCES Patient(PatientID), ExaminationTypeID REFERENCES ExaminationType(ExaminationTypeID), Date, VetID REFERENCES Vet(VetID)

Data is uniquely stored, referenced in table containing transactional data.
Form template would have RDL for creating the card, from the relevant list data.
The idea behind 3NF is store related information in the same list, only once, and reference that data wherever applicable.
Gets complicated when Vet changes name, but you want to reprint previous statement with Vet's previous name ... :)


Query to delete all rows which are not referenced in any other tables Let's say i have a table called images. Images table is referenced in many other tables (movies, books, tv_series) through a foreign key.

My question is if it is possible to write a query which will delete all orphaned rows without specifying exact tables (eg JOIN movies ON ...). This is important because if new table which references images will be introduced it will make the query outdated. If the query will be executed without changes it could delete images which are used in new table.

I don't think you can write a single query, but you can write a query to get the list of tables that refer to the images table, and then you can do a second task which deletes the rows from images which are not referred to in any of the tables in the list.

List of tables that refer to the images table:

SELECT rf.TABLE_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_COLUMN_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS rf
  INNER JOIN information_schema.KEY_COLUMN_USAGE kcu ON
    kcu.CONSTRAINT_NAME = rf.CONSTRAINT_NAME AND
    kcu.CONSTRAINT_SCHEMA=rf.CONSTRAINT_SCHEMA
WHERE rf.REFERENCED_TABLE_NAME = 'images' AND
  rf.CONSTRAINT_SCHEMA = 'your_database';

Delete all rows in images not referred to by those tables:

For this part, perhaps it's better to do a loop and look at one and one row in images. Assume '$row_i_val' is the PK column value of the current row you're looking at:

SELECT sum(q.c) FROM
(
  SELECT count(*) as c FROM $table1 WHERE $table1_col = $row_i_val
  UNION ALL
  SELECT count(*) as c FROM $table2 WHERE $table2_col = $row_i_val
  UNION ALL
  SELECT count(*) as c FROM $table3 WHERE $table3_col = $row_i_val
  UNION ALL ...
) q

If this sum is 0, then you can delete it:

DELETE FROM images WHERE pk_col = $row_i_val;


SQL Server 2008 R2 Express high insert slowdown possibly due to PAGEIOLATCH_SH I have a program that insert row to a table though multiple connections. For each insert, the connection would open and then close. When I run the program, the insert were fast. However, once it reach about 3000 rows or so, SQL server start slowing down. Eventually it become 30x slower than it should be.

The insert statement is NOT a stored procedure, by simply dynamic sql every time when insert into the database.

This is a SQL Express, so memory utilization is limited. CPU utilization is low when running the insert statements.

If I kill the application and run the process again, the insert will be fast again, for the first few minutes until it become a snail again.

When I ran the following SQL

select r.session_id
,status
,qt.text
, r.statement_start_offset/2
, r.statement_end_offset
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
,r.wait_type
,r.wait_time
,r.last_wait_type
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

I notice the query has wait_type of PAGEIOLATCH_SH.

In fact, I notice some other Select statements are having the same problem.

What is PAGEIOLATCH_SH?

What other information should I look for to fix to issue?

Ultimately how do I solve this performance issue?

Thanks

PAGEIOLATCH_SH means a page is read from the disk. There must be more at play in your workload than just INSERTs.

Simply and cheaply put you need a non-Express instance, more RAM and a faster disk. The complex and more expensive answer is you need to fix the application so that it does not read that many pages (ie. it does not do table scans).


Can I Run Two Full Backups on SQL Server 2005 at the Same Time? Running SQL Server Express 2005 here.  We are creating several programs which run a backup on the database using Server Management Objects (SMO) mostly through C# (.NET).  We have separate processes that will invoke the backup at different times.

Are there any issues with running two full backups at the same time if they are dumping the backup to different locations?  The processes are pretty independent of each other and it would be a bit of work to synchronize them all.

Shouldn't be an issue, assuming your I/O subsystem can keep up. ("Different locations" may mean different things to different people - it's not just about drive letters.)


Is it possible to increase Oracle execution plan output indentation? It seems Oracle's execution plan indent each row for just one character. See below image. Is there a setting to increase this value? To me, this is not very readable.

You can use PLAN_TABLE for such result as following:

Your statement for generating the execution plan

EXPLAIN PLAN
SET STATEMENT_ID = 'mystatement1'  -- give your statement a meaningful ID so that we can fetch it from PLAN_TABLE using it
    FOR
SELECT
    *
FROM
    ACTIVE_USERS;

Now, Fetching the execution plan from PLAN_TABLE

SELECT
    ID,
   -- LPAD(' ', LEVEL - 1) -- default format
    LPAD(' ', LEVEL * 2) -- Indentation is done using this
    || OPERATION
    || ' '
    || OPTIONS
    || ' '
    || OBJECT_NAME "Operation",
    OBJECT_NAME   AS "Name"
FROM
    PLAN_TABLE
CONNECT BY PRIOR ID = PARENT_ID
           AND PRIOR STATEMENT_ID = STATEMENT_ID START WITH ID = 0
                AND STATEMENT_ID = 'mystatement2'
ORDER BY
    ID;

Output:

You can add more columns into result according to your need and also add more spaces using LPAD in the above query.

Cheers!!


Running psql/postgres client in an ephemeral mode in a container I want to link two containers1 where one is a full-blown Postgres (9.3)/PostGIS server, and another one is pure client needing no configuration. Just bear with me - this is a for an evolutionary POC which will later emerge into reactive microservices architecture, and we'll abandon container linking. For now, I am experimenting.

My question is about the Postgres (9.3) client.

I would like to link client container to postgres server, and just run some trivial queries from psql from the container's bash prompt using either scheduled events (cron), or from a docker's exec command line.

For this, I would like to use psql in the dumbest possible way - no initdb, no pga_hba.conf, not postgres.conf, just do something like:

1. Link containers (assume I have pg_server container running):

docker run -it --link pg_server:pg_server_1 --name pg_client_1 pg_client

2. Run query from client (client_1) to running server container (pg_server_1):

docker exec pg_client_1 psql -U my_user -h pg_server_1 -p 5432 -d my_db -c "select uncertainty_estimate(' land_cover', manifold_envelope(-81.85, 6.8, -81.01, 7.8, 4326))"

So this is all docker stuff so far.

This is a Docker file I would like to use for my client. Notice it only installs Postgres.

FROM centos:6.8
MAINTAINER Me

ENV PG_FULL  "9.3"
ENV PG_VER "93"
ENV OS_MAJOR "6"
ENV OS_MINOR "8"

RUN rpm -ivh http://yum.postgresql.org/${PG_FULL}/redhat/rhel-${OS_MAJOR}-x86_64/pgdg-centos${PG_VER}-${PG_FULL}-1.noarch.rpm && \
    yum -y install postgresql${PG_VER}

USER postgres

Question: can I just run the container like this and get to psql prompt from a docker exec, or do I need to configure anything else so I can run a psql client in the client container against the server container (as I have shown above)?  The problem is that the container dies as it executes. I need to keep it alive, probably by running some kind of a process in it. That still does not answer the question of the most ephemeral setup for postgres client.

1 - I will appropriately network them later in Docker Compose.

Currently your Dockerfile doesn't contain an execution script so it will finish & exit...

If you want it to always run, you can 'tail -f some file '

But, you can skip it and combine the 2 commands to run the psql command & exit:

docker run -it --link pg_server:pg_server_1 --name pg_client_1 psql -U my_user -h pg_server_1 -p 5432 -d my_db -c "select uncertainty_estimate(' land_cover', manifold_envelope(-81.85, 6.8, -81.01, 7.8, 4326))"

Docker treats the last arguments as a command to run directly.


Failover clustering SQL server 2016 standard I have some trouble finding my way in and distinguishing standard vs enterprise features in SQL Server 2016. What it boils down to: Does failover clustering support multiple databases in SQL Server 2016 Standard Edition?

Or put differently (perhaps?) is failover clustering always on the entire instance and all databases it contains?

Please have a look at Features Supported by Various Editions of SQL Server.

  Or put differently (perhaps?) is failover clustering always on the entire instance and all databases it contains?

No, AlwaysON Availability(AOAG) groups can be configured for specific set of databases. It is not necessary to include all databases which reside on instance. So suppose you have 7 databases on instance you can configure AOAG for 4 databases and leave aside 3 databases.

The unit of failover would be the AG group, this means when failover will happen all databases which are part of that AG will failover
One or multiple databases can be grouped within an Availability Group
One SQL Server instance can support multiple AGs. The Availability Group is the unit of failover
A specific database can be part of one Availability Group only and cannot be a part in multiple AGs

Please read more about AlwaysON Availability groups from this blog it has many links start with link "What is AlwaysON"

Basic AG would only support one database.


SQL Server Linux Disable Remote Connections What is the best way to disable remote connections on a SQL Server Install on Linux (CentOS 7)? I.E. force SSH Tunneling to connect over localhost.

I typically block ports explicitly with IPTables/Firewalld rules, and have 1443 closed off to the outside.

However with MySQL I also tend to close off users to local access only, i.e. 'testUser'@'localhost' and not 'testUser'@'%'. Also, the default if you run mysql-secure-installation is to turn of remote connections as root (SA in SQL Server). This ensures that if my network is breached, they still have to SSH Tunnel into the server.

How do I achieve this?

I was reading this doc and was not able to turn off remote connections when running:

EXEC sp_configure 'remote access', 0 ;
GO
RECONFIGURE ;
GO

Thanks to David Browne for pointing me in the right direction. Sometimes knowing what terms to search for is the hardest part. I also got direction from this article

So to change the SQL Server to listen on localhost or 127.0.0.1 all you have to do is add the following configuration to the file /var/opt/mssql/mssql.conf:

[network]
ipaddress = 127.0.0.1

then restart the server with:

sudo systemctl restart mssql-server

If you want to get a complete list of all configuration parameters you can run

/opt/mssql/bin/mssql-conf list

Note a sample of the output is like

hadr.hadrenabled                           Allow SQL Server to use availability groups for high availability and disaster recovery
language.lcid                              Locale identifier for SQL Server to use (e.g. 1033 for US - English)
memory.memorylimitmb                       SQL Server memory limit (megabytes)
network.disablesssd                        Disable querying SSSD for AD account information and default to LDAP calls
network.enablekdcfromkrb5conf              Enable looking up KDC information from krb5.conf
network.forceencryption                    Force encryption of incoming client connections
network.forcesecureldap                    Force using LDAPS to contact domain controller
network.ipaddress                          IP address for incoming connections

So you can add any of those to the /var/opt/mssql/mssql.conf file. The syntax is that the prefix before the dot goes in square brackets with the suffix listed below (see above for example).

To test it was working I checked:

1) That the applications using it were up

2) Running sudo nmap -PN <servername> to determine if the port was open

3) Trying to open a connection from a remote computer where I previously could with sqlcmd -S <servername> -U <username> which threw the error

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..


PostgreSQL parallel scan with "greater" or "less" than I have two queries. One has "=" other one has ">" in as filter condition. My question is why planner plans only query with equals in condition to run in parallel.
If I mess with defaults for parallel_tuple_cost and parallel_setup_cost I can make both run in parallel. But I would like to know why it is planned this way with default configuration.
I am providing queries which I executed and planner explain output. There are no indexes in for fields which are used in fiter.

This runs in parallel with two workers:
VACUUM; EXPLAIN ANALYSE select screen_name from accounts where followers_count = 100;

(cost=1000.00..81857.06 rows=401 width=12) (actual time=0.442..149.981 rows=5028 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on accounts  (cost=0.00..80816.96 rows=167 width=12) (actual time=0.100..113.974 rows=1676 loops=3)
        Filter: (followers_count = 100)
        Rows Removed by Filter: 1154464
Planning Time: 0.040 ms
Execution Time: 150.294 ms

This runs with single worker:
VACUUM; EXPLAIN ANALYSE select screen_name from accounts where followers_count > 100;

Seq Scan on accounts  (cost=0.00..106105.64 rows=2278992 width=12) (actual time=2.608..562.870 rows=2286022 loops=1)
  Filter: (followers_count > 100)
  Rows Removed by Filter: 1182399
Planning Time: 0.059 ms
JIT:
  Functions: 4
   Options: Inlining false, Optimization false, Expressions true, Deforming true"
   Timing: Generation 0.369 ms, Inlining 0.000 ms, Optimization 0.186 ms, Emission 2.316 ms, Total 2.871 ms"
Execution Time: 640.952 ms

Queries have only different sign in filter condition.
EDIT1:
Using less conservative settings:
SET parallel_tuple_cost to 0;
SET parallel_setup_cost to 0;
SET max_parallel_workers_per_gather to 4;
VACUUM; EXPLAIN ANALYSE select * from accounts where followers_count > 100;

Gather  (cost=0.00..73589.74 rows=2278693 width=121) (actual time=0.220..391.700 rows=2286022 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Seq Scan on accounts  (cost=0.00..73589.74 rows=569673 width=121) (actual time=0.024..117.873 rows=457204 loops=5)
        Filter: (followers_count > 100)
        Rows Removed by Filter: 236480
Planning Time: 0.065 ms
Execution Time: 484.223 ms

Using default settings:
SET parallel_tuple_cost to 0.1;
SET parallel_setup_cost to 1000;
SET max_parallel_workers_per_gather to 2;
VACUUM; EXPLAIN ANALYSE select * from accounts where followers_count > 100;

Seq Scan on accounts  (cost=0.00..106100.83 rows=2278739 width=121) (actual time=2.610..552.259 rows=2286022 loops=1)
  Filter: (followers_count > 100)
  Rows Removed by Filter: 1182399
Planning Time: 0.083 ms
JIT:
  Functions: 2
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 0.462 ms, Inlining 0.000 ms, Optimization 0.175 ms, Emission 2.239 ms, Total 2.876 ms"
Execution Time: 634.788 ms

Your > query returns hugely more rows than =.  Each of the rows being returned from a parallel worker needs to get passed up from the parallel worker to the leader, and doing this is not free.  (This is the cost accounted for by parallel_tuple_cost).  A row filtered out by the parallel worker, on the other hand, does not suffer this expense as it is not passed up.  If most rows will be filtered out, it makes sense to do the filtering in parallel.  If few rows will be filtered out, it might not make sense to do the filtering in parallel but then do the extra work of shuffling the data around between processes.
What you have not shown us is whether doing the > query in parallel is actually faster than doing it in a single process.  Maybe you need to tune parallel_tuple_cost down some.  The default settings is quite conservative.


Minimum Permission to Restart SQL Services Occasionally, our developers need to restart SQL services.  We do not want said developers to be local admins on each server if not necessary.  What is the minimum permission required so that the developers can restart the SQL Services (database engine and agent) remotely using SSMS?  We use SQL Server 2008 R2 on Windows Server 2008 R2.

You can  grant the Service Start/Stop right for the SQL Server and Agent service to the Developers or their Windows group.

You can use

Use Group Policy
Security Templates or
Use Subinacl.exe

Above all are described in this KB article How to grant users rights to manage services

As a side note, you can use sysinternals tool - accesschk.exe. It will help you to find out -

  what kind of accesses specific users or groups have to resources including files, directories, Registry keys, global objects and Windows services.


Difference between COUNT(ColName) vs COUNT(ALL(ColName)) Both query return the same output:

select COUNT(id) from Employee

select COUNT(All(id)) from Employee

So what is the difference in SQL-Server 2008R2?

There is no difference.  ALL is the default keyword, as opposed to DISTINCT.


is shared storage safe in Postgres if all servers are read-only? Description of shared storage in Postgres: https://wiki.postgresql.org/wiki/Shared_Storage

Related question: https://serverfault.com/a/620315/137464

Both of these make it clear that it's not safe to have one write instance and many readers.  What about if all instances are read-only?

In the answer you linked to, Craig says:

  It's possibly practical to do it with minor changes to PostgreSQL( if all the servers are read-only, but I haven't investigated it as it's a pretty uninteresting use-case.

(emphasis mine).

The problem is, there is no way to start a PostgreSQL server and tell it "you are only allowed to look at this data directory, you are not allowed to write anything at all". Even a PostgreSQL server operating as a "hot standby" takes the liberty of writing arbitrary files inside its data directory (e.g. postmaster.pid, temporary files used for on-disk sorts, rewriting hint bits, and so on). If two or more servers were coaxed into operating in hot standby mode against the same data directory, chaos could ensue.

There is simply no "look but don't touch" mode for a Postgres server, so the answer to:

  What about if all [Postgres server instances operating against the same shared data directory] are read-only?

is: not possible today without changing Postgres to implement a true read-only mode.


Select orphaned rows (in a generic way) I have a table my_tables which gets referenced as foreign key in several tables.

I would like to select all rows of my_table which are not referenced in an other table.

AFAIK, it should be possible to do this in a generic way (with some introspection magic).

Quoting the manual:

A foreign key must reference columns that either are a primary key or
form a unique constraint.

So that's not necessarily limited to the PK. But if we start with pg_constraint, we get all FK constraints pointing to the target table automatically. No need to provide any key columns - except if you want to limit to certain FKs.
Using the object identifiers type regclass and table aliases, we can keep the function short and the result safe and unambiguous:
Basic query
SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord) -- original order
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)             -- possibly n cols
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END     -- parentheses for multiple columns
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = 'my_table'::regclass -- target table name, optionally schema-qualified
AND    c.contype = 'f'  -- FK constraints
GROUP  BY c.confrelid;

Produces a query of the form:
SELECT * FROM my_table t
WHERE  NOT EXISTS (SELECT FROM schema1.tbl1 WHERE col1 = t.id)
AND    NOT EXISTS (SELECT FROM "tB-l2" WHERE ("COL2", col3) = (t.col4, t.col5));

Which returns all rows that are not currently referenced by any FK constraint.
If cardinality(c.conkey) > 1 then it's safe to also assume cardinality(c.confkey) > 1. So only count once to decide whether to add parentheses.
Full automation
To make this work for any input table dynamically, create a polymorphic function taking a row value of the table:
CREATE OR REPLACE FUNCTION f_orphans(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE (  -- exactly the query from above
SELECT format(E'SELECT * FROM %s t\nWHERE  NOT EXISTS (SELECT FROM %s'
            , c.confrelid::regclass
            , string_agg(format('%s WHERE %s = %s)', c.conrelid::regclass, src.cols, tgt.cols)
                       , E'\nAND    NOT EXISTS (SELECT FROM '))
FROM   pg_constraint c
     , cardinality(c.conkey) AS col_ct
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg(quote_ident(attname), ', ' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.conkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.conrelid, fld.attnum)
   ) src
     , LATERAL (
   SELECT concat(CASE WHEN col_ct > 1 THEN '(' END
               , string_agg('t.' || quote_ident(attname), ', t.' ORDER BY fld.ord)
               , CASE WHEN col_ct > 1 THEN ')' END) AS cols
   FROM   unnest(c.confkey) WITH ORDINALITY fld(attnum, ord)
   JOIN   pg_catalog.pg_attribute a ON (a.attrelid, a.attnum) = (c.confrelid, fld.attnum)
   ) tgt
WHERE  c.confrelid = pg_typeof(_tbl)::text::regclass  -- input goes here!
AND    c.contype = 'f'
GROUP  BY c.confrelid
);

END
$func$  LANGUAGE plpgsql;

Call (important)!
SELECT * FROM f_orphans(NULL::my_table);

Or:
SELECT * FROM f_orphans(NULL::myschema.my_table);

Related:

PostgreSQL: Pass table as argument in function
Retrieving all PK and FK
SQL order by query results in any arbitrary (but reproducible) manner
How to preserve the original order of elements in an unnested array?


create schema with optional relations I have 4 tables that has the same columns. Tier1, Tier2, Tier3, Tier4. Their columns are id, code, and name.
The complete relationship is like this: Tier1->Tier2->Tier3->Tier4 Where Tier1 has many Tier2 and so on. But on some cases there are some tiers that get skipped. Like Tier1->Tier3->Tier4 so there is no Tier2, instead from Tier1 it got directly at Tier3.
My question is, how can I make a schema for this condition? I don't know how to start searching because I don't know the keyword for this problem.

Rule of thumb: fewer tables and more columns.
In your case you should probably have one tier table, and make the level another column. Then have a constraint that level can only be between 1 and 4.
If the tiers are always in order and have those values you don’t need any more data — the absence of a record with level 3 would imply tiers 1 -> 2 -> 4.
You might need two columns for keys: one ID for each individual record and a second column which has the same value across each group of tiers that belong together. It’s that column that you would refer to in a foreign key from (an)other table(s).
But without knowing more about your exact use-case I can only keep this advice generic.


Wrong error in SSIS - [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: “ResultSetType_Rowset” I have an Execute SQL task which gives returns only one row with one column - a number. I set my result set to single row. Despite that, the task fails. Why ? How do I fix it ?

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the
ResultSetType: "ResultSetType_Rowset".

In the Execute SQL Task, when the Result Set property is set to any value except None, the result set(s) must be mapped on the Result Set page, as shown below.

The "Result Name" column is a bit misleading; what's actually required is a zero-based number.

Unfortunately, none of these requirements are validated at design-time, so the editor will happily let you click OK without entering all the necessary information.


When is a trigger fired? My portal will be picking data every 2 hours from a file to a SQL Server table, lets say TableSource. As data is received in TableSource, it should also be copied into TableDestination, so we wrote a trigger for it.

My question is: what is the interval or rate of triggers getting fired in SQL Server? Will it be fired for each row (like MySQL), or after 500 inserts or after completion of all the inserts?

Data will be inserted in bulk, not manual entries.. maybe like 40k - 50k per hour. Can I run my trigger only once, after all my 40K are completely inserted into TableSource?

On a FOR/AFTER type trigger, it gets fired immediately after each insert, update or delete statement, prior to commit.

An INSTEAD OF trigger works differently and actually provides alternate logic for your insert, update or delete.  It fires in place of your insert, update or delete statement.  In this case it doesn't sound like the right approach.

Use the inserted or deleted logical tables to capture the results of the insert or delete. Since you are doing bulk inserts, make sure to write the trigger to handle multiple rows at a time.

I'm not a big fan of the approach but I've seen a lot of developers using cursors in triggers. That tends to have negative performance impacts so I usually stick to normal insert statements.

One additional point, though, which I should clarify: if you are actually using the T-SQL BULK INSERT statement, it's possible that triggers will be skipped. There is an override, FIRE_TRIGGERS that you will want to make sure to use. From that documentation link:

  FIRE_TRIGGERS
  Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

  If FIRE_TRIGGERS is not specified, no insert triggers execute.


Reclaim disk space from dropped column without downtime I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used.

The docs suggest doing a table rewriting ALTER TABLE to force the space back, but that is not safe to run while the table is used and would cause downtime. Are there any practical options that don't require any downtime? I tried to run the pgcompact tool, but that did not change anything.

ALTER TABLE .. DROP COLUMN ... marks the column as deleted in the system table pg_attribute. The table itself is not otherwise manipulated until rows are rewritten some way or another. The drop itself is very fast, but it does take a brief ACCESS EXCLUSIVE lock. I would not call that "downtime", though.
Actually reclaiming disk space is the tricky part. You could use
the community tool pg_repack instead of VACUUM FULL or CLUSTER (which lock the table exclusively). It avoids exclusive locks, but it needs free space on disk to operate. More details:

VACUUM returning disk space to operating system

Or, if rows in your table are updated eventually, you can just wait it out. Every new row version is written with the dropped column set to NULL. When the old row version is overwritten or removed by VACUUM you basically regain the space. Some corner case exceptions apply, though. See:

Dropping column in Postgres on a large dataset


Indexing a date column in MySQL doesn't work if a date function is used I have a table with the following schema
table_name

id int
task_id int
completed_date datetime (INDEX IX_TBL_NAME_COMPLETED_DATE)

I run EXPLAIN on this query
EXPLAIN
    SELECT
        *
    FROM table_name TBL
    WHERE
        TBL.completed_date BETWEEN date1 AND date2

This query runs and fetches records using the index on completed_date
However,
While running the same query with a date function
EXPLAIN
    SELECT
        *
    FROM table_name TBL
    WHERE
        CONVERT_TZ(TBL.completed_date, timezone1, timezone2) BETWEEN date1 AND date2

The index isn't made use of resulting in a slow query.
Can someone explain the reason behind this behavior and solution to optimize queries like this?

This is a common issue with functional transformations of any type not only dates. To avoid this issue just move the transformation to the constant side of comparison. Instead of
WHERE CONVERT_TZ(TBL.completed_date, timezone1, timezone2)
      BETWEEN date1
          AND date2

use the next syntax: (notice the reverse transformation of the TZs)
WHERE TBL.completed_date
      BETWEEN CONVERT_TZ( date1, timezone2 , timezone1 )
          AND CONVERT_TZ( date2, timezone2 , timezone1 )

The reason of that behaviour is that plain index contains the plain values ordered by itself. Functional indexes implemented in mysql-8.0.13+ can store the values ordered by some function. And only that function can be indexed on lookups. Sometimes it's impossible to move the fuctional transformation to the constant side, so the functional index is the only option.


How to optimize a log process in MySQL? In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns like user_id and some tag_id's.

The problem I have, is that these tasks in some cases can go up to 1.000.000 per year per user. So if I would store all these activities in one table. that would obviously become very big (=slow).

What is best to do here? Create a single table per user (so I have 100.000 log tables) or put all these activities in one table? And what kind of engine should I use?

One important thing to note: Although i simplified the situation a bit the following doesn't look normal, but users can also change values in these tables (like tag_id's).

Some simple arithmetic:

100k users * 1m events per user per year * 100 bytes per event =~ 10 TB per year

In MySQL, single table is limited in size at about 4TB. I would think it should be bigger than that, but this is what official doc says.

In other words, you would be effectively forced to split or partition your huge table into set of smaller ones.

Alternatively, you can use NoSQL databases like Hadoop or MongoDB.
But, it can be painful if you are used to do everything in SQL.