Correct way of configuring remote users to connect to Microsoft SQL Server 2012 I have 2 Servers, a web server and a database server.
Web server runs a number of .net sites that need to connect to the database server.

Currently, I have a configuration string for each application, that specifies a database instance, user name and password, and then have to add in these users onto the Database, and give them the necessary permissions.

Locally however, I can use the integrated Ap Pool Identity. This means that when the code is deployed config strings need to be changed, and a number of db users need to be managed, along with relevent passwords.

To me this doesn't seem the best way. I have read about Managed Service Accounts, but the last thing I read implied that they are not officially supported yet (this was in 2014).

So which is the 'correct' way of connecting my app pool users from one server to the database on another?

Managed service accounts are supported in SQL server 2012 onwards to run it's services.

There is no 'correct' way but managed service accounts on the application server are normal domain users as the SQL Server is concerned so as long as you can use them for the application they are a nice option. You might have to type the name in (domain\appuser$) as older versions of SSMS don't know the object type but you can add them manually

To minimize the configuration changes you can do the following:

Use client aliases on your application servers in the connection strings, for each application create an alias for the database (use cliconfg.exe) so that AppA connects to the the database server AppAdb. The alias on test will then point to the testdb server, prod to prod. Then you dont need to change the hostname in the connection string and you dont need to worry about the production application server suddenly starting to talk to the test database.
Use contained databases to minimize the need to create users on the database servers, you can then migrate databases between servers without having to remembering to add the users.
Use integrated App Pool Identity's and windows authentication using domain users, managed service accounts when possible, then you are not storing password in .config files, gMSA will allow you to have the same user on different servers.
If you have to use SQL Server authentication encrypt the passwords.


Report Server Reports Hanging I'm working on a issue with heavily fragmented indexes on a large production DB. I've pretty much identified the indexes that are heavily fragmented, including those that are not really being used. I plan to rebuild some and remove others. So my next step is to devise a before and after time test.

One of the symptoms of this is SSRS reports taking about an hour to render. I'm new to Report Server. I can see that a report is being embedded in the ASPX page using a ReportViewer control with the ServerPort ReportPath and ReportServerUrl properties set. My problem is trying to figure out how to time the display of the report from start to finish in the code-behind. I can write the start time to a file in the Page_Load but I can't figure out how to record the end time... Pre-Render could just hang and I'm not sure if this is the only page lifecycle event I can tap into to record this. Should I use a Windows Service, and if so, how would I trigger/record the start and end times that way?

I'd really appreciate some feedback on whether this is possible via the display page's code-behind.

To see how long a report takes to load, and what stage is taking up the time use this in SSMS:

USE ReportServer
SELECT * FROM ExecutionLog3 ORDER BY TimeStart DESC

This will show you a log of the activity for each report being used.
you will be able to see start time, end time (and calculate time taken), which parameters are being used, and the time taken for each of the stages of report loading (TimeDataRetrieval, TimeProcessing, TimeRendering) in milliseconds. Further details relating to the query here

Use this link to troubleshoot each stage, as per your findings


What are these SFP files that SQL Server distributes? What are the SFP files that come with SQL Server 2017?

/opt/mssql/lib/sqlservr.sfp
/opt/mssql/lib/sqldkxplat.sfp
/opt/mssql/lib/secforwarderxplat.sfp
/opt/mssql/lib/system.common.sfp
/opt/mssql/lib/system.certificates.sfp
/opt/mssql/lib/system.netfx.sfp
/opt/mssql/lib/system.sfp

So it turns out these are packed archive. It's part of the "Platform Abstraction Layer" that hosts the operating system in a library, or "Drawbridge" Library OS. Not much is known about Drawbridge yet, but here is one resource on Github that pointed me over to a tool sfpack.

sfpack was able to extract these archives.


How to get database name in a server-level DDL trigger I'm creating a Server level trigger that fires after a create table statement, and I want to print the database name and table name.

CREATE TRIGGER LogTempTables
ON ALL SERVER
AFTER CREATE_TABLE
AS
BEGIN
SET NOCOUNT ON;

       DECLARE @EventData XML = EVENTDATA()
       DECLARE @TableName NVARCHAR(50)
               = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(50)')
       DECLARE @Database NVARCHAR(50) = DB_NAME()

       PRINT @Database
       PRINT @TableName
END
GO

If I create a table in database ABC123 it prints:

  master
  TestTable

DB_NAME() will always return the context of the trigger, not the context of the event. But you're in luck: the EVENTDATA() contains the object name and the database name. Try adding this to your trigger (also you should be very careful about using 50 characters, because both tables and databases can have longer names).

DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
PRINT @db;


Failover and Failback SQL Server Log Shipping I had a question on Log shipping Manual Fail over

Let me explain about environment, Primary Server A (with Cluster setup A-P Mode) Secondary server B(DR server standalone).

We had enabled Log Shipping on a database TEST from Primary Server and now we would like to test by doing a manual fail over and i had few questions.

Log Shipping Jobs are disabled on both A and B Servers and Log shipping is not disabled or removed, Once the Server B is brought online by restoring the all the logs from Server A

Can i re-attach/reconfigure the secondary database to existing Log shipping once it is online.
I tried using backup with no recovery option on secondary database and log shipping restore jobs are skipping.
Is there any method expect role changing can we reconfigure the log-shipping without restoring the database from first?

any references would be appreciated.

Thank You

If i understand correctly, you want to reinitialize log shipping from B to A?

If yes, then once you bring the DR site database (B) online you automatically break the LSN chain hence the restore log files will be skipped. The only way to reestablish log shipping is to remove log shipping setup completely, create backup of database B, restore at database A in stand by/readonly and then setup log shipping from B.


Galera and in-memory I've got a question about Galera cluster + MySQL/MariaDB and RAM.

Basically, our MySQL cluster needs to run in-memory.

But:

We have 3 TB of data;
We don't have 100,000$ for RAM :)
We need High Availability.

In this case, is Galera a good solution? Will it run with 3 nodes (each with 128GB RAM and SSD's)?

A Galera node's memory usage is very similar to a normal MySQL/MariaDB standalone server. If 128GB is enough for a standalone server to handle your workload, it will be ok for a Galera node too.

Of course you can also have server with different characteristics. In which case, you will also need a load balancer that will take into account the servers "weight". You can use MaxScale, for example.


dbca cant remove database cleanly when I ran dbca as administrator under windows server 2008R2 is used to delete the database completely ( database folder, control files, datafies, redo logs, then remove all related windows services to the database), but recently I noticed when I use it I found the services still exist as well as the trace (still generated), mail folder inside oradata still exist, as well as I can try to remove it countless time.

DB trace:

2015-02-16 15:31:48.434: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2015-02-16 15:31:48.439: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2015-02-16 15:31:48.439: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2015-02-16 15:31:48.440: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2

*** 2015-02-16 15:31:50.494

2015-02-16 15:31:50.494: [  OCRMSG]prom_waitconnect: CONN NOT ESTABLISHED (0,29,1,2)
2015-02-16 15:31:50.494: [  OCRMSG]GIPC error [29] msg [gipcretConnectionRefused]
2015-02-16 15:31:50.494: [  OCRMSG]prom_connect: error while waiting for connection complete [24]
2015-02-16 15:31:50.494: [ default]ut_read_reg:2:ocr registry key SOFTWARE\Oracle\olr cannot be opened. error 2

Any suggestion to fix this issue?

When you install Oracle there are a set of registry entries under hkey_local_machine, software, oracle. If you don't have the registry, and don't want the Oracle home, then you can just delete the directories. There is an Oracle directory under program files, which stores your Oracle Inventory, and your Oracle Home where ever you put that.


How to Traverse All Foreign Keys to Generate All Possible Paths I'm attempting to traverse all of the foreign keys in a SQL Server database to generate all of the possible paths between tables.  The query below seems to mostly work for what I am needing except that it seems to run indefinitely.  My goal is to be able to find the most efficient foreign key path between any two tables in the database by using the path with the least number of "hops" to my destination table.  Is there a way to limit the query below so that it doesn't go beyond 20 hops?

WITH cte
AS
(
    SELECT
        fk.create_date
        , fk.modify_date
        , fkc.constraint_object_id AS ConstraintId
        , OBJECT_NAME(fkc.constraint_object_id) AS ConstraintName
        --, fkc.referenced_object_id AS PrimaryKeyTableId
        , OBJECT_NAME(fkc.referenced_object_id) AS PrimaryKeyTableName
        --, fkc.referenced_column_id AS PrimaryKeyColumnId
        , rc.name AS PrimaryKeyColumnName
        --, fk.parent_object_id AS ForeignKeyTableId
        , OBJECT_NAME(fk.parent_object_id) AS ForeignKeyTableName
        --, fkc.parent_column_id AS ForeignKeyColumnId
        , lc.name AS ForeignKeyColumnName
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns rc
        ON  rc.OBJECT_ID = fkc.referenced_object_id
        AND fkc.referenced_column_id = rc.column_id
    INNER JOIN sys.foreign_keys fk
        ON  fk.OBJECT_ID = fkc.constraint_object_id
    INNER JOIN sys.columns lc
        ON  lc.OBJECT_ID = fk.parent_object_id
        AND fkc.parent_column_id = lc.column_id
)
, cte2(create_date, modify_date, ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path ) AS
    (
        SELECT
            create_date, modify_date, ConstraintName
            , PrimaryKeyTableName, PrimaryKeyColumnName
            , ForeignKeyTableName, ForeignKeyColumnName
            , 1 , CAST(QUOTENAME(PrimaryKeyTableName + '.' + PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte
    UNION ALL
        SELECT
            cte.create_date, cte.modify_date, cte.ConstraintName
            , cte.PrimaryKeyTableName, cte.PrimaryKeyColumnName
            , cte.ForeignKeyTableName, cte.ForeignKeyColumnName
            , cte2.Hops +1, CAST(cte2.path + '-> ' +QUOTENAME(cte.PrimaryKeyTableName+ '.' + cte.PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte2 INNER JOIN cte ON cte2.ForeignKeyTableName = cte.PrimaryKeyTableName
        AND cte2.ForeignKeyColumnName != cte.PrimaryKeyColumnName
    )
SELECT
ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path + '-> ' + QUOTENAME(ForeignKeyTableName + '.' + ForeignKeyColumnName) AS Path
FROM cte2

I would try one or both of the following:

You've already added a Hops counter column in the anchor. In the recursive part of the CTE, you can filter WHERE cte2.Hops<20
In the first cte common table expression, I would add WHERE fk.parent_object_id!=fk.referenced_object_id to exclude infinite self-referencing foreign keys (like parent-child hierarchies).


Managing AUD$ without DBMS_AUDIT_MGMT I have a SYSTEM's table AUD$ where AUDIT records are stored. My AUDIT_TRAIL is set to DB,EXTENDED. I am on Oracle DB version 12c (12.2.0.1.0).

With time (in 3 years) my AUD$ table grew to 500GB in size, especially because of it's 2 CLOB columns (SQLBIND and SQLTEXT).

I want to shrink  storage consumed by AUD$ to minimum, without losing any audit data.

I came up with the following plan:

CREATE TABLESPACE TS_AUDIT_HISTORY
CREATE USER AUDIT_USER in tablespace TS_AUDIT_HISTORY
CREATE  partitioned TABLE AUDIT_USER.AUDIT_HISTORY_TABLE in tablespace TS_AUDIT_HISTORY with:

3.1. COMPRESS HIGH on LOB's  (SQLBIND and SQLTEXT)
3.2. COMPRESS HIGH on all partitions

INSERT INTO AUDIT_USER.AUDIT_HISTORY_TABLE select * from SYS.AUD$; COMMIT;
TRUNCATE TABLE SYS.AUD$

I haven't implemented my plan on any Production environment yet, since almost everyone advices against any other solution but DBMS_AUDIT_MGMT - which I don't want to use because it doesn't compress table's data / CLOB's).

But I have implemented my plan on a Development environment and results were amazing - After compressing I got from  500GB -> 50GB which is like 90% compress ratio !

My questions:

A) How 'smart' is to truncate SYSTEM's table AUD$, can it have any other impact besides the fact that user's won't be able to log in?

B) When I will be TRUNCATEing AUD$, table will be locked, which will prevent users to log in! - any way to 'bypass' that, since users being able to log in to DB is very important?

C) What do you think of my plan?

Any input / concern / comment is welcome.

STATUS UPDATE:

I successfully created a new tablespace, new user (with default new tablespace), new table with partitions and compression on it as well as on the LOB's and inserted all the data from SYS.AUD$ to the new table -I had to use parallel hint here and it still took me 6 hours. Afterwards I truncated AUD$. Original AUD$ was 500GB in size and now the new one (with all the records of course) is only 40GB in size. You were right guys, Truncate SYS.AUD$ finished in like 1 or 2 seconds.

So now I saved like 460GB.... but not really. Coz now these 460GB can be used only in AUD$'s tablespace (SYSAUX) and not in other tablespaces :/   I really hoped that TRUNCATE TABLE AUD$ DROP STORAGE; actually releases the allocated storage, but it does not. I can see that SYSAUX is 460GB less in size now, but these 460GB didn't free in ASM storage.

The only 100% way to reallocate the storage to be available as Usable storage in ASM as far as i know is:

Drop a table (which is of course not an option for AUD$)
export / import table (which is not an option since Oracle does not allow that for AUD$ table, already tried to export it with expdp but it didn't work).

So how do I actually make the 'freed' / deallocated storage available to ASM / other tablespaces?

A), B) - You performed the same in the test environment, so you have an estimation of how long it takes. If it is short enough to do it during operation, go for it. If not, you can do it during a scheduled downtime. Typically users do not log in every second or so, sessions get created then they are reused by applications or connection pools. If you have frequent logins to the database, that is flawed design. Also, what is more important? The audit or the users being able to log in. You can temporary disable auditing.

C) - COMPRESS HIGH requires the Advanced Compression option to be licensed. If you have the necessary license, go for it. If not, then you will have some explaining to do.

Generally about audit: instead of moving the audit inside the database, you should be working on removing it from the database. Storing and handling audit data inside the source database is a pain in the ass. The amount of audit data stored inside the database should be kept as low as possible. You have 500 GB of unneeded data that is included in your backups, copied to clone databases. You should regularly archive then delete audit data from the database. In my opinion, instead of storing any audit data in the database, sending the audit data to a remote server through syslog is a much better solution.


Is it possible to do sums of aggregation that stops aggregating once the condition is reached? Say I have a table amounts with millions of rows tallying up to some number, but I'm really just checking if it's > 100. Is there a way to stop aggregation since taking this:

SELECT SUM(amount)
FROM stored_value;

Into this:

SELECT SUM(amount)
FROM stored_value
HAVING SUM(amount) > 100;

However this does not change anything on the amount of aggregation. Something that would work with date ranges.

If you don't care what order your rows that contribute to the sum is in:

select * from (
    select ...n sum(amount) over () as sm
    from ...
    where ...
) as x
where sm > 100

If you do care about the order, you should add info on how the cumulative sum is calculated:

select * from (
    select ...n sum(amount) over (order by ...) as sm
    from ...
    where ...
) as x
where sm > 100

You may even sumarise per something like:

select * from (
    select ...n sum(amount) over (partition by ...
                                  order by ...) as sm
    from ...
    where ...
) as x
where sm > 100

However, since you are talking about stopping, when the sum reaches something I suggest what you need is something like:

select * from (
    select ...n sum(amount) over (partition by ...
                                  order by ...) as sm
    from ...
    where ...
) as x
where sm <= 100


Load flat file into a predetermined table Scenario

Table created

Data in flat file loaded to the table but with errors. please advise how to fix it

(a) 'address' is split to 3 columns causing mismatch between the source and destination

(b) 3 Nulls in the 'B_Date' column due to different format. Do I need to massage the raw file before load into the Microsoft SQL or what is the SQL formula?

raw file

Why do you think address is being split into three columns? Based on your screenshot column 5 is being mapped to address and 6 and 7 aren’t being mapped to anything.

Can you please share the full error messages? Also does your file have headers, I presume not based on your screenshot. Try adding headers to the flat file and letting the wizard create the destination table for you.


DACPAC Deployment Rebuilding All Indexes I'm in process of deploying a DACPAC provided by our ISV.  The DACPAC is rebuilding every index in the database.  We're testing in a Lab now, however, this will create a significant impact to our maintenance window when we move into production.  Is anybody aware how I can skip this index rebuild process in the DACPAC deployment?

The most likely reason this is happening is because there are different options / settings on the indexes in the model (the dacpac file) and the target (your actual database).  The deployment, by default, will rebuild the index with the new options to get the target to match the model.

There are two deployment options you can enable to avoid this.  I would try them one at a time to see which one is causing the rebuild:

/p: IgnoreIndexOptions=True
/p: IgnoreLockHintsOnIndexes=True

There are two more options that ignore index-related things (IgnoreFillFactor and IgnoreIndexPadding).  These are enabled by default, but you might want to check and make sure your publish process isn't explicitly setting them to False.


What are the potential issues after data or log disk failure? Scenario:

A client recently had an issue with an Azure VM where the (non-OS) 'premium' storage disks were erroneous removed without warning by Microsoft. This is the virtual equivalent to the hard drives being yanked out of a running server.

They have SQL Server 2014 running on this VM with the data and log files placed separately on the two 'failed' drives.

The live, production, non-clustered databases are being used fairly heavily via various services and applications - with the high probability of commands and transactions having bin in-progress or mid-commit when the issue occurred.

After the issue, the VM was forcibly powered-off, and only became 'operational' after a couple of reboots.

Question:

While seemingly no issues have thus far been identified, my question is what are the possible SQL failure modes this could present? Does the transaction log / SQL server architecture account for this type of whole disk failure? Is it possible to have data loss in this situation (without considering corruption of the disk itself). And what checks should/can be performed after such an event?

what are the possible SQL failure modes this could present?  Does the transaction log / SQL server architecture account for this type of whole disk failure?

SQL Server uses a write-ahead log (WAL), so any completed transactions should have at least been flushed to disk (unless you are using delayed durability).  This means that when your database comes back online, all committed transactions will be replayed from the log file.  In-progress transactions will be rolled back as well.

  Is it possible to have data loss in this situation (without considering corruption of the disk itself)

Despite the WAL architecture, I'm sure Windows didn't appreciate the abrupt disk loss.  It certainly seems possible that data could have been corrupted - for instance, if a single 8 KB page was halfway done being flushed to the physical log file, and the disk was removed at that instant, it seems like the log would be corrupted.

If you're using the Full recovery model, you could likely still do a point in time recovery up to right before the corruption.  But there would be some data loss due to the partial write.

  And what checks should/can be performed after such an event?

There's really not much to be done other than running DBCC CHECKDB.  This will check the database to see if it was corrupted during this unusual event.

Another option would be to restore a backup (from before this event) to a separate database / server, and then use a comparison tool (like SQL Data Compare from Red Gate) to see if there is any data that's different between the backup and the current database.


How do I generate a tnsnames.ora file to connect to an Oracle Database? I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find.

Do I need to generate the tnsnames.ora file myself? If so, where do I place it? If not, how does Oracle generate it for me?  If I do need to generate it, what is the appropriate syntax for the file?

You can easily create a tnsnames.ora [text] file.  It should be in $ORACLE_HOME/network/admin/ and should look something like this:

 ORATST=
 (description=
   (address_list=
     (address = (protocol = TCP)(host = fu.bar)(port = 1521))
   )
 (connect_data =
   (service_name=oratst)
 )
)


DDL_admin vs db_owner permissions I'm taking over a project that involves removing and limiting permissions of all database users across our server farm. (fun times)

One of the permissions currently being limited is db_owner permissions.
This permission is being reviewed on a case-by-case basis, but a common change is to replace the db_owner permissions with the following:

db_datareader
db_datawriter
db_ddladmin
db_executor

I would like to define the exact difference between the two (to inform clients).
However, as far as I can tell, the difference between the two should be:

db_accessadmin permissions
db_backupoperator permissions
db_securityadmin permissions

So in effect they would lose:
[ALTER ANY USER]
[CREATE SCHEMA]
[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]
[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]
[DROP DATABASE]

Is there anything else that a user would loose once db_owner is replaced by the four roles above?
Does this actually serve much of a purpose security wise?

db_ddladmin vs db_owner

From what I can tell from what I tested and read up on, for the most part your list looks accurate except db_ddladmin DOES allow you to CREATE SCHEMA. I did confirm that the other security permissions you listed were indeed denied.

Denied with DDLADMIN only:

[ALTER ANY USER]

[BACKUP DATABASE], [BACKUP LOG], [CHECKPOINT]

[ALTER ANY APPLICATION ROLE], [ALTER ANY ROLE]

[DROP DATABASE]

Noting that the. . .

db_datareader will allow SELECT access to all tables
db_datarwriter will allow INSERT, UPDATE, and DELETE access to all tables
db_executor will allow EXECUTE access to all executable objects

Additonally, having db_ddladmin role permissions may mean. . .

  Note: Since you have so many different versions of SQL Server from 2005 - 2014, it may be best to have a small set of users test
  this initially to see who screams to iron out any kinks, etc.

Objects they own with this role will not be owned by DBO so you may have to deal with ownership chaning issues if there's ever a problem with something at this level. I'm not 100% certain that this would be a problem but it's worth mentioning just in case.

Source: Ownership Chains
With this role (may vary depending on the version of SQL Server) they may be able to add SQL security principles defined in the current DB to objects they own still, just not all objects (ones they do not own) nor add a new server-level defined principal to the DB level.

Additionally, not having DBO role permissions may mean. . .

  Note: Since you have so many different versions of SQL Server from 2005 - 2014, it may be best to have a small set of users test
  this initially to see who screams to iron out any kinks, etc.

Not having the DBO role may prevent certain SSMS designer GUI interfaces (SQL Server version varying) from populating or opening without error (e.g. when modifying tables or columns through the GUI) even though doing it via T-SQL works and the permissions are in place. In some versions of SQL Server this may be resolved by allowing GRANT VIEW DEFINITION where this is an issue and it can also just be a warning only on certain versions of SQL Server.

Resources

You are not logged in as the database owner or as a user that is a member of the db_owner role. You will not be able to save changes to tables that you do not own.
db_ddladmin Role doesn't allow use of "design" functions in SSMS

  "We try to prevent giving users/developers dbo in their QA databases
  as much as we can. One of the problems with this is that they still
  need to be able to create and modify database objects such as user
  tables. Many devs are new to MS SQL and thus tend to stick with the
  GUI (SSMS) for this sort of work. The problem arises when we grant
  them db_ddladmin (not dbo) and they are no longer able to modify
  tables or columns via the table designer GUI. Instead, they have to
  take additional time to learn the TSQL commands and their syntax (that
  they may never need again) or engage the DBA team which takes time
  away from our other activities.

  I don't know if this is a bug or a feature request but I consider it a
  bug since the user has sufficient permissions to alter the table via
  TSQL but the GUI gives them messages stating:

  "You are not logged on as the database owner or system
  administrator. You might not be able to save changes to tables that
  you do not own." AND "Table [schema].[table] is set to read only,
  user doesn't have enough rights on this table."

  A trace seems to point to the check being a is_member('db_owner')
  which will preclude members of db_ddladmin even though they do in fact
  have permissions to modify the object. Microsoft SQL Server Management
  Studio"

  Posted by Agent DBA on 1/25/2010 at 7:06 AM

  I had a similar issue and managed to solve it by performing the
  following grant

GRANT view definition on schema:: <schemaname> to <username>

Other Considerations

Since you state that this is being reviewed on a case-by-case basis

  One of the permissions currently being limited is db_owner permissions.

  This permission is being reviewed on a case-by-case basis, but a common change is to replace the db_owner permissions with the following:

  db_datareader
  db_datawriter
  db_ddladmin
  db_executor

Have you considered creating additional custom roles for more "all object" DB-level access that each person needs rather than granting them the db_ddladmin role as that will probably give them more than they actually need to DB level objects as well.

I usually give what's needed exactly and nothing more for them to do their job and if there's a "usual" or "standard" need for DB level object access to all objects in a DB, I create a custom DB role sort of like the the db_executor but see my below example. This way you can grant the people what they really need to ALL DB object in a particular DB if you're not getting object level explicit in your DBs for their security.

----Custom Database Roles

/* CREATE A NEW ROLE  -- Execute to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Execute
GRANT EXECUTE TO db_All_StoredProc_Execute

/* CREATE A NEW ROLE  -- Alter to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_Alter
GRANT ALTER ANY SCHEMA TO db_All_StoredProc_Alter

/* CREATE A NEW ROLE  -- View Definition to all stored procs including newly created ones*/
-- Database specific
CREATE ROLE db_All_StoredProc_View
GRANT VIEW DEFINITION TO db_All_StoredProc_View

/* CREATE A NEW ROLE - Any schema alter and create procedure permissions */
-- Database specific
CREATE ROLE db_All_CreateProc_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateProc_AlterSchema
GRANT CREATE PROCEDURE TO db_All_CreateProc_AlterSchema
GO

/* CREATE A NEW ROLE - Any schema alter and create table permissions */
-- Database specific
CREATE ROLE db_All_CreateTable_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateTable_AlterSchema
GRANT CREATE TABLE TO db_All_CreateTable_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create function permissions */
-- Database specific
CREATE ROLE db_All_CreateFunction_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateFunction_AlterSchema
GRANT CREATE FUNCTION TO db_All_CreateFunction_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create aggregate permissions */
-- Database specific
CREATE ROLE db_All_CreateAggregate_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateAggregate_AlterSchema
GRANT CREATE AGGREGATE TO db_All_CreateAggregate_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create view permissions */
-- Database specific
CREATE ROLE db_All_CreateView_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateView_AlterSchema
GRANT CREATE VIEW TO db_All_CreateView_AlterSchema

/* CREATE A NEW ROLE - Any schema alter and create schema permissions */
-- Database specific
CREATE ROLE db_All_CreateSchema_AlterSchema
GRANT ALTER ANY SCHEMA TO db_All_CreateSchema_AlterSchema
GRANT CREATE SCHEMA TO db_All_CreateSchema_AlterSchema

I also wanted to share a db_DDLAdmin_Restriction role you may want to consider to consider creating otherwise with explicit DENY to restrict what db_ddladmin give access to so you could at least create this on the DBs where you grant them this role and set the explicit DENY for the actual object types, etc. you don't want them to have access to.

For example, if you know they will definitely be creating stored procedures and functions, you can exclude DENY CREATE FUNCTION, DENY CREATE PROCEDURE, DENY ALTER ANY SCHEMA.

---Create ddladmin restriction custom DB role
DENY ALTER ANY ASSEMBLY                    TO db_DDLAdmin_Restriction
DENY ALTER ANY ASYMMETRIC KEY              TO db_DDLAdmin_Restriction
DENY ALTER ANY CERTIFICATE                 TO db_DDLAdmin_Restriction
DENY ALTER ANY CONTRACT                    TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE DDL TRIGGER        TO db_DDLAdmin_Restriction
DENY ALTER ANY DATABASE EVENT NOTIFICATION TO db_DDLAdmin_Restriction
DENY ALTER ANY DATASPACE                   TO db_DDLAdmin_Restriction
DENY ALTER ANY FULLTEXT CATALOG            TO db_DDLAdmin_Restriction
DENY ALTER ANY MESSAGE TYPE                TO db_DDLAdmin_Restriction
DENY ALTER ANY REMOTE SERVICE BINDING      TO db_DDLAdmin_Restriction
DENY ALTER ANY ROUTE                       TO db_DDLAdmin_Restriction
DENY ALTER ANY SCHEMA                      TO db_DDLAdmin_Restriction
DENY ALTER ANY SERVICE                     TO db_DDLAdmin_Restriction
DENY ALTER ANY SYMMETRIC KEY               TO db_DDLAdmin_Restriction
DENY CHECKPOINT                            TO db_DDLAdmin_Restriction
DENY CREATE AGGREGATE                      TO db_DDLAdmin_Restriction
DENY CREATE DEFAULT                        TO db_DDLAdmin_Restriction
DENY CREATE FUNCTION                       TO db_DDLAdmin_Restriction
DENY CREATE PROCEDURE                      TO db_DDLAdmin_Restriction
DENY CREATE QUEUE                          TO db_DDLAdmin_Restriction
DENY CREATE RULE                           TO db_DDLAdmin_Restriction
DENY CREATE SYNONYM                        TO db_DDLAdmin_Restriction
DENY CREATE TABLE                          TO db_DDLAdmin_Restriction
DENY CREATE TYPE                           TO db_DDLAdmin_Restriction
DENY CREATE VIEW                           TO db_DDLAdmin_Restriction
DENY CREATE XML SCHEMA COLLECTION          TO db_DDLAdmin_Restriction
DENY REFERENCES                            TO db_DDLAdmin_Restriction
GO


SQL Server Tuning a table for massive inserts I'm trying to solve a performance issue and I need advice.

I have a table, which about twice a day has a massive surge of inserts coming in, and exactly at the same time everybody is trying to read from it. So I have locks and everything becomes painfully slow.

My performance requirements are as follows:

many writers, from different sources, and i prefer each write to be atomic.
i don't care about the order of the writes, number 7 can come in before number 3.
writes should end ASAP, I want writers to wait the least possible amount of time.
data is never updated, only inserted.
I have many many readers.
readers want to see the most updated snapshot possible at the time of their read.
I can live with readers seeing delayed data (10-20 seconds)
I need occasionally do complex and large queries on this data, so i need several indexes on it.

How would you approach this, and how would you implement this with SQL Server?

I'm thinking of splitting it into 2 tables... one for the inserts, and one for reading, and a worker moving data.

the large queries will run only on the second, the readers that need updated data, can run on the second, and complete what they need from the first.

One solution I used was to thread out writes to multiple staging tables, then collect the data in batches. We put the staging tables on their own filegroups to try and segregate the I/O as much as possible, though we did end up with two tables on each available LUN, as opposed to each table getting its own dedicated I/O.

Since you said order of writes doesn't matter, this may be viable, but I don't know if 20 seconds will be sufficient to make this work and still not encounter a bunch of blocking.

So let's say we have a base table like this, which is where everyone reads from (and is currently writing to):

CREATE TABLE dbo.StockData
(
  StockID INT,
  ...other info...
);

Then we have staging tables like this, where we're going to write instead:

CREATE TABLE staging.StockData0
(
  StockID INT,
  ...other info...
);

CREATE TABLE staging.StockData1
(
  StockID INT,
  ...other info...
);

... up to StockData9

*Don't bother mimicking all of the non-clustered indexes, constraints etc. on these staging tables,

Then assuming you have singleton inserts via a stored procedure, change the stored procedure to use dynamic SQL and insert into one of the 10 tables based on the StockID value:

DECLARE @sql NVARCHAR(MAX) = N'INSERT staging.StockData'
  + CONVERT(CHAR(1), @StockID % 10 + '(StockID, cols...)
    SELECT @StockID, @params...;';

EXEC sp_executesql @sql, N'@StockID INT, @params...', @StockID, @params...;

Then have a background job that performs a very quick, metadata operation moving these tables into a dummy schema - this minimizes the amount of time that is spent blocking users' ability to write while you move this data into the real table (which you can now control much better than sporadic writes from all over the place). So first create a second copy of the tables in a different schema:

CREATE SCHEMA shadow  AUTHORIZATION dbo;
CREATE SCHEMA holding AUTHORIZATION dbo;
GO

CREATE TABLE shadow.StockData0
(
  StockID INT,
  ...other info...
);

-- repeat for 1-9

Now the job would do this:

TRUNCATE TABLE shadow.StockData0;

BEGIN TRANSACTION;

  ALTER SCHEMA holding TRANSFER staging.StockData0;
  ALTER SCHEMA staging TRANSFER shadow.StockData0;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER holding.StockData0;

-- repeat for 1-9 (I actually used dynamic SQL in a loop
-- so I wouldn't have to repeat myself ten times...

I blogged about this part of it here and here.

The job would then go on to insert batches into the primary table, with a delay in between, to allow some reads to happen.

BEGIN TRANSACTION;

  INSERT dbo.StockData(StockID, ...cols...)
    SELECT StockID, ...cols...
    FROM holding.StockData0;

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:01';

-- repeat for 1-9

This was several years ago, and something that didn't occur to me at the time (which I remember now, too, after seeing the other link posted by wBob) is that using TABLOCK inside those transactions may help speed up the inserts (though you'll want to test based on volume, isolation level, etc).

It might also be useful (even with all this work) to enable Read Committed Snapshot Isolation and/or if you are on Enterprise Edition considering partitioning (and aligning the staging tables to those partitions).


Location of #th iteration of a character or a word in a sql text field How can I get the location for the #th iteration of a character or a word in a sql server text field?

There's no specific support for this in Transact-SQL.

You could write your own function using CHARINDEX.

Alternatively, you could use a library that someone else has already written, for example the SQLsharp SQLCLR extension library. Among the over 240 functions and procedures is SQL#.String_NthIndexOf:

-- Result: 13
SELECT SQL#.String_NthIndexOf
(
    N'abc abc abc abc abc',  -- Input string
    N'abc',                  -- Search for this
    1,                       -- Starting at position
    4,                       -- Nth occurrence
    1,                       -- Case sensitive
    0                        -- Don't count overlaps
);

I have no association with this (mostly free) product, I am just a fan.


SQL Server Diff not restoring from Azure BLOB storage I'm setting up a process to back up a SQL Server 2016 database to Azure blob storage and subsequently restore to another server. I'm doing a weekly full back up and hourly differential. The issue I'm having is that the differential will not restore over the full back up.

Back up to Azure:

BACKUP DATABASE [DB_NAME] TO
URL =
N'https://DB_BLOBNAME.blob.core.windows.net/livebackup/DB_NAME_FULL.bak'
WITH  CREDENTIAL = N'BackupCred' , FORMAT, INIT,
NAME = N'DB_NAME_FULL', SKIP, REWIND, NOUNLOAD,  STATS = 10,  COMPRESSION
GO

BACKUP DATABASE [DB_NAME] TO  URL =
N'https://DB_BLOBNAME.blob.core.windows.net/livebackup/DB_NAME_DIFF.bak'
WITH CREDENTIAL = N'BackupCred',  DIFFERENTIAL ,
NOFORMAT, NOINIT,  NAME = N'DB_NAME_DIFF', NOSKIP, NOREWIND, NOUNLOAD,
STATS = 10
GO

I'm using Azcopy to bring the two .bak files down to the local disk, no issues there. Then restoring from Local file system.

DROP DATABASE DB_NAME
GO
RESTORE DATABASE DB_NAME FROM DISK =
'C:\AzSyncFolders\DB_NAME\DB_NAME_FULL.BAK' WITH NORECOVERY
GO

RESTORE DATABASE DB_NAME FROM DISK =
'C:\AzSyncFolders\DB_NAME\DB_NAME_DIFF.BAK' WITH RECOVERY
GO

All pretty straightforward stuff, but I'm consistently getting this message.

    Msg 3136, Level 16, State 1, Line 8
    This differential backup cannot be restored because the database has not been >>restored to the correct earlier state.
    Msg 3013, Level 16, State 1, Line 8
    RESTORE DATABASE is terminating abnormally.

When I do a full and differential back up on the server locally using the same scripts they both restore correctly.

Am I missing anything obvious in my scripts either in the back up or restore?

Definitely no additional full back up between FULL and DIFF

Just using .BAK out of convention, not sure that having the .DIFF extension will make a difference.
Will check to see if there are multiples in the .BAK. No multiples in there.

Changed to use INIT, still getting the error.

**** Update  ****
Apparently there's a third party tool also taking backups to another location. This would seem to be causing my mismatch between the full and diff LSN. Looks like I'm going to have to take more full backups and abandon the use of diffs. Scott Hodgin called it on the first answer. Thanks for all the help.

Most likely, you have multiple Differential backups in your DB_NAME_DIFF.bak file, and the restore is picking one that occurred before the most recent FULL.  As I hinted in my comment, and Scott stated in his.

Edit the following to check for multiple files in a backup device:

RESTORE HEADERONLY
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'
WITH NOUNLOAD;
GO


Joining 4 tables of which one is unrelated I have 4 tables as follows.

I want to find a wholesum resultset, limited to 25 records, in the following way:

Users from Table1 based on a condition
And those Users should not be in Table2 [Non restricted users]
Join those records to Table3 on Table1.Group_id = Table3.Group_MEMBER_ID where Table3.GroupName = 'someValue'
Join Table1 with Table4 on Table1.UserName = Table4.UserName

Am currently using two queries and am using UNION. Something like this:

      ( SELECT  Table_4.DeviceName
            from  Table1
            left join  Table2 on Table1.UserName = Table2.UserName
            inner join  Table4 on Table1.UserName = Table4.UserName
            where  Table1.some_column = 'someValue'
              and  Table2.UserName = null
            limit  25
      )
UNION
      ( SELECT  Table4.DeviceName
            from  Table1
            inner join  on Table1.Group_ID = Table3.GROUP_MEMBER_ID
            inner join  Table4 Table1.UserName = Table4.UserName
            where  Table3.GroupName='someValue'
              and  Table1.some_column = 'someValue'
            limit  25
      );

The point is, I cant limit the wholesum resultset to 25 records. I should use LIMIT to each query. Is there a way to merge it into a single query so that I can limit the wholesum resultset to 25?

Note: PL/SQL and CTE not available in my Development System and hence am not able to use it.

Problem 1:  LIMIT is somewhat meaningless without ORDER BY to indicate which 25.

Problem 2:  Don't say x=null, it won't do what you think.  Instead do x IS NULL.

Problem 3:  Do you really want a "cross join"?  That is, a JOIN without an ON clause, and hence gets all combinations of the two tables?

Problem 4:  Well, I did not check the rest of what you have.

Back to your question...  The pattern is

( SELECT ... ORDER BY ... LIMIT 25 )
UNION ALL  -- or DISTINCT, depending on your understanding of the data
( SELECT ... ORDER BY ... LIMIT 25 )
ORDER BY ... -- Yes, again
LIMIT 25;

If you need an offset of, say 100, then:

( ... LIMIT 125 )
UNION ...
( ... LIMIT 125 )
ORDER BY ...
LIMIT 100, 25;


can't create schema for mysql database I am trying to create a schema for a mySQL database through my sites phpMyAdmin section. However when I try to run the following script:

CREATE SCHEMA `mydb`

I get this error and I am unsure what to do! I am very new to mySQL so any advice would be much appreciated. I am aware that it is some sort of permission problem?

ERROR 1044: Access denied for user 'user'@'%' to database 'mydb'

The next time you login to MySQL, run this query:

SELECT USER(),CURRENT_USER();

What does this give you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

What comes back for CURRENT_USER() contains all the rights you had the momemnt you logged in. Chances are, you do not have the necessary privileges.

Please look over the grants you have and correct them or connect with a user that has the necessary privileges.

To see the privileges assigned, please run

SHOW GRANTS FOR CURRENT_USER();

In your comments, you stated you have the following rights upon login (from the output of the CURRENT_USER() function):

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE ON waitronmain`.* TO ''waitronmain''@''%'';

These are all the rights you have. Since you are in a Managed DB setting, you cannot be grants rights to create a database. What is more, they are not going to make it easy for you to do so.. Although you have the CREATE privilege in the list, it does not apply to the database level, only the table level.

Here is proof: I have a database called zipcodes. I will create a user called rolando

mysql> grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Here is what the mysql schema says on the CREATE privilege:

mysql> select user,host,create_priv from mysql.user where user='rolando';
+---------+------+-------------+
| user    | host | create_priv |
+---------+------+-------------+
| rolando | %    | N           |
+---------+------+-------------+
1 row in set (0.00 sec)

mysql> select user,host,create_priv from mysql.db where user='rolando';
+---------+------+-------------+
| user    | host | create_priv |
+---------+------+-------------+
| rolando | %    | Y           |
+---------+------+-------------+
1 row in set (0.00 sec)

mysql>

So, as I stated, the user does not have a global CREATE privilege. The user has table CREATE privilege because mysql.db has Y for create_priv.


LOOP in a stored procedure to calculate sum I'm trying to write a stored procedure that does the sum calculation on a field using parameters ID and date.

For this purpose I have two tables. My table that contains data

ID      CAF     SDATE
1       12345   15/06/2018
1       04564   16/06/2018
1       03589   30/06/2018
1       00099   01/07/2018
3       00056   15/06/2018
3       00325   20/06/2018
4       00123   01/06/2018
5       04445   12/06/2018
5       16676   23/06/2018

And the table that contains parameters

Prameter table

ID  SDATE1      SDATE2
1   01/06/2018  30/06/2018
3   15/06/2018  30/06/2018
4   01/06/2018  30/06/2018
5   15/06/2018  01/07/2018

My query that I want to use in my stored procedure:

select T1.ID,SUM(T1.CAF) From
(SELECT *
  FROM TABLE
  where ID = @ID
  and SDate between @SDATE1 and @SDATE2) T1
Group By ID

The result I'm expecting is the following:

ID  SUM(CAF)
1   20498
3   381
4   123
5   16676

I believe that I need to run some kind of loop to run my query for every ID, but I'm struggling a little bit.

No loop needed, you can calculate the sums simply by joining by ID and filtering the date with a BETWEEN.

SELECT
    P.ID,
    SUM(D.CAF)
FROM
    Data AS D
    INNER JOIN Parameter AS P ON D.ID = P.ID
WHERE
    D.SDATE BETWEEN P.SDATE1 AND P.SDATE2
GROUP BY
    P.ID


Will PostgreSQL server work if I install the service in mounted partition I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).

I am facing problem with memory since my root partition having only 10 GB. I have verified the data folder (/opt/postgresql/9.1/data/), and noticed few things.

/opt/postgresql/9.1/data/base directory having more data
pg_largeobject table is having more data

So, I planned to do following activity,

Take the full backup of existing database(s) - custom format (*.sql)
Uninstall the postgresql from the root directory, using uninstall.sh (which is available the postgresql path)
Install the new postgresql in mounted path (/newly/mounted/remote/path/postgresql/)

If I do above steps, will postgresql service start and work without any issue?
(if no issue, I will restore the database backup)

Any suggestion?

(using postgresql-9.1)

Yes, you can move your base directory to any location you wish where the postgres service has write access and enough storage available. There are a few things to note

your startup script needs to set it's PGDATA directory correctly to the new location
your clients that connect locally have to set their PGDATA to the new location.
Tablespaces in postgres add a lot to complexity. Better is to try to prevent them being used at all.

Tablespaces are directories that are pointed to by symbolic links in $PGDATA so you can keep them if you like. They will be resolved.


How to exclude tables when using pg_dumpall with postgresql? If use pg_dump, it has a --exclude-table-data option to ignore special table.

But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?

The name pg_dumpall itself says “dump all”, which means you can't exclude anything. There is no such pg_cumpall option available.

Use pg_dump instead.


Falied to load resource files of Oracle APEX plugin I've installed a plugin in APEX, but when I've tried to use it on a page, I've got some nasty errors and page not working properly.

The files are present in the plugin settings:

But, as you can see below, they are not being loaded for some reason. I get 404 errors instead.

I have ORDS running and APEX 19 installed on DB 19c XE.

What could be causing this? Wrong ORDS configuration maybe? How to fix it?

After APEX upgrade from one version to another you should recompile i.war by running java -jar ords.war validate --database apex. java -jar ords.war static could also be useful.

For debug it is convinient to edit ORDS config file defaults.xml with these strings:

<entry key="debug.debugger">true</entry>
<entry key="debug.printDebugToScreen">true</entry>

Don't forget to set them false on production.


best performance for storing notifications in postgresql I have a question concerning the performance of 2 different options, which one is better?
Explanation:
I have 5 tables in a postgresql db: users, followers, posts, likes and comments. They look like this (simplified):
users (
  user_id bigint,
  username character varying(24)
);
followers (
  follower_id bigint,
  user_id bigint,
  following_user_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);
post (
  post_id bigint,
  user_id bigint,
  text text,
  timestamp timestamp with time zone DEFAULT NOW()
);
likes (
  like_id bigint,
  post_id bigint,
  user_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);
comments (
  comment_id bigint,
  post_id bigint,
  user_id bigint,
  comment text,
  timestamp timestamp with time zone DEFAULT NOW()
);

I now want to make a notifications component in my app to show someone he received a new like or comment on a post.
FIRST OPTION:
The first option (I have now) is to create a sixth table named notifications:
notifications (
  notification_id bigint,
  type character varying(12),
  g_user_id bigint,
  r_user_id bigint,
  s_user_id bigint,
  post_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);

A notification row would look like this:
+---------------------------------------------------------------------------------------+
| notification_id | type   | g_user_id | r_user_id | s_user_id | post_id | timestamp    |
+---------------------------------------------------------------------------------------+
| 1               | post   | 1         | NULL      | NUL       | 1       | 2021-01-01...|
| 2               | comm   | NULL      | 2         | 1         | 1       | 2021-01-01...|
| 3               | like   | NULL      | 1         | 2         | 1       | 2021-01-01...|
| ...                                                                                |
+---------------------------------------------------------------------------------------+

It seems to me this is the easiest solution, a query to select and order the notifications for a user (and fetch meta-data like the username of the sender_user_id) is easy with a JOIN.
The problem I'm facing is: I have more notification types (not only likes and comments but around 20 different notification types). Some notifications are for a user specific (defined in receiving_user_id), some notifications are 'general' and are for all users following an account (I defince this in general_user_id).
My (simplified) query to fetch these notifications now look like this. I'm selecting all notifications for r_user_id (receiving_user_id) 1 with a JOIN to the table users to select all usernames from the sending_user_ids (s_user_id) (if not null) and to select all "general" notifications (all notifications with g_user_id NOT NULL where g_user_id is a user_id that user_id = 1 is following in the table followers).
SELECT
    a.notification_id,
    a.type,
    a.timestamp,
    b.username
FROM
    notifications a
LEFT JOIN
    users b
  ON
    a.s_user_id = b.user_id
WHERE
    (
      a.r_user_id = 1
      OR
      a.g_user_id IN (SELECT following_user_id FROM followers WHERE user_id = 1)
    )
ORDER BY
    a.timestamp DESC

The biggest problem I have with this solution is that it requires an extra table and extra commands in my node.JS code. When someone likes a post, I have to create a notification row, when some dislikes a post, I have to delete this notification again.
SECOND OPTION:
My second option is to just leave these 5 tables and with a UNION ALL query "create" the notifications on the fly, if possible.
I don't know how other people create a notification system, and I didn't found much tutorials about it. Is it OK to create a separate notifications table (and use postgresql trigger functions to automatically create/delete notification rows), or should I create these notifications 'on the fly' based on the rows in likes, comments and posts?

There's nothing wrong with creating a dedicated table for notifications, along the route your example above is choosing to take. Whether you use triggers or handle managing it with calls from the application side is up to you and equally of merit as well.
Alternatively I was thinking similar to your last proposed solution, but by using a View as a way to centralize the different notification types and stay current in real-time. You would UNION ALL your different notification types and when you select from the View, you would likely order by the timestamp descending to show the latest notifications first.
The only other part you'd need to come up with is how to manage which notifications a user has already seen. For that you'd likely need a logging table of the notification's id and type, and had columns for the user's id and timestamp of when they viewed their notifications. Then in the View above you'd OUTER JOIN to this viewed notifications log, so you can have an additional column in it called isNew and you'd know which ones are new to the current user since the last time they looked at their notifications. At least this viewed notifications log table would be written to less frequently (only whenever a user looks at their notifications) than just a pure notifications table (every new instance of a notification), so it would be much transactionally lighter.


Backup Error with Msg 3201, Level 16, State 1, Line 1 & Operating system error 3 When i am taking the backup in network share drive with mention below TSQL.

Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'H:\DatabaseBackup\DatabaseName.bak';
Go

I am getting the following below error in SSMS.

 Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'H:\DatabaseBackup\DatabaseName.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

As I have checked to 'xp_cmdshell' through this TSQL . The run_value & config_value  as follows:

name       minimum  maximum config_value    run_value
xp_cmdshell 0           1     1               1

Note : Here H is my Network share.

Any suggestion will be appreciated.

SQL Server can only see network drives that are mapped in the Windows Profile of the SQL Server service account. So you'd have to log on as that account (not your own) and map the drive, for SQL Server to be able to use it for backups. Backups run using the credentials of the SQL Server service account, not yours. As suggested in the comment above, try using a full UNC patch instead (e.g. '\fileserver\fileshare\backups\backupfile.bak' which will not require mapped drives.

Note that the SQL Server service account will require read/write permissions to the destination. Being able to access external file system is one very good reason to use a domain account for your SQL Server service account, rather than LOCAL SYSTEM etc..


Performance benefit of taking unused databases offline I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to offline but I wanted to know:

Is there a performance benefit to having these unused databases in the offline state instead of sitting online but unused without any connections to them or queries run against them?

If the database has objects in memory, taking it offline will remove those objects from the buffer. This frees up memory for other database objects.

Also, taking it offline ensures that automated maintenance plans (backups, index maintenance, integrity checks...) don't waste any resources doing maintenance on it.


How to move a database from SQL Server 2012 to SQL Server 2005 What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)?

I know I cannot:

restore a backup of the database on SQL Server 2005
detach & attach

I know I can:

use import data wizard, and I tried it on one database, but it only moves data, and even that was troublesome as I needed to do a lot of work creating temp tables to maintain identity columns, recreating all FKs, indexes etc.

Is there an easier option?


Link Server Error - From SQL2008r2 to SQL2014 I'm getting the following error when I try to query a table using a link server.
The link server is created on SQL2008r2 instance to connect to an SQL2014 instance.

  The OLE DB provider "SQLNCLI10" for linked server "server\SQL2014"
  does not contain the table ""db_name"."dbo"."table_name"". The table
  either does not exist or the current user does not have permissions on
  that table.

Here is the TSQL i'm using to create the link server

EXECUTE sp_addlinkedserver @server = N'server\SQL2014', @srvproduct=N'sql_server', @provider=N'SQLNCLI10'

EXECUTE sp_addlinkedsrvlogin @rmtsrvname='server\SQL2014', @useself='false',@rmtuser='sa', @rmtpassword='sa_password'

When I test the connection that succeeds

The linked server login is sa so permissions can't be an issue.

sa user is enabled and table exists i can connect to and query all the tables directly on the SQL2014 instance using the sa user.

Where am I going wrong?

Please try specifying the @datasrc parameter when you create a linked server, e.g.

EXECUTE sp_addlinkedserver
@server = N'server\SQL2014',
@srvproduct=N'sql_server', @provider=N'SQLNCLI10',
@datasrc = N'server\SQL2014'

The confusion may lie with the @server parameter being a descriptive name of the linked server to use in your code.  @datasrc is actually physical server name used by OLEDB to make the connection.


Does SQL Server allow INSERTs into tables and indexes to be written in parallel? If I have a table that has many indexes, and I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or will the rows be inserted using parallelism?

I run a statement that inserts rows into the table, will SQL Server insert rows one-at-a-time, or use parallelism?

Before SQL Server 2016, an individual T-SQL INSERT statement always used an execution plan that inserts rows and maintains associated nonclustered indexes using a single logical processor. This answers one aspect of your question.

The question of inserting rows 'one at a time' requires a bit of explanation. Fundamentally, data modifications in an INSERT...SELECT execution plan always operate as one or more row-at-a-time streams that execute in serial order.

That said, the query optimizer often has a choice between two execution plan strategies:

Insert rows to the base table and nonclustered indexes using a single plan operator; or
Use a separate plan operator for each nonclustered index, clustered index, or heap.

The first option means that each row is inserted to the base table, and all nonclustered indexes are updated in a serial sequence, before moving on to the next row. This is known as a narrow or per-row, insert strategy.

The second option means all rows are inserted to the base table (though still one at a time) before doing the same thing for each nonclustered index, again in a serial sequence. This is known as a wide or per-index insert strategy.

The optimizer makes a cost-based choice between narrow and wide strategies for each nonclustered index, so it is quite common to see an execution plan that maintains some nonclustered indexes in the same operator as the base table, while other indexes are maintained using separate operators.

The wide/per-index strategy also allows potential optimizations such as sorting the set of rows in key order for each index in order to promote a sequential access pattern.

You can find more details about this and execution plan examples in my blog article, Optimizing T-SQL queries that change data.

There is a feedback suggestion to allow parallel maintenance of nonclustered indexes. It was received favourably on Connect (and later migrated to the new platform), but the idea has not been implemented yet.

While it is limited to a single processor for data modifications, INSERT...SELECT can use parallelism in the part of the plan that identifies and creates the data to insert. From SQL Server 2016, INSERT...SELECT can perform parallel inserts to a heap.

For best insert performance, check that your query meets the criteria for minimally-logged operations, possibly requiring trace flag 610. Details can be found in the Data Loading Performance Guide.

You might also want to investigate using other SQL Server facilities that do allow parallel inserts (with some caveats) using multiple processes:

SSIS (SQL Server Integration Services)
T_SQL BULK INSERT (from a file)
The bcp utility

SELECT INTO does allow parallelism (Server 2014+) but this requires that the table does not exist before the process begins, and any nonclustered indexes would have to be added afterward.

If you are using Enterprise Edition, partitioned tables offer additional possibilities to optimize bulk insertions. Again, see the Data Loading Performance Guide for details.

Related articles written by me:

Minimal Logging with INSERT…SELECT into Empty Clustered Tables
Minimal Logging with INSERT…SELECT and Fast Load Context


pgAdmin - "cannot open logfile!" (WINDOWS 10; Postgres 9.4) Our Windows 10 clients have both PostgreSQL 9.4 and pgAdmin3 (v1.22) installed.
I keep receiving the FATAL: "Cannot open the logfile!" error by invoking pgAdmin3 in either
of the following EXE locations.

C:\Program Files(x86)\pgAdmin III\1.22\pgAdmin3.exe
C:\Program Files\PostgreSQL\9.4\bin\pgAdmin3.exe

The problem is that when pgAdmin3 starts the pgAdmin splash screen will not dismiss and you cannot gain
access to configure the postgresql.conf file.  I'm assuming the log file its looking for is pgAdmin.log but if I navigate to C:\Users\<user_id>\Documents\postgresql I cannot find such a log file.  I tied to create an empty pgAdmin.log file whith not luck of gaining proper entry into pgAdmin3.

First you need to identify the value of %APPDATA% on your WINDOWS system.
Change directory to %APPDATA%
Does the folder postgresql exist?  If not create the folder with this name.
Chand directory into postgresql
Does the folder pgAdmin exist?
if not create the folder with this name (note the capital "A").
In here copy the postgresql.conf file from your Postgres or pgAdmin installation folder
Create an empty pgAdmin3.log file in here
Done


How to create a database for unknown kinds of data? I am in the process of designing a database for a new PHP/MySql-based application.

My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and changing.

Here is the problem example:
The application will be a shopping website that has many kind of products all of them have some shared attributes such as title and price but some kinds have specific details such as expiry date some have isbn some non.

This is just an example but I really have many kinds with many different attributes.

I can create a table for each kinds, but what I have is not all the available kinds, many kinds of items are unknown at this time.

Is their a way to accommodate this problem without over head in the user's side?

Unknown kinds of data sounds to me somewhat fishy.  Your examples, of course, are all knowns.  For goods and services careful analysis and normalization are important and I think you can get away from EAV modelling (which I think will cause mor problems than it solves) for core data.  The rest could be stuffed in XML fields or the like.  Additionally if you do your design right you can always expand the information appropriately.  Consider the following three tables:

CREATE TABLE products (
    id int autoincrement primary key,
    sellprice numeric,
    part_code varchar(10),
    title varchar(32),
    description text
);

CREATE TABLE barcode_type (
    id int autoincrement primary key,
    label varchar(15) not null unique
);

CREATE TABLE make_model (
    id int autoincrement primary key,
    make varchar(15) not null,
    model varchar(15),
    barcode_type int references barcode_type(id),
    barcode varchar(32)
);

Now with this you can assign barcodes (including ISBN, EAN, UPC, etc, to various parts, one per make/model combination.  If you need to support more barcode types, this is not hard to add.  As far as expiration dates, where these go depends on where you are tracking them.  If you want to have temporary pricing, or pricing for groups of customers, you can add that too.

However what you are describing doesn't sound very unstructured.  I would suggest starting with a minimal design and expanding as needed rather than an EAV design and later regretting it.


Understanding rows count computation PostgreSQL 9.4

I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column:

       most_common_vals
{20000,80000,40000,60000,100000}

Now, I run the following simple query EXPLAIN ANALYZE SELECT * FROM customers WHERE income=123123 to understand the rows count estimating. Output:

Seq Scan on customers  (cost=0.00..738.00 rows=1 width=268) (actual time=4.669..4.669 rows=0 loops=1)
  Filter: (income = 123123)
  Rows Removed by Filter: 20000

Since the optimizer doesn't have statistic for the income value 123123, it made a wild guess for 0.5% of the table size. So, the estimated row count should have been 500. But the optimizer returned the 1. Why? Maybe I didn't understand the estimating process of row counting of unknown values?

Couldn't you explain it a bit?

Since it was not in most_common_vals, PostgreSQL then looks in the histogram.  If there is no histogram, it would conclude the most common values are the only values present in the table, and therefore the estimate for 123123 is zero.  But it doesn't allow zero estimates in most places, to prevent div by zero error, and instead clamps it at 1.

0.5% is for cases where there is no information, like for a generic query plan or a join where the value won't be known at planning time.

But having a list of MCV and the one you want not being present in that list and there being no histogram to fallback on does constitute information.


MySQL syntax error 1064 near IN stored procedure an aspiring DBA Here.

I'm trying to create a stored procedure for a query I'm running. It is a memsql server which is MySQL based. The procedure is as such:

delimiter //
CREATE PROCEDURE calc_campaign (IN usr VARCHAR(38))
BEGIN

Awesome SELECT statement that uses a variable

END //

The error description is:

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 'IN usr VARCHAR(38)) BEGIN my_statement; END'
at line 1

The syntax I used seems to correspond with this:
 https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
 And with pretty much every other procedure I saw on Stack forums.

What is it I'm missing here? Thanks for any help!

MemSQL is not MySQL - see e.g. their page MySQL Features Unsupported in MemSQL. For details on their stored procedure implementation, please refer to MemSQL's documentation on stored procedures:

[USING database_name] CREATE [OR REPLACE] PROCEDURE procedure_name ( [parameter_list] )
  [RETURNS { data_type [data_type_modifier] } ] AS
  [DECLARE variable_list] [ ...n ]
  BEGIN
    procedure_body
  END ;

parameter_list:
  parameter_name data_type [data_type_modifier [ ...n ] ] [, ...]

data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

variable_list:
  variable_name type_expression [= value_expression ] ; [... ;]

As you can see, there is apparently no support for declaring parameters as IN, OUT, or INOUT. (They do however support default values for parameters, something which neither MySQL nor MariaDB support!)


Refresh Materialized View impact on the DB Hi we are running an PostgreSQL 9.6 database in Amazon RDS under a m4.large(2cpu 8gb) and a provisioned IOPS of 1000. The use case is the following: We have a table with several million of registries (4M more or less) and we have created a materialized view with a subset of this table (2M aprox) changing some columns types to be more efficient in querying. Our pg_conf has not been changed, being RDS Postgres' default.

This is our view definition:

CREATE MATERIALIZED VIEW public.customers_mv as
SELECT
    id,
    gender,
    contact_info,
    location,
    social,
    categories,
    (social ->> 'follower_count')::integer AS social_follower_count,
    (social ->> 'following_count')::integer AS social_following_count,
    (social ->> 'peemv')::float AS social_emv,
    (social ->> 'engagement')::float AS social_engagement,
    (social ->> 'v')::boolean AS social_validated,
    search_vector,
    flags,
    to_tsvector('english',concat_ws(' ','aal0_'||(customers.location ->> 'aal0'),
      'aal1_'||(customers.location ->> 'aal1'),
      'aal2_'||(customers.location ->> 'aal2'),
      'frequent_location_aal0_'||(customers.location -> 'frequent_location' ->> 'aal0'),
      'frequent_location_aal1_'||(customers.location -> 'frequent_location' ->> 'aal1'),
      'frequent_location_aal2_'||(customers.location -> 'frequent_location' ->> 'aal2'),
      'last_post_location_aal0_'||(customers.location -> 'last_post_location' ->> 'aal0'),
      'last_post_location_aal1_'||(customers.location -> 'last_post_location' ->> 'aal1'),
      'last_post_location_aal2_'||(customers.location -> 'last_post_location' ->> 'aal2'),
      'admin_location_aal0_'||(customers.location -> 'admin_location' ->> 'aal0'),
      'bio_location_aal0_'||(customers.location -> 'bio_location' ->> 'aal0'))) as loc_vector
FROM public.customers
WHERE (customers.social -> 'follower_count') > '5000'
AND customers.social ? 'last_posts'
AND (customers.flags IS NULL OR NOT customers.flags @> '{"destroy": true}'::jsonb);

CREATE INDEX customers_mv_followerc_idx ON customers_mv USING BTREE (social_follower_count);
CREATE INDEX customers_mv_folling_idx ON customers_mv USING BTREE (social_following_count);
CREATE INDEX customers_mv_emv_idx ON customers_mv USING BTREE (social_emv);
CREATE INDEX customers_mv_gin_social_idx ON customers_mv USING GIN (social jsonb_path_ops);
CREATE INDEX customers_mv_partial_social_validated_idx ON customers_mv (social_validated) WHERE social_validated = FALSE;
CREATE INDEX customers_mv_categories_idx ON customers_mv USING gin (categories);
CREATE INDEX customers_mv_gin_location_idx ON customers_mv USING GIN (location jsonb_path_ops);
CREATE INDEX customers_mv_gin_loc_vector ON customers_mv USING gin(loc_vector);
CREATE UNIQUE INDEX customers_mv_uniq_id_idx ON customers_mv (id);

Our view has some columns just for accessing data like location or social (both jsonb type) and some other like loc_vector for querying faster.

Now the problem:
The problem occurs when we try to refresh, refresh concurrently or create a new materialized view, when we try to launch the refresh command the CPU or the write IOPS crash the DB.

Here we can se how hard it hits the DB

What would be the ideal setting given our requirements?

Its a problem of increasing infrastructure or maybe a matter of configuration/optimization?

Your problem is likely the indexes.

IIRC, refreshing a materialized view drops the existing data and create a new "table" with the current data.

What this does for your indexes is re-index the entire subset of data, which based on your indexes send like a significant workload.

My recommendation would be to not use the built-in materialized views and to instead roll your own equivalent, incrementally updating data so your are only writing stale data/indexes.

The trade off is that this will be much more complex to build out than simply creating a materialized view, and you'll need to consider how you want to track invalidation of stale data.


How to write this query in SQL Server Compact in more elegant way? The query is :

 Select
        Case AStatus
            When 1 then 'Success'
            When 0 then 'Faliure'
        END,
        Case AStatus
            When 1 then 'AStatus'
            When 0 then 'AStatus'
        END,
        Case AStatus
            When 1 then 'Expected:1'
            When 0 then 'Recived: 0'
        END
    From Statuses
    Where LocalPath= 'c:\Status'

The original query that didn't work well in SQL Server Compact (and equal to above query) is:

Select
   Case AStatus
      When 1 then 'Success', 'AStatus', 'Expected:1'
      When 0 then 'Faliure', 'AStatus', 'Recived: 0'
   end
From Statuses
Where LocalPath= 'c:\Status

There is no multi value CASE statement in T-SQL. However, you could do a join to a constant value row source like this:

SELECT *
  FROM dbo.Statuses AS S
  LEFT JOIN (VALUES(1,'Success','AStatus','Expected:1'),
                   (0,'Failure','AStatus','Received:0')
            ) AS SV(AStatus, Status, Reason, Message)
    ON S.AStatus = SV.AStatus;


MongoDB Replication initial sync fails with OplogStartMissing I'm currently testing replication in our development environment.

Our current (primary) mongo instance has around 70-80 GB of data. I created a new instance and added it to the replica set.

It takes around 4 hours to sync the collections at which point it starts playing back the oplog to catch up with writes on the primary. But doing rs.printSlaveReplicationInfo() shows that the secondary keeps lagging more and more behind the master, and eventually fails with the following error:

OplogStartMissing: error fetching oplog during initial sync :: caused by :: Our last optime fetched: { ts: Timestamp(1581379449, 5879), t: 4 }. source's GTE: { ts: Timestamp(1581379449, 20317), t: 4 }

At which point the initial sync starts over again. Both the master and slave are using version 4.2

How big is your oplog?

Could it be that the oplog is filling up and overwriting the operations since the sync started before the initial sync has finished?  If this is the case, the new secondary will never catch up. I had this error once before, but can’t recall the exact error now.

Check the oplog start / end points by using

rs.printReplicationInfo()

If this is the case, either increase the oplog size or pause the application (if possible).

I’ll dig through an old ticket and check the error I had with a sync failing.


SQL Connector for Azure Key Vault - Cannot use more than one key vault I am using SQL Server 2016 Enterprise Edition with SQL Connector for Azure Key Vault. I am trying to attach more than one Key Vault to my SQL instance but it is not working.

I went through these instructions to set up the EKM provider and create a credential, create a new database encryption key from the asymmetric key, etc. for the first key vault - testkeyvault1. I am trying to repeat these same actions using a second credential which is configured for the second key vault - testkeyvault2

However, I keep getting the following error:

  Msg 33049, Level 16, State 2, Line 70
  Key with name 'test2rsakey0' does not exist in the provider or access is denied. Provider error code: 3112.  (Provider Error - No explanation is available, consult EKM Provider for details)

SQL Server is clearly looking at the wrong key vault. Looking at the event viewer shows this as well:

Vault Name: testkeyvault1.vault.azure.net
Operation: getKeyByName
Key Name: test2rsakey0
Message: [error:112, info:404, state:0] The server responded 404, because the key name was not found. Please make sure the key name exists in your vault.

If I add testkeyvault2 first, and then repeat the process for testkeyvault1, the results are reversed. So it seems that SQL Connector looks for keys only in the first configured key vault. I even checked the registry and ensured that keys exist for both key vaults in the following path: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider\Azure Key Vault\

I need to be able to use multiple key vaults since some customers want to provide access to their own key vault to encrypt the database stored on our servers.

Is there any way around this? Any alternative solutions to use multiple key vaults, or to allow customers a BYOK model?

Stupidly enough, opening a new tab in SQL Studio fixed the issue. I guess when creating a credential, it seems to "stick" to the tab on which it was created, even if creating and binding a second credential within the same tab. Running the same commands in a new tab worked.


Unable to understand correlation between MaxWorkers created and CPU utilization I am having a strange issue which i am unable to understand:
On my server with 64 Logical processors having maxworkers 1472, there are peak periods in day where workers created for an hour lets say goes beyond maxworkers available. Ranges somewhere 1800-1900 in that duration.
From what I can understand and saw is there a burst of blocking happening in that period. Lots of update commands blockings its own process while that UPDATE will block other inserts and selects over that range of couple of minutes.
However CPU does not tend to increase and stays steady at 30% on avg. Should the CPU not spike to 100% or atleast much higher than 30% when workers available are exhausted ? OR those waiting threads are not even on schedulers?
Please help me understand , thanks

Correlation between MaxWorkers created and CPU utilization
I strongly recommend you to read the Thread and Task Architecture Guide doc. I'll quote some relevant parts:
About the higher number of workers and the relation between maxworkers and CPU:

On a very busy SQL Server Database Engine, it's possible to see a
number of active tasks that's over the limit set by reserved threads.
These tasks can belong to a branch that is not being used anymore and
are in a transient state, waiting for cleanup.

About the blocks you're experiencing:

If you suspect that there is a performance problem, it is probably not
the availability of worker threads. The cause is more likely something
like I/O that is causing the worker threads to wait.

About the CPU consumption not going beyond 30% while having requests blocking each other:

When a worker's quantum expires and is no longer active, the
respective task is placed in a FIFO queue in a RUNNABLE state, until
it moves to a RUNNING state again, assuming the task won't require
access to resources that are not available at the moment, such as a
latch or lock, in which case the task would be placed in a SUSPENDED
state instead of RUNNABLE, until such time those resources are
available.

And here's the list of states a worker can be (from sys.dm_os_workers). Unless it's on the RUNNING state, it's not using the CPU, therefore, you could have way more requests existing at the same time than the 1472 you expected to see if they're not in fact using the CPU.

Worker state. Can be one of the following values:
INIT = Worker is currently being initialized.
RUNNING = Worker is currently running either nonpreemptively or
preemptively.
RUNNABLE = The worker is ready to run on the scheduler.
SUSPENDED = The worker is currently suspended, waiting for an event to
send it a signal.

How to diagnostic your situation
With that in mind, you should check if you don't have a bottleneck caused by other resources (e.g., disk, network) required by the requests that are blocking the others. Paul Randal's article SQL Server Wait Statistics (or please tell me where it hurts…) has a terrific query to retrieve info about what could be causing trouble on your server. Before you assume the problem is the number of workers, I advise you to run that diagnostic on your server.


Question about 'user process' in the context of Oracle The following is an except from oracle documentation.

  Overview of User Processes

  When a user runs an application
  program (such as a Pro*C program) or
  an Oracle tool (such as Enterprise
  Manager or SQL*Plus), Oracle creates a
  user process to run the user's
  application.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm

As my understand, an user process is a piece of software that can connect to an oracle server. You (the user) can start a piece of this kind of software, then connect to oracle. If so, why Oracle creates a user process to run the user's application?

Thanks.

an user process is a piece of software
  that can connect to an oracle server.
  You (the user) can start a piece of
  this kind of software, then connect to
  oracle.

Not quite. A User Process in Oracle is different than a client, which is what I think you are referring to. The user starts a client program (SQL*Plus, Pro*C, etc.), which runs on the client system. This program contacts the Oracle database server, typically either via TNS or IPC. It is the Oracle database that spawns the User Process. It's a bit confusing terminology in that a User Process from the database perspective is really just another process that Oracle starts, but it is specific to a user's connection to the database - it is a server process. It is outside of the user's direct control.


Paginated SqlServer Query with Changing Data The traditional way to do server-side pagination of queries with SqlServer (earlier than 2012) has been to use ROW_NUMBER(), as somewhat covered here and in other places.

Let's say that I'm building an "infinite scroll" UI component in a web app, and using such a paginated query on the server side to fetch blocks of results which are then rendered.  When the user scrolls down, the next page of results are fetched using the ROW_NUMBER() of the last record from the previous request as the starting point for a new request.

This works very well, but I'm wondering what to do when the underlying data changes.  If my results are ordered by time, and a new record is entered, that new record would be at the "top" of the list.  The ROW_NUMBER() of the results would then all shift by one, breaking the continuity of the infinite scroll, thereby producing a duplicate returned record.  Deleting a record results in the opposite problem of a "disappearing" record because the client already "fetched that ROW_NUMBER()".

Is there a way to overcome this in the query?  I know I could suck out the entire result set and cache it in memory in my App Server or something, creating a sort of "data freeze" at least for that session, but I'd prefer not to.  Given that each row in the result set has a unique PK aside from the ROW_NUMBER() value, it seems like it might be possible to ask "give me the next 20 rows starting with this PK" to avoid shifting ROW_NUMBER() values, but I can't see a way of expressing this.

Given that each row in the result set has a unique PK aside from the ROW_NUMBER() value, it seems like it might be possible to ask "give me the next 20 rows starting with this PK" to avoid shifting ROW_NUMBER() values, but I can't see a way of expressing this.

I'm assuming that your clustered index is the same thing as your primary key. This is possible to do in SQL Server but the syntax isn't the most intuitive. Your code will look more complicated as the number of primary key columns increases. The basic technique that you want is described here.

I'm going to use the following table with a two column primary key as an example:

CREATE TABLE your_table (
    PK_COL1 INT NOT NULL,
    PK_COL2 INT NOT NULL,
    OTHER_COLUMN VARCHAR(100),
    PRIMARY KEY (PK_COL1, PK_COL2)
);

Consider the following query:

SELECT TOP (20) t.PK_COL1, t.PK_COL2, t.OTHER_COLUMN
FROM your_table t
WHERE (t.PK_COL1 = @id1 AND t.PK_COL2 > @id2) OR t.PK_COL1 > @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;

That will very efficiently give you the next 20 rows following the last primary key that you've already processed in the app. All that you need to do is get 20 rows, keep track of the latest primary key that you processed in the app, then use the latest primary key to set @id1 and @id2 to get the next 20 rows. Since you're using the primary key as an anchor it shouldn't matter if a new row is inserted in the middle of data that you've already processed.

We can throw a little data into the table for a demo:

INSERT INTO your_table WITH (TABLOCK)
SELECT
t.RN / 16
, 1 + t.RN % 16
, REPLICATE('Z', 100)
FROM
(
    SELECT TOP (10000) -1 +
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN  master..spt_values t2
) t
OPTION (MAXDOP 1);

Let's say that we've processed up to the row with PK_COL1 of 10 and PK_COL2 of 3:

DECLARE @id1 INT = 10;
DECLARE @id2 INT = 3;

SELECT TOP (20) t.PK_COL1, t.PK_COL2, t.OTHER_COLUMN
FROM your_table t
WHERE (t.PK_COL1 = @id1 AND t.PK_COL2 > @id2) OR t.PK_COL1 > @id1
ORDER BY t.PK_COL1 ASC, t.PK_COL2 ASC;

I get a very fast clustered index seek:

STATISTICS IO results:

  Table 'your_table'. Scan count 2, logical reads 4

The result set is as follows:

╔═════════╦═════════╗
║ PK_COL1 ║ PK_COL2 ║
╠═════════╬═════════╣
║      10 ║       4 ║
║      10 ║       5 ║
║      10 ║       6 ║
║      10 ║       7 ║
║      10 ║       8 ║
║      10 ║       9 ║
║      10 ║      10 ║
║      10 ║      11 ║
║      10 ║      12 ║
║      10 ║      13 ║
║      10 ║      14 ║
║      10 ║      15 ║
║      10 ║      16 ║
║      11 ║       1 ║
║      11 ║       2 ║
║      11 ║       3 ║
║      11 ║       4 ║
║      11 ║       5 ║
║      11 ║       6 ║
║      11 ║       7 ║
╚═════════╩═════════╝

For the next batch, you would use @id1 INT = 11 and @id2 = 7.


PostGIS "could not open extension control file" when creating extension I've followed the instructions here to install PostGIS
http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt but when I went to create the extension I got the following error. I'm using PostgreSQL 9.3.2 and Ubuntu 12.04. The extension doesn't appear in the dropdown list on pgadmin3 either.

# create extension postgis;
ERROR:  could not open extension control file "/opt/PostgreSQL/9.3/share/postgresql/extension/postgis.control": No such file or directory

Looks like you have more than one PostgreSQL install. You're connected to a PostgreSQL install from EnterpriseDB, in /opt. This install cannot see, or use, extensions installed into the Ubuntu-packaged PostgreSQL install.

The instructions you followed will have installed PostgreSQL 9.3 from http://apt.postgresql.org/, i.e. PGDG. You should connect to that PostgreSQL install, not the EnterpriseDB one, if you want to use the PostGIS you installed.

You need to either use the system-packaged (PGDG) PostgreSQL install on your system, or install PostGIS separately into the EnterpriseDB-packaged PostgreSQL install using their StackBuilder product.


MS SQL Server 2017 for Linux - Installing to non default paths I want to install SQL Server 2017 for Linux on RHEl7.4.. but I want to use LVM to create a series of volumes for each of the important volumes.

/mssql/EBSData
/mssql/EBSLogs
blah blah blah

Is there a way to do this without mounting the volumes, symlinking the paths, then installing?

I'd assumed the installer might have had additional args/flags to change the default installation path, but doesn't seem too.

You can set environment variables to control this: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables.  When you run the mssql-conf utility (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf) it will pick up these values and configure them for you.

This will allow you to change your directories:

Default data directory
Default log directory
Default dump directory (memory dumps etc)
Default backup directory

and more.


Limit the number of records depends on the part of primary key I created database for football matches.

I have 2 tables with a many-to-many relationship, so I also created a junction table:

Referee

CREATE TABLE referee (
  id_referee INT IDENTITY PRIMARY KEY,
  id_type_referee INT NOT NULL,
  first_name varchar(20) NOT NULL,
  last_name varchar(30) NOT NULL,
  date_of_birth DATE NOT NULL,
  FOREIGN KEY(id_type_referee) REFERENCES type_referee(id_type_referee)
);

Match

CREATE TABLE match (
  id_match INT IDENTITY PRIMARY KEY,
  id_stadium INT NOT NULL REFERENCES stadium(id_stadium),
  id_season INT NOT NULL REFERENCES season(id_season),
  date_of_match DATE NOT NULL,
  audience INT CHECK (audience>=0)
);

Junction table

CREATE TABLE match_referee(
  id_referee INT NOT NULL,
  id_match INT NOT NULL,
  PRIMARY KEY(id_referee,id_match),
  FOREIGN KEY (id_referee) REFERENCES referee(id_referee),
  FOREIGN KEY (id_match) REFERENCES match(id_match)
);

I want to limit the number of referees at a single match to three. How can I do that?

And I want to limit the number of referees on a single match to three,
  How can i do that?

One method is with a trigger:

CREATE TRIGGER tr_match_referee
ON dbo.match_referee
FOR INSERT, UPDATE AS
IF EXISTS(
    SELECT 1
    FROM inserted
    JOIN dbo.match_referee WITH (READCOMMITTEDLOCK) ON
        match_referee.id_match = inserted.id_match
    GROUP BY match_referee.id_match
    HAVING COUNT(*) > 3
    )
BEGIN
    RAISERROR('No more than 3 referees allowed per match', 16, 1);
END;

Another approach is to add a column with a unique referee number per match along with a unique and check constraint. You would need to assign the referee number when assigning a ref to a match.

ALTER TABLE dbo.match_referee
    ADD match_referee_number int NOT NULL
    CONSTRAINT CK_match_referee_match_referee_number
    CHECK (match_referee_number BETWEEN 1 AND 3);
ALTER TABLE dbo.match_referee
    ADD CONSTRAINT UN_match_referee_id_match_match_referee_number
    UNIQUE(id_match, match_referee_number);


MySQL 5.6.27: How to get latest pairs for different points in time in a single query? I use the following query to get pairs of latest s1.value for given group ids and some fixed date. Now if I want to query pairs for different dates I have to run this query several times with different dates. My question is can I rework this query so I could query value pairs for several dates at once?

SELECT
    s1.group_id, s1.value, s1.ts_week_start
  FROM stats_kpi AS s1
  WHERE
    (SELECT COUNT(*) FROM stats_kpi as s2
       WHERE s2.group_id = s1.group_id
         AND s2.ts_week_start >= s1.ts_week_start
         AND s2.ts_week_start <= timestamp('2018-10-01')
    ) <= 2
    AND s1.group_id IN (3939)
    AND s1.ts_week_start <= timestamp('2018-10-01')
  ORDER BY s1.ts_week_start DESC;

UPD #1

Sample input:

group_id | value | ts_week_start
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00
       1 |     3 | 2018-01-03 00:00:00
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00
       2 |     3 | 2018-01-03 00:00:00
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00

I want to get the following, for two groups(1, 2) and three dates (2018-01-05, 2018-01-02, 2018-01-01)
get pairs for latest and previous to latest values to the date, i.e. the desired result would be like:

group_id | value | ts_week_start
-- for group 1 and dates 2018-01-02/2018-01-01
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00

-- for group 1 and date 2018-01-05
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00

-- for group 2 and dates 2018-01-02/2018-01-01
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00

-- for group 2 and date 2018-01-05
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00

For 2018-01-01 there's only one record in the result - latest one and no previous.

Schema and data:

CREATE TABLE `stats_kpi` (
  `group_id` bigint(20) NOT NULL,
  `value` double unsigned DEFAULT NULL,
  `ts_week_start` timestamp NULL DEFAULT CURRENT_TIMESTAMP
  )

INSERT INTO `stats_kpi` VALUES (1, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (1, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (1, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (1, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (1, 5, timestamp('2018-01-05'));
INSERT INTO `stats_kpi` VALUES (2, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (2, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (2, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (2, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (2, 5, timestamp('2018-01-05'));

UPD #2

I modified proposal of Kapil Bhagchandani so it's a single query now. What I don't like is the querying dates from the stats_kpi table while I know them upfront:

SELECT
DISTINCT s1.group_id, s1.value, s1.ts_week_start
FROM stats_kpi AS s1,
  (SELECT DISTINCT(s3.ts_week_start) AS ts FROM stats_kpi AS s3
     WHERE s3.ts_week_start IN (TIMESTAMP('2018-01-05'), TIMESTAMP('2018-01-02'), TIMESTAMP('2018-01-01'))) AS dates
WHERE(
    SELECT COUNT(*) FROM stats_kpi as s2
    WHERE s2.group_id = s1.group_id
    AND s2.ts_week_start >= s1.ts_week_start
    AND s2.ts_week_start <= dates.ts
) <= 2
AND s1.group_id IN (1,2)
AND s1.ts_week_start <= dates.ts
ORDER BY group_id,value,s1.ts_week_start DESC;

SET @date:='2018-01-03';

select * from ( select ta.*,
                       if(@typex=ta.group_id,
                          @rownum:=@rownum+1,
                          @rownum:=1+least(0,@typex:=ta.group_id)) rown
                from stats_kpi ta, (select @rownum:=1, @typex:='_') zz
                where ts_week_start <= timestamp(@date)
                order by group_id, ts_week_start DESC
              ) yy
where rown < 3

fiddle


MongoDB pinging failed for distributed lock pinger caused by WriteConcernFailed:waiting for replication timed out. Error details: { wtimeout: true } W SHARDING [replSetDistLockPinger] pinging failed for distributed lock pinger :: caused by :: WriteConcernFailed: waiting for replication timed out. Error details: { wtimeout: true }

In my MongoDB logs i found these kind of error at particular time (18:21 -18:39)on all primaries and secondaries in my replica set and config servers  what causes this kind of issues & will it effect to the cluster and whats the solution to resolve this kind of issue

Your SECONDARY nodes are lagging on replication and your write concern dictates that write to the database should be acknowledged with X nodes, but before the system got enough acks, there was write timeout.

Check rs.printSlaveReplicationInfo() on all replica sets during time when you have hight load.


To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them? I know you cannot drop a table that has active foreign key constraints.

So I was wondering is it safe to:

ALTER TABLE myTable NOCHECK CONSTRAINT all
DROP TABLE myTable

Rather than dropping the constraints first, table dropping:

ALTER TABLE myTable DROP Constraint FK_MyTable_AnotherTable
ALTER TABLE myTable DROP Constraint FK_MyTable_YetAnotherTable
DROP TABLE myTable

SQL Server prevents you from dropping the table if a foreign key exists, nocheck or not:

create table t1
(id int not null, id2 int null)

create table t2
(id int primary key)

insert into t2 values (1)

insert into t1 values (1,1)

alter table t1 add constraint fk1 foreign key (id2) references t2(id)

alter table t1 nocheck constraint all

-- add a value to confirm nocheck working as expected
insert into t1 values (2,2)

-- lets clear out data from t1
truncate table t1

--alter table t1 drop constraint fk1

drop table t2

Gives you: Could not drop object 't2' because it is referenced by a FOREIGN KEY constraint.

So it's a moot point, really, you'll have to drop the FK first, either way.