MySQL: summary query for date activity by last 7 days, week and all year totals I have a customer table with records like

+-----+-------+-------------+-----------+
| ID  | Name  | Date        |  Status   |
+-----+-------+-------------+-----------+
| 1   | Joe   | 2014.02.01  | New       |
| 2   | Bob   | 2014.02.02  | Requested |
| 3   | Rob   | 2014.02.02  | New       |
| 3   | Dale  | 2014.02.02  | New       |
| 4   | Jane  | 2014.02.03  | New       |
+-----+-------+-------------+-----------+

... assume data going back in time here

Using a "Group By" query I can easily get a result like the following:

SELECT Status, Date, Count(*) FROM Customer GROUP BY Status, Date

+------------+-------------+-------+
| Status     | Date        | Count |
+------------+-------------+-------+
| New        | 2014.02.01  |     1 |
| New        | 2014.02.02  |     2 |
| New        | 2014.02.03  |     1 |
| Requested  | 2014.02.02  |     1 |
+------------+-------------+-------+

I want to also add rows for week total and a year to date total, so something like the following:

+------------+-------------+-------+
| Status     | Date        | Count |
+------------+-------------+-------+
| New        | 2014.02.01  |     1 |
| New        | 2014.02.02  |     2 |
| New        | 2014.02.03  |     1 |
| New        | Week1       |     4 |
| New        | YearToDate  |   500 |
| Requested  | 2014.01.02  |     1 |
| Requested  | Week1       |     1 |
| Requested  | yearToDate  |   200 |
+------------+-------------+-------+

The "Week1" would be just the total of the previous 7 days

The "YearToDate" would be just the total of that group since the start of the year.

I'd ideally like to do this with a single query.  However I don't know how.  My other alternative is to write 3 queries:

a) Select last 7 days and group by day of week

b) Select last 7 days and group by week in year

c) Select since beginning of year and group by

And then mash them together in my program (not my ideal solution).

Any help is appreciated.

SELECT * FROM
(
    Status,Date, Count(*) RowCount FROM Customer
    WHERE Date > (DATE(NOW()) - INTERVAL 7 DAY)
    GROUP BY Status,Date
    UNION
    Status,WEEK(Date) WK,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,WK
    UNION
    Status,'YearToDate' YTD,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,YTD
) A ORDER BY Status,Date;


What are the advantages of a monitor server for log shipping I'm researching and implementing log shipping as part of disaster recovery for our SQL server. I've seen that a monitor server is recommended (but not required) for monitoring the log shipping process. What is the advantage of having that monitor server? Is it the only way to view the history of the log shipping process? Is it useful to have in case one of the servers goes down so you can get some data on what happened?

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though...

Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary server, so you are none the wiser (server being down then you are not getting restore alerts that things are out of date).

You notice a couple of days later, but by then it's too late and you have to restart log shipping from scratch.

Having a monitor server just helps to keep an eye on things and gives you a warning point not related to the servers involved in log shipping (think of it kind of like the witness server in mirroring, but without the automatic failover)


How to copy user rights and roles from one user to another I require a PL/SQL script that copies all of a user's rights, roles and mandates from one user to another.

I am using Oracle 12c.

may be helpful.

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','username') FROM DUAL;

set long 9999999
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','username') FROM DUAL;


Mysql: Separate memory for databases Is there a way to separate memory allocation for databases in mysql on the same server?
Say for database_1, we would like to allocate 20GB RAM,
and for database_2, 10GB RAM.
Is there any way this is possible.

No.
Don't do it.

I say not to do it because it usually leads to folly.  If one database grows faster than the other, then it is starved while the other has spare space.  This may hobble the overall system more than the original benefit you envisioned.

Another example:  Having /tmp on a separate filesystem.  I hate to think how many times DBAs find queries crapping out, only to discover that their tiny /tmp fs was starved while the main disk was swimming in space.

On the other hand, there are valid cases for splitting up things -- one disk is an old, slow, spinning drive, another is a fast, low latency, SSD.  Or you add an extra drive, but it is not convenient to get the OS to see all the drives as one larger virtual drive.  These involve spelling out what databases / tables / logs to put where -- and lead to the potential problem that I mentioned above.

If you are referring to RAM, then the general rule is to give most of RAM to innodb_buffer_pool_size, then forget about RAM allocation.  Only a few percent of RAM needs to be allocated for table cache, etc, etc.  The buffer pool is used by all tables 'equally' -- that is on a roughly LRU basis.


How to configure mongodb replicaset of 3 nodes in authentication mode I had configured a mongodb replicaSet of 3 nodes. It was running fine But WHen i created a adminUser and dbuser on primary node and enabled the authorization in mongodb.conf file of all nodes then they are not connecting. Below is the output of rs.status() after enabling authorization. Kindly Help.

{
    "set" : "replset1",
    "date" : ISODate("2017-07-15T04:09:41.796Z"),
    "myState" : 3,
    "term" : NumberLong(1),
    "heartbeatIntervalMillis" : NumberLong(2000),
    "members" : [
        {
            "_id" : 1,
            "name" : "10.10.8.1:27017",
            "health" : 1,
            "state" : 3,
            "stateStr" : "RECOVERING",
            "uptime" : 54841,
            "optime" : {
                    "ts" : Timestamp(1500033801, 1),
                    "t" : NumberLong(1)
            },
            "optimeDate" : ISODate("2017-07-14T12:03:21Z"),
            "infoMessage" : "could not find member to sync from",
            "configVersion" : 1,
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "10.10.8.2:27017",
            "health" : 0,
            "state" : 6,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                    "ts" : Timestamp(0, 0),
                    "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-07-15T04:09:37.008Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "authenticated" : false,
            "configVersion" : -1
        },
        {
            "_id" : 3,
            "name" : "10.10.8.3:27017",
            "health" : 0,
            "state" : 6,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                    "ts" : Timestamp(0, 0),
                    "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-07-15T04:09:37.136Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "authenticated" : false,
            "configVersion" : -1
        }
    ],
    "ok" : 1
}

You made things in wrong order.
At start you have "open" replica set. You put authorization on and you have so called "localhost exception" what means that you (still) can connect primary directly without authorization. On that connection you MUST first create "root" (super user) user

use admin
db.createUser(
{
    user: "root",
    pwd: "toor",
    roles: [ "root" ]
})

Now, your "localhost exception" is gone! You must authenticate your self. You can logout and then login again OR just give command

db.auth("root","toor")

And start creating other (normal) users.


Which Editions of Oracle are Free? I am aware of Oracle Express edition which is up to version 11g, which is pretty dated. However, I have read that Oracle are changinging/have changed their licensing of newer editions so that we will no longer have to wait years before a newer Express edition becomes available.

I teach SQL to users from different backgrounds, some of whom use Oracle at work. It would be useful to point them to a package they can practice with. However, I find Oracle’s web site somewhat perplexing.

Which editions are currently available free for personal use, such as training?
Is there also a free edition available to a small business, such as with 4 computers?

Express Edition [XE] is available for usage by small businesses.  You get no upgrades or patches.  Support is only available by internet like Google and DBA SE.  You are limited to 1 CPU Core, 1 GB of RAM, and 11GB of user data.

Current version of XE is 11.2.0.2.  Version 18 XE is due out soon (October 2018).  I've read Oracle will then release newer versions on an annual basis.  Max user data is increased to 12GB of data.  But, "Adv. Compression" is suppose to be included (increases user data to est. 40GB).  This information comes from a post by the Project Manager for XE on the Oracle community forum for XE.

For personal learning, you can use any edition.

For your classes, I would probably use XE for the classroom but mention VirtualBox.  Oracle has a prebuilt VirtualBox VM for learning. A small list can be found here.

Additionally, http://LiveSQL.oracle.com provides a free "test stuff out" similar to "db fidle".  (I would not use that for classroom)

More details (with respect to licensing) should be referred to your local friendly Oracle sales representatives.


Load Testing When SQL Server Caching Is Disabled I was doing a load test by sending the same web request 5000 times one after the other. Here are the total data transfer amounts I observed:

From SQL Server to SAN: 6 MB
From SAN back to SQL Server: 1.5 MB
From SQL Server back to IIS Server: 71 MB

And here are the mdf and ldf file size changes in full recovery mode:

mdf size increase: 1 MB
ldf size increase: 4 MB

As you can notice, despite the 71 MB returned to IIS Server, the total data from SAN to SQL Server was 1.5 MB. After doing some research, I figured that this was due to the SQL Server caching data and not hitting the disk for each request.

So, in order to disable this behavior, I used CHECKPOINT; DBCC DROPCLEANBUFFERS; after each request. Here are the results after this change:

From SQL Server to SAN: 390 MB
From SAN back to SQL Server: 11 GB
From SQL Server back to IIS Server: 71 MB

mdf size increase: 1 MB
ldf size increase: 30 MB

My questions are:

1. What is the reason that data transfer from SQL Server to SAN increased from 6 MB to 390 MB? Shouldn't disabling caching have only affected reading from the disk, not writing to it?

2. Data from SAN to SQL Server became 11 GB. Is this a better indicator of the production environment rather than the 1.5 MB?

3. Data from SAN to SQL Server became 11 GB. And I wonder why SQL Server needs to fetch that much data from disk even though it is only returning 71 MB back to the IIS Server. Since I am using appropriate indexes, I assume SQL Server shouldn't be caching a lot of pages.

4. ldf size increase in cache disabled case is almost 8 times the increase in the cached case. Why is this?

Your tests are flawed.  Your data sizes are too small to produce any meaningful results.  CHECKPOINT/DROPCLEANBUFFERS after every query is giving results that would never correlate with reality.

As for your questions:

Assume a simple query run in a loop:

UPDATE questions SET up_votes = up_votes + 1 WHERE id = @id

This query will generate a small transaction log entry for each query, and dirty the page it hits.  If you update the same row 1000 times, you could generate 1000 small transaction log entries with one 8k write (at some point later).  If you issue a CHECKPOINT, you will force the dirty page to be written to disk after every query (at least 1 additional 8k write, maybe more depending on indexes).  The DROPCLEANBUFFERS will force the next query to issue multiple 8k reads. (the data, the intermediate index levels, GAM pages, SGAM pages, PFS pages, etc, etc, etc)

No.  Even if you have a 500 GB database and 8 GB of RAM, a lot of meta-data and intermediate level index pages will remain in memory and are critical for performance.
See above about GAM/SGAM, etc.
CHECKPOINTS will generate a trivial amount of additional log activity.  You are still squarely in the "background noise" range with your data volumes to evaluate log growth in any meaningful fashion.

As an analogy, It is like attempting to measure the gas mileage in a vehicle just by backing out of the driveway.

If you are concerned with SAN performance, then look into Microsoft's DiskSpd.exe tool (formerly SQLIO).  The documentation has sample configurations that relate to known SQL Server access patterns.


How can I Automate MongoDB schema design from existing MySQL Database and Logs? I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your thoughts,some links or references.

1) What are the MySQL parameters which can help me to automate the process?

2) What are the criteria to design the idle MongoDB database? What I should keep in mind while designing MongoDB database? Example MongoDB schema for any MySQL database would be great help.

3) How denormalizing the database will help?

4) Is there any existing opensource application/tool which can help automation process?

I would not recommend doing automatic migrations from any relational databases (or other that differs in logic) to MongoDB. Of course it can be done, but the database performance might not be (and will most probably not be) good.

Now, answering your questions directly:

1) what do you mean by MySQL parameters?

2) Check the official docs page about the topic: Data Modeling in MongoDB

3) This question has already been answered in StackOverflow - Embed vs Reference.
Nevertheless, here is where automatic migration rules will most probably fail. You need to decide this based on your use cases and necessities. Also, be aware that mongoDB doesn't support joins (meaning you will have to do it manually on the client side).

4) Only know MongoHub - it has an import/export tool that supports mysql


Datediff SECOND with DATETIME type and rounding returns questionable results I am puzzled by the following behaviour in SQL Server (tested on SQL Server 2017).

The goal is to return the number of seconds between two different datetime values. The two datetimes typically lie pretty close to another (differences measured between 0.4 and 2 seconds).

I started with the simplistic expression DATEDIFF(SECOND, startDateTime, endDateTime) and assumed that it might return 0 seconds for up to 499 (in fact due to precision 497) milliseconds and round up to 1 second for values starting at 500 milliseconds.

I read the MSDN Documentation but didn't get any wiser. It mentions something about "datepart boundaries" stating that "Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). " I have milliseconds in difference. Does that also qualify as "being adjacent"?

However this is not how it works. It seams that if a boundary to the next minute is crossed the value of DATEDIFF(SECONDS...) always yields 1 while for the same interval all within one minute the seconds yield 0. Please have a look at rows with id 8 and 9 as well as 10 and 11 in the following result set:

Here's the code for the example:

WITH cteValues AS
(
    SELECT
        id,
        CAST(startDateTime AS DATETIME) AS startDateTime,
        CAST(endDateTime AS DATETIME) AS endDateTime
    FROM
    (
        VALUES
        (   1,'20200202 23:56:19.737',  '20200202 23:56:21.723'),
        (   2,'20200202 23:56:25.690',  '20200202 23:56:27.297'),
        (   3,'20200202 23:56:31.453',  '20200202 23:56:32.753'),
        (   4,'20200202 23:56:36.940',  '20200202 23:56:37.533'),
        (   5,'20200202 23:56:37.353',  '20200202 23:56:37.927'),
        (   6,'20200202 23:56:38.653',  '20200202 23:56:39.213'),
        (   7,'20200202 23:56:42.630',  '20200202 23:56:43.163'),
        (   10,'20200203 06:34:50.900', '20200203 06:34:51.363'),
        (   8,'20200203 06:34:50.100',  '20200203 06:34:50.600'),
        (   9,'20200203 06:34:50.800',  '20200203 06:34:51.300'),
        (   11,'20200203 06:34:50.100', '20200203 06:34:50.563')
    ) t (id,startDateTime, endDateTime)
)
SELECT
    id,
    startDateTime,
    endDateTime,
    CAST(endDateTime-startDateTime AS TIME) AS resultCast,
    DATEDIFF(SECOND, startDateTime, endDateTime) AS resultDatediffSeconds,
    ROUND(DATEDIFF(MILLISECOND, startDateTime, endDateTime) / 1000.0, 0) AS resultSecondsExtractedFromMilliseconds,
    DATEDIFF(MILLISECOND, startDateTime, endDateTime) AS resultDatediffMilliseconds
FROM
    cteValues
ORDER BY
    resultDatediffMilliseconds DESC

Play for yourself in this SQL Fiddle.

Why is that so? It is astonishing that the same command with the MILLISECOND option returns a precise amount of seconds but with the SECOND option it does not.

Which way would you recommend to write the second calculation if one cares about accurately rounded results and is open for the best performing solution?

Think of DATEDIFF as a truncation and a subtraction.

First the given datetimes are truncated to whatever datepart is chosen. In your case this is SECONDS. So '20200203 06:34:50.100' becomes '20200203 06:34:50' and '20200203 06:34:50.600' becomes '20200203 06:34:50'. Subtract these and the difference is zero - there are zero second-sized time boundaries between the two given inputs.

Note that this is truncation and not rounding, and that the answer depends on the granularity of the datepart chosen. It can be confuing. The difference between '20191231 23:59:59' and '20200101 00:00:00' using YEAR, MONTH, DAY, HOUR, MINUTE and SECOND are all 1.

If you want to report to a precision of one tenth of a second you must calculate to the next most precise datepart (millisecond) and ROUND() the result.


Why only ~half of RAM is used by Oracle on Linux server? Why Oracle 11.2 would be using only around half of available RAM on RHEL5 box? I have 64GB but 32GB is free which really annoys me. Any memory not used is wasted memory in my books - especially on Linux boxes.

Unfortunately I have no DBAs available currently so can't ask them...

host-rac-1|root[/root]# free -m
             total       used       free     shared    buffers     cached
Mem:         64455      34021      30434          0       1014       2958
-/+ buffers/cache:      30048      34407
Swap:        16415          0      16415
host-rac-1|root[/root]#

/dev/shm only used in 1%:

host-rac-1|root[/root]# df -m |grep tmpfs
tmpfs                    40960       254     40707   1% /dev/shm
host-rac-1|root[/root]#

meminfo:

host-rac-1|root[/root]# cat /proc/meminfo
MemTotal:     66002520 kB
MemFree:      31231544 kB
Buffers:       1039520 kB
Cached:        3022168 kB
SwapCached:          0 kB
Active:        5520172 kB
Inactive:      1245240 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     66002520 kB
LowFree:      31231544 kB
SwapTotal:    16809976 kB
SwapFree:     16809976 kB
Dirty:            4452 kB
Writeback:           0 kB
AnonPages:     2754560 kB
Mapped:         346652 kB
Slab:           429768 kB
PageTables:     122116 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  36169508 kB
Committed_AS:  5439032 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    366812 kB
VmallocChunk: 34359371495 kB
HugePages_Total: 13322
HugePages_Free:    500
HugePages_Rsvd:    491
Hugepagesize:     2048 kB

Snippet from init.ora:

###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
db_unique_name=DBNAME
diagnostic_dest=/orabase/dbhome_1
memory_target=13497270272

###########################################
# Processes and Sessions
###########################################
processes=400
sessions=445

If there is anything I could add to improve this questions please let me know.

Normally we allocate about half of the systems memory for shared_pool and database buffers, preferably mapped on huge pages. AMM is not compatible with huge pages so when a dba is working with the system he would disable that and manage the memory manually. Define a minimum size for the database buffers and also for the shared pool.
The rest of the memory is available for processing things like sorts and merges.
Maybe the workload of your system is low and could do with a smaller footprint. I also noticed this is a RAC system. RAC likes to have some free memory.
So as a rule of thumb your system is setup pretty much default and could be adjusted to better suit your workload. The exact sizing depends on the usage of the shared_pool and how much caching your need to give a usable performance. At a certain point performance is not improved by giving more memory.


How do I query for system views in a SQL Server database? I have some queries that run against Query Store, the view sys.query_store_wait_stats is not in SQL Server 2016, but it is in SQL Server 2017.  I want my queries to work in SQL Server 2016 and SQL Server 2017+.

To accomplish this I used IF EXISTS as below

EXEC sp_query_store_flush_db;

SELECT * INTO Admin.dbo.query_store_runtime_stats FROM sys.query_store_runtime_stats;
SELECT * INTO Admin.dbo.query_store_runtime_stats_interval FROM sys.query_store_runtime_stats_interval;
SELECT * INTO Admin.dbo.query_store_plan FROM sys.query_store_plan;
SELECT * INTO Admin.dbo.query_store_query FROM sys.query_store_query;
SELECT * INTO Admin.dbo.query_store_query_text FROM sys.query_store_query_text;
IF EXISTS(select * FROM sys.views where name = 'query_store_wait_stats') -- View not in SQL Server 2016
    Begin
    SELECT * INTO Admin.dbo.query_store_wait_stats FROM sys.query_store_wait_stats;
    End
SELECT * INTO Admin.dbo.query_context_settings FROM sys.query_context_settings;

Which would seem like a good solution, EXCEPT that, the below does not return any results on SQL Server 2017 database with Query Store active

select * FROM sys.views where name = 'sys.query_store_wait_stats'

Microsoft has this answer: How do I find all views in a database?

USE <database_name>;
GO
SELECT name AS view_name
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
  ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
  ,create_date
  ,modify_date
FROM sys.views;

But it only returns user views, NOT system views.

A couple of simple tests also do not return the system view, which is present. You can try them without a WHERE and not get any system views.

select * FROM sys.views where name = 'sys.query_store_wait_stats'

SELECT *
   -- DISTINCT NAME
FROM SYS.OBJECTS
WHERE TYPE IN ('U','V')
AND NAME= 'query_store_wait_stats'

I could test for SQL Server version, but If the view gets added to SQL Server 2016 in a Service Pack, I want my query to have only checked for the presence of the view.

How do I find (or test for) the presence of a system view?

It's not just if objects exist as a whole, you also have to make sure columns line up. Microsoft adds new stuff, and sometimes backports it. It's nice not to rely on version checking, or worry about it failing if someone hasn't rebooted after patching.

As a related example, when dm_exec_query_stats got information about tempdb spills and I wanted to show those columns, I used code like this to test for them.

DECLARE @tempdb_spills BIT = 0;

IF 4 = (   SELECT COUNT(*)
           FROM   sys.all_columns AS ac
           WHERE  ac.name IN ( 'total_spills', 'last_spills', 'min_spills', 'max_spills' )
           AND    OBJECT_NAME(ac.object_id) = 'dm_exec_query_stats' )

SET @tempdb_spills = 1;


Unable to find Audit specification when I try to create Server level audit I am trying to use 'SERVER_PRINCIPAL_CHANGE_GROUP' audit action on a SQL Server to capture the sp_addlogin events. But when I try to create a Server level audit using GUI, I am not seeing the Audit action type drop down when I try to create the audit. Any help on how to do this ? I just want to capture who added a server level login using the SQL Server audits.

We can't troubleshoot pointing and clicking through a GUI, because we don't know exactly what you did or what you might have missed.

You need to create a server level audit (which is like a container for specific audit definitions) and a server level audit specification (which is the part that dictates the auditing).

I suggest looking at the documentation for examples using DDL (e.g. CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION). This wasn't too hard to work out:

USE master;
GO

CREATE SERVER AUDIT ServerAudit
TO FILE
(
    FILEPATH = N'C:\temp\',
    MAXSIZE = 0 MB,
    MAX_ROLLOVER_FILES = 2147483647,
    RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE,
    AUDIT_GUID = '0da5fd7b-8218-486f-8641-c31465cae657'
);
GO

ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION [AuditPrincipals]
FOR SERVER AUDIT [ServerAudit]
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);
GO

So then I did this:

CREATE LOGIN floob WITH PASSWORD = 'blat', CHECK_POLICY = OFF;

And I saw this in the audit log:


SQL Server: Clarity on which service user does what (backups, etc) RE accessing network shares To backup / restore to or from a network share, either the SQLServer or SQLAgent user needs access to that share.

Which is it?  SQL itself or the agent?

And then how?

(I wanted to create the SQLServer user on a remote system, but did not know the pw for the SQLServer user on the db server itself... so I went to set the pw on the db server SQLServer user... and was not allowed to, even though I am a member of administrators...)

What am I missing here?

Scenario:

I have OLD-DB and NEW-DB servers.
I wanted to do a backup on OLD-DB   to \new-db\incoming-logs\
I set up the SQLAgent user on new-db, with
same pw as that user on old-db... but it failed, with error that
cannot access that share.

So now I have a backup in \old-db\backups\  and would like to restore directly from there (rather than copying).... AND I need to set up log shipping between these two systems.

NOTE: Active Directory / domain users are NOT available. This is plain jane, two stand alone servers.

Thank you for the missing piece. I know I am close!

The Service Account for SQL Server needs to be granted access to the network share in question.

This indicates a need for you to configure SQL Server to start with a Windows Active Directory account.  If you don't have access to a domain account, you will need to create an account on the SQL Server machine with a matching account name and password on the target network machine.

Use SQL Server Configuration Manager to modify the startup config of the SQL Server Service.

The only way to allow a service on one machine to access a network share is to provide network access with a username and password.  Make sure the SQL Server service can access the network share before restarting SQL Server by logging onto the SQL Server machine using the same username and password, and confirm you can see the network folder.

You also need to ensure you aren't trying to restore from a "mapped" network drive, since the "mapping" won't necessarily be there for the SQL Server service to "see".  Instead of using a mapped drive letter for the restore, use a UNC name, such as \\server\share\folder\backup.bak


Units of measurement in the database I have a table of products that can be measured by weight and size, and the user can enter which unit he wants to use for the measurement of any product. In the future he might need to see these measurements converted to different units.

I've looked up existing questions about this, and imo they had some complicated solutions using a table for the units and another table for converting from unit to unit, but I don't think I need all that.

My idea so far is to always store the measurement in the smallest unit I'll use (millimeter for distance and milligrams for weight) alongside with a column that tells in which unit this measurement should be displayed, that way I can even make calculations in the db queries, and in the GraphQL API layer or event in the client I'd convert the measurement the desirable unit.

What are the pitfalls to this approach?

The problem is that you should not allow free-form values for the units, but have a second table of units and reference that with a foreign key:

CREATE TABLE unit (
   unit_id bigint PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE measurement (
   measurement_id bigint PRIMARY KEY,
   unit_id bigint NOT NULL REFERENCES unit,
   value double precision NOT NULL
);

That guarantees that you can only store existing units in your measurement table, which is good for data quality (and it is not much extra effort).

Otherwise you can end up with entries like cm, centimeter, centimeters and Zentimeter all meaning the same thing.


Is there any impact whatsoever of enabling FILESTREAM on active SQL Server? I may have to enable Filestream on a live SQL Server at work, but first need to know about the possible impact/implications of doing so on the performance/operation of the databases held therein.

I have done some reading and have not found anything daunting, but since it's fairly important, thought I had better check on here.

Enabling filestream by it self will not have any impact on the databases not using filestream. Queries on filestream data will be done using system cache not buffer cache so you might need to make sure that the SQL server service is not using all availible resources, see http://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html


SQL Server turn Series into a Range Has anyone found a great way to convert individual rows to date ranges for a specific value. In this case, I'm looking to get a date range for each code in my given table.

Date        Code
1/1/2017    F
1/2/2017    F
1/3/2017    F
1/4/2017    R
1/5/2017    P
1/6/2017    P
1/7/2017    P
1/8/2017    P

My result should be similar to the following.

From        To          Code
1/1/2017    1/3/2017    F
1/4/2017    1/4/2017    R
1/5/2017    1/8/2017    P

Assuming there are no gaps in the range of dates for a particular code, a simple aggregation query would solve this:

DECLARE @TestData TABLE (
    DateInfo DATE
    ,Code VARCHAR(1)
    )

insert into @TestData (DateInfo,Code)
values
('1/1/2017',    'F'),
('1/2/2017' ,   'F'),
('1/3/2017' ,   'F'),
('1/4/2017' ,   'R'),
('1/5/2017' ,   'P'),
('1/6/2017' ,   'P'),
('1/7/2017' ,   'P'),
('1/8/2017' ,   'P')

;
WITH TestData
AS (
    SELECT min(DateInfo) AS 'From'
        ,max(DateInfo) AS 'To'
        ,Code
    FROM @TestData
    GROUP BY code
    )
SELECT convert(VARCHAR(10), [From], 101) AS 'From'
    ,convert(VARCHAR(10), [To], 101) AS 'To'
    ,Code
FROM TestData
ORDER BY [From]

If the dates for a particular 'code' in your example are not consecutive, then you have what is known as a 'Gaps and Island' problem.  I took this example and tweaked it for your situation.  I added a new row for code 'F' with a date of 1/9/2017 so you could see how the same code could have multiple date ranges.

DECLARE @TestData TABLE (
    DateInfo DATE
    ,Code VARCHAR(1)
    )

insert into @TestData (DateInfo,Code)
values
('1/1/2017',    'F'),
('1/2/2017' ,   'F'),
('1/3/2017' ,   'F'),
('1/4/2017' ,   'R'),
('1/5/2017' ,   'P'),
('1/6/2017' ,   'P'),
('1/7/2017' ,   'P'),
('1/8/2017' ,   'P'),
('1/9/2017' ,   'F')
;
;

WITH mycte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY code ORDER BY [DateInfo]
                ), [DateInfo]) AS grp
    FROM @TestData
    )

--select * from mycte       --Uncomment to see the data from mycte

SELECT Code
    ,min([DateInfo]) AS [From]
    ,max([DateInfo]) AS [To]
FROM mycte
GROUP BY Code
    ,grp
ORDER BY [From]

The ROW_NUMBER() window function in this example creates 'partitions' of each code and assigns a row number for each row in each 'partition'.  A new 'code' starts the row number back to 1 for that partition.

The DateAdd logic takes the source date from your table, subtracts 'its' row number as 'days' and creates a 'grp' column that we can use to GROUP BY when we select from mycte.

To get a better understanding of what the code with the ROW_NUMBER() window function is doing, you can uncomment the line that says uncomment to see mycte and just run down the that select statement.  It returns the following result - (Notice that the unbroken date sequences for each 'code' have the exact same 'grp' data.)

Now, it just a matter of selecting the MIN as [From] and MAX as [To] for each 'grp' and order appropriately to give you the final result.


Fill-in Missing Dates with calendar table Hi i want the output to look like this. Filling the missing dates from the DATE_TBL. I tried UNION but no luck.

From this table: Log_table:

and DATE_TBL

FROM DATE_TBL
LEFT JOIN LOG_TBL USING(date)


Data warehouse staging architecture This is a question about data warehouse design. We are setting up a healthcare datawarehouse and starting with 2 major source systems that combine for about 20,000 tables and 2 TB of data. 1) It is highly dimensional data 2) We don't wan't to heavily effect OLTP systems

We have chosen an incremental Kimball design. My question is, should all of the data be staged, then sorted into inserts/updates and put into the data warehouse. Then the staging data would be cleared for the next incremental load.

This leaves you with 1 copy of the data.

The other method would be to incrementally load it into staging, sort it into inserts/updates and store it in the same format as the source systems. Then we would combine data from the source systems into the datawarehouse from the full copy.

This would essentially leave you 2 copies of the data, one in the form of the source systems and 1 loaded into the actual datawarehouse.

What is the best practice for this? I originally thought it would be best to only store the copy in the data warehouse and clear the source tables each load.

However, in that case if you ever have to go back to an existing dimension and add a column you would have to re-load all the dependent source tables. Plus you would lose history?

It just seems really inefficient to store it twice though....just wanted some thoughts on the design, your experiences and best practice.

Personally i have staging tables for extract, transform and persistent data storage.

Whether you do full exports or incremental loads will depend on what tools you have, your strategy and whether your app schema and data support it. Sometimes you cant avoid full exports.

Adding a column to a dimension isnt a big deal, but backfilling historic data could be very difficult or may not be possible at all. Trying to reconstruct how an app looked at a point in time retrospectively would be a major undertaking. You would need a very good case to justify that.

All of the things you mention are possible, but only you can decide if the cost/benefit is worth it.


SQL Server Management studio slow connection or timeout when using Windows Authentication I'm getting extremely long delays (10~30 seconds) in SQL Server Management Studio 2014 when attempting to connect to a SQL Server 2012 instance over TCP using Windows Authentication. This happens when connecting Object Explorer or a new blank query window. Once connected, running queries is fast. The problem does not happen when I connect using SQL Server authentication.

Environment:

Windows 7, logged in as a domain user
TCP connection via IP address (not hostname)
The server is at a remote location connected via VPN
No encryption

When I logged into a co-worker's Windows 7 computer with my domain account, and connected to the same SQL Server through the same VPN, there was no delay. When the same co-worker logged into my PC with his own domain account, he experienced the delay. These tests show that the problem is unique to my PC. Also, the problem only appears when connecting to this specific SQL Server and VPN; I can connect to other SQL Servers on the local network via Windows Authentication without any delay.

Things I've tried with no success:

Disabled anti-virus and firewall
Renamed the "12.0" folder under "%userprofile%\AppData\Roaming\Microsoft\SQL Server Management Studio" to "_12.0" to force SSMS to recreate my user settings.
Force Network protocol to TCP rather than <default>. I also tried Named Pipes but my server isn't setup for that.
Installed SSMS 2012 and tried that instead of 2014.
Disabled IPv6
Blackholed crl.microsoft.com to 127.0.0.1 in my etc\hosts file.
Disabled the Customer Experience Improvement Program in SSMS, Visual Studio, and Windows.
Uninstalled all SQL Server related apps from my PC and reinstalled just 2012.

TCPView clues:

Using TCPView, I noticed that when I make a new connection, its state becomes ESTABLISHED right away, but then one or two more connections with the SQL Server are continually attempted and closed with TIME_WAIT. On my co-worker's computer, these connections are ESTABLISHED and solid. So I'm pretty sure this is the source of the timeouts, but what are the connections for, and why do they fail? (I don't have any addons in my SSMS.)

Any ideas?

Update: Intellisense/Autocomplete clue(?):

I noticed that once I finally do connect, Intellisense/Autocomplete doesn't work. Do those require separate connections from SSMS? I tried disabling them, and it didn't seem to resolve the long connection delay.

Try running a trace with SQL Profiler while you, and then your coworker, connect to the server.
Select RPC, SQL Statement & PreConnect - Starting/Completed.
Select Save Results To Table option, then compare the 2 tables to find the bottleneck.

Or, since you're connecting by IP, it could be doing a Reverse DNS lookup. If so, add a entry in your hosts file.


Oracle database 12c upgrade to 19c I was asked to advise some business analyst on migration from Oracle DB 12c to 19c. The thing is I have never used or done anything related to Oracle DB. From my experience with MSSQL during upgrade I know that some features became deprecated hence it required a bit of manual work for example re write custom made stored procedures, there were some issues with indexes and etc..

The good thing with MSSQL was it told you before upgrade what issues you will encounter and what is deprecated. Can you do check it similarly in Oracle Db aswell?

Also, please share your upgrade stories, did it went smooth? Did you encountered any issues afterwards? What should be kept in mind (that might break after upgrade) before upgrading? Also I would appreciate any related articles/videos/etc...

Upgrading 12c to 18c or 19c should not be a problem. I don't think that 19c is that different from 12c. The dbua tool should be able to do the upgrade. You still need to test with a test database. But running the dbua tool should work. Going from pre-12c to 19c is a much bigger issue depending on what features are used.


Clear cache in Windows I would like to make some timing tests in my PostgreSQL database. I would like to clear cache every time I run a query to disable influence of caching on my timing results. I've read some topics about clearing that cache in Linux, but not found how to do that in Windows. Could you help me please?

OS: Windows 10.
PostgreSQL: 9.6.0.

There's no way to clear the caches easily. On Linux you can stop the database server and use the drop_caches facility to clear the OS cache; be sure to heed the warning there to run sync first.

There is also a presentation from 2ndquadrant that shows Inside the PostgreSQL Shared Buffer Cache. It outlines the following points:

Windows does not perform well with large shared buffers
settings. Effective maximum size is around 10,000-50,000
buffers
I This talk is more appropriate for UNIX-like platforms; can’t
really take full advantage of the buffer cache on Windows

Digging deeper into this, I found an open source project that clears the cache. I don't see many people forking or using this project though. The safest way is to just restart the server since there is no way to bypass or flush the database's cache. But feel free to look at the code and see if it will work for you.

You can see all prepared statements available in the session by querying the pg_prepared_statements system view. This will allow you to see if the plan cache was cleared. You might even be able to get away with a DEALLOCATE statement. This might be what you are looking for...


MySQL: Converting Character set fails? I'm in the progress of migrating my database from latin1 to utf8 character sets/collations.

I wrote a simple script that loops through all of my tables and executes this statement:

  ALTER TABLE {database}.{table} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

This works great for almost all of the tables, but I noticed there are a few tables that fail to convert - the ALTER reports successful and there were no errors, but the collation remains as latin1.

Upon further inspection, I noticed that this particular table only has Integer columns, so perhaps this is the reason? But shouldn't that still at least change the table collation in the event I add VARCHARs later?

** edit **

I used phpMyAdmin's UI to convert the table and it gave me a slightly different command:

  ALTER TABLE {table} DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

What is the difference between these to statements; why would one work and not the other?

The ALTER TABLE ... DEFAULT CHARACTER SET ... command sets the default character set and collation for the table. It does not affect the existing VARCHAR/TEXT columns and their data. Only newly created fields will use the specified charset and collation.
The ALTER TABLE ... CONVERT TO CHARACTER SET ... command changes the character sets and collations of all the existing VARCHAR/TEXT fields (and converts the data). It actually should change the default charset for the table as well. It is not specified obvious enough here: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html but it works in that way. On my MySQL 5.1.45 the default charset is changed by the CONVERT operation even it does not include VARCHAR-fields. Also the CONVERT operation may change the datatype if it cannot store the new length of data.


Preserve CASE WHEN output as new column in MySQL I am new to MySQL and this question may look silly, but:
I have a dogs table and I categorize their size by their weight:
SELECT *,
    CASE
        WHEN weight < 15 THEN 'Small'
        WHEN weight < 40 THEN 'Medium'
        ELSE 'Big'
    END AS Size
FROM dogs;

This outputs my table with a new column Size at the end.
And I would like to permanently append that column to this table so it's visible when I use SELECT * FROM dogs;

ALTER TABLE dogs
ADD COLUMN Size VARCHAR(6) AS (CASE WHEN weight < 15 THEN 'Small'
                                    WHEN weight < 40 THEN 'Medium'
                                    ELSE 'Big' END) VIRTUAL

MySQL 8.0 Reference Manual  /  ...  /  CREATE TABLE and Generated Columns


Migration to new database We have a weird situation. We have an old database and new database both different schemas (SQL Server). We are using abinitio to replicate data from old to new.

Now if for some reason the new structure does not work for us, we want a rollback plan to copy data from new to old. But this could also mean records that are updated in new and also inserts.

Also if this has to be done with no downtime to customer, how can we achieve this?

It's not a weird situation at all - lots of project managers say they want this until they discover just how hard it is. This is way, way harder than it sounds. Let's take a simplified scenario to illustrate it:

CustomersV1 table, original bad design you're moving away from:

CustomerID uniqueidentifier
CustomerName varchar(100)
BalanceOutstanding - decimal, total amount they owe us

CustomersV2 table, new design:

CustomerID - integer, autonumbered identity field
CustomerName nvarchar(100) - because now we want to store unicode data
(No BalanceOutstanding field - instead we're going to have the app calculate current balance by querying several other tables)

Moving from CustomersV1 to CustomersV2 is hard enough, but how do you deal with:

Keeping a mapping of the old CustomerID to new CustomerID
What happens if a customer puts unicode data in the new CustomersV2.CustomerName, which simply can't be sent back to CustomersV1.CustomerName due to the mismatched data types
Changing application code to refer to BalanceOutstanding gathered via different ways

In reality, I've been on several projects where managers wanted to accomplish something like this, but once we even white-boarded out the changes to just one table, they realized we couldn't keep them both in sync at a price that the customers were willing to pay.

I have been on one project where the customers did pay, and here's how they did it:

Built custom SSIS packages to keep the data in sync in both directions
Built complex business rules to handle scenarios like we discussed above (what happens if someone stores data in the new schema that the old schema won't handle - in that case, the customers were willing to simply walk away and lose that data)
Tested migrations in both directions, repeatedly, leading up to go-live
Froze the database schemas on both sides months in advance to prevent changes to the complex SSIS packages

So to start the discussions with your management, lay out the differences in just one of the complex tables, and white-board out what would happen if they had to fail back from the new to old schemas. Talk about the ways you could lose data, and how much work it would be to build apps that would migrate the data back and forth.


Setting up Power BI to schedule publishing from PostgreSQL My company suddenly switched to Power BI as a corporate visualization tool but I have a large PostgreSQL database/infrastructure in place.

I was able to download PostgreSQL drivers and connect to my data, but I am not seeing a way to schedule refreshes/publishing. If I go to the Power BI 'cloud' portal I see that the cloud can connect to Azure SQL Database, Azure SQL Data Warehouse, SQL Server Analysis Services, and Spark.

Can I configure the Power BI cloud to refresh data from my local PostgreSQL database? Or, if I create a Linux VM on Azure and install PostgreSQL, will I be able to schedule refreshes there? I just want to add the dashboards which update every 30 minutes so an end user just needs to refresh their browser. Or do I need to pipe data from PostgreSQL to a Microsoft DB?

I would much rather be able to use PostgreSQL if possible, and I would like to have Linux as well as the server OS because I am using Python Airflow to schedule tasks (don't think it works with Windows).

You should be able to do this using the Power BI On-premises data gateway.  You'll need to install it on a Windows computer, along with the Postgres ODBC driver, but it shouldn't have any problem talking with your Postgres install on Linux.


Meaning of various user account privileges in phpMyAdmin Reference the attached screenshot:

I created a non-root user, and gave it all privileges to a database. It now has All Privileges of Type -- global and database-specific. What's the difference between these 2 "Types"?
Why three root User names? What's the purpose of having so many?

Your second question was well covered by joanolo.
I would like to address your first question only.
On Apr 17, 2014, I answered this question : How to grant super privilege to the user?. In that post, I explained how the SUPER privilege was impossible to grant to a database-specific user. I clarified the difference between global privileges and database-specific privileges by comparing the table structure of mysql.user and mysql.db.
To show the difference between them in terms of grants side-by-side, please run this query:
SELECT
REPLACE(glb,'_priv','') global_privilege,
IFNULL(REPLACE(dbs,'_priv',''),'NOT ALLOWED') database_privilege
FROM (SELECT A.column_name glb,B.column_name dbs FROM
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user'
AND column_name LIKE '%priv') A
LEFT JOIN
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='db'
AND column_name LIKE '%priv') B
ON A.column_name=B.column_name) AA;

When you run this in MySQL 5.7.12, you get the following output:
+-------------------+--------------------+
| global_privilege  | database_privilege |
+-------------------+--------------------+
| Select            | Select             |
| Insert            | Insert             |
| Update            | Update             |
| Delete            | Delete             |
| Create            | Create             |
| Drop              | Drop               |
| Grant             | Grant              |
| References        | References         |
| Index             | Index              |
| Alter             | Alter              |
| Create_tmp_table  | Create_tmp_table   |
| Lock_tables       | Lock_tables        |
| Create_view       | Create_view        |
| Show_view         | Show_view          |
| Create_routine    | Create_routine     |
| Alter_routine     | Alter_routine      |
| Execute           | Execute            |
| Event             | Event              |
| Trigger           | Trigger            |
| Reload            | NOT ALLOWED        |
| Shutdown          | NOT ALLOWED        |
| Process           | NOT ALLOWED        |
| File              | NOT ALLOWED        |
| Show_db           | NOT ALLOWED        |
| Super             | NOT ALLOWED        |
| Repl_slave        | NOT ALLOWED        |
| Repl_client       | NOT ALLOWED        |
| Create_user       | NOT ALLOWED        |
| Create_tablespace | NOT ALLOWED        |
+-------------------+--------------------+
29 rows in set (0.04 sec)

Given this display, it is impossible to grant RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, and CREATE TABLESPACE to a database-specific user.
For example, you don't want a database-specific user to

Shutdown the Database
See the processlist of other users
Setup Replication on their own
Kill DB Connections
Create a New User
Create Giant Tablespace Files
Load an External File into the Database
Create External Files in the OS using SELECT ... INTO OUTFILE
and so forth

SUPPLEMENTAL INFO
There are also table-specific grants in mysql.tables_priv and column-specific grants in mysql.columns_priv. These grants are stored in ENUM columns.
Just run DESC mysql.tables_priv; and DESC mysql.columns_priv; and see.


How to connect client applications to SQL SERVER 2012 cluster? Following are the environment Details :

Database Server : SQL SERVER 2012
Operating System : Windows Server 2012

Single instance of SQL SERVER 2012 is installed in cluster configuration with 2 nodes. I have 3 IP addresses, one for each node of SQL SERVER and one for the cluster.

I am able to connect to this configuration by providing SQL SERVER instance name to the DataSource of connection string but i am unable to connect using cluster name as data source

so the following is working

<add name="Default" connectionString="Data source=SQLInstanceName;Initial Catalog=DatabaseNAme;User Id=userid;Password=password;Connection Timeout=120;" providerName="System.Data.SqlClient" />

but changing SQLInstanceName to IP address/Name of cluster does not work

<add name="Default" connectionString="Data source=clustername;Initial Catalog=DatabaseNAme;User Id=userid;Password=password;Connection Timeout=120;" providerName="System.Data.SqlClient" />

Is this by Design?

My understanding is that client application needs to connect to cluster and not to specific instance.

Neither of those should work. If it's a named instance, it should be:

DATA SOURCE=ClusterName\SQLInstanceName;

If it's a default instance, just:

DATA SOURCE=ClusterName;

You are always connecting to an instance of SQL Server, whether it is clustered or not. The cluster will know which node in the cluster is currently hosting the instance, and this should not really be a concern of yours, at least in terms of the connection string - that's kind of the whole point, a failover to a different node in the cluster should not require your application to change anything.


Differential Backup since a date Hi is it possible to do a differential backup in ssms 2014, so that it gets all changes since a specified date?

Differential backups are backups of any changes to the DB since the last full backup. So in theory, yes, you could have it do a diff backup since some date (if you did a full backup on that date). Otherwise, no. It'll always be since the last full backup.


SQLAnywhere: How to write result of a query into a file with non-interactive session I'm looking for a way to write resultset of a query to a file maybe analog of queryout of MS SQL or the COPY from PostgreSQL

When using something like

select count(*) from "table" OUTPUT TO 'results.txt' FORMAT TEXT

I'm getting

  Could not execute statement. Syntax error near 'TO' on line 1
  SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1

When running it, as discribed in documentation like this:

select count(*) from "table";
OUTPUT TO 'results.txt' FORMAT TEXT

e.g. inside a procedure, I'm getting an error telling me that there is no result for being outputted.

You can specify the OUTPUT to a file in your SQL directly:

dbisql -c "connection-string" SELECT * FROM table; OUTPUT TO 'results.txt' FORMAT TEXT

As per the Sybase documentation.


Modify the column names of database tables to fit my app's technical limitations? I work in a company which works on gigantic (let's say hundreds of gigabytes) databases delivered by 3rd parties. Further, I have to create a data base connection from within a software (that is, an application program) used by the company to load and visualize the data.

The problem

The database has column names character lengths of 32+, but the software app only supports column names lengths of up to 31. So these tables containing the columns can not be loaded, and I can not really work with the database.

The questions

Should I touch the database and shorten the column names everytime it is necessary (for updates etc..) or should I rather push the company to use software which supports longer column names ?

On the one hand, I think it is very ugly to touch the raw data, on the other hand the app licenses the company already bought were very expensive and just switching the sotware might not be such a popular move...

Or does it depend on the particular case? Would the number of affected columns or the price of the software correlate in any way with the decision I would have to make?

Or is there an even better solution for this?

Build Views on top of the tables.  Every RDBMS should have this functionality as VIEWS are part of the SQL Standard.
 A View will allow you to call the columns whatever you so choose without having to change the underlying structure provided by the 3rd parties you say are providing the databases.  Views may also allow you some flexibility to standardize names and maybe even data types to your liking.  Finally, if you get additional data from the 3rd parties that can easily be loaded into the current structures, this will minimize any rework needed for future ETL processes.


sp_Blitz "Too Much Free Memory" on seldom-used SSRS instance sp_Blitz (20160715) is reporting "Too Much Free Memory" on a seldom-used 2012 SP2 CU12 test instance (Developer Edition) that only contains the databases created and used by SSRS. The Details are:

  0GB of free memory inside SQL Server's buffer pool, which is 0GB. You
  would think lots of free memory would be good, but check out the URL
  for more information.

I modified the query to return the raw values and got Free = 587,896 and Total = 1,052,256. My altered query:

SELECT Free = cFree.cntr_value, Total = cTotal.cntr_value
FROM sys.dm_os_performance_counters cFree
INNER JOIN sys.dm_os_performance_counters cTotal ON cTotal.object_name LIKE N'%Memory Manager%'
    AND cTotal.counter_name = N'Total Server Memory (KB)                                                                                '
WHERE cFree.object_name LIKE N'%Memory Manager%'
    AND cFree.counter_name = N'Free Memory (KB)                                                                                '
    AND CAST(cTotal.cntr_value AS BIGINT) * .3 <= CAST(cFree.cntr_value AS BIGINT)
     AND CAST(SERVERPROPERTY('edition') AS VARCHAR(100)) NOT LIKE '%Standard%'

dbo.sp_BlitzCache (@BringThePain = 1) displays 316 rows, all apparently built-in SSRS housekeeping statements. Is it more likely that:

I have a problem as described at
https://www.brentozar.com/blitz/too-much-free-memory/, or
The WHERE clause of the sp_Blitz query needs tweaking to avoid a false positive in this circumstance

I logged a bug for it over on the SQL Server First Responder Kit repository on Github. You can check it for status and also add to it as needed.


SQL Server 2008R2 Full Text Catalog Missing Records I create one simple Full Text Catalog on a varchar(500) filed, which mainly store address text like 24 King Street Melbourne VIC 3000. The catalog is set to auto track changes. But I found there are always a few records in the table are not indexed by Full Text Catalog after I fully populate it.

For example, the table has 10,000 records, but the full text has only 9,997 records. So there are three records are not indexed. I rebuild the full text and all records are indexed. But after user start to insert data, there are missing records again. Table records increased to 11,000. But the full text records only have 10,999. Any idea why full text always missing some records?

If I run a manual update on the missing record, it will automatically indexed.

The filed included in the full text is updated by a trigger. It seems full text has issues tracking field that are updated by trigger?

Environment

SQL Server 2008 R2 Service Pack 2 - 10.50.4000

Full Text is set to Auto Track Changes

Full-Text Catalog Indexing takes time.

The official Microsoft Document states (emphasis by me) in the section Population based on change tracking:

  By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.

Reference: Populate Full-Text Indexes (Microsoft Docs)

You might want to consider using Incremental population based on timestamp which is mentioned in the same document.

  An incremental population is an alternative mechanism for manually populating a full-text index. If a table experiences a high volume of inserts, using incremental population can be more efficient that using manual population.+
  You can run an incremental population for a full-text index that has CHANGE_TRACKING set to MANUAL or OFF.
  The requirement for incremental population is that the indexed table must have a column of the timestamp data type. If a timestamp column does not exist, incremental population cannot be performed.
  SQL Server uses the timestamp column to identify rows that have changed since the last population. The incremental population then updates the full-text index for rows added, deleted, or modified after the last population, or while the last population was in progress. At the end of a population, the Full-Text Engine records a new timestamp value. This value is the largest timestamp value that SQL Gatherer has found. This value will be used when the next incremental population starts.


Checking if the query is made async or sync on SQL Server 2008 R2 I have tried to make async calls to my SQL Server instance as you may see on my SO post:

ASP.NET MVC 4 asyncronous database call: never returns the HTTP response

I did a benchmarking and the sync version is better than async one when there is even lots of concurrent loads. I am not sure if I got this right and opened the connection as async to my SQL Server instance.

The query does not take too much time and considering the fact that asynchronous code has much more overhead than the sync one, this might be the issue here as well. On the other hand, honest to say, I am not sure how to check if I opened the connection successfully as async or not.

Is there any chance I could check if the query is successfully completed as asynchronously?

As far as I can tell, SQL Server doesn't know a query is asynchronous. It's always synchronous to SQL Server. The connection string change appears to affect the client only.

sys.dm_exec_connections etc don't have anything to show whether a query is asynchronous or not


PostgreSQL vacuum starting too late (every 100k instead of 10k dead rows) I have a large table (50M) and autovacuum seems to work properly in general. However I started monitoring it with Datadog and I have noticed something strange:

select relname, reloptions from pg_class where relname='subscriptions';

{
  autovacuum_vacuum_scale_factor=0,
  autovacuum_analyze_scale_factor=0,
  autovacuum_vacuum_threshold=10000,
  autovacuum_analyze_threshold=10000
}

So you expect the table to be cleaned every time it reaches 10k dead rows.

However the graph of dead rows shows that vacuum starts much later, at about 100k or even 200k dead rows:

Why? How can I make the vacuum run more frequently (every 10k dead rows)?

Autovacuum will start right away, but it takes some time to process the table. During that time, you don't see a decrease in the number of dead tuples.

To make autovacuum be more aggressive, don't make it start sooner, but make it faster. A threshold of 10000 with a scale factor of 0 seems insane to me, it will cause autovacuum to permanently process this table (at a snail's pace), and you don't end up with significantly less bloat.

Every table needs a certain amount of bloat, and the 20% you have by default are typically fine.

So I recommend these settings:

ALTER TABLE subscriptions
   RESET (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor),
   SET (autovacuum_analyze_threshold = 50000, autovacuum_vacuum_cost_delay = 0);

That will cause autovacuum to process the table as fast as possible, and you won't end up with much more than 20% bloat. Autoanalyze will run very frequently.

If you really don't want 20% of bloat in spite of what I said, consider setting autovacuum_vacuum_scale_factor to 0.1. I wouldn't go lower than that.


Postgres group and aggregate(sum) JSONb arrays and non JSONb properties Version
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc, a 224fe214a p 3971489d3e, 64-bit
I am trying to build a query which (distinctly) groups values from JSONb columns, and also sums values from others, and non JSONb columns.
Table Definition (I have removed other columns that are not relevant)
id varchar(255) NOT NULL,
casualties jsonb NOT NULL,
involved_parties jsonb NULL,
tags jsonb NULL,
reported_at int8 NULL,

casualities
Each row has a single object which represents the count of casualties across categories, for the incident.
{"police_deaths": 0, "civilian_deaths": 0, "criminal_deaths": 0, "military_deaths": 0, "police_injuries": 0, "emergency_deaths": 0, "civilian_injuries": 1, "criminal_injuries": 1, "military_injuries": 0, "emergency_injuries": 0}

involved_parties
This is an array of objects.  Each row shows zero or more involved parties (who was involved in the incident).  The data looks a little misleading at first as for each entry within the array the involved party/incident relationship has an ID.  This doesnt really give us anything and I dont need this, but it is in the data currently.
[
  {"id": "2a0fd9dc-40bd-40dc-88ce-bc819fe9cdd8", "type": "group", "group": {"id": "6d342bfc-72c4-4588-ab95-1b3bdfb4881a", "name": "Naxals"}, "involvement": "Actor"},
  {"id": "dafc4726-3d3d-40cb-bbaf-63fa57250b44", "type": "group", "group": {"id": "18c6d3f6-c3eb-45db-9a02-26606f85d7eb", "name": "Indian Security Forces"}, "involvement": "Directly Targeted"}
]

Its the group, and involvement data I am interested in here.
affected sectors
This structure is much like the involved parties.
[
  {"id": "fcb952ef-3139-4fe7-ba15-7d800bdc60ae", "sector": {"id": "668d330e-aee5-4291-be98-df9c32b5b420", "name": "Military"}},
  {"id": "d1b71bae-29ac-48a2-ab41-a6979d720171", "sector": {"id": "550a4aa0-6d6f-4be2-ba33-f35d159ee686", "name": "Police/Law"}}
]

Here it is the sector I am interested in.
reported_at
This is the epoch representation of when the incident was reported by our analysts.
Desired Output
For the records in the query I want a single row.
The single row has the following columns:
incident_count,
casualties,
involved_parties,
tags,
min_reported_at,
max_reported_at

The incident count should be just that, the number of rows formally represented.
The casualties object always has the same properties in the JSON and I want to sum them.  So, there will be a single object with the sum of all police_deaths, civilian_deaths etc.
For the involved parties and affected sectors; each should have a single array containing a unique set of the parties/sectors from the rows
Min/Max reported at should be the min/max over all rows.
I tried from this starting point:
select
    jsonb_agg(incidents.affected_sectors) as affected_sectors,
    jsonb_agg(incidents.involved_parties) as involved_parties
from incidents

But this was painfully slow (9 seconds). So I then tried by expanding each object out to a row, then trying to collapse it back but got lost off and bad results.
I'd appreciate any pointers here
Thanks,
Mark.

Ok, so i've got a working query that is happening in an acceptable timeframe.  It feels ugly, so if there are obvious ways I can improve it please do let me know.
with base_data as (
        /*This is where the query for incidents/static assets goes*/
        select affected_sectors, involved_parties, reported_at, tags, casualties
        from incidents
        ------------------------------------------------------------
)
select  /*unique affected_sectors*/
        (
            select jsonb_agg(ssect.sector)
            from (
                select sect.sector
                from base_data,
                 jsonb_to_recordset(base_data.affected_sectors) as sect(id varchar, sector jsonb)
                group by sect.sector
                ) ssect
        ) unique_sectors,
        /*unique involved parties*/
        (
            select jsonb_agg(spart.group)
            from    (
                select grp."group"
                from base_data,
                jsonb_to_recordset(base_data.involved_parties) as grp(id varchar, "type" varchar, "group" jsonb, involvement varchar)
                group by grp."group"
            ) spart
        ) unique_groups,
        /*min reported at date*/
        (
            select min(reported_at) from base_data
        ) min_reported_at,
        /*max reported at date*/
        (
            select max(reported_at) from base_data
        ) max_reported_at,
        /*unique tags*/
        (
            select jsonb_agg(stags.tags)
            from    (
                select value tags
                from base_data,
                jsonb_array_elements(base_data.tags)
                group by value
            ) stags
        ) unique_tags,
        /*summary casualty counts*/
        (
            select json_object_agg(key, val)
            from (
                select key, sum(value::numeric) val
                from base_data cas, jsonb_each_text(cas.casualties)
                group by key
                ) scas
        ) casualty_counts,
        /*Incident Count*/
        (
            select count(1) from base_data
        ) incident_count

In our database this runs in around 700ms for 10000 incidents on a clear cache.
I'd have liked this to be sub 200ms and will continue to hack at it.  If I come up with anything more useful I'll add a comment.


Query with CASE expressions on a Logger Devices database select t.DATETIME,  max(t.Depth) Depth,  max(t.Temperature) Temperature from(
                SELECT CAST(readingDTTM AS DATETIME) DATETIME,
                case Sensor when 'Depth' then lValue end as Depth,
                case Sensor when 'Temperature' then lValue end as Temperature
                FROM loggerData2 WHERE sName = 'Easington' AND
                Sensor in ('Depth','Temperature')
                GROUP BY CAST(readingDTTM AS DATETIME), lValue, Sensor
   )t
group by t.DATETIME ORDER BY t.DATETIME DESC;

The above query shows the following results when querying my database table called loggerData2.
It gets a distinct DATETIME based on both Depth and Temperature (Sensor in the database table).

DATETIME              Depth     Temperature
-------------------   ------    -----------
09/10/2018 10:30:00   48.670    10.35
09/10/2018 10:00:00   48.670    10.35
09/10/2018 09:30:00   48.670    10.35
09/10/2018 09:00:00   48.670    10.35
09/10/2018 08:30:00   48.670    10.35
09/10/2018 08:00:00   48.670    10.35
09/10/2018 07:30:00   48.680    10.35
09/10/2018 07:00:00   48.670    10.35
09/10/2018 06:30:00   48.670    10.35

My problem now is this is only set up for one site at a time.

What I am trying to do is the same as the above query, but this time for only Depth (sensor) but for multiple sites (sName).

DATE TIME             Depth (sitename1)   Depth (sitename2)
-------------------   -----------------   -----------------
09/10/2018 10:30:00   48.670              46.756
09/10/2018 10:00:00   48.670              46.756
09/10/2018 09:30:00   48.670              46.756
09/10/2018 09:00:00   48.670              46.756
09/10/2018 08:30:00   48.670              46.756
09/10/2018 08:00:00   48.670              46.756
09/10/2018 07:30:00   48.680              46.756
09/10/2018 07:00:00   48.670              46.756
09/10/2018 06:30:00   48.670              46.756

Any ideas would be greatly appreciated.

You can get it by using PIVOT relational operator, but it requires a dynamic query due you don't know the name of SiteName in advance.

The STUFF query builds a coma-separated string with the names of the sites. The PIVOT query use its to get MAX(Depth) of each site.

DECLARE @col AS nvarchar(max);
DECLARE @cmd AS nvarchar(max);

SET @cols = STUFF((SELECT ',' + QUOTENAME(sName)
                   FROM loggerData2
                   WHERE sName = sName IN ('SiteName1', 'SiteName2', ...)
                   FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @cmd =    'SELECT DT, ' + @col
            + 'FROM
               (
                   SELECT
                      sName as SiteName,
                      CAST(readingDTTM AS DATETIME) DT,
                      lValue as Depth
                   FROM
                     loggerData2
                   WHERE
                     sName = sName IN ('SiteName1', 'SiteName2', ...)
                     AND Sensor = 'Depth'
               ) src
               PIVOT
               (
                    MAX(Depth) FOR SiteName IN (' + @col + ')'
            + ') pvt';

EXEC sp_executesql @cmd;


Best solution for High Availability and SSRS on SQL Server 2008 R2? I have 2 Physical Servers with SQL Server 2008 R2. – SQL Server 1(Active) & SQL Server 2 (Passive)
Web Application is developed using .Net 4.0 Framework.

I want to know the best solution to have high availability and also have SSRS for reporting.

Planned solution: Mirroring for Failover, and Transaction Replication for SSRS as the mirrored database can only be used for failover scenarios. SSRS will be on the Passive server, to reduce the load on the Active server.

Let me know if the solution is correct. Also suggest alternate approaches.

If you can convince the reporting users that they don't really need real-time reports, you can take advantage of the ability to create a snapshot of the mirror database. See Database Mirroring and Database Snapshots on Technet.

  Unlike the mirror database itself, a database snapshot is accessible
  to clients. As long as the mirror server is communicating with the
  principal server, you can direct reporting clients to connect to a
  snapshot. Note that because a database snapshot is static, new data is
  not available. To make relatively recent data available to your users,
  you must create a new database snapshot periodically and have
  applications direct incoming client connections to the newest
  snapshot.

Mirror/snapshot combinations can also be beneficial if you want to run ETL over an OLTP database to load into a data warehouse. Example architecture in this article on SSC.

The advantage in both cases is removing the overhead of running a replication feed, if you can convince your users that an hourly lag on reporting data is acceptable. In the vast majority of cases it is perfectly reasonable but the perception can be tough to change.


Optimize UNION query in MySQL I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more than 35 years of age.

We are using "vertical partionning": 1 table per criter. For example:

* user_country
- id_user
- id_country

We would like to do this kind of query:

SELECT id_inscri FROM userdata_langue
WHERE id_langue='43'
  UNION
SELECT id_inscri FROM userdata_sexe
WHERE sexe='2'
  UNION
SELECT id_inscri FROM userdata_nb_jour
WHERE nb_jour>='31'
  UNION
SELECT id_inscri FROM userdata_last
WHERE last<='2013-04-07'
  AND last>='2013-04-03' ;

How do I optimize that?

----- More details

Explain output of the query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY userdata_langue ref id_langue   id_langue   1   const       398846  Using index
2   UNION   userdata_sexe   ref sexe    sexe    1   const   1667137 Using index
3   UNION   userdata_nb_jour    range   nb_jour nb_jour 2   NULL    5830    Using where; Using index
4   UNION   userdata_last   range   last    last    3   NULL    371614  Using where; Using index
NULL    UNION RESULT    <union1,2,3,4>  ALL NULL    NULL    NULL    NULL    NULL

SHOW CREATE TABLE

Table   Create Table
userdata_langue CREATE TABLE `userdata_langue` (
 `id_inscri` bigint(20) NOT NULL,
 `id_langue` tinyint(3) unsigned NOT NULL,
 PRIMARY KEY (`id_inscri`),
 KEY `id_langue` (`id_langue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

From the EXPLAIN output, it looks like first table userdata_langue would benefit from an index that contains both id_langue and id_inscri. If there is already an index on that combination, try forcing it.

The second table userdata_sexe could be partitioned on sexe (I assume there are only two options here?). This would speed up the scan required to fetch all those.

I am not sure if mySQL would benefit from moving the order of the UNION DISTINCT components around. It might be worth trying to move component 3 and 4 up to the top of the query.

Finally, consider if you can afford to increase the sort buffer size in InnoDb (see: http://www.mysqlperformanceblog.com/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/) to make sure you can hold everything in memory while mySQL calculates the UNION DISTINCT.


can't find column stats for newly created table I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.

database current settings are:

I started with creating a simple table:

create table recomp(num int)
go

Then I insert some rows:

insert into recomp
values (5)
go 100

and run my query below 5 times (I used where 1 = (select 1)) for full optimization:

select * from recomp
where 1 = (select 1)

in my plan cache I have the following:

Now the interesting part, I again insert new rows in order to trigger auto update stats for num column of the recomp table:

insert into recomp
values (8)
go 500

Here stats for num column has updated. The next what I am going to do is run my select query:

select * from recomp
where 1 = (select 1)

When I check my plan cache:

and an event which captures recompiles:

my question is: where can I find stats for num column?

The optimizer doen't need any stats to optimize those queries, so none are created.

  When the automatic create statistics option, AUTO_CREATE_STATISTICS is
  ON, the Query Optimizer creates statistics on individual columns in
  the query predicate, as necessary, to improve cardinality estimates
  for the query plan.

AUTO_CREATE_STATISTICS Option

Try something like

select *
from recomp
where num = 4

And you should see a statistic created.


executing single query in loop increase time every time Am executing one query which is the same as below. Now here the problem is as limit increasing its increasing execution of time.

I am adding limit using a PHP script. So anyone likes to suggest to stop increasing the time of execution by mysql?

There is total records are 12042993 which I have to copy from one table to another table. But when am executing the single query without limit that time it's not working.

So will you please suggest a better way to avoid this increasing time?

For now, I have added one index which is below. I know its wrong. But for me, I just have to concentrate only and only on this query. There is no any other query is running on the entire server/database.

alter table cdrs add index cdrs_data  (`callstart`,`uniqueid`,`accountid`,
    `type`,`callerid`,`sip_from_uri`,`callednum`,`billseconds`,trunk_id,disposition,
    debit,cost,provider_id,pricelist_id, package_id,pattern,notes,reseller_id,
    provider_call_cost, call_direction,calltype,progress_media_stamp,
    progress_mediamsec, is_recording,call_reward_amount);

insert into cdrs_data(uniqueid,accountid,type,callerid,sip_from_uri,callednum,
    billseconds,trunk_id,disposition,callstart,debit,cost,provider_id,pricelist_id,
    package_id,pattern,notes,reseller_id,provider_call_cost,call_direction,
    calltype,progress_media_stamp,progress_mediamsec,is_recording,call_reward_amount)
  select  uniqueid,accountid,type,callerid, sip_from_uri, callednum,billseconds,
       trunk_id,disposition, callstart,debit,cost, provider_id,pricelist_id,package_id,
       pattern,notes,reseller_id,provider_call_cost, call_direction,calltype,
       progress_media_stamp,progress_mediamsec, is_recording,call_reward_amount
    from callinfo
    where callstart >= '2017-01-30 00:00:00' and callstart < '2019-05-06 00:00:00'
    order by callstart asc limit 24000,3000

All suggestions are appriciated.

What percentage of the 12M rows are you transferring?

This gets slower and slower as you increase the OFFSET:

limit 24000,3000

Instead, "remember where you left off".  More:  http://mysql.rjweb.org/doc.php/pagination and http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Is there an index on callstart?

The INDEX cdrs_data is a terribly long index.  The only way it might be useful is if it is "covering".

The TABLE cdrs_data -- does it have any indexes?  Of so, disabling the indexes before starting the inserts is likely to speed things up.  Reenable afterward.

Another option is to use mysqldump with a --where clause. for the dump side; then mysql for loading in the new place.


Postgres: Given some parent id's how can I SELECT X child records for each? In Postgres, if I have :

Parent Table
id, name
1, Parent1
2, Parent2
.....
5, Parent5

Child Table
id, parent_id, name
1, 1, Child1
2, 1, Child2
.....
6, 2, Child6
.....
25, 5, Child25

so 5 children for each parent, how do I get, say, the first 3 children for parent id 1,3,4 in one query?

Assuming the "first" is defined through the id column in the child table, something like this should work:

select p.id, p.name, c.name
from parent p
  join (
     select c.name,
            c.parent_id,
            row_number over (partition by c.parent_id order by c.id) as rn
     from child c
  ) ch
   on ch.parent_id = p.id
  and ch.rn <= 3
where p.id in (1,3,4);


PostgreSQL Return Joined Results of Select I'm learning PostgreSQL and was wondering if I can put long selects in stored procedures like this one:

CREATE OR REPLACE FUNCTION api.book_list()
RETURNS TABLE (
  id         BIGINT,
  published  DATE,
  category   VARCHAR,
  author     VARCHAR,
  name       VARCHAR
) AS $$
BEGIN
  SELECT
    b.id,
    b.published,
    c.name AS category,
    b.author,
    b.name
  FROM
    schemas.book AS b
  JOIN
    schemas.category AS c
  ON
    c.id = b.category_id;
END;
$$
LANGUAGE plpgsql;

It is supposed to return select from two joined tables.

It gives the following error:

select * from api.book_list();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "book_list" line 3 at SQL statement

Use RETURN QUERY SELECT ...;.

PostgreSQL is complaining because you aren't specifying what to return. Also, since you aren't actually passing parameters to the function this could also be accomplished by a view.


postgresql cannot connect as user to data server I'm very new to postgresql, I installed it on my windows machine, and created a few databases and users, and I'm trying to connect with one of the accounts I created. If I log in using the postgres account, and I list all users, I get the following:

    postgres=# \du
                                         List of roles
   Role name    |                         Attributes                         |    Member of
----------------+------------------------------------------------------------+------------------
 oak_admin      | Superuser, Create DB                                       | {}
 oak_admin_r    | Cannot login                                               | {}
 oak_test_roles | Password valid until infinity                              | {}
 oak_test_user  | Password valid until infinity                              | {oak_test_roles}
 oak_user       |                                                            | {}
 oak_user_roles | Password valid until infinity                              | {}
 postgres       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

now I listed all the databases, and I got the following:

postgres=# \l
                                                  List of databases
   Name    |  Owner   | Encoding |           Collate           |            Ctype            |   Access privileges
-----------+----------+----------+-----------------------------+-----------------------------+-----------------------
 oakdb     | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
 postgres  | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
 template0 | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 | =c/postgres          +
           |          |          |                             |                             | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 | =c/postgres          +
           |          |          |                             |                             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
(5 rows)

With that I thought I had the users and I had the databases, so I should be able to connect. So I thought maybe I mistyped the password, so I altered the password to something very easy and tried that out, I got the following error:

postgres=# ALTER USER oak_admin WITH PASSWORD  'very_easy';
ALTER ROLE
postgres=# \q

C:\Users\charb>psql -U oak_admin
Password for user oak_admin:
psql: FATAL:  database "oak_admin" does not exist

Any ideas what am I doing wrong?

The default db name, if not provided, is the username.
In this case oak_admin.

Provide the database name you want to
connect to using the --dbname= option. https://www.postgresql.org/docs/current/static/app-psql.html

In this example you should add --dbnsme=oakdb


Does a covering nonclustered index help to answer Select * queries? Our application developers run a lot of SELECT * queries against our database system, unfortunately.

The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to create a 100% covering non-clustered index for those cases. For example actually there is an index with 3 index columns and additional 25 included columns suggested. Other indexes with the key columns suggested are already in place but then require lookups.

I am a little surprised about this. However since the underlying queries are running very often with high costs and the suggested index comes with an impact of about 50% this is maybe an interesting option.

I know it is not best practice and bad design to support those queries, but I doubt this could be changed within adequate time, so I wonder if those indexes will help at all or produce more costs than benefit?

Is there a rule or do I just have to try / error the consequences?

Row counts are between 60,000 and 2.1 million.
The missing indexes DMVs tell me on average (over multiple servers) 2424 seek operations could be performed for the queries (this is a number counting up continuously for about 3 months and the query cost of 19 in average could be impacted positively of about 50%.
Removing SELECT * would be best, but I am asked to optimize it as is, and since SQL Server itself suggests this, I think it is worth thinking about it anyway.
The existing queries are not really slow, < 1 sec, but this is quite subjective as well I think. Execution plan tells the key lookups cost 80% of the whole, so I guess saving this might be desirable anyways.
I did some tests without / with the 100% covering index. Results:

SELECT * :  Costs before: 0,026 after: 0,00329 (new index used) = -88% improvement
UPDATE: before: 0,043333 after: 0,0533 (ix used) = +20%
INSERT: before: 0,1133 after: 0,1233 (ix not used)=equal
DELETE: before: 0,645 after: 0,655 (ix used) =equal.

Conclusion: It tunes the selects much more than it slows down the changes. Why not apply?

The missing index suggestions are opportunistic entries added whenever the optimizer happens to notice that an exact-match index for the current set of predicates it is considering do not exist on the base object.

The information recorded in the DMVs is intended to be a helpful input to the normal activities of a skilled database tuner; it is not intended to be interpreted as the result of a comprehensive analysis. The data recorded is intentionally a complete set of the keys and other columns needed, together with a brief indication of the reason it was added (equality, inequality, include).

The "impact" recorded is also very basic: it is the difference in estimated cost, according to the cost model used by the optimizer. As usual, this needs to be treated with a healthy dose of skepticism, because it is very unlikely that the actual performance characteristics of your hardware match the optimizer's model, even vaguely.

Note also that cost estimations depend heavily on cardinality (row count), average row size, and distribution information. If these are not reasonably close to the actual values, the "cost" is an even less reliable metric. Nevertheless, right now, estimated cost is all we have, so that's the number reported. Again, SQL Server is just trying to be as helpful as it can; it is up to the person reviewing the information to interpret it correctly.

Tests based on estimated costs are potentially informative to an early assessment of target areas for improvement, but they are no substitute for actual measurements of whatever metrics are important in your environment. The relative importance of duration, CPU usage, physical reads, memory usage ... and so on ... will be different in different circumstances.

The point really is to measure where the real problems are, record a baseline, make improvements, then measure and assess the benefits against the costs.

For a more thorough analysis than the missing index feature provides, you would need to run a representative workload through the Database Engine Tuning Advisor. Even there, the recommendations will still need to be assessed by a human, and something similar to the above baseline-assess-change-measure protocol followed.

That said, not all final decisions will be based on performance comparisons. There may be other considerations, like the avoidance of a particularly inconvenient lookup-related deadlock. Index tuning is as much art as science sometimes.


length of the result exceeds the > length limit (2GB) of the target large type Getting the following error message on the Sql Server Error Log:

  SQLServer Error: 599, WRITE: The length of the result exceeds the
  length limit (2GB) of the target large type. [SQLSTATE 42000]
  (LogToTableWrite)

How can I find what job is causing this?

While I can tell you that in my case, I found the job causing the error by it's frequency, (the error was happening every five minutes) and there were only 2 jobs running every 5 minutes.  However, after knowing which job was causing the error, I spent a lot of time to determine why the error was happening, given that the job itself was not failing.

Turns out that in the SQL Server Agent, Job Activity Monitor, Job Properties, Steps, (Edit),  Advanced tab, someone checked the option: "Append output to existing entry in table", which caused that the output continued to grow over time, resulting in the error: "length of the result exceeds the > length limit (2GB) of the target large type".  Unchecking the option circled below solved the issue for me.


Is execution of synonyms not logged in target database? I've been working on an issue where a single select within a stored procedure failed due to permission-issues, and we traced it to a call to a Synonym:

USE DatabaseA
GO
CREATE SYNONYM [dbo].[DatabaseB_dbo_TableT] FOR [DatabaseB].[dbo].[TableT]
GO

When debugging this, we set up a number of traces in the SQL Server Profiler to see what was going on.

DatabaseB, being the database holding the table that ultimately delivered the data has few connections, so we were able to set up the profiler without any other filters than the DatabaseName (and excluding the profiler's Application Name).

If I SELECT directly from TableT, my query shows up in the profiler for DatabaseB as expected.

But if I access the same data through the synonym, it only shows up in the profiler for DatabaseA - not for DatabaseB, which I would have thought.

Is this really as intended? While I haven't checked this with different users/schemas (experimenting with the auditing), it seems counterintuitive to me that I can access data in a database, without having this reflected in a profiler that is supposedly logging anything going on in the database.

I hope somebody can offer some insight to this.

The query is run in DatabaseA, not DatabaseB, so I'd say by design. That is, SQL Profiler captures "what is the connection database context" not "In what database is the object am I accessing"

This makes sense: each database is logically isolated from each other. Using a synonym or other cross database query is a special case because you can't ensure all databases are in synch (especially around restores) nor enforce foreign keys etc

An other example...

USE DatabaseD
GO
SELECT *
FROm
  DatabaseA.dbo.TableA A
  JOIN
  DatabaseB.dbo.TableB B ON ..
  JOIN
  DatabaseC.dbo.TableC C ON ..

It runs in DatabaseD so this is what you'd trap in profiler

Workaround? As far as I know (I haven't tried though) you can't distinguish the database of an accessed object, only of the connection.


How is possible to configure TLSv1.1 protocol for SSL connection in PostgreSQL? I need to configure TLS 1.1 protocol for SSL connection in PostgreSQL.

I was not able to see protocol setting in the PostgreSQL configuration

It is required to disable SSL protocols and TLSv1 and enable only TLSv1.1 (or TLSv1.2)

Added

PCI DSS v3.1 requires that fallback to SSL and TLSv1 will be disabled.

Is it is possible to configure PostgreSQL to negotiate using TLSv1.1 only?

If you know that it is impossible please share this information

Added 2

Unfortunately, the configuration of ssl_ciphers is not enough since you can use same ciphers for different protocols.  It is required to configure SSL_METHOD as described here:
https://www.openssl.org/docs/manmaster/ssl/ssl.html.

My conclusion that the configuration of SSL_METHOD (or SSL_PROTOCOL) is missed from the PostgreSQL configuration and it can not be complaint to PCI DSS 3.1.

Please correct me if I am wrong.

@BrianEfting was correct, you can specify the appropriate cipher suites to only allow TLSv1.2 which should fit your PCI-DSS 3.1 specification needs.

Using a cipher list like this in the ssl_ciphers option in your postgresql.conf:

ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!3DES:!MD5:!PSK

along with setting ssl_prefer_server_ciphers=true, should be sufficient to allow only TLSv1.2 connections.

You can verify this using SSLyze which knows about the PostgreSQL protocol.

To test, I used the following command:

./sslyze.py --sslv2 --sslv3 --tlsv1 --tlsv1_1 --tlsv1_2 localhost:5432 --starttls=postgres --hide_rejected_ciphers

Which gave the output below under PostgreSQL 9.4 on Debian Wheezy showing that all cipher suites except for the TLSv1.2 ciphers specified were rejected, which should satisfy the requirements of PCI-DSS 3.1 by using TLSv1.1 or greater.

postgres@pgsqlsec4:~/sslyze$ ./sslyze.py --sslv2 --sslv3 --tlsv1 --tlsv1_1 --tlsv1_2 localhost:5432 --starttls=postgres --hide_rejected_ciphers

 AVAILABLE PLUGINS
 -----------------

  PluginCompression
  PluginHeartbleed
  PluginChromeSha1Deprecation
  PluginSessionRenegotiation
  PluginOpenSSLCipherSuites
  PluginSessionResumption
  PluginHSTS
  PluginCertInfo

 CHECKING HOST(S) AVAILABILITY
 -----------------------------

   localhost:5432                      => ::1:5432

 SCAN RESULTS FOR LOCALHOST:5432 - ::1:5432
 ------------------------------------------

  * SSLV2 Cipher Suites:
      Server rejected all cipher suites.

  * TLSV1_2 Cipher Suites:
      Preferred:
                 ECDHE-RSA-AES128-GCM-SHA256   ECDH-256 bits  128 bits
      Accepted:
                 ECDHE-RSA-AES256-SHA384       ECDH-256 bits  256 bits
                 ECDHE-RSA-AES256-GCM-SHA384   ECDH-256 bits  256 bits
                 DHE-RSA-AES256-SHA256         DH-1024 bits   256 bits
                 DHE-RSA-AES256-GCM-SHA384     DH-1024 bits   256 bits
                 ECDHE-RSA-AES128-SHA256       ECDH-256 bits  128 bits
                 ECDHE-RSA-AES128-GCM-SHA256   ECDH-256 bits  128 bits
                 DHE-RSA-AES128-SHA256         DH-1024 bits   128 bits
                 DHE-RSA-AES128-GCM-SHA256     DH-1024 bits   128 bits

  * TLSV1_1 Cipher Suites:
      Server rejected all cipher suites.

  * TLSV1 Cipher Suites:
      Server rejected all cipher suites.

  * SSLV3 Cipher Suites:
      Server rejected all cipher suites.

 SCAN COMPLETED IN 0.73 S
 ------------------------
postgres@pgsqlsec4:~/sslyze$


SQL Developer - trying to import an excel file into a table I'm trying to import an excel file into a table. The excel file has dates in the following format: 20/08/2008  12:00:00 AM
and I am telling SQLDEV the following: DD/MM/YYYY H24:MI:SS AM

To import the file, I am right clicking on the table in SQL Developer, hitting import data and then selecting the file, I map the columns and set the format to above and get the above error. Here's the table:

--------------------------------------------------------
--  DDL for Table DRUG_INFO_NOS
--------------------------------------------------------

  CREATE TABLE "DANIEL"."DRUG_INFO_NOS"
   (    "DIN" VARCHAR2(20 BYTE),
    "TRADE_NAME" VARCHAR2(50 BYTE),
    "FORM" VARCHAR2(40 BYTE),
    "GENERIC_NAME" VARCHAR2(60 BYTE),
    "MANUFACTURER" VARCHAR2(100 BYTE),
    "CR_DATE" DATE,
    "NOC_DATE" DATE,
    "FIRST_CLAIM_DATE" DATE,
    "SEQUENTIAL_ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATAL03" ;
--------------------------------------------------------
--  Constraints for Table DRUG_INFO_NOS
--------------------------------------------------------

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("DIN" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("TRADE_NAME" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("GENERIC_NAME" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("MANUFACTURER" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("SEQUENTIAL_ID" NOT NULL ENABLE);

but I am getting the above error. What's up?

This link shows how to deal with importing excel file and invalid date format: how-to-import-excel-to-oracle. Hope this helps.