Statement Based Replication still giving duplicate Entry Error New to the replication. I am configuring multi-master replication. I did the following steps:

made one slave.
configured two masters to it.

Now i am performing following scenario:

disconnect both masters from this slave. (TEST_TABLE having the last row with P_ID 30)
After discoonecting, add an entry (into TEST_TABLE) on both MASTERS. (primary key entry with auto_incremeent went to 31)
reconnect slave to master-1 (It replicate the data with P_ID 31 on it)
reconnect slave to master-2 (Here comes the Duplicate Entry ERROR)

I am unable to figure out what the purpose of replication is if i have to face this issue? or am i missing something too basic?

I have done this with both Row Based Replication as well as Statement Based Replication but same error. So what's the point of SBR? Only to log statement rather than row data? Or if it's replicating the statement, why issuing the same P_ID and not just running the command generally and auto_incrementing the id to 32.

Reference query can be: **INSERT INTO TEST_TABLE ('firstname','lastname') VALUES('f_name_entry','l_name_entry')**

If really i am getting all this wrong and this is the exact behavior replication do, then whats the solution to it?

Banging my head for whole week on this. Kindly ask if anything is missing and Sorry if i am unable to explain something. Thanks in advance to bear me.

Replication behaved exactly how it is supposed to be- you wrote the same row (as in, it had the same id on both masters, even if data was different), but a multi-master setup is not a tight cluster- it just redos what on the replicas the same things that happened on the master, up to the ids. It does not handle conflict resolution automatically.

I understand that you expected, with Statement Based Replication, to replicate the actual SQL (vs ROW, which replicates the previous old and new rows), so it gets a new, proper ID. While this is exactly what happens (what it is written to the binary log), it also stores internally the auto_increment id. This is because if later you run a DELETE or an UPDATE, or an INSERT... SELECT it would affect the wrong rows.

Generally, multi-source replication does not behave like a tight cluster- you want to use it just to consolidate data from different tables, or to share tables among several different replica sets. If you want automatic conflict resolution and being able to write to any server on a set, you want clustering technologies such as Galera or InnoDB Cluster which would make your writes either fail or work in a coordinated way when inserted the same data on several hosts.

Technically, it would be possible to making work the setup you want- there is this option called auto_increment_increment (combined with auto_increment_offset) that allows to "merge" inserts from several masters by, for example, creating only odd auto_increment values on one host and even on the other so you configure:

MASTER 1:
auto_increment_increment=2
auto_increment_offset=0

MASTER 2:
auto_increment_increment=2
auto_increment_offset=1

so the autoincs don't collide. There are other multiple options, like dropping or renaming the auto_increment column on the replica or creating triggers on it, both while using STATEMENT based replication so they generate the right code.

However, these 2 options are quite fragile (e.g. auto_increment_increment only works for inserts, not for updates or deletes), and tend to make replication break due to side effects- plus when it breaks it is difficult to fix it due to this custom setup, so I don't recommend it normally. I would suggest to avoid setting up replication with different masters and replicas unless it is in a very controlled environment (separate tables or dbs) and try clustering technologies instead. The actual decision would depend on what you really want to achieve in the end, as it is not fully clear on your question- so I am just explained why it didn't work and how people make it work normally.


ALLOW_SNAPSHOT_ISOLATION Duration I would like to enable ALLOW_SNAPSHOT_ISOLATION on a production database that has ~400 million total records. I understand that 14 bytes will need to be added to each record.

If I set ALLOW_SNAPSHOT_ISOLATION will it block for a period of time proportional to the record count, or will the data be updated asynchronously?

Primarily, I want to be sure that my database will not be out of service for hours when this setting is enabled.

...will it block for a period of time proportional to the record count,
  or will the data be updated asynchronously.

Neither.

The additional 14 bytes are used to store two elements:

The transaction sequence number that modified the row.
A pointer to the previous version of the row in the version store (in tempdb) if one exists.

These are only added to the row when it is modified, subsequent to your enabling snapshot isolation. There is no blocking or additional load generated to add the 14 bytes to each row at the point you switch snapshot on.

The only blocking action you may encounter at the point of enabling is due to the need to wait for all current transactions to commit, which is worth keeping in mind. Ideally make the change during a quiet period or preferably a moments downtime where you shut all activity out.

If downtime isn't an option, avoid any period where long running transactions might occur (ETL for example). If you don't get a response within a few seconds, you can query sys.dm_tran_active_snapshot_database_transactions to identify what's getting in the way.


How to update a huge MySQL table from a text file I have a huge MySQL table with 1.3 TB data with InnoDB engine format.

Now I have a text file containing updated values of 2.2 GB of records in the 1.3 TB of data.

How do I proceed ?

I am a newbie to this data loading part. Please guide me through this.

Here the sample data present in the .txt file.

91653   8       2       1       4       Publisher                               2       1       4
91653   8       2       2       5       Publisher                               3b      1       5

since you are using TSV file you can use the following command to import the data:

LOAD DATA INFILE 'data.tsv' INTO TABLE database_name.my_table;

references:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

http://forums.devshed.com/mysql-help-4/importing-tab-separated-file-tsv-table-853669.html

http://dev.mysql.com/doc/refman/5.1/en/loading-tables.html


UPDATE, subquery with SELECT TOP I wrote this query:

UPDATE authors
SET best_seller =
    (
    SELECT TOP 1 book_id
    FROM books
    WHERE books.author_id=authors.author_id
    ORDER BY copies_sold DESC
    )

I would like the SELECT to be evaluated once for every row in books, so that every author record contains his/her most sold book. Let's ignore:

Performance
The bad idea of storing something that could be computed in other ways
The case that there is a tie between 2 books of the same author

Do you see any other problem with the query, or will it work as expected in all cases?

I don't see any issues with your query, regarding correctness of the update.

Another way to get the same results, using window functions.

Regarding efficiency, you'll need to check execution plans and test both methods.

UPDATE a
SET a.best_seller = b.book_id
FROM authors AS a
  JOIN
  ( SELECT author_id, book_id,
           rn = ROW_NUMBER() OVER (PARTITION BY author_id
                                   ORDER BY copies_sold DESC)
    FROM books
  ) AS b
  ON  b.author_id = a.author_id
  AND b.rn = 1 ;


Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012 Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev database on both servers. Other than the server version the databases are identical and the Compatibility Level is still set to 90 (SQL Server 2005).

When I run a query on SQL Server 2005, similar to the example below, a string is returned with extra spaces for any blank fields. If I run the same query on SQL Server 2012 there are no extra spaces being returned. As far as I can tell it seems to be an issue with doing a UNION ALL against a table with no records and a char data type but I wanted to ask anyway.

For the below example you'd need to create a table with one NOT NULL, Char(2) column named Unit6. Leave the table empty with no records once created.

CREATE TABLE [dbo].[tblTest](
    [Unit6] [char](2) NOT NULL
) ON [PRIMARY]

When I run the following query I receive two different results depending on the server version:

SELECT
   Unit5, Unit6,
   LEN(Unit6) as Len_Unit6,
   '--|' + Unit6 + '|--' as Spanner
FROM
   (SELECT
       '13' as Unit5,
       '' as Unit6
    UNION ALL
    SELECT
        '',
        Unit6
    FROM tblTest) as MyData

Results on SQL Server 2005

Results on SQL Server 2012

Hopefully you can see the Spanner column shows two spaces when run on 2005 vs zero spaces when run on 2012.

This is a major issue as the app runs queries similar to the above example and then later uses string functions to cut up a single string for database inserts and updates. Without the spaces which are being included on SQL Server 2005 the string functions are carving up the wrong pieces which causes any inserts or updates to fail.

Any information would be most helpful. Thank you!

Server Versions

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)
Oct 25 2013 19:04:40
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Check the ANSI_PADDING setting:

  Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

http://msdn.microsoft.com/en-us/library/ms187403.aspx

I just tried on both SQL Server 2005 and 2012 and I don't get those results:

Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
    Dec 10 2010 10:56:29
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

(1 row(s) affected)

Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)

Microsoft SQL Server 2012 (SP1) - 11.0.3349.0 (X64)
    Mar  8 2013 17:33:56
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

(1 row(s) affected)


Maintenance Plan produces BAK files I cannot use I have a maintenance plan that does the following:

Check Database Integrity
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Backup Database
Maintenance Cleanup Task

It runs on schedule once a day and I have now tried to re-install a BAK on my local machine and I get the following error message:

  The Log or differential backup cannot be restored because no files are
  ready to rollforward.

I barely understand the concept of how Transactional Logs are handle by SQL. In my mind, when a transaction runs and completes, not sure what "trace" leaves on the log file but in min mind that should just be a trace, not operational data that the MDF depends on.

From what I read ...it seems I'm wrong :)

But going back to my initial problem, I need to create a daily LOG that is USABLE.

I thought using a Maintenance Plan was the way.

Obviously not !!!

=========
EDITED:
I removed the unnecessary blocks ...but does that solve my original problem?

Maintenance plans are certainly one way to perform database backups and a multitude of customers use them with success each day. My guess here, looking at the inclusion of the shrink job and the reorganize followed by rebuild that the maintenance plan was just quickly created without understanding the tasks.

When you say you have a "backup database" task - what type of backup is it? My assumption in this answer is that you are taking Full Backups and then Taking Differentials and possibly Log backups.

The problem likely isn't your backup, per se (though I suggest you spend some time understanding recovery models and the backup command at books online as you are likely not creating your backups in the most optimal order for your recovery intent).

I believe you are likely having an issue because you are restoring in this order:

1.) Restore the full database backup (.bak) first keeping all the defaults
2.) Attempt to restore the differential or the log backup.

Or you are just attempting to restore the log or differential on top of an existing database.

If this is the case - the issue is understanding how recovery models work and the proper restore sequence. (One more link to read - about transaction log restores) but in a nutshell:

If you plan on restoring any differentials or log backups, you have to first restore the full backup AND you must specify "WITH NORECOVERY" when restoring that full bakup. If you keep the default (WITH RECOVERY) then SQL Server performs the necessary recovery processes to make your database operational (basically rolls forward transactions that were done and consistent at the time of the backup, rolls back ones that weren't). If you want to restore additional backups (only the latest differential or each log file between the last full or differential backup and the time you want to restore to) then you must specify WITH NORECOVERY for each restore until the last one where you specify WITH RECOVERY finally.

I'd definitely spend some significant time with the Microsoft Books Online links I sent here and click on the links they refer to as well to best understand backup and recovery.


How to speed up string cleanup function? I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced.

I am new to Postgres. My function ufn_cie_easy() performs way too slow:

DECLARE
  letter char = '';
  str_result TEXT = '';
  x integer;
  y integer;
  asc_code int;
BEGIN
  y:=1;
  x:=char_length(arg);
  LOOP
    letter=substring(arg from y for 1);
    asc_code=ascii(letter);
    IF (asc_code BETWEEN 47 and 58) or (asc_code BETWEEN 65 and 90) or (
        asc_code BETWEEN 97 and 122) THEN
      str_result := str_result || letter;
      ELSIF (asc_code BETWEEN 192 and 197) THEN
      str_result := str_result || 'A';
      ELSIF (asc_code BETWEEN 200 and 203) THEN
      str_result := str_result || 'E';
      ELSIF (asc_code BETWEEN 204 and 207) THEN
      str_result := str_result || 'I';
      ELSIF (asc_code BETWEEN 210 and 214) OR (asc_code=216) THEN
      str_result := str_result || 'O';
      ELSIF (asc_code BETWEEN 217 and 220) THEN
      str_result := str_result || 'U';
      ELSIF (asc_code BETWEEN 224 and 229) THEN
      str_result := str_result || 'a';
      ELSIF (asc_code BETWEEN 232 and 235) THEN
      str_result := str_result || 'e';
      ELSIF (asc_code BETWEEN 236 and 239) THEN
      str_result := str_result || 'i';
      ELSIF (asc_code BETWEEN 242 and 246) OR (asc_code=248) THEN
      str_result := str_result || 'o';
      ELSIF (asc_code BETWEEN 249 and 252) THEN
      str_result := str_result || 'u';
      ELSE
      CASE asc_code
        WHEN 352 THEN str_result := str_result || 'S';
        WHEN 338 THEN str_result := str_result || 'OE';
        WHEN 381 THEN str_result := str_result || 'Z';
        WHEN 353 THEN str_result := str_result || 's';
        WHEN 339 THEN str_result := str_result || 'oe';
        WHEN 382 THEN str_result := str_result || 'z';
        WHEN 162 THEN str_result := str_result || 'c';
        WHEN 198 THEN str_result := str_result || 'AE';
        WHEN 199 THEN str_result := str_result || 'C';
        WHEN 208 THEN str_result := str_result || 'D';
        WHEN 209 THEN str_result := str_result || 'N';
        WHEN 223 THEN str_result := str_result || 'ss';
        WHEN 230 THEN str_result := str_result || 'ae';
        WHEN 231 THEN str_result := str_result || 'c';
        WHEN 241 THEN str_result := str_result || 'n';
        WHEN 376 THEN str_result := str_result || 'Y';
        WHEN 221 THEN str_result := str_result || 'Y';
        WHEN 253 THEN str_result := str_result || 'y';
        WHEN 255 THEN str_result := str_result || 'y';
        ELSE str_result := str_result;
      END CASE;
      END IF;
    y:=y+1;
    exit when  y=x+1;
  END LOOP;
  return str_result;
END;

The function should be noticeably faster with fewer assignments (none, actually, in this updated version):

CREATE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
BEGIN
RETURN replace(replace(replace(replace(replace(replace(
         translate($1,'ŠŽšžŸÝÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜàáâãäåçèéêëìíîïñòóôõöøùúûüýÿ!"#$%&()*+,-./:;<=>?@[\]^_`{|}~€‚ƒ„…†‡ˆ‰‹‘’“”•–—˜™›¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×Þð÷þÐ'
                     ,'SZszYYAAAAAACEEEEIIIINOOOOOOUUUUaaaaaaceeeeiiiinoooooouuuuyy')
         ,'Œ','OE')
         ,'Æ','AE')
         ,'œ','oe')
         ,'æ','ae')
         ,'ß','ss')
         ,'''','');
END
$func$  LANGUAGE plpgsql;

Assignments are comparatively expensive in plpgsql.

But I suspect you really want to remove all accents (diacritic signs). Postgres provides the function unaccent() with the additional module unaccent:

CREATE OR REPLACE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
SELECT translate(unaccent(
        replace(replace(replace(replace(replace(
          $1
         ,'Œ','OE')
         ,'Æ','AE')
         ,'œ','oe')
         ,'æ','ae')
         ,'ß','ss')
       ), '!"#$%&()*+,-./:;<=>?@[\]^_`{|}~€‚ƒ„…†‡ˆ‰‹‘’“”•–—˜™›¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿×Þð÷þÐ''', '');
$func$  LANGUAGE sql IMMUTABLE;

Substantially faster, yet. And superior in every aspect if you actually want to remove all diacritic signs.

In addition to unaccent():

Include an escaped ' ('') in translate() directly.
Make it a simple SQL function.
Make the function IMMUTABLE.

Detailed explanation and more related issues to consider if you need an IMMUTABLE function with unaccent():

Does PostgreSQL support “accent insensitive” collations?

In Postgres 9.5 or older we need to expand ligatures like 'Œ' or 'ß' manually, since unaccent() always substitutes a single letter:

    SELECT unaccent('Œ Æ œ æ ß');

    unaccent
    ----------
    E A e a S

What I would do in Postgres 9.6

After this update to unaccent:

  Extend contrib/unaccent's standard unaccent.rules file to handle all
  diacritics known to Unicode, and expand ligatures correctly (Thomas
  Munro, Léonard Benedetti)

Bold emphasis mine. Using unaccent() like instructed in the linked answer above:

CREATE OR REPLACE FUNCTION ufn_cie_easy(text)
  RETURNS text AS
$func$
SELECT trim(regexp_replace(regexp_replace(public.unaccent('public.unaccent', $1)
                                        , '[^a-zA-Z\d\s]', '', 'g')
                         , '\s+', ' ', 'g'));
$func$  LANGUAGE sql IMMUTABLE;

Almost, but not quite 100 % identical to what you had. Better IMHO:

Replace all accents and expands all ligatures with unaccent().
Remove all noise characters (everything except ASCII letters, digits and white space).
Fold whitespace to a single space (' ').
Trim leading and trailing spaces.


Date range query for past 24 hour in Mongo shell I am setting a cron job to collect results from MongoDB database profiler. I'd like to collect results within a 24 hrs period. I plan to run mongo command with javascript.

Question is, in Mongo shell, how do I write a query to find a date range from 24 hrs ago? Such as:

db.system.profile.find({
    "timestamp" : {
        $lte : <current date & time>,
        $gt : <date & time 24 hrs ago>
    }
})

Found answer here:
https://stackoverflow.com/questions/1296358/subtract-days-from-a-date-in-javascript

db.system.profile.find({
  "timestamp" : {
    $lt: new Date(),
    $gte: new Date(new Date().setDate(new Date().getDate()-1))
  }
})


pg_restore: [archiver] unsupported version (1.11) in file header postgresql My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a dump file from my server.

The dump sentence I used was:

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "/home/damian/backups/mybase.backup" mybase

To restore I'm using:

pg_restore -i -h localhost -p 5432 -U postgres -d mybase -v "/home/damian/backups/mybase.backup"

How can I restore a dump file from a newer version of PostgreSQL into an older version of PostgreSQL?

Thanks for any help in advance.

Edit: I tried to use a different parameter in the option -F, this time I tried to make a dump file in plain text but some of the information didn't restore correctly, how can I solve this?

Edit: I thought the problem was the encoding my database was using but I changed it to UTF8 -which is my dump file encoding- but I got the same error as before:

error: syntax error at or near Â«1Â»
LINE 1: 1 TELÃFONO NÃMERO TELEFÃNICO MEDIANTE EL CUAL SE PUEDE ESTAB...

I'm really lost at what is causing these errors.

PostgreSQL really doesn't put much effort into supporting moves from newer major versions back to older ones.  One big reason for that is that newer versions usually support features which you just can't install on older versions; although any given database might not be using any of the new features, the project philosophy is that they would rather not support it than to support it in a buggy or incomplete fashion.

Your best bet might be to generate a "plain" format dump rather than a "custom" format dump.  This is just a text file of SQL commands.  If you aren't using any new features, it might just apply cleanly.  If not, you can look into the errors and decide what to do for a back-port.


How to access a fresh install of SQL Server 2012? I've performed a fresh install of SQL Server 2012 on my local development PC. I've done this before and it has worked, however this time I'm getting the following error message when I attempt to sign into SQL Server.

"Login failed for user xxxxxxxxxx (Microsoft SQL Server, Error: 18456)"

How can I access my local instance of SQL Server? I'm rather ignorant about SQL Server security.

Since you have access to the machine itself, you can follow the instructions here to (re)add yourself as a sysadmin: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out

The short version of what that says is:

Stop SQL Server
Restart with the minimal configuration option -m
Log into the server with sqlcmd -S {your machine name or localhost}
Run the following commands:

CREATE LOGIN [Domain\LoginName] FROM WINDOWS;
EXEC sp_addsrvrolemember '[Domain\LoginName]', 'sysadmin';

Stop SQL Server
Restart it with the normal configuration


what do vertical lines refer to in database diagram view source: https://msdn.microsoft.com/en-us/library/ms188251.aspx

Examples above

I understand one to one relationships, one to many, PK, FK and all of that, but

I get really confused about understanding what verical lines refer in a relational database setup

Could someone elaborate on the highlighted area?

What is #1 and #2 referring to in this diagram?

Is #1 referring to a one to many relationship for COUNTRY: TITLEID?
and #2 discount to STORE ID?

I'm having trouble visually interpreting the diagram

The answer for to your question is further down in the article:

  Endpoints The endpoints of the line indicate whether the relationship is one-to-one or one-to-many. If a relationship has a key at one endpoint and a figure-eight at the other, it is a one-to-many relationship. If a relationship has a key at each endpoint, it is a one-to-one relationship.

The key represents the "one" and the figure-eight (or infinity symbol, I presume) represents the "many".

The relationships aren't tied to the visually closest column name in the diagram, but to the column with the same name in each table. For instance, the one-to-many relationship between publishers and titles is tied to the pub_id column.


AlwaysOn Availability Groups: connecting correctly? This question is about the correct way of accessing an Availability Group Listener:

Assume I have two AlwaysOn Availability Groups with the following AG's: AG1 and AG2.
I also have 2 listeners called LISTENER1 (on AG1) and LISTENER2 (on AG2).
I have 2 SQL nodes NODE1 and NODE2 and both have 2 SQL instances running: INSTANCE1 and INSTANCE2.

How do I correctly connect to the primary replica of AG1 via the listener?
I know it's not supposed to be NODE1\INSTANCE, but which of the following is correct?

LISTENER1

or

LISTENER1\INSTANCE1

I noticed both work fine and I assume the latter connects to the primary replica of LISTENER1 and than lets SQL Browser do its magic and connects to INSTANCE1 (which - by luck - happens to be correct in this particular setup).

I noticed if I use LISTENER1\INSTANCE2, this works as well, BUT connects to the INSTANCE2 instance of the node which hosts the primary replica of AG1!

The rabbit hole goes even futher: if I install a default instance on both SQL nodes I get yet another behavior: Connecting to LISTENER1, connects me to the default instance.

Is my logic correct (SQL Browser service) or am I just completely wrong here?

FYI: Named instances in this case use dynamic ports and all listeners are hosted on port 1433

There's some weirdness when you use AG Listeners along with multiple standalone SQL instances.  Note that the more common configuration is to install a single standalone instance per node, possibly participating in multiple AGs.

When you enable TCP/IP connections for a SQL instance, the default configuration is for the SQL instance to listen on a port (either fixed or dynamic) on "All IP Addresses":

For standalone instances "Listen All" means that it will listen for incoming connections on all current and future IP addresses.  If an IP address is later added to the system, the instance is already listening on its port on the new IP.  And an AG Listener is just a Network Name and IP that the cluster adds to whichever node hosts the primary replica.

For Failover Cluster Instances, "Listen All" means that the instance will listen on all IP addresses that are dependencies of the FCI's Virtual Network Name, so they don't have this problem.

So, when you have multiple standalone instances where one or more participates in an AG with an AG Listener, you need to perform some extra configuration to prevent every instance from listening on every AG Listener's IP.

Configure each instance to listen on a non-default port (can be dynamic), only on the host's main IP(s), reserving port 1433 for the AG Listener(s).  That way you can connect to each AG Listener without a port number or instance name, and to the instances directly only with a port number or instance name.


How should I build the architecture of my MongoDB database? I have a project about e-Sports. In this project, I have VDS Game Servers for players. With this game servers, our players will play Ranked Games and players will earn Matchmaking Rank Points for their rankings. When 10 players match in web application/desktop application/mobile application; the system will choose a server which one is idle and game will start. After the start of game, the status of Server will need to be changed real-time.

For example, Team A has 9 score and Team B has 10 score. When Team A got one more point, the match status should be [Team A 10 - 10 Team B] and I can get this changed in database.

Server documents look like this:

{
    ip: address.split(':')[0],
    port: address.split(':')[1] || 27015,
    pass: pass,
    live: false,
    map: '',
    maps: [],
    knife: false,
    score: [],
    round: 0,
    knifewinner: false,
    paused: false,
    freeze: false,
    unpause: {
        'TERRORIST': false,
        'CT': false
    },
    ready: {
        'TERRORIST': false,
        'CT': false
    },
    steamid: [],
    admins: [],
    queue: [],
    players: {},
    banner: '',
    pool: [],
    banned: [],
    picked: [],
    stats: ''
};

The Question

Should I use MongoDB for real-time changes (updates) in this project?

Thanks!

Should I use MongoDB for real-time changes (updates) in this project?

Yes, you can use MongoDB for real-time updates. There are two options available for real-time updates based on whether you are using MongoDB Atlas(Fully Managed Cloud MongoDB service) or Self managed MongoDB service.

Self Managed(On-Prem/Cloud) - From MongoDB 3.6 the new feature Change Streams got introduced. Change streams allow applications to access real-time data changes without the complexity and risk of tailing the oplog. Applications can use change streams to subscribe to all data changes on a single collection, a database, or an entire deployment, and immediately react to them.
MongoDB Atlas - This comes with a service called Stitch Triggers. This will enable you to execute application and database logic automatically, either in response to events or based on a pre-defined schedule. This is the backend uses the Change Streams functionality.

So MongoDB should be a perfect option for your requirement.


How and when do I use an alias in my SQL statement I have a query in which I do some calculation and then I give that column an alias. In the next column I would like to use the result of that calculation in an IF statement. MySQL did not recognise the alias when I used it as a condition but instead required me to rewrite the whole query, put some brackets around it and then carry out the condition checking.
Here is the SQL query:

SELECT StudentId
    ,SubjectID
    , (COUNT(StudentId) / (
        SELECT COUNT(SubjectID)
        FROM lectureattendancein
        WHERE SubjectID ='MIS4' ) * 100) AS Percentage
    , IF((COUNT(StudentId) / (
        SELECT  COUNT(SubjectID)
        FROM lectureattendancein
        WHERE SubjectID ='MIS4'
        )* 100) >= 80, 'ALLOWED', 'NOT ALLOWED') AS ExamAdmit
FROM attendancein
GROUP BY StudentId, SubjectID
HAVING SubjectID='MIS4'
ORDER BY StudentId ASC

I would like to use the column alias called Percentage in the IF statement

Structured Query Language is a declarative language.  Declarative languages don't tell the computer how to run, they simply describe the desired result.  The implication here is the alias is only important for the results, not the execution of the query.

Since MySQL (and most other RDBMSs) only apply aliases after the query has completed, MySQL will complain if you attempt to use an alias in another column since it doesn't actually understand what the alias represents until the query completes.


Why does this query, missing a FROM clause, not error out? So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!?

SELECT

    1
   ,r.id
   ,'0D4133BE-C1B5-4141-AFAD-B171A2CCCE56'
   ,GETDATE()
   ,1
   ,'Y'
   ,'N'
   ,oldItem.can_view
   ,oldItem.can_update

FROM Role r

JOIN RoleObject oldReport
    ON r.customer_id = oldReport.customer_id

JOIN RoleItem oldItem
    ON oldReport.id = oldItem.role_object_id
        AND r.id = oldItem.role_id

WHERE r.id NOT IN (SELECT
        role_id
    WHERE role_object_id = '0D4133BE-C1B5-4141-AFAD-B171A2CCCE56')

AND oldReport.id = '169BA22F-1614-4EBA-AF45-18E333C54C6C'

This statement is legal (in other words, no FROM is required):

SELECT x = 1;
SELECT x = 1 WHERE 1 = 1; -- also try WHERE 1 = 0;

The trick is when you introduce a column name that clearly can't exist. So these fail:

SELECT name WHERE 1 = 1;

SELECT x = 1 WHERE id > 0;

  Msg 207, Level 16, State 1
  Invalid column name 'name'.
  Msg 207, Level 16, State 1
  Invalid column name 'id'.

But when the invalid column is introduced in something like a subquery, what SQL Server does when it can't find that column in the inner scope of the subquery, is traverse to an outer scope, and make the subquery correlated to that outer scope. This will return all rows, for example:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE 1 = 1);

Because it's essentially saying:

SELECT * FROM sys.columns WHERE name IN (SELECT sys.columns.name WHERE 1 = 1); /*
              ^^^^^^^^^^^                       -----------
                   |                                 |
                   -----------------------------------    */

You don't even need a WHERE clause in the subquery:

SELECT * FROM sys.columns WHERE name IN (SELECT name);

You can see that it's really looking at the outer scoped table, because this:

SELECT * FROM sys.columns WHERE name IN (SELECT name WHERE name > N'x');

Returns far fewer rows (11 on my system).

This involves adherence to the standard about scoping. You can see similar things when you have two #temp tables:

CREATE TABLE #foo(foo int);
CREATE TABLE #bar(bar int);

SELECT foo FROM #foo WHERE foo IN (SELECT foo FROM #bar);

Obviously, this should error, right, since there is no foo in #bar? Nope. What happens is that SQL Server says, "oh, I didn't find a foo here, you must have meant the other one."

Also, in general, I would avoid NOT IN. NOT EXISTS has the potential to be more efficient in some scenarios, but more importantly, its behavior doesn't change when it is possible that the target column could be NULL. See this post for more info.


Is there any best number for rows count in a table to start using indexing? I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between table's rows count and using index, but I don't know if there is an standard table's rows count or not.

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index.

The rowcount in a table does not dictate the use of Index. Its your queries (pattern) that should dictate the need for creating indexes, so that the data retrieval is as fast as possible.
The query optimizer relies on statistics to produce optimal query plan.
From SQL Server Index Design Guide :

The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost.
Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead.
Wide indexes, on the other hand, cover more queries. You may have to experiment with several different designs before finding the most efficient index. Indexes can be added, modified, and dropped without affecting the database schema or application design.

You should use SQL Server's DMV to tune your indexing strategy or even use sp_BlitzIndex to get more insights.
Refer to :

Query Tuning Fundamentals: Density, Predicates, Selectivity, and Cardinality
Ten Common Threats to Execution Plan Quality


Run out of virtual memory after added to triple physical RAM and change SQL Server memory option? Recently, we added some physical RAM to our server which running SQL Server 2008 R2 instance, the total memory was changed from 64GB to 192GB.

And we tuned the memory option of SQL Server by changing the 'min server memory' from 16GB to 64GB, and the 'max server memory' from 48GB to 128GB.

And the pagefile of Windows Server 2008 R2 is left unchanged with 64GB.

But after two days later, Windows Server reported run out of virtual memory, some 'Windows successfully diagnosed a low virtual memory condition' events can be found in EventLogs. We cannot connected via remote desktop client, and cannot launch mmc or notepad application even though operating from console, only Explorer and task manager can be opened. at that time, about 50% physical memory was used.

And now, we change 'min server memory' back to 16GB, the server runs without any resource exhaustion detector events, but the physical RAM usage was always lower than 64GB.

SQL Server Version:

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64) Jun 24 2011
  13:09:43 Copyright (c) Microsoft Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Please help us to solve it.

Thanks.

I believe your virtual memory should be, at minimum, the same size as your physical memory.

I once had a new server deployment that had tested well, but performed terribly under a full load. MS got involved, and finally figured out we had a 16GB page file, with 128GB physical memory. Their recommendation was that the page file be at least 1.5 times as large as physical memory.

Bumping our page file size up to 192 GB got performance back to normal. Not sure if 1.5 X physical memory is truly required (in fact, I doubt it), but page file significantly less than physical memory does seem to be a Bad Thing.

NOTE: Our system at the time was Windows Server 2008R2, SQL Server 2008.


SQL Server 2012 Restore backup to new DB Name I seem to remember that, in 2008, you could restore a backup to a new copy of a DB, by changing the name in the "Destination Database" field from the restore-wizard.  It would create a brand new DB, which is a copy of the original DB restored to the point in time that you wanted.  I have not for the life of me figured out how to make SQL 2012 do this.

Now, I understand (thanks to Aaron Bertrand) that this didn't really change, and that 2012 is actually making it more obvious to me that this strategy was a bad idea in the first place!

So, what I need to do is this: Create a new DB, 'MyDB_Copy', from an existing DB, 'MyDB', by using its backup files.  We have nightly full-backups (.bak) and every-15-minute TLogs (.trn).  I don't want the existing 'MyDB' to be affected/touched at all, because it's "live".

After the MyDB_Copy is created from the main full-backup file, I then need to restore a few dozen TLog backups to get it to a certain point in time.

Based loosely on Example E in the documentation, open a new query window and run:

RESTORE DATABASE MyDB_Copy FROM DISK = 'C:\blahblah\MyDB.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'MyDB' TO 'C:\blahblah\Data\MyDB_Copy.mdf',
---------------------------------------^^^^^
  MOVE 'MyDB_log' TO 'C:\blahblah\Data\MyDB_Copy.ldf';
-------------------------------------------^^^^^

The logical names are not important; the physical file names are. This makes assumptions about your logical file names and that there are only two; run EXEC MyDB..sp_helpfile; to be sure.

If you need to restore logs, then change RECOVERY to NORECOVERY:

  WITH REPLACE, NORECOVERY,
----------------^^

Then you can issue a series of:

RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\file1.trn' WITH NORECOVERY;

And on the very last one:

RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\fileN.trn' WITH RECOVERY;

Or if you only need part of a log up until a point in time (I assume you've checked where the LSNs and times are so you know exactly which files you need):

RESTORE LOG MyDB_Copy FROM DISK = 'C:\blahblah\fileN.trn' WITH
  STOPAT = '<some point in time Friday>', RECOVERY;

The way you said worked in previous versions would never have worked, unless the backup came from a different server. By default it will try to put the new mdf and ldf files in the exact same place, and this isn't possible.


Slow SQL Server Database (approx 2TB) - high CPU due to index fragmentation? I've posted this over at ServerFault but didn't have much luck getting a response.

I have a fairly large SQL Server database (~2TB). Most of the data is in one table (~6 billion rows).

Two weeks ago I dropped two non-clustered indexes on the large table and migrated the data onto a single 6TB RAID SSD array. I then recreated the two indexes which took quite some time (assuming because I currently have the data (for table and indexes) and log on the same array and it seems that with the RAID I can't have fast sequential AND random r/w at the same time).

Anyway after recreating the indexes it ran very well for about a week. During the week I have been slowly running a clean on the large table which just removes old unneeded rows. So far I've removed about 300 million out of the 6 billion, and at a guess I still have a lot more to go.

Now after about a week of running like this it is now running very slow and I'm not sure which would be best to do.

Current situation:

Dual Xeon
192GB RAM
Windows Server 2012 with SQL Server 2012
CPU is hitting 100% (16 cores) - was only using about 50% prior to the slowdown
IO doesn't seem to working too hard (no queue)

Large Table currently has (I don't have any fragmentation info prior to now):

1x Clustered index: 48% fragmentation
1x Non-clustered index: 36% fragmentation
1x Non-clustered index: 10% fragmentation
I used to have two more indexes on this table but dropped them a while ago

What do you think would best fix my problem

Rebuild the non-clustered indexes on the same array (assume that this should fix the problem but will take a long time to do as it did before. Will probably have the same problem in the near future as I'm still cleaning the table)
Rebuild the non-clustered indexes on a new RAID array (should fix as above but may be faster)
Move the non-clustered indexes to a new RAID array (quickest option)
Recreate the two old indexes on a new RAID array (not sure if this relieves CPU or IO pressure)

Do fragmented indexes cause higher CPU usage?

Is there anything else I could be missing?

TIA

Instead of looking at indexes, start looking at what queries are causing the high CPU use. Start with Michael J. Swart's DMV queries to find the top CPU-using queries. Look at what indexes the queries are using, whether they're doing things like implicit conversion, etc. But generally no, fragmentation by itself probably isn't doing this.


Logic of Assigning values to variables in Scalar-valued Functions I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.

While troubleshooting my rewrites I came across some oddities in the original code which do not seem to make sense based on my understanding of variables in SQL

If I were to write

 Declare @var as Integer
 Select @var = col1 from table

Then this would always return the last item in col1. However, in the function that I am working with, this does not seem to be the case. Initially, there was a where clause which was changing results based on the input, but, removing that, the above property should hold. For the most part, it does, however there are a couple of cases where this is not true.

The outline is roughly similar to the following:

create function f (
@var1 ... )
returns datatype
begin
    Declare @temp1 ... (several vars declared here)
    select @temp1 = col1, @temp2 = col2, ..., @tempX = max(colX) from(
        select col1, col2, ... ,
        colX * (case when name = 'X' then 1 else 0)
        from table1
        INNER JOIN table2 on ...
        inner join table3 on ...
        left join table4 on ...
        where x) as sq
    group by ...
    return ...
end)

While more convoluted than the basic example above, with the where clause removed, it seems that it should be logically equivalent, however, If i run the function and look at what it's actually returning, it is not always the same value. Please note, that for testing purposes, the function is only returning one of the variables assigned through the select. Which, assuming similar functionality to the simple case given above, should always return the same value.

The values that are passed in as parameters should not affect this specific statement (this is a subset of the whole function, but I've created a smaller function to test this part of the code and decided to keep the same values passed in for consistency's sake). Only the newly declared variables are used and given values; excluding the where clause which I have commented out during my testing.

Then, my question is, how would these tables change in order to change the last element if the joins are always going to be the same and there is no where clause to change the values in the joins? grouping should be consistent as well since all of the tables should be the same every time the function is run.

The subquery should always return the same table and then the variables should be assigned the last value in their specific column, meaning that the return for the function should always be the same value.

Then this would always return the last item in col1.

What does "last item" mean?  In SQL Server, and indeed most if not all RDBMSs, there is no concept of "last" unless you specify an ORDER BY clause.
The apparent order of results can change drastically if the plan changes due to things like parameter sniffing, updated statistics, new or modified indexes, etc., etc.
Never1 rely on observed order behavior without an explicit ORDER BY.
This question and the attached answers make for interesting reading on the topic, as well.

Yes, I did just say never.  This instance is an exception to the rule "never say never."


Possible INDEX on a VARCHAR field in MySql I am working in a MySql database, with a table like this:

+--------------+
|  table_name  |
+--------------+
|    myField   |
+--------------+

...and I need to make a lot of queries like this (with 5-10 strings in the list):

SELECT myField FROM table_name
WHERE myField IN ('something', 'other stuff', 'some other a bit longer'...)

There will be around 24.000.000 unique rows

1) Should I use a FULLTEXT or and INDEX key for my VARCHAR(150)?
2) If I increase the chars from 150 to 220 or 250... would it make a great difference? (Is there any way to calculate it?)
3) As I said, they are going to be unique, so myField should be a PRIMARY KEY. Isn't it rare to add a PRIMARY KEY to a field which is already a VARCHAR INDEX/FULLTEXT?

SUGGESTION #1 : Standard Indexing
CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    key (myfield)
);

If you index like this, you can either look for the whole string or do left-oriented LIKE searches
SUGGESTION #2 : FULLTEXT Indexing
CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    fulltext (myfield)
);

You can effectively use searches for individual keywords as well as whole phrases. You will need to define a custom stopword list because MySQL will not index 543 words.
Here are my other posts from the past two years on FULLTEXT indexes

May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

SUGGESTION #3 : Hash Indexing
CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    hashmyfield char(32) not null,
    primary key (id),
    key (hashmyfield)
);

If you are looking for one specific value and those values could be lengths well beyond 32 characters, you could store the hash value:
INSERT INTO mytable (myfield,hashmyfield)
VALUES ('whatever',MD5('whatever'));

That way, you just search for hash values to retrieve results
SELECT * FROM mytable WHERE hashmyfield = MD5('whatever');

Give it a Try !!!


How do I return an index for each nvarchar col in a table? Imagine a simple table as follows

Create table test ( c1 nvarchar(10), c2 nvarchar(10), i1 int, c3 nvarchar(10) );

Is there a way I can query and get The index for the nvarchar columns?

The index would be 0 for c1 and 1 for c2 and 3 for c3 or if it is 1 and 2 and 4 respectively that is also ok

Check out the INFORMATION_SCHEMA. I guess something like:

SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ...
  AND TABLE_NAME = ...
  AND DATA_TYPE IN (...)


Semi-Annual archiving of SQL Server database Twice a year we create a static, archived subset of our production database.  The newly created database contains roughly half of the tables as the production db (log/audit tables, etc. are not copied over).  Also, several records are not included (records marked as inactive, etc.)

Once created the archive db is set to read-only access.  From the front-end, users are able to switch from the production db to any one of the read-only archives.  The archives remain available indefinitely.

The data is in SQL Server 2008 R2.  What is the best way to automate this type of situation (we've done it several different ways in the past, but have not settled on a single, straight-forward approach.)

Restore From Backup --> [Drop Unused Tables]

Perform a full backup of database ProductionDb.
Restore from backup to database ArchiveDb2011B.
[optional] Drop unnecessary tables.
Set database ArchiveDb2011B to read-only.

Problems with this approach:

It uses the most disk space.  Our previous archives are roughly 1GB in size.  Our production db is about 4GB in size.  If we skip step 3, we end up with archives four times bigger than what we typically have had.
Dropping tables requires first dropping any related foreign keys.
Triggers remain in place in the archive (the production db lives in a merge replication topology).  Is this a moot point because the archive will be read-only?

Pros:

It is relatively simple.  If we skip step 3, it is dead simple.
Performance should be pretty good because all of the indexes remain in place.  (We have missed indexes in the past using other approaches.  We don't usually realize it's a problem unless we happen to overhear users complaining about how slow a particular archive is.)

We have not used this approach for archiving purposes.  We have used this approach to create dev environments and it works well in that scenario.  It seems like the biggest drawback is the extra disk space used.  Disk space keeps getting cheaper, but it is a four-fold difference over other approaches.


Query slower with index on 14M rows table DB: Amazon RDS MySQL (OS: Linux, 2 vCPU, Memory: 8GB)

I have a table with almost 14M rows of data.

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

As you can see, I use an index on meterid.

Another table which stores device IDs (around 100 rows of data)

CREATE TABLE `devices` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`location` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `meterid_UNIQUE` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

To get 15 minute aggregated data, I use the below query

SELECT AVG(metervalue) as value
 , DATE_FORMAT(date_time, "%d %b %Y %H:%i") as label
FROM meterreadings
WHERE meterid IN (SELECT meterid from devices)
  AND date_time BETWEEN '2018-07-23' AND '2018-07-24'
GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15
ORDER BY date_time ASC;

Query performance is very bad - It takes approximately around 12 seconds to execute, and causes a temporary spike in DB server usage as well.

EXPLAIN on this query returned this:

1   SIMPLE  devices index   meterid_UNIQUE  meterid_UNIQUE  19      125
   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  meterreadings   ref meterid meterid 19  devices.meterid 322
   Using where

I dropped the index on meterreadings and surprisingly the query performance is better - almost about 6 seconds now. I am still wondering why?

EXPLAIN on the query after dropping the index

1   SIMPLE  meterreadings   ALL                 14580167    Using where;
Using temporary; Using filesort
1   SIMPLE  devices ref meterid_UNIQUE  meterid_UNIQUE  19
   meterreadings.meterid    1   Using index

I am currently doing my query operation on the table without index - Is there a way I can optimize the table / query to do the operation faster (like a composite index on two columns?)

[The table is growing approximately by around 40 rows per second]

You should play with it a bit, because it might not be clear beforehand what solution will produce the best results.

A few points to consider

It is very likely that an index on the date column will provide you with better selectivity, as it has a higher selectivity.
Composite indexes are usually a good idea, but please make sure to chose the order correctly date_time, meterid  vs. meterid, date_time. In most cases it makes more sense to leave columns with dense values (i.e dates, floats) to the end, as any column in the index following them is unlikely to have any effect. ( try meterid, date_time for an index.)
Subselects might force the optimizer to use a specific plan. Try converting it into a join if possible.


Count values in column on join then group by Forgive me if this is super easy. I am doing some data analysis on the Stack Overflow database model. I need to count the number of badges a user has. They can have multiple of the same badge name.

For example:

A user can have 3 x Teacher badges, and 2 x Editor badges, etc.

I would like to be able to see it in the below format:

 UserName | Badge_Name | Amount
 ------------------------------
 JamesBond| Teacher    | 3
 JamesBond| Editor     | 2
 Arthur1  | Teacher    | 5

So far all I am able to retrieve is the display name of the user and the amount of badges they hold:

SELECT t2.DisplayName, COUNT(t1.Id) AS TotalNumberOfBadges
FROM Badges t1
JOIN Users t2 ON t2.Id = t1.UserId
GROUP BY t2.DisplayName
ORDER BY t2.Id;

Without seeing the schema I've had to guess but it seems that you just need to add the Badge_Name to the SELECT list and the GROUP BY, like this:

SELECT t2.displayname AS UserName, t1.Badge_Name,
       Count(t1.id) AS TotalNumberOfBadges
FROM   badges t1
       join users t2
         ON t2.id = t1.userid
GROUP  BY t2.displayname, t1.Badge_Name
ORDER  BY t2.id;

If your badges table contains a different column name for the name of the badge you will have to sub that in for where I've used Badge_Name.


Does mysql or mariadb have the concept of SQL Server's "included columns" for indexes? In SQL Server you can create an index and have additional columns included on that index that can help performance in certain circumstances. Is that same ability available for mysql or mariadb under a different name, perhaps? I was unable to find anything using the term "included columns".

Looking at the MySQL Documentation, the glossary indicates this about Covering Indexes:

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

The implication here is that there is no direct equivalent of an INCLUDE statement in MySQL, however if the index provides coverage of the columns involved in an index, it can, rather obviously, still be considered to be covering, much like an index with included columns in SQL Server would be.
MariaDB has the following in their documentation for covering indexes:

A "Covering" index is an index that contains all the columns in the SELECT. It is special in that the SELECT can be completed by looking only at the INDEX BTree. (Since InnoDB's PRIMARY KEY is clustered with the data, "covering" is of no benefit when considering at the PRIMARY KEY.)

Mini-cookbook:

Gather the list of column(s) according to the "Algorithm", above.
Add to the end of the list the rest of the columns seen in the SELECT, in any order.

Examples:

SELECT x FROM t WHERE y = 5; ⇒ INDEX(y,x) -- The algorithm said just INDEX(y)
SELECT x,z FROM t WHERE y = 5 AND q = 7; ⇒ INDEX(y,q,x,z) -- y and q in either order (Algorithm), then x and z in either order (covering).
SELECT x FROM t WHERE y > 5 AND q > 7; ⇒ INDEX(y,q,x) -- y or q first (that's as far as the Algorithm goes), then the other two fields afterwards.

The speedup you get might be minor, or it might be spectacular; it is hard to predict.
But...

It is not wise to build an index with lots of columns. Let's cut it off at 5 (Rule of Thumb).
Prefix indexes cannot 'cover', so don't use them anywhere in a 'covering' index.
There are limits (3KB?) on how 'wide' an index can be, so "covering" may not be possible.


transaction log restore we have an external production database (maintained by another company) and we are trying to build an internal database that replicates the production database for reporting purposes (both sql-server).

Here is the scenario: they have sent us a full backup (BAK) which I already have it restored properly. And they will send us the transaction logs (TRL) on a daily basis.

I know I can restore it with BAK plus all the TRL sequentially and overwrite the previous restore. But is there a way that I can restore with the most recent log I received 'incrementally'?

Thanks in advance. And correct me if I used inappropriate terminologies.

:)

It depends on whether your local server is Enterprise Edition.

If so, you can restore the database backup and leave it in NORECOVERY mode.  You will not be able to directly access the database, but you can create a SNAPSHOT database which will be Read Only.

That snapshot database can be dropped, the new TRL restored in NORECOVERY Mode, and recreate the snapshot.


Parallel pg_restore unsupported I've used many times this technique for transferring databases between servers:

pg_dump --no-owner -Fd mydb -j 4 -f tmp/mydb
scp -r tmp/mydb otherserver:tmp/

then in the other server:

dropdb --if-exists mydb &&
createdb -T template0 mydb &&
pg_restore -j 4 -d mydb tmp/mydb &&
rm -rf tmp/mydb

It has worked flawlessly.
Using Postgres 9.3.16 and CentOS 7.

But now, in a new server, pg_restore complains:

pg_restore: [archiver] parallel restore is not supported with this archive file format

I could remove the -j 4 parameter, but theorically it should be valid.
According to the pg_restore doc:

  -j number-of-jobs
  --jobs=number-of-jobs

  Run the most time-consuming parts of pg_restore — those which load
  data, create indexes, or create constraints — using multiple
  concurrent jobs. This option can dramatically reduce the time to
  restore a large database to a server running on a multiprocessor
  machine.

  Each job is one process or one thread, depending on the operating
  system, and uses a separate connection to the server.

  The optimal value for this option depends on the hardware setup of the
  server, of the client, and of the network. Factors include the number
  of CPU cores and the disk setup. A good place to start is the number
  of CPU cores on the server, but values larger than that can also lead
  to faster restore times in many cases. Of course, values that are too
  high will lead to decreased performance because of thrashing.

  Only the custom and directory archive formats are supported with this
  option. The input must be a regular file or directory (not, for
  example, a pipe). This option is ignored when emitting a script rather
  than connecting directly to a database server. Also, multiple jobs
  cannot be used together with the option --single-transaction.

I'm using the directory format in pg_dump: -Fd !
Why does it complain?

I don't know why, but I have as server Postgres 9.3 and psql, pg_restore and pg_dump belong to version 9.2.

/usr/bin/pg_restore is version 9.2, but /usr/pgsql-9.3/bin/pg_dump is version 9.3.

This is the cause of the problem.

postgresql.x86_64                                     9.2.18-1.el7                                  @base
postgresql-devel.x86_64                               9.2.18-1.el7                                  @base
postgresql-libs.x86_64                                9.2.18-1.el7                                  @base
postgresql-odbc.x86_64                                09.03.0100-2.el7                              @base
postgresql93.x86_64                                   9.3.17-1PGDG.rhel7                            @pgdg93
postgresql93-contrib.x86_64                           9.3.17-1PGDG.rhel7                            @pgdg93
postgresql93-libs.x86_64                              9.3.17-1PGDG.rhel7                            @pgdg93
postgresql93-server.x86_64                            9.3.17-1PGDG.rhel7                            @pgdg93

The solution is simply:

yum remove postgresql


table organization with data that will never change for reference, almost all my DB experience is with mongodb and this is my first postgres project.
I'm storing data updates from decentralized finance crypto pools.
The data structure I'm exploring is very simple.
There are tokens:
type Token =
    {
        Address:    string     // primary key
        Decimals:   int
        Symbol:     string
    }

This data will NEVER change but is referenced everywhere; roughly 2k entries
type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

this is updated every 5 mins, however ONLY Token0Amount and Token1Amount change and I want to keep a history.
I've roughly 500 of these objects with a new update every 5 minutes, so 140k new entries per day.
so the first thing I wanted to do is make a join between the Token0/1Address fields and the Token record, but since the Token just adds an int and a short (up to 32chars, but usually < 8 chars) string, I was wondering is this is doing much saving (I don't know how much space the join is taking).
then I was wondering if it is worth it to split the object into two pieces:
type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
    }

and
type LiquidityTokenUpdate =
    {
        Address:        string    // matches the address of the liquidity token
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

and just replicate this object. But then now a query may pull the update, which pulls the liquidity token which has to pull 2 token objects.
Since this is for a public GraphQL interface, there can be quite a few usage patterns that will pop up. I can't predict a specific type of queries because this is something I want to make public for the community once it's ready.
I understand that caching is my friend here and since there static data is small it all fits in RAM, but overall would it make sense to try to save space (each record is not very large) and add the cost of the joins, or lookup in the local cache, or is it better to just save everything at each update since disk is cheap after all?
Please, put in context that this is not something I have experience with; I've read about joins, normalizing vs denormalizing tables, etc but I don't have practical experience with that. I dealt with very large data sets but in a non SQL context and this is my first time here, so I hope the question provides the info needed :D

I'm not the one who downvoted your question, so I can't speak on behalf of that person, but my guess is because your question is a little abstract and wordy, and this site is usually for more direct and concretely worded questions, that was the downvoter's reasoning, because it's a little unclear exactly what your goals are / what you're asking. That being said, I agree it is annoying to be downvoted without explanation, so conversely you have my upvote, because your question is fair enough.
Anyways, what I can say is it sounds like the size of your data is of no concern here for PostgreSQL. A quick calculation shows that at the rate of new data you mentioned, you'd still be under half a billion records in a 10 year timespan. I wouldn't be even concerned about caching so much because your object is rather small (ergo a single row in the table isn't very wide and will be small).
If your Token object and TokenLiquidity object have a one-to-one relationship then it doesn't realistically matter too much if you normalize them into two tables or denormalized them into one, because you wouldn't have data redundancy, and the row / table wouldn't grow much in total size by combining them into one table, so there's no concern there either.
If the relationship from Token is one to many with TokenLiquidity then that's a different story because you'll end up repeating properties of the Token across multiple rows of the TokenLiquidity table, which can make management more difficult should one of those properties' values change.
The only thing I'd personal recommend, because it just makes sense structurally, is having a third separate table dedicated to storing the history of the LiquidityToken table and furthermore would specifically just store the Token0Amount, Token1Amount and Address (and maybe a DateTime based field to log the when) fields to minimize data redundancy / maximize normalization. This will ensure your LiquidityToken table is as lightweight as possible, and ergo making the joins to the Token table as efficient as possible too. The history table would only be joined to as needed, and if you had the DateTime field, for only as far back as needed.


Re-subscribing to a merge publication after edits made offline Hypothetical Question - To help scenario planning.

Let's say I have a 2008 R2 Merge Publication with 5 active subscribers.  The publication is deleted and all subscriptions are removed.  A week or so goes by and data is continually added or updated on the local databases that used to be subscribers.

A week later a new publication is created on the original database and the 5 local boxes are all given new subscriptions.  What happens to the data that was added locally during the week no subscription existed?

A: Is it uploaded to the publisher once the new subscription is created?
B: Is the data from the week offline deleted?
C: Is the data from the week offline ignored by the publisher (and hence other subscribers)?
D: Something else happens

If C?  Is there a way to mark those records for replication so they can be brought up to the publisher, and ultimately the other subscribers?

None of the above.

If the 5 new subscriptions are initialized with a snapshot then by default all subscription database objects will be dropped and recreated from the snapshot bcp files.  So the data from the week offline will appear to be deleted.

  If C? Is there a way to mark those records for replication so they can
  be brought up to the publisher, and ultimately the other subscribers?

With Merge there is an option to upload pending changes before reinitialization but since your subscriptions have been removed you won't have this option.

The way to handle this is to manually sync the databases using tablediff utility or Red Gate's SQL Data Compare.  Then recreate the publication from the manually merged database - then subscriptions.


How to specify column positions in insert statements generated by pg_dump? I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases:

DB1.schema1.table1 has these columns:

name_pk | date | type

while DB2.schema1.table1 has these:

name_pk | date | value | type

I need to add value in the first database so that I can dump its data and restore it to DB3 (along with DB2 data, DB3 has the same columns than DB2).

What would be the best way to solve this? I thought:

Just add the "value" column to DB1 before dumping the data. However, because PostgreSQL doesn't have INSERT ... AFTER clause, the column gets added to the end, which is not good.
Dumping the table with schema-only into schema.sql, editing the resulting file manually and adding the missing column to the right spot. It does the trick, but it requires manual work and not suitable for my script going through multiple databases.
Adding column to the end, then adding the rest columns after that and deleting the ones before it. This requires finding out the names and types of the rest of the columns.
Is there any other way of doing this easily?

If you're using pg_dump you can use the --attribute-inserts flag when dumping the data - it adds column lists to the generated INSERT statements:

postgres=# create table test ( col1 integer, col2 integer, col4 integer, col3 integer ) ;
CREATE TABLE
postgres=# insert into test values(1,2,4,3);
INSERT 0 1
postgres=# \q

postgres@ubuntu-xenial:~$ pg_dump --attribute-inserts postgres
--
-- PostgreSQL database dump
--
-- * OUTPUT SNIPPED *
--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.test (col1, col2, col4, col3) VALUES (1, 2, 4, 3);

By doing this the order of columns does not matter.


Fast reads without paying the price on writes We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, search and aggregate these transactions, fast reads would be very nice.

We want to guarantee very fast writes, and adding indexes to cover some of the reads would slow down writes.

The good news is that we can afford stale data. I was considering to create a copy of the data to a read-optimized table (with indexes) every n minutes, which also allows for a periodical bulk-copy (thus limiting the number of ops?)

I'm looking for an opinion on whether this is a valid strategy. If you consider this a decent strategy, do you have implementation options pointers? If not, what are the alternatives?

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance of a clustered columnstore is quite staggering due to the inherent advantages of columnar storage and batch processing. But even more so on time series (which your data probably is), due to very likely segment elimination.

On SQL Server 2016 there are some specific improvements targeting your case, see Real-Time Operational Analytics Using In-Memory Technology and Speeding up Business Analytics Using In-Memory Technology.

For SQL Server 2012 and earlier my recommendation is to upgrade to 2014 or 2016.

In any case, I would shy away from Transactional Replication, for two reasons:

it forces full logged INSERT for bulk operations (=very dramatic write perf impact)
I wouldn't want to be the admin responsible for the replication agents on a 1000 writes/min env.

Also need to consider that log shipping or AlwaysOn readable secondaries can only offload the processing of querying, but not the schema requirements (ie. indexes). Any index required by querying on the replica would have to be created on the original DB, and the price is paid at write time.

Of course I assume you did due diligence and your writes are optimized now, ie. all the advice in The Data Loading Performance Guide is applied and your upload is bulk and minimally logged.


Is having all entity tables inherit from one main entity table a bad idea? My co-worker is designing a schema where most entities (like contacts, quotes, tasks) inherit from an "entity" table, which keeps track of things like which tenant owns the entity, and chagnes to the entity. This is similar to VTiger CRM's schema.

Is this a terrible idea? What should I tell him the problems are?

Is there a name for this pattern?

ENTITY
id PK
tenant FK TENANT
version
draft
deleted

ENTITYCHANGE
entityId PK FK ENTITY
version PK FK ENTITY
createdBy
createdAt
fieldName
fromValue
toValue

CONTACT
id PK FK ENTITY
name
[other contact fields]

QUOTE
id PK FK ENTITY
contactId FK CONTACT
[other quote fields]

Yes, there are some issues with this design.  The pattern is Entity-Attribute-Value at least for the EntityChange table.  See the link for all the reasons why this almost always should be avoided.

The Entity table itself may not be EAV, but it does nothing but increase complexity without adding any value.  To know if a Contact has another tenant, another version, another draft, or is logically deleted you have to join.  Even if none of these are applicable to the Contact table, your design dictates their existence and accommodation.

If you decide that Contacts can be logically deleted then add a deleted column to the contact table.  If you decide you need different drafts of Contacts, then add a drafts column, etc.  Essentially, there is no advantage to moving these attributes to a common table.  It increases storage requirements, reduces performance more than it helps, and make the system more complex.


Replication To Enterprise & Standard Edition subscribers We have a defined and well running transactional replication process.
One publisher, one distributor (on separate servers) and several subscribers (pull). All servers have Enterprise Edition.
Latency checked using a canary table. I use a canary table on all subscribers instead of tracer tokens. Don't see any additional latency. There is a great article by Kendra Little showing that canary tables are very good for this measure, since tracer tokens are too patient.
The replication is not for the whole database, only several tables, but the tables are relatively big and there are a lot of inserts.
I added one more subscriber using Standard Edition. It runs well and has no issues except it shows more Latency issues, not huge but it is alerting much more than all other subscribers.
Questions
How can I handle this issue? Is it possible to improve? What should I check?
If the server is outperformed on inserts, what should I do to improve it?
Information added in response to comments
The Standard Edition server itself is the same as the other subscribers. All servers are created by DevOps using the same configurations. We have 12 CPU cores on all the subscribers. The Standard Edition subscriber usually performs the same as all other subscribers (Enterprise). Only some times it starts to show increasing delay (30min-1hr) and it takes more than hour to recover.
PLE is 55146 (on Enterprise subscriber it is 178775). The main wait stats on the Standard subscriber are:

CXPACKET
PAGEIOLATCH_EX
PAGEIOLATCH_SH
PREEMPTIVE_OS_AUTHENTICATIONOPS
IO_COMPLETION
LOGBUFFER

The latency is between the distributor and the new subscriber. It usually serves ok and shows no latency as for all other subscribers. But once or twice a day it show latency that at its peak gets to 30min-1hr. I find that it happens when there are thousands of updates coming.
The tables are not partitioned. Everything in replicated tables suits Enterprise & Standard.

Take a 1-2GB file and copy it from Dist to a 'good' subscriber. Note speed/sec and elapsed time. Then do the same to the 'latent/new' subscriber. If much difference you have a hardware/storage/bandwidth issue. Failing that, maybe subscriber db settings are off...small autogrows, etc


Linking SQL servers error When i try to connect a linked server, i´ll get this error message :

TITLE: Microsoft SQL Server Management Studio

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Named Pipes Provider: Could not open a connection to SQL Server [5].
OLE DB provider "SQLNCLI10" for linked server "10.237.69.11" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "10.237.69.11" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 5)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476

I have tried resolve by steps described in here :

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1121ae61-1224-406a-8b8a-49f236df3edf/cannot-setup-a-linked-server-in-sql-server-get-this-connection-error-message?forum=sqlexpress

But still no change,
I have set TCP/IP protocol enabled in SQL conf. manager for both servers.
I have tried to ping first PC from the second and it worked.
I have set the Incoming TCP rules for 1433 port in firewall for both servers.
I have  set the incoming UDP rules for 1434 port in firewall for both  servers.
( I did not set the same rules for Outcome )   -- could it be the problem ?

Remote connections on both servers are enabled,

I want to connect MS SQL 2008 with MS SQL 2014 could it be problem with different versions ?

Your error here is

  server not found

This is because you used @srvproduct=N'SQL Server', in this case you should use SQL Server's pc name in case of default instance, i.e. this connection string would be correct in case of default instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name', @srvproduct=N'SQL Server'

or use this one in case of named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_pc_name\inst_name', @srvproduct=N'SQL Server'

If you want to use any other name, even just IP (or IP,port in case of named instance, use @provider=N'SQLNCLI':

for default instance

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11'

for named instance:

EXEC master.dbo.sp_addlinkedserver @server = N'my_linked_server_name_as_I_want_it', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'10.237.69.11,your_port'

If you want to use GUI (SSMS), or change server name to your pc name, or use another radio button position, for ex. SQL Native Client (other than SQL Server)


Query to combine each duplicate found entry Hey all I am wanting to create a query so that I can combine each of the found duplicates into one entry.

An example of this is:

 Name        | ID    | Tag   | Address           |carNum
 -------------------------------------------------------
 Bob Barker  |2054   |52377  |235 Some road      |9874
 Bill Gates  |5630   |69471  |014 Washington Rd. |3700
 Bob Barker  |2054   |97011  |235 Some road      |9874
 Bob Barker  |2054   |40019  |235 Some road      |9874
 Steve Jobs  |8501   |73051  |100 Infinity St.   |4901
 John Doe    |7149   |86740  |7105 Bull Rd.      |9282
 Bill Gates  |5630   |55970  |014 Washington Rd. |3700
 Tim Boons   |6370   |60701  |852 Mnt. Creek Rd. |7059

In the example above, Bob Barker and Bill Gates are both in the database more than once so I would like the output to be the following:

 Bob Barker|2054|52377/97011/40019|235 Some road     |9874
 Bill Gates|5630|69471/55970      |014 Washington Rd.|3700
 Steve Jobs|8501|73051            |100 Infinity St.  |4901
 John Doe  |7149|86740            |7105 Bull Rd.     |9282
 Tim Boons |6370|60701            |852 Mnt. Creek Rd.|7059

Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.

I am hoping a SQL query guru would have a query to do this for me!

Code from another forum

  SELECT [USER],
    STUFF(b.[licenseDup],1,1,'')                                            AS licenseDup,
    STUFF(c.[yearDup],1,1,'')                                               AS yearDup,
    STUFF(d.[makeDup],1,1,'')                                               AS makeDup,
    STUFF(e.[modelDup],1,1,'')                                              AS modelDup,
    STUFF(f.[colorDup],1,1,'')                                              AS colorDup
  FROM
    (SELECT DISTINCT a.*            FROM [FEBCardb].[dbo].[sheet1$]         AS a)           AS a
    CROSS APPLY
    (SELECT '/' + b.[License]       FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) b(licenseDup)
    CROSS APPLY
    (SELECT '/' + b.[Year]          FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) c(yearDup)
    CROSS APPLY
    (SELECT '/' + b.[Vehicle Make]  FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) d(makeDup)
    CROSS APPLY
    (SELECT '/' + b.[Vehicle Model] FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) e(modelDup)
    CROSS APPLY
    (SELECT '/' + b.[Color Name]    FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) f(colorDup);

This produces the following (what I see from that query above):

USER   |licenseDup     |yearDup   |makeDup       |modelDup         |colorDup
------------------------------------------------------------------------------
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
S89542 |XXXXXX         |2010      |Dodge         |Viper            |Black

And this is what I am looking to see:

USER   |licenseDup     |yearDup   |makeDup       |modelDup         |colorDup
------------------------------------------------------------------------------
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
S89542 |XXXXXX         |2010      |Dodge         |Viper            |Black

Since performance is obviously not going to be a high priority here (all this XML PATH concatenation is quite expensive), and since the only real problem with the output is that you have duplicates, try changing this:

SELECT [USER],
    STUFF(b.[licenseDup],1,1,'')    ...
  FROM
    (SELECT DISTINCT a.* FROM [FEBCardb].[dbo].[sheet1$] ...

To this:

SELECT DISTINCT [USER],
    STUFF(b.[licenseDup],1,1,'')    ...
  FROM
    (SELECT a.* FROM [FEBCardb].[dbo].[sheet1$] ...


Error: "The certificate cannot be dropped because one or more entities are either signed or encrypted using it." I have a certificate I would like to remove from the database.
If I issue the command
DROP CERTIFICATE <FooCert>

I get the error
The certificate cannot be dropped because one or more entities are either signed or encrypted using it

According to Jason Strate, I should be able to find out what's signed by the certificate.
The following query returns 0 rows:
SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c
INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE'
AND c.name = 'FooCert'

I've also tried decoupling the entities per this SO question.
https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv
How can I remove the dependencies on this cert so I can remove it?

To find items associated with Certificates and Asymmetric Keys, first try the query posted in this DBA.SE Answer:

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

If that does not return any objects, next try the following queries which look for:

Logins
Users
Service Broker Endpoints
Database Mirroring Endpoints
Symmetric Keys
Database Encryption Keys (used for TDE)

Please note that Logins are Server/Instance-level while everything else is Database-level. Also, Database Encryption Keys, while being at the Database-level, are reported in a DMV that returns data for all databases, and so does not change based on the "current" Database.

-- Server / Instance Logins (results not sensitive to local / current Database)
;WITH certs_n_keys AS
(
  SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
         crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
         crts.thumbprint
  FROM   [master].sys.certificates crts
  UNION ALL
  SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
         [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
         asym.[sid], asym.thumbprint
  FROM   [master].sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
       sp.[name] AS [PrincipalName], sp.principal_id, sp.type_desc,
       sp.create_date, sp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.server_principals sp
        ON sp.[sid] = cnk.[sid];

-- Database Users
;WITH certs_n_keys AS
(
  SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
         crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
         crts.thumbprint
  FROM   sys.certificates crts
  UNION ALL
  SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
         [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
         asym.[sid], asym.thumbprint
  FROM   sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
       dp.[name] AS [PrincipalName], dp.principal_id, dp.type_desc,
       dp.create_date, dp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.database_principals dp
        ON dp.[sid] = cnk.[sid];

-- Service Broker Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       endpts.*
FROM   sys.certificates crts
INNER JOIN sys.service_broker_endpoints endpts
        ON endpts.certificate_id = crts.certificate_id;

-- Database Mirroring Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       endpts.*
FROM   sys.certificates crts
INNER JOIN sys.database_mirroring_endpoints endpts
        ON endpts.certificate_id = crts.certificate_id;

-- Symmetric Keys (scroll results to the right to see Key name)
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       ncrptns.*, '---' AS [---], symkys.*
FROM   sys.certificates crts
INNER JOIN sys.key_encryptions ncrptns
        ON ncrptns.[thumbprint] = crts.[thumbprint]
INNER JOIN sys.symmetric_keys symkys
        ON symkys.[symmetric_key_id] = ncrptns.[key_id];

-- Database Encryption Keys (for TDE; results not sensitive to local / current Database)
SELECT crts.name, crts.certificate_id, crts.principal_id,
       crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
       DB_NAME(dbkeys.[database_id]) AS [DatabaseName], dbkeys.*
FROM   [master].sys.certificates crts
INNER JOIN sys.dm_database_encryption_keys dbkeys
        ON dbkeys.[encryptor_thumbprint] = crts.[thumbprint];


Set based query to group data ordered data I have a table of user action data and I would like to try and group it into 'sessions', so I could figure out how many times a user has logged on, how long they stay online, and how many actions they do during the time...

Full sql fiddle here: http://sqlfiddle.com/#!6/e24a50/4

Basically I have a table defined below, and I would like to group the actions when there is less than 20minutes of activity between two requests

CREATE TABLE WebsiteUsage (
      Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
      UserId INT NOT NULL,
      ApplicationId VARCHAR(MAX) NOT NULL,
      TimeStamp DATETIME NOT NULL
    )

Ideally, I would get a resultset that resembled this:

UserId      Session     StartTime               EndTime                 ActionCount
----------- ----------- ----------------------- ----------------------- -----------
1           1           2015-04-15T10:28:03.887 2015-04-15T10:38:18.037 58
1           2           2015-04-15T14:40:26.277 2015-04-15T14:56:02.857 13

I've got the time between requests using the following query, but I cannot figure out how to group them based on when MinutesSinceLastRequest < 20

WITH cte AS (
SELECT *,
    PreviousTimestamp = LAG([Timestamp], 1, NULL) OVER (PARTITION BY UserId, ApplicationId ORDER BY [Timestamp])
FROM dbo.WebsiteUsage
)
SELECT
    *,
    MinutesSinceLastRequest = DATEDIFF(MINUTE, PreviousTimestamp, [Timestamp])
FROM cte

Is there a way to accomplish this in a set based operation or do I have to fall back to the old cursor?

Start by making a column like CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20

Then wrap that in a rolling sum - this will change every time there's a 1 for IsOver20: SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum

So now you can group by this easily. Include PARTITION BY UserId if you need to break it down by UserId too.


Transfer Data between MySQL and MongoDB Databases My latest IT project has me working with MongoDB for the first time.  While I have a bit (very little) experience with NoSQL, I have always mostly worked with MySQL using Workbench.

I need to migrate the data from a MySQL database into MongoDB as my employer is switching over from the former to the latter.

Can someone suggest what might be the easiest way to do that?

Thanks a bundle!

This will probably get closed, but last time I was working with MongoDB heavily (1-2 years ago) nothing beat RoboMongo. It was free before and is open sourced now as well, which is pretty neat.


How to increase sql server performance? denormalization (making cascade delete off) vs making duplicate data I have a database with following related table ( sql server enterptise edtion 2014)

each state have many citites which each city has may municpalArea and so on

   State => Cities = > MuniciplArea => CallCenter => CallCenterCode => **LandLine**

Then I have a LandLine table which is required to be searchable by the value from the above tables e.g

   select from landline where stateId =1

The main requirements is performance

The two solutions that I have in mind are:

using relationship in landline table and making cascade delete off because of circular reference so i would have something like :

    landline (table)
        MunicipalAreaId  , CallCenterId , StateId,CityId, CallCenterCodeId

the other solution is cutting the relationship between tables so my landline table is like

    landline (table)
        MunicipalCode  , CallCenterCode , StateCode ,CityCode , CallCenterCode

both of these solutions are provided to avoid join in order of better performance.

maybe joining these tables doesn't have that much of impact on performance that I'm afraid of so how should I design tables?

should i go with either of the above solutions? ( which one)

or should i go with joining tables ? ( is performance good enough? )

is there any alternative solutions?

Update:
e.g Joins that are needed to select landlines from the same state

   SELECT *   FROM     LandLines AS [Extent1]
      INNER JOIN  CallCenterCode  AS [Extent2] ON [Extent1].[SabetCallCenterCodeId] = [Extent2].[CallCenterCodeId]

      INNER JOIN [dbo].[CallCenter] AS [Extent3] ON [Extent2].[CallCenterId] = [Extent3].CallCenterId]

      LEFT OUTER JOIN [dbo].[MunicipalArea] AS [Extent4] ON [Extent3].[MunicipalAreaId] = [Extent4].[MunicipalAreaId]

      INNER JOIN [dbo].[City] AS [Extent5] ON [Extent4].[CityId] = [Extent5].[Id] WHERE 63 = [Extent5].[StateId]

How big is this database? How many rows are in each table? Etc?

I would say that normalized data is default state to try to obtain. It is a leaner database, rows are shorter, and indexes may be used more effectively.  The short, leaner rows therefore lead to a smaller, leaner database.

One of the major accelerators of performance is memory.  If you can get your 5 tables to remain cached in memory, that will be a performance accelerator for your queries since you will avoid much of the disk I/O overhead.

You identify that you are joining with IDs (which are usually integers), so your indexes may be narrow and offer relatively inexpensive joins.

If you decide to denormalize, your tables will be bigger because they are carrying more redundant data on every row.  This causes a need for more memory to keep the data in cache and will require even more I/O when the cache is insufficient to buffer the data. (And your backups are bigger.)

In addition, you have taken on the task to denormalize and to maintain the denormalized data. This is an extra load of programming and on the server as well: consuming memory, I/O, and CPU.

But sometimes denormalization is the best choice.  Data Warehouses, for example, are largely denormalized data.  Also, you may find that in your system the benefits of denormalization may exceed the cost.

Still, you are asking a forum for an answer.

Your best answer would come by building normalized test case and seeing how it works.  Even though you may not have a lot of 'real data', you should generate a fairly large data set in the millions of rows to test with.

You can try to find a tool that does it for you (RedGate has one for example, but it is not free) or generate the data yourself so that you control the complexity.  There are online sources of states, cities, et cetera, and you can make up call centers, generate landline numbers and so forth.

Then try it.

If you do not like the performance, then create a denormalized table to test.  And put some effort into writing the code to maintain the denormalization, since that will become integral to your process.


Having trouble designing my preferred backup scheme First some background:

I have two Windows servers running MS-SQL-2014 setup in an availability group (synchronous auto-failover, preferred backup on secondary). They host many databases.

Right now we only need weekly backups (copy only-full backups). We don't need transaction log backups but we have to do them (and then immediately delete them) because the databases have to be in full recovery mode for the availability group, so we burn all our storage up if we don't. I use a maintenance plan (copied on both servers) to run these backups.

In the maintenance plan, I backup the dbs, then the transaction logs, then delete older then 6 months dbs, and then delete ALL transaction logs.

My Questions are these:

How can I keep a smaller interval of backup longer, say keep 1 db backup every 6 months after the initial 6 months, but delete the others.
Is there a better way of handling the transaction log issue, then making them and then deleting them. Feels like I'm burning hard drives cycles for nothing.

First of all grab Ola Hallengren's scripts to do the backups (http://ola.hallengren.com) and drop the maintainance plans. Ola's scripts have much better retention settings and will allow you to set a mark in hours how long to store the backups and know how to take the backups of the secondaries.

And for your questions:

Set up a small script in SQL Server agent or Windows task scheduler that copies a backup file to a secondary folder where you keep it longer than the others or do a mirrored backup to a secondary folder every six months. A small powershell script or xcopy command that runs every six months and copies the last backup file will do just fine
Backup log DatabaseName to disk ='NUL' will make a log  backup to the windows equivalent of /dev/null, no disk writes or space wasted. Nogood to restore from though.


How to add a volume to current in use MySQL database related to the problem I faced here No space left on device, I want to adopt a long term solution about the point no space left on device. One of the action includes to add a volume to the machine in which the database runs (MySQL 8, Ubuntu 20.04 and Digital Ocean provider).
I would like to know which one is safest way and rules to follow in order to avoid loosing reference and/or data when I will add a volume on the machine.
Considering that this database is running in production without any replication.
Thank you in advance for the support

I followed this guide, very useful and complete
https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04


How is this called in a snowflake schema? I'm learning the basics of DWH modeling and I didn't find a name to refer to what I colored in red in the image below, i.e. a group of tables related to each other, with one of them being related to the fact table?

Does this have a name? (e.g. relationship branch?)

In Kimball terminology it would be a ﬁxed depth hierarchy. Using Inmon I think just a normalised dimension.


Does MySql event will start if the previous event doesn't finish yet? Example I have an event that will run every 5 seconds, then the first instance of the event is triggered but more than 5 seconds later the instances still not done executing. What will happen to the event would it execute again after 5 seconds even the first event is not finished yet or it will wait for the first instance of the event to finish before executing?

Thanks

To quote the manual:

  If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.

In other words, the new copy will run.

This could lead to more and more running, so you should worry about it.