Identity Columns or UDF that explicitly generates a unique id?

Your colleague is an idiot.

The solution won't be scalable, the UDF isn't concurrent (same reason as this). And how do you deal with multi-row inserts: this would require a UDF call per row

And migrating to other RDBMS doesn't happen often in real life... you may as well not use SQL Server now and use sequences on Oracle and hope you don't migrate away.

Edit:

Your update states that moving data is for refreshing non-production databases.

In that case, you ignore the identity columns when refreshing. You don't compromise your implementation to make non-prod loading easier. Or use temp tables to track the identity value changes.

Or use processes: we refresh our test system every night from production which avoids the issue entirely. (And ensures our prod backup can be restored too)


Generally, your top priority when designing a relational database is data integrity. Performance is a distant second at best. If you sacrifice data integrity, you may one day defending the charge, "This query returns the wrong answer!" with the rationale, "Yes, but it does so very quickly."

Quite lame as far as excuses go, yes?

However, the overhead of data integrity is small and localized. By localized, I mean that the overhead is generally on the DML (Insert, Update, Delete) operations. The queries will usually perform better.

1.Should I use unique key as much as I could in the database when the value of the field is unique? Absolutely. Always. Never fail. Defining a field as unique creates an index on the field. Odds are good that any unique field will be used heavily in the filtering criteria: where uniquefield = value. Now you have data integrity and fast access.

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future? No. If you don't know enough to create a field, you don't know the data type, the uniqueness, the nullability, anything needed to properly define a field, including a proper name. It's easy enough to add a field to an existing table once you know all the particulars. There is no need to plan ahead on this.

3.In the comment_table, I use user_id for foreign key, but I heard using too [many] foreign key[s] will make the database slow, ... As I said before, data integrity constraints, such as foreign keys, can make DML a little slower, but foreign keys are completely irrelevant to queries. They are ignored, they play no role at all. So go ahead and define foreign keys, unique constraints and whatever other constraints make sense to keep a tight reign on data integrity.

It makes no sense at all to have excellent response time in a database filled with anomalous data. Fortunately, "response time" in most OLTP databases refers to query time. Proper data integrity checks will, at worst, have no effect on query response time. At best, they will actually make it better.

This is where good analysis comes in. In SQL tuning, you typically speed up query time by sacrificing DML time -- and vice versa. Typically, response time concerns for OLTP databases lean towards queries. However, there are instances where the DML is the primary concern. So which hits your tables most often: DML or queries? Generally, it's not even close -- like 100 to 1 in one direction or the other. If you can speed up the 100 side by moving a little overhead to the 1 side, so much the better.

But never sacrifice data integrity. It's never worth it.


Block insertions on a mysql table with specific value

The most powerful approach is to wrap all the basic operations like INSERT, DELETE, UPDATE and SELECT into the set of the stored routines. That acts like an API to the database and can be extended by any desired checkouts/conversions/calculations you ever want. If parameters passed to the stored routine meet some conditions you can INSERT them. Otherwise you reject those data without inserting.

Same effect can be achieved by trigger BEFORE INSERT that look like this:

BEGIN
  IF NEW.id IN (100, 200, 300, 456)
    THEN SET NEW.id = 1;
  END IF;
END

where id=1 is already existent record and id column have UNIQUE constraint. But triggers in general are unclear and non-transparent tool that can be very difficult to debug. Also autoincrements and triggers can interfere with unforeseen consequencies so it's better to avoid triggers as possible.


How to detect in which schema a procedure is called?

You can use one of the functions of Oracles owa_util's package named WHO_CALLED_ME.

To demonstrate this feature, we can create a table which will store the results of the call to Oracle's procedure.

create table T_CALLSTACK (
    OWNER_NAME      varchar2(30),
    OBJECT_NAME     varchar2(30),
    LINE_NUMBER     number,
    CALLER_INFO     varchar2(100),
    INSERTDATE      date
);

As a test procedure which will insert some values we use:

create or replace procedure P_CALLED_BY_T is

    v_Owner          varchar2(30);
    v_PackageName    varchar2(30);
    v_LineNumber     number;
    v_CallerT        varchar2(100);

begin

    owa_util.WHO_CALLED_ME (v_Owner, v_PackageName, v_LineNumber, v_CallerT);

    -- v_Owner will contain the schema name of the procedure which
    -- called this procedure itself
    insert
      into T_CALLSTACK (OWNER_NAME, OBJECT_NAME, LINE_NUMBER, CALLER_INFO, INSERTDATE)
    values (v_Owner, v_PackageName, v_LineNumber, v_CallerT, sysdate);

end P_CALLED_BY_T;

The procedure WHO_CALLED_ME will return the schema's name as well as the package name and the line number inside the calling package body, caller-T is the type of database object which called our custom procedure P_CALLED_BY_T. If the caller itself is just a procedure, the variable v_PackageName will hold the procedure's name instead of a package name.


MySQL table replication on the same server

MySQL Replication was designed to replicate data between two MySQL Instances, not two MySQL Databases within the same instance.

What you need to do is write triggers in db1.table1 to write the same changes to db2.table1.

See my old post How to set up triggers in MySQL for copying a field for some ideas.


Why 2 allocation units during online index rebuild to different file group?

How Online Index Operations Work:

  Temporary mapping index

  Online index operations that create, drop, or rebuild a clustered
  index also require a temporary mapping index. This temporary index is
  used by concurrent transactions to determine which records to delete
  in the new indexes that are being built when rows in the underlying
  table are updated or deleted. This nonclustered index is created in
  the same step as the new clustered index (or heap) and does not
  require a separate sort operation. Concurrent transactions also
  maintain the temporary mapping index in all their insert, update, and
  delete operations.

Disk Space Requirements for Index DDL Operations

  If a clustered index is created, rebuilt, or dropped online, a
  temporary nonclustered index is created to map old bookmarks to new
  bookmarks. If the SORT_IN_TEMPDB option is set to ON, this temporary
  index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same
  filegroup or partition scheme as the target index is used. (My note: default is OFF)

Index ID 254 is indeed the mapping index. The Online Indexing Operations in SQL Server 2005 whitepaper has more details.


PostgreSQL roles versus users, grant permissions

A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can also log in as. So "user" and "group" are essentially terms that indicate the intended usage of a role, there's no real distinction between them.  Even in the PostgreSQL flavour of SQL the two are used more or less as synonyms.  For example, the documentation on CREATE USER says:

  CREATE USER is now an alias for CREATE ROLE.

Granting all ... see the manual for GRANT. You probably actually want to grant rights to ALL TABLES IN SCHEMA public rather than all tables in the database.

Granting some rights ... same thing, but instead of GRANT ALL use GRANT SELECT, INSERT for example. Again, see the manual.

A role is a user, and/or a group. You can only grant to roles, because roles are all that there is.


Split Vector is taking too much time in mongo shard

The splitVector commands are slow because your system is slow, it's not the cause of the slowness itself - it just happens to be running frequently and hence is showing up a lot in your logs - it is a symptom not a cause (unless you are running many, many mongos processes and hitting an edge case).  People often make the same mistake by misdiagnosing slow serverStatus commands (run every 60 seconds by MMS monitoring) as a root cause.

Usually the slowness is because your system is oversubscribed and hitting a bottleneck somewhere, but you have not provided enough information to make a guess as to what your issue is.  I would recommend looking for other evidence in the logs as well as the usual suspects (IO, CPU, Memory) to narrow down the actual root cause here.


Installation of SQL Server RAID 10 vs RAID 5

I did a series of TCP-C tests against several of the common RAID levels and was surprised that in a write-heavy scenario RAID5 was over 3x slower than RAID10 (it was 319% slower, to be specific).  Your mileage may vary according to the actual ratio of reads versus writes in your scenarios.

-Kev


Inserting into a table a row represented by JSON

I think you want something like this.

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, t TEXT )
  RETURNS VOID
  SECURITY INVOKER
AS $$
    BEGIN
        EXECUTE FORMAT(
          'INSERT INTO %I SELECT * FROM jsonb_populate_record( null::%I, $1);',
          t,
          t
        )
        USING data;
    END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int, b text );

SELECT fn_alter_entity( '{"a": 5}', 'foo' );
SELECT fn_alter_entity( '{"a": 42}', 'foo' );
SELECT fn_alter_entity( '{"a": 5, "b": "foo"}', 'bar' );
SELECT fn_alter_entity( '{"a": 42, "b": "baz"}', 'bar' );

test=# TABLE foo;
 a
----
  5
 42
(2 rows)

test=# TABLE bar;
 a  |  b
----+-----
  5 | foo
 42 | baz
(2 rows)

But seeing where you may be going, I would highly suggest you look into PostgREST.


UPDATE waits for lock too long after SELECT in MySQL (Innodb)

Perhaps you should impose a SELECT FOR UPDATE

SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

I have mentioned this before

Mar 08, 2013 : How are DB locks tied to connections and sessions?
Mar 12, 2013 : How I prevent deadlock occurrence in my application? (See MECHANISM #3)

UPDATE 2015-02-05 22:22 EST

There will always be row locks imposed, and there is no getting around it. Yet what is the difference between your code and my code ?

Think about it. Your running the SELECT in your code does not shield the rows from changes. Those row locks you are seeing would happen during the UPDATE. My code imposes the locks on rows and all corresponding index entries during the SELECT. This makes the UPDATE have less stress.

This is in agreement with the MySQL Documentation

  A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

UPDATE 2015-02-10 22:05 EST

Your issue seems to be pointed out already by @RickJames: the index on seen field.

Take a look at the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

Please note two structures from the Picture

Insert Buffer With the InnoDB Buffer Pool (IB_IBP)
Insert Buffer With the System Tablespace (better known as ibdata1) (IB_SYSTBLSPC)

These structures are responsible for updating nonunique indexes.

Now, please note the effect of setting seen=1 on id = 315.

Assuming seen is either 0 or 1 (Cardinality of 2)

Having an index on seen with just 0 and 1 as values makes the index no different than managing two linked lists, each ordered by the PRIMARY KEY id.
Changing a 0 to 1 (or 1 to 0) will place information into IB_IBP
When it time to write the change to pics.ibd

Copy of the Changed Data Page is Written to the Double Buffer
Information Needed for Index Positioning is passed from IB_IBP into IB_SYSTBLSPC

The index entry of id 315 is moved from the side of the index where seen=0 to the other side of the index where seen=1

If you are changing the archive column in your benchmarking, these same machinations would also apply to the archive index.

In addition to the indexes being churned into and out of the InnoDB Buffer Pool, don't forget where this question started: ALL THE ROW LOCKS.

Keep something in minds about row locks. When a row lock is issued, an entire page can also be locked. The page can be the location of multiple primary key values. I have another posts where I discussed page locks (Dec 31,2012 : Queries getting stuck on very simple COUNT queries)

Please note in your comment, that you said

  And what is more important that in the most cases it works fast

Why not all cases ? Because of the reasons I just mentioned. @RickJames did mention first the seen index, and he is right to suggest removing that index. Along those same lines, I suggest removing the archive index as well (especially if the archive value is being changed in the benchmark). Without removing those indexes, look at all the stress you are imposing on the InnoDB Infrastructure even though the table has 70 rows. It would be far worse with thousands or millions of rows.


Is there a way to prevent special characters on schema objects?

MySQL was designed to include special characters in Schema Object Names such as

database names
table names
column names

Certain characters (such as ' '(blank), hyphens (-), and other strange characters) can still be used within Schema Object Names by enclosing the identifier in backquotes (`).

According to the MySQL Documentation on Schema Object Names, there are a variety of characters that are permitted within Schema Object Names:

  Identifiers are converted to Unicode internally. They may contain
  these characters:

  Permitted characters in unquoted identifiers:

  ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar,
  underscore)

  Extended: U+0080 .. U+FFFF

  Permitted characters in quoted identifiers include the full Unicode
  Basic Multilingual Plane (BMP), except U+0000:

  ASCII: U+0001 .. U+007F

  Extended: U+0080 .. U+FFFF

  ASCII NUL (U+0000) and supplementary characters (U+10000 and higher)
  are not permitted in quoted or unquoted identifiers.

  Identifiers may begin with a digit but unless quoted may not consist
  solely of digits.

  Database, table, and column names cannot end with space characters.

You are welcome to get the source code, tweek it to check all characters in any potential Schema Object Identifier, and either allow or deny.

The only thing I can suggest is never to use backquotes when creating databases and tables.


Absolute beginner needing some help with PostgreSQL and Python

DATABASE

The primary function of a database is to ensure that you always have valid data.

Defining valid data is done with CONSTRAINTS (including NOT NULL) and Data Types.

Your application design seems to be based on the idea: "a database is a bit bucket to store data".

This isn't how you treat a database.

A lot of your requirements can be handled by the database if and only if you properly define what is valid data.

Your applications should:
 1. Try to modify the data.
 2.  If the modification fails, handle it within your application.

Constraints

Here is how constraints can throw an error for your application to handle your specified situations.

Adding row to Search table (scan entry)

After you type in the membership ID, you attempt to INSERT a row into the Search table.

The Foreign Key Constraint will automagically lookup the membership_id in the User's table.  If the membership_id is not found, the INSERT throws an error.

Adding row to User's Table (membership info)

After you ask for the person's name for the given membership_id, you attempt to INSERT a row into the User's table.

A Primary Key Constraint will ensure that one row and only one row exists in the User's table with that membership_id.

If that membership_id already exists, an error is thrown for your INSERT statement.

Updating User's information

You are going through a "update customer info" set of pages.  You attempt to UPDATE a row in the User's table.

If that membership_id does not exists, an error is thrown for your UPDATE statement.

If row exists, UPDATE; if not, INSERT

Some databases have a MERGE or UPSERT statement that allows you to UPDATE a row.  If it doesn't exist, it will INSERT the row.

Lookup Member's Name from User's Table (membership info)

You SELECT some data from the Users table.

I'm not 100% sure if you'll receive a "no data found" error for this one.

However, you can always check if you have received 0, 1, or more rows within your application.

Race Condition

A lot of your "Race Condition" concerns should be handled by an ACID Compliant database (eg PostgreSQL)


Can you set up a SQL server maintenance plan to restore a database?

Yes you can. After your backup task, add an Execute TSQL task that has the restore command. Join them together with a success constraint.

Add a Send Mail task and join it to both the backup task and the restore task. Make it an OR constraint and convert it to a failure constraint rather than success


Replication feature in SQL Server 2017 on Linux

At the moment, replication is not supported on Linux. However, according to the release notes, replication support will be added in a future release.

Release notes for SQL Server 2017 on Linux


SUGGESTION #1
Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings:
innodb_buffer_pool_instances=2 # If your Server is DualCore
innodb_buffer_pool_instances=4 # If your Server is QuadCore
innodb_buffer_pool_instances=6 # If your Server is HexaCore
innodb_buffer_pool_size=90G

I addressed InnoDB and Swapping in one of my older posts (How do you tune MySQL for a heavy InnoDB workload?). I referred to Jeremy Cole's blog The MySQL “swap insanity” problem and the effects of the NUMA architecture. You might need to increase innodb_buffer_pool_size to 60%-75% of RAM. In your case, I would go with 90G (70.3125%).
SUGGESTION #2
For more CPU engagement I recommend these
innodb_read_io_threads=8
innodb_write_io_threads=16
innodb_thread_concurrency=0

I discuss why in my old posts

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

OTHER SUGGESTIONS

You might more RAM on the DB Server (Setting innodb_buffer_pool_size to 60-75% of RAM)
You might need to tune your queries if you read lots of unnecessary data as indicated by the Innodb_buffer_pool_read_ahead_evicted status variable.


DB2 GLOBAL VARIABLES Refresh for datapool managed sessionis

You can set variables to a new value in a session but you would need your application to spot the date change and update the variable for the session accordingly. Or alternatively, kill all sessions when the date changes!


MySQL - InnoDB - Partitioning a table with uniqueness

In my answer to the other Question, I was assuming you wanted to add PARTITION BY RANGE (TO_DAYS(updated_at)) and then DROP PARTITION to rapidly delete "old" records.

The Question here talks about using PARTITION BY COLUMNS(_month).  There is essentially no difference.  However, what do you expect to happen after you finish with month #12?  Move on to 13?

I don't see a way to get a one-step uniqueness check and have partitioning.  I do provide some non-partition DELETE optimizations here .  That will let you use a UNIQUE key and have some performance in the deletion process.

PRIMARY KEY (uniqueness, __month) provides a uniqueness constraint on the pair, but not uniqueness by itself.  One failing of PARTITIONing is the inability to declare something unique across all partitions (unless the partition key is part of it).

If you ever need to search across a range of months, you can't do it across December to January.


Accept null value on parent column in multipart relationship

Set [OwnerId] on OwnerID to allow nulls.
Put a null value record in your Category table:

INSERT INTO Category (OwnerId,Title) SELECT NULL,'blah??'

I also added  to your category table:

CONSTRAINT UC_OwnerID UNIQUE(OwnerID)

this is what I have working for you in SQL Fiddle http://sqlfiddle.com/#!3/ac720/2.


How to make a table name an attribute in PostgreSQL?

This can be considerably simpler and faster:
DO
$do$
BEGIN
EXECUTE (
   SELECT E'CREATE TABLE t_new AS\n'
          || string_agg(format('SELECT tableoid::regclass AS source_table, * FROM %s'
                              , c.oid::regclass::text)
                       ,E'\nUNION ALL\n' ORDER BY c.oid::regclass::text)
   FROM   pg_namespace n
   JOIN   pg_class c ON c.relnamespace = n.oid
   WHERE  n.nspname = 'public'  -- your schema here
   AND    c.relkind = 'r'       -- only tables
   AND    c.relname LIKE 't_%'  -- your table pattern here
   );
END
$do$;

Replace * with your desired columns.
Produces and executes a query of this form once:
CREATE TABLE t_new AS
SELECT tableoid::regclass AS source_table, * FROM t_20140707
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140708
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140709
...

So you get one new table containing everything plus the source table name.
Explain

A DO statement is a good choice for a one-time operation. You cannot return values from it, but you can create a new table. You may want to use a TEMP TABLE ...

tableoid::regclass works to retrieve the (properly escaped) table name of the source table and is very handy in various situations. More details:
Get all partition names for a table

I build a query in a single meta-query instead of looping, which is almost always faster and cleaner (if possible). Related answer:
Loop through like tables in a schema

Queries on catalog tables are regularly much faster and offer everything Postgres actually has. For instance the system column oid I am using here. Aside from that, there are pros and cons for using the information schema. Mostly it is useful to achieve some portability across major versions and various RDBMS. Portability doesn't seem relevant for a one-time operation and it hardly ever works properly in my experience anyway. More on that:
How to check if a table exists in a given schema


How to create unique index for both cases

For Postgres and Oracle:

create unique index unique_combinations
    on my_table (least(sender_id, recipient_id), greatest(sender_id, recipient_id));


How to password protect a field in access?

The following is solely based on Internet research so your mileage may vary.

It appears that you can password protect certain tables.  This article walks you through the process.  Essentially what you need to do is create a workgroup.  Once your workgroup is created you login into your database via a link that workgroup creation wizard produces, and you can set the permissions for all of your users.

I also found this answer on StackOverflow that says it can't be done so who knows.

The more robust solution would be what is suggested as an alternative in the StackOverflow answer above.  Just upgrade to SQL Server Express.  That should be a low cost solution that will offer a nicer, more manageable RDBMS in my opinion.  However I recognize that transition isn't always an option.


So in your case, currently all the nodes(A, B and C) are running as standalone nodes with a different set of data.

Now you are trying to establish a replica with the existing nodes.

As per your question, first, you are trying to convert node A to a replica set member and then adding B and C to the replica set. So mostly, in this case, node A will become primary and B and C will be added as secondary. Since B and C are added as secondary for one time it will replicate all data from A. This one time process is called Initial Sync.

When Initial Sync is happening, mongod removes the contents of the dbPath directory of B and C node.

Please follow the steps in Deploy a Replica Set to set up the replica set.

Answering your second question, this procedure will delete all users and data from Node B and C.


Cannot create a row of size 11842 which is greater than the allowable maximum row size of 8060

This is down to the implementation of how VARCHAR(MAX) and NVARCHAR(MAX) actually store their data. If the number of bytes needing to be stored per column is less than or equal to 8000 bytes then the data will be stored in-row, if it is greater than 8000 bytes then it will be stored in LOB pages. When this happens, the the row holds a pointer to the LOB page that actually contains the data.

So, working backwards, the average length of data attempting to be stored stored in each column is:

(11842 - (4 + 510)) / 299 = 38 (approximate bytes / 19 characters)

As the value for each column is underneath the 8000 byte limit, it will attempt to store the data in-row and therefore exceed the limit for a page.

You can check out sys.dm_db_index_physical_stats to find out how much is actually stored in-row vs in LOB pages. Also, SQL Hints has a decent article that covers the basics.

Since you're probably stuck with this vendor database, and the data being imported, you can force off-row storage for all nvarchar(max) columns in the table with:

EXEC sp_tableoption 'dbo.mytable', 'large value types out of row', 1;


Error in in sysft_Name while restoring database

I think you need a "\" at the end of the path.

RESTORE DATABASE [new] FROM DISK = N'E:\a\L11.bak' WITH FILE = 1, MOVE N'itest' TO N'E:\a\3.mdf', MOVE N'itest_log' TO N'E:\a\4.ldf', MOVE N'sysft_test' TO N'E:\a\', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO


SQL job is completing without sending email to configured recipients

The issue was the size limitation. We just made the file smaller using a shorter date range. Also changed size limit through sql function in case we wanted to select a larger date range later.

Reference: Database Mail Stored Procedures (Transact-SQL)

Particularly:

sysmail_help_configure_sp
sysmail_help_profile_sp
sysmail_help_account_sp
sysmail_help_principalprofile_sp
sysmail_help_profileaccount_sp


How to implement optional query parameters

You should not use user_id$i is null or c.agent_id = user_id$i. The optimizer is not able to handle the 2 branches separately and you will end up with a full table scan. Here is a simple blog post about it: link

The NVL version (c.agent_id = nvl(agent_id$i, c.agent_id)) is slightly better. There is a feature called NVL optimization, where the optimizer creates 2 branches from an NVL predicate and performs an UNION ALL on them. This is also demonstrated in the above blog post. This works on very simple queries like the one you posted above, but if you have more parameters like this, it will not work and you will end up with full table scan again.

You can also rewrite your query to use UNION ALL as already suggested in the comment.

To be honest, I do not recommend any of the above.

I have seen this countless times, where for example there was a form with multiple search fields, and clicking on search eecuted a query with lots of columnX = nvl(paramX, columnX) predicates. That will perform poorly, because the above small optimization does not work on complex queries with a lot of parameters.

The same goes for UNION and predicate pushing. If you have a predicate outside of the UNION that could be pushed down to the branches, the database can do that. It works with simple queries, but if you a complex query with multiple UNION, that may or may not happen.

Such queries cause a lot of performance problems. Developers think it results in simpler code and it is elegant to handle separate cases with a single SQL. The database is not good at that. It can not optimize complex queries for multiple outliers based on multiple parameters. If you want performance, you have to work for it. You should change the SQL based on the parameter. With your example, if a value is specified for the parameter use this:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1
    and c.agent_id = agent_id$i;

If there is no value specified for the parameter use this:

select c.agent_id,
    c.date_time
from calls c
where 1 = 1;

With such a simple query it does not make sense, but as your query gets more complex with more predicates, that is the way to go.


According to this StackOverflow post, if you have the server-level settings (referenced below) configured with ForceEncryption set to Yes, then that will enforce an encrypted connection regardless of the SSMS GUI Encrypt connection option being checked or not prior to making connections to that server. This may be a sufficient workaround for people where the "encrypted connection" is of more importance than the actual option being checked within the SSMS GUI.

According to Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager), you do this as such:

  To configure the server to accept encrypted connections

  In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then
  select Properties.
  In the Protocols for Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for
  the Certificate box, and then click OK.
  On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
  Restart the SQL Server service.

  To configure the client to request encrypted connections

  Copy either the original certificate or the exported certificate file to the client computer.
  On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.
  In the console pane, right-click SQL Server Native Client Configuration, and then click Properties.
  On the Flags page, in the Force protocol encryption box, click Yes.

  source


How to unlock a table in IBM DB2 Data Studio 3.1?

Three unlock table commands are

    COMMIT
    ROLLBACK
    DISCONNECT


Detailed behaviour of mongos in context of sharded Mongo cluster

It does the second option 'route the insert to the correct regional shard directly`.

See this answer for details.

Where to insert data after sharding?

More resource:

https://docs.mongodb.com/manual/core/sharded-cluster-query-router/


You could do it like this:

SELECT  CONCAT(Name,' ', Surname) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;

DB-Fiddle

As a remark, if Fullname or Surname can be NULL then you would have to add a check for that, could be done with IFNULL()

SELECT  CONCAT(IFNULL(Name,''),' ', IFNULL(Surname,'')) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;


How can I change the names of all tables to uppercase?

To rename table names in UPPER Case:

SELECT 'EXEC sp_rename '
+ '''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ''', '
+ '''' + UPPER(TABLE_NAME) + ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

To rename Columns names in UPPER Case:

SELECT 'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' +
COLUMN_NAME + ''', ''' +
UPPER(COLUMN_NAME) + ''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
'isMSShipped') = 0

Result generated by above commands can run on query window to rename table name or column name in upper case respectively.


Best practices for allowing rollback of individual customers on a shared database

Mimic the transaction system of PostgreSQL:

Every affected table has a column visible_from and visible_to, type timestamp with time zone.

When you update or delete a table, set visible_to on the old version of the row to the current timestamp rather than actually updating or deleting it.

When you update or insert a table, add a new row with visible_from set to the current timestamp.

This way you can determine the state of the table at any given point in time. Rolling back is now a matter of deleting some rows and clearing visible_to on others.
Periodically a job removes data with visible_to older than a certain grace period.


MySQL Foreign Key Prevents Deleting Table — But Table is Empty

You shouldn't be able to drop a table that is the parent of another dependent table, even if it has no data. Because if you drop the parent, then you will never be able to insert any data in the child. You must first drop any child tables that reference enterprise_rma.

Something doesn't match your description, though. You said you were having trouble dropping the table, but your example about the foreign key checks shows you using DELETE.

DELETE is not the same as DROP TABLE. After DELETE, there are no rows in the table, but the table still exists. After DROP TABLE, the table doesn't exist, i.e. it doesn't show up if you run SHOW TABLES.


backup a specific set of tables to be moved to a remote system

Just take a full backup (with compression, if possible), restore it on the other server, and then you can select into or insert/select without having to worry about what happens to carriage returns, line feeds, and other characters that cause problems for files. You want to move (parts of) a database, not files.

Trying to just extract some 120 million rows from a database sounds like more trouble than it's worth. You're making this harder than it should be, for what? To save a few bytes in data transfer?


How can I drop a view only if it exists in Oracle?

An alternate method:

  DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP VIEW my_view';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
  /

This has the added benefit of not needing to query the data dictionary .. just try to fire the drop .. and if it doesn't exist .. that's fine .. no real issue.


Migrating Oracle smallfile to bigfile tablespace

You have a few options.

Create your new bigfile tablespaces, then either:

1) Move the tables one by one with:

alter table mytable move tablespace bigfile_tablespace;

Remember to move indexes too!

alter index myindex rebuild tablespace bigfile_index_tablespace;

2) Export the database with Data Pump, drop the existing objects, then reimport using a remap_tablespace clause, for example:

impdp remap_tablespace=OLDSMALLTS1:NEWBIGTS1,OLDSMALLTS2:NEWBIGTS2 directory=mydir dumpfile=mydumpfile.dmp logfile=mylogfile.log

3) Use DBMS_REDEFINITION to move the objects. An example of this can be found in my answer to another question, here.

Once done, drop all of the old objects and then the tablespaces.

If you can afford the downtime, datapump will be the easiest option.


How to create new table with same constraints and indexes?

SELECT INTO is not going to do this for you, because while it maintains the column names and data types, it doesn't keep a lot of the other aspects of the table, such as constraints, indexes, etc. The only thing it really keeps outside of the columns is the IDENTITY property if one of the columns has it.

Right-click the original table in Object Explorer, and select Script Table As > Create To > New Query Window. You should have a CREATE TABLE script for the original table, including all the constraints, etc. Note that you may have to set some of these settings (Tools > Options > SQL Server Object Explorer > Scripting) to true in order to get all of the table attributes you want, so this may take some trial and error:

Now, just hand-modify that script to specify the new table name, and make sure to adjust all of the constraint and index names such that they are unique (how much work this will be will depend on whether you have an established naming convention or if you just take what SQL Server will throw at you).


You can use partition by reference option
Example :

create table trans (
  trans_id     number not null,
  res_id        number not null,
  trans_date  date not null,
  amt           number,
  constraint fk_trans_01
    foreign key (res_id)
    references res
)
partition by reference
   (fk_trans_01);

Some restricions :

Parent table Primary Key and Unique constraints used for  reference
partitioning must be enabled and non-deferrable.
Child table “partitioning constraints” must be enabled and
non-deferrable
All child table columns used in “partitioning constraints” must  be
defined as NOT NULL.
This is required to assure that every child row maps to exactly one  parent row.
Child tables created using reference partitioning can never  be
disassociated from their parents without being dropped. – There is no
ALTER TABLE command to convert a reference  partitioned table into a
non-reference partitioned table.
As with other tables referenced via foreign key constraints,
parent tables cannot be dropped until all foreign key  constraints
referencing them are removed.  – In the case of reference
partitioning, this means all descendent  tables must first be
dropped, since the partitioning constraints on  the child tables
cannot be disabled or dropped.

NOTE:

Reference Partitioning has strong advantages and strong limitations,
so its use requires careful planning.

Reference Partitioning should not simply be used wherever possible.
Use of Reference Partitioning appears best suited for very large
tables  and related tables, whose contents share the same life cycle.


Unify specific tables in one product table

Why not add product_type_attributes into design 1? If product_type_attributes is necessary and it is in multiple types then design 2 is much better compared to design 1 because it is more normalized.


Error 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

The fix was to specify the IP address when creating the user and granting it permissions:

CREATE USER 'demouser'@'127.0.0.1' IDENTIFIED BY 'p';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON demodb.demotable TO 'demouser'@'127.0.0.1';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.01 sec)

Then you can log in this way:

mysql -u demouser -p -h 127.0.0.1 demodb

Credits to http://www.rathishkumar.in/2017/04/error-1045-28000-access-denied-for-user-user-host-using-password-YES.html


How to compare time portion of dates for multiple dates

which have a date column which looks like 2020-10-12 12:45:55

That's not a date column, but a timestamp (date and time). Assuming type timestamp, not timetamptz? (You should always disclose actual table definitions.)
SELECT *
FROM   tbl
WHERE  timestamp_in_disguise::time >= '22:00'
OR     timestamp_in_disguise::time <= '05:00';

See:

Postgres data type cast

Or:
...
WHERE  timestamp_in_disguise::time NOT BETWEEN '05:00' AND '22:00';

The first includes bounds and you can adjust as needed.
The second uses BETWEEN, which always includes bounds - so excludes them in the negated expression.
You can still use a hack to include bounds building on the  inside knowledge that Postgres stores times and timestamps with microsecond resolution (6 fractional decimal digits) in its current implementation.
...
WHERE  timestamp_in_disguise::time NOT BETWEEN '05:00:00.000001' AND '21:59:59.999999';

But I strongly advice against the latter. Building on implementation details is brittle and ugly.
Casting the string literals to the right is optional as their type is derived from the typed column.
If we are, in fact, dealing with timestamptz you need to define where in the world it's supposed to be 22:00 etc. (You may need to think about that with timestamp, too.) See:

Ignoring time zones altogether in Rails and PostgreSQL

About BETWEEN and including lower and upper bound:

How to add a day/night indicator to a timestamp column?

If you run this kind of queries a lot, consider an expression index:
CREATE INDEX ON tbl ((timestamp_in_disguise::time));  -- parentheses needed


Script fails to run as SQL Server Agent Job, completes successfully when run from SSMS

The default for QUOTED_IDENTIFIERin SSMS is ON while it is OFF for SQLCMD, OSQL, BCP and in your case especially SQL Server Agent.

For reference, these are the different default options per application

+-------------------------+------------+------+-------------------+------------+
|                         |  ADO .Net, | SSMS | SQLCMD,OSQL, BCP  | ISQL       |
|                         |  ODBC      |      | SQL Server Agent  | DB-Library |
|                         |  OLE DB    |      |                   |            |
+-------------------------+------------+------+-------------------+------------+
| ANSI_NULL_DFLT_ON       | ON         | ON   | ON                | OFF        |
| ANSI_NULLS              | ON         | ON   | ON                | OFF        |
| ANSI_PADDING            | ON         | ON   | ON                | OFF        |
| ANSI_WARNINGS           | ON         | ON   | ON                | OFF        |
| CONCAT_NULL_YIELDS_NULL | ON         | ON   | ON                | OFF        |
| QUOTED_IDENTIFIER       | ON         | ON   | OFF               | OFF        |
| ARITHABORT              | OFF        | ON   | OFF               | OFF        |
+-------------------------+------------+------+-------------------+------------+

The above table is copied from  Erland Sommarskog

So you would need to add SET QUOTED_IDENTIFIER ON as the first line in your job step.


Partitioning a table with LOB columns in Oracle 11g

Yes, it seems you overlooked it indeed:

PARTITION BY HASH(X)
(PARTITION P1
    TABLESPACE T1
    LOB (my_lob_column) STORE AS (TABLESPACE T1_LOB  /* here */ ),
 PARTITION P2
    TABLESPACE ts2
    LOB (my_lob_column) STORE AS (TABLESPACE T2_LOB))
)


Downloading image data type contain to desktop SQL Server 2008

This answer provides the following Simple Talk article with instruction and information on how to extract this data.  Unfortunately, there is no cut and dried method for natively converting this data because of the hurdles in storing this kind of data within a database table.

For more discussion on this practice and the issues surrounding it, I suggest you read this answer.


Oracle: Connects with sqlplus but not from perl

The connection string format is wrong. Use

$dsn = "DBI:$driver:$host/$sid";


An Oracle database instance is made basically of three kinds of files: database files (aka datafiles), controlfile, and redo log files.

You can check their names and paths by issuing these simple queries, logged with a DBA privileged account:

select name   from v$datafile;
select name   from v$controlfile;
select member from v$logfile;

Your question is a very newbie one. No problem with it, of course, but you should take a read on Oracle Database Concepts manual.

https://docs.oracle.com/database/121/CNCPT/toc.htm


As others have said, shrinking a database in this situation is not at all inappropriate.

What the experts mean when they say things like "Stop Shrinking Your Database Files. Seriously. Now." is that you don't want to shrink them just because they are a little bit bigger than you expect. You want to make sure you know why your database is "too big" before you shrink it. Otherwise, you're only treating the symptom, not the disease.

For example, maybe a big weekly data import causes your database to grow by 200 GB every Sunday, and then a consolidation removes 195 GB of that data every Monday. If you look at your database on Tuesday, it'll seem to be much bigger than it needs to be. But if you shrink it, it'll just grow back on the following Sunday.

In the case presented in this question, the size of the databases is the result of an erroneous initial setup, rather than the result of normal activity. As such, shrinking it makes good sense.


Database email to know the history of backups

There are various SQL scripts available online, for eg: one can be found here to retrieve the backup history and then use sp_send_dbmail to send the email for the retrieved backup history as needed by scheduling it via SQL agent job.

However, the method that i have been using is to make use of Powershell to send such reports as described here Use PowerShell to Report SQL Server Backup Status

You can save the above script and execute it manually whenever you need to. However, to get the most out of it, its recommended scheduling it daily by using SQL Server Agent. Doing this will give you early notification of issues and allow you to act quickly to get those databases backed up.


There is nothing inherently wrong with NULL values present in every row, especially if that solves your problem.

If you are using SQL Server, and are expecting the majority of rows to contain a numerical selection, with only a very small number of audio answers, you might decide to make the AudioID column "sparse".  See the Microsoft Docs for details about how to use sparse columns.

Definitive answers to database design questions like this are highly dependent on a variety of factors, none of which you've detailed in your question.  For instance:

How many rows are you expecting to insert, update, delete on daily, weekly, and monthly basis?
How wide will each row be?  If each row is hundreds of text columns wide that will necessitate a different answer than if each row is three integer columns wide.
Are you planning to query the data directly, or will you aggregate it from this table into a data warehouse?

If you can, please add the answers to these questions using the edit link directly beneath your question.


Why does Log Shipping .TRN file copy just stop

Restarting both the source and destination servers housing the sql engines seemed to fix the issue. its been a week and all backup, copy and restore has no issues except for just that one SharePoint reporting db WebAnalyticsServiceApplication_ReportingDB_77a60938-####-####-####-##########.
Thanks to everyone for their input, as I at least came out of this more knowledgeable.


SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error

You are overcomplicating things.

You only need one derived table (aka "sub-select"). Using a lateral join makes it possible to push the values from the outer query into the sub-select:

select a1.empid, a1.log_date::date, sum(a2.log_date - a1.log_date) as duration
from activity a1
  join lateral (
    select min(off.log_date) as log_date
    from activity off
    where off.action = 'OFF'
      and off.log_date > a1.log_date
      and off.empid = a1.empid
  ) a2 on true
where a1.action = 'ON'
group by a1.empid, a1.log_date::date;

This assumes that an OFF event always comes after an ON event.

a2.log_date - a1.log_date returns an interval. Do turn that into minutes, extract the epoch value from that interval which is the interval in seconds. Then divide that by 60:

extract(epoch from sum(a2.log_date - a1.log_date)) / 60 as minutes


postgresql replay_location in pg_stat_replication is empty

It looks like you are seeing this on the master.  The documentation says this about replay_location:

  Last transaction log position replayed into the database on this standby server

If this DB was never a standby yet, that column will be empty.

Note: you should upgrade to the latest minor version (9.5.6 at the time of writing).


How to split data from column to multiple columns

String splitting functions return the data in rows, because handling dynamic columns is a lot more complex in SQL because that's not really what SQL is supposed to be used for.

To get data in columns, you can use for example pivot or max + case, something like this:

select
  max(case when ItemNumber = 1 then Item end),
  max(case when ItemNumber = 2 then Item end),
  max(case when ItemNumber = 3 then Item end),
  max(case when ItemNumber = 4 then Item end),
  max(case when ItemNumber = 5 then Item end)
from
  [dbo].[DelimitedSplit8K]('Masha/Sasha/Nadia/Loki','/')

You could also create a function / SQL that uses charindex + substring to find the places of the separator and just select the 5 pieces directly without creating them into rows.

If you need to handle dynamic amount of columns, then you'll have to create the SQL dynamically and calculate the maximum number of separators in your data first.


Sync many client database to one central database

It seems to me that you're falling into an XY Problem. You want to see the data from lots of different databases, and have the mindset that you should only connect to a single database. That line of thinking logically lead you to the conclusion that you need to bring all the information into one central database.

IMO your real problem is needing to see the data in multiple databases in one application.

One way to accomplish this is to merge the separate databases back into a central database like you suggested. Since you aren't looking to modify the existing clients to use the master database this seems like a complicated and error prone plan that will increase your maintenance costs.

You have to figure out how to merge separate databases without conflicts.
You have to worry about performance tuning the master database separately from the clients since it is going to naturally have much more data than any single source database.
You are artificially increasing the storage/hardware/licensing costs by creating a master database that is only going to be used by a small number of users.
While the space/size constraints of SQL Express might work for the client databases, once all the databases are pulled into the master database the combined total might overwhelm SQL Express's constraints.

I would recommend you consider taking a different approach. Instead of concentrating the databases why not concentrate the connection strings? Following this route you would only have to write a screen for the admins to select the project and then perform a lookup for the correct connection string for the project's database. Then you would be able to essentially use the client but dynamically swap out the connection string depending on what database is being targeted.


How to generate list of dates --- ie every day for the past 2 years?

Using pure T-SQL, you can do the following:

;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();

Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:

SELECT [DatetimeVal]
FROM   [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');

If you want the ending date to be in the future, just replace the GETDATE() in either query.


Perhaps this will help.  If your data looks something like this:

create table mytab1 as (select level myid1 from dual connect by level <=2000000);
create table mytab2 as (select level+20000 myid2 from dual connect by level <=100);

No indexes:

  Cost: 881, Consistent Gets: 3140, CPU Used: 174

Index on MyTab1 Table:

create index myid1_1 on mytab1 (myid1);

  Cost: 104, Consistent Gets: 103, CPU Used: 14

Index on both MyTab1 and MyTab2:

create index myid2_1 on mytab2 (myid2);

  Cost: 53, Consistent Gets: 102, CPU Used: 12

Your mileage will vary depending on quite a few factors, but in general an index on myid1 would probably be beneficial.

If you haven't already you should read through the Oracle Concepts Guide (pdf) particularly the section on indexing.


SET NEW.size = LENGTH(OLD.file);

No SUM, since there is only one row.
Specify which file with OLD..


Try this.

Select * FROM @stuff
  WHERE vendorid = @vendorID
  AND loglevel Is Null


Hard for us to know if it will be a net positive. These are pretty much no-brainers:

setting all operations to ONLINE, because you can
ignoring tables with < 1000 pages

You should still test those, though.

But we have no idea what impact defrag/rebuild has on your read workload, and whether holding off until some % is better, gains you nothing, or maybe even makes some read queries worse.

Other people have already perfected this type of maintenance and taken you out of the guessing game. Get Ola Hallengren's scripts or our tool, Fragmentation Manager. You can make changes like you're suggesting by simply changing parameter values. And don't forget that these people have already worked out lots of bugs you haven't come across yet and thought of things you haven't thought of yet. And when new operations are offered online in newer versions, or new options are added to different maintenance features, these tools and scripts will be updated for you. What you've done is affectionately known as re-inventing the wheel:

Re-Blog : The cost of reinventing the wheel

Refusing to move to a more automated solution because of investments in the past is kind of like keeping a terrible housekeeper or gardener because you paid them last year. Sometimes you just need to cut the cord.


How can I manage to view if a table has been read recently in postgresql 12?

Unless the table is only ever accessed via stored procedures or other programmed logic and you have that set to audit access, and measures are taken to stop access by more direct means, then you probably can't.
DBMSs usually don't log read access in a manner that you could query this way, if at all.


Tuning a query with: index, window function?

Simplest with DISTINCT ON - and also fast for data distribution with few rows per code_id:

Select first row in each GROUP BY group?

SELECT DISTINCT ON (code_id) *
FROM   win_a
WHERE  effective_date < current_timestamp
ORDER  BY code_id, effective_date DESC, id;

And add a NOT NULL constraint to the column effective_date or you have to make that:

...
ORDER  BY code_id, effective_date DESC NULLS LAST, id;

And adapt the index accordingly:

CREATE INDEX win_a_idx on win_a (code_id, effective_date DESC NULLS LAST, id);

Explanation:

Unused index in range of dates query

Note also that I added the id column!
However, Postgres may not use the index at all. For few rows per code_id, and while you select all of them, a sequential scan may be fastest in any case.

For many rows per code_id other query techniques may be faster:

Optimize GROUP BY query to retrieve latest record per user

For your case - unless you have a separate table holding all possible code_id:

WITH RECURSIVE cte AS (
   (  -- parentheses are required
   SELECT *
   FROM   win_a
   WHERE  effective_date < current_timestamp
   ORDER  BY code_id, effective_date DESC NULLS LAST, id
   LIMIT  1
   )
   UNION ALL
   SELECT w.*
   FROM   cte c
   ,      LATERAL (
      SELECT *
      FROM   win_a
      WHERE  code_id > c.code_id  -- lateral reference
      AND    effective_date < current_timestamp -- repeat condition
      ORDER  BY code_id, effective_date DESC NULLS LAST, id
      LIMIT  1
      ) w
   )
SELECT *
FROM   cte
ORDER  BY code_id;

This is actually going to use the index.

As for your question 1: a test table with just 500 rows is not very useful. Postgres will use a sequential scan in any case. An index is not going to be faster for a small table like that.


Is it possible to modify postgresql.conf using pg_ctl?

Reading the documentation page for pg_ctl carefully shows you what happens with the stuff you pass to it using -o:

  -o options

  Specifies options to be passed directly to the postgres command; multiple option invocations are appended.

So you can, for example, do the following:

pg_ctl -D {your data directory} -o "-c listen_addresses='*'" start

where the whole double-quoted expression is passed to postgres (for details, see its own documentation).

As Craig Ringer pointed out in a comment, it might be easier and, more importantly, has a lasting effect if you change these parameters using ALTER SYSTEM.


Let's analyze the string '5.6.11-osx10.7-x86'.
5.6.11 is the MySQL version.
osx is the platform.
10.7 is the version of the MacOS X package for the 5.6.11 MySQL version.

That package was compiled on a x86_64 machine.


How does the log reader process bulk updates

In the log records those are separate statements that are written to the log.  The log reader would then batch those up and run them as a single transaction against the database.

If your database uses stored procedures, you can replicate the stored procedures, which would then allow the stored procedure call to be replicated instead of the actual data updates.


Rolando's solution has many caveats.  The first being one replica stream is necessarily not replicating while the other works.  This is going to give you periods of time where your slave is out of synch.  You now have to play a delicate balancing act to ensure each has enough time to catch up when it has its "turn".

As described you also have to play book keeper of log positions to switch back to.  This really just seems buggy, opening the window for missing or inconsistent data or even breaking replication when it goes wrong (either being caused by even a just 'off by one' error in the log position)

I would recommend just running multiple mysql instances.  There's nothing stopping you from running two or more mysql's on the same machine.  They cannot both operate on the same port of course.  I don't really see this as being a problem though as every client and library allows you to specify something other than 3306.

Just specify port=3307 (or whatever in one of the .cnf files).

You will also want to take care in ensuring the individually configured buffer pools and other memory configurations aren't at odds with each other.  This is actually a benefit though as you can more finely tune those settings to the specific requirements of the individual databases that are being replicated.

This way you just have two replication streams running into the same server; never behind, no book keeping required, no "swapping" script required.


MySQL -> Loop through a table, running a stored procedure on each entry

Create a second procedure that uses two nested cursors.

Cursors in stored procedures allow you to do a very non-SQL-like thing: iterate through a result set one row at a time, putting the selected column values into variables and doing things with them.

They are easily misused, since SQL, being declarative rather than procedural, should usually not need "for each"-type operations, but in this case, it seems like a valid application.

Once you get the hang of them, cursors are easy, but they do require a structured approach in their supporting code that isn't always intuitive.

I recently provided some fairly standard "boilerplate" code for working with a cursor to call a stored procedure in an answer on Stack Overflow, and I'll borrow very heavily from that answer, below.

Using a cursor requires some standard boilerplate code to surround it.

You SELECT the values you want to pass, from wherever you're getting them (which could be a temporary table, base table, or view, and can include calls to stored functions) and then call your existinf procedure with those values.

Here is a syntactically valid example of the necessary code, with comments to explain what each component is doing.

This example uses 2 columns to pass 2 values to the called procedure.

Note that there events that happen here are in a specific order for a reason.  Variables have to be declared first, cursors have to be declared before their continue handlers, and loops have to follow all of those things.

You can't do things out of order, so when you nest one cursor inside of another, you have to reset the procedure scope by nesting additional code inside BEGIN ... END blocks within the procedure body; for example, if you needed a second cursor inside the loop, you'd just declare it inside the loop, inside another BEGIN ... END block.

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_proc` $$
CREATE PROCEDURE `my_proc`(arg1 INT) -- 1 input argument; you might need more or fewer
BEGIN

-- declare the program variables where we'll hold the values we're sending into the procedure;
-- declare as many of them as there are input arguments to the second procedure,
-- with appropriate data types.

DECLARE val1 INT DEFAULT NULL;
DECLARE val2 INT DEFAULT NULL;

-- we need a boolean variable to tell us when the cursor is out of data

DECLARE done TINYINT DEFAULT FALSE;

-- declare a cursor to select the desired columns from the desired source table1
-- the input argument (which you might or might not need) is used in this example for row selection

DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
 CURSOR FOR
 SELECT t1.c1,
        t1.c2
   FROM table1 t1
  WHERE c3 = arg1;

-- this fancy spacing is of course not required; all of this could go on the same line.

-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
-- and since this is a CONTINUE handler, execution continues with the next statement.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- open the cursor

OPEN cursor1;

my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
LOOP

  -- read the values from the next row that is available in the cursor

  FETCH NEXT FROM cursor1 INTO val1, val2;

  IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
    LEAVE my_loop;
  ELSE -- val1 and val2 will be the next values from c1 and c2 in table t1,
       -- so now we call the procedure with them for this "row"
    CALL the_other_procedure(val1,val2);
    -- maybe do more stuff here
  END IF;
END LOOP;

-- execution continues here when LEAVE my_loop is encountered;
-- you might have more things you want to do here

-- the cursor is implicitly closed when it goes out of scope, or can be explicitly closed if desired

CLOSE cursor1;

END $$

DELIMITER ;


Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?

ABSOLUTELY format it 64k.. tempdb will be faster and less taxed than the other san datafiles. Factors can be many, but the main one being heavy tempdb usage.  SQL Server allocates space in multiples of 64k, as does Win Server, by design.
Writing into a properly pre allocated datafile on its own 64k formatted drive is very significant to performance.  64k chunk would go from 16x4k data file reads to tempdb in 1x64k packet write as opposed to 16k packet writes.   4k allocation on the read san drive really isn't a big concern unless there's lots of fragmentation, it's the writing that will be impacted by 4k San.
Thats the mathematical truth. However in theory, a properly raided San even at 4k may not be as noticable because consider the drive head multiples they're writing with.

Beyond that are architectural modification you can use such as having tempdb use multiple equal data partitions. But that is probably beyond scope.

That is my short answer, hope I didn't confuse the issue, by being short.


I have replaced artful-pgdg with zesty-pgdg in /etc/apt/sources.list.d/pgdg.list and it worked for me

https://wiki.postgresql.org/wiki/Apt/FAQ

I am using a non-LTS release of Ubuntu

Non-LTS releases of Ubuntu are only added to the repository if the packages from the latest LTS release are incompatible with the release in question. Using the latest LTS release instead generally works, unless library dependencies cannot be fulfilled, in which case we will provide updated packages for the non-LTS release.


Does setting a database to single_user prevent delivery of Service Broker messages?

SINGLE_USER means only a single session can connect to the database, whether that session is SQL Server itself, SQL Server Agent, or some user makes no difference.

If you need to ensure no one can access the database, the only option is to take it offline.

If you are comfortable with members of the sysadmin or dbcreator roles, or the db_owner fixed database role, having access, you can put the database into RESTRICTED_USER mode.  See this MSDN page for details about that.


In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?

The best way to do this for a DB2 database running on Linux, UNIX or Windows is use the DB2 Design Advisor.

This tool (included with DB2) allows you to specify a workload (which could include hundreds of queries), and it will analyze the workload and the existing database to recommend indexes and/or other physical design changes to optimize the workload.


Is it possible to backup users from MongoDB admin database?

So after digging around further i found how to do that.

In short i needed to take the system.users collection or rather use a query to get only users from that collection that follow a certain pattern. for me it was user@some.domain, without the query i would have gotten all service users also which i did not want or need.

The tricky part was mongorestore which did not work if i wanted to restore only the system.users collection

The error i got was:

2020-01-28T12:44:49.902+0200    assuming users in the dump directory are from <= 2.4 (auth version 1)
2020-01-28T12:44:49.960+0200    Failed: the users and roles collections in the dump have an incompatible auth version with target server: cannot restore users of auth version 1 to a server of auth version 5
2020-01-28T12:44:49.960+0200    0 document(s) restored successfully. 0 document(s) failed to restore.

I got the error because mongorestore is unable to determine the version of the dump mongo if you dont give it system.version collection also.


Check which condition has met - Sql Server

Turn your function into an in-line table-valued function, and then pass your extra info in other columns. You can easily call it using CROSS APPLY in your FROM clause, and then use the result in WHERE predicates, SELECT lists, wherever you like. Plus being an ITVF, it’ll perform much better.


MySQL Stored Procedure not throwing error if it contains a query that succeeds

Wow, that's a relevant problem, because you can't identify an error in a procedure, an this passes hidden next to some valid result! I think your question should be more popular.

After many tries and tests I found a way to solve it. You'd expect to loop through the procedure result set, until no more results are available (the PHP Manual way). However, I found an alternate control flow to catch the errors after previous valid results:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $sql="CALL procedureWithErrors()";
    $conn=new mysqli($host,$user,$pass,$db);

    /*The usual way (does not work)*/
    $conn->multi_query($sql);
    do{
        $result=$conn->store_result();
        if($result){
            while($row=$result->fetch_assoc()){
                print_r($row);
            }
            $result->free();
        }
    }while($conn->more_results() && $conn->next_result());

    /*This way works*/
    $conn->multi_query($sql);
    while(($result=$conn->store_result())!==FALSE){
        if($result){
            while($row=$result->fetch_assoc()){
                print_r($row);
            }
            $result->free();
        }
        $conn->next_result();
    }

    /*Just closing and error handling, not relevant code from here*/
    $conn->close();

}catch(mysqli_sql_exception | Exception $e){
    $error="Error #".$e->getCode()." ".$e->getMessage().PHP_EOL;
    if(isset($conn) && get_class($e)=="mysqli_sql_exception")
        $error.="SQLSTATE #".$conn->sqlstate." Statement: $sql".PHP_EOL;
    $error.=$e->getTraceAsString();
    echo(nl2br($error));
}finally{
    if(isset($result) && $result instanceof mysql_result) $result->free();
    if(isset($conn) && is_resource($conn) && get_resource_type($conn)==='mysql link') $conn->close();
}
?>

The problems seems to be that mysqli::more_results and mysql::next_result return FALSE if an error happens, so they can't distinguish between an error and the lack of further results.


Running Excel addin for MDS2019 vs MDS2016

It's not entirely clear what part of the stack your question is on. Assuming you're running a second server in parallel with SQL Server 2019 with MDS 2019, then you can certainly connect to either instance in parallel. Specifically here's the download for the Excel Add-In for MDS 2019 which should be backwards compatible to connecting to your MDS 2016 instance. You should be able to even deploy your models from your 2016 instance over to your 2019 instance by following these docs.
If you do need to still give your end users a version of Excel with the MDS 2016 Add-In, then you'll need a separate machine (a VM, or however you prefer) with Excel installed and the MDS 2016 Add-In installed, as I doubt there's stability in installing two instances of Excel, each with the respective version of the Add-In, on the same machine. But maybe it's possible, you'd have to test first.


MySQL Correlated Subquery (with multiple columns) per row

Is this what you are looking for?

SELECT  s.id,
        COUNT( rv.per_ip ) as readers,
        AVG( rv.per_ip ) as avg_visits_pr
    FROM  sites AS s
    JOIN
    (
        SELECT  u.site_id,
                COUNT( * ) AS per_ip
            FROM  site_hits_unique AS u
            WHERE  u.date >= CURDATE( ) - INTERVAL 30 DAY
            GROUP BY  u.site_id, u.ip
            HAVING  per_ip > 1
    )  AS rv ON rv.site_id = s.id
    WHERE  s.id IN (162888, 42705, 11412)

Indexes:

site_hits_unique: INDEX(site_id, date, ip)

Or maybe this:

SELECT  site_id,
        COUNT(DISTINCT ip) as readers,
        COUNT(*) / COUNT(DISTINCT ip) as avg_visits_pr
    FROM
    (
        SELECT  site_id, ip, COUNT(*) AS per_ip
            FROM  site_hits_unique
            WHERE  date >= CURDATE( ) - INTERVAL 30 DAY
              AND  site_id IN (162888, 42705, 11412)
            GROUP BY  site_id, ip
            HAVING  per_ip > 1
    ) AS rv
    GROUP BY  site_id;


How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?

The alter is failing because the row has a value of null for the TS column. If you remove the 'NOT NULL` from your alter statement it will work correctly though you will still end up with the row you first inserted with a value of null for TS.
You will get the same error if you do the alter without trying to set a default. The alter you are trying to use will not replace the null value and, as a result, you are getting the error.
You may change the value of the row before the alter but, as you have it written, the alter will not work as you've seen.


Invoke-ProcessASDatabase fails with "Cannot connect to server"

You have two options here, which I can think of.

You can stick with the current setup of your Automation Account and add to firewall of the Azure Analysis Services instance IPs of the Azure Automation.

There is official links to this list, which you can download from here:

Azure IP Ranges and Service Tags – Public Cloud

Second option is to go with  Hybrid Runbook Worker. You can set up a worker designated to run your PowerShell scripts and then you have to white-list only IP of this particular worker. You can even use a machine from the onpremise infrastructure.

Automate resources in your datacenter or cloud by using Hybrid
Runbook Worker


Is sslmode=verify-full enough to connect securely to postgresql?

If done correctly, it is secure.

The biggest vulnerability would be someone tricking you into removing the sslmode=verify-full parameters from your connect string, or some similar social engineering attack.


Sorting the Table and getting the position

Analytic functions have not been implemented in MySQL yet. There are some ways to overcome this limitation.

Joining the table to itself using not equality but > or >= and then using GROUP BY and COUNT(*) (what @deszo is essentially doing - a self LEFT JOIN). Downside is that it may not be fast enough, especially if you are self-joining a complex query/view.
Using MySQL variables (I should add a huge Warning though: using MySQL variables this way may be broken in future MySQL releases. This is not a guaranteed behaviour):

SELECT LastName
     , FirstName
     , RowNumber
     , Rank
     , DenseRank
     , RowNumber_OverPartitionBy
FROM
  ( SELECT p.*,
           @rown := @rown + 1 AS RowNumber
         , @rnk :=  CASE WHEN LastName = @prev_lastname
                      THEN @rnk
                      ELSE @rown
                    END AS Rank
         , @drnk := CASE WHEN LastName = @prev_lastname
                      THEN @drnk
                      ELSE @drnk + 1
                    END AS DenseRank
         , @rowp := CASE WHEN LastName = @prev_lastname
                      THEN @rowp + 1
                      ELSE 1
                    END AS RowNumber_OverPartitionBy
         , @prev_lastname := LastName
    FROM
        Person p
      CROSS JOIN
        ( SELECT @rown := 0, @rnk := 0
               , @drnk := 0, @rowp := 0
        ) AS dummy
    ORDER BY LastName
           , FirstName
  ) AS p
ORDER BY LastName
       , FirstName ;

You can test in SQL-Fiddle: test-1

In other DBMS, that have window (analytic) functions, you could have the same in a much more compact query:

SELECT LastName
     , FirstName
     , Row_Number() OVER(ORDER BY LastName, FirstName)
         AS RowNumber
     , Rank() OVER(ORDER BY LastName)
         AS Rank
     , Dense_Rank() OVER(ORDER BY LastName)
         AS DenseRank
     , Row_Number() OVER( PARTITION BY LastName
                          ORDER BY FirstName)
         AS RowNumber_OverPartitionBy
FROM
    Person p
ORDER BY
    LastName
  , FirstName ;

Test in SQL-Fiddle (SQL-Server, Postgres, Oracle): test-2


Custom error code class and numbers for Postgres stored functions

The SQL standard gives you a guideline:

  The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed
  by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character
  encoding form and is restricted to <digit>s and <simple Latin upper case letter>s. Table 33, “SQLSTATE class
  and subclass values”, specifies the class value for each condition and the subclass value or values for each class
  value.

  Class values that begin with one of the <digit>s '0', '1', '2', '3', or '4' or one of the <simple Latin upper case letter>s
  'A', 'B', 'C', 'D', 'E', 'F', 'G', or 'H' are returned only for conditions defined in ISO/IEC 9075 or in any other
  International Standard. The range of such class values is called standard-defined classes. Some such class codes
  are reserved for use by specific International Standards, as specified elsewhere in this Clause. Subclass values
  associated with such classes that also begin with one of those 13 characters are returned only for conditions
  defined in ISO/IEC 9075 or some other International Standard. The range of such subclass values is called
  standard-defined subclasses. Subclass values associated with such classes that begin with one of the <digit>s
  '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
  'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined conditions and are called implementation-defined subclasses.

  Class values that begin with one of the <digit>s '5', '6', '7', '8', or '9' or one of the <simple Latin upper case letter>s
  'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', or 'Z' are reserved for implementation-defined
  exception conditions and are called implementation-defined classes. All subclass values except '000', which
  means no subclass, associated with such classes are reserved for implementation-defined conditions and are
  called implementation-defined subclasses. An implementation-defined completion condition shall be indicated
  by returning an implementation-defined subclass in conjunction with one of the classes successful completion, warning, or no data.

If your exception matches one of the SQLSTATEs in Appendix A of the PostgreSQL documentation, simply use that SQLSTATE.

If you need to use your own SQLSTATE, let it begin with any of 5 to 9 or I to Z.

If you need to define a custom warning, use an SQLSTATE that starts with 01 and whose third character is any of 5 to 9 or I to Z.


How do I change the instance name in MS SQL 2005 without installing a new instance?

It's not possible.

  You need to reinstall it. There is no
  instance rename facility for the
  Database Engine.

http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/d5d297b0-2995-46ba-9d3b-d1ec8825f479


SQL Server Merge vs MySQL Insert On Duplicate Key performance comparison

IODKU is the best of the options in MySQL.  It works something like this:

Use some UNIQUE key (possibly the clustered, unique, PRIMARY KEY) to locate the row to modify.
If no such row, perform an INSERT.
If such a row, perform an UPDATE.

You can't get faster than that.

Note that step 1 will cache everything that is needed for #2 or #3 in the buffer_pool.  (Well, OK, secondary indexes are handled in a 'delayed' way via the "Change Buffer".)

Further note that the statement is atomic, whereas your 2-statement alternatives need to be in a transaction.

Keep in mind that REPLACE is DELETE (0, 1, or possibly multiple rows, if you have multiple unique keys), then INSERT.  Note that the AUTO_INCREMENT (if used) value is thrown away and a new one is created.

As for that messy query, it can probably be done with GREATER() and/or IF() and/or VALUES().  Assuming that you are trying to merge multiple rows in, you need the IODKU+SELECT syntax:

INSERT INTO Target (key, a)
    ON DUPLICATE KEY UPDATE
        SET a = VALUES(a)
    SELECT Target.key, Target.a
        FROM Target
        JOIN Source ON Source.key = Target.key
        WHERE Target.x > 'value';

Since I don't know what MERGE does, I can't give you all the details.


Returning a result set with multiple rows based on max date

You simply want:

SELECT
    [Customer ID],
    MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
    [Customer ID];

Ok - you've revised it. You now want to order the rows and pick the top one:

WITH numbered AS (
    SELECT
        [Customer ID],
        [Some Date],
        [Balance],
        ROW_NUMBER() OVER (
            PARTITION BY
                [Customer ID]
            ORDER BY
                [Some Date] DESC
        ) AS rownum
    FROM[Cust Date TABLE]
)
SELECT
    [Customer ID],
    [Some Date],
    [Balance]
FROM numbered
WHERE
    rownum = 1;


The most efficient method to obtain the total entries into a mysql table

If the table is MyISAM then you can

select table_rows from information_schema.tables
where table_schema='my_db' table_name = 'my_table';

or

select count(1) from my_db.my_table;

Either will work since the row count is physically stored in the .MYD file of the MyISAM table.

If the table is using InnoDB, the information_schema is absolutely no good for you because of the MVCC and transaction isolation it supports.

What is even worse about InnoDB is terms of counting is the fact that mysqld will guess at the cardinality of a table (row count) by doing BTREE dives in the indexes.

To learn more about this InnoDB Counting phenomenon, please read my Apr 23, 2012 post : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?


lock escalation issue when using DDL trigger

Is is preferred that you post a full repro. I tried to repro this and below code returned "ALTER TABLE" and not the text you posted.
My guess is that you use some tool to modify the table and this tool starts by changing lock escalation before doing the real stuff. Just a guess, though, since we don't have a repro.
Anyhow, here's a repro using your trigger code and just doing a SELECT of the data you return from EVENTDATA.
And, as @Mo64 mentioned, you should pick up the correct element from EventData. See the second SELECT statement in the trigger code (the first is from your code):
CREATE TRIGGER TableTrigger
ON DATABASE
FOR
    CREATE_TABLE,
    ALTER_TABLE,
    DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;

--Not the correct element:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)')

--This is the one you want:
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
END;
GO

CREATE TABLE t(c1 int)
GO

ALTER TABLE t ALTER COLUMN c1 tinyint

Also, your trigger code is strange. You pick up the data from EVENTDATA several times, without picking up the value that you really want (the value that is to go into existing_table_name and new_table_name). Dig into XPath a bit more to get the values you really want. Below is a snippet from a DDL trigger that captures a couple of element values, perhaps that might get you started:
DECLARE @ev xml
SET @ev = EVENTDATA()
SELECT @ev
DECLARE @PostTime datetime2 = @ev.value('(/EVENT_INSTANCE/PostTime)[1]','datetime2');
DECLARE @LoginName sysname = @ev.value('(/EVENT_INSTANCE/LoginName)[1]','sysname');
DECLARE @TSQLCommand nvarchar(max) = @ev.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');


cannot convert yyyymmdd to yyyy-mm-dd - arithmetic overflow error

The column is actually stored as an int, and it means something different in datetime, so you'll need one extra step in your CONVERT:

CONVERT(DATETIME, CONVERT(CHAR(8), next_run_date), 112);

To show why you can't do this directly to an int:

SELECT CONVERT(DATETIME, 0);
SELECT CONVERT(DATETIME, 1);

When it's an int, it's actually the number of days since 1900-01-01.


How to Restore MongoDB Database After Having Deleted DB Files From Directory

3-rd Party Tool

You might want to consider recovering the files using the PhotoRec tool.

Reference: PhotoRec Homepage

  PhotoRec is file data recovery software designed to recover lost files including video, documents and archives from hard disks, CD-ROMs, and lost pictures (thus the Photo Recovery name) from digital camera memory. PhotoRec ignores the file system and goes after the underlying data, so it will still work even if your media's file system has been severely damaged or reformatted.

Prerequisites

First of all you will have to ensure that no more writes are performed against the disk in your Ubuntu system, otherwise you might be overwriting the deleted files.

Step by Step Guide

Then you can consider following the step by step instructions provided on their website.

  This Recovery example guides you through PhotoRec step by step to recover deleted files or lost data from a reformatted partition or corrupted file system. For lost/deleted partitions or deleted files from a FAT or NTFS file system, try TestDisk first - it's usually faster and TestDisk can retrieved the original file names. Translations of this PhotoRec manual to other languages are welcome.

  ...

  Under Unix/Linux/BSD, you need to be root to run PhotoRec (ie. sudo testdisk-6.13/photorec_static)

Reference: PhotoRec Step By Step

Short Quick Version

Start PhotoRec from a USB medium or other disk than where your database files reside.
Select disk.
Hit Proceed
Select partition.
Hit either
 a. Search
 b. Options
 c. File Opt
 d. Quit (well not really, unless you are unsure)
After hitting Search the tool will try and to determine the file system. If you know your filesystem type, select the appropriate one.
Select either the Free space option or the Whole option depending on your requirements.
Select where your recovered files should be copied to.

  Tip: Copying to another drive (USB, eSata, other disk than where the database resides) will increase the probability of not destroying the file you are trying to recover.

Hope that the files are displayed as recovered in the summary.

The files on your disk might have already been overwritten and unrecoverable. But good luck anyway.

  Disclaimer
  I am in not way affiliated with PhotoRec or any of its employees.
  No guarantee given that the provided solution will result in recoverable files. You are trying this procedure at your own risk.

Future Considerations

Implement a backup solution (script/3-rd party tool/...)
Test your backup solution
Test a restore of database backup which were created with the backup solution

Reference: MongoDB Backup Methods (MongoDB Site)


Standard ways to return SQL Server database to a specific state

To be honest, a full backup will be the easiest and quickest way to do that.  Simply backup your database (a full backup) to the point you want to revert to, and then when you need to get back to that point, do a full restore.

Also, depending on your version and edition of SQL Server (currently not specified in your question), you could work with Database Snapshots, and use a snapshot to revert back to.

The above two solutions are quick and easy if you're wanting to revert the entire database back to a certain point (either at the full backup, or the snapshot).  But if you're only looking to revert a subset of your database back to a certain point in time (think: certain types of development, benchmarking, or demo'ing) then the easiest thing would probably be a reversion script to target your specified affected objects.

With a tiny database that you want to revert the entire thing back, the first two options would be quickest.  But if you are dealing with a relatively large database, where a restore is just not working for you timewise, then you could also consider a script to do the revert.  It all depends on what you're trying to do, how big the database is, and what you're willing to handle based on the clock vs. trouble of reverting.


How to make sure that mysqldump command is working in command prompt

So just as dwjv suggested, set the PATH OS system environmental variable to include the path on the OS where the mysqldump.exe exists (http://www.computerhope.com/issues/ch000549.htm); otherwise, change the directory in your command window to that path where it is and then run the command.

Add the full path (exampleC:\Program Files\MySQL\MySQL Server\bin\mysqldump\) where the mysqldump.exe is located to the environmental system variable of the OS where you're running this.
Example:C:\Users\IT-Admin>CD /D "C:\Program Files\MySQL\MySQL Server\bin\mysqldump" and then press enter to get this in the command window where you'd type 'mysqldump' as shown here C:\Program Files\MySQL\MySQL Server\bin\mysqldump>mysqldump and then press enter.
Furthermore, you could put the full path to the mysqldump.exe in with double quotes around it and then press enter as well e.g. "C:\Program Files\MySQL\MySQL Server\bin\mysqldump" and then press enter.

So there are three potential solutions for you!


How to insert data from a local MSSQL db table to a remote MSSQL db table using WCF application coded in .NET

I can see four ways you could accomplish this:

1. Linked servers

You can establish a linked server on both the local and remote SQL Server, which will allow you to reference tables not only on the same machine, but also on the remote machine, like so:

INSERT INTO some_server.some_database.some_schema.some_table
SELECT * FROM local_database.local_schema.local_table;

You might run into trouble with your database administrators - some are perfectly fine with linked servers, others not. Also, there's a bit of work to making authentication over linked servers work without introducing security risks.

2. Replace the stored procedures with application logic

Create two connections in your application, one to the source database, the other one to the target database. Then, for each row you read on the source connection, you insert/delete/perform some action on the target connection.

The downside with this approach is that you can run into performance issues if you work with large amounts of data, or if you're not careful with your database design and indexing.

3. ETL tools

There are a range of data tools out there, including SSIS and Pentaho, that allow you to create data transfer/synchronization processes in a graphical interface in Visual Studio. SSIS is included with SQL Server.

Obviously, there's a considerable learning curve to mastering these tools.

4. SQL Server replication

For bonus points, see if setting up replication between the two SQL Servers could solve your problem, or ask a database administrator for help with this.


What is "Field List" command in mysql processlist?

Look for any lines in the PROCESSLIST with a Time higher than that one (1188).  It probably indicates the villain.  The owner of both rows may give a clue.

A "metadata lock" involves something other than simple queries -- possibly ALTER TABLE or LOCK TABLES (as for mysqldump), etc.

"Field list" sounds like probing information_schema for the columns in the table, but I could be wrong.

Another possibility is that there is an open transaction that is waiting to be COMMITted.  (And it is 'between' statements when the PROCESSLIST was grabbed.)

SHOW OPEN TABLES; might give another clue.


How to reference only affected rows in AFTER UPDATE trigger

Use the inserted table, which is a special table available inside triggers containing the rows that will be updated/inserted into the table.

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN
    UPDATE [dbo].[app_bread_crumbs]
    SET last_updated_by = SYSTEM_USER,
    last_updated_on = GETDATE()
    FROM dbo.app_bread_crumbs abc
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.id = abc.id);
END


Change AWS's Read Replica's streaming replication to trigger based replication

The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it doesn't complete giving a conflict error.

This behavior is controlled by the parameters max_standby_streaming_delay / max_standby_archive_delay. You can fiddle with these parameters in the RDS Parameter Group used by your Read Replica instance to allow more time for queries against your Read Replica to complete.

  For this very reason and also due to new requirement, we need to make this replication process daily, i.e. instead of continous updation we need a trigger based approach(something like a daily backup approach).

If you'd like a snapshot of your primary database refreshed nightly, you could do this with a cron job restoring RDS snapshots every night. I don't think RDS has a button to do this automatically for you, but it shouldn't be too hard to script up a nightly create-db-snapshot + restore-db-instance-from-db-snapshot using the AWS CLI, or boto, or whatever interface to AWS you like. You could even maintain a Route53 entry which would always point to the most-recent instance, and leave the old instances lingering for a day or so before being killed off, so that sessions running against existing instances overnight wouldn't be interrupted.

  I saw in the AWS's console,there are various parameters we can set. Is there some parameter which I can set to achieve my requirement? Or the only way out for me is to delete the read replica instance and use a trigger based replication tool like Bucardo and use an EC2 instance for this?

Supposedly it is possible to hook up Bucardo to RDS now that RDS Postgres supports the session replication role, but if you want a nightly snapshot I think you'll be much better off using RDS instance snapshots.


comparing column values when there are nothing in common between tables

like this perhaps.

WITH t1 AS ( SELECT col3 AS tbl1col3,row_number()OVER() FROM tbl1 limit 10)
    ,t2 as ( select col6 AS tbl2col6,row_number()OVER() FROM tbl2 limit 10)
    ,t3 as ( select col1 AS tbl3col1,row_number()OVER() FROM tbl3 limit 10)
SELECT tbl1col3, tbl2col6, tbl3col1
FROM t1
    FULL OUTER JOIN t2 USING (row_number)
    FULL OUTER JOIN t3 USING (row_number)
;


Update statement using Case statement, derived table, and ROW_NUMBER


The best way to limit server hog using resource governor

The trick to Resource Governor is thinking about what you want to protect rather than what you want to throttle.  You set minimum/reserve resources for your good users which protects them from the bad users.

My advice is to start conservative and slowly turn the knobs. You'll likely just need one Resource Group for your "Good Users". You would set their minimum CPU and/or Memory to, say 10%.  Everything else, including your Bad Users, will go to the Default pool.

If Bad Users were all running CPU heavy queries, and the Good User's query came along, it would throttle the Bad so the Good would be guaranteed to at least have 10% of the CPU.

You may decide that a 10% minimum isn't enough and you need to increase it. Just be measured in your approach.  I personally have never found the need to touch the Maximums, though I suppose there are use-cases.

Also, the Resource Governor DMVs provide some great information. You can create your "Good Group" and just leave it at the defaults to see the stats. They are quite interesting on their own.

Another thing to be aware of is to make your Classifer function very simple. Remember, this function will get called on every connection so SQL will know which Group to route the connection, so don't put a lot of crazy logic in there.

Typically you'll just do simple CASE compare based on Application Name or Host Name or Login Name.

for example:

CREATE FUNCTION [dbo].[my_classifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN

DECLARE @HostName NVARCHAR(256)

SELECT @HostName = HOST_NAME()

RETURN
(
    SELECT CASE
    WHEN (@HostName = 'GoodUser') THEN N'GoodUser_WorkloadGroup'
    ELSE  'default'
    END
);
END


subquery must return onl one column in postgresql

If temptable has one record, then you probably dont need subqueries at all. It it has more the one, then you need aggregates. Probably the best is not to provide more info, but study some examples like here: https://www.w3resource.com/PostgreSQL/postgresql-xml-functions.php and build your xml from inside moving outward step by step like
1:
SELECT xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
    CASE WHEN q1_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount)
FROM temptable;

2:
SELECT xmlforest(xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
    CASE WHEN q1_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount) as "componentRewards",
xmlforest('q2_parasiticide_carton_rebate' AS rewardName,
    CASE WHEN q2_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount) as "componentRewards")
FROM temptable;


How to get a new pid file of Mysql and restart Mysql Successfully?

The result you are showing:

root      6490  0.0  0.2 112720  2332 pts/0    S+   03:48   0:00 grep --color=auto mysql

is actually the pid and process for the  ps aux | grep mysql command. It is not actually a process for MySQL.

A running process for MySQL would look similar to this:

mysql     1894  0.8  9.8 1865804 1192032 ?     Sl   Jul01 300:53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/DBNAME.err --pid-file=/var/lib/mysql/DBNAME.pid

You can use the command pkill to kill the running MySQL processes by name. However, it's not recommended to kill MySQL in a forceful manner.

 pkill -f mysql

This will match the filename pattern for any part of the command line

To start/stop the MySQL services, you can run:

service mysqld start
service mysqld stop
service mysqld restart

or

service mysql start
service mysql stop
service mysql restart


When changing startup parameters on a cluster, does each node have to be updated manually?

The registry should have been updated automatically.  Take a look at the BOL reference on configuring server startup parameters:

  On a cluster, changes must be made on the active server while SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.

In order to further troubleshoot why that didn't happen, we would need to know more surrounding information (errors, procedures for changing and failover, etc.).  When you say "after attempting a move to the alternate node that the startup params were not synchronized..." what do you mean by "move to the alternate node"?  Did you failover the instance?


How to select top 3 vendors without duplicate entries?

SELECT TOP(3) --WITH TIES
    h.vendor
    ,MIN(d.Rate) as Rate
FROM #Header as h
    inner join #Detail as d
        on  H.HNo = D.DNo
GROUP BY h.vendor
order by RATE

or option 2

SELECT TOP(3)
    a.vendor
    ,a.RATE
FROM
(
    SELECT
        h.vendor
        ,d.RATE
        ,ROW_NUMBER() OVER(PARTITION BY h.Vendor ORDER BY d.Rate ASC) as RN
    FROM #Header as h
        inner join #Detail as d
            on  H.HNo = D.DNo
)a
WHERE a.rn = 1
ORDER BY a.RATE ASC

output:

vendor  RATE
V1  10.00
V4  20.00
V3  30.00


Not able to Connect to a named instance remotely

Try connecting to 311SQLI01,1430.  If that works, turn on the SQL Browser service.

SQL Browser needs to be turned on for port number translation ( it's off in your screenshot ) and you don't seem to be referencing the specific port in your connection attempts.


Windows patching on 3 node Always On Availability Group

To be honest there is no definite answer to your question.

If you do the last fallback to N1 you tested it on all available nodes. Within limits, more testing seems to be better to me. But to be honest it might not be absolutely necessary.

Leaving it on N2 will get you the added benefit of using the N2 node for primary workload until the next update.

It might even be feasible to do the fallback to N1 and then do another failover to N2 to test if N1 is running and then have N2 as primary until the next update.

In conclusion: I don't think it is absolutely necessary to the fall back to N1 but if you can afford the time during your maintenance window I would consider just doing it (and maybe even do the failover to N2 afterwards).