Are there security-issues with a read-only-account on Oracle-DBs? I am currently working as IT-maintenance-guy for a classic MVC-system(oracle-db/jboss) at public sector. The newest feature should be the shutdown of my oracle-db-readOnly-Account cause of "security reasons" (<-- Yes, it is not any more specific than that). I tried to some investigation on what that security threats might be, but i ended up finding no argument suiting my case.

My question is:
Can a readOnly-Access to an Oracle-Database cause any serious security issues?
I am looking for arguments that i can toss at the decision-maker that there is no threat coming from me having a readOnly-Account!

Additional information:

I am also inside the house-LAN (behind exterior firewall)
I am a learned SQL-user.
I don't store data on my local machine and i have no intention of selling/leaking any data. Beside the fact that the data is not the slightest bit intresting for any outsider ;)

Thanks in advance for any good ideas/arguments/issues/hints!!

Yes, there are.

every account puts load on the server and has the potential to bring it down by overloading it with silly queries or sometimes just bad luck.
more and more important: privacy issues. When a company leaks data they can get serious fines.
company secrets can be stored in the database. Leaking them does not help their case.

control and audits are getting more and more important.
A solution can be to have a copy system in place that generates an adhoc query database that is de-personified enough so privacy concerns can be remedied. It also alleviates the production host from the load caused by the research queries.
A controlled reporting tool can also be helpful, I just don't see how that mitigates privacy concerns other than having predefined reports that just don't show personal data.


Why is this query failing with invoker rights? I connect to oracle as sysdba and run this

but why is it failing when called with invoker rights? That is the p_user call.

Here is the code:

alter session set current_schema=x;
CREATE or replace package p_definer as
    procedure d(name_ varchar2);
end;
/
CREATE or replace package p_user authid current_user as
    procedure d(name_ varchar2);
end;
/
CREATE or replace package body p_definer as
    procedure d(name_ varchar2) as
    begin
        execute immediate 'drop procedure '||name_;
    end;
end;
/
CREATE or replace package body p_user as
    procedure d(name_ varchar2) as
    begin
        execute immediate 'drop procedure '||name_;
    end;
end;
/
create or replace procedure f_foo as
BEGIN
    dbms_output.put_line('hello world');
END;
/

And here are the calls

SQL> call p_user.d('f_foo');
call p_user.d('f_foo')
     *
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "G_GS.P_USER", line 2

Why doesn't that work?

SQL>
SQL> call p_definer.d('f_foo');

Call completed.

Because this is a new 12c security feature.

User x creates a harmless package and everyone starts using it, even users with higher privileges, like SYS.

Now user x knows that SYS uses this package regularly, so user x could replace the contents of this package with some malicious code any time and do anything in the database, knowing the code will be ran by SYS sooner or later.

12c prevents this with this new feature:

INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges

But if SYS trusts x, the following can be done:

grant inherit privileges on user sys to x;

This will allow x to inherit the privileges of SYS when running code like the above.


Shrinking Reluctant Log Files I have several SQL Server 2008 databases with transaction logs which have gotten out of hand and filled the drive. One of these logs was 68gb yikes.

I know better than to kill off my backup chain by truncating the log but when I do a shrink alone, on several of the databases I get no reclamation of space whatsoever and on those that I do, the amount is negligible. I have verified that the client is running t-log backups hourly and that these have succeeded for the last several weeks.

Obviously I'm missing something. Can someone point me in the right direction or am I just stuck with having to manually truncate and pray nothing happens in the meantime?

This is one of those exceptions where it's probably okay to execute a DBCC SHRINKFILE. The log file is most likely this large not because of what is going on right now but because there was a time when hourly transaction logs were not being run (or you had an exceptionally large transaction that ran between or across log backups). I'm not sure where the misconception comes from, but backing up the log does not shrink the file for you, it only frees up space within the file.

You can perform a one-time operation like this without interfering with the log chain (or having to "hope" that "nothing happens in the meantime"), preferably right after a log backup has completed successfully:

DBCC SHRINKFILE(yourdb_log, 4000);

This should shrink the file to ~4 GB, which hopefully is plenty of room to accommodate your hourly transactions. You may want to pick a different size, I'm not sure...

If this command doesn't shrink the file as much as you expect, you can check why the log modification may be blocked using:

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'yourdb';


after update trigger sends data to log table and sends tag_id .I need tag Name? I Have 4 tables:

Documents, document_tag, doc_document_tag and log.

In document: doc_id, Doc_name are saved
In Doc_tag: Tag_id,Tag_name are saved
In doc_document_tag multiple tags are save against one or more
documents.

for every table logs are generated.

My question is that in my case in doc_document_tag  only tag_id is saved and I need tag_name

Based on your description, the Doc_Tag table should have the Tag_name you need.

In the future, giving actual table definitions, some sample data and the actual expected result will help in getting a more accurate and complete answer.

This query example might get you started

  SELECT DT.tag_name
  FROM dbo.logs L
  INNER JOIN dbo.doc_document_tag DDT
  ON DDT.Tag_id = L.Tag_id
  INNER JOIN dbo.Doc_Tag DT
  ON DDT.Tag_Id = DT.tag_id;


How does one identify if Oracle Data Guard is enabled in Oracle database? How does one identify if the Oracle Data Guard feature is enabled in Oracle database?

If this can be determined by a query, what is the query for finding the same?

There is a built-in procedure for that.

set serveroutput on
declare
  feature_boolean number;
  aux_count number;
  feature_info clob;
begin
  dbms_feature_data_guard(feature_boolean, aux_count, feature_info);
  dbms_output.put_line(feature_boolean);
  dbms_output.put_line(feature_info);
end;
/

Sample output when Data Guard is not used:

0
Data Guard usage not detected

Sample output when Data Guard is used:

1
Number of standbys: 1, Redo Apply used: TRUE, SQL Apply used: FALSE, Snapshot
Standby used: FALSE, Broker used: TRUE, Protection mode: MAXIMUM PERFORMANCE,
Log transports used: LGWR ASYNC, Fast-Start Failover used: FALSE, Real-Time
Apply used: TRUE, Compression used: FALSE, Flashback used: FALSE

The source of the above procedure is not wrapped, so one can browse it and find how the database detects Data Guard usage.

-- check for Data Guard usage by counting valid standby destinations
execute immediate 'select count(*) from v$archive_dest ' ||
    'where status = ''VALID'' and target = ''STANDBY'''
    into num_standbys;

if (num_standbys > 0) then
    feature_boolean := 1;

Basically, if the result of this query is greater than 0, than the Data Guard is in use as per the procedure:

select count(*) from v$archive_dest where status = 'VALID' and target = 'STANDBY';


Find how many SQL Server instances are installed On a SQL Server 2012 instance, how can I get the names of SQL Server instances using a SQL query?

I want to show instance names currently installed in SQL Server.

Here are some options using what is already available on a server with Windows Server and SQL Server installed.

You can list Services on a server with a name starting with SQL Server (...:

sc \\IP_or_Name query | find /I "SQL Server ("

IP_or_Name is the IP or DNS name of the Windows Server 20xx server.

Or with Powershell:

Get-Service | Where-Object {$_.DisplayName -like "SQL Server (*"}

You can look at the content of the registry key HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL using Regedit.

You can also use Powershell:

Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'

Key HKLM:\Software\Microsoft\Microsoft SQL Server\InstalledInstances gives a list of name separated by line breaks.

Powershell commands can be executed remotely using Invoke-Command:

Invoke-Command -ComputerName computer -ScriptBlock { command } -Credential username

Using sqlcmd you can get a list of the instances which broadcast their names on the network:

sqlcmd -L

Or with Powershell which has the advantage of showing Version number:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()


MySql Freely avaliable dataset I need MySQL dataset for testing purposes to check and improve performance and optimization.

Is there any freely available dataset (MySQL) of 200k rows ??

MySQL provides an Employee Database for instruction and learning purposes:

Database on GitHub: https://github.com/datacharmer/test_db

Instructions and Information: https://dev.mysql.com/doc/employee/en/


Optimize MySQL query and index for multiple column range query I have a table like this:

CREATE TABLE IF NOT EXISTS `jobs` (
    `job_id` varchar(36) NOT NULL,
    `job_status` varchar(30) NOT NULL,
    `created_at` datetime NOT NULL,
    `lease_date` datetime,
    `priority` int NOT NULL,
    PRIMARY KEY(`job_id`),
    INDEX `job_status_priority_lease_date` (`job_status`, `priority`, `lease_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The full query I want to perform on the table would be the following:

select * from jobs
where job_status="IN_PROGRESS"
and lease_date<"10minutesago"
order by
priority
limit 100

I am implementing database as a job queue. 10 min is like the job time out, I only want to act on the jobs that are already timed out. Jobs has different priority so I was ordering by it. Also, I want to make my DB job queue behave like FIFO, that's why my index includes lease_date.

I definitely want my results to be ordered by Priority, e.g. two chunks of the results with Priority 0 and 1. I don't need my results to be strictly ordered by lease_date, as long as they are Ascending order in their own chunks that is good enough for me. e.g. result set:

priority lease_date
0        2017-08-19
0        2018-09-20
1        2016-08-20
1        2018-10-20

Is my index enough for that? How much can I optimize the query and build an index to boost the performance as much as possible?

By "10minutesago" do you mean NOW() - INTERVAL 10 MINUTE?

order by priority DESC sorts only on priority.  The ordering within it is unpredictable.  It might be ascending, it might be descending, or seemingly random.

Your INDEX(job_status, priority, lease_date) won't get as far as lease_date.  It will take care of where job_status="IN_PROGRESS" and it might then walk through the rows in priority order.  But that's all.

You could say ORDER BY priority DESC, lease_date DESC, but and lease_date < "10minutesago" will not make use of the index.

Think of building an index thus:  First have any number of columns tested with =.  Then have one range column.

(After OP's edit)

Your example implies that you need

ORDER BY priority ASC, lease_date ASC

As for the question of efficiency, ...

The WHERE clause needs

INDEX(job_status, lease_date)  -- in this order; adding `priority` won't help

If the optimizer would prefer to focus on the ORDER BY, then

INDEX(job_status, priority, lease_date)  -- in this order

Notes:

job_status is first because it is tested with =.
Tacking on just lease_date makes it possible to handle the entire WHERE in the index.  But there will still be an "filesort" to handle the ORDER BY.
Tempting the Optimizer to focus on the ORDER BY with the second index (to avoid the filesort) may or may not backfire.  It depends on how much of the data needs to be skipped over because of the lease_date filter.

If you have a thousand rows in the table, simply add both indexes; the query will be "fast enough".  If you have a million rows, then you are stuck with a tough problem and should rethink the queuing mechanism.  No matter what you do with the indexes, occasionally the wrong index will be used and the query will run painfully slow.  Be aware that a hiccup in the system can make a queue sporadically jump from a civilized number of entries to a huge number.

Do you imply that this table is a "queue"?  How long does it take to perform an item in the queue?  If it is a "short" time, then "Don't queue it, just do it".

Add priority to WHERE

If you change to

select * from jobs
    where job_status = "IN_PROGRESS"
      AND priority = 1
    and lease_date < NOW() - 10 MINUTE
    order by priority
    limit 100

then either of these becomes optimal:

INDEX(job_status, priority, lease_date)
INDEX(priority, job_status, lease_date)

See my Cookbook for discussion.

If you have a mixture of with and without priority, then have these two:

INDEX(job_status, lease_date),
INDEX(job_status, priority, lease_date)

The Optimizer will pick between them.


Performance improvement Outer Apply I have a query which runs little slower.

SELECT b.BreakdownClassificationId,
       k.IsinCode,
       k.SedolCode,
       ClassificationDate,
       NAME,
       InstrumentType,
       GeographicalLocation,
       CapSize,
       Currency,
       ExchangeName,
       HoldingDomicile,
       MaturityDate,
       Sector,
       MajorSector
FROM   #BreakdownSet b
       OUTER apply (SELECT TOP 1 IsinCode,
                                 SedolCode,
                                 ClassificationDate,
                                 NAME,
                                 InstrumentType,
                                 GeographicalLocation,
                                 CapSize CapSize,
                                 Currency,
                                 ExchangeName,
                                 HoldingDomicile,
                                 MaturityDate,
                                 Sector,
                                 MajorSector
                    FROM   dbfinex.dbo.PfPortfolioHoldingClassificationFtid x WITH (nolock)
                    WHERE  ( x.isincode > ''
                             AND x.isincode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.sedolcode )
                            OR ( x.isincode > ''
                                 AND x.isincode = b.isincode )
                    ORDER  BY CASE
                                WHEN x.sedolcode = b.breakdowncode THEN 1
                                WHEN x.isincode = b.breakdowncode THEN 2
                                WHEN x.sedolcode = b.sedolcode THEN 3
                                WHEN x.isincode = b.isincode THEN 4
                                ELSE 5
                              END,
                              classificationdate DESC) k

Execution Plan

Order By inside the Cross Apply is very costly, is there any better way to write this query ?

You can eliminate the sort if you like, although it's hard to say if that will necessary improve query performance. The key is how you constructed your ORDER BY clause along with the search conditions. If there's any row that matches x.sedolcode = b.breakdowncode then you want to take that row, otherwise you go to the next condition. With the right indexes we can avoid the sort by splitting up the APPLY. The SQL Server query optimizer actually gives a nice hint because it transforms your OR conditions into UNIONs.

I'm going to create a limited example in order to show the general query plan shape that I'm after. I will also assume that the PfPortfolioHoldingClassificationFtid table has a primary key and a clustering key on a PK column. Here is my test data:

CREATE TABLE #BreakdownSet (
    BreakdownClassificationId BIGINT NOT NULL,
    breakdowncode VARCHAR(10) NULL,
    sedolcode VARCHAR(10) NULL,
    isincode VARCHAR(10) NULL
);

INSERT INTO #BreakdownSet
SELECT
  t.RN
, CASE WHEN RN % 10 = 1 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 4 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 7 THEN t.RN ELSE NULL END
FROM
(
    SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE TABLE dbo.PfPortfolioHoldingClassificationFtid (
    PK BIGINT NOT NULL,
    isincode VARCHAR(10) NOT NULL,
    sedolcode VARCHAR(10) NOT NULL,
    ClassificationDate DATE NOT NULL,
    OTHER_COLUMN VARCHAR(200) NOT NULL,
    PRIMARY KEY (PK)
);

INSERT INTO dbo.PfPortfolioHoldingClassificationFtid WITH (TABLOCK)
SELECT
  t.RN
, t.RN
, t.RN
, DATEADD(DAY, t.rn / 100, '20170101')
, REPLICATE('OTHER', 40)
FROM
(
    SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE INDEX isin_date ON dbo.PfPortfolioHoldingClassificationFtid (isincode, ClassificationDate);

CREATE INDEX sedol_date ON dbo.PfPortfolioHoldingClassificationFtid (sedolcode, ClassificationDate);

Here is your query with my table definitions:

SELECT b.breakdownclassificationid,
       k.isincode,
       k.sedolcode,
       classificationdate,
       other_column
FROM   #breakdownset b
       OUTER apply (SELECT TOP 1 isincode,
                                 sedolcode,
                                 classificationdate,
                                 other_column
                    FROM   dbo.pfportfolioholdingclassificationftid x WITH (
                           nolock)
                    WHERE  ( x.isincode > ''
                             AND x.isincode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.sedolcode )
                            OR ( x.isincode > ''
                                 AND x.isincode = b.isincode )
                    ORDER  BY CASE
                                WHEN x.sedolcode = b.breakdowncode THEN 1
                                WHEN x.isincode = b.breakdowncode THEN 2
                                WHEN x.sedolcode = b.sedolcode THEN 3
                                WHEN x.isincode = b.isincode THEN 4
                                ELSE 5
                              END,
                              classificationdate DESC) k;

Not surprisingly I get a different plan than you, but the sort still has a high estimated cost. What if we split the APPLY into four parts and have each APPLY only return the primary key of the table? If we have a covering index for each APPLY then we can find the primary key of the matching row with at most four index seeks. No sorting is required. We can also skip doing the seeks that we don't need by adding filters into the APPLYs but that isn't guaranteed behavior. Here's one way to write it:

SELECT b.breakdownclassificationid,
       k.isincode,
       k.sedolcode,
       classificationdate,
       other_column
FROM   #breakdownset b
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.sedolcode = b.breakdowncode
                    ORDER  BY classificationdate DESC) a1
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.isincode = b.breakdowncode
                           AND a1.pk IS NOT NULL
                    ORDER  BY classificationdate DESC) a2
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.sedolcode = b.sedolcode
                           AND a2.pk IS NOT NULL
                    ORDER  BY classificationdate DESC) a3
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.isincode = b.isincode
                           AND a3.pk IS NOT NULL
                    ORDER  BY classificationdate DESC) a4
       LEFT OUTER JOIN dbo.pfportfolioholdingclassificationftid k
                    ON k.pk = COALESCE(a1.pk, a2.pk, a3.pk, a4.pk);

With 1.5 M rows in the temp table, the worst possible case should be 6 million nonclustered index seeks and 1.5 million clustered index seeks.

The query runs in two seconds on my machine. The runtime doesn't matter because I have nonsense data. However, there isn't a sort in the plan. I uploaded the query plan to Paste The Plan. You should consider doing that as well for future questions. Here's also a screenshot of the actual plan:

The query that you have does a nested loop join and doesn't go parallel. With LOOP JOIN and MAXDOP 1 hints the query finishes in seven seconds on my machine. Here's the plan and here's a screenshot:


Move Oracle database to new server I have a Oracle 11g R1 Standard Edition database on production server working on Windows 2008 R2 SE. Unfortunately server it not working properly (os/hardware failure) and I need to move it to temporary machine for a few days.

My first idea was to create full backup on production server and restore it on temporary. Second was, to duplicate database using RMAN. Both methods works fine, but it takes around 4 hours to complete all operations. Database should go back to production sever in few days, so again system will be stopped. It gives 8 hours of system unavailability.

Datafiles are around 60 GB and database is running in archivelog mode

Can you suggest me how can I minimize this time without violating Oracle SE license?

Thanks in advance

As a side note, rman is very fast when backing up to solid state disks.  If you can get your hands on some put them in a raid you'll probably back up and restore in probably less than 30 minutes.

In either event, here's how to move the db to another host with hardly any downtime.  Here's the overview:

1. do a full rman backup.

2. copy everything in the flash recovery area to the new host

3. go through the process of restoring the db to the new host.
   a. There are a handful of steps to do this.  It's not too hard.
   b. First the full backup is restored.
   c. Then each archive log is applied to the new instance.
   d. When all of the archive logs are have been applied, the db is opened.

Keep the production db up and running while you are doing steps 1,2,3a,3b. This may take a few hours if you have slow disks/network.

Once the level 0 backup is restored, apply the archive logs sitting in the flash recovery area which you copied in step 2.

At this point, all you need to do put the production db in read only mode and then copy and apply the last set of archive logs which were created after you copied the FRA.  This should be very fast.

Then open the db and change clients so they now use the new db.


Create File Stream Enabled Database in Network File (NAS Server) I want to create a filestream enabled SqlServer database using NAS server as the storage. I already passed the creation of DB step. Look at my previous question https://stackoverflow.com/questions/16516515/creating-sql-server-database-with-data-and-log-file-on-synology-nas-server. I can create an ordinary db but couldn't managed to create filestream enabled DB. Is it possible to do this?

I didn't used Filestream enabled database before. Recently I had to install one for a document tracking and archiving software. I wanted to put the db on nas server because I don't have too much space on server harddisk. After some research I found out even I can put data and log files on nas I can not create filestream enabled database using nas so I installed totally on server instead.
"Thank You" for Whom Who Viewed my question.

Ferda


Secure way to connect: Windows Authentication or SQL Server Authentication and why? Which is the best way to create a connection string for SQL Server?

Should I be using a trusted connection string (windows authentication) or an untrusted connection string (sql server authentication) and why?

My concern is to make my connection string more secure, so it does not get compromised by an attack.

I have read on many forums that Windows Authentication is more secure, and if we use SQL Server Authentication then the user id and password could be sniffed over the network.

Windows Authentication is going to be the most secure means, as opposed to SQL Server Authentication.  The reason behind this is because with Windows Authentication, the OS handles the auth as opposed to SQL Server handling it with its respective method.

When you use Windows Auth, SQL Server just uses the authentication token that is stored when you log into Windows (the OS).  This token-based security is more secure than SQL Server's native username/password security.

In other words if you can, choose Windows Authentication.  There are times though when you cannot get around SQL Server authentication (legacy application, third-party vendor, etc.).


RDS Does Not Allow Restoring MS SQL DB from S3 I have a question for you folks who might be familiar with the intricacies of restoring a .bak file from S3 to an AWS RDS MS SQL instance.  Here is what I have tried, and how far I have gotten:

First some context:  I am the sole IT guy at an all Mac, non-profit, special needs school (we help the kids who fall through the cracks).  I recently received a backup of our SIS from a vendor who could only provide it by way of an MS SQL backup file, which ends in .bak.  According to my research, I should be able to spin up a free AWS RDS instance of MS SQL, and then restore the .bak file to that free instance.

I have successfully created an AWS account with a free-tier RDS instance that has a singular, blank (brand new) MS SQL database called database-1.  The instance is using MS SQL Express, version 14 (as far as I can tell).  Other specific actions I have taken include the following, all taken from articles I have found online discussing how to do this:

Created an Option Group with the MSSQK-BACKUP-RESTORE option, and added that Option Group to the RDS database called database-1.
Created an IAM role called restoreadmin and gave it premissions to do everything on my S3 instance.
Created an S3 bucket called Achieve-DB, and uploaded the MS SQL backup file to it.

The articles I used to guide me through all of this, btw, are:

Amazon RDS Support for Native Backup and Restore in SQL Server
Secure Access to S3 Buckets Using IAM Roles

However, despite doing everything listed above, including banging my head on the keyboard a few times, I am not able to restore the backup from S3 to the database in RDS.  The procedure for doing so, ostensibly, is the following:

Within the RDS console, select the database you would like to restore to,
Click the Restore from S3 button
Follow the wizard...

The problem I am running into is that when I click on the Restore from S3 button, the only two options for restoring are Amazon Aurora and MySQL.

Can anyone here suggest where I might be going wrong?  Thank you!!

The quick answer is "don't waste your time with AWS and RDS".  But there are nuances.  Those solutions might be appropriate for some things but trying to open a MS SQL backup file on a Mac is not one of them.  Here is what you want to do:

https://apple.stackexchange.com/questions/385430/how-to-read-ms-sql-backup-file-on-mac

The key to it all, for me, was this step by step article that walks you all the way through it:

https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-ver15


Do locks on readonly secondary databases propagate to the Read/Write Database I'm thinking about using SQL Server Availability Groups in SQL Server 2012 to offload reporting on our main database to a second read only server that is kept in sync through the availability group.

My question is when I run big reports on the read-only server that would normally create lots of blocking if run on the read/write version do the locks propagate from the read only server back to the read/write server? Or do the locks stay only on the read-only server and just cause blocking on data waiting to be synced?

Trying to see if having a read-only server would resolve some issues I am having.

AlwaysOn secondaries map all reads to versioned reads, which are (almost) lock-free
Even with versioned reads there are some (few) locks in place, eg. schema-stability locks on obejcts. There is never any sort of propagation of locks from secondaries to primaries. Furthermore, there cannot be any propagation of locks from secondaries to primaries simply because the secondary is a back-in-time copy of the primary. There is no point in propagating a lock on object 'foo' from secondary to primary because the primary may had dropped 'foo' 2 hours ago and the secondary did not catch up yet. The propagation of locks downstream from primary to secondary goes through the log records and as such the locks are re-acquired on the secondaries when the log is redone, therefore the lock is always applied to the appropriate structure (if 'foo' existed on the primary when the lock was acquired, then by definition exists on the secondary at the moment the lock is re-played).
Secondary reads may block redo (eg. by holding on a schema-stability lock when the redo is trying to acquire a schema-modification). The redo resumes when the secondary reads releases the lock. And 'redo' in this context means 'applying log from the primary on the secondary', ie. catching up.


How do I automatically enable or disable report jobs at AOAG failover? I have an AlwaysOn Availability Group with 2 DB servers. I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary) when either of these DB servers is restarted (after it's been powered down for whatever reason).

I see that message 35266, e.g., AlwaysOn Availability Groups connection with primary database established for secondary database 'XXXX' ...... is logged when the DB server is powered up. So, I can set an alert for it and execute a job to disable the jobs I mentioned above. However, I've noticed that this message is consistently logged twice per database, one immediately after the other.

Why is message #35266 logged twice per database?

As it turns out, AOAG event ID #35266 quite regularly occurs 2 or 3 times as a node is coming up or down. So long as what you do when a 35266 is detected isn't toggle-like in nature, you should be OK. It works fine in my case because what I do when I detect a 35266 is to (depending on the text in the event) either enable or disable specific jobs. So if I enable my job 2 or 3 on the primary node in rapid succession, no big deal

Here is what I ended up with.

Create SQl Server Agent job 'Enable Or Disable Rpt Jobs'

Accept defaults except as follows

Name: Enable Or Disable Rpt Jobs
Owner: account that has permissions to run T-SQL to enable/disable SQL Server Agent jobs
Enabled: Checked

Create job step:

Step name: enable_or_disable
Type: Transact-SQL script
Database: master

Command:

DECLARE @DB nvarchar(25)
DECLARE @MSG nvarchar(200)
SET @DB = '{DB name that provides report data}'

If sys.fn_hadr_is_primary_replica ( @DB ) = 1
BEGIN
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    .
    .
    .
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    SET @MSG = '"' + @DB + '" is PRIMARY. Enabled report jobs'
    PRINT @MSG
END
ELSE
BEGIN
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 0
        .
        .
        .
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    SET @MSG = '"' + @DB + '" is not PRIMARY. Disabled report jobs'
    PRINT @MSG
END

Schedule:

Enabled: Yes
Schedule Type: Start automatically when SQL Server Agent Starts

Create SQl Server Agent Alert 'Alert Disable Rpts on 35266'

Accept defaults except as follows

Name: Alert Disable Rpts on 35266
Database name:
Error number: 35266
Raise alert when message contains: checked
Message Text: connection with primary database established for secondary database '{DB name that provides report data}'

  Example: connection with primary database established for secondary
  database 'DB_NAME'

Response:

Execute job: checked
Select the job: Enable Or Disable Rpt Jobs

Create SQl Server Agent Alert 'Alert Enable Rpts on 35266'

Accept defaults except as follows

Name: Alert Enable Rpts on 35266
Database name:
Error number: 35266
Raise alert when message contains: checked
Message Text: connection with secondary database established for primary database '{DB name that provides report data}'

  Example: connection with secondary database established for primary
  database 'DB_NAME'

Response:

Execute job: checked
Select the job: Enable Or Disable Rpt Jobs


Mysql speed issues with SHOW COLUMNS or SHOW FIELDS To avoid making code changes every time we have a database change, we just use the SHOW FIELDS (Alias of SHOW COLUMNS) to get all of the columns for Inserts. We call this multiple times for multiple tables in various scripts.

Recently we have discovered speed issues so we started to look at the temp table disk report and found that the SHOW FIELDS FROM 'sometable' all had 100% hits to the disk temp table.

I've tried turning up our query cache size (yes it is enabled), and I've disabled the meta calculations 'SET GLOBAL innodb_stats_on_metadata = OFF;'

We are still getting 100% disk temp table hits on all of these show queries. Is there an easy way to fix these without a code change? We would like to still do our inserts without updating the columns in the code, and the SHOW FIELDS call is part of a framework and we would like to avoid rewriting it if possible.

Thanks

Would you consider a RAM disk for temp tables ?
First, create the RAM disk (16GB)
mkdir /var/tmpfs
echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab
mount -t tmpfs -o size=16g none /var/tmpfs

Next, add this to my.cnf
[mysqld]
tmpdir=/var/tmpfs

Then, just restart mysql
service mysql restart

Now, all temp tables will land in RAM
I have discussed this before

Oct 09, 2012 : Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL
Nov 30, 2012 : Is it bad to create many mysql temporary tables simultaneously?
Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
Nov 15, 2013 : MySQL creates temporary tables on disk. How do I stop it?

Give it a Try !!!


Find the remote userid causing updates on my table Small question here. I found some answer close to it but not exactly what I'm looking for.

Here is the situation: I have a SQL Server trigger on a table. Its task is to record the username (logon id) of the user who make some insert/update/delete.

So far, it's not so hard to find the information.

The complicated part is coming. The insert statements are made through an Access view with a generic SQL Server account.

What I want to do is find the (Windows) user that launch Access. I was able to find the host name (the name of the computer) but how can I found the user?

Thanks!

You cannot, if you are using a generic username to log in to SQL Server. Host name is as close as you can get. It shouldn't be hard to translate it to a user name with a query in Active Directory.


Splitting a csv bulk import to postgres into smaller blocks using psycopg2 I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller batches.

FOr comparison, when using MS-SQL and using bcp, files get automatically split into 1000 record batches. That is exactly what I (think I) want to do with COPY.

My main script is Python 3.5.2, the database is PostgreSQL 9.5.10 on Ubuntu 16.04 LTS. I am using psycopg2's copy functionality (on the simple account of it being the first that worked). I'm happy to change that, as long as I can call it from python (since I need to do some other processing per file -- organizing files, searching, determining the target stage table, unzip, cleanup, etc. -- since I am not sure how exactly the data transfer works using this method. Per psycopg2's doc the sql-version of copy is used (https://www.postgresql.org/docs/current/static/sql-copy.html), not the command line version. However I suspect the entire file will be buffered through stdin, but again, I'm not sure.

con = psycopg2.connect(database=dbname, user=user, password=password, host=host, port=port)
cur = con.cursor()
f = open(pathTempFile)
cur.copy_expert("""copy public.stage_datafile from STDIN DELIMITER ',' CSV HEADER""",
    file=f)
f.close()
con.commit()
con.close()

This process works fine, and I'm not even sure there are any drawbacks. However, it seems there might be a better/more robust way? I will use a trigger to export the data from the staging tables into the normalized main tables, surely that would benefit from smaller data blocks?

I appreciate any suggestions on making this process more robust.

Addendum:

I only need to import the data once, not regularly.
I could also use sed to generate some temporary files and import those, in fact I'm doing that while debugging

I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller batches.

Probably not. It'll work just fine. You're probably better off running the CREATE TABLE and COPY in the same transaction though, that way you generate minimal WAL.


Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation I am trying to evaluate SQL Server 2014 by using the 2014 evaluation edition to upgrade an in place SQL Server 2008 Standard sp4 instance.

At the end of the process I expect to have a SQL Server 2014 evaluation version running.

I am getting an error that says I cannot upgrade features.

Is there a migration path that I can use?

You cannot upgrade from SQL Server 2008 SP4 to SQL Server 2014 evaluation edition. Its not supported and wont allow you please see Supported Version and edition upgarde

You should not worry much about upgrading from 2008 Sp4 to 2014. The upgrade would not be a much of problem but how would application behave is something on which you should focus. You can download SQL Server 2014 evaluation edition , which is equivalent to enterprise edition for 180 days. Restore database from 2008 to 2014 and start testing. Don't use any enterprise features. When you are satisfied with application performance do an inplace upgrade to SQL Server 2014 standard

Before doing in place upgrade make sure you run upgrade advisor and mitigate all issues(if any) pointed by UG advisor.


What is the cost of adding a foreign key in mysql? I have an application which has a product_def table defining all products.
The recrods in product_def are fixed and  will never be deleted.

CREATE TABLE `product_def` (
`product_id` INT( 5 ) UNSIGNED AUTO_INCREMENT ,
`product_name` VARCHAR( 20 ) DEFAULT NULL ,
PRIMARY KEY ( region_id ),
) AUTO_INCREMENT=1 ENGINE=innodb CHARACTER SET utf8 COLLATE utf8_general_ci   ;

I have another order_records table which contains 5 products in a record.
This order_records table will be gigantic. I am wondering whether I shall use foreign keys in order_records to refer to product_def table.
What is the cost to use foreign keys in a table in consideration of data size and searching or updating speed?
My case here is that the records in product_def table will never be deleted, therefore data integrity is not the concern here.

My questions are :

(1) Assume order_records table has records 10G in size without those 5 foreign keys. Then how big order_records will be if I add 5 foreign keys to the table?

(2) What is the performance difference in updating and selecting from database after adding 5 foreign keys?
(The table order_records will be extremely huge.)

CREATE TABLE `order_records` (
`order_id` BIGINT( 5 ) UNSIGNED AUTO_INCREMENT ,
`order_1_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_2_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_3_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_4_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`order_5_product_id` INT( 5 ) UNSIGNED  NOT NULL ,
`name` VARCHAR( 20 ) DEFAULT NULL ,
PRIMARY KEY ( order_id ),
FOREIGN KEY ( order_1_product_id ) REFERENCES product_def(product_id)  ON UPDATE CASCADE
   ???  use foreign key  ????
FOREIGN KEY ( order_5_product_id ) REFERENCES product_def(product_id)  ON UPDATE CASCADE
) AUTO_INCREMENT=1 ENGINE=innodb CHARACTER SET utf8 COLLATE utf8_general_ci   ;

In MySQL, a foreign key requires an index. If an index already exists, the foreign key will use that index (even using the prefix of an existing multi-column index). If no index exists, defining the foreign key will build the index.

So the size increase and time to create a foreign key is about the same as to create an index on the same column(s).

The performance of a SELECT is not impacted significantly by the presence of a foreign key. Only a slight additional work for the optimizer, to consider the new indexes.

The performance of updating is more, because for each foreign key, an INSERT/UPDATE/DELETE has to check to see if the constraint is satisfied. That means a primary key lookup to the referenced tables. This impact is measurable, and it is greater if the referenced tables are not in the buffer pool.

Another impact is the locking. If I update a row in a child row that has a foreign key, InnoDB places a shared-lock on the referenced rows in the parent tables. That means no one can update those parent rows until I commit. If you have lots of threads updating child rows, then the parent rows may be locked most of the time, and this can make it hard to do concurrent work in the parent tables. Not so much a performance problem, but a concurrency problem.

As with all "how does that perform" questions, the answer really depends on your workload. If you don't have concurrent updates, for example, that issue may not effect you for all practical purposes. Testing it yourself with load testing is the only way to be sure. It's not something anyone on StackOverflow can answer precisely for you.


Changed a user name in MySQL, but user is still able to login using previous user name I created a user with user name user_dev. Then created a database sampledb and granted some privileges to this user on this database. Then I decided to edit username and changed it to user_local, and granted same privileges to this new name on same database.
Now both user_dev and user_local are able to login, but user_dev has no access to sampledb and privileges for user_local are correct.

SELECT `user` FROM `mysql`.`user`

does not show user_dev.
My question is, why user_dev is still able to login? Should I take any more steps to prevent this user from loging in?

There are several possibilities:

If you edited the privileges manually, you need to execute:

FLUSH PRIVILEGES;

To reload the privileges cache on memory (needed to speedup the checks on every query).
If you edited the privileges manually, you may have omitted an entry. Do not trust the SHOW GRANTS FOR, because it may omit the privileges edited directly on tables. Make sure that there are no user_dev not only on the user table, but on all these.
The fact that there is no user_dev user literally does not mean that a user_dev user cannot login. I will explain myself. The user that you submit is compared against the user privileges tables, so user_dev (let's say that is on localhost) matches:

'user_dev'@'localhost'
'user%'@'127.0.0.1'
''@'%'
'%'@'localhost'

In fact, by default, anonymous users from localhost can login, and all users can read and write on the test database. Run mysql_secure_installation to avoid that.


SQL Server query on server taking longer than laptop Our program uses an SQL server database as its back-end (SQL server 2008-r2 - 2014). The program is OK for performance but the Database seems to slow down a bit too much.

As part of the nightly process we need to run quite a lot of queries which currently is finishing too late. (Start at 12:00 midnight end at 9:00 am. People start work then and need the database) This is OK for now but we are still setting up the database and more batch jobs need to be setup.

For example with the same database on my laptop and the server one of the queries that we need to improve takes 2 minutes on my work laptop and 3 hours on our clients server. The clients server is new and has been built to our specs that we gave them(Specs below).(Server is dedicated to our SQL instance.)

So the server is taking 9000% times longer to run the query than my work laptop.
This is not just for the one query or one client server. This is happening with most of our client servers and their queries.

I understand it should be faster on my laptop but I'm thinking not by that much.
So my question is: What on our clients server environments can be making that type of difference? and What can I do about it?

Specs

OS Version: Windows Enterprise Ed

Memory 96 GB

Processor: 2 * 8 Core HyperThreaded CPU's

Controller 1

Disk subsystem 1: Raid 1 for OS - 2 * 300GB 10K HDD

Disk subsystem 2: Raid 10 for SQL Server Data - 8 * 300GB 15K HDD

Disk subsystem 3: Raid 10 for SQL Server TempDB - 4 * Enterprise grade SDD 256GB

Controller 2

Disk subsystem 4: Raid 10 for SQL Server Logs 8 * 146GB 15K HDD

Disk subsystem 5: Raid 10 for SQL Server Non-clustered Indices - 4 * Enterprise grade SDD 512GB

We had an issue with a select statement using like '%findthis%' was causing slowness issues. One server it'd run fast whereas the other server would be quite slow. We were able to remove one of the % signs to '%findthis' allowing the optimizer to correctly interpret the query--on both servers yielding similar performance results. (%findthis% is simply bad form, but it wasn't our code to change unfortunately).

Long story short, make sure your SQL Server versions are identical. For example, run select @@version and look at the numeric sequences like 10.50.6000.34.

Our versions were almost identical but our test server was running faster than production because it was running a trace flag--and a minor patch. The next patch for SQL Server would have included the flag so I'm told.

Run DBCC TRACESTATUS(-1) on both servers to make sure they're running the same trace flags as well. I forget which trace flag was running in test that made it faster, but the newest patch to production SQL would have enabled it. It's been a while and most people are out for the holidays, or I'd have more specifics on which flag and which patch.


How do I go about type iterations in a SQL Query? I have a Dependency table that has a ParentID, ParentEntityType, ChildID, and ChildEntityType along with additional fields. The EntityTypes refer to a variety of object types (Nodes, Groups, Transactions, etc.) which are all stored on their respective tables with the ID being the key. These tables contains some additional fields that I need to grab (e.g. the Name of the node).

For example: a Dependency record, with omitted fields, looks like

My question is how to smartly go about going through all the types. What I came up with was a left join for each type filtered with a WHERE and coalescing the resulting mass but that seems awful and would need to be duplicated for the child. I feel there has to be a better way but I'm a bit stumped.

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
    LEFT JOIN (SELECT  c.ContainerID
        ,c.Status
        ,c.Name
        FROM dbo.containers as c) as con on d.ParentNetObjectID = con.ContainerID AND d.ParentEntityType = 'Orion.Groups'
   LEFT JOIN (SELECT n.NodeID... --A left Join for every flavor that looks the same as above
WHERE 1=1 --Not important for this
ORDER BY ParentName

I would just join the tables directly
Coalesce is fine here I think

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
LEFT JOIN dbo.containers as con
  on d.ParentNetObjectID = con.ContainerID
 AND d.ParentEntityType = 'Orion.Groups'
LEFT JOIN  ...
ORDER BY ParentName

You could put the 3 tables in a view or cte

select "orion" as type,
        c.ContainerID
       ,c.Status
       ,c.Name
 FROM dbo.containers as c
union all
...


What is difference between primary index, secondary index, unique index and normal index? I work on Oracle systems where I two type of indices exists in any table:

Unique index (Based on any search key which is unique key).
Normal index (Index made on any search key which is not candidate or primary key).

But now I came to know about two new type of indices from a university documentation:

Primary index
Secondary index

How are primary and secondary indices different from each other? Are they the same as unique and normal index respectively in Oracle?

I also want to know about below two indices:

Dense index
Sparse index

Can we consider dense and sparse indices as type of primary and secondary indices?

Primary index

A primary index is an index on a set of fields that includes
the unique primary key for the field and is guaranteed not to contain duplicates.
Also Called a Clustered index.
eg. Employee ID can be Example of it.

Secondary index

A Secondary index is an index that is not a primary index and may have duplicates.
eg. Employee name can be example of it. Because Employee name can have similar values.

Dense Index

Index record appears for every search key value in the file.
Dense indexes point directly to individual records.

Sparse index

contains index records for only some search key values.
Applicable when records are sequentially ordered on search key.
Just as with book indexes, sparse database indexes don’t point to individual records, but to ‘pages'


LEFT JOIN not joining the second table I'm having a problem with my SQL command running on MariaDB.
I have the following tables:
reservations:

id
child_id
time_from
time_to
user_id

1
1
2021-05-17 13:20:00
2021-05-17 14:20:00
NULL

2
2
2021-05-19 08:00:00
2021-05-19 12:30:00
2

3
2
2021-05-14 10:00:00
2021-05-14 13:00:00
NULL

users:

id
email
role
name
surname
contact

2
one@email.com
admin
peter
peterson
123456789

3
two@email.com
assistant
john
johnson
NULL

4
three@email.com
admin
mike
mikeson
987654321

5
four@email.com
admin
leo
leoson
NULL

6
five@email.com
admin
jane
janeson
NULL

I'm trying to join those tables by the reservations.user_id and users.id using LEFT JOIN, however unsuccessfully.
This is the command I made and edited many times without success:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable FROM reservations
LEFT JOIN (
    SELECT users.id AS userID, users.name AS name, users.surname AS surname FROM users
) AS users ON reservations.user_id = users.userID
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;

However this is all I'm getting:

id
child_id
time_from
time_to
user_id
removable

3
2
2021-05-14 10:00:00
2021-05-14 13:00:00
NULL
1

1
1
2021-05-17 13:20:00
2021-05-17 14:20:00
NULL
1

2
2
2021-05-19 08:00:00
2021-05-19 12:30:00
2
1

Basically I'm getting the first table without the data of the second table.
I do need to include rows with user_id NULL as it is used for confirmation reasons. Am I missing something?
Here is the requested dbfiddle: dbfiddle
The column is users.id but in the LEFT JOIN SELECT query I rename the column to userID. user_id of the reservation is a column allowing NULL, as it is a user assigned for the reservation. NULL => no one has been assigned yet.
What I'm trying to do is to get all the reservation rows and users assigned by staff to the reservation. Imagine it as assigned guard for the child. That's why I need to get all upcoming reservations with information who the guard is going to be.
If the user_id is not NULL, the table should contain even the columns users.name and users.surname since I'm selecting them in the join. That's what I'm looking for.

After clarification in the fiddle you provided, the only other way I can interpret your issue is you're saying you're not seeing the columns for the users table even though you joined to it. That's because you didn't list any columns from the users table in the SELECT list.
You can add any or all columns from your users subquery you want to the SELECT list like so:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable,
    users.*
FROM reservations
LEFT JOIN (
    SELECT users.id AS userID, users.name AS name, users.surname AS surname FROM users
) AS users ON reservations.user_id = users.userID
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;

Note you shouldn't use * in your SELECT list as it's bad practice and can lead to dependency issues later on, and rather you should explicitly list the column names you want.
Furthermore it seems like you might've gotten a little confused because you used a subquery to join in the users table but that's not necessary, rather you can re-write your query simpler like this:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable,
    users.id AS userID, users.name AS name, users.surname AS surname
FROM reservations
LEFT JOIN users
    ON reservations.user_id = users.id
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;


Can I use a single stored procedure to operate on different schemas based on the executing user I have thousands of schemas with same set of tables, Each user has a default schema. But I dont want to create copies of a single stored procedure in every schema. Can a single stored procedure access the tables of user specific schema.

I have created the test projects to test this but it is throwing error as the table not found

create Database PermissionsTest

-- Create  user1 and UserSchema1  and assign permissions
CREATE LOGIN [User1] WITH PASSWORD=N'User1'
GO
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[UserSchema1]
GO
ALTER LOGIN [User1] Enable
go
CREATE SCHEMA [UserSchema1] AUTHORIZATION [User1]
GO

-- Create  user2 and UserSchema2  and assign permissions
CREATE LOGIN [User2] WITH PASSWORD=N'User2'
GO
CREATE USER [User2] FOR LOGIN [User2] WITH DEFAULT_SCHEMA=[UserSchema2]
GO
ALTER LOGIN [User2] Enable
go
 CREATE SCHEMA [UserSchema2] AUTHORIZATION [User2]
GO

-- Create StoredProcedure Schema and creating the role to execute on this     schema
CREATE ROLE [ExecuteSprocsOnStoredProcsSchema] AUTHORIZATION [dbo]
GO

ALTER AUTHORIZATION ON role::ExecuteSprocsOnStoredProcsSchema TO User1;
GO

Create Schema [StoredProcedures] AUTHORIZATION [ExecuteSprocsOnStoredProcsSchema]
Go

EXEC sp_addrolemember N'ExecuteSprocsOnStoredProcsSchema', N'User1'
GO
EXEC sp_addrolemember N'ExecuteSprocsOnStoredProcsSchema', N'User2'
Go

-- GRANT Execute ON SCHEMA :: StoredProcedures TO     ExecuteSprocsOnStoredProcsSchema

GRANT CONNECT TO [User1]
Grant Connect to [User2]

grant SELECT ON SCHEMA::[dbo] TO [User1]
GO
grant SELECT ON SCHEMA::[dbo] TO [User2]
GO
----------------- Database data side changes ---------------
USE PermissionsTest
GO
CREATE TABLE [dbo].[StaticTable](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [int] NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

insert into StaticTable values (420);

-- drop table [UserSchema2].[Table1]

CREATE TABLE [UserSchema1].[Table1](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [varchar](max) NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

CREATE TABLE [UserSchema2].[Table1](
   [pkcol] [int] IDENTITY(1,1) NOT NULL,
   [col1] [varchar](max) NULL,
PRIMARY KEY CLUSTERED ([pkcol])
)
GO

Create Procedure [StoredProcedures].[Insert_Table1]
as
begin
Insert into table1 values (newID());
Insert into table1 select col1 from StaticTable;
end

after setting up the system for two users and the stored propcedure Insert_Table1

When I try to execute the stored procedure by User1 I get this error

  Msg 208, Level 16, State 1, Procedure Insert_Table1, Line 6
  Invalid object name 'table1'.

but when I run the queries separately as user1 it gets executed without any problem.

I know while executing the stored procedure the stored procedure temperarily takes the permissions of the Schema owener of that Stored procedure

I have created the schema with authorization to the role assigned to user1 already.

Create Schema [StoredProcedures] AUTHORIZATION [ExecuteSprocsOnStoredProcsSchema]

Am I missing anything.. Is there any way to make it work? Or is there any way that a single stored procedure can be used over multiple schemas..

You could probably use dynamic SQL,

CREATE PROCEDURE [StoredProcedures].[Insert_Table1]
AS

DECLARE @sql nvarchar(max)=N'
INSERT INTO table1 VALUES (NEWID());
INSERT INTO table1 SELECT col1 FROM StaticTable;
';

EXECUTE sys.sp_executesql @sql;

However, you should be aware that this is problematic for several reasons. The most obvious ones are:

If you're duplicating the entire schema for every user, there's probably something seriously wrong with your database design. Instead of giving users their own schemas, you should design permissions into the table (with a user column and row-level security),
It breaks ownership chaining (the user will now need permissions to the base tables, as opposed to inheriting rights from the stored procedure),
You need to manage SQL injection if your stored procedure accepts parameters that go into the SQL statement (preferably using parameterization),
Dynamic SQL has some other effects with regards to parameterization and performance that you should be aware of if you're dealing with larger OLTP-style loads.


Locking in "Read Committed Snapshot" If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending

eg:

update table1 set col1 = col1 + 1 where PKcol < 3
update table1 set col1 = col1 + 1 where PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3
update table1 set col1 = col1 + 1 where NonPKcol = 3
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

In above case PKcol is primary key in table and NonPKcol is a non-primary key.

Will updates be locked for only the rows satisfying the 'where' condition? Is it based on value or index or primary column?

The exact behaviour of concurrent modifications depends, in part, on the access path chosen by SQL Server to locate records to change.

If SQL Server uses the clustered index to locate data to change, locks will generally be taken on clustered index keys (and/or pages, etc.). If SQL Server locates rows to change using a nonclustered index, locks will be taken on the nonclustered index. In both cases, exclusive locks are also generally taken on the clustered index before the modification is actually performed.

Anticipating locking behaviour can be a fun and educational exercise, but it often the wrong question to ask. If you are experiencing blocking on locks where it does not seem necessary, then it might be the right question, but it is a very advanced one, requiring detailed internal knowledge to fully explain. I'll show an example based on your data later on.

Most often, the real question is "which isolation level should I use?". Locks are an implementation detail, used to provide the guarantees offered by the various isolation levels. The guarantees are the important thing. You should understand the different behaviours that are possible under each isolation level, and then make an informed choice. Please refer to that link for all the details.

When modifying data, RCSI behaves the same as standard READ COMMITTED. It will block if it needs to read something that is currently locked by another session's uncommitted changes. Once the blocking session commits or rolls back its changes, the blocked update continues, reading the committed values present at the time the blocking lock was released. This behaviour is required to prevent "lost updates", which are not allowed under any isolation level supported by SQL Server.

The following demo shows that the precise blocking behaviour depends on which locks are needed according to the query plan selected by the optimizer. In some cases, the update will block, in other cases, it will not. SQL Server always respects the guarantees provided by the user's isolation level, regardless of the implementation-defined locking behaviour.

Test Table and Data

CREATE TABLE dbo.Table1
(
    PKcol integer PRIMARY KEY,
    NonPKCol integer NULL UNIQUE,
    col1 integer NULL
);

INSERT dbo.Table1
    (PKcol, NonPKCol, col1)
VALUES
    (1,1,0),
    (2,2,0),
    (3,3,0),
    (4,4,0),
    (5,5,0);

Uncommitted Update

On a separate connection, run:

BEGIN TRANSACTION;
UPDATE dbo.Table1 SET NonPKCol = 997 WHERE PKcol = 3;
UPDATE dbo.Table1 SET NonPKCol = 998 WHERE NonPKCol = 3;
UPDATE dbo.Table1 SET NonPKCol = 999 WHERE NonPKCol = 5;

Note the lack of a COMMIT or ROLLBACK TRANSACTION.

Test Results

-- (1) Succeeds (no conflicting locks encountered)
update table1 set col1 = col1 + 1 where PKcol < 3

-- (2) Waits for an X lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where PKcol = 3

-- (3) Waits on U lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3

-- (3) Succeeds when read access is by NONCLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(2)) where NonPKcol < 3

-- (4) Blocks on U lock for NONCLUSTERED index key NonPKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol = 3

-- (5) Blocks on U lock for nonclustered index key NonPKcol = 5
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

-- (5) Succeeds when access is by CLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(1)) where PKcol < 3 and NonPKcol = 5


Use SQL Server Database Mail with Amazon SNS Is it possible to configure SQL Server 2012 Database Mail (hosted on EC2) with Amazon SNS?

I'd love a single place to manage all alert notifications and their subscribers in SNS, but Database Mail requires SMTP account information which SNS doesn't seem to provide, but I'm not sure.

I have been away from SQL Server for over a decade but will try to answer this based on my familiarity with what SNS is(n't).

SNS is able to fan-out a notification to multiple destinations over a number of outbound transports:

email (raw, message body only)
email (json, with metadata about the SNS message in addition to the body)
http or https to a web endpoint
SMS to a mobile device
SQS, publishing the message to Amazon Simple Queue Service queue(s)

However, it only has one inbound transport: a web API, so it isn't exactly an email distribution management platform though it does have portions of that functionality.  As such, you wouldn't be able to submit messages directly into SNS from an SMTP client.

If there is a mechanism by which you could publish the notifications with an http client within SQL Server, then you could potentially wire this in... but otherwise, you'd need to find or write an SMTP-to-SNS gateway.

Such a "gateway" would emulate an SMTP server on the front-end and an SNS API client on the back-end, to bridge this gap.  I've written one of these in Perl as a proof-of-concept, but it's not in production yet and it has a lot of hard-coded proprietary assumptions, so wouldn't be particularly useful outside of my specific environment -- but is was not particularly difficult to implement.  The message ID returned by SNS is handed back to the SMTP client as the SMTP transaction id.  (This implementation assumes the message is being submitted by a smart host, and puts the burden on the SMTP client, returning a 4XX error if for any reason the SNS API interaction fails -- accepting emails from SQL Server, you might need to shift that burden onto the gateway.)

(SNS can also send addressible push notifications to mobile applications, but this is a very different application of SNS, and uses a different API, but also doesn't accept inbound SMTP).


Query to check for free disk space and send an alarm if the disk space exceeds threshold value I would like to create a job which sends an email alert when my server disk space goes beyond a specific threshold value.Since my data files are bigger there is very little free space on disk partitions.

  Is is possible to write a Query which checks all the disks for the
  free disk space and give an email alert if the disk space goes below
  the set threshold value?

Check out this article:
Disk Space Monitoring: How To

And do not forget to monitor other crucial metrics: log file usage, cpu, blocking and so on...


How to store bitcoin value and other currencies values together in MySql database? I need to store bitcoin value and other currencies values together , I have a decimal column (16,8) for amounts. It behaves normally for bitcoin values , but when I want to store other currencies like Usd or Eur It becomes like this eg. 100 Usd becomes 100.00000000 .  0.50 Usd becomes 0.50000000

My question is should I store bitcoin values and other curriencies values in same column?
I will have millions of rows, is it bad for performance (read , write , count ) or this is just cosmetic issue?

As far as you use a DECIMAL(16, 8) field, values are stored with 6 decimal digits. It doesn't matter if they are bitcoins, euros, dollars, ...

If you want to use a monetary type, then you should take care and properly configure the server just to hold all values.

  8.2. Monetary Types

  The money type stores a currency amount with a fixed fractional
  precision; see Table 8.3. The fractional precision is determined by
  the database's lc_monetary setting. The range shown in the table
  assumes there are two fractional digits. Input is accepted in a
  variety of formats, including integer and floating-point literals, as
  well as typical currency formatting, such as '$1,000.00'. Output is
  generally in the latter form but depends on the locale.

CREATE TABLE Amounts(ID int, Value decimal(16,8), Value2 money);

INSERT INTO Amounts VALUES
(1, 100, 100), (2, 12.22636, 12.22636), (3, 0.1, 0.1);

SELECT * FROM Amounts;

✓

3 rows affected

id |        value |   value2
-: | -----------: | -------:
 1 | 100.00000000 | £100.00
 2 |  12.22636000 |  £12.23
 3 |   0.10000000 |   £0.10

dbfiddle here


SQL Server 2014 schema changes in 24/7 multi user environment We have SQL Server 2014 Enterprise installed to run a database which should be available 24/7. Our database is huge enough (200gb +). Also we have a lot of services that hits our database every minute to read, update or insert new data. We want to provide a "hot" redeploy feature for our clients and make our daily updates (.net and schema updates) transparent to clients. We have found a solution based on cluster with load balancer to update binaries of our app, but we still have some misunderstanding about database`updates deployment process and what are the best practices to solve this issue.

For schema changes, bring one server down, apply schema changes, bring it back up, and then apply same changes to the second instance.
Can it be accomplished with SQL Server tools, and is this a common approach? How sync data after server is backed up? Or am i completely thinking into the wrong direction and are there better solutions?

Our common Schema changes: add/drop column, add/delete stored procedure

Below will require a bit of more planning and testing.

Blue-Green Concept :

The gist of Blue-Green Concept is to divide your production into 2 environments and they are identical all times (data synchronization) wherein

The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment.
At the same time Green will be your staging/testing environment wherein you will upgrade your schema/build or product to the NEXT release, do a full regression test and get signed off by your business users. Once happy, during a cut-over period, you will promote the Green to be your "LIVE" environment and demote the Blue to be a preprod/staging or testing for the next release.

This way, you have a very less downtime and the risk of deployment failure on a live system (which is in maintenance window, since you are doing upgrade) will be highly minimized. Also, following the Blue-Green approach, you will be oscillating between LIVE and PREVIOUS version which will be staging for the next version.

Again, this will require more hardware/licensing as well as planning and testing.

Most of the steps can be automated using DACPACs and PowerShell. Also, if you are installing multiple instances on one server, make sure to re-balance the Memory settings when switching between Blue and Green. The LIVE environment gets more memory than the Passive environment.

In my current environment, we have implemented Blue/Green Model for Agile Code Deployment that allows us to promote code every 2 weeks with ample amount of time for testing and business sign-off. Also, its a breeze to rollback in case something goes horribly wrong. We have automated majority of the deployment stuff using Dacpacs and PowerShell.

(Image source)

Also refer to Grant Fritchey's article on Rollback and Recovery Troubleshooting; Challenges and Strategies


SQL Join to put rows into columns I have two Tables
TABLE 1
ID       Name
1001     XYZ
1002     ABC

TABLE 2
ID    GAME
1001   A
1001   B
1002   A

I want the outcome of two tables join as
ID   NAME   GAME1    GAME2
1001 XYZ     A        B
1002 ABC     A

I tried with a simple join and it gives me multiple rows for 1001 and 1002
Query
SELECT t1.ID, t1.Name, t2.Game
FROM t1, t2
WHERE t1.ID = t2.ID

1001  A
1001  B
1002  A

NA

Actual O/P
1001  A
1001  B
1002  A

Expected O/P
ID   NAME   GAME1    GAME2
1001 XYZ     A        B
1002 ABC     A

Here is one of the ways to do it!

First you need to convert the t2 table as columns provided you are certain about the values of the game.

SELECT
    max(CASE WHEN game = 'A' THEN game END) GAME1,
    max(CASE WHEN game = 'B' THEN game END) GAME2,
    ID
FROM T2 GROUP BY ID

This will give you something like

 game1 game2   ID
  A      B    1001
  A     NULL  1002

Now join this with your t1.

SELECT t1.id, t1.name, S1.GAME1, COALESCE(S1.GAME2, '') FROM t1
    JOIN (SELECT
                max(CASE WHEN game = 'A' THEN game END) GAME1,
                max(CASE when game= 'B' THEN game END) GAME2,
                ID FROM T2 GROUP BY ID) S1
        ON t1.id = S1.ID

Keep in mind that this will be more lengthy when the game types increase.


Backing up a single node Percona XtraDB Cluster I have a single node percona XtraDB Cluster.

Version:
Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061

All tables in the DB are using InnoDB engine and the database is partitioned.

The server is a RHEL 6 server and I want to upgrade it to RHEL 7. What I am planning to do is, stop mysql on the old server, shut it down, disconnect the disk (old and new servers are VMs), connect it to the new RHEL 7 server, turn the new server on, mount the disk, change mysql configuration so that it will point to the new filesystem. Sounds good on paper but will this actually work?

Well, I've tested this and the answer is yes. One small thing to keep in mind is that in RHEL 6 default filesystem is EXT4 while in RHEL 7 it is XFS and you will be mounting an EXT4 filesystem to a RHEL7 machine.


Portable client tool to connect to SQL Server Express LocalDB and execute a query I am running out of ideas on this little problem:

I need to execute a query on a LocalDB database directly on user's computer. I  do not want to install anything there and there is only .NET (4.5.2) and SQL Server 2014 Express LocalDB installed.

I do not want to copy database file to my computer and back againt with a little modification, I need to run just one or two queries directly on that machine.

Is there any portable command line tool that would allow me to do this? SQLCMD is not usable, since it requires installation of ODBC driver, SSMS requires installation too.

I suggest just writing a simple .NET console app to do just that. It can accept parameters for "ConnectionString" and "Query". This will be flexible enough to use in various situations.

using System;
using System.Data;
using System.Data.SqlClient;

namespace SimpleSqlCmd
{
    class Program
    {
        static void Main(string[] args)
        {
            using(SqlConnection _Connection = new SqlConnection(args[0]))
            {
                using(SqlCommand _Command = new SqlCommand(args[1], _Connection))
                {
                   _Connection.Open();

                   _Command.ExecuteNonQuery();
                }
            }

            return;
        }
    }
}

Usage would be:

SimpleSqlCmd "Server=(localdb)\InstanceName;" "Do some SQLs;"

UPDATE:

I have created a project on GitHub to host a more functional / complete version of the code shown above. The code on GitHub will be updated over time whereas the code here will not be updated (since that is really not the purpose of this site). The repository URL for Simple SQL Exec (renamed, to avoid confusion, from "SimpleSqlCmd" as it is shown above):

https://github.com/SqlQuantumLeap/SimpleSqlExec


What is the best way to automatically create a snapshot of the table at the certain time? I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts).

What is the best way to do it?

Edited: Table that I have to snapshot has about 50.000.000 records. Only 5-20% of them are changing over the day.

Added: Actually I have one idea to add insert/update trigger for each table that should be shapshoted and write all changes to another table that is partitioned on daily basis.

Please tell me if it is a god idea or not?

I'd use either of pg_dump or create table foo_log as select * from foo

Added: Actually I have one idea to add insert/update trigger for each table that should be shapshoted and write all changes to another table that is partitioned on daily basis.
Please tell me if it is a god idea or not?

Sounds good too. That works with rules too, e.g.:
create rule as on insert to yourtable_log__ins
do also
insert to yourlogs select new.*;

And then, daily:
begin;
create table yourlogs_[date] () inherits (yourlogs);
create or replace rule yourlogs__ins as on insert to yourlogs
do instead
insert into yourlogs_[date] select new.*;
commit;

The main difference with creating the table on the fly on a daily basis will be the overhead that you'll be introducing on every insert/update, as opposed to grabbing the updated rows all in one go.


Issue with one query - can some Index help to make it faster? I am working on some IBM web application and it has one query hard coded in some part of the application which I cannot change or optimize  - so modification of the SELECT statement is not possible.
This is DB2 database.

Just to mention one important thing that query always returns 0 or 1 ROW!!!

Query is:

select *
from workorder
where parent= '682845'
and siteid= 'NY'
and woclass in ( 'WORKORDER' )
order by wosequence, taskid

Can someone please help me with definition of the proper index for this because this query is slowing the application too much and customer has big complaint.

I am not sure if I create index with three columns PARENT,SITEID,WOCLASS it will help because of the order by on which I do not have influence_
Thanks

A 3-column index on (parent, siteid, woclass) will most probably improve the efficiency of the query.

It will certainly help to avoid the table scan. Most likely, with conditions on 3 columns, the rows that match will be very few or at least a very small percentage of the table and an index seek will be far more efficient than a table scan.

Even better, if the number of rows is small, the required sort will be efficient as well so you won't need to include the order by columns in the index (which may not be an option anyway, as the order by could be arbitrary, for all we know). If the query always returns 1 row, as you say, you certainly don't need the additional columns in the index. Why is there an ORDER BY in the query, if that's the case?

So, test the behaviour (of the query but also the overall behaviour of the applications and other queries) in your production/stage environment after you add the index and if the efficiency gain is good enough, roll the change in production.

(By the way, I'm not 100% sure about DB2 but you don't need to stop anything to add an index in most DBMS, I don't think you'd need it in DB2 either. You might get some locking off course and applications could be interrupted due to the locks, especially if the table is big, and various DBMS have different options to address these side effects. So it might be good to add the index in some "quiet" period, say between 04:00 and 06:00 that you know the database / applications have the least usage.)


What does column family-oriented datastore mean in practice I was reading this article about HBase. Within it, the storage format of HBase is described:

HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed by a key that you can use for lookup

So what does column family oriented mean? Is it the same as column oriented? In that case, how can it be both column family oriented and row oriented?

So row oriented storage is like this:

001:10,Smith,Joe,40000;
002:12,Jones,Mary,50000;
003:11,Johnson,Cathy,44000;
004:22,Jones,Bob,55000;

Column oriented storage is like this:

10:001,12:002,11:003,22:004;
Smith:001,Jones:002,Johnson:003,Jones:004;
Joe:001,Mary:002,Cathy:003,Bob:004;
40000:001,50000:002,44000:003,55000:004;

So how is data actually stored in HBase? Do you have example?

Column families in HBase are a method of "vertical partitioning" of an HTable, which separates subsets of the table columns into distinct physical structures. This improves query performance (via reduced I/O requirements) if your use cases frequently access certain groups of columns together.
Within each column family data are stored in a row-oriented manner. So, assuming that in your example "001".."004" are the record keys, and two column families, the data would be stored somewhat similar to this:
CF1                     CF2
|Key|First|Last   |     |Key|Band |Salary |
|---|-----|-------|     |---|-----|-------|
|001|Joe  |Smith  |     |001|10   |40000  |
|002|Mary |Jones  |     |002|12   |50000  |
|003|Cathy|Johnson|     |003|11   |44000  |
|004|Bob  |Jones  |     |004|22   |55000  |

Thus, if you only needed to calculate the average salary, you would read a smaller file without unnecessary data in it.
More about HBase record layout.


What is the algorithm used for Index Recommendation in SQL Databases? I have used tuning adviser tool in SQL Server which provide index recommendation based on a given workload.

I want to create a similar tool for PostgreSQL. I went through some research papers. But I am unable to find the exact algorithm used by SQL Server Tuning Adviser to provide index recommendations.

If anyone is aware of the algorithm or any source from where I can get the algorithm please guide me. Please note that I want to provide recommendation based on a workload, not for a single query. Thanks in advance.

The database tuning advisor exact algorithm isn't documented, but you can have somewhat of a look how it works by using a server side trace to look at the commands it generates.

I'm not sure what algorithm it uses to determine which ones to create but it relies heavily on  creating hypothetical indexes and then using the undocumented DBCC AUTOPILOT to create execution plans that use these hypothetical indexes.

I suppose it then somehow compares the resulting execution plans to determine which ones have the lower cost.

Daniel Farina has an excellent post explaining how it works and how you can do it by hand if you really want to: SQL Server Performance Tuning with Hypothetical Indexes.

From that page:

  You are tuning a query and need to check the performance impact of a
  new index. But the table has millions of rows and it will take a lot
  of time to create an index. Keep reading and you will see how to
  create and test hypothetical indexes with the undocumented DBCC
  AUTOPILOT command.

and

  Since this is an undocumented feature there is not information about
  it but, as far as I can tell, giving the fact that AUTOPILOT is the
  key component of Database Engine Tuning Advisor (DTA); this should
  work on all version in which DTA is available.


Speed efficient query for membership first joined, latest category from membership table (min, max) I have the following table representing membership information:

    CREATE TABLE IF NOT EXISTS `membership` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `organisation_id` int(11) NOT NULL,
      `membership_subcategory_id` int(11) NOT NULL,
      `start` datetime DEFAULT NULL,
      `end` datetime DEFAULT NULL,
      `amount` decimal(9,2) DEFAULT NULL,
      `amount_paid` decimal(9,2) DEFAULT NULL,
      `notes` mediumtext,
      `order_id` int(11) DEFAULT NULL,
      `payment_type` varchar(20) NOT NULL,
      `active` tinyint(4) NOT NULL DEFAULT '1',
      `cancelled` tinyint(4) NOT NULL DEFAULT '0',
      `cancelled_date` datetime DEFAULT NULL,
      `cancellation_reason` mediumtext,
      `certificate_sent` date DEFAULT NULL,
      `welcome_email_sent` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`),
      UNIQUE KEY `order_id_2` (`order_id`,`start`,`end`,`organisation_id`),
      KEY `membership_subcategory_id_idx` (`membership_subcategory_id`),
      KEY `organisation_id_idx` (`organisation_id`),
      KEY `order_id` (`order_id`)
    )

organisation_id is a member
the membership year goes from 1 Jul to 30 Jun, start records when the membership in each year has started - this may be anywhere in the first year, but then its always 1 Jul unless a year is skipped
membership_subcategory_id is an industry category the membership applies to for each year.

I need an efficient query to get the date joined and latest membership category.

I've tried this query, but I get "Invalid use of group function" as an error

SELECT m.organisation_id, m2.membership_subcategory_id, MIN( m.start )
FROM membership m
INNER JOIN membership m2 ON m.organisation_id = m2.organisation_id
WHERE MAX( m.start ) = m2.start
GROUP BY m.organisation_id, m2.membership_subcategory_id

Not sure if I understand your requirements correctly. This query will return, for every organization, the minimum start date and the latest membership_subcategory_id (when the order is by start):

SELECT    organisation_id,
            MIN(`start`) AS
          first_start_date,
            ( SELECT    m2.membership_subcategory_id
              FROM      membership AS m2
              WHERE     m2.organisation_id = m.organisation_id
              ORDER BY  m2.`start` DESC
                LIMIT 1
            ) AS
          last_membership_subcategory_id
FROM      membership AS m
GROUP BY  organisation_id ;

The subquery will be executed as many times as the number of different organizations in the table, so efficiency depends on that. An index on (organisation_id, start, membership_subcategory_id) would help minimizing the subquery execution time.

Using a derived table:

SELECT    gm.organisation_id,
          gm.first_start_date,
          m.membership_subcategory_id AS last_membership_subcategory_id,
          m.*                           --- whatever other data you want
                                        --- from the latest `start` date
FROM      ( SELECT    organisation_id,
                      MIN(`start`) AS first_start_date,
                      MAX(`start`) AS last_start_date
            FROM      membership
            GROUP BY  organisation_id
          ) AS gm
    JOIN
          membership AS m
              ON  m.organisation_id = gm.organisation_id
              AND m.`start` = gm.last_start_date
  ;


Change IP of nodes in Oracle RAC I have a two node RAC database running in OEL 7 server. If there is a need to change the IP addresses of the already running server to a new one, will that hamper the functioning of the database?

What changes on the database front would I need to incorporate to implement this?

Is it even possible in the first place to run the database after changing the IPs?

The database does not care about the IP addresses. Grid Infrastructure does. The method to change the IP addresses can be found in detail in the below MOS notes:

How to Modify Public Network Information including VIP in Oracle Clusterware (Doc ID 276434.1)

How to Modify Private Network Information in Oracle Clusterware (Doc ID 283684.1)


Why and how sometimes I get the "absent table error" while there are definitely Sch-M lock? I have a synthetic test, which reproduces some error we have on the production environment. Here are the 2 scripts to reproduce it:

1st

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 ROLLBACK
IF object_id('test') IS NOT NULL
    DROP TABLE test
IF object_id('TMP_test') IS NOT NULL
    DROP TABLE TMP_test
IF object_id('test1') IS NOT NULL
    DROP TABLE test1
CREATE TABLE test(Id INT IDENTITY PRIMARY KEY)
GO
INSERT test DEFAULT VALUES
GO 2000
WHILE 1 = 1
BEGIN
    CREATE TABLE TMP_test(Id INT PRIMARY KEY)
    INSERT TMP_test SELECT * FROM test

    WAITFOR DELAY '0:00:00.1'
    BEGIN TRAN

    EXEC sp_rename 'test', 'test1'
    EXEC sp_rename 'TMP_test', 'test'
    EXEC sp_rename 'test1', 'TMP_test'

    DROP TABLE TMP_test
    commit
END

2nd

SET NOCOUNT ON;

DECLARE @c INT
WHILE 1 = 1
BEGIN
    SELECT @c = COUNT(*) FROM Test IF @@ERROR <> 0 BREAK
    SELECT @c = COUNT(*) FROM Test IF @@ERROR <> 0 BREAK

    /* and repeat this 10-20 times more*/
    SELECT @c = COUNT(*) FROM Test IF @@ERROR <> 0 BREAK
END

So, the problem is that when I run the 1st script in one session and leave it running, and then run the 2nd in separate session I can get this type of error:

  Msg 208, Level 16, State 1, Line 13 Invalid object name 'Test'.

The question is - WHY i see this error in case when there is a COMMIT in the cycle end of the 1st script and never get one if there is a ROLLBACK?

I have a feeling, that it is somehow connected to the situation, when the script commits, there is still the table with the name test but it is a different object and the 2nd script have to recompile itself. And this is OK. But why it gets the missed table error? AFAIK - when I rename the table inside a transaction - it holds Sch-M lock to the tran end?

Can somebody answer or guide me to the tech papers I can read deeply and understand the reason?

Really interesting and tough question.  I was not able to find any official documentation of this behavior, and I suspect there may not be any (though I'd love it if someone corrects me on that!).

My research leads me to believe that it is the plan compilation step that is vulnerable to this race condition.  Note that I was able to run your test queries for an hour with no errors, but if I repeatedly kick kick off your process, I get an immediate error a portion of the time.  When it does hit an error, it always does so right away on plan compilation.  Alternatively, you can add "OPTION RECOMPILE" to the COUNT(*) in the loop, forcing a new plan to compile at each trial.  Using this approach, I see the error almost immediately every time I have run your scripts.

I was able to reproduce the error with a controlled series of steps that seems to hit the error on every trial, removing the need to set up a loop and rely on randomness.

I also threw in a potential fix (to use ALTER TABLE...SWITCH) which might be possible to try out in your production environment.  Now, on to the details!

Here are the steps to reproduce:

-- Instructions: Process each step one at a time, following any instructions in that step

-- (1) Initial setup: Clean any relics and create the test table
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 ROLLBACK
IF object_id('test') IS NOT NULL
    DROP TABLE test
IF object_id('TMP_test') IS NOT NULL
    DROP TABLE TMP_test
IF object_id('test1') IS NOT NULL
    DROP TABLE test1
CREATE TABLE test(Id INT IDENTITY CONSTRAINT PK_test PRIMARY KEY)
GO
INSERT test DEFAULT VALUES
GO 2000

-- (2) Run the COUNT(*)
-- This will acquire the Sch-S lock, and we use HOLDLOCK to retain that lock.
-- This simulates a race condition where this query is running at the time the first rename occurs.
BEGIN TRANSACTION
SELECT COUNT(*) FROM Test WITH (HOLDLOCK)
GO

-- (3) In another window, run the block of code that performs the renames
-- This will be blocked, waiting for a Sch-M lock on "test" until we complete step 4 below
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
EXEC sp_rename 'test', 'test1'
EXEC sp_rename 'TMP_test', 'test'
EXEC sp_rename 'test1', 'TMP_test'
DROP TABLE TMP_test
GO

-- (4) Now, commit the original COUNT(*) and immediately fire off the query again
-- We use OPTION (RECOMPILE) to make sure we need to compile a new query plan
-- The COMMIT releases the Sch-S lock, allowing (3) to acquire the Sch-M lock
-- This batch will now be waiting for the Sch-S lock again, on the same object_id,
-- but that object_id will no longer point to the correct object by the time the lock
-- is acquired.
COMMIT
SELECT COUNT(*) FROM Test OPTION (RECOMPILE)
GO

Using these steps, we can dig in a little bit more to what is causing the error.  To do so, I ran a trace with the following events on: SP:StmtStarting, SP:StmtCompleted, Lock:Acquired, Lock:Released.

What I found is that the following occurs, in order (but omitting some detail in between):

The first COUNT(*) acquires a Sch-S lock
The first COUNT(*) acquires some locks on statistics (presumably to build a query plan)
The first COUNT(*) releases a Sch-S lock (end of plan compilation)
The first COUNT(*) acquires a IS lock (beginning of execution), then runs successfully
The sp_rename runs up to the point where it needs to take a Sch-M lock, then is blocked by the first COUNT(*), which is not yet committed
The first COUNT(*) is committed
The sp_renames acquires the Sch-M lock
The second COUNT(*) requests a Sch-S lock and is added to the blocking chain behind the sp_rename
The sp_renames complete (at this point, the second COUNT(*) is waiting on a Sch-S lock for an object_id that is no longer the correct object_id)
The second COUNT(*) acquires the Sch-S lock
The second COUNT(*) acquires a Sch-S lock on sys.sysschobjs, presumably as a sanity check because it has detected (or needs to confirm) that something is amiss with Sch-S lock it was granted on the object_id that used to be "test"
The Invalid object name 'Test' error is thrown

However, if a similar sequence of events occurs when a plan compilation is not needed (such as when running your loop), SQL Server is actually smart enough to detect that the object_id has changed.  I also traced that case, and I found a situation where the second COUNT(*) performed the following sequence

Acquires the Sch-S lock (on the object_id that used to be "test")
Acquires a Sch-S lock on sys.sysschobjs
Releases both of these locks
Acquires a IS lock on a different object_id; the new object_id of "test"!
Runs successfully to completion

So it does look like this adaptive logic is in place, as you hypothesized.  But it looks like it may be in place only for query execution, and not for query compilation.

As promised, here is an alternative snippet for section (3) that appears to provide a way of renaming tables that resolves the concurrency problem (at least on my machine!):

-- (3) In another window, run the block of code that performs the "renames"
-- Instead of sp_rename, we use ALTER TABLE...SWITCH
-- This appears to be a more robust way of performing the same logic
CREATE TABLE test1(Id INT PRIMARY KEY)
CREATE TABLE TMP_test(Id INT PRIMARY KEY)
INSERT TMP_test SELECT * FROM test
ALTER TABLE test SWITCH TO test1
ALTER TABLE TMP_test SWITCH TO test
ALTER TABLE test1 SWITCH TO TMP_test
DROP TABLE TMP_test
DROP TABLE test1
GO

Lastly, here is one other link that I found useful in getting my head wrapped around how to think about locking in the context of tables whose names are changing:

http://www.sqlnotes.info/2011/12/02/sp_rename-causes-lock-leaking/


Database backup: NT AUTHORITY\\SYSTEM is not able to access the database When trying to automatically backup a MsSQL database on a Windows 2012 Server, the following exception is thrown, and hence the backup is not taken:

  Description: Error encountered during backup. Error: [Microsoft.SqlServer.Management.Common.ExecutionFailureException:The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "TestDB" under the current security context. BACKUP DATABASE is terminating abnormally. An exception occurred while executing a Transact-SQL statement or batch.]

We had 2 instance and NB MSSQL Job was failing and we just added (NT AUTHORITY\SYSTEM) under sysadmin in security roles and restarted the Netbackup services. NB SQL Jobs Started working.


Find invalid objects in database? I'm playing around with PostgreSQL and would like to validate that everything is valid after migrating a data model.

In: how-to-show-all-invalid-objects-in-postgresql it is stated that only indexes need to be checked, but I noticed that I can create a function that references a table that does not exist:

CREATE OR REPLACE FUNCTION DUMMY()
RETURNS INT AS $$
BEGIN
    return( select count(1) from table_that_does_not_exist );
END;
$$
LANGUAGE plpgsql;

In the same fashion, I can create a valid function and drop the dependent table without a complaint

Is there a way to detect that a function is in an invalid state, preferably from SQL? Is there other things that can be invalid in the same way (and how to detect that)?

While I'm at it, In Db2 a table has an internal counter that is increased for certain operations (drop column for example). When this counter reaches a certain value (3?) the table becomes in-accessible and needs to be reorganized (vacuum seems to be a similar operation in Postgres). Can a table in Postgres become invalid after a certain number of "dangerous" operations, if so, how would one detect that a table is about to enter an invalid state?

I guess what I'm trying to find out how I can detect if there are any invalid objects in the database before discovering it in run-time.

Is there a way to detect that a function is in an invalid state,
  preferably from SQL

For plpgsql, that's the purpose of the plpgsql_check extension.

Otherwise, functions (and now procedures too) can be written in all kinds of languages, so the job of parsing the body of functions is offloaded to these languages. PostgreSQL itself can't know when a function refers to any particular database object: it can't even parse the function's body to start with.

Also, it's arguable that code referencing a not-yet-existing table is necessarily invalid. It may be a temporary table that is meant to always be created before calling the function.

  Can a table in Postgres become invalid after a certain number of
  "dangerous" operations, if so, how would one detect that a table is
  about to enter an invalid state?

When a column is dropped, it's marked as dropped but still uses a slot in pg_attribute and in the heap. If columns are constantly dropped and new columns are constantly added to a table, at some point a row would try to grow bigger than a page, which is forbidden, even though its number of live columns would be reasonable and would cause no problem in a new table. So in a way, you could say that drop column / add column are operations that can make a table unusable, when used recklessly.

There are certainly other corner cases like that, and every case would need its own detection mechanism.

  I guess what I'm trying to find out how I can detect if there are any
  invalid objects in the database before discovering it in run-time.

Framed like that, the question looks too general. In general, objects don't have  a valid flag, with some exceptions such as indexes ith pg_index.indisvalid