Alternative for taking the backup of SQL DB's on backup drive We just got new servers to take care of, regarding their backups, handed over by the company B who used to manage the backups, but they never did via any mant'ce plans or SQL jobs.They did that task through NBU client. Thus backups were never backed up on the drives on that respective SQL server.

Now, as per the security/audit findings we need to have backups on the drive.

But the issue is we never had any backup drive on those servers. Moreover many of the servers only have a single data drive.

Therefore we came up with a plan to back them over NW onto the server with ample amount of space as of now, till we get the backup drives installed on each server.

But again we came with a challenge of resources that is being utilized while copying / taking backups over the NW like CPU and Memory usage got high.

Can someone help with their expertise on what alternative can be used, because we are short of space as of now and adding a drive per our process takes a month.

Kindly suggest!

Thank you!

Speaking as someone who has taken over from multiple third parties who have done NetBackup database backups you can safely assume three things.

Many of your servers and individual databases have not been backed up. You would do best to confirm yourself through msdb checks.
The "tapes" being backed up to are probably not accessible, do not work, or otherwise don't exist.
You can probably find a lot of NetBackup failure notifications in ErrorLog which have been ignored for days, months, and years.

Regarding your specific situation backups are meant to be taken to network shares and backing up to the same server is wrong. What is going to happen when your server VM crashes or becomes corrupt? You've lost all your backups.

Whatever security / audit issues have come up are obviously wrong and you should be rallying against them. Or - print out your ironclad disclaimer and get your manager to sign it - because they're going to be fired down the line when they lose everything and you don't want this to destroy your career along with their poor decisions.

Best practice? A network share like you mentioned, and standardise on Ola Hallengren's scripts like others mentioned.

Don't be afraid to modify the setup script a little (or add your own to run afterwards) to set up the jobs the way you like them and to schedule them; you can also stagger them server by server if you like to spread the load. I wrote a corresponding script in PowerShell to help do the deployments (create a folder on each server to store the output text files; grant the service account access; create the network folder and grant the service or network account access; then deploy the jobs in the way we want them and with appropriate schedules).

You should also prioritise setting up your registered server list so that in the mean time you can run mass checks of msdb catalogs to make sure you haven't missed anything.

Network and CPU going high doesn't mean anything unless it impacts Production; the only important thing aside from gigabit network interfaces is that the network share is somewhere relatively close to whatever network segments you have. And the initial FULL backups will always be an issue, that's why they're scheduled for weekends, then daily DIFF will be fine.

Otherwise the only databases where this really becomes a problem are when they start going over 1TB (especially if it's much higher, 5TB, 20TB) and those need special attention.


Complex `UNIQUE INDEX` scenario - Games, teams, players, positions! I have a table

CREATE TABLE (game, team, player, position);

If I create a unique index on (game, player, position) I get a table where in the same game, the same player can't play the same position for both teams. That's useful, but it's still possible for someone to insert a row where the same player plays two different positions, one on either team.

Is it possible to write some sort of unique index or constraint that makes it impossible for the same player to play for both teams in a game?

You could use an EXCLUDE constraint:

create extension btree_gist ;
create table foo (game text, team text, player text, position text,
    exclude using gist (game with =, player with =, team with != ));

You will want to put the != column at the end of the list of columns, otherwise enforcing the constraint will be slower.

insert into foo values ('1', 'detroit', 'parish','catcher');

insert into foo values ('1', 'cubs', 'parish','LF');

ERROR:  conflicting key value violates exclusion constraint "foo_game_player_team_excl"


Standby controlfile error I am doing dataguard. I have manually done a backup for the standby database. It was working fine. Problem arose when I used the standby controlfile. On the primary, I have created a standby controlfile and transferred it to the standby.

scp test_sdy oracle@db3.oracle.com:/u03/app/oracle/oradata/TEST1/control01.ctl
cp /u03/app/oracle/oradata/TEST1/control01.ctl \
   /u03/app/oracle/flash_recover_area/TEST1/control02.ctl

The standby's init.ora contains:

*.control_files='/u03/app/oracle/oradata/TEST1/control01.ctl','/u03/app/oracle/flash_recovery_area/TEST1/control02.ctl'

When I start up the standby database using pfile, I get this error:

ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u03/app/oracle/oradata/TEST1_SDY/system01.dbf'

Did I do something wrong with the standby control file? How can I fix this?

I think you problem is in the parameters file of standby database, may be you have defined something as:

LOG_FILE_NAME_CONVERT='/u03/app/oracle/oradata/TEST1','/u03/app/oracle/oradata/TEST1_SDY'

This parameter change the location on dbfiles for standby version. Here a link of documentation:

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm#REFRN10098

If you remove this line in parameter file of standby database and restart should be work, with the same control file standby version created before.


Split CLOB into multiple VARCHAR2 I currently am working with some data that is being stored as a CLOB between 4000  and 6000 characters. I would like to perform the TRANSLATE function on it but I learned that it's not possible with a CLOB. So my thought is to split the CLOB into multiple VARCHAR2's, and perform the TRANSLATE operation on each part. Say I have a 6000 character CLOB. I started by using SUBSTR(COL,4000,1) and storing the first 4000 in a variable. However, I'm having trouble getting the characters between 4000 - 6000. How can I go about splitting this CLOB?

Look at DBMS_LOB.SUBSTR and pay close attention to the differing definition from the SUBSTR you are familiar with.


Need help with Double Entry DB Design I'm developing a simple double entry accounting system for managing the payments, charges, cars expenses, etc. for car rental companies. I'm done (Development and Database wise) with the Main operation system for managing cars, customers, contracts and so on and now I'm stuck in the accounting part.I have come up with the below DB design for managing the accounting part but I'm still not 100% sure if I can use it and consider it as a go.

Now in the t_Transaction table I have a Credit Account and Debit Account with 1 Amount field. My question is what is the best approach for managing the amount part?
1- Is it better to be logged into 1 field in the database? but then how can I distinguish weather the amount is a debit or a credit while generating reports.
2- Or should I have 2 separate fields (a. Debit Amount) and (b. Credit Amount) and then based on the transaction 1 field will have the actual amount while the other will have NULL value.

I'm still confused on how to develop the code for logging the transactions into the database as this is my first time development for an accounting application

It's not clear from your question how much of double entry bookkeeping your application will be supporting. Your database is overly constrained when it comes to the various transactions that can occur in full blown double entry bookkeeping.

In particular, there are certain transactions where the debit is split among two or more accounts that receive the debit, or the credit is split among two or more accounts that receive the credit.  These transactions represent unusual transactions in your case, if I understand your case correctly.  But you may find it necessary to support them.

To illustrate the above, consider when a homeowner makes a mortgage payment.  In the homeowner's books the debit side of the transaction will be split between Mortgage Principal and Mortgage Interest.  Mortgage Interest is an expense, while Mortgage Principal is a liability, which the mortgage payment reduces.

Again, You may or may not need to support split transactions.

Given that this is your first time out with DB design for double entry bookkeeping, it may well be worth your while to bone up on the subject, on order to avoid delivering a substandard product.  There are many excellent books on this subject.  In addition,  if you want to see some accounting databases that are freely available, you can go to http://www.databaseanswers.org/data_models/ and look under data models for accounting.

I'll give an oversimplified summary here.

Many double entry bookkeeping databases split transaction data into two tables, which I'll call TransactionHeader and TransactionDetail.  TransactionHeader has columns for data that occurs only once per transaction, such as TransactionID and Date.  TransactionDetail contains data about a transaction as it affects just one Account, and there are at least two transaction details for each transaction.  Data in TransactionDetail includes TransactionID, ItemID, AccountID,  DebitAmount, and CreditAmount.

Split transactions have more than two TransactionDetails associated with them.
The sum of the credits must equal the sum of the debits for any transaction.  Sometimes, this can be expressed as a database constraint.  Other times, the programmer is forced to maintain this aspect of data integrity in the application.

Some database builders collapse CreditAmount and DebitAmount into a single column called Amount.  Credits are entered as negative amounts.  If you do sums on groups of items, the right thing will happen.  There are, however, downsides to taking this shortcut.  I'll leave it up to the books that teach you about bookkeeping databases to fill that in.

Again, if this is overkill in your situation, feel free to disregard this answer.

Hopefully, no one will come along later and attempt to use your databases for purposes it was never intended to cover.  But I have seen this happen in the real world, with disastrous consequences.


Is there any interruption of service when switching recovery models? I need to switch to FULL recovery model for several SQL Server 2008 databases. However these are in production. Whilst doing these things out of hours is good practice I wanted to plan for any down time or interruption in service, no matter how brief.

Does anybody know the impact changing the recovery model to Full has on a production database?

Clarification: I have a whole process scripted (backup's and logging) and tested for log shipping and restoring the other end. However the one thing I couldn't find details on was if changing the model would have an impact on availability or performance during the process.

I've never had any issues from switching from simple to full. You will have to consider your backups tho.

Microsoft has some information about making the switch in the link below,

http://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx


Difference between row-based and statement-based replication in MySQL? What is the actual difference between row-based and statement-based replication. I am actually looking in terms of replication's effect on the slave.

If I am using row-based replication then what is the effect on the slave and if I am using statement-based then what is the effect?

Please also take the following parameters in consideration:

   replicate-ignore-db  and replicate-do-db

Thanks....!

Statement-Based Replication (SBR) will replicate SQL Statements. It is easier to read using mysqlbinlog dump program.

Row based replication (RBR) will replicate actual data changes. Binary logs and relay logs will grow much faster than SBR. You also cannot identify the SQL that generated the changes.
(Please read updated information for MySQL 5.6.2. below)

Here is a more comprehensive pros-and-cons list : http://www.databasejournal.com/features/mysql/article.php/3922266/Comparing-MySQL-Statement-Based-and-Row-Based-Replication.htm

MySQL will allow both types of statement/row formats in its binary logs. I recommend STATEMENT based. Default binary log format is MIXED which is OK.

Since you are using replicate-ignore-db and replicate-do-db, I would trust Statement-Based Replication.

Update for MySQL 5.6.2:

Introduced in MySQL 5.6.2 the binlog_rows_query_log_events system variable causes a MySQL 5.6.2 or later server to write informational log events such as row query log events into its binary log. So we can identify the SQL that generated the changes.

Reference: 17.1.4.4 Binary Log Options and Variables


Efficient method of inserting a column into a table based on another table Consider the following tables

CREATE TABLE Main (
    id INT PRIMARY KEY,
    state VARCHAR(255) DEFAULT NULL
)

CREATE TABLE ColorTable (
    color VARCHAR(255),
    state  VARCHAR(255)
)

INSERT INTO Main (id, state) VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'B'),
    (5, 'C'),
    (6, 'A'),
    (7, NULL)

INSERT INTO ColorTable (color, state) VALUES
    ('Yellow', 'A'),
    ('Red', 'B'),
    ('Green', 'C'),
    ('Black', NULL)

I would like to add a new column 'color' into the table 'Main'. I want this such that the color for each id is based on the state in 'Main', and the corresponding color in 'ColorTable'.

I can achieve this through the following:

ALTER TABLE main
ADD color VARCHAR(255)

UPDATE main
SET main.color = ColorTable.color
FROM main
LEFT JOIN ColorTable ON main.state = ColorTable.state

UPDATE main
SET color = 'Red'
WHERE state IS NULL

I am curious as to alternative methods of doing this–can I achieve the same result using a SELECT INTO or something else?

No, you cannot alter the table structure and insert the data into new column in one statement.

But you can combine last 2 updates:

UPDATE main
SET main.color = CASE WHEN main.state IS NULL
                      THEN 'Red'
                      ELSE ColorTable.color
                      END
FROM main
LEFT JOIN ColorTable ON main.state = ColorTable.state


ORA-01017 and ORA-12545 when trying to connect to oracle 12c with sqlplus I'm trying to connect to an oracle 12c database from a remote machine, but it fails with two errors.

sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.37)(Port=1521))(CONNECT_DATA=(SID=ora12)))"

SQL*Plus: Release 9.2.0.8.0
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: user
Enter password:  (pass)
ERROR:
ORA-12545: Connect failed because target host or object does not exist

On the connecting machine I can do:

$ tnsping server12

TNS Ping Utility for Linux: Version 9.2.0.8.0
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora12)))
OK (0 msec)

So there seems to be connectivity.

If I run the same sqlplus command on the DB server, I get

$ sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.37)(Port=1521))(CONNECT_DATA=(SID=ora12)))"

SQL*Plus: Release 12.2.0.1.0

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

The TNS on the connecting machine says

server12 =
  (DESCRIPTION =
  (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SID = ora12)
  )
  )

I can also connect from SQLDeveloper using Basic Connection Type and hostname = 192.168.1.37 and port 1521 and SID=ora12.

Any ideas on how to make it connect?

You can't connect from a 9.2.0 SQL*Plus to a 12.2.0.1 version or Oracle Database.

It was never a supported option as displayed in the Interoperability Support Situation matrix chart in an Oracle Support Note.1 (Oracle Support Login required to read the Oracle Document.)

A SQL*Plus 9.2.0 client was only able to connect to 11.2.0 server or lower.

The lowest supported version of SQL*Plus that could connect to an 12.2.0.1 version of Oracle Database would be an 11.2.0.x client.

1 Oracle: Note 207303.1

Reference: oracle 9i client connecting to oracle 12c server
 (Stackoverflow.com)


Adding specific information to a local file I have several files on a webserver (not stored in the database). I'd like to store several information to this specific file. The user should be able to post comments about this file.

My question is:

What is the best way in order to store information in a database in addition to a file that is stored on a webserver... How do I connect both parts?

For a SQL Server solution, FILESTREAM (or FILETABLE if you can use SQL Server 2012) was built to solve this type of problem.

This solution will allow you to store the files in a central network location (most importantly, not on a web server box) outside the database, and manipulate metadata information within the database.

As mentioned in my comment, other RDBMS systems may also have similar capabilities (I'm not familiar enough with them to say, though).


Issues in starting MongoDB service I've installed MongoDB 3.0 in RHEL 6. From root user, I'm trying to start the mongo service but it is getting failed and the log file is not getting updated as well.

2015-10-13T09:53:26.133+0530 E NETWORK  [initandlisten] Failed to unlink socket file /tmp/mongodb-27017.sock errno:1 Operation not permitted
2015-10-13T09:53:26.133+0530 I -        [initandlisten] Fatal Assertion 28578
2015-10-13T09:53:26.133+0530 I -        [initandlisten]

***aborting after fassert() failure

The above is the last updated log.

I have another user 'oracle' from which also it is not starting.

I also tried running mongod --repair

It gives:

2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] MongoDB starting : pid=11033 port=27017 dbpath=/data/db 64-bit host=vmrac2
2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] 2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: You are running on a NUMA machine.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **          We suggest launching mongod like this to avoid performance problems:
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **              numactl --interleave=all mongod [other options]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 16384 processes, 65536 files. Number of processes should be at least 32768 : 0.5 times number of files.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] db version v3.0.6
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] git version: 1ef45a23a4c5e3480ac919b28afcba3c615488f2
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] build info: Linux ip-10-67-194-123 2.6.32-220.el6.x86_64 #1 SMP Wed Nov 9 08:03:13 EST 2011 x86_64 BOOST_LIB_VERSION=1_49
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] allocator: tcmalloc
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] options: { repair: true }
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] finished checking dbs
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] now exiting
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to close sockets...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: waiting for fs preallocator...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: closing all files...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] closeAllFiles() finished
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: removing fs lock...
2015-10-13T11:02:59.475+0530 I CONTROL  [initandlisten] dbexit:  rc: 0

Things I have tried:

Deleting /tmp/mongodb-27017.sock but it says no file found with that name.
Changing the net.port in the config file to change the .sock file.
Setting unixDomainSocket : enabled: false

run these commands for ubuntu

sudo rm -rf /tmp/mongodb-27017.sock
sudo service mongod start
sudo ps aux | grep mongo
sudo kill


Non Super User cannot alter stored procedures and functions I am running into an issue granted a user access to alter stored procedures and functions within MySQL 5.7.22-0 running on Ubuntu.

User has ALL PRIV on the database except GRANT
User has SELECT PRIV on mysql.proc
User can DROP and CREATE --> they can not ALTER

Here is the output from SHOW GRANTS

SHOW GRANTS FOR 'userA'@'%'

GRANT USAGE ON *.* TO 'userA'@'%'
GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'userA'@'...
GRANT SELECT ON `mysql`.`proc` TO 'userA'@'%'

Output for SHOW CREATE PROCEDURE on a specifc pro

SHOW CREATE PROCEDURE mydatabase.spAddEmailListContact

spAddEmailListContact   NO_AUTO_VALUE_ON_ZERO

CREATE DEFINER=`userB`@`%` PROCEDURE `spAddEmailListContact`(
        IN `EmailListID` INTEGER(11),
        IN `CustID` INTEGER(11),
        IN `ContactID` INTEGER(11))
    MODIFIES SQL DATA
BEGIN
INSERT INTO
  emaillistcontacts
(
  EMAILLIST_ID,
  CUST_ID,
  CUSTCNT_ID
)
VALUES (
  EmailListID,
  CustID,
  ContactID
);
END utf8mb4 utf8mb4_general_ci  latin1_swedish_ci

Any thoughts on what I am missing?

Note: A key requirement is to allow SEVERAL developers to edit procedures without requiring them to be super users.  (this is so simple to do in mssql :-/ it surprises me that this is difficult in mysql)

Perhaps you want this in the CREATE:

SQL SECURITY INVOKER

or, more likely,

SQL SECURITY DEFINER


Error while configuring mirroring, Microsoft SQL Server, error : 1418 I have a SQL Server 2008 Enterprise SP1 installed on my both principle and mirror server. SQL Server and SQL Server agent service is also started by same ID having sysadmin rights.

But when I am trying to establish mirroring between them, it is reflecting an error message, Microsoft SQL Server, Error:1418

I have checked the endpoint of both server that are in started state. and also dropped and create endpoints again. please note i am not using the witness server here.

Principle server endpoint port:- 5022
mirror server endpoint port :- 5023
mirror server at endpoint port 5022 also didn't work.
Both 5022 and 5023 ports are opened on firewall

but when I execute the following script on both server, asynchronous(high safety mode) mirroring established.

TO SETUP MIRRORING in synchronous(high safty) ON PRINCIPLE SERVER:-
ALTER DATABASE SMPP
SET PARTNER = 'TCP://principle server IP:5022'

TO SETUP MIRRORING ON MIRROR SERVER:-
ALTER DATABASE SMPP
SET PARTNER = 'TCP://mirror server ip:5023'

I also tried to put hostname of principle and mirror server instance in above script but no luck.

Please suggest any working solution as soon as possible.

Microsoft error no 1418:-
this error occurs due to communication problem between endpoints.first of all you have to check TCP/IP for the instance was enabled or not,if any duplicate endpoints exists,in
case login was not created inn the other instances of mirroring pair,check for the edition of principal and mirror servers.


Why would a Clustered Index Seek return a higher "Actual Number of Rows" than there are rows in the underlying table? I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, until a few hours or days later when a bad plan gets cached again.

Looking at the "bad" plan, I noticed something that strikes me as odd: there is a Clustered Index Seek on a table that currently has about 1.7 million rows. The "Estimated Number of Rows" for this operation is about 1200, which is definitely in line with the average row count I would expect from that operation in this case, but the "Actual Number of Rows" is in excess of 60 million! Following the fat line from this leaf node, various downstream operations such as joins and sorts are being performed on all 60 million, causing excessive slowness, spills to tempdb, and other badness.

I must be misunderstanding what a Clustered Index Seek actually does, because I wouldn't think it's possible for it to "output" more rows than are in the underlying table. What could cause this? And better yet, any pointers on how to fix it?

[Bonus points for including something like "sp_updatestats fixes it every time but can't figure out how to fix it permanently? Go read this article." This has been a general problem for us on a few different fronts lately.]

The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).

Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows


Not Able to access user privileges for root in mysql I accidentally deleted all privileges for root and users. I would like to know how to restore them. I don't have access to USER privileges for any other user.

Restart mysqld_safe with this option added --skip-grant-tables

That will work only from localhost, and gives you a clean slate for creating a new root password.


In-place upgrade vs. new install My search for this question yielded some similar questions, but none that I could see exactly like this.
Question: In your experience, when doing a SQL Server upgrade, say from 2008 to 2017, or from 2012 SP1 to 2017, or whatever from/to (also depending on whether the direct upgrade path is available or not), is it better to start with a brand new installation, or with an in-place upgrade? What are the challenges you faced when doing one versus the other (SQL Server upgrade, in-place or new fresh install)? What poison do you pick? What is your own personal experience and advice?

Thank you in advance, Raphael.

I have been doing a ton of migrations at my current job and the one time we tried doing an in place upgrade we ended up losing a days worth of work. This is my normal process when moving from an older system to a new one.

IT Creates a new VM with SQL Server configuration (multiple drives for splitting data, temp, log, etc.), usually with the same specs as the old system.
I install SQL Server on the new server
Restore the latest backup from the old system onto the new one and assign basic permissions to allow users/developers to test.
Once they confirm compatibility I take advantage of DBATools and Powershell to migrate users, jobs, and everything else required to the new server. I will also make a fresh backup/restore of the old system databases.
Users/Developers test again and confirm system is operational.
Schedule a transition period, usually on weekends late at night. IT makes a snapshot of the VM. Set old databases as read only, backup/restore databases to new system, once the new system is ready users and developers test.
Only after they confirm it is working IT will shutdown the old system (they delete the VM after a month of no issues).

This is obviously fairly generic, sometimes if the system is small enough I will use DBATools migrate command and just move everything over at once. Sometimes the time between steps is days or weeks, even months if its a core system.


Jobs view is not visible to read-only account users I am the admin of one of the database, and I have created a view in SYS schema using below code which returns the status of the running jobs:

CREATE OR REPLACE VIEW CAF_DB_REFRESH AS
SELECT *
FROM all_scheduler_job_run_details
WHERE OWNER = 'CAFANALYST'
  AND log_id > 2573
ORDER BY log_date;

I have created public synonym for this view and given access to read only account which will be used by all users in my team.

When they perform the following query, they are getting only column names with no rows:

select * from CAF_DB_REFRESH;

Could you please guide me what is the problem and if any special privileges required for read only account? If yes, I request you to guide which one is the best to see but not to modify by read only user.

As @Jeffrey pointed out, ALL_* view is not useful in your case. You should use DBA_* views because they show the records for all owners of the job objects.

The following sequence works just fine for me:

$ sqlplus /nolog

SQL> connect / as sysdba
Connected.

SQL> select distinct owner from dba_scheduler_job_run_details;

OWNER
------------------------------
EXFSYS
SPONGEBOB
SYS

SQL> create or replace view sys.jcaf as
  2  select * from dba_scheduler_job_run_details
  3  where owner = 'EXFSYS';
View created.

SQL> grant select on sys.jcaf to spongebob;
Grant succeeded.

SQL> create public synonym jcaf_v for sys.jcaf;
Synonym created.

SQL> connect spongebob/passwd
Connected.

SQL> select distinct owner from jcaf_v;

OWNER
------------------------------
EXFSYS


Migrating PostgreSQL files to new server We have a Win 2008 server that went down because of cpu failure.  We were running parts of the Atlassian stack (JIRA, Confluence, Stash, and Bamboo) on it using PostgreSQL as the database.  I now have the harddrive from that dead server mounted in to a Win 7 machine as a secondary harddrive.  We unfortunately did not have backups setup.  All of the instructions I see for migrating databases to new machines involve running PostgreSQL cli programs against a running PostgreSQL instance, but in my case all I have is a folder not a running PostgreSQL server.  I would like to be able to install PostgreSQL on the Win 7 machine, somehow get the data migrated over to the new PostgreSQL installation and off of that secondary drive.  Then I would like to export the data out using the standard procedures and move it to a new production server or possibly export using the Atlassian backup tools if we end up moving the data in to the Atlassian cloud service where they use who knows what db engine.  Either way though I need the data off the olddrive and running in a new PostgreSQL server to move forward.  Anyone have any ideas?

Install the latest minor release of the same PostgreSQL major version - e.g. if you were using 9.3.1 before, install 9.3.9. Make sure it's the same architecture - i.e. if you were using a 32-bit PostgreSQL before you must install 32-bit PostgreSQL now.

Stop the service using the services control panel (services.msc).
Delete %PROGRAMFILES%\PostgreSQL\9.3\data.
Copy the old data directory to that location.
Get properties on the data directory and in the security tab recursively grant full control to NETWORKSERVICE.
Start the service using services.msc
immediately set up backups.


ERROR: invalid GeoJson representation the GeoJSON Spec show,

{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}

But when I wrap that in a call to ST_GeomFromGeoJSON, like this,

SELECT ST_GeomFromGeoJSON($${
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [125.6, 10.1]
  },
  "properties": {
    "name": "Dinagat Islands"
  }
}$$);

I get the error,

  ERROR:  invalid GeoJson representation

From the docs on ST_GeomFromGeoJSON

  ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document.

This means you'll want to cut out just the value from the geometry in your GeoJSON document,

SELECT ST_AsText(geom)
FROM ST_GeomFromGeoJSON($${
  "type": "Point",
  "coordinates": [125.6, 10.1]
}$$) AS geom;

     st_astext
-------------------
 POINT(125.6 10.1)

ℹ ST_GeomFromGeoJSON is the inverse of  ST_AsGeoJSON (which only produces the geometry section of GeoJSON document.)


"tempdb metadata memory-optimized" configuration option Checking SQL Server 2019 CTP 2.5 (15.0.1500.28) I found this new configuration option and was wandering what is behind it. Couldn't find any information so far and was wondering if you know where to find more about it?

As far as I can tell the feature has not been officially announced yet.

There are a couple of resources available on this feature from members of the community:

Youtube video from Erik Darling

Blog post from Ned Otter

This would appear to be the configuration option that will allow you to put some TempDB metadata tables into memory, preventing many types of resource wait and hopefully speeding use of that database up on busy systems. The value of the configuration option can be found with this query:

SELECT * FROM sys.configurations
WHERE configuration_id = 1589


Reverse pattern matching: find the longest prefix I have a database with the phone rates. Rather simple one.

Code     Price
----     -----
1        0.005
18       0.002
1888     0.000

Now I have a phone number arriving to the database: 18127551221. I want to do a query and get the price of the call.

I want to be able to find longest possible match in the "Code" column. Length of the Code can be up to 6 symbols so I want something versatile.

Any ideas?

Something like this should work:

SELECT *
FROM prices
WHERE '18127551221' LIKE "Code" || '%'
ORDER BY "Code" DESC
LIMIT 1


NLS_LANG and NLS_CHARACTERSET I thought I read that NLS_CHARACTERSET has the encoding of the database and NLS_LANG the encoding of the client. Is that correct?

Does that mean that the two encodings can be different?

In some other documentations, I read that the NLS_LANG is defined by the NLS_CHARACTERSET. Which assertion is true?

The NLS parameters can be quite tedious to understand and I recommend searching for the term "Setting Up a Globalization Support Environment" in the official documentation.

NLS is short for National Language Support and is the beginning of multiple parameters that are available in the various Oracle products.

NLS_LANG

NLS_LANG is described as a variable that defines the <LANGUAGE>_<TERRITORY>.<CHARACTER_SET> of your client environment.

An example of a valid NLS_LANG setting would be:

NLS_LANG=AMERCICAN_AMERICA.US7ASCII

Language

The individual parts of the variable define how Oracle displays information. If your <LANGUAGE> is AMERICAN then messages, days and months will be displayed using American spelling. Sorting is also affected by this part of the parameter

Territory

Territory affects things like the default date, settings for money values (e.g. $ sign), and number formats (e.g. 3,000,000.12 to display three million point 12)

Character_Set

This is the bit where it becomes pretty interesting. This part of the NLS_LANG parameter is described as the

  ...character set used by the client application.

Summary NLS_LANG

So in essence you are configuring how you client would display data stored in the database. If your database contains the following data:

 Employee     | Employment Start    | Salary
--------------+---------------------+--------
 Michael Ward | 2018-01-01          | 100240

...then if your client has AMERICAN_AMERICA.US7ASCI as the NLS_LANG setting, then querying this table would return:

 Employee     | Employment Start     | Salary
--------------+----------------------+-------------
 Michael Ward | 01-JAN-18            | 100,240

...whereas having a setting of FRENCH_FRANCE.WE8ISO8859P1 would result in:

 Employee     | Employment Start     | Salary
--------------+----------------------+-------------
 Michael Ward | 01/01/18             | 100'240

Lets follow up with the other parameter...

NLS_CHARACTERSET

The NLS_CHARACTERSET parameter is setup during the creation of your database instance and shouldn't be changed thereafter. (It can be altered but it can cause data corruption even in the DATA DICTIONARY objects). This parameter defines the encoding of the data in the ...

NLS_CHARACTERSET Parameter

... CHAR, VARCHAR2, LONG and CLOB columns of a table.

NLS_NCHAR_CHARACTERSET Parameter

... NCHAR, NVARCHAR2 and NCLOB columns of a table.

Summary NLS_CHARACTERSET

The parameter is used to tell the RDBMS system with which encoding it shold store data in certain columns.

I have intentionally left a large portion of the explanation regarding NLS_CHARACTERSET out, because it gets really complicated when comparing encodings between AL8UTF8, AL32UTF8 and AL16UTF16 (which can only be use in conjunction with the NLS_NCHAR_CHARACTERSET parameter) and all the other possible Unicode settings.

Comparison

When retrieving data from a database column NLS_LANG will add all the bells and whistles (date formatting, time formatting, number formatting) to the data that is being retrieved.

The NLS_CHARACTERSET parameter defines with which encoding the data is stored in the columns. (ASCII, Unicode, ...)

Answering your Question

  Does that mean that the two encodings can be different?

Yes, you can have two totally different encodings. You could store you data using a setting of NLS_CHARACTERSET = AL32UTF8 and still display and transport the data to the database with NLS_LANG=American_America.US7ASCII.

  Display vs. Storage


Empty result set and Case When = 1 I'm pretty sure this is a implicit conversion but I wasn't able to find information about it.

Suppose I have this SQL:

SELECT
CASE WHEN (SELECT 1 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

My subquery is returning one row and hence compared to 1. This leads my CASE WHEN to produce the 1 output.

Even if I set the subquery to return no rows like the sample below, the CASE statement returns  the 0 result:

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END

Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ?
I don't feel comfortable using this type of comparison. In my humble opinion, it's better to use EXISTS like this:

SELECT
CASE WHEN EXISTS(SELECT 1 WHERE (1=1)) THEN 1 ELSE 0 END

Is there any advantage or disadvantage if I don't use EXISTS ?

Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ?

No, The query didn't return 0 because the empty result is considered equal to 0 but because the empty result set is considered NULL and null isn't equal to anything.

As you found out

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END

Returns 0

But

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 0 THEN 1 ELSE 0 END

Also returns 0.

So the empty result set is not 1 and not 0, it's NULL as evidenced by

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) is NULL THEN 1 ELSE 0 END

which returns 1.

See this dbfiddle with that code.

NULL is unknown so SQL Server doesn't know what it's equal to.

  In my humble opinion, it's better to use EXISTS

If you rely on the existence of a result set, I agree EXISTSis probably the way to go. That's what the construction is made for.
If you want to compare 2 values off course you'll need to use CASE but for your stated scenario I'd use EXISTS

Your comparison will break if your result set has more than one row or column.

For example

SELECT
CASE WHEN (SELECT 1,2 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

returns

  Msg 116 Level 16 State 1 Line 2   Only one expression can be specified
  in the select list when the subquery is not introduced with EXISTS.

and

SELECT
CASE WHEN (SELECT 1 WHERE (1=1) UNION SELECT 2 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

returns

  Msg 512 Level 16 State 1 Line 1 Subquery returned more than 1 value.
  This is not permitted when the subquery follows =, !=, <, <= , >, >=
  or when the subquery is used as an expression.


Find fragmented space free below HWM Oracle That High Watermark Value always eludes me when calculating free space in my tablespaces.

Will the following query consider fragmented free spaces of object below the HWM value for each TS?

select * from sys.sm$ts_free;

(That would be in Oracle 10gR2)

In short, I want to know how much (the total, over and below the HWM) space does Oracle have to keep inserting things.

If my query is wrong, can someone provide one that does that?

pd. I also have other messy queries summing the segment sizes of objects, but then again, I do not know how all this wraps together with fragmented space and HWM. An explanation here would be highly appreciated.

Answer is complex and involves the knowing of different Oracle concepts. I try to explain (sorry if I am going to be too wordy).

The High Water Mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used (Oracle Concept definition). Considering that you are using ASSM (Automatic Segment Space Management)

all blocks above HWM are unformatted and never been used. All blocks below HWM might be:

Formatted and contain data
Formatted and empty because the data was deleted
Allocated, but currently unformatted and unused

DML operations do not release free space from the table below the HWM. Therefore a lot of DML on a table might leave the table fragmented. DDLs always reset the HWM. More consideration about the block use can be done considering two impotant storage parameters: PCTFREE and PCTUSED.

What about HWM in files? The high water mark of a file shows you the smallest size the file could be "shrunk" down to. Any DBA has its own query to find the HWM in a datafile. Personally I use one taken by Tom Kyte and is this:

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

The HWM position in a datafile does no represent necessarily the free space available in a datafile. You may have a 20Gb tablespace where dba_free_space shows 19gb free but latest HWM is set at 18Gb position.

In conclusion: the position of HWM in a datafile does not represent the real space freely available in the datafile. For this, the query on sm$ts_free is right. Additionally tables can have allocated more space than one is really used.


AG with overlapping AG groups We are currently trying to implement a two node AG where node 1 has two AG's. The first AG is primary to node 1 and secondary to node 2, the second AG is primary to node 2 and secondary to node 1. I was told that this is the set up we will be using because we wanted to make use of all resources, for example node 1 will be responsible for 3 databases and node 2 will have 2 databases, I guess this is a pseudo active/active set up. I personally don't know the use case for this set up, but it does seem a little bit over the top. Any pro's, con's or resource help would be helpful.

I have seen setups that run 2 AG's in a 3 node cluster. Usually all the databases are running on the primary server, but they can (and do) failover each AG separately. The setup will work, the question now is, do you want to do it?

Some problems you will have to consider:

Extra admin of running and debugging 2 AG's (and confusion of what runs where)
Extra overhead on SQL of running a 2nd AG (minimal impact for the small number of databases)
Extra licensing costs. SQL isn't cheap, if you have Software Assurance you can run a non-readable secondary for free. If you want to split the load you need to fully license both servers. (Note: I'm not a licensing expert)
The big one: Can both servers run a full load on all of the databases should 1 server fail?

The last point is the important one, and the one that makes this setup less desirable. If you are splitting the 2 AG's across 2 servers because 1 server doesn't have the resources, then what are you going to do when 1 server crashes and the AG fails over? You now have one server running all of the databases. Can that server cope with the load? If so, keep it simple and use it all the time. If not, your AG is not going to do the HA/DR that you'd hoped it would.

One scenario I did see where this setup worked was a database running on the secondary that consumed a lot of CPU due to batch processing. The business decided that should either node fail, they wanted to still be able to query the data (very small load on SQL) but were happy to stop the batch processing of new data. They chose to go with 2 AG's and added a whole lot of code to their batch processing so it could turn itself off when it detected both AG's were being served from the same server. In normal operations this took a heavy CPU load off their primary OLTP server, but at the cost of having to stop a fairly important process should 1 node fail.


MariaDB mysqldump lacks all data - how do I force including it? for years I've been using mysqldump without much trouble.  Recently I joined a project that is using "MySQL dump 10.14  Distrib 5.5.52-MariaDB, for Linux (x86_64)" and when I attempt to do a dump it includes all of the create table statements, and the routines if I include --routines, but it doesn't contain any of the data.  There are no INSERT statements in the file at all.

I've tried on multiple databases within this DBMS instance, and I've tried using several different user accounts, including root.  I've checked that the users I'm using have access to the data, and if I log directly into mysql from those usernames I can browse the data.

I've been unable to find any help information on the web because all posts seem to assume that the insert data will just be there (which I think it right).  Has anyone seen something like this before or is there a suggestion of what I might try to get a real database backup?

I recognize this is a very old MariaDB version, I'm new to the team and I'm willing to update it, but not until after I know the data is successfully backed up somewhere.

Thanks in advance for your help, this is really confusing me.

Here's what I'm trying:

/usr/bin/mysqldump -u root -p --all-databases  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

/usr/bin/mysqldump -u root -p -d databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Before posting my question I did just a few more tweaks to see what might be going wrong.  I may have stumbled into an answer.  So I posted the question anyway and I'm going to post a potential answer here in hopes that it helps the next person.

The -d option in the second row I thought was short for --databases, but it appears to do something else.  I don't see it in the docs (https://mariadb.com/kb/en/library/mysqldump/) but when I changed that to --databases I started getting results with the INSERT INTO statements.

So, instead of:

/usr/bin/mysqldump -u root -p -d databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Use:

/usr/bin/mysqldump -u root -p --databases databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Or (without naming the option at all):

/usr/bin/mysqldump -u root -p databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql


é being converted from database to site I have a MySQL database that is storing business data... In the database, when running a select against a business name that contains an accented e (é), it comes up correctly (showing me that it is stored correctly.) On our website, though, it is showing up as the diamond with the question mark.

I checked out database and found the following:

Character_sets_dir          /usr/share/mysql/charsets/
Character_set_client        utf8
Character_set_connection    utf8
Character_set_database      latin1
Character_set_filesystem    binary
Character_set_results       utf8
Character_set_server        latin1
Character_set_system        utf8

Where things get lost is on the website itself... The web page is encoded as UTF8...

Any ideas of where the conversion may be failing?

-Wes

You need to ask the database from another angle. Please run this:

SHOW CREATE DATABASE mydb;

This will tell you what it sees. If you want to change the Character set and.or collation, do this:

ALTER DATABASE mydb ... ;

In mysql, ALTER DATABASE can change character set and/or collation. I have mentioned this in my past posts in the DBA StackExchange :

Dec 04, 2012 : Why default character_set_server is latin1?
Sep 18, 2012 : change encoding of database from latin1_swedish_ci to utf8
Sep 26, 2011 : How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?
Sep 19, 2011 : How to convert mysql database charset to utf-8


How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement My Query is

declare @id INT
set @id = 69
select
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"@id"***.value')) Agency,
   (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) Protocal
from actions_new a
  left join action_data ad on ad.id_ref = a.id
where baid = 12;

You should cast @id to varchar and concate it. like this.

(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,

Complete query is. Hope this will help you.

declare @id INT
set @id = 69
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+cast(@id as varchar)+'"***.value')) Agency,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) Protocal
from actions_new a
left join action_data ad on ad.id_ref = a.id
where baid = 12;

Thanks!


Mass data upload from Excel - technology I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list.

Requirement:
There will be a web application which will allow user to upload Excel document in specified format(expecting 1 Million of rows) and it will then Save somewhere in DB and run some calculation against our data base values. After that will present user with some numbers/chart based on calculations.

My first question is MySQL DB good enough for this requirement over any other DB like Postgres?
Second question is how much time will it take to upload 1 million rows of data from Excel, considering there will be 6 columns?
Third, which technologies should I consider to make this application fast and efficient like InnoDB, etc.

Once again I have done my research but I could not reach into conclusions, and any DB expert feedback/help will be highly appreciated.

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user.  Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use:

COPY table FROM 'fpath' CSV HEADER DELIMITER ';'

You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well.

If you must use excel formats like .xlsx, why not use something like Pentaho and develop an application that can connect to any database. There is a java api for this one. Other ETL tools may do the trick too.

In these instances, upload speed is under 10 minutes. My last upload over a non-commercial network of 900000 rows lasted 20 minutes in Pentaho. A straight copy to in PostgreSQL is actually much faster.

As for speed, querying is your major problem. If you are just copying and querying, Postgres is fine. However, if you are running intensive queries, counts; aggregation, use an ETL tool like Pentaho that you can run from Java and connect with a connection pool like BoneCP for Postgres or another connection pooling software for other databases.

Most databases support connection pooling but have connection limits so look at your configuration settings.


Is it safe to add more than primary data file to Temp DB in SQL server 2008 R2 as we have database server with 4 Core processor? Is it safe to add more than primary data file to Temp DB in SQL server 2008 R2 as we have database server with 4  Core processor and please tell me the steps to do it .And does it will tune performance?

Tip : Our Temp DB size is about 22 GB which is bigger than our Production database and does it normal?

The article linked by @McNets is a good one.
The One And Only One article has code you can use: https://www.brentozar.com/blitz/tempdb-data-files/

Because you only have 4 cores, you can probably just create a total of 4 TempDB files.

If you use the code snippet in Brent's article, make sure you match up the beginning of the file name section:

FILENAME = N'

with the folder directories you already have.

You don't have to restart the engine if you're not shrinking the the original file while adding these. As for the size, it's not necesaarily a bad thing that it is bigger than your prod database, though it could be indiciative of something. Do you delete a bunch of records out fo there recently?


How badly does innodb fragment in the face of somewhat out-of-order insertions? Let's say there's a composite primary key on (field1, field2)

Also, let be X a theoretical set of n numbers

Inserts look like this:

INSERT INTO table(field1, field2)
VALUES([a number picked at random from X], [an incrementing number]);

Every m inserts, a number is randomly removed from X, and another one is
randomly added.

Let's say n=1000 and m=500. In other words, every 500 inserts, one element in set X changes, but set X always has 1000 elements.

How badly will the B+tree become fragmented?

In the case of an index, fragmentation can vary greatly. Yet, nothing can be worse that entering data into an index already sorted.
To illustrate, let's take the worse case BTREE, a balanced binary tree. A balanced binary tree is a binary tree that monitors the height of its child nodes and will only allow for a difference in heights of :

-1 (Left TreeNode Taller Than the Right TreeNode)
 0 (Left TreeNode and Right TreeNode Same Height)
+1 (Left TreeNode Shorter Than the Right TreeNode)
See Definition of AVLTrees

Over 25 years ago, I learned that entering already ordered data into a balanced binary tree results in 45% of the tree nodes requiring rotation and rebalancing.
Common sense would have it that if I enter the numbers 1-15 ordered as follows:

8,4,12,2,6,10,14,1,3,5,7,9,11,13,15
8,12,4,14,10,6,2,1,3,5,7,9,11,13,15
8,4,12,2,6,10,14,15,13,11,9,7,5,3,1
8,12,4,14,10,6,2,15,13,11,9,7,5,3,1

into a balanced binary tree, absolutely no tree rebalancing would ever occur.
This being the case, entering random data into a balanced binary tree could never be as bad as entering already sort data. On average, random ordering would require less tree rebalancing than sequential ordering.
Now, let's get away from balanced binary trees into something more real-world, BTREEs. Treenodes in BTREE indexes usually hold some power of two index page entries + pointers to a lower BTREE node. Page splitting would occur in the event pages become full. Consequently,  many early BTREE pages would end up half full due to page splitting (The execption to this would be with bulk inserts and in-memory sorting of keys and writing out more compact index pages with as little fragmentation as possible). Other than that exception, insertion of ordered data in a BTREE page would cause page splits with a very small degree of regularity. Random inserts would at the very least wreak less havoc than loading ordered data.
InnoDB
In terms of InnoDB, you have the gen_clust_index. It has hooks to another type of index used internally for tracking row IDs. Never think about the gen_clust_index as just any other BTREE. It is what the Oracle world would call an index-organized table. Column ordering for InnoDB is handled so differently from MyISAM that can you detect a notable difference if you attempt to reorder row data in a specific key order.
According to pages 148,149 of MySQL Database Design and Tuning

it shows that doing ALTER TABLE tblname ORDER BY column-list; would greatly improve retrieval for MyISAM tables by physically ordering rows. In contrast, doing ALTER TABLE tblname ORDER BY column-list; would have no effect on InnoDB tables because data is always ordered by RowID within the clustered key.
MORAL OF THE STORY
When it comes to InnoDB, do not worry about randomness of data. No matter what machinations you perform on tables indexes, the clustered key has the final say on access. Whatever order you load the data, gen_clust_index will abide by it. No questions asked.


Separate month and year columns, or date with day always set to 1? I'm building a database with Postgres where there's going to be a lot of grouping of things by month and year, but never by the date.

I could create integer month and year columns and use those.
Or I could have a month_year column and always set the day to 1.

The former seems a bit simpler and clearer if someone is looking at the data, but the latter is nice in that it uses a proper type.

Personally if it's a date, or can be a date, I suggest always storing it as one. It's just easier to work with as a rule of thumb.

A date is 4 bytes.
A smallint is 2 bytes (we need two)

... 2 bytes: one smallint for year
... 2 bytes: one smallint for month

You can have one date which will support day if you ever need it, or one smallint for year and month which will never support the extra precision.

Sample data

Let's look at an example now.. Let's create 1 million dates for our sample. This is approximately 5,000 rows for 200 years between 1901, and 2100. Every year should have something for every month.

CREATE TABLE foo
AS
  SELECT
    x,
    make_date(year,month,1)::date AS date,
    year::smallint,
    month::smallint
  FROM generate_series(1,1e6) AS gs(x)
  CROSS JOIN LATERAL CAST(trunc(random()*12+1+x-x) AS int) AS month
  CROSS JOIN LATERAL CAST(trunc(random()*200+1901+x-x) AS int) AS year
;
CREATE INDEX ON foo(date);
CREATE INDEX ON foo (year,month);
VACUUM FULL ANALYZE foo;

Testing

Simple WHERE

Now we can test these theories of not using date.. I ran each of these a few times so as to warm things up.

EXPLAIN ANALYZE SELECT * FROM foo WHERE date = '2014-1-1'
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=11.56..1265.16 rows=405 width=14) (actual time=0.164..0.751 rows=454 loops=1)
   Recheck Cond: (date = '2014-04-01'::date)
   Heap Blocks: exact=439
   ->  Bitmap Index Scan on foo_date_idx  (cost=0.00..11.46 rows=405 width=0) (actual time=0.090..0.090 rows=454 loops=1)
         Index Cond: (date = '2014-04-01'::date)
 Planning time: 0.090 ms
 Execution time: 0.795 ms

Now, let's try the other method with them separate

EXPLAIN ANALYZE SELECT * FROM foo WHERE year = 2014 AND month = 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12.75..1312.06 rows=422 width=14) (actual time=0.139..0.707 rows=379 loops=1)
   Recheck Cond: ((year = 2014) AND (month = 1))
   Heap Blocks: exact=362
   ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.64 rows=422 width=0) (actual time=0.079..0.079 rows=379 loops=1)
         Index Cond: ((year = 2014) AND (month = 1))
 Planning time: 0.086 ms
 Execution time: 0.749 ms
(7 rows)

In fairness, they're not all 0.749.. some are a bit more or less, but it doesn't matter. They're all relatively the same. It simply isn't needed.

Within one month

Now, let's have fun with it.. Let's say you want to find all intervals within 1 month of Jan 2014 (the same month we used above).

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE date
    BETWEEN
      ('2014-1-1'::date - '1 month'::interval)::date
      AND ('2014-1-1'::date + '1 month'::interval)::date;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=21.27..2310.97 rows=863 width=14) (actual time=0.384..1.644 rows=1226 loops=1)
   Recheck Cond: ((date >= '2013-12-01'::date) AND (date <= '2014-02-01'::date))
   Heap Blocks: exact=1083
   ->  Bitmap Index Scan on foo_date_idx  (cost=0.00..21.06 rows=863 width=0) (actual time=0.208..0.208 rows=1226 loops=1)
         Index Cond: ((date >= '2013-12-01'::date) AND (date <= '2014-02-01'::date))
 Planning time: 0.104 ms
 Execution time: 1.727 ms
(7 rows)

Compare that to the combined method

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE year = 2013 AND month = 12
    OR ( year = 2014 AND ( month = 1 OR month = 2) );

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=38.79..2999.66 rows=1203 width=14) (actual time=0.664..2.291 rows=1226 loops=1)
   Recheck Cond: (((year = 2013) AND (month = 12)) OR (((year = 2014) AND (month = 1)) OR ((year = 2014) AND (month = 2))))
   Heap Blocks: exact=1083
   ->  BitmapOr  (cost=38.79..38.79 rows=1237 width=0) (actual time=0.479..0.479 rows=0 loops=1)
         ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.64 rows=421 width=0) (actual time=0.112..0.112 rows=402 loops=1)
               Index Cond: ((year = 2013) AND (month = 12))
         ->  BitmapOr  (cost=25.60..25.60 rows=816 width=0) (actual time=0.218..0.218 rows=0 loops=1)
               ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.62 rows=420 width=0) (actual time=0.108..0.108 rows=423 loops=1)
                     Index Cond: ((year = 2014) AND (month = 1))
               ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.38 rows=395 width=0) (actual time=0.108..0.108 rows=401 loops=1)
                     Index Cond: ((year = 2014) AND (month = 2))
 Planning time: 0.256 ms
 Execution time: 2.421 ms
(13 rows)

It's both slower, and uglier.

GROUP BY/ORDER BY

Combined method,

EXPLAIN ANALYZE
  SELECT date, count(*)
  FROM foo
  GROUP BY date
  ORDER BY date;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=20564.75..20570.75 rows=2400 width=4) (actual time=286.749..286.841 rows=2400 loops=1)
   Sort Key: date
   Sort Method: quicksort  Memory: 209kB
   ->  HashAggregate  (cost=20406.00..20430.00 rows=2400 width=4) (actual time=285.978..286.301 rows=2400 loops=1)
         Group Key: date
         ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.012..70.582 rows=1000000 loops=1)
 Planning time: 0.094 ms
 Execution time: 286.971 ms
(8 rows)

And again with the composite method

EXPLAIN ANALYZE
  SELECT year, month, count(*)
  FROM foo
  GROUP BY year, month
  ORDER BY year, month;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23064.75..23070.75 rows=2400 width=4) (actual time=336.826..336.908 rows=2400 loops=1)
   Sort Key: year, month
   Sort Method: quicksort  Memory: 209kB
   ->  HashAggregate  (cost=22906.00..22930.00 rows=2400 width=4) (actual time=335.757..336.060 rows=2400 loops=1)
         Group Key: year, month
         ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.010..70.468 rows=1000000 loops=1)
 Planning time: 0.098 ms
 Execution time: 337.027 ms
(8 rows)

Conclusion

Generally, let the smart people do the hard work. Datemath is hard, my clients don't pay me enough. I used to do these tests. I was hard pressed to ever conclude that I could get better results than date. I stopped trying.

UPDATES

@a_horse_with_no_name suggested for my within one month test WHERE (year, month) between (2013, 12) and (2014,2). In my opinion, while cool that's a more complex query and I'd rather avoid it unless there was a gain. Alas, it was still slower though it's close -- which is more of the take away from this test. It simply doesn't matter much.

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE (year, month) between (2013, 12) and (2014,2);

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=5287.16..15670.20 rows=248852 width=14) (actual time=0.753..2.157 rows=1226 loops=1)
   Recheck Cond: ((ROW(year, month) >= ROW(2013, 12)) AND (ROW(year, month) <= ROW(2014, 2)))
   Heap Blocks: exact=1083
   ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..5224.95 rows=248852 width=0) (actual time=0.550..0.550 rows=1226 loops=1)
         Index Cond: ((ROW(year, month) >= ROW(2013, 12)) AND (ROW(year, month) <= ROW(2014, 2)))
 Planning time: 0.099 ms
 Execution time: 2.249 ms
(7 rows)


Planning simple CMS: How much relation? I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes with low-end hardware.

As I am doing database design this carefully for the first time I am struggling more than a bit and could use some help. I've made two models for the database, which both fail to satisfy me:

Model 1: Everything dynamic is an entity (i.E. menu items, blog posts, projects):

I want to achieve maximal scalability by using the same base for every content type, but one of my examples already fails: the menu. Not all menu item's might have a URL (dropdown-item's for example) and some might be separator, which would be impossible to achieve with this.
Model 2: Posted dynamic content are entities (i.E. blog posts, projects, comments):

Not as scalable as Model 1, but most likely faster. Still, contents won't need a preview (which is shown as description on the landing page) as they're only shown on their parent's pages.

The Project-class is just an example class for content-type-specific metadata, in this case the info for a button that could lead to a github repository, an image gallery and so on.
I've got the feeling I'm completely failing at this, I also fear that this will result in hundreds of queries when trying to get comments on something (checking for parent's on each one for correct nesting etc.).

I'll be glad for everyone getting me an advice on how to approach this better, a tip on how to view this from a different angle or anything else that could point me in the right direction.

Once you start stepping into areas like hierarchies, you probably want to know your RDBMS to some extent.  If your RDBMS supports recursive queries natively your hierarchies will look very different then if each iteration is a round trip to the db.

This being said there are a few warnings that come to my mind looking at your schema.  I believe these are likely to impact both performance and data integrity.

The separate parents table strikes me as somewhat dangerous.  What are the rules for parents and children?  Can you have blog -> menu_item -> blog -> comment -> menu_item?  This first creates the possibility of problems that may be difficult to track down, but also consider that if the rules are based on smaller tables, actually enforcing the rules becomes possible  If your menu items are broken off, a caching layer becomes a possibility. Same with other things that are less frequently changed, and the amount of effort to generate the menu on the part of the db will be reduced.
I actually think your second model, if things like menu items, etc. are broken off is likely to be both more scalable and faster.  I would still suggest breaking apart comments and blog posts, and tracking the relationships there more closely.   If you are worried about scalability and your site has 10000 blog posts and 1000000 comments, being able to filter the comments also by the attached blog post is likely to mean you can get to use better indexes at least on some db's.

I am reminded of the phrase "premature optimization is the root of all evil."  Design a good db schema first and then worry about speed and scalability.


DDL Trigger not working as expected SQL Server 2012 I have a DDL Trigger in SQL Server 2012 as:

CREATE TRIGGER [AuditProcChanges]
ON DATABASE FOR
CREATE_TABLE,ALTER_TABLE,DROP_TABLE
AS
DECLARE @message_body XML
SET @message_body = EVENTDATA()

    BEGIN
       BEGIN TRANSACTION
       INSERT  INTO MyChangeLog(vcEventType, dtEventTime, vcServerName, vcLoginName
                                            , vcUserName, vcObjectName, vcObjectType, vcSQL)
            SELECT  @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                        'varchar(128)') AS EventType,
                    GETUTCDATE() AS PostTime,
                    @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                        'varchar(128)') AS ServerName,
                    @message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
                                        'varchar(128)') AS LoginName,
                    @message_body.value('(/EVENT_INSTANCE/UserName)[1]',
                                        'varchar(128)') AS UserName,
                    @message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
                                        'varchar(128)') AS ObjectName,
                    @message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
                                        'varchar(128)') AS ObjectType,
                    @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                        'nvarchar(max)') AS TSQLCommand

        COMMIT TRANSACTION
    END

But when I'm altering the table from design view (right click and Design), the events that are getting logged are only of creation of temp table and dropping of main table. Create_Table event is not getting captured.

Can't we capture the log events when we alter a table from design view. Please help.

Your trigger has no problem at all (the only question I have is why do you open another transaction inside your trigger if all you do is 1 SELECT only?)

When you alter a table in such a way it has to be recreated (for example adding a column not at the end of the existing columns), the actions server takes are following (simplified):

create a new table called dbo.Tmp_old_tbl having the new
structure (note, it's not temporary table that lives in tempdb, it's
just a new table residing in your db)
insert all the data from your old table into the new one
EXECUTE sp_rename N'dbo.Tmp_old_tbl', N'old_tbl', 'OBJECT'

So that "temp table" that you saw created is in fact your new table, renamed from Tmp to your old_tbl


SSRS CROSS Domain Reports Server Authentication I have a pretty complicated situation at hand, let me try to explain.

I have a SQL Server and a Web Server in a separate domain (lets call it domain A).

SQL Server has the databases for Reporting services, The web server has reporting services installed.

The SSRS also have SSL certificates installed and we are using https protocol to connect to the reports manager.

Now I need to give access to users from domain B to connect to the Reports Manager (report server on Domain A).

The users from Domain B cannot have any logins in the Domain A where the reporting services are installed, I know to access the reports manager I need to add domain logins/groups to the reports manager and assign them appropriate roles to access the reports.

What options do I have (if any) to give access to users from Domain B to connect to a Reports Manager on Domain A?

Important Note:

The access to reports server is via NLB with external facing IP, the reports server (web server) or the SQL server does not have any external facing IP

I don't recommend it but

You have two options:

Connect using a domain username & password from network A (everyone
in network B connecting as DOMAINA/userx)
Allowing anonymous access.  (this is inherently dangerous and you would want at a bare minimum to pass something to all of your reports as part of your process for security authentication and probably change the default structure for uploading http://local/reportserver might become http://local/rs)


Replication stalling on one subscriber I currently have transactional replication set up for three databases from the same Publisher to the same three Subscribers in SQL Server 2005. Publisher and Distributor are on the same server.

One of the databases on one Subscriber stops for several hours each day at different times with no indication of why. No problem with all three databases on the other two Subscribers or the other two databases on the problem Subscriber.

Searching the system logs shows nothing abnormal happening on the Subscriber and Replication Monitor details just says "delivering replicated transactions". Inserting a tracer token shows the Publisher to Distributor is normal.

Any suggestions for troubleshooting this problem?

Finally traced the issue to a table on the Subscriber that was missing the PK index. Still not too sure how this could have happened as all subscribers were initialized from the same snapshot at the same time. Re-initializing the individual subscriber had no affect.
I had to;
    - remove the article from the publication
    - run the Snapshot Agent
    - manually drop the table on each Subscriber
    - add the article back to the publication
    - re-run the Snapshot Agent

All good now.


should I give dbo permissions to NT AUTHORITY\NETWORK SERVICE? We're in the process of assessing a new application that will require access to our MSSQL database

I've set up a user that is a member of a role only giving it readonly access to certain views in the database and the application is fine using this user to access our database.

However it says I still have one thing left to do and that's to give NT AUTHORITY\NETWORK SERVICE dbo permissions on the same database

At the moment this is only a test environment but it will need rolling out onto our live system if we choose to use the application

Am I right in saying it would be insecure to give dbo permissions to this service account?

please let me know if there's anything I'm misunderstanding about the service accounts

I wouldn't directly say it is insecure to give dbo permissions to that account but it is definitely more secure to have the application run under an account only it can use and apply permissions accordingly.


Which columns to place the clustered index I have a system that has been up-and-running for almost 3 years now. Previously (and currently) the front-end was using NHibernate for all of the database access.

Right now, I'm in the process of converting over to Dapper and utilizing stored procedures for EVERYTHING (reads, writes, etc).

Now that NHibernate is gone, I don't believe the current structure of tablename, tablenameid (clustered primary key) is optimal anymore for every table. Lots of my tables will NEVER be accessed via the primary key, UNLESS it's for a delete (even that can be avoided).

So, here are a couple examples of table structures, and which fields I believe should be the clustered index.

Example 1:

Event table
------------------------------------------
EventId int primary key clustered,
Season int not null,
EventDate datetime not null,
EventName varchar(100) not null,
EventType varchar(10) not null,-- (soon to be it's own table, but not right now...there are only 2 possible values, Tournament or Dual)
SchoolId int not null,
OpponentId int NULL,
etc...

So currently, I have the clustered index on the primary key. This is one table that is accessed 99% of the time by Season, and SchoolId. (Very rare that the EventId is used, when it is, it's for a delete)

Here's where the trickiness comes in. If the EventType is 'Dual', then uniqueness is by Season, EventDate, SchoolId, OpponentId. If the EventType is 'Tournament', Then uniqueness is on Season, EventDate, SchoolId, EventName (OpponentId will be null).

Under this architecture, I don't believe I can/should have a unique key, which isn't really my issue...

Am I safe to assume that the clustered index for this table should be on the Season and SchoolId columns?

Example 2:

WrestlerRanking table
-----------------------------
WrestlerRankingId int primary key clustered,
Season int not null,
Week int not null,
IsCurrent bit not null,
WrestlerId int not null,
etc...

In this scenario, similar to the one above, 99% of the this table is accessed by Season, WrestlerId, and IsCurrent. Uniqueness can be set by Season, Week, and WrestlerId.

Would/should the clustered index for this table be on Season and WrestlerId, even though the majority of joins to this table will include the IsCurrent column? This is definitely not unique as there could be around 20 records for the Season and WrestlerId combination (20 different weeks of rankings).

Here's where the trickiness comes in. If the EventType is 'Dual', then uniqueness >is by Season, EventDate, SchoolId, OpponentId. If the EventType is 'Tournament', >Then uniqueness is on Season, EventDate, SchoolId, EventName (OpponentId will be >null).

I'd look at what's common between these two queries to determine what columns to use in the Clustered Index. Remember, the Clustered index is effectively how the data is arranged on the disk, so it should have a structure similar to the hierarchy that queries against the table will reference. If every query, or the most critical ones, all look at Season, SchoolId and EventDate regardless of EventType, then I'd use those in the Clustered index. Additional Non-clustered covering indexes can be created for specific queries (around EventType or OpponentId, for example) which INCLUDE the columns returned to prevent key lookups.

Clustered indexes do not need to be unique, they need to specify how the data should be arranged so that queries can quickly seek to the desired location. The primary key should be unique, but it doesn't have to be a clustered index. If it's just a reference for a row value that doesn't have any useful information that queries will use (i.e. you never join on it), then it should not be part of the clustered index.

For the second example, I would probably use Season, Week, and WrestlerId if those are most queried. The order should be in the logical heirarchy that the data is queried in...which brings me to my next point:

All this advice is contingent on the execution plans of your queries.

Always, ALWAYS, use actual execution information to determine what the best indexes for your queries are. Got some table scan issues? Implement a clustered index. Got a Clustered index scan slowing you down? Look into implementing a non-clustered covering index for that query based on the columns it's looking up (often suggested by the DB engine itself). Got key lookup? Try using an INCLUDES on the affected index with the looked-up columns. Indexing hypothetically, before you're super familiar with the DB engine, often leads to wasted time and rework. I'm not saying to shut your brain off and just blindly follow the query plan's suggested indexes, but instead use the query plan to build your indexing strategy rather than trying to preempt it, or second guess it.


mysqldump Error 2013 I have a database installed, that I would like to backup  in mysql.  The problem  is mysqldump fails on exporting the 'maia_mail' table

# mysqldump -u root -p maia > maia.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `maia_mail` at row: 15

It runs for less than 30 seconds and gets error out as above.

The total size of the DB is 1.3GB with the maia_mail table being 1.0GB

In my.cnf I have these set:

[mysqld]
max_allowed_packet      = 1300M
[mysqldump]
max_allowed_packet      = 1300M

Please advise or give some guidance on how to dump the database?

I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it's your best (only) option. Here it goes:
SUGGESTION #1 : Disable extended inserts
The default setting for mysqldump would include clumping together hundreds or thousands of rows in a single INSERT. This is known as an extended INSERT. It is causing some overrun beyond just max_allowed_packet.
I answered a post back on Sep 01, 2011 (MySQL server has gone away obstructing import of large dumps) where I discussed doing the same thing for importing a large mysqldump. I believe disabling extended INSERT would help with creating a troublesome mysqldump as well.
mysqldump -u root --skip-extended-insert -p maia > maia.sql

Bad news: What this does in create an INSERT command for each row. This will definitely increase the time it takes to perform the mysqldump. Consequently, it will also increase to time is take to reload (probably by a factor of 10-100.
I have discussed skip-extended-insert before

Aug 12, 2011 : Why is mysqldump file so large?
Aug 09, 2013 : Backup / Export data from MySQL 5.5 attachments table keeps failing!
Nov 16, 2014 : How to batch multiple insert statements for database dump with MySQL?

SUGGESTION #2 : Dump binary data as hex (OPTIONAL)
To make the mysqldump's binary data more byte portable, dump such data in hexadecimal
mysqldump -u root --skip-extended-insert --hex-blob -p maia > maia.sql

Bad News: It will bloat the mysqldump a little more
GIVE IT A TRY !!!
Side Note : The maximum size of max_allowed_packet is 1G


Efficient join with data spread into different tables I have to work with an ancient database schema on Microsoft SQL Server where records are split into different tables but referenced in a central table.

A record in my central table will look like this:

| ID | Firstname | Lastname | Table    |
| 1  | John      | Smith    | users001 |
| 2  | North     | West     | users005 |

The database will then contain multiple tables users001, users002 through usersN where individual action records are stored. I can join a users-table with the central table using the id column (ID is a fk in every users-table).
These tables can be quite long (500k rows or more).

Now, in order to do calculations over all users, I created a view that basically does a UNION ALL of all users-tables which I can then query.
Obviously that is quite expensive hence my question:
Any idea how one can efficiently join these tables?

A stored procedure is not an option unhappily as the application performing the calculations can only work with tables or views (BI Suite I cannot influence).

You've got a couple of options - but none of them are likely to produce lightning-swift results.

Note I'll be making a few assumptions below. The main one is that all the usersXXX tables have the same structure (same column names, same datatypes).

Massive LEFT JOIN - if there are fewer than 256 usersXXX tables (or more, maybe - see comment below code), and you're only joining to the central table (we'll call that User, for convenience), then you can (in theory) create a single statement like the following:

SELECT U.ID, U.firstname, U.lastname, COALESCE(U001.field1, U002.field1, ... , U255.field1) as field1, ...
  FROM User U
         LEFT JOIN users001 U001 ON (U.ID = U001.ID)
         LEFT JOIN users002 U002 ON (U.ID = U002.ID)
        ...
         LEFT JOIN users255 U255 ON (U.ID = U255.ID)

Note that all references to a given field from the usersXXX tables, will need to COALESCE the field from the possible 255 tables.

SQL Server claims to have an upper limit of 256 tables in a query, which is why that's our limit here. However, the documentation is not consistent. On another page the maximum number of tables per SELECT statement is "Limited only by available resources". In addition, experiments have shown that queries can contain more than 256 tables. Another test had involved 599 tables.

The awkward nature of referring to the COALESCEd fields makes this hard to maintain and hard to read.
Your current view. I'm assuming (if you're using the view to do calculations over all users) that you may not necessarily be joining to the User table.

So, your view probably looks something like this (you can add an INNER JOIN to User if you want):

SELECT field1, field2, ... FROM users001
UNION ALL
SELECT field1, field2, ... FROM users002
UNION ALL
...
UNION ALL
SELECT field1, field2, ... FROM usersZZZ

You've already stated that this leaves something to be desired performance wise - but we'll get back to that.
Next, there's a direct use of UNION ALL. Instead of going through a view (simplifying the construction of the query), use the actual UNION ALL of the tables directly. This may perform better that option 2, if you're using a WHERE clause. It depends on how the query optimizer handles things - if there's relatively few records to bring back, but the query optimizer insists on materializing the view before applying the WHERE clause, then this will help. If (as I suspect) the query optimizer would apply the WHERE clause at the individual table level rather than trying to materialize the view, then this would probably have little performance benefit.
Last, you can query each table individually, and put the results in a temp table. The possible performance advantage here would be that each individual query would be much simpler, would take far less time to generate a plan for, and would run relatively quickly. The disadvantages are that a separate plan would be generated for each table (meaning that even the relatively short time to generate these query plans must be multiplied by the number of tables for the first run), and that the "simple" query must be run NNN times (where NNN is the total number of tables).

So, the real question - what's making the current query so costly? If it's simple the amount of data that must be gone through, then playing with alternate queries is unlikely to result in something dramatically faster. If, on the other hand, the sheer volume of different tables involved is causing a really convoluted plan to be generated, than an alternative that might create simpler plans (maybe option 3, definitely option 4) could be worth a try.

You'll probably want to look at the actual query plans being generated, in addition to looking at the time each alternative takes.

Also, keep in mind that perfection is not always desirable. If the current query is being used for reporting, for example, and the reports are taking longer than you'd like, but an acceptable amount of time, then spending a lot of time to shave off 25 of the current run time may simply not be worth it. If, on the other hand, a daily report is taking 25 hours to run, that's a different scenario.


How to convert an Update a query from MS Access to Oracle SQL Software I have an update query below written in Microsoft Access and I would like to
convert that to Oracle SQL Developer 17.2.0.188.
There is also an image in the attachment showing the query expression.

The query said: "update the Products.UnitsInStock to UnitsInStock-Quantity
if the ShippedDate is not null and This_Order_Was_Shipped = "YES"

UPDATE Products INNER JOIN (
           Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
       ) ON Products.ProductID = [Order Details].ProductID
SET    Products.UnitsInStock = [UnitsInStock] -[Quantity]
WHERE  (
           ((Orders.ShippedDate) IS NOT NULL)
           AND ((Orders.This_Order_Was_Shipped) = "YES")
       );

Right.  So ORACLE (at least 10g) does not accept JOIN criteria in an Update Statement.  At least not in the way it's written in your post.

Instead, you'll need to write it as a simple SELECT statement (subquery), then WRAP that in an update, OR as an EXISTS.

Here's how to do it in a "subquery".

https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle

I may have missed something, I didn't test this out fully in Toad as I have to work on some tasks.  Anyone please feel free to edit.

*Note: ORACLE does not accept Brackets [], please remove your spacing in the columns when you convert to ORACLE if you're planning to.  Underscores are easier.  The workaround I'm using is Quotes.

Another note, I'm not sure I like the efficiency of the queries.  Seems like it would be more efficient to start at Orders or Order Details vs. Products, especially considering all your WHERE clauses are on the Orders Table.

So your Select validation query would be something like:

SELECT Products.UnitsInStock AS OLDUnitsInStock,
       (Products.UnitsInStock - Products.UnitsInStock - Quantity) AS NEWUnitsInStock
       --FULLY QUALIFY THE QUANTITY COLUMN ABOVE TO AVOID AMBIGUITY
FROM Products
INNER JOIN "Order Details"
    ON Products.ProductID = "Order Details".ProductID
INNER JOIN Orders
   ON Orders.OrderID = "Order Details".OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.This_Order_Was_Shipped = 'YES'

Your Update would look something like:

UPDATE
(
SELECT Products.UnitsInStock AS OLDUnitsInStock,
       (Products.UnitsInStock - Products.UnitsInStock - Quantity) AS NEWUnitsInStock
       --FULLY QUALIFY THE QUANTITY COLUMN ABOVE TO AVOID AMBIGUITY
FROM Products
INNER JOIN "Order Details"
    ON Products.ProductID = "Order Details".ProductID
INNER JOIN Orders
   ON Orders.OrderID = "Order Details".OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.This_Order_Was_Shipped = 'YES'
) updateTable
SET updateTable.OLDUnitsInStock = updateTable.NEWUnitsInStock;


Should I index before or after importing a dump? I am using mongodb to persist a very big-size (90G), which has nearly 40,000,000 items. I read and parse this file and insert all items into mongodb (my programming language is perl, batch_insert, and I map one item to one mongodb document).

Before I insert, I have already pre-created indexes (about 10 index keys). I find that  insert speed cannot meet my need (200 to 400 items per second). I know that too many index keys will definitely slow down my insert, especially when the size of collection becomes quite big. So I wonder if I can index them after I have dumped all the data into db.

Can anyone tell me if this way is available, and if it can definitely save my time?

Yes, you can index them after you have imported (there will then only be the default _id index on the collection).  This is also recommended because the resulting indexes will be more compact and more efficient (for similar reasons foreground vs background indexing is preferred if you can afford to do it).  It will take some time to complete though, especially with 10 indexes to build.

To build after the import, simply do not define any indexes until after your import is complete, then use the ensureIndex() command to create the required indexes afterwards (with the usual caveat that such index creation will be resource intensive).  For more information:

http://docs.mongodb.org/manual/core/index-creation/


SQL Server migration using a replica I want to migrate a fairly big SQL Server instance (100 GB+) to a cloud machine.
I don't think the attach/detach method will be a good idea. Because sending big files over the internet would take a long time (probably more than 1 night) I thought about configuring snapshot/transactional replication from the source to the cloud instance.

The idea is to let the replication take its course over a few days and then after the databases are up to date simply stop the replication and direct the application to the subscriber database.

Is it possible? Do you have any other ideas? Maybe AlwaysOn (I guess it requires Enterprise and not Standard edition)?

What version of SQL Server are you using?  From SQL Server 2008 onwards, Backup Compression can offer superb rates of compression.  I've seen backups pack down to 10% of their original size, so your 100GB+ could be more like 10GB.  Of course this really varies depending on the type of data you are storing; XML doesn't pack down too well for example.

You also have options like breaking the backup into multiple files so you could copy multiple files in parallel rather than one big backup file.  This won't help much if your "pipe" to the cloud is narrow though.

Have a look at your compressed backup size, then work out how many Megabytes per second you can transfer through your existing pipe ( ftp, WAN, etc ? ) eg by transferring a sample 1GB file to get a rough idea.

A final option, and something I'm looking at right now is SQL Server 2014 Clustered Columnstore with full Archival Compression ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ).  Intended for 'cold', rarely accessed data, again this really varies but I've seen up to 30x compression.  YMMV.  I'm therefore considering an option of 1) drop all indexes, 2) add clustered columnstore with archive compression, 3) backup and move to cloud 4) recreate earlier indexes or leave as it.   I'm currently investigating this and the relationship between clustered columnstore compression and backup size.

Good luck!


Need ODBC connection to postgresql to include materialized views I have an Access front end user interface that links to tables in a postgreSQL database using the most recent ODBC driver. I can link to the postgreSQL tables and views but can't get to the materialized views.

This thread describes a patch to the driver to include materialized views. Does anyone know if this patch is included in the most recent driver? If not, how would I apply it? If it is included, which options need to be selected so that materialized views are accessible from MS Access?

I have found that Postgres views are included in the linked tables within Access. Click on any linked table within Access and within the list should be your views.  I always find it useful within Postgres to preface a view name with 'VIEW_' so that they are all readily visible and accessible within Access.


Database gives "ORA-01109: Database not open" error when it IS open Running Oracle Database 12c on Windows Server 2012

Whenever I attempt to do a log switch (alter system archive log current), I am given the error in the title. Searching online shows the simple solution of opening the database via alter database open, then checking the status via select status from v$instance to ensure it is open will fix the issue. However, anytime I check the status, it is already in an OPEN state. alter database open doesn't do anything as it's already open.

Anyone have any idea why I'm encountering this issue? Any help would be appreciated.

Thanks

Your database instance is open.

Your database open_status is a different thing, it could be open READ ONLY. The database is expected to be open for READ WRITE when you switch a logfile. So you might conclude that the error message is not complete.

select name, open_mode from v$database;

and since you are in 12c also check:

select name, open_mode from v$pdbs;

Most likely your database is not open for READ WRITE, as the error states. Question that remains is: why?

Documentation about Oracle Database Instance

When your database is open READ ONLY and try to perform a log switch, you get ORA-1109 : database not open.


Partial access MongoDB databases without replication. Is that possible? Is there any possibility to deploy a structure in case if when one or two mongod(s) in sharded cluster will be unavailable, so we'll be able to read and write partial results in\from remaining shards? But in the current case I don't want to deploy Replicaset structure. Just for the clarification, our sharded structure must be without replicasets.
Thank you.

You haven't mentioned what driver you will be using, so I can't give you a direct link to the specific option in your language of choice, but yes there is an option to return partial results from a sharded cluster if there are shards down (I still don't advocate running without replica sets, but that's a different discussion).

You can set it in the shell as follows (sample values, will work for findOne also):

db.collection.find({}, {}, 0, 0, 0, DBQuery.Option.partial)

Each of the drivers should have support for setting this option, either directly or similar to the shell via an option.  See the partial option toward the bottom of the node.js cursor documentation for example or the DBCursor setOptions and associated QUERYOPTION_PARTIAL in Java.  For reference, the relevant feature requests to add partial results options are SERVER-2020 and SERVER-4610


How to deal with SQL Server Full Text Log Files? We use SQL Server full text search functionality.   As a result we have lots of full text log files like SQLFT0003200286.LOG.5 in the sql server error log folder.  Is there anything we can do to ensure these files are periodically purged (oldest files purged first)?

It seems your option is this undocumented stored procedure running in a job:
EXEC sp_fulltext_recycle_crawl_log @ftcat = 'FullTextCatalogName';

Check the Recycle Fulltext Catalog Log Files article by Jonathan Kehayias.
I just tested it in a lab environment and it purges the oldest fulltext log file creating a new live file (in my lab I could see 1 live file and 6 old files, so this must be the default number of files SQL Server keeps).