azure sql failover duration I  have a azure sql database in North Europe Region. It is almost 5 MB and it has Basic Pricing Tier. That database is not in Azure VM. I am using PaaS environment.
I configured Geo-Replication between North Europe and West Europe. When I tried to failover from West Europe to North Europe, it took 15 minutes. It is unacceptable, 15 minutes for 5 MB. Do you have any idea to reduce failover time?

Thank you

As Igor Pagliai(MSFT) Blog Here of July 3, 2014 has as very well mention about
Deep Dive: SQL Server AlwaysOn Availability Groups and Cross-Region Virtual Networks in Azure.

SQL Server AlwaysOn Availability Groups Supported between Microsoft
Azure Regions Here
VNet-to-VNet: Connecting Virtual Networks in Azure across Different
    Regions Here
Configure AlwaysOn Availability Groups in Azure VM (GUI) Here
Configure an ILB listener for AlwaysOn Availability Groups in Azure Here

As per Igor Pagliai , Azure Virtual Network Bandwidth and Latency is main factor in failover scenario.

This is always my primary concern, even on-premise, When I have to build a multi-site AlwaysOn consideration: the connectivity between different datacenters will provide enough network bandwidth and low latency? Remember that we are in Azure and the connectivity between datacenters is provided by connecting two (at least) Virtual Networks using a VPN tunnel that comes with capacity limits, then let me recap some basic considerations:

Today, for each Virtual Network, you can have only one VPN Gateway;
Each VPN Gateway is able to provide, at the very maximum, a
theoretical limit of 100Mbits/sec;
If you connect a VPN Gateway to    multiple Virtual Networks,
including on-premise local networks, each    channel will share the
same theoretical bandwidth limit of    100Mbits/sec;
VPN Gateway is backed up by two “hidden” Azure VMs of a    certain
size in active-passive configuration, able to provide 99,90%
availability SLA:

Cloud Services, Virtual Machines and Virtual Network SLA
Here

ExpressRoute or Virtual Network VPN – What’s right for me?
Here

NOTE: In this blog post, I will not cover ExpressRoute, I didn’t test for AlwaysOn AG scenarios.


Is there a way to add "distinct" to SSMS IntelliSense? For the last few versions of SSMS (SQL Server Management Studio), I have noticed that IntelliSense does not contain the word distinct.  Is there a way I can add this to the IntelliSense word bank?

The only instructions for configuring IntelliSense I could find had no options for adding to the word bank.

Is there an undocumented means to do this? While not critical to my productivity, it is something I would like to correct.

No, I don't believe there is any way to manipulate the IntelliSense dictionary. And you'll notice other T-SQL keywords are missing from the list, too:

SELECT
DELETE
INSERT
FROM
GROUP
ORDER
JOIN
WHERE
HAVING

UPDATE is there, but only because it is overloaded (a function you can use inside a trigger to see if a specific column was updated).

Further, I'd have to wonder what is wrong with your data model that you're typing DISTINCT enough where auto-complete will have any impact on your productivity at all. DISTINCT really should be rarely used; almost every time I see it in a query, it's being used to remove duplicates caused by bad design or bad join logic.


Failed to create, join or add replica to availability group in SQL Server 2014 I have a 2 node FCI and a standalone SQL Server installation on a non FCI node. I have been automating the configuration/installation of FCIs, AGs, and DB Replicas which has worked fine up to now in all of my testing.

Today I am getting the error below when I execute:

USE [master]
GO
CREATE AVAILABILITY GROUP [AGName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR
REPLICA ON N'Node3\ReadOnly' WITH (ENDPOINT_URL = N'TCP://Node3-blah.blah.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    N'Primary/Primary' WITH (ENDPOINT_URL = N'TCP://primary.blah.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

Error:

  Msg 19405, Level 16, State 17, Line 3
  Failed to create, join or add replica to availability group 'AGName', because node 'Node3' is a possible owner for both replica 'Node3\ReadOnly' and 'Primary/Primary'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again.

Node 3 is not part of the FCI.  It has a standalone installation of SQL Server and it is not listed as a possible owner.

If I attempt to failover to Node 3 the FCI informs me that it is not a possible owner.

I'm not sure what caused this.  I did failover using the FCI between node 1 and 2 several days earlier.  I did remove the listener this time to test that, as that was the last thing I was working on.  Any thoughts?

I could just tear down the FCI and have the automation recreate it but I wanted to try to solve this without doing that just in case this happens in prod one day.  We should be able to evict the node from the WSFC and re-add it as well but I don't want to bug our ops team which has to do that.  We will be tearing down the entire cluster and letting it build from scratch before releasing it though.

Edit. This is the output from the FCI node:

select * from sys.dm_os_cluster_nodes

NodeName    status  status_description  is_current_owner
---------------------------------------------------------
SQNodeL001-LA   0   up  1
SQNodeL002-LA   0   up  0
SQLNode003-LA   0   up  0

The output from the standalone is empty.  It is part of the WSFC but not FCI or AG yet.

The powershell output shows us that all 3 can be owner nodes which is odd.

ClusterObject                                               OwnerNodes
-------------                                               ----------
SQL Server (Instance)                                           {SQNodeL001-LA, SQNodeL002-LA, SQNodeL003-LA}

From the GUI it does not have node 3 selected as a preferred owner.  Pardon for making minor changes and removing the names.  The first node name is all in lowercase.  The other 2 are in upper case.  It looks like I might need to do all this in powershell to get the accurate data, haven't gotten to automating that step yet.  That'll be next sprint.

Edit 2 - solved:

Thanks to Sean's PowerShell query I was able to see that it is still listed as a owner in PowerShell although it is not in the GUI. I removed it using get-clusterresource "sql server (instance)" | set-clusterownernode -Owners node1 node2 and it worked.  Thanks Sean!

Msg 19405, Level 16, State 17, Line 3 Failed to create, join or add
  replica to availability group 'AGName', because node 'Node3' is a
  possible owner for both replica 'Node3\ReadOnly' and
  'Primary/Primary'. If one replica is failover cluster instance, remove
  the overlapped node from its possible owners and try again.

This happens for two main reasons that I've witnessed.

Reason #1 - The resource/group is set to have ownership on the node in error

Sometimes (for a multitude of reasons) resources and resource groups in windows clustering won't always have the same ownership. The best way to diagnose this error is to first check to see what SQL Server (which calls windows clustering APIs) thinks the cluster nodes are:

SELECT * FROM sys.dm_os_cluster_nodes

Once we know what is in the cluster, check via Powershell to see what the cluster thinks the ownership is for the FCI:

Get-ClusterOwnerNode -Resource "SQLFCIInstanceName"

This will return the nodes that could own the cluster resource. Chances are it'll include the node name of a node that we know shouldn't really be there.

To fix this, run the following powershell command:

Get-ClusterResource -Name "SQLFCIInstanceName" | Set-ClusterOwnerNode -Owners NodeName1,NodeName2

Double check by running the first powershell command to check ownership, then try to add the replica to the AG again.

Reason #2 - Node Names + Language != Node Names

If the language used wasn't US_English there would be a good chance that the node names (when compared to each other) wouldn't necessarily compare properly. This would cause a whole bunch of other issues (and it does) with the cluster outside of the AG.

This can be tested by taking the node names, converting them to upper or lower and the comparing them against themselves. Sounds like it should always work... but some languages have special characters that don't do the UPPER and LOWER conversions well.


postgres 9.3. resets log_min_duration_statement after receiving SIGHUP I'm using PostgreSQL-server 9.3.5. After setting log_min_duration_statement to 1000ms I'm sending SIGHUP and see that server resets value to '-1':

-bash-4.1$ psql
psql (9.3.5)
Type "help" for help.

postgres=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 -1
(1 row)

postgres=# select set_config('log_min_duration_statement', '1000', 'true');
 set_config
------------
 1s
(1 row)

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 -1
(1 row)

postgres=#

What may be an a root cause?

After reload you get have settings from config (default)
you must use false as 3 parameter - it is is_local - just for current
transaction

sqldb=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 -1
(1 row)

sqldb=# select set_config('log_min_duration_statement', '1000', false);
 set_config
------------
 1s
(1 row)

sqldb=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 1s
(1 row)


ALTER TABLE ... ROW_FORMAT=Compressed times out I'm trying to change a table with the command:

ALTER TABLE ... ROW_FORMAT=Compressed

This fails after 15 minutes, the log says:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

and I'm left with a large temporary table:

-rw-rw---- 1 mysql mysql 37G Jun  1 10:53 #sql-ib788-3264739711.ibd
-rw-rw---- 1 mysql mysql 15K Jun  1 10:37 #sql-13e5_95.frm

Whereas the table is only 100MB. I'm using MariaDB 10.0.16 on SLES 12.

I've actually backed up this table and restored it on a test VM running openSUSE 13.2/MariaDB 10.0.13 and the ALTER TABLE takes 60 seconds. Unfortunately I need to perform this process on the actual server.

The table has approximately 100,000 rows. If I cut it down to 1,000ish rows the statement works (the exact number of rows depends on whether I remove from the start or end of the table).

The log reports the InnoDB status and I noticed this:

---TRANSACTION 8665481870, not started
MySQL thread id 4, OS thread handle 0x7f8bf01b3700, query id 709 localhost user1 altering table
ALTER TABLE mdl_user ROW_FORMAT=Compressed
---TRANSACTION 8665481869, ACTIVE 253 sec
mysql tables in use 1, locked 1
MySQL thread id 4, OS thread handle 0x7f8bf01b3700, query id 709 localhost user1 altering table
ALTER TABLE mdl_user ROW_FORMAT=Compressed
Trx read view will not see trx with id >= 8665481871, sees < 8665481870

Which looks to me like it's trying to use two threads which are deadlocked although I may be misunderstanding what this is telling me.

What's going wrong?

Please note that I am not a MySQL developer, I use MS SQL Server.  But the behavior in your post suggests the following:

It does not look like a deadlock to me, especially with the error messages:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

A deadlock normally would quickly determine which connection to roll back instead of processing up to 600 seconds before crashing the server.

Likely what is happening is that the table metadata change (the ALTER TABLE) cannot happen while there are other transactions using the table.

http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

The link says, in part: "To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session."

EDIT: Sorry for the misunderstanding regarding deadlocks.

Per this post, https://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

Ensure that: Innodb_adaptive_hash_index=0


Using TOP and getting different result sets I am trying to write an efficient query for deleting chunks of data. To this end I hoped to avoid an index scan by using the primary key to get the oldest records. However I'm seeing some unexpected results returned.

I hoped this

SELECT TOP 15 OrderID FROM [Order]

Would give me the oldest 15 records because I could rely on the primary key incrementing and therefore the order of storage would be low to high in the table.

However, this returns a different result set

SELECT TOP 15 OrderID FROM [Order] ORDER BY DateCreated ASC

Which seems to be a more accurate but more expensive way of getting the result I need.

Confusingly, this

SELECT TOP 15 * FROM [Order]

Gives a different set of OrderID s (PK) to this

SELECT TOP 15 OrderID FROM [Order]

I understand that http://msdn.microsoft.com/en-gb/library/ms189463.aspx explains that order cannot be guaranteed without an ORDER BY clause but expected the PK to order for me and can't explain the differences between the final two select clauses.

Take a look at the plans. When you use SELECT * it probably uses the clustered index, and when you only want one column, maybe there is a skinnier index to use.

Don't "expect" a certain order. If you don't tell SQL Server how to order, then it will use the most efficient way possible, and this can change due to probably more than 20 factors.

If you want a certain order, SAY SO. Please read #3 here:

T-SQL Tuesday #56 : SQL Server Assumptions

Also, this post by Michael Swart may be an interesting read:

Without ORDER BY, You Can’t Depend On the Order of Results

If you want your second query to be more efficient, you can consider creating an index on DateCreated (you may want to include OrderID - not sure of the present index structure).

For your actual goal of deleting n rows at a time, oldest first, and assuming OrderID is an IDENTITY column (so order create date should roughly align with that), why not use this approach (based on this great blog post, also by Michael Swart):

-- pick a datetime for the newest row you want to delete
-- let's say you want to delete all orders before Jan 1 2014:

SELECT @MaxOrderID = MAX(OrderID)
  FROM dbo.[Order] -- terrible table name, also always use dbo prefix
  WHERE DateCreated < '20140101';

DECLARE @BatchSize INT = 1000,
        @LargestOrderProcessed INT = -1,
        @NextBatchMax INT,
        @RC INT = 1;

WHILE (@RC > 0)
BEGIN
  SELECT TOP (@BatchSize) @NextBatchMax = OrderID
    FROM dbo.[Order]
    WHERE OrderID > @LargestOrderProcessed
    AND OrderID <= @MaxOrderID
    ORDER BY OrderID;

  DELETE dbo.[Order]
    WHERE OrderID > @LargestOrderProcessed
    AND OrderID <= @NextBatchMax;

  SET @RC = @@ROWCOUNT;
  SET @LargestOrderProcessed = @NextBatchMax;
END

In order to minimize the impact on the log, you may want to add some additional logic in there, from my blog post Break large delete operations into chunks. As for the dbo prefix, see Bad habits to kick : Avoiding the schema prefix.


MySQL 5.5 - determining correct write[read]_io_threads on high-end system? Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD .  Ubuntu 64 server.

Heavy Innodb load at times - ~ 2000+ queries per sec.  Heavy read and write.

Currently we are running on the default write_io_threads of 4.

Are we "nerfing" the power of our server?
Is it safe to run it on max write_io_threads=64 and read_io_threads = 64 ?

How is this number determined?

Back on March 15, 2012, I answered this question : MySql recommended hardware.

In my answer, I mentioned a client who is still in my employer's web hosting company to this  very day. They are currently running 3 DB Servers in Circular Replication. Each DB Server has:

MySQL 5.5.9
192 GB RAM
162 GB InnoDB Buffer Pool
Dual hexacore (that's right, 12 CPUs)
1.8TB Disk Volume
528 multitenant databases
500 GB of InnoDB Data
innodb_file_per_table enabled

I have them set with

innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 162G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 384M

Bottom line: You got the cores, and 128G RAM? What are you waiting for ? Crank it up and have some fun. My client has been all but flabbergasted with these settings against that hardware since March 2011. You will be blown away !!!

CAVEAT

All kidding aside, you should be careful when working with VMs(VMWare, AWS, any other cloud-based MySQL Instances) or commodity hardware. However, with that much firepower in bare metal, by all means, you should exploit those settings because any version of MySQL before MySQL 5.5 works with single threaded InnoDB. If you have MySQL 5.1, the InnoDB plugin (starting from MySQL 5.1.38) would have to be installed and then set. All Percona Server 5.1 binaries already have these in place. Since you have MySQL 5.5, these settings are natively installed and have been tested in many respects. You just have to take responsibility for tuning the new InnoDB Settings.

Please see my other posts on setting InnoDB's new settings:

Jan 11, 2012 : 16 Cores 12 GB RAM server MySql Configuration - my.cnf
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

UPDATE 2013-01-18 21:52 EDT

You need to take the bull by the horns and setup some benchmarks. Let's go back historically. innodb_read_io_threads and innodb_writes_io_threads did not come with MySQL 5.0 or 5.1. Someone created a plugin and made it available in MySQL 5.1.38. The defaults were 4. I also notice that Percona Server implemented these things early in MySQL 5.1 with defaults of 8. Most people who use Percona Server out-of-the-box swear by it and often say that it is better than MySQL. However, this comparison is usually done with tweeking InnoDB. So, of course, Percona Server, with double the number of threads, would be better.

Given this conservative way to get better performance (upgrading to Percona), you will never really know how good, bad, or ugly InnoDB will go unless you tune for it. Keep in mind my post from Nov 24, 2011. Multiple versions of MySQL were benchmarked and, in some cases, InnoDB under MySQL 4.1 outperformed 5.x in a single-threaded environment. That's given the same level playing field.

To tip the scales in your favor, you must tune InnoDB. Rather than give you any single definitive Pick-This-Pick-That guide, you need to read about the settings InnoDB ghas been given to make multiple cores come alive. Not every environment is like my client's dual hexacore servers or your dual hexadecacore.

If I had to give you a good starting point, it would have the be mysqlperformanceblog.com. Everything performance-centric about mysql is there for you to read, select, and benchmark.


Execution plan for select with empty value using wildcards Short question: with this: foo like '%%', MySQL go through all records or through none?

If you are thinking why i need this, it's because i'm concatenating a search string and if this have no performance problem, i have to write less code.

Simple answer LIKE '%%' will always trigger an FULL index scan if the column is indexed.

see http://sqlfiddle.com/#!2/382bca

if not indexed it will always trigger an FULL table scan.

see http://sqlfiddle.com/#!2/a0fd0/1

Note an FULL index scan and FULL table scan is almost the same but an FULL index scan is more likely to be faster if the index size is smaller what is most likely the case..


How can I save a severely fragmented database/table? I've recently made a discovery that might explain my slow SQL run times.

I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 GB this is a phenomenally HUGE issue. The result is that I have a severely fragmented table (and likely the entire db is severely fragmented). It makes updates incredibly slow as SQL must first create the space for the update, before writing it. And to run updates, it has to piece together thousands of 1 MB pieces of information each time before performing calculations.

So my question: is there a way to resolve this and defragment? I've already gone into Properties - Files and reset the mdf growth level to 100MB, and db recovery is on simple. Can I defragment, or do I need to just go in and recreate the table, or re-create the entire db? I know it will be a lot of time and work, but my main goal for this db is time efficiency. It needs to be as fast as possible and if that means starting from the beginning again, so be it.

In addition, it was brought to my attention that rather than having one very large mdf I can create 'filegroups'. I understand this is something like saving multiple mdfs; one for each table, or even index. How can I safely create these filegroups and will they also help optimize query time and efficiency?

If you have a clustered index on the table the fastest way to defragmanet your table would be to drop and recreate the clustered index. On versions prior to SQL Server 2005 you can defrag standard indexes using the DBCC INDEXDEFRAG command:

http://msdn.microsoft.com/en-us/library/ms177571.aspx

Or the more prefered way on SQL Server 2005 and above is by using the ALTER INDEX statement:

http://technet.microsoft.com/en-us/library/ms188388.aspx

If you have no indexes at all on the table then you will need to defragment the file using Windows (although this does not move the data in the file - it only moves fragments of the file closer to each other.)

As for the filegroups question; multiple files can help improve performance when the files are on separate disks. This is because you can take advantage of concurrent read/writes. It's generally accepted as good practice to put large indexes in a different file on a different drive to the table that they belong to.

I hope this helps you.


Find and print the total number of menu items and the average price of the menu items for each restaurant For this, I have the query:

SELECT r.restaurantName, COUNT(m.itemNo), AVG(m.price)
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN Dish d
ON d.dishNo = m.dishNo;

It generates this result:

+----------------+-----------------+--------------+
| restaurantName | COUNT(m.itemNo) | AVG(m.price) |
+----------------+-----------------+--------------+
| Tasty Thai     |              12 |    11.479167 |
+----------------+-----------------+--------------+

So, it is displaying the correct average, however, it has picked ALL menu items I added to the table instead of just for each individual restaurant, as well as counting all of the items. It also is only displaying one of the three restaurants I inserted.

How do I fix this?

Database code:

CREATE TABLE Restaurant(restaurantID Integer Primary Key, restaurantName Char(15) NOT NULL, type Char(15) NOT NULL, city char(15) NOT NULL);

CREATE TABLE Dish(dishNo Integer Primary Key, dishName Char(15) NOT NULL, type ENUM ('ap', 'en', 'ds'));

CREATE TABLE MenuItem(itemNo Integer Primary Key, restaurantNo int, dishNo int, price DECIMAL(4,2) CHECK(price IN(price>=5 and price<=50)), FOREIGN KEY(restaurantNo) REFERENCES Restaurant(restaurantID) ON DELETE NO ACTION, FOREIGN KEY(dishNo) REFERENCES Dish(dishNo) ON DELETE SET NULL);

CREATE TABLE FoodOrder(orderNo Integer Primary Key, itemNo int, dateof date CHECK(dateof IN(dateof>='2017-01-01')), timeof time, FOREIGN KEY(itemNo) REFERENCES MenuItem(itemNo) ON DELETE CASCADE);

You are missing GROUP BY:

SELECT r.restaurantName, COUNT(m.itemNo), AVG(m.price)
FROM Restaurant r
INNER JOIN MenuItem m
ON m.restaurantNo = r.restaurantID
INNER JOIN Dish d
ON d.dishNo = m.dishNo
GROUP BY r.restaurantName;


Add constraint to table creation in SQL I have to create a table Product with elements P, PType, PName, Price and Origin.
I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I wanted to add the constraint in an alter table but I saw that using an alter table means modifying the original table after its creation, but I need to have this condition at the creation of the table. I don't know how to write this constraint, can someone give me a hint?

IN MySQL 8 (>= 8.0.16) You can use a CHECK CONSTRAINT
In older Versions you will need an INSERT/UPDATE TRIGGER
That said, you should also normalize your table and use a lookup (or reference/helper) table for an Origin id and a Type id.

CREATE TABLE Product
(
    P INT
  , PType varchar(19)
  , PName varchar(20)
  , Price DECIMAL(8,2)
  , Origin varchar(50)
  , CONSTRAINT CHK_type CHECK ((Ptype = 'meat' AND Origin ='Europe')
                           OR Ptype <> 'meat' )
);

INSERT INTO Product VALUES (1,'meat','Kobe Beef',10000.00,'Japan')

Check constraint 'CHK_type' is violated.

INSERT INTO Product VALUES (1,'apple','Breaburn',10.00,'New Zealand')

✓

db<>fiddle here


Find all uses without User Mappings I am looking for a query to list all users without a mapping to a database. I have over 500 users and I think that there are some SQL or Windows accounts that are not needed any longer.

Below code will give all the Logins with Database Mapping. It gives all the available logins. You can remove the mapping part.

--Logins and Permissions
DECLARE @dbname VARCHAR(50)-- database name
DECLARE @SQL VARCHAR(MAX)

CREATE TABLE #LoginPermissions
(
ServerName Varchar(50),
DBName Varchar(50),
LoginType Varchar(10),
srvLogin Varchar(100),
srvRole Varchar(100),
dbUser Varchar(100),
dbRole Varchar(100)
)

DECLARE db_cursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
Insert Into #LoginPermissions
select
@@SERVERNAME AS ServerName,'''
+ @dbname + ''' AS DBName,
[Login Type]=
case sp.type
when ''u'' then ''WINDOWS''
when ''s'' then ''SQL LOGIN''
when ''g'' then ''GROUP''
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
case when convert(char(25),dbp2.name) IS NULL then ''Public'' ELSE convert(char(25),dbp2.name) end as dbRole

from ' +
@dbname + '.sys.server_principals as sp join '
+ @dbname + '.sys.database_principals as dbp on sp.sid=dbp.sid left join '
+ @dbname + '.sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id left join '
+ @dbname + '.sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join '
+ @dbname + '.sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join '
+ @dbname + '.sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id'

EXECUTE (@SQL)
       FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM
(
Select distinct srvLogin, LoginType,
case when srvRole = 'sysadmin' then '' ELSE DBName END as DBName, ServerName, ISNULL(srvRole, '') as srvRole,
case when srvRole = 'sysadmin' then '' ELSE dbRole END as DBRole from #LoginPermissions

UNION ALL

Select name,
[Login Type]=
case [type]
when 'u' then 'WINDOWS'
when 's' then 'SQL LOGIN'
when 'g' then 'GROUP'
end, '', @@SERVERNAME,
CASE WHEN IS_SRVROLEMEMBER ('sysadmin',name) = 1 THEN 'sysadmin'
     WHEN IS_SRVROLEMEMBER ('serveradmin',name) = 1 THEN 'serveradmin'
     ELSE ''
END AS SrvRole, ''
from sys.server_principals where convert(char(45),name) not in (select distinct srvLogin from #LoginPermissions)
) x
Where LoginType IS NOT NULL
order by ISNULL(srvRole, '') desc, srvLogin

DROP TABLE #LoginPermissions


Measuring latency of a long-distance SQL connection We have written a simple "client" app for a customer that connects to their SQLServer database, runs about 360 queries against a "property", and returns that data as a .Net object we call a "cProp".

The queries vary in complexity, but the very longest take perhaps 400ms. When we recently ran it for 60 properties, the entire run took about 3 minutes. The customer has decided to move their database to a remote server with 65ms between us. As a result, the same query required 51 minutes.

Now I'm 99% sure the problem is row retrieval, but I need to prove that. The DBA ran a trace on the process, but having never used it before, I was dismayed to find out how little information it contained. What I did see is that it took just under 4 minutes of server time. This reinforces my belief that the problem is in the row retrieval.

But how do I measure that? The trace returns nothing about the number of rows or total data size - am I doing it wrong? I also know that batching happens outside the server, so how do we even find out what's going on there? Generally speaking, how does one debug issues that are taking place "outside the server" so to speak.

Just to clarify some math here:

360 queries per "property"
60 properties * 360 = 21,600 queries
3 minutes in duration is about 120 queries per second

So in your best case, you're averaging 120 queries per second throughput.

After moving the SQL Server to somewhere that has a ping latency of 65ms, you're averaging 7 queries per second.

Your best bet is to

measure wait statistics while this query is running

If you don't have a monitoring tool, a simple way to do this is running sp_BlitzFirst, an open source tool developed by Brent Ozar Unlimited.  It'll take a snapshot of your wait stats (among other things), wait 5 seconds, and then take another snapshot, and show you a summary of the biggest issues
You can also see the wait stats since the server started using a query like the one in Paul Randal's blog post: Wait statistics, or please tell me where it hurts

Once you've got those results, examine the highest wait stats, and use the SQL Skills wait stats library to dig into what they mean

Based on your description, it's likely that you'll see ASYNC_NETWORK_IO waits.  This can indicate slow network transfer.  But usually it means that the client application is doing row-by-row processing, and SQL Server is waiting to send rows (when it really wants to fire them over the network as fast as possible).

On a related note - in general, it would be far more effective to try to pull larger "groups" of data - rather than running 21,000 queries, and thus making 21,000 round trips over the (potentially slow) network.  This is called a "chatty" interface or API, and is generally considered to be an anti-pattern.

Consider that 65ms * 21,600 queries is 1,404,000 ms, AKA over 23 minutes of overhead.  That's a big chunk of overheard that could be removed simply by limiting the number of roundtrips.


Change mysql password interactively Is it possible to change the user password in mysql in an interactive mode like it one can do in PostgreSQL for example using \password command?
My goal is not to provide the new password together with the command like it is described in mysql documentation about how to change the user password.

From the mysqladmin documentation:

  In MySQL 5.6, the new password can be omitted following the password
  command. In this case, mysqladmin prompts for the password value,
  which enables you to avoid specifying the password on the command
  line. Omitting the password value should be done only if password is the final
  command on the mysqladmin command line. Otherwise, the next argument
  is taken as the password.

In earlier versions you can't do this as those will complain for 'Too few arguments to change password' (thanks Kaarel for reporting this).  In theory you can issue a command like

mysqladmin -u kaarel password newpass

and then it does not appear in the database logs - but may still be stored elsewhere, like .bash_history.


Why mongo write only one partition The /dev/mapper/vg_db1-lv_root partition used 100%,but the other partition is free,why mongodb store data only use this partition. This is why? How can I solve this problem?

[root@db1 shard11]# df -alh
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg_db1-lv_root   50G   48G     0 100% /
proc                           0     0     0    - /proc
sysfs                          0     0     0    - /sys
devpts                         0     0     0    - /dev/pts
tmpfs                       1.9G     0  1.9G   0% /dev/shm
/dev/sda1                   485M   40M  420M   9% /boot
/dev/mapper/vg_db1-lv_home  3.6T  197M  3.4T   1% /home
none                           0     0     0    - /proc/sys/fs/binfmt_misc

The fdisk -h result :

[root@db1 shard11]# fdisk -l

Disk /dev/sda: 1999.0 GB, 1998998994944 bytes
255 heads, 63 sectors/track, 243031 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0001c91f

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64      243032  1951634432   8e  Linux LVM

Disk /dev/sdb: 1999.0 GB, 1998998994944 bytes
255 heads, 63 sectors/track, 243031 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000ebd30

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1      243032  1952146432   8e  Linux LVM

Disk /dev/mapper/vg_db1-lv_root: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_db1-lv_swap: 4160 MB, 4160749568 bytes
255 heads, 63 sectors/track, 505 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/mapper/vg_db1-lv_home: 3939.6 GB, 3939621666816 bytes
255 heads, 63 sectors/track, 478965 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

And Why the two results are inconsistent?

There is no inconsistency here, this is simply due to how you have the volumes mounted and where you have located you MongoDB database path.  You have one logical volume mounted on root (/) and the other mounted on /home - I am guessing you do not have your database path in MongoDB set to something under /home instead it is set to something else which is under the root path.

The most common database paths for MongoDB are /data/db (the default) and /var/lib/mongodb (default on Ubuntu for example), but you can set it to whatever you want.  Given how you have your mount points configured, both of those common defaults would go under the root mount point and hence onto the /dev/mapper/vg_db1-lv_root volume.  If you switch your database path to be under /home or you re-do your mount points appropriately the data will then be placed on the second volume.


federated tables? synchronization? replication? [mysql] I have a REMOTE suppliers' table with, let's say, a code and a name, and several client databases will be creating new suppliers.

Server database

code  |  name
------+-------
1     |  Mark
2     |  John
3     |  Jodie

I thought that a federated table would fit the best, as every client could create new suppliers in a centralized table which assigns incremental codes,

Obviously, if the Internet connection is down, clients won't be able to create new suppliers, so this won't work for me.

I thought that the best idea would be assigning a prefix to each client, and the code count would be assigned locally.

Local table client A

code   |  name
-------+------
A1     |  Mark
A2     |  Jodie

Local table client B

code  |  name
------+------
B1    |  John

Then, the data would be merged into a single remote table.

Remote merged table

code  |  name
------+---------
A1    |  Mark
B1    |  John
A2    |  Jodie

But at this point, I am lost, I don't know if there is a solution for this problem, or I would need to merge them with a cron job + script.

Maybe a multiple-masters-to-1-slave would work?

Is there any way to schedule merge jobs?

Any idea would be appreciated.

You are asking for pain by having the data split among machines is a haphazard way.

Consider Galera (PXC, etc) as a multi-master approach.  If one node goes down or is isolated by a network outage, it is self healing after things come back.

Yes there are multiple solutions (now) for multi-sourcing a slave.  MariaDB has had an offering for some time.  Oracle's MySQL has one.  Galera allows each node to be slave to some 'external' Master.


Multi-variance products My current system houses different variants of Products in different tables;

Productgroups
Productcolors
Productsizes
Productmodels

Products can reside under 1 Productgroup and can have multi-variance Colors, Sizes and Models.

The logic on filling the database now fully relies on the website-scripts; meaning when I make a new Product in my back-office I am filling and relating the Colors, Sizes and Models to the product and make sure they have a combination of themselves.

This structure quickly grows; like 3 Color-variants of 6 Sizes and 2 Models already convert into 36 different products.

I wonder if there is a more logical way to this Database-wise. The problem is that in the future it might be possible that a product will get another property (let's say Sublabel). How could I make this method ever-extendable?

I read about EAV, but that's really too much info to go on for now. Before I dive in the deep, I wonder if there are other approaches on this matter.

If you don't want to go for a full EAV solution, you could try something like this:

product_base
------------
  id
  product_group_id
  (other fields)

product_option_types
--------------------
  id
  description

product_options
---------------
  id
  product_id (fk to product_base.id)
  product_option_type_id (fk to product_option_types.id)
  description

This will let you have data that looks like this:

product_base
ID  |  name | product_group_id
------------|-----------------
1   |  Foo  | 1

product_option_types
ID  |  description
------------------
1   |  colour
2   |  size
3   |  model
4   |  sublabel

product_options
ID  | product_id | product_option_type_id  | description
---------------------------------------------------------
1   |  1         | 1                       | red
2   |  1         | 1                       | green
3   |  1         | 2                       | small
4   |  1         | 2                       | large
5   |  1         | 3                       | special edition
6   |  1         | 3                       | standard edition
7   |  1         | 4                       | Bar

So you end up with a "Foo" product that has 2 colours (red and green), two sizes (small and large), two models (standard and special) and a sublabel (Bar).

Note that in product_options, you'll need a unique constraint on (product_id, product_option_type_id, description).

you could try to go further and attempt to normalize the values of product_options.description to a separate table, but that might be more complicated than it's worth.


SSIS Package Failing since Database failed over to Secondary (Basic Availability Group) Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to Secondary. Since we failed over the SQL Agent Job is failing with error in package indicating that the login failed for domain user (DOMAIN\etl.user.

We also noticed this error: The login is from an untrusted domain and cannot be used wit Integrated authentication.

Below is the visual of our Architecture and explanation:

More details:

Connection string is set at the SQL Agent job and the connection is made using  Listener.
The SQL Agent job is ran as Proxy user as we do not want the user to have SysAdmin privilege.
I have ensured that the domain login on secondary server exist.

Anyone has any idea why the package would fail?

I figured what was the issue. Apparently the SSIS job was connecting with 2 databases that site on 2 different clusters, thus it required to connect to 2 different Listeners. When I added the user on secondary nodes of both clusters the issue was resolved. We were able to test this during the patching windows when we had to fail-over.


Why does ORACLE cache recovery create uncommited blocks during instance recovery? The following is an excerpt from an ORACLE book

  During cache recovery, Oracle replays the transactions from the online redo log files since
  the last checkpoint. During this roll forward operation, both committed and uncommitted
  changes are applied to the data files. At the end of the roll-forward operation, the data files
  will have committed changes, uncommitted changes that were written to the data files to free
  up buffer cache space and uncommitted changes applied by the roll-forward operation. The
  database can be opened as soon as cache recovery is complete.

The sentece in bold above is the one I don't understand. How can a redo log file generate uncommited data since all its information are recorded after COMMIT ?

Further it says

  In the transaction-recovery phase of instance recovery, Oracle applies
  undo blocks to roll back uncommitted changes in data blocks that were either written before
  the instance crash or made by the roll-forward operation during cache recovery.

I understand that if dirty blocks were written to disk without a prior COMMIT (if at all possible), during transaction recovery, undo blocks can be used to roll them back. BUT how can undo blocks can be used to rollback uncommitted changes (what arethose changes anyaway...) introduced by replaying redo logs during cache recovery ?

Hmmm I don't get it since online redo log, which should only contain committed information, create uncommited information during instance recovery.

How can a redo log file generate uncommitted data since all its information are recorded after COMMIT ?

LGWR process flushes redo logs from redo log buffer cache to online redo log files when-

A user fire commit explicitly.
One third of space in redo log buffer is filled.
Three seconds of time has passed since last redo flush.
DBWn process writes dirty buffer to disk.

These all triggers redo flush which may include uncommitted transaction.

BUT how can undo blocks can be used to rollback uncommitted changes (what are those changes anyway...) introduced by replaying redo logs during cache recovery ?

Let me give you an example，suppose you have a data block which contains one row with single column with value 5. You simply try to update it using update tbl set col1=6. During this process undo is generated to reproduce it's original value which is 5 and redo is generated to reply the transaction, means to reproduce the latest value which is 6.
Next, if this transaction is uncommitted and unfortunately instance crashes, during the next startup, Oracle(Specifically SMON process) takes the blocks which need to be recovered into the buffer cache and re-apply the changes using redo logs and if someone tries to read the row that you had modified before, Oracle simply rolled back the data block containing the row to its original value which is 5 using undo.
Update:
The following alert log excerpt is extracted from this blog.
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
read 80458 KB redo, 1721 data blocks need recovery
Started redo application at
Thread 1: logseq 490, block 52626
Recovery of Online Redo Log: Thread 1 Group 1 Seq 490 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 491 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 492 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Completed redo application of 15.26MB
Completed crash recovery at
Thread 1: logseq 492, block 31015, scn 6082434
1721 data blocks read, 1721 data blocks written, 80458 redo k-bytes read
Sun Jan 09 00:50:27 2011

This clearly shows that block number 1721 needs recovery. And in order to do that the block is loaded into the memory(buffer cache) and redo is applied. The block is written back to data file after the recovery.
This is also explained in(Chapter-9 Redo and Undo, How Redo and Undo Work Together, Page ) Expert Oracle Architecture Third Edition book by Thomas Kyte.
From ASKTOM

Hi Tom,

To do the crash recovery , Database needs datablocks(needed recovery) to be brought into SGA. May i know which process will bring them?.

Thanks
Followup
smon will read them and apply redo to them during the recovery phase.

Instance/Crash Recovery


Cannot access any database in SQL Server 2008 R2 Express after moving workstation from domain to workgroup My workstation that has SQL Server 2008 R2 Express for development purposes installed has been moved out of a domain into a Windows workgroup. While being part of the domain I used the account "MyDomain\MyUserName" that had domain admin rights. Now I log in to my computer as "MyComputerName\MyUserName" that has local admin rights.

In the SQL Server Configuration Manager I have changed the account that starts the "SQL Server (Express)" service from "MyDomain\MyUserName" to "MyComputerName\MyUserName".

I can connect with the instance in SQL Server Management Studio (using Windows authentication) and I can see all databases in the Object Explorer in the left side. But once I try to open any database node in the Object Explorer I get a message that access to the database is not possible. I get this message for all databases except system databases like "master" or "tempdb".

If I try to run any query on one of the databases I get a "SQL Server Message 916":

  The server principal "MyComputerName\MyUserName" is not able to access
  the database "MyDatabase" under the current security context.

Is there any way to the get access to my databases again?

Edit

I also tried to create a new database manually in SSMS but it throws the error:

  CREATE DATABASE permission denied in database 'master' (Microsoft SQL
  Server, Error: 262)

Edit 2

I forgot to mention that the workstation runs with Windows XP (Service Pack 3) and the domain the workstation has been removed from was an old Windows NT domain - in case this should be important to find the reason for the problem.

I have been in this situation before. If you do not have the sa password and you are not a member of the sysadmin group then you only have one option remaining to you which is to start SQL Server in maintenance mode. This will allow you to reset the sa password and to add yourself to the sysadmin server role.

Here is a link that I found useful when I had to do this as it describes setp-by-step what you need to do. Good luck!

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx


Index fragementation ? is it unavoidable This is follow up question to @martin smith excellent answer https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

After that i have watched MCM video of Paul randal which shows why index fragmentation matters more in large scans only for huge tables..If i understood correctly, external/logical fragmentation occurs when logical continuity of pages differ from physical order in which they should be..

Suppose i have big table  and identity as clustered key and no updates to clustered key and all pages are in logical order,does Sql guarantee all these pages lay in disk in close proximity...during first inserts it may block some space for this table,but after that many inserts may happen to other tables as well,so data might not be in close proximity...Is my understanding correct ?

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most.

But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge problem. There are typically other matters that are more important to tune than trying desperately to avoid fragmentation.


Constrain unique value between multiple tables I have a database where there's one base table that is extended (by foreign keying) by other tables. Something like this:

I've been assigned a task where I have to do the following check before inserting an "Animal":

Animals Id must be UNIQUE
(Thus) Fishes Id must be UNIQUE
There may be ZERO OR MORE "dead" (Alive = False) Fishes for a specific FishEspecieId
There may be ZERO OR ONE "alive" (Alive = True) Fish for a specific FishEspecieId

The task suggested that I do it on API insert method, something like this (pseudo-code):

Method Insert(Fish fish)
    Fish[] fishes = DB.GetAll<Fish>()

    if (fishes.Any(f => f.FishEspecieId == fish.FishEspecieId f.Animal.Alive))
        throw "Only one alive fish allowed"

    Animal animal = DB.Insert<Animal>({alive: true})
    DB.Insert({Id: animal.Id, ...})

I'm actually going to, indeed, do a check in the API. However, I think it's a good idea to also ensure the rules on the DB side.

How may I do this in SQL Server? And is this indeed a good idea or should I keep only in the backend side?

Ps. This is only a MRE. I know that if this was my database I should make it a single table. If that was the case, I would simply use UNIQUE(FishEspecieId) WHERE (Alive = 1). In my case, however, I have many tables that extends "Animal", or something like this.

You can enforce this with an Indexed View (or a trigger, of course).  EG

create table Animal(Id int primary key, Alive bit)

create table Fish(Id int primary key references Animal, FishEspecieId int)

go
create or alter view vFish_Alive
with schemabinding
as
select f.Id, f.FishEspecieId, a.Alive
from dbo.Fish f
join dbo.Animal a
  on f.Id = a.Id
where a.Alive = 1

 go

 create unique clustered index pk_vFish_Alive on vFish_Alive(FishEspecieId)

 go
 insert into Animal(id,alive) values (1,1),(2,1)

insert into Fish(id,FishEspecieId) values (1,1)

insert into Fish(id,FishEspecieId) values (2,1)
--Cannot insert duplicate key row in object 'dbo.vFish_Alive' with unique index 'pk_vFish_Alive'. The duplicate key value is (1).


Cassandra nodetool repair parallelism: "parallel" versus "dc_parallel" If I run nodetool repair --full I see settings like this:

repairing keyspace some_keyspace with repair options
  (parallelism: parallel, primary range: false, incremental: false,
   job threads: 1, ColumnFamilies: [], dataCenters: [], hosts: [], # of ranges: 735)

But if I run nodetool repair --full --dc-parallel I see this:

repairing keyspace some_other_keyspace with repair options
  (parallelism: dc_parallel, primary range: false, incremental: false,
   job threads: 1, ColumnFamilies: [], dataCenters: [], hosts: [], # of ranges: 1992)

Note the parallelism setting. Since Cassandra 2.2, "parallel" has become the default. I was wondering what the difference is between "parallel" and "dc_parallel". The documentation implies that dc_parallel is faster because it operates in all DCs at once, but in my brief testing it seems to be the opposite; perhaps it is due to my replication settings – most keyspaces are set to use NetworkTopologyStrategy and place an equal number of replicas in each of my DCs – but I would like to be more sure than just a guess. "parallel" is not well documented.

For what it's worth, I am running Cassandra 3 (DSE 5.0.3.)

The problem here is the poor choice of name for the --dc-parallel option, IMO.

Currently there are 3 "parallelism degrees" that is possible to specify for a repair operation: sequential, parallel and datacenter-aware (dc-parallel).

Parallel repair is the oldest option and it simply allows all nodes to run repair tasks at once.  This can be pretty CPU-intensive.
Sequential repair was introduced in order to reduce the CPU impact of repair on the whole cluster.  It is implemented by taking snapshots on all affected nodes then performing Merkle tree calculations using snapshots, one node at a time.  It puts less pressure on the CPU at a cost of longer run time and additional space taken by the snapshots.
Finally, "dc-parallel" repair is actually sequential repair within every DC, while all DCs are allowed to run these repairs at the same time.  So with this option only one node per DC is actively working on repair at a time.  This is not different from sequential if you only have a single DC.


Implementing uniqueness with Oracle For example, say I have something like

CREATE TABLE COMPANY(id int not null primary key, ...);

CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null,
 CONSTRAINT FK_DEP_COMPANY_ID FOREIGN KEY(company_id) REFERENCES COMPANY(id),...);

CREATE TABLE EMPLOYEE(id int not null primary key, department_id int not null,
  username varchar(30) NOT NULL, ...,
 CONSTRAINT FK_EMPLOYEE_DEP_ID FOREIGN KEY(department_id) REFERENCES DEPARTMENT(id));

I have to implement uniqueness of EMPLOYEE.username within one company.

I see 2 ways.
  1. Using AFTER statement level trigger on EMPLOYEE table (something similar to http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936 , case1)
  2. Just adding company_id to EMPLOYEE

Neither of them looks ideal to me, and I wonder what is the proper way to implement uniqueness in such case.

Thanks.

Mostly just for my own amusement, you can do this with a materialized view which has a unique index:

CREATE MATERIALIZED VIEW LOG ON COMPANY
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON DEPARTMENT
WITH PRIMARY KEY, ROWID (company_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE
WITH PRIMARY KEY, ROWID (department_id, username)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW xcheck_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT c.id as company_id, lower(e.username) as username,
    c.rowid as c_rowid, d.rowid as d_rowid, e.rowid as e_rowid
from company c, department d, employee e
where d.company_id = c.id
and e.department_id = d.id;

CREATE UNIQUE INDEX xcheck_ind ON xcheck_mv(company_id, username);

Then attempting to insert an employee record for a different department with the same username gives a unique constraint violation, although not until you commit:

insert into company (id) values(1);

1 row created.

SQL> insert into department (id, company_id) values(1, 1);

1 row created.

SQL> insert into department (id, company_id) values(2, 1);

1 row created.

SQL> insert into employee (id, department_id, username) values(1,1,'Joe Bloggs');

1 row created.

SQL> insert into employee (id, department_id, username) values(2,2,'Joe Bloggs');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (STACKOVERFLOW.XCHECK_IND) violated

Just for fun I put lower() in the MV definition to catch the most basic workaround - so trying to insert 'joe bloggs' also fails - but this model is never going to be very robust.

I'm not saying this is a good idea, just that it's possible...


Should I create a Factless Fact Table or Denormalise my table I am creating a data warehouse from a store database and I have a question regarding the design of my dimensions and facts.

In the store database a table exists for Person, Person_Address and Person_Address_Type. These are linked by another tabled name Entity_Address_ID which links the three tables by their primary keys to give details on what a person's address is and what type of address it is.

My question is, should I create a dimension for all three tables, and a fact-less fact table to link them together or should I de-normalise my dimensions and add to each dimension a foreign key for the address  and address type they are linked too?

Here is a very quick UML of what the current database looks like to provide clarification

Merge them.  Sometimes it's worthwhile to have a separate Addresses table, but generally it's simplest to make those fields of a single Customers dimension.  Because addresses are nearly unique, you're not going to save many records by hiving them out to their own table.

If your Customers table is really large, and addresses change relatively often then moving Addresses out as a Type-4 SCD could be a worthwhile optimization, but a small one.

If there's interest in analyzing customers independently of their specific sales (e.g., "20% of our top-100 customers are international"), you can absolutely add a NumCustomers dummy column to this Customers table to turn it into a factless fact table.


Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries? I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but they share very few columns. For example, all posts have user_id, description, email_on_question, and email_on_bid columns, but on top of this will have 10 - 20 unique fields. All in all, I'd say about 8 out 60 columns are shared between post types.

Because of the vast number of unique columns, and because some post types have unique behavior and children, I initially decided to go with Concrete Table Inheritance - each object is represented by a separate table, with shared columns being duplicated on each table:

-- SportsPost -- | -- WorldNewsPost -- | -- TechnologyPost --
user_id            user_id               user_id
description        description           description
team               location              industry

The problem with this approach is that it's very difficult to query and aggregate all posts. I have a dashboard view which lists the top 20 most recently created posts, and in order to retrieve the data for this view I have to execute a separate query on 10 different tables! I execute all 10 queries inside of a transaction to save myself from doing multiple roundtrips, but I'm still concerned that the performance of the query will be very poor as the application scales and new post types are created.

I'm considering switching over to Single Table Inheritance at this point. Instead of executing 10+ queries for the dashboard view, I'll only need to execute one query. Does this sound like a reasonable approach? Are there any drawbacks of STI (other than NULL columns) that I'm not aware of?

If you are primarily concerned with the complexity of getting the 20 most recent records, you could either write a view that does a UNION ALL of the tables, or create an empty parent table with all the common columns and have all the other table inherit using the PostgreSQL-specific inheritance feature, to hide the visual complexity of the query.  Although it probably won't improve the performance of the query.

Changing the structure as you suggest would very likely make the specific query you asked about faster, but other queries that query only records of one type would probably get worse.  You have to decide which queries are more common and more important.


Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval? Title sums it up.  I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been wondering if perhaps it would be a more efficient technique in the long run.  Are they equivalent (due to being roughly the same amount of processing in the end), or are there cases where one presents a distinct advantage over the other?

It depends a lot on what you're doing with the records, and what your network architecture looks like. For example, if the stored procedure is going to reduce the overall amount of data transmitted from MySQL to PHP, and you have a relatively slow link between the PHP instance and the database, a SP could certainly help you.

I would recommend you start with some of the 'heavier' manipulations you do to your data on the PHP side, see what you can write as a SP, and then benchmark it.


Single User Mode and Replication If I put a replicated database in single user mode will the Log Agent count as the single user?

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I'm trying to block users from connecting to my database while it is being updated in the morning. So I was thinking of putting it in Single User mode, run update then put it back in multi user mode. But the database is also a publication database.

Yes you can do that. Before hand make the log reader job disabled. This will make sure the jobs are not looking for the database.

For more clarity look at the below link :

MSDN : Replication and Single user Mode


Why can't I create a new schema with the owner set to dbo? I created a test account for checking permissions against.
It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database.
But when it tries to create a new schema on Northwind, the creation fails if it tries to authorize dbo as the schema owner, what's up?

Version:
SQL Server Management Studio: 15.0.18183.0
Microsoft Analysis Services Client Tools: 15.0.1487.0
Microsoft Data Access Components (MDAC): 10.0.18362.1
Microsoft MSXML: 3.0 6.0
Microsoft Internet Explorer: 9.11.18362.0 Microsoft
.NET Framework: 4.0.30319.42000
Operating System: 10.0.18362

The db_datareader, db_datawriter, and db_ddladmin permissions will not give the necessary privilege for this action.

db_datareader:    Members of the db_datareader fixed database role can read all data from all user tables.
db_datawriter:    Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_ddladmin:  Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

As you can see, these permissions will give you privileges to create and manipulate some database structure. However, they will not give you the privileges to alter other user or create schema.
Check this image from Microsoft of database level roles and permissions

Note that permission db_ddladmin does not grant you the privilege to create SCHEMA. To do this you need set your test user as a membership of db_accessadmin. This role will grant privileges to create schema and alter any user. The alter any user privileges will be necessary to set dbo user as a schema owner:
USE [your_database]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [teste]
GO

After this, you will can authorize DBO in the new schema like:
USE [your_database]
GO
CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO

Another role will grant permissions to do this is the db_owner, but is "some powerful" permission to this. That is why the correct role is db_accessadmin. You can read more about each permission at this link
Remember to use the commands in the correct database with:
USE [your_database]


Instant synchronization of multiple SQL servers First of all, I am sorry if this is a duplicate. I was unable to find anyone who adressed this particular question, or I might have failed to realize it as I read it. Most likely I used the wrong search-phrases, as I am not all that familiar with the terminology.

We have 3 servers:

A. SQL-server

B. Alarm system

C. Website + API + Other

Server B and C has to connect to server A all the time, and there have been several instances of downtime which has resulted in a lot of customer dissatisfaction.

We want to have the following: (different letters to make commenting easier, though it may not :p )

W. Alarm system (with SQL-server)

X. Website + API (with SQL-server)

Y. Website + API (with SQL-server)

Z. Other (with SQL-server)

EDIT: X and Y are technically identical, but X would normally host the website, and our apps and 3rd party solutions would normally connect to the API on server Y, to ensure that a hickup is only felt through one channel. If X were to crash, then Y would serve both the website and the API until X was back online and vise versa.

It is vital that the SQL-servers are synchronized. If a client makes a change in the database through the website (server X) and then activates the Alarm system W (to which the change matters) then it must have allready been synchronized.

Each system is using only some of the databases and tables, to save on resources we could sync only the vital data instantly, and the rest every few hours or so. Edit: not an option

We will be using SQL Server 2014 on all servers (unless someone convinces me otherwise)

Do we need any 3rd party software to accomplish this?
We are obviously keen on using the most cost-efficient solution, both money-wise and resource-wise, but customer dissatisfaction must end at all cost, we have never lost a client and we don't intend to lose one for having been cheap.
Would we have to use triggers, or should we, for the vital tables?
Could we use views?

That is to say; create the same views on all SQL-servers which loads from whatever server has the latest timestamp for that perticular table, in such-and-such a way that it will have instant sync. A real sync would still be necessary, but less frequent.
Not sure how this would work, but I wouldn't be surprised if someone have done this at some point, and found a solution to all the obvious and less obvious problems.

We have several functions, procedures, and jobs that are changed every now and then, can these be synced, or do I have to update them manually (which is not a big deal)?

UPDATE:

We've been in contact with a company that has helped us in the past with setting up servers, and getting the correct versions and licenses. Their suggestion was the following:

Give each SQL server a copy of the tables, to which they will read and write data.
Have replicated all tables from the other servers on each server; from which it can only read.

This way, each server can work independently, and have access to any changes made on the other servers.

I am not quite sure how to detect deletion of rows, other than to run through and see what's missing, unless TRIGGERS work post-synchroniously in replicated tables?

EDIT: Another concern of mine is what would happen something is inserted in the same table, on different servers, while the replication is down due to network problems or whatever. Then we'd have 2 rows with the same ID, and we'd have no reason to assume the latter is not an edit of the former (other than the fact that the ID is in only 2 of 4 severs, but it could well be in all 4 if the network is down for a long time)

These problems could all be solved ny adding more colums for creation date, last edit date, insert-id, and global-id.
But then we'd have to change so much on our website and in other systems as well, that we'd prefer to avoid it.

Any other obstacles we might face with this solution?
Any better ideas?

I may be understanding this slightly wrong so I apologise if I am.

If you have two databases that you need to be identical and are on 2014 then use the AlwaysOn High Availability Group.

Since you're data centres are at separate locations use the Async mode

This will mean the database is kept completely up to date (all be it possibly with a few second delay) and you can have the secondary node as a read-only replica, this means that your alarm system can read into that database run all the checks etc you would normally.

the Always on system keeps everything up to date, so if the connection drops, when it comes back online it will merge over all changes

It also means that if your main centre goes down you can set it to automatically failover to the secondary, when the main datacentre comes back online it will re-sync with the (now) primary node, at which point you can fail it back over to your main centre.

You can run this on multiple databases, so we have our main DB and our Admin DB synced across our nodes, however what runs all the jobs and direct actions on each side is not replicated so can stay independent of each other


Data Compression Through Base36 Encoding I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type.

I'm thinking about using Base36 encoding to accomplish this. Are there any other ways to go about fixing this problem?

More often than not, Base36 encoding will lead to a loss of precision as the base conversion process requires floating point number operations. Not only will this corrupt your data when converting to and from Base36, you will also be consuming more space (INT takes up 4 bytes, many Base36-encoded integers near the 32-bit unsigned integral limit will take up more than this).

See PHP's documentation for a thorough explanation on the loss of precision.

Answer: You should be using BIGINT for your operations.


Temporary file write failure on MySQL When running an ALTER TABLE ... ADD COLUMN ... I get:

Error Code : 1878
Message    : Temporary file write failure.

And on log_error (/DATA/mysql/.err)

2016-06-29 17:27:01 7f11f08a7700 InnoDB: Error: Write to file (merge) failed at offset 3145728.
InnoDB: 1048576 bytes should have been written, only 327680 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

Context

I'm running MySQL on a VM. Guest is SuSE, host is Windows 7.
Got the error after having dropped the schema and recreating it from a dump.
I had the same error one day ago (except the statement was a create index), and it just spontaneously stopped happening. But this time it's happening for a while.
If I keep retrying, the count of bytes written in the logs gets smaller every once in a while.

It's likely the disk space is the root cause. Watch the space free in the tmpdir location whilst the ALTER is running.


Get disk space percentage free with SQL Server 2005? I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.

However, this only gives me an absolute number of MB free. What I really need is percentage free.

Is there another way in SQL Server 2005 to get this? If not, is there some other light-weight way to get it? If I can, I want to avoid installing a Java JRE, or Perl, or Python on my database server. Perhaps vbscript, or a small Windows executable on the file system?

Yes, I know I can Google this, and I have. It looks like there are a few ways to accomplish it, and I'm curious how my DBA brethren have handled this.

This can be done through PowerShell.  See the below:

Get-PSDrive |
    Where-Object {$_.Provider -like "*FileSystem"} |
    Select-Object Name, Used, Free, @{Name = "Used_Percent"; expression = {$_.Used / ($_.Used + $_.Free) * 100}}

Or, through WMI:

Get-WmiObject -Class Win32_LogicalDisk |
    Select-Object DeviceID, VolumeName, @{Name = "Used_Percent"; expression = {($_.Size - $_.FreeSpace) / $_.Size * 100}}

Please see Aaron's comment to your question.  I completely agree with him, this is not something that is easily done through SQL Server.  You can finagle this to be run within SQL Server, but it's not necessarily a native way to get this information directly from SQL Server.


should I perpetuate bigint? I have a base table used everywhere called Customers.  Of course, its unlikely that table will ever grow past int size, but it is keyed on a bigint nonetheless.

I don't believe that I can change the type of this identity column to int.  But what is your advice for building stored procedures, table type variables etc that refer or carry data for this column?  Should I perpetuate bigint in these routines?  Will they run leaner if I use int, leaving it to the engine to run implicit conversions?

It is always a recommended practice to keep the parameters to be the same data type. This is to prevent potential implicit conversion issues with predicates which can lead to the query optimizer not being able to utilize statistics, or indexes, which leads to poorer performance, and can lead to serious CPU and storage performance issues.


Why does my columnstore index not fill the entire rowgroup? I'm using [vColumnstoreDensity] to monitor the health of my columnstore indexes.
I've noticed that I've got one index that looks strange.
As far as my understanding goes, the columnstore index will fill a rowgroup before starting the next rowgroup. For best index performance, fuller row groups are better.
Having said all of this, I have one table that I'm having trouble understanding. Even after an index rebuild using the largest resource class (to give the maximum amount of memory possible to the build process) the [vColumnstoreDensity] view still shows this index as being spread across many partially full rowgroups.

COMPRESSED_rowgroup_count
4936
COMPRESSED_rowgroup_rows
2693512978
COMPRESSED_rowgroup_rows_MIN
468
COMPRESSED_rowgroup_rows_MAX
739443
COMPRESSED_rowgroup_rows_AVG
545687

Edit:
This is a clustered columnstore index.

There is an Extended Event that fires when a columnstore group is being 'cut', I think the event is column_store_index_build_process_segment. This event will have a 'trim' reason, and you should look for two possible trim causes:

memory (unlikely for your case), if the index build does not have enough memory to build a segment.
dictionary size (the likely cause), this happens if the dictionaries used to encode the data in a column segment reach the maximum size (16Mb).

Of course, to capture this event you need to set up an XE session during the index build (the linked article shows how).

You could also look at the post-build artifacts, specifically at the dictionaries, and see if the secondary dictionaries associated with the small segment are already at full size (16Mb). This would indicate the likely cause of trim to be full dictionary.


MySQL, many writes waiting on disk i/o access I'm seeing a disk i/o problem with a MySQL DB under the following scenario:

24 CPU's total on the computer
22 threads writing simultaneously to separate tables
each write is done using INSERT INTO ... VALUES ..., where there can be ~5000 rows being inserted in a single insert statement
each row's input VALUES are all integers, looking something like this, for example: (13953,195,-149)
tables are MyISAM

The problem is that when all 22 threads are each busy writing the large bulk insert operation, MySQL reports that they are all stuck in state 'closing tables', which equates to flushing the data to disk.

My first analysis found that jdb2 process was most likely causing a bottleneck since the MySQL data directory was under the / partition and journaling was, of course, turned on.

I read that for MySQL data directories this isn't necessary, so I repartitioned the disk, moved the MySQL data directory over to the new partition, turned off journaling for this new partition, and restarted the system.

This resulted in an improvement where the 22 threads were able to complete with no i/o wait and no getting stuck on closing tables, but only for a while, when all threads again end up waiting on 'closing tables'.

mysqladmin processlist, for example, has this for output (cut to save space), where the bottleneck is quite obvious:
processlist output

And then when I check to which process is holding up disk i/o, i find it to be:
[kworker/u65:3]

Which is a kernel process worker.  google helped me little in identifying what this is.

So, my questions:

What is this kernel worker thread causing a disk i/o bottleneck, even when journaling is turned off?
If I were to switch the table type to InnoDB, and change the disk partition to raw (with journaling also off, of course), would this take kernel threads out of the picture?
And any other, general, recommendations to remove this bottleneck?

some additional info, per rick james' request:

1) show create table output:

'CREATE TABLE `psd1` (
  `psd_fk` int(10) unsigned NOT NULL,
  `perbin_fk` mediumint(8) unsigned NOT NULL,
  `power` smallint(6) NOT NULL,
  KEY `psd_fk` (`psd_fk`),
  KEY `perpow` (`perbin_fk`,`power`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs'

2) tables are not bigger than RAM, but there are thousands of tables, possibly hundreds of Mb's each.
3) RAM => 32Gb.
4) rows are ordered by 1st column: which is a foreign key (just my own, not a formal MySQL foreign key) to a primary key of another table.

thanks...

Switch to InnoDB.  Heed the advice here .
Change a few things in my.cnf because of the Engine change.
Don't do anything special with disk.

InnoDB works with 16KB chunks, not 4KB, like MyISAM for data; 16KB vs 1KB for indexes.

If you still have trouble,...  What indexes are on your table?  What order are the rows in?  Are the tables bigger than RAM?  How much RAM do you have?  Let's see SHOW CREATE TABLE.


BASH: DBCA: command not found When I use dbca command as Oracle 11g user I have the following error message:

BASH: DBCA:  command not found

and when I checked the bash profile I found the following:

    # .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
    . ~/.bashrc
fi

# User specific environment and startup programs

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oracledev-srv.etqint.com; export ORACLE_HOSTNAME
#ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
#ORACLE_BASE=/oracle11gr2/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
#ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

PATH=$PATH:$HOME/bin
unset USERNAME
cd $ORACLE_BASE
export PATH

Server information: RedHat Linux 6.3, Oracle installed on the server 11g R2 and 12c.

What I did for investigation:

check the bash file and found out they are correct
navigate to bin file and hit dbca but still the same error message
edit the bash file manually in-case I missed something
reboot the server

And still the same error message.

BASH: DBCA:  command not found

DBCA should be lower-case.


How to keep the sync active with Bucardo I have a master-master sync setup between 3 nodes or 3 different host machines using bucardo. All works good.

However when one of the database or a host is not reachable or goes down for some reason, the other 2 nodes go out of sync too. It stalls the sync and goes into inactive state.

I would like to keep the sync active between the other 2 nodes at the least. And the third one should catchup once its back online. Is this possible?

Let me know how should I go about setting up bucardo sync for such a scenario.

I solved this problem by made separate syncs i.e.

host1->host2
host2->host3
host3->host1

And, for resurect from "stalled" status, made cron's script that check db status and sync status and activate it.


Only local administrators can connect remotely I'm facing a problem that when I try to connect to the SQL Express instance from client to server. The Server logs an error:

  SSPI handshake failed with error code 0x8009030c, state 14 while
  establishing a connection with integrated security; the connection has
  been closed. Reason: AcceptSecurityContext failed. The Windows error
  code indicates the cause of failure. The logon attempt failed
  [CLIENT: 192.168.245.50]

The account I log on with is sysadmin and have all the possible permissions on the database its trying to connect to but still it fails. But if I add the user that is trying to connect to the local administrators group it works. This is not a good solution, I don't want all users to be local administrators on the server. Why is it that the user needs to be local admin?

And I think the SPN is correctly set.

Its all in same domain and using domain accounts.
Windows Autentication is used for login.
Shouldnt be problem with the connectivity to the DC.
Named instances name is used (it works if the domain user is local admin).

Log from SQL error log:

  03/09/2015 15:22:47,Logon,Unknown,Login failed. The login is from an
  untrusted domain and cannot be used with Windows authentication.
  [CLIENT: 192.168.245.50] 03/09/2015 15:22:47,Logon,Unknown,Error:
  18452 Severity: 14 State: 1. 03/09/2015
  15:22:47,Logon,Unknown,SSPI handshake failed with error code
  0x8009030c state 14 while establishing a connection with
  integrated security; the connection has been closed. Reason:
  AcceptSecurityContext failed. The Windows error code indicates the
  cause of failure. The logon attempt failed   [CLIENT: 192.168.245.50]
  03/09/2015 15:22:47,Logon,Unknown,Error: 17806 Severity: 20
  State: 14.

Log from the client trying to connect:

  LSRetailPosis.POS.Program: System.Data.SqlClient.SqlException
  (0x80131904): Login failed. The login is from an untrusted domain and
  cannot be used with Windows authentication.    at
  System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
  owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean
  allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions
  userOptions, DbConnectionInternal& connection)    at
  System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
  owningObject, TaskCompletionSource1 retry, DbConnectionOptions
  userOptions, DbConnectionInternal& connection)    at
  System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
  owningConnection, TaskCompletionSource1 retry, DbConnectionOptions
  userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
  connection)    at
  System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection
  outerConnection, DbConnectionFactory connectionFactory,
  TaskCompletionSource1 retry, DbConnectionOptions userOptions)    at
  System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
  outerConnection, DbConnectionFactory connectionFactory,
  TaskCompletionSource1 retry, DbConnectionOptions userOptions)    at
  System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1
  retry)    at
  System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1
  retry)    at System.Data.SqlClient.SqlConnection.Open()    at
  LSRetailPosis.Settings.Database.CheckDatabaseExistence()    at
  LSRetailPosis.Settings.Database.CheckDatabaseSettings()    at
  LSRetailPosis.Settings.ApplicationSettings.InitializeSettings(String[]
  args)    at LSRetailPosis.POS.Program.InitializeAndLoadPOS(String[]
  args, Boolean owned, SplashScreenForm splashScreenForm)    at
  LSRetailPosis.POS.Program.Main(String[] args)
  ClientConnectionId:d57c7fd9-4a47-41b7-af07-ed93627d2930.

I found a solution to this problem.

I added the domain user to: Local Security Policy -> Local Policies -> User Rights Assignment -> Access this computer from the Network


How to set Postgresql database to see date as "MDY" permanently How can I set my database to see the 'date' as "MDY" without running:

SET datestyle = "ISO, MDY";

every-time I'm trying to access it?
I'm using Postgresql version 9.1, Ubuntu 12.04.
My system locale, at the time of the database installation was set to en_CA.utf8 and I recently changed it to en_US.utf8.

show lc_CTYPE

returns: --> "en_CA.UTF-8"

but

show LC_CoLLATE

returns: --> "en_CA.UTF-8"

I used to have a very similar problem years ago, and then discovered that I can do

ALTER DATABASE database_name SET datestyle TO "ISO, MDY";

Try that.  (This works on a per database basis, for a solution for all of your databases, set this parameter in you postgresql.conf - thanks to @a_horse_with_no_name.)

Please be aware that the default setting is not locale independent.  However, after initdb sets it to whatever it wants, you can change it yourself in the postgresql.conf.

@swasheck's comment made me note that with my settings:

test=# SHOW lc_ctype;
      lc_ctype
--------------------
 Hungarian, Hungary
(1 row)

test=# SHOW lc_time;
      lc_time
--------------------
 Hungarian, Hungary
(1 row)

setting datestyle has the following effect:

SET datestyle TO "ISO, MDY";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

Now this is not really expected - and it's the same on an other server with en_US.UTF8, too.  Trying "ISO, DMY", too suggests me that the "ISO" part is more or less overriding the other part when producing an output.  For input values, it has the expected effect, as summarized in the table below:

                          input values
           '2016/01/21'   '01/21/2016'   '21/01/2016'         output
──────────────────────────────────────────────────────────────────────
ISO, YMD        OK             --             --            2016-01-21
ISO, DMY        --             OK             --            2016-01-21
ISO, MDY        --             --             OK            2016-01-21

-- means above that the given input style results in an error for the given datestyle setting.

Upon careful reading of the documentation, one can see that datestyle is a pair of partially conflicting settings:

  For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).

For me it means that one has to find the one that fits their needs by a bit of experimenting - but the best way is to leave it at the default and always use an unambiguous input format.  Two of these are 'YYYY-MM-DD' and 'YYYYMMDD'.  I prefer the former - even using this IRL ;)

Note: the datestyle setting (and other runtime settings) from postgresql.conf can be overridden by an ALTER DATABASE bla SET datestyle ..., setting it permanently for a single database, or by SET datestyle TO ... setting it for the current session.  This latter might be useful when importing some third party data with a different date format.


sql syntax error 1064 in workbench-generated script I'm not very familiar with sql, so I'm using workbench to create a db. I keep getting this error:

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ')
    REFERENCES `project2`.`REF` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE' at line 6
SQL Code:
        CREATE TABLE IF NOT EXISTS `project2`.`REF_AUTH` (
          `REF#` INT(11) NOT NULL AUTO_INCREMENT,
          `RAUTHOR` VARCHAR(45) NOT NULL,
          PRIMARY KEY (`REF#`, `RAUTHOR`),
          CONSTRAINT `REF#`
            FOREIGN KEY ()
            REFERENCES `project2`.`REF` ()
            ON DELETE CASCADE
            ON UPDATE CASCADE)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = big5

SQL script execution finished: statements: 4 succeeded, 1 failed

Any help would be greatly appreciated.

Give this a try, your issue was the syntax with the foreign key and the references.

CREATE TABLE IF NOT EXISTS `project2`.`REF_AUTH` (
  `REF#` INT(11) NOT NULL AUTO_INCREMENT,
  `RAUTHOR` VARCHAR(45) NOT NULL,
   PRIMARY KEY (`REF#`, `RAUTHOR`),
   CONSTRAINT `REF#`
      FOREIGN KEY (`REF#`) REFERENCES `REF` (`REF#`)
      ON DELETE CASCADE
      ON UPDATE CASCADE)
   ENGINE = InnoDB
   DEFAULT CHARACTER SET = big5

Syntax link

Another helpful link


Is it safe to delete the innodb schema on a new Amazon RDS instance? As per the title, I'm struggling to find out if it's safe to delete the innodb schema that's created on a fresh Amazon RDS instance.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             | -- This was automatically created when the instance was created
| mysql              |
| performance_schema |
+--------------------+

I know it's not doing any harm being sat there - there's no tables/content or anything, I'm just curious as to why it's there and I've not been able to find out why from any other sources.

There is a reason you cannot touch the innodb database.

This is where Amazon stores the system tablespace file ibdata1

If you run

SHOW GLOBAL VARIABLES LIKE 'innodb_data_home_dir';

you might see something like this

/rdsdbdata/db/innodb

It will point to this folder as the base of InnoDB.

If you run

SHOW GLOBAL VARIABLES LIKE 'innodb_log_group_home_dir';

you might see something like this

/rdsdbdata/log/innodb

This is where ib_logfile0 and ib_logfile1 live.

If you run

use innodb
show tables;

you will see nothing because there are no .frm files in it.

Even though the innodb database contains no storage engine data files (.frm or .ibd), you cannot drop the innodb database because the folder /rdsdbdata/db/innodb is not empty.


SQL Server Automatic Tuning (for query plans) vs. Availability Groups In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?
I don’t think so, because query store data is stored in the read/write database, gets copied to all AG read-only replicas, would thus not represent/support any query activity being performed on a given read-only replica, and so could not support automatic tuning for any read-only replicas.
Is this correct? I’m hoping someone can disprove this, or provide some kind of work around. We’re getting serious query sniffing problems on some important read-only replica queries, and if automatic tuning will make this go away, management might buy into an upgrade.
(I was not able to find anything on this on the web, and hardly anything on automatic tuning.)

In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?

No, it wont as documented in BOL(for azure but still applies to sql server on prem):

Attempting to enable Automatic tuning via T-SQL on the read-only secondary will result in a failure as having a different tuning configuration on the read-only secondary is unsupported.

Is this correct?

Yes, it is. Erin from SQLSkills has a nice FAQ that says : Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy.
if you are running into parameter sniffing issues, you can use query hints (with proper testing) as explained by Paul White in his blog post


Case Sensitive Ordering within the CMS Server List I've got to believe there's a reason for this, but for the life of me I cannot figure it out.  It looks like ordering of Server Names within any Central Management Server is Case SENSITIVE, regardless the collation of the server acting as the CMS, itself.

Attached is an example of a CMS hosted on my local machine, which is configured with a server collation of SQL_Latin1_General_CP1_CI_AS.  CI meaning Case Insensitive.  When selecting from the system table holding the CMS objects, the proper order is returned, however as you can see in this screenshot, the way the servers are listed in the UI, the order is obviously being treated in a Case Sensitive manner.

Since it doesn't seem to be server-level related, I assume it's client tools related, but there is no where within the tools that I can identify a collation level (or even case sensitivity setting) that would affect this.

Obviously there is nothing critical about this question, but if someone does know how to force the UI to be case insensitive, I'd appreciate it as I like to Camel Case my server names which is throwing off the ordering.

I believe this is a bug and I could reproduce it.
I entered a connect item for it: https://connect.microsoft.com/SQLServer/feedback/details/3136521


Replacing the results of a row I'm working with Microsoft Reporting Services 2008.  In our database, we have a small group of confidential students that need to be taken into account for several queries.  If they are a confidential student, the database needs to essentially return nothing.  Currently, we do something along the lines of:

select
  case
    when CONFIDENTIALITY_IND = 'Y' or :EmployeeClass = 'XA'
      then 'CONFIDENTIAL'
    else db.table.name
  end as name
from
  db.student

This isn't a big deal for such a small query, but most of them return many fields (often 20+) and I'm not a fan of so many case statements for obvious reasons.  Because of how Reporting Services work, I can't do a general replacement either, it must be done on each and every field.

Is there a more efficient method where I could replace every value in the row with 'Confidential' in a single case statement, or at least something more elegant?

Edit:  To clarify, that there isn't just this one field in the select.  I only wrote one for the example, but in production, some reports are looking at displaying a huge amount of columns.  I'm trying to avoid making two comparisons on every column for reports that could return rather large sets of data.

what you can do, is to write two stored procedures with the following logic:

Build a stored procedure which does the case thing. This may be optional but the resulting sql would look better and easier to debug/develop.
Define a stored procedure which has as input the tablename
Read out the columns of the table - maybe here can be done some grouping/distinct which columns fall into the case CONFIDENTIALITY_IND
Build up the sql statement dynamically by iterating over the result of 3. When the column is your special case column - apply the stored procedure of 1. Otherwise select it. How to build up dynamic sql look here: http://www.dba-oracle.com/t_oracle_execute_immediate.htm - execute immediate is your friend here.

Hope I understood your problem correct. This would be my way


T-SQL How to Create a new column as INT from Datetime column as YYMM or YYYYMM which is the simpliest way to cretae new column and keep old datetime ?
Use FORMAT?

Result like 201805, 201806 or 1805, 1806 etc from column datetime "2018-05-15 09:45:00.000"


Multiple AG groups complicated set up question Suppose I have two AGs:

AG #1

Server A (primary)
Server B (replica)

AG #2

Server C (primary)
Server D (replica)

Now I need Server F to have all the DBs from server A and C. Is it actually possible to have Server F serve as a replica in both AG #1 and AG #2?

Is this as simple as adding both the listeners (from AG #1 and AG #2) on server F?

Currently, servers on AG#1 and AG #2 are in two different WSFCs. Also, I need to add all the listeners from diff AGs to server F? In other words, server F can be the replicas server of 4 different set of AGs that is made of 8 other servers(2 node per set)?

Is there a limit to how many AG groups can be in server F for 2012 Enterprise?

Nothing additional is required. You would just add server F as another replica for both AG #1 and AG #2 (provided that you do not have collisions in your database names).

Just remember that they all need to be members of the same WSFC (given the version you are working with).

With 2012 you must have the Windows machines on the same WSFC in order to add them to an AG, as such you will not be able to use the F server for both AGs (you'll have to choose one or the other).


Maintaining multiple build versions of asp .net / SQL Server applications We have new versions of .net applications deployed every week for testing given in form of new build files. Can any one suggest how I can maintain many versions of the builds without having to keep many copies of builds and databases?

Here's what I have in mind :

For each build maintain a new folder in inetpub > wwwroot
Have one database
Keep SQL Server stored procedures in same one database in different^schemas(*) or keep it in local folder drives (*not sure how to do this)
In web.config file point to same database for each of the builds, but somehow be able to suggest which build version of stored procedures to use for each build from the web.config (again not sure how to do this)

Is this possible and how? A way of version control and deploy and maintain builds on a single db Or can anyone recommend other better methods for maintaining multiple build versions, any links or thoughts will be helpful

This would be an unusual deployment pattern: normally you'd want to have 1-to-1 correspondence between an application and a database. This mainly is because you'd want your development environment to mirror your future production environment as much as possible.

If you cannot create databases on the server you could still look at pre-creating a set of deployment slots (e.g. QA1-QA5) and re-using them.

One thing that might help would be something like DbUp - it allows you to easily deploy just the delta scripts that are needed.
Alternatively you could look at DB change management software like DBGhost or RedGate SQL CI.