Input requested on localization setup for DB I am building an ASP.Net site that will pull dynamic data from SQL server. The site will be built with localization/globalization foundations in anticipation that in a year or so the site will start holding translations for non-English speaking users. I already know how accomplish this in ASP.Net for the static content but I would like some suggestions on a DB design for the dynamic data. I have toyed with the following 3 ideas and would like some input on them and any thoughts on other ways.

Create a unique database for each language set and use ASP.Net to dynamically change which DB the data comes from.
Create copies of each table appending the local code to hold the translated data (i.e. Table-en, Table-fr, Table1-en, Table1-fr)
Leave DB and table design intact (only adding a single column to hold the local code) and insert translated data as a new row entry.

I have ended up going with a dual table design. The first table would contain all data that would not have a translation for it. A second table would have the word "Translation" appended to it and it would contain columns to cover all the data needing translations and have a single translation per row.


Hardware Cluster to VMware, is vMotion an alternative for clustering? We are migrating DB servers from "real" clusters to VMware. The question is with VMware how do you get similar functionality in VMware? Things like failover etc?

Is vMotion the answer? I wonder what other people do, what is considered best practice?

vMotion and clustering aren't alternatives for one another. vMotion moves the same "machine" (albeit virtual) from one physical host to another.

This provides some sort of resilience to hardware failure (unless it's the shared SAN bombing out) but you can't fail over to another host if you would like to create a maintenance window with minimal downtime for example. vMotion won't help you if you need to patch the (virtual) servers without downtime.

If you need the clustering for other things than hardware failure resilience I wouldn't rely on the VMware features but I would create 2 VM's on separate physical boxes and create a regular cluster between the 2 VM's.

vMotion adds a few features like being able to move VM's between hosts to redistribute load and hopefully recover from a hardware failure but is not an alternative for clustering. Clustering between 2 VM's is not different from clustering between 2 physical machines.


ORA-01031: insufficient privileges connecting to standby I configured a standby database and it worked for a while, but now I noticed that the redo logs thread on standby is far behind the primary. After researching alert log and trace files, I figured out that there is a problem with connection to the standby server.

When I type on the primary server:

sqlplus sys/manager@TESTSTAN as sysdba

The output is:

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 29 13:36:17 2016

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

ERROR: ORA-01031: insufficient privileges

But I am able to connect from the standby to the primary:

[oravis@standbysrv bin]$ sqlplus sys/manager@TESTPROD as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 29 13:46:18 2016

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size          1336960 bytes
Variable Size         335546752 bytes
Database Buffers       79691776 bytes
Redo Buffers            6094848 bytes
Database mounted.
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
testprod     MOUNTED

Password file the same on both hosts. Also, I've recreated it for a few times and this didn't help. remote_login_passwordfile parameter is exclusive on both servers.

listener.ora on the standby

    SID_LIST_LISTENER =
    (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = teststan)
        (ORACLE_HOME = /u01/app/oravis/product/11.2.0/dbhome_1)
        (SID_NAME = test) ) )

    LISTENER =
     (DESCRIPTION_LIST =
     (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = standbysrv)(PORT = 1521))
     ) )
    DYNAMIC_REGISTRATION_LISTENER=off

tnsnames.ora on the primary:

TESTSTAN =
 (DESCRIPTION =
 (ADDRESS_LIST = (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = standbysrv)
         (PORT = 1521)) )
 (CONNECT_DATA = (SERVICE_NAME = teststan) )
 )

TESTPROD =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = oratest)
         (PORT = 1521)) )
 (CONNECT_DATA =
         (SID = test)
         (SERVICE_NAME = testprod)
 ) )

According to the output of the command ps -ef|grep pmon which has shows that the instance name of the standby database is teststan.

[oravis@standbysrv ~]$ ps -ef|grep pmon oravis 2749 1 0 16:02 ? 00:00:00 ora_pmon_teststan oravis 2825 2697 0 16:03 pts/0 00:00:00 grep pmon

But in the description of listener on standby database, SID_NAME is defined as 'test'.

The Oracle tries to authenticate sys user using password file(orapwtest) which has never existed.
So set the SID_NAME parameter on listener.ora file on standby site to 'teststan'.


Storage snapshots for consistent backup of postgresql - different data and log volumes We are running many Linux VM's in a vmware/shared storage environment, each running its own instance of postgreSQL (a mix of 9.0 and 9.3). Currently, the entire VM sits on a single root partition/volume, and we've had great success (~8 years) using storage-based snapshots of the underlying VMFS volumes for backup/restore process (and replication to our DR site).

Due to the architecture of our storage, it would be advantageous to separate postgres WAL files to a non-cached, mostly-write volume to give us less cache churn on the storage side. With our storage (Nimble Storage), we can assign both volumes to a single protection/snapshot group, but I haven't been able to elicit from our vendor that the snapshots will happen at EXACTLY the same time across all volumes in the protection group - it likely will, but there's always that chance that its milliseconds apart.

To that end, we ran some experiments, all while writing data to the DB as fast as possible using pg_bench. After the experiments, we restored our snapshot'ed volumes and started the VM+postgres

Snapshot both data and log volumes close to simultaneously - result: DB recovered
Snapshot data volume first, log volume ~1 minute later - result: DB recovered
Snapshot log volume first, data volume ~1 minute later - result: DB recovered
Snapshot log volume first, data volume ~3 minutes later, after a WAL checkpoint wrote new data to datafiles: result: DB recovered

So testing seem to tell us as long as both snapshots are consistent at the volume level, and relatively close together, you get a consistent copy of the DB, based on the time of the WAL/Log volume snapshot.

My question: Is this safe? What are the corner cases we are missing in our testing, and what could go wrong?

Postgres' doc indicates this is not safe, but testing seems to indicate its pretty robust:
http://www.postgresql.org/docs/9.1/static/backup-file.html

  If your database is spread across multiple file systems, there might not be any way to obtain exactly-simultaneous frozen snapshots of all the volumes. For example, if your data files and WAL log are on different disks, or if tablespaces are on different file systems, it might not be possible to use snapshot backup because the snapshots must be simultaneous. Read your file system documentation very carefully before trusting the consistent-snapshot technique in such situations.

NOTE: Yes, we know about other options to make sure they are consistent, like putting PostgreSQL into hot backup mode or using our storage's VMware integration to quiesce the VM's themselves, but we are looking for a storage-only solution for speed, convenience, and zero impact to our clients.

The documentation you cited says it all, but I wouldn't blame you if you want to try to verify the claims of the vendor regarding snapshots taken at the same time. Perhaps a way of uncovering something could be to stress test the WAL system more specifically.

e.g. In addition to your pgbench-based tests, try adding random calls to pg_switch_xlog() to force log rotation, shorter and longer checkpoint intervals (shortening and lengthening checkpoint_timeout and checkpoint_timeout) and even using small or large wal file sizes.

Unless there's something I'm missing, for your snapshots not taken at the same time, I would attribute your recovered DBs perhaps to a bit of lucky timing. In the last case, imagine you took your log snapshot while the current xlog location was, say, 0/A1C0FFEE. Then you have 3 minutes of particularly heavy load on the system, that causes a full cycle through the WAL files, and your DB is now at 0/DEADBEEF when the data snapshot is taken. When you attempt to restore, the WAL files being written to at the time of the data snapshot are long gone, and recovery will fail.


Oracle table transfer between databases in two different hosts I have a 500Gb table which I need to transfer to another Oracle database in another remote host. I have a 10 Megabits/sec speed connection between two hosts. What can be the most efficient way to transfer the table? With minimal chance of disaster.

There shouldn't be any data loss even with fluctuating connection. The method must be fastest among all.

I want a solution without using external drives.

Use the SneakerNet

The SneakerNet may be old, but it is still useful for some extreme cases like yours.

Buy a 1 TB hard drive
Create a Transportable Table Space (TTS) or Dump file (expdp) on the HD
Manually transfer the hard drive.

USPS/UPS/FedEx also works

Load it onto remote server

EDIT

The below section was added due to the comment from the original poster.

Still want to use your internet to transfer?

Increase the bandwidth at both sites.

This is extremely location specific.  If you can, this would be recommended.
I believe 4G LTE (cell phones) can transfer at rates that are faster than 10 Mbps.

Maybe, you are at some extreme location that makes SneakNet unviable (eg 10 day hike in the amazon).  Or, that is the fastest speed available for any ISP.

Extreme example: you are trying to transfer 500GB of data from Opportunity and its modem has a transfer rate of 10Mbps.

I'm assuming you have some sort of restriction due to your terrestrial position.

Data can be made READ ONLY

The READ ONLY requirement is to ensure that the data does not change over the 5 - 10 days it will take to copy everything

Create a database link
Use DBMS_PARALLEL_EXECUTE (DPE) with a PARALLEL_LEVEL => 1

The SQL statement you will use is a simple INSERT ... SELECT statement. (with the appropriate WHERE clause for DPE)

Your bandwith is going to be maxed out for those 5+ days.

You will need to adjust the chunk size to match some personal preference for "ok, I can wait x minutes to redo this CHUNK that failed".

DPE is used to help you easily manage those failed chunks.

Table is part of an OLTP

In this scenario, the table is guaranteed to change throughout the 10+ days (2* calculated) to transfer the data.

Your OS of choice could be your blessing or your curse.

Also, you will need a lot of disk space.  A lot more than 500GB (more like 1.5TB to 2TB).

Similar to DPE, you break up the data into smaller, more manageable chunks.  However, you need to do all of the work.

Steps involved

create expdp file (oracle documentation)
compress it. (OS specific)

this might reduce your data. Your Millage May Vary (YMMV)

split file into multiple chunks (OS Specific)
gather an md5/sha-256 checksum on all of the pieces (OS Specific)
ftp each chunk (OS Specific)

restart the FTP as necessary
make sure you are ready to wait 5+ days.
if internet connection is unreliable, write a shell script to automate the process (OS Specific)
you are probably using a shared connection.  Plan for 10+ days

gather the md5/sha-256 checksum for all of the pieces on the remote site (OS Specific)
compare (OS Specific)

resend missing pieces that are missing
resend pieces whose MD5/sha-256 checksum do not match.
you did put the FTP in BINARY mode first.  right?

join the files together (OS Specific)
uncompress the file(OS Specific)
impdp the file (oracle documentation)

The low end estimate of 115 hours is based on the given uncompressed data size.  If your data can be compressed, you can see significant reduction in size. Your mileage may vary (YMMV).


Cannot disable CDC - sp_cdc_disable_db_internal gives error 8152: 'String or binary data would be truncated'? Trying to disable CDC and getting this error:

  Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 264
  Could not update the metadata that indicates database … is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_drop_objects]'. The error returned was 8152: 'String or binary data would be truncated.'. Use the action and error to determine the cause of the failure and resubmit the request.

I got around this by manually dropping the objects the SP was attempting to drop. In the end, the SP ran but required a manual commit afterwards. No more CDC. I was also able to enable CDC successfully.


Help with SQL Server Error "ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'" So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2:

  Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider
  'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this
  provider through a linked server.

Researching the issue, I've found more than a few articles listing making changes to the OPENROWSET parameters, registry value changes, AdHoc DIstributed Queries, etc. (Links to articles below)

http://www.johnsoer.com/blog/?p=538

http://www.remotedbaexperts.com/Blog/dba_tips/2010/09/ad-hoc-access-to-ole-db-provider-microsoft-ace-oledb-12-0-has-been-denied/

We're still receiving the error after following the steps in the links above.

The caveat here is that when we created a test account and gave it sa - it ran just fine. My colleagues with admin access are also able to run the query without any issues.

This query is being fed from an excel file local to the server hosting this instance.

Anyone know of any other fixes for 2008 ?

I have just had the same problem yesterday, and here is how I was able solve it:

Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio),

the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there.

After I added the key to the registry, I was able to run the Query with a non-admin user as well.


Combine results of two queries, where the second relies on the first I have one query which is returning

name(text), total, created_at

And another query which is returning the same thing

name(text), total, created_at

The second query usually is returning more rows, but I only need the rows where the query1.created_at = query2.created_at

Example:
Query 1 returns:

test, 10, 2015-03-06
test, 12, 2015-03-07

Query 2 returns:

newtest, 14, 2015-03-05
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

The expected output:

test, 10, 2015-03-06
test, 12, 2015-03-07
newtest, 15, 2015-03-06
newtest, 9, 2015-03-07

QUERY 1:

SELECT
  co.name
  sum(c.total)
  c.created_at
FROM c_report c
INNER JOIN contact co on co.id = c.contact_id
GROUP BY co.name, c.created_at

QUERY 2:

SELECT
  po.name
  sum(p.total)
  p.created_at
FROM p_report p
INNER JOIN person po on po.id = p.person_id
GROUP BY po.name, p.created_at

I would use a CTE for the first query and reuse it for the second query:

WITH cte AS (
   SELECT co.name, sum(c.total) AS total, c.created_at
   FROM   c_report c
   JOIN   contact  co ON co.id = c.contact_id
   GROUP  BY co.name, c.created_at
   )
SELECT * FROM cte
UNION ALL
SELECT po.name, sum(p.total), p.created_at
FROM   p_report p
JOIN   person   po ON po.id = p.person_id
WHERE  EXISTS (SELECT 1 FROM cte WHERE created_at = p.created_at)
GROUP  BY po.name, p.created_at;

Be sure to use UNION ALL, not UNION. You don't want to remove duplicates, and it's faster that way.


Do I need to keep SQL Server Profiler interface open? I'm running SQL Server Profiler on SQL Server 2008R2. I opened Profiler, gave it the required settings (including a stop time 24 hours from now), and then left. Profiler was running from my desktop and the database it's monitoring is located on some server. I was connected using VPN. My VPN lost connection at some point and I came back and saw that Profiler has stopped working completely - and that the table is a few hours behind. Is it not that the Profiler runs on server?? Is Profiler a desktop app?

On that note, do I need to keep it open? I thought that Profiler creates a job and it'll run until the stop time mentioned in the settings.

Do not use profiler. You can use XEvents or Server Side trace and let it run for certain duration only.

I have written a process to run server side trace and import the results into database.


Stored procedure that accepts an id as a parameter - how to execute for multiple ids list? Hi I have the following stored procedure that works for a single id (field22):

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_procedure`(IN f22  INT)
BEGIN

UPDATE Table1 p join Table2 b set p.f1= (p.f1-  (LEAST(p.f2,b.amount)*coalesce((select rate from CurrencyExchangeRate where   fromCurrency=b.currency and toCurrency=p.currency and date(date)='2017-01-  15'),1))) ,
 p.f5= (p.f5- (LEAST(p.f2,b.amount)*coalesce((select f6 from   CurrencyExchangeRate where fromCurrency=b.currency and toCurrency=p.currency and   date(date)='2017-01-15'),1))),
p.f7= (p.f7- (LEAST(p.f8,b.amount)*coalesce((select rate from    CurrencyExchangeRate where fromCurrency=b.currency and toCurrency=p.currency and date(date)='2017-01-15'),1))) where status!=2 and p.id=b.f13 and b.id=f22;

UPDATE Tabl2 SET status=2, f1=3,changeDate=now() where id=f22 and status!=2;

INSERT INTO Table4 (type,f1,f2,date,f3,amount,f5,f6) select 11,2,tr.id,now(),tx.f11,tr.f12,tr.amount-tr.f13+tr.f14,tx.id from Table6 tr join  Table7 tx where tr.id=tx.f77 and tr.id=f22;

END

I would like to execute this stored procedure for a known list of ids i have:

  [1,22,443, 2112 ...];

Whats the quickest way to go about it?

If you can pass in the string `"1,22,443", then you construct the query:

SET @sql := CONCAT("... IN (", f22, ")...");

then PREPARE it and EXECUTE.

Not clean, but it should work.

If there is only one element to send in, the IN will still work fine.  (An empty string would lead to a syntax error.)


Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row,  the replication failure occurs with the following message at slave (show slave status \G;):

  Last_Error: Table definition on master and slave does not match: Column 18 type mismatch - received type 19, rtmariadb10.empdetails
  has type 11

Here

Master: Mariadb 10.1,binlog_format: row ;
Slave : Mysql 5.1, binlog_format=statement/row/mixed(any one of these)

Can someone please help to solve this issue?

You have encountered one of the reasons for saying that replication must not go from a newer Master to an older Slave.

Would you care to explain the ultimate goal; maybe we can discuss a workaround.


How can I relate ‘address’ with other two entity types? In a certain business domain, there are three entity types: Address, Merchant and Hotel, and they're related as follows:

A Merchant can have many Addresses, and
a Hotel can have only one Address at a time.

In my database structure, I want to “use” the same table for both entity types, and I think that I need to have two columns for that, that is to say, merchant_id and hotel_id in the Address table.

I feel this design is not perfect because in many cases either of the two columns would contain NULL marks. What is recommended approach?

You should not use the same table for entities of the different nature.

In the RDBMS paradygm table represents the type or class. For each specific type/class you have, you should use the separate table. Sure you can mangle the table by bells and whistles like entity_type_id. Sure you can cheat and define your entities in common as "something that have an address(es)".

But the direct and inevitable result of such approach is the complexity and non-obviousness of the queries on that tables. Each time you will be forced to check what exact "subtype" of the entities you deal with. More conditions in the WHEREs, more complex JOINs and their ON's and so forth.

Indeed, there are lot of cases when we are forced to denormalize our databases or to break the ACIDity for sake of performance. But if your primary goal is the clear and comprehensible design - you have to avoid violations of the basic principles. Pretty good explanation you can find in the foundational book "An Introduction to Database Systems" by C.J.Date, part II chapter 5.

As mentioned by Gypsy Spellweaver, you need an auxilliary "pure relationship tables":

+----+-------+-----+    +----+----------+-----+    +----+---------+-----+
| id | Hotel | ... |    | id | Merchant | ... |    | id | Address | ... |
+----+-------+-----+    +----+----------+-----+    +----+---------+-----+

      +----------+------------+    +-------------+------------+
      | Hotel_id | Address_id |    | Merchant_id | Address_id |
      +----------+------------+    +-------------+------------+

Here two bottom tables contains nothing but IDs of corresponding hotels, merchants and addresses.

When you want to know what adresses are associated with some hotel your query is:

SELECT w.*, z.*
  FROM Hotel    AS w
  JOIN H_A      AS q ON q.hotel_id = w.id
  JOIN Address  AS z ON z.id = q.address_id
 WHERE w.Hotel = "Holiday Inn"
;

Query that returns all the hotels and merchants having the same address:

SELECT w.*, m.*, z.*
  FROM M_A       AS s
  JOIN H_A       AS q ON q.address_id = s.address_id
  JOIN Hotel     AS w ON q.hotel_id = w.id
  JOIN Merchants AS m ON s.merchant_id = m.id
  JOIN Address   AS z ON z.id = q.address_id
;


Does schema normalization enhances query efficiency I understand the importance of normalization in reducing data redundancy and various anomalies but I am wondering if it can also enhance the performance of your query?

It depends on the situation.

If normalization leads you to split your relations into multiple tables, and if you build those tables with primary keys and foreign keys which are defined to the database so that they are properly indexed, then in that sense query performance is enhanced.

Similarly, if your table is not in 1NF you might have to do expensive table scans and text parsing to find examples of a particular data item in a multivalued column.  In 3NF this column could be indexed making the query more efficient.

It's worth noting that normalizing your schema may also make your queries simpler and easier to maintain for the same reasons and in the same situations.

On the other hand, when data has become static (e.g. historical data) it is often denormalized to make retrieval faster.  This frequently takes the form of precalculating values, especially aggregate values.  This is the approach of data warehousing.  It is a good practice, as long as the risk of data insert/update/delete anomalies is very, very low or easy to manage.


Add data from rows to different columns if more than 1 row have a partial match I tried to find a solution but do not find anything to add multiple columns from partially matching rows.

Data:(Maximum of 3 programs per person)

name     surname    program
Jan      Peters     program1
Jan      Peters     program2
Pete     Peters     program1
Jane     Smith      program3
Jane     Smith      program1
Jane     Smith      program2

Result needed:

name     surname    prog1       prog2      prog3
Jan      Peters     program1    program2
Pete     Peters     program1
Jane     Smith      program3    program1   program2

Even just pointing me into a direction would help.

Welcome.

Try this.

select name, surname,
    max(prog1) as prog1, max(prog2) as prog2, max(prog3) as prog3
 from
    (select name, surname,
     if(program= 'program1', 'program1', '') as prog1,
     if(program= 'program2', 'program2', '') as prog2,
     if(program= 'program3', 'program3', '') as prog3
    from db1) as tempdb
 group by name, surname;

This is possible only if the three program values are fixed / known prior to the code (itself).

For unknown values of program this is not possible directly. In such case, you may have to move them to another table / temporary table / view by multiplying columns and then assembling the same into one with more columns.


How to generate a self-signed SSL certificate for MS SQL server 2008 R2 using OpenSSL? Using OpenSSL, I'd like to generate a self-signed certificate for usage with Microsoft SQL server 2008 R2. This might be unusual, but I'd like to learn what's going on behind the scenes.

I have several problems:

1) The certificate, after having it installed into the trusted root certificates of the local computer, is not listed in MSSC utility.

2) Therefore, I have forcibly added it by changing the registry as described in several posts. After having done so, the SQL server service didn't start any more. The event log shows

  Unable to load user-specified certificate [Cert Hash(sha1)
  "350F3D0D987525361CE4CC5E6D72274228C5AF5B"]. The server will not
  accept a connection. You should verify that the certificate is
  correctly installed. See "Configuring Certificate for Use by SSL" in
  Books Online.

followed by other error messages which are undoubtedly caused by that first one.

I am nearly sure that the certificate I generated does not fulfill SQL server's requirements. However, for the life of me, I can't figure out what is missing. This leads to the third problem:

3) Many posts out there recommend the tool CheckSQLssl.exe to examine the certificate and to debug issues. However, I can't find that tool anywhere (not on my SQL server, no download available, even Microsoft doesn't seem to know about it).

These are the commands I am using to generate the certificate:

openssl genrsa -out key.pem 2048
openssl req -new -key key.pem -out csr.pem -subj /CN=server.example.com
openssl req -x509 -sha256 -days 36500 -key key.pem -in csr.pem -out cert.pem
openssl x509 -extfile /etc/ssl/pp-openssl.cnf -sha256 -days 36500 -signkey key.pem -in cert.pem -out ext.pem
openssl pkcs12 -export -out cert.pfx -inkey key.pem -in ext.pem

So I generate the key and a CSR which contains the server's FQDN as common name (lines 1 and 2). Then I sign the CSR and generate a self-signed X509 certificate (line 3). Then I add the EKU for Server Authentication (see below) (line 4) to the certificate and finally convert it into PFX format (line 5).

pp-openssl.cnf just contains one line to add the correct EKU:

extendedKeyUsage=critical,1.3.6.1.5.5.7.3.1

When examining that PFX certificate on the server, it seems that it should meet the requirements:

C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx |find "EXC"
    KeySpec = 1 -- AT_KEYEXCHANGE
    Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE

and (only relevant snippet of the output)

C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx
    2.5.29.37: Flags = 1(Critical), Length = c
    Enhanced Key Usage
        Server Authentication (1.3.6.1.5.5.7.3.1)

I have read that the EKU and AT_KEYEXCHANGE are important requirements; both are obviously met.

What else could be missing? I'll provide the complete output of certutil -dump -v if it helps.

I managed to generate a self-signed certificate that works for encryption of connections to MS SQL Server by using the New-SelfSignedCertificate command for PowerShell.
Example:
New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" -DnsName "$env:COMPUTERNAME",'localhost.' -KeyAlgorithm "RSA" -KeyLength 2048 -Hash "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" -NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"

Running it:
C:\mssql>notepad create-ss.ps1

C:\mssql>powershell
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\mssql> .\create-ss.ps1

   PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\MY

Thumbprint                                Subject
----------                                -------
14151D156A16557AAA67A4C9465631520CC18626  CN=WIN-9U7N81SI03H

Use of the certificate:

Giving access to the certificate private key is required as well.
Find the account used to run MS SQL Server:

Now from Management Console, Certificates Add-on, Select Manage Private Keys

Add the user running MS SQL

Restart MS SQL Server and you should be done.


Unknown storage engine 'XtraDB' I installed Percona Server 5.6.12-rc60.4, when I want create table such as :

CREATE TABLE IF NOT EXISTS `mydb`.`table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = XtraDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

this error occurred:

 #1286 - Unknown storage engine 'XtraDB'

How can I create a table with XtraDB engine?

XtraDB replaces InnoDB (it's compatible and a drop in replacement), so just add ENGINE = InnoDB

However, it should be the default in MySQL 5.5.x onwards


Comparing running total with group by working with Two scenarios
CREATE TABLE [dbo].[Test](
    [LineID] [int] NULL,
    [BusinessUnit] [nvarchar](50) NULL,
    [Value] [nvarchar](50) NULL,
    [Amount] [money] NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'DEF', 'Apple', 40.00 UNION
SELECT 3, 'ABC', 'Apple', -20.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

above scenario is Invalid -- and i need to get line 1, 2, 3, 4 as output because the amount total is not Zero for same business unit as to follow the sequence.
ABC should adjust to zero (Sum of Amount) in the order of InvoiceLine, before new business unit line being added.
So DEF(Line 2) started before ABC business unit's lines turned to zero.

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'ABC', 'Apple', -20.00 UNION
SELECT 3, 'DEF', 'Apple', 40.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

this is Valid, and the code should not return any value as this is valid entry.
DEF (line 3) started with ABC's amount total equals to zero.

Answer based on clarifications in comments
The question, really, is giving two data scenarios and saying in the first case, all rows should be returned in a select because when you order the rows by lineID and keep track of the cumulative sum of the Amount value then any time the BusinessUnit value changes if the cumulative sum of the Amount value is not zero, return the rows. In the second scenario, the cumulative sum for Amount is zero every time the Business Unit value changes when ordered by LineID - so no row is returned.
There are two ways to do this.
You can write a cursor that will iterate through your rows one by one and use local variables to store values, track the cumulative sum and note which rows fail the check, then somehow store those rows in a temporary table and return the table contents at the end.
However, as someone noted in comments, the strength in a database lies in set operations - dealing with sets of data at a time. So...
The second way to do this is as a "single" statement. This is really an aggregation of multiple statements - but they are all selects. The approach below makes use of the T-SQL lag function, which can read values from prior rows in an ordered result set. This function requires a partition clause - which allows us to create "windows" on our data - but we don't actually need those windows - we are happy to treat the whole dataset as a single window. So I guess ultimately this also processes rows one by one, but using T-SQL's native function rather than writing our own cursor.
Last note before the solution - you have a column [Value] which contains the word "Apple" on every row. It seems irrelevant to the question, so I have ignored it. If this column affects the behviour you are seeking, you will have to adjust the below SQL suitably to deal with your [Value] column.
Here is the solution - including data setup and tear down for each of your two scenarios.
Important! - The lineID value for the failing rows is in the column priorLineID (and not lineID)
This is because we are "reading behind" with the lag function - so we don't know if the businessUnit has changed until we get to the next row and look back at the prior one. At that time we know the businessUnit changed so we test whether the prior cumulative total was zero and if not, return the current row and provide the priorLineID in its own column. You can expand the SQL to return whatever prior row data values you need.
delete from test;
go

-- TEST CASE 1 - all rows returned because the cumulative total for Amount is not zero
--  when BusinessUnit changes, when ordered by LineID

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'DEF', 'Apple', 40.00 UNION
SELECT 3, 'ABC', 'Apple', -20.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

select * from test;

with EXPANDED_DATA as (
select lineID, BusinessUnit, value, amount from test
union select 999999999,'','',0
),
PARTITIONED_DATA as (
select
lineID,
BusinessUnit,
AMount,
lag(BusinessUnit,1,'') over (partition by 1 order by lineID) as priorBusinessUnit,
lag(lineID) over (partition by 1 order by lineID) as priorLineID,
lag(amount) over (partition by 1 order by lineID) as priorAmount
 from EXPANDED_DATA
)
,
WITH_PRIOR_CUMULATIVE_AMOUNT as (
select *,
case when priorBusinessUnit = businessUnit then priorAmount + amount else amount end as CumulativeBusinessUnitTotal
from PARTITIONED_DATA
),
WITH_PRIOR_TOTALS as (
select *,
lag(CumulativeBusinessUnitTotal) over (partition by 1 order by lineID) as priorCumulativeTotal
 from WITH_PRIOR_CUMULATIVE_AMOUNT
)
select * from WITH_PRIOR_TOTALS
where BusinessUnit <> priorBusinessUnit and priorCumulativeTotal <> 0

delete from test;
go

-- TEST CASE 2 - no rows returned because the cumulative total for Amount is zero
--  when BusinessUnit changes, when ordered by LineID

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'ABC', 'Apple', -20.00 UNION
SELECT 3, 'DEF', 'Apple', 40.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

select * from test;

with EXPANDED_DATA as (
select lineID, BusinessUnit, value, amount from test
union select 999999999,'','',0
),
PARTITIONED_DATA as (
select
lineID,
BusinessUnit,
AMount,
lag(BusinessUnit,1,'') over (partition by 1 order by lineID) as priorBusinessUnit,
lag(lineID) over (partition by 1 order by lineID) as priorLineID,
lag(amount) over (partition by 1 order by lineID) as priorAmount
 from EXPANDED_DATA

)
,
WITH_PRIOR_CUMULATIVE_AMOUNT as (
select *,
case when priorBusinessUnit = businessUnit then priorAmount + amount else amount end as CumulativeBusinessUnitTotal
from PARTITIONED_DATA
),
WITH_PRIOR_TOTALS as (
select *,
lag(CumulativeBusinessUnitTotal) over (partition by 1 order by lineID) as priorCumulativeTotal
 from WITH_PRIOR_CUMULATIVE_AMOUNT
)
select * from WITH_PRIOR_TOTALS
where BusinessUnit <> priorBusinessUnit and priorCumulativeTotal <> 0

Original answer below
(Originally the question seemed to be about validating the insert statements at the time of insertion and I basically made the comment that you really can't do that. I will leave this part of the answer below).
If I understand your question correctly, you are saying the first attempt at insert is invalid because of the sequence of the select statements fails a business rule, and that the second attempt at insert is valid because the sequence of select statements passes that business rule. Is this correct?
If so, note that your select statements have been "joined together" (effectively), using union statements. This means your selection of four rows of data is carried out as a single statement - and there are no guarantees about the order in which those select statements will be processed.
The only difference between your first and second insert - as far as the database is concerned - is that the lineID value varies between the two statements, for a given combination of data.
However the more important implication to you is that it seems you want the database to validate something that it is not designed to validate - re-read my comment that there is no real difference between those two inserts, as far as the database is concerned.
Reading between the lines, I am wondering if your list of select statements is being generated by application code? If so, I would suggest your application should be validating the values it is appending to the query. That said, regardless of the order of rows, the net result (as far as the database is concerned) will be the same (notwithstanding the lineID difference).


Scaling and Reducing Reads On A Similar Query Filter Suppose that I run five queries against the same table with the same starting filter, like:

SELECT ColumnDate, ColumnKey, ColumnValue
FROM QueryTable
WHERE ColumnDate > DATEADD(DD,-1,GETDATE())
---- each other query would have additional criteria

All queries hit the same exact table five times each with further filters and grabbing one record.  The starting filter is always the same though - and it generally grabs less than 1% of the total records on the table.

An alternative would be to read the table once, keeping it in a subset - like a CTE or temp table - then filtering on that subset in further queries or subqueries in the SELECT statement, which would be a fraction of the table with only one read of main table.  Temp table example:

SELECT ColumnKey
(SELECT ColumnKey FROM #temp WHERE ColumnValue = 'sf')
FROM #temp
WHERE ColumnValue = 'nyc'

On a smaller data set of less than 10K records, I don't see an impact in testing this alternative; as this scales to higher amounts, where the date could be filtering a two day period out of twenty years, would this start to perform better?

Or, another better question to ask is what is a better "best practice" than hitting a table five times with a similar query, if any?

As Aaron pointed out a CTE is a tool for developers to reuse code. It will not encourage the SQL Server query optimizer to spool the results to disk so they can be reused.

You're talking about trading space for time. It's very difficult for anyone to issue a statement that will be a best practice for all scenarios. The benefits of using a temp table here depend on the table size, which indexes are defined on the table, the workload, the workload on tempdb, etc.

For an example of when using a temp table is clearly better, consider a billion row table with no indexes. Out of those billion rows none of them match the initial filter condition. You don't use any space but avoid four full table scans of the large table.

For an example of when using a temp table is clearly worse, suppose that there is a heavy workload on the server and space in tempdb is limited. Your query could fail or cause other queries to fail if it loads too much data to a temp table.

My advice would be to start with the simplest code or the code that makes the most sense to you and to measure the performance of it. Depending on the results consider adding indexes, using a temp table, or other tricks to speed up the performance of the queries.

In the systems that I work on its very common for different users to run similar or sometimes even identical queries. I'm sure that if we took all of the queries after the fact it would have been possible to create temp tables that would have decreased overall server resource utilization. However, SQL Server is designed to deal with this kind of workload. Pages from tables that are needed for queries are loaded into the buffer cache. Subsequent queries on the same tables will often run faster because data is already in memory.


Management Studio won't recognize other file extensions I am using SSMS 2012. I am working with .PRC, .TAB, and .UDF files. I added these file extensions to SSMS so that it would treat them like .SQL files already via Tools > Options > Text Editor > File Extension, but recently SSMS has stopped recognizing these files (e.g. won't show them with color coding or allow me to run them against a database). Funnily enough when I remove a file extension from a file it then recognizes them because it has "Map extensionless files to SQL Query Editor" checked.

Does anyone know what the problem might be?

According to this Connect item, this bug was fixed in Service Pack 1.

However, if the issue persists, you can try this convoluted workaround posted over at SQLServerCentral by Marios Philippopoulos:

  OK, got it to work with help from http://www.progtown.com/topic374931-sql-server-2012-rtm.html.

  Opened regedit.exe as Administrator.

  Then:

  On [HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\FileExtensionMapping\PRC] I changed the Default key value to {B5A506EB-11BE-4782-9A18-21265C2CA0B4}.

  On [HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\Default Editors\PRC] I changed the Custom key value to {B5A506EB-11BE-4782-9A18-21265C2CA0B4}.


Windows Authentication for SQL Server Linked Servers Currently, on our project, we have a situation: there are a SQL Server 2012 instance IA in domain A and MS SQL Server 2012 instance IB in domain B. Domains A and B are trusted. IB configured as the linked server for IA.

Could you help with an idea how can be configured "forwarding" of Windows authentication security context from instance IA to IB and what is the set of requirements to doing this? Is it only way to use Kerberos delegation for linked server or is enough that they are located in trusted domains?

UPDATE:

There is an issue... it is clear that Kerberos delegation can be used for that but what about other ways? This not work for our case in the reason that for Kerberos delegation between different Active Directory Domain controllers will work only in the case when both od Domain Controllers are located on machines with OS Windows 12+ (not our case). Possibly it can be enough that SQL Server instances located in trusted domains. If client application located on the same server as SQL Server instance IA everything is working well without SPN configuration and etc. But what about the case when client application located on other server (different from IA and IB)?

The only way you can pass Windows Credentials between servers to my knowledge is via delegation as you've already taken a look at.  If there's truly no way to get delegation working in your environment, an alternative to delegation is to fall back to mapping your Domain accounts to SQL authenticated accounts on the remote server that are setup with permissions that are equivalent to your needs.  The downside here is any query executed remotely will be logged to the SQL Authentication account and not your AD Account, so audits won't be as transparent unless you take pains to map a one-to-one SQL Authenticated account to AD Account.

To map AD accounts to remote SQL Authenticated accounts, you first need to create the SQL Authenticated accounts on the remote server and then configure local logins to map to them on your linked server.  If you're happy to use the GUI, you can find this window under Server Objects → Linked Servers → Linked Server Name → Properties → Security Page:

If you prefer TSQL instead, you can perform this same functionality via the sp_addlinkedsrvlogin stored procedure.

Final Note Here: Because you don't have delegation setup, you'll want to leave the Impersonate checkbox unchecked or if you're using TSQL, the @useself parameter should be passed in as FALSE otherwise it will try to pass through authentication which won't work in your scenario.


Best practices relational design I'm creating tables in a database currently and one of the tables has a status column. the status column has 4 different statuses that it could potentially have. I've made a separate table that has table_statuses and links them together via id. But I'm wondering if I shouldn't just type in the varchar directly.

i.e I have

-- -----------------------------------------------------
-- Table `m_document_version_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `m_document_version_status` (
  `document_version_type_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `value` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `m_document_version_status` (`value`) VALUES ("wip");
INSERT INTO `m_document_version_status` (`value`) VALUES ("scheduled");
INSERT INTO `m_document_version_status` (`value`) VALUES ("current");
INSERT INTO `m_document_version_status` (`value`) VALUES ("deprecated");

-- -----------------------------------------------------
-- Table `m_document_version`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `m_document_version` (
  `document_version_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `version` varchar(12) NOT NULL,
  `major` int(4) NOT NULL DEFAULT 0,
  `minor` int(4) NOT NULL DEFAULT 0,
  `patch` int(4) NOT NULL DEFAULT 0,
  `document_type_id` int(1) NOT NULL DEFAULT 1,
  `status` int(1) NOT NULL DEFAULT 1,
  `created_date` DATETIME NOT NULL,
  `launch_date` DATETIME DEFAULT NULL,
  `deprecated_date` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

I'm wondering if I shouldn't just remove the version_status table and input the varchars directly for simplicity

These look, to me, like statuses in a workflow that you are creating.
As such, it is highly likely that these status values will change over time.  Because of this, I would discount the use of an Enum.  Changing an Enum requires you to change the table definition, which requires you (as DBA) to get involved.  Adding a new status [row] into a regular table makes it very easy to add new statuses, potentially even being a part of your application and Users being able to do this for themselves!  (Obviously, you'll have a constraint that prevents them from deleting statuses that are in use!)

Also, I would recommend this approach over using a simple, varchar column, if only for the sake of consistency.  is it possible that someone (you?) could accidentally(?) enter a status of "WIP" instead of "wip" and what effect would that have on your application code?


Is there a way to order a PostgreSQL table by its primary key automatically? I want to dump the whole content of a table, ordered by its primary key. Intuitively, I would like to do a

SELECT * FROM %table_name% ORDER BY %primary_key_of(table_name)%;

This would be run by an external tool, knowing the list of the tables, but not knowing their primary keys.

If there is no better option, we can assume that all primary keys are single-column.

I am using PostgreSQL-9.5, but an upgrade is possible.

By default, simple SELECT * FROM %table_name% will not order results by primary key.

But you can obtain list of columns of primary key of table:

SELECT ind_column.attname AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE tbl.relname = 'my_table'
  AND ind.indisprimary;

And then build dynamic query with this columns in ORDER BY clause.

Add INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace and WHERE condition on sch.nspname if you have multiple tables with the same name in different schemas.

You can obtain PK's for all tables in 1 query:

SELECT sch.nspname AS "schema"
  , tbl.relname AS "table"
  , array_agg(ind_column.attname) AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE sch.nspname <> 'pg_toast'
  AND ind.indisprimary
GROUP BY "schema", "table";


Does surrogate key assignment for a fact table require that the source data has natural keys? Let's say I have a simple OLTP database with orders, products, and customers:

And from it, I am building a data mart with an orders fact table, product dimension, customer dimension, and date dimension:

When loading the orders table into fact_orders (let's say I was using an SSIS Lookup Transformation to assign the surrogate keys), does that mean that the source of data for the orders would also need to have the natural "foreign key" values that were associated with the order in the OLTP system?

In other words, would the data that's being loaded come from a query like this?

SELECT
  order_date,                   -- needed to get date surrogate key
  customer_name,                -- needed to get customer surrogate key
  product_name,                 -- needed to get product surrogate key
  order_number,                 -- denegenerate dimension,
  qty_ordered AS order_qty,     -- measure
  total_amount AS order_amount  -- measure
FROM orders o
  INNER JOIN customers c
    ON o.customer_id = c.customer_id
  INNER JOIN products p
    ON o.product_id = p.product_id

I'm not sure that you need "natural" keys, but you probably do need to maintain a key mapping of sorts. So you need to understand what relationships map between your source and target systems, identify the keys for those relationships and build your key mappings from there.

I had a previous question on this called "What is the best practice for mapping from natural keys to integer-based keys? (ETL)".

  EDIT: So far I am seeing at least three, if not four mappings.

CustomersToDim_Customers (customer_id, dim_customer_id)
ProductsToDim_Products (product_id, dim_product_id)
OrderDatesToDim_Date (order_date, date_id) or (map_id,order_date,date_id) if you want to use a key to map.

  And lastly, I see the order_id as your key to the fact table. So I
  would go

OrdersToFactOrders (order_id,dim_date_id,dim_customer_id,dim_product_id)

  In my case I renamed the fields for the mart with dim_field_id because
  I didn't want name collision within my tables or confusion as to which
  Id they pointed to. Your ETL would have to know that
  CustomersToDim_Customers.dim_customer_id really maps to
  Dim_Customers.customer_id and that
  CustomersToDim_Customers.customer_id really maps to
  Customers.customer_id.

  I would also be half-inclined to include the order_number in the
  OrdersToFactOrders mapping table, but that is because I like to have
  tracking data for audit purposes. Makes my life easier. But, based on
  what you told me, order_number and order_id are one-to-one so the
  inclusion of order_number would be redundant then and only necessary
  if you have a perfectionist paranoia to make sure your data is correct
  on both sides (I really like to make sure that A on side A and B on
  side B are really correct after the ETL is done.).


How to get all records from one table and see if they are used in another without a sub query I'm trying to write a SQL statement that pulls down all the records from one table and looks to see if they are used in another. SQL is not my strongest language, so I'm sure this is a pretty simple query. Here are my two tables:

tblOrderContracts
--------------------------------------
OrderContractID           | bigint
OrderID                   | bigint
UserContractID            | bigint
UserName                  | varchar
OrderContractDateCreated  | datetime
OrderContractStatus       | varchar

tblUserContracts
-------------------------------------
UserContractId            | bigint
UserName                  | varchar
UserContractName          | varchar
UserContractFileName      | varchar
UserContractDateCreated   | datetime
UserContractStatus        | varchar

A user can apply any number of contracts to an order, so what I need to do is get a list of all the contracts, and see if they are used in a particular order. Right now, I use a sub query to get the job done, but I don't like using sub queries if I don't need to.

SELECT uc.UserContractId, uc.UserContractName,
(SELECT oc.OrderID FROM tblOrderContracts as oc WHERE uc.UserContractID =
oc.UserContractID AND oc.OrderID = 466 AND OrderContractStatus =
'Active')
FROM tblUserContracts as uc
WHERE uc.UserName = 'vandel212' AND UserContractStatus = 'Active'

Ideally I'd like to get a result set that looks like this where if value is NULL in the OrderID column, that means it's not used:

UserContractId  |  UserContractName  |  OrderID
-----------------------------------------------
36              |  test              |  466
37              |  test2             |  NULL
38              |  test3             |  NULL

Any help is greatly appreciated. Thanks!

You can use a LEFT JOIN to get the same results. Note how the WHERE condition of the subquery was moved to the ON clause of the join:

SELECT
    uc.UserContractId,
    uc.UserContractName,
    oc.OrderID
FROM
    tblUserContracts AS uc
    LEFT JOIN tblOrderContracts AS oc
    ON  uc.UserContractID = oc.UserContractID
        AND oc.OrderID = 466
        AND oc.OrderContractStatus = 'Active'
WHERE
    uc.UserName = 'vandel212'
    AND uc.UserContractStatus = 'Active' ;


Backing up a really huge SQL Server database We have a SQL Server 2008 database of size over 200GB. There is no disk space on the server to take a backup. I really need to take a backup or make another copy of this database. what would be the best way to achieve this?

Thanks

If you do not have enough diskspace first confirm that you have backup compression enabled. This will greatly decrease the size of your database backup. If you still do not have enough space on your server to put your backup.

I see 3 options of achieving this

Try backing up to a network share which has sufficient diskspace to store your database backup

--> If you do not have enough space on a networkshare

Try backing up to different files on different drives/networkshares so you split the total size of the backup and store all the parts on different locations. This will also increase the speed of your backup
(Check out http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/)

--> If you still do not have enough space on any server/share/...

If you can physically reach your server, go buy a USB disk with sufficient space and connect this to the server and backup to this location.


Database Restoration Issue I am new with postgresql.

When I attempt to restore database a .sql.gz file into the database with psql, the restore does not complete when the database size is too large (32Gb, for example).

These reason again I restore empty tables.
How can I resolve this issue?

psql -U postgres -d dbname -f <filename>

It may not be the data size, but rather the number of tables and schemas.  Database dumps and restores are known to run into problems in those cases.

For large backups the preferred backup approach is to use pg_basebackup which creates a snapshot of the database files at a specific time.  These are restored much faster.  Unfortunately this does not necessarily work for certain sorts of things, like restoring across major versions (but see pg_upgrade).

the raw data size is not a problem.  But in your case, pg_dump may not be very useful and you may want to look at other ways of making a backup.


How to denote separate records as "one and the same"? Let's say there are two separate Person records

V. Baker
Vincent Baker

each with their own various associations. These could be things like photos, contact information, etc.

And let's say that it turns out these are the same person. What's the best way to figuratively "combine" them so that it's possible to access either record and also pull in the direct and associated content from the other record/s as well? I say "figuratively" because they'll need to remain separate so that each record's unique data will be preserved.

Create a one-to-many self-referencing relationship where one Person can have one or more People?
Create a separate column with a unique identifier? And when two or more records are deemed to be the same, simply give them all the same key?
Or some other technique I don't know about yet?

My concern, though, is that whatever solution I implement, there's a good chance it will need to be transitive. So if A is noted as being the same as B, if a new C comes along and is noted as being the same as B, this should also result in C being noted the same as the original A.

Thank you in advance for any help and insights!

Here is a suggestion   users_equal_user

create table  user_equal_user (
ueu_user_id int primary key ,
ueu_link_user_id int);

Select * from users where user_id in
(select ueu_link_user_id from user_equal_user where ueu_user_id = pass_in_user_id);

This will provide a many to many relation your looking for and be unlimited

EDITED OP asked how to deal with A=B=C=D to get all the other cross linked ids
so here is some untested code that should get you close

create or replace function get_associated_ids(puser_id int,
                                       puser_id_matched int[] )
 RETURNS table(user_id int, user_id_linked int)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$
declare
    _r record;
    _found int;
    _matched int[]; --use to block already processed ids,
                    --this is here encase  of a
                    --bad entry that creates a circular
                    --reference that would create an endless
                    --recursive loop

begin
        _matched = array_append(puser_id_matched, puser_id);
        for _r in Select ueu_user_id, ueu_link_user_id
                    from user_equal_user
                    where ueu_user_id = puser_id
                        and ueu_user_id <> any(puser_id_matched)
                        and ueu_link_user_id <> any(puser_id_matched) ;

            select ueu_user_id into _found  from user_equal_user
                     where ueu_user_id = _r.ueu_user_id;
            if found then
                    _matched = array_append(_matched, _found) ;
                    return query ( select * from
                            get_associated_ids(ueu_user_id, _matched ) );
            end if;
            user_id = _r.ueu_user_id;
            user_id_linked = _r.ueu_link_user_id;
            return next;
        end loop;
end;
$BODY$


Mysql database replication on different vlan/subnet/another site Saw the posts for "MySQL high availability, failover and replication with Latency"

Is it possible to work it in a DR site when its not in the same vlan?

Being asynchronous, replication works very well across WANs or otherwise remote locations. I have used such topologies many times in the past (e.g. replication from West Coast to East Coast, from US to Middle East etc.).

Just as with normal replication, but somewhat more obvious, you must watch for slave lag if you intend to read from slave and you must have the very latest data updated. Due to latency, bandwidth issues etc. you may find that your remote slave tends to lag more than a local slave. This doesn't have to be the case and I have witnessed remote slaves keeping up just fine with the master.

If you're using a VPN, there's nothing special about setting up this kind of replication. Otherwise, please consider using replication with SSL, so that your traffic is encrypted.


What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files? Willing to solve this problem related to a wrong value for is_media_read_only database property I did some research and tests, but in the end I couldn't sort out what exactly triggers the UPDATE of the column is_media_read_only on sys.database_files.

According to sys.database_files documentation, the column is_media_read_only should have one of two possible values:

  1 = File is on read-only media.

  0 = File is on read-write media.

With this information I did the following experiment with two different versions of SQL Server:

Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1 (X64)
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Plugged a pen-drive in my notebook (Drive E:) and created a database as follows:

CREATE DATABASE [MyDB]
 ON  PRIMARY
( NAME = N'MyDB_01', FILENAME = N'D:\DataBases\MyDB_01.mdf'),
 FILEGROUP [SECONDARY]
( NAME = N'MyDB_02', FILENAME = N'E:\DataBasesPendrive\MyDB_02.ndf')
 LOG ON
( NAME = N'MyDB_log', FILENAME = N'D:\DataBases\MyDB_log.ldf')
GO

Queried sys.database_files:

USE MyDB;
GO
SELECT file_id, name, physical_name, is_media_read_only, is_read_only, state_desc
FROM sys.database_files;
GO

This was the result:

Once the database was created I opened a CMD prompt and ran diskpart. On the utility I issued the following commands:

list disk
select disk 2
attributes disk set readonly
attributes disk

Disk 2 (my pen-drive) is now a read only media, so I expected the is_media_read_only value to change, but when I queried sys.database_files again there was no change: is_media_read_only was still 0. So I started doing random procedures with the database to see if anything would make SQL Server notice that the database was now sitting on a read only media and update the value to 1. I could achieve this in two situations:

1st: changing the database to READ_ONLY mode and back to READ_WRITE.    (it must be both actions, just one of them won't do the trick):

USE master;
GO
ALTER DATABASE MyDB SET READ_ONLY;
ALTER DATABASE MyDB SET READ_WRITE;
GO

2nd: detach and attach the database back again.

These were the two situations in which I could have is_media_read_only updated to 1 as you can see on the picture:

Now it was time to revert the pen-drive to read_write again, so I went back to diskpart and ran:

select disk 2
attributes disk clear readonly

When I changed Disk 2 to read write mode back again the is_media_read_only would only update to 0 upon a detach and attach procedure.
I even tried to restart the SQL Server instance hoping it would update the value, but no luck.

Until a detach and attach of the database SQL Server would leave me with value 1 for is_media_read_only even though the disk was no longer READ_ONLY.

Thus my questions: What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files? Could the behavior shown with this experiment be a bug of SQL Server?

I'm an engineer from SQL Server product team. This read-only media behavior is not by design and we issued a hotfix for it.

To describe the behavior, the read-only media check is done when a file is opened (e.g., DB startup, DB state change like readonly->readwrite). SQL then maintains an read-only media flag for a file in memory and this can be persisted into metadata (on system catalog). The flag may be persisted into metadata when other file metadata happens to be modified or the DB state changes. The real problem here is that once it was hardened on the metadata (which is on the primary data file), we did not reset it properly even the media is no longer read-only. One condition that can reset the flag is attach, but it only does for secondary data files.

The desired behavior is that the read-only media flag is turned off when a file is opened and the media is no longer read-only. So, when a database restarts, its state changes, it is restored, or it is attached, the is_media_read_only should reflect it correctly for every file.

Fix

The fix is documented with KB Article 4538378.


Dropped column still has value when recreated with Postgres table of 150M rows I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working:

ALTER TABLE "public"."WorkoutExercises" DROP COLUMN "_etl";
ALTER TABLE "public"."WorkoutExercises" ADD COLUMN "_etl" bool;

However

SELECT
    *
FROM
    "WorkoutExercises"
WHERE
    "_etl" = TRUE
LIMIT 1000;

Returns 1000 results. Why is that, and how can this be fixed?

This can only be if ...

you did not actually commit your transaction (yet) and running the second query in a different transaction.
or you have other transactions writing to the table in the meantime
or something is seriously broken
or you are dealing with two different tables:

It's worth mentioning that your ALTER TABLE commands are on "public"."WorkoutExercises", while your SELECT is on "WorkoutExercises".

Can it be, there is a second table named "WorkoutExercises" in a different schema, and that schema shows up in your current search_path before public (or public is not in the search_path at all)?

To check:

SELECT n.nspname AS schemaname, c.relname, c.relkind
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  relname = 'WorkoutExercises';

And:

SHOW search_path;


MySQL InnoDB Index in swap I can't find any info regarding my question. This is more theoretical question.
For example, i have table keywords.

CREATE TABLE IF NOT EXISTS `keywords` (
  `kid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `keyword` varchar(120) NOT NULL,
  PRIMARY KEY (`kid`),
  UNIQUE KEY `custom_idx` (`language_id`,`keyword`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

My server has 1GB of free RAM memory. My table has millions of rows and size of my custom_idx is more than 1GB. Index can't fit into RAM memory. What would mysql do ? Is only part of this index will be stored in the memory ? Will this break BTREE/Clustered index performance dramatically ?

Also, if size of my index can't fit into innodb_buffer_pool_size limits, what would happen ?

Is replication is the answer to enormous InnoDB databases where indexes can't fit into RAM memory ?

I need to keep all my indexes in RAM ? What if my db size is more than 500gb?

InnoDB caches both data pages and index pages into the InnoDB Buffer Pool

MyISAM caches index pages only. In fact, you can do something interest if you combine replication with MyISAM.

SUGGESTION #1

Since there only 6700 spoken languages on Earth, you should use SMALLINT for language_id instead of INT. Makes for a smaller table and smaller index.

SUGGESTION #2

If you use MySQL Replication, try out this crazy idea: Convert the keywords table on the Slave to MyISAM. Leave the Master's Copy of the keywords as InnoDB.

You could run this query on the Slave ONLY to convert it...

ALTER TABLE keywords ENGINE=MyISAM;

SUGGESTION #3

Once you make the keywords table MyISAM on the Slave, you could create a dedicated MyISAM Key Cache just for keywords as follows (Example: Setup 512M KeyCache for keywords):

First create a script on the Slave

cd /var/lib/mysql
echo "SET GLOBAL keywords_cache.key_buffer_size = 1024 * 1024 *512;" > InitMySQL.sql
echo "CACHE INDEX keywords IN keywords_cache;" >> InitMySQL.sql
echo "LOAD INDEX INTO CACHE keywords;" >> InitMySQL.sql

Next, create the following entry for the startup script in /etc/my.cnf on the Slave

[mysqld]
init-file=/var/lib/mysql/InitMySQL.sql

Then, just restart mysql

service mysql restart

This keeps keyword index pages away from the general MyISAM Key Cache and in a dedicated cache.

SUGGESTION #4

Perform your keyword lookups on the Slave

I hope this helps !!!


declare keyword error mysql This is the code I've written:

delimiter //
create procedure `update_pts`()
begin

set @ctr=1;
set @cnt=(select count(*) from participant);

declare cs1 cursor for select sap from participant;

open cs1

while @ctr<@cnt do

fetch next from cs1 into @sap

set @points=(select sum(players.points) from players where pname in (select player1,player2,player3,player4,player5,player6,player7,player8 from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept from team where sap=@sap) and year=(select cyear from team where sap=@sap));
set @pointschess=(select points from players where sport='chess' and dept=(select chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept from     team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;

set @ctr=@ctr+1;

end while;

close cs1;
deallocate cs1;
end //;

MySQL gives me an error on the declare keyword saying 'syntax error, unexpected DECLARE_SYM'.
I tried changing the order of my statements by putting the 2 set statements

set @ctr=1;
set @cnt=(select count(*) from participant);

after the declare statement but then MySQL gives me an error in the while statement saying 'syntax error, unexpected WHILE_SYM, expecting ';''

Can anyone please explain to me why this is happening and how do i fix it?

Thanks

Updated code @Abdul:

delimiter //
create procedure `update_pts`()
begin

 DECLARE noMore BOOLEAN DEFAULT FALSE;

declare cs1 cursor for select sap from participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;
#set @ctr=1;
#set @cnt=(select count(*) from participant);
open cs1;

#while @ctr<=@cnt do
REPEAT
fetch cs1 into @sap;
if not noMore then
set @points=(select sum(players.points) from players where pname in (select player1 from     team where sap=@sap
                    union all
                    select player2
                    from team where sap=@sap
                    union all
                    select player3
                    from team where sap=@sap
                    union all
                    select player4
                    from team where sap=@sap
                    union all
                    select player5
                    from team where sap=@sap
                    union all
                    select player6
                    from team where sap=@sap
                    union all
                    select player7
                    from team where sap=@sap
                    union all
                    select player8
                    from team where sap=@sap));
set @pointsc=(select points from players where sport='cricket' and dept=(select cdept     from team where sap=@sap) and year=(select cyear from team where sap=@sap));
set @pointschess=(select points from players where sport='chess' and dept=(select     chessdept from team where sap=@sap) and year=(select chessyear from team where sap=@sap));
set @pointstt=(select points from players where sport='tt' and dept=(select ttdept from     team where sap=@sap) and year=(select ttyear from team where sap=@sap));
set @pointst=(select points from players where sport='throwball' and dept=(select tdept     from team where sap=@sap) and year=(select tyear from team where sap=@sap));
set @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

update participant set points = @pointsf where sap=@sap;
end if;
#set @ctr=@ctr+1;
until noMore
end repeat;

close cs1;
#deallocate cs1;
end //

I believe that your syntax is not correct can you please follow this syntax

DROP PROCEDURE IF EXISTS update_pts;
DELIMITER //

CREATE PROCEDURE `update_pts`()
BEGIN

DECLARE noMore BOOLEAN DEFAULT FALSE;
DECLARE cursorsap VARCHAR(255);
DECLARE cs1 CURSOR FOR SELECT sap FROM participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;

SET @ctr=1;
SELECT count(*) INTO @cnt FROM participant;

OPEN cs1;

REPEAT
FETCH cs1 INTO cursorsap;
    IF NOT noMore AND (@ctr < @cnt) THEN
        SELECT sum(players.points) INTO @points FROM players WHERE pname in
        (
        SELECT player1 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player2 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player3 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player4 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player5 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player6 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player7 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player8 FROM team WHERE sap=cursorsap
        );

        SELECT points INTO @pointsc FROM players WHERE sport='cricket' AND dept=(SELECT cdept FROM team WHERE sap=cursorsap) AND year=(SELECT cyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointschess FROM players WHERE sport='chess' AND dept=(SELECT chessdept FROM team WHERE sap=cursorsap) AND year=(SELECT chessyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointstt FROM players WHERE sport='tt' AND dept=(SELECT ttdept FROM team WHERE sap=cursorsap) AND year=(SELECT ttyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointst FROM players WHERE sport='throwbALL' AND dept=(SELECT tdept FROM team WHERE sap=cursorsap) AND year=(SELECT tyear FROM team WHERE sap=cursorsap);

        SET @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

        update participant SET points = @pointsf WHERE sap=cursorsap;

        SET @ctr=@ctr+1;
    END IF;
UNTIL noMore
END repeat;
CLOSE cs1;
END //

Please use this updated code let me know if you still have issues.


DML queries with different performance on similar servers I am measuring the performance between two servers that serve a database via MySQL 5.6. The DB1 server is a dedicated database server and the DB2 server shares resources with other services such as HTTP, Redis and AMQP (it has more workload).

The idea is separate all DB2 services on dedicated servers. The problem comes with the performance of DML commands between servers. Executing the same query, the DB2 (shared server) performs better than the dedicated server (between 400ms and 3500ms faster on average in all queries). They two servers mount the same type of hardware, operating system (CentOS 6) and the database is identical (including indexes).

DB1 has CPU with 8 threads and 32GB of RAM. The my.cnf configuration is as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Fine Tuning
thread_stack = 512K
key_buffer = 1G
max_allowed_packet = 32M
sort_buffer_size = 12M
read_buffer_size = 12M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 64M
query_cache_size = 512M
query_cache_type = 1
join_buffer_size = 16M
tmp_table_size = 512M
max_connections = 2048
max_heap_table_size = 64M
wait_timeout = 3000

skip-external-locking
skip-name-resolve

# InnoDB
innodb_buffer_pool_size = 24G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table = ON
innodb_log_buffer_size = 64M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 0

DB2 has CPU with 16 threads and 64GB of RAM. The my.cnf configuration is as follows:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

symbolic-links=0

# Fine Tuning
thread_stack = 512K
key_buffer = 384M
max_allowed_packet = 32M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 128M
query_cache_type = 1
join_buffer_size = 8M
tmp_table_size = 256M
max_connections = 800
max_heap_table_size = 32M
wait_timeout = 5000

skip-external-locking
skip-name-resolve

# InnoDB
innodb_lock_wait_timeout = 5000
innodb_thread_concurrency = 8
innodb_additional_mem_pool_size = 40M
innodb_file_per_table=1

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0

Anyone who knows what may be happening?

Thank you!

A large query_cache_size can hurt performance.

innodb_buffer_pool_size is quite different between the servers.  How big is the dataset?


Importing a .bacpac from SQL Azure into a local SQL Server 2016 server gives errors I'm trying to import a .bacpac created from SQL Azure into a SQL Server 2016 server that has no existing databases beforehand. It will run but run into issues such as:

  Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data
  plan execution failed with message One or more errors occurred. --->
  System.AggregateException: One or more errors occurred. --->
  System.AggregateException: One or more errors occurred. --->
  System.AggregateException: One or more errors occurred. --->
  System.Data.SqlClient.SqlException: Violation of PRIMARY KEY
  constraint 'PK_ITIssue'. Cannot insert duplicate key in object
  'dbo.ITIssue'.

I have verified the primary key is unique on the source database. It will also throw errors for some foreign key relationships. It stops importing the table it's on and continues to the next one.

Please download the latest version of SSMS to import the dacpac.

Download it from here.

Hope this helps.

Regards,

Alberto Morillo


Is there a possibility to see cascade path in MS SQL I'm having trouble with creating a foreign key in a database, because of multiple cascade paths.
I am aware of some of my FKs cascading the delete, but I cannot traverse back to find the multiple cascade path.

So I'm trying to find a way to caclulate the actual cascade paths of my database.
Is there anything besides manually querying the FKs to show the paths?

Inspired by @David Spillett I forged a small script, which will at least Show all references:

SELECT
    FK.name,
    PSCH.name + '.' + PTBL.name + '.' + PCol.name AS PARENT,
    RSCH.name + '.' + RTBL.name + '.' + RCOL.name AS REFERENCED,
    PSCH.name + '.' + PTBL.name + '.' + PCol.name + ' -> ' + RSCH.name + '.' + RTBL.name + '.' + RCOL.name
FROM
    sys.foreign_keys FK
JOIN
    sys.foreign_key_columns FKC
    ON FK.object_id = FKC.constraint_object_id
JOIN sys.tables AS PTBL
    ON FKC.parent_object_id = PTBL.object_id
JOIN sys.schemas as PSCH
    ON PTBL.schema_id = PSCH.schema_id
JOIN sys.columns AS PCOL
    ON FKC.parent_column_id = PCol.column_id
    AND FKC.parent_object_id = PCol.object_id

JOIN sys.tables AS RTBL
    ON FKC.referenced_object_id = RTBL.object_id
JOIN sys.schemas as RSCH
    ON RTBL.schema_id = RSCH.schema_id
JOIN sys.columns AS RCOL
    ON FKC.referenced_column_id = RCol.column_id
    AND FKC.referenced_object_id = RCol.object_id

WHERE
    FK.delete_referential_action = 1

With a Little CTE-Magic, this could probably be turned into something which Shows all cascade paths.

You can read information about FKs from sys.foreign_keys. The object_id and
referenced_object_id will be references to rows in sys.tables (or sys.objects) so you can pull out the names of the tables involved. If you need more detail add in sys.foreign_key_columns and sys.columns.

A simple query on those might be enough to tell you what you need, or you could use a recursive CTE to generate a complete map of the tree of relationships.


Permission denied when trying to install sp_blitz on in the master database in Amazon RDS I am trying to install sp_blitz on AWS RDS SQL Server (14.0.3223.3) standard edition and getting following error:

Msg 262, Level 14, State 18, Procedure sp_Blitz, Line 1 [Batch Start Line 0]
CREATE PROCEDURE permission denied in database 'master'.
Msg 208, Level 16, State 6, Procedure sp_Blitz, Line 2 [Batch Start Line 3]
Invalid object name 'dbo.sp_Blitz'.

Please advise, thanks.

That error message means you don't have enough permissions to install things in the master database.

That's okay: you can install sp_Blitz (and any other stored procs) in a user database instead, and they'll work fine there.

If you run into that same error in a user database (one you created), then you'll need to check with your sysadmins about your permissions.


One SQL Server 2014 giving different password hash I've been using script based off Microsoft's EXEC sp_help_revlogin script to synchronise SQL Server usernames and passwords between hosts. This has been working fine for a long time now on SQL Server 2008 R2, but with us planning our move to SQL Server 2014 I've run into a snag.
I have three staging SQL Server 2014 machines. On all the servers I run the following command:
CREATE LOGIN [TestAccount]
WITH PASSWORD = N'1234567890',
     DEFAULT_DATABASE = [master],
     CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

For two of them, I get a generated "Create" command of:
CREATE LOGIN [TestAccount]
WITH PASSWORD = 0x010075DDCA54FCEF12CB11C4E64040E877B9FF5872C41EB98095 HASHED,
     SID = 0xCA78345104805C4E89004969D05D551B,
     DEFAULT_DATABASE = [master],
     CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Cool. So far, so good. But, on the third server, I get a "Create" command of:
CREATE LOGIN [TestAccount]
WITH PASSWORD = 0x02002F6CB52E7F571AD422689021EB9EC1BE2AB4576AE6EC12485333A4CB892A9197B440E1471376A5AAC5160847F636A637D1F499880D7653ABC4DB4714746856E01DE41E09 HASHED,
     SID = 0x19337EA32254A64F8FC018474B58DBA5,
     DEFAULT_DATABASE = [master],
     CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Which doesn't work. The hash is way too big.
Is there some compatibility level flag or something that I've somehow got turned on on this one server that is generating old fashioned password hashes or something?

Giving the checkmark to Ryan as he was right. I was actually connecting to the wrong instance installed on the same physical machines as the SQL Server 2014 instances. I was very tired on Friday night.

This is because since SQL Server 2012 the hash algorithm has changed.

Starting from this version, the hash is twice as long.
You can read up on the three hash versions that are used for the various versions of SQL Server in the sp_help_revlogin microsoft article, under Remarks:

  A password can be hashed in three ways:
  VERSION_LEGACY: This hash is a 16-byte pre-SQL Server 2000 hash.
  VERSION_SHA1: This hash is generated by using the SHA1 algorithm and is used in SQL Server 2000 through SQL Server 2008 R2.
  VERSION_SHA2: This hash is generated by using the SHA2 512 algorithm and is used in SQL Server 2012.

Which means that if you want to move a SQL Server 2012 or higher user to a 2008r2 instance, you'll need to find a different method.

I tested it quite a bit now. Any user created under SHA1 when moved using the password hash to a new version server (2012+) can then be moved back as long as the password isn't changed. However any new user created in 2012+ will not be moveable to 2008r2 or prior.


Bundle SQL scripts for Oracle I have bunch of sql-scripts used to create various tables, procedures, functions and views. Everything is in separate files; is there a good way to bundle these and run each file in a specific order? Merging/concatenating all the files into one is also a possibility, but I'd prefer a bundle if possible.

Example (fictional/pseudocode): Master.sql contains the following:

src: DDL/table1.sql
src: DDL/table2.sql
src: fns/function1.sql
src: fns/function2.sql
src: views/view1.sql

...and so on. Running Master.sql would then execute each of the scripts listed in the file in the order listed. Is something like this possible?

You can run them in SQLPlus like this;

@DDL/table1.sql
@DDL/table2.sql
@fns/function1.sql
@fns/function2.sql
@views/view1.sql

Make sure each one commits where necessary and don't finish each individual script with EXIT.


Why are the system tables in MySQL and MariaDB utf8 and not utf8mb4? I know the general advice is never to use utf8, but instead to use utf8mb4. If that's the advice, then why do the system tables themselves use utf8?

You can see this in the MariaDB test suite, and and the MySQL test suite; or, by using SHOW CREATE TABLE mysql.db;

By Definitions as per MySQL Reference Manual:

  utf8mb4: A UTF-8 encoding of the Unicode character set using one to
  four bytes per character.

  utf8mb3 : A UTF-8 encoding of the Unicode character set using one to
  three bytes per character.

Since, utf8mb3 (also known as UTF8) can not store all languages like Chinese or Emoticons, it is recommended to use utf8mb4 when it comes to store application data.

For simple example, You want to extend your existing business into China.  if you have used utf8mb4, your application is always ready to adopt your business extension while in case of utf8mb3(UTF-8), you need an character set upgrade to your application.

While storing system's data MySQL does not store information (supplementary character) which requires utf8mb4, this could be the reason, why MySQL itself is not using utf8mb4.


Retrieve logon trigger from backup A logon trigger was added and dropped from our production database. I have full backup of master after the trigger was added, and before it was dropped. I am trying to recover from the backup but I cannot see it under the server_triggers of that backup. Is there a way to recover logon trigger from a DB backup?

The backup is from 11PM yesterday night. Trigger was added yesterday morning and dropped today morning.

Restoring master is a bit of a pain.

I assume you are just trying to recover the definition?

I just tried creating a logon trigger, backing up master and opening the *.bak file in notepad and searching for "CREATE TRIGGER" - it was plainly visible in the file.

Alternatively you could restore it as a regular user database (assume master2 below) and see if the following finds it (will need to connect via the DAC)

USE master2

SELECT *,
       CAST(imageval AS NVARCHAR(MAX)),
       CAST(imageval AS VARCHAR(MAX))
FROM   sys.sysobjvalues
WHERE  CAST(imageval AS NVARCHAR(MAX)) LIKE N'%TRIGGER%'
        OR CAST(imageval AS VARCHAR(MAX)) LIKE '%TRIGGER%'

The above worked for me. YMMV and make sure you sanity check any results (the first method definitely won't work if the definition is split over multiple pages and may find ghosted versions).

If the trigger object is encrypted, I imagine it would be visible in sys.sysobjvalues using the DAC, though certainly my LIKE clauses wouldn't find it. You would need to look in sys.sysschobjs and get the objid from the name. Hopefully it isn't encrypted or it would need a good read of this article: The Internals of WITH ENCRYPTION


Update badly formed XML to allow parsing I have inherited a production system that needs some fine tuning to enable easy extract of the result set.  A record gets added to a table which contains an XML field, with contents similar to the below:

<PROG>PROGX_</PROG><STNTYPE>LEAKTEST</STNTYPE><STNID>S1</STNID><TRCODE>SN_REQUEST</TRCODE><OPERATORID>222</OPERATORID><PARTID>PARTID</PARTID><TRSTATUS>0000</TRSTATUS><TRTEXT>TEST OK</TRTEXT><T1_USL>0.45</T1_USL><T1_LSL>-0.45</T1_LSL><T1_RESULT>05,CC/M</T1_RESULT><T1_STATUS>OK</T1_STATUS><OVERALL_RESULT>1</OVERALL_RESULT><OVERALL_STATUS>OK</OVERALL_STATUS>

However, I believe the formatting of the XML is invalid and I want to be able to clean this up a bit, by adding a root and grouping this a bit better.  Something similar to along the lines of:

<RESULTSET>
    <PROG>PROGX_</PROG>
    <STNTYPE>LEAKTEST</STNTYPE>
    <STNID>S1</STNID>
    ...etc...
    <OVERALL_STATUS>OK</OVERALL_STATUS>
</RESULTSET>

(The idea being then that I can use SQL views to then properly split this).

Any ideas if this is possible?  I'm new to XML on SQL.

You can get this information on SQL Server without adding a root element:

create table tbl (foo xml);

insert into tbl values ('<PROG>PROGX_</PROG><STNTYPE>LEAKTEST</STNTYPE><STNID>S1</STNID><TRCODE>SN_REQUEST</TRCODE><OPERATORID>222</OPERATORID><PARTID>PARTID</PARTID><TRSTATUS>0000</TRSTATUS><TRTEXT>TEST OK</TRTEXT><T1_USL>0.45</T1_USL><T1_LSL>-0.45</T1_LSL><T1_RESULT>05,CC/M</T1_RESULT><T1_STATUS>OK</T1_STATUS><OVERALL_RESULT>1</OVERALL_RESULT><OVERALL_STATUS>OK</OVERALL_STATUS>
');

select
    foo.value('(/PROG)[1]', 'nvarchar(100)') PROG,
    foo.value('(/STNTYPE)[1]', 'nvarchar(100)') STNTYPE,
    foo.value('(/STNID)[1]', 'nvarchar(100)') STNID
from   tbl;
GO

PROG   | STNTYPE  | STNID
:----- | :------- | :----
PROGX_ | LEAKTEST | S1

db<>fiddle here


Creating 7 digit codes in database for every new insertion I am aware that using identity column we can auto-increment the keys from 0, one-by-one. What I want to do is:

Generate codes in SQL server 2014 which will be of 9 digits (operational codes).

First 2 digits are going be pre-defined and remaining 7 will increment by 1 for every new insertion of record.  For example:

51 (predefined ) and remaining 7 are 0000001  and so insertions will happen as:

510000001
510000002
510000003
510000004
510000005
510000006

and so on.

If it always needs to start with 51 then you can use a sequence (or identity) that starts at 510,000,001.

CREATE TABLE #Test (
    ID BIGINT IDENTITY(510000001, 1),
    SomeColumn VARCHAR(10)
);

INSERT  INTO #Test (SomeColumn)
VALUES ('Test'), ('Test2');

SELECT * FROM #Test AS t;

Just keep in mind that once the sequence reaches 519,999,999, it will not restart at 510,000,001 (particularly if you use the identity property) but will continue at 520,000,000.

Also, since you are on SQL Server 2014, please consider the fact that starting from the 2008 version IDENTITY values are cached and can have gaps in them after a restart. If you want to have the values follow exactly (i.e. no gaps) you should either use a SEQUENCE starting at 510000001 or apply Trace Flag 272.
This seems to be documented in this connect item


How to insert values into a table with default values from a select query in PostgreSQL? Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null?

In my case, the SELECT statement is selecting from JSON.

In the attempts below, I was successful by explicitly retrieving the sequence but I am hoping there is a way to either INSERT using the DEFAULT values. Or select DEFAULT values without having to call the default functions explicitly.

-- Example table
create table animals
(
    id serial,
    nm character varying (30) NOT NULL, --name
    typ character varying(10),
    tvi integer,
    tvf numeric(8,3)
);

insert into animals VALUES (DEFAULT,'mouse','m',4,12.45);

select row_to_json(a) from animals a;

select * from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

--All good.

-- Attempt #1
INSERT INTO animals
select id ,nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

/*
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, mouse, m, 4, 12.450).
********** Error **********

ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, mouse, m, 4, 12.450).
*/

-- Attempt #2
INSERT INTO animals
select DEFAULT,nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

/*  I didn't  expect this to work, but it does illustrate what I am trying to accomplish
ERROR:  syntax error at or near "DEFAULT"
LINE 2: select DEFAULT,nm,typ,tvi,tvf from json_populate_record(null...
               ^
********** Error **********

ERROR: syntax error at or near "DEFAULT"
SQL state: 42601
Character: 28

*/

-- Attempt #3
INSERT INTO animals
select nextval('animals_id_seq'::regclass),nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

/*  This works, but I'm hoping for a way to accomplish this without knowing the underlying functions generating the default values.
Query returned successfully: one row affected, 11 msec execution time.
*/

select version();  --'PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit'

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like:

INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po.....

You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.


can't create foreign key in sqlite I'm trying to run this code in sqlite:

sqlite> CREATE TABLE test(id integer primary key AUTOINCREMENT, page varchar(10), dp_record_id integer, foreign key (webserver_id) REFERENCES target_dp(id));

But I'm getting the following error message:

  Error: unknown column "webserver_id" in foreign key definition

Here's the parent table:

sqlite> .schema
CREATE TABLE target_dp(id integer primary key AUTOINCREMENT, server_name varchar(255), location_code varchar(10), active bit(1));
sqlite>

I can't see where my syntax error is.
Thanks in advance for your time.

You are missing a webserver_id column in the TEST table.

CREATE TABLE test(id integer primary key AUTOINCREMENT
                , page varchar(10)
                , dp_record_id integer
                , webserver_id integer
                , foreign key (webserver_id) REFERENCES target_dp(id));

Works just fine.


Mysql - Problem speed of access on query (Low latency query sometime) My web application is real time site that need a speed of access on every query (SELECT, UPDATE, INSERT) that must less than 200ms

Because one of my php script task is query (SELECT, UPDATE, INSERT) the work on every secound
and then loop every secound alway across web server running.

Well, On average 98% each query time is less than 0.001-0.005 secound on each query stagement. the problem is
Sometime around 3 time per hour, the query (SELECT, UPDATE, INSERT) can take up to 1s and may be 5s, so they has waiting and effect the next query loop that damage for whole business application.

i so find the solution such as row locking, turning mysql it not ensure speed of access less than 200ms, sometime it query 5s ooh!!
but the solution that i found to reducing the problem are move that from INNODB to MEMORY but sometime still a problem and the important thing are the need the recovery when system has crash.

  Example >> in some UPDATE problem that table size < 1M with 30 record and update on primary key it not sence

the question are it has many thing effect each other. HOW to slove this problem. How to start to find problem.

Do you have any idea or algorithm or if u need more some information i will give you.

thank you
so sorry about my english. i am thai

You may switch the table from innodb to memory, but ensure the size of the table isn't that huge so that it doesn't eat up entire of its memory. Yes, you may safe guard or recover the table during system crash. Have master/slave replication setup and once the table is created on master with memory as engine type and change the table engine type to innodb (on slave only). So that even at the instance of master crash/restart you will get the data from slave.


Grant SELECT on all tables in Redshift I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group:

CREATE GROUP data_viewers;
CREATE USER <user> PASSWORD '<password>' IN GROUP data_viewers;

Now I would like to allow this group to be able to read data from any table:

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO GROUP data_viewers;

The command returns GRANT. Now when I connect to Redshift as my newly created user and issue SELECT * FROM something.something; I get:

  permission denied for schema something

I tried granting permissions to something: GRANT SELECT ON ALL TABLES IN SCHEMA something TO GROUP data_viewers; but this has not changed anything.

How can I allow users from my group to SELECT data from any table in the schema?

You need the USAGE privilege (at least) for the schema as well:

GRANT USAGE ON SCHEMA something TO GROUP data_viewers;

Related Postgres example:

Permission for sequence in another schema

Remember you only granted permissions to already existing tables. Does not apply to tables created later. To cover those, too:

ALTER DEFAULT PRIVILEGES FOR USER role_that_creates_tables
IN SCHEMA public
GRANT SELECT ON TABLES TO GROUP data_viewers;

Amazon Redshift implemented DEFAULT PRIVILEGES as well.

Here is a complete cookbook for Postgres:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

Be aware of some differences between mainline Postgres and Redshift! Redshift sticks to separate users and groups, while Postgres replaced that with the universal concept of roles:

Why did PostgreSQL merge users and groups into roles?

And I am not sure how Redshift handles sequences ...