postgres: "alter default privileges" issue I am implementing a schema access model on postgres (I know oracle). Using guidance from http://bonesmoses.org/2016/02/26/pg-phriday-corralling-the-hordes/ I created a data owner account and two roles (_ro and _rw), and a second user account which is granted the _rw role.
I created a table in the Owner account which I expect to be automatically accessible via the roles because I issued a "ALTER DEFAULT PRIVILEGES" command to automatically issue GRANTs in future-created tables. However, when I log in as the user account, a select on the table returns "ERROR:  permission denied for relation checklist1new"

Create the Owner account

CREATE   USER access1 WITH PASSWORD 'whatever';
CREATE SCHEMA access1 AUTHORIZATION access1;
ALTER USER access1 SET search_path = access1;
ALTER SCHEMA access1 OWNER TO access1;

CREATE GROUP access1_ro;
CREATE GROUP access1_rw;

GRANT USAGE ON SCHEMA access1 TO access1_ro;
GRANT USAGE ON SCHEMA access1 TO access1_rw;

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT SELECT ON TABLES TO access1_ro;

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1
GRANT USAGE ON SEQUENCES TO access1_rw;

ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

CREATE TABLE TEST_TABLE_access1(col1 integer);

Create the user account

CREATE USER user1 WITH PASSWORD 'whatever';
GRANT CONNECT on DATABASE rwinkwavu to user1;
GRANT access1_rw to user1;

Attempt to query the table, logged in as user1

select * from access1.TEST_TABLE_access1
ERROR:  permission denied for relation TEST_TABLE_access1

I expected this query to return 0 rows. What am I doing wrong?
Postgres 9.4 on macOS.

Either you've forgot to add a permission to SELECT in the GRANT below, quoted from the question:

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

... or, if you really meant that having the role access1_rw doesn't let  read the tables even tough it lets write to them, then you must grant the role access1_ro to user1 (in addition to granting the role access1_rw).

Not having either of those has the consequence that user1 was never granted the right to SELECT from tables in the mentioned schema.


Is MySQL Cluster just for in-memory databases? In MySQL website I see below definition for MySQL Cluster.

  MySQL Cluster is a technology that enables clustering of in-memory
  databases in a shared-nothing system.

I want to use Clustering for a chat application like viber that there are some Ejabberd servers, which use MySQL as database.Is it necessary that  databases use in-memory tables. according this definition all databases and tables should be in-memory or not?

This is the link of MySQL Cluster definition.

In NDB Cluster, data does not have be memory-resident, but I think the indexes do.

This technology is good for high availability and high activity.  It is not for the typical RDBMS application.

NDB's Replication is based on "eventual consistency", where in you can do conflicting inserts/updates, but define the rules of which one wins.

While it is closely related to the rest of MySQL, NDB Cluster is not identical; there will be some coding changes if you migrate.


Rolling Back DB Changes After Deployment Edge Cases I manage a mixed DB environment and our programmers could use some good rollback options.  I was thinking of using SQL Compare, Red Gate Source Control, and a few other tools but I can't figure out what the best way would be to ensure rollbacks with minimal data loss.

Assuming we're in edge cases such as when a foreign key is added to another table which gets populated with customer data, and we need to roll back changes to the primary table.  What are some of the industry standard best practices to ensure smooth rollbacks outside of transaction log restores, snapshot restores, redgate source control, etc. to deal with these harder to recover from scenarios?

Thanks!

Well I think that having a test system that supports the workload, data and edge cases you have in production should mitigate the need to roll back. If you can properly test the deployment in a similar system, you should have very few scenarios where you have to roll back.

That said, you can also do many things to make your code changes backward compatible. I wrote a series of tips on this a while back, including one where I change a relationship:

http://www.mssqltips.com/sqlservertip/2074/make-your-sql-server-database-changes-backward-compatible-when-changing-a-relationship/

http://www.mssqltips.com/sqlservertip/2071/make-your-sql-server-database-changes-backward-compatible-when-renaming-an-entity/

http://www.mssqltips.com/sqlservertip/2050/make-your-sql-server-database-changes-backward-compatible-when-dropping-a-column/

http://www.mssqltips.com/sqlservertip/2035/make-your-sql-server-database-changes-backward-compatible-when-adding-a-new-column/

Every application is different, so you're not going to find many "industry standard" solutions to this. But you should be able to whip together plenty of common sense that will reduce or eliminate your need to worry about it.

And no, I'm not saying you shouldn't have a rollback plan, but when changes are properly planned and tested, the rollback plan shouldn't require a significant effort to devise, and should rarely - if ever - be used.


How to make 6 partitions of table by 6 months and 7th partion of older than 6 month data? we have a table structure with periodical data in a time-stamp column.
how to make 7 partition of this table with 7th portion as data older than 6 months.

I have already thought of running a cronjob and manually transferring the data to archive table if the above solution does not work out.

Possible, but efficiently.  Only works for PARTITION BY RANGE.

Let's say you are starting to populate partition "2106_09" and need to migrate data in partition "2016_02" to partition "Older".  The command would be something like

ALTER TABLE tbl
    REORGANIZE PARTITION `2016_02`, `Older`  INTO (
        PARTITON `Older`  LESS THAN ('2016-03-01')
                 );

(Caveat: check the syntax.)

Note that you will need to construct this with a different partition name and "less than" each month.

But the problem is that it will rebuild "Older", which will take an amount of time based on how big it is.

This . may have some tips and code that is useful.


Calling/Scheduling multiple SSIS jobs for ETL process - what is one fails? So I've got a procedure:

ALTER PROCEDURE [dbo].[use___MasterLoadAll]
AS
--Must run in order
EXEC [dbo].[use__MasterLoad.Staging];
EXEC [dbo].[use__MasterApplyBRs];
EXEC [dbo].[use__MasterLoad.pdw.*];
EXEC [dbo].[use__MasterLoad.FPDW_Cube];

My issue is if the Business Rules fail on the controller I'd like to stop the procedure - Here is what's in the EXEC [dbo].[use__MasterApplyBRs]

ALTER PROCEDURE [dbo].[use__MasterApplyBRs]
AS
EXEC [dbo].[usp_ApplyDMBusinessRules];
EXEC [dbo].[usp_ApplyIndirectBusinessRules];

Then EXEC [dbo].[usp_ApplyDMBusinessRules]

ALTER PROCEDURE [dbo].[usp_ApplyDMBusinessRules]
AS
-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'DMController.dtsx', @execution_id=@execution_id OUTPUT,
@folder_name=N'StagingImport', @project_name=N'BusRulesDirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr

-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync

-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)

Then EXEC [dbo].[usp_ApplyIndirectBusinessRules]

ALTER PROCEDURE [dbo].[usp_ApplyIndirectBusinessRules]
AS

-- Set up the exection
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'InDirectController.dtsx', @execution_id=@execution_id OUTPUT,
@folder_name=N'StagingImport', @project_name=N'BusRulesIndirect', @use32bitruntime=False, @reference_id=Null
DECLARE @DWConnStr sql_variant = N'Data Source=DWSERVER;Initial Catalog=DataWarehouse;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'ppDWConnStr', @parameter_value=@DWConnStr

-- System params
DECLARE @LOGGING_LEVEL smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@LOGGING_LEVEL
DECLARE @Sync smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=@Sync

-- Execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

IF EXISTS (select 1 FROM ssisdb.catalog.executions where execution_id = @execution_id AND status = 4)
RAISERROR('Start with Google; End with http://dba.stackexchange.com/questions/ask', 16, 1)

So finally each of those controllers have similar TSQL that calls a bunch of Business Rules Packages - but if anyone of those packages fails the Controller will continue and end reporting failure -

Therefore if a Controller reports failure within the Business Rules I need MasterLoadAll to stop - How?

Have you considered using a SQL job to run the packages?
If each package fails when an error occurs, you can have your job exit immediately.

Just an idea...


Contains statement Query I need to check the string with the column if the column contains that string or not.

example:String 'how to make pizza'  and the column if has 'pizza' i need to fetch that..
like

SELECT * FROM event WHERE ('%how to make pizza%' LIKE EventName);

any idea plz..

You have to take into consideration the Full-Text Search Functions

  Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

The initial setup and configuration is very simple


Multiple product attribute inventory database design I have a database to build based on inventory for my shop.   This is the current data that I have put into tables. This is the package:

This is the product attribute:

This is the table to produce

The problem is, each product has different packages. The data shown is the product that has 2 packages, "color" and "power".

So the question is:  how to do "color" and "power" as column on the database?
and what will happen if it has more than 2 packages?

I am using MySQL for this.

What I'm going to do is to track the quantity,

product-23 : white, 100 = 30qty
product-23 : white, 50 = 30qty
product-23 : black, 100 = 30qty

What I have is 2 tables "package" and "values", includes already have tables like product that has product id.

So whenever one user is trying to purchase product-23 : white, 100, it will check the quantity on the database.

Here is the fiddle

You have basically created a basic Key Value Store. See here for more details on that. You will need to make use of the PIVOT command to get the result you need. Check here for that.


WSREP: failed to report last committed and stop I have galera cluster and I have 4 nodes. I'm importing some data. however one node gives error. Warning. The warning is;

warning WSREP: Failed to report last committed 59102, -q (interrupted system call)
mysqld_safe Number of processes running now: 0
mysqld_safe WSREP: not restarting wsrep node automatically
mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What is that error? How can I solve this? This node shouldn't stop, not mysql service stopped in that error. Thanks

warning WSREP: Failed to report last committed

This is just a warning, and in itself isn't a cause for concern. I think it's unlikely that this caused the node to stop. See also Percona's blog post about it.

And then:

  mysqld_safe Number of processes running now: 0

Again, according to Percona (Support):

  Your mysqld process was killed, most likely by OOMkiller. Check the system log. Joining node will refuse to accept connections until it synchronizes with cluster.


Failed to insert records into table in different server using sql server agent My idea is to insert records from a table into another one in different server using T-SQL.

I've made maintenance plan along with its job and found below error.

  Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user
  'MANYAR\MIT-DB2$'. [SQLSTATE 28000] (Error 18456)Unable to open Step
  output file.  The step failed.

I suspect SQLSERVERAGENT from source server might not have permission in destination server.  I've tried to execute the T-SQL script in query and the scripts work well.

I believe you are seeing two different errors on your history report.

You should validate the step output file on the job step(s).
You should validate the linked server permissions & authentication.

Recreating the step output error

Testing with a location that does not exist

Create a jobstep that tries to access a step output folder location that does not exist

Regular T-SQL job steps might report the error but not fail the job, I used a maintenance plan to force the failure here

Start the job

Resulting error:

  Executed as user: AgentServiceAccount. Unable to open Step
  output file.  The step failed.

Testing with insufficient permissions

The same error is returned when no permissions are granted

  Executed as user: AgentServiceAccount. Unable to open Step
  output file.  The step failed.

Recreating the linked server permission error in combination with the step output error

Create a linked server

Authenticate with a non existing login

Create a job and jobstep to select from the newly created linked server

Point to a non existing output file

With the final resulting error on job execution matching yours

  Executed as user: AgentServiceAccount Login failed for user
  'nonexistinglogin'. [SQLSTATE 28000] (Error 18456)Unable to open Step
  output file.  The step failed.

Validate the job step output & the linked server permissions and authentication and you should be good to go.


The query processor could not produce a query plan I'm getting this message:

  Msg 8624, Level 16, State 17, Line 1
  Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

This is what I run

;WITH TableA AS
(
   SELECT 101 as A_ID
)
,TableB AS
(
   SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses
   UNION ALL
   SELECT 2 , 101  , 'YYY'
   UNION ALL
   SELECT 3 , 101  , 'ZZZ'
   UNION ALL
   SELECT 4 , 102  , 'AAA'
)
SELECT
    A_id
    ,x.x.value('(./text())[1]','varchar(500)') AS fieldX
FROM
    TableA AS A
OUTER APPLY
    -- CROSS APPLY
    (SELECT
         ',' + B_Courses
     FROM
         TableB AS B
     WHERE
            1=1
            AND B.B_A_ID  = A.A_ID
     FOR XML PATH(''),TYPE
    ) x(x)

With OUTER APPLY, I get the message shown above.

With CROSS APPLY, everything works nicely.

I resolve by adding in select this :

STUFF((SELECT ','+B_Courses
    FROM
         TableB AS B
    WHERE
        B.B_A_ID  = A.A_ID
    FOR XML PATH(''),TYPE).value('(./text())[1]' ,'VARCHAR(500)'),1,1,'')

Tested on:

Edition           ProductVersion    ProductLevel
Express Edition     9.00.3042.00    SP2

and

Edition                     ProductVersion  ProductLevel
Developer Edition (64-bit)  11.0.2100.60    RTM

Any idea way is  this happening?

Thanks

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is  no real need for the APPLY, eg

;WITH TableA
AS
    (
    SELECT 101 as A_ID
    ), TableB
AS
    (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL
    SELECT 2 , 101  , 'YYY'  UNION ALL
    SELECT 3 , 101  , 'ZZZ'  UNION ALL
    SELECT 4 , 102  , 'AAA'
    )
SELECT
     a.A_id,
    (
    SELECT ',' + B_Courses
    FROM TableB AS B
    WHERE B.B_A_ID  = A.A_ID
    FOR XML PATH(''),TYPE
    ).value('.', 'VARCHAR(20)')
FROM TableA A

NB These errors will also be generating 20MB stack dumps (.mdmp) files to your Log directory.


Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users? I am working on a stored procedure that retrieves the objectGUID from active directory.  I am storing the result in a temp table and then returning the value in an output parameter for use with other processes.  The SP will be called from different stored procedures as well as web applications PHP, ASP Classic and ASP.Net.

I read HERE that (regarding temp tables):

  If created inside a stored procedure they are destroyed upon completion of the stored procedure.  Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user.  Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects.  You may notice that my sample temporary table name started with a "#" sign.

Sounds like I am good to go but I wanted to get some advice to make sure there aren't any gotchas I am unaware of.  Here is the SP.

Thanks in advance.

CREATE PROCEDURE stp_adlookup
@user varchar(100),
@objectGUID varbinary(256) OUTPUT
AS
SET NOCOUNT ON;
DECLARE @qry char(1000)
CREATE TABLE #tmp(
objectGUID nvarchar(256)
)

SET @qry = 'SELECT *
FROM openquery(ADSI, ''
SELECT  objectGUID
FROM    ''''LDAP://mydomaincontroller.com''''
WHERE sAMAccountName = ''''' + @user + '''''
'')'
INSERT INTO #tmp
EXEC(@qry)
SELECT @objectGUID=CAST(objectGUID as varbinary(256))  FROM #tmp;
DROP TABLE #tmp
SET NOCOUNT OFF;
GO

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time.

(However, don't use global ##temp tables, signified with two leading pound/hash signs.)

But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me):

CREATE PROCEDURE dbo.stp_adlookup -- ALWAYS use schema prefix
  @user varchar(100),
  @objectGUID varbinary(256) OUTPUT
AS
BEGIN -- use body wrappers
  SET NOCOUNT ON;

  DECLARE @qry nvarchar(max); -- don't use CHAR for dynamic SQL

  SET @qry = N'SELECT @o = objectGUID
    FROM openquery(ADSI, ''SELECT  objectGUID
      FROM    ''''LDAP://mydomaincontroller.com''''
      WHERE sAMAccountName = ''''' + @user + ''''''')';

  -- can probably parameterize the above, but those single
  -- quotes are a nightmare. Not sure if they're necessary
  -- but I do not feel like trying to untangle them.

  EXEC sys.sp_executesql @qry, N'@o UNIQUEIDENTIFIER', @o = @objectGUID OUTPUT;

  -- SET NOCOUNT OFF; -- don't do this.
END
GO


SQL Spool FILE SQLPLUS BATCH FILE */

SET colsep ';'
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 800
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET NUMW 20

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT '.csv' spool_extension
FROM dual;
/

SPOOL \\SERVERNAME\FOLDER\FOLDER\FOLDER...ETC\InternationalReport&&suffix
@@"C:\FOLDER\FOLDER\International_report\International";
/
Exit;

Currently, it will spool the .csv file but I need it to have an output of InternationalReportyyyymmdd.csv

Any ideas on how exactly to set this up? Thanks!

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension
FROM dual;

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension -- Solved the problem after I added TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')

Hope this can help someone else who is looking for a solution


Understand why rank() over doesn't fit to don't select duplicates rows I would like to understand why I have different results

I have a table called active_transfert where I log image transfert

user_id | image_id | created_at
--------|----------|-----------
1       |1         |2014-07-10
1       |2         |2015-01-21
2       |1         |2015-05-23
3       |1         |2016-07-22
4       |6         |2017-06-01
4       |6         |2014-08-22

I would like to return unique user_id per image_id.

SELECT user_id,
       image_id
FROM active_transfert
GROUP BY user_id,
         image_id; --50

SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          rank() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_transfert) AS i
WHERE i.i_ranked = 1; -- 53

I run those queries against Redshift. Why my second query doesn't prevent duplicated records (same user_id and image_id)?

Expected result :

user_id | image_id |
--------|----------|
1       |1         |
1       |2         |
2       |1         |
3       |1         |
4       |6         |

RANK() is a deterministic function, meaning that duplicates will be labelled with the same rank value. The outputs of your queries suggests to me that there are multiple records with the same user_id and image_id that also have the same created_at value. These records will all return with the same RANK() value.

If you run your inner query, you will see these duplicates where all three attributes are the same. If the created_date is also the largest value for that combination of user_id and image_id they will all have the RANK() value of 1.

To get your desired output, you should instead be using ROW_NUMBER(). This is a non deterministic function when the parameters in the OVER clause do not uniquely determine the rows, which is true in this case. This will assign a unique result to every row, but as your RANK() query provided duplicates it will assign each of these rows a unique value at random.

Your second query using ROW_NUMBER():

SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          ROW_NUMBER() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_downloads) AS i
WHERE i.i_ranked = 1;


Speeding up COUNT(*) - WHERE clause slowing query The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on 'messages_id'. The 'hidden' field is a bit field (not null) and 'message_type_id' is an int field (not null) and message_id is an int field (PK).

SELECT COUNT(*)
FROM messages
WHERE message_type_id = 1
AND (hidden = 0 OR message_id = @message_id)

Thanks in advance!

Another approach to remove the OR.
You could try this and have 2 indexes on the table to satisfy the WHERE clauses.

SELECT
    COUNT(*)
FROM
    (
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND hidden = 0
    UNION
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND message_id = @message_id
    ) X


Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant? Given the use case:

Tenant data should not cross talk, one tenant does not need another
tenant's data.
Each tenant could potentially have large historical data volume.
SQL Server is hosted in AWS EC2 instance.
Each tenant is geographically distant.
There is an intention to use third party visualization tools such as PowerBI Embedded
The data volume is expected to grow over time
The cost of the system is constrained.
The solution must be maintainable without a 24/7 production DBA
The solution should be able to scale horizontally.
Total number of tenants is less than 50

What would be a recommended architecture, are there any reference implementations for this use case? I believe many people might have already faced this problem for enterprise software development.

I think this is a different situation from Handling growing number of Tenants in Multi-tenant Database Architecture. The use case mentioned in that question deals with a higher number of tenants, which is very different from having very few (50) large tenants. The architecture mentioned might be a solution here, which is what I want to know more about.

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer.

When you’re building an application for lots of clients, there’s two common ways to design the database(s):

Option A: Put all clients in the same database
Option 2: Build one database per client

Putting All the Clients in the Same Database

It’s simple: just add a Client table at the top of the schema, add a ClientUsers table to make sure people only see their own data, and away we go.

Benefits of this approach:

Easier schema management. When developers deploy a new version of the application, they only have to make schema changes in one database.  There’s no worries about different customers being out of sync or on the wrong version.

Easier performance tuning. We can check index usage and statistics in just one place, implement improvements easily, and see the effects immediately across all our clients.  With hundreds or thousands of databases, even the smallest change can be difficult to coordinate.  We can check our procedure cache contents and know for certain which queries or stored procedures are the most intensive across our entire application, whereas if we’re using separate databases per client, we may have a tougher time aggregating query use across different execution plans.

Easier to build an external API. If we need to grant access to our entire database for outsiders to build products, we can do that easier if all of the data is in a single database.  If the API has to deal with grouping data from multiple databases on multiple servers, it adds development and testing time.  (On the other hand, that “multiple servers” thing starts to hint at a restriction for the one-database-to-rule-them-all scenario: one database usually means all our load impacts just one database server.) In your case, with PowerBI, having everyone in one database will make managing connections much easier.

Easier high availability & disaster recovery. It’s really, really simple to manage database mirroring, log shipping, replication, and clustering if all we have to worry about is just one database.  We can build a heck of an infrastructure quickly.

Putting Each Client in its Own Database or Shard

You still need a client listing, but now it becomes a directory - for each client, you also track the shard it lives in. On startup, your app queries this table, and caches it in RAM. When it needs data for a client, it connects directly to that shard (database & server).

Benefits of this approach:

Easier single-client restores. Clients are unreliable meatbags.  (Except mine – they’re reliable meatbags.)  They have all kinds of “oops” moments where they want to retrieve all of their data back to a point in time, and that’s a huge pain in the rear if their data is intermingled with other client data in the same tables.  Restores in a single-client-database scenario are brain-dead easy: just restore the client’s database.  No one else is affected.

Easier data exports. Clients love getting their hands on their data.  They want the security of knowing they can get their data out anytime they want, avoiding the dreaded vendor lock-in scenario, and they want to do their own reporting.  With each client’s data isolated into their own database, we can simply give them a copy of their own database backup.  We don’t have to build data export APIs.

Easier multi-server scalability. When our application needs more power than we can get from a single server, we can divide up the databases between multiple servers.  We can also spread out the load geographically, putting servers in Asia or Europe to be closer to clients.

Easier per-client performance tuning. If some clients use different features or reports, we can build a specialized set of indexes or indexed views just for those clients without growing everyone’s data size.  Granted, there’s some risk here – by allowing schema differences between clients, we’ve just made our code deployments a little riskier and our performance management more difficult.

Easier security management. As long as we’ve properly locked down security with one user per database, we don’t have to worry about Client X accessing Client Y’s data.  However, if we just use a single login for everyone, then we haven’t really addressed this concern.

Easier maintenance windows.  In a global environment where customers are scattered around the globe, it’s easier to take customers offline for maintenance if we can do it in groups or zones.

Which one is right for you?

There’s no one right choice: you have to know your own company’s strengths and weaknesses.  Let’s take two of my clients as examples.

Company A excels at hardware performance tuning.  They’re really, really good at wringing the very last bit of performance out of hardware, and they don’t mind replacing their SQL Server hardware on a 12-18 month cycle.  (They refresh web servers every 4-6 months!)  Their Achilles’ heel is extreme compliance and security requirements.  They have incredible auditing needs, and it’s just easier for them to implement bulletproof controls on a single server, single database than it is to manage those requirements across thousands of databases on dozens of servers.  They chose one database, one server, many clients.

Company 2 excels at development practices.  Managing schema changes and code deployments across thousands of databases just isn’t an issue for them.  They have clients around the world, and they’re processing credit card transactions for those clients around the clock.  They need the ability to spread load geographically, and they don’t want to replace servers around the world every 12-18 months.  They chose one database for each client, and it’s paying off as they start to put SQL Servers in Asia and Europe for their offshore clients.


Using "IN" with multiple rows? So I got a query with a whole bunch of joins and that returns ONE row, like this (just an example):

select t1.chickenId, t2.beefId, t3.fishId from Food f
join t1 on t1.id = f.id
join t2 on t2.id = f.id
join t3 on t3.id = f.id
where f = 'something'

A result would be like this for example:

chickenId | beefId | fishId
5          58         14

Now the part I don't know how to do... The reason for using the above query is because I want to use the result to find something in another table.

I have another table named "Extras" and I want the rows from that table that have the ids from any of the columns of the previous query. So if the id 5, 58 or 14 is in Extras table, I want those rows.

This following query is incorrect but I believe it can be used to demonstrate what I'm trying to achieve:

select * from Extras where extra = 'Potatoes' and foodId in (select t1.chickenId, t2.beefId, t3.fishId from Food f
    join t1 on t1.id = f.id
    join t2 on t2.id = f.id
    join t3 on t3.id = f.id
    where f = 'something')

Any idea how I can achieve this? I'm using Microsofts SQL Server 2008

Your IN subquery has 3 columns, so it can't be compared with the one value of Extras.foodId.

You could use EXISTS instead and move the IN condition inside, making it a correlated subquery:

select e.*
from Extras as e
where e.extra = 'Potatoes'
  and exists
      ( select *
        from Food as f
          join t1 on t1.id = f.id
          join t2 on t2.id = f.id
          join t3 on t3.id = f.id
        where f.f = 'something'
          and e.foodId in (t1.chickenId, t2.beefId, t3.fishId)
      ) ;

Another possibility would be to keep the IN syntax you have and convert the 3 columns into one (and each row into 3) with CROSS APPLY:

select e.*
from Extras as e
where e.extra = 'Potatoes'
  and e.foodId in
      ( select v.foodId
        from Food as f
          join t1 on t1.id = f.id
          join t2 on t2.id = f.id
          join t3 on t3.id = f.id
          cross apply
            ( select t1.chickenId union
              select t2.beefId    union
              select t3.fishId
            ) as v (foodId)
        where f.f = 'something'
      ) ;

If the first query you provide is guaranteed to return one row always, you could also use a simple join without any subqueries:

select e.*
from Food as f
  join t1 on t1.id = f.id
  join t2 on t2.id = f.id
  join t3 on t3.id = f.id
  join Extras as e
    on e.foodId in (t1.chickenId, t2.beefId, t3.fishId)
where e.extra = 'Potatoes'
  and f.f = 'something' ;


Two SQL Server databases share the same MDF and LDF files but have different data I have a strange circumstance where I have a production database and a restored copy as another database on the same SQL Server instance, and both of them are using the same MDF and LDF files (i.e. they have the same "Logical" files in SQL Server Management Studio).  From what I've found while researching this, it is possible, but all indications are that they would both be pointing to the same data, a reasonable assumption.

However, in my case, both databases actually contain different data.  If I run a select statement on two tables with the same name, one in each database, they return different data.  I've double, triple, and quadruple verified this.  How is this possible?  None of the MS docs or anything I've found have talked about two DBs sharing the same MDF files but having different data.

There are no NDF files involved, and there is one file group in each DB with only one file in each file group.

It is not possible to have two databases point to the same database files, except in the weird Autoclose case that you linked to, which no one should be using that setting anyway.

Run the below query to view what physical files the databases are pointing to:

SELECT database_id, name, physical_name
FROM master.sys.master_files

With that information, you should be able to see that the databases are in fact not pointing to the same database files.

You also can see this information in the SSMS GUI. Right click on the database, go to properties and then to Files. Now SCROLL to the right as the physical files aren't visible yet. The combination of Path and File Name will be unique.


MSSQL - Query efficiency to many filters I'm making an ASP.NET website which users can select more than 20 different filters at the same time. Something like 'Product Type', 'Year', 'State-of-Usage', 'Color', 'Size' and so forth.

Many of these items are related to other tables. So, my query makes a 'SELECT' using WHERE and 'INNER JOIN' to 12 other tables.

The problem resides in parameters that can be present or not.
I mean, depending of the user choice, some parameters will be NULL or not and I can't utilize them in my query (like using ISNULL()) because I'm affraid of return a wrong result caused by this.

So, my solution is build the query string depending of the parameters received , I mean, the query will consider only those which have real values.

declare @SEARCH_QUERY nvarchar(max);

set @SEARCH_QUERY = 'select AcervoID, Categoria, Fabricante, Personagem, Estado,
                        IIF(LEN(Description) > 120, LEFT(Description,120) + ''...'', Description) [mDescription],
                        Value, Availability, ActiveLink, Title,
                        RIGHT([thumb], CHARINDEX(''\'', REVERSE([thumb])) -1) [Image],
                        E.UsoID, E.FontAwesome
                        from Acervo A
                        inner join Categorias C on C.CatID = A.Categoria
                        inner join Fabricantes F on F.FabID = A.Fabricante
                        inner join Personagem P on P.PersID = A.Personagem
                        inner join EstadoUso E on E.UsoID = A.Estado
                        where A.Ativo = ''true'' and A.Disponiveis > 0'

  if @m_Cat >-1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.Categoria = ' + convert(nvarchar(4),@m_Cat)
  if @m_Fab >-1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.fabricante = ' + convert(nvarchar(4),@m_Fab)
  if @m_Pers > -1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.Personagem = ' + convert(nvarchar(4),@m_Pers)
  if @m_EU is not null
    set @SEARCH_QUERY = @SEARCH_QUERY + @m_EU

exec sp_executeSQL @SEARCH_QUERY

Is there a best way to do it?

Thanks in advance.

The basic form you have is correct, though you want to avoid concatenating parameters into the string the way you have. See:

Revisiting catch-all queries
#BackToBasics : An Updated "Kitchen Sink" Example

The efficiency part will depend ultimately on the supporting indexes you have for your queries, along with other local factors, like hardware, blocking, rows returned, etc.


MYSQL - Optimizing MAX(CASE WHEN ...) I have a table similar to the following (Updated: added a type column** Simplified the real query too much I guess)

CREATE TABLE versions(
   type INT NOT NULL,
   version INT NOT NULL,
   important BOOLEAN NOT NULL,
   PRIMARY KEY (type, version)
)

Running SELECT type, MAX(version) FROM versions GROUP BY type is very efficient and retrieves a single row per type from the database.

However, running SELECT MAX(CASE WHEN important=1 THEN version END) FROM versions GROUP BY type seems to generate a full table scan.

Since I have an index on version I expected MySQL to start scanning versions from the highest and stop when it finds the first that has "important=1". Usually, with my data this requires scanning just a few rows.

Looking at performance_schema.events_statements_history (ROWS_EXAMINED column) it seems like MySQL scans the entire table in order to retrieve the row I need.

Any ideas on how to improve the performance of this query will be much appreciated.

For the simple queries (without GROUOP BY), you can rewrite them as:

SELECT version
FROM versions
-- WHERE important = 1
ORDER BY version DESC
LIMIT 1 ;

and check if the execution plan does not do a table scan and using the index in the way that you expect.

Additionally, you can add an index on (important, version).

For the GROUP BY type queries, an index on (important, type, version) would fit better.

Rewriting that query with LIMIT 1 is not trivial as we want many maximum (one for each type). But if the index alone doesn't improve things much, here is a different query that will use the above composite index.

Performance is usually improved with this method when there are few distinct type values:

SELECT dt.type, v.version
FROM
    ( SELECT type
      FROM versions
      WHERE important = 1
      GROUP BY type
    ) AS dt
  -- LEFT
  JOIN versions AS v
  ON  v.important = 1
  AND v.type = dt.type
  AND v.version = ( SELECT vi.version
                    FROM versions AS vi
                    WHERE vi.important = 1
                      AND vi.type = dt.type
                    ORDER BY vi.version DESC
                    LIMIT 1
                  ) ;

There are variations you could try. For example, removing the important = 1  (only from inside the derived table dt) and using LEFT JOIN there. This would give you all types (and NULL values for the types that have no version marked as "important".

If you have a separate table, with all the types (i.e. PRIMARY KEY (type)), you could completely replace the dt with that table.


SQL Server Query Plan Hash Collision I'm wondering if anyone can shed light on a worry of mine that a query_plan_hash collision could cause a query to be executed as an entirely different query.

The hash is a 16 digit Hex that sp_help sys.dm_exec_query_stats says is a binary. Therefore it's only a 64 bit hash, and a collision seems very probable (considering SHA1 [160 bit] was just verified to have had collisions).

Would the plan_hash and the query_plan_hash both have to collide for this (query to be executed as an entirely different query) to happen?

I'm also curious if there is a setting somewhere in SQL Server that will allow us to change this hash to SHA2-512 (to reduce the possibility of a collision). Our data is very important.

I searched high and low via Google and Stack Exchange forums.

Neither query_hash nor query_plan_hash are used to look up query plans for execution, so the exact concern in the question does not arise.

The documentation for sys.dm_exec_query_stats explains that:

query_hash is provided to allow users to aggregate resource usage for  queries that differ only by literal values (ones that would parameterize to the same form).
query_plan_hash is provided to allow users to calculate the cumulative cost of queries with similar execution plans.

Unsurprisingly, the SQL Server developers have gone to great lengths to ensure that cached plans are only reused when it is safe to do so. This goes well beyond the obvious requirement that the cached plan must match the 'query' exactly.

Briefly:

SQL Server has multiple plan cache stores, each implemented as a hash table. A hash value and cache key pair is used to connect a compiled plan with a particular 'query'.

The hash value is (database_id * object_id) mod (hash table size). This determines the hash bucket in a particular cache store. The object_id is known metadata for objects like  procedures, functions, and triggers. For ad-hoc SQL, the object_id is a hash of the entire batch text.

The collection of attributes that must match for a cached plan to match a 'query' is known as the cache key. This is used to find and confirm an exact match within the hash bucket. Some (not all) cache key attributes can be found in the documentation for sys.dm_exec_plan_attributes.

Importantly, for ad-hoc SQL, the entire batch text is one of the cache key attributes.

More details can be found in 3.0 How Cache Lookups Work (and other articles in the series) by the Microsoft SQL Programmability & API Development Team.


How do I terminate a running SELECT query without restarting MySQL? I have a MySQL select query that is not getting killed. Please help!

And we have load avg building up on this server.

 | 64446 | root    | localhost | dast-india | Killed  | 3758 | Sorting result | select * from dastnumbers_0 order by addeddate desc limit 25 |

I tried killing and unlocking the tables as usual it works for selects. But now its not responding for that too. Please help.

Based on the comments trail on this question, I have some bad news and good news.
BAD NEWS
I believe dastnumbers_0 is an InnoDB table. You can verify what with
SELECT engine FROM information_schema.tables
WHERE table_name = 'dastnumbers_0';

You summoned all the rows with your SELECT query because of there being no WHERE clause. An attempt to sort 2 billion rows must take place.
Even worse, InnoDB attempts to keep a snapshot of the entire table in the rollback segments and undo tablespace within ibdata1 ( See picture of this in http://www.scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing ). This is manifested as an increased size of ibdata1. When you killed the query, InnoDB attempts clear out whatever was collected in a snapshot.
You will have to just let go ahead and clear. If you kill the mysqld process prematurely, it will no matter. On the next mysql startup, it will pickup where it left off.
I was in commentary discussion with @a_horse_with_no_name about this. In theory, snapshots should not be happening on a straightforward SELECT. The only way this could happen with a SELECT query is if the DB Connection's session has autocommit disabled or a BEGIN TRANSACTION ... COMMIT/ROLLBACK block was issued. Please let us know if this is the case.
GOOD NEWS
You could refactor your query. First look at the query:
select * from dastnumbers_0 order by addeddate desc limit 25;

It will get everything, sort it in descending order, then that the first 25 rows. That's an expensive operation. Make sure you have an index on addeddate . If you do not have an index on addeddate or you are not sure, you may need to create it with this:
ALTER TABLE dastnumbers_0 ADD INDEX (addeddate);

This will take a long time since you have 2 billion rows, but you only have to do this once.
Then, make the query get only the 25 keys for the table. Suppose dastnumbers_0 has id as its primary key. You could now have the query do something like this:
SELECT B.* FROM
(
    SELECT id FROM dastnumbers_0
    ORDER BY addeddate DESC LIMIT 25
) A LEFT JOIN dastnumbers_0 B USING (id);

This technique can work because I answered a question like this in StackOverflow and got a 200-point bounty out of 11 submitted answers : https://stackoverflow.com/a/6023217/491757
Give it a Try !!!


SQL Server 2014 - Compute scalar over computed indexed column I've created a table a short while ago, and it currently handles roughly 100M rows.

For reporting purposes, I added a computed, non-persisted column which is the HASHBYTES in SHA1 of 4 non-NULLable varchar columns in this table. I'm not using a scalar function for this : it was such as :

    ALTER TABLE myTable
    ADD myHash AS(CAST(HASHBYTES(SHA1, myString1, myString2, myString3, myString4) AS varbinary(20)))

Then I added an index on it, so as to be able to perform a self-join on this computed column, as such :

    CREATE INDEX IX
    ON myTable (myHash)

The query plan of  the report query shows a Compute Scalar operator that does the HASHBYTES computation. I wonder why this happens, since this value is stored in the index; the report query only INNER JOINs once on this computed column (i.e. it does not appear in any other clause, nor between the SELECT and the FROM clauses).

So I persisted the column, but the plan is identical. Can someone explain this behavior please ?

I believe the "computer scalar" operator in the plan where it is using the index is actually not being executed.  On my test rig with 1,000,000 sample rows, which is shown below, the query without the non-clustered index is a lot slower than the query that uses the clustered index on the myhash column.

USE tempdb;
SET NOCOUNT ON;

IF EXISTS (SELECT 1
    FROM sys.objects o
    WHERE o.name = 'HTest'
        AND o.type = 'U')
DROP TABLE dbo.HTest;

CREATE TABLE dbo.HTest
(
    HTest_ID INT NOT NULL
        CONSTRAINT PK_HTest_ID
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , V1 VARCHAR(255) NOT NULL
    , V2 VARCHAR(255) NOT NULL
    , V3 VARCHAR(255) NOT NULL
    , V4 VARCHAR(255) NOT NULL
);

INSERT INTO dbo.HTest (V1, V2, V3, V4)
SELECT TOP(1000000)
    o1.name
    , o2.name
    , o3.name
    , o4.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

ALTER TABLE dbo.HTest
ADD MyHash AS(CAST(HASHBYTES('SHA1', V1 + V2 + V3 + V4) AS VARBINARY(20)));

Below we have two test runs, one without the index, and the 2nd one with an index on the MyHash column.

IF EXISTS (SELECT 1
    FROM sys.indexes i
    WHERE i.name = 'IX_Htest_MyHash'
)
DROP INDEX IX_Htest_MyHash
ON dbo.HTest;

PRINT (N'');
PRINT (N'-----set stats io on---------------------------------------------------');
PRINT (N'');

SET STATISTICS IO, TIME ON;

PRINT (N'');
PRINT (N'-----run 1 (no index)--------------------------------------------------');
PRINT (N'');

SELECT MyHash
FROM dbo.HTest;

PRINT (N'');
PRINT (N'-----end of run 1------------------------------------------------------');
PRINT (N'');

CREATE INDEX IX_Htest_MyHash
ON dbo.HTest(MyHash);

PRINT (N'');
PRINT (N'-----run 2 (with index)------------------------------------------------');
PRINT (N'');

SELECT MyHash
FROM dbo.HTest;

PRINT (N'');
PRINT (N'-----end of run 2------------------------------------------------------');
PRINT (N'');

SET STATISTICS IO, TIME OFF

The salient bits from the output of this is:

-----run 1 (no index)--------------------------------------------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'HTest'. Scan count 1, logical reads 9409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1560 ms,  elapsed time = 7516 ms.

-----end of run 1------------------------------------------------------

-----run 2 (with index)------------------------------------------------

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'HTest'. Scan count 1, logical reads 4227, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 4912 ms.

-----end of run 2------------------------------------------------------

As you can see, run 2 is clocking in with far less CPU time, and around half the reads of run 1.

The plan for run 1:

The plan for run 2:

Looking at the XML for the 2nd plan, you can see the <ComputeScalar> operator is actually just a lookup.  Look at the 5th line <ScalarOperator ScalarString="[tempdb].[dbo].[HTest].[MyHash]">:

  <ComputeScalar>
    <DefinedValues>
      <DefinedValue>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
        <ScalarOperator ScalarString="[tempdb].[dbo].[HTest].[MyHash]">
          <Identifier>
            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
          </Identifier>
        </ScalarOperator>
      </DefinedValue>
    </DefinedValues>
    <RelOp AvgRowSize="21" EstimateCPU="1.10016" EstimateIO="3.11572" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="1" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="4.21587" TableCardinality="1000000">
      <OutputList>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
      </OutputList>
      <RunTimeInformation>
        <RunTimeCountersPerThread Thread="0" ActualRows="1000000" ActualRowsRead="1000000" ActualEndOfScans="1" ActualExecutions="1" />
      </RunTimeInformation>
      <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
        <DefinedValues>
          <DefinedValue>
            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Column="MyHash" ComputedColumn="true" />
          </DefinedValue>
        </DefinedValues>
        <Object Database="[tempdb]" Schema="[dbo]" Table="[HTest]" Index="[IX_Htest_MyHash]" IndexKind="NonClustered" />
      </IndexScan>
    </RelOp>
  </ComputeScalar>

Presumably, the plan for the 2nd query includes the base calculation for the computed column, even though the results actually come from the index without any calculations actually taking place.


Oracle PLSQL code recovery I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error.
I consider to install new instance.
Questions:
1) Can I restore/recover PLSQL code from "dead" oracle database?
2) Where is the PLSQL stored?

EDIT: My situation is described here: ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"

You need to restore/recover your database. If you can't then yes the code is buried in the system table spaces. But it won't be easy looking through the data file with a hex editor trying to find the code. You should either try to restore the database or just rewrite the code. Do you have a backup of your database? Do you have all of the data files, control files, online redo files? If you don't have a backup and your database is in an inconsistent state, then you can probably alter database recover until cancel and then point Oracle to your online redo logs to see if it can find what it needs.

Otherwise you might be out of luck. What is in your alert log for your database?


Test-Automation: Diff Data in Oracle Database - what are best practices? We're re-writing a COBOL application in Java. We want to make sure, the applications behave the same, by validating our unit tests produce the same DB changes.

How can we easily diff the data in the DB to a given master-DB (e.g. the Database in which the COBOL program has written data). We want to do this in an automated way. Bonus: Is it possible to exclude several columns like date columns in a comparison?

There are two methods for comparing tables.  Method 1 works well with Unit Testing software.

You will want to use some sort of Unit Testing Software (and run it from the command line).

For full automation, you'll need some sort of DevOps Infrastructure.

SQL Developer Unit Testing

SQL Developer ($0) has some Unit Testing Capabilities

Running it from Command Line was found in this Related StackOverflow question.

./sdcli.exe unittest -run -suite -name AWARD_BONUS_SUITE -repo UNIT_TEST_REPO_CONNECTION -db TARGET_SCHEMA_CONNECTION

Building the Test

You'll need to create a no-op function to "test".
The "Process Validation" will be of type "Query returning no row(s)"

Building the Query

I cheat.  I use a template based code generator to create the comparison SQL(s).

There are a few different ones for Oracle. (FTLDB, tePLSQL, jk64)

There is also a plug-in for SQL Developer that allows you to place a GUI around the parameters for your template (eg table_name and db_link).  The name for the plugin is oddgen.

Method 1

This is the Brute-Force Method of comparing tables.

I cheat.  I use a template based code generator to create this code.

with old_data as (
    select
        banner -- specify your columns here
    from v$version
), new_data as (
    select
        banner
    from v$version@production
)
select * from (
    select 'new - old' test, a.* from new_data a
    minus
    select 'new - old' test, b.* from old_data b
)
union all
select * from (
    select 'old - new' test, a.* from old_data a
    minus
    select 'old - new' test, b.* from new_data b
)

Method 2

This one is OK for small tables.

select /*+ parallel */ sum(
    ora_hash( e.empno
           || e.ename
           || to_char( e.hiredate, 'yyyy-mm-dd hh24:mi:ss')
    )
) hash_value
from scott.emp e


Should I in general partition tables by tenant IDs in a multi-tenant system? We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this:
create table Things(
    accountId varchar(64) not null,
    internalId varchar(64) not null,
    externalId varchar(256) as (concat(accountId, '-', internalId)) stored,
    ...
    primary key (accountId, sourcedId),
    unique (externalId),
    foreign key (accountId) references Accounts (id)
);

All queries either have an accountId in the where clause, or they use externalId. There are no cross-account queries.
We're expecting to have a total of 200 accounts. The sizes of the other tables (like Things) vary from 5 rows per account for some tables (1000 rows total) to 225K rows per account for some other tables (45M rows total). (These are the numbers we use for performance testing - they're the max numbers)
The DB size is ~150 GB. 95% of the scenarios are reads.
The RDBMS is Mysql 8.0.16 (AWS RDS).
We don't have any performance issues at the moment and we're not trying to make anything work faster. But I'm wondering if NOT partitioning the tables like Things by accountId is a "premature pessimization"?

Partitioning is often more about administration (swapping partitions in and up while updating a warehouse, etc.) than performance in general OLTP scenarios and, especially if all your data is on the same drive/array, can result in a performance decrease.
If you need partitioning later you may choose to do it by a different property, perhaps partitioning such that newer, more active, data can be kept on your fastest drives. If this is the case then you have made more work for yourself as you will need to unpick the existing partitioning scheme before implementing the new one.
So while this is at least a little opinion and not 100% fact, I would say partitioning now is premature.


Not only client connections? Is "MongoDB Wire Protocol" also used for replication? It seems that MongoDB Wire Protocol is also used for replication. Not only client/driver connections. Is that true and what are the main differences between client/driver and replication traffic?

  The MongoDB Wire Protocol is a simple socket-based, request-response
  style protocol. Clients communicate with the database server through a
  regular TCP/IP socket.

From a protocol perspective, there is no difference. A really basic way to think of it is this (assuming you start a secondary that is reasonably up to date and is not performing an initial sync):

Replication is a client connection from a secondary to the primary
That is done using TCP/IP and with the MongoDB wire protocol being used over the top (unless you are using SSL, which would add that to the mix)
The secondary creates a tailable cursor and begins streaming the operations from the Oplog
The secondary applies these idempotent operations to its data in order to reach the same state as the primary

Now, in reality things are more complicated than that but that gives you a good starting point. You still have to add in multi-threaded replication, replication chaining so that you can replicate from another secondary rather than the primary etc. These are advanced topics which I have given multi-hour whiteboard sessions on (when I worked in MongoDB) and beyond the scope of this answer, not to mention the fact that I am likely out of date with my information at this point.

If you would like to go deeper into how replication works, I recommend the internals posts from Kristina Chodorow (though they are a little dated these days) as well as the various talks the MongoDB engineers have given on the topic (here is one example).


Deadlock resource when try to drop datafile This morning I did a mistake, I made a datafile offline by using

alter tablespace users drop datafile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\O1_MF_USERS_6LD56LRR_.DBF';

The reason is I want to drop that datafile. After that I add one more datafile to that tablespace n try to delete the offline datafile. But I got the error:

  deadlock found while waiting for resource.

Can anyone help me to fixed this so that I can drop that datafile safely.

This error is produced for various reasons stated below:

  Too high activity - Re-running the job during a less busy period can
  fix this ORA-00060 deadlock error. Poor application design - As noted
  in the trace file:  "The following deadlock is not an ORACLE error. It
  is a deadlock due to user error in the design of an application or
  from issuing incorrect ad-hoc SQL."

  ASSM - This error can happen when the target tablespace is using
  "segment space management auto".  See these notes on deadlocks with
  ASSM.  Beware, ASSM can cause huge deadlocks, because ASSM does not
  perform well under heavy DML loads.

  RAM shortage - In rare cases this ORA-00060 deadlock error can happen
  with a RAM shortage at the Java layer or Oracle layer

One way is to use the trace file to see what is causing the deadlock.

The most common reason for which such deadlock situation occurs is that some resource(s) might be using the datafile that you want to drop.

The best possible solution is to close/terminate all the sessions using the resources on the mentioned datafile and then delete the datafile.

Another solution is by shutting down the database and then making the necessary changes.

There are many ways/queries to find the SID(Session ID) which is causing the lock. Please refer the website for some examples -->http://www.oraclerecipes.com/monitoring/find-blocking-sessions/

Analogy in Windows: It is similar to renaming/deleting a file when in use.
Hope this solves your problem !!


NOTIFY vs. pg_notify I write a DB LISTENing program something like as shown here (Example 3)==> https://www.postgresql.org/docs/9.1/static/libpq-example.html

I have a problem with pg_notify such that;

NOTIFY channel

==> works fine, C program catches the signal

whereas

SELECT pg_notify('channel','This is a test');

==> Program can't catch anything.

Any idea?
Note: My Version is PostgreSQL 10.5

In NOTIFY Channel, 'Channel' is treated as an SQL identifier, and will automatically be transformed to lower case if not surrounded by double quotes, like NOTIFY "Channel".  This is usual PostgreSQL behavior.  The arguments to pg_notify are strings, not SQL identifiers, so they are case-preserved.


What is the behavior of MIN_ACTIVE_ROWVERSION() on replicas in SQL Server AlwaysOn? MIN_ACTIVE_ROWVERSION() returns the lowest rowversion allocated within still-open transactions. This is important when using rowversion columns for reading data where you want to be sure that you wont subsequently miss rows that have been written with rowversions lower than the batch you have just read.

What is the behaviour of this function when called on synchronous and asynchronous replicas in an AlwaysOn Availability Group situation?

On further investigation, it appears than MIN_ACTIVE_ROWVERSION() only behaves correctly on the primary instance, as that is the only place the full transactions are running on.


Incremental extract strategy using SSIS and CDC I'm looking for a strategy for extracting data from a SQL Server database that is configured for change data capture (CDC) using SSIS.

Most of the documentation I have seen around using CDC and SSIS are simple scenarios where there is one source table configured with CDS, a conditional split to determine the inserts, updates and deletes and then those corresponding actions.

My current extract consists of queries that join multiple tables together and then feeds that into the transform and load. Changes could happen in any of those tables, not just one.

One approach could be to just look for changes on the base table and do lookups or merges on the other tables. However that would miss changes on a supporting table if there is no corresponding change in the source table.

Another option would be to do individual extracts on all tables involved and then do the joining in the transformation layer. But again, there could be times that one or more tables isn't changed and isn't available for the join.

What I'm leaning towards is one of these 2 options:

Totally separate all tables into their own ETL that then do multiple inserts and updates on the destination table. For example, only process inserts for the source table of the query and process updates for all the supporting tables after the source table has been populated.
Store all previous extracts for a table in a versioned directory in the spirit of a "data lake" and then look up the related record from a previous version.

Is there a strategy that I'm not aware of?

I have used the CDC functions to extract data in the past. My plan was to try to use the SSIS CDC components themselves if possible.

My main reason for looking into this is to increase the frequency of our loads to be on an hourly basis instead of a nightly basis. Using CDC on my biggest tables would reduce the load, and reduce the time needed. It would still be ideal to only pull the data that has changed no matter the size.

We also have the ability to add a read only secondary to our AG to scale out reads similar to what Dave describes in his answer. However, the time taken to pull all the data out would still be the same even though the same load wouldn't be on the source system.

This appears to be your stumbling block in relation to most of the demos of SSIS CDC:

  My current extract consists of queries that join multiple tables
  together and then feeds that into the transform and load. Changes
  could happen in any of those tables, not just one.

As such you're on the right track with your first proposed option. For your join issues - it seems that you either have data that changes in tandem or a data set that shouldn't implement CDC. You should probably reduce the number of CDC tables and do a full load from others. At the least you could come up with a custom incremental that makes sense for your JOIN requirements while also reducing your data set size. Something like:

SELECT *
FROM InfoTable
WHERE Date >= '01-01-2016'

The typical pattern for data warehouse ETL is to move table by table from source to a Staging database, which replicates the table structure of the source systems nearly 1 to 1 (only for tables you actually use and skip things like IDENTITY which will fail your inserts/transfer). Following this pattern should solve at least part of the CDC issue.

From Staging to DataWarehouse you should have your joins, business logic, clean-up, etc. in the ETL. One of the benefits of this pattern, beside simplifying incremental source system pulls, is the reduced load on LoB/OLTP systems. You get in and out from the source system as fast as possible and leave the majority of the ETL burden on the SSIS/DW server(s).

You could setup a variety of incremental load patterns on all your tables. The importance of moving ETL logic/resource use out of source systems becomes even more important if you're running that frequently. If you're trying to meet an hour load window there may be other options beside just incremental loads... even if your time is consumed by data transfer (e.g. network settings).


Postgresql: A bit confused on listing all roles in my system I am just getting into PostgreSQL. I am ultimately interested in using PostGIS but thought it would pay off in the long run to get thoroughly familiar with the client/server authentication systems and also some general database management concepts through the command line instead of pgadmin GUI.

Going through the PostgreSQL documentation, it shows two methods for listing the defomed roles in the system.

\du

and

SELECT rolname FROM pg_roles

I wanted to try these out on my own system, and I did the following in cmd:

psql -U postgres
psql (11.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I did:

postgres=# SELECT rolname FROM pg_roles;
          rolname
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
(9 rows)

As you can see the listed roles are different, which is very confusing to me. my pg_hba.conf file is as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

I changed all the authentication methods to trust because I wanted to facilitate access to the superuser role, since I forgot my password and could not find any straightforward method to retrieve it.

Why are the role names different for the two commands? Are

 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend

also roles?

If you want to find out, what exactly the "backslash commands" do, just start psql with the option -E (or --echo-hidden).

If you do that, you will see that \du uses the following query:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

So \du simply filters out all roles where the name starts with pg_, because role names starting with pg_ are reserved for Postgres built-in roles and you can't create a role with that prefix manually.

The default roles are explained in the manual


SQL Server 2014 user login keeps being dropped I have an issue where I have a user login which keeps being dropped randomly without any trace in the error log. How do I find out why it's behaving as such or who is doing this, even though I am sure it's not a person who is doing this. Thanks

Possibly setup, sql audit, a server side trace/extended event and filter for sp_droplogin or a server-scoped trigger like below.

CREATE TABLE ##AuditDroppedLogin(UserName sysname, DateDropped DATETIME);
GO
CREATE TRIGGER WhoDidIt
ON ALL SERVER
FOR DROP_LOGIN
AS
BEGIN
INSERT INTO ##AuditDroppedLogin
SELECT USER_NAME(), GETDATE()
END

Then simply query ##AuditDroppedLogin for any events. Very basic example.

Below is also a server side trace script to track sp_droplogin

    /****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 10/01/2017  13:15:30         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text C:\Traces\WhoDroppedLogin, with your existing appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

declare @stoptime DATETIME
declare @filecount int

SET @stoptime =DATEADD(hh,48,GETDATE()) -- runs for 48 hours

SET @filecount = 5

exec @rc = sp_trace_create @TraceID output, 2, N'C:\Traces\WhoDroppedLogin', @maxfilesize, @stoptime, @filecount
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%sp_droplogin%'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

To stop before 48 hours run, replace 2 with your TraceID.
EXEC sp_trace_setstatus 2, 0


Execution order in complicated queries I know the query execution order for simple queries.

However, I get confused understanding the exact execution order in this query :

select
      M.DateKey  As DateKey ,
      PersonnelCode As PersonnelCode,
      Isnull((select top 1 BranchCode
                  from [Financial].[viwPersonnelLocation]
              where PersonnelCode = p.PersonnelCode and DateKey <= M.DateKey
                  order by DateKey desc)
                  ,p.BranchCode) As BranchCode,

          Isnull((select top 1 CertficateCode
                   from [Financial].[viwPersonnelEducation]
                   where PersonnelCode = p.PersonnelCode and DateKey <= M.DateKey
                   order by DateKey desc)
                   ,p.Educationcode) As Educationcode

    from (select DateKey,MonthOfYearKey,EDATE
          from [Common].[DimTime]
          where LastDayOfMonthKey = 1) M

                Inner Join

               [Financial].[viwPersonnelInfo] As P
                   on m.DateKey >= p.DateOfEmployeement
    where (m.DateKey <= p.DateOfLeave) or (p.DateOfLeave='' or p.DateOfLeave is null) and m.EDATE <= getdate()

You should look at the execution plan for the exact order. As the plan depends on heuristics based on indexes, statistics and the like, you can't tell simply from the query.

So hit Ctrl-L in SSMS and look at the execution plan. Each operator calls the one on its right, requesting rows as needed.

The physical order in the execution plan may be quite different from the logical order expressed in the query. Itzik Ben-Gan has a good flowchart.


connect to sqlplus without writing as sysdba I want to connect sqlplus sys/test without writing as sysdba.
I'm wondering if there is a way to avoid that.

$ alias sqs='sqlplus / as sysdba'
$ sqs

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 15 20:33:31 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>


How are these two user drops different? Other than the obvious, what is the difference between:

drop user user_01@host01;

and

drop user 'user_01'@'host01';

In a sample test, they both achieved the same result. From Account Names docs (5th bullet point), looks like they are not the same ... yet they appear to do the same thing ... ?

MySQL 5.5 on Centos 6.x.

They are identical.

The bullet point you mention states:

  The user name and host name parts, if quoted, must be quoted
  separately. That is, write 'me'@'localhost', not 'me@localhost'; the
  latter is interpreted as 'me@localhost'@'%'.

And that is what you did, you quoted them separately. In other words, all these are equivalent:

drop user    user_01@host01;

drop user   user_01 @ host01 ;

drop user  'user_01'@'host01';

drop user 'user_01' @ 'host01' ;

And if you wrote:

drop user 'user_01@host01';

... it would be interpreted as:

drop user 'user_01@host01' @ '%';


How do you move a SQL Server 7 database to SQL Server 2012? I would like to move a database from SQL Server 7.0 to SQL Server 2012. They are not compatible for a backup and restore. Is there a way to export from SQL Server 7.0 and import it to 2012? I only need the tables -- no views or stored procedures.

Solution 1

Copy the data using an ETL process.  This assumes you have a SQL Server 7 instance up and running with the database accessible.  You have several tools at your disposal, but the simplest approach I could see is:

Script out all the SQL Server 7 objects and create them in SQL Server 2012. You might have to adjust the script if there's any SQL Server 7 specific syntax.
Use an ETL process to copy data.  Some alternatives:

BCP - Use BCP to dump every table to a RAW or text file, then use BCP to load them into the SQL Server 2012 objects.
Import/Export Wizard in SQL Server 2012 - Use the Import/Export wizard to create data transfer tasks.
SSIS - This is essentially the same as the Import/Export Wizard, but with more functionality.

Overall, this could be a tedious process, but allows you to copy data directly from one database to the other. If you go the BCP route, you will need some storage for the dump files, but this doesn't have to be database storage.

Solution 2

Upgrade the database by using an intermediate step of upgrading to SQL Server 2005. This assumes you have access to SQL Server 2005 media and a server to stand it up on. SQL Server 7 can not be upgraded to SQL Server 2008 as that upgrade path is not supported, but if you can restore the database to SQL Server 2005, you can then successively backup the SQL Server 2005 version and restore it to SQL Server 2012.


What is less time costly? I'm migrating a database from DB2 to MySQL. There are big tables, so it takes a huge amount of time. The question here is:

If there's a table with about 9 millions of records with InnoDB storage engine.

What of the next approaches would take less time?

Create the table without indexes, foreign keys or any constraints. Load the data. Alter table to set the constraints
Create the table with constraints and load the data.

In theory the load of the data on an indexed table is slower than on a non indexed table. But creating an index and foreign keys on big tables takes a lot of time.

Generally it is better to load an InnoDB table with nothing more than the PRIMARY KEY.  Then do one ALTER TABLE to add all the indexes and foreign keys.

If you don't have an explicit PRIMARY KEY, that is bad.  Change a UNIQUE key to PRIMARY, if practical.

But, the foreign keys can be a problem because of dependencies.

Plan A:  ALTER one table at a time, but in an order such that there cannot be any FK issues.

Plan B:  Disable FKs while doing the ALTERs, then enable them.

In InnoDB, the PK is 'clustered' with the data.  Hence, the data is sorted by the PK and stored on disk in that order.  So, it is best to have the PK defined, and to supply the data to the loader in PK order.  The PK+Data is in a BTree.

Secondary keys (and FKs) involve separate BTrees.  Building them incrementally can lead to thrashing in the "buffer pool" (see innodb_buffer_pool_size).  Building such an index separately can involve a unix sort -- more efficient for really big indexes.

I would call 9M rows rather big (90th percentile), but not huge.

Another caution:  UNIQUE keys need to check as they are inserted.  I think that implies that you define UNIQUE keys up front, rather than afterwards.


Can i SELECT two user names by id in another table with join? I have table sb_bans which including BannerID and UnbannerID.
And another one sb_admins which including aid and name.

This is my query:

SELECT sb_bans.*, sb_admins.authid as admin_authid, sb_admins.user as admin_name
FROM sb_bans
LEFT JOIN sb_admins
ON sb_admins.aid = sb_bans.aid
ORDER BY `sb_bans`.`bid`

Table sb_bans look like this:

| bid | BannerID | UnbannerID |
|=====|==========|============|
|  1  |    26    |     26     |
|  2  |    27    |     13     |
|  3  |    10    |     13     |
|  4  |    11    |     27     |
|  5  |    13    |     10     |

Table sb_admins look like this:

| aid |   name   |
|=====|==========|
| 10  | Tomi     |
| 11  | Dominik  |
| 13  | Michal   |
| 26  | Kristian |
| 27  | Daniel   |

And i want make SELECT what will look like:

| bid | BannerID | UnbannerID |
|=====|==========|============|
|  1  | Kristian | Kristian   |
|  2  | Daniel   | Michal     |
|  3  | Tomi     | Michal     |
|  4  | Dominik  | Daniel     |
|  5  | Michal   | Tomi       |

I only know how to make it when i want use name by aid only once.
It is how i have it in my quary code upper.
Please can someone help me configure SQL code to make it work well.
Thanks so mutch.

For MySQL, see here: https://www.db-fiddle.com/f/sjtJBAccd4gvZYZChbuWmx/1

create table sb_bans (
bid     int,
bannerid int,
unbannerid int
);

create table sb_admins (
aid int,
name varchar(10)
);

insert into sb_bans values (1,26,26);
insert into sb_bans values (2,27,13);
insert into sb_bans values (3,10,13);
insert into sb_bans values (4,11,27);
insert into sb_bans values (5,13,10);

insert into sb_admins values (10, 'Tomi');
insert into sb_admins values (11, 'Dominik');
insert into sb_admins values (13, 'Michal');
insert into sb_admins values (26, 'Kristian');
insert into sb_admins values (27, 'Daniel');

select b.bid,
       s.name as banner_id,
       s1.name as unbannerid
  from sb_bans b
  join sb_admins s
    on b.bannerid = s.aid
  join sb_admins s1
    on b.unbannerid = s1.aid


Can I change the SID of an Oracle database? The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

Is there an easy & quick way to change the SIDs of the test databases on the server?

Dropping & recreating of the database is an option for me. But I'm looking for something requiring less time.

The other option to assign names in the clients tnsnames.ora is prone to errors, because they are not administrated centrally.

Compared with the time to drop & create a database on SQL-Server, the amount of time required to create a new Oracle database is excessively greater.
Further on SQL-Server you can rename SQL-Server instances. [ Usually you rename the server where SQL-Server is running and have some problems until you rename the server too ].

You need to recreate the control file

This post by Kaunain Ahmed describes the necessary steps:

  do: alter database backup controlfile to trace;
  extract the "create controlfile" command from the
  background-dump-destination tracefile.
  shutdown the DB.
  Change the DB-Name in your init.ora and change the init.ora
  Change the SID in the /etc/oratab or /var/opt/oracle/oratab
  Change the SID in your environment and source it
  Startup the database to mount-status
  startup mount
  Re-Create the controlfile with the statement from position 2.
  Do a alter database rename global_name to
  10.Change the TNS-Configuration accordingly
  $ORACLE_HOME/network/admin/*.ora Look for SID and GLOBAL_NAME

There are other tools referenced in the thread.

Here's a post by AskTom which references the process in more detail. While it's for 10g, it should still work.


SQL Server to MySQL, which fork? I have to migrate a SQL Server database to  MySQL but i have no idea which fork to use (MySQL, Percona or MariaDB).

We don't have so much experience with MySQL and we haven't used it for high production workload.

Asynchronous replication cross datacenter for disaster recovery failover is required.

The database is used for a read intensive application but we will use cache layers and we are more concerned about new user peak traffic with writes for registrations etc

Is there a fork that fits best our needs?

MariaDB is the open source community driven version that has the  endorsement of all of the unaffiliated MySQL developers.
MySQL is a trademark owned by Oracle and which has continued developed funded by Oracle. Some of this development is pretty awesome. I wouldn't discard it. I believe the Oracle developers are likely more skilled than the community working on MySQL.
PostgreSQL is the best and most advanced open source database. Though it has nothing at all to do with MySQL, I highly suggest you use it instead. It's faster, more spec compliant, and better documented.

The Maria vs MySQL is a mess and all feature comparisons are pretty bad. So read them all! Good luck with it.

MariaDB official comparison: https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-features/


How to recover Postgres replication slave after fatal error On 4 Debian 8 Jessie servers, I have PostgreSQL 9.4.3 master + 3 slaves. After substantial data changes on master, slave logs showed this error:

LOG:  started streaming WAL from primary at 182/0 on timeline 1
FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000018200000000 has already been removed

What steps do i need to restore/rebuild the slaves?

If you set up an archive_command and have your WAL segments saved elsewhere, you can just point a restore_command in the recovery.conf on each of the secondaries at your WAL archive, and they should grab the next needed segment and carry on happily.

If you didn't set up an archive_command, you'll need to take a pg_basebackup on each of your secondaries, because without that WAL segment, they can never catch up. Since you're on 9.4, I would recommend also setting up a replication slot, which will prevent the primary from recycling a WAL segment that is required for the streaming replicas.

You can find more out about this with the replication slot documentation.

Otherwise, you need to have an archive_command set that saves the WAL elsewhere to catch up, or you need to adjust wal_keep_segments high enough on the primary that it doesn't recycle files under heavy load on your system.

Setting up the archive_command in your postgresql.conf is covered in the Continuous Archiving and Point In Time Recovery documentation.

An example of a similar situation to the one you are in, and one potential solution to prevent it, is covered in Offsite Replication Problems and How to Solve Them.

Hopefully this is enough information for you to get your replicas back to a working state. =)


How can I RAISE_APPLICATION_ERROR and do an INSERT in the same trigger? From what I see, having the RAISE_APPLICATION_ERROR cancels the other operations that I would have in the trigger. I tried writing first the INSERT and then the RAISE_APPLICATION_ERROR hoping that they get executed in the order in which they are read but it doesn't work like this...The error is thrown and the INSERT is canceled. How could I make both of the operations get executed (from the same trigger) ?
I also tried creating two triggers .. one of them doing the RAISE_APPLICATION_ERROR part and the other doing the INSERT and establishing the order using FOLLOWS and PRECEDES but it didn't work.
The trigger should block the DROP DDL (that's what for I used the RAISE_APPLICATION_ERROR) and it also should do an insert in a LOG type table (that's what for I used the INSERT).

Log type operations are usually done via AUTONOMOUS_TRANSACTION procedures.

Don't have a logging package, get one from GitHub.

https://github.com/OraOpenSource/Logger


SQL 2016: OPENDATASOURCE Msg 7357, Level 16, State 2, Line 1 SELECT MAX(VersionNumber)
FROM OPENDATASOURCE(
    'SQLNCLI',
    'Data Source = I.P.V.4;User ID = sa;Password = pswd;catalog=MyDB'
    ).MyDB.dbo.tbl_Version

results in a "speak to the red-hand" error that does not occur on over 20 other remote SQL instances inquired-of from the same source SQL instance:

  Msg 7357, Level 16, State 2, Line 1 Cannot process the object ""MyDB"."dbo"."tbl_Version"". The OLE DB provider "SQLNCLI11" for
  linked server "(null)" indicates that either the object has no columns
  or the current user does not have permissions on that object.

Source is SQL 2016, targets vary from 2008R2, 2012, 2014 up to 2016 SP1 CU2, Enterprise and Standard Editions.

I used Idera's SQL Admin Toolset to get the server configurations from a working baseline instance, and compare it to 2 working instances, and 3 non-working instances.

All 3 of the latter reside in a Hong Kong Rackspace datacenter.  The working instances are in either a US or a UK Rackspace datacenter.

Aside from the collation of the non-working instances being different, there are no differences of merit (MAXDOP and MAXMEM don't count).

I also used that toolset's SQL Database Configuration tool and set the baseline as a working 2016 DB, then compared it to another working instance's 2014 DB, and the not-working 2016 DB - the only difference is the SQL Compatibility Level.

Ad Hoc Distributed Queries is running "ON", on all servers.

All 3 client protocols are enabled.  All TCP/IP configurations in SQL Client configs are identical.

I can connect via SSMS from my laptop using the I.P. of the not-working instance, using the same SQL login and password, and run a SELECT * from the same table, and get all rows.  I can see everything via SSMS.

I opened Server Objects/Linked Servers/Providers/SQLNCLI11 and looked at the properties on working and non-working instances - identical - the DisallowAdhocAccess setting is "OFF".

Although BOL doesn't state that after the RECONFIGURE to set the Ad Hoc Distributed Queries config to "ON", one must restart SQL, is this an omission/something that is really "required"?

Having Googled for the Msg line, plus most of the error text and found just 10 hits, none of which are useful, I took out the text message and still found nothing relevant that includes the "Msg 7357" portion.

Does anyone suspect a firewall issue? I doubt that because I can get through with SSMS, as I said above...

What (else) am I missing?

Thanks for any insights.

Found the issue...

The table is no longer a table - it's a SYNONYM over the same table schema that has moved to a named schema from dbo, and has a new name: SystemVersion.  OLE DB provider choked! (SQLNCLI or SQLOLEDB)


Distribution Server (1) Replicating Source Server (2) To Destination Server (3) I'm setting up replication from a source server to a destination server using a third party distribution server.  I've added the source server to the list of publishers on the distribution server (Distributor Properties => Add => Add SQL Server).  When I go to add a new publication on the distribution server, I don't see the source server's databases as options to replicate to the destination server.  Also, if I select the option "Publisher Properties" on the distribution server, to see a list of databases that are checked - either transactional or merge - none of the databases of the source server are listed.

Oddly enough, if the publisher was an Oracle server (it is not), I see the option to add that by entering the server on the distribution server, so I'm confused why I'm not seeing it for SQL Server.

Added (current answer is wrong)

If I do the steps suggested in the current answer, then look at the Job Agent step, what I get is:

-Publisher [SourceServer] -PublisherDB [DB] -Distributor [SourceServer] -Publication [NAME] [Security Related Info]

In addition sp_get_distributor also shows the wrong server as the distributor (SourceServer).

Let me explain again:

The publisher should be the source server.
The distributor should be the distributor server.
Therefore "I've added the source server to the list of publishers on the distribution server" and then creating the publication on the source server does not correctly set up the proper distributor server.

Also, to correct Microsoft's own website at this link, if I follow to the letter what they say, they are flat out wrong on step 4

  Specify a Distributor if distribution has not been configured on the
  server. For more information about configuring distribution, see
  Configure Publishing and Distribution. If you specify on the
  Distributor page that the Publisher server will act as its own
  Distributor (a local Distributor), and the server is not configured as
  a Distributor, the New Publication Wizard will configure the server.
  You will specify a default snapshot folder for the Distributor on the
  Snapshot Folder page. The snapshot folder is simply a directory that
  you have designated as a share; agents that read from and write to
  this folder must have sufficient permissions to access it. For more
  information about securing the folder appropriately, see Secure the
  Snapshot Folder. If you specify that another server should act as the
  Distributor, you must enter a password on the Administrative Password
  page for connections made from the Publisher to the Distributor. This
  password must match the password specified when the Publisher was
  enabled at the remote Distributor. For more information, see Configure
  Distribution.

In 2012, there is no option to specify a distributor.  So, if I go to their link to set one up:

  Right-click the Replication folder, and then click Configure
  Distribution.

There is no Configure Distribution option.

I'm not sure if this helps you or not, but In SSMS in all versions (2005+), if you haven't configured Distribution, you'll see the option configure distribution,

but if you've configured a distribution or have one on the server, then you'll have the below menu and won't see configure distribution.


Suppress a event from eventviewer Is there any way to suppress an event logging in event viewer.
I am getting below event id 10028 which is getting logged in eventvwr.I know the reason why it is getting logged. Can I know if any way of suppressing this event id in eventvwr.

There is no easy way to suppress specific events (with the exception of log backups using TF3226 or using the -n startup parameter which suppresses all events as ably described here).

What you should probably be doing is addressing the root cause of the issue.  If you have decided not to do that, you can simply suppress the ids when you report on your event log.  There are a few ways to do this:

Use Filters in Windows Event Viewer
Click the 'Filter' button on the right-hand side, and enter the ids you want to suppress preceded with a minus sign, eg

Using Powershell cmdlet Get-EventLog and filtering the output, eg

Get-EventLog Application -Newest 100 | Where-Object { $_.EventID -ne 3014} | Format-Table TimeWritten, EventID, Message -auto

In summary, try filtering the event ids at your reporting time, rather than trying to suppress them altogether.  At least if they are recorded in the event log, they could be analysed by someone else or for another purpose.

HTH