An alternative, and sometimes better performing approach could be to add an attribute to your date dimension along the lines of "IsFuture".

Depending on your data load/processing schedule you could have a field in your datawarehouse or a calculated column in your dsv precalculating if the date is in the future or not.

Instead of performing an on the fly calculation with an IIF in your MDX you could then create a calculated measure using SCOPE like this (untested, from memory)

Scope
 (
    [Date].[IsFuture].&[0]
 ) ;

   This = 1;
end scope;

Just an idea I'm offering, your question doesn't provide a lot of detail to create a working piece of sample code but this could be an alternative approach.
This would return empty if the date is in the future instead of 0 but depending on your requirements that could provide better results if your client is only returning NONEMPTY/NONEMPTYCROSSJOIN results.


'LDF' is not a valid database file header. The PageAudit property is incorrect

Ok , is it possible to import without log file?

Yes, but stop using deprecated procedures like sp_attach_db (see the big warning at the top).

Next, you need to supply paths to SQL Server that make sense. Can you get to /home/ in Windows Explorer? Can you do that when logged in as the SQL Server service account?

CREATE DATABASE LIQ_DEV_V9
  ON
  (
    name = LIQ_DEV_V9,
    filename = 'C:\...\LIQ_DEV_V9.mdf'
  )
  FOR ATTACH_REBUILD_LOG;

This might fail if SQL Server attempts to create a new log file in a location where that file already exists; so if the damaged LDF file is in that same folder, you may want to rename it, move it, or delete it.

More importantly, you really shouldn't be relying on mdf/ldf files as backups; please read this post:

Bad Habits : Using MDF/LDF Files as "Backups"


Always On Availability Groups on single VM

I definitely understand the comments from Lee and Rodrigo, and really appreciate you pointing me at those additional resources.  I think your observations are generally correct.
However, I have discovered an approach that may bear fruit on this endeavor.  Rafael Rodriguez has accomplished this through the use of Docker Containers.  He has an article about how he did this AOAG Read-Scale Clusterless.
He also has the entire project in a git repository.
I downloaded it and fired it up in Docker on my Win10 workstation.  I was able to connect to both instances from SSMS, add a table to the primary and then see it show up in the secondary replica!
I think this essentially answers this question.  My next step is to set up the particulars of my use case, and determine a testing strategy to get benchmarks and performance improvement data.  I will try to circle back and post any results.
Thanks to everyone for helping me move forward on this project!  Merry Christmas!!!


Is There A Way To Tell How Many Times A Query Has Executed?

You can use this query with few caution and need to understand thoroughly what the result is telling you.

sys.dm_exec_query_stats (Transact-SQL)

  Returns aggregate performance statistics for cached query plans in SQL
  Server. The view contains one row per query statement within the
  cached plan, and the lifetime of the rows are tied to the plan itself.
  When a plan is removed from the cache, the corresponding rows are
  eliminated from this view.

If a plan is not cached you will not see those query and does not
mean those did not run.
No way to aggregate accurately per day, you only get last execution time and number of times that the plan has been executed since it was last compiled.

Copied from above link and added 2 more columns.

SELECT qs.execution_count,
       creation_time,
             last_execution_time,
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
                 (CASE WHEN qs.statement_end_offset = -1
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text,
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
--WHERE qt.text like '%SELECT%'
ORDER BY qs.execution_count DESC;

If you need an exact count for 24 hours for any query you will need to set up your own extended event collection.


How to GRANT permission to execute stored procedure?

in Excel when they go to Data -> Get Data -> From Database -> From SQL Server Database

That's PowerQuery. Just ran through that on Excel, and stored procedures don't appear, but Table-Valued Functions do. And you can call a stored procedure, but it won't show up in the query designer. You just enter 'exec dbo.Proc1' in the SQL Statement dialog:


A database differential backup without a full backup

It is not possible to perform a differential backup of a database if no previous backup was performed. A DIFF (differential) backup relies on the previous FULL backup.

  A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

Reference: Differential Backups (SQL Server) (Microsoft Docs)

Proving the Thesis

You can check this with the following steps:

Create a New Database

USE [master]
GO
CREATE DATABASE [Q209724_Database]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Q209724_Database', FILENAME = N'C:\SQL\SQL_DATA\Q209724_Database.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Q209724_Database_log', FILENAME = N'C:\SQL\SQL_LOGS\Q209724_Database_log.ldf' , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Q209724_Database] SET COMPATIBILITY_LEVEL = 130
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Q209724_Database].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

The database is created and has not yet been backed up.

Perform a DIFF Backup of The New Database

BACKUP DATABASE [Q209724_Database]
TO  DISK = 'C:\SQL\BACKUP\Q209724_Database.bak'
WITH DIFFERENTIAL,
     NOFORMAT,
     NOINIT,
     NAME = N'Q209724_Database-Full Database Backup',
     SKIP,
     NOREWIND,
     NOUNLOAD,
     STATS = 10
GO

Output

Msg 3035, Level 16, State 1, Line 91
Cannot perform a differential backup for database "Q209724_Database", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 91
BACKUP DATABASE is terminating abnormally.

So if you haven't previously performed a FULL backup, you will be unable to perform a DIFF backup.

Conclusion

After your database was created somebody or a backup solution has performed a backup of your database.

Checking Backup History

You can change the database name in the following script to the name of your database and check the backup history for a backup of the database:

SELECT
   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   -- msdb.dbo.backupset.expiration_date,

   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   -- msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name,
   -- msdb.dbo.backupset.description,
   -- msdb.dbo.backupset.is_copy_only,
   -- msdb.dbo.backupset.is_snapshot,
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   'EOR'

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE 1 = 1

AND     database_name IN ('Q209724_Database')  -- database names
ORDER BY
           2 desc,3 desc

You should see an entry containing a Full remark for the backup_type column in the result set.

You can then analyse when and possibly who/what performed the FULL database backup.


Queries that ran, and the number of times they ran

for the query monitoring you have two solutions from MySQL level:

solution 1:

create log file for the query by editing my.ini or my.cnf

slow_query_log_file = "C:/slowquery.log"
log = log_file_name

solution 2:

Create log tables:

Slow queries:

CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

General queries

CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

  SET global general_log = 1;
SET global log_output = 'table';

To show the log use the following command:

select * from mysql.general_log;

if you want the slow query:

select * from mysql.slow_log;

To count the number of query executing:

show session status like "Queries";


SQL Server 2012 compatibility with Microsoft Server 2016

Yes, OS (Windows Server) and database (SQL Server) are separate entities. So the only question is compatibility between those two products.
I'm guessing it was tested and should be ok, but at this moment SQL Server 2012 doesn't have Windows Server 2016 listed (and won't have, at least not till September):

https://msdn.microsoft.com/en-us/library/ms143506%28v=sql.110%29.aspx

Best thing You can do is download latest Technical Preview and test it with SQL Server 2012 in Your environment:

https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-technical-preview


Change data capture - how to know who made the change?

I filed a bug about this, but it was closed as "by design."

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

Unfortunately you will have to use another technique (e.g. SQL Audit or a trigger) to get this information (and some custom code to try to correlate it to the CDC data as much as you can). I wrote about this deficiency also in my chapter "SQL Server Audit, Change Tracking, and Change Data Capture" in the book SQL Server MVP Deep Dives (volume 1).

I'm sorry that I don't have a better workaround for you but the built-in capabilities of CDC just aren't going to meet your requirements. :-(


It is normal you cannot associate two different user to a single login for a database. You must understand the difference between a login and a user.

A login authenticate you on the server. Once you are authenticated, the login will impersonate a user to access the specified database of the connection string.

So a login can have only one user per database.

Sample : login [myDomain\myUser] has access on SqlServer. This login can have a user per database : userA for databaseA, userB for databaseB.

If you want to add an other user to the login for the same database, you have to drop the first user. Instead, you have to create a different login for the second user.

Hope this help you.


v1 is untyped and Postgres doesn't know what to do with that.  At some point, either while defining the record or when returning it you have to specify the columns.  Try specifying the column names in the SELECT within an IF for different types.  I put in dummy checks for the IF to be replaced with what you need to decide the row type.

DO
$body$
DECLARE
v1 RECORD;
BEGIN

IF (1=3) THEN--logic to decide which data here
    SELECT 1 AS c1,2 AS c2,3 AS c3 INTO v1;
    CREATE TABLE temp AS SELECT v1.c1, v1.c2, v1.c3;
ELSE
 IF (2=2) THEN--logic to decide which data here
    SELECT 4 AS c1,5 AS c2 INTO v1;
    CREATE TABLE temp AS SELECT v1.c1, v1.c2;
 END IF;
END IF;

END;
$body$
LANGUAGE
'plpgsql'

Alternatively you could also:

DO
$body$
BEGIN

IF (1=3) THEN--logic to decide which data here
    CREATE TABLE temp AS SELECT 1 AS c1,2 AS c2,3 AS c3;
ELSE
 IF (2=2) THEN--logic to decide which data here
    CREATE TABLE temp AS SELECT 4 AS c1,5 AS c2;
 END IF;
END IF;

END;
$body$
LANGUAGE
'plpgsql'


Is it bad to have a cyclical relationship?

Here is the original model -- at least as worded in the question.

Though there is nothing cyclical here it does have few problems. For example, the model has no relationship between County and District, although you do state that "District has many Counties". It is also possible to have StudentRecord {RecordId, TeacherId, CountyId} such that that teacher does not teach in the district the county is located in.

One possible fix may look like this, though I do not think this would be complete solution, but it may be a good starting point. I will use predicates -- marked by | -- and constraints (italics) to describe the model. Predicates map to tables and constraints to PK,AK, FK.

| District (DistrictId) exists.

District is identified by DistrictId.

| County (CountyCode) is located in district (DistrictId).

County is identified by CountyCode.

Each county is located in exactly one district; for each district that district may have more than one county.

If a county is locaeted in a district, then that district must exist.

| Teacher (TeacherId) exists.

Teacher is identified by TeacherId.

| Teacher (TeacherId) is licensed to teach in district (DistrictId).

For each teacher, that teacher may be licensed to teach in more than one district.

For each district, more than one teacher may be licensed to teach in that district.

If a teacher is licensed to teach in a district, then that teacher must exist.

If a teacher is licensed to teach in a district, then that district must exist.

| School (SchoolId) is located in county (CountyCode), district (DistrictId).

School is identified by SchoolId.

For each school, that school is located in exactly one county, district.

For each county, district; more than one school may be located in that county, district.

If a school is located in a county, district; then that county must be located in that district.

| Teacher (TeacherId) teaches in school (SchoolId), in district (DistrictId).

Each teacher may teach in more than one school in a district.

For each school in a district it is possible that more than one teacher teaches in that school.

If a teacher teaches in school in a district, then that school must be located in that district.

If a teacher teaches in school in a district, then that teacher must be licensed to teach in that district.

| Student (StudentId) exists.

Student is identified by StudentId.

| Student (StudentId) attends school (SchoolId).

Each student may attend more than one school, for each school is is possible that more than one student attends that school.

If a student attends school, then that student must exist.

If a student attends school, then that school must exist.

| Student record (RecordId) was generated for student (StudentId) by teacher (TeacherID) in school (SchoolID).

Student record is identified by RecordId.

If a student record was generated for a student by a teacher in a school, then that student must attend that school.

If a student record was generated for a student by a teacher in a school, then that teacher must teach in that school.


Should it really take 15 fold time to order an indexed column?

Well it depends where this column is being used. Just because it's indexed (and presumably in some order) doesn't mean that using it in a query will magically make it fast.

Assume there are two indexes in the table, for id (ordered by id) and for user_id (ordered by user_id). If MySQL uses the latter to quickly look up the rows that satisfy the WHERE predicate then the result is that it might have all the rows in user_id order.

You want them in id order, so MySQL has to order them. Your previous query didn't have to do ordering. It should come as no surprise that more work == more time.

  What can I do to decrease such a performance hit?

Look at the execution plan. MySQL has EXPLAIN.

You should also make your queries as simple as you can. I assume your images table has a primary key, so the DISTINCT in SELECT DISTINCT images.* FROM images ... doesn't really make sense.

Also by taking all the columns (images.*) you're forcing MySQL to acquire every attribute for each row that is returned, whereas being more selective (no pun intended) with your columns might result in less data needing to be returned. Simplifying somewhat: less columns gives MySQL an opportunity to satisfy the query using only small indexes, rather than the table data.


SQL Server Configuration Manager not display services

I know of one other thing that you can try.  It affects SQL Server 2005 specifically, but might also be useful in your scenario.  Essentially, the wmiprvse.exe executable runs under the context of NETWORK SERVICE.  SSCM loads the Sqlmgmprovider.dll into this executable under the same security context.  Ensure that NETWORK SERVICE has READ permissions to the SQL Services in question.

The steps needed to accomplish this are detailed in the Microsoft Support Article:

Some or all SQL Server 2005 services are not listed in SQL Server Configuration Manager, or you receive a "No SQL Server 2005 components were found" error message when you perform operations in SQL Server 2005 Surface Area Configuration.

To do this in a standalone environment is a bit trickier.  Let's try this with SQL Browser first:

Open cmd.exe as an administrator.
Type the following command:  sc <server_name> sdshow <service_name>
Copy the string returned to your favorite notepad clone, we will need this string to revert back to prior configuration if this does not work.  This is our SDDL string.  The next step will be our concatenated SDDL string.
Add the following to the end of the string:  (A;;CCLCSWRPWPDTLOCRRC;;;NS)
Type the following command:  sc <server_name> sdset <service_name> <concatenated_SDDL_string>
Open SSCM and see if SQL Browser displays as expected.

The two commands run should look something like this:

sc \\my-server sdshow SQLBrowser

sc \\my-server sdset SQLBrowser D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)(A;;CCLCSWRPWPDTLOCRRC;;;NS)


How to join different tables with same propriety tables?

I think that a combination of the two solutions you provided is what you are looking for.

I think specifically what you are wanting to do is create a separate table for each of your attributes (IMAGE, DESCRIPTION, etc...).  Then you will create a bridge table between your objects and their attributes.  This allows you to have tables for each of your attributes.  That way you have those items normalized.  The bridge table then allows you to maintain the 1-to-many relationship between each of your objects and its attributes.  The downside with this plan is you will end up having a lot of tables as your attributes increase or your object types increase.  But the separation of data elements will allow you to keep the data integrity much better.

There is another solution utilizing a generic OBJECT-ATTRIBUTE table that allows you to maintain everything now and in the future in one place with a little bit of effort.  There is a great article provided by Red Gate which recommends against this method.  I would check out the "One table to hold all domain values" section.

Here is what I think you are looking for.  Assuming:

PLATE is a child table of MENU
ITEM is a child table of PLATE
The only 3 attributes you currently have are DESCRIPTION, ALLERGEN and IMAGE

Let me know if there are any questions or if this solution does not take care of everything you are looking for.


A view would allow you to group columns from different tables so you can view that data all at once.
An example would be if you had a SALES view to report on
you would create the SALES VIEW by joining a Customer table so you can see who purchased the item, you would also include a Inventory, or Items table so you can see the details of the item purchase.

So next time you need to query which customer bought which item, you would just query against the view that would contain all the columns you need


Can i pair multivalued attribute with an entity?

Yes, you can.

You could create a table for Diseases:

TABLE disease
-------------
disease.id (PK)
disease.name

Then you can model Diagnosis (note that's a junction table) this way:

TABLE diagnosis
---------------
diagnosis.id (PK)
diagnosis.date
diagnosis.doctor_id (FK)
diagnosis.patient_id (FK)
diagnosis.disease_id (FK)

So you can add a Disease to an Investigation:

TABLE investigation
-------------------
investigation.id (PK)
investigation.type
investigation.title
investigation.description
investigation.disease_id (FK)

And finally, here's the table for Testing:

TABLE testing
-------------
testing.id (PK)
testing.date
testing.investigation_id (FK)
testing.patient_id (FK)

The Testing can as well be included as a field in Investigation instead of having its own table, unless the exercise requires it.

Then there are the tables for Doctor and Patient which you already modeled.

This is the description of the tables, from it you can easily create the ER diagram, remembering that a FK on a table connects to the PK on another table.


How to use mongoexport command with ssl?

You are using the --sslCAFile argument, this is to specify the Certificate Authority file to be used against the server's certificate.

I think you might need to use --sslPEMKeyFile instead of --sslCAFile.
Better yet, you might need to specify them both if you are using self-signed certificates with a custom Certificate Authority.


EXPLAIN EXECUTE completes quickly, but is slow without EXPLAIN

When you run EXPLAIN, it only returns to you the plan that the database has created for running the query.  When you remove EXPLAIN, then you are asking the database to actually perform the query.

What this implies is that, essentially, your query is going to take 3 minutes.  Okay, so it's more complicated than that...

Try EXPLAIN (ANALYZE,BUFFERS)

By running a query with EXPLAIN (ANALYZE,BUFFERS) at the beginning, you are asking the database to actually perform the full query, but rather than returning results to you, return a report about the actual query performance.

Here's a few things to keep in mind:  the first time you run a query with EXPLAIN (ANALYZE,BUFFERS), it probably has to access data and indexes which are on disk, and so this will slow down the query for sure.  However, try running the same query a second time, and most often all of the data you need to perform the query is residing in the shared buffers. Then, you'll see the query typically finishes much more quickly on subsequent runs.

In the end, EXPLAIN (ANALYZE,BUFFERS) is a good tool for hunting down how long your query is actually going to take, and if your query plan is any good.

I personally don't really use prepared statements, so maybe someone else will chime in about these.  I know that it may have an effect on the quality of the query planner results.


RETURN TABLE is converted to RETURN SETOF int in Postgres function

What you see is obviously a display bug in the current pgAdmin 1.20. I could reproduce it.

You must be aware that the code you see is re-engineered from system table entries.

If you look up your function in the system catalogs directly, you'll find that your the return type has been registered properly (at least it works for me in pg 9.4):

SELECT pg_get_functiondef(oid)
FROM   pg_proc
WHERE  proname = 'test';

More useful functions like pg_get_functiondef() in the manual.

You might want to ask for this on the pgAdmin support list:

http://www.postgresql.org/list/pgadmin-support/

Mail to pgadmin-support@postgresql.org

  How can I force the returned column to have a name of identifier?

Your approach with RETURNS TABLE(identifier int) is the right way. RETURNS SETOF does not take a parameter name, unless you combine it with an OUT parameter. Like so:

CREATE OR REPLACE FUNCTION test(parm1 date
                              , parm2 interval
                              , OUT identifier int)
  RETURNS SETOF integer AS ...


Best query to exclude existing friends from query

NOT IN is typically the slowest option. LEFT JOIN / IS NULL is more promising. Or NOT EXISTS:

Select rows where value of second column is not present in first column

Query

Just your query, formatted:

SELECT u.id_user, u.username
FROM   "user" u
LEFT   JOIN friend_request fr ON fr.sent_to = u.id_user
                             AND fr.sent_from = 288
LEFT   JOIN friends f ON f.friend_to = u.id_user
                     AND f.friend_of = 288
WHERE  u.username LIKE '%tom%'
AND    fr.sent_to IS NULL
AND    f.friend_to IS NULL;

Or, a variant with NOT EXISTS anti-semi-joins:

SELECT u.id_user, u.username
FROM   "user" u
WHERE  NOT EXISTS (
   SELECT 1 FROM friend_request
   WHERE  sent_to = u.id_user
   AND    sent_from = 288
   )
AND    NOT EXISTS (
   SELECT 1 FROM friends
   WHERE  friend_to = u.id_user
   AND    friend_of = 288
   )
WHERE  u.username LIKE '%tom%';

Indexes

The most important factor for performance. An ideal setup would have:

A trigram GIN or GiST index on "user".username. You need the additional module pg_trgm. Instructions - and Gin or GiST?

How is LIKE implemented?

If index-only scans are possible, append the otherwise useless id_user. You need the additional module btree_gin (or btree_gist respectively):

Inner join using an array column

Then:

CREATE INDEX usr_trgm_idx ON "user" USING gin (username gin_trgm_ops, id_user);

Multicolumn indexes (or a UNIQUE / PK constraint) on (sent_from, sent_to) and (friend_of, friend_to) - with columns in that order. The reversed order would work, too, but not quite as good. Each query checks for a single friend_of combined with many different friend_to, which is faster with the suggested column order, because this way tuples are much rather localized on the same or a few index pages.

That said, the proof of the pudding is still in the eating.
Get the final verdict from EXPLAIN (BUFFERS, ANALYZE) in your production database.

Aside

Do not call your table user, that's a reserved word.


mongodb :: failures in bring up mongo server

If you add a new replica node, Mongodb will itself copy the data across to the new node. Unless if your data size is huge and do not want your network to be flooded in transferring the data you can follow the backup method.


Replacing a SQL server involved in Transactional Replication with least amount of downtime

You could add the new virtual server as a subscriber and once all of the data is replicated to the new virtual server you can switch connections and delete the old subscriber server.


These are dangerously big; lower them to about 1% of RAM.
tmp_table_size                  = 256M
max_heap_table_size             = 256M

Some of the allocated space gradually grows until it hits a limit derived from a setting or combination of settings.
Do you have anything other than MySQL running on that machine?  If not, I don't expect it to fill up the 8GB.


If you're on SQL Server 2008 R2, I'd recommend taking a look at the Open Data Protocol (also known as OData). Microsoft has some FAQs on it as well, and there is a Java client library available.

Also take a look at sqlREST, which exposes relational databases using web services and works with most databases that have a JDBC driver.


RAID 10 array configuration for SQL Server

Based on what you've described I would go with your second option and here is why.

Creating just one array and slicing it up into several logical volumes won't be any different than just having one large logical volume when it comes to I/O since you are still sharing the same underlying drives that make up the arrays.

SQL server runs best if you can separate the I/O types (sequential/random). Log files are written sequentially and any more than one database log on them will change the sequential pattern to random slowing the write operations to the database. Data files are written randomly so you are fine to mix multiple database data files on the same drive array.

For option 2 I would put the log file of tempdb on one array, the log file of your database on another array and the data files for your database and tempdb on the last array.

If you can swing a some additional dollars at the hardware here are a few suggestions that could help you offload some of the I/O.

You can offload the tempdb from the storage array by adding some PCI drives like Fusion IO to place the data and log files on. You should be able to purchase the smallest size they offer unless your database will be doing things that will cause a lot of tempdb usage.  You can add those drives from that third array back to the data array which will increase the IOPS available.

Buy more memory for the server. The 64GB limit only applies buffer pool. SQL server will use as much memory that the OS can support for everything else. Kendra Little from Brent Ozar Unlimited discusses the details in her blog post.

The final item would be to use the top end RAID controller that HP offers. You will get 1GB of on board memory to help with write caching to the disks.  You should also be able to control the percentage of that memory that is used for read and write caching. You should set it to 100% write as SQL server can't really utilize any read caching on the controller.


You can't update pg_database to change a database (you shouldn't be updating system tables at all, although you can do it for some of them).

Use ALTER DATABASE instead:

alter database my_template_1
  with is_template true
       allow_connections false;


Setup the user for edit/view permission to assigned one assigned entity of three (tables) available

Your question is ambiguous. Do you wish for a user to see all rows in one table, but only one of the three tables? Should a user be limited to only certain rows within a table? Must this be enforced within the schema or is it OK to use other aspects of the application and infrastructure? Anyway, here are some ideas.

Most DBMS have quite fine grained permission systems. It may be possible to GRANT just the permission required on each table.

Create views that expose just the correct tables, columns or rows. Grant permissions on the views but not the underlying tables.

Write stored procedures for each read & write. Grant access to these SPs but not the tables. Alternatively, let everyone execute all SPs but have the code reject unauthorised actions. Triggers can achieve a similar end.

In the application have separate screens for each read or write action for each entity type. Limit access to the screens through the authorisation system.

For a SQL-only solution for row-level access, add further tables, each keyed by userid and the store or business or corporation id. There will be one further column which is the permission granted. For each statement touching a base table join to this permission table and qualify on the userid and action. If the user does not have rights zero rows will be affected.

These permission tables can get big. It may be useful to put users into groups and assign rights to the groups. If there are rules to control which, say, stores a user sees these can be modelled instead of the naive approach above. The precise table definitions will depend on how the rules normalise. This can get quite tricky and will require a lot of testing.


Postgres complex insert or update with 2 unique constraints

You can combine multiple data-modifying statements by putting them into WITHs:

WITH constraint_1 AS (
  SELECT 1
  FROM external_ids
  WHERE external_id = $2
    AND disabled_by = ''
),
constraint_2 AS (
  UPDATE external_ids
  SET disabled_by = ''
  WHERE user_id     = $1
    AND external_id = $2
    AND disabled_by <> ''
  RETURNING 1
)
INSERT INTO external_ids (user_id, external_id, disabled_by)
SELECT $1, $2, ''
WHERE NOT EXISTS (SELECT * FROM constraint_1)
  AND NOT EXISTS (SELECT * FROM constraint_2);

Like the equivalent stored procedure, this might have races with concurrent updates, depending on the transaction isolation level.


MYSQL: Creating a table with same columns on remote machine

On Remote Machine :

Open command prompt
Log in to mysql :   mysql -uusername -p -h
fire : show create table TABLE_NAME
copy whole
exit

On local machine's command prompt, type :

mysql -uusername -p

paste there and press enter


Running SSRS and SSAS from different server than the primary database instance(SQL Server Engine)

1.How they talk each other(the two Servers)?

From the database engine perspective, both SSRS and SSAS are clients like any other application.  These query data from SQL Server using SQL queries.  Microsoft just chose to include these components with SQL Server licensing and media.

  2.Where the cube for SSAS stored?

On the server with SSAS installed (server 2).

  3.Where the report builder build report?

On the server with SSRS installed (server 2).

  4.Can you give my high level explanation how server 1 and server 2 interact or direct me some document I can read?

In addition to what I mentioned in #1, client applications can query the SSAS server without involving the database engine at all after cubes are built.  However, SSRS has a dependency on the reporting server databases as well as data sources that may also be on SQL Server.

Below are a couple of architecture diagrams you might find useful:

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

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


As per my understanding, your business rules (requirements) are as follows.

Each User can have one or many User Friends.
Each User Friend must belongs to one User.
Each User can send one or many Messages.
Each Message is sent by one User.

By following the above mentioned business rules, your database design may looks like as follows,

USER
{
 user_id
}

PASSWORD
{
password_id,
user_id
}

USER_FRIENDS
{
userfriend_id,
user_id,
added_by,
removed_by
}

MESSAGE
{
message_id,
message,
sent_by,
recieved_by,
is_read
}

If you need to track the user history, add the required data fields with domains such as timestamp, date, etc...at the appropriate entities (message history, login history, friends login, add/remove history). For calculating storage space and setting limit for users, you can done that using programing. I hope this will give you an idea.


A CLR aggregate will almost certainly be the fastest way of doing this. But perhaps you don't want to use one for whatever reason...

You say that the source for this is an expensive query.

I would materialise this into a #temp table first to ensure it is only evaluated once.

CREATE TABLE #test
(
ID INT,
name NVARCHAR(128),
car  NVARCHAR(128)
);

CREATE CLUSTERED INDEX ix ON #test(ID);

The execution plan I get for the query in the question first does the concatenation for every row in the outer query and then removes the duplicates by id, SomeField_Combined1, SomeField_Combined2.

This is incredibly wasteful. The following rewrite avoids this.

SELECT t.id,
       stuff([M].query('/name').value('/', 'varchar(max)'), 1, 1, '') AS [SomeField_Combined1],
       stuff([M].query('/car').value('/', 'varchar(max)'), 1, 1, '')  AS [SomeField_Combined2]
FROM   (SELECT DISTINCT id
        FROM   #test) t
       OUTER APPLY(SELECT (SELECT id,
                                  ',' + name AS name,
                                  ',' + car  AS car
                           FROM   #test
                           WHERE  #test.id = t.id
                           FOR XML PATH(''), type) AS M) M

However for the following test data (1000 ids with 2156 rows per id for me)

INSERT INTO #test
SELECT v.number, o.name, o.type_desc
FROM   sys.all_objects o
       INNER JOIN master..spt_values v
         ON v.type = 'P' AND v.number BETWEEN 1 AND 1000

I still found Kenneth's solution with two XML PATH calls much faster and less resource intensive.

+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
|                 | CPU Time (Seconds) | Elapsed Time (Seconds) | #test Scan Count | #test Logical Reads | Worktable logical reads | Worktable lob logical reads |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+
| Single XML PATH | 51.077             | 15.521                 | 1,005            | 60,165              | 51,161                  | 1,329,207                   |
| Double XML PATH | 3.1720             | 3.010                  | 2,005            | 92,088              | 14,951                  |   233,681                   |
+-----------------+--------------------+------------------------+------------------+---------------------+-------------------------+-----------------------------+

For each distinct id in #test it performs two operations instead of one but this operation is significantly cheaper than constructing the XML and then reparsing it.


Is binary logs required on the slave in order to do replication?

Other than doing point-in-time restores, there are two additional reasons a Slave could have binary logging enabled.
REASON #1
If a Slave is also a Master

binary logging must be enabled on Slave
log-slave-updates must be enabled on the Slave

This would allow another Slave to connect to a Slave with binary logging enabled as a Master
REASON #2
If a Slave is not a Master, but you want to failover to the Slave to become a new Master

binary logging must be enabled on Slave
log-slave-updates must not be enabled on the Slave (this prevents binary logs from growing on a Slave as long as the DB Server has the role of a Slave)

That way, after failing over your application to the Slave to become the Master, you do not have to restart mysql to enable it later. You could then setup replication to the new Master.


Redefining a table with a user-defined type using DBMS_REDEFINITION

alter type xxx noneditionable;
will allow you to have a table (non-editionable) reference your type (editionable by default) and still enable your schema for editions.


how to view colmn names of joined tables

Just putting this point out first and foremost, you should not use select *, you should declare the columns you want, even if you want everything you should have everything listed one by one so any column additions later on don't potentially screw up other functions.

Now that's over

This will get you the column names, but I can't guarantee the order that you will be pulling the data out, they will be listed here generally in the order that the tables / columns were created, which will be potentially be different to what you are getting in your initial query,

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema =  'DatabaseName'
AND table_name
IN (
'food', 'conditions', 'flavors', 'symptoms', 'tags'
)

you could replace the select with

SELECT CONCAT( table_name,  '.', column_name,  ', ' )

and then copy the output to the select statement (remember to remove the final,) which then allows you to pick the exact order you want everything to come out in which is a win under you're not using select * and a win under you get to choose which columns are where


The processes use and request different types of locks on the tables

Exclusive (X)
Shared (S)
Intent exclusive (IX)
Intent shared (IS)
Shared with intent exclusive (SIX)

And the compatability matrix looks like this:

      (X)  (S)  (IX) (IS) (SIX)
(X)    ✗    ✗    ✗    ✗    ✗
(S)    ✗    ✓    ✗    ✓    ✗
(IX)   ✗    ✗    ✓    ✓    ✗
(IS)   ✗    ✓    ✓    ✓    ✓
(SIX)  ✗    ✗    ✗    ✓    ✗

process203dad848 (A) has a X (exclusive) lock on the BasketHeader table
and is requesting an IX (Intent Exclusive) lock on the BasketItem table

process14f3f2108 (B) has an IX (Intent Exclusive) lock on BasketItem and is waiting to get a RangeS-U on BasketHeader.

processf32b5848 (C) has an IX lock on BasketItem and is waiting for it to be turned into a X lock

As you can see on the table above IX locks are compatible so seeing two of those on the BasketItem table is perfectly normal.

The RangeS-U makes this interesting as range locks only happen when you are running transactions if you are using serializable isolation level

Whats happening is that (A) holds an exclusive lock on BasketHeader and is waiting for an IntentExclusive lock on BasketItem. (B) is running in serializable isolation level and and is waiting for getting an exclusive Shared lock on BasketHeader and holds an IntentExclusive lock on BasketItem While (C) is Converting its IX lock to X lock.

(B) who is running in serializable will not be able to continue unless it gets its RangeS-U on BasketHeader and will not yield for (C). (A) cannot continue until it can get its lock on BasketItem and there you have your deadlock


SQL Server 2008 - Log file out of control & cant shrink it

Do you need point it time recovery?  If not then change the database's recovery model to Simple.

Does the database have mirroring or replication configured?  That can also cause the transaction log to blow up quite large.


What program to use to change settings on db servers?

As a MySQL DBA, may I be so bold as to suggest my favorite tool?

The mysql client program itself

There are also a variety of startup options for the client program.

Examples:

-h hostname or host IP
-P port number
-u username
-p password
-e allows execution of a semicolon delimited list of SQL queries inside double quotes
--skip-column-names will suppress the column heading for output of SQL queries

You see all the options by doing this

mysql --help --verbose

You can see global variables and status in the mysql instance using SHOW GLOBAL VARIABLES; and SHOW GLOBAL STATUS; You can also see session variables and status in the db connection using SHOW VARIABLES; and SHOW STATUS;

Once you have successfully connected to the mysql instance, you have a wide variety of session and server options that you can set for the current session or permamnently for all new sessions.


You declared the FK constraints "inline" and you fell into a mysql trap. You can check with SHOW CREATE TABLE followers that there is no FK now! This known behaviour is mentioned in the mysql documentation:

  MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

It should really say that the syntax is recognized as valid SQL but is ignored and the foreign key constraints are not created.

To solve the issue, the CREATE TABLE statement should have been:

CREATE TABLE followers (
    id_follower INT NOT NULL,
    id_following INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    PRIMARY KEY (id_follower, id_following),
    CONSTRAINT follower_fk
        FOREIGN KEY (id_follower)
        REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT following_fk
        FOREIGN KEY (id_following)
        REFERENCES users (id) ON DELETE CASCADE
);

If you already have data in the table, you can add the constraints with an ALTER TABLE statement:

ALTER TABLE followers (
    ADD CONSTRAINT follower_fk
        FOREIGN KEY (id_follower)
        REFERENCES users (id) ON DELETE CASCADE,
    ADD CONSTRAINT following_fk
        FOREIGN KEY (id_following)
        REFERENCES users (id) ON DELETE CASCADE
);


Typical wisdom is 1.5x index size, plus a little fudge depending on fill factor and how full your current index pages are (in most cases I would expect it to be slightly less than 1.5x). But I would definitely not cut it close. If you don't have 150GB free on your drive, I would find a way to do it elsewhere. I don't know if WITH DROP_EXISTING will help here, somehow I don't think so.


See SQL Server Reporting Services Reports without logging in?

You only have to run in Administrator mode because you haven't added any users to SSRS. You need to grant permissions. For example on the root folder in SSRS add a windows user/group to the Browser role.

For more details see https://technet.microsoft.com/en-us/library/ms156014%28v=sql.110%29.aspx


How does NHibernate handle execution plan?

To answer your first question, NHibernate does not handle execution plans.  SQL Server handles execution plans.  If the dynamic SQL produced by NHibernate is parameterized, the plans will be classified as "Prepared" and will be reused assuming the parameters provided in each subsequent execution could produce the same optimized query plan.  If the dynamic SQL is not parameterized, the execution plan will be classified as "Adhoc" and could still be reused.

I use this bit of T-SQL to monitor cache sizes for various query plans.  I believe I copied this from Paul Randal's site (http://www.sqlskills.com/BLOGS/PAUL/), but it has been so long that I can no longer know for sure.

SELECT
    objtype AS [CacheType],
    count_big(*) AS [Total Plans],
    sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs],
    avg(usecounts) AS [Avg Use Count],
    sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1],
    sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC;
GO


Find statements related to sys.dm_db_missing_index... tables (SQL Server)

One approach that I like to use, is to focus just on the most frequently executed queries and not all queries that might benefit from having indexes. The reason for this is the impact and cost (CPU time, I/O) that additional indexes would have on DML (DATA MODIFICATION LANGUAGE) statements.

Imagine a table customers that has 4 columns (id_customer, first_name, last_name, address) that has no nonclustered indexes.

The query UPDATE customers SET first_name = 'fred'  where id_customer = '1' only updates the table customers. If you add a nonclustered index on first_name and last_name called idx_cust_first_last, that same update statement above now has to modify not only the table customers but also the index idx_cust_first_last for the affected row or rows. This results in an additional IO and CPU cost that isn't there without the index.

In short, just because an index shows up in the missing index views, doesn't mean you should add it until you are certain that the benefit gained from that index outweighs the evenutal cost to the modifications made to your data.

Here is a link to an article written by Kimberly Tripp on the subject which you may find useful.

That said, here is a query I use to display queries that are missing indexes in desending order based on total cumulated execution time. So queries that are taking a long time to finish show up on top of the list. In my experience, the execution_count column is extremely important. This is because the execution count shows just how "popular" that query truly is. If a query takes a very long time to run, shown by the total_elapsed_time column but was only executed five times then adding an index may not be useful because that query is only run occassionaly.

Here is the script. Its not mine.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Database)[1]', 'NVARCHAR(256)') AS DatabaseName
,s.sql_handle
,s.total_elapsed_time
,s.last_execution_time
,s.execution_count
,s.total_logical_writes
,s.total_logical_reads
,s.min_elapsed_time
,s.max_elapsed_time
,p.query_plan
,p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName
,p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact
,ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage
,ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:MissingIndexes/sp:MissingIndexGroup/sp:MissingIndex/sp:ColumnGroup') AS t1 (ColumnGroup)
CROSS APPLY t1.ColumnGroup.nodes('./sp:Column') AS t2 (ColumnGroupColumn)
WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan//sp:MissingIndexes') = 1
ORDER BY s.total_elapsed_time DESC

And here is an example of the output

This queries displayed here were either currently being executed or already in cache. If the cache has recently been cleared by a server restart or by a dbcc command, then there may not be much information to display.

Microsoft's documentation on sys.dm_exec_query_plan and sys.dm_exec_query_stats which are being used to return information about cached query plans


best way to implement user with trial subscription

In the first place, renewal_date is more an attribute of Account than Institution; so it's in the wrong table. But even further, I would recommend a Subscription entity that would define a many-to-many relationship between Account and Tools.

(Oh. You don't have Tools. Well, maybe you should. You seem to have only one tool. Can you guarantee that will forever be the case?)

Then renewal_date would properly be an attribute of the relationship between Account and the tool they have access to.


How to do automatic failover for PostgreSQL with application automatic switch?

Compare the JDBC documentation:

  To support simple connection fail-over it is possible to define multiple endpoints (host and port pairs) in the connection url separated by commas. The driver will try to once connect to each of them in order until the connection succeeds. If none succeed, a normal connection exception is thrown.

  The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database

So your URL is fine. If JIRA complains about it, that would be a JIRA bug.


How to run EXPLAIN ANALYZE and get results of the query?

If you do:

set auto_explain.log_min_duration TO 0;
set auto_explain.log_level TO notice ;
set auto_explain.log_analyze to on;

then the client will receive the execution analysis.  You just need to make it capture that message and distinguish them from other messages that might come along on the same channel.  If you need an example, 'psql' does capture and display these messages (but doesn't interpret them).


What alternatives exist when a table requires too many foreign keys?

If there's any way to group parts, you might be able to introduce intermediate tables as a workaround.  This won't work.

Parts
+ Table 1
+ Table 2
+ ...
+ Table 400

But something along these lines might.

Parts
+ RedOrangeYellow parts
  + Table 1
  + Table 2
  + ...
  + Table 200

+ GreenBlueIndigoViolet parts
  + Table 201
  + Table 202
  + ...
  + Table 400

I'd want to take a hard look at your DDL before I recommended doing this, though.  And if you do this, don't start throwing ID numbers all over the place. You ought to be able to join "Table 400" directly to "Parts" without including "GreenBlueIndigoViolet parts".


Yes, why not? Did you try it?

CREATE TABLE dbo.foo(fooid INT PRIMARY KEY);

CREATE TABLE dbo.bar(barid INT PRIMARY KEY,
  fooid INT NULL FOREIGN KEY REFERENCES dbo.foo(fooid));

INSERT dbo.foo SELECT 1;
INSERT dbo.bar SELECT 1,1;
INSERT dbo.bar SELECT 2,NULL; -- succeeds
GO
INSERT dbo.bar SELECT 3,2; -- fails

If this is not what you meant, then please elaborate or re-word your question.


The Listener is a virtual object that "sits" on top of your AG, and directs your traffic.

You can create the Listener object in AD - SQL-HA-LISTENER as a computer, and grant access on it, to the actual computer nodes (replica hosts) that will be in your AG.


Event though it says its a downgrade, I would say its kind of an upgrade since Developer Edition is equivalent to enterprise in-terms of features.

A straight Start-DbaMigration (from dbatools) will copy all stuff.

I would suggest to provision new machines and just use dbatools to migrate off and then decomm the old standard edition.

Just be aware, if in PROD you are using Standard Edition and your devs started using Enterprise features since they are available in Dev edition, then you will be in trouble as those wont work in PROD being in standard edition.


What is the Server name for SQL Server2008?

The server name (and instance name) for SQL Server Express by default would be localhost\sqlexpress (or .\sqlexpress, or yourMachineName\sqlexpress, or x.x.x.x\sqlexpress, where x.x.x.x is your machine's IP address).  That is provided two things:

You on the machine that houses the database engine
You kept the defaults for SQL Server Express installation to make it a named instance, named SQLEXPRESS

As for the login.  Most likely if you just installed it, select Windows Authentication and try to connect.  I'm guessing you have added your account as SA priv.  Did you create an sa account during installation?  If so, and you enabled Mixed Mode authentication then you can log in with the sa credentials you created initially.


Cleaning up MSrepl_errors, SQL Server Replication

There a stored procedure named [sp_MShistory_cleanup] called by the job [Agent history clean up: distribution].

That job should take its params from the following setting:

Check if your job is enabled and running as scheduled.

Later edit: mentioned that job because its stored procedure has this following piece of code inside:

-- Delete MSrepl_error entries
DELETE FROM MSrepl_errors
    WHERE time <= @replerr_cutoff
    OPTION(MAXDOP 1)
SELECT @error = @@error, @num_replerror_rows = @@rowcount
IF @error <> 0
    GOTO FAILURE

This should handle the errors cleanup. I checked several of my instances and they all have only new errors, not old, so I suppose the job is also doing this part correctly.

Can you run the procedure manually and see if it runs correctly?


Mysql Connections are string when checking from SHOW STATUS LIKE '%onn%';

Their docs indicate that connections variable is,

  "The number of connection attempts (successful or not) to the MySQL
  server."

I just stopped and restarted the service on my dev server and connections dropped from 28 to 6.

Threads_connected is

  "The number of currently open connections."

and those are shown using show processlist;


For 8 million rows, I don't think there will be a lot of difference between SSIS and the SQLBulkCopy class. In SSIS, you do have to make sure you are using the fast load option in the OLE DB Destination, otherwise it will surely be slower.
If the source is local, you could try a SQL Server Destination, it might be a tad quicker than the OLE DB Destination.


Is there any difference between putting a column alias at the start or the end of the column definition?

There is no difference in the underlying functionality of the two types of aliasing (as opposed to =).  What it boils down to is exactly what you mentioned:  Readability and maintainability.

In my opinion the former (<Expression> as <Alias>) is much more readable as it is self explanatory.  When you have SELECT ColumnName = 1 I think it'd be pretty easy to mistake that as setting a variable on those long tired nights.  You might mistake that as SELECT @ColumnName = 1 and that would be completely different functionality.  Therefore, to circumvent any possibility of the query "double look", or even worse...error in understanding/coding, I go with SELECT 1 as ColumnName 100% of the time.

Personal preference, but consistency (for yourself and within your team) is king.  Whatever you find easiest, go with and do it all the time.  There is nothing more frustrating than switching back and forth for somebody troubleshooting/reviewing/maintaining code.

The third unmentioned way is to use <Expression> <Alias>.  In other words, your second way without the as keyword.  I think this is just as bad as the = symbol.  It lacks readability at the gain of what?  Not typing three extra characters (as and a space).  Not worth it.

For exaggeration purposes, take a look at a query like this:

use AdventureWorks2012;
go

select
    [New Name] = Name,
    NewDepId = DepartmentID,
    GroupName as GName,
    ModifiedDate MyModDate
from HumanResources.Department;

Not code that I'd want to review.


Your interests are generally not well-served by learning somewhat vague "rules" like "OR should be avoided" or "group_concat is slow."

Neither is actually correct, they only have elements of truth underlying them.

Use the correct capability to accomplish your purpose, while understanding that sometimes your interests are served by modifying your purpose to accommodate inherent limitations.

Using GROUP BY (not simply GROUP_CONCAT) can cause a performance hit if the optimizer has no effective way to accommodate the grouping... but this has nothing to do with whether GROUP BY should be used.  If you need it, then of course you should use it... but manipulating strings is a cake-walk compared to scanning disks and sorting without an index, and the CPU cycles consumed by the concatenation are near the noise floor.

The take-away here is to understand how the optimizer sees your query, and how it plans to resolve it.  Using EXPLAIN SELECT ... to verify that the server has made sensible choices (and that you have written a sensible query) should be second-nature to you if you want good performance.

https://dev.mysql.com/doc/refman/5.7/en/explain.html#explain-execution-plan

Interpreting the output can seem like a bit of a dark art, but you'll find it's worth the effort.


Your query is using the index to fetch the results but needs to sort them afterwards which is an expensive operation.

"scanAndOrder" : true, scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor.

The solution is to apply an index on {imei:1, type:1, time:-1} or {type:1, imei:1, time:-1} (the imei,type order on the index depends on imei and type selectivity)


SQL Import/Export defaults to byte stream on export

When you retrieve or load data, SSIS tries to automatically convert it to the correct types. If SSIS can’t implicitly convert the data—and transforming the data within the package doesn't work—you might need to modify the XML mapping files (C:\Program Files\Microsoft SQL Server\110\DTS\MappingFiles -- folder), stage the data so it’s compatible with both SSIS and the data source, create a custom component that can retrieve or load the data, or implement a solution outside of SSIS to prepare the data.

SSMS on the background creates an SSIS package. At the end it will even allow you to save it as an SSIS package.


$ rm -f /var/lock/subsys/mongod

$ rm -f /var/lib/mongodb/mongod.lock


How to find the number of queries executed since server startup in PostgreSQL

I don't believe there is a way to do that.  The view pg_stat_database will give you info on the number of transactions and number of rows, but not the number of queries.  Also, the counters are reset only upon manual intervention, or upon crashes.  Clean restarts do not reset them.


What should be innodb_buffer_pool_chunk_size for pool_size=2G, pool_instances=2?

You should leave the innodb_buffer_pool_chunk_size "as is".

Last year, Percona published some nice formulas to figure out the best chunk size

determine_best_chunk_size{
  if innodb_buffer_pool_size / innodb_buffer_pool_instances < innodb_buffer_pool_chunk_size
  then
    innodb_buffer_pool_chunk_size = roundDownMB(innodb_buffer_pool_size / innodb_buffer_pool_instances)
  fi
}
determine_amount_of_chunks{
  innodb_buffer_amount_chunks_per_instance = roundDown(innodb_buffer_pool_size / innodb_buffer_pool_instances / innodb_buffer_pool_chunk_size)

  if innodb_buffer_amount_chunks_per_instance * innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size - innodb_buffer_pool_size > 1024*1024
  then
    innodb_buffer_amount_chunks_per_instance++
  fi
}

determine_best_chunk_size
determine_amount_of_chunks

innodb_buffer_pool_size = innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size * innodb_buffer_amount_chunks_per_instance

Since you have only 2GB for the Buffer Pool, it is definitely not worth the effort to do any tuning,


I found the following information at this link which might be of some help:

SELECT * FROM sys.traces
WHERE path LIKE N'%audittrace_%'

Find the trace that is called audittrace.trc:
--Stop the trace
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 0

--Delete the trace definition from the server
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 2


Why should I create an ID column when I can use others as key fields?

1 - It's faster.  A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields.  It's more efficient to compare integers than strings.

2 - It's simpler.  It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types.

3 - It's data-independent.  If you match on the ID you don't need to worry about the relation changing.  If you match on a name, what do you do if their name changes (i.e. marriage)?  If you match on an address, what if someone moves?

4 - It's more efficient  If you cluster on an (auto incrementing) int field, you reduce fragmentation and reduce overall size of the data set.  This also simplifies indexes needed to cover your relations.

EDIT

To the specific points you just added:

1 and 2 - It's still much quicker to compare an int than a string, space considerations aside.  You are also conveniently ignoring overhead needed to store length of variable length fields (normally 2 bytes per field per row).

3 - If you cluster on the ID field then it doesn't add anything extra.  It SAVES space since you are using a more efficient row id.

4 - And then when that person changes their username your links all break.

5 - You really don't know what you are talking about here.  You do have to store the data, that's correct, but it's much more efficient to index and JOIN on the int than on a combination of other fields.


How to generate unique Auto increament id field across multiple data centers

If you are using replication to 'replicate' data between the different data centers then you can use something like:

auto_increment_increment=3

auto_increment_offset=1 #data center 1

auto_increment_offset=2 #data center 2

auto_increment_offset=3 #data center 3

which will then offset the auto increment accordingly. e.g.

data center1 - 1, 4, 7

data center2 - 2, 5, 8

data center3 - 3, 6, 9


Restoring a Database Using Full Backup and Transaction Logs in SSMS

To perform a restore to (for example) 11:45 AM on October 21st, you need the last full backup that was taken prior to that time, plus your transaction log backups up to and including Noon (assuming the log backups happen exactly on the hour and half hour) on October 21st.

As you do not have a full backup prior to October 25th, you cannot do a restore to a point in time prior to that. With the backups you currently have, you cannot achieve what you're attempting to do.


MySQL - ERROR 1045 (28000): Access denied for user

The reason you could not login as repl@'%' has to do with MySQL's user authentication protocol. It does not cover patterns of users as one would believe.

Look at how you tried to logged in

mysql -u repl -p123

Since you did not specify an IP address, mysql assumes host is localhost and tries to connect via the socket file. This is why the error message says Access denied for user 'repl'@'localhost' (using password: YES).

One would think repl@'%' would allow repl@localhost. According to how MySQL perform user authentication, that will simply never happen. Would doing this help ?

mysql -u repl -p123 -h127.0.0.1

Believe it or not, mysql would attempt repl@localhost again. Why? The mysql client sees 127.0.0.1 and tries the socket file again.

Try it like this:

mysql -u repl -p123 -h127.0.0.1 --protocol=tcp

This would force the mysql client to user the TCP/IP protocol explicitly. It would then have no choice but to user repl@'%'.


Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

To answer your first part, there is a performance impact because of disk space utilization and index maintenance.  The wider the column the heavier the read I/O will be upon record retrieval and the slower the index maintenance tasks will be.  Ideally, you want to create your field data type to be as narrow as possible to limit these two factors.

For your second part, unless you need to know what the value of the description field is, then there is no reason to join to the Ref tables when selecting out of the People table.  The FKs from the People table to the RefCode tables will guarantee valid values for the RefCode1 and RefCode2 columns upon insert/update into the People table.


SQL - Is it possible to have a Trigger statement with a WHERE clause that is an equality measure

In Oracle triggers, you can access the modified table using :NEW (for inserted and after an update data) and :OLD (for deleted and before the update).

Also there is noUPDATE .. FROM syntax. Your trigger can be written:

CREATE TRIGGER team_no_inc
    BEFORE INSERT ON Heroes
    FOR EACH ROW
BEGIN
    UPDATE Teams SET no_of_members = no_of_members + 1
    WHERE Teams.team_id = :NEW.team_id;
END;

Tested at dbfiddle.uk.


How to extract certificate attributes from a single column and split into many?

The first part is only to show you that the procedure works

CREATE tABLE tablecertificate  (
stationery_name varchar(500)

INSERT INTO tablecertificate values
("CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"),
("CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"),
("OU=Eng, O=Company Ltd")

CREATE PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE certificate varchar(500) DEFAULT "";
  DEClARE curcertificate
      CURSOR FOR
          SELECT stationery_name FROM tablecertificate;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

  #Temporary Table that holds the splittet part
   DROP TEMPORARY TABLE IF EXISTS mycertificate;
  CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

  OPEN curcertificate;
    getcertificate: LOOP
      # get first row elemnt
      FETCH curcertificate INTO certificate;
      IF finished = 1 THEN
          #Last element reached
          LEAVE getcertificate;
      END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
      SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
      SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
          SET current_cert_part = rest_cert_part;
      END IF;
      #Examine first element of the string
        CASE
          WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
          #loop throuigh the string
          set  current_pos = LOCATE(delim,rest_cert_part);
          set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
          set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

          if length(trim(current_cert_part)) = 0   then
              #ÖLast elemnt in string
              set current_cert_part = rest_cert_part;
          end if;
          CASE
              WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
          END CASE;
      END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);

  END LOOP getcertificate;
  CLOSE curcertificate;
SELECT * FROM mycertificate;
END

cALL splitcertifcate();

CN       | OU     | O             | L    | S        | C
:------- | :----- | :------------ | :--- | :------- | :---
CN=User1 | OU=Eng | O=Company Ltd | L=D4 | S=Dublin | C=IE
CN=User2 | OU=Eng | O=Company Ltd | L=D2 | S=Dublin | C=IE
         | OU=Eng | O=Company Ltd |      |          |

✓

db<>fiddle here
The complete stored procedure including producing the tsv file
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE certificate varchar(500) DEFAULT "";
    DEClARE curcertificate
        CURSOR FOR
            SELECT stationery_name FROM tablecertificate;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

    #Temporary Table that holds the splittet part
     DROP TEMPORARY TABLE IF EXISTS mycertificate;
    CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

    OPEN curcertificate;
    getcertificate: LOOP
        # get first row elemnt
        FETCH curcertificate INTO certificate;
        IF finished = 1 THEN
            #Last element reached
            LEAVE getcertificate;
        END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
        SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
        SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
            SET current_cert_part = rest_cert_part;
        END IF;
        #Examine first element of the string
        CASE
            WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
            WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
            #loop throuigh the string
            set  current_pos = LOCATE(delim,rest_cert_part);
            set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
            set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

            if length(trim(current_cert_part)) = 0   then
                #ÖLast elemnt in string
                set current_cert_part = rest_cert_part;
            end if;
            CASE
                WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
                WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
            END CASE;
        END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);

    END LOOP getcertificate;
    CLOSE curcertificate;

    SELECT * FROM mycertificate INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cert.tsv"
    FIELDS TERMINATED BY '\t'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
END//
DELIMITER ;

Result tsv file
"CN=User1"  "OU=Eng"    "O=Company Ltd" "L=D4"  "S=Dublin"  "C=IE"
"CN=User2"  "OU=Eng"    "O=Company Ltd" "L=D2"  "S=Dublin"  "C=IE"
""          "OU=Eng"    "O=Company Ltd"  ""       ""        ""

You must check the INTO OUTFILE folder, that must correspond with the entry in the my.ini/cnf file
secure-file-priv='C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/'


1 big merge publication or N small ones?

I've been looking for an answer to no avail.

However, I've found a place where some advantages are mentioned:

Easier to maintain.
Multiple data streams going to the subscription database at once (hopefully).
If the merge fails for a publication, only this publication has to be fixed and reinitialized.

So I think there would be no performance improvement having just 1 big one publication. On the contrary, if the data streams are parallel, we can even get a better performance when splitting the merge in N small publications.


Run SQL Profiler to identify who, where etc. You have various columns with hostname, ntusername etc that will allow you to see usage.

For example: is if just one client or user who notices? Are they running a different ADP?

The view call can be embedded in some Form properties too as the RecordSource for that form, or as the source for some control. It may not be in VBA. You'd have to write some VBA to iterate through the forms, controls and reports testing assorted properties.


SQL Server Trigger When New Value  Old Value

There are a few things that need to be fixed in your trigger:

The WHERE condition of c2.NAME <> I.NAME will always be false since the inserted pseudo-table is the current definition of those same rows in the real table (at least in an AFTER Trigger). This one line was preventing anything else from happening.
If it weren't for issue #1, you would be seeing "odd" behavior regarding what rows in the contact table were being updated by this Trigger. The issue here is that you have two independent references to the same table in the main UPDATE clause and the FROM clause. You have UPDATE contact (notice there is no table alias) and then FROM contact c2 (notice the c2 alias, making this a second reference to the same table. You can either remove the c2 alias from the FROM clause (such that both references are just contact), OR, what I recommend for readability is to keep the alias and use it in place of the table name in the UPDATE clause, such that it reads: UPDATE c2 ....
Finally, when comparing strings to determine if there are any changes, you really need to use a binary collation (one ending in _BIN2 if available, else one ending in _BIN). This will prevent any "insensitive" options (such as case and/or accent) from masking changes, and it will also prevent string normalization from masking any changes.

Updated code:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE (NAME))
  BEGIN
    UPDATE con
    SET    con.ERROR = NULL
    FROM   contact con
    INNER JOIN DELETED del
            ON del.ID = con.ID
    WHERE  ins.NAME <> del.NAME COLLATE Latin1_General_100_BIN2
  END;
END;


If you did not take a log backup between the full yesterday and your restore today, then no, everything changed since yesterday is gone.

In a best case "accidently deleted data" scenario, the process is to take a log backup to capture the "tail"of the log, restore a full backup of the DB to an alternate location, restore log backups using STOPAT to get to the point right before the accidental delete and copy the data.

If you don't have the room to restore an extra copy, or the accidental deletion is severe enough that you must restore over the existing database, then it is twice as important to backup up the tail of the log.


The solution of the problem is that table MCOUNTRY did not have the primary key. It was only configured with unique constraint that was called is the same way that primary keys. Even when the unique column was used in query Oracle could not provide expected result.

Something obvious but still lesson to learn, unique constraint is not the same as primary key where it comes to joins.

Have a nice day.


Which version of linux OS has support for MongoDB installation?

You should be able to easily install on most (if not all) flavours of x86-based Linux distributions.

There are officially packaged versions for:

Ubuntu
Debian
RPM (Redhat, CentOS, Fedora, Amazon Linux)

You can also install from tarball, or check if there are packages available in the "ports" equivalent for your distribution.


How to check if there are no time gaps in PostgreSQL?

Chapter 1: Linked List

One way to have this (no gaps) type of constraint enforced in the database is to split activity into the starting and ending parts and then use UNIQUE and FOREIGN KEY constraints to emulate a linked list.

1a.

Every activity_start should reference the previous activity_end:
(country, activity_start) REFERENCES (country, activity_end).
Two periods cannot have the same activity_start and country or activity end and country:
UNIQUE constraint on (country, activity_start).
and UNIQUE (country, activity_end).
We actually don't need both of them, only the second one, for the foreign key to be defined. The exclusion constraint doesn't allow two periods to have the same start or end.
We should not allow multiple rows with (-Infinity, +Infinity) or series like: -Infinity -> DateA -> Infinity -> DateB -> +Infinity. This is achieved with the two partial indexes.

Code:

CREATE TABLE expenses_commissionrule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity tsrange NOT NULL,
    activity_start timestamp,
    activity_end timestamp,
    CONSTRAINT non_overlapping_activity
        EXCLUDE USING GIST (country WITH =, activity WITH &&),
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end),
    CONSTRAINT activity_start_end_fk
        FOREIGN KEY (country, activity_start)
        REFERENCES expenses_commissionrule (country, activity_end),
    CONSTRAINT activity_ck
        CHECK (activity IS NOT DISTINCT FROM
               tsrange(activity_start, activity_end, '[)') )
) ;

CREATE UNIQUE INDEX country_start_ufx
    ON expenses_commissionrule
        (country)
    WHERE (activity_start IS NULL) ;

CREATE UNIQUE INDEX country_end_ufx
    ON expenses_commissionrule
        (country)
    WHERE (activity_end IS NULL) ;

We can then try to insert (valid) data:

WITH ins
    (country, activity_start, activity_end)
  AS
    ( VALUES
          ('IT',  null::timestamp,  null::timestamp),

          ('FR',  null,             '2000-01-01'),
          ('FR',  '2000-01-01',     null),

          ('GR',  null,             '2000-01-01'),
          ('GR',  '2000-01-01',     '2012-01-01'),
          ('GR',  '2012-01-01',     '2017-06-01'),
          ('GR',  '2017-06-01',     null)
    )
INSERT INTO expenses_commissionrule
    (country, created, modified, activity, activity_start, activity_end)
SELECT
    country, now(), now(),
    tsrange(activity_start, activity_end, '[)'),
    activity_start, activity_end
FROM ins ;

Works fine:

> INSERT 0 7

And try with invalid data:

--
( VALUES
      ('US',  null::timestamp,  '2000-01-01'::timestamp)
)
--

-- Fails:

> ERROR:  insert or update on table "expenses_commissionrule" violates
      foreign key constraint "activity_start_end_fk"
> DETAIL:  Key (country, activity_end)=(US, 2000-01-01 00:00:00) is not
      present in table "expenses_commissionrule".

Another try:

( VALUES
      ('UK',  null::timestamp,  '2000-01-01'::timestamp),
      ('UK',  '2000-01-01',     '2000-01-01')
)

-- Fails:

> ERROR:  duplicate key value violates unique constraint
      "country_activity_end_uq"
> DETAIL:  Key (country, activity_end)=(UK, 2000-01-01 00:00:00)
      already exists.

1b.

After all these, we can spot that activity is not really needed in the table as we have start and end and we can compute it. Thus it can be removed:

CREATE TABLE expenses_commissionrule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity_start timestamp,
    activity_end timestamp,
    CONSTRAINT non_overlapping_activity
        EXCLUDE USING GIST
            (country WITH =,
             tsrange(activity_start, activity_end, '[)') WITH &&),
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end),
    CONSTRAINT activity_start_end_fk
        FOREIGN KEY (country, activity_start)
        REFERENCES expenses_commissionrule (country, activity_end)
) ;

-- plus the two filtered indexes. We do need those.

1c.

And then we realize that - due to the foreign key we added - we don't really need the exclusion constraint any more. We can have the same effect by enforcing that activity_end is after activity_start.

CREATE TABLE expenses_commissionrule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity_start timestamp,
    activity_end timestamp,
    CONSTRAINT non_overlapping_activity
        CHECK (activity_start < activity_end),
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end),
    CONSTRAINT activity_start_end_fk
        FOREIGN KEY (country, activity_start)
        REFERENCES expenses_commissionrule (country, activity_end)
) ;

-- plus the two filtered indexes. We do need those.

Chapter 2: No List

After the all the tedious effort, let try something simpler. No explanation this time, lets read the code first and explain later:

CREATE TABLE ec_rule (
    id serial NOT NULL PRIMARY KEY,
    country varchar(2) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    activity_end timestamp,
    CONSTRAINT country_activity_end_uq
        UNIQUE (country, activity_end)
) ;

CREATE UNIQUE INDEX country_end_ufx
    ON ec_rule (country)
    WHERE (activity_end IS NULL) ;

OK, what happened here? This is much simpler, it can't possibly be used the same way as the previous designs! It can't possibly be equivalent. Or maybe it can?

Lets see what happens here:

activity_start is missing completely. How are we supposed to find the periods if they are not stored in the table?

The answer is that activity start is indeed stored, just not in the same row as activity end. That's what the foreign key was all about, to ensure that every end had a matching start. So we can easily find the start of every period using LAG():

CREATE VIEW expenses_commissionrule AS
SELECT
    id,
    country,
    created,
    modified,
    LAG(activity_end) OVER (PARTITION BY country
                            ORDER BY activity_end)
        AS activity_start,
    activity_end
FROM
    ec_rule ;

Chapter 3: Spot the difference

While all the above design manage to enforce the "no gaps" rule, they all fail to enforce the second rule: "the sum of all intervals should be -INF : +INF."

Can this be amended? It seems to be easier after all once we have achieved no gaps.

Well, yes and no. And no. It isn't easier. It's similar to enforcing that a table has at least one row. That seems easy, too, but it's actually very hard, if not impossible with DDL alone.

For the specific problem though, the "yes and no" means that the rule:

can be enforced with design 1 (although it makes it even more complicated).
but not with design 2 (at least I can't see a way).

For design 1, we'd need to add a second foreign key (from activity end to the next start) and a unique constraint (country, activity_start).  This essentially would convert our list to a doubly linked list. And for the foreign keys to be satisfied, the list would have to either be infinite (which isn't possible) or have ends both left and right, which means two rows with nulls, one for left and one for right (foreign keys are satisfied if one of the columns is NULL).

For design 2, you'd have to ensure - outside of DDL - that for every country there is one row where activity_end is NULL (that row is the rule for the period up to +Infinity).


SQL DATEDIFF coercion differences between databases on same SQL instance?

This will happen if you are in a lower compatibility level (80) and indirectly try to convert a value that is outside the supported range of the older types. I have added an example illustrating exactly this scenario in the following answer:

What is the actual behavior of compatibility level 80?

See the section of my answer entitled Conversions involving new date/time types.


Can I configure Oracle such that a Windows client can connect to a Linux Oracle server without a password?

Kerberos is way too complicated for this task, I would not bother with it.

ODAC supports using the a Secure External Password Store:

Using Secure External Password Store

You can point to the wallet location in your configuration in the settings section .

An example:

https://dbaportal.eu/2015/11/26/odac-12c-release-4-odp-net-managed-driver-with-oracle-wallet-gotcha/


MongoDB - Change primary shard only for a certain database

Yes you can with a single command:

db.adminCommand({ movePrimary : "YourDBName", to : "shard001" })

then is important to flush configuration on all mongos (suppose all than the one that did the move, but better be safe):

db.adminCommand({flushRouterConfig: 1})

If your databases are small you can do it on-the-fly, else you better plan for a downtime only for this database during the move process.

Useful link:
http://docs.mongodb.org/manual/reference/command/movePrimary/


MySQL replication 'connecting to master'

If you have validated the credentials you are using for replication and they work connecting through another client, your 1045 error may lie with the new default authentication plugin in MySQL8. When trying to connect with an insecure connection and an account created with the caching_sha2_password, it will continuously attempt the connection without being successful.

Try connecting using the same replication credentials from the slave to the master through a MySQL client. This will ensure your networking and credentials are both valid. If you cannot do this, check your network/firewall and your replication credentials.


Trace Flag 9567 : Enables compression of the data stream for Always On
Availability Groups during automatic seeding. Compression can
significantly reduce the transfer time during automatic seeding and
will increase the load on the processor. For more information,
see Automatically initialize Always On availability group and Tune
compression for availability group.

As the document says, just when automatic seeding process during. Synchronization doesn't relate to this trace flag.


sp_send_dbmail is populating 00 bits between all characters in a result set

The file encoding for the attachments is standard UTF-16 Little Endian (LE) -- one of the various Unicode encodings. The first two bytes, shown in the first image, are FF FE. These are the Byte Order Mark (BOM), which is an explicit indication of the files encoding. And you can see in the chart on that linked Wikipedia page that FF FE indicates an encoding of UTF-16 Little Endian.

UTF-16 uses 2 or 4 bytes per "character", so the 00 null bytes are really just half of each of those characters. And, because it is Little Endian instead of Big Endian, the bytes in each pair are swapped. Meaning, the highlighted 00 in the top image is paired with the preceding 50 (for the capital P). But the pair 50 00 is just Little Endian for 00 50, which is Unicode Code Point U+0050 (there is a chart at the bottom of that page that shows the encoding variations).

If you were to open that file in any editor that auto-detected the file encoding via the BOM, it would know that it was a UTF-16 LE encoded file and display it correctly, as you would expect to see it. Just try it out in something like Notepad++.

The first thing I would do is contact the vendor and/or check the documentation to see if either the e-commerce platform already has a means of dealing with UTF-16 LE encoded files (a.k.a. "Unicode" in Microsoft-land), OR if they can make a small change to handle Unicode files. I would be surprised if they both don't already handle this in some fashion and won't do it if not already able to. Given that this world is moving towards more internationalization rather than less, you can't be the only client needing to pass in a Unicode file. Also, converting the file to ASCII Extended could lead to data loss if your data naturally has characters in it that do not fit into a single code page.

The last thing I would do is update the definition of sp_send_dbmail. I am not saying to never, ever do that, but in most cases it is unnecessary. If anything, it would be preferable to convert the encoding of the attachment after it is sent (which will probably need to be done by the recipient of the email). An intentional conversion will allow for specifying the correct encoding, whether it be UTF-8 or Extended ASCII using one of the many available code pages.


Why query optimizer doesn't use negative filter index

Why do you think it should use the filtered index? You've used SELECT * in your query and your predicate involves more than just the filter condition. SQL Server is going to have to look up those columns in another index that covers it anyway (your SELECT * plan should include a key lookup to retrieve those other columns).

SQL Server will decide based on stats/cardinality (e.g. how many rows currently match the filter, how many rows total) and decide at that point which index it should use. In this case, it is probably a coin flip, because it can seek to the 19 rows that match the ParentId predicate, and not cost any more in I/O to do so. It might choose the filtered index if it represented a much smaller portion of the table than 30%. You should see what it does, though, if you add OPTION (RECOMPILE) to the query. Depending on your parameterization settings, it may be using a plan that was cached with a different parameter value (e.g. one that doesn't match the index filter).

In this case, I believe you are seeing this behavior because, well, RTM. Please install Service Pack 3 and consider Cumulative Update 16. I browsed quickly and didn't see anything relating to filtered indexes, but this could be a by-product of some other optimizer wonkiness that has been fixed in the meantime. In any case, I can't really think of any good reason why you should continue running RTM at this time.

Could also be related to these are other "bugs known gaps in functionality":

Connect #329459 : SQL does not use filtered index on a simple query
Connect #454744 : Filtered index not used and key lookup with no output
Connect #643850 : Filtered Index execution plan is not optimized

I've also blogged about a whole slew of limitations with filtered indexes, and pointed to a ton of related Connect items. Most of these remain unfixed and, for the most part, filtered indexes have received absolutely no attention since they were first introduced. If you're going to use these objects, and expect them to be used to speed up your queries, you'd be wise to familiarize yourself with all of these limitations, and consider using an index hint to force them to be used in some scenarios. Caveats with using an index hint, of course:

It may not work better (you don't always know better than the optimizer); it may work better for some queries but not all. Usually SQL Server will use the filtered index if (a) it can, and (b) it deems it to be the most efficient access method.
Queries will fail if the index is dropped, or re-created with a new name.


What is the preferred / common way to separate "access-tokens" in a database design

The answer is neither.  Since a user could have multiple tokens, the primary key is not the same and it's meaning within the context of the application is not the same as the tokens used for email verification/password reset.
So:

You have a table for session_token.  If you will search by token to validate a user then the primary key is token.  If you will primarily search by user the primary key will be (user_id,token) with an unique constraint on token.
You have a table for user_token or something similar.  The primary key is (user_id,token_type).  You may or may not need an alternate key on token depending on how things are used.  This is preferable to separate tables as you can add a new type without creating a new table.  You will need a table token_type that provide integrity to that column.  Use values that are easy to understand.

Do not slap an row identifier on each table (id) as:

It does not provide uniqueness
It is unnecessary to replace the existing primary key
It is counterproductive as it will never be used to access either table.


How to design a database for storing a sorted list?

If rank isn't completely arbitrary but is instead derivable from some other property (e.g. name, player score, etc.) then take a good look at Joel's answer.

If it is an arbitrary property of your data, then that should be stored as a column in your table of records. Assuming Amazon's SimpleDB is similar to the typical RDBMS, you can then index this column and quickly satisfy all your above queries with the appropriate indexing strategy. This is normal for an RDBMS.

Given that you expect high insert and update activity, but also relatively high read activity, I recommend doing the following:

Cluster the table on the rank, especially if the vast majority of your queries are against rank. If not, or if choosing a clustering key is not available in SimpleDB, then just create an index with rank as the leading column. This would satisfy queries 3-6.
An index on the record first and then rank (or, in the SQL Server world, just record and INCLUDE-ing rank, or just record if you've clustered on rank) would satisfy query 7.
Operations 1 and 2 can be optimized by spacing out your data appropriately (i.e. setting the FILLFACTOR in SQL Server). This is especially important if you cluster on rank.
As you insert or update ranks, maintain as much of a gap between rank numbers as possible to minimize that possibility that you will need to re-rank an existing record to accommodate a rank insert or update. For example, if you rank your records in steps of 1000 you leave enough room for about half that many changes and inserts with minimal chance you'll need to re-rank a record not directly involved in those changes.
Every night re-rank all records to reset the rank gaps between them.
You can tune the frequency of the mass re-rankings as well as the rank gap size to accommodate your expected number of inserts or updates relative to the number of existing records. So if you have 100K records and expect your inserts and updates to be 10% of that, leave enough room for 10K new ranks and re-rank nightly.
Re-ranking 500K records is an expensive operation, but done once a day or week off-hours should be fine for a database like that.  This off-hours mass re-ranking to maintain the rank gaps is what saves you having to re-rank many records for each rank update or insert during your normal and peak hours.

If you expect 100K+ reads on a 100K+ sized table I do not recommend using the linked list approach.  It will not scale well to those sizes.