Can I upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer?

That is not a supported upgrade path.

  how would you recommend I proceed?

My recommendation would be to install side-by-side and then migrate (backup and restore) your databases to the SQL Server 2014 instance.


Understanding time format of the EXPLAIN command - Postgres

actual time=8163.890..8163.893 means

Initializing that step ("startup") took 8163.890ms
Running the whole step took 8163.893ms

So in that case nearly the complete work was done in the startup phase of that step.

Edit:
The same logic is "applied" to the cost information

cost=2928781.21..2929243.02 means:

The cost to initialize this step was estimated at: 2928781.21
The cost to perform the step was estimated at: 2929243.02

(note that "cost" does not have a unit - it's an arbitrary value)

This is also explained here:
http://www.postgresql.org/docs/current/static/using-explain.html


DACPAC Deployment Rebuilding All Indexes

The most likely reason this is happening is because there are different options / settings on the indexes in the model (the dacpac file) and the target (your actual database).  The deployment, by default, will rebuild the index with the new options to get the target to match the model.

There are two deployment options you can enable to avoid this.  I would try them one at a time to see which one is causing the rebuild:

/p: IgnoreIndexOptions=True
/p: IgnoreLockHintsOnIndexes=True

There are two more options that ignore index-related things (IgnoreFillFactor and IgnoreIndexPadding).  These are enabled by default, but you might want to check and make sure your publish process isn't explicitly setting them to False.


Why we design distributed availability group?

What is the distributed high availability benefit which we can not leverage with traditional AG? If you can explain one scenario of failover which we can not avail in traditional AG?

Distributed availability groups are a new feature introduced in SQL Server 2016, as a variation of the existing Always On Availability Groups feature. No announcements have been made as to whether future versions of SQL Server will allow previous versions to participate in the same distributed availability group. If that scenario were enabled, it would allow distributed availability groups to be part of a SQL Server version upgrade plan.

For your understanding I have just copy and paste the appropriate stanza articles from the documentation for Distributed availability groups:

  A distributed availability group is a special type of availability group that spans two separate availability groups. The underlying availability groups are configured on two different Windows Server Failover Clustering (WSFC) clusters. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability-group deployment. As long as two availability groups can communicate, you can configure a distributed availability group with them.

  The following figure shows a high-level view of a distributed availability group that spans two availability groups (AG 1 and AG 2), each configured on its own WSFC cluster. The distributed availability group has a total of four replicas, with two in each availability group. Each availability group can support up to the maximum number of replicas, so a distributed availability group based on Standard Edition can have up to four replicas, and one based on Enterprise Edition can have up to 18 total replicas.

  The individual WSFC clusters and their corresponding availability groups follow traditional rules. That is, they can be joined to a domain or not joined to a domain (Windows Server 2016 or later). When two different availability groups are combined in a single distributed availability group, there are four scenarios:

  Both WSFC clusters are joined to the same domain.
  Each WSFC cluster is joined to a different domain.
  One WSFC cluster is joined to a domain, and one WSFC cluster is not joined to a domain.
  Neither WSFC cluster is joined to a domain.

Your second question:

  In which scenario the design of distributed AG is preferable?

Again, quoting from the same documentation:

  Here are the three main usage scenarios for a distributed availability group:

  Disaster recovery and easier multi-site configurations
  Migration to new hardware or configurations, which might include using new hardware or changing the underlying operating systems
  Increasing the number of readable replicas beyond eight in a single availability group by spanning multiple availability groups


How Does SSIS Manage Connection Traffic?

The machine, or in this case SSIS server, that calls the DTSX package will be where it executes, using its local resources (i.e. CPU and RAM) to channel data through the SSIS server.  The ETL transformations will be done on the SSIS server, again using its resources. The remote machines with data sources and destinations will still see disk IO or ram increase, but not much else.

Executing ETL jobs on database servers might not be the best idea depending on what you're doing, as this could cause the database server to be slow. Just look at what you're trying to accomplish and go from there.

I've read and used as a reference WROX's Professional Microsoft SQL Server 2012 Integration Services.  Great resource if you're going to be using SSIS a lot.

Understanding the Differences between Local and Remote Execution


There are several ways to do this. Either remove the join and convert the subqueries to correlated:

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b
     WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS PET3,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS  b
     WHERE b.TIP = 'ALT' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS ALT3
FROM ANEXA2A_TABEL1  a ;

or keep the join and use GROUP BY (I converted to LEFT join so you also see results for values that the second table has no related rows):

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    SUM(CASE WHEN b.TIP = 'PET' THEN b.CANT ELSE 0 END) AS PET3,
    SUM(CASE WHEN b.TIP = 'ALT' THEN b.CANT ELSE 0 END) AS ALT3
FROM ANEXA2A_TABEL1  a
    LEFT JOIN ANEXA2A_TABEL2_ROWS  b
        ON b.FK_ID_ANEXA = a.FK_ID_ANEXA
GROUP BY
    a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2 ;

There are several more options to do this (i.e. first group by table b in a derived table and the join to table a, or using PIVOT, or using the specific to Oracle function DECODE instead of CASE,  ...)


Only a sysadmin can specify '@output_file_name'

To the best of my knowledge, this is a hard rule in SQL Server. See for instance here: https://docs.microsoft.com/en-us/sql/ssms/agent/job-step-properties-new-job-step-advanced-page.
You can of course wish for a change in Agent security architecture, but I don't hold my breath for that to happen: https://feedback.azure.com/forums/908035-sql-server.
In the end, perhaps you can work ut an interface with wrappers, signed procedures that creates your jobs, so they are owned by sysadmin without making this person a sysadmin. I can't say for sure whether it'll work out for you in the end, though, but perhaps worth looking into. This would be a good start: http://www.sommarskog.se/grantperm.html.


Log all user error messages for retrospective troubleshooting

The problem with a server side trace is that logging the error messages without any context probably isn't very helpful and collecting that context requires tracing and filtering the more expensive events such as SQL:StatementCompleted

As you are on SQL Server 2008 you can look at extended events for this. This is much more efficient as instead of capturing all statements and then discarding those without error it only fires on error then retrieves the statement text. For some example code see my answer here. though I recommend you download the Extended Events SSMS Addin and customize it for your needs.


If you must keep keywords as a CSV, you can use FIND_IN_SET().

SELECT COUNT(*) as num
FROM table
WHERE FIND_IN_SET('hello', keywords)
AND FIND_IN_SET('world', keywords);


Changing database context before creating new database

Often these examples are accompanied by (or are expected to be copied and pasted to a script with) other commands, such as a DROP DATABASE first (which can’t happen if you’re in the context of that database that you’re trying to drop and re-create).

There are a few commands that need to be run in master, but not a whole ton. This may just be a convention by habit rather than by reason, and in all honesty, it probably is the source of many mistaken deployments to master instead of a user database. I wrote a tip about this situation:

Cleaning up the SQL Server master database


Many to Many Table with multiple same-column references

I think unless you have to save space or share a lot of translations you could just do one-to-many mapping documents -> document_translations

CREATE TABLE document_translations
(
    document_id BIGINT NOT NULL,
    language_id INT NOT NULL,
    title TEXT,
    summary TEXT,
    CONSTRAINT document_translations_pk PRIMARY KEY (language_id, document_id)
);

Then you could use external translation memory to help with translations.

If you want to share translations then I don't think you need translated_documents_translations as many-to-many table. Just use translated_documents like this:

CREATE TABLE translated_documents
(
  document_id BIGINT NOT NULL
    CONSTRAINT translated_documents_pkey
    PRIMARY KEY
    CONSTRAINT translated_documents_documents_id_fk
    REFERENCES documents,
  language_id INTEGER,
  title UUID,
  summary UUID,
  CONSTRAINT translated_documents_title_fk
  FOREIGN KEY (title, language_id) REFERENCES translations,
  CONSTRAINT translated_documents_summary_fk
  FOREIGN KEY (summary, language_id) REFERENCES translations
);

CREATE TABLE translations
(
  content_hash UUID NOT NULL,
  language_id INTEGER NOT NULL,
  translation TEXT,
  CONSTRAINT translations_content_hash_language_id_pk
  PRIMARY KEY (content_hash, language_id)
);

And JOIN documents -> documents_translations -> translations


How do I generate a Loader_Generate_Script for all states and regions?

All of this is stored in tiger.state

SELECT stusps,name,division,statefp
FROM tiger.state
ORDER BY name;

The script expects it's arguments in the form of the two character stusps code,

\t
\a
\o /gisdata/statescript.sh
SELECT Loader_Generate_Script(ARRAY['AL' , 'AK' , 'AS' , 'AZ' , 'AR' , 'CA' , 'CO' , 'MP' , 'CT' , 'DE' , 'DC' , 'FL' , 'GA' , 'GU' , 'HI' , 'ID' , 'IL' , 'IN' , 'IA' , 'KS' , 'KY' , 'LA' , 'ME' , 'MD' , 'MA' , 'MI' , 'MN' , 'MS' , 'MO' , 'MT' , 'NE' , 'NV' , 'NH' , 'NJ' , 'NM' , 'NY' , 'NC' , 'ND' , 'OH' , 'OK' , 'OR' , 'PA' , 'PR' , 'RI' , 'SC' , 'SD' , 'TN' , 'TX' , 'VI' , 'UT' , 'VT' , 'VA' , 'WA' , 'WV' , 'WI' , 'WY'], 'sh');

(the method of \t \a \o was borrowed from PostGIS in Action Second Edition)


Using GROUP BY WITH ROLLUP on column that contains NULL

you have a "solution", so by saying

  I am wondering if there is a better solution.

I think you want to comment about it. Let me focus on several aspects:

SELECT ID [...] GROUP BY word, independently of having or not a WITH ROLLUP is wrong. You are selecting a field whose value is undetermined. In particular, with MySQL you get exposed of returning a random value; or if you use an strict mode (recommended, and by default in the latest versions of MySQL), of the query to fail: 'db_9_b0ff7.test.ID' isn't in GROUP BY
Be it formally or not, WITH ROLLUP Note- (I refer to MySQL implementation) is something that is nice to have in some cases because it avoids a double rescan of the table, but I have not seen a lot in production code. Nothing wrong with it, but they were an exclusive MSSQL feature until it was implemented on SQL1999, and either it was not available widely back in the day or, like in the case of MySQL it was a limited implementation. Reference: https://www.percona.com/blog/2007/09/17/using-group-by-with-rollup-for-reporting-performance-optimization/
I do not see in principle a problem with your final query, if we run explain on both queries (without ID), we see:

MariaDB [test]> EXPLAIN SELECT   if(count(word) = 0, "empty", ifnull(word, "total")) as word,   count(*) as occurrences FROM test group by word with ROLLUP\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

Warning (Code 1052): Column 'word' in group statement is ambiguous
MariaDB [test]> EXPLAIN SELECT   ifnull(word, "total") as word,   count(*) as occurrences FROM ( SELECT   ifnull(word, "empty") as word FROM test ) tmp GROUP BY word WITH ROLLUP\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

Normally the hate towards MySQL is because its limitations on early versions of MySQL. I am not going to say that it is now perfect, there will be still issues, as MySQL development, as it has always been, focused on a fast, easy-in, easy-out RDBMS, and not a full-blown relational engine, but a subquery in the right place is ok (after all, some queries will require a subquery, no matter what. Your usage is ok- it will only have an overhead of going over the summarized rows again and I suppose on the real deal, those will not be significant compared to the full set.

Note- on my instance up here, the subquery is not showed, but you can see it with materialized subqueries in 5.6 here: http://sqlfiddle.com/#!9/b0ff7/7 (It will execute the inner query and then the outer part, with no issue). An index could definitely help here (due to the filesort), but that depends on the final number of records. The subquery may hurt performance if an index (which does not exist now) cannot be used because it. You have to test your specific mysql version and create such an index.
I think your problem is with the data model- in the SQL world (outside of Oracle) NULL is an unknown/invalid value. Seem to be correlating that to 'empty' which is not right- if a word is known to not exist, it should be '' (the empty string, not NULL). I do not know if you can change that, but I do not like your current model. I understand that you may not have something to say about it, so I will not focus on that. Alos you count NULL words? More reasons to use '' instead of NULL.
Now the final question is, is it possible to do it without a subquery on MySQL, and will it be better/less ugly? I do not really see a way- we could do a join, but it will still be to a subquery. I can think of an alternative, which is:

SELECT * FROM (
    SELECT   word,
    count(*) as occurrences
    FROM test
    GROUP BY word with rollup)
tmp
ORDER BY occurrences;

That makes sure that the last row contains the summary (and if there is other null, it is the actual null value). The order would happen even on the case of only null values. On the contrary, it may even slower as it requires ordering, and again you may need an index, and that index may or may not be used.


impdp import fails with object already exists in oracle 10g?

Looks like a serious error. What is writte in the alertlog file?
First thought is that the file is damaged.
How exactly did you drop the tablespace[s]?
how exactly did you perform the export?
how exactly did you re-create the tablespace[s]?
how exactly did you re-create the schema?
how exactly did you perform the import?
You should have dropped them using

drop tablespace X including contents and datafiles;

This would have taken care for cleanup of the old tablespaces. I also expect that you would have gotten errors during the import. When messing with databases and tablespaces, a little dba training would be very good. For 10g start here Oracle® Database 2 Day DBA
10g Release 2 (10.2)

and yes, this is more a dba question and task than for a developer.


How to stop Firebird SuperClassic (run as application) in Win 7?

I don't have enough points to make this a comment, but it looks like based on this page
the answer is net stop fb_inet_server.


THIS IS THE QUERY YOU NEED

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SET @nexttime = 0;
SELECT
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)
;

SAMPLE DATA

mysql> use test
Database changed
mysql> drop table if exists airport_taxi;
Query OK, 0 rows affected (0.13 sec)

mysql> create table airport_taxi
    -> (id int not null auto_increment primary key,
    -> date date,time time,direction varchar(10));
Query OK, 0 rows affected (0.38 sec)

mysql> insert into airport_taxi values
    -> ( 7,'2014-04-15','21:35:00','to'),
    -> ( 8,'2014-04-15','15:20:00','from'),
    -> ( 9,'2014-04-15','17:35:00','to'),
    -> (10,'2014-04-15','08:15:00','to'),
    -> (11,'2014-04-15','14:40:00','to'),
    -> (12,'2014-04-15','11:35:00','to'),
    -> (13,'2014-04-15','14:35:00','from');
Query OK, 7 rows affected (0.21 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from airport_taxi;
+----+------------+----------+-----------+
| id | date       | time     | direction |
+----+------------+----------+-----------+
|  7 | 2014-04-15 | 21:35:00 | to        |
|  8 | 2014-04-15 | 15:20:00 | from      |
|  9 | 2014-04-15 | 17:35:00 | to        |
| 10 | 2014-04-15 | 08:15:00 | to        |
| 11 | 2014-04-15 | 14:40:00 | to        |
| 12 | 2014-04-15 | 11:35:00 | to        |
| 13 | 2014-04-15 | 14:35:00 | from      |
+----+------------+----------+-----------+
7 rows in set (0.00 sec)

QUERY EXECUTED

mysql> SET @GivenDate = '2014-04-15';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
    -> FROM airport_taxi WHERE date=@GivenDate;
Query OK, 1 row affected (0.00 sec)

mysql> SET @nexttime = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     TIME(FROM_UNIXTIME(StartTime)) "start",
    ->     TIME(FROM_UNIXTIME(EndTime)) "end",
    ->     FLOOR((EndTime - StartTime)/60) diff_min
    -> FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
    -> FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
    -> FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
    -> UNION SELECT
    ->     TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    ->     FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    ->     UNIX_TIMESTAMP(@LastDateTime))/60)
    -> ;
+----------+----------+----------+
| start    | end      | diff_min |
+----------+----------+----------+
| 08:15:00 | 11:35:00 |      200 |
| 11:35:00 | 14:35:00 |      180 |
| 14:35:00 | 14:40:00 |        5 |
| 14:40:00 | 15:20:00 |       40 |
| 15:20:00 | 17:35:00 |      135 |
| 17:35:00 | 21:35:00 |      240 |
| 21:35:00 | 00:00:00 |      145 |
+----------+----------+----------+
7 rows in set (0.00 sec)

mysql>

Just remember to set the @GivenDate to whatever date you need

BTW Your last line is based on midnight the next day. Therefore, the diff_min is 145 not 25. If you have a knock-off time of 10:00 PM at th eairport, then change

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)

to this

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) - 120

Subtracting 120 backs 2 hours away from midnight of the next day makes the last line's end 10:00 PM

Give it a Try !!!

UPDATE 2014-04-16 09:44 EDT

I cannot really test this, but you can try this:

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SELECT id INTO @LastID FROM airport_taxi
WHERE date=@GivenDate ORDER BY time DESC LIMIT 1;
SET @nexttime = 0;
SELECT
    id,
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT id,(@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT id,FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT @LastID,
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)
;

I hope this helps !!!


TPC-C contains a fixed number of transactions, not queries. They are described in Clause 2 of http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf. Sample implementations including SQL are in Appendix A.


The problem is twofold:

First Firebird 3 uses tighter security and has the old authentication mechanism disabled by default. So clients depending on older connection libraries will not work out of the box.

To allow the old authentication mechanism to work, the following setting needs to be set in firebird.conf:

AuthServer = Legacy_Auth, Srp, Win_Sspi

(be sure to remove the # prefix)

Second, the pure-java wire protocol implementation of Jaybird (the Firebird JDBC driver) uses an outdated authentication mechanism that is not supported by this Legacy_Auth option. Using Jaybird to connect to Firebird 3 at this time will only work if you use the native option (with the jdbc:firebirdsql:native: URL-prefix and jaybird2x.dll and fbclient.dll).

Addendum 1

Firebird 3 (since Alpha 1) has now also implemented the old authentication method used by Jaybird, so Jaybird will now also work without using the native library (as long as the legacy authentication is enabled, and the user has been setup with the legacy usermanager).

See also Jaybird and Firebird 3

Addendum 2

Jaybird 3 and higher support the more secure Srp authentication model out of the box, and Jaybird 3.0.4 introduced support for the wire protocol encryption. This means that it can connect to Firebird 3 without additional configuration.


The concepts of quorum, and owners are separate topics. Just because a member of the WSFC doesn't get a vote in quorum does not mean it can't own a resource. Additionally, SQL Server doesn't really play a role at all--the same concepts apply regardless of what type of clustered resource you're dealing with.

Quorum:
The quorum is the number of votes necessary to transact business on your WSFC. Depending on your WSFC configuration, voters can be nodes (servers), a drive, or a file share. You need more than 50% of your votes in order for the WSFC to be online. If you lose 50% or more of your voters, then the WSFC and all clustered services (including your FCI) will go offline and not come back until you have (or force) quorum.

In your configuration, you have a File Share Witness (hopefully in an impartial location reachable by both primary & DR sites), and you've also changed the NodeWeight to 0 for your DR servers. Rather than thinking of NodeWeight as "The DR servers don't get to vote," you should think of it as "The DR servers get to vote, but their vote doesn't count." They are still there, they're still part of the WSFC, it's just that the WSFC doesn't listen.

Even though the FSW isn't hosted on the cluster (or, perhaps, more accurately, because the FSW isn't hosted on the cluster!), it still has to be "present" to vote. Which brings us to the concept of ownership...

Cluster Owner/Host Server:
Your WSFC has a network name and an IP address. That name & IP has to be tied to a machine that is part of your cluster. More specifically, it can be tied to any one machine in your cluster. This is part of your WSFC.

In your scenario, your DR servers have no vote for quorum, but they still must be possible owners of the WSFC Host Server. If you manually fail over to your DR site (which will involve forcing quorum because you have zero voters in DR), then one of your DR servers must host the cluster name & IP. If it doesn't, then your cluster can't come online.

Your FSW is "owned" by the same server that owns/hosts the Cluster itself. Therefore it must have all servers, including DR, as possible owners. When you do force quorum and come online in your DR site, the WSFC is going to want to continue talking to the FSW.

Your original question:

  ...what would be the effect if I limited the possible owners to just
  those nodes at the primary site?

I suspect that you would have problems when you tried to force quorum and bring your WSFC online in the DR site--though, that's just a guess.

From a SQL Server perspective, you just care that you have quorum and the WSFC is reliably up. If you're having issues in that area, I'd look at the specific issues to your reliability. In reality, you probably don't really care which server is hosts your WSFC (and FSW).


DBCC CHECKDB behavior with MAXDOP settings

If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my available say 98 schedulers would be used? or still just 8? or just few?

Per the documentation, in this case "If MAXDOP is set to zero then SQL Server chooses the max degree of parallelism to use" just like it does with any other query where you override the default MAXDOP settings.  SQL Server can use up to the maximum of 64 processors when MAXDOP is effectively set to 0.

if i run DBCC CHECKDB with no MAXDOP say a default with server level
MAXDOP 8 , should i see then all 8 schedulers used? or it can be less
than 8

Then SQL Server chooses a max degree of parallelism up to 8 just like it does with a normal query with a MAXDOP override specified.
You should check to make sure the servers are the same, have the same instance level CPU settings, the same MAXDOP (check database level MAXDOP as well), and ensure that the database being checked is the same. All of these can affect the performance and parallelism chosen.


PostgreSQL PREPARE query with IN () parameters

Use an array to represent the series of values:

pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])");

$strpar = "{3,4,6,8,10}";
pg_execute($con, "prep", array($strpars));

The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.


Create a SQL Server Login and User with limited permissions for CRUD application Access

If you want a user to be able to read any table and view in your database then you would run the following from SQL Server 2012 onwards

ALTER ROLE db_datareader ADD MEMBER MyUser;

For previous versions you would run

sp_addrolemember 'db_datareader','MyUser';

db_datareader is a built in role that grants read access to everything.
db_datawriter is its equivalent for writes.

If you want to grant limited CRUD access then you need to create a new role and use the GRANT statement to assign permissions to that role.  After you have done that you can use the statements above to make your user a member of your new role as well.

CREATE ROLE MyLimitedCRUDRole;

For the stored procedure part of your question I would set up an explicit role for stored proc access.  I would keep this separate to MyLimitedCRUDRole as the visibility of what MyLimitedCRUDRole is of increasingly high importance in a GDPR world.

I would also advise having roles that have clear and single purpose for clarity.

CREATE ROLE db_AllProcExecutor;
GRANT EXECUTE ON MyStoredProc to db_AllProcExecutor;

It is possible to generate the GRANT statements dynamically but on any database with a security sensitivity I would be very careful doing so.


Generate metadata file from PostgreSQL server

If you want something quick and dirty, I would just use psql to dump out all the tables.

for t in $(psql -Ac '\dt' | awk -F'|' '$3 == "table" {print $2}');
do
    psql -c "\\d $t"
done > metadata.tables

Job done.


Counting the number of calls of a stored procedure

There is no such thing as a stored procedure trigger.

If you want an absolutely accurate count, then yes, change your CLR procedure (or whatever wrapper(s) it is called from) to write an entry to a log somewhere, every time.

If "close enough" is "good enough" then you can periodically poll the DMV sys.dm_exec_procedure_stats which will tell you the execution_count at any point in time. This count is typically accumulated since the last service restart (I'm not sure if certain other activities, such as specific RECONFIGURE actions, reset this DMV as it does for some others). So, you would have to poll it periodically, and factor in any service restarts that have happened in the meantime.

Here is a simply query demonstrating how to poll execution counts at any given point in time:

USE your_database;
GO

SELECT
  s.name, p.name, p.type_desc,
  ps.execution_count,
  ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
INNER JOIN sys.procedures AS p
ON ps.[object_id] = p.[object_id]
INNER JOIN sys.schemas AS s
ON p.[schema_id] = s.[schema_id]
WHERE ps.database_id = DB_ID();

Here is another bit of code that can clear out this DMV without a restart:

USE [master];
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

There's a complete (at least AFAIK) list here:

http://mattsql.wordpress.com/2012/06/25/dont-believe-everything-you-read-reconfigure-flushes-the-plan-cache/

How you consolidate what happens when there has been a service restart (or any of these other DMV-clearing activities) is probably a completely different question.


How to time SPARQL queries on an Oracle12c database?

If you have access to the server, use SQLPlus running on the server to invoke your query; that will eliminate the network delay.  To get somewhat precise timings, invoke these SQL*Plus commands first:

set timing on
set autotrace on statistics

Invoke the SQL a few times; the first time will likely cache the data and subsequent runs will reflect that.  Then do the same queries via your client.

If you had DBA access you could turn on tracing for a session and see exactly how much time is used for each part of the query.


Looking for suggestions to Migrate Amazon RDS Large Instance to non-amazon host - Live

Dump RDS and import it into mysql that holds all data in RAM disk:

Buy strongest possible EC2 instance that might hold your database in memory twice (you might prepare everything on a small instance and then ramp it up)
Create RAM disk, install and configure mysql 5.6 to hold both data and redolog on the RAM disk
Stop your db-using services, dump the RDS directly into the RAM-backed mysql
stop mysql, rsync the DB to a final place, kill the machine

You should do a dry run to estimate expected downtime.


Composite or Single-Field Clustering Key

This is bollocks:

  ... ClaimId alone is a better clustered key because it is narrower

because of this

  ClaimId alone is NOT uniquej

A non-unique clustered index will add a 4 byte uniquifier to remove ambiguity of ClaimId because it is the clustered index. Why? One reason is all NC indexes refer to it: so how to know ClaimId is which?

It was demonstrated (some time ago, maybe not valid now and can't find it) that non-unique clustered indexes break when you exhaust 2^32 values of the 4 byte uniquifier

Edit :

Question states ClaimId is not unique so assumed that uniqifier exists. No need to comment that it may not exist in the context of the question


Logging is in reference to Write-Ahead-Logs.
Essentially, everything gets written ahead of time to a log. That log can be played back at any point in time if there is a problem with database constancy (crash-safety). This means data is written twice, once to the log with representation in memory, and a later point in time flushed to the table's heap.
The log can also be shipped to a slave and can play a role in point-in-time (PITR) replication.
Check out the documents on the PostgreSQL implementation of Write-Ahead logging for more information.

If the system crashes (not shut down cleanly), the contents of unlogged tables will be deleted as stated in the manual:

However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown


You were so close. Only a missing parenthesis at the very end of the dynamic sql string, the finishing parenthesis for the PIVOT.

If you need to be able to handle multiple rows in dbsize with same database_name, you have to add a COUNT column and GROUP BY in your basedata query and change from COUNT(id) to SUM(count_column) in your PIVOT.

DECLARE @ColumnNames nvarchar(MAX) = ''
DECLARE @SQL nvarchar(MAX) = ''

SELECT @ColumnNames += QUOTENAME( database_name) + ',' from (select distinct database_name from dbo.dbsize) t
SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1)
SET @SQL = 'SELECT * FROM ( SELECT database_name, COUNT(*) AS cnt FROM dbsize GROUP BY database_name) AS basedata
            PIVOT ( SUM(cnt) FOR database_name IN (' +@ColumnNames + ')) AS PivotTable'
EXECUTE sp_executesql @SQL

SQL Fiddle


If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:

USE tempdb;

DROP TABLE IF EXISTS #LOB_FOR_ME;

CREATE TABLE #LOB_FOR_ME (
ID BIGINT,
MAX_VERNON_WAS_HERE VARCHAR(MAX)
);

CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);

Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:

USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 1, REPLICATE('Z', 8000)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');

There are no LOB pages in the index:

╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2540 ║         2540 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2540 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

But if I add rows with values that take 8001 bytes:

USE tempdb;

INSERT INTO #LOB_FOR_ME
SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
FROM master..spt_values;

SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');

Now I have 1 LOB page in the index for every row that I just inserted:

╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
║ index_level ║  index_type_desc   ║ alloc_unit_type_desc ║ page_count ║ record_count ║
╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
║           0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║       2556 ║         5080 ║
║           1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║         18 ║         2556 ║
║           2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA          ║          1 ║           18 ║
║           0 ║ NONCLUSTERED INDEX ║ LOB_DATA             ║       2540 ║         2540 ║
╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝

You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:

SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 1;

Results upon executing:

  Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
  0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If I instead query the rows with 8001 bytes:

SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
FROM #LOB_FOR_ME
WHERE ID = 2;

Now I see lob reads:

  Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
  lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.


Adding Data to Multiple Tables Using Single Query

This is not possible.  The INSERT statement takes a single destination object as per the syntax (BOL reference):

[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ]
        { <object> | rowset_function_limited
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ]
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ]
        | derived_table
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES
        }
    }
}
[;]

Take note of the <object> placeholder on the 6th line of the syntax snippet.

Is there a particular reason why you want to insert into two tables in one statement?  Perhaps you are looking for both inserts to either succeed or fail as one?  In that case, you would be looking at explicit transactions:

begin tran;
    insert into dbo.YourTable1 ...
    insert into dbo.YourTable2 ...
commit tran;

Besides code brevity and atomicity, I wouldn't see any other reason that you'd want to combine multiple destination tables into a single INSERT statement.


Query 1

EXPLAIN is not simple to follow.  First, let me explain how to optimize the two queries.

SELECT s.Important, c.Interesting
FROM table1 s, table2 c
WHERE s.IdForTableC = c.id

That's the old way to write a JOIN; please learn the new way:

SELECT s.Important, c.Interesting
    FROM table1 s
    JOIN table2 c  ON s.IdForTableC = c.id

The ON explains how the two table relate to each other.

Now there is no filtering via a WHERE clause, so all that can be done is

Go through all the rows of one of the tables.  (No index is useful)
Foreach row, reach into the other table.

If s is used for step 1, then an index on id is needed in c for step 2.
If c is used for step 1, then an index on IdForTableC is needed in s for step 2.

The convention is to name the PRIMARY KEY "id".  A table must have a PRIMARY KEY.  I'll guess that c has PRIMARY KEY(id).  This makes starting with s a slam-dunk.  But, instead, the Optimizer decided to start with c -- as seen by it being row 1 in the EXPLAIN.

"ALL" means that the entire table is scanned.  Some of the NULLs go along with that.  "PRIMARY" does not mean much in this context.  c has about 467 rows.

To get into s, it used table2.c.id.  Something is wrong there.  That syntax says "database table2, table c, column id".  Is your database called "table2".  Ugh.

The index named IdInTableC was used.  Presumably, you had

INDEX(IdInTableC)

so that name is the same as the column.  5 probably means it is a NULLable INT.  Use NOT NULL unless you have a purpose for NULL.  The JOIN will find an estimated 2 rows in s.

I guessed wrong as to which order c and s would be used.  But the symmetry of the query implies that there might not be a lot of difference in which to start with.

Query 2

Again, let's use the new JOIN syntax:

SELECT q.Important, c.Interesting
    FROM table1 q
    JOIN table2 p  ON q.IdInTableP = p.id
    JOIN table3 c  ON p.IdInTableC = c.id
    WHERE  c.Measure = 1

It is usually best to "filter" first.  The only filter is c.Measure = 1, so c will probably be the first table, especially if you have

INDEX(Measure)

Then, the chain must be:  c -> p -> q.  So these are needed:

p:  INDEX(IdInTableC)
q:  INDEX(IdInTableP)

With those indexes, all three tables should use INDEX, perhaps with ref or eq_ref.

More Tips

In the Extras column:

"Using index" means that the index is "covering".  That is, all the columns needed are found in the index.  This is a minor performance boost.
"Using index condition" is different and mostly out of your control; it also implies a perf boost.
"Using join buffer" -- the second table is fully loaded into memory; this may be a big perf boost.
"Using temporary, Using filesort" -- People wrongly fear these.  There is some performance loss, but usually one or both are required.  After all, ORDER BY implies a sort.  The absence of "filesort" implies an optimization that is sometimes available.

For some basic index-creation tips, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql

I don't use EXPLAIN that much; I try to go straight from the SELECT to the optimal index.


How do I Loop through a table and update a field in SQL

Using a common table expression with row_number() to partition by AccountId and order by [RowId]:

;with cte as (
  select *
    ,  NewOrderId = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)
update cte
  set OrderId = NewOrderId;

Without using the common table expression:

update r
  set OrderId = NewOrderId
  from (
    select *
      ,  NewOrderId = row_number() over (
            partition by AccountId
            order by [RowId]
      )
    from Renewals
      ) as r

test setup: http://rextester.com/FSUD49402

create table Renewals (
    [RowId]   int     not null
  , AccountId char(1) not null
  , OrderId   int         null
);
insert into Renewals (RowId, AccountId) values
  (1,'A'), (2,'A'), (4,'A')
, (5,'B'), (6,'B'), (7,'C');

with cte as (
  select
       [RowId]
    ,  AccountId
    ,  OrderId
    ,  NewOrderId = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)

update cte
  set OrderId = NewOrderId;

select * from Renewals;

results in:

+-------+-----------+---------+
| RowId | AccountId | OrderId |
+-------+-----------+---------+
|     1 | A         |       1 |
|     2 | A         |       2 |
|     4 | A         |       3 |
|     5 | B         |       1 |
|     6 | B         |       2 |
|     7 | C         |       1 |
+-------+-----------+---------+

To answer the question in the comment of this answer:

  I am using this data to update the previous row. [...] I am trying to show that Row 1 above was renewed by row 2 was replaced by row 4 and row4 has not been renewed as yet

Can be done with:

select
    r.*
  , RenewedBy=p.RowId
from Renewals as r
  left join Renewals as p
    on p.AccountId = r.AccountId
   and p.OrderId = r.OrderId+1

returns:

+-------+-----------+---------+-----------+
| RowId | AccountId | OrderId | RenewedBy |
+-------+-----------+---------+-----------+
|     1 | A         |       1 | 2         |
|     2 | A         |       2 | 4         |
|     4 | A         |       3 | NULL      |
|     5 | B         |       1 | 6         |
|     6 | B         |       2 | NULL      |
|     7 | C         |       1 | NULL      |
+-------+-----------+---------+-----------+

If this is the only reason for the previous update, we don't have to update the table at all. This will get the same results as above:

;with cte as (
  select *
    ,  rn = row_number() over (
          partition by AccountId
          order by [RowId]
    )
  from Renewals
)
select
    r.RowId
  , r.AccountId
  , OrderId = r.rn
  , RenewedBy=p.RowId
from cte as r
  left join cte as p
    on p.AccountId = r.AccountId
   and p.rn = r.rn+1


PostgreSQL pgpool-II replication mode doesn't work?

Postgres instances are normally replicated using Streaming replication which uses the WAL(Write Ahead Logs), which are transferred to secondary database to be replicated. So all incoming queries from application will go to the primary database and gets executed. These executions generate WALs which are then transferred to the secondary database and applied so that the secondary comes in sync with primary database.

As per pgpool documentation, the replication it uses is a statement based replication where the application connects to pgpool server instead of database(primary/secondary), and the incoming queries are then forwarded to database instances to be replicated.

There are a lot of disadvantages of using pgpool replication setup. Functions like random(), CURRENT_TIMESTAMP, and sequences can have different values on different databases, also if the pgpool crashes, no query will execute, since the application is connected to pgpool, not the database.


How to recover the suspect mode database in SQL Server 2008?

According to Paul Randal, this seems to be a bug in checkdb. Check this article from Paul which discusses about this issue.

The workaround is to run CHECKDB on the database with TABLOCK


MySQL Query was killed, but caused mysql database to not accept new reads or writes

For future refference for myself and anyone else with this issue, run "pkill mysql", move "ibtmp1" (like 90G) inside of /var/lib/mysql to another location, then start mysql again.


Microsoft Help Viewer in ssms 2014 doesn't open local documentation

Essentially 2014 is configured by Microsoft to use Help Viewer 1.0 so you have to redirect it yourself to use 2.x.

There is a post on MSDN social on how to do this here and the handy little tool you need is on CodePlex here.

In regards to why it is not finding your documentation when it loads Help 1.0, I am going to assume it is because you have installed the documentation in the Help 2.0 directory and not the 1.0 directory (big assumption as you didn't say otherwise).

Also, be aware that the 2.x documentation format differs from the 1.x format so even if you did put it in the correct directory it may not be able to read it anyway.


Assignment of dbms_metadata.get_ddl to pl/sql variable fails

CLOB is not the problem. You may have a special character somewhere in the table definition.

Above code works in general. But below is an exception, to reproduce your error:

begin
  execute immediate 'create table t1 (c1 varchar2(100) default ''' || chr(0) || ''')';
end;
/

desc t1

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(100)

declare
    v_tmp  CLOB;
begin
   v_tmp := dbms_metadata.get_ddl('TABLE', 'T1');
end;
/

Which gives:

ORA-06502: PL/SQL: numeric or value error
LPX-00217: invalid character 0 (U+0000)
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 4

The whole error message may reveal more, and I would check the specific table in the dictionary views (ALL_TABLES, ALL_TAB_COLS), and look for such "anomalies".


How can I allow part of a key to be null and enforce uniqueness of records with nulls?

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets.

User-Actions taken during a given Period ( defined by the composite key of Year-Week )
User-Actions taken during an unknown Period

My initial impression is that the unknown period must needs be a different dataset. Surely if you don't know when an action has taken place you also do not yet know enough to move it from its staging environment to the table where User-Action-Period is the unique identifier. I don't know MS Access well enough to comment on the specifics of null keying in that environment, but I strongly recommend defining your data model for yourself in such a way that you know if you need to include the "equivalent-to-null" keyed records on the same table.
From your description, it seems that the problem occurs when attempting to insert multiple instances of a User-Action where the Period occurred is not known. Once again, if you do not know enough to uniquely identify the committed time of the User-Action, it is appropriate to stage the metrics you are recording about the same User-Action elsewhere until you can appropriately identify the relationship this User-Action has to the other similar User-Actions that occurred at another key-able time Period.
I see two paths forward:
If only one User-Action with "unknown" time-key is permissible
Example: The action is "pending" or "to-be-completed". The user will always be submitting data for a future / not-completed period.
Solution: Pick your favorite method of keying the time period as "equivalent-to-null" and make the key columns not-nullable. You may continue to store these datasets in the same table
Gotcha: If the user submits an action with a committed period while an unknown period exists for the same User-Action, you may run into a situation where data is committed where the period is known but other data already committed for an unknown period ( which happens to be the same one ) is left in the "unknown period" state and lost for that User-Action-Time record and the user doesn't immediately know why.
Multiple User-Actions with "unknown" time-keys are permissible
Example: Users are able to uniquely identify the same action type with a different identifier for an unknown Period.
Solution: You must define another way to key the data ( where the time the action occurs is a metric of the other multi-keyed record ). Allow the users to submit data to this separately stored dataset and merge it into your User-Action-Time period dataset using appropriate logic. It's more work, but there's no way around it if the datasets are bona-fide separate.


As commented, arguments from both sides are valid. Let's call them "star" (the flattened schema of your DBA) and "EAV" (entity-attribute-value). The latter can serve as a hint. Details in this related answer:
Is there a name for this database structure?

Well, if your 500 metrics are of well known type and you don't invent new ones / drop old ones all the time, it's not the worst case EAV scenario, but similar.

There is no "standard" way to do it. The more flexible approach would be the "EAV" schema. Then you can easily add and delete metric-types: add a row to the referenced metric_type table, or delete one (cascading to metrics table). That would need a schema-change and be more expensive in the "star" model.

You can do quite a bit for either model with smart covering / partial / multicolumn indexes.

Some decision guidance

Aggregates (min/max/avg/...) on a single metric-type? -> "star"
Aggregates considering all or many different metrics? -> "EAV"

Do these attributes describe a common entity? -> "star"
Or is it just a bunch of numbers that may be grouped / split up any other way? -> "EAV"

Your data is written once and then never changed? -> "star"
Or do you run UPDATES on selected metrics? -> "EAV"

Is your set of 500 metrics complete? The set hardly ever or never changes? -> "star"
New metrics are added, existing metrics are dropped all the time? -> "EAV"

Storage size

Concerning your comment:

  Storage is less important now for optimization, we are focusing on query times.

Storage size is a major factor for query times. The number of data pages that have to be read to satisfy a query is probably the most important single factor for performance.

Let's start with your casual remark: Data type is int or double.

int occupies 4 bytes.
double occupies 8 bytes.

Assuming all columns are NOT NULL, 500 integer columns, plus 1 timestamp plus row overhead (no padding) would occupy 2036 bytes in the "star" schema. No compression possible. Here is how you calculate that:
Configuring PostgreSQL for read performance

If you mix int with double, be sure not to waste space for padding. For instance, group integer and double metrics.

In the "EAV" model, you'd need at least 44 or 52 bytes per row. 22000 or 26000 bytes for one timestamp. 11 - 13 times as much. That matters. For one hour's worth of data, you need to fetch 2000 data pages or more (default page size 8k) vs. around 180 pages for the "star" schema.

Here are some tools to measure size:
Measure the size of a PostgreSQL table row

I think storage size can be the key to performance here. If you are focusing on query times, and I had to make a wild guess, the "star" schema is probably the better choice.

But as I said, it depends on a lot of details.

Either way, you may be interested in cross tabulation ("pivot table"). The tablefunc module provides the crosstab() function:
Have aggregate function group results in one row


how to make levenshtein distance faster for mysql

Functions are slow and it is a complex function. Try optimizing the function.

Or select down to like .NET and do it there.

If you get a lot of exact matches you can make that separate.

SELECT o as distance
FROM stuff ORDER BY
where substring(keywords, 1,5) = 'texas')

union

SELECT levenshtein(substring(keywords, 1,5),'texas')
from FROM stuff
where substring(keywords, 1,5) <> 'texas')
FROM stuff ORDER BY distance ASC


You need to add indexes:

image (person_id, image_id)
has (image_id, tag_id) - you can remove id column from this table and make this index a primary key
It is not the best idea to use "text" data type for person.name column. It would be better to use varchar or nvarchar. For example, varchar(100) should be enough. Then create index on this column too: person (name).


mongo DB The disk space completelly filled for replicate instance

Immediate action to regain redundancy

Make absolutely positively sure your data fits on the secondary. DO NOT PROCEED IF IT DOESN'T OR YOU ARE IN DOUBT. YOU HAVE BEEN WARNED!
Shut down the secondary
Delete the content of the secondaries dbpath (for example /var/lib/MongoDB)
Restart the secondary
The secondary will now resync from the primary, creating unfragmented datafiles which will make the smallest space possible.

Change deployment (!)

If one of the members has less space available than the others, there will be a point in which it can't replicate any more and drop out of the replica set, reducing failover capabilities. This usually happens 3am on a Saturday morning, when you just got home from a party ;)

You have to make absolutely sure that your replica set members have the same amount of disk space available. If you absolutely can't do this, you have to artificially limit the space available to each member, either by creating dedicated partitions of the exact same size (preferred way) or by using MongoDB's quotaFiles option.


Select data from a table and update a second table using most recent value

If I understand correctly, you are trying to update Registries table with the newest row of Stampings table, replacing single quotes ' (if exists) by a pair of single quotes ''.

As I pointed out in my comments you can get it by using a single SQL command, that you can execute on SSMS or by using a C# program.

I've set up a fiddle with your table schema (or similar because you have not provided columns size):

CREATE TABLE Stampings
(
    ID INT,
    Date DATE,
    Hour VARCHAR(5),
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);

CREATE TABLE Registries
(
    ID INT,
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);

INSERT INTO Registries VALUES
(1, 'AAA', 'Name 1', 'Surname 1', 'A'),
(2, 'BBB', 'Name 2', 'Surname 2', 'B'),
(3, 'CCC', 'Name 3', 'Surname 3', 'C'),
(4, 'DDD', 'Name 4', 'Surname 4', 'D');

INSERT INTO Stampings VALUES
(1, '20180101', '02:05', 'AAA', 'AAA Name', 'AA1 ''Surname', 'E'),
(2, '20180101', '05:05', 'AAA', 'AAA Name', 'AA2 ''Surname', 'E'),
(3, '20180101', '12:05', 'AAA', 'AAA Name', 'AA3 ''Surname', 'E'),
(4, '20180102', '12:05', 'BBB', 'BBB Name', 'BB4 Surname', 'F'),
(5, '20180102', '15:05', 'BBB', 'BBB Name', 'BB5 Surname', 'F'),
(6, '20180103', '11:05', 'CCC', 'CCC Name', 'CC6 ''Surname', 'G'),
(7, '20180103', '12:05', 'CCC', 'CCC Name', 'CC7 ''Surname', 'G'),
(8, '20180103', '13:05', 'CCC', 'CCC Name', 'CC8 ''Surname', 'G');

Now I'm using a single command to update your data in this way:

UPDATE r
SET    Surname = REPLACE(s.Surname, '''', ''''''),
       Name = s.Name,
       EmployeeFreshman = s.EmployeeFreshman
FROM   Registries r
CROSS APPLY (SELECT TOP 1
                    CardCode,
                    Surname,
                    Name,
                    EmployeeFreshman, Date, Hour
             FROM   Stampings
             WHERE  CardCode = r.CardCode
             AND    Surname LIKE '%''%'
             ORDER BY Date DESC, Hour DESC) s;

CROSS APPLY join returns the newest Stampings inserted row of each Registry,  and REPLACE(s.Surname, '''', '''''') do the trick with the Surname

This is the final result:

SELECT * FROM Registries;
GO

ID | CardCode | Name     | Surname       | EmployeeFreshman
-: | :------- | :------- | :------------ | :---------------
 1 | AAA      | AAA Name | AA3 ''Surname | E
 2 | BBB      | Name 2   | Surname 2     | B
 3 | CCC      | CCC Name | CC8 ''Surname | G
 4 | DDD      | Name 4   | Surname 4     | D

db<>fiddle here


In SQL Server, what's the best way to split multiple delimited strings into rows without a function?

Transform the delimited string to a JSON array, and use OPENJSON, which exposes the lexical ordering for arrays.  eg
This
select *
from openjson('["w","x","y","z"]')

outputs
key       value
--------- -------
0         w
1         x
2         y
3         z

So
DROP TABLE IF EXISTS #TestData;

CREATE TABLE #TestData(GroupId VARCHAR(10),
                       Val1 VARCHAR(100),
                       Val2 VARCHAR(100)
                       );
INSERT INTO #TestData
VALUES ('G1', '1,2,3', 'a,b,c'),
       ('G2', '4,5,6', 'd,e,f'),
       ('G3', '1,2,3,4', 'w,x,y,z');

with q as
(
  SELECT GroupId, concat('["',replace(Val1,',','","'),'"]') Val1,concat('["',replace(Val2,',','","'),'"]') Val2
  FROM #TestData
)
select GroupId, v1.value Val1, v2.value Val2
from q
cross apply openjson(Val1) v1
cross apply openjson(Val2) v2
where v1.[key] = v2.[key]

outputs
GroupId    Val1     Val2
---------- -------- ---------------
G1         1        a
G1         2        b
G1         3        c
G2         4        d
G2         5        e
G2         6        f
G3         1        w
G3         2        x
G3         3        y
G3         4        z


Track write requests on Read-only database

A readonly database is "READ_ONLY". No writes are allowed - the application will get an error stating that the database is readonly.

You can run a server side trace (since you are stuck with sql server 2005) and look for user error messages and filter your database by ID.


High MySQL "InnoDB Writes Per Second" - Can't see the queries?

What you need is a better visual representation of the InnoDB Storage Engine

Think about the kind of things that trigger writes that can be monitored

Number of Write Operations

Innodb_dblwr_writes to the Double Write Buffer
Redo Logs

Innodb_os_log_writes and Innodb_os_log_pending_writes : Logical
Innodb_os_log_fsyncs and Innodb_os_log_pending_fsyncs : Physical

InnoDB_data_fsyncs and InnoDB_data_pending_fsyncs
Innodb_data_writes throughout the InnoDB Architecture

Bytes Written with InnoDB

Innodb_pages_written : NUmber of pages (data and index) written to InnoDB tables. Multiple this by 16384 (16KB) to get the number of bytes written to InnoDB tables.
Innodb_dblwr_pages_written Number of Double Write Pages Written. Multiple this by 16384 (16KB) to get the number of bytes written to the Double Write Buffer.
Innodb_os_log_written Bytes Written to the Redo Logs
Innodb_data_written Bytes written throughout InnoDB Architecture

These are among the things that are being churned inside and outside of InnoDB. Yet, this is not everything. Check the MySQL Documentation on the Status Variables.

My guess is that MySQL Workbench is just monitoring Innodb_data_writes.

If the data writes are high, given this

1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s

which is about 15 queries per second, you may need to spend some time tuning InnoDB. Since InnoDB does fuzzy checkpointing, InnoDB will reveal a bottleneck in writes if you see constant writes that never abate. That could be due to the OS or VM. It could also be due to an undersized InnoDB Buffer Pool (I see it's 100% full). Lots of reads will bring in data and index pages into the Buffer Pool. New or updated data and index pages from the INSERTs and UPDATEs need to squeeze into the Buffer Pool as well. Perhaps a bigger Buffer Pool is in order.

From another angle, look at the number of rows accessed

     36474186 Rows from INSERTs
    163927609 Rows from UPDATEs
     11892445 Rows from DELETEs
    212294240 Rows Written
1747097889216 from SELECTs

When you divide 1747097889216 by 212294240, that means for every row you update, there are 8229.6 rows that are being read. This goes along with the 2245.46 (33165.95/14.97) SELECTs for every INSERT, UPDATE, or DELETE. I can see writes squeezing into the Buffer Pool. This makes me feel that the Buffer Pool, the Log Buffer, and the Redo Logs should all be increased.


mysql ERROR 1205: 1205: Lock wait timeout exceeded; try restarting transaction

Some other connection was running something really long - probably longer than 50 seconds.

Possibly another connection is in autocommit=0 mode, but just sitting there, forgetting to do a COMMIT.

If you can, get the PROCESSLIST before it times out.  Also do SHOW ENGINE=InnoDB STATUS;

Provide SHOW CREATE TABLE credentials.  Look for other writes to that table, and check for index usage.


Yes, mirror the content of the view to a PGSQL. But never mind, I was able to do it with a local SQL express installation very easily.


Create/Alter stored procedure without compiling

What you are referring to is deferred name resolution and is further explained here Deferred Name Resolution and Compilation.

  Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.

Basically, no you are not able to alter the parsers rules for what objects must exist at parse and compile time. I've previously had this issue when deploying SPs which reference linked servers and those linked servers did not have a valid connection. To work around it I updated the linked sever logic to be dynamic SQL.


PostgreSQL 9.6 error: invalid resource manager ID

I have by now concluded that this is a symptom of PostgreSQL encountering malformed WALs. These error messages also seem related:

LOG: invalid record length ...
LOG: record with incorrect prev-link ...

The root cause was apparently that a call to pg_basebackup -X stream did not terminate. (This was apparently an issue titled "pg_basebackup -x stream from the standby gets stuck if there is no traffic in the database" that was resolved in PostgreSQL 9.2, but may have cropped up again.) When I had to interrupt the call with CTRL-C, this may have left malformed WALs.

I am now using pg_basebackup -c fast -X fetch instead, and this works fine. Given that and for my current purposes, it is no longer necessary to understand details around (PostgreSQL) resource managers.


PostgreSQL can be built to either use the operating system's time zone database or its own.  Check with the command pg_config --configure whether the option --with-system-tzdata was used.  If not, it is using its own time zone data, and that hasn't been updated yet.  If so, to fix, either wait for the next releases, or install the packages the way you have installed it on the other hosts.


Please visit this link.
There are exact explanations about the memory limits especially in confunction with SQL Server 2016 SP1.

  In simpler terms, buffer pool memory in SQL Server is a memory cache responsible for caching data and index pages for the entire instance. The rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory. The memory consumed by caches outside buffer pool is not restricted by above memory limits and can grow up to limits defined by "max server memory". This is not specific to SQL Server 2016 SP1 and is also applicable to earlier releases of SQL Server as well.

And

  For example: A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by "max server memory" or total memory on the server if max server memory is uncapped.


The configuration described in the original topic is a bit strange. There are exists two resource groups SYSTEM (CNO, CNO_VIP1, CNO_VIP2, FSW) and AG (LISTENER, LISTENER_VIP1, LISTENER_VIP2, AG).

These two resource groups can be owned by different WSFC nodes. For example SYSTEM is owned by node from DC2 and AG is owned by node from DC1. In this case, if the link between DC1 and DC2 is broken:

Then DC1 will have only 1 vote (NODE1)
Then DC2 will have only 2 votes (NODE3 + FSW)

Accordingly to the quorum configuration DC2 part will alive (due to majority of votes) and DC1 will die (due to minority of votes). But as soon as synchronization between DC1 and DC2 is asynchronous there won't be automatic failover to DC2.

So you will end up with the situation when DC2 is alive but can not handle DB workload, because AG will wait manual intervention (allow_data_loss).

To avoid such situation, you need to assign votes like that

DC1:

NODE1: 1
NODE2: 1

ASYNC SYNCHRONIZATION

DC2

NODE3: 0
NODE4: 0

In that configuration AG will fail over to the NODE2 (DC1).

You'd better to explain the purpose of DC2. Is it disaster recovery DC or you need it for read scale or both?


Is there a configuration option that lets me limit the percent of a hard drive that Cassandra uses?

I dis not saw such a option by Cassandra configuration, but you can use OS quota to limit disk usage.

Or more sophisticated is to write own small script to stop Cassandra, when Cassandra disk usage is too high. It will not be very long.


How to give access to a user on only two specified procedures and specified tables on SQL Server?

Bu default, a user don't have any premissions. So, all you have to do is to grant the permissions you want:
GRANT EXEC ON proname TO usrname
GRANT SELECT ON tablename TO username

I don't understand what you mean by "tables to be fed".
If a proc operates on a table (SELECT, INSERT ...) then it is enough to have EXEC permissions on the proc, you don't need the (SELECT, INSERT...) privilege on the table in order to execute the proc. This assumes the same over of the proc as the table, called "ownership chaining".


Connect to SQL Server on Linux over the network

1st you need to identify your VM IP address, supposing it is 10.1.1.10

sqlcmd -S 10.1.1.10 -U SA -P "YourPassword"

Either check connectivity between your host and your VM using

ping 10.1.1.10

and/or

telnet 10.1.1.10 1433

Read detailed instructions on Microsoft's link below:

sqlcmd on Linux

Regarding to web connection, there's no guarantee to be able to connect to the VM via web, that imply many aspects.


The tutorial you followed was mine and was the accepted answer.
However, the other answer given by AaronBrown had a very strong caveat.

Instead of having a single point of failure, now you have three.
What if one of the servers goes down? Now replication is broken everywhere:
How do you replace it easily and align the binary log positions? You can't without doing some very complicated and error prone binary log analysis.
So, now you have to restore all three servers from a backup. Wait, was that backup taken at the same moment in time? Nope, because there's no way to make a transactional backup across multiple servers. So, you're left with broken replication and attempting to fix it with pt-table-checksum and pt-table-sync...or rebuilding the entire cluster from scratch.

In your particular case, I adamantly advise against using pt-table-sync. Why ? Running pt-table-sync will create a script that you can run on the Slave so that the Slave will end up looking like its Master. THAT WILL DELETE DATA FROM THE SLAVE THAT ORIGINATED FROM THAT SLAVE IN ITS ROLE AS A MASTER !!! This is why I upvoted AaronBrown's answer and I wrote a comment on AaronBrown's answer as to why I liked his answer stating

I have to agree with your answer because I have had to do just what you are saying with some clients. In some cases, if you do not remember to precede the output of pt-table-sync with "SET SQL_LOG_BIN=0;", unintended changes may cascade and you end up chasing your tail like a crazed dog. Error prone, indeed !!!

WHAT TO DO NEXT ...
To sync each node, you will have to perform the following:
From ServerA (Master) to ServerB (Slave)
STEP 01) On the Master, create dumpfile with binary log disabled in your session as first line
STEP 02) mysqldump from Master without overwriting (using --insert_ignore)
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DUMPFILE=/path/to/MasterDumpFile.sql
MYSQLDUMP_OPTIONS="--single-transaction --no-create-info --insert-ignore"
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN}`
echo "SET sql_log_bin = 0;" > ${DUMPFILE}
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} >> ${DUMPFILE}

STEP 03) Move the dumpfile to the Slave
STEP 04) Execute the DumpFile on the Slave
STEP 05) Repeat Steps 1-4 for ServerB (Master) and ServerC (Slave)
STEP 06) Repeat Steps 1-4 for ServerC (Master) and ServerD (Slave)
STEP 07) Repeat Steps 1-4 for ServerD (Master) and ServerA (Slave)
When done, all nodes should look identical.
Sorry I don't have an automatic solution, but this is the next best thing.


SQL server databases stuck in restoring state

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore.

The database is now waiting for the latest transaction log file.

You can either:

Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or
Restore the database again, but this time using ... WITH RECOVERY; ... or
Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY;

Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

See this for details:

http://technet.microsoft.com/en-us/library/ms177446.aspx
https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state


You can create:

Step#1 - stored procedure, which will run as many queries as You need - http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html
Step#2 - Native MySQL Events - which will call this stored procedure - https://dev.mysql.com/doc/refman/5.7/en/create-event.html

In case when You do not want use MySQL bud scheduler, You always can call this stored procedure from PHP, cron, any other SQL script and etc


Mysql query runs longer than long_query_time variable

Just looking at the query you have you are missing indexes for:

is_template
archived
market_segment
template_type

I am assuming the indexes you currently have are used for other queries correct?

I am thinking you probably need an index on the other tables name column.

ALTER TABLE `projects` ADD INDEX project_ix1( is_template , archived, market_segment, template_type );
ALTER TABLE `template_types` ADD INDEX templatetypes_ix1( alias );
ALTER TABLE `market_segments` ADD INDEX marketsegments_ix1( name );

You might want to break up the multi-column index, since this is only for the query you asked about, and I don't know what other queries you might have in the pipe.  Let me know the results.


Map Oracle roles to Active Directory groups?

You want to manage your users, roles and user to role mappings in AD?

I think Enterprise User Security is what you want, see http://docs.oracle.com/cd/E11882_01/network.112/e10744/concepts.htm#autoId10

Basically you'll need Oracle Enterprise Edition, and, after you've configured your database to use your directory (see http://docs.oracle.com/cd/E11882_01/network.112/e10744/getstrtd.htm#CBHDBFGC ), you can create your users and roles as
CREATE USER x IDENTIFIED GLOBALLY;
CREATE ROLE y IDENTIFIED GLOBALLY;

You can assign privileges to role y, but you should manage user to role mappings in AD.


SQL Select records where any word / value contained within one string is also contained within another string

I don't know the requirements of your project but with the info you provided on this post, this qualify for a data quality project from Data Quality Services (DQS):

Data Quality Project (DQS)


mysql cluster with innodb utilizing high memory

The main memory usage in an SQL node is for InnoDB's buffer pool.  What is the value of innodb_buffer_pool_size?  (Use SHOW VARIABLES LIKE 'innodb_buffer_pool_size';)  How much RAM do you have?  Normally, it should be set to about 70% of RAM.  The memory usage will grow until it hits the setting.  After that it will fluctuate slightly as other allocations come and go.


If a table with a surrogate key has a column known to have unique non null values (eg SSN), does it violate 3NF?

A relation R is in third normal form if every non-prime attribute of R
  is non-transitively dependent on each candidate key of R

E.F.Codd, 1971, Further Normalization of the Data Base Relational Model

It is implicit in the definition of a relation that a relation must have at least one key. Nothing about 3NF or any other Normal Form requires that a relation should have only one key.

Unfortunately books on database design and normalization have plentiful examples of relations with only a single key and rather fewer examples with more than one key. This strikes me as odd given that multiple keys appear to be very common practice these days. The dearth of practical examples in non-academic literature seems to be one cause of confusion about the role of keys in database design. Another cause of confusion is the popular mnemonic "nothing but the key". That phrase is usually attributed to Bill Kent but it isn't an accurate definition of 3NF.


In Oracle, if parallel_max_servers is set to be significantly less than the DOP can the explain plan be sub-optimal?

It would be a very strange thing if parallel_max_servers is set to a value below cpu_count * parallel_threads_per_cpu. I would call that a mistake by the DBA even. Let me bring to your attention a new feature of 11g (you should always mention your version, by the way), called Automatic DOP, that I have introduced in a posting here:
http://uhesse.wordpress.com/2009/11/24/automatic-dop-in-11gr2/


SQL Server Facets: "Linked Server" exist but "Registered Server" doesn't - why?

There is no such thing as a registered server in SQL Server itself.

Registered Servers can be added into SQL Server Management Studio to simplify managing multiple servers.

Policy Based Management, and facets, cannot be used against Registered Servers since they don't actually exist in the database engine.


If you're actually using Sql Server 2016, you can probably use the new STRING_SPLIT command to create a 'table' to join against.  Just put your comma delimited integers in the STRING_SPLIT command.  Here is an example.

declare @tb_Order table (col1 int, postalcode varchar(5))
insert into @tb_Order values(1,'11111')
insert into @tb_Order values(2,'22222')
insert into @tb_Order values(3,'33333')
insert into @tb_Order values(4,'44444')
insert into @tb_Order values(5,'55555')
---------------------------------------
SELECT a.*
FROM @tb_Order a
JOIN STRING_SPLIT('11111,22222,33333,44444,55555',',')
    ON convert(varchar(5),value) = a.postalcode;


SQL Server Mirroring vs 3rd party replication products such as DoubleTake

SQL Server's native database mirroring includes something really slick: automatic page repair.  If the principle detects a corrupt page on disk, it fetches a clean copy of the page from the mirror automatically without taking the database down.  You don't get that level of database repair with any third party product.  That database awareness is one of the reasons I prefer the native tools - well, that and I just don't trust third party filter drivers on database servers.  I've seen too many blue screens of death due to third party filter driver bugs.

Bad news: database mirroring is deprecated in SQL 2012.  This means it's still around for this version, but will be removed in the future.

Fantastic news: SQL Server 2012 introduced AlwaysOn Availability Groups to replace mirroring, and they're awesome.  You can actually query or back up from the mirror, and you get up to four mirrors.  Some can be synch and some can be asynch.  This gives you all kinds of flexibility over database mirroring, and it blows away the third party tools.


How to add table to database inventory in sqlplus oracle 12c

You either login as user Inventory before running the DDL, or you create the table by specifying the Schema like

create table Inventory.Items
( ... );


The -PublisherFailoverPartner option should help you out in your case. I am assuming you want to have replication running irrespective of database running on principal/mirror server.

Below links have your issue covered and has all the details on how to configure it.

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/replication-and-mirroring-setup/

https://www.pythian.com/blog/how-to-configure-transactional-replication-mirroring-failover/

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

Test it out in your test environment before implementing it on PROD.


Join two tables and return data and count in a single query

select l.email_list_id, l.email_list_name,
    count(d.email_uniq_id) as full_count,
    count(case when d.blacklist = 0 then d.email_uniq_id end) as white_count,
    count(case when d.blacklist = 1 then d.email_uniq_id end) as black_count
from tbl_email_list as l
left join [tbl_email-details] as d on d.email_list_id = l.email_list_id
group by l.email_list_id, l.email_list_name;

By counting things which can be null, we let zeroes appear in the result set, which is a very useful technique. Here I do it for both the blacklist results and the overall (based on the outer join).


Give this formulation a try:
INSERT INTO `hlh_record_status_submission`
SELECT rse.* FROM `hlh_record_status_events` AS rse
    JOIN ( SELECT MIN(id) AS min_id
              FROM `hlh_record_status_events`
              WHERE status IN (21,24,31,40)
              GROUP BY record_id
         ) AS x  ON min_Id = rse.id;

and replace INDEX(status) with
INDEX(status, record_id, id)

Also, you should really change all your tables to InnoDB.
The subquery (a "derived" table) will be faster because of the index starting with what is in its WHERE clause and because that index is "covering".  The JOINing to the outer part is (I assume) using the PRIMARY KEY(id) making that efficient.
Historically, the construct IN ( SELECT ... ) has been notoriously inefficient, so I try to avoid it.  JOIN and EXISTS ( SELECT ... ) are sometimes viable alternatives.
More discussion of building optimal indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql


RDS instances use Elastic Block Storage (EBS). EBS documentation explains what request merge means:

the underlying drive technology determines the maximum amount of data that a volume type counts as a single I/O. I/O size is capped at 256 KiB for SSD volumes and 1,024 KiB for HDD volumes because SSD volumes handle small or random I/O much more efficiently than HDD volumes.
When small I/O operations are physically contiguous, Amazon EBS attempts to merge them into a single I/O operation up to the maximum size. For example, for SSD volumes, a single 1,024 KiB I/O operation counts as 4 operations (1,024÷256=4), while 8 contiguous I/O operations at 32 KiB each count as 1 operation (8×32=256).

High number of merged I/O requests means your database is requesting a lot of sequential blocks, and EBS is able to translate that into fewer but larger, hence more efficient I/O operations. Low number of merged I/O requests probably means your database is mostly accessing random blocks.
To answer your

Came from where? Issued by whom?

-- by whatever is reading from and writing to EBS volume(s) on your RDS instance, presumably Postgres.


TSQL - SQL Server 2008 Maintenance Plan - anything else?

First have a look on this brilliant solution by Ola Hallengren to build your own maintenance plan. It has been well recognised and trusted by many SQL Server pros.

If you are just after "one off" on dev/demo solution or want to get some insight please try the below. You will be interested in "Hint" column on this occasion:

DECLARE
@Minimal_Page_Count int = 1000,
@Max_allowed_Fragmentation decimal(5,2) = 5,
@Hours_Since_Last_Stats_Update int = 24
---------------------------------------------------------------------------------------------------

DECLARE @WorkingHours float = (SELECT DATEDIFF(mi, [sqlserver_start_time], SYSDATETIME()) FROM sys.dm_os_sys_info)/60.0

;WITH [index_usage_stats] AS
(
SELECT
[object_id],
[index_id],
[Reads] = [user_seeks]  + [user_scans] + [user_lookups] + [user_updates],
[Writes] = [user_updates],
[Last_Read] = [last_user_seek],
[Last_Write] = [last_user_update]
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
UNION ALL
SELECT [object_id], [index_id], NULL, NULL, [last_user_scan], NULL
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
UNION ALL
SELECT [object_id], [index_id], NULL, NULL, [last_user_lookup], NULL
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
), [ius] AS
(
SELECT
[object_id],
[index_id],
[Reads] = MAX([Reads]),
[Writes] = MAX([Writes]),
[Last_Read] =MAX([Last_Read]),
[Last_Write] = MAX([Last_Write])
FROM [index_usage_stats]
GROUP BY [object_id], [index_id]
), [Table_Stats] AS
(
SELECT
i.[object_id],
[Table_Row_QTY] = SUM(CASE WHEN i.[index_id] = 1 THEN p.[rows] ELSE 0 END),
[Total_Table_Size_(MB)] = CASE WHEN SUM(a.total_pages) > 0 THEN CAST(SUM(a.total_pages) / 128.0 as decimal(18,1)) ELSE 0 END,
[Not_Used_Table_Size_(MB)] = CAST(CASE WHEN SUM(a.total_pages) > 0 THEN SUM(a.total_pages) / 128.0 ELSE 0 END - CASE WHEN SUM(a.used_pages) > 0 THEN SUM(a.used_pages) / 128.0 ELSE 0 END as decimal(18,1))
FROM sys.indexes            i
JOIN sys.partitions         p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN sys.allocation_units   a ON p.[partition_id] = a.[container_id]
GROUP BY i.[object_id]
), [ALL] AS
(
SELECT
[DB_Name]   = db_name(),
[Schema] = OBJECT_SCHEMA_NAME(ips.[object_id]),
[Object_Name] = object_name(ips.[object_id]),
[Index_Name] = ix.[name],
[Allocation_Unit_Type] = ips.[alloc_unit_type_desc],
[Index_Type] = ips.[index_type_desc],
[Index_Size_(MB)] = CASE WHEN ips.[page_count]>0 THEN CAST(ips.[page_count] / 128.0 as decimal(18,1)) ELSE 0 END,
ts.[Total_Table_Size_(MB)],
ts.[Not_Used_Table_Size_(MB)],
ts.[Table_Row_QTY],
[AVG_Fragmentation(%)] = CAST(ips.[avg_fragmentation_in_percent] as decimal(5,2)),
[Is_Unique] = CASE ix.[is_unique] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_PK] = CASE ix.[is_primary_key] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_UC] = CASE ix.[is_unique_constraint] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Has_Filter] = CASE ix.[has_filter] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_Disabled] = CASE ix.[is_disabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Fill_Factor] = ix.[fill_factor],
[ius].[Reads],
[AVG_Reads_per_hour] = CAST([ius].[Reads] / @WorkingHours as decimal(16,1)),
[ius].[Last_Read],
[ius].[Writes],
[AVG_Writes_per_hour] = CAST([ius].[Writes] / @WorkingHours as decimal(16,1)),
[ius].[Last_Write],
[Reads_vs_Writes] = CASE WHEN [ius].[Reads] > 0 AND [ius].[Writes] > 0 THEN CAST(CAST([ius].[Reads] as decimal(18,5)) / ([ius].[Reads] + [ius].[Writes]) as decimal(6,3))END,
[Last_Stats_Update] = CAST(STATS_DATE(ips.[object_id], ips.[index_id]) as smalldatetime)
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) ips
JOIN sys.indexes ix ON  ips.[object_id] = ix.[object_id]
                    AND ips.[index_id]  = ix.[index_id]
LEFT JOIN [ius] ON  [ius].[object_id]   = ips.[object_id]
                    AND [ius].[index_id]    = ips.[index_id]
LEFT JOIN   [Table_Stats] ts ON ts.[object_id] = ips.[object_id]
WHERE   ips.[index_id] > 0
AND    ips.[page_count] >= @Minimal_Page_Count
)
SELECT *,
[Hint] = CASE WHEN [AVG_Fragmentation(%)] >= @Max_allowed_Fragmentation THEN N'ALTER INDEX [' + [Index_Name] + N'] ON [' + [Object_Name] + N'] REBUILD WITH (FILLFACTOR = ' + CAST((CASE [Fill_Factor] WHEN 0 THEN 100 ELSE [Fill_Factor]END) as varchar(3)) + N');' ELSE N'' END + nchar(13) + CASE WHEN DATEDIFF(hh,[Last_Stats_Update], SYSDATETIME()) >= @Hours_Since_Last_Stats_Update THEN N'UPDATE STATISTICS [' + [Object_Name] + N'] [' + [Index_Name] + N'] WITH FULLSCAN;' ELSE N'' END
FROM [ALL]
ORDER BY [AVG_Reads_per_hour] DESC


SQL Server Optimizer Time Threshold To Evaluate Candidate Plans

As far as I know, there is no hard "limit" in terms of some absolute value of time that the optimizer searches for a plan.

Each execution is budgeted some amount of percentage of time to explore the plan options. There are a number of factors that go into this - I trust that those factors are always changing somewhat behind the scenes between versions of SQL Server - there are optimizer improvements made and some new options or approaches to options could come out, the budget calculated could change, etc.

SQL Server (thankfully) uses a process where it looks for a "good enough plan" or a reasonable plan. The more complex your query is the more insanely high the number of permutations of choices the optimizer has to execute your query. So we want it to find a reasonable enough plan and move on - so our query can actually start executing. The whole point is "spend enough time to give me a plan that should work alright, don't spend too much time optimizing and exploring options"

We really don't have control over that. And we really shouldn't. We should look to the plan the optimizer came up with and focus more on tuning our queries, tuning our designs and tuning our indexes to best serve the business need.

Another way - in about 15 years of SQL Server Tuning, I've not really ever had to consider "darn it, if only the optimizer had 4 more ms of optimization" - normally all you need to know to make your queries faster is right there in the query plan you did get, or exploring questions like "what about a serial plan instead of a parallel plan? what happens if I throw a join hint in just to force order and see what decisions the optimizer makes so I can rewrite accordingly? What about row goals? What if I split this logic up, etc"


phpmyadmin/ mysql how to copy a table with all its constraints

Within phpMyAdmin, if you go to the table you wish to copy and click the "Operations" tab, there should be a section "Copy table to (database.table)"

Here you will be able to copy the table with the same constraints as the table you are trying to copy.


postgresql - paginate correctly ordering by non-distinct values?

I believe the simplest solution is to sort on multiple columns (the one(s) you need for your basic sort, plus another column (or set of columns) that would make the effective "key" of your query unique).

This is more difficult (but may not be impossible) if your table(s) don't have a primary or unique key; you'd have to establish that there is a set of columns that could uniquely identify the rows you're presenting.


As this is a loss less join thus in lossless join we know that on joining we can get original relation along with some extra tuples thus answere is r⊂S where r is the relation in which original reltions were decomposed.


Index names need to be unique in the entire database; indexes are not part of table so they need to be uniquely identified. The Interbase 6.0 Language Reference says:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

  ...
  index Unique name for the index

The Firebird Book, 2nd Edition by Helen Borrie says:

  CREATE INDEX <index-name>
  —names the index. The identifier must be distinct from all other object identifiers in the database, apart from constraint and column identifiers.