Database of the website "eMovies" Here is my exercise:

  Given database of the website "eMovies" that manage data about cinema movies. build the given database in Access and create the relationships between the tables.

My attempt:

My questions:

I am not sure at all about the relationships that I made between the tables at all.
I am not sure about the keys

EDIT: After asking my doctor the table and the keys are correct

Begin with the question, what is your requirement? based on your question, you need to develope a relational database for managing movies data.

Before starting to build a database, you have to ask a lot of questions to understand about how the movies business work within the requirement scope. Lets start with the question,

Who is important to your movies business?

Based on the question, ACTOR, DIRECTOR.

What is important to movies business?

GENRES, MOVIE.

Now, the next question will be what is the definition of ACTOR, DIRECTOR, GENRES, MOVIES?

For example, An ACTOR is a human being, who acts in atleast one movie. Note that you should have a clear definition of entity (business concept) or else it will lead to multiple interpretation. (see here).

After understanding the business concepts, let us go for business rules. Some simple questions (based on your question).

Can an Actor act in more than one Film?
Can a Movie coontains more than one Actor?
Can a Movie directed by more than one Director?
Can a Movie categorized as more than one Genre?

Example business rule from your question.

Each Actor can act in one or many Actor Movie.
Each Actor Movie must be acted by one Actor.
Each Movie can contain one or many Actor Movie.
Each Actor Movie must belongs to one Movie.
Each Directos can direct one or many Director Movie.
Each Director Movie must be directed by one Director.
Each Movie can contain one or many Director Movie.
Each Director Movie must belongs to one Movie.
Each Genres can contain one or many Genres Movie.
Each Genres Movie must belongs to one Genres.
Each Movie can contain one or many Genre Movie.
Each Genre Movie must belongs to one Movie.

By applying these business rules, our new model may look like as follows:

This model is not normalized, after applying normalization (see here) process, you will have clear and detailed data model for your application.

I hope this model will help you to begin your assignment. Thank you.


Hosting ASP.Net session database on same same instance as the user database? We have a web farm using SQL Server sessions.  We are seeing occasional timeouts connecting to the session DB even though overall utilization is low.  The ASPState session database is on the same instance as the application database.  I suspect this is not a good idea because the user database queries are closely correlated with the session database queries.  What do you think?

If this is a bad idea, would it help to move it to another SQL Server instance on the same machine? Or is it best practice to run the session DB on another machine?

Personally, in addition to having an extra machine for the session DB, I would consider

moving the session database files onto different disk arrays (not partitions on the same volume)
adding more RAM in case it is read load, not write load causing the problem

The first option includes moving your user databases and tempdb to different disk arrays too. That is, one file per array basically.

I wouldn't think about either of these because you don't add any new resources:

another SQL Server instance on the same machine
using virtualisation for my SQL Servers


Custom Collation on MySQL Unknown I've created a new collation as per the instructions mysql. In fact this tutorial was the exact use-case I needed. I've added the collation, and restarted mysql. When I try to run

ALTER TABLE `my_table` CHANGE COLUMN `my_column` `my_column` VARCHAR(32) CHARACTER SET 'latin1' COLLATE 'latin1_fulltext_ci' NOT NULL DEFAULT '' ;`

I get the error Error Code: 1273. Unknown collation: 'latin1_fulltext_ci'. When I run SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME = 'latin1_fulltext_ci'; I get:

+--------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+----+------------+-------------+---------+
| latin1_fulltext_ci | latin1             | 62 |            |             |       0 |
+--------------------+--------------------+----+------------+-------------+---------+

So, I know MySQL is finding it. I've run repair, and check for upgrade on the tables and tried creating new tables, but I keep getting the same error Error Code: 1273. Unknown collation: 'latin1_fulltext_ci'.

Does anyone know what could be causing this?

Version details

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.73-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

I had an extra space in my collation in the latin1.xml file. Removing that, fixed it!


How to send only updated values of a row? CREATE OR REPLACE FUNCTION notify_col()
  RETURNS trigger AS
$BODY$
    BEGIN
        PERFORM pg_notify('channel', row_to_json(NEW)::text);
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
COST 100;

I'm trying to listen on channel, for any updates in the row (transformed with row_to_json()). But I want pg_notify() to send only updated values of the row, not the whole row.
Do I need an if condition or is there is a better way to do this?

This sends a JSON array of columns that actually changed, with their new value:

CREATE OR REPLACE FUNCTION notify_col()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM pg_notify('channel'
                   , (SELECT json_agg(n)::text
                      FROM   json_each_text(to_json(OLD)) o
                      JOIN   json_each_text(to_json(NEW)) n USING (key)
                      WHERE  n.value IS DISTINCT FROM o.value));

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER tbl_upd_aft
BEFRE UPDATE ON tbl
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)  -- optional
EXECUTE procedure notify_col();

db<>fiddle here

If works by converting OLD and NEW to JSON and expanding each to a set of key/value pairs. Then join on the key and only keep columns (now rows) that actually changed. Finally aggregate into a JSON array.

Note the WHEN condition to the trigger. Updates that do not actually change anything don't fire the trigger. (So the trigger is guaranteed to process actual changes.)

If there can be more triggers consider an AFTER trigger to only report what's actually changed. And reconsider the WHEN condition if triggers might add changes - which would circumvent this condition.

Related:

Is it possible to dynamically loop through a table's columns?
Trigger with multiple WHEN conditions
How do I (or can I) SELECT DISTINCT on multiple columns? (about empty updates)

Aside: consider this note in the manual:

  array_to_json and row_to_json have the same behavior as to_json except for offering a pretty-printing option.


Delete query uses 100% CPU with in Azure SQL Database I have a relatively small database, maybe a few hundred thousand rows, and we have a stored procedure that is meant to clear out a 'Dealer' (the application aggregate root) and all of it's related data.  The database lives on a v12 Azure SQL instance, and when it hits one particular table, it consumes 100% of the instance's DTU/CPU and takes a really long time to finish.

I believe the issue is that the table has a ton of foreign keys, probably about 30.  Looking at the execution plan you can see that it's doing a ton Nested Loop with index scans to find dependent rows.  All of these rows were already delete prior to the attempt to delete the rows from this particular table, so these loops are actually pointless.

My question is, would it be more efficient to disable the foreign key constraints on these dependent tables, do the delete, and then re-enable to constraints?  Would there be some negative effect to doing that, aside from the fact the it's technically possible that some bad data gets inserted while the constraints are disabled.  Or is there an even better way to accomplish what I want to accomplish?

UPDATES
This is a v12 Azure SQL database, it is not full blown SQL Server 2012 in a VM.  I am not batching any deletes, and the offending statement is part of a larger SPROC.  I identified this particular table delete by tearing the sproc apart and running bits and pieces of it separately.  When it got to this table, it took literally 15 minutes to complete.  It will affect on the order of hundreds of rows, varying depending on the state of the particular 'Dealer' we're clearing out.

We cannot do soft deletes in this case because usually we're clearing this dealer out so it can be replaced (soft deletes would cause duplication and would require a lot of code to work around).  I have investigated other types of blocking and wait states and found nothing else that seemed to be negatively affecting this.  As I mentioned this database is relatively small, and it's mostly used for very simple CRUD operations.

Here's is the execution plan, it's huge.

The XML version of the execution plan can be found here: https://gist.github.com/CodingGorilla/6cf7a87df9257d5f93e0d545af9839c2

As you note the plan shows many index scans[1], a couple of the indexes multiple times such as Facilities.IX_CustomerID. That will be why is it spinning the CPU resource: those indexes will be in the buffer pool so little IO is needed and the CPUs are being made to chew them, probably in their entirety, repeatedly.

If this is from a simple DELETE statement and all the other activity is the engine enforcing foreign key constraints then I would check for foreign keys without supporting indexes. For instance one of the scans of [Facilities]" Index="[IX_CustomerID] has the search predicate [DataMateWeb].[dbo].[Facilities].[SurfaceCleanerID]=[DataMateWeb].[dbo].[Chemical].[ChemicalID],  which suggests an index covering [Facilities].[SurfaceCleanerID] is not present or for some reason can not be used. When you define a foreign key an index is not automatically created to go with it which can surprise people and that would explain a scan of the table/cluster rather than multiple seeks on an index. In any cases where those indexes are the supporting indexes for the foreign keys, make sure that they are correctly defined and don't have badly out-of-date statistics.

If the delete operation isn't that simple then make sure any joins and sub-queries used in deciding which rows will be deleted are supported by appropriate indexes and that the joining and filtering predicates are arranged such that they are sargable where ever possible.

Failing the above (i.e. all the FKs and any other filtering & joining activity all look fine with regard to having indexes they could potentially seek on), the next straw I'd clutch for is: if you are deleting many rows at once (DELETE WHERE IN (<a result that could be a long list>)) then I've seen that make SQL Server scan the index instead of using multiple seeks. This feels a bit nasty[2] but: check this by trying to confirm the behaviour with just one row and if that changes at least some of the scans to seeks (and improves performance as a result) consider cursoring through the rows that need to be removed and doing them one at a time.

[1] Which as they are clustered index scans are effectively table scans which for large tables means a lot of data is being touched.
[2] Because it is nasty. But if it is nasty and works, it still works!


Ring topology support of MongoDB ReplicaSet and Sharding? Just curious if the following ring-like topology supported/recommended or is right for a sharded/replicaset cluster.

version: mongodb 2.4.5

hosts(all vms)

#[MongoDB Config]

mongodb_roles="replset1 replset2 replset3 replset4 replset5 config route"

replset1_ips="192.168.1.27 192.168.1.28"
replset1_port=27011
replset1_shard="s1"

replset2_ips="192.168.1.28  192.168.1.29"
replset2_port=27012
replset2_shard="s2"

replset3_ips="192.168.1.29 192.168.1.30"
replset3_port=27013
replset3_shard="s3"

replset4_ips="192.168.1.30 192.168.1.18"
replset4_port=27014
replset4_shard="s4"

replset5_ips="192.168.1.18 192.168.1.27"
replset5_port=27015
replset5_shard="s5"

config_ips="192.168.1.15 192.168.1.16 192.168.1.17"
config_port=20000

route_ips="192.168.1.15 192.168.1.16 192.168.1.17"
route_port=30000

Thanks,

MongoDB (as at 3.0) only supports a single primary per replica set. Replica sets can have up to 50 members, with up to 7 voting members. The 2-node replica sets you have described should have a third member (either a data-bearing secondary node or a voting-only arbiter) to allow for failover. Replica sets require a strict majority of votes (n/2+1) in order to elect and maintain a primary, so a 2 node replica set requires both members to be healthy (see: Replica Set Elections).

Replication Topology

In terms of replication flow, the deployment of a replica set could be described as either:

A self-configuring tree topology, with the current primary as the root node. By default MongoDB allows replication chaining so secondary nodes can choose to sync from other secondaries based on ping time (for example, to mitigate bandwidth usage when multiple secondaries are in a remote data centre).
A self-configuring star (or hub-and-spoke) topology, if you disable replication chaining.

You can influence selection of the current primary by configuring member priorities for a replica set, but the general goal is to provision a set of peer nodes (with similar hardware resources such as CPU/RAM/disk) to allow any data-bearing secondary to be promoted to primary.

Sharded Cluster Query Topology

With a sharded cluster your application connects via the mongos routing service, which will direct queries & commands to the appropriate shard(s) and aggregate results as required. The networking topology here might be described as a partial mesh, as each mongos connects to the 3 config servers and the current primary for each shard (and potentially secondaries, based on read preferences).

Note: Your approach with the use of distinct ports for different roles (replica set membership, config server, mongos) makes sense as a way to self-document the sort of service you are connecting to.


Use GROUP_CONCAT() to generate polygons in mysql I have a table that has columns zone(name of the zone which is a string), longitude (decimal), latitude (decimal), logical order (integer).

logical_order indicates the order in which the coordinates can be combined to create the polygon. For example, each zone will have multiple rows in this table with different longitude, latitude. For each zone row, the logical_order will start at 1 (for starting point) and increase by one per row. For example, if a zone has 3 points, logical_order will run from 1 through 3. Coordinates at 3 are not the same as at 1. In other words, the coordinates do not close the polygon.

I'm trying to create POLYGON by grouping coordinates of each zone and using spatial package as below:

I tried the below query:

SELECT t0.zone, ST_GeometryFromText(CONCAT("POLYGON(", GROUP_CONCAT(t0.coordinate ORDER BY t0.logical_order SEPARATOR ','), ")")) FROM
(
    SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, logical_order FROM zones
    UNION
    SELECT zone, CONCAT(longitude, ' ', latitude) AS coordinate, COUNT(zone) + 1 AS logical_order FROM zones GROUP BY zone
) t0
GROUP BY t0.zone

However, this gives me the error:

Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext.

How can I fix this error? Am I approaching the right way?

EDIT:

If I try without the ST_GeometryFromText() in the second query, I get a string like:

POLYGON(77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800)

After adding two parenthesis like below still give the same error:

POLYGON((77.5068350000 -11.4907909800,179.7363280000 -11.4907909800,179.7363280000 -60.0000000000,77.5068350000 -60.0000000000,77.5068350000 -11.4907909800))

EDIT:

SQL fiddle with sample data: http://www.sqlfiddle.com/#!9/5094e5/5

The error is likely cause because the "cycle" isn't closed, i.e. last point in the polygon does not match the first one.

This can be from two issues:

the group_concat_max_len setting is too low (default is 1024).

Solution: increase it, either in the server or session level.

the error-prone GROUP BY used to add the additional point.

The GROUP BY you use is prone to give wrong results, as it may not always select the row you want (WHERE logical_order = 1) to read the coordinates:

(
    SELECT zone,
           CONCAT(longitude, ' ', latitude) AS coordinate,
           logical_order
    FROM zones
    UNION
    -- This is to close the polygon by adding the first coordinate also
    -- as the final coordinate of the zone.
    SELECT zone,
           CONCAT(longitude, ' ', latitude) AS coordinate,
           COUNT(zone) + 1 AS logical_order
    FROM zones
    GROUP BY zone
) t0

I'd write it like this:

(
    SELECT zone,
           CONCAT(longitude, ' ', latitude) AS coordinate,
           logical_order
    FROM zones
    UNION ALL
    -- This is to close the polygon by adding the first coordinate also
    -- as the final coordinate of the zone.
    SELECT zone,
           CONCAT(longitude, ' ', latitude),
           1000000     -- unlikely to have a million points polygon
    FROM zones
    WHERE logical_order = 1
) t0


Exporting data from Microsoft Access table file subsets to plain text I have a problem in exporting data from Microsoft Access. I have an Access file made of several rows and columns, like this:

Every row contains a subset of data, tha could be observed by clicking on the "+" button:

I would like to export all these data (main table & subsets), but there's a problem.
When I click on External Data -> Export -> Text file, Ms Access exports only the main table data (1st Figure), and completely omits the data of the subsets (2nd Figure).

How could I export all these data (main table & subsets)?

Thanks

Microsoft Access does not know how you want the files exported when you use the standard "export" text function.

Here is a trivial piece of code that you can add to a Module, and run from a Command Button on a form, or directly from the Immediate Window:

Public Sub ExportTables(ByVal DestinationPath As String)

    Dim a As Long

    For a = 0 To CurrentDb.TableDefs.Count - 1
        If Not (CurrentDb.TableDefs(a).Name Like "MSys*") Then
            DoCmd.TransferText AcTextTransferType.acExportDelim _
                , _
                , CurrentDb.TableDefs(a).Name, DestinationPath & "\" & CurrentDb.TableDefs(a).Name & ".csv" _
                , True
        End If
    Next a

End Sub

Add a Module to your Access database, and paste in the code above.  Press "CTRL-G", enter ExportTables "C:\temp", then press Enter - this will export all tables in the database into C:\TEMP in comma-separated-values format (.CSV) ready for import into Excel or other databases.


What's happening if one of concurent lock on a table rename the locked table? I've hundred of insert as following each minutes:

BEGIN ISOLATION LEVEL SERIALIZABLE;

  INSERT INTO table1 VALUES (...);

COMMIT;

And I've the following transaction which will occured once:

BEGIN ISOLATION LEVEL SERIALIZABLE;

  LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE;

  /* Rename the locked table */
  ALTER TABLE table1 RENAME TO table1_renamed;

  /* And I recreate the table */
  CREATE TABLE table1 (
    ...
  );

COMMIT;

The second transaction will blocked all INSERTs until it commits.

When it commits, will inserts be proceed on the new table1 or on the renamed one?

In other world are locks acquired on object's name (so it's resilient on rename operation) or on object's reference?

The objects id (oid) at the time the statement is prepared and executed. Pg_locks doesn't even store the relations name.

        View "pg_catalog.pg_locks"
       Column       |   Type   | Modifiers
--------------------+----------+-----------
 locktype           | text     |
 database           | oid      |
 relation           | oid      |
 page               | integer  |
 tuple              | smallint |
 virtualxid         | text     |
 transactionid      | xid      |
 classid            | oid      |
 objid              | oid      |
 objsubid           | smallint |
 virtualtransaction | text     |
 pid                | integer  |
 mode               | text     |
 granted            | boolean  |
 fastpath           | boolean  |


Stored procedures vs inline SQL in a Job step I have created a job with two steps:

Delete 50000 rows from an error table older than 90 days.
Create and email a quick report on the number of new records created in the error table that day, literally a:

SELECT count_big(*)
FROM table
WHERE datevalue < GETUTCDATE()
AND datevalue > DATEADD(DAY,-1,GETUTCDATE())

...but with some happy formatting to make an email table.

Currently I have step 2 running SQL in the job step rather than calling a stored procedure.

My question is, does it make a difference (performance or otherwise) in SQL Server 2014, to run a job step that calls a stored procedure or one that executes inline SQL?

does it make a difference (performance or otherwise) in SQL Server 2014, to run a job step that calls a stored procedure or one that executes inline sql?

In your case, since you are just doing reporting, it won't make much difference either using inline SQL or a stored procedure.

Aaron Bertrand provides a really good answer to a related question:

Stored procedures vs. inline SQL


mysql trigger on a federated table I have two MySQL(5.6) databases D1 and D2 on two different machines.
There is a table T1 on D1 and there are two tables T2A and T2B on D2.
I want to update T2B whenever T1 is updated.

T2A is a federated table and T1 is base table of it.
There is an "After Update" trigger on D2 which updates T2B whenever T2A is updated.

When there is an update on T2A, T2B table is updated by trigger.
When there is an update on T1, T2A table is updated by federation but the trigger to update T2B as well is not triggered.

So when there is an update on T1, T2B table is not updated automatically, only T2A is.
How to solve this?

Federated tables are not transactional by nature, so you don't want to put a trigger on it. Alternative would be to put a trigger to post to another local table.
See: Federated tables and triggers


Use LDAP for name look up with Oracle Instant Client Partly as a result of having problems with conflicting versions or bitness with the full installation of Oracle client, my development team (of which I'm a member) has been moving towards using Oracle Instant Client and avoiding the installation on our development machines for most new projects. This has worked out pretty well, even to the point that we deploy to production with this setup. Since we're mostly a .NET shop, we're using the native client (with .NET wrappers), rather than the Java one.

Now a client is requiring that we register the database service with an LDAP server and use the LDAP server for name look up. They have provided sqlnet.ora and ldap.ora files (and an accompanying encryption config file) that appear to be correct. In particular, the sqlnet.ora file does specify LDAP as a possible source for looking up net service names:

names.directory_path=(tnsnames,ldap)

I am confident that the machine I'm working with can use LDAP to access the service since I was able to do so using SQL Developer (which allowed me to input the LDAP information directly). Assuming they are correct, how can I get Instant Client to recognize this configuration and use LDAP for the name look up?

What I've tried so far

I have tried setting the TNS_ADMIN environment variable to the directory that contains the provided files, and while this works fine for making Instant Client recognize a tnsnames.ora file, it doesn't seem to be enough to get it to start looking at LDAP. Here are the results (with paths, usernames, and passwords changed):

(The following commands happen to be Windows Command Prompt, but I imagine something similar is doable on Linux. The sqlplus seen here is the Instant version as well, with accompanying Instant Client binaries in the same directory.)

C:\TEMP>SET TNS_ADMIN=C:\path\to\sqlnet\ldap\and\tnsnames\ora\files

C:\TEMP>echo %TNS_ADMIN%
C:\path\to\sqlnet\ldap\and\tnsnames\ora\files

C:\TEMP>sqlplus.exe USERNAME/PASSWORD@LOCALTNSNAME

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 7 10:22:25 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produ
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

C:\TEMP>sqlplus.exe USERNAME/PASSWORD@LDAPTNSNAME

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 7 10:24:21 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Side note

Are there tags for TNS, LDAP, or Instant Client (or even just normal Oracle Client) that could be added to this question? I couldn't locate any.

Place the sqlnet.ora and ldap.ora files in your $ORACLE_ADMIN directory and make sure to set the environment variables. I made mine the TNS_ADMIN directory

(Linux) my .bashrc

export TNS_ADMIN=/opt/oracle/instantclient_12_1/network/admin
export ORACLE_ADMIN=${TNS_ADMIN}

[ku14lts:/opt/oracle/instantclient_12_1/network/admin]
$ ls
ldap.ora  sqlnet.ora  tnsnames.ora

And I was able to connect to a DB not in my tnsnames.ora


no pg_hba.conf entry for host "[local]", user "pgadmin", database "postgres", SSL off on OSX local  all  postgres    trust
host  all  all  0.0.0.0/0  trust
hostnossl    all          all            0.0.0.0/0  trust

I would think either the second or the third would match these criteria.

I've restarted my service on brew with brew service restart postgres@9.6

Am I missing some setting here?

First, the meanings of the fields  are:

# local       DATABASE  USER  METHOD  [OPTIONS]
# host        DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# host[no]ssl DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

The attempt is:

host = “[local]” (meaning through a Unix domain socket)

user = “pgadmin”

database = “postgres”

SSL = off

Now your lines:

local  all  postgres    trust

does not match because the user pgadmin is not postgres

host  all  all  0.0.0.0/0  trust

does not match because host only matches TCP/IP connections and [local] is not a TCP/IP connection.

hostnossl    all          all            0.0.0.0/0  trust

does not match for the same reason than the previous line.

If you want to "trust" everyone locally through the Unix domain socket, you may change the first line to:

local  all  all    trust


ODBC Connections locking tables in SQL Server We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the completely impractical practice of allowing many users to extract data from this system at will for the purposes of manipulating the data into their preferred flavor of reporting (BI, Powerquery, Access, Excel, etc)

Though all of these report generating users only have select rights, there are several other applications that have the ability to actually manipulate data for the purposes of doing things like EDI, automated data entry.

Logically this creates a number of race conditions on the tables. First come first serve, or worse still, still serving, please wait, or just NO... I believe it is further causing what has been years of previously unexplained data corruption requiring vendor assistance to correct. Because not all of the applications are properly coded to anticipate the chance that something else may have interrupted its own private intent.

So the task is to prove/correct/mitigate it where possible on a system I only recently inherited.

My first instinct tells me it cannot be done, without some sort of IPC/error/transaction control between different vendor's products and their will to fix it, control over source code, or ability to restrict it, that this CANNOT be fixed from a DBA perspective, it is simply bad practices leading to what should be anticipated as potentially unwanted side effects.

So all I can do at this point is prove it, and see that if with proof, I can solicit some sort of behavioral change in the hearts of the management that insists it has to be this way.

First step, is that I am running a continuous profiler output to disk, albeit resource intensive, I have to be able to go back and look at what has been submitted, by whom, and what impact it had. I am also using perfmon logging and PAL to try and cross reference intensive behavior on the server, "when the processor and memory are pegged, what was the SQL server doing at that instance (most likely servicing one of the GUI written, poorly performing, ten table joining, non indexed queries, that some report writer/tool tossed together"

One of those situations just occurred, we had a user in the ERP system that could not perform a function, we identified the user logged into the database that was not using the ERP software (Was using ODBC, SQL user with select only access, and MS Access), had them disconnect and the function went through. Management refuses to believe they are related because they "Should have been dealing with different tables, and the user only had select access" and I have no profile history of the instant just before it happened.

So all of that boils down to a question to the DBA fellows out there, given the task of PROVING that application A is being adversely affected by application B, what suggestions would you have to offer?

Unfortunately, we've dealt with too may issues with this where I work. In cases where this has been a problem, people who don't know databases, literally open a SQL table "live" in MS Access. At first things can appear transient because they may open and close Access quickly. But then there are those moments where they leave MS Access open on the live SQL table while they go away for lunch or perhaps through the long weekend--blocking applications that try to use the database. Just to state the obvious--blocking can adversely affect any application trying to use the blocked SQL row, table, or database.

All we really had to do to prove anything was capture data from sp_who2 to find the blocking chains.

That being said, when I'm away from the console, I still use the following code I wrote over a decade ago (I mean I'm still using DBCC InputBuffer) to find these pesky issues -- if needed (please run in a test/dev environment to see if it meets your needs). Though certainly not the Rolls Royce of routines it still works for me.

This code essentially creates a table, BlockingProcesses, in a new database DBADMIN to store the blocking information. Two stored procedures sp__Maint_BlockWatch and sp__Maint_Blockingprocesses work together to find the blockers. And a SQL job runs once a minute to check for blocks. You may need to change minor things in the code below like the user who needs to execute the job, possible scheduling, etc.

CREATE DATABASE DBADMIN
GO

USE [dbadmin]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlockingProcesses]
(
    [PK] [int] IDENTITY(1,1) NOT NULL,
    [last_batch] [datetime] NULL,
    [spid] [int] NULL,
    [BlockedTotal] [int] NULL,
    [LoginName] [varchar](128) NULL,
    [DBName] [varchar](128) NULL,
    [HostName] [varchar](128) NULL,
    [Program_Name] [varchar](255) NULL,
    [CPU] [bigint] NULL,
    [Physical_IO] [bigint] NULL,
    [Memusage] [bigint] NULL,
    [Open_tran] [int] NULL,
    [EventInfo] [varchar](max) NULL,
    [InsertTime] [smalldatetime] NULL,
    [Params] [varchar](500) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlockingProcesses]
   ADD DEFAULT (getdate()) FOR [InsertTime]
GO

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp__Maint_BlockWatch]
AS
    declare @blocker smallint

    DECLARE blocker_cursor CURSOR FOR
         select distinct blocked
         from sysprocesses
         where blocked != 0

    OPEN blocker_cursor
    FETCH NEXT FROM blocker_cursor INTO @blocker

    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
      BEGIN
  FETCH NEXT FROM blocker_cursor INTO @blocker
           CONTINUE
      END

  exec sp__Maint_Blockingprocesses @blocker

 FETCH NEXT FROM blocker_cursor INTO @blocker
    END
DEALLOCATE blocker_cursor

GO

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp__Maint_BlockingProcesses]    Script Date: 09/20/2016 13:31:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp__Maint_BlockingProcesses]
@spid smallint
as
declare
@blockedTotal int,
@loginame varchar(128),
@dbid int,
@hostname varchar(128),
@program_name varchar(128),
@cpu bigint,
@physical_io bigint,
@memusage bigint,
@open_tran int,
@dbname varchar(128),
@EventInfo varchar(255),
@Last_batch datetime
create table #temp
(
 EventType varchar(50),
 Parameters int,
 EventInfo varchar(255)
)

insert into #temp
exec ('dbcc inputbuffer('+@spid+')')

select @EventInfo=eventinfo from #temp
select @blockedTotal= count(*) from sysprocesses where blocked = @spid
select @loginame=loginame, @dbid=dbid, @hostname=hostname, @program_name=program_name, @cpu=cpu,
@physical_io = physical_io, @memusage=[memusage], @open_tran=open_tran, @Last_batch=last_batch
from sysprocesses where spid=@spid
select @dbname = name from sysdatabases where dbid=@dbid
insert into dbadmin..blockingprocesses(last_batch,spid, BlockedTotal, LoginName, DBName, HostName, Program_Name, CPU, Physical_IO, Memusage, Open_tran, EventInfo)
values(@last_batch, @spid, @BlockedTotal, @LogiName, @DBName, @HostName, @Program_Name, @CPU, @Physical_IO, @Memusage, @Open_tran, @EventInfo)
drop table #temp

GO

USE [msdb]
GO

/****** Object:  Job [_Monitor Blocks]    Script Date: 09/20/2016 13:29:34 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2016 13:29:34 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'_Monitor Blocks',
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Look For Blocking]    Script Date: 09/20/2016 13:29:34 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Look For Blocking',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=1,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec sp__Maint_BlockWatch',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Run Once a Minute',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20060918,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959
        --, @schedule_uid=N'bf518f11-c5ba-438a-8afd-e4e33e8dad1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


SQL Server - DELETE from subquery/derived table Is there a way I can turn the below SELECT statement into a DELETE?

I would like to delete the corresponding returned records from the [ETL].[Stage_Claims] table.

Since I used derived tables I can't reference the Stage_Claims table.

To summarize, the 2 physical tables used in the below query have identical structures. Only difference is DUPS_Claims is a subset of Stage_Claims.

DUPS_Claims contains duplicate records found in Stage_Claims. If a record exists 3 times in Stage_Claims, we will have that record 3 times in DUPS_Claims as well.

Stage_Claims contains all records including the duplicate records in DUPS_Claims.

I would like to remove the duplicate records from Stage_Claims leaving only 1 unique record for every duplicated record.

Stage_Claims has just short of 1 million rows so I do not want to use Row_Number / Partition on the entire table as it takes over 2 minutes to run.

The below query I have runs in about 15 seconds and successfully identifies only the duplicate records  (not including the original unique record we want to keep) but I have not been able to figure out how to delete the records that are returned from SC.

Is it possible or should I just take a different approach?

SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1

Convert your select statement into a CTE, and DELETE FROM the CTE, as in:

;WITH del AS
(
SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1
)
DELETE FROM del;

Standard warning: You should test this in a non-production environment.

You can simplify your query quite a bit, and likely get better performance by using the below query, which does not make use of the intermediate table, DUPS_Claims, since it is absolutely unnecessary:

;WITH cte AS
(
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
)
DELETE
FROM cte
WHERE rn > 1;

I created a non-clustered, non-unique index on both tables, then looked at the execution plans for both variations.

The first variant:

The second variant:

The first variant scans the index twice, whereas the second variant clearly only needs to scan the index a single time, and doesn't require a relatively expensive merge join in my somewhat contrived example.  My sample ETL.Stage_Claims table contains 89 unique ID values, each duplicated 89 times, for a total of 7921 rows.

In case CTEs aren't your thing, you can use this approach to delete from a derived table, instead:

DELETE c
FROM (
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
) c
WHERE rn > 1;

The query plan for the above DELETE from the derived table:


Getting the part for string using sql server query I have rows like below

10470_AMK-4799__OFS_BROWSERTC
6038_DMUSER_DM.READ.REPLACEMENT
6043_DMUSER2__OFS_DM.OFS.SRC.VAL
10194_AMK-TC037__OFS_BROWSERTC
64948_AMK-TABLETUSER__OFS_TWS
10010_AMK-0357

I want to write a query which can produce the part of string like below (from the first underscore up to the second, both not included):

AMK-4799
DMUSER
DMUSER2
AMK-TC037
AMK-TABLETUSER
AMK-0357

I am using Microsoft SQL Server 2014.

Assuming you want data between first instance of _ and second instance of _ OR length of string in-case second _ is not present.

Try this Query:

-- Create Table Variable
DECLARE @tblString TABLE(StringVal VARCHAR(512));
-- Insert Values in Table Variable
INSERT INTO @tblString(StringVal)
select '10470_AMK-4799__OFS_BROWSERTC' UNION ALL
select '6038_DMUSER_DM.READ.REPLACEMENT' UNION ALL
select '6043_DMUSER2__OFS_DM.OFS.SRC.VAL' UNION ALL
select '10194_AMK-TC037__OFS_BROWSERTC' UNION ALL
select '64948_AMK-TABLETUSER__OFS_TWS' UNION ALL
select '10010_AMK-0357';
-- Select and process for expected Result
SELECT SUBSTRING(
                  SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal))
                ,0
                ,CASE when CHARINDEX('_',SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal))) <> 0
                    then CHARINDEX('_',SUBSTRING(StringVal,CHARINDEX('_',StringVal,0)+1,LEN(StringVal)))
                    else LEN(StringVal) as Result
                end
                )
FROM @tblString

The results are as below:

Result
-------
AMK-4799
DMUSER
DMUSER2
AMK-TC037
AMK-TABLETUSER
AMK-0357

This will achieve current scope.


MySQL : How do I partition based on Month of date I have a table having id as primary key and date column.

I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that !

I am using mysql 5.6 on AWS RDS.

alter table report.track_pdp_visit partition by hash(start_date);

It gives me following error:

  Field 'start_date' is of a not allowed type for this type of
  partitioning

From here, below is an example that range partitions by YEAR - change as appropriate.

Here is the part of the manual that explains how to alter tables and add partitions. You should be able to take it from here.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE);


MySQL Replicate Databases without Tables or Content I would like to replicate all the databases from a MySQL master to a slave used for archiving data older than 30 days.  It isn't common for this data to be accessed so I would like it removed from the master.  I only need to replicate the database table structure without any content.  But only two tables (tablea and tableb) out of the 10 tables need to be created in the database structure.

The table content will be imported using an archive script that is using pt-archiver which is why I don't need the content replicated to the slave.  However when the main database is removed from the master it should also remove the database from this archive slave.  Is this possible using replication?

It isn't possible with replication as has been correctly pointed out, since that's not how replication works, nor can it be configured to work that way.  Replication either does or does not handle any given table (depending on configuration) but when replication does handle a given table, the data is replicated and replication depends on the rows in the tables starting out and remaining identical.

What I'm hearing you say is that you want some of the same schemas and tables on the 2nd server as exist on the master, but they will be populated via external means, and you want those tables to go away when they are dropped from the master.

Here is a high-level view of how this could be accomplished via automatic means using only things built-in to MySQL.

Obviously this is a dangerous operation if not carefully written since we're talking about the automated dropping of tables and databases, so... caveat lector.

enable the event scheduler on the archive machine
enable the federated storage engine on the archive machine
within another schema you create solely for the purpose of this exercise, define 2 FEDERATED tables that connect back to the master.  One of these points to information_schema.tables and the other points to information_schema.schemata.
write a stored procedure that selects from the archive machine's information_schema.schemata table, left join the federated schemata table on catalog_name, schema_name where schema_name not in ('mysql','information_schema','performance_schema','any','other','schemas','you want not','to be deleted','for sure')
take values from the resulting rows, which will be all of the databases that exist on the local machine but not on the master... and, using dynamic sql, drop those databases from the archive machine.
do the same thing with the information_schema.tables table, which will identify tables you can drop.
use the event scheduler to automate the running of this stored procedure.  Don't schedule it too often, perhaps only once a day, because selecting from the information_schema.tables table can be a resource-intensive operation. MySQL gathers stats when you do this that can be costly especially in terms of I/O.


How to check the health of a SSAS database? And of its backups? regarding backups of the SSAS databses, based on the following behaviour:

Backup on an empty SSAS database hangs

Is there a way to check whether or not the SSAS backup is healthy?

I would accept powershell, AMO, anything, any tool to get this done.

Plus after backing it up, how would I know that the backup file is not corrupted as well?

If yes, then is there a way to fix it, without rebuilding the cubes from scratch?

Our cubes here take a whole lot of processing and time to get rebuild, I am trying to figure it out a way to avoid that, in case the of a disaster recovery scenario.

I found the same question below, but I could not make it work. If at least there would be an example.

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

I don't think you can verify an SSAS database isn't corrupt apart from querying it. If you query it and you get errors it's corrupt.

If your database ends up corrupt the only sure way to recover is to restore the last known good backup, even though sometimes you can get away with messing in the .xml files or the data folder structure there is no guarantee and it's certainly not automated.

You can test an OLAP backup using msmdsrv.exe on the server (located in <driveletter>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin).

For example executing

C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin>msmdsrv -j -b "f:\backup\olap\olap.abf"

on the server I'm currently on yields this:

  Backup file f:\backup\olap\olap.abf contains database with the Name
  of OLAP and  Id of OLAP consisting of 179 files with the total size of
  100632864 bytes All files were successfully tested

If you are using encryption you need to use the -k switch to provide the password

If the backup is invalid I don't know of any automated method to fix it apart from creating a new backup.


What would cause a transactional replication subscription to restart repeatedly with no errors? I have set up a transactional replication publication/subscription to test a migration from an on-premise SQL 2016 Enterprise Edition installation to Azure SQL DB and I'm finding that when the database snapshot finishes uploading to Azure, it just starts over again from the beginning. It finishes within 5-6 hours and uploads ~125GB of data, so I don't believe it is anything to do with the subscription expiring or exceeding any timeout thresholds. It is set to run continuously, rather than on a schedule.

I've looked at the distribution history in the MSdistribution_history table and can't see anything which mentions the subscription being marked for reinitialization, all that is there are entries showing 'Bulk copied data into table ...', and then at the point where I can tell it restarted, it goes back to 'Creating primary key index on table...' and 'Applied script x...'. I have also looked at the MSrepl_errors table and msdb..sysreplicationalerts and both are empty.

I have tried deleting the Azure SQL DB and publication and going through the process from the beginning again, but I am seeing the same thing after having done this.

Is this something that someone has seen before and could explain the cause of and how to fix it?

Just in case anyone else comes across this problem, I believe I have found the solution.

I ran through all of the steps again from beginning to end in preparing the database for replication, and I realised that one of the triggers we have on the database was not marked with the 'Not for replication' property. It previously had been, but a code change had been made and deployed a version of the trigger without this property. After changing this back I set up a new snapshot and applied it successfully with no problems.

Due to the size of the database I was trying to replicate, I haven't tested flipping the setting back to off and trying it again to confirm this was what fixed it, although I did try it with a smaller database and didn't see the same behaviour. My thought on this is that the trigger was causing some sort of error with the data but not properly throwing an error message.

To check whether this property is set or not just run the following script and look at the is_not_for_replication property - 1 means it is set, 0 means it is not.

SELECT name,
is_not_for_replication
FROM sys.triggers
WHERE is_ms_shipped = 0;

If you are seeing the issue described above and then find that one or more triggers do not have this property set, update the definition of the stored procedure to include the not for replication property and try again. Setting the property is explained here: https://stackoverflow.com/questions/44882512/how-to-create-trigger-with-not-for-replication-in-merge-replication-of-mssql


Give permission to select from system tables I have a database Microsoft SQL Server 2008 r2.
A user called marie is defined, who can access tables on the main database; this works well.
Now, I would like marie to be able to perform this query:

SELECT resource_type,spid,login_time,status,hostname,program_name,nt_domain,nt_username,loginame
  FROM sys.dm_tran_locks dl
  JOIN sys.sysprocesses sp on dl.request_session_id = sp.spid

The tables involved are master tables; how do I give permission to marie to read from them?
I already tried to execute the following as dbo:

GRANT ALL on sys.dm_tran_locks TO marie
GRANT ALL on sys.sysprocesses TO marie

Still, when marie tries to perform the query above, the error is:

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

What am I doing wrong?

System views require a slightly elevated state to view, since they are instance wide.  You'll want to GRANT VIEW SERVER STATE for this:

GRANT VIEW SERVER STATE TO marie;


How can sqlplus knows where ORACLE_SID point? As I understand, SID is unique value to identify an Oracle database instance.

And I can set default database instance sqlplus use by changing ORACLE_SID environment variable.

But how can sqlplus know where the SID point?

I have thought tnsnames.ora let sqlplus know the information but there seems not present that kind information.
(If so when does sqlplus use tnsnames.ora?)

When you use SQLPlus and make a bequeath connection (e.g., not going through the listener and not specifying the SID/Service Name in the connect string or using a TNS alias), SQLPlus/Oracle uses the combination of ORACLE_SID and ORACLE_HOME to uniquely identify the instance that it connects to.

Important to note: You may have multiple instances running on one server which all share the same OH, but they will have different SIDs.  Or you could have multiple instances running from multiple Oracle Homes.  Two instances on one machine will never share the same combination of SID and OH.

It's important to note that this connection method will only work when you're making connections from the command line of the server that the Oracle database/listener is running on.

If you're connecting from a remote server (or from the same server if you need to connect to a specific service - e.g. a PDB in 12c), you must specify in the connect string one of either a tnsnames alias, or the hostname [optional: port] and the service name you're connecting to.

e.g,:

sqlplus scott/tiger@hr

This assumes the tnsnames.ora on the client is set up with an alias for "hr".  This TNS alias will have the hostname, port, and SID/Service Name in it.

sqlplus scott/tiger@hostname[:port]/servicename

This connects to the hostname and SID/Service Name provided on the command line.  ORACLE_SID, ORACLE_HOME variables and TNS are entirely unrelated to this connection and are not used.

A real example:

sqlplus hr/hr@productiondatabase.company.com:1521/hrdb


SQL Server 2017 Availability group not routing to secondary server I've set up a dev AlwaysOn environment on SSMS 2017.
I have my AG set up:

I also ran the following scripts:

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB1.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB2.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB2','DEV-DB1')));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB1','DEV-DB2')));

now when I connect to the listener (using SSMS2017) with ApplicationIntent=ReadOnly and I run select @@SERVERNAME it still shows me DEV-DB1. I thought it would show me that I'm getting data from the secondary DEV-DB2

What am I missing?

2 more things to be noted here while you are trying to connect to the Listener via the Management Studio for ReadOnly routing:

Make sure that you are also providing the database Name along with "ApplicationIntent=ReadOnly" in the Additional Connection parameters section of SSMS
SSMS sometimes has sticky connections so it could be possible that it is still connecting to an existing connection that was made via SSMS before providing the parameters "ApplicationIntent=ReadOnly". Try restarting the SSMS.


Parsing JSON data from a text column in Postgres I have JSON in a file as follows:
[xyz@innolx20122 ~]$ cat cgs_test.json
{"technology":"AAA","vendor":"XXX","name":"RBNI","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}
{"technology":"AAA","vendor":"XXX","name":"RRCADD","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}

and I have loaded this to one temp table:
create table temp_json (values text);
\copy temp_json from '/home/xyz/cgs_test.json';

Now I want to extract this data into separate columns as:
technology
vendor
name
temporal_unit
regional_unit
dataset_metadata
rk

dataset_metadata is a JSON column and other columns are strings.

To answer this question what I did was the following (see fiddle here):
This was quite a learning experience for me (+1 for that BTW) so I'll go through the steps in my logic and hopefully this will help you - but I'm doing it for myself really :-)
Step 1:
CREATE TABLE temp_1
(
  json_input TEXT NOT NULL
);

Step 2:
Populate it with your two records. However, your two records aren't proper JSON - they need a [ at the beginning and ] at the end in order for them to be proper JSON - so, I've put them into the INSERT at the very beginning of the process - I leave it up to the reader to experiment with what happens if you leave them out - you can put them in later in the process - see below.
There may be a way of not having to do this with a different JSON function - beyond my pay grade I'm afraid - but I'd appreciate any suggestions for improvements.
INSERT INTO temp_1 VALUES  -- strings copied from the question
('{"technology":"AAA","vendor":"XXX","name":"RBNI","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}'),
('{"technology":"AAA","vendor":"XXX","name":"RRCADD","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}');

Then, turn the strings into JSON as follows - this can be done in bulk:
--
--  This is to turn the "ordinary" strings into JSON per record. If this isn't
--  done, the process will fail further down. Will work for an arbitrary number
--  of records
--

UPDATE temp_1
SET json_input = '[' || json_input || ']';

Step 3:
Create another table:
CREATE TABLE temp_2
(
  final_json JSON NOT NULL
);

Step 4:
Populate this by:
INSERT INTO temp_2 SELECT json_input::JSON FROM temp_1;

The ::JSON cast is necessary, otherwise the INSERT will fail as json_input is of type TEXT.
Then, clear down `temp_1':
--
--  Clear down temp_1.
--  This is needed for next batch, otherwise you'll have strings beginning
--  with '[[' and then '[[[' (endings same with multiple ']'s) and so on!
--

DELETE FROM temp_1;

Step 5:
Create a table to hold the data:
CREATE TABLE json_table
(
  technology TEXT,
  vendor TEXT,
  name TEXT,
  temporal_unit TEXT,
  regional_unit TEXT,
  dataset_metadata JSON,
  rk SMALLINT
);

Step 6:
From here, I obtained this method to populate the table:
INSERT INTO json_table
SELECT (json_populate_recordset(null::json_table, final_json)).* FROM temp_2;

There's another way shown in the fiddle - from here - YMMV?
Step 7 - final check:
SELECT * FROM json_table;

Result:
technology  vendor  name    temporal_unit   regional_unit   dataset_metadata    rk
AAA XXX RBNI    hour    cell    "{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}"   1
AAA XXX RRCADD  hour    cell    "{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}"   1

A couple of thoughts:

Your dataset_metadata field doesn't appear to be "proper" JSON - maybe a suitable REGEXP_REPLACE and/or a CAST might be in order? I don't know the process by which you get your /home/xyz/cgs_test.json file - but it may be worth looking at how the record is generated - ideally, you should correct any issues as early in the pipeline as possible.

I don't know your exact circumstances, but you should carefully consider the advice here - you might want to hive your metadata off into a separate table with a FOREIGN KEY connecting it to the parent record?


How to install SQL Server 2012 on Windows 10 Currently this has been a huge pain for me.
I have a fresh Windows 10 VM. I Installed SQL Server 2012. I open up the SQL Server Studio 2012, and there is no server name that I can choose from in the "Connect to Server" dialog box that shows up.

Additional Context:
10.0.2100.60 is the sql server version
4.0 is the .net framework

Turns out all I was doing was installing just the management studio, and not the sql server service, which is why it wasn't showing up.


Check progress of alter index reorganize / rebuild How can I check the progress / status when I submit an alter index reorganize / rebuild ?

It's really hard to say how long your rebuild will take, as SQL itself doesn't really know in advance and cant give you an estimate.

You can use the following query to use the dm_exec_requests dmv to view how long your index rebuild has been going on for, and to verify that SQL doesn't really have an estimate:

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('Alter Index')

However when it comes to a real estimate on time required you can read this nice blog post from sqlmunkee, which summarises this by saying "..it depends. ":

  And since we're not all on the same hardware, using the same software,
  or looking at the same data, the answer has to be… ..it depends

Frustrating, but true, sadly.


Joining two Mysql VIEWs takes a very long time I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of movies by that actor/actress.

This is a query where I get movies by actor name:

$query = $conn->prepare("SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId WHERE c.castName LIKE :q");
 $query->execute(array(':q' => '%' . $searchText . '%' ));

My question:

The above query works fine if user select a name from auto-completion list. However I would like to enable user to write any name (even if he couldn't find from auto-completion list). For example, If user write "tom" in the textbox, and click on "search" button, I want to show list of all movies by all actors that their name containing word "tom".

For this purpose, I used LIKE :q and ':q' => '%' . $searchText . '%' in the above query, but the query never ends!!(I think because cast_movie is a VIEW which is quite large (with 3 million rows) and joining this view with the other table takes a very long time (actually I waited for 10 minutes and it didn't finished yet).

Could someone kindly let me know if there is any way to fix this? (I read we can use index for joining very large tables, but I think it's not possible to define index for cast_movie since it is VIEW.)

More info about tables and views:

cast_movie is a view which is made by joining "movie_roleNames" and "movies".

movie_roleNames is also a view which is made by joining two tables "Cast" and "nameRoles".

film_info is also a view that is made by joining two tables "movies" and "movies_info".

The structure of the above tables:

table "movies": Id, movieName, ImdbId(unique Id of movies), Rate, numVotes, year (indexes: ImdbId, movieName, year)

table "cast": castName, castImdbID (unique Id of casts) (indexes: castName, castImdbID)

table "nameRoles": Id, castImdbId, movieImdbId, role_Id, (indexes: movieImdbId, castImdbId)

VIEW "movie_roleNames": Id, castName, castImdbId, movieImdbId and the join statement was: SELECT n.Id, c.castName, n.castImdbId, n.movieImdbId FROM nameRoles as n join Cast as c ON n.castImdbId = c.castImdbID

VIEW "cast_movie": Id, castName, castImdbId, movieImdbId, movieName and the join statement was: SELECT m.Id, r.castName, r.castImdbId, r.movieImdbId, m.movieName FROM movie_roleNames AS r JOIN movies AS m ON r.movieImdbId = m.ImdbId

All ideas are highly appreciated,

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly.

Let's take your query and look for 'tom'

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE c.castName LIKE '%tom%';

This joins everything. Then, it scans the resulting temp table and returns every row with tom.

Let's implement the FULLTEXT method

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

I can almost assure you that a full table scan will occur during the join because I have experienced it hundreds of times:

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
Jul 18, 2012 : Why full-text-search returns less rows than LIKE

I started writing posts about FULLTEXT and query refactoring ever since I saw a post from  webmasterworld.com entitled MySQL Match Against + a very expensive join. How do I optimize this?

Given the information I just mentioned let's take your query and refactor it to handle the FULLTEXT and stilldo a good join.

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

First, apply the MATCH ... AGAINST operator to cast_movie and return ImdbId and other fields

SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)

Make that a subquery to provide keys to join and retrieve data from film_info

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM
(
    SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
    WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)
) as c JOIN film_info as f ON c.ImdbId = f.ImdbId;

This should produce a better result.

If your tables use InnoDB Storage Engine, remember to tune the InnoDB Fulltext options. Any fulltext option that does not start with innodb_ is meant for MyISAM (See my post MySQL FullText search on string shorter than 3 chars returns no rows)

Give it a Try !!!


MySQL cluster replication: Slave cluster should not store any data except data replicated from master cluster? I am researching MySQL cluster replication. I read a section "We assume that the slave server or cluster is dedicated to replication of the master, and that no other data is being stored on it" in MySQL cluster manual. According to the section, slave cluster should not store any data except data replicated from master cluster.

I want to know for sure because I have an intention to deploy two cluster that a SQL node are both master and slave.
Example: I have two clusters with SQL node A belongs to cluster a and SQL node B belongs to cluster b

SQL node A --- master of node B and also slave of node B  ---> SQL node B

SQL node B <--- master of node A and also slave of node B ---> SQL node A

I think an bidirectional replication channel help me have two cluster for backup and scale out read/write (It allow web application to be read/write to two SQL nodes)

I am wondering the model is feasible ?

I do not see any problem (if you configure it properly: bin_log on, log_slave_updates, row replication, different server ids for all servers, same exact versions on all servers), but only if you use one of the clusters as read-only at any time.


I am trying to connect My R studio to SQL Database on the Remote Desktop The SQL Server on the remote desktop is configured to have neither login nor password, it is configured to use Windows Authentication.

Code

I am using the following code to connect:

library(DBI)
library(RODBC)
dbhandle <- odbcDriverConnect('driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS')

Error Generated

Warning messages:

1:

    In odbcDriverConnect("driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS") :
      [RODBC] ERROR: state 28000, code 18456,
message [Microsoft][ODBC SQL Server Driver][SQL Server]

Login failed for user 'HMINDIA\274074'

2:

In odbcDriverConnect("driver=SQl Server;server=FAC-270938-ENG2;Database=HRMS")
   ODBC connection failed

Basically, when you failed to connect to your SQL Server, the issue could be:

Network issue. SQL Server configuration issue.
Firewall issue.
Client driver issue.
Application configuration issue.
Authentication
    and logon issue.

Check this link for more details.

In your error message it is just a login failure:

  Login failed for user 'HMINDIA\274074'

you need to create a login.
there are windows logins and sql logins:

for example (windows login - attached to the active directory):

USE [master]
GO

CREATE LOGIN [my_domain\my_login]
FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

--grant the access and permissions accordingly
--in this case it is a sysadmin
--ALTER SERVER ROLE [sysadmin] ADD MEMBER [my_domain\my_login]
GO

example of creating a sql login:

USE [master]
GO

CREATE LOGIN [my_ETL_login] WITH PASSWORD=N'Hare_Krishna108',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- grant access to this login
-- this login will be able to access and read database CAMemeMail:

    USE [CAMemeMail]
    GO
    CREATE USER [my_ETL_login] FOR LOGIN [my_ETL_login]
    GO
    USE [CAMemeMail]
    GO
    ALTER USER [my_ETL_login] WITH DEFAULT_SCHEMA=[dbo]
    GO
    USE [CAMemeMail]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [my_ETL_login]
    GO

And don't forget the firewall


INSERT MASTER.sys.sysdatabases result into a temporary table I have this query that iterate all databases on my SQL Server

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM MASTER.sys.sysdatabases

where name not in ('master','tempdb','model','msdb')

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor

But how can I store the result all in one temporary table?

Dan Guzman was right (in a comment he deleted) that you can just use master.sys.master_files, and that you should certainly avoid old views like sysdatabases and sysfiles, but if you need to do a similar thing using data that is not conveniently rolled up into master for you, you can do something like this:

DECLARE @db sysname, @sql nvarchar(max), @exec nvarchar(max);

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT name FROM sys.databases
    WHERE database_id > 4
      AND state = 0;

OPEN c;

CREATE TABLE #files(db sysname, fn nvarchar(1000), sz int);

SET @sql = N'INSERT #files(db,fn,sz)
    SELECT @db, physical_name, size
    FROM sys.database_files;';

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
  EXEC @exec @sql, N'@db sysname', @db;
  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;

SELECT db, fn, sz FROM #files;

DROP TABLE #files;

Notice no clunky string concatenation, no messing with 16 consecutive single quotes, and what happens in your code if a database is named oh'crap? I keep databases like this around for exactly this reason:

  Msg 105, Level 15, State 1, Line 1
  Unclosed quotation mark after the character string 'as DB_name, SF.filename, SF.size FROM sys.sysfiles SF'.

However, you could just change the script you have right now to just do this:

INSERT #temptable(columns) EXEC sys.sp_executesql @Command;

You might also be interested in the procedure I wrote, sp_foreachdb, as surely along the way someone will suggest you use sp_MSForEachDB, which is undocumented, unsupported, and horribly broken.

Making a more reliable and flexible sp_MSforeachdb

It's also part of Brent Ozar's First Responder Kit.


Memory utilization at 98%, why not 100%? I wonder why SQL Server does not use all available memory - 100% when I am quite sure there is a memory bottleneck. For example, one server I administer starts with using 91% of memory and gradually increases up to 98 but not 100. Increase trend is as follows after a restart at 23:00:

58% after restart and reaches 79% throughout the night. I suppose this should be due to overnight back-ups taken.
82% by 11:00 next day.
90% by 12:00
A steady trend throughout the day until 23:00 when memory utilization increases to 93%
95% next morning at 08:00
97% at 17:00
98% next day at 14:00 and stays there.

What might be causing that 2% not being utilized?  Is it possible that Windows O/S is preventing it? If so, how can I prove that indeed Windows is keeping it to itself?

I am referring to the overall memory usage by the server - i.e the OS. Max memory configuration is already set well beyond the server's current memory. I have a monitoring tool at hand for tracking memory utilization.

Memory utilization by the server is no less nor above 98%. This is a dedicated, single-instance, two database SQL Server on which no other services, applications etc. are running. SQL Server might be using most of the allocated memory -i.e. 98%- but my question is why is it not using the rest 2%? I'm not sure if I am becoming too peevish in questioning that 2%?

Thanks to the query Determining Current Memory Allocation in Monitor memory usage I confirm Memory_usedby_Sqlserver_GB is 60 out of 64 GB total RAM. I was unable to find the column for max server memory and total RAM in the output of sys.dm_os_process_memory DMV (maybe due to SQL Server 2008 R2) but I may confirm it is a 64 GB server and max. server memory is set to 2147483647 MB.

I don't know if my server will utilize any extra memory once they are put in the slots. I am trying to prove if the server needs more memory, (if so) will make use of it and thus make a cause to push for purchase. I will be left in an awkward position if the memory is bought and memory utilization does not increase and stays in its current state - 60 GB. Seeing 60 GB of RAM being utilized after increasing the server's memory from 64 GB to 128 GB will be a waste of money.I want to make sure before the fact if the server will benefit from the new extra RAMs or be indifferent.

Everything requires memory including the OS just like SQL Server. The Operating System can remove memory from SQL Server, unless you happen to have locked pages in memory permissions for SQL Server. However, even then the OS will always win to keep itself running, so if it needs memory it will force SQL Server to give it up.

As well, just because SQL Server pulled up to 98% memory does not necessarily mean it is using it all. You would have to dig into memory usage of SQL Server itself to know what it is doing with it all. It holds it until the OS request it back, forcefully or in a nice way.

I will also note, you should not have max memory set "well beyond the server's current memory" capacity. SQL Server thinking it can grab more memory than there is really is can cause performance issues. Max memory is just the buffer pool limit, this does not include other areas of SQL Server processes that grab memory. So it is best to keep the max memory lower so the OS and other processes for SQL Server have some.

To dig into into performance, use Perfmon, DVMs, your monitoring tool (if it includes SQL Server monitoring).

Specifying Max memory: I base it on the OS but you also have to consider what else is running on the server. Are you running other components of SQL Server (SSIS, SSAS, SSRS)? Do you have any agents for monitoring or security purposes? An example, base value for me with Window Server 2012 R2 is leaving the OS 6GB of RAM and everything else goes to SQL Server.

See Monitor memory usage in the product documentation.


Record with variable number of columns? I am trying to model a cable mapping for devices. Each device has multiple cards, and each of those cards has multiple ports. Since both the number of cards and ports vary, I am confused on how to establish the correct normalized form and relationships that would address one device record having an arbitrary number of cards and those cards having an arbitrary number of ports. Any thoughts?

This sounds like a really simply one-to-many relationship.

For SQL Server, I would write this like:

CREATE TABLE Devices
(
    DeviceID INT
    , DeviceName nvarchar(255)
);

CREATE TABLE Cards
(
    CardID INT
    , CardName nvarchar(255)
    , DeviceID INT
);

CREATE TABLE Ports
(
    PortID INT
    , PortName nvarchar(255)
    , CardID INT
);

INSERT INTO Devices VALUES (1, 'Test Device 1');
INSERT INTO Devices VALUES (2, 'Test Device 2');
INSERT INTO Cards VALUES (1, 'Card 1 in Test Device 1', 1);
INSERT INTO Cards VALUES (2, 'Card 2 in Test Device 1', 1);
INSERT INTO Cards VALUES (3, 'Card 1 in Test Device 2', 2);
INSERT INTO Cards VALUES (4, 'Card 2 in Test Device 2', 2);
INSERT INTO Ports VALUES (1, 'Port in Card 1, Device 1', 1);
INSERT INTO Ports VALUES (2, 'Port in Card 2, Device 2', 4);

SELECT *
FROM Devices;

SELECT *
FROM Cards;

SELECT *
FROM Ports;

This allows a Device to have multiple Cards, which in turn can have multiple Ports.

The results:

The 3 tables can be JOINed together like this:

SELECT DeviceName, CardName, PortName
FROM Devices
    INNER JOIN Cards ON Devices.DeviceID = Cards.DeviceID
    INNER JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

If you use LEFT JOIN like this:

SELECT DeviceName, CardName, PortName
FROM Devices
    LEFT JOIN Cards ON Devices.DeviceID = Cards.DeviceID
    LEFT JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

you get these results:

This is an image showing the table relationships:


Why is column default value not portable through different RDBMS? Some ORMs (doctrine) do not support default values in database schema definition, because they states it is not portable.
But SQL92 supports the DEFAULT value clause, and all the databases supported by the ORM (MySQL, PostgreSQL, SQL Server, Oracle) support it too.

What are the pitfalls of the DEFAULT clause in different vendors/versions of RDBMS?

An ORM that is intended to be truly storage-layer agnostic will generally assume the absolute minimum from the data layer (tables, rows, columns of standard types, indexes, PKs, FKs, and that is about it) and will implement everything else like defaults and more complex constraints themselves.

While every relational database I could mention (mssql, oracle, prostgres, sqlite, mysql, ...) supports default values as defined in SQL-92, things vary a lot more once you consider using document oriented DBs and other less traditional arrangements for your storage layer (they might not support them and if they do the operating semantics may not be quite the same).


How Should I Configure Memory for Multiple Instances of SQL Server? We have a set of SQL Server instances (2008 R2) that run on the same virtual server.  Each instance represents a stage in our development cycle (Dev/Test/Stage/etc.).  The instances all need to be online at any given time, but load tends to be isolated to one instance as a time, depending on where we are in the release lifecycle.

SQL Server seems to grab and hold whatever amount of memory we set as the max.  What I'd like to know if there is any way of configuring the instances to be "smart" about memory consumption so they are not blocking each other from getting memory when needed.

SQL Server will use as much memory as you can throw at it (pretty much), and it is always advisable to set a limit on every instance.  The default setting is 2147483647MB.

Depending on your usage and own unique circumstances, your settings may vary, but normally a good starting point is to set the max server memory between 75 and 90 percent, depending on how much RAM you have.  Glenn Berry share a good indication a few years back HERE.

If you have multiple active instances on a server, you might have to share the resources between them, for e.g. if you have 32GB of RAM with 3 instances, you should assign around 9500MB to each instance.  When you are using just one instance at a time, the others will not release the RAM they are using.  You could free up their RAM by stopping the service.

When you have other applications / services running, the amount of memory might have to be adjusted to accommodate them.


Upgrading Operating System only. OS 2016 - 2019 Currently we have a number of Servers that are using Windows 2016 with SQL Server 2017. The support team want to do an in-place upgrade of the OS to 2019 due to 2016 Mainstream support ending in Jan 2022. I don't want to upgrade the SQL Server versions, so I am wondering if it is Ok to just upgrade the OS? I will obviously be taking backups and the like but just wanted to know if there were any gotchas I should be aware of before agreeing with the OS upgrade?
Thanks

I suggest you read up on Database Compatibility at https://aka.ms/dbcompat
What this will tell you is that validation is not done at the SQL Server major version level but at the database level.  This should ease your concerns about upgrading from SQL Server 2017 to 2019.
However, if you are staying on the same version for cost purposes (e.g. you don't have software assurance to get free upgrades) then running SQL Server 2017 on Windows 2019 is fully supported and does not change the features or functionality of SQL Server.  See the support matrix for confirmation of that


I want to add a user/login to role in another database without switching databases I have a role / login in a 'new' database mynewdb with a role "emailsender". I have scripted a profile for sp_send_dbmail and am now trying to script adding the the login to the DatabaseMailUserRole in msdb and seem to be going around in circles. Importantly I want to run my update script in mynewdb. I know I can use SSMS to do this. The question is:

How to script adding the user to the role in T-SQL from the mynewdb database without switching to msdb?

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


kill all queries - MySQL Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing?

On account of how I don't like typing.

From the Linux command line

for PROC_TO_KILL in `mysql -h... -u... -p... -A --skip-column-names -e"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -A --skip-column-names -e"KILL QUERY ${PROC_TO_KILL}" ; done

You can change the grep option in the header of the for loop to locate a specific user or specific string in the query.

If you have MySQL 5.1 where the processlist is in the INFORMATION_SCHEMA, you can do this to generate the KILL QUERY commands in bulk from within the mysql client:

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery
FROM information_schema.processlist WHERE user<>'system user'\G

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.


Postgres JOIN strange behaviour I'm just starting studying Postgres and I'm in a situation where, depending how I do the JOIN on my tables, the performance and plan output seems really strange.

Those are the table used with their indexes:

create table escola
(
    pk_codigo integer not null
        constraint pk_escola
            primary key,
    nome varchar(100),
    municipio varchar(150),
    uf char(2),
    cod_municipio integer,
    uf_id integer default 0 not null
        constraint fk_escola_uf_id
            references tb_uf
)
;

create index idx_escola_uf
    on escola (uf)
;

create index idx_escola_uf_id
    on escola (uf_id)
;

create index idx_multi_escola_uf_pk
    on escola (uf, pk_codigo)
;

create table if not exists candidato
(
    pk_numero_inscricao bigint not null
        constraint candidato_pk
            primary key,
    cod_municipio_residencia integer,
    municipio_residencia varchar(150),
    uf_residencia char(2),
    uf_nascimento char(2),
    situacao_conclusao numeric(1),
    ano_concluiu smallint,
    idade smallint,
    sexo char,
    fk_codigo_escola integer
        constraint fk_candidato_codigo_escola
            references escola,
    uf_prova char(2)
)
;

create index if not exists idx_candidato_codigo_escola
    on candidato (fk_codigo_escola)
;

create table tb_uf
(
    uf varchar(2),
    pk_id serial not null
        constraint tb_uf_pkey
            primary key
)
;

create unique index tb_uf_uf_uindex
    on tb_uf (uf)
;

create unique index tb_uf_pk_id_uindex
    on tb_uf (pk_id)
;

And the queries (with plans):

EXPLAIN ANALYZE
SELECT pk_numero_inscricao, pk_codigo
FROM escola e
  JOIN candidato c
    ON c.fk_codigo_escola = e.pk_codigo
WHERE e.uf = 'RJ'
;

Time without EXPLAIN ANALYZE: 916ms
Plan: https://explain.depesz.com/s/M6B

EXPLAIN ANALYZE
SELECT pk_numero_inscricao, pk_codigo
FROM escola AS e
  JOIN candidato AS c
    ON c.fk_codigo_escola = e.pk_codigo
  JOIN tb_uf AS u
    ON e.uf_id = u.pk_id
WHERE u.uf = 'RJ'
;

Time without EXPLAIN ANALYZE: 72ms
Plan: https://explain.depesz.com/s/E3MR

EXPLAIN ANALYZE
SELECT pk_numero_inscricao, pk_codigo
FROM escola AS e
  JOIN candidato AS c
    ON c.fk_codigo_escola = e.pk_codigo
WHERE e.uf_id = 19
;

Time without EXPLAIN ANALYZE: 961ms
Plan: https://explain.depesz.com/s/v67V

The weird thing happening for me is that queries 1 and 3 are slower than query 2, although query 2 has an extra join. Does anybody know what might be causing this?

I noticed that Index Scan on table candidato is a lot slower on queries 1 and 3 also, and that makes no sense for me, since the final result is the same.

Another point is that EXPLAIN ANALYZE is adding a lot of overhead into the queries.

Thanks in advance! If I need to provide any more information, I can edit this post if needed!

The answer is pretty "searchable" when you notice, that 1st and 3rd query fetch 1.2M rows from candidato ...just to exclude 90% of them from the results.

2nd query returns 1 row from tb_uf, which forces Nested Loop plan.

This means that planner has wrong assumptions about statistics (expected results count) or costs (of random seek). You could either tune these values:

https://www.postgresql.org/message-id/20060926193553.GA27268@oppetid.no

Have Postgresql query planner use nested loop w/ indices over hash join

or manually force a Nested Loop. Although my intuition tells me it should be better to have Hash Join here.

Not having the data, I'd suggest to try one of:

set enable_mergejoin = off
using CTE to force order of operations, like (3rd query with minimal modifications for easier understanding):

 WITH e AS (SELECT * FROM escola WHERE uf_id = 19)
 SELECT pk_numero_inscricao, pk_codigo
 FROM e
   JOIN candidato AS c
     ON c.fk_codigo_escola = e.pk_codigo


Modify function to use it in multiple triggers I have tables that have almost the same columns. The first table has the current values and the other table has the old values and an additional column to store the date data is inserted. The second table is named as the first table with the prefix _bit. Example:

CREATE TABLE x (
  id serial PRIMARY KEY,
  f1 integer NOT NULL,
  f2 character varying(10) NOT NULL,
  cuando timestamp without time zone NOT NULL DEFAULT now()
);

CREATE TABLE _bitx (
  id integer,
  f1 integer,
  f2 character varying(10),
  cuando timestamp without time zone,
  cuandobit timestamp without time zone DEFAULT now()
);

I have a trigger function that copies the OLD values of a table to another:

CREATE FUNCTION bitacoratabla() RETURNS trigger AS $$
   BEGIN
      INSERT INTO _bitx VALUES((OLD).*);
      RETURN NEW;
   END;
$$ LANGUAGE plpgsql

The trigger:

CREATE TRIGGER trigbitx
  BEFORE UPDATE
  ON public.x
  FOR EACH ROW
  EXECUTE PROCEDURE public.bitacoratabla();

All this works fine but I must do this for multiple tables that follow the same rules.

I modified the function in order to create the name of the table where I will insert data but I am having problems with that and with (OLD).*

CREATE FUNCTION bitacoratabla() RETURNS trigger AS $$
   DECLARE
      tabla text := '_bit' || quote_ident(TG_TABLE_NAME);

   BEGIN
      EXECUTE 'INSERT INTO ' || tabla || ' VALUES(' || (OLD).* || ')';
      RETURN NEW;
   END;
$$ LANGUAGE plpgsql

I am new to PL/pgSQL and thought I did not have to do more. Any ideas?

UPDATE

As all you suggest it is better to use format() and pass values with USING keyword. Thank you. This is what I did:

CREATE OR REPLACE FUNCTION bitacoratabla() RETURNS TRIGGER
AS $$
   BEGIN
      EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME) USING OLD;
      RETURN NEW;
   END
$$ LANGUAGE PLPGSQL;

Answers so far still suffer from one or the other shortcoming.

Use the USING clause of EXECUTE to pass values. Do not concatenate the text representation of values into the SQL string. Expensive and error-prone.
Properly escape all identifiers when used in dynamic SQL to defend against sneaky errors with double quoted identifiers and possible SQL injection. Even if you think you do not need it. Do it on principal.

But this would be an error waiting to happen:

'_bit' || quote_ident(TG_TABLE_NAME)

For a table name "MyTable" this would produce _bit"MyTable" - syntactical nonsense. It must be:

quote_ident('_bit' || TG_TABLE_NAME)

Or use format('... %I ...', '_bit' || TG_TABLE_NAME)

The only correct solution is what you added to your question yourself:

CREATE OR REPLACE FUNCTION bitacoratabla()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME)
   USING OLD;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Detailed explanation:

INSERT with dynamic table name in trigger function

But why does it work with an additional column?

  the other table has the old values and an additional column to store the date data

This happens to work as long as the additional column in the target table comes last because, quoting the manual on INSERT:

  If no list of column names is given at all, the default is all the
  columns of the table in their declared order; or the first N column
  names, if there are only N columns supplied by the VALUES clause or query.

So you better be sure the additional column(s) come last in the table definition of the target table, and all other columns are listed in matching order, now and in all future - or you must add an explicit target column list (which would prevent a generic function to begin with).


Changing a column from NOT NULL to NULL - What's going on under the hood? We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an INT). Just the nullability. The statement is as follows:

Alter Table dbo.Workflow Alter Column LineId Int NULL

The operation takes in excess of 10 before we stop it (we haven't even let it run to completion yet because it's a blocking operation and was taking too long). We'll probably copy the table to a dev server a test how long it actually takes. But, I'm curious if anyone knows what SQL Server is doing under the hood when converting from NOT NULL to NULL? Also, will affected indexes need to get rebuilt? The query plan generated doesn't indicate what's happening.

The table in question is clustered (not a heap).

As alluded to by @Souplex in the comments one possible explanation might be if this column is the first NULL-able column in the non clustered index it participates in.

For the following setup

CREATE TABLE Foo
  (
     A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     B CHAR(1) NOT NULL DEFAULT 'B'
  )

CREATE NONCLUSTERED INDEX ix
  ON Foo(B);

INSERT INTO Foo
            (B)
SELECT TOP 100000 'B'
FROM   master..spt_values v1,
       master..spt_values v2

sys.dm_db_index_physical_stats shows the non clustered index ix has 248 leaf pages and a single root page.

A typical row in an index leaf page looks like

And in the root page

Then running...

CHECKPOINT;

GO

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

SELECT Operation,
       Context,
       ROUND(SUM([Log Record Length]) / 1024.0,1) AS [Log KB],
       COUNT(*) as [OperationCount]
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName = 'dbo.Foo.ix'
GROUP BY Operation, Context

Returned

+-----------------+--------------------+-------------+----------------+
|    Operation    |      Context       |   Log KB    | OperationCount |
+-----------------+--------------------+-------------+----------------+
| LOP_SET_BITS    | LCX_GAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_IAM            | 0.100000    |              1 |
| LOP_SET_BITS    | LCX_IAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | 8.700000    |              3 |
| LOP_FORMAT_PAGE | LCX_INDEX_LEAF     | 2296.200000 |            285 |
| LOP_MODIFY_ROW  | LCX_PFS            | 16.300000   |            189 |
+-----------------+--------------------+-------------+----------------+

Checking the index leaf again the rows now look like

and the rows in the upper level pages as below.

Each row has been updated and now contains two bytes for the column count along with another byte for the NULL_BITMAP.

Due to the extra row width the non clustered index now has 285 leaf pages and now two intermediate level pages along with the root page.

The execution plan for the

 ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

looks as follows

This creates a brand new copy of the index rather than updating the existing one and needing to split pages.