Main reason why I'd choose NULL is a bit simpler queries - you don't need to care about self-relationship when you are querying ,for instance, number of referred users by the given. You can also save some space in case you create an index on client_id (it usually makes sense) because some RDMS, for example Oracle, don't include NULLs into index, others, like SQL server, allow filtered indexes (WHERE client_id IS NOT NULL).


Migrate all databases from one SQL Server 2005 to another

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016

Migration always have a downtime. So depending on how much your downtime  is, you can go with setting up either :

Logshipping ( I have used this method and it reduces the downtime considerably)

a. Set up logshipping from the source to destination server as usual. Keep the sync time 1 min (which is minimum).

b. On the day of migration, make sure the logbackups are in sync.

c. Stop all the logshipping jobs and then take the final tail log-backup and put the primary databases in no-recovery mode (this is just in case you need to go back to primary).

d. restore the tail log backups on the secondary with recovery bringing all the secondary databases online.
Database Mirroring : I do not recommend mirroring for migrating 200 databases as you will easily exhaust the worker threads as described in this KB article.. If there were less databases, then this would be a viable option.

If you have enough downtime then I would recommend

backup / restore method (as opposed to detach / attach) as if something goes wrong, you atleast have a good backup to restore back. this script will get you started with moving all the logins and databases. Just modify it to use backup / restore (or take full backups before running the script - as it uses detach/attach method).

Things you can do prior to migration :

Refer to this answer for migration steps. Some may apply to your scenario as well.
script out all the jobs / ssis packages, etc --OR --  As suggested by @shanky, you can restore the system databases from the old server to new server before hand - that will give you all the logins, jobs, etc on the new server.
turn on Instant File Initialization on the new server.
Have multiple tempdb data files with equal size.
Enable Trace Flag 1118 -  to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.
Configure max and min memory correctly. Especially Max memory away from default.
Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
Best is to install sp_Blitz from Brent Ozar.  Run it and address the critical and high priority issues reported by it.
You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.

Edit:

You can use powershell to migrate your all databases from one server to another as described at Use PowerShell to Migrate Entire SQL Server Instance (db, logins, jobs, etc)

  Migrates databases with simple or complex file structures via backup & restore or detach & attach. Also migrates users with passwords, SIDs, server & db permission sets, server & db roles, server configuration settings, SQL Agent objects, user objects in systems databases.


No results from traceid from sys.fn_trace_getinfo(0);

It looks like you have no traces running on your server.

The function mentioned in your post title is:

SELECT * FROM sys.fn_trace_getinfo(0)

Using either a 0 or a NULL as the parameter will produce a listing of all running traces.  That list could look like this:

traceid property    value
1   1   2
1   2   D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_6215.trc
1   3   20
1   4   NULL
1   5   1
2   1   1
2   2   NULL
2   3   NULL
2   4   NULL
2   5   1

However, if you use another traceid it will only show the description rows for that trace. (Or no values if the traceid is invalid.)

The other function that you show was:

SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);

Using a traceid, this will produce a list of event ids for the traceid.  An invalid traceid will return no rows.

Since you are not getting any return on your queries, then there should be no traces running.


Your trigger is designed to only fire after an insert not an update. Try changing the code to:-

create trigger flightinput2 on reservation AFTER INSERT, UPDATE as
begin
   update reservation set Departure_Airport='LDN',Arrival_Airport='KUL' where Flight_Code='EM0088'
end

EDIT:- As pointed out, the code above will update all records within the database that have a flight code of EM0088

To prevent this you could update the code to:-

 create trigger flightinput2 on reservation
 after insert, update
 as
 begin
      update reservation
        set Departure_Airport='LDN',
            Arrival_Airport='KUL'
        from inserted ins
  inner join reservation r on ins.PrimaryKeyColumn = r.PrimaryKeyColumn
       where r.Flight_Code='EM0088'
  end

Just change PrimaryKeyColumn to the PK of your table.

EDIT2 - If you want to change values in the table based on flight codes you could do this:-

  create trigger flightinput1 on reservation
  after update
  as
  begin

  declare @code char(10);

  select @code =
    ins.flight_code
  from inserted ins

  if @code = 'MH0007'
    update reservation
    set Departure_Airport='XXXX',
    Arrival_Airport='KUL'
    from reservation r
    inner join inserted ins1 on ins1.resid = r.resid

  else if @code = 'EM0088'
    update reservation
    set Departure_Airport='LDNXX',
    Arrival_Airport='KUL'
    from reservation r
    inner join inserted ins1 on ins1.resid = r.resid

  end


While PowerShell is high on my list of things to learn I don't know much about it right now and I needed a csv quick, so I wrote a simple query in T-SQL to get the data I needed out of the XML in a tabular format and then just copied that into excel and saved it.

-- get XML from file
DECLARE @xml XML;
SELECT
    @xml = CAST(A.[BulkColumn] AS XML)
FROM OPENROWSET(BULK 'C:\bpa_results.xml', SINGLE_BLOB) A;

-- query XML data
WITH XMLNAMESPACES ('http://schemas.microsoft.com/powershell/2004/04' as x)
SELECT
    S.*
FROM @xml.nodes('x:Objs/x:Obj') A ([obj]) -- "root" objects
CROSS APPLY A.[obj].nodes('x:LST/x:Obj') B ([obj]) -- findings objects
CROSS APPLY
    (
        SELECT
            A.[obj].value('@RefId', 'NVARCHAR(500)') [root_obj_RefId],
            B.[obj].value('@RefId', 'NVARCHAR(500)') [findings_obj_RefId],
            B.[obj].value('x:Props[1]/x:S[@N="ResultNumber"][1]', 'NVARCHAR(500)') [ResultNumber],
            B.[obj].value('x:Props[1]/x:S[@N="ResultId"][1]', 'NVARCHAR(500)') [ResultId],
            B.[obj].value('x:Props[1]/x:S[@N="ModelId"][1]', 'NVARCHAR(500)') [ModelId],
            B.[obj].value('x:Props[1]/x:S[@N="SubModelId"][1]', 'NVARCHAR(500)') [SubModelId],
            B.[obj].value('x:Props[1]/x:S[@N="RuleId"][1]', 'NVARCHAR(500)') [RuleId],
            B.[obj].value('x:Props[1]/x:S[@N="ComputerName"][1]', 'NVARCHAR(500)') [ComputerName],
            B.[obj].value('x:Props[1]/x:S[@N="Context"][1]', 'NVARCHAR(500)') [Context],
            B.[obj].value('x:Props[1]/x:S[@N="Source"][1]', 'NVARCHAR(500)') [Source],
            B.[obj].value('x:Props[1]/x:S[@N="Severity"][1]', 'NVARCHAR(500)') [Severity],
            B.[obj].value('x:Props[1]/x:S[@N="NeutralSeverity"][1]', 'NVARCHAR(500)') [NeutralSeverity],
            B.[obj].value('x:Props[1]/x:S[@N="Category"][1]', 'NVARCHAR(500)') [Category],
            B.[obj].value('x:Props[1]/x:S[@N="Title"][1]', 'NVARCHAR(500)') [Title],
            B.[obj].value('x:Props[1]/x:S[@N="Problem"][1]', 'NVARCHAR(500)') [Problem],
            B.[obj].value('x:Props[1]/x:S[@N="Impact"][1]', 'NVARCHAR(500)') [Impact],
            B.[obj].value('x:Props[1]/x:S[@N="Resolution"][1]', 'NVARCHAR(500)') [Resolution],
            B.[obj].value('x:Props[1]/x:S[@N="Compliance"][1]', 'NVARCHAR(500)') [Compliance],
            B.[obj].value('x:Props[1]/x:S[@N="Help"][1]', 'NVARCHAR(500)') [Help],
            B.[obj].value('x:Props[1]/x:S[@N="Excluded"][1]', 'NVARCHAR(500)') [Excluded]
    ) S
WHERE   S.[ResultNumber] IS NOT NULL
    AND S.[Severity] != 'Information';


How to implement constraints that go beyond a single row in PostgreSQL?

For your first example, let's assume your tables are defined in the following way:

CREATE TABLE person
(
    person_id integer PRIMARY KEY,
    person_name text NOT NULL
) ;

CREATE TABLE food
(
    food_id integer PRIMARY KEY,
    food_name text NOT NULL,
    food_type text NOT NULL  /* Unnormalized, for simplicity */
) ;

CREATE TABLE purchase
(
    purchased_at timestamp not null default now(),
    person_id integer NOT NULL REFERENCES person(person_id),
    food_id integer NOT NULL REFERENCES food(food_id),
    PRIMARY KEY (person_id, food_id, purchased_at)
) ;
CREATE INDEX idx_purchaser_food_id
    ON purchase(food_id, person_id) ;

I would create a function that checks for your constraint. It's a simple SQL statement, with one corner case (the first purchase), and a "literal" translation of your requirement: the current food type is in (the set of already existing ones):

CREATE FUNCTION
    allowed_food_type_for_person(in _person_id integer, in _food_id integer)
RETURNS
    boolean AS
$body$
    SELECT
        -- Corner case for first purchase
        (NOT EXISTS
             (SELECT
                  *
              FROM
                  purchase
              WHERE
                  person_id = _person_id
             )
        )
        OR
        (
           -- Current food types
          (SELECT
              food_type
          FROM
              food
          WHERE
              food_id = _food_id)
          IN
          -- Existing food types
          (SELECT
              food_type
          FROM
              purchase
              JOIN food USING(food_id)
          WHERE
              person_id = _person_id)
          )
$body$
LANGUAGE SQL STABLE ;

Now you can add the constraint you wish:

ALTER TABLE purchase
    ADD CONSTRAINT check_purchase_food_type_is_limited
        CHECK(allowed_food_type_for_person(person_id, food_id));

Now, let's imagine this is your data:

INSERT INTO person
   (person_id, person_name)
VALUES
   (1, 'Alice Cooper'),
   (2, 'Bob Geldorf') ;

INSERT INTO food
   (food_id, food_name, food_type)
VALUES
   (1, 'Banana', 'Fruit'),
   (2, 'Orange', 'Fruit'),
   (3, 'Pear', 'Fruit'),
   (4, 'Lettuce', 'Vegetable'),
   (5, 'Coliflower', 'Vegetable'),
   (6, 'Cellery', 'Vegetable'),
   (7, 'Asparagus', 'Vegetable') ;

You can have 'Alice' do a first purchase:

INSERT INTO
    purchase
    (person_id, food_id)
VALUES
    (1, 1) ;    -- Alice purchased a Banana (fruit)

Now, she buys an orange, OK:

INSERT INTO
    purchase
    (person_id, food_id)
VALUES
    (1, 2) ;    -- Alice purchased an Orange (fruit), Ok

But if she tries to buy 'Lettuce':

INSERT INTO
    purchase
    (person_id, food_id)
VALUES
    (1, 4) ;    -- Alice tries to purchase a Lettuce => fail

ERROR:  new row for relation "purchase" violates check constraint "check_purchase_food_type_is_limited"
DETAIL:  Failing row contains (2017-07-11 19:46:08.830481, 1, 4).

You can play with it at dbfiddle here

For your 2nd example, you'd do something equivalent, but more sophisticated. In any case, there are other ways to solve the "diamond problem" you seem to have, by defining the tables in a different way. Check Data inconsistency prohibition if a table refers to another via two many-to-many relationships, for a very good explanation.

Caveat: As per comment from @DanielVerité: this can have concurrency problems that actual constraints don't have. They can (most probably) be solved by using TRANSACTION ISOLATION LEVEL SERIALIZABLE; incurring the heavy penalty it implies.

Side question: Why are you against people having a diverse diet? ;-)


Is there a slave configuration that stops replication of mysql.mysql and grants?

Use binlog-ignore-db instead. This post explains the behaviour and answers your question. If not, comment.


Different table size on sp_spaceused and standard report "Disk Usage by Top Tables"

Frankly, I wouldn't use either. You can find your biggest tables immediately - with more flexibility - and not worry about where @updateusage has been set.

CREATE PROCEDURE dbo.TopTables
  @NumberOfObjects INT = 100,
  @MinimumSizeInMB INT = 10
AS
BEGIN
  SET NOCOUNT ON;

  SELECT TOP (@NumberOfObjects)
    [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
    index_count = COUNT(i.index_id),
    size_in_MB = SUM(p.reserved_page_count)*8/1024.0
  FROM sys.schemas AS s
  INNER JOIN sys.objects AS t
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.indexes AS i
  ON t.[object_id] = i.[object_id]
  INNER JOIN sys.dm_db_partition_stats AS p
  ON t.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE t.is_ms_shipped = 0
  GROUP BY s.name, t.name
  HAVING SUM(p.reserved_page_count)*8/1024.0 >= @MinimumSizeInMB
  ORDER BY size_in_MB DESC;
END
GO


Is smallest to largest the rule of thumb in a multi-table JOIN?

SQL is declarative language, query is not executed the way you write it . Most modern RDMS have decent optimizer which is capable of choosing correct join order when relatively small number of tables is involved , statistics is up to date,  data not skewed, appropriate indexes exist, etc.

Query optimization is a very broad topic, and it's quite specific to particular vendor. It may involve adding hints, creating materialized views, using fixed execution plans, caching tables in memory, and many other things. To some extent you may control join order with the way you write query (for instance,  subquery inside WITH  will be more likely executed first compared to the same subquery inside inline view, but again, it's very specific to RDMS you're using.

As I rule of thumb, I'd recommend starting with a query that gives you expected results, and try optimizing it only if it's slow - the optimizer will do the rest.

Side note: COUNT can appear in HAVING clause, not in WHERE ; the query will fail on the most RDMS.


Giving server-role to login breaks stored procedures

Grant CONTROL SERVER to the login rather than adding it to the sysadmin role.

As documented in Books Online:

  The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

CONTROL SERVER gives the same rights as sysadmin but without this side-effect.

One caveat: you would need to check for any code that depends on the user explicitly being a member of the sysadmin role (for example, code using IS_SRVROLEMEMBER).


SQL SP: How to return one inactive record along with active records

It looks to me like a simple WHERE IsActive = 1 OR specialtyId = @specialtyId should work.


What is the disadvantage of maintaining both row and column table storage?

Maintaining two representations of data is obviously more complex than maintaining only one, and added complexity always has a cost. That cost might only be in code complexity and not measurable in terms of raw throughput, but even that needs to be carefully evaluated (more complexity implies higher risk of bugs, and higher cost of maintenance).

That's a fundamental trade-off, like everything else - are the benefits of these new features good enough to warrant the added costs (complexity, CPU, or anything else)? Apparently they are.

Regarding inserts, remember that inserts (and updates) generally have two sides: a read side and a write site. The read side (if only, e.g., checking a primary key for duplicates) can potentially be accelerated by the new in-memory structures. Same for join processing that can happen in complex inserts/updates/merges.

The write side itself can't be faster* (since it does what it used to do without the in-memory option, and then some to maintain consistency of the in-memory store), but if you make the read side an order of magnitude faster, the overall processing time can be reduced. By how much is an open question, and will depend on a whole lot of factors.

Also note that if you end up removing indexes, this will have an impact on insert performance for the affected tables. This would be an "indirect" benefit - not a speedup thanks to the in-memory option per se, but because that option allowed you to reduce the number of indexes.

*Never say never. There may be corner cases.


So you attempted to restore the database in production but it didn’t finish? Can you elaborate more, did it finish completely, we’re there any error messages, etc.

Then you were able to successfully restore the database to a test server?

Have you verified if the backup file is good? See this article if not

https://www.mssqltips.com/sqlservertutorial/113/checking-to-make-sure-a-sql-server-backup-is-useable/

You may want to check that and if ok try to restore in production again.


How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

PG_TABLE_DEF

Amazon considers the internal functions that INFORMATION_SCHEMA.COLUMNS is using Leader-Node Only functions. Rather than being sensible and redefining the standardized INFORMATION_SCHEMA.COLUMNS, Amazon sought to define their own proprietary version. For that they made available another function PG_TABLE_DEF which seems to address the same need. Pay attention to the note in the center about adding the schema to search_path.

  Stores information about table columns.

  PG_TABLE_DEF only returns information about tables that are visible to the user. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schemas.

  You can use SVV_TABLE_INFO to view more comprehensive information about a table, including data distribution skew, key distribution skew, table size, and statistics.

So using your example code (rewritten to use NOT EXISTS for clarity),

SET SEARCH_PATH to '$user', 'public', 'target_schema';

SELECT "column"
FROM dev.fields f
WHERE NOT EXISTS (
  SELECT 1
  FROM PG_TABLE_DEF pgtd
  WHERE pgtd.column = f.field
  AND schemaname = 'target_schema'
);

See also,

Official docs on Querying Redshift System Tables


delete hung soon after vacuum verbose analyse

I'm not able to test this since I don't use postgresql 8.4 but i think you have to:

check which transaction(check Sql statement) is locked and which is locking.

to check it

SELECT
    kl.pid as blocking_pid,
    ka.usename as blocking_user,
    ka.current_query as blocking_query,
    bl.pid as blocked_pid,
    a.usename as blocked_user,
    a.current_query as blocked_query,
    to_char(age(now(), a.query_start),'HH24h:MIm:SSs') as age
FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a
        ON bl.pid = a.procpid
    JOIN pg_catalog.pg_locks kl
        ON bl.locktype = kl.locktype
        and bl.database is not distinct from kl.database
        and bl.relation is not distinct from kl.relation
        and bl.page is not distinct from kl.page
        and bl.tuple is not distinct from kl.tuple
        and bl.virtualxid is not distinct from kl.virtualxid
        and bl.transactionid is not distinct from kl.transactionid
        and bl.classid is not distinct from kl.classid
        and bl.objid is not distinct from kl.objid
        and bl.objsubid is not distinct from kl.objsubid
        and bl.pid  kl.pid
    JOIN pg_catalog.pg_stat_activity ka
        ON kl.pid = ka.procpid
WHERE kl.granted and not bl.granted
ORDER BY a.query_start;

Note:this is for postgresql v9.1 i am not sure this work for 8.4 or not.

You mentioned about dead lock so it mean your transaction are waiting each other until occur deadlock(no transaction completed(commit or rollback) so make sure the locking transaction was completed(process 12729) and then transaction (Process 12800) will not wait anymore.
Vacuum is occur table lock level too. when u run vacuum command it will locked the target tables to reclaims storage occupied by dead tuples so that why when you check the df - your free disk space is higher than before.
about Privileges

WARNING:  skipping "pg_authid" --- only superuser can vacuum it
WARNING:  skipping "pg_database" --- only superuser can vacuum it
WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
WARNING:  skipping "pg_pltemplate" --- only superuser can vacuum it
WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it

you need to use supper user(postgres) to make vacuum verbose analyse for above tables (pg_*) or you just specified the target table only, it no need to use supper user privileges.

ex:

vacuum verbose analyse [table_name];


join two tables and limit results by an ordered list

you need to join table with priority and use ROW_NUMBER function to sort rows for each property, then you just need to leave rows with row number = 1:
WITH CTE AS (
SELECT t1.property
,t1.value
,ROW_NUMBER() OVER (PARTITION BY t1.property ORDER BY t2.priority) AS rn
FROM i18n t1
JOIN i18n_cfg t2 ON t1.locale = t2.locale
WHERE t1.app = 'test'
AND t2.user = 'lm'
)
SELECT property, value
FROM CTE
WHERE rn = 1

solution without window functions and variables:
SELECT t1.property
,(SELECT t2.value
  FROM i18n AS t2
  JOIN i18n_cfg AS t3 ON t2.locale = t3.locale
  WHERE t2.property = t1.property
  AND t2.app = 'test'
  AND t3.user = 'lm'
  ORDER BY t3.priority
  LIMIT 1) AS value
FROM
(
SELECT DISTINCT property
FROM i18n
WHERE app = 'test'
) AS t1

link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cf1d3f4757c3d84d3f5ad2bea0966a3c


You're thinking of Materialized Query Tables or MQTs.  They are still part of the product, see this documentation.


Design ER with complicated relationships between different tables

Your idea of having iterations as a join table for trays and projects seems good.

samples are obviously strongly related to iterations, because one sample belongs an iteration, and an iteration contains several samples. Therefore I would make an iterations 1..n samples association.

Then, compartments belongs to a tray. But we can also see compartments as generic identifiers which, when associated with a tray, defines a specific "location" for a sample. We already know to which tray samples of an iteration are associated. Also, a compartment can contain several samples, and a sample can be spread into several compartments. So we need a join table between samples and compartments.
If we keep compartments as generic identifier (see them as slot numbers in a tray), we don't need relationship between trays and compartments. When filling a sample, we already know to which tray it belongs through the iteration. The user will just have to fill the compartment(s) where the sample is located.

You can also enforce the compartment choices for the user by setting a type to the tray, which let you know what compartments can be choosen.


MySQL select returns no rows while there are many

I tried manually adding a test user and the TABLE_ROWS was updated "4270" but when I select I get only the test user I added. Anyway, I learned that TABLE_ROWS is only a rough estimate and I guess it is not updated regularly enough.


DB_ID context from farther up call stack

You cannot accomplish this with functions in a utility database. You can however create utility procedures in the master database, mark them as system objects, and call them from the context of any database on your system. An article with a good example can be found at this location.


Why is this blob saving when used as a concatenated string but not as a prepared statement

The bind_param page mentions (emphasis mine) for the types parameter that

  corresponding variable is a blob and will be sent in packets

which is done in this manner:

$null = NULL;
$stmt->bind_param('sbiiissi', $input[1], $null, $input[3],
         $input[4], $input[5], $input[6],
         $DateAdded, $_SESSION[$thisTracker]);
$stmt->send_long_data(1, $input[2]);

If your BLOB data exceeds the max_allowed_packet size you will need to call send_long_data multiple times, sending a smaller fragment of it each time.

More info in this Oracle blog.


How to perform multiple inserts into postgresql database in one query

It seems your tables are defined with a serial or identity column and you want to use the generated value from one insert as a foreign key in the next statement. This can indeed be done using a data modifying CTE combined with the returning clause:

with new_recipient as (
  insert into recipient
    (name, address)
  values
    (name, address)
  returning recipient_id
), new_order as (
  insert into orders
     (recipient_id, total_price, total_quantity)
  values
     ((select * from new_recipient), 2000, 20)
  returning order_id
)
insert into items
  (order_id, item, price, quantity, total)
values
  ((select * from new_order), 'item1', 230, 2, 260),
  ((select * from new_order) , 'item2', 500, 2, 1000);

However this could also be done using three different insert statements:

into recipient
  (name, address)
values
  (name, address);

insert into orders
   (recipient_id, total_price, total_quantity)
values
   (lastval(), 2000, 20);

insert into items
  (order_id, item, price, quantity, total)
values
  (lastval(), 'item1', 230, 2, 260),
  (lastval(), 'item2', 500, 2, 1000);

The function lastval() returns the most recently generated sequence number. So in the second insert that would be the recipient_idand in the third statement it would be theorder_id`.


mongodb removeShard issue with config.system.session

If you login directly to that shard (and not mongos), you can drop that collection. After you have logged in to that shard, you can check with db.collection.find() command that there is no data what you want to save, before you drop it.


SQL Server Replication changing a tables ID numbers

You need to turn no 'NOT FOR REPLICATION' on the field, then the target will take the identity field from the source table rather than trying to generate its own

this can be done by:

alter table dbo.testtable
alter column [id] add NOT FOR REPLICATION

NOTE, I'm not sure what doing this while replication is enabled will do, if you do enable it while replication is on, ensure your replicate schema changes setting is on for that publication


MySQL command denied for DELETE with JOIN

I've finally tracked down the cause of the permissions issue I'm running into. It turns out that the problem was actually a bug in MySQL.

We're running our infrastructure on AWS Aurora which runs MySQL version 5.10. To set up for providing more information, I tried to recreate the problem locally in a docker container, using the latest version of MySQL (5.7.16). To my surprise, I couldn't reproduce this issue locally, which led me to believe the problem has to be with the difference in MySQL version.

Going through the MySQL version logs, I noticed that in MySQL version 5.6.27, a bug was fixed:

  DELETE could check privileges for the wrong database when table
  aliases were used. (Bug #20777016)

This sounded promising so I modified the query to not use aliases and sure enough, the permissions issues went away.


If the reporter had any thoughts of rigor I would expect the hot cache / cold cache information to be included in the report. Otherwise it is like saying a car costs $y without saying which option, warranty, service or taxes that includes. Similarly I would expect to see it stated that subsequent tests were performed under the same conditions and how that was achieved. In less formal discussion I would assume hot cache, as that is the usual operating condition, unless told otherwise.

The two conditions emphasise different aspects of the host configuration. For a cold cache one sees more of an impact from the disk subsystem. For hot cache it is more about memory pressure and efficiency of the query plan. It would be unusual to find a workload that was purely one or the other in a real-world scenario, however.

SQL Server, for one, assumes a cold cache when costing a query plan.


How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set.  Both your original procedure and your new procedure could then use the function.  The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY.  In this way the core query code would not be duplicated.  Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/

create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/

SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;


What happens if the mysql database's innodb log files are lost?

First things first: kudos for not actually deleting the files -- moving files out of the way is always a better idea than just deleting them -- but don't try to put the old log files back.

It's possible that you might be able to analyze them but you can't put them back into service on this server, because the log sequence number (LSN) in the log files won't match the one stored in ibdata.

InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?

It would most likely then begin crash recovery and could potentially start "repairing" things that were not broken... so, I definitely would not try this.

  The question is, how much data was lost?

If any data was lost, it would have been recent changes (inserts/updates/deletes); however, quite contrary to what I've always thought, it may actually be safe to do what you did with innodb_fast_shutdown set to 1 (but not 2).

http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html

Those instructions imply that as long as it's not set to '2' you'd be fine, and if it were set to '2,' they say to set it to '1' -- as opposed to '0' as I would have expected... so it looks like either you're good to go, or there's a fairly serious flaw in the documentation.


Seems like it was really

cp -Rfv /var/lib/mysql /var/tmpfs/

Working great now.


You need to remove the comma from this part of your code:

CASE
  WHEN state IS NULL, THEN 'N/A'
  ELSE state
END

The correct version would be:

CASE
  WHEN state IS NULL THEN 'N/A'
  ELSE state
END

You can also use the coalesce function:

COALESCE(<VALUE>,<OUTPUT IF NULL>)

The COALESCE function translates well this code:

CASE WHEN <value> IS NOT NULL
     THEN <value>
     ELSE <value_2>
     END

Coalesce returns the first non null value in the list.

Using COALESCE, your query will appear like this:

SELECT
    customerNumber,
    customerName,
    COALESCE( state, 'N/A') as state,
    country
FROM
    customers;


Get distinct results based on column and satisfy condition

If you want the distinct keywords then you need to count the keyword_id, not the position column.

So your expression:

COUNT(CASE WHEN position BETWEEN 4 AND 10 THEN position ELSE NULL END )

becomes:

COUNT(distinct CASE WHEN position BETWEEN 4 AND 10 THEN keyword_id ELSE NULL END )

So the final statement is:

SELECT
  pr.project_id,
  COUNT(DISTINCT pr.keyword_id) as total_keywords,
  count(distinct CASE WHEN position BETWEEN 4 AND 10 THEN keyword_id ELSE NULL END ) AS pos4
FROM
  project_reports pr
GROUP BY pr.project_id;

SQLFiddle example: http://sqlfiddle.com/#!15/b1015/1


Linking SQL Server tables to a PostgreSQL Server

Use:

A foreign data wrapper like odbc_fdw or tds_fdw (see foreign data wrappers list, foreign data wrapper documentation); or
dbi-link - see github repo


MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM?

According to the MySQL Documentation, the InnoDB Buffer Pool is set to 128MB by default in MySQL 5.5.

You can show how much of the InnoDB Buffer Pool is in use and reserved as follows:

SELECT
    BufferPoolUsed BytesUsed,
    (BufferPoolUsed / power(1024,1)) UsedKB,
    (BufferPoolUsed / power(1024,2)) UsedMB,
    (BufferPoolUsed / power(1024,3)) UsedGB,
    BufferPoolReserved BytesReserved,
    (BufferPoolReserved / power(1024,1)) ReservedKB,
    (BufferPoolReserved / power(1024,2)) ReservedMB,
    (BufferPoolReserved / power(1024,3)) ReservedGB
FROM
    (
        SELECT
            (A.num * B.num) BufferPoolUsed,
            (A.num * C.num) BufferPoolReserved
        FROM
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_page_size') A,
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_buffer_pool_pages_data') B,
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_buffer_pool_pages_total') C
    ) AA
;

I have it set to 256M in MySQL 5.5.12 for Windows. Here is my output:

mysql> SELECT
    ->     BufferPoolUsed BytesUsed,
    ->     (BufferPoolUsed / power(1024,1)) UsedKB,
    ->     (BufferPoolUsed / power(1024,2)) UsedMB,
    ->     (BufferPoolUsed / power(1024,3)) UsedGB,
    ->     BufferPoolReserved BytesReserved,
    ->     (BufferPoolReserved / power(1024,1)) ReservedKB,
    ->     (BufferPoolReserved / power(1024,2)) ReservedMB,
    ->     (BufferPoolReserved / power(1024,3)) ReservedGB
    -> FROM
    ->     (
    ->         SELECT
    ->             (A.num * B.num) BufferPoolUsed,
    ->             (A.num * C.num) BufferPoolReserved
    ->         FROM
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_page_size') A,
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_buffer_pool_pages_data') B,
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_buffer_pool_pages_total') C
    ->     ) AA
    -> ;
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
| BytesUsed | UsedKB | UsedMB   | UsedGB             | BytesReserved | ReservedKB | ReservedMB | ReservedGB |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
|   6864896 |   6704 | 6.546875 | 0.0063934326171875 |     268435456 |     262144 |        256 |       0.25 |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
1 row in set (0.00 sec)

mysql>

You can set this value when MySQL starts up. For example:

To set it to use 1G of RAM

[mysqld]
innodb_buffer_pool_size=1G

To set it to use 256MB of RAM

[mysqld]
innodb_buffer_pool_size=256M

After changing it in my.cnf, you must restart mysql for it to take effect.


How to find number of products having SALE price 50% of MRP using MYSQL?

In order to (finally) solve your problem, I did the following

CREATE TABLE product
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR (15) NOT NULL,
  price_type VARCHAR (10) NOT NULL,
  price INTEGER NOT NULL
);

Populate the table - I've added extra records, because, as I've found (again), testing with 1 record (or set - i.e. name = 'book') - I added books 1, 2 and 3, because I doubt if you're only selling one book and pens 1 and 2.

INSERT INTO product (name, price_type, price)
VALUES
('book1', 'MRP',  10),
('book1', 'SALE',  5),
('pen1', 'MRP',  10),
('pen1', 'MRP',  5),

('book2', 'MRP',  14),
('book2', 'SALE',  7),
('pen', 'MRP',  16),

('book3', 'MRP',  30),
('book3', 'SALE',  15),
('pen', 'MRP',  8);

The query

SELECT SUM(cnt) AS number_of_50
FROM
(
  SELECT  p1.name, COUNT(p1.name) AS cnt
  FROM product p1
  JOIN product p2
    ON p1.name = p2.name
  WHERE p1.price = 2 * p2.price
  GROUP BY p1.name
) AS tab

Result

number_of_50
5

dbfiddle here!

p.s. Apart from having more than one record, you might consider using a fiddle for your tables and data for any questions in the future? Also, please also always supply your version of MySQL, it can make a real difference to the answer. There are some articles on how to ask questions here on my profile - you might want to take a look? Welcome to the forum!


Your queries are equivalent to the following:

UNLOCK TABLES;
lock tables hive.NEXT_COMPACTION_QUEUE_ID write;
UNLOCK TABLES;
lock tables hive.NEXT_LOCK_ID write;
UNLOCK TABLES;
lock tables hive.NEXT_TXN_ID write;

  LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

  — http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

The correct way to lock multiple tables is in a single statement:

lock tables hive.NEXT_COMPACTION_QUEUE_ID write,
            hive.NEXT_LOCK_ID write,
            hive.NEXT_TXN_ID write;


When you restore (either via SQL or GUI), you have to be sure that you don't leave the database expecting more restores (of differentials or logs).

This is done in SQL using WITH RECOVERY

In the GUI, it is the "Recovery state" panel (point 12):

  Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)


you can create an user and add all privileges trough grant command or
you can just create a role and grant all privileges your role have to the new created role

`GRANT A TO B;`

It will grant all right of role A to B.

If A is a superuser, you also need: ALTER USER B WITH SUPERUSER;

reference link1 and link2


What data type for a column which can have three states: yes, no, unknown

You want boolean.

It can have true (=yes), false (=no) and null (=unknown)

More details in the manual:
https://www.postgresql.org/docs/current/static/datatype-boolean.html

"Avoid nullable columns" is only a recommendation. If you need an "unknown" state, then NULL is exactly what you are looking for. It was specifically created to represent "unknown".

Postgres even support the keyword unknown for boolean expressions: where some_column is unknown is equivalent to where some_column is null if some_column is defined as boolean


SSMS: Limit a login to be able to view and work only with some databases, but not able to make any changes on the server level

To connect to SSMS they need the CONTROL SERVER permission.

No, that is not true at all. I hope you didn't learn that from any documentation, blog post or presentation. If you did, you should tell us where, so we can hunt them down and correct it.

In order to connect, they need to be a member of the public role (granted by default to all logins). To do anything aside from connect, they need to be granted explicit permissions or added to server-level roles. To connect to a database, they need CONNECT. To do anything aside from connect to a database, they need to be granted explicit permissions or added to database roles.

If you don't want them to have the permissions inherited from CONTROL SERVER, then don't grant that permission. Simple.

And don't test what an end user can do at the service level by connecting to the server in your copy of Management Studio using their credentials. The ability to perform certain operations (like restarting the service) has nothing to do with accounts or permissions inside SQL Server - your Windows domain account is checked, regardless of what user is connected in Object Explorer. To verify this, go to someone's workstation who doesn't have the ability - via their Windows account - to restart services at the Windows level, and try again. Or create a guest account on your own machine.


duplicate entry issue with large mysql database table

From here - take a dump of the table, drop it and recreate it. Reload your dump - that solved the issue here. If this doesn't solve the issue, then please post SHOW CREATE TABLE my_table\G and also the contents of my.cnf.


How to efficiently eliminate data from select when rows are different by one column value

I wouldn't use a JOIN. If you would like to look for rows that have a set of columns in common (in your case, all but one), I would just GROUP the rows by the common columns. Then, restrict which groups you want to return (using a HAVING clause):

SELECT
    A, B, C, D, min(E) AS E
FROM
    t
GROUP BY
    A, B, C, D
HAVING
    count(*) = 1 ;

The min(E) part is just a trick to be "SQL compliant". max(E), or first(E) (for the databases that have it) would do the job as well. As you're choosing just groups with a single row, the min value of a column within the group is the single value there is. In mySQL 5.6 and earlier, leaving E as a column would work as well; although this is not SQL standard and doesn't work in 5.7 (thanks to
ypercubeᵀᴹ for pointing the fact that this doesn't work on 5.7).

Results are:

|  A |  B |  C |  D |  E |
|----|----|----|----|----|
| a2 | b2 | c2 | d2 | PA |

You can get everything at this SQL Fiddle

You can also take a different approach. Look for rows that literally comply with what you're looking for: that is, it does NOT exist another row with the same A, B, C and D and different E. In this case, use an EXISTS condition in your WHERE clause:

SELECT
    A, B, C, D, E
FROM
    t AS t0
WHERE
    NOT EXISTS
    (SELECT
         *
    FROM
        t AS t1
    WHERE
             t1.A  = t0.A
         AND t1.B  = t0.B
         AND t1.C  = t0.C
         AND t1.D  = t0.D
         AND t1.E <> t0.E
    ) ;

Older versions of MySQL would probably not handle this query too well, because EXISTS conditions are not well optimized. mySQL 5.7 does a decent job; and so do most other databases (Oracle, MS SQL Server, PostgreSQL, ...) . You do need an index on (A, B, C, D, E) (or, at least, (A, B, C, D) to be really performant.

SQL Fiddle

The two queries will not work the same in the case where you have two exact duplicate rows (which I interpret is a case not allowed by your spec). The first one will return no rows, whereas the second one will return both rows.

MySQL 5.6 Schema Setup:

CREATE TABLE t
(
    `A` varchar(2),
    `B` varchar(2),
    `C` varchar(2),
    `D` varchar(2),
    `E` varchar(2)
) ;

INSERT INTO t
    (`A`, `B`, `C`, `D`, `E`)
VALUES
    ('a1', 'b1', 'c1', 'd1', 'PA'),
    ('a1', 'b1', 'c1', 'd1', 'PR'),
    ('a2', 'b2', 'c2', 'd2', 'PA'),
    ('a3', 'b3', 'c3', 'd3', 'PA'),
    ('a3', 'b3', 'c3', 'd3', 'PR');

For your extended case, the second query is your starting point.

Change your equalities with whichever conditions meet your needs.

For instance, if your D columns is a timestamp, and you want to consider that "two times are equivalent if the difference is less than 2 minutes"... then change

t1.D = t0.D

by

abs( unix_timestamp(t1.D) - unix_timestamp(t0.D) ) < 120 -- seconds

The possibility of nulls (that work as "jokers") should be handled with:

(t1.A = t0.A or t0.A is null or t1.A is null)

If E is nullable, then the comparison is done with

NOT (t1.E <=> t0.E)

which is mySQL equivalent of (t1.E is distinct from t0.E)

This will give you:

 SELECT
     A, B, C, D, E
 FROM
     t AS t0
 WHERE
     NOT EXISTS
     (SELECT
          *
     FROM
         t AS t1
     WHERE
              (t1.A = t0.A or t1.A is NULL or t0.A is NULL)
          AND (t1.B = t0.B or t1.B is NULL or t0.B is NULL)
          AND (t1.C = t0.C or t1.C is NULL or t0.C is NULL)
          AND (abs( unix_timestamp(t1.D) - unix_timestamp(t0.D) ) < 120)
          AND NOT (t1.E <=> t0.E)
     )
     AND t0.E <> 'PR' ;

 A  | B  | C  | D                   | E
 :- | :- | :- | :------------------ | :-
 a2 | b2 | c2 | 2017-05-17 01:34:28 | PA
 a4 | b4 | c4 | 2017-05-12 19:16:15 | PA

dbfiddle here

Don't expect this kind of queries to be efficient in realistic scenarios.


Failure during install of SQL Server 2016 sp2

I've seen this error on other versions of SQL Server on a legacy build.

In my case, it was caused by the owner of a role or schema was not the default:
Something like db_datareader was authorised by a user account (they no longer have sysadmin)

How to recover?

Could not restore master because the master database version was lower than the binaries version
Created a new install with the correct version to get it running, then restored master to that, then copied master mdf back to the original install.

Unfortunately, I can't remember the exact details now.
It's the only time in 20 years of SQL Server I've...

seen a service pack install fail that stops SQL Server running
had to deal with a version mismatch on restore of master


You may use t-sql for restore. Make sure that you create new files instead of replacing your existing database.

Try someting like this:

    USE [master]
    RESTORE DATABASE [new database name] FROM
    DISK = N'path to your full backup' WITH
    MOVE N'logicaldatafilename' TO N'path to your mdf file',
    MOVE N'logicallogfilename' TO N'path to your ldf file',
    NOUNLOAD,  STATS = 5

GO

I hope it helps.
Janos


I went ahead and opened an Oracle SR, and this is what Oracle had to say:

  1) Do the batch commits span multiple connections?

No. Oracle will write data from Buffer to redologs once sufficient redo is generated by the session. Since your session is closed Oracle will write it immediately.

  2) Is there anyway to find out how often the batched commits are
  written to disk and become "durable"?

No. I have checked all oracle Documents and Metalink but that information is not available.  The most advanced documentation about Asynchronous Commits is available from this link.

  3) Are the batched commits written to disk serialized?

From what I understood from the documentation, they are serialized. Since transactions are written to Disk when "sufficient" redo is generated, I expect A will be written before B. Worst case they will be written at the same time. Please note that this not true with Multiple sessions like "A" from Session 1 and "B" from Session 2.


Is it possible to export postgres database without views?

There's no option that excludes all views. But there are workarounds.

If you isolate all your views in a single schema (as in create schema views_only...), you can use

either the -n or --schema=public argument to select only the public schema (might not do what you want),
or the -N or --exclude-schema=views_only.

If you follow a strict(ish) naming convention, you can use

either the -t or --table=something to select only the tables,
or the -T or --exclude-table=something to exclude the views.

Be careful with this. In the relational model, base tables and views share a namespace for a reason--they're supposed to be interchangable. SQL generally honors that part of the relational model. Some people use prefixes for database objects, so "we know whether an object is a view, a table, or a stored procedure that returns a table". If you build views with names like "view_wibble", and later decide to replace that view with a table, you have the distasteful options of naming your table "view_wibble", or rewriting application code, stored procedures, other views, etc to use "tbl_wibble" instead of "view_wibble".

But you can use these command-line options more than once. So you can name each of the views you want to exclude. The is probably the best idea.

pg_dump ... --exclude-table=view1 --exclude-table=view2 --exclude-table=view3 ...


How to set up an SQL Server authentication login for a database instead of using Windows Authentication

Ok found the answer after an hour of trying various options.

Server properties - Security (see image)

Shame there is no warning message somewhere when you create SQL Authentication users that they have no access.


Why is this query sometimes slow, and what can I do to make it faster?

Most likely, the EXPLAIN plan is probably stating that the query will perform an index merge. That's when two indexes are searched and the intersection between them yields the results.
SUGGESTION
You can probably achieve slight better performance by creating one index as follows:
ALTER TABLE logs ADD INDEX (deployment_id,type,received_at,time_slice);

This will read from the index only to generate results. It should not have to read from the table. The reason ? Every column in the query is in the index.
TRADEOFF
This will make the table bigger but can improve read speed.
If the performance is the same after creating the index, you can probably do without the index at this time.
GIVE IT A TRY !!!


Is there a way to prevent "reset role" in PostgreSQL

1
RESET ROLE would just "return" you back to the role you used to login. here
also SET ROLE rolename

  must be a role that the current session user is a member of

so " gaining permission he may not have" is not the case.

2
pgbouncer indeed will require such mapping.

3 to "reuse connections but have different authorization for different user upon connection." you can use set authorization which is very similar to SET ROLE.

There is no session pooler that would do it for you I believe...


How should I migrate a large MySQL database to RDS?

I just recently spent a lot of time trying to figure out a 15GB transition to RDS. Ended up finding a script on one of the amazon forums that I modified to my own uses and seems to work well. I'm not sure if you can do single transaction, but the dump itself is very quick compared to the actual transfer. I think 15GB only took me 12 minutes to dump, so even if it doesn't have single transaction option I don't think you'd have a very long span of time for inconsistencies to occur. I'm not sure if that's good enough for you, but I found the solution a lot more graceful than the flat file method.

#!/bin/bash

declare -a dbs=(dbname1 dbname2 dbname3 dbname4);

j=0
while [ $j -lt 4 ];
#4 is the number of dbs
do

echo "Dumping ${dbs[$j]} DB"
time mysqldump --order-by-primary --host=sourcehost --user=sourceuser --password=sourcepass `echo ${dbs[$j]}` > /tmp/`echo ${dbs[$j]}`.sql
echo "Adding optimizations to ${dbs[$j]}"
awk 'NR==1{$0="SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;\n"$0}1' /tmp/`echo ${dbs[$j]}`.sql >> /tmp/`echo ${dbs[$j]}`X.sql
mv /tmp/`echo ${dbs[$j]}`X.sql /tmp/`echo ${dbs[$j]}`.sql
echo "SET unique_checks=1; SET foreign_key_checks=1; COMMIT;" >> /tmp/`echo ${dbs[$j]}`.sql
echo "Copy ${dbs[$j]} into RDS"
time mysql --host=yourrds.rds.amazonaws.com --user=rdsuser --password=rdspassword `echo ${dbs[$j]}` < /tmp/`echo ${dbs[$j]}`.sql &

j=$(($j+1))
done


Poor performance by LINQ to SQL generated query execution plan

Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve.

I don't see any indication that there is likely to be useful optimizations that involve just indexes and statistics.  The entire query isn't included in the plan, but it's a monstrous, frightful thing, and it needs to be rewritten.

The main problem with the query is that it's a TOP 100 query, but is not able to cheaply identify the 100 rows, and then perform the rest of the JOINs.  Instead it creates a 7M row result, sorts it and takes the first 100 rows.

This really needs to be addressed by whoever wrote the query (here the application developer).  You can perhaps help by helping them articulate what the intent of the query is, and suggesting a better way.


ComputerNamePhysicalNetBIOS and "Current Host Server" shows different machines

Windows Server Failover Clustering requires a minimum of two computers (Virtual Machines or Physical Machines).  Lets refer to these as Node01 and Node02.
Part of the install process for the Windows Server Failover Clustering role defines the name of the cluster.  For our purposes let's call that MyCluster.
Once you have created MyCluster, you can add SQL Server instances to the cluster as independent "cluster resource groups".  We'll refer to our SQL Server clustered instance as MyClusterSQL\INSTANCE.
So, to clarify, we have:
╔════════════════════════╦═══════════════════════╗
║          Item          ║         Names         ║
╠════════════════════════╬═══════════════════════╣
║ Computers              ║ Node01 and Node02     ║
║ Cluster Virtual Server ║ MyCluster             ║
║ SQL Server Instance    ║ MyClusterSQL\INSTANCE ║
╚════════════════════════╩═══════════════════════╝
Now, if you run SELECT SERVERPROPERTY('ComputerNamePhysicalNetbios') it will return either Node01 or Node02 depending on which computer is hosting the SQL Server Instance.
SELECT SERVERPROPERTY('MachineName'); will return MyClusterSQL.  The Microsoft Docs for SERVERPROPERTY says this about "MachineName":

Windows computer name on which the server instance is running.
For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

SELECT @@SERVERNAME; will return MyClusterSQL\INSTANCE.
Server Manager always returns the name of the computer you connect to.  In our case, this will be either Node01 or Node02.  If the SQL Server Clustered Instance is running on Node02, and you connect to it using Remote Desktop to the MyClusterSQL server, Server Manager will show Node02, even though you connected to MyClusterSQL.


Scripted SQL Deployment in a Transaction with Rollback

Is your database online during your release or do you have a maintenance window?

If you have a maintenance window and are running Enterprise Edition you could create a database snapshot pre-deployment.

Then you can easily revert to the snapshot if your release process does not execute properly.


Unresolvable Ambiguity problem on Join Tables

GROUP BY ID. That's where the problem is. MySQL can't guess which table this column ID belongs to. There is an ID column in both the added wp_teleapo_supplier table and in one of the 3 others (I guess the wp_posts one).

All your column references should be prefixed with the table name (or alias): (object_id, term_id, post_type, post_title, post_content, ID). The only reason that these other references did not produce this error is because you were lucky and they only appeared in a single table, from the 4 tables in the FROM clause.

(some of the prefixes may be wrong. But if they are, you'll get an error and you can correct them:)

SELECT wpp.ID AS theid, s.ID AS supplierid, wpp.post_title, t.name AS category
FROM wp_posts AS wpp
  LEFT JOIN wp_term_relationships AS r ON wpp.ID = r.object_id
  LEFT JOIN wp_terms AS t ON term_taxonomy_id = t.term_id
  LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE wpp.post_type = 'post'
  AND (wpp.post_title LIKE '%bag%'
    OR t.name LIKE '%bag%'
    OR wpp.post_content LIKE '%bag%')
GROUP BY wpp.ID ;


How to create a new DB with same data from another DB in oracle 10g

One way would be to user RMAN's Duplicate Database feature.


Is there a function which the opposite of NULLIF?

NULLIF(expr1, NULLIF(expr1, expr2)) does what you want.


Select User who don't own a specific item

That's typical query, that can be implemented with EXISTS() function:

SELECT
    *                           -- get fields
FROM
    users as u                  -- from users
WHERE
    NOT EXISTS(                 -- where there is no
        SELECT
            TRUE
        FROM
            owns as o           -- owns
        WHERE
            (o.userId = u.id)   -- with this user
            AND
            (o.itemId <> 1)     -- and such item
    );

For multiple items check you can use IN() in condition check of correlated subquery:

SELECT
    *                             -- get fields
FROM
    users as u                    -- from users
WHERE
    NOT EXISTS(                   -- where there is no
        SELECT
            TRUE
        FROM
            owns as o             -- owns
        WHERE
            (o.userId = u.id)     -- with this user
            AND
            (o.itemId IN (1, 3))  -- and items from subset
    );


Yes, there is no way to have a foreign key that points to different tables.
I think you should import the 14 tables into a single table, perhaps adding a column that indicated from which original table the data came (if you need that information).
Tables under 100000 rows are considered small these days, so I wouldn't worry on that account.


Why does Publisher need to be able to connect to Remote Distributor?

I believe whenever transactional replication is initialized, the Publisher snapshots the articles in the publication and pushes it across to the subscriber. The remote Distributor and pull subscriber are used for the transactions after replication has been setup and initialized.


Should I upgrade compatibility level of MS Report Server database

This is not a question we can decide for you. Please review this Microsoft document especially the section with heading Differences Between Lower Compatibility Levels and Levels 110 and 120. Go through each item and decide for yourself.

Once you test thoroughly with new compatibility level you will know if you have any reason to keep old compatibility level (for backward compatibility). I prefer to use new compatibility level as you can see newer versions of SQL Server only support up to 3 or 4(maximum) older versions.  If you do not do it now at some point you will be upgraded anyway.


How can you have a combined primary key for one table?

Postgres has sequences, and you can definitely use more than 1 of them for the same table. For example, the following code generates value for invoiceNumber column based on invoiceType in the trigger :

create sequence seq_1;
create sequence seq_2;

create table table1(invoiceType varchar(10) not null,
invoiceNumber int not null,
constraint PK_table1 PRIMARY KEY (invoiceType,invoiceNumber),
constraint CHK_invoiceType CHECK (invoiceType IN ('Type1','Type2')));

CREATE OR REPLACE FUNCTION table1_seq_generator() RETURNS trigger AS $body_start$
    BEGIN
        IF NEW.invoiceType = 'Type1' THEN
          NEW.invoiceNumber := nextval('seq_1');
        ELSIF NEW.invoiceType ='Type2' THEN
          NEW.invoiceNumber := nextval('seq_2');
        END IF;
        RETURN NEW;
    END;
$body_start$ LANGUAGE plpgsql;

CREATE TRIGGER biTable1 BEFORE INSERT ON table1
FOR EACH ROW
WHEN (NEW.invoiceNumber IS NULL)
   EXECUTE PROCEDURE table1_seq_generator();

---------------Testing ----------------------------
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   SELECT * FROM table1;

You don't necessary have to do it in trigger, application code itself can get nextval for different sequence.

Also, I'd not use invoiceType,invoiceNumber as primary key constraint, but rather have surrogate pk and unique constraint on  invoiceType,invoiceNumber (or branch,invoiceNumber according to your naming ).


Possible to use a Trigger as failsafe for insert in two databases?

After a lot of try and error I can't seem to be able to contain critical error in the try.

The big problem here is that performing the remote insert inside the trigger involves a distributed transaction. Triggers execute inside the transaction of the calling statement; in this case, the transaction is the auto-commit transaction for the original INSERT statement. This local transaction is promoted to a distributed transaction when the operation on the linked server is attempted. Crucially, errors raised by DTC cannot be caught by TRY...CATCH.

An alternative approach is to write the view trigger so it only writes to the local table. A separate process (e.g. a SQL Agent job) then attempts to perform the remote inserts, removing successfully-processed entries from the local table.

To make this work, you will need to process the remote inserts one row at a time, via a stored procedure on the remote server that contains a TRY...CATCH block to catch any errors locally. This is restricted to a row at a time because the insert data must be passed via parameters to the stored procedure.

The job process needs to use a transaction to ensure local deletes and remote inserts are atomic, which you'd think would raise the same sorts of issues as with the trigger, but no - this does work in all scenarios I have tested so far.

You could also use Service Broker for this, which is a bit more work initially, but would avoid the row-at-a-time restriction, and is probably neater all round.

  Second problem is performance.

You can reduce the impact of this by reducing the connection timeout value using sys.sp_serveroption, for example:

EXECUTE sys.sp_serveroption
    @server = N'LINKEDSRV',
    @optname = 'connect timeout',
    @optvalue = N'1'; -- seconds

This is a moot point though if you decide to implement using Service Broker.


How to clean up memory usage of a database after a big sql query

Paul White wrote a response to the question What's a good way to get SQL Server to release memory to the operating system on a dev machine?.  This approach IS at the instance level, but that may be acceptable for your situation.

Quoting Paul:

  I routinely increase and decrease the max server memory configuration
  option for the SQL Server 2005-2016 instances running on my laptop
  (with lock pages in memory enabled). Has never caused me any problems,
  and memory always seems to be released quickly.

With that in mind and referencing Server Memory Server Configuration Options, it would seem that you could simply invoke the following T-SQL once to reduce the memory and then again to increase the memory by modifying the max memory value:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO


Poor Query performance in postgresql 9.3

This is an analytics query, analytics queries are never simple in terms of how much data they need to examine.

The where a.statename='Karnataka' is redundant, because it was already in CREATE MATERIALIZED VIEW and repeating it in your query has no effect, since it will match all rows. PostgreSQL sees this from cardinality and knows it needs to scan the full table.

Also, no index will help you here since you are touching a large portion of table. Indexes only work when touching small amounts of data.

If you run this query often, you need to cache it. If a value needs to be exact even as the original records are updated, you need a trigger.


How to best manage a queue of external processes

You could have a background job (SQL Agent or other) that runs the stored procedure, ensuring that you only have one instance running at a time.

The web app, instead of directly invoking the procedure, could just write a row to a "job table" or "queue table" and the background process works off of that.


How can I run SQL Server Vulnerability Assessment from a SQL Job?

You are getting that error because the module is not imported.

You have to use

import-module -name sqlserver  # or path to the module

see : Running PowerShell in a SQL Agent Job


You don't need a listener.ora. The listener will happily start without one, but will just start without supporting any services. pmon will periodically register databases with the listener, but you can force registration manually.

Move the file away:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>move listener.ora listener.foo
    1 file(s) moved.

Start the listener:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-NOV-2016 13:50
:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\host\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                04-NOV-2016 13:50:08
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\host\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))
The listener supports no services
The command completed successfully

Register the database with the listener:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 4 13:50:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter system register;

System altered.

SQL>

The listener now supports the database:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-NOV-2016 13:51
:20

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                04-NOV-2016 13:50:08
Uptime                    0 days 0 hr. 1 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\host\list  ener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>


Always On Failover Clustering vs Always On Availability Groups

To start with there is nothing called as Always On or Always ON availability groups it is simply called as Availability groups.

  Always On Clustering and Always On Availability Groups are 2 separate concepts. Clustering is a HA solution and AG is a DR solution. Is the Always On Clustering same as Windows server clustering?

Yes you are correct. Always on clustering means you have created availability groups on database(s) which is part of failover cluster instance (FCI) and availability groups simply means two standalone nodes which are part of WSFC or not are configured to form AG, there is no FCI here.

  To create an Always On cluster - we have to launch the installer and choose the "New SQL server failover cluster installation", then on every new node we need to launch the installer and choose "Add Node"

That is basically creating an SQL Server failover cluster instance. Yes after you have created an FCI you can go ahead and add the databases residing on FCI to an AG. This is what you are calling as Always On cluster and this is what official MS doc calls it.

For point 3 please refer to above two quotes. Again note the difference between AG and AG residing on FCI.

  Assuming we have a fresh SQL instance (with no HA/DR) and are planning to configure only AG, then we first need to ensure that the "Failover clustering" windows feature is enabled on each participating node.

If you are planning to configure ONLY AG you do not need failover cluster instance. AG can be configured with 2 or more nodes which are part of WSFC or not (starting from SQL Server 2017 we have clusterless AG). Remember an FCI will always require some kind of shared storage, OTOH AG does not requires any kind of shared storage.

  In above scenario, "Failover clustering" windows feature is enabled so is clustering a pre-requisite for AG? Is the concept of clustering in Point 2 and Point 4 the same.

WSFC is prerequisite for AG but starting from SQL Server 2017 you can have AG without WSFC but that is not a real AG. WSFC is prerequisite for FCI though.

EDIT:

  If I wish to configure both HA/DR using Always On (AG) and Failover Clustering, then is best practice to follow Point 2 and then point 4 or the opposite way? Also, should we use both the virtual cluster name and Listener or either one if sufficient?

You need to first configure WSFC then install FCI like you mentioned in point 2 and point 4 then create database and then configure availability groups. Virtual cluster name is for FCI while Listener is for AG. You cannot configure cluster without virtual cluster name you have to have one, but you can have AG without listener. The only work of listener is to direct connections to primary replica which can be on any of the node.

  What is difference between "SQL server failover cluster installation" and windows clustering?

Windows server failover cluster(WSFC) is windows server feature on which whole "clustering" technology is based while FCI is at SQL Server level which leverages WSFC for failovers to provide AH to SQL Server instance.


If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted

That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). There are a few exceptions: things that cannot be rolled back, like incremented serials or dblink calls.

  ... return the user friendly error to the user

You might just go with what Postgres returns by default. If you insist on custom error messages, be careful not to catch the wrong exception. You want to return two different messages for the same error code 23502. You would need separate blocks to achieve this in plpgsql.

  and log the sql server error message somewhere

If you are happy with the standard log output in the database log, that's plain and simple (and highly configurable). The manual:

  Whether messages of a particular priority are reported to the client,
  written to the server log, or both is controlled by the
  log_min_messages and client_min_messages configuration
  variables. See Chapter 18 for more information.

If you think of writing to a table, that would be the tricky part since everything is rolled back in case of an exception. That would include everything written to a log table. You'd have to fake an autonomous transaction to persist the log entries even though the transaction is rolled back. Detailed instructions:

Persistent inserts in a UDF even if the function aborts

To ensure passwords with proper length, simply create a CHECK constraint with a descriptive name. Like password_needs_8_char_or_more. Then you don't have to do anything extra. Postgres will raise an exception including the name of the constraint in case of a check_violation (error code 23514).

I would make it a simple SQL function with data-modifying CTEs. Fastest, safest, simplest - and go with the built-in error messages and built-in error logging:

CREATE OR REPLACE FUNCTION create_user_func(
   role text, firstname text, lastname text, email text, username text
 , is_enabled boolean, city text, state text  -- char is a dubious data tpye
 , zipcode int, password text, team text
 , OUT new_user_id int
   ) RETURNS integer AS
$func$
   WITH i1 AS (
      INSERT INTO users (firstname, lastname, email, username, is_user_enabled
                       , city, us_state, zipcode, user_password)
      VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10)
      RETURNING user_id
      )
   , i2 AS (
      INSERT INTO role_members (user_id, role_name)
      SELECT i1.user_id, $1
      )
   INSERT INTO team_members (user_id, dealership)
   SELECT i1.user_id, $11;
$func$ LANGUAGE sql;

And you probably do not want to use the data type char. Just use text. Or varchar(1) if you need the length restriction. Or possibly the special type "char" (with double quotes) if it's just a single ASCII character:

Any downsides of using data type “text” for storing strings?


Automation approach to executing Shell scripts and performing checks on an Oracle DB

If it were me, I would put everything in the database that I can.  For instance, all SQL logic should go into packages/procedures, and I would control the execution of these routines via DBMS_SCHEDULER jobs if possible.  Notifications based off of results from these routines can be sent via UTL_MAIL.  Putting this in the database allows you to lock it down with proper security.

If you truly need to execute Shell Scripts (steps 1 and 2 above) outside of the database, there are various ways you can do this from a PL/SQL routine, but they get hard to manage if things don't go exactly as planned, so the shell script needs to be able to handle errors gracefully.  Just try to minimize security issues by properly locking down any external files being called, etc.  I'm not a fan of calling shell scripts from PL/SQL code, but ultimately your process and comfort level is going to dictate your approach.

There are other approaches too, especially if you have money to spend.  Another option would be to look into enterprise-level scheduler applications.  There are a fair number out there, and if this process is complicated enough or starts affecting other processes within your enterprise these can help make sense of the chaos.

At the end of the day, it'll come down to your comfort level with the technology(ies) and the amount of resources (e.g. time and money) you can spend that will determine your best approach.


Conditionally track CDC on a table in SQL Server 2016

Unfortuantely, what you want to do is currently not possible. Documentation here.

However what you can do is on the CDC table put a view that will apply your filter post-capture.
You will loose the extra space, but you will get a view of only the data you want filtered.
Recommendation would be to run your queries on the CDC table with filters in the WHERE clause though rather than bothering with an extra view.

Hope this helps bring ideas.


SQL Server Configuration Manager returns remote procedure call failed

In my case there were two instances of the "SQL server Configuration Manager" in SysWOW64. Only the latest version worked. The other version produced the RPC error message.

C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager10.msc FAILED
C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager11.msc WORKED


Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 “No such file or directory”) when configuring cluster

Check your config files /etc/my.cnf and /etc/my.cnf.d/server.cnf and look for the socket parameter. Make sure it's the same for client and server. A good place for the socket parameter is in the [client-server] section in /etc/my.cnf. The socket file is created by the MariaDB server when it starts up.

Note that there are other places where MariaDB config files could reside, and these could potentially override your settings in the two files mentioned above. For more details, see this page in the MariaDB KB.

Also note that you may be able to connect to MariaDB through TCP rather than through the socket. You can specify that you want to use TCP rather than the socket with mysql ... --protocol=TCP. If this is successful, you can then do SHOW VARIABLES LIKE 'socket'; to find out where the socket file really is (or should be). You can then connect with mysql ... --socket=/path/to/file.sock or edit the config files so that the client reads it correctly from there.

In some cases you may also be able to figure out what socket file location is used by listing the processes: ps -ef | grep mysql which could output something like

root     23900     1  0 13:27 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/vserv-03.pid
mysql    24402 23900  0 13:27 ?        00:00:50 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep_on=ON --wsrep_provider=/usr/lib64/galera/libgalera_smm.so --log-error=/var/lib/mysql/vserv-03.err --open-files-limit=65535 --pid-file=/var/lib/mysql/vserv-03.pid --socket=/var/lib/mysql/mysql.sock --wsrep_start_position=98b22222-aa16-1114-ba1a-886633c6b8eb:55133333

As you can see here (scroll right), it's using a socket at /var/lib/mysql/mysql.sock.

EDIT:
A really good way of finding the location of the socket file is:

netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     22418    /var/lib/mysql/mysql.sock


Unknown returned type in PostgreSQL query

You can do this without generating a warning by creating a type and casting the records to it:

create type t as (a integer, b varchar(255));

select * from unnest(array[(1,'hello'), (3,'world')]::t[]);
┌───┬───────┐
│ a │   b   │
├───┼───────┤
│ 1 │ hello │
│ 3 │ world │
└───┴───────┘

tested on 9.4 and 9.3 (db<>fiddle here)


Largely ignored, as you're saying.

Extended properties are a bit awkward to read and write both from T-SQL and GUI. IMHO documentation should be maintained elsewhere (database projects, project documentation etc.).

Here's a good article on extended properties, which also addresses some of your doubts.


Logminer archived redo logs in other database

Yes. If you read the documentation it clearly states:

  There are four basic objects in a LogMiner configuration that you
  should be familiar with: the source database, the mining database, the
  LogMiner dictionary, and the redo log files containing the data of
  interest:

  The source database is the database that produces all the redo log
  files that you want LogMiner to analyze.
  The mining database is the database that LogMiner uses when it
  performs the analysis.


You can create a For Loop Container. For an example, see the mssqltips article Configure the SQL Server Integration Services For Loop Container.


Error importing SQL Azure Bacpac to SQL Server 2014 LocalDb

Turns out the solution is pretty simple, you need to ensue you have sql Server 2014 sp1 installed. can't believe I missed that!


First, you should run the following:
SELECT COUNT(1) ReplicationUserCount
FROM mysql.user WHERE Repl_slave_priv = 'Y';

If ReplicationUserCount > 0, then the DB Server can be used as a Master. Ask your sysadmins or DBAs if there are any active or dormant Slaves.
If ReplicationUserCount = 0, then the DB Server is standalone. You could then just delete all or some of your binlogs using these methods:
METHOD #1
RESET MASTER;

When you run this, all binlogs are erased and localhost-bin.000001 is created.
ALTERNATIVE: Shutdown mysql, delete localhost-bin.*, and startup mysql.
METHOD #2
PURGE BINARY LOGS TO 'localhost-bin.000190';

When you run this, all binlogs before localhost-bin.000190 are erased.
METHOD #3
PURGE BINARY LOGS BEFORE DATE(NOW());
PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 3 DAY;
PURGE BINARY LOGS BEFORE '2014-03-26 15:30:00';

When you run this these, this is what they do

all binlogs before today are erased
all binlogs before midnight 3 days ago are erased
all binlogs before 3:30PM on Mar 26, 2014 are erased

CAVEAT #1
If methods 2 or 3 fail, this indicates that there may be a problem with the file localhost-bin.index. It keeps a text file with the list of the bin logs. If the file is out of sync, use METHOD #1. It will recreate localhost-bin.index.
CAVEAT #2
Do not erase the binlogs from the Linux command line with rm or from the Windows Explorer. Doing so will throw the localhost-bin.index out of sync. If you do that, just do METHOD #1 and mysqld will clean it all up.
CAVEAT #3
If you set expire_logs_days = 7 in your my.cnf (or my.ini), it will automatically run this
PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 7 DAY;

on every log rotation or mysql restart.
EPILOGUE
Since you are interest in recovering space, choose one of the three methods. You can place expire_logs_days = 7 in your my.cnf (or my.ini) if you want. If you do not want it to grow past2 or 3 binlogs, then set this in your config file:
[mysqld]
expire_logs_days=1

and run
SET GLOBAL expire_logs_days=1;

to limit the number of binlogs to a single day.
Keep in mind that setting expire_logs_days does not work if localhost-bin.index is out of sync.


Should I define a FOREIGN KEY column as NULLable?

Yes.  Relationships (in ER modeling) can be optional.  There is no better way to represent a non-existent relationship in a foreign key than by putting in a NULL.  When you do a join, exactly the right thing happens.  When you do  a count, exactly the right thing happens.

The only reason to make a foreign key not NULLable is to make the relationship mandatory.  This rule might come from the subject matter, as captured by an ER model.  It depends on the situation.

In the question you posed, you already had a case of an optional relationship.  Not all invoices have any refunds.


Receiving "Query processor could not produce a query plan because of the hints defined in this query" when using FORCESEEK but can use FORCESCAN?

SQL Cannot "Seek" as the order of this index is by date first and then by PrimaryKey. As you are selecting with a date that match the full content of the index and as the PrimaryKey are not ordered (they are but only within each specific date), SQL have to scan it.
Try to change your index for this one:
CREATE NONCLUSTERED INDEX IX_Filtered_Table1_PrimaryKeyField
ON Table1 (PrimaryKeyField)
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

Sql Should then be able to use it (as the date match) and seek into the PrimaryKeyField (and the hint will probably not be required as this will probably what SQL will decide to do anyway)


Implementing HA for SQL Server for a .NET Application that uses MS DTC

Here is the most complete official-ish statement available about the risks and consequences of using DTC in an unsupported configuration with AGs:

  This means you could test a cross database transaction, force failover
  and everything work fine.   Test again it fails.   It could
  be as simple as the controlling DBID changes on the secondary and the
  outcome of the cross-database transaction can’t be properly
  determined.

  For Availability Groups you should avoid the use of DTC and
  cross-database transactions.  Relying or using them could manifest as
  a problem with the database recovery activities and that is certainly
  not what you want in a HADR environment.

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/

Note that all use of DTC with AGs was unsupported in SQL 2014 and before.  In SQL 2016 support was added for DTC transactions not involving multiple databases on the same instance.  In SQL 2017 that restriction was removed.


The SQL Server Network Interface library could not register the Service Principal Name (SPN)

You specifically get error "0x21c7, state: 15" when an attempt is made to register the SPN which already exists, but possibly under different account.

When you run SQL Service under local system account, an SPN is registered under the Computer Object in AD. You can confirm from below:

setspn -l ComputerName

In above results, If you see any SPNs for MSSQLSvc then they need to be dropped with below commands on AD (You need to have Domain Admin permissions for this) so that you can then register SPN under new Service account:

setspn -d MSSQLSvc/FQDN:XXXXXXXXXX ComputerName

Once the above is done, restart SQL Service under the Service Account adn it should successfully register the SPN under the new Service account and you will be able to confirm by running the below command:

setspn -l DomainName\LoginName

Reference: http://www.b-blog.info/en/changing-service-account-for-ms-sql-server-2012-and-the-following-issues.html

You could also use Microsoft Kerberos Configuration Manager for SQL Server to automatically detect and fix such SPN Issues:

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/


Item Index for all items in nested XML query

I found the answer - I needed to create a CTE that uses a union of all my child and parent records and creates a ROW_NUMBER(), then JOIN to that CTE to get the ROW_NUMBER() value which will be unique across all records.

Solution fiddle here.

Full solution to paste into SSMS:

BEGIN TRY
    DROP TABLE #Child
    DROP TABLE #Parent
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #Parent
    (RecId int PRIMARY KEY NOT NULL,
     PersonName varchar(100), Age int)
CREATE TABLE #Child
    (ChildID int identity PRIMARY KEY NOT NULL,
     ParentRecId int FOREIGN KEY REFERENCES #Parent(RecId),
     SalesAmt money)

INSERT INTO #Parent
  (RecID, PersonName, Age)
VALUES
  (1, 'Aaron Bertrand', 99),
  (2, 'Paul White', 20),
  (3, 'JNK', 33)

INSERT INTO #Child
  (ParentRecID, SalesAmt)
VALUES
  (1, 10.00),
  (1, 20.00),
  (2, 15.15),
  (2, 100.00),
  (3, 0.00)

;WITH IDs AS
(
    SELECT
       RN = (ROW_NUMBER() OVER (ORDER BY  RecId,CASE WHEN ChildId IS NULL THEN 0 ELSE 1 END) -1),
       RecId,
       ChildId
    FROM
       (
       SELECT
          RecId, ChildId = NULL
       FROM
          #Parent
       UNION ALL
       SELECT
          RecId, ChildId
       FROM
          #Parent P
       INNER JOIN
          #Child C
              ON C.ParentRecId = P.RecId) x
)

SELECT
  P.RecId as 'RID',
  P.PersonName as 'PNAM',
  P.Age,
  I.RN as 'Index',
  (
    SELECT
      C.SalesAmt as 'SAMT',
      I.RN as 'Index'
    FROM
      #Child C
    INNER JOIN
       IDs I
          ON I.ChildId = C.ChildID
    WHERE
      C.ParentRecId = P.RecId
    FOR XML PATH ('ChildRec'), ROOT ('ChildRecs'), TYPE
  )
FROM
  #Parent P
INNER JOIN
    IDs I
       ON I.RecId = P.RecId
       AND I.ChildId IS NULL
FOR XML PATH ('Parent'), ROOT ('Parents'), TYPE


Sync data in SQL Server 2014 with Oracle database12c

You have a few options:

Transactional replication: this has been around for quite some time
and works nicely within its narrowly defined scenario.
Unfortunately, the use of non-SQL Server subscribers is deprecated.
Change data capture + SSIS: this is the Microsoft recommended solution. It's a bit more involved than replication (to me anyway) but it's also more flexible. You can do some really powerful things with SSIS as the data mover, if you ever need it. Basic concepts described here with a detailed example here.
Oracle solutions: you can also do it from the other end. Oracle has similar capabilities as SQL Server including their GoldenGate software. However, I believe it's extra $$$ for license. I don't think you'll need to tread this path but calling it out for completeness.


MySQL statement in Project Voldemort site

AFAIK MySQL Cluster has the ability to scale, but it is really a manual process to do so. In fact, it takes more planning and more hardware to do such scaling. Here is why:
MySQL Cluster
According to "Guide to Scaling Web Databases with MySQL Cluster" (October 2011 Whitepaper), page 5 paragraphs 4,5 under the subheading 'Auto-Sharding':

MySQL Cluster is implemented as an active/active, multi-master database ensuring updates made by any application or SQL node are
instantly available to all of the other nodes accessing the cluster.
Tables are automatically sharded across a pool of low cost commodity data nodes, enabling the database to scale horizontally to
serve read and write-intensive workloads, accessed both from SQL and
directly via NoSQL APIs. Up to 255 nodes are supported, of which 48
can be data nodes ... MySQL Cluster excels at both aggregate and
per-node performance, eliminating the requirement to provision and
manage racks of poorly utilized hardware.

Scaling can be automatic, but within the confines of the MySQL Cluster architecture.
MongoDB
MongoDB can autoshard by means of running a routing program called mongos in front of shards. The router knows where data is located, so applications can connect to it and issue requests normally. There is no apparent limitations on the amount of servers to store data and communicate requests.
Conclusion
Just comparing MySQL Cluster to MongoDB, you find auto-sharding available in both. The major difference is that you are limited to 48 data nodes, even though a maximum of 255 servers can be configured for MySQL Cluster infrastructure (The remaining 207 maximum nodes make up SQL nodes [providing mysqld process and other APIs] and management nodes [providing ndb_mgmd process]). MongoDB does not have such an architectural limitation. MySQL Cluster can, by design, run out of elbow room.
Saying that" read and writes scale horizontally" would quickly apply to products that internally perform this. Using MySQL Replication, this statement holds true. Why? MySQL Replication is asynchronous (even MySQL 5.5 is Semisynchronous, at best), whereas MySQL Cluster and MongoDB (as well as other NoSQL Databases) are synchronous with the backing of ACID-compliance.


In sqlite, how are return values chosen for columns that are not used for aggregation or grouping?

The database will return the value from some arbitrary row in the group.
(In the current implementation, it's the value from that last row in the group that was processed, but due to indexes or other optimizations, the processing order might not be predictable.)

Since SQLite 3.7.11, using MIN() or MAX() guarantees that values from a row with the minimum/maximum value are returned.


Given the evil query is long-running you don't have to stress out about trapping it synchronously with its execution. It would be good if the approach was relatively lightweight and easy to remove.
I'd go with an agent job, executed on a schedule likely to overlap with the evil query. Once every few minutes should suffice given the evil query runs for thirty minutes or so.
This job examines all currently-running work by querying sys.dm_exec_requests. If start_time is more than a few minutes ago you likely have your culprit. Find the actual SQL text for each piece of work.
declare @query   nvarchar(max);
declare @victim  smallint;

select
    @query  = s.text,
    @victim = r.session_id
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as s;

A little bit of SUBSTRING(), CHARINDEX() and LIKE will confirm you're dealing with a "SELECT" without a "WHERE". If the evil query is submitted in a batch with other queries you'll have to be more careful when parsing the SQL text.
It can then be ended with KILL(@victim). There is a risk that, between being identified and killed, the evil query finishes and its session id is re-allocated to a "good" query. If evil's execution time is consistently long you can use start_time to reduce this risk. You know your environment and what's acceptable.


Oracle Data Pump Import with running advanced replication

Basically Advanced Replication works with materialized views, materialized view logs and triggers.
To replicate data from one side to another, you need to have INSERT, UPDATE or DELETE statements to make the replication mechanism recognize that there is new/changed data. When you import data with direct path method you skip all this stuff. This means you need conventional path method to import data.

DataPump chooses the method by itself on a set of conditions. These conditions can be found here: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL804

I'm not 100% sure which method is being used in your case but i doubt that it is conventional path.

Oracle does not tell how to force conventional path method but you could try to import the data with the old imp utility. When the export is done with DIRECT=N the imp should be done in conventional method.

Maybe someone else has experiences with Advanced Replication and DataPump. My experiences are limited since Oracle pushes Golden Gate as the future replication technology.


Patching of Windows Server 2012 running SQL Server 2014

No, this is backwards.

Typically, you wanna patch the secondary, let that sit around for a few days, then fail over to it manually.

Once things are up and running there, run some smoke tests. If all those pass, patch the primary, and fail back during your next maintenance window.

  "Primary gets patched, DBs fail over to Secondary automatically"

You don't have a dev environment to test this stuff on, huh?


improving performance of concurrent inserts on innodb with randomized primary

One INSERT per transaction, containing 50 rows, with "consecutive" (or "nearby") PRIMARY KEY values will be optimal, regardless of one or many connections.

Using MD5, UUID, etc, is a disaster for huge tables.  When the table is too big to be fully cached in RAM, the inserts become I/O bound.  In contrast, 50 'adjacent' records will cause almost no I/O.

A secondary index that is a MD5 or UUID is just as bad as the PRIMARY KEY being such.  This is because a secondary key is a BTree, just like the PRIMARY KEY (and the data) is a BTree.  (OK, the secondary key is not quite as bad, because it is probably smaller.)

If you have multiple connections doing inserts, plan on having occasional deadlocks.  Deal with them by re-executing the batch INSERT.

As for "gap locks", first let's get the big picture figured out.  That is, deal with the advice I have given you, and provide a SHOW CREATE TABLE.


SSMS 2016 Status bar -no option to see session id

I found it. It was hidden due to long name of my instance and userId.  Once I hover the cursor on top I could see it. Here is a screenshot.


Unfortunately, this is how MySQL Query Optimizer treats FULLTEXT indexes. When a MATCH clause is the only clause in the WHERE, the index will be used. When used in conjunction with AND, the index may easily get overlooked.

I wrote about this behavior before in Mysql fulltext search my.cnf optimization

SUGGESTION : Rewrite the query as the union of two FULLTEXT searches

SELECT * FROM content
WHERE MATCH(a, b, c) AGAINST ('keyword')
UNION
SELECT * FROM content
WHERE MATCH(d) AGAINST ('keyword');

GIVE IT A TRY !!!


Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU

causing very high Lck_M_U wait thus high CPU

The locks should not really be causing high CPU use - a thread that is waiting for locks to clear is doing just that: waiting and not consuming processing resource. So waiting for locks a lot is a symptom of the statements taking a long time to run, not a cause of it.

I suspect that the tables are not well indexed for the query that is working out what to update, particularly the derived table sub-query. If each run of this is scanning a large amount of data instead of being able to seek it more efficiently, then that would explain the high CPU cost (and the waiting - each call is waiting for the last to finish that scanning). As suggested in the comments already, posting a sample query plan would help here, as would a list of indexes defined on the relevant tables.

Looking at the plan posted after writing the above, I still suspect that better index options will help, add to the question the indexes that are currently defined.

It would also help to describe what the update is trying to achieve.

Is it right that it is updating thousands of rows each time? An estimated 55,979 rows in this instance. If most of the time the values will not change you can at least remove a lot of the page write activity and the need to attain update locks by adding AND (DelayedPoints <> 0 OR Points <> ISNULL(computed.Points, 0)) to the final WHERE, or if DelayedPoints may be NULL in some cases AND (DelayedPoints <> 0 OR DelayedPoints IS NULL OR Points <> ISNULL(computed.Points, 0)). The reason this will help is that SQL Server will perform the updates without checking that they are not necessary, so adding your own check will save it a pile of log and data page updates most times the statement is run (obviously don't do this if you have extra logic such in triggers that is relying on being triggered by these no-op updates).


Best way to automate the loading of data daily into google big query from SQL Server

See this Q&A at Stack Overflow: Migrate SQL Server database to BigQuery

SQLCMD is your friend. It dumps the results of a query into a CSV.


Linked Server : How to set an exclusive lock on a remote table?

When trying to execute a transaction across multiple servers, you need to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.

If my understanding is correct, BEGIN TRANSACTION creates a local transaction by default. Under certain circumstances (for instances, if you're doing an UPDATE on a table on the remote server) this will be escalated into a distributed transaction. If you have remote proc trans turned on for the remote server, then things like running a remote stored procedure should also automatically escalate to distributed transactions.

Otherwise, the remote operation will follow the normal transaction rules as a standalone operation on the remote server - in this case, the SELECT would be in its own one-statement "local" transaction on the remote server, and the lock would be released when the SELECT completed.

You may also need to use SET XACT_ABORT ON.

And, (as you noted in comments), you'll need to be sure the MS Distributed Transaction Coordinator service is on.