Does sys.sql_logins.is_policy_checked mean that the policy has been checked?

No.

While the documentation currently has the following arguably ambiguous statement about what this flag means:

  Password policy is checked.

What it really means, and should say, is that the flag serves two purposes:

  The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last set, and (b) the password was specified in plain text (not with a hash).
  The password policy will be checked the next time the policy is set, but only if (a) the password policy is enabled at that time, and (b) the password is specified in plain text (not with a hash).

(And note that "the policy" also refers to enforcing expiration and the fact that the user must change the password on next login, but since the complexity is typically the focus of auditing operations, I'm going to focus only on that aspect.)

The is_policy_checked bit is set to 1 if CHECK_POLICY = ON during a CREATE LOGIN or ALTER LOGIN event, even if the policy isn't checked at the time. As you can probably gather from above, this check does not happen in these scenarios:

The password is specified using the HASHED keyword (a very common tactic when migrating logins between servers or copying logins to log shipped / mirrored / AG secondaries). It is obviously not possible to check the complexity of the password if you don't have the pre-hashed value.
The local password complexity policy is not enabled at the time the event occurs.
Not covered in my proposed rewording above, but you can ALTER LOGIN without setting a new password, and still change the flag (thanks to @AMtwo for illustrating this). I suspect this may have been done by clever people trying to fool an auditor.

These problems are all easy to demonstrate.

Since most people I've talked to about this have always assumed that is_policy_checked actually means that the current password meets the current password policy, I think it's important that something changes here so that users have the right expectations and understand that this flag does not necessarily mean all is well. At the very least, the documentation should be updated to reflect reality, somewhat like I have pointed out above. But there are other things that can be done, too.

A warning could be raised if CHECK_POLICY = ON is specified but the policy can't, in fact, be checked (either because the password is specified with a hash, or because the password policy has been disabled, or because the command is a simple attempt to bypass or set the flag, e.g. ALTER LOGIN blat WITH CHECK_POLICY = ON;).
CHECK_POLICY could be deprecated, in favor of ACTIVELY_CHECK_POLICY and perhaps CHECK_POLICY_ON_NEXT_CHANGE. The columns in sys.sql_logins should be policy_has_been_checked and policy_will_be_checked. I am not married to these names, but they are a lot more accurate than the current wording.
If I choose ACTIVELY_CHECK_POLICY = ON and the policy can't be checked during the execution of the command, I should receive an error message and the flag should not be set to 1 (or even the login creation or password change should not succeed).
I don't think it makes sense in this case to continue with the current behavior, where I can specify that I want the policy to be checked, but even if it can't, the password is allowed and the login is created/altered (this is bad, IMHO, regardless of the state of the flag after the fact - but at least if it were set to 0, such bypasses could be identified).

Today there is no reliable way - without manually changing their passwords to something you know is secure - to audit your SQL logins and be confident that they all meet your complexity policy. In this day and age of ever-increasing data, more and more data breaches, and the obvious need to secure systems tighter and tighter, this is a problem that needs to be addressed. I've blogged about this and created a Connect item about it:

Please help me fix a big security loophole
CHECK_POLICY is a no-op with CREATE LOGIN ... PASSWORD ... HASHED

I encourage you to vote on the Connect item and, more importantly, be sure you're not auditing your systems with false perceptions about how this DDL option and metadata work.

Please don't brush this aside as a "non-issue" because you are perfectly comfortable with how it works and already know that the flag can't be trusted - you are not the user I'm worried about; it's everyone else.


CREATE PLUGGABLE DATABASE REFRESH MODE MANUAL from remote fails with ORA-00922

create_pdb_clone::=

12.1 syntax:

12.2 syntax:

pdb_refresh_mode is available starting with version 12.2, it is not available in your version.


Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel?

Like MguerraTorres suggests, SSRS has probably got confused somewhere, and the value suggests it's getting cast to some kind of floating point datatype somewhere along the line.

Try checking the datatype SSRS is assigning by looking at the xml in the .rdl file raw text.

for a simple bigint column I'm seeing an SSRS field definition something like this:

<Field Name="i">
  <DataField>i</DataField>
  <rd:TypeName>System.Int64</rd:TypeName>
</Field>

You might then be able to edit the datatype directly in that text stanza and trick SSRS into sorting itself out


How do i store hash indices on my database

There are several considerations:

Consistency: if you store everything in the database, consistency between database entries and the hash are automatically guaranteed by the transaction system. That simplifies architecture and development.

Read/Write Performance: reading bigger data from the database performs much worse than reading files from the file system.

Backup/Maintenance: large databases are more difficult to back up than large file systems.

Search performance: unless you build special GiST index support for operators on these hashes (which requires C programming and dealing with the guts of PostgreSQL), you won't be able to efficiently search for the hashes in the database.


Logging is always a good use case for non relational stores (text book example).

You've only given us one query pattern, so going by that I would create a single compound index, equality to inequality.

I would suggest reading up on indexes at:

MongoDB Index Introduction

and

MongoDB FAQ: Indexes


It doesn't look like it. pg_create_restore_point just calls XLogRestorePoint(...) after a bunch of sanity checking, to write the restore point record to the transaction logs. Restore point information is only referenced in recoveryStopsAfter(...), which is part of recovery.

I'm not really sure how PostgreSQL would expose restore points in the catalogs anyway. How would it know a restore point was obsolete and could be removed?

I suggest tracking this yourself using a regular table, where your system deletes from it when it purges old WAL. Your table should contain the restore point name and the LSN (xlog location) at which the point was created. That way you can delete old restore points when you purge the corresponding backups.

So, something like:

INSERT INTO my_restore_points (rpname, rplsn)
VALUES
('some_restore_point', pg_create_restore_point('some_restore_point'))

The other way to do it would be to use pg_xlogdump to scan xlogs for restore points, but that'll be slow and clumsy. Plus I'm not sure 9.2's pg_xlogdump is good enough.


DB2 LUW: Should temporary tablespaces share bufferpools with other tablespaces?

First of all, you must have a bufferpool that matches the page size of the tablespace. By that token if the temporary tablespace is the only one with the 32K page size then it will have the bufferpool for its exclusive use.

If you have other tablespaces with the 32K page size, only monitoring the system performance will tell you if there may be a benefit from a separate temp space bufferpool.

You can use select * from sysibmadm.mon_bp_utilization to look at the bufferpool hit ratios and select * from table (mon_get_bufferpool(NULL,NULL)) to check page cleaner activity (POOL_NO_VICTIM_BUFFER and POOL_DRTY_PG_STEAL_CLNS should ideally be 0). If you see that a drop in the bufferpool hit ratio or high dirty page contention coincides with the temporary space use (a spike in POOL_TEMP_DATA_L_READS in select * from table (mon_get_tablespace(NULL,NULL))), creating a separate bufferpool of an appropriate size for the temporary tablespace in question might be useful.


Mongodb find() operation sometimes extremely slow

You are not using the correct index. The current index will bring the results sorted but it have to perform a full collection scan. You need an index like:

{"category_memberships.node":1,"ships_to_australia":1,"ps":1,"salesRate":-1}

This would limit the search and sorts at the same time.

The query execution variation has to do with the hotset and collection locking.


Log size is different between primary and secondary node with log_since_last_log_backup_mb

I'd expect it to be NULL on the primary node if a backup has never happened on this node. You can confirm this by checking when the last log backup happened for those databases on the primary node:

SELECT   d.name,
         d.dbid,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
WHERE d.dbid in (19,34,21) --from your primary node
GROUP BY d.name, d.dbid
ORDER BY backup_finish_date DESC

And then on the secondary:

SELECT   d.name,
         d.dbid,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
WHERE d.dbid in (19,34,33) --from your secondary node
GROUP BY d.name, d.dbid
ORDER BY backup_finish_date DESC


Availability Group reporting disconnected replica

This question is very old and likely no more troubleshooting will be possible, however for those of you stumbling upon this question having the same issue the following may be of some help. This isn't meant to be a complete list of all issues that could occur, but it will hit the main ones that do.

When the secondary replica is marked as disconnected by the primary, this means the primary hasn't heard or can't contact the secondary replica over the database mirroring endpoint. There are various reasons for this:

Endpoint configuration

One of the most common causes are misconfigured endpoints for the Database Mirroring role. There are various options and configurations that can be used and sometimes, especially when created by hand, those configuration options do not match. Take the OPs example:

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING (ROLE=ALL)

This creates an endpoint called dbm_endpoint on tcp port 5022 for database mirroring. However it leaves out quite a bit of configuration options to which we assume are defaulted but we really do not know. This means the default options such as Windows authentication using either Kerberos or NTLM, it also doesn't specify which IP address (v4, v6, interface) is used, nor does it specify encryption options to which the default is REQUIRED and depending on the version of SQL Serve either RC4 or AES.

That is quite the assumption to make, especially when it isn't working. If you're not sure what the endpoint is set to, join sys.tcp_endpoints with sys.database_mirroring_endpoints to turn the resulting single row to individual configuration options.

There are advanced configurations, for example, that use certificates since they can't use Windows authentication. We don't know if the OP has a proper domain and if these servers are joined. Again, the assumption based off the endpoints is that they are, however we don't know for sure. When using certificates the largest issue is that the same certificate is used on multiple replicas yet the private key is not exported when copying the certificate to import to the replicas. Since the endpoints use the private/public key pair, using the same certificate will require that endpoint at some time to use the private key. Make sure the certificate is exported and imported with both keys if you're using it on multiple replicas.

When specifying the IP Address, the default is ANY which could be any of the IPv4 or IPv6 addresses. Though most places don't register or use IPv6 addresses, I do see it as an issue every once and a while. Checking to see what addresses are registered to a host in your DNS can help narrow the issue. You can also setup a network capture and filter based on the port. Every once and a while an IPv6 address is used and it's improperly setup, disabled, or firewalled on other servers but is returned via DNS.

Availability Group Replica Specification

When creating the availability group each replica will need to be specified and as such will need an endpoint url which will define the communications port (database mirroring endpoint).

Taking the OPs example:

REPLICA ON
        'SQL1' WITH
            (
                ENDPOINT_URL = 'TCP://sql1.sql.sandbox.net:7022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            ),

This replica, called SQL1 has a TCP endpoint on sql1.sql.sandbox.net port 7022. the address seems to be a FQDN and should be checked that the proper DNS lookups, (if using) Kerberos SPNs, and firewall rules exist or are created. If there are multiple interfaces or addresses that are returned on a lookup of the address, then it may make sense to narrow it down to a specific address in the endpoint url for testing or to narrow it down in the mirroring endpoint specification.

SQL Server Permissions

One of the last surface areas for the disconnected issues to appear is the endpoint permissions on each replica. Since each endpoint the account will need the CONNECT permission (similar if using certificates) in order to use the endpoints. This is a fairly common issue, so common that this is marked in the errorlog and gives you the fix in the error message which is to grant the connect permission to the endpoint.

Windows Clustering

Since the replica manager replies upon Windows Server Failover Clustering to be working (not counting Linux, Docker, or Read-Scale) in order to start up, do make sure the cluster service is running on each replica and each node shows an up status in the cluster (Powershell, Failover Cluster Manager, etc.). If the cluster service is not running, then the manager will stop until it comes back up - this will be logged in the errorlog and should be very evident.

Troubleshooting Methodology

When nothing else works or shows up, what do you do?

Availability groups are a distributed system, this means there are many individual parts (replicas) that are distributed among many areas (databases) that function through a common set of protocols (availability groups). Given this, the troubleshooting methodology differs from typical single instance or clustered instance troubleshooting.

Each part needs to be checked against itself and against the overall metadata available. Additionally, each part needs to have the intermediary items checked against each other intermediary (such as firewalls, network cards, load balancers, etc.).

Start by checking the errorlog on each replica as only some errors (due to where the error occurs) are only applicable when the replica is primary or secondary. After an overview of each errorlog, this should point in a direction - if it does not, then most likely the issue is under SQL Server which could mean networking, firewall, OS, etc. System and application logs can be helpful here as a next step.

Look out for NDIS drivers installed on the system as they could be blocking traffic or incorrectly working and dropping traffic. Additionally, do check the interface diagnostics for the network cards as things such as dropped/discarded packets may be an issue if the disconnected state is intermittent.

When all else fails, check every piece of metadata against the other and make sure all match and are the same. In the OPs metadata specified above, one port was on 7022 and the other on 5022. While this in of itself is not incorrect and can be used, it does make issues in other places such as firewall rules where someone can easily misread the port or assume and not read that the ports are the same.

Depending upon skill level and knowledge, a network trace from the primary side and the secondary side would be ideal. This can show the packets leaving the primary/secondary and hopefully arriving at the opposite side. If it does not, an intermediary is not configured correctly - look for firewalls, load balancers, routers, etc., that are stopping the traffic.


A way to resolve this would be to put the joining of things with tags into separate tables.  You would have your original tables users, posts, meta-tags and perhaps items.  In addition, you would have user-tags, post-tags, and perhaps item-tags.  The later three tables would only have the keys from the specific type table (e.g. user_id) and the key from the meta-tags table.

This design would also allow you to have other attributes to a tag that apply across all the base tables.


Primary keys must be unique. As long as any particular user/user_id can't become more than a single seller/buyer (each), you will be fine. Whether or not that restriction does hold, depends on your domain. It would, e.g., fail, if a user can run multiple shops with each shop's owner being (considered) a distinct seller. (You might, e.g., want to attach contact information to a seller, which could be different from shop to shop - but specific to the seller, not to the shop. Just think of co-owned businesses…)
In case of doubt, you might want to play safe, and not make the foreign key the primary key to save you from the trouble of propagating a redesign through your schema some time in the future.

As for MySQL trying to attempt to fill in the gaps: If it is a foreign key, you(r application) will be the only one to insert records. Thus: No.


Getting the error "Can't rename a table because it is published for replication" while it's not

sp_droparticle will probably do the trick. Details: https://msdn.microsoft.com/en-us/library/ms173832(v=sql.105).aspx


Select date based on the daylight saving periods

MySQL documentation states:

  MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored.

If you want some other behavior use DATETIME as indicated.


SQL schema for movie rating and seenlist

What does created mean?  When the record was created?  If so, of what use is that?  Perhaps it means when_seen (or rated)?

I see no necessity to separate the two tables.

Since seen is not "optional", and rating is, consider

rating TINYINT UNSIGNED NULL  COMMENT '1..10'

Where the column is NULL until the user specifies a rating.

NULL has the advantage that

SELECT movie_id, AVG(rating)
    ...

will ignore the NULL values and get the 'correct' average.


SQL datatype choice for flagging a read/ unread message?

Performance of a particular design depends on the distribution of data to a large extent, and the access path. Are you expecting most messages to have the flag, or most messages not to? From the docs:

  My tests show that a table scan often
  starts to perform better than a
  nonclustered index access when at
  least 10 percent of the rows are
  selected. I also found that the
  optimizer switches from nonclustered
  index access to table scan prematurely
  (i.e., when nonclustered index access
  still shows better response time than
  the corresponding table scan). In many
  cases, the optimizer forces a table
  scan for queries with result sets of
  approximately 5 percent, although the
  table scan becomes more efficient than
  index access at selectivities of 8 to
  10 percent.

And of course, if there are any other predicates on the query, and the clustered index if any. For example, are you likely to want to access all unread messages within a certain timeframe? Or sent to/from a particular user? How big are the message bodies and are they stored inline? These are just rhetorical questions, mind.

So which is better, a BIT and a DATETIME or just a DATETIME for performance? You will have to benchmark with some representative data and access patterns to find out. I'm afraid this one can't be answered with just theory. But you can of course create the table with the boolean in and just not use it if you find the latter is better - it only adds an overhead of 1 byte per row.

If you were asking the opposite question, how to efficiently query the not-NULL rows, I would have said use a filtered index.


when selecting from multiple tables I get a multiplication of the records

The part of the filter that performs the join is t1.mobileno = t2.mobile AND t2.mobile = t3.mobileno. For that to be sensible, it is likely that you need two of the following three statements to be true:

mobileno is unique in traffic
mobile is unique in details
mobileno is unique in registernumbers

Because the join is returning more rows than you expect, I guess this is not the case. For further help we'll need to know the DDL for the tables (in particular the primary keys)

---EDIT

on second thoughts, I think

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)

needs to have some more brackets:

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND ( latitude > 48
           OR (latitude < 52 AND longitude > 2)
           OR (latitude > 52 AND longitude < 2) )

although some of the conditions seems to be redundant


Microsoft SQL Server how can i detach localdb to run on any computer

For LocalDB you'll need to install SQL Server Express on all the machines where you want to run your application. If you don't want to do that, have a look on SQL CE. That allows you to have only DB file moved about and all the required libraries can be included in your project.


How and when does SQL Agent update next_run_date/next_run_time values?

Short Answer
It looks like the data in msdb.dbo.sysjobschedules is updated by a background thread in SQL Agent, identified as SQLAgent - Schedule Saver, every 20 minutes (or less frequently, if xp_sqlagent_notify has not been called and no jobs have run in the meantime).
For more accurate information, look at next_scheduled_run_date in msdb.dbo.sysjobactivity. This is updated in real-time any time a job is changed or a job has run. As an added bonus, the sysjobactivity stores the data the right way (as a datetime column), making it a lot easier to work with than those stupid INTs.
That's the short answer:

It could be up to 20 minutes before sysjobschedules reflects the truth; however, sysjobactivity will always be up to date. If you want a lot more details about this, or how I figured it out...

Long Answer
If you care to follow the rabbit for a moment, when you call sp_add_jobschedule, this chain of events is set into motion:
msdb.dbo.sp_add_jobschedule == calls ==> msdb.dbo.sp_add_schedule
                                         msdb.dbo.sp_attach_schedule

msdb.dbo.sp_attach_schedule == calls ==> msdb.dbo.sp_sqlagent_notify

msdb.dbo.sp_sqlagent_notify == calls ==> msdb.dbo.xp_sqlagent_notify

Now, we can't chase the rabbit any further, because we can't really peek into what xp_sqlagent_notify does. But I think we can presume that this extended procedure interacts with the Agent service and tells it that there has been a change to this specific job and schedule. By running a server-side trace we can see that, immediately, the following dynamic SQL is called by SQL Agent:
exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME
  SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2)
  UPDATE msdb.dbo.sysjobactivity
    SET next_scheduled_run_date = @nextScheduledRunDate
    WHERE session_id = @P3 AND job_id = @P4',
N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',
20120819,181600,5,'36924B24-9706-4FD7-8B3A-1F9F0BECB52C'

It seems that sysjobactivity is updated immediately, and sysjobschedules is only updated on a schedule. If we change the new schedule to be once a day, e.g.
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,

We still see the immediate update to sysjobactivity as above, and then another update after the job is finished. Various updates come from background and other threads within SQL Agent, e.g.:
SQLAgent - Job Manager
SQLAgent - Update job activity
SQLAgent - Job invocation engine
SQLAgent - Schedule Saver

A background thread (the "Schedule Saver" thread) eventually comes around and updates sysjobschedules; from my initial investigation it appears this is every 20 minutes, and only happens if xp_sqlagent_notify has been called due to a change made to a job since the last time it ran (I did not perform any further testing to see what happens if one job has been changed and another has been run, if the "Schedule Saver" thread updates both - I suspect it must, but will leave that as an exercise to the reader).
I am not sure if the 20-minute cycle is offset from when SQL Agent starts, or from midnight, or from something machine-specific. On two different instances on the same physical server, the "Schedule Saver" thread updated sysjobschedules, on both instances, at almost the exact same time - 18:31:37 & 18:51:37 on one, and 18:31:39 & 18:51:39 on the other. I did not start SQL Server Agent at the same time on these servers, but there is a remote possibility that the start times happened to be 20 minutes offset. I doubt it, but I don't have time right now to confirm by restarting Agent on one of them and waiting for more updates to happen.
I know who did it, and when it happened, because I placed a trigger there and captured it, in case I couldn't find it in the trace, or I inadvertently filtered it out.
CREATE TABLE dbo.JobAudit
(
  [action] CHAR(1),
  [table] CHAR(1),
  hostname SYSNAME NOT NULL DEFAULT HOST_NAME(),
  appname SYSNAME  NOT NULL DEFAULT PROGRAM_NAME(),
  dt DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

CREATE TRIGGER dbo.schedule1 ON dbo.sysjobactivity FOR INSERT
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'I', 'A';
GO
CREATE TRIGGER dbo.schedule2 ON dbo.sysjobactivity FOR UPDATE
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'U', 'A';
GO
CREATE TRIGGER dbo.schedule3 ON dbo.sysjobschedules FOR INSERT
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'I', 'S';
GO
CREATE TRIGGER dbo.schedule4 ON dbo.sysjobschedules FOR UPDATE
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'U', 'S';
GO

That said, it is not hard to catch with a standard trace, this one even comes through as non-dynamic DML:
UPDATE msdb.dbo.sysjobschedules
  SET next_run_date = 20120817,
      next_run_time = 20000
 WHERE (job_id = 0xB87B329BFBF7BA40B30D9B27E0B120DE
 and schedule_id = 8)

If you want to run a more filtered trace to track this behavior over time (e.g. persisting through SQL Agent restarts instead of on-demand), you can run one that has appname = 'SQLAgent - Schedule Saver'...
So I think that if you want to know the next run time immediately, look at sysjobactivity, not sysjobschedules. This table is directly updated by Agent or its background threads ("Update job activity", "Job Manager" and "Job invocation engine") as activity happens or as it is notified by xp_sqlagent_notify.
Be aware, though, that it is very easy to muck up either table - since there are no protections against deleting data from these tables. (So if you decided to clean up, for example, you can easily remove all the rows for that job from the activity table.) In this case I'm not exactly sure how SQL Server Agent gets or saves the next run date. Perhaps worthy of more investigation at a later date when I have some free time...


Methods for identifying differences in accumulating history table?

Using your current method you might use a query such as the one below.  This example uses derived tables to treat each backup date range as if it where a table.  Then joins two backup date derived tables (named Today and Previous) on the emp# (I have used EmpNo in my example) and uses the WHERE clause to get only records with at least on difference between the two.  Note that the syntax for derived tables may vary depending on what database product you are using.  Adding the database product to the question might help the question be answered more accurately.

SELECT
    Today.EmpNo
    , Today.First AS TodayFirst
    , Today.Last AS TodayLast
    , Today.DOB AS TodayDOB
    , Previous.First AS PreviousFirst
    , Previous.Last AS PreviousLast
    , Previous.DOB AS PreviousDOB
FROM
    (
        SELECT *
        FROM employeehist
        WHERE BackupDate = '3/10/2014'
    ) AS Today

    INNER JOIN
    (
        SELECT *
        FROM employeehist
        WHERE BackupDate = '3/9/2014'
    ) AS Previous
        ON ( Today.EmpNo = Previous.EmpNo )

WHERE  ( Today.First != Previous.First OR Today.Last != Previous.Last OR Today.DOB != Previous.DOB );

To find all versions of a particular employee record use DISTINCT.  This will return unique versions of each record.

SELECT DISTINCT First, Last, DOB
FROM employeehist
WHERE EmpNo = 1;

You might also consider creating a trigger on the employee table which will only write records to the history table when changes are made. This would allow you to track all edits to an employee without having to compare each backup version to every other backup version.


Update Query said "ORDER_DETAILS"."QUANTITY": invalid identifier

For reference, this is a continuation of the following post.
How to convert an Update a query from MS Access to Oracle SQL Software

I kind of wish you would have continued our conversation there, but I guess they can be merged somehow.

I think you might have taken what I said literally and just switched ORDER DETAILS table to ORDER_DETAILS in your SELECT statement instead of changing the actual object's name.

The object's name was [ORDER DETAILS] and needs to be identified as "ORDER DETAILS" in ORACLE (with Quotes instead of brackets).  Try that and see.

If not, and you actually did change the table itself to ORDER_DETAILS, then your issue is that you're using ORDER_DETAILS.Quantity in your SET.  That is not appropriate.  You NEED to give the subquery that the UPDATE wraps an ALIAS and say:

SET Alias.OldValue = Alias.NewValue

You should set up your SELECT to retrieve the OldValue of the column, and the NewValue of the column.  Please see the link I included in my original answer:
https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle


You can retrieve the complete DDL statement for each index from the Postgres system catalogs.

The following will retrieve all CREATE index statements for a single table:

select pg_get_indexdef(idx.oid)||';'
from pg_index ind
  join pg_class idx on idx.oid = ind.indexrelid
  join pg_class tbl on tbl.oid = ind.indrelid
  left join pg_namespace ns on ns.oid = tbl.relnamespace
where tbl.relname = 'your_table_name'
   and ns.nspname = 'your_table_schema';

You can spool the output into a script and run it after you have copied the data and swapped the table names.


Defragmenting indexes SQL Server bases on avg_page_space_used_in_percent

I have these rules of thumb:

if there are fewer than 10,000 pages in a table, I don't really care about fragmentation;
if the storage layer is solid-state, I don't really care about fragmentation;
once or twice a month I might rebuild indexes on really fragmented tables, but only if fragmentation is demonstrably causing performance issues, which it usually isn't.

Fill factor can affect fragmentation, as can page splits, bad clustered index choices, and so on. Find the actual performance issues using a baseline, and focus on those.


Exclude certain categories from a flag column

For anyone wondering, the solution was :
p.Categories & (SELECT COALESCE(SUM(IntValue), 0)
FROM CategoryExceptionByUser
  LEFT JOIN PCategories
    ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId
WHERE UserId = @CURRENT_USER) <= 0


I didn't find anything wrong with your statement, you need perhaps DELIMITERs

DELIMITER $$
CREATE
    TRIGGER `livre_disponible` BEFORE INSERT ON `livres`
    FOR EACH ROW
   BEGIN
        IF NEW.nb_exemplaires=0 THEN
            SET NEW.disponible=0;
        ELSEIF NEW.nb_exemplaires>=1 THEN
            SET NEW.disponible=1;
        END IF;
    END;
$$

DELIMITER ;

  Updated : you missed also a ';' ==> END IF; END;


Joanolo pointed out if the name had a comma which was not in my case. Therefore, I have changed the format of the derived columns expression to the ContactName as follows:

SupplierFirstName

SUBSTRING(ContactName,1,FINDSTRING(ContactName," ",1) - 1)

SupplierLastName

SUBSTRING(ContactName,FINDSTRING(ContactName," ",1) + 1,LEN(ContactName) - FINDSTRING(ContactName," ",1))


Store equations/formula in mysql db table

You can try to leverage dynamic SQL.

If you need to get a calculated value for an id

DELIMITER $$
CREATE PROCEDURE get_value(IN _id INT)
BEGIN
  SET @sql = NULL;

  SELECT CONCAT('SELECT ', formula, ' value FROM table1 WHERE id = ', 1)
    INTO @sql
    FROM table1
   WHERE id = 1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Note: You can of course use OUT parameter instead of returning the resultset if you want to.

Sample usage:

CALL get_value(1);

Sample output:

|           VALUE |
|-----------------|
| 82.916129032258 |

Here is how a procedure might look like to get all values calculated by formulas

DELIMITER $$
CREATE PROCEDURE get_values()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
    'SELECT id, ', formula, ' value FROM table1 WHERE id = ', id)
    ORDER BY id SEPARATOR ' UNION ALL ')
    INTO @sql
    FROM table1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Sample usage:

CALL get_value(1);

Sample output:

| ID |           VALUE |
|----|-----------------|
|  1 | 82.916129032258 |
|  2 |    0.0000109375 |

Here is SQLFiddle demo for both procedures


A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following:

Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.
Take backups of all databases on the old box.
Restore master from the old box to the new box having the SQL started in single user mode. Restore master method
Restore each database from the old box to the new box with NORECOVERY option in order to be able to apply future Diff or T-log backups.
Restore msdb from the old box to the new box.

If you build and configure the new box to look exactly like the old one, then you will have minimal fuss.

I have no idea how big your databases are, so this method could take a long time. The easiest thing to do would be to shut down the old box and copy the MDF and LDF files and then (after restoring master) you would only need to restart SQL to have your databases online. But you stated that is not an option, as the databases are not allowed to be offline.


After some tests, in order for the DMF to work, it appears that the service account needs to have at least READ access to the root volume of where the database files are located at, in addition to the SQL Server login security VIEW SERVER STATE permission.

The READ permission does not have to be granted explicitly to the service account. It could be granted through other user/groups,

1) SQL Server service account
2) Everyone
3) Users
4) NT Authority\Authenticated Users

The idea is SQL Server service account needs at least READ permission to the root volume. I have listed the test and details here.


SQL Server : database free space vs data file free space

There are apparently errors in the calculations in SSMS.

Shrink Database dialog

Currently allocated space: show the allocated space for both data and log. Correct.

Available free space: show free space for only data, doesn't include log. Incorrect.

The calculating of the free space is off. It divides the full database size (including log) with the free space for the data file (only). Also, it presents the full database size (including log) and for free space, it only show free space for data.

Shrink File dialog

Referring to data file. This is inconsistent. For one of my databases, is show the correct info for the primary data and only file. For a different database (stack overflow 10 GB), it is way off with free space (both number and percentage). For the SO db, it shows only about 600 MB used, when in fact it is about 1.6 GB. My guess is that there is an overflow in a variable in the host language within SSMS causing a wraparound (if it were in TSQL you'd get an error instead)

I.e. disregard the GUI. Collect info from some more correct query/method (what Niels suggested for instance - I use my own sp_dbinfo for these things). and then use the DBCC SHRINK commands directly (preferably DBCC SHRINKFILE).

And, of course don't shrink unless you really really really need that disk space, it is a significant amount of disk space and you are willing to pay the price for shrinking.


Like many things, the answer to this question is "it depends". In this case, what it depends on is how many transactions each product will get.

With low to moderate volumes of transactions, it will be very fast to compute the running total on the fly and you don't have to write lots of code to compute, store and maintain the totals.

Index your table!

Make sure you have an index on (or starting with) production_transaction.product_id so that you can quickly retrieve all transactions for a given product.
An index on the combination of product_id, transaction_date and a query specifying product_id and a date range (or >= today) will give you a day's transactions for a given product, and with good performance.

Test!

Be sure to test this in your Test / Q&A environment! Generate representative transaction loads, but also much higher loads to see how it will perform in the expected and unexpected cases.

Even at high transaction levels, you will have to weigh up the performance associated with updating and storing the running totals vs how often they are queried.


SQL Server 2017 CU3 will not add read-scale replica. Thinks there is a cluster

I finally found Creating a clusterless availability group using windows server 2016 and sql server vNext CTP 1.4 on Stack Overflow, which got me on the correct path.

I had been using the wizard to create the AG, when that failed I used the wizard to script out the t-sql and run step by step. What I failed to notice is that the wizard creates the replica on the primary, but never actually joins the replica on the secondary.

That is, this command executes on the primary:

ALTER AVAILABILITY GROUP [XXXXX]
ADD REPLICA ON N'YYYYY' WITH (other stuff)

but this command never runs on the new secondary:

ALTER AVAILABILITY GROUP [XXXXX] JOIN WITH (CLUSTER_TYPE = NONE);

And apparently the cluster_type = none is critical. At any rate, I am watching my clusterless AG successfully build now, so woohoo! And maybe worth mentioning that I am using SSMS 17.5. The wizard code seems to need an update to include the missing step.

As a follow up. I worked with MS through my company's support agreement. They verified there were a few scenarios that exhibited the wizard behavior described above. They are targeting SSMS 17.6 to incorporate the updates.


Possibilities to speed up InnoDB INSERTs and UPDATEs

InnoDB Architecture

You may find this intriguing but you can double or triple the speed of InnoDB writes. How ?

Disable the double write buffer (using innodb-doublewrite)

First, add this to my.cnf (my.ini for Windows)

[mysqld]
innodb-doublewrite=0

Then, restart mysql

Linux: service mysql restart
Windows

net stop mysql
net start mysql

WARNING

In the event of a crash, the crash recovery phase of the mysql startup may produce inconsistent data or lost data.

If you are using Linux, you can counteract that threat by setting up Percona XtraDB Cluster (PXC) with two or more nodes. If your main node crashes, then you can manually failover to one of the other nodes. Make sure all nodes in PXC has the double write buffer disabled.

GIVE IT A TRY !!!


SQL Server Agent Alerts and Severity Errors

Yes, this is working as designed.
Looking at the docs:

SQL Server Agent reads the application log and compares events written there to alerts that you have defined.

The mechanism that is used for SQL Agent alerting is to read the log. Thus, if it's not logged, there is no alert.
Instead, you can use Extended Events to capture errors, even if they aren't logged. You can then write your own process to read the Extended Events file and send alerts as necessary.


Unable to apply patch in Oracle Grid Home

Thanks all for your help. Here is the solution:

Do standalone installation of Grid Software on each server one by one. (Don't configure Grid now.)
Download the required patch.
Unzip the patch in temp folder and then copy and paste it to Oracle GRID_HOME\OPatch directory. Don't delete any file, if asked click on Merge.
Set the ORACLE_HOME=GRID_HOME and then after set the path of OPatch in PATH environment.
Now apply the patch by following the steps provided in README file along with patch.
Once it is done, Start the Grid configuration , hopefully you won't face any issue.


SP_whoisactive capture on Alwayson Secondary Replica issue

Since you're running sp_WhoIsActive, which looks at all running queries on the server, you can run it from any database. Just choose your database context as master or tempdb in your Agent job step.


Sql Service broker BEGIN DIALOG CONVERSATION and SEND. Should they be in a transaction?

You are correct that the BEGIN DIALOG CONVERSATION does not specify using a TRANSACTION for starting a Service Broker dialog. But many developers use them as a standard practice.

For example, the 2008 R2 TechNet page Beginning a Conversation and Transmitting Messages seems to recommend BEGIN TRANSACTION and COMMIT TRANSACTION (or ROLLBACK) as a good practice.

This topic Configuring Service Broker for Asynchronous Processing also shows transactions being used.

These multi-step examples that use transactions do so for the same reason transactions are used in other code.  Namely to ensure either a complete transaction or else a rollback to a consistent state.

Of course, you can decide if your Service Broker dialog is simple enough that you do not need a transaction. But that is up to you to decide.


More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows

An IN clause gets "rewritten" to this form, essentially:

WHERE
    TableID = 260905384
    OR TableID = 260915601
    OR TableID = 260929877
    ...

I have heard, and observed, that SQL Server has a hard coded limit of 64 OR predicates that it will put in a scan or seek operator.  This isn't publicly documented anywhere as far as I can tell, and there isn't any way I'm aware of to change it either.

Beyond 64 OR expressions, as you mentioned, you end up with the "Constant Scan" plan, which drives multiple seeks or scans into the index (one for each literal).

Putting that many values literal values in an IN construction is generally considered a bad idea.  If possible, change the way the query is written.  You could, for example, insert all of those values into a temp table, and then do an INNER JOIN to that temp table table on the TableID column.


The output of your query is fine but you must then pivot the data using GROUP BY. (SQL Fiddle)

Query:

SELECT p.id, p.name
  , MAX(IF(a.name='height',par.val,0)) As 'height'
  , MAX(IF(a.name='width',par.val,0)) As 'width'
  , MAX(IF(a.name='length',par.val,0)) As 'length'
FROM products p
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id
GROUP BY p.id, p.name
;

Output:

id  name    height  length  width
1   test    2       20      25
2   test2   3       35      30

Data:

CREATE TABLE Products(id int, name varchar(50), info varchar(50));

INSERT INTO Products(id, name, info) VALUES
(1, 'test', 'just a description for test')
, (2, 'test2', 'just another description');

CREATE TABLE Attributes(id int, name varchar(50));
INSERT INTO Attributes(id,name) values
    (1, 'height')
    , (3, 'length')
    , (2, 'width')
    , (4, 'power')
    , (5, 'id');

CREATE TABLE prod_attri_rel(id int, prod_id int, attri_id int, val varchar(20));
INSERT INTO prod_attri_rel(id, prod_id, attri_id, val) values
    (1, 1, 1, '2')
    , (2, 1, 2, '25')
    , (3, 1, 3, '20')
    , (4, 2, 1, '3')
    , (5, 2, 2, '35')
    , (6, 2, 3, '30')
    , (7, 2, 4, 'UBEC')
    , (9, 2, 5, 'BC2212-850');


Is there any view or table to show how long a session have been in Idle state in Oracle?

Yes, v$session stores this information.

v$session.LAST_CALL_ET:

  If the session STATUS is currently ACTIVE, then the value represents
  the elapsed time in seconds since the session has become active. If
  the session STATUS is currently INACTIVE, then the value represents
  the elapsed time in seconds since the session has become inactive.

Documentation link.


In what order MySQL permissions are applied on database?

According to MySQL 5.7 Documentation under Privileges Provided by MySQL

  The privileges granted to a MySQL account determine which operations
  the account can perform. MySQL privileges differ in the contexts in
  which they apply and at different levels of operation:

  Administrative privileges enable users to manage operation of the
  MySQL server. These privileges are global because they are not
  specific to a particular database.
  Database privileges apply to a database and to all objects within it.
  These privileges can be granted for specific databases, or globally so
  that they apply to all databases.
  Privileges for database objects such as tables, indexes, views, and
  stored routines can be granted for specific objects within a database,
  for all objects of a given type within a database (for example, all
  tables in a database), or globally for all objects of a given type in
  all databases.

From this, the order is:

Global Privileges
Database Privileges
Table Privileges

The tables that drive the order are:

mysql.user
mysql.db
mysql.tables_priv
mysql.columns_priv

With regard to an identical username @ host, please note what pages 486,487 state about mysql's authentication algorithm from MySQL 5.0 Certification Study Guide

  There are two stages of client access control:

  In the first stage, a client attempts to connect and the server either
  accepts or rejects the connection. For the attempt to succeed, some
  entry in the user table must match the host from which the client
  connects, the username, and the password.

  In the second stage (which occurs only if a client has already
  connected sucessfully), the server checks every query it receives from
  the client to see whether the client has sufficient privileges to
  execute it.

  The server matches a client against entries in the grant tables based
  on the host from which the client connects and the user the client
  provides. However, it's possible for more than one record to match:

  Host values in grant tables may be specified as patterns contains
  wildcard values. If a grant table contains entries from
  myhost.example.com, %.example.com, %.com, and %, all of them
  match a client who connects from myhost.example.com.

  Patterns are not allowed for the User values in grant table entries,
  but a username may be given as an empty string to specify an anonymous
  user. The empty string matches any username and thus effectively acts
  as a wildcard.

  When the Host and the User values in more than one user table record
  match a client, the server must decide which one to use. It does this
  by sorting records with the most specific Host and User column values
  first, and choosing the matching record that occurs first in the
  sorted list, Sorting take place as follows:

  In the Host Column, literal values such as localhost, 127.0.0.1,
  and myhost.example.com sort ahead of values such as %.example.com
  that have pattern characters in them. Pattern values are sorted
  according to how specific they are. For example, %.example.com is
  more specific than %.com, which is more specific than %.

  In the User column, non-blank usernames sort ahead of blank usernames.
  That is, non-anonymous users sort ahead of anonymous users.

  The server performs this sorting when it starts. It reads the grant
  tables into memory, sorts them, and uses the  in-memory copies for
  access control.

When you look at these two perspectives, mysqld should always go top down when evaluating grants. Keep in mind that

GRANT SELECT ON Demo.table1 TO abc@123; is stored in mysql.tables_priv
GRANT ALL PRIVILEGES ON Demo.* TO abc@123; is stored in mysql.db
For more information, please see my older post Unable to remove permission for mysql.user

AND THE OSCAR GOES TO ...

GRANT ALL PRIVILEGES ON Demo.* TO abc@123;


Avoid Sort on Indexed Group By caused by DATE_TRUNC

There is no way to avoid the sort with your query, because PostgreSQL does not know if the rows returned by the index scan will be correctly sorted or not, and a group aggregate requires sorted input.

Your only chance is to increase work_mem until you get a hash aggregate, which does not require sorted input.


Reporting on a SQL DB Server with many write operations and few read operations

I know that when a write is happening to a SQL Server database table, a read to that table will have to wait.

This isn't necessarily true. Proper indexing can help mitigate blocking waits by giving queries adequate paths to data, and helping modification queries find the data they're going to be modifying.

  I know that it is always best to perform reporting from a duplicated instance of the same database. But for a few reports, is it worth to set up a duplicate instance for reporting?

Potentially, because:

  I get complaints sometimes that some reports are running slow.

Finally:

  Can it be the read wait while a write happens?

Take a look at SNAPSHOT ISOLATION. If the reports are slow because of blocking, this can help. If the reports are slow for other reasons, at least you'll know pretty quickly. Heh.


Database design to record occurences in tv show

I think given the requirements and that at some point you may want additional information that wasn't listed here, I would go with a fully normalized approach.
3 Tables: Guests, Episodes, Episode_Guests
Then depending on if you want to do this for more than one show, another table for Shows(or series).
As Paparazzi mentioned, the Guests table should contain sex.  The Episode table should contain a date.  Also, if you are going to do this for multiple shows, the Episode table should also have a foreign key back to the Shows table.
The Episode_Guests table should record every instance of a Guest appearing on an Episode so all it would need is a foreign key relationship back to guests and another for Episode.


MySQL - Query records between yesterday and today but with a specific time

USE CURDATE() and INTERVAL
SELECT CURDATE() - INTERVAL 1 DAY + INTERVAL 8 HOUR, CURDATE() + INTERVAL 8 HOUR

RESULT
# CURDATE() - INTERVAL 1 DAY + INTERVAL 8 HOUR  CURDATE() + INTERVAL 8 HOUR
2020-11-19 08:00:00                             2020-11-20 08:00:00


ORA-31114: XDB configuration has been deleted or is corrupted

Removing XDB

Run the following script to remove XDB

SQL> @C:\Oracle\Ora11\R2\rdbms\admin\catnoqm.sql

It is very important to restart your database after removing XDB and before installing it again.

Installing XDB

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password

B. XDB user default tablespace

(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)

C. XDB user temporary tablespace

D. SecureFiles = YES or NO

(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:

SQL> catqm.sql A B C D

SQL> @C:\Oracle\Ora11\R2\rdbms\admin\catqm.sql XDB XDB TEMP YES

Recompile all invalid objects within the database

SQL> @C:\Oracle\Ora11\R2\rdbms\admin\utlrp.sql

Verify XDB Status

Check registry

SQL> select comp_name, version, status from dba_registry where comp_id = 'XDB';

COMP_NAME            VERSION     STATUS
-------------------  ----------  -----
Oracle XML Database  11.2.0.1.0  VALID

Check invalid objects owned by XDB

SQL> select owner, object_name, object_type, status from dba_objects where owner = 'XDB' and status = 'INVALID';

no rows selected


How to safeguard a symmetric key in SQL Server

I have dealt a lot with PCI and HIPAA recently since we've developed the ApexSQL Audit compliance tool (some details about the tool can be found here), and during a few years of working on the tool and demoing the tool, I was engaged in numerous discussions about the status of DBA in relation to compliance rules. PCI and HIPAA are not developed to deal with "trust" but to eliminate "trust someone" as a category, so PCI DSS is quite strict in that. DBA usually is not the one who is allowed to have access to data related to compliance, and lot of customers are using our tool to place the control of the DBA as well... not because they "don't have a trust" in DBA, but because the specific compliance say/recommend that he shouldn't be trusted. Most of the security breaches are inside jobs, so PCI DSS is there to prevent inside jobs as well

Quite often question was how to prevent DBA to access the data. There are active and passive methods to do that, like segregation of duties between the DBA or attempt to limit the DBA access to some specific tables/views... but so far it seems that passive methods are more efficient. Auditing all user activities and especially activities on objects that stores the sensitive data are the crucial things (including the SELECT)... of course all security related operations (including anything related to keys) must be audited as well as most DDL activities, especially the ones related to triggers and stored procedures must be tracked as they are based on procedural programming language where someone can hide the malicious code that would otherwise be invisible

When somebody want to do something bad, he want to stay unnoticed, and if you establish the auditing of all activities which can be used for some malicious job, and ensure that nothing can go unnoticed, than your passive method in fact became the active method, or better say the pro-active method. If you can be alerted immediately on the desired events, than you can react immediately as well to deal with any security treat and prevent a damage

Just want to state that I'm working for ApexSQL and this is the reason that I mentioned our tool, but there is a bunch of other tools that dealing with compliance and auditing as well


How does SQL Server Merge Replication maintain referential integrity?

Review How Merge Replication Detects and Resolves Conflicts

A lineage column is used which contains the generation and the node that made the change.  The actual winner decision during the conflict depends upon the conflict resolver used for the article. Using the default prority-based resolver, a priority value per node should be assigned per subscriber to determine the winner for all conflicts.

With regards to referential integrity and the concept of conflict resolution in mind, the processing order with which tables (articles) are processed is your only means of guiding the conflict resolution before your child tables.  You can modify the order of the article processing in the merge publication via the @processing_order parameter for sp_addmergearticle


Is there any difference on backup process whether it is recovery model is Full or Simple?

A full database backup will give you everything in the database regardless of recovery model.  Upon restore, any transactions that were still in-flight when the backup completed will be undone.

Recovery model really comes into play during restores.

With SIMPLE recovery, you can only take full and differential backups and that is the granularity that you can restore.

With FULL recover, you still take full (and optional differential) backups, but you also take transaction log backups and can use the combination of the full, diffs and logs to restore to a point in time.


GRANT DROP ON TABLE springbootdb.* TO AdminUser

There isn't a specific "drop table" permission. But you can give them control on the schema using:

GRANT CONTROL ON SCHEMA::springbootdb TO AdminUser;

This allows them to drop any table in that schema, but also to do all kinds of other things you might not intend. You can take some of those things away with more granular DENY permissions, or with reactionary tools like DDL triggers (which can roll back operations based on the object and user).


Modifying an Existing Column Definition

  You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

SQL> show parameter BLANK_TRIMMING;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
blank_trimming               boolean     FALSE

SQL> alter system set BLANK_TRIMMING=true scope=spfile;

System altered.

Then bounce the database.

SQL> startup force;

SQL> update tblchar set col1=TRIM(col1);

SQL> alter table tblchar modify col1 char(16 byte);

Table altered.

Documentation:
Altering Tables


How can I inspect the security settings of a SQL Server Endpoint?

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under.  If SQL Server is using a domain account, then that account automatically has access to the endpoint.  If SQL Server is using a built-in account such as [Network Service] or [Local System] etc, then the endpoint must be configured with a security certificate, and only holders of the certificate can access the endpoint.

The relevant section from the document:

  Determining the Authentication Type for a Database Mirroring Endpoint

  It is important to understand that the SQL Server service accounts of your server instances determine what type of authentication you can use for your database mirroring endpoints, as follows:

  If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints. If all the server instances run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration for the availability databases and is recommended.

  If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

  If your server instances use Windows Authentication, you can create database mirroring endpoints by using Transact-SQL, PowerShell, or the New Availability Group Wizard.

  Note:

  If a server instance that is to host an availability replica lacks a database mirroring endpoint, the New Availability Group Wizard can automatically create a database mirroring endpoint that uses Windows Authentication.

  If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If you are using certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

  There is no automated method for configuring database mirroring security using certificates. You will need to use either CREATE ENDPOINT Transact-SQL statement or the New-SqlHadrEndpoint PowerShell cmdlet. For more information, see CREATE ENDPOINT (Transact-SQL).

Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
    INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'

However in my case on SQL Server 2005 the original mirroring endpoints do not show up in the results of this query - I assume this is because they default security allows CONNECT access to the account SQL Server is running under (as explained above).

So the canonical answer to the question, how do you know who has CONNECT access to an ENDPOINT, is the sum of the query above, and the account SQL Server is using, along with any possible certificates you've configured with access to the endpoint.


You could use substr() and concatenation (Oracle 12c): select the first 4 characters, then concatenate '-' and the rest of the string.

create table t
as
select '1234567891234567' card_num from dual;

select card_num
, substr(card_num, 1,4) || '-' || substr(card_num, 5, length(card_num) - 4)
  formatted
from t;

SQL> select card_num
  2  , substr(card_num, 1,4) || '-' || substr(card_num, 5, length(card_num) - 4)
  3    formatted
  4  from t;

CARD_NUM          FORMATTED
1234567891234567  1234-567891234567

... the table is "overkill" for this example - but it may make it easier to experiment/adjust your query.


SQL Server 2012 Availability Group is it "AlwaysON"?

No.  During a failover, clients will notice.  Your connection to the SQL Server will be broken, and you'll have to retry your transaction.


The most efficient way to join huge tables

update a1
set  a1.value_last = LAG(value, 1,0)  OVER (partition by id ORDER BY period)
  ,  a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
from tab1 a1

index on id, period

or just use a view - that might surprise you

CREATE VIEW tab1LastNext
AS
select a1.id, a1.period, a1.value
     , LAG(value, 1,0)  OVER (partition by id ORDER BY period) as value_last
     , LEAD(value, 1,0) OVER (partition by id ORDER BY period) as value_next
from tab1 a1;

for logging
need to hope value is not null or this gets messy

select 1;
while @@rowcount > 0
begin
  update top (10000) a1
  set a1.value_last = LAG(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1
  where LAG(value, 1,0) OVER (partition by id ORDER BY period) is not null
    and LAG(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_last
end
select 1;
while @@rowcount > 0
begin
  update top (10000) a1
  set a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1
  where LEAD(value, 1,0) OVER (partition by id ORDER BY period) is not null
    and LEAD(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_next
end


From a design perspective, why would the Student_Education table be needed at all?

The information that a specific student follows a certain type of education is implied by the existence of the corresponding row in the <type>Education table.

If Student_Education's reason for being is for SQL joins, it could be a view returning the union of all the <type>Education tables.

As for the foreign keys to Education, to be sure that each entry in <type>Education can only refer to its corresponding type, I can see two options:

As @hypercube suggests in the comments, add a <type>Education.educ_type column with a fixed value and a table-level foreign key:
FOREIGN KEY (educ_id,educ_type) REFERENCES Education(unique_id,type)
Split Education into as many subtables as they are education types, and have column-level foreign keys from each <type>Education.educ_id to the corresponding subtable. It's also the proper design choice if the Education table should be expanded in the future with columns that are specific to certain education types. Education could also be turned into a view as the union of the subtables, at least for the common columns.


If you have created the backup of a SQL Server 2008 database and are trying to restore it on a SQL Server 2005 instance, you will receive this error.  You cannot do this.

Your best bet would be to script out the database (as long as all of the schema and objects are 2005 compatibile).  You can't downgrade a database.


Is it possible to combine these three join queries into one?

Not sure if there is anything that I miss here, but you can count conditionally like:

select allurls.id
    , count(case when s1.hit_type = 0 then 1 end)
    , count(case when s1.hit_type = 1 then 1 end)
    , count(case when s1.hit_type = 2 then 1 end)
    , count(distinct s1.source_id)
from allurls
join stats s1
    on allurls.id = s1.allurl_id
where s1.hit_date >= '2018-01-15'
  and s1.hit_type between 0 and 2
group by allurls.id;

 id     c1  c2  c3  c4
aaa     1   0   0   1
cnn     16  1   4   3

Some things to note:

I moved hit_type predicate to where, it does not make a logical difference, but I prefer to have only the relationship between tables in the join clause if possible.

distinct is not a function, count(distinct(s1.source_id)) is confusing. Better use count(distinct s1.source_id)


Removing Database from AlwaysOn Availability Group

Simply run

RESTORE DATABASE TestDB WITH RECOVERY

on the secondary.


How to find out which host / IP address / program performed a delete?

You will not be able to find this information after the fact. You'll need to add a trigger and a logging table (or set up an expensive server-side trace, or add manual logging to your data access methods).

Here is a very quick example of how to implement a logging table and a trigger:

CREATE TABLE dbo.TableNameDeleteLog
(
  PK_From_TableName INT,
  Program NVARCHAR(128),
  Host NVARCHAR(128),
  IP VARCHAR(48),
  When DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO

CREATE TRIGGER dbo.LogDelete_TableName
ON dbo.TableName
FOR DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @p NVARCHAR(128), @h NVARCHAR(128), @i VARCHAR(48);

  SELECT @p = s.host_name, @h = host_name, @i = c.client_net_address
    FROM sys.dm_exec_sessions AS s
    INNER JOIN sys.dm_exec_connections AS c
    ON s.session_id = c.session_id
    WHERE s.session_id = @@SPID;

  INSERT dbo.TableNameDeleteLog(PK_From_TableName, Program, Host, IP)
    SELECT PK_Column, @p, @h, @i
    FROM deleted;
END
GO


DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)

Did the drive the snapshot is on or the drive that the source DB is on run out of space?

There's no way to repair a corrupt snapshot. The only thing you can do with it is drop it.


You can as a minimum execute sp_who2 that will show session information, but more advanced and detailed methods are available, plenty of scripts available, it depends what you are after specifically.

Great set of tools available at Brent Ozar: First Responder Kit

If you want a better tool to just look at  queries that are currently running, try sp_WhoIsActive, by Adam Machanic.


Yes, you just need to specify the schema before the table name. For example, if the referencing table is in schema qa and the referenced table in xx:

ALTER TABLE qa.test_results
    ADD COLUMN file INTEGER,
    ADD FOREIGN KEY (file) REFERENCES xx.files (file) ;

Then all of your queries will also need to be formatted using schema.table


PostgreSQL: Non-continious replication of new data with COPY

I see the following possible solutions:

Change the source table

Change the source table to include a column that you can use to (uniquely) identify the data you have already synchronized. A column based on a sequence (serial or identity in Postgres 10) or column defined as timestamp that is populated through a trigger.

If that column is indexed, this is probably the most efficient solution.

Keep track of copied data

Create a table in the source database that contains the already synchronized rows.

create table synchronized
(
  devicename  character varying(30),
  id          integer,
  primary key (id, devicename)
);

The following statement would return all rows that are not yet synchronized and "marks" them as transferred in a single query. It should be possible to use that as the source for the copy statement (but I haven't tested it).

with todo as (
  select *
  from data
  where (id,devicename) not in (select id,devicename from synchronized)
), done as (
  insert into synchronized (id, devicename)
  select id, devicename
  from todo
)
select *
from todo;

Create a foreign table and use that for synchronization.

Using a foreign data wrapper you create a foreign table in your local Postgres server that connects to the remote server. Then whenever you want to synchronize the data, you can do that with a single statement:

insert into local_table
select *
from remote_fdw_table
on conflict do update
    set "timestamp" = excluded.timestamp,
        value = excluded.value,
        variable = excluded.variable;

That is probably the least efficient solution as it will always transfer the complete table to your local computer and overwrite/insert as needed. But this requires no changes on the source database and other than the I/O needed for the SELECT, has not impact on the source.

The connection that is used by the FDW does not need to be valid all the time. It only needs to work, when you run the synchronization. As long as you don't access the foreign table, it doesn't matter if your computer can connect to the source or not.

Copy everything into a staging table, then use "upsert"

A variation of the solution with the foreign table is to simple export the whole source table, import that into a staging table with the same structure, then use the above INSERT ON CONFLICT approach to insert/update the real table.

That requires one more step for synchronization but might be easier to handle depending on your environment.


Check to see the amount of space actually used in your database. You probably have a lot of free space available. A quick way to do this would be to connect to your database and use exec sp_spaceused which will give you the database size and unallocated (unused) space.

When SQL Server backs up the database it only needs to backup the pages that contain data (and the log), which means that the backup can easily be smaller than the files themselves.

This question has also been answered in Why is a .bak so much smaller than the database it's a backup of?


64 bit always. You do understand 32 vs 64 bit generally?
Windows Server 2008 R2 of course. Windows 7 is desktop, Windows 2003 is obsolete

Have you considered Linux though?


Most likely, you have both Postgresql 8.4 and Postgresql 9.2 installed on this server.

Prior to pg9.0, the RPM packages for postgresql were configured such that you could only have one version installed at a time. Upgrading the RPM replaced the old version with the new.

From pg 9.0, the RPM packages have been configured to allow installation of multiple versions side by side. This is to facilitate in-place upgrade of databases (which requires both versions installed during the conversion). The packages are names postgresql92, postgresql93, etc.

Also note that it would not be possible for both versions to run simultaneously on the same port.

My guess is that your server was rebooted, and when it came up, either they were both configured to start and the 8.4 version started first, or perhaps the 9.2 version is not configured to start at boot at all.

You can confirm this with:

yum list installed "postgres*"

to see that you have both versions installed. You can check which versions are configured to start at boot with:

chkconfig --list | grep postgres

To stop the 8.4 version and start the 9.2 version, so that you can access your data:

service postgresql stop
service postgresql-9.2 start

To make sure that 9.2 starts up next time you boot, and 8.4 does not:

chkconfig postgresql-9.2 on
chkconfig postgresql off

All the above commands executed as root.

If you do not specifically need the 8.4 postgresql installed on your server I would recommend you remove it.


Error in AG Dashboard when looking at a Distributed AG

As a couple users mentioned in the comments, if you are not actually having problems with the AG, this is likely a bug with SSMS.

You mentioned you're on 17.4, and I see in the release notes for 17.6 that there are several fixes for AG issues, including:

  Fixed an issue when the primary is down and manually failover to secondary, a NullReferenceException will be thrown.

(a NullReferenceException in .NET classically includes the message you mentioned - "Object reference not set to an instance of an object")

You should try updating to 17.6 (build 14.0.17230.0) to see if the problem is resolved there.


InnoDB Cluster Read-Only Lag Behind Writer

SHOW SLAVE STATUS is only applicable to master/slave replication channels.

For Group Replication, you would look at the Replication_group_member_stats table. In particular the two queue sizes, the certification queue and the applier queue. The applier queue length tells you how many transactions behind this node is in applying the transactions which the group has agreed/promised to apply (in the same order) through the consensus protocol.

That column is new in MySQL 8. You can see how I calculated it by hand here in 5.7 (the gr_applier_queue_length() function).

We also plan to add the applier queue length (lag indicator) to the status output in the MySQL Shell AdminAPI.


You need to set JOB_QUEUE_PROCESSES to a positive number in order to enable database jobs to run. JOB_QUEUE_PROCESSES controls the number of jobs that are allowed to run simultaneously so if you set the parameter to 1, only 1 job could run at a time. If you're going to be creating multiple materialized views that you want to refresh at approximately the same time or if you are potentially going to be creating additional jobs, you may want to set this parameter to a value greater than 0.


SQL SERVER 2014 Standard - High Availability Listener solution

With SQL Server 2014 Standard Edition you can create a two-node failover infrastructure. For that, you need to have in each node at least Windows Server 2008 R2 SP1 Standard, because you need to first generate a failover cluster with Windows Server.

If you want a multi-instance failover cluster, then you need to generate two SQL Server 2014 installations, since the product works in an Active-Passive mode. I mean, you will need to install the first instance, SQL1 and then you will proceed with the second instance, SQL2. You will need to configure each instance so SQL1 runs in node 1 and SQL2 runs in node 2.

Also, it's very important and recommended, that you have at least a SAN or some device that can provide you with the shared storage schema, since previous to the SQL Server failover cluster installation you need to validate the Windows Server failover cluster support to the SQL Server one.


Size of TempDB Microsoft SQL Server 2005

Tempdb will be recreated if the server is restarted and at that time tempdb will be reset size wise to initial size settings. In the meantime once the temporary tables are dropped space will free in the data files but size on disk will remain the same.

I wouldn't however worry about the size unless you have space issues.  Next time you run queries that use tempdb a lot you will save on growth operations and get a bit of a boost to performance since it will use the above mentioned free space in the data files


I started to play with RedGate's tool - Script Manager. You can have them together in a single place with some UI. It may worth a try :).


Using scope_identity() to get ID "during" insertion of row?

Do exactly as Aaron said, or if you are afraid of nulls, or don't want to use COALESCE, perhaps you could do something along these lines:

USE tempdb;
GO
-- Create the table
CREATE TABLE dbo.T
(
    IdentityID INT NOT NULL CONSTRAINT PK_T PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , CopyOfIdentityID INT NULL
);
GO
-- create a trigger to copy the values from column a to column b
CREATE TRIGGER T_Trigger ON T
AFTER INSERT
AS
    UPDATE dbo.T SET CopyOfIdentityID = i.IdentityID FROM dbo.T INNER JOIN inserted i ON t.IdentityID = i.IdentityID;
GO
-- insert some test values (this would typically be some other piece of code, perhaps
-- a stored proc or something.
INSERT INTO dbo.T DEFAULT VALUES;
-- you could return the SCOPE_IDENTITY() here for use in the update below.
SELECT SCOPE_IDENTITY();
-- show the row prior to changing the b column to some other value
SELECT *
FROM dbo.T;
-- update the b column to some other value.
DECLARE @SomeOtherID INT;
SET @SomeOtherID = 2;
UPDATE dbo.T SET CopyOfIdentityID = @SomeOtherID WHERE T.IdentityID = 1;
-- show the row with the updated value.
SELECT *
FROM dbo.T;

Results:

Simple, and doesn't rely on anything fancy.


What is meant by a blind write in a schedule?

If there is no read that happens prior to the first write then it is said to be a blind write.

Example. Consider the following schedule:

W3(X) is a blind write, as there is no read before write [R3(X) before W3(X)]
W2(X) is not a blind write, as a read happens before write [R2(X) before W2(X)]


update variable from multiple tables in one row

If your db2.table1 and db2.table2 tables are joined by some key field and the name field in both databases is unique, you can use the following query to update your data:

UPDATE `db1`.`table1` t SET
t.`birthday` =
(SELECT t2.`birthday` FROM `db2`.`table1` t1 JOIN
`db2`.`table2` t2 ON (t1.`key` = t2.`key`)
WHERE t.`name` = t1.`name`)

This should work if you need to update db2 -> db1. If you need to update db1 -> db2, the query can be changed as follows:

UPDATE `db2`.`table2` t2 SET t2.`birthday` =
(SELECT t.`birthday` FROM `db1`.`table1` t  WHERE t.`name` =
(SELECT t1.`name` FROM `db2`.`table1` t1 WHERE t1.`key` = t2.`key`))


Azure SQL Database - dedicated administrator connection (DAC)

As per Microsoft documentation, Diagnostic Connection for Database Administrators is available for the Azure SQL database. If you are not familiar with DAC, read this official documentation from Microsoft.

By default, the connection is only allowed from a client running on
the server. Network connections are not permitted unless they are
configured by using the sp_configure stored procedure with the remote
admin connections option.

Ref: Diagnostic Connection for Database Administrators
There is a tip in the same document.

In my test, I can still connect to the Azure SQL database with DAC using SSMS or SQLCMD. I did make the Microsoft Azure SQL team aware and suggested changing the value from zero to one.
In SSMS, follow the same process that you use to connect to on-premises SQL Server. Do not use the ‘Connect Object Explorer’ connections. Open a ‘New Query’ window and then connect. Prefix server name with ‘ADMIN:’.

During my testing, I get an error message but the connection was successful (SSMS 18.8).

If you do not mention the database name, you will only connect to the master database. You cannot switch context to any user databases. You will get an error if you try to change the context from Master to another database. Every database in an Azure SQL server has to be connected separately depending on which one you want to work with.

To connect to any database other than master type your database name in the connection string.

For SQLCMD, you can use this command. You must use -d switch for the database name. If you are using Azure Active Directory for authentication use -G switch.
sqlcmd -S servername.database.windows.net -U userName -P StrongPassword -d master -G -A

For On-Premises SQL Server, you will use sys.endpoints system catalog view to see details about a DAC. This system catalog view does not exist in Azure SQL Database.

Caption: Output of sys.endpoints from SQL 2019 CU 9
For Azure SQL Database, use the below query to determine who is using DAC and all other details about the DAC session. Dimitri Furman from Microsoft provided this query.
SELECT s.*
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE wg.name = 'DACGroup';


The problem has been solved with some hints from:

Performance Data Collector in 2008
Data Collector - Cannot Find Database

When I setup Data Collection I did the following:

Setup

SSMS > serverA
Management > Data Collection
Configure Management Data Warehouse
Next
 > Check: Create or upgrade a management data warehouse
Next
New
 > Database name: DBA_MDW
OK
 > Server name: serverA.company.com
 > Database name: DBA_MDW
Next
New Login
 > Login name: DOMAIN\SQLmdw
OK
 > Check: mdw_admin
 > Check: mdw_reader
 > Check: mdw_writer
Next
Finish
Close

Configuration

Configure Management Data Warehouse
Next
 > Check: Set up data collection
Next
 > Server name: serverA.company.com
 > Database name: DBA_MDW
 > Cache directory:
Next
Finish
Close

To uninstall Data Collection, I used the script from Remove associated data collector jobs. I then followed the above setup and configuration steps, but this time under the configuration step I simply put serverA instead of serverA.company.com. That seems to have made all the difference and I'm now collecting data.


The ultimate answer turned out to be different encoding in the two databases.  Thanks Erwin for steering me in that direction.

My apologies for not asking the question correctly.  I am definitely out of my depth in this area.


SQL Server columnstore indexes are created for datawarehouse purposes.

When should I use this?

I would use a CLUSTERED columnstore index standard for most fact tables, because you will get a better read performance & it is good for writing data to it! note: there is a big difference in the way you insert data. you should read up on that on this post.
http://www.nikoport.com/2013/07/06/clustered-columnstore-indexes-part-6-observing-the-behavior/

However, since the SQL Server 2016 you can use columnstore indexes to combine a OLAP workload on your OLTP system.

This is done by using the filtered NON-CLUSTERED columnstore index. The way to do this is by filtering your data between hot and warm data. The filtered columnstore index will then keep a copy of the filtered warm data part.

What is Hot and Warm data?

F.E. You have a customer who buys one banana, then he wants to change his order and makes it one banana and 2 apples, then he cancels one apple. This is typical hot part of your data. Which has a lot of update and inserts and deletes. After he pays for this order the order becomes a warm portion of the data which means only that the data will not change frequently.

The trick is putting a non-clustered index with a filter to only have the warm data inside your non-clustered columnstore index. In this case it could be a flag payed or something similar. This is something that is different in every company.

Why should you use this?

You will get an incredible read speed activity due to the batch mode processing.This will greatly increase your large read queries on the system.

NON-Clustered columnstore trick

There is also a trick using an empty filtered non-clustered columnstore index.
F.e.

CREATE NONCLUSTERED COLUMNSTORE INDEX TESTCCI on dbo.TEST
(
ID
) where ID = -1 and ID = -2

This will create an empty nonclustered columnstore index (SQL Server will also not read any data to create the index). After you do this your read queries will be able to benefit from batch mode processing and thus greatly improving your big read queries on your oltp system.


I wish to restore a gzipped tarball - what does mongodump --archive do? Howto do a mongorestore with --gzip?

I think the problem you have is that the archive format created with --archive is not a tarball (and the docs don't say it is anywhere that I could find). Rather it is a custom packaging format which you can see the details of here. Based on a quick scan of the code, it looks like a lightweight format containing a series of headers, metadata which describes the raw BSON. Short of creating a standalone binary to create a compatible archive file, you can't do this manually.

If you do mongodump without archive, but with --gzip, it will actually gzip the individual files, and you can emulate that by doing the dump normally then gzipping each file in the folder separately. Those compressed files could then be restored theoretically with mongorestore --gzip.

Overall I would advise to just use --archive in the 3.2 tools and stay away from trying to recreate it manually, but the --gzip option is as close as it is going to get without a bunch of work.


Multiple AG groups complicated set up question

Nothing additional is required. You would just add server F as another replica for both AG #1 and AG #2 (provided that you do not have collisions in your database names).

Just remember that they all need to be members of the same WSFC (given the version you are working with).

With 2012 you must have the Windows machines on the same WSFC in order to add them to an AG, as such you will not be able to use the F server for both AGs (you'll have to choose one or the other).


Multi-threaded replay of master activity for load/performance testing

I actually have a suggestion that will take some work but is doable.

THEORETICAL SCENARIO

You have 3 databases: db1, db2, db3
Production DB had binary logging enabled

What you need are the following to create enough traffic:

mysqldump of Production DB two weeks old (TwoWeekOldData.sql) of db1, db2 and db3
Binary logs on Production DB that are two or more weeks old
Four DB Servers running MySQL

DBServers 1-3 are Slaves to Production DB
DBServers 1-4 have Binary Logging Enabled
DBServer1 has replicate-do-db=db1 and has only db1 data loaded
DBServer2 has replicate-do-db=db2 and has only db2 data loaded
DBServer3 has replicate-do-db=db3 and has only db3 data loaded
DBServer4 has all data from TwoWeekOldData.sql loaded

You will need the following:

Run mysqlbinlog against all binary logs on Slave1 and collect SQL into db1traffic.sql
Run mysqlbinlog against all binary logs on Slave2 and collect SQL into db2traffic.sql
Run mysqlbinlog against all binary logs on Slave3 and collect SQL into db3traffic.sql
Run these three traffic SQL files in parallel like this

mysql -hdb4 -u... -p... -A -Ddb1 < db1traffic.sql &
mysql -hdb4 -u... -p... -A -Ddb2 < db2traffic.sql &
mysql -hdb4 -u... -p... -A -Ddb3 < db3traffic.sql &

You can use DBServer4 to measure

server load
query performance
I/O utilization
Whatever you want to measure

As I mentioned in the outset, this takes some setup work along with access to old data and all binary log entriers since the old data's last backup.

I hope this gives you some guidance on producing real traffic rather that simulating ordinary load.

GIVE IT A TRY!!


Subquery returned more than 1 value. This is not permitted + Trigger Error SQL Server

Actually Triggers fire once rather than multiple times if update statement modify more than one row.

Your trigger trying to update multiple rows which means Inserted table will also have more than one row and when you try to set Flag values, it return you error.

Join your table's key with Inserted

CREATE TRIGGER [dbo].[Is_Confidential_or_VIP] ON [EDB].[dbo].[Patient_Info]
    AFTER UPDATE, INSERT
AS
    BEGIN

        UPDATE  patinfo
        SET     Is_Confidential_or_VIP = CASE WHEN VIPType = 'MDP-A'  --- 'Like' Replaced with '='
                                                   OR VIPType = 'MDP-B'
                                                   OR ConfidentialRegistrationFlag = 'confidential'
                                              THEN 1
                                              ELSE 0
                                         END
         -- select case VIPType = 'MDP-A' OR VIPType = 'MDP-B'  OR ConfidentialRegistrationFlag = 'confidential' then 1 else 1  END,*
        FROM    EDB.dbo.Patient_Info patinfo
                INNER JOIN inserted i ON patinfo.EPN = i.EPN

    END


It it evident from your question, that 30 min worth of transactions came into the database while the mysqldump was in progress.
In order for the binlog position to move and the mysqldump to work, the mysqldump parameter --single-transaction had to have been issued. What does it do ???
According to MySQL Documentation on mysqldump --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.
To dump large tables, combine the --single-transaction option with the --quick option.

The log file and position recorded in the mysqldump (usually between lines 23-25) simply give you the place to start when setting up a MySQL Replication Slave.
EXAMPLE
Suppose you ran mysqldump --single-transaction from IP 10.20.30.40 (call it Server M1) and loaded that mysqldump into another server (the one to become the MySQL Replication Slave, call it Server S1).
You run the following commands on S1
CHANGE MASTER TO
    master_host='10.20.30.40',
    master_post=3306,
    master_user='replicationusername',
    master_password='replicationpassword',
    master_log_file='mysql-bin.000002',
    master_log_pos=155;
START SLAVE;

What this will do is activate two DB threads

I/O Thread : This will copy all binlog events from the initial position you gave it or the last position it successfully executed from the master (M1). When it gets to the end of the binlog it was reading, it will move to the next binlog (mysql-bin.000003) and keep going from there.
SQL Thread : This will execute all binlog collected from the I/O until there are no more. Then, the SQL thread will wait for new events from the I/O Thread to the collected.

What are binlog events ?? The recorded SQL commands that a Master Server records when the Master has executed them.
Would you like to know what the binlog events look like ???
You would go to M1 and run the following
mysqlbinlog --start-position=155 --stop-position=497824459  mysql-bin.000002 > binlog_events.sql
less binlog_events.sql

The file binlog_events.sql will contain all the binlog events that executed on the Master while the mysqldump was in progress.
If you want those events on another server without setting up MySQL Replication, you could load the mysqldump and then load binlog_events.sql into a new server and have the dump just as up-to-date as possible.
YOUR ACTUAL QUESTION
Yes, you will further corrupt the DB.


I sort of simulate macros using dynamic SQL, but it's pretty clunky since this isn't what T-SQL was designed for.

DECLARE @sql nvarchar(max) = N'SELECT foo_$alias$ = 1;';

DECLARE @alias sysname = N'bar';

SET @sql = REPLACE(@sql, N'$alias$', @alias);

EXEC sys.sp_executesql @sql;

Output:

foo_bar
-------
      1

My concern would be that the consuming application is able to handle all of the potential column names coming back from the database (I assume your real example is more complex, because the application certainly has the ability to know the current month).


How can I fix this unexpected Windows Event Log message which appears whenever a database with FILESTREAM is created or restored?

It could be two things. If you have Windows 10, Creators update, there is a patch for the SQL Server.

In all other cases this fixed the error for me:

ALTER DATABASE [dbname] SET ANSI_NULL_DEFAULT ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ANSI_NULLS ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ANSI_PADDING ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ANSI_WARNINGS ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ARITHABORT ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET AUTO_CLOSE OFF WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET CONCAT_NULL_YIELDS_NULL ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET QUOTED_IDENTIFIER ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET PAGE_VERIFY NONE  WITH NO_WAIT
GO


How to execute two transactions concurrently in PostgreSQL?

Each pgAdmin query window has it's own session. You can run one transaction at a time in each session. It's not possible to run two transactions concurrently in the same session (at least not in pgAdmin or psql).

You can open and bind more sessions to the same query window with the "connection bar" in the menu of the query window, then switch between multiple open sessions. But it's simpler to just open another query window.

pgAdmin runs everything you execute at once inside a transaction. It's wrapped into BEGIN; .. COMMIT; implicitly - unless you issue explicit transaction control commands.

Start a transaction start with BEGIN; followed by SQL commands and don't commit yet. Switch to the other query window and do the same. Now both transactions are running concurrently. Some effects don't show until you COMMIT; one or both transactions.

Similar answer with detailed instructions on SO:

How to simulate deadlock in PostgreSQL?

As for your code and error message:

Like @Jack already told you, you are using PL/pgSQL code, the default procedural language extension in Postgres (similar to PL/SQL in Oracle). It's only valid in a function or anonymous code block.

A code block can only be executed as a whole.