Why is Database Administration so hard? I know a lot of Database Administrators and they are all over 28-29 years old.

Is all database administration like that? I mean, is this about getting experience more than at least 7-8 years?

Or is being a database administrator so hard?

The position requires a broad spectrum of knowledge ranging from development to system administration and even management.  Not only must a DBA know about backup, recovery, internal operations, memory and security, but also how to communicate with both developers and management.  A DBA could be giving a high level presentation to management, helping a developer tune a query, provisioning disk space for a new system and restoring data from backup all within the same hour.  These responsibilities require a wealth of knowledge with little overlap.

The consequences of failure are usually greater for a DBA than a developer.  DBAs often support dozens, even hundreds of different applications and systems most of which are vital to the success of the company.  A security breach, recovery failure, or performance problem could have far reaching and devastating ramifications.  This requires a level of knowledge and experience that can’t be gained in a short amount of time.

The better a DBA does their job the less visibility they have.  A DBA with a database that is secure, recoverable, available, and performing well will lack recognition.  DBAs get noticed when there are problems.  Not only do they get noticed when their problems are self-inflicted, they also get blamed when the database has problems due to poor coding, improper network setup, or incorrectly configured storage.

I switched from developer to DBA when I was 29.  For me the things that make being a DBA difficult also make it rewarding.  I enjoy absorbing and using a wide spectrum of knowledge, and the greater opportunity for failure makes the avoidance thereof all the more meaningful whether others see that or not.

Like most skills, learning to be a dba takes time. Becoming a good dba takes longer. The more you read and learn the more knowledge you can apply.

Another path to becoming a dba is report writing or as an application expert. The more time you spend with hands on SQL, the more you will learn about how dbs work. Becoming proficient in SQL queries will provide a good starting point for becoming a dba.


Mariadb is giving warning for this query and table Please create the following table,

CREATE TABLE test3 (

  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test3 (meta_key, meta_value) VALUES
('utm_source', 'facebook'),
('lead_date', '20180915'),
('utm_source', 'facebook'),
('lead_date', '20180905');

then run the following query

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date') as q0
where date(meta_value)<CURDATE()   ;

you will get following warning while running them (use adminer to see them)

Level   Code    Message
Warning 1292    Incorrect datetime value: 'facebook'
Warning 1292    Incorrect datetime value: 'facebook'

Ideally, this should not have happened. Can someone explain what's wrong here?

Update
This query is actually part of a bigger set of queries. It has been reduced to this level to highlight the warnings.
The issue is not with CURDATE. if you write date(meta_value) is NOT NULL it will give warnings.
In fact, it will give warnings with all date related functions in where clause.

It does not seem that it could be an optimization issue.
Try the following queries with a join. At this point, the optimizer cannot optimize since I am comparing with another table.
It will still give a warning.

drop table if exists test3;
CREATE TABLE test3 (

  meta_key varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  meta_value varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO test3 (meta_key, meta_value) VALUES
('utm_source', 'facebook'),
('lead_date', '20180915'),
('utm_source', 'facebook'),
('lead_date', '20180905');

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date') as q0 , (select date('20200101') as
compare) as d
where date(meta_value)<compare  ;

Now try another query:

select meta_value
from (select meta_value
      from test3
      where meta_key='lead_date' limit 1000000) as q0
where date(meta_value)<CURDATE()  ;

This will not give an error.

You expect the subselect (select meta_value from test3 where meta_key='lead_date') to materialize only rows that contain values in meta_value that can be converted to the DATE data type, but this is not happening: the optimizer inlines the subselect and simultaneously applies two predicates: meta_key='lead_date' and date(meta_value)<CURDATE() to all rows in the table, which obviously fails for rows containing 'facebook' in meta_value.

Date format is '2018-09-15', not '20180915'.

Don't use subqueries when you don't need to.

select  meta_value
    from test3
    where  meta_key = 'lead_date'
    where  meta_value < '2020-01-01';

Have

PRIMARY KEY(meta_key)

In the long run, you will find EAV schema to be a pain.


Check if a primary key value is referred in any other table I am using oracle database.

Is there a way I can check if the primary key value in one table is being used as a foreign key value (have any associations) in any other table?

Example: if I have the Student_id as primary key in STUDENT table. And there are other tables in the schema like CLASS, MARKS, SPORTS etc.

I have student_id=100 with me. I need to find out all the references of this student_id value '100' in the entire schema wherever it is linked as a foreign key from the parent table STUDENT.

You can check dba_constraints:

Get the primary key constraint name:

select constraint_name
from dba_constraints
where owner = '<SCHEMA_NAME>' and table_name = 'STUDENT' and constraint_type = 'P';

Then check where that constraint is used:

select owner, table_name, constraint_name
from dba_constraints
where r_constraint_name = '<result from previous query>' and constraint_type = 'R';

To get the column name of the referential constraint, you can use dba_cons_columns:

select owner, table_name, column_name
from  dba_cons_columns
where constraint_name = '<constraint name from previous query>';

Plugging all of this together, you can do something like:

  SELECT fk.owner, fk.table_name, col.column_name
    FROM dba_constraints pk
    JOIN dba_constraints fk
      ON pk.constraint_name = fk.r_constraint_name
     AND fk.constraint_type = 'R'
    JOIN dba_cons_columns col
      ON fk.constraint_name = col.constraint_name
   WHERE pk.owner = :owner AND pk.table_name = 'STUDENT' AND pk.constraint_type = 'P';

select fk.table_name, col.column_name
from USER_constraints fk
JOIN user_cons_columns col
    ON fk.constraint_name = col.constraint_name
where constraint_type='R'
 and r_constraint_name in (
     select constraint_name
     from user_constraints
     where constraint_type in ('P','U')
       and table_name='TABLE_NAME'
);


Character Set Database Migration This is a follow-up question to a previous thread I had posted. We're in the process of migrating to a new character set. Now, I am a programmer and we have DBA's who will be performing the work but I'd like to get a little bit more information about the process. Our current character set is WE8ISO8859P1 and uses BYTE character semantics. However, we are migrating to AL32UTF8. A new database was created containing the AL32UTF8 character set using BYTE semantics. The DBA ran a script on some tables that checked to see if there would be any issues (I believe it was CSALTER). After running the script, it said that some of the rows in our table would be truncated. That table contains a VARCHAR2(BYTE) column that's set to 4000 BYTES. The rows that resulted with potential issues all contain special characters. My question is, how can we export/import the tables in our database without having information truncated? Why is it saying that it would be truncated? My first thought was that since we changed the character set, it may be storing some of the characters from the old database table different than when it's stored into the new database with the character set using up more storage.

The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.

By way of example, this query shows you that the Euro symbol (0x80 in WE8ISO8859P1) becomes 2 bytes in AL32UTF8:

SQL>  select length(convert(chr(128),'AL32UTF8','WE8ISO8859P1')) from dual
  2  /
LENGTH(CONVERT(CHR(128),'AL32UTF8','WE8ISO8859P1'))
---------------------------------------------------
                                                  2
SQL>

To list all characters that will be affected by the change, you can use the following query:

with n as
(
select level as c from dual
connect by level <= 255
)
select c as "WE8ISO8859P1 value",
       '"'||chr(c)||'"' as Character,
       length(convert(chr(c),'AL32UTF8','WE8ISO8859P1')) as "New length"
from n
where length(convert(chr(c),'AL32UTF8','WE8ISO8859P1'))>1;

Unfortunately the maximum length a CHAR or VARCHAR string can be in Oracle is 4000 bytes. The only option available to you if characterset conversion pushes you over this limit is to convert the columns to use the CLOB datatype, but we warned - CLOBs are difficult to deal with and can present challenges.

You could first create empty tables by importing only metadata (data pump can do that). Then you can run a script than changes BYTE columns to CHAR before importing data. This way data will not be truncated since effective maximum length will be the same as in old database.


How to set an Oracle user's default schema? I created a few new users in Oracle.  However, when running sqlplus, they all need to fully qualified the table names in query.  What's the best way to set a default schema for these new users?

There is nothing like PostgreSQL's set search_path in Oracle.

The closest thing I can think of would be a logon trigger for the user that run's an ALTER SESSION SET CURRENT_SCHEMA ...

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
EXCEPTION
  when others
    then null; -- prevent a login failure due to an exception
END;
/

If the list of users isn't too long, you can create a database logon trigger so you don't have to create that trigger for each user:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
BEGIN
    if (user in ('TOM', 'DICK', 'HARRY')) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
  when others
    then null; -- prevent a login failure due to an exception
END logon_trg;
/

Of course the list of users where you want to change the default schema, can also be taken from a table. In that case you only need to insert or delete rows from there in order to "activate" this feature (rather than re-creating the trigger each time).

Another option would be to create synonyms each time you create user that point to the real tables. You could automate that using a stored procedure that loops through all tables in one schema and creates the synonyms for them in the other schema.

Unless all your Oracle users work on the same tables I would strongly advise against using public synonyms which you would have to create only once - they can cause a lot of trouble if different application users exist in your installation.

Edit:

Following Alex's suggestion, here is a logon trigger that checks the role rather than a username:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
declare
  has_role boolean;
BEGIN

    has_role := dbms_session.is_role_enabled('FOOBAR_ROLE');

    if (has_role) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception
   when others
      then null; -- prevent a login failure due to an exception
END logon_trg;
/

I wouldnt think there is a way to set one. The user is the schema.
AFAIK you can only set default tablespace.


How to get a column A which has the smaller column B grouping by column C? I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column.
Here a set of data to explain my thoughts:

CREATE TABLE DATAS (
    ID INT(2) ,
    CATEGORY INT(2) ,
    ORD INT(1)
);

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);

Result expected:

ID   CATEGORY
-------------
3    1
4    2

Bonus question, how to retrieve just one ID even if there's some duplicates for the "order" column (ORD) ?

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);

Result expected (using a MIN for example or other suggestion):

ID   CATEGORY
-------------
3    1
4    2
6    3

Oracle or MySQL queries are welcomed, thanks a lot.

Oracle solution to both problems:

SELECT ID, Category FROM (
   SELECT FIRST_VALUE(ID) OVER
      (PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
      , Category, ID FROM DATAS
   )
   WHERE ID = IDOfSmallestOrdForCategory;

Generic solution to both problems:

SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;

Oracle DDL/DML:

CREATE TABLE DATAS (
    ID       Integer,
    CATEGORY Integer,
    ORD      Integer
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);

This returns an answer to the first part:

mysql> SELECT ID, CATEGORY, MIN(ORD) FROM DATAS GROUP BY CATEGORY;
+------+----------+----------+
| ID   | CATEGORY | MIN(ORD) |
+------+----------+----------+
|    1 |        1 |        1 |
|    4 |        2 |        1 |
|    6 |        3 |     NULL |
+------+----------+----------+
3 rows in set (0.00 sec)

The answer to second part is a bit more complex. You may only want one answer, but which answer do you want? Greatest ID? Least CATEGORY? Subjective. Otherwise this might do it:

mysql> SELECT * FROM (SELECT ID, CATEGORY, MIN(ORD) AS min FROM DATAS GROUP BY ID, CATEGORY) m GROUP BY min ORDER BY ID ASC;
+------+----------+------+
| ID   | CATEGORY | min  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    6 |        3 | NULL |
+------+----------+------+
4 rows in set (0.01 sec)


How to use variables with RESTORE FILELISTONLY Storing the result of RESTORE FILELISTONLY in a temporary table called #filepaths.

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = 'Z:\BACKUPS\my_database_backup.bak'
END
GO

insert into #filepaths
exec sp_restore

How can I get 'Z:\BACKUPS\my_database_backup.bak' into a variable? So that the script looks similar to this.

DECLARE @BACKUP_PATH as nvarchar(max) = 'Z:\BACKUPS\my_database_backup.bak'

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = @BACKUP_PATH
END
GO

insert into #filepaths
exec sp_restore

Thanks,
Craig

I think you have things just a little messed up. How about:

CREATE PROCEDURE dbo.usp_restore
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO

Creating the procedure should be completely separate from any individual invocation. So in a different window, or two weeks later, you should be able to do this (again, completely separately from the creation of the stored procedure):

DECLARE @bp NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

INSERT #filepaths EXEC dbo.usp_restore @backup_path = @bp;

Some other commentary you should look into:

Why you shouldn't use the sp_ prefix
Why you should always use the schema prefix

If you don't want a stored procedure to help with this (why not?), sure you can do this in a much messier way. Assuming you already have a #temp table, #t, created with the right columns and data types:

DECLARE @path NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

DECLARE @sql NVARCHAR(MAX) = N'RESTORE FILELISTONLY FROM DISK = @path;';

INSERT #t EXEC master.sys.sp_executesql @sql, N'@path NVARCHAR(MAX)', @path;

But still, why not just have a stored procedure ready when you need it, instead of porting around all this bulky code?

I think you can only do this with dynamic SQL. Replace the body of your SP with the following:

DECLARE @BACKUP_PATH as nvarchar(max) = 'Z:\BACKUPS\my_database_backup.bak'

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN

  DECLARE @SQL NVARCHAR(MAX)

  SQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @BACKUP_PATH  + '''

  EXEC SP_ExecuteSql @SQLToExecute

END
GO

insert into #filepaths
exec sp_restore


Is it possible to have two copies of a table clustered in different arrangements seamlessly? I have a table with around 100M rows.  It only gets data inserted once/day but we need to do selects a lot.  The selects are usually simple but need to return 100s of thousands of rows sometimes.

It's unique based on three columns node_id, pricedate, hour which are integer, timestamp, integer respectively.  It was slow for most queries but I clustered it to node_id, pricedate and that fixed the slowness for most queries.  Those queries were of the type:

select * from mytable where node_id in (1,2,3,4)

We still occasionally need to do queries like:

select * from mytable where pricedate>='2016-05-01'

These are still slow because it's clustered by node_id first. We have an index on pricedate already. The issue is that the users often need enough data that the query engine throws out the index and uses a seq scan. Once it's using a seq scan, it benefits greatly from having the data clustered in the way that it is being queried.  This leads to the problem that I have where some queries benefit from one clustering and other queries from the other:

It would be nice if there was a way to have two physical copies of the table where one copy is clustered one way and the other is clustered another but user access to it appears as though there is only 1 table and the DB engine would ensure they're in sync. Obviously there'd be write penalties in doing this but that's inconsequential for our usage.

Would something like this be possible?

I'm guessing there isn't a built in way to do what I describe. To do it anyway I guess I'd make a table called mytable_dup with the same unique key constraint but with the alternate clustering and then setup triggers to insert to it whenever the master is inserted/updated/deleted.  That seems doable but from here, would there be a reasonable way to select from the duplicated table that will be efficient?

I'm running PostgreSQL 9.4 at home and 9.5 on Google.

To have the data held in two different physical sequences one must store the data twice. This can be achieved by defining a second, covering index. A covering index contains all of the columns required by a query. This way the optimiser has no need to refer to the base table to read further values and is unlikely to revert to a base table scan for the query plan. The optimiser performs an index-only scan. As the choice of index is made by the optimiser and not be the programmer no application code need be altered to take advantage during reads. No further objects are required to maintain consistency during writes.

The columns used in the WHERE clause will be the leading columns of the index. The sequence of the other columns is unimportant. As and when PostgreSQL supports the INCLUDE syntax this index can be changed to use it.

The downsides include a) extra disk to store this data b) additional latency during writes to maintain the index c) more system maintenance required for reorgs and such, and d) as queries change the covering index(es) have to change to match e) correspondingly larger and longer backups and restores.

So assuming you want to keep it simple for the users of the select clause, and not knowing how they are executing it...

how about using a function?

One alternative:

Pass the entire select clause as a parameter(s) to a function,

parse it for the where clause column

and then direct it to the table or materialized view as suggested by @dezso?


Why can't RDBM's cluster the way NoSQL does? One of the big plusses for nosql DBMS is that they can cluster more easily.  Supposedly with NoSQL you can create hundreds of cheap machines that store different pieces of data and query it all at once.

My question is this, why can't relational DBMS do this like mysql or sql server?  Is it that the vendors just haven't figured out a technical way to do this with their existing product, or is there some issue with the relational model that prevents this from being feasible?  What is so great about the NoSQL way of storing and accessing data (key/value, documents, etc) that makes clustering easier, if this is true at all?

Distributed Database Systems 101

Or, Distributed Databases - what the FK does 'web scale' actually mean?

Distributed database systems are complex critters and come in a number of different flavours.  If I dig in to the depths of my dimly remembered studies on this at university I'll try to explain some of the key engineering problems to building a distributed database system.

First, some terminology

ACID (Atomicity, Consistency, Isolation and Durability) properties: These are the key invariants that have to be enforced for a transaction to be reliably implemented without causing undesirable side effects.

Atomicity requires that the transaction complete or rollback completely.  Partially finished transactions should never be visible, and the system has to be built in a way that prevents this from happening.

Consistency requires that a transaction should never violate any invariants (such as declarative referential integrity) that are guaranteed by the database schema.  For example, if a foreign key exists it should be impossible to insert a child record with a reverence to a non-existent parent.

Isolation requires that transactions should not interfere with each other.  The system should guarantee the same results if the transactions are executed in parallel or sequentially.  In practice most RDBMS products allow modes that trade off isolation against performance.

Durability requires that once committed, the transaction remains in persistent storage in a way that is robust to hardware or software failure.

I'll explain some of the technical hurdles these requirements present on distributed systems below.

Shared Disk Architecture: An architecture in which all processing nodes in a cluster have access to all of the storage.  This can present a central bottleneck for data access.  An example of a shared-disk system is Oracle RAC or Exadata.

Shared Nothing Architecture: An architecture in which processing nodes in a cluster have local storage that is not visible to other cluster nodes.  Examples of shared-nothing systems are Teradata and Netezza.

Shared Memory Architecture: An architecture in which multiple CPUs (or nodes) can access a shared pool of memory.  Most modern servers are of a shared memory type.  Shared memory facilitates certain operations such as caches or atomic synchronisation primitives that are much harder to do on distributed systems.

Synchronisation: A generic term describing various methods for ensuring consistent access to a shared resource by multiple processes or threads.  This is much harder to do on distributed systems than on shared memory systems, although some network architectures (e.g. Teradata's BYNET) had synchronisation primitives in the network protocol.  Synchronisation can also come with a significant amount of overhead.

Semi-Join: A primitive used in joining data held in two different nodes of a distributed system.  Essentially it consists of enough information about the rows to join being bundled up and passed by one node to the other in order to resolve the join.  On a large query this could involve significant network traffic.

Eventual Consistency: A term used to describe transaction semantics that trade off immediate update (consistency on reads) on all nodes of a distributed system for performance (and therefore higher transaction throughput) on writes.  Eventual consistency is a side effect of using Quorum Replication as a performance optimisation to speed up transaction commits in distributed databases where multiple copies of data are held on separate nodes.

Lamport's Algorithm: An algorithm for implementing mutual exclusion (synchronisation) across systems with no shared memory.  Normally mutual exclusion within a system requires an atomic read-compare-write or similar instruction of a type normally only practical on a shared memory system.  Other distributed synchronisation algorithms exist, but Lamport's was one of the first and is the best known.  Like most distributed synchronisation mechanisms, Lamport's algorithm is heavily dependent on accurate timing and clock synchronisation beteen cluster nodes.

Two Phase Commit (2PC): A family of protocols that ensure that database updates involving multiple physical systems commit or roll back consistently.  Whether 2PC is used within a system or across multiple systems via a transaction manager it carries a significant overhead.

In a two-phase commit protocol the transaction manager asks the participating nodes to persist the transaction in such a way that they can guarantee that it will commit, then signal this status.  When all nodes have returned a 'happy' status it then signals the nodes to commit.  The transaction is still regarded as open until all of the nodes send a reply indicating the commit is complete.  If a node goes down before signalling the commit is complete the transaction manager will re-query the node when it comes back up until it gets a positive reply indicating the transaction has committed.

Multi-Version Concurrency Control (MVCC): Managing contention by writing new versions of the data to a different location and allowing other transactions to see the old version of the data until the new version is committed.  This reduces database contention at the expense of some additional write traffic to write the new version and then mark the old version as obsolete.

Election Algorithm: Distributed systems involving multiple nodes are inherently less reliable than a single system as there are more failure modes.  In many cases some mechanism is needed for clustered systems to deal with failure of a node.  Election algorithms are a class of algorithms used to select a leader to coordinate a distributed computation in situations where the 'leader' node is not 100% determined or reliable.

Horizontal Partitioning: A table may be split across multiple nodes or storage volumes by its key.  This allows a large data volume to be split into smaller chunks and distributed across storage nodes.

Sharding: A data set may be horizontally partitioned across multiple physical nodes in a shared-nothing architecture.  Where this partitioning is not transparent (i.e. the client must be aware of the partition scheme and work out which node to query explicitly) this is known as sharding.  Some systems (e.g. Teradata) do split data across nodes but the location is transparent to the client; the term is not normally used in conjunction with this type of system.

Consistent Hashing: An algorithm used to allocate data to partitions based on the key.  It is characterised by even distribution of the hash keys and the ability to elastically expand or reduce the number of buckets efficiently.  These attributes make it useful for partitioning data or load across a cluster of nodes where the size can change dynamically with nodes being added or dropping off the cluster (perhaps due to failure).

Multi-Master Replication: A technique that allows writes across multiple nodes in a cluster to be replicated to the other nodes.  This technique facilitates scaling by allowing some tables to be partitioned or sharded across servers and others to be synchronised across the cluster.  Writes must be replicated to all nodes as opposed to a quorum, so transaction commits are more expensive on a multi-master replicated architecture than on a quorum replicated system.

Non-Blocking Switch: A network switch that uses internal hardware parallelism to achieve throughput that is proportional to the number of ports with no internal bottlenecks.  A naive implementation can use a crossbar mechanism, but this has O(N^2) complexity for N ports, limiting it to smaller switches.  Larger switches can use more a complex internal topology called a non-blocking minimal spanning switch to achieve linear throughput scaling without needing O(N^2) hardware.

Making a distributed DBMS - how hard can it be?

Several technical challenges make this quite difficult to do in practice.  Apart from the added complexity of building a distributed system the architect of a distributed DBMS has to overcome some tricky engineering problems.

Atomicity on distributed systems: If the data updated by a transaction is spread across multiple nodes the commit/rollback of the nodes must be coordinated.  This adds a significant overhead on shared-nothing systems.  On shared-disk systems this is less of an issue as all of the storage can be seen by all of the nodes so a single node can coordinate the commit.

Consistency on distributed systems: To take the foreign key example cited above the system must be able to evaluate a consistent state.  For example, if the parent and child of a foreign key relationship could reside on different nodes some sort of distributed locking mechanism is needed to ensure that outdated information is not used to validate the transaction.  If this is not enforced you could have (for example) a race condition where the parent is deleted after the its presence is verified before allowing the insert of the child.

Delayed enforcement of constraints (i.e. waiting until commit to validate DRI) requires the lock to be held for the duration of the transaction.  This sort of distributed locking comes with a significant overhead.

If multiple copies of data are held (this may be necessary on shared-nothing systems to avoid unnecessary network traffic from semi-joins) then all copies of the data must be updated.

Isolation on distributed systems: Where data affected on a transaction resides on multiple system nodes the locks and version (if MVCC is in use) must be synchronised across the nodes.  Guaranteeing serialisability of operations, particularly on shared-nothing architectures where redundant copies of data may be stored requires a distributed synchronisation mechanism such as Lamport's Algorithm, which also comes with a significant overhead in network traffic.

Durability on distributed systems: On a shared disk system the durability issue is essentially the same as a shared-memory system, with the exception that distributed synchronisation protocols are still required across nodes.  The DBMS must journal writes to the log and write the data out consistently.  On a shared-nothing system there may be multiple copies of the data or parts of the data stored on different nodes.  A two-phase commit protocol is needed to ensure that the commit happens correctly across the nodes.  This also incurs significant overhead.

On a shared-nothing system the loss of a node can mean data is not available to the system.  To mitigate this data may be replicated across more than one node.  Consistency in this situation means that the data must be replicated to all nodes where it normally resides.  This can incur substantial overhead on writes.

One common optimisation made in NoSQL systems is the use of quorum replication and eventual consistency to allow the data to be replicated lazily while guaranteeing a certain level of resiliency of the data by writing to a quorum before reporting the transaction as committed.  The data is then replicated lazily to the other nodes where copies of the data reside.

Note that 'eventual consistency' is a major trade-off on consistency that may not be acceptable if the data must be viewed consistently as soon as the transaction is committed.  For example, on a financial application an updated balance should be available immediately.

Shared-Disk systems

A shared-disk system is one where all of the nodes have access to all of the storage.  Thus, computation is independent of location.  Many DBMS platforms can also work in this mode - Oracle RAC is an example of such an architecture.

Shared disk systems can scale substantially as they can support a M:M relationship between storage nodes and processing nodes.  A SAN can have multiple controllers and multiple servers can run the database.  These architectures have a switch as a central bottleneck but crossbar switches allow this switch to have a lot of bandwidth.  Some processing can be offloaded onto the storage nodes (as in the case of Oracle's Exadata) which can reduce the traffic on the storage bandwidth.

Although the switch is theoretically a bottleneck the bandwidth available means that shared-disk architectures will scale quite effectively to large transaction volumes.  Most mainstream DBMS architectures take this approach because it affords 'good enough' scalability and high reliability.  With a redundant storage architecture such as fibre channel there is no single point of failure as there are at least two paths between any processing node and any storage node.

Shared-Nothing systems

Shared-nothing systems are systems where at least some of the data is held locally to a node and is not directly visible to other nodes.  This removes the bottleneck of a central switch, allowing the database to scale (at least in theory) with the number of nodes.  Horizontal partitioning allows the data to be split across nodes; this may be transparent to the client or not (see Sharding above).

Because the data is inherently distributed a query may require data from more than one node.  If a join needs data from different nodes a semi-join operation is used to transfer enough data to support the join from one node to another.  This can result in a large amount of network traffic, so optimising the distribution of the data can make a big difference to query performance.

Often, data is replicated across nodes of a shared-nothing system to reduce the necessity for semi-joins.  This works quite well on data warehouse appliances as the dimensions are typically many orders of magnitude smaller than the fact tables and can be easily replicated across nodes.  They are also typically loaded in batches so the replication overhead is less of an issue than it would be on a transactional application.

The inherent parallelism of a shared-nothing architecture makes them well suited to the sort of table-scan/aggregate queries characteristic of a data warehouse.  This sort of operation can scale almost linearly with the number of processing nodes.  Large joins across nodes tend to incur more overhead as the semi-join operations can generate lots of network traffic.

Moving large data volumes is less useful for transaction processing applications, where the overhead of multiple updates makes this type of architecture less attractive than a shared disk.  Thus, this type of architecture tends not to be used widely out of data warehouse applications.

Sharding, Quorum Replication and Eventual Consistency

Quorum Replication is a facility where a DBMS replicates data for high availability.  This is useful for systems intended to work on cheaper commodity hardware that has no built-in high-availability features like a SAN.  In this type of system the data is replicated across multiple storage nodes for read performance and redundant storage to make the system resilient to hardware failure of a node.

However, replication of writes to all nodes is O(M x N) for M nodes and N writes.  This makes writes expensive if the write must be replicated to all nodes before a transaction is allowed to commit.  Quorum replication is a compromise that allows writes to be replicated to a subset of the nodes immediately and then lazily written out to the other nodes by a background task.  Writes can be committed more quickly, while providing a certain degree of redundancy by ensuring that they are replicated to a minimal subset (quorum) of nodes before the transaction is reported as committed to the client.

This means that reads off nodes outside the quorum can see obsolete versions of the data until the background process has finished writing data to the rest of the nodes.  The semantics are known as 'Eventual Consistency' and may or may not be acceptable depending on the requirements of your application but mean that transaction commits are closer to O(1) than O(n) in resource usage.

Sharding requires the client to be aware of the partitioning of data within the databases, often using a type of algorithm known as 'consistent hashing'.  In a sharded database the client hashes the key to determine which server in the cluster to issue the query to.  As the requests are distributed across nodes in the cluster there is no bottleneck with a single query coordinator node.

These techniques allow a database to scale at a near-linear rate by adding nodes to the cluster.  Theoretically, quorum replication is only necessary if the underlying storage medium is to be considered unreliable.  This is useful if commodity servers are to be used but is of less value if the underlying storage mechanism has its own high availability scheme (for example a SAN with mirrored controllers and multi-path connectivity to the hosts).

For example, Google's BigTable does not implement Quorum Replication by itself, although it does sit on GFS, a clustered file system that does use quorum replication.  BigTable (or any shared-nothing system) could use a reliable storage system with multiple controllers and partition the data among the controllers.  Parallel access would then be achieved through partitioning of the data.

Back to RDBMS platforms

There is no inherent reason that these techniques could not be used with a RDBMS.  However lock and version management would be quite complex on such a system and any market for such a system is likely to be quite specialised.  None of the mainstream RDBMS platforms use quorum replication and I'm not specifically aware of any RDBMS product (at least not one with any significant uptake) that does.

Shared-disk and shared-nothing systems can scale up to very large workloads.  For instance, Oracle RAC can support 63 processing nodes (which could be large SMP machines in their own right) and an arbitrary number of storage controllers on the SAN.  An IBM Sysplex (a cluster of zSeries mainframes) can support multiple mainframes (each with substantial processing power and I/O bandwidth of their own) and multiple SAN controllers.  These architectures can support very large transaction volumes with ACID semantics, although they do assume reliable storage.  Teradata, Netezza and other vendors make high-performance analytic platforms based on shared-nothing designs that scale to extremely large data volumes.

So far, the market for cheap but ultra-high volume fully ACID RDBMS platforms is dominated by MySQL, which supports sharding and multi-master replication.  MySQL does not use quorum replication to optimise write throughput, so transaction commits are more expensive than on a NoSQL system.  Sharding allows very high read throughputs (for example Facebook uses MySQL extensively), so this type of architecture scales well on read-heavy workloads.

An interesting debate

BigTable is a shared-nothing architecture (essentially a distributed key-value pair) as pointed out by Michael Hausenblas below.  My original evaluation of it included the MapReduce engine, which is not a part of BigTable but would normally be used in conjunction with it in its most common implementations (e.g. Hadoop/HBase and Google's MapReduce framework).

Comparing this architecture with Teradata, which has physical affinity between storage and processing (i.e. the nodes have local storage rather than a shared SAN) you could argue that BigTable/MapReduce is a shared disk architecture through the globally visible parallel storage system.

The processing throughput of a MapReduce style system such as Hadoop is constrained by the bandwidth of a non-blocking network switch.1  Non-blocking switches can, however, handle large bandwidth aggregates due to the parallelism inherent in the design, so they are seldom a significant practical constraint on performance.  This means that a shared disk architecture (perhaps better referred to as a shared-storage system) can scale to large workloads even though the network switch is theoretically a central bottleneck.

The original point was to note that although this central bottleneck exists in shared-disk systems, a partitioned storage subsystem with multiple storage nodes (e.g. BigTable tablet servers or SAN controllers) can still scale up to large workloads.  A non-blocking switch architecture can (in theory) handle as many current connections as it has ports.

1 Of course the processing and I/O throughput available also constitutes a limit on performance but the network switch is a central point through which all traffic passes.

MySQL clustering can shard using multi mastering replication and hashing shards across the cluster.


ORA-01031 while creating a view as DBA I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always get ORA-01031 errors.
Here is my query:

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instanz = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
;

And this is the output I get:

Line: 10 Column:20
SQL-Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.

I even tried it with the SYS account, but with the same result. Am I missing something important?

create table usr_t_user_reg
(username varchar2(30),
db_instance  varchar2(30),
primary key(username,db_instance)
)
/

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instance = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
/

Running the script with sqlplus gives the following error message
Table created.

          FROM sys.dba_users
               *
ERROR at line 8:
ORA-01031: insufficient privileges

this is one of the errors where the error description of the Oracle Database Error Messages 11g Release 2does not give a usefule hint

ORA-01031: insufficient privileges
Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.
Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

In the Oracle Database SQL Language Reference 11g Release 2 you can find the following prerequisite for creating a view

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Neither SELECT_CATALOG_ROLE role nor DBA role is sufficient for creating your view.
So issue a
GRANT SELECT ON DBA_USERS TO your_user;

and now your view will work.
Instead of the object privilege SELECT ON DBA_USERS you can grant the system privilege SELECT ANY DICTIONARY. In  contrast to SELECT_CATALOG_ROLE this is not a role but a system privilege.
Note that the error message spots to line 8 of the CREATE VIEW statement that is the line that actually contains the object that causes the error.

ORA-01031: insufficient privileges
  01031. 00000 -  "insufficient privileges"

If you are receiving this error  for view there are two things:

missing grant
open the  view and check from which table its selecting data as maybe the view is selecting the data from some other table. Grant it to user.


A more efficient way to write multiple CASE statements SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( Table 1.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( CASE WHEN Table 3.DO = 'Z' THEN 'Y' END ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3 )) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table 4. COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3)) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( Table 3.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  then CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1 ) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL AND ( Table.COLUMN1) - ( CASE WHEN ( CASE WHEN ( Table.COLUMN1)
  IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL
  THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) < 90 THEN 'Y' ELSE 'N' END END ) = 'Y' THEN 1 ELSE 0 END,

Is there a more efficient way to write this statement?

Here is one way to rewrite the query that should give the same results without nested CASE statements. I'm checking for NULLs first, to potentially short-circuit additional testing. I considered using a single WHEN, with an OR between each expression, but OR isn't deterministic so this may perform better. If those columns are mostly null (vs mostly filled), you could try rewriting it to test for IS NOT NULL instead of IS NULL, and see if there is a performance difference:

CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table1.Full IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
        AND Table3.DO = 'Z'
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table4.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table2.COLUMN3 IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table3.Full IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
...

There is a isnull function in TSQL, writing it something like this might shorten up your query since you are basically checking to see if the column is not null so you can subtract the two columns and see if it is greater than or equal to 180

case when isnull(table.column1,0) - isnull(table.column2,0) >= 180 then 'Y' else 'N' end


Copy SQL Server replication configuration from production to test environment Where I'm currently working we have an SQL Server's instance for a billing database and another instance for customer database. Both databases keep some tables synchronized using replication mechanism. I'm trying to set up some automated way to get fresh backups from production into QA/DEV environments, but in order for our platform to be kind of a mirror of production environment we need this replication thing between customer and billing databases to work. I know very few about these kind of things, I'm only a developer by unfortunately no one else is willing to do it.

My question is, Is it possible to copy the replication setup in production to QA without having to go along all the process of setting up replication step by step in QA/DEV environments? Maybe generating some scripts of this setup from production and with some small changes enable it in QA/DEV environments?

Additional notes:

We use an instance for QA/Dev customer databases.
Another instance for QA/DEV billing databases.
Both instances run in the same server.

SQL Server version:

  Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)
      Dec 13 2016 04:40:28
      Copyright (c) Microsoft Corporation
      Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

This is probably a dumb question, but I'd appreciate any help about it.

Thanks.

From the Microsoft Docs on Replication:

If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.
In the event of server failure or the need to set up a test environment, you can modify the script by changing server name references, and it can be used to help recreate your replication settings. In addition to scripting your current replication settings, you should script the enabling and disabling of replication. For information about scripting replication objects, see Scripting Replication.

You can script the replication objects, then modify the server names to suit the test/dev environment via SQL Server Management Studio:

Connect to the Distributor, Publisher, or Subscriber in Management Studio, and then expand the server node.

Right-click the Replication folder, and then click Generate Scripts.

Specify options in the Generate SQL Script dialog box.

Click Script to File.

Enter a file name in the Script File Location dialog box, and then click Save. A status message is displayed.

Click OK, and then click Close.

The resulting script can then be ran against the test/dev environment to automatically setup replication, just ensure you modify all server names as needed.  You really don't want the test/dev environment to interfere with the production environment.

Assuming "Customer" database is the publisher and "Billing" the subscriber in a transactional push replication topology, your refresh steps (from prod) would look something like below:

Script out all the permissions from both Customer and Billing DBs on the DEV instances.
Since you want the replication configuration on DEV to mirror that in prod, Script out the replication from the publisher on prod by right-clicking the publication in question (in SSMS)-->generate Scripts--> select "To create or enable the components"-->Hit "Generate Script" button to either script out in a new SSMS window or save the script to file.
Restore both DBs (with overwrite) using the corresponding backups from prod i.e. Billing on DEV would be refreshed from Billing on Prod and same for Customer.
Remove the carried over prod users from the refreshed DBs in DEV followed by running the exported permissions in #1.
This completes the refresh of the two databases. Now proceeding with reconfiguring replication between them.
Run the publication creation script (from #2) on the publisher instance on DEV by carefully changing the server and account names to reflect the DEV environment since the script was extracted from prod. Make any other changes such as post-snapshot script path etc, if it exists.
DO NOT run the last portion of the script which runs the "sp_addsubscription". Only snapshot and log reader agent should be created at this point.
Now, manually initialize the subscription database by running the following on publisher in the publication DB. This would simply join the publisher and subscriber WITHOUT needing a full reinitialization thus saving a lot of time. Data would start replicating from this point on.

USE [PubDB]
GO
EXEC sp_addsubscription
@publication = ,
@subscriber = '',
@destination_db = ,
@subscription_type = push,
@sync_type = 'replication support only'
Ensure that running the above code creates MSupd, MSins, MSdel stored procedures in the subscriber DB. This is important.
Run the sp_addpushsubscription_agent (last part of the script from #2)
Start the synchronization.


Relations, dependencies and normal form I have some issues in understanding about relations and dependencies. I will show two examples and what I want to know:

Assume the relation GTX {G,T,X} with the following dependencies:

G,X -> T
T -> X

or this one:

Relation {W,X,Y,Z}:

X,Y -> Z
Z -> W

So to my question, how do I properly draw a dependency diagram for
this kind of relations? (tutorials or/and explanations are much
appreciated)
How do I see what the primary key is? (I know what a pk is)
In which normal form are the relations?

I have never been able to understand this fully and always got confused by the tutorials I found. I appreciate all answers!

From a set of FDs, you can only infer a set of candidate keys.

A "primary key" is one particular member of that set of candidate keys that you elect to be "primary".  And there are no scientific rules that tell you which one you should choose - you can pick just any key and label it "primary".

In your first example, you should end up with a set of candidate keys

{ {T G} {G X} }

In the second example, you should end up with a singleton set of candidate keys

{ {X Y} }

As for how to arrive at the set of keys, you can take a look at https://stackoverflow.com/questions/5735592/determine-keys-from-functional-dependencies/5736174#5736174

The relation what you gave shows that X and Y depends upon Z.  So Z should be the primary key in the table with Z,X,Y as columns.  Again Z depends upon W. So in another table W should be the primary key and Z should be the foreign key relating to the first table.


Error on SELECT - Primary filegroup is full A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results into a flat file and purge them from the DB. When I tried to query the view (of 5M records) this error showed up:

  Msg 1105, Level 17, State 2, Line 1
  Could not allocate space for
  object 'dbo.SORT temporary run storage:  140744470495232' in database
  'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup.

What is the recommended file growth I should set, how is this error managed or solved?
Thanks in advance.

The quickest fix would be to restart the SQL Server and Tempdb will be recreated with default size and empty files.

But if it's a production server you can't really restart it when you want. A real fix would be to add a new file on a different drive and run your queries.

An example would be (new file of starting size 1 MB, increase 100 MB, limit 500 MB):

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'newtempfile', FILENAME = N'e:\newtempfile.ndf' , SIZE = 100MB, MAXSIZE = 500MB , FILEGROWTH = 100MB )
GO

And then, when you have time, check what's using Tempdb so much. But likely you'll still need space for TempDB, so you'd better plan space for this and assign enough space for this db, as it's very important for a healthy system (you can simply look at it as a RAM part).

PS1: check if you don't have a Cartesian product anywhere in your select statements, because that number seems a bit high.

PS2: If you have enough free space on the TempDB drive, check if the files didn't get to their limit and autogrowth is disabled. If yes, enable autogrowth (not by percent, but by some specific size you feel comfortable with).

PS3: a good solution would be to break that ETL process into smaller transactions. Instead of removing 1 Bil records at once, do it by 1000 mill .. or play with batch sizes until you feel safe with the length of the process and the size of the files. You'll likely obtain the result faster and not have huge increases in space at once.

Clean up your disk space and you will be fine. SQL temporary files persist on your disk even if you delete them from SSMS. You will have to delete manually these files or create more space on your disk to allow more data to run.


Getting the top count of records from a table for each month and user I have a table where translations are stored along with the user_id and when the translation was added.
I want to create a query that shows the 'Translator of the month' (the person who did the most translations in that month).

I have a query to get the top translator of all translations:

SELECT user_id, COUNT(user_id)
FROM gp_translations
WHERE (STATUS='current' OR STATUS='old')
GROUP BY user_id
ORDER BY COUNT(user_id)

and I have a query that shows the year/month:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

but when combining both I fail miserably. The query runs endlessly:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`, (
SELECT distinct user_id
FROM gp_translations t2
WHERE (STATUS='current' OR STATUS='old') AND     YEAR(t2.date_added)=YEAR(t1.date_added)
AND MONTH(t2.date_added)=MONTH(t1.date_added)
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
LIMIT 1)
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

The table gp_translations has the following structure:

 CREATE TABLE `gp_translations` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`original_id` INT(10) NULL DEFAULT NULL,
`translation_set_id` INT(10) NULL DEFAULT NULL,
`translation_0` TEXT NOT NULL,
`translation_1` TEXT NULL,
`translation_2` TEXT NULL,
`translation_3` TEXT NULL,
`translation_4` TEXT NULL,
`translation_5` TEXT NULL,
`user_id` INT(10) NULL DEFAULT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'waiting',
`date_added` DATETIME NULL DEFAULT NULL,
`date_modified` DATETIME NULL DEFAULT NULL,
`warnings` TEXT NULL,
PRIMARY KEY (`id`),
INDEX `original_id` (`original_id`),
INDEX `user_id` (`user_id`),
INDEX `translation_set_id` (`translation_set_id`),
INDEX `translation_set_id_status` (`translation_set_id`, `status`),
INDEX `date_added` (`date_added`),
INDEX `warnings` (`warnings`(1))
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=342587;

I know I am doing something seriously wrong here. But I can't find the way to properly merge both with decent speed. Any help is appreciated. MySQL version is 5.5.34

Due to lack of windows functions and cte, mysql version is quite verbose, but I hope it will give you desired results in reasonable time (I know it's not fast at all):

SELECT a.*, c.*
FROM
(
   SELECT yr,mn, MAX(translations_per_month) as max_user_translations
   FROM
   (
     SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id,
     COUNT(1) as translations_per_month
     FROM `gp_translations` t1
     INNER JOIN gp_users u ON
      (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
     GROUP BY  YEAR(`date_added`), MONTH(`date_added`),user_id
    )b
    GROUP BY yr,mn
)a
INNER JOIN
(
   SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
   COUNT(1) as translations_per_month
   FROM `gp_translations` t1
   INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
   GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON  (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)

Also, I made a guess that status column is in gb_users table ; if not, the query needs to be modified a bit.

use rank and partition,
partition by year/month and rank order by count(user id)
then you have users by they rank(translation count) in each partition(year/month)

https://stackoverflow.com/questions/13962950/sql-rank-over-partition-on-joined-tables

https://msdn.microsoft.com/en-us/library/ms189798.aspx


Get the Record where only Two Records exists in SQL I want to get the parentID where user1 and user2 are using and only the two of them are using.
for example,
ParentTbl
| ID   |           Remarks              |
|------|--------------------------------|
| 1    | only one use this              |
| 2    | two user, _this is the ouput_  |
| 3    | three users use this           |

UserTbl
| ID | UserID | ParentID   |
|----|--------|------------|
| 1  |    1   |      1     |
|_2_ | ___1___|   ___2___  |
|_3_ | ___2___|   ___2___  |
| 4  |    1   |      3     |
| 5  |    2   |      3     |
| 6  |    3   |      3     |

It should be Parent 2
I already have :
select ParentID from UserTbl
where UserID IN (1,2)
group by ParentID
having COUNT(*) = 2

But the output is wrong.

If I understand you correctly, what you want is
SELECT ParentID
FROM   UserTbl
GROUP BY ParentID
HAVING SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2
AND    COUNT(*) = 2

The first condition SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2 is to check User 1 and 2 are in
The second condition COUNT(*) = 2 users count = 2 (only user 1 and 2)

I arrived to this answer, but I am not sure if it will give me the best performance.
I added a condition that excludes the invalid IDs where it is not equal to the specified users.
select parentid from UserTbl
    WHERE parentID NOT IN (
                          SELECT parentID
                          FROM UserTbl
                          WHERE userid NOT IN (1,2))
    AND userid IN (3,2)
    GROUP BY parentID
    HAVING COUNT(DISTINCT(userid)) = 2


Running total with count? As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count:

sum(count (distinct (customers)))

Say if I ran the count alone, the result would be:

Day | CountCustomers
----------------------
5/1  |      1
5/2  |      0
5/3  |      5

I need output with the sum to be :

Day | RunningTotalCustomers
----------------------
5/1  |      1
5/2  |      1
5/3  |      6

I've done running totals before using the coalesce method, but never with a count. I'm not sure how to do it now that I have the count.

Here are a few methods you can compare. First let's set up a table with some dummy data. I'm populating this with a bunch of random data from sys.all_columns. Well, it's kind of random - I'm ensuring that the dates are contiguous (which is really only important for one of the answers).
CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT);

CREATE CLUSTERED INDEX x ON dbo.Hits([Day]);

INSERT dbo.Hits SELECT TOP (5000) DATEADD(DAY, r, '20120501'),
  COALESCE(ASCII(SUBSTRING(name, s, 1)), 86)
FROM (SELECT name, r = ROW_NUMBER() OVER (ORDER BY name)/10,
       s = CONVERT(INT, RIGHT(CONVERT(VARCHAR(20), [object_id]), 1))
FROM sys.all_columns) AS x;

SELECT
  Earliest_Day   = MIN([Day]),
  Latest_Day     = MAX([Day]),
  Unique_Days    = DATEDIFF(DAY, MIN([Day]), MAX([Day])) + 1,
  Total_Rows     = COUNT(*)
FROM dbo.Hits;

Results:
Earliest_Day         Latest_Day           Unique_Days  Total_Days
-------------------  -------------------  -----------  ----------
2012-05-01 00:00:00  2013-09-13 00:00:00  501          5000

The data looks like this (5000 rows) - but will look slightly different on your system depending on version and build #:
Day                  CustomerID
-------------------  ---
2012-05-01 00:00:00  95
2012-05-01 00:00:00  97
2012-05-01 00:00:00  97
2012-05-01 00:00:00  117
2012-05-01 00:00:00  100
...
2012-05-02 00:00:00  110
2012-05-02 00:00:00  110
2012-05-02 00:00:00  95
...

And the running totals results should look like this (501 rows):
Day                  c   rt
-------------------  --  --
2012-05-01 00:00:00  6   6
2012-05-02 00:00:00  5   11
2012-05-03 00:00:00  4   15
2012-05-04 00:00:00  7   22
2012-05-05 00:00:00  6   28
...

So the methods I am going to compare are:

"self-join" - the set-based purist approach
"recursive CTE with dates" - this relies on contiguous dates (no gaps)
"recursive CTE with row_number" - similar to above but slower, relying on ROW_NUMBER
"recursive CTE with #temp table" - stolen from Mikael's answer as suggested
"quirky update" which, while unsupported and not promising defined behavior, seems to be quite popular
"cursor"
SQL Server 2012 using new windowing functionality

self-join
This is the way people will tell you to do it when they're warning you to stay away from cursors, because "set-based is always faster." In some recent experiments I've found that the cursor out-paces this solution.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
  FROM g INNER JOIN g AS g2
  ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];

recursive cte with dates
Reminder - this relies on contiguous dates (no gaps), up to 10000 levels of recursion, and that you know the start date of the range you're interested (to set the anchor). You could set the anchor dynamically using a subquery, of course, but I wanted to keep things simple.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], c, rt = c
        FROM g
        WHERE [Day] = '20120501'
    UNION ALL
    SELECT g.[Day], g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with row_number
Row_number calculation is slightly expensive here. Again this supports max level of recursion of 10000, but you don't need to assign the anchor.
;WITH g AS
(
  SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY),
    c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
), x AS
(
    SELECT [Day], rn, c, rt = c
        FROM g
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

recursive cte with temp table
Stealing from Mikael's answer, as suggested, to include this in the tests.
CREATE TABLE #Hits
(
  rn INT PRIMARY KEY,
  c INT,
  [Day] SMALLDATETIME
);

INSERT INTO #Hits (rn, c, Day)
SELECT ROW_NUMBER() OVER (ORDER BY DAY),
       COUNT(DISTINCT CustomerID),
       [Day]
FROM dbo.Hits
GROUP BY [Day];

WITH x AS
(
    SELECT [Day], rn, c, rt = c
        FROM #Hits as c
        WHERE rn = 1
    UNION ALL
    SELECT g.[Day], g.rn, g.c, x.rt + g.c
        FROM x INNER JOIN #Hits as g
        ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
    FROM x
    ORDER BY [Day]
    OPTION (MAXRECURSION 10000);

DROP TABLE #Hits;

quirky update
Again I am only including this for completeness; I personally wouldn't rely on this solution since, as I mentioned on another answer, this method is not guaranteed to work at all, and may completely break in a future version of SQL Server. (I'm doing my best to coerce SQL Server into obeying the order I want, using a hint for the index choice.)
CREATE TABLE #x([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x([Day]);

INSERT #x([Day], c)
    SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt1 INT;
SET @rt1 = 0;

UPDATE #x
SET @rt1 = rt = @rt1 + c
FROM #x WITH (INDEX = x);

SELECT [Day], c, rt FROM #x ORDER BY [Day];

DROP TABLE #x;

cursor
"Beware, there be cursors here! Cursors are evil! You should avoid cursors at all costs!" No, that's not me talking, it's just stuff I hear a lot. Contrary to popular opinion, there are some cases where cursors are appropriate.
CREATE TABLE #x2([Day] SMALLDATETIME, c INT, rt INT);
CREATE UNIQUE CLUSTERED INDEX x ON #x2([Day]);

INSERT #x2([Day], c)
    SELECT [Day], COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
    ORDER BY [Day];

DECLARE @rt2 INT, @d SMALLDATETIME, @c INT;
SET @rt2 = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT [Day], c FROM #x2 ORDER BY [Day];

OPEN c;

FETCH NEXT FROM c INTO @d, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rt2 = @rt2 + @c;
  UPDATE #x2 SET rt = @rt2 WHERE [Day] = @d;
  FETCH NEXT FROM c INTO @d, @c;
END

SELECT [Day], c, rt FROM #x2 ORDER BY [Day];

DROP TABLE #x2;

SQL Server 2012
If you are on the most recent version of SQL Server, enhancements to windowing functionality allows us to easily calculate running totals without the exponential cost of self-joining (the SUM is calculated in one pass), the complexity of the CTEs (including the requirement of contiguous rows for the better performing CTE), the unsupported quirky update and the forbidden cursor. Just be wary of the difference between using RANGE and ROWS, or not specifying at all - only ROWS avoids an on-disk spool, which will hamper performance significantly otherwise.
;WITH g AS
(
  SELECT [Day], c = COUNT(DISTINCT CustomerID)
    FROM dbo.Hits
    GROUP BY [Day]
)
SELECT g.[Day], c,
  rt = SUM(c) OVER (ORDER BY [Day] ROWS UNBOUNDED PRECEDING)
FROM g
ORDER BY g.[Day];

performance comparisons
I took each approach and wrapped it a batch using the following:
SELECT SYSUTCDATETIME();
GO
DBCC DROPCLEANBUFFERS;DBCC FREEPROCCACHE;
-- query here
GO 10
SELECT SYSUTCDATETIME();

Here are the results of the total duration, in milliseconds (remember this includes the DBCC commands each time as well):
method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1296 ms   1357 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1655 ms   1516 ms
recursive cte with row_number   19747 ms  19630 ms
recursive cte with #temp table   1624 ms   1329 ms
quirky update                     880 ms   1030 ms -- non-SQL 2012 winner
cursor                           1962 ms   1850 ms
SQL Server 2012                   847 ms    917 ms -- winner if SQL 2012 available

And I did it again without the DBCC commands:
method                          run 1     run 2
-----------------------------   --------  --------
self-join                        1272 ms   1309 ms -- "supported" non-SQL 2012 winner
recursive cte with dates         1247 ms   1593 ms
recursive cte with row_number   18646 ms  18803 ms
recursive cte with #temp table   1340 ms   1564 ms
quirky update                    1024 ms   1116 ms -- non-SQL 2012 winner
cursor                           1969 ms   1835 ms
SQL Server 2012                   600 ms    569 ms -- winner if SQL 2012 available

Removing both the DBCC and loops, just measuring one raw iteration:
method                          run 1     run 2
-----------------------------   --------  --------
self-join                         313 ms    242 ms
recursive cte with dates          217 ms    217 ms
recursive cte with row_number    2114 ms   1976 ms
recursive cte with #temp table     83 ms    116 ms -- "supported" non-SQL 2012 winner
quirky update                      86 ms     85 ms -- non-SQL 2012 winner
cursor                           1060 ms    983 ms
SQL Server 2012                    68 ms     40 ms -- winner if SQL 2012 available

Finally, I multiplied the row count in the source table by 10 (changing top to 50000 and adding another table as a cross join). The results of this, one single iteration with no DBCC commands (simply in the interests of time):
method                           run 1      run 2
-----------------------------    --------   --------
self-join                         2401 ms    2520 ms
recursive cte with dates           442 ms     473 ms
recursive cte with row_number   144548 ms  147716 ms
recursive cte with #temp table     245 ms     236 ms -- "supported" non-SQL 2012 winner
quirky update                      150 ms     148 ms -- non-SQL 2012 winner
cursor                            1453 ms    1395 ms
SQL Server 2012                    131 ms     133 ms -- winner

I only measured duration - I'll leave it as an exercise to the reader to compare these approaches on their data, comparing other metrics that may be important (or may vary with their schema/data). Before drawing any conclusions from this answer, it'll be up to you to test it against your data and your schema... these results will almost certainly change as the row counts get higher.

demo
I've added a sqlfiddle. Results:

conclusion
In my tests, the choice would be:

SQL Server 2012 method, if I have SQL Server 2012 available.
If SQL Server 2012 is not available, and my dates are contiguous, I would go with the recursive cte with dates method.
If neither 1. nor 2. are applicable, I would go with the self-join over the quirky update, even though the performance was close, just because the behavior is documented and guaranteed. I'm less worried about future compatibility because hopefully if the quirky update breaks it will be after I've already converted all my code to 1. :-)

But again, you should test these against your schema and data. Since this was a contrived test with relatively low row counts, it may as well be a fart in the wind. I've done other tests with different schema and row counts, and the performance heuristics were quite different... which is why I asked so many follow-up questions to your original question.

UPDATE
I've blogged more about this here:
Best approaches for running totals – updated for SQL Server 2012

Just another way, costly, but version independent. It doesn't use temp tables or variables.

select T.dday, T.CustomersByDay +
    (select count(A.customer) from NewCustomersByDate A
      where A.dday < T.dday) as TotalCustomerTillNow
from (select dday, count(customer) as CustomersByDay
        from NewCustomersByDate group by dday) T


upgrading Oracle forms and reports 6i to 12c We are planning a migration of an Oracle Forms and report 6i application to 12c, and at the same time we will upgrade to the latest version of Oracle (from 10g to 12c).

I would like to know the detailed steps that we can follow, or from where to start?
Is there anything that we must consider before doing this?
Any problems that we might encounter?
How much time will it take?
Taking in consideration that we have Over 900 .fmb/fmx files and Over 700 .rep/rdf files.

Any information whatsoever would be helpful !

I would recommend consulting with a consultant/company that specialises in migrating Oracle Forms and Reports. Why? Because we recently performed a similar migration and after 2 months we were not finished.

Oracle has changed some things internally and features that previously worked can cease to function with the new version.

Oracle has a special site dedicated to this task:

Fusion Middleware Upgrading Oracle Forms 6i to Oracle Forms 12c

I know this is not a complete answer, but so much depends on the complexity of your current environment, that it would be very hard to give you an estimation.

firstly you have to arrange a test server on which os and 12c db and application will be installed.

then oracle provide inbuilt tool to migrate oracle form to higher version
using that tool you have to migrate all the forms then deploy it on test server.
you may stuck on report's portion for reports use library rp2rro.pll
do some custom changes on rp2rro if required then run your application on browser and enjoy 12c. it's very impressive.


Why is my @@Version unchanged after upgrading? I have SQL Server 2008 R2. I installed SQL Server 2014 with the option:

  Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

and the process ended correctly.

But when running on localhost:

SELECT @@Version

I still have:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

Sorry if the question is basic but I am very lost at the moment.

I found the problem: The installer I had was just for SQL Server Management Studio, as @MarkSinkinson pointed out.

If you have this same problem, verify you have the correct installation file (not SSMS only, or an incompatible Edition).

Other helpful things to check, from comments originally left on the question:

You may have multiple instances installed, upgraded one of them, and connected to another. Or the upgrade didn't take (this can be easy to dismiss without noticing in the final step of the upgrade wizard). Please note that 2008 and 2008 R2 are completely separate, independent releases.
Make sure that you are connected to right instance in any case.
Check you didn't uncheck the database engine installation option

You didn't select any instance when upgrading, just repeat the process and in step "select instance" choose the one you want to upgrade.
Hope it helps


What units used in DBA_TABLESPACE_USAGE_METRICS? What units are used in the usage metrics (USED_SPACE and TABLESPACE_SIZE columns) described in Oracle's DBA_TABLESPACE_USAGE_METRICS static data dictionary view?

The Oracle Database Reference only tells they are the "Total space consumed by the tablespace" and "Total size of the tablespace", without mentioning units. Are they in bytes? Megabytes? Blocks?

Blocks. Blocks are the units of the DBA_TABLESPACE_USAGE_METRICS.USED_SPACE and DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE. The latter column accounts for possible AUTOEXTEND MAXSIZE.

EDIT: I'm not sure what is the meaning of USED_SPACE for undo tablespace though. For example:

SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents GROUP BY tablespace_name, status ;

TABLESPACE_NAME                SUM(BLOCKS) STATUS
------------------------------ ----------- ---------
UNDOTBS1                               128 ACTIVE
UNDOTBS1                              5312 UNEXPIRED
UNDOTBS1                              8960 EXPIRED

SQL> select USED_SPACE from DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAME='UNDOTBS1';

USED_SPACE
----------
      2864

SQL> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

I usually calculate so:

SELECT tbm.TABLESPACE_NAME,
   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
   tbm.USED_PERCENT
FROM dba_tablespace_usage_metrics tbm
     join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
WHERE tbm.tablespace_name = 'tablespace_name'


Extract mdf & ldf files from backup file? Is there a way to extract SQL Server files (.mdf & .ldf) from a backup file (.bak) to a physical location, without restore database?

I need a restore query or script to restore files from bak file only, no attaching to server.
Please don't speak about detaching query after restore.

ApexSQL has recently released a tool that can do this. It's ApexSQL Restore. I haven't tested it fully yet, but you have to connect to a SQL Server instance to do this. It attaches database backup file and creates MDF and LDF files for the database.

If you have a .bak file, and all you want is to restore that file to a mdf. Then this is what you can do. It's a little hacky but works.

First you need an empty .mdf and .ldf files. The easier way to do that is to create a Web Project in Visual Studio and then In the App_Data folder right click and add new database. That will create an mdf and a .ldf.

Now copy those files to the data directory of your SQLSERVER Express. If it usually under the C:\Program Files\Microsoft SQL Server. In my case it is in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

So if your database is PhoneBook just copy:

copy PhoneBook.mdf “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook.mdf”
copy PhoneBook_log.ldf “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook_log.ldf”

Now attach your database:

open a Visual Studio 2008 Command Prompt or other prompt (Remember to right click the Run as Administrator, specially if it is Vista)

in the command prompt run:

sqlcmd –S ./SQLEXPRESS

and run the following commands:

CREATE DATABASE Phonebook ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook_log.ldf' )
FOR ATTACH ;
GO

copy your .BAK file to the data directory.

copy PhoneBook.bak “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook.bak”
NOTE: Why the data directory: It seems that in some installation you can have permission issues

RESTORE DATABASE PhoneBook
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'
WITH MOVE 'Phonebook' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook.mdf',
MOVE 'Phonebook_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Phonebook_log.ldf',
REPLACE

After that if everything runs ok then you can detach your database:

1> exec sp_detach_db 'Phonebook'

Now you can copy your mdf files back to where you wanted them

NOTE: If you don't know the name you have to put for Data and Log then Run commands like

`RESTORE HEADERONLY FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'

RESTORE FILELISTONLY FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PhoneBook2.BAK'`

Hope this helps


Select multiple values in LIKE Operator I have a SQL query given below, I want to select multiple value using like operator.

Is my Query correct?

SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM       employee
INNER JOIN employee_mdata_history
ON         employee.ident=employee_mdata_history.employee_ident
WHERE      employee_id like 'emp1%' , 'emp3%'
ORDER BY   rx_dt desc

If not, can anyone correct me?

My table has large amount of data starting with 'emp1' and 'emp3'.
Can I filter the result by top 3 "emp1" and top 2 "emp3" based on rx_dt?

Alternatively you can try the following method:

SELECT
  x.*
FROM
  (
    VALUES
      ('emp1%', 3),
      ('emp3%', 2)
  ) AS v (pattern, row_count)
  CROSS APPLY
  (  -- your query
    SELECT top (v.row_count)
               employee_id, employee_ident, utc_dt, rx_dt
    FROM       employee
    INNER JOIN employee_mdata_history
    ON         employee.ident=employee_mdata_history.employee_ident
    WHERE      employee_id like v.pattern
    ORDER BY   rx_dt desc
  ) AS x
;

The VALUES row constructor represents your pattern list as a table, additionally supplying each pattern with the number of rows to retrieve for that pattern. The CROSS APPLY operator applies your query to every row of the pattern list, i.e. to every pattern, limiting the number of rows for each pattern to the corresponding value from the pattern list.

As a side note, please let me take this opportunity to suggest that you always qualify your columns with the table alias in a query that is reading from two or more tables. That makes your query easier to read/understand. You can always use short aliases to avoid repeating potentially long table names. For instance:

SELECT TOP (1)
  e.employee_id,
  h.employee_ident,
  ...
FROM
  dbo.employee AS e
  INNER JOIN dbo.employee_mdata_history AS h
    ON e.ident = h.employee_ident
WHERE
  e.employee_id LIKE ...
ORDER BY
  ...

You are using TOP(1) and order by clause. So you will get only the first top record ordered by the clause.


Teradata SQL syntax from date to quarter I need Teradata SQL syntax to extract quarter from a date:

Date: (YYYY-MM-DD)
2015-09-12
2015-05-22
2014-12-15

Given below is the expected output:

3Q15
2Q15
4Q14

Query which I have tried (wrapped for readability):

SELECT DATE, ((CAST(EXTRACT(MONTH FROM DATE) AS BYTEINT)-1)/3)+1
    || 'Q' || substring(DATE,3,2) quarter

Error:

  Missing parameters

Teradata supports Oracle's TO_CHAR syntax since TD14:

TO_CHAR(date, 'q"Q"yy')

sel curr_payment_quarter,
    ((CAST(EXTRACT(MONTH FROM curr_payment_DATE) AS BYTEINT)-1)/3)+1
    || 'Q' || substring(curr_payment_quarter,3,2)  as quarter,
    curr_payment_DATE
from PAYMENT_DETAILS

To be clear, Teradata supports sel as a shortcut for select


SSMS fail to connect localhost SQL Server 2008 R2 suddenly Until today when accessed by remote desktop to my sql server could connect using localhost, but suddenly I cant (without any update or change in my server), now I can connect using localhost\instance but cant using localhost.

This is the error, and also it affects connections to several linked servers in this machine

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Checkpoints:

Firewall - disabled
Allow remote connections - yes
TCP connection allowed - yes
Server running - yes
User running services - domain administrator

Also in my computer I can access to this server using SSMS and only the IP.

In our development environment the user running sql server services is local administrator and everything go fine, but our network team doesn't change anything in our domain that affect administrator user.

SO: Windows Server 2008 R2 Datacenter SP1

If it's a named instance well, then, you should have always been using localhost\instance. This is how it's supposed to work. You shouldn't have been able to connect before and I suspect there is a vital detail missing that would explain that.

Possible explanations (there may be others): the instance was the only one on the machine, and maybe an alias was set up or it happened to be running on port 1433 or the browser was able to redirect to the instance. The alias has since been removed or the service has been restarted and/or the instance is for other reasons (such as a manual config change) now on a different port or the browser service has been disabled.

In any case, the solution is to update your connections to reference the full path to the instance, namely: localhost\instance or as Kin suggests make sure the instance is hard-coded to run on a specific port and then use localhost,<port number>.

Restarting the server solved the problem.


I have following two MySQL queries and i want to execute it inside one procedure Following query returns 10 rows of recipe id's i wants to pass first query's output which is of 10 rows to the second query and display the output of both the queries inside a procedure.

Query 1:

SELECT
    p.ID as recipe_ID,
    p.post_title as recipe_title,
    p.post_date as modified_date,
    p.post_name as recipe_url,
    CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count,
    CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status,
    CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status,
    CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

FROM
    posts p
LEFT JOIN social_val s on(p.ID=s.post_id)
LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1)
LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
term_relationships as t, term_taxonomy tt
WHERE
  p.post_type = 'recipe'
    AND p.post_status = 'publish'
    and p.ID = t.object_id
    and tt.term_id = 1279
    and t.term_taxonomy_id=tt.term_taxonomy_id

ORDER BY modified_date DESC
LIMIT 0 , 10

Query 2
Below query is self join which generates image of above particular recipe id.

SELECT
    p.guid as recipeImageURL
FROM
    posts p,
    posts m
where
    p.post_type = 'attachment'
        AND p.post_parent = m.ID
        AND m.ID = ID ORDER BY p.post_modified DESC
LIMIT 1;

If you need to loop over the output of one query to another you can use cursors.

For eg, in this stored routine to find in all tables databases we have looped over:

DECLARE table_cur CURSOR FOR SELECT ...
OPEN table_cur;
table_loop:LOOP
...code...
END LOOP;
CLOSE table_cur;

So in your case declare cursor for first query and loop over each recipeid.

I have tried with CURSOR inside single procedure:

First Procedure: Display recipe details

BEGIN
SELECT
                    p.ID as recipe_ID,
                      p.post_title as recipe_title,
                      p.post_date as modified_date,
                      p.post_name as recipe_url,
                      CASE WHEN s.total_count THEN s.total_count ELSE 0 END as total_like_count,
                      CASE WHEN c.bookmark=1 THEN 1 ELSE 0 END as bookmark_status,
                      CASE WHEN c.like_status=1 THEN 1 ELSE 0 END as like_status,
                      CASE WHEN pm.meta_value=1 THEN 1 ELSE 0 END as unlock_status

              FROM
                  posts p
              LEFT JOIN social_val s on(p.ID=s.post_id)
              LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=userid)
              LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
              term_relationships as t, term_taxonomy tt
              WHERE
                p.post_type = 'recipe'
                  AND p.post_status = 'publish'
                  and p.ID = t.object_id
                  and tt.term_id =termid
                  and t.term_taxonomy_id=tt.term_taxonomy_id
               ORDER BY modified_date DESC
                LIMIT offset , 5;

CALL recipe_list(userid,termid,offset); //calling second procedure for image
END

Second Procedure: (For recipe images)
BEGIN
        DECLARE post_id int DEFAULT 0;

-- declare cursor for recipe_image_cursor
 DEClARE recipe_image_cursor CURSOR FOR
SELECT
p.ID as recipe_ID
FROM
posts p
LEFT JOIN social_share_counts s on(p.ID=s.post_id)
LEFT JOIN social_status_connection as c on(p.ID=c.post_id AND c.user_id=1)
LEFT JOIN postmeta pm on(p.ID=pm.post_id AND pm.meta_value=1 AND pm.meta_key='recipe_lock'),
term_relationships as t, term_taxonomy tt
WHERE
p.post_type = 'recipe'
AND p.post_status = 'publish'
and p.ID = t.object_id
and tt.term_id = 1279
and t.term_taxonomy_id=tt.term_taxonomy_id
LIMIT 0 , 10;

OPEN recipe_image_cursor;

get_image: LOOP

FETCH recipe_image_cursor INTO post_id;

--Display Results
SELECT
                         p.guid as recipeImageURL
                        FROM
                            posts p,
                            posts m
                        where
                            p.post_type = 'attachment'
                                AND p.post_parent = m.ID
                                AND m.ID = post_id ORDER BY p.post_modified DESC
                        LIMIT 1;
 END LOOP get_image;
 CLOSE recipe_image_cursor;
END

How can i display result of cursor in column format..is it possible? or which is the better way?..i am new with procedures and cursor


MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster? I'm running an ETL process and streaming data into a MySQL table.

Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck.

Anyway, it's a basic insert/ update function. It's a list of IDs as the primary key/ index .... and then a few attributes.

If a new ID is found, insert, otherwise, update ... you get the idea.

Currently doing an "update, else insert" function based on the ID (indexed) is taking 13 rows/ second (which seems pretty abysmal, right?). This is comparing 1000 rows to a database of 250k records, for context.

When doing a "pure" insert everything approach, for comparison, already speeds up the process to 26 rows/ second.

The thing with the pure "insert" approach is that I can have 20 parallel connections "inserting" at once ... (20 is max allowed by web host) ... whereas any "update" function cannot have any parallels running.

Thus 26 x 20 = 520 r/s. Quite greater than 13 r/s, especially if I can rig something up that allows even more data pushed through in parallel.

My question is ... given the massive benefit of inserting vs. updating, is there a way to duplicate the 'update' functionality (I only want the most recent insert of a given ID to survive) .... by doing a massive insert, then running a delete function after the fact, that deletes duplicate IDs that aren't the 'newest' ?

Is this something easy to implement, or something that comes up often?

What else I can do to ensure this update process is faster? I know getting rid of the 'web connection' between the ETL tool and DB is a start, but what else? This seems like it would be a fairly common problem.

Ultimately there are 20 columns, max of probably varchar(50) ... should I be getting a lot more than 13 rows processed/ second?

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists.

Syntax is not as simple as REPLACE INTO, but there are not "weird" side effects (because REPLACE works as DELETE/INSERT pair, it can break on foreign keys or delete referencing rows when you didn't want it to)

You may use REPLACE INTO. The disadvantage of it is that it creats high IO, as each existing record will be deleted and then inserted (as opposed to being updated).
Try loading the new rows' IDs into a separate table on the destination server, then run a delete on the destination joining this new table with the existing table using the ID. After that you run your ETL with INSERTs only