Dynamic length obfuscation method in T-SQL

No, I am not aware of any built-in function that does exactly this. But, you can still accomplish this without doing anything too complicated.

You could use the built-in CRYPT_GEN_RANDOM function (introduced in SQL Server 2008 R2) which generates random values based on a supplied length. The output is in hex/binary values so each byte returned is represented as two alphanumeric characters (hence the / 2 + 1 part below).

DECLARE @InputString NVARCHAR(4000) = 'hello';
SELECT SUBSTRING(CONVERT(VARCHAR(8000),
                         CRYPT_GEN_RANDOM((LEN(@InputString) / 2) + 1),
                         2),
                 1,
                 LEN(@InputString)) AS [Obfuscated];

SET @InputString = 'test';
SELECT SUBSTRING(CONVERT(VARCHAR(8000),
                         CRYPT_GEN_RANDOM((LEN(@InputString) / 2) + 1),
                         2),
                 1,
                 LEN(@InputString)) AS [Obfuscated];

Returns something along the lines of:

8C108

9A7A

The only real downside here is that this needs to be done inline as CRYPT_GEN_RANDOM cannot be used in a User-Defined Function (UDF: Scalar or Table-Valued). However, it can still be applied in a set-based approach using a CTE as shown here (just set @MaxLength to the max length of the column being obfuscated):

DECLARE @MaxLength INT = 10;

;WITH cte AS
(
    SELECT CONVERT(VARCHAR(8000),
                   CRYPT_GEN_RANDOM((@MaxLength / 2) + 1),
                2) AS [Random]

)
SELECT tmp.[String],
       cte.[Random],
       SUBSTRING(cte.[Random], 1, LEN(tmp.[String])) AS [Obfuscated]
FROM   (VALUES (N'test'), (N'Hello')) tmp(String)
CROSS JOIN  cte;

Returns something along the lines of:

String    Random          Obfuscated
------    ------------    ----------
test      F99B3888F993    F99B
Hello     D3250E74F0A3    D3250

As you can see, CRYPT_GEN_RANDOM returns a different value for each row.

Also, not sure if this is acceptable or not, but the only alpha characters returned are A - F.

OR, if you want the obfuscation to be repeatable for the same input value, or at least don't mind it being repeatable and prefer that this code be in a function so that it is easier to apply to multiple columns, you can use the HASHBYTES function which, like CRYPT_GEN_RANDOM, returns hex/binary bytes. Unlike CRYPT_GEN_RANDOM, the output length is fixed (in this case at 64 characters since I am using SHA2_256), so I used REPLICATE to repeat the hashed valued if the length of the input string is more than 64 characters. Also unlike CRYPT_GEN_RANDOM, HASHBYTES can be used in a User-Defined Function (UDF) :-).

CREATE FUNCTION dbo.Obfuscate(@InputString NVARCHAR(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  SELECT SUBSTRING(REPLICATE(CONVERT(VARCHAR(8000),
                                     HASHBYTES('SHA2_256', @InputString),
                                     2),
                             (LEN(@InputString) / 64) + 1),
                   1,
                   LEN(@InputString)) AS [Obfuscated];
GO

And that can be used as follows:

SELECT tmp.[String],
       LEN(tmp.[String]) AS [InputLength],
       ob.[Obfuscated],
      LEN(ob.[Obfuscated]) AS [OutputLength]
FROM   (VALUES (N'test'), (N'Hello'), (REPLICATE(N'A', 63)),
               (REPLICATE(N'B', 64)), (REPLICATE(N'C', 65)),
               (REPLICATE(N'D', 4000))) tmp(String)
CROSS APPLY dbo.Obfuscate(tmp.[String]) ob;

Returns something along the lines of:

String                       InputLength    Obfuscated                    OutputLength
------                       -----------    ----------                    ------------
test                               4        FE52                                 4
Hello                              5        A07E4                                5
AAAAAAAAAAAAAAAAAAAAAA...         63        4B589C85DE74E76487730F3...          63
BBBBBBBBBBBBBBBBBBBBBB...         64        79813FB6480F354F1C6017A...          64
CCCCCCCCCCCCCCCCCCCCCC...         65        FB4B38FBA41ECC24B5B0F68...          65
DDDDDDDDDDDDDDDDDDDDDD...       4000        5D01CC6508C164E652B5C77...        4000

PLEASE NOTE: If you need alpha characters beyond A - F and/or need to have distinct obfuscated values for distinct input values (i.e. reduce chances of collisions), then either method above can be adapted easily enough to do that.


Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

I'd question why you have OrdersForProductX tables at all
It's possible the FK problem you've asked about can be designed out...

If these tables have the same structure, then you simply need a ProductType column on some OrderProduct table. Then Payment just links to that with one FK

If the table have different structures, I assume they have some common attributes. So, you can have a common OrderProduct table then specific child table per product type (see below) Again, Payment just links to the commone table with one FK

This is the "superkey/subtype pattern"

UQ1 is the "super key" used a foreign key on the subtype tables
Each subtype table has a composite PK and FK on (OrderID, ProductType)
Each subtype table has a CHECK constraint to restrict types in that table

OrderProduct

OrderID, PK, UQ1
ProductType, UQ1
CommonThing1
...

OrderProductA

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = A
ProductAThing1
...

OrderProductB

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = B
ProductBThing1
...


Cannot start recv'ing chunk, sharding is not enabled and no config server was provided

Shutdown all mongod and mongos (those two) processes, your shards and that config server. When nothing is running, first start shardServers when those are up, start config server and last mongos. Connect to mongos with mongo.

I noticed that with your tags, you haven't define where $minkey --> 1, SiteId: 10 or SiteId:20 --> $maxKey goes. So those chunks can be anywhere.

When chunkMove happens, what config servers log says?


Default InnoDB page size is 16K. Pages go into a larger 1MB data structure called an extent. MySQL allocates 4 extents at a time.

All of these are stored in one large file, so boundary issues have minimal effect on InnoDB.

You can read more about MySQL file space management.

Modern hard drives have read caches, and they read ahead, so contiguous sectors are already available when you ask for them without having to perform another seek.

Given enough working space (innodb_buffer_pool_size), InnoDB will store much of your indexes and data in memory, only waiting on disk for update transactions and commits.

According to MySQL documentation, it seems MySQL reads an extent (1MB) at a time from disk to the buffer pool, where it will access it a page at a time.


Do my metrics justify changing the MAXDOP settings?

I would investigate the CXPacket issues first - see what's causing those.  Can you identify a selection of queries that cause those wait types?  Can you then see what is causing the problem through the actual execution plan?  You're looking at gather streams steps, and looking for uneven loads coming in.  Try to identify the source of those - a likely suspect is out-of-date / not helpful index statistics.


Oracle equivalent of sp_rename in SQL Server

Community Wiki answer containing the solution given by Justin Cave in a question comment

execute immediate takes a string.  The snippet you posted has no strings in it.  You'd need to assemble the appropriate string and then pass that to execute immediate.  Something like:

execute immediate 'alter table ' || var_table_name || ' rename ' ||
    var_col || ' to ' || var_new_col`.

Of course, you probably want to put that in a local variable so that if/when it fails you can log the actual SQL statement.


How to simplify repeating variables in where clause for multiple columns SQL Server

One good way to get reasonable performance from this type of query is to insert the 'val1', 'val2', ... values into a temporary table, then join against that temporary table.

We start with some sample data:

USE tempdb;
IF OBJECT_ID('dbo.Items') IS NOT NULL DROP TABLE dbo.Items;
CREATE TABLE dbo.Items
(
    ItemID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Items
        PRIMARY KEY CLUSTERED
    , col1 varchar(30) NOT NULL
    , col2 varchar(30) NOT NULL
    , col3 varchar(30) NOT NULL
    , col4 varchar(30) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.Items (Col1, Col2, Col3, Col4)
VALUES ('value0', 'value02', 'value03', 'value04')
    , ('value11', 'value21', 'value31', 'value41')
    , ('value5', 'value6', 'value7', 'value8');

To find desired rows, we insert the search terms into a temporary table, then join against it:

IF OBJECT_ID('tempdb..#SearchItems') IS NOT NULL DROP TABLE #SearchItems;
CREATE TABLE #SearchItems
(
    val varchar(30) NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO #SearchItems (val)
VALUES ('value2%')
    , ('value03%')
    , ('cow%');

SELECT i.*
FROM dbo.Items i
    INNER JOIN #SearchItems si
        ON (i.col1 LIKE si.val)
            OR (i.col2 LIKE si.val)
            OR (i.col3 LIKE si.val)
            OR (i.col4 LIKE si.val);

The output from the query above:

+--------+---------+---------+---------+---------+
| ItemID |  col1   |  col2   |  col3   |  col4   |
+--------+---------+---------+---------+---------+
|      1 | value0  | value02 | value03 | value04 |
|      2 | value11 | value21 | value31 | value41 |
+--------+---------+---------+---------+---------+


The more common and accepted way is that once a job lands on a server in an AG you start editing it:

Add a first job step that checks whether this is the primary.
Exit on success if it isn't.
Otherwise continue onto the normal job step.

This has been explored in a few other SE questions (they don't specifically relate to CDC which is why you didn't find them):

How to run SQL Job only on primary replica in AlwaysOn Availability Groups?
SQL Server Agent Jobs and Availability Groups

The second way is to create a watchdog job using similar methods which will toggle the jobs on and off. This is probably more acceptable seeing as you're fiddling with SQL Server internal jobs. But it's up to you.

I wasn't quite happy with the samples above hardcoding AG names and such. I prefer to just have the job step operate within the database in question, and this way it can work out for itself if it even has a group and is part of it (as your jobs should continue running even if you temporarily remove them from an AG).

Set Nocount On

If  Exists (
    -- Database in an AG
    Select  Top 1 0
    From    sys.availability_databases_cluster adc
    Join    sys.availability_groups ag
    On      adc.group_id = ag.group_id
    Join    sys.dm_hadr_availability_group_states dhags
    On      ag.group_id = dhags.group_id
    Where   adc.database_name = Db_Name()
    )
And Not Exists (
    -- Database in an AG which is Primary on this instance
    Select  Top 1 0
    From    sys.availability_databases_cluster adc
    Join    sys.availability_groups ag
    On      adc.group_id = ag.group_id
    Join    sys.dm_hadr_availability_group_states dhags
    On      ag.group_id = dhags.group_id
    Where   adc.database_name =  Db_Name()
    And     Upper(dhags.primary_replica) = Upper(@@Servername)
    )
Begin
Raiserror('This is not the primary replica.', 16, 1) With Nowait
Return
End

-- Check if the database isn't accessible
If  Not Exists (
    Select  Top 1 0
    From    sys.databases
    Where   databases.name = Db_Name()
    And     state_desc = 'ONLINE'
    )
Begin
    Raiserror('The database doesn''t exist or isn''t ONLINE on this node.', 16, 1) With Nowait
    Return
End

This kind of thing is also useful for SSIS jobs and other bits that pop up over time.

Going one step further you probably want to create a PowerShell script which will go through and check your servers from time to time for AGs which have CDC jobs and which don't have a watchdog, so that you will be notified to create one.


If you want to omit those specific SQL statements, you could probably put them into the TextData NOT LIKE filter for the trace. Escape the underscore with [_].


New instance with same @@VERSION as existing instances

No, unfortunately there is no magic "be like that guy" feature. What you have to do is either:

install RTM and then apply the SP + CU; or
slipstream the same SP + CU into the RTM binaries and install that. These instructions are applicable to both SQL Server 2008 and SQL Server 2008 R2, though the process is a little easier in SQL Server 2012.

The former is easier but could require more downtime.


Try adding the All member into the set on rows:

SELECT
  NON EMPTY
    {[Measures].[Order Count]} ON 0
 ,NON EMPTY
    {
        [Date].[Year].[Year].ALLMEMBERS*
        [Date].[Quarter].[Quarter].ALLMEMBERS*
        {
          [Order].[Customer].[Customer].ALLMEMBERS
         ,[Order].[Customer].[All]
        }
    }
  DIMENSION PROPERTIES
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON 1
FROM [Order]
CELL PROPERTIES VALUE


MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?

Part 1 of the answer to your question is no, you can't selectively tell MySQL what not to store in the InnoDB buffer pool.  It's an integral part of how InnoDB manipulates table data.

Part 2 is that you don't really want to or need to, because it's smart enough to work out what should stay there if the pool is not large enough to retain your entire data set.

Incidentally, I might have mentioned previously... don't trust tuning scripts.

At best, they may give you "interesting things" to investigate and read up on, but it's absolutely impossible to properly "tune" a MySQL server without understanding the nature of the data that's on that server... and, you're just as likely to tweak your performance downward as well as up.

InnoDB doesn't load anything into the buffer pool that isn't in some way useful.  Leave a newly-restarted MySQL server alone without running any queries, and the buffer pool will sit there, allocated but mostly empty.  SELECT from a table, and some number of the pages from that table's tablespace on disk will be loaded into the pool, but not usually all of them.  Which pages depends on which rows you retrieve, what indexes are needed to find them, whether it's doing read-ahead, etc.

UPDATE a table and the pages with the rows you update will be loaded into the pool, modified in the pool, and then written back to disk in the background.  INSERT and DELETE bring pages into the pool and ultimately flush them back to disk also.

But InnoDB keeps track of how "useful" what's currently in the buffer pool is, how recently it was needed, whether any changes have already been written back to disk or not, and will evict pages to make room for other pages when the pool is full.  Many of my production servers have an InnoDB buffer pool that is smaller than the server's data set and essentially 100% full 100% of the time, but there's no performance impact from that when a large amount of the data hasn't been accessed for some time.

If a page in the pool hasn't been changed since it was loaded, or if it has been changed but since that time the changes have been written back to disk, and it hasn't been needed for a while, that chunk of the pool is readily reusable for something else the next time InnoDB wants to load something back into the pool from disk... so it's "in use" but still "available."

Unless you're frequently accessing all 50GB of those blobs, the size recommendation from the tuning script is overblown.  Of course, in an ideal world, your pool would be larger than all of your tables and indexes and you'd have an all-in-memory database that only had to access the disk to flush changes out... but in the real world of real data, the chances are pretty good that a large portion of your data is not frequently accessed, and there's no way for a simple script to anticipate those conditions.


How does SQL Server perfom lock when using CROSS APPLY?

A query is not an executable program. The SQL query specification language enables us to define the logical results of a query, but it is up to components like the query optimizer and execution engine to take care of the physical processing involved in delivering those results.

In principle, the physical processing side of things is free to perform whatever operations it likes, so long as the data returned matches the query specification. It might use hashing, scanning, sorting ... anything it likes, so long as the results match the logic of the query.

For example, the optimizer might well produce a very similar (or even identical) user-visible execution plan for both your sample queries, because they logically describe the same result. On the other hand, it might not; and it does not really matter either way.

The second major factor here concerns the transaction isolation level that the query is executed at. The different isolation levels available each provide different guarantees regarding the effects on one query due to concurrent data modifications by another.

Locks are just one possible mechanism by which these isolation guarantees might be enforced by the database engine. Other isolation implementations that do not use locks at all are available, in SQL Server as well as most mainstream products.

The point is that the engine guarantees to respect the requested isolation level, and it may do so using locks, or it may not. These details may be important sometimes from a practical point of view (for example when blocking occurs) but they are not truly fundamental.

As a result of all this, there is usually no direct relationship between the original written form of the query and the locks that might be taken - there's just too much other 'stuff' going on in between.

  Isn't there even known "tendencies" of the SQL Server's behavior when it deals with RBAR scenarios? Something to be aware of maybe?

That comes with experience, though that is not universally a good thing. Certainly there is plenty of advice out there, though some is outdated, or based on a misconception.

In SQL Server, we can often safely encourage "set-based thinking" and say cursors, loops, and row-by-row processing should most often be avoided, but that is not the whole story, though this is all going a bit beyond your original question.

My usual advice is to write good queries first (using whatever syntax you find most natural) against well-designed and maintained databases, then worry about any specific issues like locking (optimizer limitations, plan shape issues etc.) if and when they arise. People do sometimes learn to avoid things like 'APPLY' for the wrong reasons, and have to unlearn it all later.

In general, I am pretty skeptical of advice that says to always prefer one syntax element or construction over another.

Resources:

Lesson 2: Understanding Logical Query Processing - Microsoft Press
Basics of Transaction Isolation Levels - SQL Server Customer Advisory Team
SQL Server Isolation Levels - My series of articles


Check attribute table referenced foreign key

It's possible to add a_type to b, and have check constraint on b.a_type.  Breaking normalization a bit allows enforcing storing details in proper table . In Oracle you can have 2 constrains supported by the same index, for instance PK on a.id, and UNIQUE on (a.id,a.type) , then some tables that require proper type may have FK to (a.id,a.type), and other tables that don't care about type can refer to a.id .


good practices of creating tables in mysql

I once asked for answers to a problem very similar to yours, and even though I think back on how frustrated I was after a Software Architect did it to me, now, as a much wiser developer, I give the same answer to you.

  Database Design for Mere Mortals: 2nd Edition
  (2003) by  Michael J. Hernandez. There is also a  Database Design for Mere
  Mortals: 3rd Edition
  (2013) by the same author.

 -- Come back in a couple years as a database engineer, then give someone else the same answer.


What is the recommended method of storing statements used in timer jobs?

I think any answer to this question will be 'mostly' opinion based.

When I think of 'best practices' and comparing options, I think:

Is one way more secure?
Is one way easier to maintain?
Is one way adhering to company policy better?
Is one way more efficient or perform better?

Sql Server Agent Job information (and encapsulated code) is stored in the MSDB system database.

It's often desirable to emulate a lot of processes in other 'non-production' environments and people frequently back up their production user database(s) and restore them to 'test' environments.  Storing your code in a user database (as a SP or Function) allows for that functionality to more easily be restored to a non-production environments during a 'refresh'.  Storing this functionality in a Sql Agent Job limits that flexibility because you either have to manually edit the non-production job or script out the production job and recreate the job in the non-production environment.  This can lead to production and non-production code not matching because someone didn't script the changes in the agent job.

Ultimately, you have to decide what works best for your company


Multi server administration - Failed to import Maintenance Plan to named instance

Your problem is that the Maintenance Plans are really just SSIS Packages under the hood. As your MSX server is 2014, I'm assuming that is where you created them, which means the SSIS package version will be 2014. You will most likely have to downgrade your packages to have them run on SQL 2008 R2, which means you won't be able to include them in your master job server.

This link states that SSIS only supports target servers that are the same version or higher than the master server version, so your 2008 R2 server would either have to be the master or you need to revert to using T-SQL based maintenance jobs instead of Maintenance Plans.


Is there a way to store int in ObjectID?

If you don't specify _id field when inserting a document in MongoDB, the database internally generates ObjectID and stores the document. In your case, the _id column will be populated with automatically generated ObjectID.

If you want to convert 1, 2, 3, 4 as ObjectID, No you cannot. Because ObjectID should be in 12 bytes .

The _id Field


"Use Database" command inside a stored procedure

From MySQL Documentation on Stored Routine Syntax  When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates).
USE statements within stored routines are not permitted
If you need to query tables from other databases you can do it like DBName.TableName. and you can change DBName dynamically.


MySql Database solution for specific web app problem

You could use Nested Set, but CRUD operations are more complex than other patterns, and there's no referential integrity.

The Closure Table pattern is simpler to CRUD and supports integrity, but requires an extra table.

Recursive Query is the simplest and supports integrity, but you would need a more advanced database like Postgres, SQL Server, or Oracle.

http://blog.mclaughlinsoftware.com/2009/04/03/t-sql-hierarchical-query/

http://karwin.blogspot.ca/2010/03/rendering-trees-with-closure-tables.html


Are deadlocks always a symptom of design flaws or are they an unavoidable occurrence even in well designed systems with high data writes/reads?

I am of the opinion that deadlocks are always the symptom of design flaws

With READ COMMITTED SNAPSHOT set, and foreign keys supported by indexes, deadlocks should be treated as application bugs.

That's not to say you'll never see a deadlock, or that you won't have to introduce locking hints to serialize access to objects.

But with RCSI, deadlocks should be rare enough that they can be individually diagnosed and remediated, as opposed to using a retry loop or resorting to dirty reads.


Any risks in updating the system time on a mysql 5.5 server?

With the changing of the clock times on the Master

there might be loss of data for the slave
a slave may replay a query it already processed

Rather than risk either of these extremes, you should schedule a brief downtime.

Here is what you should do:

STEP 01: Stop all writes to the Master

SET GLOBAL read_only = 1;
FLUSH TABLES WITH READ LOCK;

STEP 02: Make sure are writes have stopped on the Master

SHOW MASTER STATUS;

You may see something like this

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.005956 | 59702913 |              | dba              |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

Run this several times and make sure nothing is running

STEP 03: Set the clock time on Master (and Slave if needed)

STEP 04: On the Slave, run this several times

SHOW SLAVE STATUS\G

Make sure

Seconds_Behind_Master is 0
Read_Master_Log_File is not changing
Relay_Master_Log_File is not changing
Exec_Master_Log_Pos is not changing

STEP 04 : Run the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='bin-log.000001',master_log_pos=4;

STEP 05 : On the Master, run

RESET MASTER;

This will erase all your binlogs on the Master and start with a new one

STEP 06: On the Slave,

START SLAVE; DO SLEEP(5); SHOW SLAVE STATUS\G

Make sure Seconds_Behind_Master is 0

STEP 07: On the Master,

UNLOCK TABLES;

That's it.

It's better to do this that to just flip the clock on a live system

I have suggested this about 1.5 years ago (Risk of changing clocktime on MySQL server host)


Database architecture for big data store?

The specified data is quite common on any LAPP or LAMP scenario. I think both databases would handle this kind of loads easily. If you really need high availability, and you are on AWS; I would use RDS, with either PostgreSQL or MySQL, depending on which database you feel more comfortable with. All the necessary steps to configure and maintain the databases are taken care of for you.

If you want to prepare for High Availability, the streaming replication options of both MySQL and PostgreSQL are good alternatives. The intermediate software, that should monitor the master and slave(s) databases and decide when the master is 'dead' and a slave needs to be promoted is the most difficult part (and many times, becomes your SPOF).

Every DB has its pros and its cons. I use mostly PostgreSQL, but also MySQL for some applications that require it. If I have to make a choice, 90% of the times I'll go for PostgreSQL. However, there is not a "good one" or a "bad one". There's normally one that fits best your needs. Sometimes your SQL needs are not that complicated, but you want to use a DB that is known by your team, and maybe your team knows better MySQL. Sharding is easier (better integrated) using MySQL, for instance.


DROP vs DELETE vs TRUNCATE for staging data

The "recommended way" is to design the best solution given the relevant factors.

Options #1 and #2 are unlikely to be ideal as they result in a lot of unnecessary I/O.  #3 is what would be best in most situations, but as far as I/O is concerned it is not much different from #4.  So performance-wise, #3 and #4 can considered to be the same.  The additional I/O to drop/create a table as opposed to just truncating it is insignificant compared to the I/O of the typical data load.

Your comments seem to indicate that you need to make changes to the staging table quite often, and if that is the case, then #4 may be best for you.  Since it's a staging table, it is unlikely that permissions are complex, and if it's in a dedicated staging database where staging account is the only ID that has read/write, then you wouldn't even need to explicitly set permissions when the table is created.

Option #5 is a non-starter unless you have some specific reason to create a new database each time.


SQL Server 2014 Replication - The process could not connect to Subscriber 'xxx'

Community Wiki answer generated from question comments by Brandon Williams

In the tutorial, the repl_distribution account will need to be added to the subscription database as db_owner.

In the New Subscription Wizard, on the Distribution Agent Security page, you specify a Process Account, which would be the repl_distribution account that was created on the Publisher. Now, further down on the page is a section 'Connect to the Subscriber'.  Choose 'By impersonating the process account'.

If the account exists at the Subscriber with the same username and password, and appropriate permissions, it will work.  This is known as pass-through authentication.  Alternatively, you can use SQL authentication to connect to the Subscriber.

Response from dthree:

I tripled checked all of the security settings to make sure it was all set up right, and in that I found that I shared and set the security settings on the wrong folder for the repldata snapshot. Fixed that, and between both fixes it worked.


Why is my database 12 times bigger than inserted data?

text is the best type for plain text. Text values alone occupy about the same internally (depends on encoding details, too of course: UTF-8?). A small overhead of 1 byte per field in your case. See:

What is the overhead for varchar(n)?

The enum column contributes very little to the total size. 4 bytes per row (a real quantity internally). The length of enum names is practically irrelevant, stored onceas data type name, so with a maximum of NAMEDATALEN (normally 63 characters). Minimal overhead for the type declaration. So the enum column actually occupies less space inside the DB than in your text file.
There are various types of overhead in Postgres storage, it has to be like that. Some of them one-time costs, some other scale with the number of rows or relations etc. There are catalog tables (system data), indexes, relationships, table statistics, bookkeeping data etc.

12x the size seems more than average for 50 MB of raw text data. But that really depends on details. Many small rows have a lot of overhead, few large rows not so much, can even be compressed and smaller. a_horse already pointed to a major reason: 6 indexes - some of which obviously unnecessary, too.

Each row in a table has a tuple header occupying 23 bytes (plus alignment padding, typically 24 bytes), and possibly alignment padding between columns and tuples, 4 bytes for the item identifier, some overhead per data page (typically 8kb page size). Roughly ~ 35 bytes per row in your case.

About as much for every index tuple (header has only 8 bytes, but indexes bloat more). 6 indexes, ~ 150 bytes per table row minimum. Plus every text column is saved twice, 1x table, 1x index. And btree indexes start with a FILLFACTOR of 90, so ~ + 10 % on everything.

Your average row size is ~ 75 bytes (50MB / 700000 - assuming "lines" correspond to rows). Your table and indexes occupy about 6 times the size of the raw text. The rest is other overhead mentioned above.

Measure the size of a PostgreSQL table row

That's all assuming no table bloat (yet). Once you work with the DB (update, delete, transactions rolled back etc.) there will be dead rows and such, possibly multiplying total space. That very much depends on write patterns.

You can save a lot by removing unnecessary indexes.

And a couple of bytes per row if you move the enum column to the top of the table: less alignment padding, should average at 1.5 bytes per row, roughly 1 MB total, hardly worth it. See:

Calculating and saving space in PostgreSQL


Gather values from different related rows with JSONB fields

As per comments, you need a timestamp field and I've added it to your sample data.  Now IMHO you should first unnest your jsonb data. I've used a SUM(CASE for this purpose and it calculates too the minimum timestamp value.

SELECT
    event.job_id,
    job.some_field,
    event.payload->>'type' AS type,
    SUM(CASE WHEN event.payload->>'a' IS NOT NULL THEN 1 ELSE 0 END) AS a,
    SUM(CASE WHEN event.payload->>'b' IS NOT NULL THEN 1 ELSE 0 END) AS b,
    SUM(CASE WHEN event.payload->>'c' IS NOT NULL THEN 1 ELSE 0 END) AS c,
    SUM(CASE WHEN event.payload->>'d' IS NOT NULL THEN 1 ELSE 0 END) AS d,
    MIN((event.payload->>'ts'::text)::timestamp) as min_ts
FROM
    event
JOIN
    job
    ON event.job_id = job.id
GROUP BY
    event.job_id,
    job.some_field,
    event.payload->>'type'
ORDER BY
    1, 2;

This is the result:

job_id | some_field | type |  a |  b |  c |  d | min_ts
-----: | :--------- | :--- | -: | -: | -: | -: | :------------------
     1 | val1       | 1    |  1 |  0 |  0 |  0 | 2004-10-19 10:23:54
     1 | val1       | 2    |  0 |  0 |  1 |  0 | 2002-10-19 10:23:54
     2 | val2       | 1    |  2 |  0 |  0 |  0 | 2003-10-19 10:23:54
     3 | val3       | 1    |  2 |  0 |  0 |  0 | 2004-10-19 10:23:54
     3 | val3       | 2    |  0 |  0 |  1 |  0 | 2005-10-19 10:23:54
     3 | val3       | 3    |  0 |  0 |  0 |  1 | 2006-10-19 10:23:54

db<>fiddle here

Starting at this point you can easily filter it by type and get the desired result.


What are some ways to implement a many-to-many relationship in a data warehouse?

In my experience, a recursive hierarchy is the most practical way of tackling this. It offers the following advantages:

Unlimited depth.
Compactness.
Flexibility.
Speed.

By contrast, it takes an extra table for each level of "-to-many" joins. This is hard coded and difficult to maintain against schema updates.

By using filtered indexes, a large table of hierarchical joins can perform at superior speed to dedicated tables. The reason is each join is only "parent-child" compared to "to join table to data table". The latter has more indexes to process and store.

I've been trying to solve this problem for many years. Recently, this is what I came up with.


MySQL silently replaces UTF chars with literal question marks

I made some attempts to dig into this issue, here are the results.

When you set a connection charset (i.e. SET NAMES utf8) MySQL transparently handle encoding conversion for you. For instance if I insert a à (\xE0 in latin1 \xC3A0 in utf8) in a latin1 table using a UTF8 connection it reads the UTF 8 value and store it in table as \xE0

mysql> SELECT HEX('à');
+-----------+
| HEX('à')  |
+-----------+
| C3A0      |
+-----------+

mysql> INSERT INTO articles VALUES(50001, 'à');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT content, HEX(content) FROM articles WHERE id_p = 50001;
+---------+--------------+
| content | HEX(content) |
+---------+--------------+
| à       | E0           |
+---------+--------------+
1 row in set (0,00 sec)

When I insert invalid utf8 chars into a latin1 it replaces them with question marks as I shown in the original question.

In order to fix my issue I had to run this command on the original table (actually I tried it on a small copy of it). It takes care of changing charset, collation and also convert existing data. I took a record with a char which is different in latin1 and utf8 encoding

mysql> select HEX(BINARY SUBSTRING(content, 17, 1)), SUBSTRING(content, 17, 1) from articles where id_p = 40\G
*************************** 1. row ***************************
HEX(BINARY SUBSTRING(content, 17, 1)): 93
            SUBSTRING(content, 17, 1): “
1 row in set (0,00 sec)

mysql> ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 34905 rows affected (1 min 10,73 sec)
Records: 34905  Duplicates: 0  Warnings: 0

mysql> select HEX(BINARY SUBSTRING(content, 17, 3)), SUBSTRING(content, 17, 3) from articles where id_p = 40\G
*************************** 1. row ***************************
HEX(BINARY SUBSTRING(content, 17, 1)): E2809C
            SUBSTRING(content, 17, 1): “
1 row in set (0,00 sec)

After the conversion the “ char was replaced by its utf8 encoding in the content and all data are still readable. The conversion also changed content column type from MEDIUMTEXT to LONGTEXt because latin1 uses 1 byte per char and utf8 up to 3 bytes per char to avoid data truncation.

Now I'm experimenting with inserting invalid utf8 chars into the converted table and I get a different result for that. It seems that invalid (or not supported 4 bytes) utf chars are simply dropped from the stored value with a warning (shown only if warning enabled)

$ mysql --show-warnings

mysql> INSERT INTO articles VALUES(90000, 0xC328);
Query OK, 1 row affected, 1 warning (0,00 sec)

Warning (Code 1366): Incorrect string value: '\xC3(' for column 'content' at row 1
mysql> SELECT 0xf09f8eb6;
+------------+
| 0xf09f8eb6 |
+------------+
|            |
+------------+
1 row in set (0,00 sec)

mysql> INSERT INTO articles VALUES(90001, 0xf09f8eb6);
Query OK, 1 row affected, 1 warning (0,00 sec)

Warning (Code 1366): Incorrect string value: '\xF0\x9F\x8E\xB6' for column 'content' at row 1

After this I found that also in my original example a warning is shown if they are enabled:

-- With warnings enabled
mysql> INSERT INTO `articles` VALUES (50000, 'Hırvatistan');
Query OK, 1 row affected, 1 warning (0,00 sec)

Warning (Code 1366): Incorrect string value: '\xC4\xB1rvat...' for column 'content' at row 1

Finally to trigger an error instead of just a warning (to avoid data loss) just change SQL mode for session or globally (at server level)

mysql> SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO `articles` VALUES (50000, 'Hırvatistan');
ERROR 1366 (HY000): Incorrect string value: '\xC4\xB1rvat...' for column 'content' at row 1


How to implement database mirroring using SQL on two geographically separate servers

For mirroring the requirements are as follows:

Install same version of WINDOWS server and SQL SERVER`
Set same password with same username on windows as well as sql server`
Check the port whether 1433 port is available or not,It is sql server default port.
Check whether 5022, 5023 port opened or not(It is sql server mirroring port.)
After this you can set up mirroring.

Mirroring can be done on same version and edition of sql server.
In case of enterprise edition you can use full features of database mirroring. We can take snapshot backup of database and verify from secondary end.

And in case of standard edition mirroring can be set up but to verify on secondary side mirroring must be broken.


Why is my SQL statement sometimes slow and sometimes fast?

This Answer has a mixture of explanation ("why" slow versus fast), speedup (how to be more efficient), and non-blocking (how to avoid the trouble with mysqldump).

If few rows are that old, the main effort is scanning the entire table.  This can be helped significantly via INDEX(date)
If more than 100K rows are that old, then the main effort is saving 100K rows in case of a undo (rollback).  This can be quite costly.  Don't do more than 1K at a time.
If the count is somewhere in between, then there is both the table scan and some of the undo effort.  Note that it may find rows to delete early in the scan, or late -- thereby making this take an unpredictable amount of time.

While the lengthy DELETE is running, other things may be adversely affected -- such as timeouts in mysqldump.  So, the goal is to keep the DELETE down to only a few seconds, both by speeding it up and by decreasing how much you bite off at a time.

My Big Delete blog explains several ways to significantly improve your task.

Note that one of my techniques focuses on what to do if you are deleting more than half the table.  This involves copying over the rows to keep.  (See the details on RENAME TABLE, etc)


Create Enumerated Function Parameter Type

You can't do what Microsoft does with built-ins, sorry. You will need to write your function to accept a string, for example, and evaluate it inline, e.g.

CREATE FUNCTION dbo.CustomDateDiff
(
  @datepart VARCHAR(32), -- does not need to be NVARCHAR
  @start    DATETIME,
  @end      DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @datepart
      WHEN 'HOUR'   THEN DATEDIFF(HOUR,   @start, @end)
      WHEN 'MINUTE' THEN DATEDIFF(MINUTE, @start, @end)
      WHEN 'WEEK'   THEN DATEDIFF(WEEK,   @start, @end)
      ELSE               DATEDIFF(DAY,    @start, @end)
    END);
END
GO

Not that you can't use a conditional inside DATEDIFF - a lot of people think you could say:

DATEDIFF(@datepart,

Or

DATEDIFF(CASE WHEN @datepart = 'HOUR' THEN HOUR END,

But neither of these will work - both yield:

  Msg 1023, Level 15, State 1, Line 2
  Invalid parameter 1 specified for datediff.


ORA-12899: importing a .dmp file + character set conversion

One solution is to have both databases using the same character set. Considering your destination is a free XE version why not do a new install?  Just make sure you use EL8MSWIN1253 character set and the same UTF version.


Copy values to another column - ORA-01481: invalid number format model

% is not a valid format specifier for numbers.

See the documentation below:

Number Format Models


Get one column table when doing localization

Assuming that each month is universally identified by number (1 is January, 2 is February, etc.), it is useless to have a table that store the first twelve numbers, so you could have a single table, with the following attributes:

CREATE TABLE month_loc (
  month_number    INT NOT NULL,
  name            VARCHAR(200)    NOT NULL,
  description     VARCHAR(500)    NOT NULL,
  lang_id         INT NOT NULL,
  PRIMARY KEY(lang_id,month_number)
);

You could use this table to find efficiently a certain month in a certain language.


SQL Server 2008 - How to check FK integrity?

I think you are over complicating your requirement.

Your best option is to

Create a backup of PROD on a test server
drop all the FK's
migrate the data you want to destination server using SSIS or T-SQL or BCP (which ever one you prefer).
create FK's back.

In SQL Server you don't have a tool to do it out of the box. You will end up writing more complex tsql code which I doubt will achieve what you are trying to do + you have to take care of Identity columns as well.

Below script would loop over all the tables and select them one-by-one by taking into account referential integrity constraints (i.e. foreign keys).

Note: Below script is borrowed and modified from here written by Jamie Thomson.

    -- make sure to use SQLCMD mode in SSMS and change the database Name.
    :setvar db Adventureworks2008

    DECLARE @tables TABLE (
        schemaName sysname
    ,   tableName sysname
    ,   level int
    );

    with fk_tables as (
        select  s1.name as from_schema
        ,       o1.Name as from_table
        ,       s2.name as to_schema
        ,       o2.Name as to_table
        from    $(db).sys.foreign_keys fk
        inner   join $(db).sys.objects o1
        on      fk.parent_object_id = o1.object_id
        inner   join $(db).sys.schemas s1
        on      o1.schema_id = s1.schema_id
        inner   join $(db).sys.objects o2
        on      fk.referenced_object_id = o2.object_id
        inner   join $(db).sys.schemas s2
        on      o2.schema_id = s2.schema_id
        --For the purposes of finding dependency hierarchy
        --we're not worried about self-referencing tables
        where   not (   s1.name = s2.name
                    and o1.name = o2.name)
    )

    ,ordered_tables AS (
        SELECT  s.name as schemaName, t.name as tableName, 0 AS Level
        FROM    (   select  *
                    from    $(db).sys.tables
                    where   name <> 'sysdiagrams') t
        INNER   JOIN $(db).sys.schemas s
        on      t.schema_id = s.schema_id
        LEFT    OUTER JOIN fk_tables fk
        ON      s.name = fk.from_schema
        AND     t.name = fk.from_table
        WHERE   fk.from_schema IS NULL
        UNION   ALL
        SELECT  fk.from_schema, fk.from_table, ot.Level + 1
        FROM    fk_tables fk
        INNER   JOIN ordered_tables ot
        ON      fk.to_schema = ot.schemaName
        AND     fk.to_table = ot.tableName
    )
    insert  @tables
    select  distinct ot.schemaName,ot.tableName,ot.Level
    from    ordered_tables ot
    inner   join (
            select  schemaName,tableName,MAX(Level) maxLevel
            from    ordered_tables
            group   by schemaName,tableName
    ) mx
    on ot.schemaName = mx.schemaName
    and ot.tableName = mx.tableName
    and mx.maxLevel = ot.Level
    ORDER   BY [Level] DESC;

    DECLARE tables_cursor CURSOR FORWARD_ONLY
    FOR
    SELECT  schemaName, tableName
    FROM    @tables
    ORDER   BY [Level] DESC;

    DECLARE @schemaName         sysname
    ,       @tableName          sysname
    ,       @schemaTableName    NVARCHAR(MAX);

    DECLARE @vSQL nvarchar(max);
    OPEN    tables_cursor;
    FETCH   NEXT FROM tables_cursor INTO @schemaName, @tableName

    BEGIN TRAN
    BEGIN TRY
        WHILE   @@FETCH_STATUS = 0
        BEGIN
                SET @schemaTableName = '[' + @schemaName + '].[' + @tableName + ']';
                SET @vSQL = 'select top(100) * FROM $(db).' + @schemaTableName;
                exec (@vSQL);
                PRINT 'Selected ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows from $(db).' + @schemaTableName;
                FETCH NEXT FROM tables_cursor INTO @schemaName, @tableName;
        END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        PRINT ERROR_MESSAGE();
    END CATCH

    IF @@TRANCOUNT > 0
        COMMIT TRAN;

    IF CURSOR_STATUS('global','tables_cursor') >= 0
    BEGIN
        CLOSE tables_cursor;
        DEALLOCATE tables_cursor;
    END
    GO


ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a)

If you have enough space, you can create a copy of the actual table and do the work on that:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

Then you can change the column as desired:

ALTER TABLE tbl_name MODIFY COLUMN col_name BIGINT AUTO_INCREMENT;

Once the process is done, you can rename the tables:

RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2;

Then drop the original table, and you should have the spected result.


SQL Server - Temporary vs. Physical Tables

It can be demonstrated quite easily that it will not reduce IO nor contention, but instead increase both.

IO: Every row inserted, read or deleted from a #temp table will be now inserted, read or deleted from the @@SPID table. But every row will be wider with an additional @@SPID column, hence the number of pages needed will slightly increase and the IO will be ever so slightly bigger. But more importantly, the drop of a #temp table and the initialization of a new session's #temp table by a session will now have to be simulated with a DELETE FROM @@spidTable WHERE spid = @@SPID, and thus truncate/create operation (ie. page extent management operations) will be transformed in row operations, incomparable slower.
Contention: Every scan that was using page locks on the #temp table will now have the potential to lock a page with unrelated spid rows, thus creating previously non-existing contention. Every update that does more that hits the lock escalation threshold has the opportunity to escalate the lock to table lock and thus block every other spid.

So while is true that you won't hit the mythical IAM/SGAM/GAM contention in tempdb, the sole reason why this would happen is because your operations will become far slower due to ordinary extra IO and extra contention.


I believe you should be able to specify a session variable by placing it in a backup.cnf file:

max_statement_time=10000

And then using the --defaults-file=backup.cnf parameter to mysqldump.

(You can also place user credentials in that file so that you don't have to show the username and password on the command-line, which is a potential security issue.)


Do Sequences Have the Same Replication Range Limits As Identities?

With merge replication, identity ranges must be allocated to the publisher and each subscriber. Identity range management can be either manual or automatic.

As you already have found out, when a range is exhausted, a new range must be allocated. With automatic range management, the merge agent takes care of allocating a new range, with manual range management you decide what should happen.

With sequences, you will still have the same problem, with the difference that there is no such thing as automatic sequence range management, so your only option is manual range allocation. (FWIW, identities are implemented with sequences in SQL Server 2012 and above).

My suggestion is to avoid identities and sequences altogether: they do more harm than good. Did you know that in order to let identity range allocation happen at the publisher, the users that insert data in the database need to be members of the db_owner role? Yeah, that's awful.

With merge replication you need a uniqueidentifier ROWGUIDCOL anyway, so why don't you use that column as your surrogate key instead? If your key is really a surrogate, nobody should be able to see it or use it and it should be completely transparent to the application.


SQL Server - How to update two tables linked by foreign key with FROM statement?

All you want to do is filter on the Active column of the User table being 0.  I'd also recommend using the join syntax, but that's not necessarily relevant to your question.

UPDATE Orders
SET Active = 0
FROM Orders o
JOIN User u on o.userID = u.ID
WHERE u.Active = 0
AND o.Active != 0


PostgreSQL: initdb fails with: 'FATAL: could not access file "$libdir/euc_kr_and_mic": No such file or directory

It turned out the problem was caused by leftovers from another PostgreSQL installation. After removing that the problem was solved.


Why isn't the rows of an EXPLAIN accurate?

You may find this surprising but that's the behavior of InnoDB.

The InnoDB storage engine does cardinality approximation by walking a few levels down the nonleaf BTREE nodes and takes an educated guess.

I wrote about this a long time ago based on mysqlperformanceblog.com

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Aug 03, 2011 : When should I rebuild indexes?
Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables?

Perhaps you could try disabling that behavior by running this:

SET GLOBAL innodb_stats_on_metadata = 0;

Setting innodb_stats_on_metadata  should stabilize the approximate cardinality and you should get the same cardinality over and over again.


Why do these queries deadlock and how to work this around in MariaDB?

OR is terrible on performance, and probably equally bad on transaction locks.

pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= ...

Instead of depending on NULL in that, consider storing a date in the future.  That way this would suffice:

pxpl.DeactivationTS >= ...

EXISTS -- Since EXISTS stops at the first occurrence, LIMIT 1 is redundant.  Similarly, the inner ORDER BY seems fishy.

TINYTEXT -- This datatype has some minor disadvantages compared to VARCHAR(255)  Better yet, is to use VARCHAR with a realistic limit.

If those don't help enough, please provide a SELECT (possibly with a LEFT JOIN) that would list the rows that should be deleted.  With that, we might be able to devise a variation on the DELETE that will be more deadlock-proof.

If that, too, fails, then simply plan on handling deadlocks:  Detect them, then replay the transaction containing the DELETE.

This might be a better DELETE:

DELETE proxies FROM proxies
    LEFT JOIN proxy_xref_proxy_list AS pxpl
      ON proxies.ID = proxy_xref_proxy_list.ProxyID
    AND (proxy_xref_proxy_list.DeactivationTS IS NULL
      OR proxy_xref_proxy_list.DeactivationTS >= (NOW() - INTERVAL 24 HOUR))
   WHERE proxy_xref_proxy_list.ProxyID IS NULL


What is the significance of a semicolon at the end of SQL*Plus commands?

Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon

Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.


Restrict server login by user AND machine (within or not within a domain): is it possible?

You can create a server level trigger for LOGON and in there you can check the user, domain and the client host name using sys.dm_exec_sessions to determine whether user is allowed or not. (We can have the permitted combinations in a separate table if require)

I have done a similar thing to stop users accessing the SQL Server databases using SSMS with any SQL Logins. Only domain users are allowed via SSMS.

Hope this helps you.


How can I drop a stored procedure whose name is in a variable?

To do this, you must use dynamic SQL, as the statement DROP PROCEDURE cannot be parameterized.

As a bit of an aside, it's a good practice to always schema-qualify object names. You didn't specify the schema name in the question (or say that the variable contains a complete dot-separated name), so I've gone ahead and extended what you asked for to include it.

DECLARE @stored_procedure_schema sysname = NULL;
DECLARE @stored_procedure_name sysname = N'some_stored_procedure_name';

DECLARE @sql nvarchar(MAX) =
    N'DROP PROCEDURE ' +
        ISNULL(QUOTENAME(@stored_procedure_schema) + N'.', N'') +
        QUOTENAME(@stored_procedure_name) + N';';

EXEC(@sql);

This doesn't do any checking to see if the procedure actually exists or not before it attempts to drop it, so you'll get an error if it doesn't exist. It's trivial to use sys.procedures to check for existence, so I didn't bother with that, and you didn't ask for it either.

To preemptively answer your comment on the other answers, QUOTENAME will correctly escape any object name such that it ends up surrounded by square brackets. Specifically, yes, this does handle single quotes (and square brackets) within the procedure name itself.


Will a clustered index improve performance of updates performed sequentially on that clustered index?

The IN like this is a set of discrete values. SQL Server won't bother working out it is a range or checking ascending values.

That is, x IN (1,2,4,5) is parsed out to x=1 OR x=2 OR x=4 OR x=5. OR is non-SARGable in most cases and result in scans rather than seeks.

If you put the data into a temp table with an index and did x IN (SELECT foo FROM #bar) then it becomes a semi-join and will more likely use an index


Omitting [INTO] in the INSERT ... SELECT statement

Check out the correct answer to this question - and Bill Karwin's comment on that answer. I was quite surprised to learn that INTO was optional - but indeed it is, although according to the standard it shouldn't be. Amazingly enough, Oracle does require it - must be the only time that Oracle actually enforces one of the ANSI standards where (many*) others leave it optional!

(*) Sybase, MS SQL Server and MySQL amongst others. IBM DB2, Firebird and PostgreSQL seem to enforce the standard.


How to prevent SQL Server service account from registering / deregistering SPN?

This doesn't make sense, as you are running the service under a domain account with no "write ServicePrincipalName" permissions. If you were running under a localSystem account or a domain account with "write ServicePrincipalName" permissions, it would be able to create the spn. Is it possible that someone/something manually created the spn?

Here's an excerpt from Degremont Michel (SQL Server Core Engineer at Microsoft) explaining how this works:

If you run SQL Server under the LocalSystem account, the SPN is automatically registered as SQL registering with the machine account that has the right to create an SPN default. So Kerberos interacts successfully with the server running SQL Server.

However, if you run SQL Server under a domain account or a local account, the attempt to create the SPN may fail. When creating the service principal name fails, this means that no SPN is set for the service that is running SQL Server.

http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx

Here's another excerpt from msdn:

When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended) or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.

http://msdn.microsoft.com/en-us/library/ms191153%28v=sql.105%29.aspx

Sounds like the account "somehow" has permissions that you don't know about. Maybe group policy or something similar...


Why is this user "Admin" created in all databases?

Looks like I have found the reason, this user exists in System Databases=> Model. That is why it was created for all new databases.


In a replication setup, does corrupted data also gets replicated?

A crash often leaves MyISAM tables in need of "REPAIR".  Switch to InnoDB to avoid such.

Even then, it was rarely the "data" that was corrupted, just the index file (.MYI).  The index is maintained separately on each server; replication is not involved.

Bottom line: Don't use MyISAM any more.

If the Master discovers corruption, it will halt.  In this case, nothing will go to the Slave.

Replication involves sending changes (either in the form of write SQL statements or the effect of those statements), so corruption is not propagated.

If your definition of "corruption" includes a malicious DROP TABLE, that "corruption" will be propagated to the Slave.


csv files are tricky.

You should have a header in your csv file and also mark strings with apostrophes

But for your csv you must select latin2 as encoding see image

You must choose the right encoding, because this determines how bits and bytes are interpreted.

so if you don't want ansi you have encode it properly

See here for notepad++


MySQL index size doesn't include all values

Thanks @Akina for pointing me in the right direction: https://stackoverflow.com/questions/16732980/why-cardinality-value-in-mysql-indexes-dont-equal-distinct-count-for-column-val
Running ANALYZE TABLE my_table updated the cardinality to match index size.


Where is MySQL template directory for pristine Data?

For anyone met the same problem as me, I couldn't find the pristine Data directory. I looked everywhere but I couldn't. So I reinstalled the new copy on my second computer. And copy the Data folder from it. Now, my multiple servers work without any problem in one machine.


There are a few things I can clarify for you here:

Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before).  Mysqlpump does this by default.
When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation).  For MySQL 5.7 there are additional optimizations here.
When you trickle load indexes, they may have more page splits and a lower page fill efficiency (fragmentation).  This depends a little on the data, and if it is in order.  You are not giving me many clues with column1, column2, but say for example column1 was a timestamp it would probably be in order.
Pages in an index are logically in order, not necessarily physically.  This distinction may matter if you can not fit your working set for ranges in memory and are using spinning disks for backing storage.  It's also a little hard to answer because with spinning disks you are probably using RAID with some stripe size, and the blocks at the filesystem level may also be non-contiguous.
Be aware also that the optimizer may not consider the range scan for large ranges over a tablescan (since prior to 5.7 the cost model assumed pages were not in memory).  If you can rely on this being the case, you may want to FORCE INDEX and compare actual run times.  (More info on new cost model.)


Most efficient way to update a column where Id exists in another table

I believe by doing an EXISTS on a subquery you may be using a sub-optimal way to accomplish this. Why not emulate an INNER JOIN directly like so:
update fiscal_year y
set y.process_date = now()
from owners o
where y.owner_id = o.owner_id
and y.process_date is null;

Having an index on (owner_id, process_date) for the fiscal_year table should help ensure the most optimal operations occur in this case too.
Your tables aren't big by any means, this process should be able to execute in minutes if not seconds, as long as everything is architected properly. I'd be curious what the above yields you (with the recommended index).


In this scenario, as James Anderson suggests, one-to-one relationships are not the way to do it. One patient may be checked up, operated on, discharged etc., more than once. The tables you have now should be renamed as follows and given their own unique ID primary keys to better represent what they are trying to model:

PatientRegular to Regular
PatientDischarge to Discharge
PatientOperate to Operation
PatientCheckup to Checkup

They should still retain their PatientID columns but those columns should become foreign key reference columns back to the PatientID column in the Patient table. In other words, you will have one-to-many relationships between:

Patient to Regular
Patient to Discharge
Patient to Operation
Patient to Checkup


A way to reference other DB without hardcoding its name

If the databases are under 10 GB, then it might be easier to use SQL Server Express Edition and create a separate Instance for each customer. This would allow for full separation between the paired Databases (a more accurate testing environment) and means that you wouldn't need to change any of the DB names. If the app and SQL Server are on the same server for dev and testing, then you might get away with using SQL Server Express LocalDB (a.k.a. just "LocalDB") which is really quick and easy to create new Instances and to start and stop them. But LocalDB does not allow for TCP/IP connections, or any remote connections; it is local only.

Even if you need Standard or Enterprise Edition features, it might still be easier to create an Instance per customer rather than modifying objects to reference a different Database name, especially since any modifications introduces the risk of changing some incorrectly and then breaking something that you aren't even working on.

And, if you have a standard naming convention, such as DEV_{customer_code}, then it would be fairly easy to write a command-shell script (.cmd file) to start and stop the Instances simply by passing in the {customer_code}, and then you could create desktop short-cuts for those and/or call the script via automated testing, etc.

Don't forget, SQL Server 2016 Developer Edition is now free. You don't need the Enterprise features, but you can't beat that price :-).


If you have a license for enterprise edition, upgrade to 5.5 or 5.7 and use the audit log plugin.


mysql innodb cluster 3 nodes, 2 lagging almost 24 hours

Hello for those out there with similar issues I fixed it thanks to this amazing blog post.
https://thoughts.t37.net/fixing-a-very-lagging-mysql-replication-db6eb5a6e15d
basically my changes:

STOP SLAVE;
SET GLOBAL slave_parallel_workers=40;
START SLAVE;
set GLOBAL sync_binlog=0;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL innodb_flush_log_at_timeout=1800;`

I already had the MT setup so just needed to add this and 40 workers.
Hope this will help other people..


Log Shipping always fails, after a while

An error occurred while processing the log for database

'text.staging.fsintel.com'. If possible, restore from backup. If a backup is not

available, it might be necessary to rebuild the log.

Somehow your logs are getting miss matched.

If you have any Log backups job please disable it.
Avoid taking log backups while enabled log shipping, or just manually run the logshipping backup job for log backups.


Cannot rename the table because it is published for replication

sp_removedbreplication 'DB_NAME' that will remove replication from the database.
From How to cleanup Replication Bits by Chris Skorlinski of Microsoft SQL Server Escalation Services:

sp_removedbreplication – Removes all replication objects from a database without updating data at the Distributor. This stored procedure is executed at the Publisher on the publication database or at the Subscriber, on the subscription database.
sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.


stored procedure times out while subsequent runs take 1/6 the time

Sounds like stats are out of state, and SQL is going a full scan and loading all the data into buffer pool on the first run, then accessing that data from buffer pool for the rest of the runs.

Try flushing the buffer pool between runs and see if the times increase to confirm this.

Also can you post the execution plans?


Looking for a way of moving millions of record without impacting the transaction log

I regularly do such refactorings, without shutting the system down, and without stopping modifications against the table being refactored. I am moving data in small batches.

Copied from my blog: Refactoring large live OLTP tables without downtime

Refactoring tables does not have to be such a big deal. We do not have to shut modifications down, migrate all data into new structure, deploy modified modules, and do it all at once, while the system is down.

Doing all migration at once may be risky, and if we make a mistake, there is no easy rollback to the old version. This is why whenever we need to refactor a large table, we are using an alternative, low-risk, no-downtime, incremental approach. The method I am going to describe has been used in practice several times, and we did not have any problems with it.

All our transitions from old table structure to the new one were smooth and incremental. More to the point, we were able to set aside the migration at any time and switch to some other more important task, or leave for the day, or enjoy the weekend, all while the database was fully functional.

Typical refactoring scenario

I am totally making this up, I have never worked on bug tracking systems.

Suppose that we are storing tickets in a table dbo.Tickets, which, among other columns, has a column named AssignedTo. As such, each ticket can be assigned to only one person at any time.

However, the next version of our system will allow to assign a ticket to more than one person. Our plan is to create another table, dbo.TicketAssignments(TicketID, AssignedTo), migrate existing data to it, and change appr. 50 stored procedures affected by this change. To minimize risks, we are going to finish all database changes at least a few days before the new version of our system is released.

This means that the current version of our system is going to run against our refactored database exactly as it did against the old one. BTW, the table dbo.Tickets is quite large, and is heavily used all the time.

Before the migration. Prerequisites.

We shall need some extra disk space, approximately as much as the old table uses up. Besides, we need a server that is not struggling with its current workload, so that it can withstand some additional work for the duration of our migration.

Also we shall need a good test coverage on all modules using the table, including unit tests, stress tests, and performance baselines. Typically in our system we already have solid test coverage.

One more thing: because table structure is going to change, inserts directly into the table are not going to work any more. As a result, all test data should be populated via stored procedures that continue to work against the new table structure.

For example, we might use a stored procedure dbo.SaveTicket that has a parameter @AssingedTo to populate test data for our unit tests.

Creating new tables, changing modifications.

As our first step, we create two new empty tables:

dbo.TicketsV2, which has all the same columns as dbo.Tickets, except it does not have AssignedTo column;
dbo.TicketAssignments(TicketID, AssignedTo)

We also change all the procedures which modify dbo.Tickets, so that they write to both old and new tables.

This biggest risk in this step is introducing concurrency-related problems. We need to stress test our modifications well. If we have any problems, however, we can just run a rollback script, changing all the modifications back to their original version. Such rollback takes just a split second.

In fact, we never actually had any problems at this step, because our stress testing harness is quite solid.

Of course, our modifications get slower, but we have made sure that our hardware can handle it. We had not had actual problems with slow modifications either.

Migrating existing data

At this stage all the reports are running off the old table.

As all new changes get saved into both old and new tables, we are also moving over all existing data to the new structure. We want this migration to be non-intrusive, so we typically just run one WHILE loop, moving over like 1K-10K rows at a time, so that our migration does not hinder OLTP activity and reports.

Modifying the reports.

While the data is migrating, we can take our time changing our stored procedures to read from new tables. Because we have good unit testing coverage, we can refactor procedures with confidence - if we break something, we shall know it right away.

Because not all data has been migrated yet, we do not deploy the modified procedures.

Verifying that migration completed.

To verify that all data migrated correctly, we need to write a SQL query. To my best knowledge, there is no GUI tool that can efficiently compare large tables. Writing a query, however, is not that difficult.

This query is going to use a lot of resources. We need to be careful not to bring the server to its knees while the query is running. There are several ways to accomplish that.

Deploying modified reports.

Once we have complete and correct data in the new tables, we can start deploying new procedures. We do not have to deploy them all at once - we can deploy them five or ten modules at a time, even if some other procedures still access the old table.

There is one more problem our team need to be very careful with - our test server is not exactly identical to our production one. As such, we encounter a risk that our procedure runs fast in test environment, but is slow in production.

This is why we first deploy our changed procedures into a different schema, which (schema) is not exposed to our user. For example, instead of altering procedure dbo.GetTicketsForDay, we create a new procedure Internal.GetTicketsForDay. Only developers have privileges on Internal schema, so users cannot execute it yet. Once we have executed Internal.GetTicketsForDay in production environment and are happy with performance, we can deploy it as dbo.GetTicketsForDay.

Our only risk at this stage is that we can deploy poorly performing procedures. Our rollback strategy is simple - we just roll back to the original stored procedures that read from the old table.

Finalizing the migration.

Once all the reports access the new table, we can change our modifications, so that they no longer write to the old table. The old table can be archived out and dropped, reclaiming the additional storage which we needed for the migration.

Conclusion

As we have seen, we can refactor an OLTP table without downtime and with low risks, even if it is big and data migration takes a lot of time.

One more thing: a common reaction to such Agile war stories is a recommendation to "do it right the first time". It is so common that I would like to address it right now.

Of course, at the time when the previous version of bug tracking system, it was quite obvious that eventually we might need to add the ability to assign a ticket to more than one person. Of course, if we had the table dbo.TicketAssignments from the very beginning, we would not have to go through this complex refactoring. In other words, we should "do it right the first time", should we not?

In general, "doing it right the first time", developing a flexible database structure that we should not have to change later, makes a lot of practical sense, but not under all circumstances.

More specifically, when the previous version of bug tracking system was being designed and developed, there were hundreds of brilliant ideas, lots of great features that might be useful later on. Implementing all these hundreds of brilliant ideas would take years, and a much bigger team. It was just not possible. More to the point, it was not what our customers wanted - they wanted us to take care of their most important problems first, and quickly. They did not want to wait until we could provide more features.

Instead of trying to add as many features as possible and as a result delivering a half baked low quality product, the team concentrated on developing only must-have features, on performance, and on very high quality. As a result, the product had a very limited set of features, but it delivered everything it promised, and it was simple, very fast, and rock solid. As such, it was a success.

It would be quite easy to provide examples of over-engineered projects that tried to "do it right the first time" and miserably failed, but this is beyond the scope of this post...


Billing Module (Fee) for School Management System

In addition to seconding Joel Brown's comment I would like to suggest a couple things to help.

If this is for internal use only start with open source accounting and ERP db's.  For example, LedgerSMB or Adempiere.  Also do this if you are releasing open source.  If you are not releasing open source, consider interoperating with such software for billing.  I would be happy to help (conflict of interest alert).
Look specifically at batch processing and review requirements.  This allows human review as to how much money is owed before the money hits the books.

One thing we are doing in LedgerSMB is to have the possibility of "template transactions" which can then be copied out and filled in (possibly by automatic processes).

Accounting is a large field but if you are handling billing you really need to know it.

Along these lines, I don't really see anything wrong with what you have posted but it is quite incomplete.  You need line items for the invoices, and you need line items for the payments.  In this way it becomes a lot simpler to track how much is owed.  You may also want to separate out students from customers in case one parent has multiple children enrolled in the school.


Possible solution.

Each thread which will obtain the values from "sequence generator" have some customer_id value it will use when ask for next value. Additionally, it generates some random and unique (guaranteed!) value when starting (if all threads works in one OS instance, it can be thread's PID, for example, if not, it can be PID+IP or something else - but with 100% no collision). This random allows to distinguish the threads used the same customer_id value.

You create the my_seq table as shown above:

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer);

Additionally you create the table looks like

CREATE TABLE my_gen (
customer_id integer,
session_id integer,
generated_value integer,
UNIQUE KEY idx (customer_id,generated_value)
);

When a thread needs next sequence value, it tries to obtain it by the next code:

SELECT last_value+increment_by
    INTO @next_val
    FROM my_seq
    WHERE customer_id = @customer_id;

In that timepoint any parallel thread can obtain the same value... to avoid it, the threat tries to secure this value by:

INSERT IGNORE INTO my_gen (customer_id, session_id, generated_value)
    VALUES (@customer_id, @session_id, @next_val);

Only one thread used the same @customer_id can insert the record to this table with generated value - all another will not because of unique key.

And now the thread checks was the secure process for that value successfull:

SELECT *
FROM my_gen
WHERE customer_id = @customer_id
  AND session_id = @session_id
  AND generated_value = @next_val;

If 1 record returns - the secure process is successfull, the thread can use obtained value, and it updates my_seq table:

UPDATE my_seq
SET last_value = @next_val
WHERE customer_id = @customer_id;

If 0 records returns - the secure process is unsuccessfull, obtained value was obtained, secured and used by another thread with the same customer_id... and this thread needs to repeat the whole process of generate sequence value.

You may to assemble this operations into one stored procedure or function. Moreover, you can organize the cycle directly into SP which will execute till the next value obtained... or any amount of attempts performed to avoid infinite loop.

Or simply lock the my_seq table exclusive for read and write before first read and till last update - no parallel thread can do something to interfere in that case.


Restore from local disk vs SAN on physical machine (SQL Server Linux)

I quit the job, so I don't know what they did about it. Before I left, it was decided that a SAN admin will investigate if their storage was slow or if they were facing other challenges.
Closing this question as there is no way for me to track the issue any further.


I just added GROP BY products.id and it is fine now.  dumb, dumb.

IF this isn't proper or a better solution please feel free to post.

SELECT products.id,
                    products.title AS product_title,
                    products.code,
                    products.created,
                    products.modified,
                    products.active,
                    attributes.title AS attr_title,
                    attribute_entries.title AS attr_entry_title
                    FROM products
                    INNER JOIN attributes ON attributes.model_id = products.id
                    INNER JOIN attribute_entries ON attribute_entries.FK_attribute_id = attributes.id WHERE ( products.id LIKE '%" . $model['value'] . "%' OR products.code LIKE '%" . $model['value'] . "%' OR products.title LIKE '%" . $model['value'] . "%') GROUP BY products.id limit 10


The easiest way would be to send the email with differing subject lines. If you just want different database in different conversations make sure the database name is included in the subject line. If you want a new conversation for each email sent, include a timestamp in the subject line.


This is essentially a hardware capacity planning question. The problem with them is that if you give enough details that we can tell you what hardware you need, it'll become "too localized" to your particular set of circumstances. That said, we've got a number of pseudo-answers already written to help you:

Can you help me with my capacity planning?
How do you do Load Testing and Capacity Planning for Databases
How do you do Load Testing and Capacity Planning for Web Sites

Those are from Server Fault's list of Canonical Question/Answers.

They're not all directly related to what you're asking for, but tangentially similar. It boils down to the fact that this is your job; if you want to get it right you really either need to learn how to do it, or find a consultant who does.

One last thing; the TCO of building your own server from components is always higher on the average than buying a prebuilt server from HP or Dell. I happen to prefer HP, but the SE Sites run on Dell and the site seems to work quite well. It will be cheaper up front to build your own; but warranty, compatibility, scalability, and support will all chew that cost savings like a hungry dog with a fresh steak.

Edit:
Since you're discussing RAID levels in the comments above: What are the different widely used RAID levels and when should I consider them?


Constraints will basically allow you to enforce data integrity - limiting the values that may be inserted into a column and there by enforcing business rules. The database engine (SQL Server) will automatically enforce integrity of a database based on the constraints defined
e.g. NOT NULL, CHECK constraints, UNIQUE constraints,  PRIMARY KEY constraints, FOREIGN KEY Constraints, etc.

For Rule Engine,

  A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass or fail.

I will just point you to some Microsoft articles and other useful links (as I am not a pro in this area).

Building a Rule Engine with SQL Server
Choosing a Rules Engine Design
Creating a Rules Engine


Why would an index covering only half of primary key be useful?

The Database Tuning Advisor does not always make sensible suggestions. It is always a good idea to review any suggestions made (by any tool more that matter).

In general, the cost of an index seek is directly related to the depth of the B+Tree that makes up that index. Seeking in an index that is 8 levels deep is 4 times as expensive as seeking in an index that is only 2 levels deep. The depth of the index is strongly correlated with the length of the index key(s). Other important factors are uniqueness of the index, the length of the clustered index key and wether there are any included columns.

If you create an index on one column you generally get a index less deep than if you create an index on that same column and an additional column. However, in your case, as the index on ID-1 is not declared unique (it can't be), the clustered index key automatically becomes part of the key. Assuming there are no other columns, that additional index will be an exact copy of the clustered index. If you however have additional columns in the table, you might end up with a small difference in index depth. But even then it is usually not worth creating the index.

The difference becomes bigger if you have a lot of additional columns and you are looking at queries that would scan the new index like this one:

SELECT [ID-1],COUNT(1) AS Cnt
  FROM [dbo].[JunctionTable]
 GROUP BY [ID-1];

As all those additional column are not part of the index the scan has to go through a lot less pages. If you for example had two additional DATETIME columns in that table, the index scan speed would probably double with that new index. But you would have to run this query very often for the index to be worth the additional maintenance overhead.


SQL Server AG in primary site with separate WSFC AG in secondary site

1.Can a separate AG be a 'recipient' (restore target) of Log Shipping? In other words, can I have the primary server in a separate Availability Group be applying log backups (from log shipping) and when ready to cut over to this secondary AG, issue a RESTORE BACKUP WITH RECOVERY?

No, databases involved in an availability cannot be restored or recovered unless first removed from the availability group. You can log ship to a database NOT in an availability group, however, just fine.

  2.Ideally, it would be preferable to be able to more easily 'fail back' to the primary site. If we had a WSFC spanning both sites and the same AG on said cluster, I gather this would be far easier to accomplish. Network latency will be a concern.

All databases that belong to an AG must exist in side the SAME WSFC thus it would have to be "spanned". If network latency is a concern, work with your networking team to have dedicated bandwidth and QoS rules to make sure your traffic and the cluster traffic is getting through on time. Watch how often large bursts of log are generated, like rebuilding a very large index, and try to mitigate this as much as possible.


I have a 4-year-old post on how to schedule FLUSH HOSTS:

How do you tell which host is close to being blocked in MySQL?

In that old post, I mentioned two techniques

TECHNIQUE #1 : Use MySQL Event (See the event code from my old post)
TECHNIQUE #2 : Run perl script (See the perl code from my old post)

Setup Parameters to Heartbeat
Connect to the mysqld
Hold DB Connection open indefinitely
Run FLUSH HOSTS; on the set schedule

In either case, FLUSH HOSTS; can be executed regularly.

Notwithstanding, you should be polling status variables

SELECT * FROM information_schema.global_status
WHERE variable_name LIKE '%connect%';

to monitor connections attempts, dropped connections, and thing like these.


log-slave-updates=true

should be true on both masters. Because if both masters are accepting updates then the other passive master has to be able to update itself.
You can take backups of the bin logs whenever you want.
As to other options, I use the MIXED option for the binglog_format to allow for ROW based or STATEMENT based updates

binlog_format       = MIXED

This is because STATEMENT based replication doesn't cover all updates. But STATEMENT based replication is faster. With MIXED MySQL can switch for you when appropriate.


SQL Server transactional replication is out of sync

check it out the scripts on this question:

Replication Monitor Information using T-SQL

Just make sure you run them in the right place - publisher, distributor or subscriber.

  Recently, I noticed that the data in my subscriber is not synced up
  with the database on the publisher server.

first thing is to find out what is there to be replicated that has not been, and why.
this will give you a starting point:

from Querying msdistribution_status slow

--this is to be run on your distributor server if you have one, distribution database

USE distribution;

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

      SELECT t.article_id,s.agent_id,
    'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),
    'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
    FROM (SELECT article_id,publisher_database_id, xact_seqno
    FROM MSrepl_commands (NOLOCK) ) as t
    JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
    (NOLOCK) ) AS s
    ON (t.article_id = s.article_id AND
    t.publisher_database_id=s.publisher_database_id )
    JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
    MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
    ON (h.agent_id=s.agent_id)
    GROUP BY t.article_id,s.agent_id

and this one:

USE distribution;

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno

-- this is to be run on your publication server

this will find out what is pending, between the data you get here, and those queries above,
it will give you some info

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
exec sp_replmonitorsubscriptionpendingcmds
 @publisher ='ctdb1',
 @publisher_db = 'APIA_Repl_Pub',
 @publication ='APIA_Repl_Pub', --'Enter name of publication',
 @subscriber ='SQLPROD',
 @subscriber_db ='APIA_Repl_Sub',
 @subscription_type ='0' --0 for push and 1 for pull

  I have tried restarting the distribution agent job, but this doesn't
  seem to fix the issue. I am not really sure where to troubleshoot
  next.

there should be only one process reading the log and marking the transactions as replicated otherwise you get errors like this one:

-- Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1 [Batch Start Line 212]
--Only one Log Reader Agent or log-related procedure
--(sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time.
--If you executed a log-related procedure,
--drop the connection over which the procedure was executed or execute sp_replflush
--over that connection before starting the Log Reader Agent or executing another log-related procedure.

you mention:

  Also, it is worth mentioning that the subscriber is running on a
  secondary SQL instance, on the same physical server as the publisher,
  so I would think latency should be low.

that has an impact on the transaction log.
I understand that you are talking about availability groups?
check these scripts to see if it is healthy.

what is the size of your transactions?

what is the size and VLFs of your transaction logs?

--===============================================
--check the transaction log size for all dbs and percent available
--===============================================
DECLARE @log_stats TABLE (
    [DBName] [nvarchar](128) NOT NULL,
    log_size decimal(10,5),
    log_used decimal(10,5),
    [status] int
 PRIMARY KEY CLUSTERED
(
    [DBName] ASC
)
)

          INSERT INTO @log_stats(

                            [DBName],
                            log_size,
                            log_used,
                            [status]

          )
EXEC('SET NOCOUNT ON;DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;')

SELECT [DBName]
      ,log_size=CAST (log_size AS NUMERIC (10,2))
      ,log_used=CAST (log_used AS NUMERIC (10,2))
      ,[status]
FROM @log_stats

--===============================================
-- DBCC LOGINFO into table
--===============================================
DECLARE @log_stats TABLE (
RecoveryUnitId  bigint,
FileId int,
FileSize bigint,
StartOffset bigint,
FSeqNo  int,
Status  int,
Parity  int,
CreateLSN int
)

          INSERT INTO @log_stats(

RecoveryUnitId, FileId, FileSize,   StartOffset,    FSeqNo, Status, Parity, CreateLSN

          )
EXEC('SET NOCOUNT ON;DBCC LOGINFO(''YOUR PUBLICATION DATABASE GOES HERE'') WITH NO_INFOMSGS')
SELECT * FROM @log_stats

how it works?

The reader thread of the Log Reader executes sp_replcmds to pull commands from the transaction log.
The stored procedures below can be executed in SQL Server Management Studio to track time required to pull next pending replicated transaction.

How long do these command take to execute?
How many commands were returned?

Scanning a large transaction log for a single transaction containing a million+ row modifications may take 20+ minutes to complete.

The execution time for this query approximates the startup time for the Log Reader to replicate this same transaction.
 Display 1st pending transaction metadata such as publication_id, article id, xactid, and command_type.

Is this command part of normal database operation or an unexpected large batch of commands?

 --Return all commands from the next pending transaction
 sp_replcmds @maxtrans = 1
 GO

 --Return all commands for the next 500 transactions
 sp_replcmds @maxtrans = 500
 GO
 sp_replflush
 GO

this will give you a good starting point, however, it is a complex environment, and there are things not so clear from your question.

I tried to answer the best I could, if you let me know how it goes, I can improve my answer.


You have to surround the % character with single quotes

DROP USER user@'%';

or put the user in single quotes as well

DROP USER 'user'@'%';

From the CLI, you would run something like this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="DROP USER 'user'@'%'"
mysql ${MYSQL_CONN} -ANe"${SQL}"

An alternative method would be delete it from mysql.user

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="DELETE FROM mysql.user"
SQL="${SQL} WHERE user='user'"
SQL="${SQL} AND host='%';"
SQL="${SQL} FLUSH PRIVILEGES;"
mysql ${MYSQL_CONN} -ANe"${SQL}"


How to save mongodb data sub data more properly in this case?

AFAIK, MongoDB queries are more optimized for your second approach.

with the first approach you will probably have to do a query like:

db.businesses.find({'Reviews.gusyanto': {$exists: true}})

Which is less efficient than using:

db.businesses.find({'Reviews.userid': 'gusyanto'})


Mirroring - Server network address cannot be reached

Try some basic connectivity tests.

Verify that 5022, 5023 and 5024 are listening.
Verify that the server name you are using is correct.

From the command line :

netstat -an

On my server, you can see that 5022 is listening.

Next make sure that you can connect to those ports via telnet

telnet fully-qualified-server-name 5022

As it mentions in the Note section of the Mirroring Properties GUI, just below the witness field, the server names have to be fully qualified tcp addresses.

You should just see a black screen.  In this example I chose a name that would cause a connection failure.  If you see "Could not open connection", then the server(s) defined as mirror, principal and witness aren't reachable or you are not using the right name.

The telnet client can be added under Features in Windows 2008.

In Windows 2008, when you right click on Computer, you can see the full computer name.
You should be able to ping it as well from the command line.  ex: ping myservername

Update

Please run the following queries on each SQL Server instance and put the results in your question.  Many of these troubleshooting tips come from: http://msdn.microsoft.com/en-us/library/ms189127.aspx

Show tcp endpoints

SELECT type_desc, port FROM sys.tcp_endpoints;

Display status of mirroring endpoints

SELECT state_desc FROM sys.database_mirroring_endpoints;

Check that the ROLE is correct

SELECT role FROM sys.database_mirroring_endpoints;

Display permissions to endpoints

SELECT EP.name, SP.STATE,
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
      AS GRANTOR,
   SP.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
      AS GRANTEE
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee;
GO

The login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQL statement.

Example output:

name    STATE   GRANTOR PERMISSION  GRANTEE
TSQL Local Machine  G   sqladmin    CO      public
TSQL Named Pipes    G   sqladmin    CO      public
TSQL Default TCP    G   sqladmin    CO      public
TSQL Default VIA    G   sqladmin    CO      public
Mirroring   G   SERVERNAME\Grantor  CO      SERVERNAME\Grantee

Grantor is the account that assigned (CO) connect permission, Grantee is the account that has connect permission

From the command line run ipconfig /all and note what Host Name returns.


You are not explaining why your PIVOT query did not work for you, although it is not too hard to guess if your Response sample is representative enough of the data in that table in general. You have two sets of responses for User 2 but your query picks up only one.

As your expected output shows you want the query to return both sets, you need to teach it to distinguish between various sets of answers by the same user. One way is to use the ROW_NUMBER analytic function:

SELECT
  UserId,
  FromDate,
  ToDate,
  Project,
  Comment
FROM
  (
    SELECT
      r.UserId,
      r.Text,
      c.ColumnName,
      SetNo = ROW_NUMBER() OVER (PARTITION BY r.UserId, r.ColumnId ORDER BY r.Id)
    FROM
      dbo.Columns AS c
      INNER JOIN dbo.Response AS r ON c.Id = r.ColumnId
  ) AS derived
  PIVOT
  (
    MAX(Text)
    FOR ColumnName IN (FromDate, ToDate, Project, Comment)
  ) AS p
;

The query above makes a few assumptions:

every set of answers consists of the same set of ColumnIds;
related responses are arranged together (based on the order of Id), or, at least, if one FromDate response comes later than another FromDate response, then its related ToDate response also comes later than the ToDate response related to the previous FromDate.

If there are special cases in the real data (e.g. some sets may be incomplete), a different approach may be required.


How to automatically drop and restore PostgreSQL database

This is going to depend on how you are doing your backups and if you are doing restores on the same database cluster that you are using for your live data.

The simplest case ( for small databases ) is that you are dumping your databases with pg_dump and storing them in the cloud as SQL files, possibly compressed.

Once you have backups you can load them into a database cluster using psql; you have various options in how you do this whether or not you specify that pg_dump drops entities before creating them and whether to include database creation statements.

If you are wanting to ensure the integrity of your backups, without overwriting a database you are using ( doing the restore on the same cluster you dumped from ) you want to dump the database without the create database statements and you want to have the restore script create a database with a different name.

There are a lot of variables in play in terms of what you can include in the dump and a fair bit of subtlety about ownership and permissions that are going to be dependent on what sort of restore you are doing and what roles are going to need access.


Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2

The job is "owned" by a maintenance plan in the following table, and I don't believe the names have to match. So you may want to check the following location:

SELECT name,id,description
  FROM msdb..sysssispackages
  WHERE name = N'Clean Up History';

So you could rename that one, but it may cause problems later (some automated process may need to match on that name, or simply re-create it if it doesn't find it). So a much safer approach would be to use a different name for your own maintenance plan...