How can I connect Microsoft SQL Server to MySQL

Yes, Linked server and OPENQUERY is the right approach.
However, 5k of sales per day is already a good website. If you are already on MSSQL why don't you use MSSQL for your website? Because you are using WordPress? There are solutions for that:

Project Nami: It has a few years now so it's quite stable and keeps baing developed. You can also deploy it to Azure quite simply. It might be a good alternative if you are already on MSSQL and if you want to stay there.
Ziper: I've used it in the past, try to give it a go.
Skyvia: It says it's free up to 5k per month so it really looks like your scenario. You can use it for free now and pay later if you have more than 5k of records per day.

Or if MSSQL and MySQL is what you want and you are good in usingg Microsoft technologies you can use SSIS to update data from MySQL to MSSQL and vice versa.


I'd suggest either to use an additional table or by adding a new counter field to your current Departments table.

Then build an stored procedure or use a BEFORE INSERT trigger, to quietly add the next ID every time a new record is inserted.

CREATE TABLE [dbo].[DepartmentSeq]
(
    [department]        varchar(10)     NOT NULL,
    [sequence]          int             NOT NULL,

    CONSTRAINT [PK_DepartmentSeq]
        PRIMARY KEY CLUSTERED ([department] ASC)
)
ON [PRIMARY];
GO

--= SP [sp_DeparmentSeq_next]
--------------------------------------------------
CREATE PROCEDURE [dbo].[sp_DeparmentSeq_next]
(
@department varchar(10),
@next_sequence int OUT
)
AS
BEGIN

    DECLARE @sequence int;
    SET @sequence= 0;
    SET @next_sequence = 0;

    BEGIN TRY

        SELECT   @sequence = [sequence]
        FROM     [dbo].[DepartmentSeq] WITH (UPDLOCK)
        WHERE    [department] = @department;

        IF @@ROWCOUNT > 0
        BEGIN
            SET @sequence= @sequence + 1;

            UPDATE [dbo].[DepartmentSeq]
            SET    sequence = @sequence
            WHERE  [department] = @department;
        END
        ELSE
        BEGIN
            SET @sequence = 1;

            INSERT INTO [dbo].[DepartmentSeq] (department, sequence)
            VALUES      (@department, @sequence);
        END

        SET @next_sequence = @sequence;

    END TRY
    BEGIN CATCH

        DECLARE @en INT, @es INT, @ep SYSNAME, @el INT, @em NVARCHAR(2048);
        SET     @en = ERROR_NUMBER();
        SET     @es = ERROR_SEVERITY();
        SET     @ep = ERROR_PROCEDURE()
        SET     @el = ERROR_LINE();
        SET     @em = ERROR_MESSAGE();
        -- EXEC [dbo].[vsp_log_errors_insert] @ep, @en, @em, @es, @el;
        RAISERROR(@em, @es, @el);
        RETURN @en;

    END CATCH

    RETURN 0;

END
GO

As a second option, if there is a limited number of departments and depending on which version of SQL Server you use, you can use a SEQUENCE for each department.


Fastest mechanism to move data between tables in SQL server

The better way wqould be to fix the broken programming requiring a total re-transform of all data. THat is a terrible non-scaling approach to start with and whoever justified that - I hope they now work in another field, as far away from any project responsibility as possible.

If you use enterprise edition you could possibly use a partition and partition swaps - even with only ONE Partition that at least has some schema lock optimizations. The schema of the table never changes. It has negative implications on statistics, though, before SQL Server 2014.

At the end it really runs down to a non-competent approach that was choosen - those "smart shortcuts" always come back to bite you.


From the look of the select query in the view
SUGGESTION #1 : Don't use VIEWS
Views are notorious for acting up with Query Optimization
According to MySQL Documentation

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Percona calls Views a Performance Troublemaker
SUGGESTION #2 : Use another index (Optional)
You definitely need an additional index to assist the query
ALTER TABLE taxes ADD INDEX propertysid_amount_ndx (propertysid,amount);
ALTER TABLE taxes DROP INDEX propertysid;

That way, all the data needed for the view are in the index only. The other two indexes are not enough. Why do I say that? Even though the propertysid index was used, the amount has to retrieved from the table. Essentially, the query passes through both the index and the table.


These look, to me, like statuses in a workflow that you are creating.
As such, it is highly likely that these status values will change over time.  Because of this, I would discount the use of an Enum.  Changing an Enum requires you to change the table definition, which requires you (as DBA) to get involved.  Adding a new status [row] into a regular table makes it very easy to add new statuses, potentially even being a part of your application and Users being able to do this for themselves!  (Obviously, you'll have a constraint that prevents them from deleting statuses that are in use!)

Also, I would recommend this approach over using a simple, varchar column, if only for the sake of consistency.  is it possible that someone (you?) could accidentally(?) enter a status of "WIP" instead of "wip" and what effect would that have on your application code?


This is a bit late, but the previous answers don't provide the third alternative which is both flexible and normalized.

All tasks should be kept in a Task table, because subtasks after all are just tasks.

The relationship between tasks should be stored in a second table that has columns to identify the parent/sub task relationship based on a foreign-key relationship with the Tasks table, and which together would form a composite primary key.

Tasks

id
title
description
start_date
end_date

TaskRelationships

parent_task_id
sub_task_id

In order to find the parent tasks of a given task you can query the sub_task_id column. To find the sub-tasks of a given task search the parent_task_id column.

This structure has the added benefit of having the flexibility to add as many parent tasks to a task as your wish.


Huge database... queries too slow. How to improve performance?

An index provides a path to rapidly identify the rows that match a given predicate. They help most when you need to reference a small fraction of a table at a time. Since you need all rows this is unlikely to be helpful. They also enforce a sequence. This may be helpful if your algorithm would benefit from ordering, though you do not mention it.

The DBMS copies data from disk into memory before processing the rows. So more memory helps because more data can be held in memory, with corresponding fewer IO waits. Since you need to process 280GB the last 4GB will be in memory at the end. During the next cycle that data will be evicted and fresh data read. Unless you are able to install over 280GB more memory is unlikely to help.

Partitioning is more of an archiving thing. Sharding may help, though. That would mean spreading the data across several servers. Four servers would, approximately, quarter the time to process. You'll need a custom process to stitch the answers together, though, which may not be simple.

The simple fact is that if you need to process 300M rows you need to read 300M rows. This is likely limited by storage bandwidth. An SSD will obviously help here.

However, is there a way that you wouldn't have to process all rows each time? Could you pre-calculate these values, cache intermediate results or only process deltas between queries?

Edit Are you reading all rows only to parse some values out of the text column, but you actually do not use the majority of rows in any particular query? LIKE is not going to help eliminate IO since the majority of values will be somewhere in the middle of the text. An index will not help here. The best you can achieve is to push filtering onto the DB server eliminating network time.

A full text search may help find labels within the large text column. Perhaps redefining the column as JSON and adding a secondary index would help. But really these are kludgy workarounds for a sub-optimal design. Relational databases are built to process relations i.e. tables. Structure the data properly as a table and you will have the best chance of getting good performance. In this case that means separating the key-value pairs embeded in the text column onto separate rows, one per value. An index on the "key" column (and whatever other columns are in the WHERE clause) will be a great start. Yes, the DB may become a little larger, but the software's designed for this. There are many, many systems around with terabytes of data in billions of rows. It will be OK.


SQL Server: dm_exec_requests.wait_time vs. dm_exec_sessions.last_request_end_time

I see a lot of this from MS Office apps as well.

What is probably happening is that there are multiple ASYNC_NETWORK_IO waits per request while the clients slowly fetch and acknowledge subsets of the result set. Multiple wait events per request would explain why the current wait time is lower than the time elapsed since the last request ended. As Geoff mentioned, wait_time is only the time spent in the current wait event, not the total time the session has spent waiting.

Someone more familiar with the relevant client libraries and protocols could probably confirm whether this behavior is expected. You could probably get more detail on the internals with an extended event session capturing callstacks combined with a network packet capture, but I'm not sure that I'd want to do that in production.

The following extended event session would at least help you confirm that  there are multiple ASYNC_NETWORK_IO waits occurring per query:

CREATE EVENT SESSION [async_network_io_waits] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.client_app_name,
      sqlserver.client_hostname,
      sqlserver.client_pid,
      sqlserver.database_id,
      sqlserver.database_name,
      sqlserver.server_principal_name,
      sqlserver.session_id,
      sqlserver.sql_text,
      sqlserver.username)
    --ASYNC_NETWORK_IO waits
    --See SELECT * FROM sys.dm_xe_map_values WHERE map_value = '%NETWORK_IO%';
    WHERE (([wait_type]=(99))
      -- Waits of more than 100 ms only
      AND [duration]>(100)
      AND ([sqlserver].[database_id]>(4))
      AND ([sqlserver].[database_id]<>(32767))))
-- Change the path to something reasonable
-- Up to 4 128MB files
ADD TARGET package0.event_file(SET filename=N'C:\temp\async_network_io_waits.xel',
  max_file_size=(128),
  max_rollover_files=(4))
WITH (MAX_MEMORY=16384 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=300 SECONDS)
GO
ALTER EVENT SESSION async_network_io_waits ON SERVER STATE=START;

If you run something like SELECT * FROM BigTable UNION ALL SELECT * FROM BigTable ... in SSMS you'll see the EE session showing multiple ASYNC_NETWORK_IO events firing for the same query. You might need to change the duration predicate in the EE session to something much lower to capture that in a test scenario with SSMS (again, not something I'd want to do in prod).

I don't have SQL Server 2012 around right now, so I can't test this on the same version you're using.

Extended event sessions can have performance overhead, so I'd probably filter that session more restrictively if I used it in production.

What I would probably monitor for in your situation (using a modified version of your query, not the EE session) is a large difference between the current time and last_request_end_time where the current wait type is ASYNC_NETWORK_IO. I'd probably want to gather data about what is being matched for a long while before automatically killing anything.

I also like the suggestion about exploring dumping the output of sp_WhoIsActive to a table and have had good success with that approach to troubleshooting issues generally.


Output another word instead of the word in the table in a query

Either use the SQL standard way of CASE WHEN ... as ypercybe suggested in the comments or for more neat result, DECODE function:

DECODE(fieldref, 'enabled', 'unblocked', 'disabled', 'blocked')


What is a mysqld process in a MySQL cluster?

When it comes to a DB Server running mysqld in a MySQL Cluster environment, the DB Server's role is known as a SQL Node.
I once described the difference between a Storage Node and a SQL Node : Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes?. In the comment section of the question, Mat Keep commented that you should look at MySQL Cluster -- Storing table data on data nodes.
In a small Cluster, I do not see any significant impact of running mysqld on a Storage Node. In a larger Cluster, I would expect SQL Nodes to be setup apart from the Data Node unless you intend to store data in physical files.
You can find additional info from the three MySQL Cluster gurus in the DBA StackExchange

Frazer Clement
Mat Keep
Andrew Morgan

Based on your comment

To be more precise, I have 3 machines which run a Data Node. Each machine has 8Gb of RAM. In total the the db should store ~50 million ~1kb records. How would you do it here?

DISCLAIMER : Not SCMCDBA Certified
Personally, I would get a fourth server and run it as the SQL Node and leave the others as Storage (Data) Nodes. Make sure the SQL node and the Data nodes have the same hardware and OS settings.
If it is a low-write DB Cluster, then you could chance it and run mysqld --ndbcluster on each Data Node (no need for a fourth server).


How do I combine multiple rows when using select that are essentially the same data?

It's quite simple using GROUP_CONCAT():

select group_concat(txt order by roooow SEPARATOR '')
from story
group by id
order by roooow;

By the way, row and text are bad names for columns, as they are reserved keywords.


Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

The decisive factor is whether there is a unique constraint/index on b (key_column).

If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column).

If on the opposite, there is a unique constraint, the query is equivalent to your rewriting (SELECT some_column FROM a; and the question is whether Oracle has implemented this trick in its optimizer's suite of query transformations.

Indeed it has been implemented - in version 10gR2 and further enhanced in  11gR1 - and it's called Table Elimination. More info can be found in the oracle blog post: Why are some of the tables in my query missing from the plan? The article explains that the 11gR1 enhancement included this particular case (Outer Join Table Elimination) of an outer join to a table with a unique constraint and has an example almost identical to yours.

We can also check with a simple comparison. Notice the difference on the execution plan between the 2 cases in the SQLfiddle:

-- Case 1
create table b
( key_column int                      -- no unique constraint
, other_column int
) ;
insert into b values (1, 1) ;
insert into b values (2, 2) ;
insert into b values (3, 3) ;
insert into b values (4, 4) ;
insert into b values (1, 2) ;      -- some duplicate values
insert into b values (1, 3) ;      -- in key_column

-- Case 2
create table bb
( key_column int primary key       -- has a unique constraint
, other_column int
) ;

insert into bb values (1, 1) ;
insert into bb values (2, 2) ;
insert into bb values (3, 3) ;
insert into bb values (4, 4) ;

-- Both cases
create table a
( some_column int primary key,
  key_column int
) ;

insert into a values (101, 1) ;
insert into a values (202, 2) ;

We create views for both cases:

CREATE VIEW dummy AS
SELECT a.some_column, b.other_column
FROM a LEFT JOIN b USING (key_column);

CREATE VIEW dummy_bb AS
SELECT a.some_column, bb.other_column
FROM a LEFT JOIN bb USING (key_column);

Now the run the queries, using the view and using the tables - identical except for joining to table b vs. bb where the tables differ only that the second has a unique constraint on key_column:

SELECT some_column FROM dummy ;

SELECT some_column FROM (
    SELECT a.some_column, b.other_column
    FROM a LEFT JOIN b USING (key_column)
);

We see that on case 1 does access table b while in case 2 it does not (using the unexposed transformation).


Do I have to format all my drives into xfs format to better use mongodb?

To answer the question directly, I would say not to change your root partition (which, I'm assuming, contains all of your OS files - i.e. your kernel) file system to XFS. By all means, use XFS on your new disk system for your Mongodb data.

I'm not sure of your exact disk setup, but if you have multiple disks, I would recommend OS on one (set), swap/tmp on another, database software on another, and actual data on yet others - that way you don't have OS calls blocking db stuff and vice versa. This also helps with restoring in the (unlikely) event of a disk (set) failure.

From here, you are correct in saying that

  When running MongoDB in production on Linux, you should use Linux
  kernel version 2.6.36 or later, with either the XFS or EXT4
  filesystem. If possible, use XFS as it generally performs better with
  MongoDB.

  With the WiredTiger storage engine, use of XFS is strongly recommended
  to avoid performance issues that may occur when using EXT4 with
  WiredTiger.

From here (generally a good site), you can see that xfs is good for large files:

  XFS was developed by Silicon Graphics in 1994 for the SGI IRX
  operating system, and was ported to Linux in 2001. It’s similar to
  Ext4 in some ways, as it also uses delayed allocation to help with
  file fragmentation and does not allow for mounted snapshots. It can be
  enlarged, but not shrunk, on the fly. XFS has good performance when
  dealing with large files, but has worse performance than other file
  systems when dealing with many small files. It may be useful for
  certain types of servers that primarily need to deal with large files.

Now, I'm not sure about Mongodb, but as a database system, it probably has large files :-) - however, the kernel itself is primarily developed on ext4 and it is the default for a reason.

Remember that a recommendation is just that: a recommendation for an average set of databases with an average set of requirements! You should, if possible, benchmark with your own data on your own machines (prod and test!).

Another thing I've noticed about database file system benchmarks is that there's normally not a huge difference between them for a given task - obviously some are better than others in a given set of circumstances, but it seems to me that if one team makes a "breakthrough", others aren't far behind!

So, I would keep my kernel on ext4 and use xfs for my mongodb storage (again, with the caveat about your own benchmarking, to the extent that this is possible).

To answer @NeilLunn 's point: well, yes (what I wrote is essentially an opinion), but I like to think it is a reasonably educated one! Basically, there are lies, damned lies, and statistics! And then, there are database benchmarks :-)

Anything anyone writes on this topic will contain an element of opinion - the thing which separates the wheat from the chaff is those which take information from reputable sites, and those which urge the user to benchmark for their own apps on their own machines with their own realistic data sets and usage patterns.

I believe that I have done that - I'm not going to gainsay Mongodb about whether their systems work work better with file system x, y or z, but I also know that gainsaying Linus Torvalds on which is the best file system for the kernel is also fraught with peril!


Clustered Index Maintenance vs. Nonclustered Index Maintenance

Embarrassingly, I never knew of columnstore indexes. This table is primarily used for analytics; thus I used a clustered columnstore index with a primary key defined on the char(2) and int fields as per the suggestion from David Browne - Microsoft in the comments.

Since the question was about clustered index maintenance compared to nonclustered index maintenance, I'll "answer" that part to the best of my ability. From what I've read, it takes more resources when a clustered index has a page split than a page split on a smaller nonclustered index (with the same key). This is because the page contains far more data in the clustered index. Consequently, had I not used a columnstore clustered index, I would have used (and tested) an IDENTITY column for the clustered index. Having said that, the video
sp_BlitzErik provided in the comments is very informative—and easily digestible for plebs like me—and highlights how there is a lot of outdated recommendations "out there" based on older implementations of SQL Server; thus concerns about external fragmentation can be exaggerated.


What causes pt-online-schema-change to slow down the further it gets through a table?

A guess...  The Change Buffer is full and overflowing.

The operation is building 11 secondary indexes.  For InnoDB, the building of secondary indexes is "delayed" by storing to-do info in a part of the buffer_pool called the Change Buffer.

Eventually the "changes" need to be flushed to disk.  Your table is probably much bigger than the buffer_pool, so it is probably overflowing.  Here's (roughly) how the 11 indexes for a given row are being created for the new table:

Write 11 records to the Change Buffer.  Such a record is (I guess) the secondary key column(s) plus the primary key column(s).
Check for overflow.  If running out of room (which you are by now), read some index block from disk, fold the index entries into it, queue the block for writing back to disk, and free up the index entries from the Change Buffer.

Initially all 11 indexes were empty, so the above process was dumping blocks full of stuff out to disk -- very efficient.

As the processing continues, the indexes get bigger, and the "dumping of more entries" becomes less efficient because there are more blocks to deal with and few records to put in each one.  That is, each read-modify-write step is freeing up fewer and fewer entries from the Change Buffer.

This feeds back to slowing down the processing, which pt-online-schema-change smartly turns into smaller chunk sizes.

Alternatives

It is sometimes better to rebuild indexes after building the table.  However, you probably don't want to because it would involve downtime.
In 5.6 (and more in 5.7), many ALTERs can be performed with very little impact on the running system.  5.5 is now the oldest supported version; you should consider upgrading.  (Pt-online-schema-change is still be useful for cases where online alters do not apply.)
Vertical partitioning -- Instead of adding columns to this table, you could have considered building a 'parallel' table with the new columns.  This can usually be done with no downtime.  Although some SELECTs will need a JOIN, this is not necessarily a performance burden.


Starting SSIS Package through SSIS Catalog

I would agree with the comment from Travis Page. Having just gone through this is sounds like either the Service Principal Names(SPN) are missing for the SQL Service or the file share. Ultimately you will need the "MSSQL" SPN for your SQL Server instances and the "HOST" SPN for host of the fileshare.

Some reading/blog articles to help understand troubleshoot any kerberos issues:

This blog describes the creation of SPN's using the SETSPN command line tool:
http://blogs.technet.com/b/tristank/archive/2006/05/08/spns-r-fn.aspx

There is a tool published from Microsoft which will help determine the SPN's require for SQL Server. This blog talks about the tool:
http://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

Additionally there is this below article from Microsoft which will help troubleshoot any kerberos problems:
http://blogs.technet.com/b/askds/archive/2008/05/14/troubleshooting-kerberos-authentication-problems-name-resolution-issues.aspx


MySQL error 1593 on replication between 5.0.92 and 5.5.42

You didn't setup the server-id variable, or you didn't restart after doing it (it is not a dynamic variable, changes will not be effective until you restart). It may be showing you a good id on both servers, but it will not really work until both servers are restarted, both master and slave.

It could be a bug, but the above has happened too many times on support cases that I would bet it is that.

Regarding circular replication: 5.0 -> 5.5 "should work", 5.5 -> 5.0 is possible, but not recommended. I understand why you want that, but you may encounter many incompatibilities in the queries and format.


Is better to create a table for each room or make a single table that handles all the rooms in my case?

Here's one approach:

create table users
( username ... not null primary key
, <additional attributes>
) engine = innodb;

create table rooms
( room_name ... not null primary key
, creator ... not null
, <additional attributes>
, constraint ... foreign key (creator) references users (username)
) engine = innodb;

create table conversations
( room_name ... not null
, username ... not null
, <additional attributes>
, constraint ... primary key (room_name, username)
, constraint ... foreign key (room_name) references rooms (room_name)
, constraint ... foreign key (username) references users (username)
) engine = innodb;

Assuming users are already created, a user can create a room. A conversation is a room with one or more users. To find out who is having conversation in a room:

select username from conversations where room_name = ?

Whether this is a good approach or not depends on your requirements. Personally I like to write down facts about my business and extract a logical model from that.

I would definitely stay away from changing the model as a consequence of a user action (example create a table for a new room).


Find and Replace text in all table using mysql query

MySQL's replace function should come handy to replace the text you need:

update $table set column = replace(column, 'old_text', new_text') where column='old_text' and <other_conditions>;

Now, as you talk of replace in all tables, that's something not possible in single query. Though you can create a routine to do so!
For the sample you can refer a procedure which is finding a string in all tables of all databases.

(Though been already mentioned in comments) table_schema is a column name of information_schema.tables table which represents the database name.


SSMS 2012 mutiple query windows using same session

It is not possible to share a local temporary table between connections.

Local temporary tables are scoped to a single session, but global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Global temporary tables have two number signs (##) as the first characters of their names, for example:

CREATE TABLE ##GlobalTemp
(
    c1 integer NULL
);


Difference between Replication and Erasure coding techniques

Erasure coding, commonly used in RAID parity stripes, consists of high-availability through computation of parity and striping the resulting data across multiple storage subsystems (or disks).  Replication, more commonly known as mirroring, simply writes multiple copies of the data to more than one storage subsystem or disk.
Erasure coding incurs added latency for all writes because the incoming data must have its parity computed and written to disk.  Reads under a failure mode are going to be slower since the missing data must be reconstructed on-the-fly by comparing the non-missing data against the parity blocks.
Writes under replication are typically not affected as much as under erasure coding since the replicated data can be passed through to the underlying storage without requiring a parity calculation, and is typically written to two or more disks simultaneously in parallel.  Reads of replicated data under a failure mode are typically the same speed as reads under a non-failure mode since the data is read from the non-failed disk in it's entirety without requiring any calculation be performed to "make up" missing data.
Erasure coding typically requires less storage space than replication because the only redundant data is the parity code, whereas in replication all the data is duplicated.
Arguably the most commonly storage redundancy scheme used in SQL Server storage systems is RAID-10, which uses a combination of RAID-1 (replication or mirroring in the more common vernacular), and RAID-0 striping without parity.  A typical RAID-10 array might consist of 8 disks configured into 4 mirroring pairs, where data is striped across the 4 pairs of disks.  This enables both fast reads, and fast writes, while maintaining redundancy.  More than a single disk can fail without data loss as long as any two disks in a single mirroring pair don't fail at the same time.

Logical Disk:            C:\
                          |
Striping:    ============================
             |        |        |        |
Mirroring: =====    =====    =====    =====
           D   D    D   D    D   D    D   D
           i   i    i   i    i   i    i   i
           s   s    s   s    s   s    s   s
           k   k    k   k    k   k    k   k
           1   2    3   4    5   6    7   8

In Raid 10, up to 4 disk failures could occur in the above setup as long as no two failures occur in the same mirror pair.  With RAID-50, using erasure coding at the striping level, up to 5 disks can fail as long as only a single mirror pair has two disks fail simultaneously.  For instance, Disk1, Disk2, Disk3, Disk5, and Disk8 could fail simultaneously without data-loss.
To answer your question, any I/O that incurs extra operations over-and-above writing the data to a single disk in a non-redundant manner is going to add apparent latency to the system.  Whether one system is slower or faster than another depends greatly upon the actual implementation, with the optimizations made by each manufacturer varying greatly with target audience.
It's likely safe to say that parity (erasure coding) is slower, requires more I/O, and has a lower cost-per-GB than replication.  Replication is likely faster to rebuild, has lower write-latency, and costs more per GB.  Again, these assumptions are broad generalizations and depend on implementation details that vary by vendor and target system.


Getting data according to last hours from sqlite

Finally I found the solution to my own question

Following is the code which worked for me

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        String startDate = dateFormat.format(date);
        String endDate = "";
        for (int i = 1; i <= 24; i++) {
            System.out.println("Start Date:- " + startDate);
            calendar.add(Calendar.HOUR_OF_DAY, -1);
            date = calendar.getTime();
            endDate = dateFormat.format(date);
            System.out.println("End Date:- " + endDate);
            String data = dbAdapter.getOutDoorHourlyData(startDate, endDate);
            System.out.println("Hourly Average:- " + data);
            startDate = endDate;
            endDate = "";
        }

public String getOutDoorHourlyData(String startDate, String endDate) {
        double outdoorHourly = 0;

        Cursor cursor = _sqliteDB.rawQuery("Select AVG("
                + COLOUMN_NAME + ") from (Select * FROM "
                + TABLE_NAME + " where " + COLOUMN_NAME + " >= '"
                + endDate + "' and " + COLOUMN_NAME + " < '" + startDate
                + "')", null);

        try {

            if (cursor != null) {
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    do {
                        outdoorHourly = cursor.getDouble(0);
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        String hourlyData = decimalFormat.format(outdoorHourly);
        hourlyData = hourlyData.replace(",", ".");
        return hourlyData;

    }

 }

I hope it will help someone in future


Improve querying of Extended Events target file

Another option is Microsoft.SqlServer.XEvent.Linq instead of T-SQL to process XE data in Powershell or .NET application. The QueryableXEventData class can process XE data directly from target files or from the live event stream.

The live steam source is generally appropriate for events that occur relatively infrequently (as in your monitoring use case) whereas the file source most useful for high-frequency event use cases (e.g. capturing rpc and batch completed events for tracing).

Assuming your goal is inserting events into a table for forensics, below is a PowerShell example and target table DDL. One can use SqlBulkCopy instead of trickle inserts for high-frequency events.

Table:

CREATE TABLE dbo.missing_column_statistics (
      Timestamp datetimeoffset
    , Name varchar(100)
    , collect_cpu_cycle_time decimal(20, 0)
    , collect_system_time datetimeoffset
    , last_error int
    , task_time decimal(20, 0)
    , client_app_name varchar(100)
    , client_hostname varchar(100)
    , database_name sysname
    , nt_username varchar(100)
    , server_instance_name varchar(100)
    , session_id int
    , client_pid int
    , sql_text nvarchar(MAX)
);
CREATE CLUSTERED INDEX cdx ON dbo.missing_column_statistics(Timestamp);

PowerShell script:

# class with actions/fields of interest
class MissingColumnStatisticsEvent {

    [DateTimeOffset]$Timestamp
    [String]$Name
    [UInt64]$collect_cpu_cycle_time
    [DateTimeOffset]$collect_system_time
    [UInt32]$last_error
    [UInt64]$task_time
    [String]$client_app_name
    [String]$client_hostname
    [String]$database_name
    [String]$nt_username
    [String]$server_instance_name
    [UInt16]$session_id
    [UInt16]$client_pid
    [String]$sql_text

    MissingColumnStatisticsEvent($event) {
        $this.Name = $event.Name
        $this.Timestamp = $event.Timestamp
        $this.collect_cpu_cycle_time = $event.Actions["collect_cpu_cycle_time"].Value
        $this.collect_system_time = $event.Actions["collect_system_time"].Value
        $this.last_error = $event.Actions["last_error"].Value
        $this.task_time = $event.Actions["task_time"].Value
        $this.client_app_name = $event.Actions["client_app_name"].Value
        $this.client_hostname = $event.Actions["client_hostname"].Value
        $this.database_name = $event.Actions["database_name"].Value
        $this.nt_username = $event.Actions["nt_username"].Value
        $this.server_instance_name = $event.Actions["server_instance_name"].Value
        $this.session_id = $event.Actions["session_id"].Value
        $this.client_pid = $event.Actions["client_pid"].Value
        $this.sql_text = $event.Actions["sql_text"].Value
    }

}

# insert event data into target table
Function Insert-MissingColumnStatisticsEvent($event) {
    $insertQuery = @"
INSERT INTO dbo.missing_column_statistics (
      Timestamp
    , Name
    , collect_cpu_cycle_time
    , collect_system_time
    , last_error
    , task_time
    , client_app_name
    , client_hostname
    , database_name
    , nt_username
    , server_instance_name
    , session_id
    , client_pid
    , sql_text
)
VALUES (
      @Timestamp
    , @Name
    , @collect_cpu_cycle_time
    , @collect_system_time
    , @last_error
    , @task_time
    , @client_app_name
    , @client_hostname
    , @database_name
    , @nt_username
    , @server_instance_name
    , @session_id
    , @client_pid
    , @sql_text
);
"@

    $connection = New-Object System.Data.SqlClient.SqlConnection($targetDatabaseConnectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($insertQuery, $connection)
    ($command.Parameters.Add("@Timestamp", [System.Data.SqlDbType]::DateTimeOffset)).Value = $event.Timestamp
    ($command.Parameters.Add("@Name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.Name
    ($command.Parameters.Add("@collect_cpu_cycle_time", [System.Data.SqlDbType]::Decimal, 20, 0)).Value = $event.collect_cpu_cycle_time
    ($command.Parameters.Add("@collect_system_time", [System.Data.SqlDbType]::DateTimeOffset)).Value = $event.collect_system_time
    ($command.Parameters.Add("@last_error", [System.Data.SqlDbType]::Int)).Value = $event.last_error
    ($command.Parameters.Add("@task_time", [System.Data.SqlDbType]::Decimal, 20, 0)).Value = $event.task_time
    ($command.Parameters.Add("@client_app_name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.client_app_name
    ($command.Parameters.Add("@client_hostname", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.client_hostname
    ($command.Parameters.Add("@database_name", [System.Data.SqlDbType]::NVarChar, 128)).Value = $event.database_name
    ($command.Parameters.Add("@nt_username", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.nt_username
    ($command.Parameters.Add("@server_instance_name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.server_instance_name
    ($command.Parameters.Add("@session_id", [System.Data.SqlDbType]::Int)).Value = $event.session_id
    ($command.Parameters.Add("@client_pid", [System.Data.SqlDbType]::Int)).Value = $event.client_pid
    ($command.Parameters.Add("@sql_text", [System.Data.SqlDbType]::NVarChar, -1)).Value = $event.sql_text
    $connection.Open()
    [void]$command.ExecuteNonQuery()
    $connection.Close()
}

# ############
# ### MAIN ###
# ############
try {

    # load assemblies needed for QueryableXEventData
    $sharedPath = "C:\Program Files\Microsoft SQL Server\150\Shared" # path varies by SQL tool version
    $xeCore = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XE.Core.dll")
    $xeLinq = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XEvent.Linq.dll")
    Add-Type -Path $xeLinq
    Add-Type -Path $xeCore

    $xeSessionConnectionString = "Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Application Name=XE ETL" # run in master database context for live stream
    $targetDatabaseConnectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=XE ETL"
    $sessionName = "EE_QueryWarnings"

    # get events from file target
    # $events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("DC:\Temp\$sessionName*.xel")

    # get events from live session
    $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData(
       $xeSessionConnectionString,
       $sessionName,
       [Microsoft.SqlServer.XEvent.Linq.EventStreamSourceOptions]::EventStream,
       [Microsoft.SqlServer.XEvent.Linq.EventStreamCacheOptions]::DoNotCache)

    # Stream source will consume continuously until this script or XE session is stopped.
    # File source will read all XE files and exit.
    foreach($event in $events) {
        if($event.Name -eq "missing_column_statistics") {
            $missingColumnStatisticsEvent = New-Object MissingColumnStatisticsEvent($event)
            Insert-MissingColumnStatisticsEvent -event $missingColumnStatisticsEvent
        }
    }
}
catch {

    throw

}


Save dataset within another table best practice

If I understand well, you need the bill to still have the person's data when it was created. And if the person changed it's data, like address, you need the bill to keep having the original address where that person was billed.

In other words, you will have different 'versions' of the person.
What I would is the following:

Divide person table into two tables with master-details (or one to many) relationship. In the master table, have the fields that do not change, like ID, Name. In the details table, have a primary key field and the ID from master and other fields that may change, like address.

Person(ID primary key, name , ...)
Person_det(ID PK, Person_id referencing person.id, address, ...)

'Bill' table now would have "person_det.id" as a foreign key, instead of person.id.


How to set foreign key on two tables in MySQL?

I'll add a partial answer since it is too much information to stuff in comments. I'm not saying that it is the best solution, but unless you want to remodel, it is a possability. Given the facts you presented you can extend category_entities as:
CREATE TABLE category_entities
( cat_id int not null primary key
, prod_id int not null
, merchant_id int not null
, ...
    constraint ... foreign key (prod_id, merchant_id)
                   references products (prod_id, merchant_id)
, ...
);

Now, you may have inconsistent merchant_id information between categories and category_entities. You can prevent this by adding a unique constraint in categories:
ALTER TABLE categories ADD CONSTRAINT ak1_categories
    UNIQUE (cat_id, merchant_id);

Now you can reference this constraint in category_entities as:
ALTER TABLE category_entities ADD CONSTRAINT fk1_categories
    FOREIGN KEY (cat_id, merchant_id)
    REFERENCES categories (cat_id, merchant_id);

This will guarantee consistency between category and category_entities, but is a bit ugly since ak1_categories is a reducible key.
I've heard rumours that there exists DBMS that allow CHECK constraints with sub-queries, but I never used one. Beside MySQL 5.7 doesn't care about check constraints so that is not an option for you.
Another alternative is to add before triggers for validation of merchant_id. They are however procedural by nature so they do not tell you anything about the current situation. All you know is that no invalid data passed them as long as they were active.
As mentioned this is not a complete answer but presents some ideas that do not fit in a comment.


Need help designing an Access Control List (ACL) database structure

I favor computing and materializing granular object access entitlements so they are simple and cheap for the database to enforce at runtime.  And to simplify the relational model.

Order(OrderId, ...)
Order_AccessControl(OrderId, GroupID, AccessType)

Every time you create a group or an order you would generate the AccessControl entries using (potentially complex) business logic and configuration data, but storing, auditing and enforcing the security is simple.


Query to check if all replication articles are created at Subscriber SQL Server 2012

I am not sure if there is a better way but something simple like this should do it. If you run this query on the Publisher, it will compare the tables and will return you the difference in tables. The Publisher needs to be linked to the Subscriber.

-- Publisher
select t.name
from sys.tables t
where is_published = 1
except
-- Subscriber
select t.name
from [SERVERNAME].[REPLICATED_DATABASE].sys.tables t
where is_ms_shipped = 0;


Oracle returns an error ORA-01031: insufficient privileges

Something must have gone wrong at the O/S level - check this page for some steps to track down the problem


How do you structure a database that organizes post by location like craigslist?

Create a table called states.

Create a table called cities that as a many to one relation ship for a table called states.

Then in your post table you have a many to one to the cities tables.

Then you can use syntax like post.city.state.name to get the name of the state for the post or just post.city.name for the city.


Let's trace through the query plan from right to left. SQL Server estimates that only a single row will be returned from the t1 derived table. For each row returned it will do a clustered index scan of the VoterTelephones table in node ids 20 and 26. This means that if the cardinality estimate from the t1 derived table is wrong then SQL Server could end up doing many more clustered index scans than it expected when costing this plan. For example, if a thousand rows are returned from the t1 derived table then SQL Server will may do 2000 clustered index scans on the VoterTelephones table. If SQL Server had a more accurate cardinality estimate then it probably would have picked a different query plan.

There's another less technical way to get to a similar conclusion. Let's start at the left of the query plan this time. SQL Server thinks that only a single row will be inserted into the #tmpKeep1 table. Is that true? If you expect it to not be true then the estimated cost for the query plan could be quite inaccurate. The plan has an estimated cost of 50 magic optimizer units yet the query sometimes takes hours to finish. That's another reason to think that the cost is inaccurate compared to what actually happens when executing the query. If SQL Server has made bad assumptions or estimates about your data then it may have picked a sub-optimal query plan to return your data.

To improve query performance from I would start by trying to correct the cardinality estimate returned from the t1 derived table:

--get dupe cell phones
select TelAreaCode, TelNumber
from VoterTelephones
where TelCellFlag = 1 group by TelAreaCode, TelNumber having count(*) > 1

The most straightforward way to do this would be to put the results from that query into a temp table. SQL Server will gather stats on the temp table so you will get a more accurate cardinality estimate which is likely to change the query plan.

Depending on your version of SQL Server you may be able to improve the estimate by creating a multi-column statistics object on the VoterTelephones table, or even just by updating the stats with FULLSCAN. A filtered statistics that includes the where TelCellFlag = 1 predicate could help as well. The idea here is that if you give SQL Server more information about your data then it may be able to give you a better cardinality estimate for that derived table.

It may be true that it's too difficult to fix the cardinality estimate from that part of the query. In that case you could try creating indexes on the VoterTelephones table that eliminates the need for SQL Server to do a clustered index scan for each row in the outer table. If you create covering indexes on the table then SQL Server will do an index seek per row in the outer result set instead of a clustered index scan per row. That should be far more efficient.

For the joins to the table aliased as vt you filter on the TelCellFlag column, join on the TelAreaCode and TelNumber columns, and also need the LALVoterID and TelMatchScore columns for other parts of the query. You can get that information by carefully parsing the query text or by looking at the nested loop join operator with a node ID of 7 and the clustered index scan with a node ID of 20. For example, here is the predicate for the scan:

[S_MS].[dbo].[VoterTelephones].[TelCellFlag] as [vt].[TelCellFlag]=(1)

Here is the join predicate for the nested loop operator:

[S_MS].[dbo].[VoterTelephones].[TelAreaCode] as [vt].[TelAreaCode]
=[S_MS].[dbo].[VoterTelephones].[TelAreaCode]
AND [S_MS].[dbo].[VoterTelephones].[TelNumber] as [vt].[TelNumber]
=[S_MS].[dbo].[VoterTelephones].[TelNumber]

Here is the output column list for the clustered index scan:

[S_MS].[dbo].[VoterTelephones].LALVoterID,
[S_MS].[dbo].[VoterTelephones].TelNumber,
[S_MS].[dbo].[VoterTelephones].TelMatchScore,
[S_MS].[dbo].[VoterTelephones].TelAreaCode

You can combine that data to figure out your needs for the index. You want predicate and join columns to be in the index columns and the other necessary columns to be included columns. Something like this could work:

CREATE INDEX NAME_YOUR_INDEX ON VoterTelephones (TelCellFlag, TelAreaCode, TelNumber)
INCLUDE (LALVoterID, TelMatchScore);

You can perform a similar analysis for the other index scan in the plan (node ID 26).

Depending on your application, it's also possible that you're running into plan caching or statistics caching issues with your temp tables. I think that this is unlikely to be the cause but I'm including it in case other ideas don't work out. You can test for this by including a RECOMPILE hint in your query. Depending on how often this query runs that may not be a good option.


Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?

Do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite, store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree?

No, not all. Lets take them one by one:

MySQL. MySQL has several "engines" and depending on what engine a table is defined to use, the storage is:

InnoDB: Yes, what you describe, the table data is stored in a clustered index, with the index based on the primary key columns on the table (and if there isn't a PK defined, on the first UNIQUE index with non-null columns and in the lack of that, on a secret 6-byte internal column).
MyISAM: No, the table is a heap, not a clustered index.
other engines: (NBD, Blackhole, CSV, etc) No, I think none of them uses a clustered index (except maybe NBD, not sure)
TokuDB: Yes. Similar to InnoDB but you may define more than one clustered indexes!

SQL Server: Yes, the default behaviour for tables that have a PK is to be clustered on the PK. This can be overridden though by declaring that the PK is NONCLUSTERED. You can also define another index (not the PK) to be the clustered index of the table. If the PK is deined as NONCLUSTERED and none of the indexes is defined as CLUSTERED, then the table is a heap. In recent versions, a 3rd option was added (besides clustered index and heap):  columnstore which is a different way of organizing the table data.
PostgreSQL: No. All the tables are heaps, period. You can create additional indexes of various types (btree, hash, gin, gist, brin, etc) but the table data are stored in a heap.
Oracle: No. By default the tables are heaps unless created as Index Organized Tables (Oracle's term for clustered indexes).

Additional clarifications about table data organization:

Clustered Index: There is a btee+ index - based on some column(s) - and the leafs contain the table's data.
Heap: There is no btree index for the heap (other indexes like the PK may still use a btree). The data are stored as unordered lists of records. When a new row is inserted, it is usually added in a disk page with available space. Rows are referenced by some special Page/RowID (the details are implementation dependend and surely differ from DBMS to DBMS), so other indexes will include this reference.
Columnstore: Types of structure where data are stored as columns rather than rows.

Some more details can be found in the documentation of each DBMS and in:
- Wikipedia: Database Storage Structures
- Wikipedia: Columnstore databases


How do I retrieve columns inserted in subquery 1, and return them with columns inserted in subquery 2?

I figured it out:

You can only return fields affected by the insert (thus the error in subquery 2 return statement)

I needed to move the subquery into a CTE and then select the data I want to return

const insertUserSql = `
  with l as (
  insert into locations (
    data,
    placeid,
    user_count
  ) values (
    $1,
    $2,
    1
  )
  on conflict
  on constraint placeid
  do update set
    user_count = locations.user_count + 1
  returning
    placeid, data, uuid
), cu as (
  insert into users as u (
    password,
    email,
    first_name,
    last_name,
    location_uuid
  ) VALUES (
    crypt(
      $3,
      gen_salt('bf')
    ),
    $4,
    $5,
    $6,
    (select uuid from l)
  )
  RETURNING
    u.uuid,
    u.email,
    u.first_name,
    u.last_name,
    u.created,
    u.active,
    u.last_login,
    u.location_uuid
)
select
  l.data,
  l.placeid,
  cu.email,
  cu.uuid,
  cu.first_name,
  cu.last_name,
  cu.created,
  cu.active,
  cu.last_login,
  cu.location_uuid
  from l, cu
`;

However if there is a more efficient way to do this, let me know!


Incorrect syntax near the keyword 'WHERE' - SQL Server 2014

You can confirm whether a given value contains a capital letter or special character with a function.  Using a function will allow you to fully customize exactly which characters you want to reject/accept.

Then just use the CheckPassword function in your stored proc, and it will return a BIT/INT value that indicates whether the password is valid or not.

Or if you want to keep it in the procedure, just use REGEX, something like:

WHERE @PASSWORD COLLATE sql_latin1_general_cp1_cs_as NOT LIKE '%[ABCDEFGHIJKLMNOPQRZTUVWXYZ]%'
AND @PASSWORD NOT LIKE '%[!@#$%^&*();[[]],./]%'

The COLLATE is necessary to make the value Case Sensitive, which is important in your case.

You should add the above either as an initial check, or as a new function which you call within the procedure.  This check/function will essentially not allow the rest of the procedure to run if it finds that the password does not meet the criteria.


MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

I found an answer here that has helped my situation and now the query returns very quickly. It appears the HAVING clause is a better solution than using the Filter function as long as the dataset is reasonably small. However, I noticed that removing NON EMPTY from the second axis (ON ROWS) causes it to still be slow even using the HAVING clause. I may still need to resolve that problem, too. But, at least the first problem is solved.


Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Are there any other snags I should be aware of that might result in an
  insert, update, or deletion of a record not incrementing this value?

ora_rowscn is always incremented when a row changes - but in a default configuration it can also be incremented when a row does not change

If you need to check the whole table for udates, one method is to use auditing. On the other hand if you only need to check the row you are trying to update for conflicts, ora_rowscn with rowdependencies is ideal.


How is combined index implemented in SQL Server

To store rows in a b-tree and perform a seek, all that is needed is an ordering in which the rows should be sorted. Just like you can sort on (Category), you can also sort on the tuple (Category, OfferState). In the latter case, rows are first sorted by Category and then any ties are broken by sorting by OfferState.

The resulting index will use the same b-tree structure, but the value for each entry in the b-tree will be a (Category, OfferState) tuple.

  And how it can speedup query like...

For your query, SQL Server can perform a seek in the following way:

Seek to the first row that matches Category = 1. This can be done using the same b-tree seek you are familiar with, with SQL Server only needing to use the Category portion of each (Category, OfferState) tuple.
Begin reading rows, and continue until a row with OfferState >= 3 is found

In this way, SQL Server will be able to seek directly to the beginning of the range of rows that are needed, read those rows, and stop at the end of the range of rows. Note that you can see how this seek works by looking at the Seek Predicate property of the Index Seek operator in your query plan.

More generally, a multi-column index across columns (a, b, c, d, ...) can support a seek on any leading subset of columns, such as (a) or (a, b, c), when you are matching for equality (using =).

If you are looking for a range (e.g., b < 3), SQL Server can no longer seek on any columns that fall later in the index. In such a case, it would have to perform a separate seek within each distinct value of b, which is not supported (except in a more specific case you probably don't need to worry about: across partitions of a partitioned table).


Two MySQL Server accessing same database over NFS

I just want to check if it is technically possible or not,
  recommendation is other story. specially my application assured
  write/read will not conflict

As @RolandoMySQLDBA said, this is not possible with InnoDB or MyISAM.

Stated more simply: some of the pages of data you modify are not written to disk immediately. This is done in the background, and not all at once. So if your second read-only instance tries to read data through NFS from the same data directory, it is certain to read changes that have been partially written to disk. Basically, garbage.

MyISAM doesn't have a buffer pool, but it does make use of the filesystem as a write-back cache, so you never know whether all your changes have reached the disk or not. In this case, you'll also get an unpredictable mish-mash of data if you try to read the files through NFS from another host.

But why do you want to do this in the first place? Is it to gain greater capacity from running a large number of queries per second? You can run more than one instance of MySQL with data that is kept more-or-less in sync, by using replication. That's a more common practice, and it works well.

Is your reason to have a failover capability? In that case, you might use DRBD for block-level replication. And there are quite a few other failover solutions besides that.


Calculate rolling sum over 7 consecutive day period on PostgreSQL

By far the cleanest solution is to use window function sum with rows between:

with days as (
        SELECT date_trunc('day', d)::date as day
        FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d ),
    counts as (
        select
            days.day,
            sum((random()*5)::integer) num
        FROM days
        -- left join other tables here to get counts, I'm using random
        group by days.day
    )
select
    day,
    num,
    sum(num) over (order by day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
from counts
order by day;

The important part is to generate the timeframe in days CTE and join onto it in order not to miss any days for which there is no data.

Example

For example, if I create some test data with 20 records in last 14 days:

SELECT (current_date - ((random()*14)::integer::text || 'days')::interval)::date as day, (random()*7)::integer as num
into test_data from generate_series(1, 20);;

And also add a value before that:

insert into test_data values ((current_date - '25 days'::interval), 5);

Then use the query above:

with days as (
        SELECT date_trunc('day', d)::date as day
        FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d ),
    counts as (
        select
            days.day,
            sum(t.num) num
        FROM days
        left join test_data t on t.day = days.day
        group by days.day
    )
select
    day,
    num,
    sum(num) over (order by day rows between 6 preceding and current row)
from counts
order by day;

And get the results for whole month:

    day     | num | sum
------------+-----+-----
 2016-01-31 |     |
 2016-02-01 |     |
 2016-02-02 |     |
 2016-02-03 |     |
 2016-02-04 |     |
 2016-02-05 |     |
 2016-02-06 |   5 |   5
 2016-02-07 |     |   5
 2016-02-08 |     |   5
 2016-02-09 |     |   5
 2016-02-10 |     |   5
 2016-02-11 |     |   5
 2016-02-12 |     |   5
 2016-02-13 |     |
 2016-02-14 |     |
 2016-02-15 |     |
 2016-02-16 |     |
 2016-02-17 |     |
 2016-02-18 |   2 |   2
 2016-02-19 |   5 |   7
 2016-02-20 |     |   7
 2016-02-21 |   4 |  11
 2016-02-22 |  15 |  26
 2016-02-23 |   1 |  27
 2016-02-24 |   1 |  28
 2016-02-25 |   2 |  28
 2016-02-26 |   4 |  27
 2016-02-27 |   9 |  36
 2016-02-28 |   5 |  37
 2016-02-29 |  11 |  33
 2016-03-01 |   5 |  37
(31 rows)


Both your 2014 & 2019 instances are running on the same hardware--just different ports.

While you don't specify which ports they are using, the 2014 instance is the default instance, so it's probably running on the default port (1433), and the named instance is running on some other port (who knows... You'd have to check SQL Server Configuration Manager). When you connect to the named instance, the SQL Browser Service helps with name/port resolution, or you can specify the port in the connection string.

It sounds like your AG Listener for your 2019 instance is potentially also using a non-default port (You'd have to check the Listener configuration in SSMS... If both listeners are on port 1433, keep reading). Unfortunately, the SQL Browser Service can't do it's magic on AG Listener names.

If you look at the related documentation, it explains that using a non default Listener port means you need to include the port in your connection string:

  You can also designate a non-standard listener port; however this means that you will also need to explicitly specify a target port in your connection string whenever connecting to the availability group listener.

SHOULD you be using a non default port? In your case, yes. You have to. In fact, if you are running both listeners on the same port, that would be the problem! The documentation continues:

  If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

In your case, you are running multiple services (SQL 2014 & SQL 2019) on the same cluster node. This creates a conflict where both sqlservr.exe processes are both trying to listen on 1433, thus causing a port conflict. In this case, you'll need to change one of the listeners to use a different port, and also specify that port when connecting (Ex to connect to D-AGSQL04-PP on port 56789, specify server name in the format D-AGSQL04-PP,56789)


When would one want to use Oracle transparent data encryption

Transparent data encryption guarantees the data is always encrypted at rest, implying even the backups are encrypted, no matter where they are physically stored, be it tape, disk, a DVD, etc.

Encrypting data at rest is a great way to control who sees the data, even if someone misplaced an entire copy of the database or a steals a database backup.


"Syntax error or access violation" error with nested square brackets in column alias

Ah ... found the answer.  I was running this in an old version of Query Analyser (and got the error above), but it runs fine in SSMS, so error is (falsely) generated by a parsing error in Query Analyser.


Clickhouse create database structure for json data

ClickHouse supports types of columns as Array as Nested.

It looks like for your case Array will be enough:

CREATE TABLE json_import (
  TimeStamp DateTime DEFAULT now(),

  /* other columns */

  FirewallMatchesActions Array(String),
  FirewallMatchesRuleIDs Array(String),
  FirewallMatchesSources Array(String)
) ENGINE = MergeTree()
ORDER BY (TimeStamp);

/* insert test data */

INSERT INTO json_import (FirewallMatchesActions, FirewallMatchesRuleIDs, FirewallMatchesSources)
VALUES (['allow'], ['1234abc', '1235abb'], ['firewallRules']),
       (['allow', 'block'], ['1234abc'], ['firewallRules']),
       ([], [], []);

/* select data */
SELECT *
FROM json_import

/* result
┌───────────TimeStamp─┬─FirewallMatchesActions─┬─FirewallMatchesRuleIDs─┬─FirewallMatchesSources─┐
│ 2020-06-12 06:06:17 │ ['allow']              │ ['1234abc','1235abb']  │ ['firewallRules']      │
│ 2020-06-12 06:06:17 │ ['allow','block']      │ ['1234abc']            │ ['firewallRules']      │
│ 2020-06-12 06:06:17 │ []                     │ []                     │ []                     │
└─────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┘
*/

To reduce storage consumption consider using LowCardinality-type and data encoding.

Additional info:

Nested Data Structures in ClickHouse

Reducing Clickhouse Storage Cost with the LowCardinality Type – Lessons from an Instana Engineer

New Encodings to Improve ClickHouse Efficiency


How do I upload an .mdf file to Azure Data Studio for Mac?

Azure Data Studio is like SQL Server Management Studio: these are client programs that access SQL Server, but ADS and SSMS don't touch the MDF files directly.

What you need is SQL Server to open the MDF. Try downloading SQL Server Developer Edition - it's free. Once it's installed, follow the instructions on how to attach a database. Attaching a database is kinda like opening a .doc file in Word - you need Word installed first.


Efficient way to get data for each date range intervals using single query | MongoDB

If you have only 4 intervals then this could be an approach:
OrdersModel.aggregate([
   {
      $facet: {
         interval1: [
            { $match: { orderDate: { $gte: new Date(sdt_1), $lte: new Date(edt_1) } } },
            {
               $group:
                  {
                     _id: "$product_name", //{ name: "$name"},
                     totalQuantity: { $sum: "$quantity" },
                  }
            },
            { $project: { _id: 1, qty: "$totalQuantity", } }
         ],
         interval2: [
            { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
            {
               $group:
                  {
                     _id: "$product_name", //{ name: "$name"},
                     totalQuantity: { $sum: "$quantity" },
                  }
            },
            { $project: { _id: 1, qty: "$totalQuantity", } }
         ],
      }
   }
])

A more dynamic way would be this one:
var facets = {};

facets["interval1"] = [
   { $match: { orderDate: { $gte: new Date(sdt_1), $lte: new Date(edt_1) } } },
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
];

facets["interval2"] = [
   { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
];

facets["interval3"] = [];
facets["interval3"].push({ $match: { orderDate: { $gte: new Date(sdt_3), $lte: new Date(edt_3) } } });
facets["interval3"].push(
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
);

OrdersModel.aggregate([
   {
      $facet: facets
   }
])


what is the impact of changing log shipping schedule timing jobs?

As Shanky noted, this is not a problem as long as you are not actively conflicting with your own SLAs around backup/restores.

The documentation notes you can customize the jobs as needed (steps #8, 16, 21):

  Note the backup schedule listed in the Schedule box under Backup job. If you want to customize the schedule for your installation, then click Schedule and adjust the SQL Server Agent schedule as needed.


Trouble with SQL Server authentication in SQL Server 2012

New accounts with system administrator access can be created by other equivalent accounts but there is no way to reverse engineer the password for an existing account. You should also verify that the SQL Server you're connecting to allows for mixed authentication (Windows and SQL Server).

This StackOverflow question describes how to access authentication via SQL Server Management Studio (https://stackoverflow.com/questions/1393654/how-to-change-from-sql-server-windows-mode-to-mixed-modesql-server-2008)


How do you normalize a table that is indirectly dependent on a has-many relationship?

You can create a table ProductPrice:

CREATE TABLE ProductPrice (
  ProductID int NOT NULL,
  ProductSetID int NOT NULL,
  StoreID int NOT NULL,
  Price numeric(10,2) NOT NULL,
  PRIMARY KEY (ProductID, ProductSetID, StoreID),
  FOREIGN KEY (ProductID, ProductSetID) REFERENCES ProductProductSet,
  FOREIGN KEY (StoreID, ProductSetID) REFERENCES Store
)

ProductProductSet beging the many-to-many relationship between Product and ProductSet.

These two foreign keys would ensure that the price refers to a store that is intended to have such product. Then you can create a query to find out which prices are missing:

SELECT s.*, p.*
FROM Product p
JOIN ProductProductSet pps ON (pps.ProductID = p.ProductID)
JOIN Store s ON (s.ProductSetID = pps.ProductSetID)
LEFT JOIN ProductPrice pp ON (pp.ProductID = p.ProductID AND pp.StoreID = s.StoreID)
WHERE pp.ProductID IS NULL


SQL Server Performance hit after cache rebuild

So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ?

NO, SQL Server would not do it immediately. Rebuilding indexes does lot of page movements and so does stats update. There is quite a chance that pages already present in memory, before index operation, would have been thrown back to disk. Now when you run the query again SQL Server optimizer will prepare new plan looking at new statistics and would request I/O to bring pages into memory which were thrown back to disk.

  Will there be any slight performance degradation since it has to build the cache from start again before executing the query ?

This can cause a bit of more time being spent by optimizer in preparing new better plan and I/O bringing pages into memory but this would just be the first time. But there would not be much performance degradation. In many cases the new plan would execute query much faster and this would outweigh the time taken by optimizer to build new plan and I/O to get pages into memory.

Since you are interested about plan cache I suggest you read Plan Cache and Reuse. As per the document among other following two can also cause plan to recompile

Updates on statistics used by the execution plan, generated either
explicitly from a statement, such as UPDATE STATISTICS, or generated
automatically.
Dropping an index used by the execution plan.

BUT note that plans do not always get invalidated when you update stats. There are quite few articles describing this

What caused plan to go horribly wrong
Can update stats cause recompile when no data has changed


Why does my SQL Server show more than half a million active tasks?

This is a bug in SQL Server 2012. Details of a hotfix are at

FIX: A memory leak occurs when you enable AlwaysOn Availability Groups or SQL Server failover cluster in Microsoft SQL Server 2012


Cannot TNSPING from VirtualBox Client to Oracle Database On Host

On the database server, issue the command 'lsnrctl status'.  You should get a list of database services known to the listener.  There should be one for your database.  It is the listed 'service name' that should be the subject of the 'SERVICE_NAME parameter in your tnsnames.ora.  There is a more complete explanation of your error at http://edstevensdba.com/oracle-tns/ora-12514-tnslistener-does-not-currently-know-of-service/


Migrating Postgres server from Windows to Linux

No, you cannot do that. The files in the data directory are not compatible across operating systems (or different architectures).

You will need to use pg_dump and pg_restore


Problems loading pgAudit library on Postgres 12.2

This commit renamed IsSystemNamespace to IsCatalogNamespace in PostgreSQL v12, so you must have built the extension with PostgreSQL v11 or older by mistake.
Check the result of
which pg_config

to see what was first on your PATH when you built the extension.
Be explicit with
make USE_PGXS=1 PG_CONFIG=/path/to/pg12/bin/pg_config


There are a lot of facets to this, so it really depends.

  Does adding the XML typed columns to the same table change the speed of querying that table?

More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should not be especially involved in any performance differences

xml columns can be stored in row or out of row, with obvious implications for number of rows which can be stored in a page, fragmentation etc...  http://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx  Storing out of row should mean that more rows fit per page, so less data is read and discarded when you don't actually have the xml data stored in-row.

  What are the performance benefits when I add the xml typed
  columns to another table and join both tables when doing queries?

Well, this is basically equivalent to storing out of row.  You'll only get an administrative  benefit doing it yourself in terms of being able to manage the other table independently.

  Is it better to encode the XML data to shrink it and decode it in the application?

Perhaps.  If we're assuming that you don't need to query it in the database (xml data type with xml query techniques), then why stored it as xml at all instead of varbinary or varchar?


Best way to populate a new column in a large table?

It very much depends on details of your setup and requirements.

Note that since Postgres 11, only adding a column with a volatile DEFAULT still triggers a table rewrite. Unfortunately, this is your case.

If you have sufficient free space on disk - at least 110 % of pg_size_pretty((pg_total_relation_size(tbl)) - and can afford a share lock for some time and an exclusive lock for a very short time, then create a new table including the uuid column using CREATE TABLE AS. Why?

What causes large INSERT to slow down and disk usage to explode?

The below code uses a function from the additional uuid-oss module.

Lock the table against concurrent changes in SHARE mode (still allowing concurrent reads). Attempts to write to the table will wait and eventually fail. See below.
Copy the whole table while populating the new column on the fly - possibly ordering rows favorably while being at it.
If you are going to reorder rows, be sure to set work_mem high enough to do the sort in RAM or as high as you can afford (just for your session, not globally).
Then add constraints, foreign keys, indices, triggers etc. to the new table. When updating large portions of a table it is much faster to create indices from scratch than to add rows iteratively. Related advice in the manual.
When the new table is ready, drop the old and rename the new to make it a drop-in replacement. Only this last step acquires an exclusive lock on the old table for the rest of the transaction - which should be very short now.
It also requires that you delete any object depending on the table type (views, functions using the table type in the signature, ...) and recreate them afterwards.
Do it all in one transaction to avoid incomplete states.

BEGIN;
LOCK TABLE tbl IN SHARE MODE;

SET LOCAL work_mem = '???? MB';  -- just for this transaction

CREATE TABLE tbl_new AS
SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order>
FROM   tbl
ORDER  BY ??;  -- optionally order rows favorably while being at it.

ALTER TABLE tbl_new
   ALTER COLUMN tbl_uuid SET NOT NULL
 , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1()
 , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid);

-- more constraints, indices, triggers?

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME tbl;

-- recreate views etc. if any
COMMIT;

This should be fastest. Any other method of updating in place has to rewrite the whole table as well, just in a more expensive fashion. You would only go that route if you don't have enough free space on disk or cannot afford to lock the whole table or generate errors for concurrent write attempts.

What happens to concurrent writes?

Other transaction (in other sessions) trying to INSERT / UPDATE / DELETE in the same table after your transaction has taken the SHARE lock, will wait until the lock is released or a timeout kicks in, whichever comes first. They will fail either way, since the table they were trying to write to has been deleted from under them.

The new table has a new table OID, but concurrent transaction have already resolved the table name to the OID of the previous table. When the lock is finally released, they try to lock the table themselves before writing to it and find that it's gone. Postgres will answer:

  ERROR:  could not open relation with OID 123456

Where 123456 is the OID of the old table. You need to catch that exception and retry queries in your app code to avoid it.

If you cannot afford that to happen, you have to keep your original table.

Keeping the existing table, alternative 1

Update in place (possibly running the update on small segments at a time) before you add the NOT NULL constraint. Adding a new column with NULL values and without NOT NULL constraint is cheap.
Since Postgres 9.2 you can also create a CHECK constraint with NOT VALID:

  The constraint will still be enforced against subsequent inserts or updates

That allows you to update rows peu à peu - in multiple separate transactions. This avoids keeping row locks for too long and it also allows dead rows to be reused. (You'll have to run VACUUM manually if there is not enough time in between for autovacuum to kick in.) Finally, add the NOT NULL constraint and remove the NOT VALID CHECK constraint:

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID;

-- update rows in multiple batches in separate transactions
-- possibly run VACUUM between transactions

ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL;
ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

Related answer discussing NOT VALID in more detail:

Disable all constraints and table checks while restoring a dump

Keeping the existing table, alternative 2

Prepare the new state in a temporary table, TRUNCATE the original and refill from the temp table. All in one transaction. You still need to take a SHARE lock before preparing the new table to prevent losing concurrent writes.

Details in these related answer on SO:

Best way to delete millions of rows by ID
Add new column without table lock?


I was able to figure out from the trace file that there was line in the sqlnet.ora file that it didn't like.  I narrowed it down to the WALLET_LOCATION line and finally recognized that there was a missing parenthesis at the end of the line.


SQL server 2005 SP4 differential backup is failing

A differential backup is a "changes only" backup, and therefore can only be done when there is a previous full backup to work from. If you create a new database (or attach one from another server), and don't take a full backup, there no "original backup" to get changes since.


From my answer on stack overflow: https://stackoverflow.com/a/29772759/2534472
Ok, let's have a look at your 2NF attempt:

Student
studentID | studentName | courseCode | modCode | resultCode
Course
courseCode | courseTitle
Module
modCode | modTitle | credits

Let's talk about Student first.
Your key cannot be studentID, because the resultCode depends on studentID and courseCode (you have one result for every course). But your studentName depends only on studentID, so a part of the key (studentID,courseCode). So 2NF is violated. You need to do something like this:
Student
studentID | studentName | courseCode | modCode
Course
courseCode | courseTitle
Module
modCode | modTitle | credits
Result
studentID | courseCode | resultCode
But please keep in mind, that this respects the 2NF, but still seems not correct, because modules and courses are now completely unrelated. So try this:
Student
studentID | studentName | modCode
Module
modCode | modTitle | credits
Course
courseCode | courseTitle | modCode
Result
studentID | courseCode | resultCode
A course belongs to a module (a module has many courses) - from my point of view. This is automatically in 2NF (because the key is always just one attribute (except for the result, but there is just one non-key-attribute always depending on both key attributes), so nothing can depend on a "part" of the key). And it is in 3NF, too, because every "physical" entity has it's logical representation in the data model (more a rule of thumb than a formalism).

Now let's have a look at your 3NF attempt. I think you've got the module<->course dependency the wrong way round, but let's just concentrate on the normalization.

Student
studentID | studentName | courseCode
Course
courseCode | courseTitle
Module
modCode | modTitle | credits | courseCode
Results
studentID | modCode | resultCode

This is correct 3NF, because there is simply no other key candidate than the key itself. So there can't be any transitive dependency.
To clarify this: a key candidate is one of the many minimal sets of possible keys. In your relation you have found at least one key candidate with one element (except for the results relation). So any other key candidate cannot have more than one element. That means, that you can simply look at every single attribute and decide "can this be a key or not?" And in your example you find, that no other attribute can be a key - so it's automatically in 2NF and 3NF.


SQL Server Average Lock Wait time Historical data

@zapcon, there is DMV master.sys.dm_exec_requests;

USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

The master.sys.dm_exec_requests; DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

as well as Stored procedure such as sp_who2, & sp_who; through you can identify the blocked spid.

USE Master;
GO
EXEC sp_who2;
GO

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active' parameter.

For your further Ref How to identify blocking in SQL Server


PostgreSQL does not generate a database backup using a query as SQL Server does (ex: backup database [db_name] to disk...)! It is a different RDBMS; you can't simply do something that other one does, just because you want!

There are 2 methods to take backups from Postgres as commented before. Using pg_dump, you can produce a full sql file including all DDL and DML statements to recreate your database in another place (or restore):

pg_dump -Fp -v -h YOUR_HOST -U YOUR_LOGIN -d DATABASE_NAME -f your_file_name.sql

PostgreSQL pg_dump documentation

Or you can take physical backups by copying the pg_data folder.

It's up to you decide!


How to select the first 10 records for each table in the same database using sql

I'd be careful about using sp_MSforeachtable, as it uses the same code pattern as sp_MSforeachdb. I've pointed out the problems with sp_MSforeachdb here (work backward) and was told it wouldn't be fixed on Connect (before they killed it, and so this link no longer works) here.

Instead I would do something like this (which also includes the table name in the output, probably useful):

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'

SELECT TOP (10) [table] = N''' + REPLACE(name, '''','') + ''', *
    FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

When you're satisfied with the PRINT output (note that it will truncate at 8K), uncomment the EXEC.


large SQL Performing quite slow (roughly 2 seconds for 290 rows) when having to deal with entries that can be NULL

Use DISTINCT everywhere you can and I don't understand why not to join instead of correlating

                 SELECT psc
                 FROM   pso_history AS c
                 WHERE  ( c.project = 121
                           OR c.project IS NULL )
                 AND EXISTS (SELECT * FROM   pso_history AS x
                                    WHERE  x.project = 121
                                    AND x.pso_id = c.pso_id )

                 SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS x
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121
                    AND   x.pso_id = c.pso_id

And the entire query will be

SELECT  xyx.version_stamp, xyx.pscdesc_id, xyx.psc, xyx.pscdesctext
FROM    pscdesc_history AS xyx
JOIN    pscdesc_history AS xyy
ON  (xyx.psc IN (SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121
                    AND   x.pso_id = c.pso_id)
     OR xyx.psc IS NULL)
AND xyy.pscdesc_id = xyx.pscdesc_id
AND  xyy.psc IN ( SELECT DISTINCT c.psc
                 FROM pso_history AS c
                 JOIN pso_history AS X
                 ON      ( c.project = 121 OR c.project IS NULL )
                    AND   x.project = 121
                    AND   x.pso_id = c.pso_id)

This must work twice farster I think, check the indexes also.


If the database is truly static in nature, putting it into read-only mode would prevent log growth.  The queries that are causing the growth would fail however, so this might not actually be what you are looking for.

Put the database into simple recovery mode.  That way the transaction logs will be routinely truncated.  When in full mode, your logs will continue to grow until they are backed up.

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH ROLLBACK IMMEDIATE
GO


PL/pgSQL regclass quoting of table named like keyword

Explanation

I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution.

0.

The reason why overlay is quoted is not because it's a function name, but because it's a reserved word.
Also, overlay() is not a "PL/pgSQL function" (nor PL/pgSQL keyword), it's a standard SQL function built into the Postgres core (LANGUAGE internal, so C behind the curtains). In fact, PL/pgSQL has nothing to do with any of this.

1.

A simple logic error.

format('%I_new', old_name)

This would escape any non-standard identifier with surrounding double quotes before '_new' is appended and would fail even with text as input type. You have to append '_new' before quoting the whole of it:

format('%I', old_name || '_new')

But that still won't work for regclass. Keep reading.

2.

It's nonsense to add quotes to a regclass variable with %I, since its text representation is already quoted automatically where needed. (Similar to quote_ident(), but cannot be NULL; since the input is regclass it cannot be NULL to begin with.) You would apply it twice. Always use %s for regclass input (the string as is).

3.

format() is overkill for this. Just use quote_ident():

quote_ident(old_name || '_new')

4.

Most importantly, as mentioned above, the text representation of a regclass variable is automatically escaped. That's built into the cast. To get to the raw text, retrieve it from system catalog pg_class directly. A regclass value is just the oid of this table internally.

SELECT relname FROM pg_class WHERE oid = $1;

Solution

This does what you are looking for:

CREATE OR REPLACE FUNCTION make_name(old_name regclass)
  RETURNS text AS
$func$
SELECT quote_ident(relname || '_new') FROM pg_class WHERE oid = $1;
$func$  LANGUAGE sql STABLE;

Test

CREATE TEMP TABLE "sUICiDAL' namE"();
CREATE TEMP TABLE overlay();

SELECT make_name('overlay') AS n1
     , make_name('"sUICiDAL'' namE"')  AS n2;

     n1      |          n2
-------------+----------------------
 overlay_new | "sUICiDAL' namE_new"

Note that overlay_new is a perfectly legal identifier so it's not quoted.

If you want the name unescaped (no double quotes) just use:

SELECT relname || '_new' FROM pg_class WHERE oid = $1;


openrowset to connect to SQL server with a password containing single quotes

This is definitely one of those occasions when you should print out @string and see what it is actually set to...

SELECT a.*
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd="Fj'C(D=JJ&'XE6vx}";','select count(*) from dbo.table1 with (nolock)')a

You can see in the printed out results above (taken from Solution 1, although 2 is identical) that you are not escaping the single quotes in your connection string and so are ending the string before the 'C' and starting it again before the '&'.

You need an additional double set of quotation marks before the 'C' and after the '&'.

declare @string varchar(max);
declare @pwd varchar(max);
set @pwd = 'Fj'''+'''C(D=JJ&'''''+'XE6vx}';
set @string=
'SELECT a.*
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd='+@pwd+';'',''select count(*) from dbo.table1 with (nolock)'')a'

This gives the following string, which should now have the required escaping of the single quotes and should hopefully work.

SELECT a.*
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd=Fj''C(D=JJ&''XE6vx};','select count(*) from dbo.table1 with (nolock)')a


Adding AD Security group to a SQL linked server

With groups it should be work in the same way as single user.
You must have in place proper spn to grant kerberos delegation between the two  instances.
Maybe you have tried from ssms so you haven't done the two hops for authentication. In that way NTLM is granting you permission but when you are using an application you will need to grant authentication on two hops and so you fall on the anonymous authentication error that is not granting your user proper login.
Please check it.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15


how to select random and unique records from mysql (in php language)

Preliminary: I assume this is your table:

CREATE TABLE questions
(
    question_number INTEGER NOT NULL PRIMARY KEY,
    question VARCHAR(255) NOT NULL,
    answer_a VARCHAR(255) NOT NULL,
    answer_b VARCHAR(255) NOT NULL,
    answer_c VARCHAR(255) NOT NULL,
    answer_d VARCHAR(255) NOT NULL,
    right_answer ENUM ('a', 'b', 'c', 'd') NOT NULL
) ;

You can let MySQL do the hard work for you, and just execute the following SELECT statement, that will sort randomly all your table (i.e.: it is actually shuffling the rows), and just choose the top 5 rows (NOTE: I've used 5 instead of 15 for the sake of simplicity).

The key point: instead of generating a random number from PHP, let the database deal with the random numbers...

SELECT
     question_number, question, answer_a, answer_b, answer_c, answer_d, right_answer
FROM
     questions
ORDER BY
     rand()
LIMIT
     5 ;

(Converting this to PHP code is quite straightforward)

You will get something like:

question_number | question                                             | answer_a     | answer_b     | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :----------- | :----------- | :----------------- | :---------------------- | :-----------
              2 | Which is the colour of the White HOuse?              | Black        | Green        | Blue               | White                   | d
              3 | What's the tallest building on Earth as of 2017?     | Empire State | Burj Khalifa | Eiffel Tower       | Tokyo Skytree           | b
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes          | No           | Depends on gravity | Depends on kind of bird | b
              6 | Which is the third letter of the alphabet?           | a            | b            | c                  | d                       | c
              7 | Which is the first vowel?                            | a            | e            | i                  | o                       | a

Repeating the same query will give you a different set of questions (although some might have already be asked):

question_number | question                                             | answer_a | answer_b | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :------- | :------- | :----------------- | :---------------------- | :-----------
              9 | How many primary colours are there (for a human)?    | 3        | 2        | 6                  | 4                       | a
              5 | How many wheels does a (most common) car have?       | 4        | 6        | 8                  | 10                      | b
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes      | No       | Depends on gravity | Depends on kind of bird | b
              2 | Which is the colour of the White HOuse?              | Black    | Green    | Blue               | White                   | d
              6 | Which is the third letter of the alphabet?           | a        | b        | c                  | d                       | c

When you work with a database, it is normally a good idea to let the database give you the complete answer to your problem, instead of looping in your code.

You can see all the setup and some more executions at dbfiddle here

References:

RAND()
LIMIT


How to create index on huge oracle table?

You can try to build the index individually for each partition:

BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
    END LOOP;
END;
/

CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME||' PARALLEL 10';
    END LOOP;
END;
/

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON;


Database design - Members & companies represented by members

Why would you split into User and Account?

What is Group standing for? It's relating to Account and Company now, that I'm thinking to GroupType or separated tables AccountGroup and CompanyGroup

Above all, based on your current requirements, following my suggestions:

https://dbdiagram.io/d/5d6f2704ced98361d6de2d0d

a user can be part of multiple accounts and have different roles (admin or not)

  Tables: User (Id), Account (Id) and UserAccount (UserId, AccountId)

an account can have one or many groups

  Tables: Group (Id) and AccountGroup (AccountId, GroupId)

each group can have one or many members / a member can be part of many groups

  Tables: Member (Id) and MemberGroup (MemberId, GroupId)

a member can be either an user or a legal entity (company)

  Tables: Company (Id, MemberId), User (add MemberId)

if it's a company, then the company is represented by a user / a user can represent 0,1 or many companies in different groups

  Tables: Company (add RepresentUserId), Group (add RepresentUserId)

Please noted that this is for an idea, not a fullfill solution. I hope this help you much.


October CMS is perfect for this (disclaimer: I'm one of its maintainers). It has a robust roles / permissions system, is highly extendable, based on Laravel, and extremely easy to get started quickly with. Check out the builder plugin to kickstart your development process (it's a visual scaffolding tool to build your own plugins). It's also extremely easy to use October without the frontend "cms" module entirely and just use the backend functionality for custom Web applications.

Feel free to join the discord to ask more specific questions: https://octobercms.com/support


Speed up slave replication on large database / quickly spawn slave

There are two problems here, which must be solved independently.

Creating a slave

With that size, 100GB, mysqldump is usually to slow too be done efficiently. Try using a binary backup. You have several options: @paul is telling you one, but it has the inconvenient that the master will be locked for the duration of the copy process. Additionally, rsync can be very efficient if you have multiple small files, but it may not be for larger file sizes changed randomly throughout it (if you have a large ibdata1).

My recommendations would be snapshoting (if you are using virtual machines or a filesystem that allow it: ZFS, any other on top of LVM, etc.) or Percona Xtrabackup/Oracle Enterprise Backup. These options will make the backup process almost as fast as copying files from the filesystem without almost no lock at all. Some of them also allow for parallel copy, if your bandwidth allows it.

If none of these work for you, try using a logic parallel backup/restore utility like mydumper.

Replication with increasing lag

You must discover why that is happening first, (profile your queries on both servers) but these are some of the most common causes:

Use hardware/resources at least as good or better that the master for your slave. If the slave is slower, as replication runs in a single thread (mostly), the slave will lag.
If you have long-running transactions in concurrency, try using the binlog_format = ROW. It can augment your bandwidth usage but reduce the slave's load.
Try upgrading the MySQL versions, if that is a possibility. There has been a lot of performance improvements in terms of query execution and binary log in later versions. For example, one reason why you may be lagging is because the buffers are not hot on the recently-started slave. That is mitigated partially in the latest versions. Also, multiple-thread replication execution has been somewhat integrated.
You can relax some integrity configurations on the slave, as in the case of a crash, you can always re-import from the master in the event of the crash (innodb_flush_log_at_trx_commit, for example).
As a last resort, you can try alternative protocols that allow more synchronized communication between servers, but that is usually involves much more work.


You'd copy and paste the commands and then run them.  If you want to do this in a single step, you can write a small PL/SQL block

BEGIN
  FOR s IN (SELECT *
              FROM v$session ao
             where (length(trim(ao.username)) = 5
               and upper(substr(trim(ao.username),1,1)) = 'E')
  LOOP
    EXECUTE IMMEDIATE  'ALTER SYSTEM KILL SESSION '||
                         CHR(39)|| s.SID ||',' || s.SERIAL# ||CHR(39) ||
                         ' IMMEDIATE';
  END LOOP;
END;