How to copy a PostgreSQL database at a filesystem level between servers I have a large PostgreSQL database on a server with a failing disk. I cannot run pg_dump because of disk errors.

I have another server with the identical OS (Ubuntu 16.04) and the identical version of PostgreSQL (9.5.6). How do I copy the raw database files to the new server? I don't care if read errors corrupt parts of the database, as I have ways of identifying and fixes those issues, but I need the data copied over before I can fix it. From what I can tell, there aren't many read errors, but even one will stop pg_dump.

There's a scant wiki page on this topic, and it suggests doing a "filesystem level copy" but makes no mention of how to actually do this.

Edit: I used rsync to copy over my data directory (/var/lib/postgresql/9.5/main) and configuration directory (/etc/postgresql/9.5/main). Running sudo service postgresql start on the new server runs without error, but trying to connect via sudo -u postgres psql returns:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

and the log shows:

2017-05-23 15:13:08 EDT [14604-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-05-23 15:13:08 EDT [14603-2] LOG:  MultiXact member wraparound protections are now enabled
2017-05-23 15:13:08 EDT [14602-1] LOG:  database system is ready to accept connections
2017-05-23 15:13:08 EDT [14608-1] LOG:  autovacuum launcher started
2017-05-23 15:13:43 EDT [14602-2] LOG:  received fast shutdown request
2017-05-23 15:13:43 EDT [14602-3] LOG:  aborting any active transactions
2017-05-23 15:13:43 EDT [14608-2] LOG:  autovacuum launcher shutting down
2017-05-23 15:13:43 EDT [14605-1] LOG:  shutting down
2017-05-23 15:13:45 EDT [14605-2] LOG:  database system is shut down
2017-05-23 21:33:29 EDT [27345-1] FATAL:  could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

What is this missing certificate and how do I fix it?

Edit: I've fixed the SSL error by following these instructions, and now all my files seem to have been transferred.

My database seems to be largely accessible. The only issue I've found is when I go to do a full vacuum, I get the error:

ERROR:  could not open file "base/106800/107273": No such file or directory

How do I fix this? I realize it's file corruption, probably caused by that file not being readable during the rsync. How do I "fill it in" with blank values so the vacuum can proceed? The error occurs when vacuuming a specific table, and the data in that table can be regenerated.

This is the script I ended up having to write to transfer all the files, meant to be run from the destination server.

#!/bin/bash
set -i

# Ensure databases on both servers are stopped.
sudo service postgresql stop
ssh -t myuser@oldserver "sudo service postgresql stop"

# Ensure our user owns postgres's files so our rsync call can access them.
ssh -t myuser@oldserver "sudo chown -R myuser:myuser /etc/postgresql"
ssh -t myuser@oldserver "sudo chown -R myuser:myuser /var/lib/postgresql"

# Copy down all the files.
sudo rsync --progress -azv --delete myuser@oldserver:/etc/postgresql/9.5/main/ /etc/postgresql/9.5/main
sudo rsync --progress -azv --delete myuser@oldserver:/var/lib/postgresql/9.5/main/ /var/lib/postgresql/9.5/main

# Fix permissions.
ssh -t myuser@oldserver "sudo chown -R postgres:postgres /etc/postgresql"
ssh -t myuser@oldserver "sudo chown -R postgres:postgres /var/lib/postgresql"
sudo chown -R postgres:postgres /etc/postgresql
sudo chown -R postgres:postgres /var/lib/postgresql

# Fix a missing ssl cert that pg uses for connections.
cd /etc/ssl/certs
sudo make-ssl-cert generate-default-snakeoil --force-overwrite

# Bring the server back up.
sudo service postgresql start

Rsync reported some errors, but I'm browsing my databases via psql and pgadmin3 and so far everything looks correct.

The fix for the SSL error I found here.


Oracle RESTRICTED SESSION error I tried to install Oracle database 12c on my machine following the documentation:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/Windows_DB_Install_OBE/Installing_Oracle_Db12c_Windows.html

I have successfully installed the database itself and can login to the database pdborcl using:

sqlplus sys/Oracle_1@pdborcl as sysdba

I unlocked the hr user following the instruction using:

alter user hr identified by hr account unlock;

However, when I tried to connect to the database using the newly unlocked user: hr, it gives an error message:

SQL> connect hr/hr@pdborcl
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege.

Warning: You are no longer connected to ORACLE.

Can someone let me know how to solve this problem? Is this something with licensing?

UPDATE:

I actually tried:

alter system disable restricted session;

What I get is:

ERROR at line 1:
ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted.

The query: select logins from v$instance returns:

LOGINS
---------
ALLOWED

If alter system disable restricted session doesn't work it means that there are patch violations

Check the status with below command:

select * from PDB_PLUG_IN_VIOLATIONS;

If post steps did not complete properly then it will keep PBD's in restricted mode. Run the below steps:

sqlplus / as sysdba

shutdown immediate
startup upgrade
alter pluggable database all open upgrade;
quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose

check the log, should be no errors

sql> shutdown immediate
sql>startup
sql> alter pluggable database all open;


How to find out which object is taking space? I have database of size 537gb. How can I find out which object is taking space.
I executed sp_spaceused, which showed me unallocated space is 502GB and 88GB used space. How can I release unallocated space to get some free space. My HDD is getting full due to this. Please advise.

Edit: I have checked via below script as well. But total UsedSpaceMB of each table is 47GB. Still I am unable to figure out how DB size is 537 GB.

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
    ON i.object_id = p.OBJECT_ID
        AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 7 DESC

It appears that your database data file has empty space.

If you know for sure that the data file will not grow out again, you could shrink the file.

If you have to shrink, the cleanest way is doing this with TRUNCATEONLY so you don't move any data pages and only release the space back to the OS if it is possible.

You can get the data file(s) with this query, change the databasename to your database.

SELECT [name]
FROM sys.master_files
WHERE database_id = db_id('DatabaseName')
AND type_desc = 'ROWS';

Afterwards you could try shrinking the data page until 100GB without any movement.

Use [DatabaseName]
GO
DBCC SHRINKFILE ('name',102400 ,TRUNCATEONLY);
-- try to shrink until 100GB, without moving data pages

You can read up on shrinking date files and...

why it sucks here
why it's bad for performance here


Optimize architecture for SQL Server projects I often find myself hacking together multiple .sql files for a project and calling them one after the other through Task Scheduler. Everything I know about SQL is self-taught, so I don't know anything about useful shortcuts and best practices. I imagine there has to be a more elegant way to do what I do.
Example
Right now I work on a project in which I map GPS data from my camera photos to the GPS data of some addresses which are stored on a different SQL Server:

Import all new Address-Strings from a different SQL Server on the network (insert into..select from)
Fix the spelling of those strings (stored procedure with 50+ SET @str = REPLACE(@str, yada, yada))
call wget http://maps.googleapis.com/maps/api/geocode/json?address= [adress string] with xp_cmdshell and store the result in a text file
read it with SELECT BulkColumn FROM OPENROWSET(BULK N'D:\resultfile.txt', SINGLE_CLOB) AS Contents) to later extract Latitude and Longitude and store them in the address table
run exiftools.exe against new photos to get their GPS data too, store results in csv, BULK INSERT this CSV file into the pictures table
map pictures to addresses
create directories and move photos with xp_cmdshell

These actions are all accompanied by many layers of if xyz is not null BEGIN END", REPLACE(xyz, CHAR(10), '') and so on.
I also have three views, which are used by xampp/mysql on a local website to

show all address-photo-mappings in a tree-structure, including links to those photos
show all photos which were taken without GPS data or couldn't be mapped to a known address
show all addresses which couldn't be resolved by Google

All in all, I have:

6 .sql files
6 .cmd files which all do sqlcmd -S server\instance -i yada.sql
a scheduled task which calls these 6 .cmd files
3 views

Every little change I want to introduce makes me

re-check nearly all of these .sql files for compatibility
disable scheduled task while testing changes, reenable afterwards
alter view as.. because I can't just sync views via github

It is very tedious. Do you have any tips or something I should Google? I don't mind reading a bit on my own. Thanks!

You could create a script outside SQL Server (Powershell, VBScript, console app) which does away with your text files. The script would read input data from SQL, call the external utility and write the result directly back to the database.

If it was something written in C# then you have all the handy source control, automated tests stuff. Unfortunately, using SQL to perform external processes is possible, but painful. Sometimes you just need to chuck it in and write some middleware (in the middle of SQL and the external tools) to do the job for you.

For example, I wrote a VBScript that extracts a list of all tables from the DB then searches through .dtsx files to find them, then writes the result directly back to the database.

You may also be able to use SSIS and SQL Agent to simplify your process. SSIS is good for defining orchestration and data transfer in a visual way, though for more complex external things you often need to resort to C# script tasks. SQL Agent can be used to run SQL and command lines in order with basic workflow etc.

Community Wiki answer created from question comments by Nick McDermaid


How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database? I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control.
Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical sensory data (like room temperature). The real file system is an SD card, so frequent writes destroy it. Storing data in-memory would be a good option.
What is not a solution:

Memory storage engine can't handle TEXT columns
Aria storage engine can't handle foreign keys
MyRocks storage engine is not available for 32-bit platforms
SQLite ///:memory: can't handle the "load", throws uncountable 'cannot commit - no transaction is active' errors

What I've tried:

using tmpfs for datadir and tmpdir
tweaking InnoDB to minimal resource usage
disabling doublewrite buffering, disabling change buffering, using READ-UNCOMMITTED isolation

See configuration
But when I purge old data, the DELETE causes ibdata1 to grow nearly to the 100% size of the table I delete 10-15% of the rows from. My guess is that Rollbacks Segments and Undo Tablespace is still very active, but I can't "disable" MVCC altogether.
How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?

Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.
And:
The undo log is usually part of the physical system tablespace, but from MariaDB 10.0, the innodb_undo_directory and innodb_undo_tablespaces system variables can be used to split into different tablespaces and store in a different location (perhaps on a different storage device).
https://mariadb.com/kb/en/innodb-undo-log/
For MEMORY: What about VARCHAR(65532)?


Oracle not using index when > used in where clause We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.

Because there is no end clause to the date range, oracle doesn't choose to use the date index and ends up doing a very expensive table scan instead.  However we know this data is temporal, so we know there is no data in the future.  So if we modify the sql to include an end date of sysdate for the time period then the query uses the index and it becomes very cheap.

Unfortunately, due to a bug in the application we use we are unable to change the application. So is there any way in Oracle that I can either "teach" oracle that the max data in that table is today, so it can make a clever decision, or is the only real way to solve this problem at the database level to pin the plan?

Rgds,
Dan

(Oracle 10 Solaris Sparc)

Oracle should be able to use the statistics to determine the high and low values for any column if you are keeping them up to date:

drop table foo;
create table foo(id date primary key);
insert into foo select sysdate-level from dual connect by level<31;
select low_value, high_value from dba_tab_columns where table_name='FOO';
/*
LOW_VALUE                                    HIGH_VALUE
-------------------------------------------- ------------------------------------------

*/
execute dbms_stats.gather_table_stats('JDOUGLAS', 'FOO');
select low_value, high_value from dba_tab_columns where table_name='FOO';
/*
LOW_VALUE                                    HIGH_VALUE
-------------------------------------------- ------------------------------------------
786F0C0A0E0837                               787001080E0837
*/


IS_MEMBER() in Azure SQLDB not working for AD groups? I am trying set up RLS and want to leverage AD groups.
DB is created in Azure, and I know AD is working as I can connect using AD acount with SSMS

First test Local Machine, is working as expected (img 1)

Now try Azure

The IS_MEMBER() appears to not work as expected in Azure for AD Groups, but it is recognising it a as valid AD domain of the Domain/group (see first 2 lines)

Any ideas??  I want to be able to have a view, that has the ADGroups and then put Row Level Security RLS to filter the view for different groups

Cheers

Mark

This is quite old tread but I was facing similar issue, so if someone encountered it, hope this might help.
I had RLS implemented on SQL Server on IaaS and it was based on Active Directory Groups checks by IS_MEMBER() function. (I have sync with AAD, but here used config with local AD). It worked well until I migrated to PaaS (Azure SQL DB Single instance).
First thing was that while on IaaS I used to refer to AD groups from local AD domain controler:domain\groupname
CREATE LOGIN [domain\groupname] FROM WINDOWS
CREATE USER [domain\groupname] FOR LOGIN [domain\groupname]
select is_member('domain\groupname')
In PaaS I needed to create users for groups and reference to them without domain, as I had to switch to use integrated Azure Active Directory:
groupname
CREATE USER [groupname] FROM  EXTERNAL PROVIDER
select is_member('groupname')
Second thing was something that looks like a bug (currently have open ticket with MS)
While IS_MEMBER worked correctly for test users in PaaS, I had problem with Admin Accounts. Taking for example user1 who was in [adminGroup] on DataDB IS_MEMBER always returned 0, no matter which AAD group I was checking. What was more strange, when changing to Master db IS_MEMBER worked fine there for any AAD group!(offtopic: on master db users where also created to avoid problem with SSMS default option to reach to master DB first, and without addition there non-admin users would get connection error (so either users needed to specify in SSMS connection options destination DB, or be added with connect rights to master DB))
 It turned out that having [adminGroup] being set up as Azure Active Directory Admin on Azure SQL Server was reason for this strange behavior. I changed in our code to use [adminGroup2] for RLS, and removed admins (users) in AAD from [adminGroup] which was kept as SQL Server Azure Active Directory Admin. And with just this change IS_MEMEBER started to work just as expected!
 Other workaround I have found here, would be to use IS_MEMBER('db_owner') for admin group (as those default roles checks are working fine, but this needs additional changes to security config and would not solve problems for other cases then admins, but actually looks like this is the only problem here.
 Waiting to hear response from MS about this bug, as I doubt that that would be implemented by design...


How to add the value of my Sub-query, I want to add the value of Cash,Charge and Other_Charges as Total_payment I am using 2 tables, TL_FISCALREPORT and TL_TENDER. What I want to do is to add the total value of my 3 subqueries from TL_TENDER named as cash, charge, tender and show it in my query:

SELECT DISTINCT F.DT_TIME,F.DT_DATE,'DEPT STORE' AS CATEGORY,
      F.STOREID,F.TID,(F.CURRENT_POSITIVE) AS NEW_GT,
      PREV_POSITIVE,TOTAL_SALES_AMT,
      CAST((((F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT)/.80)*.20) AS DECIMAL(16,2)) AS
      SC_DISC,(0) AS PWD_DISC,F.REGULAR_DISCOUNTS_AMOUNT,(0) as NON_PROMO_DISC,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT) AS
      TOTAL_DISC,F.NEW_GRAND_TOTAL_VOIDS,F.DAILY_RETURNS,(0) AS ADJ,
      (TOTAL_SALES_AMT-(F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT)-0) AS GROSS_SALES ,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT) AS  SC_SALES,(0) AS PWD_SALES,(F.SC5_DISC_AMOUNT
      +F.SC20_DISC_AMOUNT+0) AS NON_VAT_SALES,F.ZERO_RATED_SALES_AMOUNT
      ,CAST((F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-
      F.ZERO_RATED_SALES_AMOUNT/1.12)AS DECIMAL(12,1)) AS VATABLE_SALES,CAST(( ((F.DAILY_GROSS_SALES-
       F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT)*.12)/1.12) AS DECIMAL(15,2)) AS
       VAT,CAST((F.DAILY_GROSS_SALES-(F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-
       F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT*.12/1.12)) AS DECIMAL(12,1))
       AS NETSALES,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) CASH,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') CHARGE,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') OTHER_TENDER
,F.FROM_FISCAL_XACT,F.TO_FISCAL_XACT ,F.POS_ITEMS_COUNT,F.PREV_VOID_COUNT,(0) AS NOSALE_TRANSACTION,
F.REPORT_TYPE FROM TL_FISCALREPORT   F
WHERE F.REPORT_TYPE=1 AND F.STOREID=1 AND F.DT_DATE ='2016-06-30'
ORDER BY TID

You just need to add this as another column in the select list:

  (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) +
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') +
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') AS YOURTOTAL

Giving:

SELECT DISTINCT F.DT_TIME,F.DT_DATE,'DEPT STORE' AS CATEGORY,
      F.STOREID,F.TID,(F.CURRENT_POSITIVE) AS NEW_GT,
      PREV_POSITIVE,TOTAL_SALES_AMT,
      CAST((((F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT)/.80)*.20) AS DECIMAL(16,2)) AS
      SC_DISC,(0) AS PWD_DISC,F.REGULAR_DISCOUNTS_AMOUNT,(0) as NON_PROMO_DISC,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT) AS
      TOTAL_DISC,F.NEW_GRAND_TOTAL_VOIDS,F.DAILY_RETURNS,(0) AS ADJ,
      (TOTAL_SALES_AMT-(F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT+F.REGULAR_DISCOUNTS_AMOUNT)-0) AS GROSS_SALES ,
      (F.SC5_DISC_AMOUNT+F.SC20_DISC_AMOUNT) AS  SC_SALES,(0) AS PWD_SALES,(F.SC5_DISC_AMOUNT
      +F.SC20_DISC_AMOUNT+0) AS NON_VAT_SALES,F.ZERO_RATED_SALES_AMOUNT
      ,CAST((F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-
      F.ZERO_RATED_SALES_AMOUNT/1.12)AS DECIMAL(12,1)) AS VATABLE_SALES,CAST(( ((F.DAILY_GROSS_SALES-
       F.SC5_DISC_AMOUNT-F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT)*.12)/1.12) AS DECIMAL(15,2)) AS
       VAT,CAST((F.DAILY_GROSS_SALES-(F.DAILY_GROSS_SALES-F.SC5_DISC_AMOUNT-
       F.SC20_DISC_AMOUNT-F.ZERO_RATED_SALES_AMOUNT*.12/1.12)) AS DECIMAL(12,1))
       AS NETSALES,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) CASH,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
               FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') CHARGE,
           (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                FROM TL_TENDER
                  WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') OTHER_TENDER
,F.FROM_FISCAL_XACT,F.TO_FISCAL_XACT ,F.POS_ITEMS_COUNT,F.PREV_VOID_COUNT,(0) AS NOSALE_TRANSACTION,
F.REPORT_TYPE,
      (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT ))
                   FROM TL_TENDER
                      WHERE TL_TENDER.TID = F.tid AND TL_TENDER.NTND =1 and TL_TENDER.DT_DATE='2016-06-30' ) +
               (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                   FROM TL_TENDER
                      WHERE TL_TENDER.TID = F.TID AND TL_TENDER.NTND =2  AND TL_TENDER.DT_DATE='2016-06-30') +
               (SELECT DISTINCT SUM(CAST(AMOUNT AS FLOAT))
                    FROM TL_TENDER
                      WHERE TL_TENDER.TID = F.TID  AND TL_TENDER.NTND >=3 AND TL_TENDER.DT_DATE='2016-06-30') AS YOURTOTAL
 FROM TL_FISCALREPORT   F
WHERE F.REPORT_TYPE=1 AND F.STOREID=1 AND F.DT_DATE ='2016-06-30'
ORDER BY TID


Query an intermediate table and join to child table Here's my database structure:

Product
Market
ProductMarket (linked to Product and Market with "product_id" and "market_id"
Price (linked to ProductMarket with "productmarket_id")

I want to query the ProductMarket table and filter by a particular product. Then, I want to associate the latest price (from the Price table) to the product (for each market), based on a date field in the Price table.

Right now, I have that:

SELECT
    mp.id AS id,
    p.name AS productName,
    m.name AS marketName,
    mp.identifier
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
WHERE
    productName = 'SomeProduct'

I'm having difficulty integrating the Price table in. How can I achieve this?

This sounds like a job for a correlated subquery.

SELECT
    mp.id AS id,
    p.name AS productName,
    m.name AS marketName,
    mp.identifier,
    (SELECT the_price_column
       FROM price pr
      WHERE pr.marketproduct_id = mp.id
      ORDER by the_date_column DESC LIMIT 1) as latest_price
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
WHERE
    productName = 'SomeProduct'

Your question said "productmarket" but the query you posted used "marketproduct" so I went with the latter in the example.

If the price table has no prices for a particular product, this query will return null for the price of that product.

The price table needs a multi-column index on (marketproduct_id, the_date_column) to optimize this query.

Update: You can get other columns, too, by repeating the subquery and asking for something different...

...
    m.name AS marketName,
    mp.identifier,
    (SELECT the_price_column
       FROM price pr
      WHERE pr.marketproduct_id = mp.id
      ORDER by createddate DESC LIMIT 1) as latest_price,
    (SELECT createddate
       FROM price pr
      WHERE pr.marketproduct_id = mp.id
      ORDER by createddate DESC LIMIT 1) as latest_date
FROM
    marketproduct mp
...

Or, you could try it with a join, moving the subquery to a join condition in the where clause to get as many columns as you need from the price table.

SELECT
    mp.id AS id,
    p.name AS productName,
    m.name AS marketName,
    mp.identifier,
    pr.the_price_column,
    pr.createddate
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
INNER JOIN
    price pr ON pr.id =
    (SELECT id
       FROM price prx
      WHERE prx.marketproduct_id = mp.id
      ORDER by createddate DESC LIMIT 1)
WHERE
    productName = 'SomeProduct';

Both of these feel slightly messy, because in the first example, we're dipping into the price table twice, and in the second example, we're referencing the price table twice with two different aliases ... but it's hard to see a way to avoid that.  The performance of the two queries should be fairly comparable, though.  In both cases, having to go twice isn't as bad as it sounds because whichever element hits the table first will have already brought the index and row data into the buffer pool (InnoDB) or OS/key cache (MyISAM) so the penalty for the second hit on the same row shouldn't be significant.


Restore PROD database to QA with AlwaysOn I want to create a script/job that will run weekly to backup my PROD database, and restore it to my QA server, then run a custom maintenance script against it to cleanup and prepare the data to be analysed.

The problem is that my QA server is running AlwaysOn Availability Group.
I have access to Powershell, "DBATools.io" scripts, SQL Server Agent, and a remote SQL server if it can help. All under SQL2016.

I also just discovered today the SQL2016 feature called AUTOMATIC SEEDING. I tried it, and it allows me to bypass the complicated "Add database to AG" steps.

So, up to now, I can simplify the steps down to this:

ALTER AVAILABILITY GROUP [SQLQA] REMOVE DATABASE [ProdData];

RESTORE DATABASE [ProdData]
        FROM DISK=N'\\nas\Backup\ProdData.bak'
        WITH FILE=1, NOUNLOAD, REPLACE, STATS=5,
            MOVE N'ProdData' TO N'S:\Data\ProdData.mdf',
            MOVE N'ProdData_log' TO N'T:\Log\ProdData_Log.LDF';

-- Missing a step here to physically remove the old database from the secondary server, at this point in "Recovery pending". If the old database is still there, the AUTOMATIC SEEDING will not work, nor the manual steps of adding a database to AlwaysOn for that matter.

ALTER AVAILABILITY GROUP [SQLQA] ADD DATABASE [ProdData];

-- Run custom maintenance/cleanup SQL code here

The big question is: what are my options to get rid of the old secondary target database?

Alternatively, anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Thanks in advance.

what are my options to get rid of the old secondary target database?

You can create linked server and use Execute At LinkedServer. This is what I am doing in my current scripts when I have to refresh AG env in Dev / QA from PROD.

You can use powershell, etc .. but Execute At LinkedServer just works fine for my needs.

  anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Refresh scripts to downstream environments are specific to company environment esp. the post restore process like desensitizing or masking data, etc. So, I would say, invest some engineering time and make the process repeatable that will save you a lot of time in future.


When to use XML data type I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the database.

As far as I can see I have 3 options:

Add a column in the table for each extra value
Add a linked table which references the original table and has records only where we need to store a value
Use the XML data type in the original table and store all of the values in this.

Are there any other options that I've not considered?

I'm trying work out the pros and cons of each method. As far as I can tell 1 would be the easiest and 2 would take the least amount of space but I'm struggling to find many resources for 3.

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario.

  The SQL Server Database Engine uses
  the SPARSE keyword in a column
  definition to optimize the storage of
  values in that column. Therefore, when
  the column value is NULL for any row
  in the table, the value requires no
  storage.

I can't imagine an XML solution performing well in this scenario, it will have a huge overhead of redundant metadata and will be slow to query.


CTE where to place the where clause to filter rows sooner (in postgresql)? I have function with CTE which looks like this:

function param(p_userId, p_templateId)

    with a(select...),
         b (select..),
         c (insert..returning..),
         d (update..returning..),
         ...k(select..)
    insert ...
    select * from k
    where  userId = p_userId
    and    templateId = p_templateId

I'm able to put the where clause right at the begining in the a CTE. If I place the condition at the end like I have it now, will it filtered the rows out at the earliest or at the latest time? What is faster?

Basically what I do is, in the CTE's which appear sooner, like the a or b, (rather then k), I'm prefetching data or precalculating columns in order to use them later in the following CTEs.

Hope this question is still not too vague.

CTEs pose as optimization fences in Postgres.

Each CTE is planned, executed and materialized separately. The predicates of a later CTE are not visible in earlier CTEs. Place conditions to eliminate rows as early as possible (a or b in your example).

Or rewrite with subqueries to allow more aggressive optimizations in Postgres. (But you should still eliminate irrelevant rows as early as possible, optimization only goes so far ...)

Details in Craig's blog post:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/


How to select records by nth minimum/maximum value of date column in postgresql? I have a table as follow:

create table x(id int, work date);

this table has the following records:

insert into x values(1,'2017-01-01'),(2,'2017-01-01'),(3,'2017-02-02'),(4,'2017-01-15'),(5,'2017-01-15');

Now, I want to select the records with first, second and third date values.
My desired output would be as follows:
If I want to select record with first date value then the output would be:

id      work
1     2017-01-01

If I select record with second date value then the output would be:

id      work
 4    2017-01-15

Any help is appreciated in advanced.

Another way:

SELECT *
FROM x
WHERE work = (SELECT DISTINCT work FROM x ORDER BY 1 OFFSET 1 LIMIT 1)
ORDER BY id
LIMIT 1;

Or more simpliest:

SELECT DISTINCT on (work)
       work, id
FROM x
ORDER BY 1, 2
OFFSET 1
LIMIT 1;

You can choose num of element by changing offset.


Galera multi-nodes Master-Master issue with READ right after INSERT I have a multi-nodes Galera setup which is master-master. An API call to a web server A could write data to either database 1, 2, 3 or 4 (we are not using HaProxy). Another API call right after that could hit web server A again. However, the second API call couldn't find the record created in the previous request.

Request 1: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Writes a record into a table with PDO transaction
Request 2: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Tried to get the record created in Request 1 but it's not available.

It's worth noticing that:

Both requests were made asynchronously.
The record eventually showed up in all nodes (manually checked, so, not sure if it wasn't there when the second request was made)

What am I dealing with here, guys?

This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.

What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."

Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/

You will need to set the "wsrep_sync_wait" session variable.


Handling partial backups from many different databases I've got an issue and I'm looking for best practice advice / whatever I can do to make this work.

I need to come up with a way to automate getting a backup from many offsite databases of unknown name, and there is one table that I cannot back up. From there then merging the data from all these backups into a single database and append a column stating where it is from.

The remote databases will all be running SQL 2016 standard, or sql 2012 Enterprise, or newer. They will not have SSIS. The local database server is running SQL 2016 Standard and can have SSIS.

After some looking around, my plan was to separate the one table I cannot back up into it's own filegroup. Then from there backing up the primary filegroup only, getting that sent to us, and then using SSIS or a service monitoring a folder for new backups. From there restoring the backup to a DB of name I can pre-set, to act as a staging area so I can merge the data as needed.

The reason for wanting the backup to go into a DB name I set here and not necessarily what is at the remote site is due to the fact that I can't guarantee what they name the databases and I'm worried about a conflict of a database we have on the server that will be handling these backups.

The issue I found with that method is that I can't seem to restore only the primary filegroup, it either complains about the name being different(even if I have with replace), or even if the name is the same it will complain that it did not back up a file from the second filegroup.  I get this error even if I include the "REPLACE" keyword: "The backup set holds a backup of a database other than the existing 'TestImport' database."

I have the ability to change the methodology as much as I need to during this stage, but receiving backups from an unknown number of databases of unknown name, and automating that into a centralized location are the items that are set. I'm open to any ideas that anybody has.

In order to do a piecemeal restore of only the primary filegroup, you need to ensure the following conditions are met:

There can be no pre-existing target for the database restore statement.  i.e. you must DROP any existing database before you run the piecemeal restore.  This applies even if you use the REPLACE keyword in the RESTORE DATABASE statement.
Use a MOVE clause to specify the storage location of the primary filegroup for the database you're restoring.  Specifying the location allows you to ensure you don't try overwriting some other database file, which would result in an error during the restore.

I would restore the database temporarily, copy the data from the restored database into a permanent database, then drop the restored database.  From the details in your question, there is no need to keep the restored database attached to the instance once you've copied the data into the permanent location.

I wrote a blog post showing the problem with performing a piecemeal restore onto an existing database, and how to rectify the problem at SQLServerScience.com


Replicate/Mirror SQL Server schema to slave databases I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database.

MASTERDB will be always empty, as I only need the schema. No data is going to be replicated/mirrored.

The scenario:

I create a new table on MASTERDB and I want that table to be created on those 10 tables automatically
If I change a field on MASTERDB, replicate the changes for all the other databases

I have looked at Mirroring and Replication, but I could not determine if these will do what I want.

Thanks in advance

Neither mirroring nor replicaiton are appropiate here. Mirroring creates identical physical copies of the database. Replication is good at replicating data, but falls short exactly at replicating schema changes, so you would use the very wrong tool for the job.

What you describe is a a problem of change control, not replicaiton. You should approach it as such. My favourite approach is described in Version Control and your Database. Brent Ozar PLF's last video also addresses the same problem: Coping with Change Control Video.


AD groups and ms SQL security: is this default behavior? I have a question regarding security in MS SQL Server (right now, I'm trying this on a SQL 2016).

Scenario:
User AD\testuser is in both AD security groups (AD\testgroup1 and AD\testgroup2).

A new database testDB is created.
A new table Testtable is created in testDB.
Login is created for AD\testgroup1
User AD\testgroup1 is created in database testDB
User AD\testgroup1 have select permission on table testTable.

We log in as AD\testuser, user only has select on TestTable --> as expected

Login is created for AD\testgroup2
User AD\testgroup2 is created in database testDB
User AD\testgroup2 granted for select, insert, update on table testTable.

We log in as testuser, user has select, insert, update on TestTable  --> as expected

Login AD\testgroup2 is removed from server (so AD\testgroup2 is still a use rin the database, but login is removed)

We log in as testuser, user has select, insert, update (we were under the impression this should only be select)

Access for user AD\testgroup2 is revoked in database testDB

We log in as testuser, user has select, insert, update (we were under the impression this should only be select)

User AD\testgroup2 is dropped in database testDB. When we test with same user, user only has select --> as expected

Why do we still get the highest permissions, even when AD group is removed from logins, and disabled in DB users?

Why do we still get the highest permissions, even when AD group is
  removed from logins, and disabled in DB users?

This is because testuser is a member of both AD\testgroup1 and AD\testgroup2 and both those groups exist in the database with object permissions, resulting in cumulative object permissions for the testuser role member.

There is no requirement that a login exists for a Windows group database user; one can add a Windows group database user and grant permissions to that user regardless of whether a login for the Windows group exists. Permissions granted to groups will be honored for role members even if a corresponding login doesn't exist, as you've observed.

Without an individual login for testuser, the user must be a member of at least one Windows group with a login to connect to the server. This was AD\testgroup1 in your example after you dropped the AD\testgroup2 login. But the user still had object permisssions granted to AD\testgroup2 until you dropped the user.


Best practice for archiving old data? We are migrating 3 slightly different SQL databases into one (SQL Server 2008R2). We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution? taking backups for it and test it in another serve prior to do this of course.

I’m not sure what best practice is for making archives, but suspect that making SQL server backups and placing on tape may be a solution.  Do you know if SQL Server backups are compressed and can be saved to tape?  There may be better solutions than tape these days, but I think tape is still the standard.

Thanks for any advice

We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution?

Instead of making database OFFLINE, make the database "READ ONLY" using :

ALTER DATABASE database_name SET READ_ONLY

And monitor what apps/ users are still connecting using server side trace.

  Do you know if SQL Server backups are compressed and can be saved to tape?

Yes, backup compression is available in sql server 2008 R2 standard edition and up.

Enable backup compression :

EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE ;

When taking backups, use BACKUP DATABASE ... with COMPRESSION.

You can save the backups to tape. Thats what we do in our company as well for data retention (We have to retain data for 10+ years). Alternatively, you can use Windows Azure Blob Storage Service.

Make sure that the archive bit is set (attribute 'A' on the backup file).


TPC-DS query generator does not generate all queries I have downloaded the DSGEN tool from the TPC-DS web site and already generated the tables and loaded the data into MS SQL server.

I am using the following command to generate the SQL statements(on windows):

dsqgen /input ../query_templates/templates.lst /directory ../query_templates /dialect sqlserver /scale 1

The command gives a single query_0.sql file as output and it has only 22 queries out of 99 queries. Also in the command prompt, an error is shown as given below:

RUNTIME ERROR: Distribution over-run/under-run
Check distribution definitions and usage for cities
index=-1, length=1000

Can anybody help?

Try the -VERBOSE option to see if there are additional information generated by dsqgen.
Also, the version of templates downloaded from tpc.org are missing some DEFINES.


Connecting SQL Server 2008 R2 to SQL Server 2000 Initially, my desktop has been installed with SQL Server 2000 Standard Edition SQL Client. Recently, I installed SQL Server 2008 R2 client and server instance. I'm using SQL Server 2008 Management Studio to replace the old SQL Server 2000 Query Analyzer and Enterprise Manager.

Yesterday I try to install SQL Server 2000 instance server but I cannot connect because when I register the server instant on Entrprise Manager I get a message that I should use the SQL Server Management Studio or SQL Server Management Objects (SMO). Where is my SQL Server 2000 instance?

Running SQL Server Management Studio on SQL Server 2000 is not really as supported option. Your best bet is to either upgrade the 2000 instance or to use SQLCMD (which should still work) to manage the instance from the command line.

You might be able to connect to a SQL Server 2000 from management studio 2005 (if you can find the install media).


Database Designer's Block Aloha DBA's,

I've got major DB Designers-block and I can't wrap my head around the proper way to design the database to meet my web application requirements. This is strictly a DB design question and I thank anybody who can help me out with this.

I'm building this web application in ASP.NET MVC and using MSSQL 2012 as my data back-end, but that's probably irrelevant for this design question.

The application will be a training/course tracker of sorts that will allow any employee with an account to keep track of their mandatory training. Each course should be able to target all employees or specific groups (all, group a, group b, group c). These courses can be one-time training or recurring (weekly, monthly, annually, etc...). Most of the courses will be online that provide certifications upon completion, but we also have on-site training that only requires acknowledgement that you were there, kind of like a check-in. For online training, users will upload their certificate and fill in a DateCompleted field.

So now that the description of the requirements is out of the way, I can discuss my major design areas that are giving me problems.

My current database schema looks like the following:

The problem with this schema is that I don't think it's flexible enough for me to be able to properly handle the requirements and I'm not sure how or if I should set up a training plan of sorts (e.g. add courses to a training plan table, then assign the training plan to a user?). And then there's handling offsite vs onsite, and the recurring courses that require re-certification at a specific interval. I'm not sure where I should be storing this...

Again, thank you for any help you can provide. I'm very appreciative. If there's anything else you need to know about my requirements, please let me know.

Mahalo!

My knee-jerk reaction would be to add a CourseSchedule table between Course and PersonTranscript. The same course could obviously be offered at different times and places, with different instructors, and maybe some sessions are online vs. on-site, etc. and you need a way to separate those properties from those that are universal for the whole Course.

Then going back to the idea of adding training plans, you could have a PersonTrainingPlan table that joins Person to Course directly (they're just planning which courses to take, not actually scheduling them), whereas PersonTranscript (and possibly a PersonSchedule table if the application warrants) joins a Person to a specific instance of a CourseSchedule.


Similar Products with bitwise I have the following tables.

So one product can have one category, one category can have n genres.(Products-> Genre Column data 6 ( 2 + 4), 132 (128 + 4) like that).

So my question is; i want to do find similar products for one product with using bitwise genres. Is that possible with sql query (or stored procedure)?

I never do something like that before, so i need help about it.

Thanks.

Yes, just use bitwise & operator:

select products.genres, genre.Id from
products inner join genre
on products.genres & genre.Id > 0
order by products.genres, genre.Id

SQL Fiddle


T-SQL code to see Windows Folder access Does anyone know how to check from T-SQL code in a stored procedure if a SQL user have read access to a WIN folder that is mapped to the SQL server? Thx Erik

SQL Server doesn't have (nor would you want it to have) native file system access built into Transact SQL. However, you can use the CLR to build a function that does specifically what you need to do and then call that CLR function from a T-SQL Stored Procedure.
See the Microsoft docs for CLR User Defined Functions for information on how to go about creating a CLR UDF.
Note that security is going to be an issue and you're going to have to make sure that your UDF isn't able to be exploited. Your SQL Agent service is going to need the necessary permissions to execute your UDF and your DBA may shoot down the idea because (a) they don't want the exposure of giving the SQL Agent service more permissions and (b) they may not like CLR UDFs as a matter of principle.
Depending on how dynamic your user permissions are, you might instead consider creating a scheduled process that reads the user permissions from your folders and records the results in a table that can be read using garden variety T-SQL when necessary.  The scheduled process could be a simple .NET Windows service or maybe a PowerShell script which is independent of your SQL Server, except that it writes results into a table. This lets you isolate the security contexts better and will probably be an easier sell to DBAs and your InfoSec folks.


Case-insensitive order while preserving the case of the returned strings I have noticed that when ordering, PostgreSQL (I'm currently working with 11 but I think the same applies to 10 and 9) is case sensitive. It returns upper-cased string before lower-cased one.
For instance, let's say I have the friends table with 3 records and the first_name column contains respectively  adam, Tony, Paul.

SELECT name
FROM friends
ORDER BY name

returns

[name]
 Paul
 Tony
 adam

To get them alphabetically ordered regardless of the case, I can use the lower function.

SELECT name
FROM friends
ORDER BY lower(name)

returns

[name]
 adam
 Paul
 Tony

Now I need to join the friends table with the cities table in order to get only the friends associated with a city while keeping the friends alphabetically ordered and without changing the case of their name .

The following query is valid

SELECT DISTINCT lower(friends.name)
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

But the returned names are all lowercase.
However, the query below

SELECT DISTINCT friends.name
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

returns the error

  ORDER BY expressions must appear in select list.

What is the simplest way to apply a case-insensitive order while preserving the case of the returned strings?

The solution is simple: don't use a join. This also removes the need for a DISTINCT.

select f.name
from friends f
where exists (select *
              from cities c
              where f.city_id = c.id)
order by lower(f.name);

Online example: https://rextester.com/SNA62411


Possible to delete a backup db file while it is being restored? I currently a have a db backup, which is very big. So big that I can't restore my db on the same drive.

Is it possible to delete the backup while it is being restored? Such that the new db takes up the space which the backup file was taking?

My backup is currently on our prod server, and there is a link between our test and prod environments.  Could I somehow expose that? Is it possible to restore my db over the net?

Yes, you can restore over the net. RESTORE DATABASE .. FROM FILE = '\server\share...'. I.e., an UNC path. Note that the service account for the SQL Server service need permissions on that share.


ETL: extraction strategy for 200 source databases What is the best extraction strategy for approximately 200 SQL Server 2005 source databases (same schema) for a daily load into a staging area in preparation for data warehouse cleansing, de-duplication, and transformations?

So far I have envisioned the following possibilities:

Transactional replication: create 200 SQL Server 2008 R2 subscribers pulling data from their respective publishers in 2005. Enable Change Data Capture on the required tables in the subscriber and the shadow tables in order to perform incremental loads into our staging database.
Rowversion: add a rowversion column on each required source table and use that in conjunction with an SSIS process to extract the change data to the staging database directly.
BCP files: create an automated task to do a nightly dump of BCP files from all source tables. Use SSIS to load these tables into the staging database as part of a full load (as opposed to incremental).

Additional thoughts:

I am nervous about the administrative and hardware overhead required to support an entirely new transactional replication topology on 200 databases.
The total combined size of the databases is about 100GB. But a majority of that is part of the transaction logs and other tables which won't be used in any fact or dimension. In other words, the BCP files won't be enormous which is why I'm considering a full extraction strategy even though everything I've read recommends against it.
I'm open to suggestions, documents, etc.

If you have 200 identical sources then you can parameterise a SSIS package with the data source and kick off nultiple threads.  These can be controlled within the package by a foreach loop or from an external source that kicks off the extractors with a parameter.

You could consider a full load for relatively small dimensional sources and an incremental load for transactional data.  This would require you to have persistent dimensions, but this is fairly straightforward to do with MERGE operations, or a pre-load area and dimension handler if you need slowly-changing dimensions.

You may wish to consider giving each source its own staging area (maybe a schema for each source in the staging database).  This eliminates locking issues on the staging tables.
Build a set of views over the staging tables (essentially just set of unions that correspond to each of the source tables) that includes data source information.  These can be generated fairly easily, so you don't have to manually cut and paste 200 different queries into the union.  Once you've staged the data then ETL process can read the whole lot from the view.

This allows the ETL to run in one hit, although you will have to come up with a strategy to deal with extract failures from individual systems.  For this, you might want to look into an architecture that deals with late arriving data gracefully, so you can catch up individual feeds that had transient issues.

BCP

For 200 simple extracts, BCP is probably a good way to go.  The sources are all identical, so the BCP files will be the same across sources.  You can build a load controller with SSIS.  Getting multiple threads to read the top off a common list would require you to implement synchronised access to the list.  The SSIS process has a bunch of loops running
in parallel in a sequence container that pop the next item, execute it and update the corresponding status.

Implementing the 'next' function uses a sproc running in a serializable transaction that pops the 'next' eligible source off the list and marks it as 'in progress' within the transaction.  This is a 'table as queue' problem, but you don't have to implement synchronised inserts - a whole batch can be pushed into the table at the start of the run.

Structure the individual extract process so that it tries once or twice again if the first attempt fails.  This will mitigate a lot of failures caused by transient errors.  Fail the task if it fails twice, and structure the ETL so it is resilient to individual extraction failures.

Incremental loads

An incremental loader is probably not worth bothering for dimension tables unless you have a really big dimension that shows real performance issues.  For the fact table data sources it probably is worth it.  If you can add a row version to the application table with a timestamp column or some such, you can pick up stuff that's new.  However, you will need to track this locally to record the last timestamp.  If there is an insert or update date on the data you may be able to use that instead.

Full Loads

What could possibly go wrong?

200 processes kicking off to do a full load places a load spike on the network and possibly the staging database.  This could lead to all sorts of transient issues like timeouts.  For small dimension tables it's probably not such a big issue.  However for 100GB there are quite a wide variety of issues - WAN saturation, locking (although the right staging architecture will mitigate that), availability of sources.  The longer the extract process has to run the bigger influence environmental factors have on the reliability of the process.

There are quite a lot of imponderables here, so YMMV.  I'd suggest an incremental load for
the larger tables if possible.


SQL Cross Database Views Security Cross Database Views:

Hi,
We have two databases

Database A: Contains dbo.Customerid table,

Database B: Contains dbo.Customerdescription table,

Database Reporting: has a schema for consultant vendors (vdr), so they can join, but not see security information, (SSN and driverlicenses, etc)

I am seeing a lot of information: What is the optimal, secure way to give access to the consultants for cross-database views?

I am seeing error messages,
"The SELECT permission was denied on the object DatabaseA.dbo.customer, etc"

create table DatabaseA.dbo.Customer
(
       Customerid int primary key,
       Customersalescode varchar(25),
       Membersince datetime
)
create table DatabaseB.dbo.CustomerDescription
(
       CustomerId int primary key,
       Firstname varchar(255),
       LastName varchar(255),
       SSN int,
       Driverlicensenumber varchar(30)
 )

create view vdr.CustomerReport
as
select
    cust.CustomerId,
    cust.Membersince,
    cds.FirstName,
    cds.LastName
from DatabaseA.dbo.Customer cust
inner join DatabaseB.dbo.Customerdescription cds
    on cust.Customerid = cds.customerid

Unless DB_CHAINING is set to true for all 3 databases, you should give the SELECT permissions on the underlying tables.

In case you set DB_CHAINING to true, all 3 owners of these databases are the same and all 3 objects have the same owner (for now it's not true in your design because your tables are owned by dbo and view is owned by vdr) permissions on the underlying tables will not be checked due to Ownership Chaining

To make ownership chaining work in your case you should do the following:

Run:

use Reporting;
go
alter authorization on schema::vdr to dbo;`

set db_chaining to true on all 3 databases:

use master;
go
alter database Reporting set db_chaining on;
alter database DatabaseA set db_chaining on;
alter database DatabaseB set db_chaining on;

check owners of all 3 databases and if it's not the same change it to the same login using

alter authorization on database::yourDB to yourLogin


Update via C# EF Core or SQL Our work involves updating products, we have a large table of products, and the prices and other pertinent information gets updated hourly. Let’s say it’s an amazon store, and we are talking of amazon products, and we have to update the selling price, buy box price etc. we pull the information hourly from amazon into our program and update the data in database.
My workflow is, pull all the products from the database into the program, (we use C# and EF Core), update the relevant products, and send back the updates to the database.
This way has the penalty of reading a lot of info from the database into the program, but I feel this is very efficient, because EF Core has change detection, so even if I assign all products with the price of the incoming products, if there is no change EF core will not change anything, it will only generate an update statement for those products which have changed information.
Also, it does not make large update statements, it generates small, targeted update statements, like
update products, set BuyBoxPrice = 12.23 where productid = 23345.
I am working with a very gifted developer, who is very natural with SQL, he argues that this way is wrong, I should rather put all the incoming info in a temp table named #products, and send it into the database, and then run a stored procedure which should do like this,
update products, set BuyBoxPrice = #products.buyboxprice from products inner join #products on products.produtid = #products.productid.
So this approach avoids having to make large reads from the database.
I am not so experienced, my question is, do reads create locks or degrade the database performance, probably yes?
Now here are the reasons I am not comfortable with his approach.
It creates a lot of unnecessary updates, which is very wasteful in my eyes, since only 25 percent of the information changes, so why update all columns.
My colleague counters, that I can remedy that by adding a where statement, like
update products where products.buyboxprice <> #products.buyboxprice
I don’t think that this reduces the penalty u pay, I think its still the same effect.
Another major worry is that large updates create locks, for that alone it should be avoided. Now of course I can break up the updates into chunks of less than 3000 etc.
Third point, when SQL has tummy trouble, it throws symptoms all over, and strange things start to happen, customers call in yelling and boss gets angry, and I have very little visibility of what’s going on, but in C# whenever something crashed is right there clear for me.
So my question is, who is right, is it more performant to do the updates via reads and EF core, or via SQL

Doing relational things will ways be fastest in the context of the database. Even though Entity Framework does things efficiently, in an apples to apples comparison, it'll always have slightly more overhead.
That being said, the big performance difference comes in with how you implement a database focussed solution or a C# focussed solution. I won't comment on your colleague's recommended alternative as that seems a little bit outside of what I'd suggest. I think there's probably better solutions, situation depending, such as using EF Core's DBContext.Find() methods to apply WHERE predicates on the primary key values, but your post asks a lot of important questions that require a lot more thought to respond to, so I'll update my answer later when I have more time.
One quick one though is yes reads do cause locks against writes in a table, and pulling in the whole table into a consumer like a C# application to only update a few records is usually not performant. Rather you should use WHERE predicates on your data pull to only access the rows you need for the lifetime of the current context of the app. (Assuming your tables are indexed appropriately.)

To address the points you made / other questions you asked:

"rather put all the incoming info in a temp table named #products": While using a temp table to temporarily hold the primary keys of the records you want to update is one way of doing things in a potentially efficient way, it sounds a little roundabout. If you already have the primary keys (IDs) of the records you want to update, then you can just pass in those IDs to the stored procedure (via a couple different options for the parameter's data type) or even better yet just write the update statement itself with those IDs and execute it. (Just a couple of ideas among many other solutions). Again if you want a pure C# solution, you should be able to use EF Core's DBContext.Find() method to only pull in the rows that need to change, and then update them. (Probably the best middleground solution.)

"do reads create locks or degrade the database performance?": Yes they can affect performance of the server by causing resource contention if you are frequently reading a lot of data. Reading from a table also acquires locks on that table that can block write queries, and cause contention. See the afore-linked DBA.StackExchange answer for more in depth information on locking.

"It creates a lot of unnecessary updates, which is very wasteful in my eyes, since only 25 percent of the information changes, so why update all columns.": I don't fully understand your statement here, not all columns are being updated based on the SQL solution your colleague suggested. And assuming he means only send the IDs of the records that need to be updated in a temp table, then only the exact number of rows that need to be updated are being updated.

"My colleague counters, that I can remedy that by adding a where statement...": This indicates to me that your previous statement is under the assumption all the rows should always be passed into the temp table. As per my previous response, you should only pass in the rows that need to be updated.

"I don’t think that this reduces the penalty u pay, I think its still the same effect.": The addition of the WHERE statement your colleague suggested is unnecessary if your temp table only contains the IDs of the rows that need to be updated. (If your temp table did contain every row in the table, then it's hard to say if it would be more efficient without seeing the execution plan of the query. Possibly it would still be more efficient than the process in EF Core of pulling in all the records into the application and then updating them.) Also if you went a different route that didn't use a temp table, and involved filtering in SQL that was logically equivalent to what you wanted to accomplish (e.g. update products set buyboxprice = 'someValue' where someOtherField = 'someOtherValue') then that filtering would be more efficient (assuming you have proper indexing) than your current process all in EF Core.

"Another major worry is that large updates create locks": Apples to apples, if you have the same query plan in both cases, then the same amount of locking is going to occur due to your updates. (Though the amount of rows that need to be updated at one time can affect the query plan.) You can play around with chunking (batches) but generally won't really be necessary until you're updating about 100,000+ records at a time. But batching is a design choice irrespective of C# and SQL, you can accomplish this in either place, so it's a moot point.

"Third point, when SQL has tummy trouble, it throws symptoms all over...I have very little visibility of what’s going on, but in C# whenever something crashed is right there clear for me.": This is not an issue with SQL rather inexperience with the framework and tools available (I don't mean this negatively, just a matter of fact, as someone who was in the same shoes a few years ago). There was likely a time when you didn't know how to use the debugger in Visual Studio right?...and breakpoints...same for using try/catch? Up until you became proficient with the tools for C#, you probably had a harder time debugging crashes and bugs. The same is true for SQL, furthermore performance issues != crashing and bugs. They require a different kind of mindset and process for debugging. If you had a non-database related performance issue in your C# application (for example if your app was slow when saving a file to disk), you wouldn't have as much clarity debugging the issue as a crash using the tools you're familiar with debugging crashes. There are many good tools and scripts available for debugging performance issues in SQL Server, please see the list below:

Brent Ozar's sp_Blitz
Get Running Queries - I fine tuned this query (I forget where I originally found it) and find it immensely helpful for on the spot debugging performance issues as it tells you all running queries on the server, where they originated from, how long they've been running and wait stats and blocking info. (I have it saved in a stored procedure which can be scheduled to routinely run and log the results to a table.)
Query Store
Extended Events
SQL Profiler (Deprecated but worth noting.)
Activity Monitor
Microsoft's Docs on Performance Monitoring
Helpful Queries from PASS
SentryOne's SQL Sentry
RedGate's SQL Monitor

In summary, as a C# developer for 8 years, and a DBA for about 3 years, it has been my experience that managing and manipulating data is most efficient when structured well with good processes that maximize the relational capabilities of the database (as opposed to trying to accomplish the same task in a functional language).


mysqld got signal 11 - it's my fault but how do I fix it? I was copying the database tables from our production server to my dev machine, and in the middle of the transfer I found out users were being locked out of the system because the select had locked a table. So...in my hurry to get the process to stop, I stupidly killed the mysqld process on my dev server.  Now, when I try to restart the service, I get the error below in my mysqld.log. I'm assuming it's because the table file for the table I was writing to is corrupted, but how do I go about getting the service to start again?  I did try moving the table files out of the database directory, but that didn't work (and yes, I put them back after!

141223 15:50:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141223 15:50:29 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.
141223 15:50:29  InnoDB: Initializing buffer pool, size = 8.0M
141223 15:50:29  InnoDB: Completed initialization of buffer pool
141223 15:50:29  InnoDB: Started; log sequence number 1 1553165392
20:50:29 UTC - mysqld got signal 11141223 15:50:29 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
141223 15:54:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
141223 15:54:03 [Warning] '--default-character-set' is deprecated and will be removed in a future release. Please use '--character-set-server' instead.
141223 15:54:03  InnoDB: Initializing buffer pool, size = 8.0M
141223 15:54:03  InnoDB: Completed initialization of buffer pool
141223 15:54:03  InnoDB: Started; log sequence number 1 1553165392
20:54:03 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337742 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84c6d3e]
/usr/libexec/mysqld(handle_fatal_signal+0x484) [0x82ec8b4]
[0x9bd400]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
141223 15:54:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

After a bunch more googling, etc., I finally came upon the solution: http://notesonit.blogspot.hk/2013/05/innodb-unable-to-lock-ibdata1-error-11.html


After Inserting , Update / Insert into another table in pl/sql trigger I am working on PL/SQL trigger i have two table

 TAG Table

SR.NO    ID    TAG    BRAND_ID
------  ----  -----  ----------
  1      10    ABC       99

MY_TAGS Table

 SR_NO    TAG    COUNT   BRAND_ID
--------   ---   ------   -------
   1      ABC       1      99

I need a trigger in such a way that

If tag ABC is inserted  with BRAND_ID=99 one more times in TAG table then i need to increase the count of ABC to 2 in MY_TAGS table
If new values inserted other than ABC for 99 then i need to insert new row in MY_TAG table

How can I handle this situation using trigger. I am new to PL /SQL any help would be great.

I tried like this , but it will always execute Insert statement (i.e else part )

 CREATE or replace TRIGGER trig1
    AFTER INSERT ON TAG
    REFERENCING NEW AS newRow OLD AS oldRow
    FOR EACH ROW
    WHEN (newRow.ID >=0)
    BEGIN
    IF(:newRow.TAG=:oldRow.TAG AND :newRow.BRAND_ID=:oldRow.BRAND_ID) THEN
       UPDATE MY_TAGS SET COUNT=(select max(counter) from MY_TAG)+1) where brand_id=newRow.BRAND_ID and tag=:newRow.TAG;
    ELSE
       INSERT INTO MY_TAGS VALUES((select max(SR_NO) from MY_TAGS)+1,:newRow.TAG,1,:newRow.BRAND_ID);
    END IF;
    END trig1;

Instead of materializing the count, which bears the risk of inconsistencies, I recommend you drop the my_tags table and create a view which uses aggregation to get the count.

DROP TABLE my_tags;

CREATE VIEW my_tags
AS
SELECT sr_no,
       tag,
       brand_id,
       count(*)
       FROM tag
       GROUP BY sr_no,
                tag,
                brand_id;

Like that you don't need any trigger, the count is always accurately calculated.


Tables accessed during last period I want to check which tables have been updated during a given period, for instance in descending order of access time per table.

How can I get that for PostgreSQL?

You can get some information about the last change to a table with xmin, eg:

select max(xmin::text::bigint) from t;

But you need to be aware of many caveats including modulo and wraparound and frozen xids.

testbed:

set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create or replace function f(p_schema in text, p_table in text)
                  returns integer language plpgsql immutable as $$
declare
  n integer;
begin
  execute 'select max(xmin::text::bigint) from '||p_schema||'.'||p_table into n;
  return n;
end;$$;
--
create table foo as select generate_series(1, 100) as id;
create table bar as select generate_series(1, 100) as id;
create table baz as select generate_series(1, 100) as id;
--

method:

select table_name, f(table_schema, table_name)
from information_schema.tables
where table_schema='stack'
order by 2 desc;
/*
 table_name |   f
------------+--------
 baz        | 784657
 bar        | 784656
 foo        | 784655
*/
--
update foo set id=id+1 where id=100;
--
select table_name, f(table_schema, table_name)
from information_schema.tables
where table_schema='stack'
order by 2 desc;
/*
 table_name |   f
------------+--------
 foo        | 784658
 baz        | 784657
 bar        | 784656
*/

cleanup:

drop schema stack cascade;
set role dba;
drop role stack;


Why can a non-clustered index not be dropped using online=on option When attempting to drop a non-clustered index using the online option such as DROP INDEX [IX_MYINDEX] ON [dbo].[myTable] WITH ( ONLINE = ON ), I receive the following error message.

Msg 3745, Level 16, State 1, Line 16
Only a clustered index can be dropped online.

The SQL Server documentation clearly states:

  The ONLINE option can only be specified when you drop clustered
  indexes.

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

But can someone please explain to me why this is the case?  In my experience, you would be much more likely to drop a non-clustered index than a clustered index, since the clustered index in most cases is also your primary key.

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does.

Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online alternative.


This failure occured while attempting to connect to the Principle server One of our clients recently came to us with a problem he's facing while working with our application. The error in the log file is the next:

  System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  This failure occured while attempting to connect to the Principle server.
  System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

As far as I know, that client has set up mirroring between two database servers and he's currently working on the fail-over server (They do that on purpose each now and then).

I am aware that I could use the CommandTimeout property in my code to increase the time before a timeout is reached.

The application is working normally, so it's connecting to the database way before this error happens. Also, connecting through SSMS is working.

Note that, due to security reasons on my client-side, I can't use a profiler to narrow down the problem to a specific query or procedure.

What I am actually concerned about is the error message in bold.

Does that error mean that the actual problem is a connection problem between the Main DB Server and the Fail-Over server or could it also be triggered by a query taking more than the default 30 seconds?

You mention two key things in your post:

Mirroring
Work is being performed against the failover-server

I suspect what is going on is actually expected behavior.  Mirroring connection strings define both a Server (e.g. the Principal Server) and a Failover_Partner (e.g. the Mirror Server).  When establishing a connection to a mirrored database, if a connection to the Principal Server cannot be attained, an attempt to connect to the Mirror Server is then performed.

Per Connect Clients to a Database Mirroring Session (SQL Server):

  If a connection attempt fails or the retry time expires before it succeeds, the data access provider tries the other partner. If a connection is not opened by this point, the provider alternately tries the initial and failover partner names, until a connection is opened or the login period times out.

What I suspect the error is showing is the failed attempt to connect to the Principal Server because the database is actually active on the Mirror Server.  The error is thrown to your logs and then a successful connection is established against the Mirror Server because that's where the database is open/active.

This is expected behavior in a mirror configuration, and if you're able to (eventually) connect to the database, that means mirroring is working.  Take note that once you failover the database back to the Principal server, this error should no longer occur because the initial connection attempt to the Principal Server will once again succeed.


MS Access Query summarizing 2 tables doesn't produce expected output I cannot successfully create a query in MS Access 2016 that correctly summarizes data from 2 tables with similar but not completely identical fields.

I have a fruit market. I buy each type of fruit once a season. I dispose of bad fruit multiple times each each season. I separately track purchases and disposal of bad fruit.

I wish to create a query that summarize the net fruit volume (purchased - bad) for each year and fruit.

Table of Purchased Fruit:

ID  CYear   Fruit   Purchased
3   2017    Apple   50
4   2018    Apple   30
5   2017    Pear    50
6   2018    Pear    60

Table of Bad Fruit

ID  CYear   CMonth  Fruit   Bad
2   2017    11      Apple   3
3   2017    12      Apple   20
4   2017    8       Pear    15
5   2018    2       Apple   10

This is my desired query output:

ID  CYear   Fruit   Purchased   Bad Net
1   2017    Apple   50          23  27
2   2017    Pear    50          15  35
3   2018    Apple   30          10  20
4   2018    Pear    60          0   60

The "Net" field is calculated from "Purchased - Bad".

I cannot create a query or union that matches the desired output. Most perplexing, because there is no record of "0" bad Pears in 2018, a query doesn't even select the 2018 Pear purchase.

Create the query which calculates the amount of bad fruits per year and save it assigning the name [Bad Fruit Year]:

SELECT CYear, Fruit, SUM(Bad) AS Bad
FROM [Bad Fruit]
GROUP BY CYear, Fruit;

Then create the query obtaining the data You need:

SELECT p.ID, p.CYear, p.Fruit, p.Purchased, NZ(b.Bad,0) AS Bad, (p.Purchased-NZ(b.Bad,0)) AS Net
FROM [Purchased Fruit] As p
LEFT JOIN [Bad Fruit Year] As b ON p.CYear=b.CYear AND p.Fruit=b.Fruit;

As a result You will have 2 queries (to clear the Queries tab of DB window You can make the first query hidden). The second one gives the result You need, it automatically calls the first one (You do not need to call/open it) while opening.

If You want You may to combine these queries into one:

SELECT p.ID, p.CYear, p.Fruit, p.Purchased, NZ(b.Bad,0) AS Bad, (p.Purchased-NZ(b.Bad,0)) AS Net
FROM [Purchased Fruit] As p
LEFT JOIN ( SELECT CYear, Fruit, SUM(Bad) AS Bad
            FROM [Bad Fruit]
            GROUP BY CYear, Fruit) AS b ON p.CYear=b.CYear AND p.Fruit=b.Fruit;

But its text maybe damaged by MS Access if You open it for view/edit after create and store (the problem is old and still irreparable).

PS. I assume there is NOT a situation when some fruit is present [Bad Fruit] table for some year while absent in [Purchased Fruit] for the same year.


Oracle Materialized View Overloads Database Summary: Materialized View across DB link eating up too many system resources
Question: Is there a way to throttle a materialized view refresh?

We have some fairly large tables that we copy from one oracle DB to several others as a cached backup if the network to the central database is down (or the central database is down). The data copy runs hourly or daily (depending on the data).  It's not mission critical or real-time.

Our first idea was materialized views on the receiving databases to  pull the data from the central database across a database link. If I remember right we couldn't do incremental refreshes because of the database links so the refreshes are full table refreshes.

We discovered when the materialized view refreshes run they eat up lots of system resources sometimes overloading the system and slowing critical stuff. (I'm a programmer not DBA so I'm not entirely sure what resource was constrained, but I think disk access, possibly memory.)

Is there any way to throttle a materialized view refresh so it won't do that?

Materialized views over a DB link most certainly can be set up as fast refreshes (i.e. only changes since the last refresh) as we use this approach ourselves.  However, what cannot be done are refresh on commit mviews over a DB link, so the mview will have to be on a refresh schedule (unless you simply want to refresh on demand, of course).

This works on Oracle Database Versions >= 10g (personally tested).  I can't vouch for < 10g versions since I started using Oracle Database in 10g.

We see very little database impact on both of our source DB's and destination DB's using this approach with roughly 30 mviews (most are medium size tables, a few million rows each) that refresh every 15 minutes.

A few notes/gotchas: The mview log has to be created on the source DB, not the destination DB (which is usually a point of confusion with this setup).

Create the DB link on the destination DB that connects to the source DB.  If the usercode that the DB link is using to connect to the source DB is different than the schema where the table/mview log table resides, then the DB link usercode on the source DB will have to be granted select privileges on both the source table and the source mview log table.

In addition, the mview log deletions don't move the HWM, so for large transactional tables, you may want to consider having a nightly process that locks the mview log table, then checks if the mview log table is empty, then truncate the mview log table in order to reset the HWM to keep fast refreshes as fast as possible.


sqlplus not working on opensuse take a look at this:

> cat .bash_profile
# .bash_profile

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

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
umask 022
#DISPLAY=:0.0
#export DISPLAY
TMP=/tmp
TMPDIR=/tmp

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_HOME

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/usr/local/bin
export PATH

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ORACLE_SID=SSAE
export ORACLE_SID

looks like working

~ # service dbora status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 24-APR-2012 10:01:40

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                09-APR-2012 13:19:59
Uptime                    14 days 20 hr. 41 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=t2libora1)(PORT=1521)))
Services Summary...
Service "xxxx" has 1 instance(s).
  Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxxDB" has 1 instance(s).
  Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxx_XPT" has 1 instance(s).
  Instance "xxxx", status READY, has 1 handler(s) for this service...
The command completed successfully

but cant connect

:~ # su oracle
>  sqlplus / as sysdba
bash: sqlplus: no se encontró la orden

I'm not a oracle guy ... any help I appreciate.. (sorry my english)

sqlplus is not in your path.

Use su - oracle instead of su oracle, as that will read the users ~/.bash* files & set the environment accordingly.


When does a port get assigned if SQL Server is set to TCP Dynamic Ports I'm trying to determine when a dynamic port is assigned to SQL Server.  If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or does it get assigned each time the instance is started.  Is it therefore possible that a new port will get assigned each time the instance is restarted?  I'm mostly curious about the 2008 through 2014 versions.

Is it therefore possible that a new port will get assigned each time the instance is restarted?

Every time you start the SQLServer Instance, it uses the port that is allocated.

When a dynamic port is assigned to sql server instance, if the port is used by another program, then SQL Server chooses another port at the time of restart i.e. A dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if its used by another program then SQL server will choose a new port.

I generally prefer to use static ports for my sql server installation.

Refer to my answer to a sort of similar question.


Log table that grows like flash I have my own app that get low traffic (like 100 ppl per day).

As part of my research I am 'logging' all the queries done to by the server in a table in the database. An example, if user enters home page it will register two queries for getting the data to display.

According to Google Analytics by average an user enters 7 pages per session, and in average I have 130 sessions per day. Assuming a bad design and think that in average a page does three queries would be a total 2730 queries per day.

Right now, my research does not have a deadline or finish point soon in the future and I am scare that at the end, all the data will be too much too process and study.

Facts:

Right now in six months of working, SELECT COUNT(*), Throws 1.360.000
rows and weights 5.56 GB.
I am a master student of computer science with emphasis of user behavior.
The server runs in a really low memory pc and there is not budget in the moment to changed it.
I cannot change the code as it could change the results.
I do not know anything about maintenance of a DB in a server and I just simply doing a service restart everyday.

The questions:

Is it possible to overload the server of data considering the poor excuse of pc that I called server?
Is it stupid to think to store the log in a way that every month will store it in a file and delete those rows from DB?
Am I right to be worried about the size of the database?

Right now we don't know the specification of your "Server" so we can't tell if space might become an issue.

Apart from that, the answers you are searching for are just a case of interpolating the available data along a time line.

Answering your questions:

From a (MySQL) database perspective: It depends. If the InnoDB page size parameter is set to 4KB then the maximum tablespace size is 16TB. For 8KB page size the tablespace can grow to 32TB. For more information on the limitations of the tables / tablespace / OS limitations visit these links:

15.8.1.7 Limits on InnoDB Tables (MySQL 8.0 documentation)
C.10.3 Limits on Table Size (MySQL 8.0 documentation)

No. Other than observing the limitations of the MySQL specifications. Others before you have stored more data for less interesting cases.
No. See above.

So space might become an issue depending on your "server" hardware.

Maintenance

As for maintaining your database, you might want to consider reading the following articles from the MySQL Documents site:

Chapter 7 Backup and Recovery

7.3 Example Backup and Recovery Strategy
7.4 Using mysqldump for Backups

Chapter 8 Optimization

8.5 Optimizing for InnoDB Tables

I guess the worst case would be if you were to lose all your data. Consider performing a simple mysqldump to have a backup of your data. Then consider implementing a backup strategy.


Locking order in cursors (Oracle) I wonder if the order in which records are locked is guaranteed when I use SELECT ... FOR UPDATE in cursors ?
For example,

DECLARE
CURSOR test_cursor IS
SELECT field1, field2
FROM table1
ORDER BY pk_field
FOR UPDATE OF field1;

When I open such a cursor, will it always lock records in the order of pk_field ?

From what I read so far order of locking is not guaranteed for regular SELECT ... ORDER BY pk_field FOR UPDATE , but I'm not sure if it's true for cursors. I'd expect it does lock in the order set in ORDER BY clause for cursors; however, I cannot find confirmation.

Thank you.

The order in which individual rows will be locked is undefined and certainly not something you should rely on since it will depend on the query plan.

In general, I would expect that the rows would not be locked in order-- the optimizer would generally expect it to be more efficient to access all the data unordered and then apply the ORDER BY.  If there was a composite index on pk_field, field1, field2, I could see the optimizer potentially choosing to do an ordered scan of the index and then locking each row in the table but that seems unlikely.


Why Model database in SQL Server is in FULL recovery mode Why Model database in SQL Server is in FULL recovery mode and other system databases are in simple recovery mode?

Why is the model database in SQL Server in FULL recovery mode and other system databases are in SIMPLE recovery mode?

I think you are asking why the model database is different from the other system databases.

New user databases are created with the same recovery model as the model database. The default recovery model for the model database depends on the edition of SQL Server you have installed.

From Choosing the Recovery Model for a Database:

  The simple recovery model is generally appropriate for a test or development database. However, for a production database, the best choice is typically the full recovery model, optionally, supplemented by the bulk-logged recovery model. However, the simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse.

Following this logic, editions targeted at test and development environments (SQL Server Express and LocalDB) set the recovery model of the model database to SIMPLE. Other editions set the recovery model of the model database to FULL.


How to merge multiple rows into one row with SQL? Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below:

Now, I want to 'merge' the rows according to the Year and Month value and to store them into another table, as shown below:

Note that, M1 means Mth = 1 (January), M2 means Mth = 2 (February), and so on. (The images are meant for description, you may ignore the accuracy of the figure.)

Question, how to do this in SQL Server?

This is a classic case of a pivot operation in SQL. The method working in, probably, most (if not all) SQL products, including SQL Server, is conditional aggregation:

SELECT
  UserID,
  Year,
  M1  = SUM(CASE Mth WHEN  1 THEN Value END),
  M2  = SUM(CASE Mth WHEN  2 THEN Value END),
  M3  = SUM(CASE Mth WHEN  3 THEN Value END),
  M4  = SUM(CASE Mth WHEN  4 THEN Value END),
  M5  = SUM(CASE Mth WHEN  5 THEN Value END),
  M6  = SUM(CASE Mth WHEN  6 THEN Value END),
  M7  = SUM(CASE Mth WHEN  7 THEN Value END),
  M8  = SUM(CASE Mth WHEN  8 THEN Value END),
  M9  = SUM(CASE Mth WHEN  9 THEN Value END),
  M10 = SUM(CASE Mth WHEN 10 THEN Value END),
  M11 = SUM(CASE Mth WHEN 11 THEN Value END),
  M12 = SUM(CASE Mth WHEN 12 THEN Value END)
FROM
  dbo.YourTable
GROUP BY
  UserID,
  Year
;

SQL Server also supports the PIVOT operator (since the 2005 version). I can see two distinct approaches using PIVOT here. One is probably more straightforward:

SELECT
  UserID,
  Year,
  M1  =  [1],
  M2  =  [2],
  M3  =  [3],
  M4  =  [4],
  M5  =  [5],
  M6  =  [6],
  M7  =  [7],
  M8  =  [8],
  M9  =  [9],
  M10 = [10],
  M11 = [11],
  M12 = [12]
FROM
  dbo.YourTable
PIVOT
  (
    SUM(Value)
    FOR Mth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
  ) AS p
;

Note that the above method assumes your table has no other columns apart from those shown in your example (UserID, Year, Mth, Value). If there are other columns, replace dbo.YourTable with a derived table pulling only those four columns:

  ...
FROM
  (
    SELECT
      UserID,
      Year,
      Mth,
      Value
    FROM
      dbo.YourTable
  ) AS s
PIVOT
  ...

The other, slightly less obvious, option would be to use a derived table to construct the final Mn columns and thus avoid the renaming in the main SELECT:

SELECT
  UserID,
  Year,
  M1,
  M2,
  M3,
  M4,
  M5,
  M6,
  M7,
  M8,
  M9,
  M10,
  M11,
  M12
FROM
  (
    SELECT
      UserID,
      Year,
      Mth = 'M' + CAST(Mth AS varchar(11)),
      Value
    FROM
      dbo.YourTable
  ) AS s
PIVOT
  (
    SUM(Value)
    FOR Mth IN (M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12)
  )
  UserID,
  Year
;

In this case you could also use just * in the main SELECT instead of the explicit column list.


What is the recommended way to join junction tables for efficient ordering/pagination? Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem.

Database: PostgreSQL 9.6

Simplified schema:

CREATE TABLE article (
  id bigint PRIMARY KEY,
  title text NOT NULL,
  score int NOT NULL
);
CREATE TABLE tag (
  id bigint PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE article_tag (
  article_id bigint NOT NULL REFERENCES article (id),
  tag_id bigint NOT NULL REFERENCES tag (id),
  PRIMARY KEY (article_id, tag_id)
);
CREATE INDEX ON article (score);

Production data info:

All tables are read/write. Low write volume, only a new record every couple minutes or so.

Approximate record counts:

~66K articles
~63K tags
~147K article_tags

Average of 5 tags per article.

Question: I want to create a view article_tags which includes an array of tags for every article record, can be ordered by article.score and paginated with or without additional filtering.

In my first attempt I was surprised to see that the query took ~350 ms to execute and wasn't using the indexes. In subsequent attempts I was able to get it down to ~5 ms but I don't understand what is going on. I would expect all these queries to take the same amount of time. What crucial concept am I missing here?

Attempts (SQL Fiddles):

multi-table joins (~350 ms), (~5 ms if ordered by article.id!) -- seemed like the most natural solution
subquery join (~300 ms) -- also seemed like a natural solution
limited subquery join (~5 ms) -- super awkward, can't be used for view
lateral join (~5 ms) -- is this really what I should be using? seems like a misuse of lateral
...something else?

Pagination

For pagination, LIMIT (and OFFSET) are simple, but typically inefficient tools for bigger tables. Your tests with LIMIT 10 only show the tip of the iceberg. Performance is going to degrade with a growing OFFSET, no matter which query you choose.

If you have no or little concurrent write access, the superior solution is a MATERIALIZED VIEW with an added row number, plus index on that. And all your queries select rows by row numbers.

Under concurrent write load, such a MV is outdated quickly (But a compromise like refreshing the MV CONCURRENTLY every N minutes may be acceptable).
LIMIT / OFFSET is not going to work properly at all since "the next page" is a moving target there, and LIMIT / OFFSET cannot cope with that. The best technique depends on undisclosed information.

Related:

Database engine with counting b-trees for efficient paging support
Optimize query with OFFSET on large table

Index

Your indexes generally look good. But your comment indicates that table tag has many rows. Typically, there is very little write load on a table like tag, which is perfect for index-only support. So add a multicolumn ("covering") index:

CREATE INDEX ON tag(id, name);

Related:

Can Postgres use an index-only scan for this query with joined tables?

Just the top N rows

If you don't actually need more pages (which isn't strictly "paging"), then any query style is good that reduces qualifying rows from article before retrieving details from the related tables (expensively). Your "limited subquery" (3.) and "lateral join" (4.) solutions are good. But you can do better:

Use an ARRAY constructor for the LATERAL variant:

SELECT a.id, a.title, a.score, tags.names
FROM   article a
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT t.name
      FROM   article_tag a_t
      JOIN   tag t ON t.id = a_t.tag_id
      WHERE  a_t.article_id = a.id
   -- ORDER  BY t.id  -- optionally sort array elements
      )
  ) AS tags(names) ON true
ORDER  BY a.score DESC
LIMIT  10;

The LATERAL subquery assembles tags for a single article_id at a time, so GROUP BY article_id is redundant, as well as the join condition ON tags.article_id = article.id, and a basic ARRAY constructor is cheaper than array_agg(tag.name) for the remaining simple case.

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Or use a lowly correlated subquery, typically even faster, yet:

SELECT a.id, a.title, a.score
     , ARRAY (
         SELECT t.name
         FROM   article_tag a_t
         JOIN   tag t ON t.id = a_t.tag_id
         WHERE  a_t.article_id = a.id
      -- ORDER  BY t.id  -- optionally sort array elements
      ) AS names
FROM   article a
ORDER  BY a.score DESC
LIMIT  10;

db<>fiddle here
SQL Fiddle