How to downgrade my mdf file from version 661 to 655 or earlier?

Simple answer: you cannot do that.

There's no tool, no undocumented way, no hack, no workaround to achieve this. SQL Server database files just aren't "backwards" compatible - you can easily go from 2005 to 2008, or from 2008 to 2012 - but you cannot go backwards.

You'll need to find another way to do this, e.g. by scripting out database structure and/or data into .sql files and running those on the old system.


Options for keeping two SQL Server Compact databases in sync?

From the development perspective, you could try the Microsoft Sync Framework.

For further information about Microsoft Sync Framework, refer to the following links:

http://msdn.microsoft.com/en-us/library/bb726015(v=sql.105).aspx
https://stackoverflow.com/questions/581110/using-ms-sync-framework-to-synchronize-two-sql-ce-dbs


Is it possible to wrap aggregate functions in Postgres?

For all I know, you cannot wrap an aggregate function. You have to write your own aggregate function.

If you write your own function, you may run into incompatibilies when upgrading or porting to another database. So, personally, I wouldn't bother and just add the delimiter to string_agg() on every call. It really isn't that much of a pain.

That said, it's not that complicated to write your own aggregate function, either:

A custom final function:

CREATE FUNCTION f_str_agg_final(anyarray)
    RETURNS text LANGUAGE SQL AS
$func$SELECT array_to_string($1, ', ')$func$;

The aggregate:

CREATE AGGREGATE f_str_agg (anyelement) (
    SFUNC     = array_append
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
   ,FINALFUNC = f_str_agg_final
);

Call:

SELECT f_str_agg (kat) from tbl;

Works for almost any type - except for array types. You would have to modify your aggregate for this. More in this closely related answer on SO.


PgAdmin4 unusable when database has lots of schemas

Yes, it will be because some of the system views will be getting extremely slow. No, switching tools is unlikely to help. There have been updates to how the system views are generated over the years to improve performance with complex databases. But 60,000 schemas? Not even tables, but schemas? That is so far beyond what 99.9% of users run that you are unlikely to find much help.

If you are committed to this approach, you should probably subscribe to the pg mailing lists where you can have an extended discussion about the beat way to manage your database.


I would guess these symptoms (PAGEIOLATCH_SH waits and longer execution times) are simply due to increased physical IO. SQL Server has to scan the entire source table during the BCP which may evict pages from buffer cache that the stored proc later references. When run by itself, the pages needed by the proc probably remain in memory.

Your assumption about BCP needing to read the entire table into memory before writing the result is incorrect. BCP streams results from the source query to the output file.


You are asking SQL Server to perform 19.5 Billion string manipulations (300,000,000 rows X 65 columns), all in one giant transaction.  That is assuming vbep_Full is a regular table with simple column definitions (not a view with joins to other tables and/or with computed comlumns).

If your CPU is not maxed out, you could try breaking the work into multiple parts, process each part in parallel.

I would also recommend working in small batches with implicit transactions.  That way if one of the batches fails, you could start that batch over again without having to start the other batches over.


How can I search a column in MySQL containing unix timestamps with a query referencing a different format?

Try this:

SQL code:

SELECT COUNT(FOO1)
FROM footable
WHERE FOOTIME >= UNIX_TIMESTAMP('2012-01-01');

SELECT COUNT(FOO1)
FROM footable
WHERE FROM_UNIXTIME(FOOTIME)>='2012-01-01';

Sample data:

CREATE TABLE footable (
  FOO1 varchar(30) primary key,
  FOOTIME int(11)
  );

INSERT INTO footable (FOO1, FOOTIME)
VALUES ('blah', UNIX_TIMESTAMP(now() - INTERVAL 1 YEAR)),
('Blahblah', UNIX_TIMESTAMP(now() - INTERVAL 2 YEAR)),
('BLablahblah', UNIX_TIMESTAMP(NOW() - INTERVAL 5 YEAR));

SQLFiddle demo


SAN for SQL Server: One large spindle set for all loads, or discrete by load?

This is a database recovery question first and foremost, then performance.

DB Recovery:

By keeping the LDF and the MDF file in the same physical array, then you lose any chance of performing a 'tail of log' backup.  This means your data is gone from either:

The last transaction log taken and copied off the failed array.
A mirrored pair/availability group/replicated database elsewhere

So first question is 'how much data can you lose'.  If you cannot lose more than the frequency in which you back your logs then putting it on that array isn't going to meet your data loss objective.

Performance:

The rest really depends on your usage patterns.  It seems like you do not have a active machine we can get a baseline from, if I'm wrong let me know.  Assuming we don't though, we should understand that DiskIO is critical, especially when there might not be enough RAM or in a high transaction environment. Do you think it will be very highly transactional?  If so, give lots of disks to the transaction log drive.  That version of the Dell SAN let's you short stroke your disks which should give you a good performance boost as the transaction log drives typically go multi disk not for space, but for write performance.

Putting TempDB on it's own disks was useful but if you don't have any real clear idea as to why you would do it, then really you should test this or just put it in the same array and test that.  TempDB can have odd patterns so you might get various performance results and growth.  Many people though do just put it on the same disks as the data.  It's up to you in this case.

It sometimes might be worth segregating disks by the app patterns and the DBs that support them.  I'm not sure if you have that option.  If you don't, your layout looks fine but without testing it's impossible to tell.  Personally I would test as much as I could first, look at how much growth we are expecting, and then see if it makes more sense to add more IOPS to the main data array, or to keep them separate.

Are you able to do any kind of benchmarking at all?


Improving concurrency in MongoDB via sharding

You are correct, with MongoDB the way to engineer around a write contention issue is to shard.

Your environment sounds like it's fairly bursty, in that you're not continually ingesting data and instead ingesting it in fairly discrete chunks. With this in mind, you could go with a collector/distributor model such as this:

     ------>{ workers }<------
     |                       |
[Shard01]               [Shard02]
    |                      |
    ---->[Persistant]<------

The workers would upsert/push their results into the sharded collection/database, and once the job was completed a batch-process then uses something like db.copyDatabases() to push the result set at the monolithic (and cheaper to run) single instance Mongo. As the copy database process can push all of its updates in one run, it should experience much fewer write-lock problems.


Sync MySQL database and Quickbooks Database

quickbooks have completely different database, its not SQL based
For quick books Enterprise you should have SQL data optimizer and you should be able integrate with MySQL. QuickBooks pro and premium don't have that feature but there are third party applications that might do database optimization.


Is there a way to associate a description with database snapshot?

Is there any other way to associate a metadata with database snapshot when it is created?

No. Since a database snapshot is read-only, you cannot modify or add any settings to it. In-fact, extended properties cannot be applied to it.

So your best option is to have a table in the database (or a utility database) called e.g. dbo.database_snapshot_history with columns like servername, databasename, snapshotname, createBY, createdate


A recursive view is just syntactic sugar for a recursive CTE.

create or replace recursive view foo (name, groupid, parentid) as
  select name, groupid,parentid from rl.groups where groupid=2
  union
  SELECT groups.name, groups.groupid, groups.parentid
    FROM rl.groups AS groups, foo
    WHERE groups.groupid = foo.parentid ;

is the same as your CTE.

If you want to parameterize a CTE, wrap it in a function.


Filtering data from a mysql table using a date range where date format is VACHAR ('AUG-18')

You should store your data differently. Storing year and month separately might be a good idea. You can do that like this:

ALTER TABLE your_table ADD COLUMN training_year int, ADD COLUMN training_month tinyint;

UPDATE your_table SET
training_year = YEAR(STR_TO_DATE(TRAINING_DATE, '%b-%y')),
training_month = MONTH(STR_TO_DATE(TRAINING_DATE, '%b-%y'));

When you don't need your TRAINING_DATE column anymore, you can drop it.

Now you could query your table like this:

SELECT gender,
       COUNT(*)
FROM your_table
WHERE training_year = 2018
  AND training_month BETWEEN 9 AND 11
GROUP BY gender;

read more about the STR_TO_DATE() function and some more here


Why is real table size much bigger than estimated size?

For each row InnoDB use additional disk space, such as:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

so, when You test it as described - it will use 30+- bytes per row, and this is will be similar with result
additional source of information:

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-physical-record.html


Set up a postgres database with a specific role without password prompt

You edited away the problem.

In your comments you say that the database is really projATX_db. At least that is what you think. But PostgreSQL translated all unquoted text to lower case, so when you run

CREATE DATABASE projATX_db;

the database is really called projatx_db

Now the entries in the password file are not subject to this lower case conversion (that is specific to SQL), so projATX_db in the password file does not match your database.


Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value


Trying to reduce number of table scans when querying on the same column but different values

The join to 'Products' in both your sub queries is redundant and can be removed. You can filter directly on "Where od.ProductCode = 'S18_1589'" There is a FK between Products and OrderDetails, so there can't be a ProductCode that is not in Products.
The general challenge you are trying to solve is called 'Relational Division'. Your query will work for only 2 products, but what about 3, 10, or 30?? Search the forum here and you will find many excellent discussions on it. For your case, I would use the following solution. It relies on rephrasing the question in double negative form - "Show me all customers, for which there isn't a single ProductCode from our ProductsList, which is not in the customer's order items"

WITH prodctsList
AS
(
SELECT *
FROM   (VALUES ('S24_1628'), ('S18_1589')) AS products(productCode)
/* Add as many products as needed */
)
SELECT customerNumber
FROM   customers AS C
WHERE NOT EXISTS (
                  SELECT productCode
                  FROM   productList
                  EXCEPT
                  SELECT productCode
                  FROM   orders AS O
                         INNER JOIN
                         orderDetails AS OD
                         ON O.orderNumber = OD.orderNumber
                  WHERE  O.customerNumber = C.customerNumber
);

A solution without EXISTS and VALUES table constructor

WITH prodctsList
AS
(
    SELECT 'S24_1628' AS productCode
    UNION ALL
    SELECT 'S18_1589'
/* Add as many products as needed */
)
SELECT O.customerNumber
FROM   orders AS O
       INNER JOIN
       orderDetails AS OD
       ON O.orderNumber = OD.orderNumber
       INNER JOIN
       productList AS PL
       ON PL.productCode = OD.productCode
GROUP BY O.customerNumber
HAVING COUNT(*) = (SELECT COUNT(*) FROM productsList);


Long running VACUUM VERBSE in Postgres 9.5 - how much time remaining?

You can find the pid of the vacuum process, then do a strace -y -p <pid> and see what part of the table it is currently reading, then compare that to the overall size of the table.  You don't want to leave strace running all the time, as it slows the process down, so see what you need then ctrl-c out of the strace.  You will have to catch it when it is reading the table, not an index, as seeing where it is at in an index is not informative.

Alternatively, if you have the entire history of that verbose output, you can see where it is by summing all of the rows and pages from INFO:  "bigtable": removed 11184548 row versions in 157984 pages and comparing that to what the size of the table and number of dead rows you believe it to have.

What you've shown here starts in the middle of one cycle and ends in the middle of the next one.  It needs to scan all the indexes each time maintenance_work_mem fills up, and since each dead tuple take about 6 bytes and the default value of maintenance_work_mem is 64MB, that closely matches what you are seeing (64MB/6 = 11184810).

I think there is one step that VACUUM VERBOSE doesn't log which it probably should.  First it scans part of the table, accumulating dead tuples until it hits the end of the table or until maintenance_work_mem is full.  It I don't think it logs the end of this step.  Then it scans each index, logging the info for each one.  Then it rescans that same part of the table, marking the dead tuples as being vacuumed away, and logs that info.

I'd interrupt the vacuum and increase maintenance_work_mem and restart. Some amount of work may be lost, but it should be small compared to the speed up you will see going forward.  Unless you conclude it is already like 99% done or something.

I'd also change maintenance_work_mem to 1GB in postgresql.conf, as well as lowering autovacuum_vacuum_cost_delay to 2ms, to try to prevent future occurrences of similar problems.

But more importantly, I'd upgrade off of 9.5 ASAP.  It is still "supported" as far as security fixes are concerned, but people with stringent performance requirements shouldn't be using a version that old.  Yes, it is common management conundrum: "Doing X, Y, and Z are all a higher priority than upgrading".  Well, upgrading will probably make X, Y, and Z easier to do.


Permissions needed to call DBMS_DATAPUMP from within package

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure.

Long Answer:

In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles assigned to those two roles). Attempting to explicitly grant all 2683 privileges resulted in 2566 failures, but the package was able to call DBMS_DATAPUMP successfully.

The question then became: which of the remaining 117 privileges are actually needed for DBMS_DATAPUMP? I then grouped the remaining privileges and revoked them one group at a time (I assumed that it was a group of related permissions that would ultimately be needed). After running through nearly all of the groups, only the set of CREATE ANY privileges remained. When I revoked that group, the package stopped working. I then granted each of those permissions, calling the package after each grant. It was finally CREATE ANY TABLE that restored functionality to the package. A little further tinkering determined that CREATE TABLE is, in fact, sufficient to call DBMS_DATAPUMP from within a package.

CREATE TABLE is the only permission that I've found needs to be granted for the functionality found in the procedure shown in the question. Other procedures/datapump settings may require additional privileges. dbms_datapump.create_job_view, for instance, requires CREATE VIEW. In that case, the error message when the privilege is missing is, more reasonably, ORA-01031: insufficient privileges.


How to change the current database in an SQL Query window in SSMS?

Read this link

This issue occurs because Azure SQL Database does not support the "use" command.

You have to install the latest cumulative update for SQL Server


How to update MariaDB if the current version is not in default directory?

take a backup. either a) a copy of the datadir, or b) a sql dump (preferably both)
install the mariadb-10.3 packages

Normally only a single major upgrade is supported like 5.5 -> 10.0 -> 10.1 -> 10.2 -> 10.3 is supported however you may as well try and having your backup ready, you can install the datadir back.

Run mysql_upgrade after the upgrade.

Or, clear the datadir, run mysql_install_db -u mysql and then load the new version with the mysqldump.

Test on a test server, if downtime or potentially incompatibly is an issue.


Postgres and Windows AD using windows authentication

First you have to note that PostgreSQL doesn't really have a concept of anonymous usage.  So you do not have a way to log in without the user being added.

You have two options. The first is you can add appropriate ad users and groups manually.  Or you can add them using an AD trigger (see https://superuser.com/questions/909554/trigger-an-event-when-computer-is-added-to-active-directory for a similar question, and full documentation at https://technet.microsoft.com/en-us/library/dd277403.aspx).  Please note, the latter is a bit of a security concern and you need to make sure you aren't giving out access beyond what you want to give).

If you go the latter direction, you need to make sure you are restricting access to the public role.


You can use check Java Heap memory for each node. Like Total Java Heap Memory and Using Java Heap Memory.
One most important is CPU Utilization for each node.
Set alerts for errors. system.log have lots of information about.
You may set alerts for data disk and log disk.
Heartbeat check of servers like if you dont receive in few min it will get alerts to you.
Also dropped mutations and hinted hindoff clear alerts.

Basically you need to start observe system.log and will get more and more error for monitoring.


Does it make sense to have CASE .. END in an ORDER BY?

General answer

First, I want to address ambiguity in the premise:

  It is common to use parameters in the WHERE part of queries, and to have some computed columns in the SELECT part, but not that common to parameterize the ORDER BY clause.

Computed columns in the SELECT part are hardly ever relevant for the query plan or performance. But "in the WHERE part" is ambiguous.

It's common to parameterize values in the WHERE clause, which works for prepared statements. (And PL/pgSQL works with prepared statements internally.) A generic query plan often makes sense regardless of provided values. That is, unless tables have a very uneven data distribution, but since Postgres 9.2 PL/pgSQL re-plans queries a couple of times to test whether the generic plan seems good enough:

PostgreSQL Stored Procedure Performance

But it's not as common to parameterize whole predicates (including identifiers) in the WHERE clause, which is impossible with prepared statements to begin with. You need dynamic SQL with EXECUTE, or you assemble the query strings in the client.

Dynamic ORDER BY expressions are somewhere in between both. You can do it with a CASE expression, but that's very hard to optimize in general. Postgres might use indexes with a plain ORDER BY, but not with CASE expressions hiding the eventual sort order. The planner is smart, but not an AI. Depending on the rest of the query (ORDER BY may be relevant for the plan or not - it's relevant in your example), you may end up with a sub-optimal query plan all the time.
Plus, you add the minor cost of the CASE expression(s). And in your particular example also for multiple useless ORDER BY columns.

Typically, dynamic SQL with EXECUTE is faster or much faster for this.

Maintainability should not be a problem if you maintain a clear and readable code format in your function body.

Fix demo function

The function in the question is broken. The return type is defined to return an anonymous record:

RETURNS record AS

But the query actually returns a set of records, it would have to be:

RETURNS SETOF record AS

But that's still unhelpful. You'd have to provide a column definition list with every call. Your query returns columns of well-known type. Declare the return type accordingly! I am guessing here, use actual data types of returned columns / expressions:

RETURNS TABLE (make text, model text, year int, euro_price int, colour text) AS

I use the same column names for convenience. Columns in the RETURNS TABLE clause are effectively OUT parameters, visible in every SQL statement in the body (but not inside EXECUTE). So table-qualify columns in queries in the function body to avoid possible naming conflicts. The demo function would work like this:

CREATE or REPLACE FUNCTION get_car_list (
    _colour            text,
    _min_price         numeric,
    _max_price         numeric,
    _sorting_criterium car_sorting_criteria)
  RETURNS TABLE (make text, model text, year int, euro_price numeric, colour text) AS
$func$
      SELECT c.make, c.model, c.year, c.euro_price, c.colour
      FROM   cars c
      WHERE  c.euro_price BETWEEN _min_price AND _max_price
      AND    c.colour = _colour
      ORDER  BY CASE WHEN _sorting_criterium = 'colour' THEN c.colour     END
              , CASE WHEN _sorting_criterium = 'price'  THEN c.euro_price END;
$func$  LANGUAGE sql;

Do not confuse the RETURNS key word in the function declaration with the plpgsql RETURN command like Evan did in his answer. Details:

Difference between return next and return record

General difficulty of the example query

Predicate on some columns (even worse: range predicates), other columns in ORDER BY, that's already hard to optimize. But you mentioned in a comment:

  Actual result sets can be in the order of several 1.000 rows (and thus, sorted and paginated in smaller chunks server-side)

So you'll add LIMIT and OFFSET to these queries, returning the n "best" matches first. Or some smarter pagination technique:

Improve performance for order by with columns from many tables

You need a matching index to make this fast. I don't see how this could possibly work with CASE expressions in ORDER BY.

Consider:

Optimize a query with small LIMIT, predicate on one column and order by another
Can spatial index help a "range - order by - limit" query


Today I found new logs at /var/log/upstart

So, terminating with signal 1 was the after effect but not the cause.

I saw next information

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

So, I do assume, that the reason was in memory excess. Thus, I decreased max_threads (actually, it's the max_connections parameter) to have a security delta of memory.


VS2012 setup spoils SQL Server 2012 Data Tools for everybody or just me?

I had a similar problem with my installation of VS 2012 (Premium). I installed VS 2012, at the custom installation options screen I chose the option for SSDT, but when actually using it, it was really absent (same error as yours).

My idea is that the default installer only adds the template for the project, but not SSDT itself. I also had to install SSDT separately (in this case only for VS 2012) and it worked (SSDT for VS 2010 is necessary only if you want to open SQL projects in VS 2010, but I guess you know that already).


How to optimize a table/db with 200 insert/sec

There are multiple design changes to make.

Use InnoDB, not MyISAM.  You need row-level locking, not table-level locking.  (And set innodb_flush_log_at_trx_commit = 2)  A blog on conversion tips:  http://mysql.rjweb.org/doc.php/myisam2innodb .  A MyISAM INSERT always blocks a SELECT.  An InnoDB INSERT rarely blocks a SELECT.

Rethink your counters.  You have reached the "limit" of bumping counters on the fly.  Instead of bumping them one at a time, batch them.  One approach is to write the actions to a table, then do batch operations on it to update the various counters.

At 200/sec or more, I recommend ping-ponging two staging tables, as described here:  http://mysql.rjweb.org/doc.php/staging_table  Note that you can also batch the INSERTs with this technique, thereby speeding them up considerably.

Get rid of all the triggers, unless you can explain something that won't be efficiently covered by the above suggestions.

180s for an insert -- that is what can happen if connections and queries pile up.  Your Max_used_connections and Threads_running were probably very high.  More than a few dozen for either of those can imply things like "180s".

It would help to know the "flavor" of the application -- Data Warehousing?  MOOG?  Search?

EDIT:

Yes, convert all of your tables to InnoDB.  Then lower key_buffer_size to 40M and raise innodb_buffer_pool_size to 70% of RAM.  (Reference:  http://mysql.rjweb.org/doc.php/memory )

max_heap_table_size and tmp_table_size, currently at 512M are dangerously high, especially when hundreds of queries are running at the same time.  Perhaps no more than 1% of RAM is wise.  This change may have no impact, or it may impact large, complex, SELECTs.  But the change should help prevent swapping, which could be the real problem you are experiencing.  (Swapping is terrible for MySQL.)


Join Two queries with different declarations

WITH StatusChanges AS
(
    SELECT
       HistoryID,PartID,Status, DateAdded,
       rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded)
    FROM aAssetHistory
) SELECT
    A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
    A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
    (
       SELECT Top 1 Disposition
       FROM aAssetDisposition AD
       WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
          CASE
             WHEN A.LocStatus='In Repair' THEN EstimatedCompletion
             WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)
             WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded
          END 'Available',A.AssetComment,
          Cast(ISNULL((SELECT Top 1 1
                       FROM aAssetMarketingDocuments AMDocs
                       WHERE A.aPartid=AMDocs.aPartid),0) As bit
       ) Attachment
    INTO #tmp
    FROM aAsset A
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid
LEFT JOIN (
          SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC)
          FROM StatusChanges SC1
          LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1
          WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

Query2:

SELECT
    A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive,
    ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange',
    ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact',
    A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes]
FROM aAssetMarketing A
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID
LEFT JOIN aCompany On [PotentialCustomerID]=COid
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID
LEFT JOIN (
          select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange,
          max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease,
          max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale
FROM [aAssetMarketingDealStructure]
JOIN #tmp A ON
A.SerialNumber, A.PartNumber, Location, A.Fund, Disposition, DocumentStage, Lease, Sale
GROUP BY AssetMarketingID) DS
    ON DS.AssetMarketingID=A.AssetMarketingID
ORDER BY A.ContactDate DESC


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

For the simple case of a single given id1:
SELECT DISTINCT x.id2   -- DISTINCT only needed if there are dupes
FROM   tbl x
WHERE  x.id1 =  @a
AND    x.id2 <> @a;

Generally, there are basically four techniques to do what you are after. Both columns could just as well be in different tables, almost the same problem.
NOT EXISTS
SELECT DISTINCT x.id2
FROM   tbl x
WHERE  <some condition>
AND    NOT EXISTS (
    SELECT FROM tbl y
    WHERE  <some condition>
    AND    y.id1 = x.id2
    );

LEFT JOIN / IS NULL
SELECT DISTINCT x.id2
FROM   tbl x
LEFT   JOIN tbl y ON y.id1 = x.id2
                 AND <some condition for y>
WHERE  <some condition for x>
AND    y.id1 IS NULL

EXCEPT
SELECT DISTINCT id2
FROM   tbl
WHERE  <some condition>

EXCEPT ALL            -- ALL to make it faster - no dupes left after DISTINCT
SELECT tbl.id1
FROM   tbl
WHERE  <some condition>

NOT IN
SELECT DISTINCT x.id2
FROM   tbl x
WHERE  <some condition>
AND    x.id2 NOT IN (
    SELECT DISTINCT id1
    FROM   tbl y
    WHERE  <some condition>
    );

You'll have to test which one is fastest for you. Benchmarks disagree. It depends on data distribution and other details. NOT IN rarely wins. It's mostly one of the first two.
As @ypercube commented: if (id1, id2) is unique, the DISTINCT clause is not needed - except the one in the NOT IN sub-query, which is meant to help performance.
Related answer with more details for PostgreSQL on SO:

Select rows which are not present in other table


Backup & Restore Error in MS SQL Server 2016

The error message

  The database was backed up on a server running version 13.00.1400. That version is incompatible with this server, which is running  version 11.00.5343.

means that you are having backup of sql server 2016 RC3 --> 13.00.1400 and you are trying to restore it on your PC which is running 11.00.5343 --> sql server 2012 SP2 + GDR 3045321 fix.

Restoring the database from higher version to lower version is not supported using backup restore or attach/detach methods.

A RESTORE HEADERONLY FROM DISK = 'X:\yourBackupFile.bak' will give you the major, minor and software version build numbers that you can confirm.

Downgrading database is a pain !

Best is to install FREE DEV sql server 2016 RTM + CU1 on your PC machine and restore the backup.


Splitting table with many field for convenience example

There are a couple of issues with your proposed design.
Starting with the simplest issue:  The "age" column. Attributes like "age" are calculated values that depend on an underlying attribute. You must be storing the date somewhere else in the database.  I recommend that you compute the age in an SQL statement or make it into a virtual column.
Second, the facts in the user_meta table seem to mash several concepts together:  User authentication, account info, and contact information. Given that mix, I'm guessing you will have only one row per user--unless you allow users to create multiple login identities based on email address. If you have a one-to-one relationship, why are you splitting it into two tables?  Absent more information about your use case, I actually think you need 5 tables:  User identity, physical addresses, email addresses, account activity, and passwords.
Your proposed schema has poor information security. Passwords should be stored separately so you can limit access and implement other security measures.  Similarly, you probably want to limit access to email addresses.
So my recommendation is that you look at ALL your user attributes and see what the logical groupings are. Consider which sets of information have a one-to-one or many-to-one relationships. Consider the security aspects when making the groupings.


System-wide startup file for Postgres 10 on Ubuntu 18.04

/etc/postgresql-common/psqlrc does work with Ubuntu packages.

Working example with Ubuntu 18.04 and PostgreSQL 11 from apt.postgresql.org.

$ echo "\echo This is the common psql rc file" | sudo tee /etc/postgresql-common/psqlrc
\echo This is the common psql rc file

$ ls -l /etc/postgresql-common/psqlrc
-rw-r--r-- 1 root root 32 mai   23 11:36 /etc/postgresql-common/psqlrc

$ sudo -u postgres psql postgres
This is the common psql rc file
psql (11.3 (Ubuntu 11.3-1.pgdg18.04+1))
Type "help" for help.

postgres=#

When such things don't produce the expected result, a quick way to troubleshoot is to launch the command with strace and examine failures/successes to open files within the captured stderr output.

Example:

$ sudo -u postgres strace psql postgres 2>/tmp/strace-output
This is the common psql rc file
psql (11.3 (Ubuntu 11.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# \q

$ grep psqlrc /tmp/strace-output
access("/etc/postgresql-common/psqlrc-11.3 (Ubuntu 11.3-1.pgdg18.04+1)", R_OK) = -1 ENOENT (No such file or directory)
access("/etc/postgresql-common/psqlrc-11", R_OK) = -1 ENOENT (No such file or directory)
access("/etc/postgresql-common/psqlrc", R_OK) = 0
openat(AT_FDCWD, "/etc/postgresql-common/psqlrc", O_RDONLY) = 4
access("/var/lib/postgresql/.psqlrc-11.3 (Ubuntu 11.3-1.pgdg18.04+1)", R_OK) = -1 ENOENT (No such file or directory)
access("/var/lib/postgresql/.psqlrc-11", R_OK) = -1 ENOENT (No such file or directory)
access("/var/lib/postgresql/.psqlrc", R_OK) = -1 ENOENT (No such file or directory)

In your case if you have a  /etc/postgresql-common/psqlrc you will see what exact errors occur when trying to access it.


The SQL Server Agent job definitions are stored in the msdb database, in the sysjobs and sysjobsteps tables. You could do a plaintext search on those tables with the following query:

SELECT *
FROM msdb.dbo.sysjobsteps
WHERE subsystem='TSQL' AND
      command LIKE '%nameofmyprocedure%'

Note that there are still cases when your stored procedure still won't show up.

If there's another stored procedure run by the job that, in turn, launches the stored procedure you're looking for
If an SSIS package launches the stored procedure
If the procedure isn't launched by SQL Server Agent, but by some other scheduler task


Dont' see option to download/upload some SSRS reports

It turns out that I had to give content manager explicitly to all the reports where download option was unavailable even though I was a site admin on that box along with content manager on parent folders of respective reports. This fixed the issue and I was able to see the download option now. It's strange when you have admin rights on entire site but still have to give rights to individual reports.


FULL joins can be tricky. Try this version:

select
    -- columns from db1
    a_GUID = a.ObjectGUID,
    a_First = a.first_name,
    a_Last = a.last_name,
    a_Full = a.description,
    a.domain_user_id,
    a.sAMAccountName,
    a.employeeID,

    -- columns from db2
    b_GUID = N'{' + b.ADobjectGUID + N'}',
    b_First = b.GivenName,
    b_Last = b.sn,
    b_Full = b.displayName,
from
    ( select *
      from db1.dbo.tb1 as a
      where a.delete_date is null
    ) as a
  full outer join
    db2.dbo.tb1 as b
  on
      a.ObjectGUID = N'{' + b.ADobjectGUID + N'}'
  and a.first_name = b.GivenName
  and a.last_name = b.sn
  and a.description = b.displayName
where
      a.ObjectGUID is null
   or b.ADobjectGUID is null
  ;


What is refreshing my table in SQL Server?

Depending on the situation outside of what you have mentioned, you might be able to eliminate this problem by creating a new account on mysql and remove access to the db or the table of all other accounts.  Then you would have to update the system elements you want affecting that data.  This approach potentially is easy, but it also potentially is unfeasible.

If you cannot do that, I'd recommend a divide and conquer approach.  There a lot of ways to divide this up.  My first impulse is to look at the following:

SEARCH FOR THE PROBLEM AREA 1:  INFORMATION SCHEMA
Search the information_schema for %tablename% to see if there are procedures, triggers, or foreign key constraints referencing that table.  Look to see what all those references are doing.  In the triggers and procedures I would look for DELETE on the table and if there are foreign key contraints affecting that table, I'd be looking to see what other tables having data deleted would delete records from the table where you entered data.

SEARCH FOR THE PROBLEM AREA 2:  APPLICATIONS TOUCHING THE DATABASE
I would search the source code for the tablename, again looking to see what each reference to the table is doing.  Depending how your application, if you have one, sets up its sql, you might be able to find the culprit by searching for "delete from tablename" to filter references that cannot delete anything.

SEARCH FOR THE PROBLEM AREA 3:  CRON
Cron could be running shell scripts referencing the database.  The cron statements could reference shell scripts that could be anywhere on a server with access to the database.

SEARCH FOR THE PROBLEM AREA 4:  PEOPLE
If other people have the permissions to have done this either directly or through their programs, how you handle this depends a lot on the nature of your responsibilities and theirs, and the relationships you have with them.  Asking around is usually a good idea, but you have to assume you might not get correct information that way. Taking away all permissions except new ones you assign is a good way to find out what is going on, but it is not always appropriate to take that sort of action, especially if you are junior to the person who permissions you eliminate.


You need to execute following query to get the hostname:

SELECT boot_val,reset_val
FROM pg_settings
WHERE name='listen_addresses';;


pg_switch_wal In Point In Time Recovery PostgreSQL

In theory, you can run "pg_basebackup" just once (or once per major version upgrade) but then it will take longer and longer to do a restore, as it has to run through all the accumulated WAL since that backup.  So it usually makes sense to do a new pg_basebackup periodically.

"The database" won't do it for you.  There are a variety of backup tools out there that probably will (pg_barman, pgbackrest, pg_probackup, etc.).  But you didn't mention using any of them.

pg_switch_wal() is not necessary as pg_basebackup does it for you.

pg_basebackup will archive the wal files if you set it to, but will only do so for WAL files which are generated during the duration of the base backup itself.  This is suitable for a snapshot backup, but not for PITR.  In other words, it will let you restore to the exact moment the backup command finished, but is not enough to let you restore to some point after that time. To backup WAL files at other times, you need to set the "archive_command" setting to something that will copy the files for you.  (Also, if you have a valid "archive_command" in place before you start the basebackup, then the basebackup itself doesn't really need to backup the WAL in the first place.  Then the base backup will not be self contained, you will have to pair it with the WAL archive in order to restore.)

The tutorial you refer to shows a setting for "archive_command".  However, the setting it shows is not bullet-proof.  As the docs say, "this is an example, not a recommendation".  It has two main problems.  Once is that it does not fsync the copy before declaring success.  That means the system can remove/recycle the WAL file from pg_wal, before the copy of it has been flushed to the disk drive; and so a crash at the wrong time can leave you with zero valid copies.  The other problem is that it seems to be copying the file locally.  That means that if your computer is destroyed, you will lose your backup at the same time as you lose your master copy.  (It is possible that it is not copying locally, because /var/lib/postgresql/pg_log_archive might be a network mounted file-system; but if so then that exacerbates the fsync problem, how do you know the copy on the remote file system is truly safe?)

If you don't want to use "archive_command", then another tool is "pg_receivewal".  You would run this on another server, and it will connect to your Postgresql instance and "pull" the wal files, rather than the "push" that "archive_command" does.

Yet another option is to set up a streaming standby on another server, set "archive_mode" to "always", and then have "archive_command" run on the standby, rather than on the master, to save your WAL files.


Combining previously different rows after changing value during query

Just aggregate one more time.

You can do this by

Materialising your intermediate resultset (SELECT ... INTO #intermediate FROM...) and then aggregating the cached intermediate results
Wrapping your current query in a set of brackets and SELECTing against whatever alias you assign to it.

See this fiddle for an example. But for verbosity's sake, assuming your data is all stored in a table called barfoo, then one way you could do this using a Common Table Expression (or CTE) is...

DECLARE @iraPrefix VARCHAR(7);
DECLARE @iraLen INT;
SET @iraPrefix = 'FOO BAR ';
SET @iraLen = LEN(@iraPrefix);

WITH L1 AS (
   SELECT [client] = (
           CASE SUBSTRING([name], 1, @iraLen)
               WHEN @iraPrefix THEN SUBSTRING([name], @iraLen+2, (LEN([name]) - @iraLen))
               ELSE [name]
           END
       )
       , CONCAT( sal.firstname
               , ' '
               , sal.lastname) AS [salesman]
       , [revenue] = SUM(net)
   FROM barfoo as sal
   GROUP BY [name],CONCAT( sal.firstname
                      , ' '
                      , sal.lastname)
)
SELECT
        [client],
        [salesman],
        SUM([revenue]) AS [revenue]
FROM L1
GROUP BY [client],
        [salesman];


How do I switch off SCHEMABINDING for a view without recreating it?

Yes. It's good that you use SCHEMABINDING (we do always) and sometimes you have to remove it to change a dependent object. Just ALTER the view

ALTER VIEW myView
--Remove this WITH SCHEMABINDING
AS
SELECT ...
GO


Dynamically deciding which operator to use based on the type of the value at hand

So after some helpful comments here and from my colleagues at work I ended up creating functions (with overloading) to solve the issue:
CREATE OR REPLACE FUNCTION contains(haystack jsonb, needle text) RETURNS boolean AS $$
    DECLARE
      result boolean;
    BEGIN
      CASE jsonb_typeof(haystack)
        WHEN 'array', 'object' THEN
          result := haystack ? needle;
        ELSE
          result := haystack LIKE '%' || needle || '%';
      END CASE;
      RETURN result;
    END;
  $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contains(haystack text, needle text) RETURNS boolean AS $$
  BEGIN
    RETURN haystack LIKE '%' || needle || '%';
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contains(haystack text[], needle text) RETURNS boolean AS $$
  BEGIN
    RETURN needle = ANY(haystack);
  END;
$$ LANGUAGE plpgsql;

Then I can just include contains(entity.field1, entity.field2) in my query and it works for all three types I need right now. I can also easily extend it to work for more types.
Thanks for all your help


MySQL has a built-in function for parsing texts in the form of SUBSTRING_INDEX().

This simplifies your query to:

SELECT * FROM the_table
ORDER BY CAST(SUBSTRING_INDEX(data,' ',-1) AS UNSIGNED);


Question on turning on GTID for MySQL master and slaves

Right, it's modded.

  “We worked around this problem in fb-mysql by relaxing the constraint...”

Note that fb-mysql is a link to https://github.com/facebook/mysql-5.6/ ... which is a Facebook fork branch  of MySQL Server 5.6.


Why would a select on a single table refer to another table?

ok, I actually just worked it out; there is a security policy in place which is causing this, strange though as not even profiler picked that up
Roger


You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'.

That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E:

SELECT CONVERT(VARBINARY(25), '0x9473FBCCBC01AF', 1);
------------ the ,1 is important ---------------^^^

Result is binary:

----------------
0x9473FBCCBC01AF


Run a SQL statement in MySQL on each connection

Found it.

The parameter is named init_connect and will run the defined sql for each connection, except for those with the super privilege. No result sets are produced by this though so selects are not recommended for this.

The big cavet I see here though is that access to the objects is based on the connecting user's permissions, which make sense.. though it does make an 'autorun' style stored procedure harder to implement as that procedure needs to be accessible to everyone, which could cause problems if it's written with the expectation to be executed only once per connection.


SQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndex

In my post How to Fix sp_BlitzIndex Aggressive Indexes Warnings, I note that:

  Aggressive Over-Indexing: when there’s a lot of blocking on a table with 10+ indexes. Your DUI queries are probably getting held up by acquiring locks across many of these indexes to update ’em.

Over-indexing means your table likely has too many indexes. You probably don't want to add more - you probably want to dig more deeply into the indexes that already exist on the table, and start getting rid of the unused and duplicate indexes.

If you'd like, you can post the output of sp_BlitzIndex for your particular table. Scroll across to the right of your sp_BlitzIndex output, and you can see a More Info query. Run that, and sp_BlitzIndex will list out your existing nonclustered indexes. That's what we'd need to help you reduce the indexing overhead.

Update 2018-10-09: thanks for adding the sp_BlitzIndex output. Based on that, I can see that your clustered index is on 3 separate fields, and it's not a unique clustered index either. Generally speaking, this isn't a great clustered index design because the 3 clustering keys (TREE_ID, LEFT_VISIT, and RIGHT_something) are included in every single nonclustered index as well. (That's what the Secret Columns show.) That makes your nonclustered indexes larger than they need to be.

In terms of the rest of the indexes, it's time to start making some tough decisions: do you really need 4 copied subsets of the table (indexes) that all start with PRJ_ID? Do you really need a 6-key-field index that starts with TREE_ID, LEFT_VISIT (the same two fields that your clustered index starts on)? Those are the questions I'd start asking about the indexes to get down to a lower number.

If you can't reduce the number of indexes, then your options are:

Tune the locking/blocking queries so they run faster (like shorten their transactions), or
Throw hardware at it - if the blocking queries are held up by slow server power, then you can get them to finish faster this way, but it's not a great solution

Hope that helps!


Incorrect syntax in adventure works install script via SMSS

It looks like you are running the script from an SSMS window and it contains SQLCMD variables. Enable SQLCMD mode from the Query menu (Query-->SQLCMD Mode). Be sure the SQLCMD variable values are set as desired before running the script.


Error 1456 when trying to SET a Witness using TSQL

Unfortunately I do not have the reputation to comment, actually I would rather add this as a comment and ask you about the output of your sys.database_mirroring_endpoints DMV first...

I'm assuming that you have verified the connection to be working using telnet or PS Test-NetConnection...

This sounds like the endpoint is not configured for the same authentication type and algorithm.

Run

select dme.*,c.name as cert_name,c.thumbprint,dbp.name as db_principal_name,spr.name as s_principal_name from sys.database_mirroring_endpoints dme
inner join sys.certificates c on dme.certificate_id=c.certificate_id
inner join sys.database_principals dbp on c.principal_id=dbp.principal_id

on your Principal and Secondary instances. Verify STATE_DESC to read "STARTED" and note down the following items which should both be the same on both instances.

role_desc
connection_auth_desc
encryption_algorithm_desc
thumbprint

Run the same statement on the witness instance and also note down the db_principal_name (last column).

Compare your values from the Principal/Mirror and the Witness. If the Witness has any values other than your mirror, change the endpoint on the Witness accordingly. Also verify the thumbprint of the certificate to be the same on your instances.

Warning: changing your endpoint on the Witness might break any Availability Groups or other database mirroring configurations there, so use caution. You can script your existing endpoint configuration in the Object Explorer before you change anything (Server Objects --> Endpoints --> Database Mirroring).

For example, if your connection_auth_desc on Principal/Mirror says "CERTIFICATE" and encryption_algorithm_desc "AES" using certificate SomeCert:

ALTER ENDPOINT endpoint_mirroring
AS TCP(LISTENER_PORT=7024, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE [SomeCert], ENCRYPTION=REQUIRED ALGORITHM AES, ROLE=ALL)

The login for the user that is stated in db_principal_name should also be allowed to connect to your endpoint. Verify this e.g. using the GUI by going to Security --> Logins --> double click the login and under Securables you should see your endpoint with "CONNECT" permission.

If not and if your Login is named "SomeLogin":

grant connect on endpoint::endpoint_mirroring to [SomeLogin]


Is it possible to set PostgreSQL roles with privileges this way?

What is possible is what's specified in the documentation for GRANT (http://www.postgresql.org/docs/current/static/sql-grant.html).

To make modifications on tables, a user must own the object. When a user owns an object, a user can do whatever they want with it (including restricting themselves any kind of access, along with the ability to grant themselves any access again).

For your limited DBA, there are non-standard ways in which your requirements can be implemented, such as a DBA creating a function with SECURITY DEFINER that validates the executing user, and the command to execute, as something allowed, like the following example:

CREATE OR REPLACE FUNCTION limited_dba_action (p_action text) RETURNS void AS
$body$
BEGIN
  IF p_action !~* '^\s*(CREATE|ALTER)\M' THEN -- add any other valid operations, such as TRUNCATE, DELETE, etc
    RAISE EXCEPTION 'Valid operations through this function are limited to creating and altering objects';
  END IF;

  IF p_action ~ ';\s*[^\s]' THEN
    RAISE EXCEPTION 'Please include only one operation';
  END IF;

  EXECUTE p_action;
END;
$body$ LANGUAGE plpgsql SECURITY DEFINER RETURNS NULL ON NULL INPUT;

GRANT EXECUTE ON FUNCTION limited_dba_action TO your_limited_dba_user;

For your system using the database, this is the standard for non-object-owners (i.e. they can do whatever is being granted to them, but not alter/drop the object).

Finally your DBA is simply a standard superuser.

So yes, what you require being able to do is perfectly possible.


Union of data from all tables with name containing a substring

Yes, you need a script but it doesn't have to be 1000 lines.

--make a table or global temporary table to put the results in

CREATE TABLE TEMP_COLUMN_DATA
( PK_ID NUMBER(9),
  TABLE_SOURCE VARCHAR2(30),
  TEXT_CONTENT VARCHAR2(4000));

--add the primary key of PK_ID, are contents larger than 4000 characters?  use a CLOB

--create a trigger on the table if the PK_ID is null add a sequence value

DECLARE
v_text  VARCHAR2(4000);
CURSOR tab_col IS
Select TABLE_NAME from all_tab_cols
where COLUMN_NAME = 'COLUMNDATA'
and table_name LIKE 'TABLE%'
--modify the cursor here to exclude any tables you dont want
--and table_name not in('xxxx','xxxxx','xxxxx')
ORDER BY 1;
BEGIN
  FOR THE_TABS IN TAB_COL LOOP
    V_TEXT := 'INSERT INTO TEMP_COLUMN_DATA SELECT NULL,'
    || THE_TABS.TABLE_NAME
    ||','
    || THE_TABS.TABLE_NAME
    || '.COLUMNDATA '
    || ' from '
    || THE_TABS.TABLE_NAME
    || ' order by 2';
    DBMS_OUTPUT.PUT_LINE(V_TEXT);
    EXECUTE_IMMEDIATE(v_text);
  END LOOP;
 END;

--then do a select distinct columndata from temp_column_data


As  @a_horse_with_no_name stated in a comment, there is a function USER that returns the user of a session, so

select USER from DUAL

is a way to find out the user of a session.


SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

It's impossible to do this in the procedure.  This wait type is a function of the scheduler.  Each SPID gets a finite amount of time (4ms) on the processor.  Once that time, or quantum, elapses, the SPID gets put back into the wait list until resources are available again.  This wait type is indicating that the SPID was not able to complete its work on the CPU in the quantum and had to "yield" to another waiting process.

This cannot be changed with any SQL code.  You can specify resources be prioritized for your load with Resource Governor, as Paul White suggested.  Even so, unless, as Kin suggested, you are experiencing a lot of CPU pressure, Resource Governor won't help either.

Check for CPU intensive operations and minimize where you can.  One that bites a lot of people is the Implicit Conversion, that will quickly be discovered by scanning the execution plan.


Can Extended Events be used instead of Auditing?

Extended Events can capture all the events that are traced by auditing?

Yes and no. The built in auditing framework is built upon the extended events framework. This means any of the built in auditing will be serviced via the XE framework, assuming it is setup.

  Can we get all the functionalities of auditing just by using XEvents?

You can get most, but it's not 100% coverage. Since the auditing is built upon the framework there would be the possibility, however not all auditing events have a 1 to 1 or even a correlated mapping to non-auditing based extended events.


Moving Innodb database from Server A to server b

Tables can be moved in physical form, although it is not instant: https://dev.mysql.com/doc/refman/5.7/en/innodb-table-import.html

A more automated way would to use partial or full backups with tools such as mysql enterprise backup or Percona XtraBackup: https://www.percona.com/software/mysql-database/percona-xtrabackup which will allow you to backup a database quickly without impacting sourde database availability.

In the future, having a replica of your database will be the easiest way to increase your availability during maintenance (switchovers should only take seconds).


This answer from SO seems to indicate it may intentionally be clouded in mystery... which makes perfect sense if you're Microsoft and very much want people to move into the cloud instead of hosting their own architecture. The cloud is where they get a much bigger slice of the $ pie, after all!

You could always default back to classic recommendations on specs based on nature of workload, size, etc. People were spec'ing VMs long before DTUs :)

Here are a few resources in this vein, though there are many more and none are all-inclusive:

SimpleTalk's What Spec Server Should I Buy?
A DBA.SE post on DW server CPU/RAM calculations
Brent Ozar's What's the Smallest SQL Server You Should Build

Brent Ozar also offers training in this area if you really need to hit the nail on the head with this server transition (I am not affiliated with Brent Ozar Unlimited in any way).

At the end of the day, you'll probably have to do some tuning either way, so I'd start small and work my way up in a test environment. You know your application(s) and workload(s) best so you need to figure out what features and specs are going to get you to your desired state.


If you deleted the SYSTEM tablespace datafile as you say, and do not have a backup, there is no recovery. You will need to recreate the database.

Side note, you should never delete a datafile directly at the OS level and never the SYSTEM.


Safely changing innodb_buffer_pool_size on MySQL replication master?

What you have seems fine. I would add the following

Run this on the Master.

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_master_info = 1;

This will cause everything that has been uncommitted to be committed on shutdown.

Then, it flushes the binlogs to disk.

On the Slaves, run this

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL sync_binlog = 1;
SET GLOBAL sync_relay_log = 1;
SET GLOBAL sync_relay_log-info = 1;
SET GLOBAL sync_master_info = 1;

This will flush all the binlogs and relay logs. If the Slaves do not have binary logging enabled, then skip SET GLOBAL sync_binlog = 1;.

Your steps look like this now:

Stop replication on the 2 slave servers with STOP SLAVE.
Run SET GLOBAL innodb_fast_shutdown = 0; on all three servers
Run SET GLOBAL sync_binlog = 1; on the Master
Run SET GLOBAL sync_master_info = 1; SET GLOBAL sync_relay_log = 1; SET GLOBAL sync_relay_log-info = 1; SET GLOBAL sync_master_info = 1; on the Slave
Stop MySQL on all 3 servers with service mysql stop.
Make the changes in the my.cnf file on the master server.
Start MySQL on the master again to get the live database back online ASAP with service mysql start.
Make the changes in the my.cnf file in both the slave servers.
Start MySQL again on both slave servers with service mysql start.


Optimize view (and underlying table) for averaging timestamps into hours

Simpler view

For this objective:

  The point of which is to give averages of the numeric columns for each hour

.. it seems just as good to truncate to full hours, which is simpler and cheaper:

CREATE OR REPLACE VIEW spp.rtprices_hourly AS
SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
GROUP  BY 1, 2;

Faster query

Either way, an equivalent query on the view with sargable predicates would be:

SELECT *
FROM   spp.rtprices_hourly
WHERE  node_id = 20
AND    hour >= '2015-02-02 0:0'::timestamp
AND    hour <  '2015-02-03 0:0'::timestamp;

This is faster, but still not as fast as it could be. The major performance hit is because the index can only be used with an index condition on node_id, which is preserved in its original state in the view. That's why your index rtprices_node_id_interval_idx with node_id first is important. Why?

Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?

The second predicate on hour has to be filtered after the tuple has been fetched from the heap (the row has been read from the table). The large majority of rows is discarded late into the process, much work for nothing.

Much faster with direct query

It would be much faster to run the original query and apply predicates before you aggregate:

SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
WHERE  node_id = 20
AND    "interval" >= '2015-02-02 0:0'::timestamp
AND    "interval" <  '2015-02-03 0:0'::timestamp
GROUP  BY 1, 2;

You'll see index conditions for all predicates now. The more efficient index is still the one with node_id first. Why?

Multicolumn index and performance

Fast & short: create a function

So, this won't work nicely with a view. Use a function instead:

CREATE OR REPLACE FUNCTION rtprices_hourly(_node_id int
                                         , _from timestamp
                                         , _to timestamp = NULL)
  RETURNS TABLE (
    hour    timestamp
  , node_id int
  , rtlmp   numeric
  , rtmcc   numeric
  , rtmcl   numeric) AS
$func$
SELECT date_trunc('hour', r."interval")  -- AS hour
     , r.node_id
     , round(avg(r.rtlmp), 2)  -- AS rtlmp
     , round(avg(r.rtmcc), 2)  -- AS rtmcc
     , round(avg(r.rtmcl), 2)  -- AS rtmcl
FROM   spp.rtprices r
WHERE  r.node_id     = _node_id
AND    r."interval" >= _from
AND    r."interval" <  COALESCE(_to, _from + interval '1 day')
GROUP  BY 1, 2
$func$  LANGUAGE sql STABLE;

Be wary of naming conflicts between OUT parameters and column names. That's why I table-qualified all columns here.

Now you get top performance with a simple query:

SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp, '2015-2-3 0:0'::timestamp);

I put in a convenience feature, the second parameter defaults to "one day later" if you omit it:

SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp);

More about function parameters and default values:

Add function parameter to SQL query WHERE clause

You can query any range:

SELECT * FROM rtprices_hourly(1, '2015-2-2 10:0'::timestamp, '2015-2-2 20:0'::timestamp);


Backing up a really huge SQL Server database

If you do not have enough diskspace first confirm that you have backup compression enabled. This will greatly decrease the size of your database backup. If you still do not have enough space on your server to put your backup.

I see 3 options of achieving this

Try backing up to a network share which has sufficient diskspace to store your database backup

--> If you do not have enough space on a networkshare

Try backing up to different files on different drives/networkshares so you split the total size of the backup and store all the parts on different locations. This will also increase the speed of your backup
(Check out http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/)

--> If you still do not have enough space on any server/share/...

If you can physically reach your server, go buy a USB disk with sufficient space and connect this to the server and backup to this location.


I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/

To summarize from the link:

Set your transaction log large enough so it will not grow during normal operation.  All update activity stops in the database when the logs grows, so you want to minimize log growths.
For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS.  You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process.  While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged.  At some point, there was activity that caused it to grow to that size (index rebuild, etc.).  There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size


Seeing 2 very different execution plans for an UPDATE between test & prod environments

Hover over the clustered index update, you'll see a list of the other indexes involved.

If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.

See Wide vs. Narrow Plans by Bart Duncan.


Find changes made in the table in sql server 2012

You can configure SQL Server Auditing (Server and Database Audits), you have the ability to audit CRUD operations as well as other activity. One warning is to be aware of how much data you are auditing, logs fill up fast.


Is there any interruption of service when switching recovery models?

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

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

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


XACT_ABORT ON not working as expected on SQL Server 2012?

Check Microsoft documentation, specifically the top line:

  The THROW statement honors SET XACT_ABORT.  RAISERROR does not. New applications should use THROW instead of RAISERROR.

I believe that is your problem, right there.

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


different db stats in MongoDB replica set

Logically answer is YES.

When I tested on our multi terabyte DB's, I got differences between replica set members, however this was measuring mistake, error on the procedure. f.ex. db.stats() is not good command, because RS node can have db.system.profile -collection, what is NOT replicated to other nodes.

How to do it right (do these on all replica set nodes):

Stop all write traffic to MongoDB, you can do that by closing all clients OR giving (at Primary) command db.fsyncLock()
select right DB (use xyz) and give command db.setSlaveOk(true)
Don't use db.collection.find().count() or db.collection.count(), use command db.collection.find({_id:{ $gt: MinKey, $lt:MaxKey} }, {_id:1}).itcount() to count how many documents there is
After you have done these itcount() commands on multiple collections and you are happy (or not) to result.. Don't forget to give (at primary) command db.fsyncUnlock()

we had maintenance break and I did this counting procedure during it and got (this time) right result. Every RS node had same count of documents. ;-)


Performance Tuning for Huge Table (SQL Server 2008 R2)

Upgrade to SQL Server 2012 and use columnstores. They thrive in these requirements. Seriously, download the evaluation edition and give it a try. Drop all indexes, drop the clustered index, simply add a non-clustered columnstore index on all columns and give it a whirl. I've seen cases just like your that reduced the execution time to 2-3 seconds, mostly because of segment elimination kicking in. Some supplemental reads:

SQL Server Columnstore Performance Tuning
SQL Server Columnstore Index FAQ
Achieving Fast Parallel Columnstore Index Builds
Work Around Performance Issues for Columnstores Related to Strings
Use Outer Join with Columnstores and Still Get the Benefit of Batch Processing
Using Statistics with Columnstore Indexes


Script to automate enabling TDE on any unencypted user databases

I was checking K.encryption_state <> 3 (encrypted) to test whether I should enable encryption.
i should have also checked for encrypted (K.encryption_state <> 2) when testing the status.


Is there a way to get the max,count, or etc for all columns without naming them individually?

https://mariadb.com/kb/en/library/procedure-analyse/  (Also works in MySQL.)

SELECT * FROM blah PROCEDURE ANALYSE();

For example, using a table of Canadian cities; notice the min and max values:

mysql> SELECT * FROM canada PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
             Field_name: world.canada.id
              Min_value: 291724
              Max_value: 297207
             Min_length: 6
             Max_length: 6
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 294465.5000
                    Std: 1583.0944
      Optimal_fieldtype: MEDIUMINT(6) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: world.canada.country
              Min_value: ca
              Max_value: ca
             Min_length: 2
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('ca') NOT NULL
*************************** 3. row ***************************
             Field_name: world.canada.ascii_city
              Min_value:
              Max_value: zehner
             Min_length: 0
             Max_length: 42
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 10.0376
                    Std: NULL
      Optimal_fieldtype: VARCHAR(42) NOT NULL
*************************** 4. row ***************************
             Field_name: world.canada.city
              Min_value: (dup population)
              Max_value: Zehner
             Min_length: 1
             Max_length: 31
       Empties_or_zeros: 3
                  Nulls: 0
Avg_value_or_avg_length: 9.7861
                    Std: NULL
      Optimal_fieldtype: TINYTEXT NOT NULL
*************************** 5. row ***************************
             Field_name: world.canada.state
              Min_value:
              Max_value: 14
             Min_length: 0
             Max_length: 2
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 1.9996
                    Std: NULL
      Optimal_fieldtype: ENUM('','01','02','03','04','05','07','08','09','10','11','12','13','14') NOT NULL
*************************** 6. row ***************************
             Field_name: world.canada.population
              Min_value: 540
              Max_value: 4612187
             Min_length: 3
             Max_length: 7
       Empties_or_zeros: 4771
                  Nulls: 0
Avg_value_or_avg_length: 4300.6415
                    Std: 85242.3200
      Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL
*************************** 7. row ***************************
             Field_name: world.canada.lat
              Min_value: 0
              Max_value: 82.4832992553711
             Min_length: 1
             Max_length: 7
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 50.09833726667297
                    Std: 5.212114845361912
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 8. row ***************************
             Field_name: world.canada.lng
              Min_value: -140.98300170898438
              Max_value: 0
             Min_length: 1
             Max_length: 8
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: -89.74846842687205
                    Std: 23.8526411452119
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 9. row ***************************
             Field_name: world.canada.stripe
              Min_value: 0
              Max_value: 1190
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 966.6869
                    Std: 37.9899
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 10. row ***************************
             Field_name: world.canada.province
              Min_value: Alberta
              Max_value: Yukon
             Min_length: 5
             Max_length: 25
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 10.9220
                    Std: NULL
      Optimal_fieldtype: ENUM('Alberta','British Columbia','Manitoba','New Brunswick','Newfoundland and Labrador','Northwest Territories','Nova Scotia','Nunavut','Ontario','Prince Edward Island','Quebec','Saskatchewan','Yukon') NOT NULL
10 rows in set (0.04 sec)


Rolled back transactions during SQL Server Mirroring Failover

In FULL SAFETY mode you should be good to do this without data loss if there are no transactions in flight. Are you waiting for the state to change from Synchronizing to Synchronized after that restart? My guess is application users are in there when you are doing the failover, though.

If the applications are still connecting and data is still being modified then there are transactions in flight and that is the price you have to pay. Best to get a window and stop access. Shut down the site or apps accessing SQL, etc.


EAV based systems (e.g. Agresso) have query performance problems inherent to the EAV structure.  The problems are really based on three underlying issues:

The structure is fiddly to query, so you get added complexity in your queries.
You can't index the base table on any of the attributes, which drives some inherent inefficiency in the query plans.
You have to do multiple joins against a large table.

Some possible strategies for mitigating EAV performance problems are:

Using a clustered index to keep all records relating to a given parent in close physical proximity can help somewhat.  This minimises the I/O.
Consider using the metadata to create a view that flattens out the EAV structure.  You may have to redefine the view every time it changes, and it won't necessarily help performance much, but it will be much easier to query.  Try to create the view as a roll-up rather than a multiple way join.
Any attribute that is not strictly user defined (eg. transaction dates, amounts) should go on the base table.  These can also be indexed.
Resolve the most selective predicates in subqueries first and join against the sub-query.
If you have the option flatten out the physical structure, even if it has to be into a series of fields like 'StringField1', 'StringField2', 'DateField1', 'MoneyField1'.  Your application will have to hold the configration data anyway, so you can just map to a field on the database.This sounds clumsy, but it's much more efficient to query, and it doesn't have to be in the users' faces.  You can also use the metadata to generate a view over the table that names the fields, so other customers of the data can see a meaningful view of the data and query it without necessarily having access to the application metadata. If you run out of columns in the table, just add more in the database.  The field mapping system can always query the system data dictionary to see what's available.

At some point in these debates, somebody often chips in with a comment about XML columns ("I know, I'll just use XML").  To paraphrase JWZ, now you have two problems.  Around 1998 they installed another circle in hell just for people who defile their systems with XML blob fields.

Although it doesn't look slick and high-tech, just having a set of user attribute columns on the table is by far the best solution.  It is the most efficient, and there are many ways to make this approach more user friendly at the database level.

EDIT: Here is a snippet of T-SQL showing how to create a view that flattens out an EAV structure.  Note that you wound have to write a generator for the view based on the application's attribute metadata and re-create it every time this is changed.

if object_id ('dbo.Foo') is not null
    drop table dbo.Foo
go

if object_id ('dbo.FooView') is not null
    drop view dbo.FooView
go

create table dbo.Foo (
       FooID     int not null
      ,FooKey    varchar (20)
      ,FooVal    varchar (100)
)

insert Foo (FooID, FooKey, FooVal)
values (1, 'Date', '2011-11-11')

insert Foo (FooID, FooKey, FooVal)
values (1, 'Amount', '100')

insert Foo (FooID, FooKey, FooVal)
values (2, 'Date', '2012-12-12')

insert Foo (FooID, FooKey, FooVal)
values (2, 'Amount', '200')
go

create view dbo.FooView
    as
select *
  from (select FooID
              ,FooKey
              ,FooVal
          from dbo.Foo) keys
 pivot (Max (FooVal)
        for FooKey in ([Date],[Amount])) pvt
go

select * from dbo.FooView

This is much more efficient than solutions involving multiple self-joins on the table, and will be somewhat efficient with a clustered index on the base entity and attribute type keys.  You will probably also want to put type conversions into the view so you can sort the results correctly.


In versions of SQL Server earlier than 2012 (plus some patching), Linked Server permissions needed SA privs to use statistics information when processing remote queries. That could be what's messing you up.

For more information, see Conor Cunningham's talk on distributed queries here.


question about using tokuDB or InnoDB for financial data

A few things to note:

XtraDB Cluster doesn't scale writes (horizontally), i.e. you can't increase number of writes by adding more nodes. However, 350,000 records per day is approx. 4 record per second, which is no problem at all for XtraDB Cluster (or any other replication topology), at least as long as the nodes are on the same network.
XtraDB Cluster only supports the XtraDB / InnoDB table storage engine, so TokuDB isn't an option if you want to use that particular cluster technology.
4 nodes isn't a good number. You should be using an odd number (3, 5, ...) of nodes, or an even number of nodes + one node with garbd, the Galera arbitrator.

XtraDB (which is InnoDB with a few patches from Percona) is a tried and tested technology, and XtraDB / InnoDB is the default storage engine in MySQL, Percona and MariaDB. InnoDB is being developed and supported by MySQL (owned by Oracle).

TokuDB is newer, it doesn't have as many years of usage and development. If I understand correctly, TokuDB is developed by Tokutek, now owned by Percona.

With these considerations, in a choice between these two technologies for a system for financial transaction data, I would probably go with XtraDB.


Hard and Fast rule for include columns in index

That index suggestion by marc_s is wrong. I've added a comment. (And it was my answer accepted too!)

The index for this query would be

CREATE NONCLUSTERED INDEX NC_EmpDep
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

An index is typically

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

Where:

KeyColList = Key columns = used for row restriction and processing
WHERE, JOIN, ORDER BY, GROUP BY etc
NonKeyColList = Non-key columns = used in SELECT and aggregation (e.g. SUM(col)) after selection/restriction


You have a problem whenever you join using ASOFDATE , such as here:

on a.CUR_DATE = b.ASOFDATE

, because ASOFDATE is defined as dateadd( day, 1-day(AsOfDate), AsOfDate). To me this seems to mean “first of the month”, and hopefully there’s no time component involved.

So, I’d add a new column to the first CTE, called maybe AsOfDateOrig.

COLLATERAL_VALUES as (
select
    COLLATERAL_TYPE,
    dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
    Value as INDEX
    ,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),

And then involve that in your join clause too.

on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig >= a.CUR_DATE and b.AsOfDateOrig < dateadd(month,1,a.CUR_DATE)

It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.

Now do the same for everywhere else you join using that.

I’d consider doing the same for PRIOR_DATE too, basically avoiding joins that involve an expression unless you have the inverse of it too.

Finally, look at your indexing strategy.

You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value), for example, and similar indexes on the other tables.


Background Info

Transactions are recorded in the log/archive log files.

From there, you can find such things as

what values were changed
time of change within +/- 3 seconds
who changed it under what context (values in SYS_CONTEXT) (*)

TIME

The "time of transaction" comes from the SCN.  As such, the time resolution is "+/- 3 seconds".

If you need a higher resolution, then the Business needs to accept some schema modifications.

example:

alter table T add (
    time_of_insert timestamp default systimestamp -- possibly INVISIBLE
);

VERSIONS BETWEEN Queries

A VERSIONS BETWEEN query adds the following pseudo columns

VERSIONS_STARTSCN
VERSIONS_STARTTIME
VERSIONS_ENDSCN
VERSIONS_ENDTIME
VERSIONS_XID
VERSIONS_OPERATION - (I)nsert, (U)pdate, (D)elete

"how far back you can go" is dependent on the UNDO tablespace.  Which is, usually, 1 hour. YMMV.

To extended the range, use Flashback Data Archive.

LogMiner

Prior to 11g, the only way to access "when a transaction occurs" is to use LogMiner.

This can be done through the DBMS_LOGMNR Package.

Flashback Data Archive

11g introduced a feature called Flashback Data Archive.

This feature

Extends the range of AS OF and VERSIONS BETWEEN queries.
runs in the background.
Reads the log/archive log files (I believe it uses LogMiner also)
records the values in a separate table
"Old values" are automatically drop

"Old Values" is determined by your Flashback Archive destination.

This feature is included with all editions of Oracle starting with 11.2.0.4. Prior to 11.2.0.4, you have to have a specific EE add-on ($$$$).

(*) With 12c, Flashback Data Archive can optionally record the values of SYS_CONTEXT also.

Other Links

Here are some additional URLs regarding Flashback Data Archive

http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html
https://youtu.be/2RXUUrxcKrw?list=PLlo3zrPbMP9_HDjsq9lcdi8RyiIvqfmBy


Your join clause is the issue here.

The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa.

a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb).  Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb.

As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table.

Something like this should work:

SELECT COALESCE(a.x,b.x) AS x
,a.y
,b.y
FROM aaa a
FULL OUTER JOIN bbb b
    ON a.x=b.x


What exactly are secondary members acknowledging in replicasets with MongoDB?

Ok, while the question(s) is(are) rather complex, the answer(s) is(are) pretty simple, albeit a bit lengthy.

The replication (simplified)

When an operation is saved in the oplog, it does not get distributed to the other members. It gets pulled by the replica set members.

A failed member rejoining the replica set will contact the primary and pull the oplog entries it hasn't processed. So it will eventually become consistent.

However, there is a caveat. For performance reasons, the oplog is a capped collection. This means there is limited space available for it. With limited space, you have a limited number of operations which can be written to the oplog. And limited operations reach back only a limited amount of time, since the oldest entries of the oplog will be overwritten when a new entry would exceed the oplogs size limit. The limited time frame is called the replication oplog window.

If member rejoins the cluster and its last operation is older than the oldest entry in the primaries oplog, it becomes stale and needs to have an complete resync of the data.

So in your example, after 3,S went down, 2,S still would happily pull the oplog.
In your example, all three are up for a short amount of time, until 2,S failed. The election now would take place, and since there is no need to change the primary 1,P would still serve in this function.

Now, when 3,S came up within the oplog window, it would simply connect to the primary, pull the oplog, apply the oplog entries to its data set and all lived happily ever after (except for the admin who has to get his back moving to get up 2,S again for double redundancy).

In case 3,S comes up outside the oplog window, you'd have to initialize a full resync by

Shutting down the instance of mongod
Wipe its dbpath
Restart the instance

Note that if 2,S isn't up and resynced during that procedure, the cluster wouldn't have a majority up and running and hence be unwritable.

Acknowledgement of operations

With a write concern of {w:1}, the primary would acknowledge the query to be

Received and
syntactically correct since it is
Applied to the in memory data set of both the data and the oplog

With a write concern of {w:1,j:1}, all of the above applies. Additionally, modifications are written to the journal, making the data guaranteed to be durable - except for the edge case of the journal being damaged.

Keep in mind that operations are written to the journal every commitIntervalMs, 100msecs by default, one third of the configured value with a write concern of {"w":x, "j":1}. We will come back to this later.

With the replication process as detailed above, it is sufficient that the {"w":x, "j":1} for x > 1 works different, since the primary is then guaranteed to have the latest data of all replica set members: With said write concern, the write to the journal is only acknowledged for the primary.

The replica set is either writable from the clients point of view (the majority of non-hidden, non-delayed members is up) or not. If it is writeable, the primary is guaranteed to have the latest data, and secondaries failing back will either automatically resync from the oplog or need to be fully resynced.

Edge case for {w:1}

There is a special condition, however. With a write concern of {w:1} it can happen that a primary about to fail accepted writes which did not make it to the secondaries. When this this machine rejoins the replica set, it will notice that its newest write operation is newer than the oldest of the new primaries oplog it hasn't processed. The instance will then identify all write operations it has accepted but didn't make it to the secondaries, writing them to the rollback folder in dbpath, up to a limit of 300MB.

Dealing with rollbacks can be quite tricky, however, it is easy to prevent: set w > 1.

Rule of thumb: Never set w < 2 unless you can afford to loose data

Conclusion

With a write concern of w<1, you prevent rollbacks and can be pretty sure that a write operation is durable. It only would not be durable if all replica set members the data was supposed to be processed by failed within a time frame of commitIntervalMs, so (for the default values) 100ms in a worst case scenario (all journals are written at the exact same point in time), 50ms in a best case scenario (the journals are shifted by exactly 50ms), giving us an average of 75ms in which all affected replica set members had to fail. If that is too much for you, either add more replica set members, lowering the probability that all servers supposed to process the operations fail, or set j:1. Still, this applies only if w replica set members fail simultaneously. With a geographically distributed replica set, the probability for this scenario equals 0 for all practical purposes. And in case of the Zombie Apocalypse happening simultaneously with an invasion by little green aliens, I assume your concerns (no pun intended) will shift.

If write operations are extremely important to be written to disk when acknowledged, you still can take the sledgehammer of forcing a file sync before you consider a write operation to be successful.


Cast varchar into int, converting any value under 0 to 0

As TINYINT only allows 0-255 you could also use

ISNULL(TRY_CAST(YourCol AS TINYINT),0)

The TRY_CAST above returns NULL if supplied an invalid input for the datatype (including -ve numbers) and I would be minded to leave it that way.

But if you must change these to 0 you can wrap it in an ISNULL


Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

I would go with mirroring with a witness, do you want to do the work of a manual fail-over at 3AM when "socks hit the fan"?
Check out Paul Randal's MCM prep videos, they might help you out in your decision.


How to limit the number of rows in select statement for a condition in sql server 2012

It is difficult to say without your current query and sample data, but looks like this is something you want

select t1.*
from
(
select t.*,
    row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk
from
 (your current query) t
) t1
where t1.rnk=1

Note: If your current query doesn't have any group by then you can plug in row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk in your current query select clause and filter it in outer query as the example above.


What are the best field data types for a table of musical keys

This is a 'primarily opinion-based' question.

I would go for a table with the following fields:

key_id    integer
key       char(1)
tonality  char(1)
name      varchar2(50)

The first field is the primary key. The second and third field make up a unique key. For key there is A to G possible. For tonality I would use - for minor, + for major, and 'space' for 'normal'. For both those fields you can make a check constraint to prevent wrong values to enter.

Since there are not many rows in this table it be read completely into memory so it will not influence response times that much.

If you do not like the 'extra' primary key then you can also use:

key   char(2)
name  varchar2(50)

The key is primary key and holds A-, A, A+, etc.


Object Reference Not Set ... Debugging Stored Procedure in Visual Studio

Do you have DELIMITER statements around the CREATE...?

Does VS have any way to debug Stored Routines?  (I suspect not.)

The failure to do it this way could cause trouble later:

START TRANSACTION;
SELECT ... from users ... FOR UPDATE;
various SETs
UPDATE ...;
COMMIT;

Another consideration...  An "audit trail" may be better achieved via a TRIGGER wherein you can compare NEW. to OLD..


Snapshot_isolation_state_desc on a Read only/Stand by Database

SQL Server always seems to set snapshot isolation (SI) on when a database is made read only. I have no idea why it does this, but it does.

You can try to turn it off, and the ALTER DATABASE statement will succeed, but snapshot isolation remains enabled while the database is read only.

One theory is that setting SI prevents unexpected errors for people running an SI transaction and accessing the read-only database, but it seems like there should be a deeper technical reason.

In any case, the behaviour is "normal", so you should not worry about it.


DB2 importing/replacing fields from a file that contains XML

Why don't you just

update table2 set column_xml2 =
(select column_xml from table1 where <predicate clause>) where <predicate clause>;

?

With CLOBs you pretty much have to do that. You can only update the entire CLOB. Now if are on DB2 9.1 or higher, you could store these in a field of type XML and then you could attempt to update specific XML elements like how you are hoping to do.


Administrator can't view tables ("server refused to connect")

I figured it out, I needed to add two lines in the ssl.conf file:
Header set Access-Control-Allow-Origin "*"
Header set Access-Control-Allow-Methods "GET, POST, PUT, DELETE, OPTIONS"
That solved it (don't forget to restart apache).


Cannot login oracle database using other users

The syntax you gave is using 'os authentication' to connect.  That is, the OS user issuing the command is a member of the os group "DBA".  (Or, on Windblows, "ORA_DBA").  I hope you did not change ownership or permission of any of the files.


How is calculated fill-factor percentage related to size of database?

What you see is correct.

When you rebuild an index first the new index is built and only then the old index is dropped. And if you don't use

SORT_IN_TEMPDB = ON

the space that server needs to make a sort is allocated within your db

Your db has now empty space, it's not space reserved to any object, it's just free space
(space that was freed after temp objects needed to do sort were dropped)

So your indexes are not empty on 42%, it's your db that is empty on 42% and if you do ashrink of data file (I don't recommend it) your db will occupy only 82Gb


Why can't we have multiple cascade paths?

This question "what can go wrong" can only be answered by the developers at Microsoft or Sybase. When there are multiple cascade paths to the same record, it's possible that the code might attempt to delete it multiple times. If it isn't constructed to deal with the possibility that the record to be deleted has already been deleted, it might throw an error. Apparently this problem actually exists (or is thought to exist!), so instead of fixing it, the implementation avoids it by preventing the definition of duplicate cascade paths. It's clearly a short-cut, because other DBMS have no problem with this situation.


Yes, you can use Native Client 10

Simply use Provider=SQLNCLI10 in the connection string


SQL Server job - powershell Where does "log to table" save logs

A simple press on the F1 key for the help would reveal, that the execution result is stored in the sysjobstepslogs table of the msdb database.


Why changing the order of network protocols does not make any sense?

I believe the issue has to do with what client is used when establishing connectivity to Server B from Server A rather than how you order your protocol order on the server itself.

The first hint comes from Choosing a Network Protocol: Enabling the Protocol (highlighting mine):

  The protocol must be enabled on both the client and server to work.
  The server can listen for requests on all enabled protocols at the
  same time. Client computers can pick one, or try the protocols in
  the order listed in SQL Server Configuration Manager.

I suspect most clients determine the desired order of the protocols being used rather than delegate that to the server.

Further digging through MS documentation yields this interesting note in the Client Protocols Properties (Order Tab) article:

  Note

  These settings (e.g. Order) are not used by Microsoft .NET SqlClient. The protocol
  order for .NET SqlClient is first TCP, and then named pipes, which
  cannot be changed.

As we can see for the .NET SqlClient, if TCP/IP is an enabled protocol on the server and the .NET SqlClient is being used, it doesn't matter what you do, you're getting a TCP/IP connection.  So with this client in particular the protocol specified on the server most definitely doesn't matter.

With these examples, I think it's pretty clear that the client you're using to connect to a server has more bearing on the protocol being used rather than the connection protocol ordering on the server itself.

In your specific case, if you really want to use Named Pipes instead of TCP/IP, you may be able to force it via a Client Alias:

You would need to create this alias on Server A for Server B.  Just note that aliases come with their own headaches (e.g. you've now got another thing to manage whenever you deal with connectivity changes on Server B), but as a workaround, it may do the trick.

Also of note, Named Pipes can become problematic on slow connections where the same issues are not as impacting via TCP/IP connections, which is likely why most clients gravitate toward the TCP/IP protocol by default.


Can't alter database roles on a restored database

Despite the fact that you're seeing your login as the owner of the database, I don't believe that information is accurate because of the output you got from select user_name() and select is_member('db_owner'). I think SQL Server is misleading you. The permission to read/write every table on the database could be explained by the public database role.

About not being able to add a new member to the roles:

Since you're not a member of db_owner, you won't be able to add a user on a database role. The doc says that:

  to change the membership in a fixed database role you need:

  Membership in the db_owner fixed database role

The problem taking the ownership:

When you tried to take ownership of the database (which is a way of being added to the db_owner role) you got the error

  Cannot find the principal '[domain login]', because it does not exist
  or you do not have permission.

Because by being member of all server roles except sysadmin, as you described, doesn't grant you the TAKE OWNERSHIP or IMPERSONATE permissions and the ALTER AUTHORIZATION for databases doc says those permissions are needed:

  Requirements for the person executing the ALTER AUTHORIZATION statement:

  If you are not a member of the sysadmin fixed server
  role, you must have at least TAKE OWNERSHIP permission on the
  database, and must have IMPERSONATE permission on the new owner login.

You can check if you have those permissions from sys.fn_my_permissions running the following queries while connected to your restored database:

SELECT * FROM fn_my_permissions (NULL, 'DATABASE')
WHERE permission_name = 'TAKE OWNERSHIP';

SELECT * FROM fn_my_permissions (NULL, 'SERVER')
WHERE permission_name = 'IMPERSONATE ANY LOGIN';

If no rows are returned, it means you won't be able to alter the owner of the database and to change that you probably need a sysadmin to execute the ALTER AUTHORIZATION  or grant you enough privileges to do so.

Granting the needed privileges:

GRANT TAKE OWNERSHIP ON DATABASE::YourDatabase TO [YourDomain\YourGroupOrLogin];
GRANT IMPERSONATE ANY LOGIN TO [YourDomain\YourGroupOrLogin];

Note: The login that is going to be the owner of the database cannot be an existent user on that database, or you'll get the following error:

  Msg 15110, Level 16, State 1, Line 1   The proposed new database owner
  is already a user or aliased in the database.

Further reading: the
Chart of SQL Server Permissions contains all the permissions you get from being a member of fixed roles (server and database level).