MySQL replication slave of slave not receiving updates

With log-slave-updates on server C, only updates written directly on B will go to server D.
Server A and Server B require log-slave-updates, otherwise, none of their updates will reach C. In general, any Master that is also a Slave must have log-slave-updates in order to relay binlog events from distant servers.
I have discussed this before

MySQL Slave Relay Logging but not logging Binary Log
Multi-master + slaves replication in MySQL

Restarting mysqld is required on servers A and B for the setting to take effect
Let me explain further
Your Setup

A is Slave of B (log-slave-updates off)
B is Slave of A (log-slave-updates off)
C is Slave of B (log-slave-updates on)
D is Slave of C
A, B, and C are Masters

You run INSERT INTO tblname on Server A what happens ?
Since B is Slave of A

B's IO Thread reads A's binary logs
B's IO Thread gets INSERT INTO tblname
B's IO Thread records it in its relay logs
B's SQL thread reads relay logs
B's SQL thread gets INSERT INTO tblname
B's SQL thread executes INSERT INTO tblnameit
Since B does not have log-slave-updates defined, B does not record INSERT INTO tblname into its binary logs. C's IO Thread never sees INSERT INTO tblname coming from server A.

You run INSERT INTO tblname on Server B what happens ?
Since A and C Slaves of B and D is a Slave of C

A's IO Thread reads B's binary logs
A's IO Thread gets INSERT INTO tblname
A's IO Thread records it in its relay logs
A's SQL thread reads relay logs
A's SQL thread gets INSERT INTO tblname
A's SQL thread executes INSERT INTO tblnameit
C's IO Thread reads B's binary logs
C's IO Thread gets INSERT INTO tblname
C's IO Thread records it in its relay logs
C's SQL thread reads relay logs
C's SQL thread gets INSERT INTO tblname
C's SQL thread executes INSERT INTO tblnameit
Since C has log-slave-updates enabled, C records INSERT INTO tblname in its binary logs
D's IO Thread reads C's binary logs
D's IO Thread gets INSERT INTO tblname
D's IO Thread records it in its relay logs
D's SQL thread reads relay logs
D's SQL thread gets INSERT INTO tblname
D's SQL thread executes INSERT INTO tblnameit

Based on this description, if you had any slaves coming from A, those slaves would never see INSERT INTO tblname coming from B.
Therefore, you need log-slave-updates on A and B as well as C.


You can create a table for every property. That way you define the property with the data type that best fits each property. A similar question and answer that describes a simple example, complete with SqlFiddle, is here:

What's the best way to store and search through many product specifications

This might be a little iffy on the scalability side. Each new property means creating a new table, although that would have no effect on existing objects or the code that handles them. So it is expandable but I don't know the absolute limits. Queries are a bit on the complicated side, with at least one COALESCE and possibly a CASE statement or two, but not, I think, any more complicated than the EAV model.


Outer joins...

SELECT *
FROM
    table_B B
    LEFT OUTER JOIN
    table_A A ON B.id_table_A = A.id
;


Convert a Unix timestamp to a DATETIME in a View

the result of the expression

to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND')

is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it is done in this example. This automatic conversion to the oracle stringtype VARCHAR2 is controlled by nls_date_format and some server settings. If you want your view to return a VARCHAR2 value with the calculated date in the format 'YYYY-MM-DD HH24:MI:SS' and not a DATE value you can do this by using a conversion function: expression

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND'),'YYYY-MM-DD HH24:MI:SS')

But in this case the return value of the view is a VARCHAR2 and not a DATE value.


provide a port option for your mongos instance, for example 27018

The error messages says: Only one usage of socket address is normally permitted for 0.0.0.0:27017. It looks like mongos instance is trying to bind to a port that is already ocuppied by another process (propably mongod)


What sort of SPN / AD Computer Delegation do you need for delegation of credentials to a linked server that connects to a file share?

There is only a single kind of Service Principal Name you need for SQL Server.

MSSQLSvc/<servername>:<portname>

It is typically necessary to implement this twice, one for the short name, and one for the long name.   If, for instance, the SQL Server is named "SQLServer1.somedomain.com" and was running on port 1433, then you'd need:

MSSQLSvc/SQLServer1.somedomain.com:1433
MSSQLSvc/SQLServer1:1433

The computer account for SQLServer1.somedomain.com in Active Directory should be granted the ability to delegate security.  Once that is working correctly, you can confirm Kerberos authentication is working by looking at:

SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = dec.local_tcp_port
    , dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

If this shows Kerberos in the auth_scheme column, then Kerberos authentication is working.  If you have a second SQL Server at the other end of a linked server, you can run that query over the linked server:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'SELECT InstanceName = @@SERVERNAME
    , InstancePortNumber = dec.local_tcp_port
    , dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;
';
EXEC [linked_server_name].tempdb.sys.sp_executesql @cmd;

The error message makes me think you're seeing a problem with file share permissions - when using Kerberos SQL Server will impersonate you when accessing the SMB share -  therefor your account needs access to it.


Can I restore all tables but not the stored procedures?

Yes, you can do this, just not by using a database BACKUP / RESTORE. Instead, you would use the SqlPackage.exe utility that is part of SQL Server Data Tools (SSDT). This utility usually assists in migrating database projects in Visual Studio to SQL Server. But, it can also extract schema and data from a database into a file, and then it can push whatever is in that file into another database.

I created a CMD script, GetDataFromProduction.cmd, that extracts the schema and data, and then publishes only the tables and data. The Stored Procedures, Functions, etc are extracted into the .dacpac file (no way around that), but they can be excluded when publishing the content into a database. The following script calls SqlPackage.exe twice, once with /Action:Extract and once with /Action:Publish. It uses /p:ExtractAllTableData=True on the "extract" to grab the data for all tables.

@ECHO OFF

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" ^
    /Action:Extract ^
    /SourceDatabaseName:TEMPTEST ^
    /SourceServerName:(local) ^
    /TargetFile:C:\TEMP\_Extract.dacpac ^
    /p:IgnoreExtendedProperties=True ^
    /p:ExtractAllTableData=True

ECHO Hit the "any" key to publish the extracted data
PAUSE

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" ^
    /Action:Publish ^
    /SourceFile:C:\TEMP\_Extract.dacpac ^
    /TargetDatabaseName:TestPublish ^
    /TargetServerName:(local) ^
    /p:DropObjectsNotInSource=False ^
    /p:ExcludeObjectTypes=Aggregates;ApplicationRoles;Assemblies;AsymmetricKeys;^
BrokerPriorities;Certificates;Contracts;DatabaseRoles;DatabaseTriggers;^
ExtendedProperties;FileTables;MessageTypes;Permissions;Queues;RemoteServiceBindings;^
RoleMembership;Rules;ScalarValuedFunctions;SearchPropertyLists;Sequences;Services;^
Signatures;StoredProcedures;SymmetricKeys;Synonyms;TableValuedFunctions;^
UserDefinedDataTypes;UserDefinedTableTypes;ClrUserDefinedTypes;Views;Audits;Credentials;^
CryptographicProviders;DatabaseAuditSpecifications;Endpoints;ErrorMessages;^
EventNotifications;EventSessions;LinkedServerLogins;LinkedServers;Routes;^
ServerAuditSpecifications;ServerRoleMembership;ServerRoles;ServerTriggers;^
ColumnEncryptionKeys;ColumnMasterKeys;Defaults;ExternalDataSources;ExternalFileFormats;^
ExternalTables;Filegroups;FullTextCatalogs;FullTextStoplists;PartitionFunctions;^
PartitionSchemes;SecurityPolicies;Users;XmlSchemaCollections;DatabaseScopedCredentials;^
Logins

PAUSE

Now, if you do not want the data for all tables, then you can specify individual tables to get. Please note that there is no option to instruct SqlPackage to get all tables except one or two. If you want all but one or two, then you need to specify all of the tables to grab. When specifying individual tables, you need to first specify /p:ExtractAllTableData=False (it was True in the example above) and then add a new "property" to specify each table in "SchemaName.TableName" format:

    /p:TableData="dbo.Table1" ^
    /p:TableData="Orders.Order" ^
    /p:TableData="Orders.OrderItems"

As you can see, you can specify /p:TableData="SchemaName.TableName" as many times as you need. Unfortunately it does not appear that there is any ability to specify a wild-card for "SchemaName.%" or anything like that (because that would make things too easy, and what's the fun in that?). However, you can use the following to generate that list of tables to just copy-and-paste into the CMD script:

SELECT N'   /p:TableData="'
        + SCHEMA_NAME(tbl.[schema_id])
        + N'.'
        + OBJECT_NAME(tbl.[object_id])
        + N'" ^'
FROM sys.tables tbl;

Please also note that while this method does do exactly what is being requested here, I have no basis for estimating at what data volumes this becomes unmanageable. I suppose if a database is too large for /p:ExtractAllTableData=True, then you can break the migration into pieces by specifying /p:ExtractAllTableData=False and then specifying a subset of tables that is manageable, and repeating that across 2 or several of these scripts.

That being said, it does handle the data sync quite nicely in that it will make whatever changes are necessary to get the existing schema and data to match whatever is in the .dacpac file. You don't need to mess with truncating the tables first or anything like that.

If you do not already have Visual Studio and/or SSDT installed, you can get SSDT by itself from: Download Latest SQL Server Data Tools

On a related topic:

  We often edit\change or update the stored procedures in the testing DB to test the data compared to the production's stored procedures!

Why are you changing the Stored Procedures? Doing this, or needing to do this, might point to a problem in your release process and/or architecture that could be improved. Generally speaking, you shouldn't be making code changes to match a different set of data. That implies that you are either hard-coding Customer info in the code (very bad for several reasons) and/or are hard-coding environment-sensitive info, such as URLs, file paths, etc. None of that should be hard-coded; it should all be pulled from config files and/or values in a config table. And you can have a config table where there is a field for EnvironmentName or EnvironmentID so that all environments can have the exact same data, and then you just need a single function to return the value of the current environment, and that will be used to filter against the config table. This makes the release process easier when add/updating/removing config data since the same release script (which usually includes values for all environments) runs in all environments and doesn't need any IF conditions to check for which environment it is running in.

Comment from O.P. on this answer:

  unfortunately I can't install the package without getting an approval

This approach technically doesn't require installing on the server running SQL Server. SqlPackage is a client tool, just like SQLCLD, SSMS, etc, etc. It just needs to be installed on a computer that can connect to the source server. So if I.T. won't let you install it on your local PC, then perhaps you can find a dev or QA server somewhere to install it on.


How do we free up disk space from allocated namespace? / ORA-03297

You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to.

First try and "shrink space" for each affected table:

alter table fragmentedtable enable row movement;

alter table fragmentedtable shrink space;

Next, check to see how much space can be freed from each of the existing tablespace files without moving the current database objects.

The following (taken from Ask Tom & assuming a block size of 8k) tells you how much space you can currently free from each datafile, in megabytes:

select file_name,
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
       ceil( blocks*8192/1024/1024) CURRENT_SIZE,
       ceil( blocks*8192/1024/1024) -
       ceil( (nvl(hwm,1)*8192)/1024/1024 ) SAVINGS
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)

For example:

FILE_NAME                                           SHRINK_TO CURRENT_SIZE    SAVINGS
-------------------------------------------------- ---------- ------------ ----------
/u01/app/oracle/oradata/PHIL112/system01.dbf              696          700          4
/u01/app/oracle/oradata/PHIL112/sysaux01.dbf              579          610         31
/u01/app/oracle/oradata/PHIL112/users01.dbf                85           93          8
/u01/app/oracle/oradata/PHIL112/undotbs01.dbf              65          120         55

SQL>

To resize, use:

alter database datafile '/path/to/data/file.dbf' resize 100m;

The next option you have is to create a new tablespace (and associated datafile(s)) and move the current database objects to the new tablespace. You can do this by using datapump, or do it manually. Tim Hall has an excellent article on how to perform this here.


Fill in missing dates for a vacation schedule

You can do this with a CROSS APPLY that will generate the dates.

SELECT m.requestor, m.schedcode, o.Date FROM mockup m
CROSS APPLY (
    SELECT  TOP (DATEDIFF(DAY, m.fromdate, m.enddate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, m.fromdate)
        FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b
) AS o

Props to GarethD's answer at Get all dates between two dates in SQL Server for the date generating code.


Data Warehouse: Can a Transaction Table also be a Dimension?

It seems that amount sold and quantities sold directly relate to a transaction and I would think those items should be in a transaction fact table and not on a dimension.

If quantities are especially interesting (e.g., enough to have a hierarchy), one might create a dimension for it, in addition to the Quantity field in the fact table. Such an OrderSize dimension could have fields like QuantityBand with values like "1 to 10" and "11 to 50", and QuantityDiscount.

  Can a Transaction Table also be a Dimension?

The answer is yes, it can. Should it? Probably no, but dependent columns are not unheard of. Just know that you are wasting tons of space with repeating columns. Also, the term is Dimensions vs Fact tables. Dimension tables describe the data while Fact tables describe what they are doing.

answer based on comments left by Scott Hodgin, clifton_h, and Jon of All Trades


Turns out you can set it in the [mysqld] block. e.g.

[mysqld]
sql_safe_updates        = 1

Test it's set after you connect:

SHOW GLOBAL VARIABLES LIKE 'sql_safe_updates';


It's a bit clunky, but this should give you the tps since restart of MySQL:

SELECT  SUM(IF(variable_name IN ('Com_commit', 'Com_rollback'),
                   variable_value, 0)) /
        SUM(IF(variable_name = 'Uptime',
                   variable_value, 0))
    FROM  information_schema.GLOBAL_STATUS


unreachable SQL Server for a group of computers

This sounds like a network problem; if the other remote clients can access the instance then it seems unlikely it is an instance-specific problem.

Be sure that firewalls on the notebooks are turned off or (better) are allowing TCP traffic through the port your server is using.
If you are connecting using the server's host name or machine name, make sure it resolves to the correct IP from the notebooks.  Try this from a command prompt on the notebooks:

C:\> ping my.databaseserver.com

or

C:\> ping OUR_SQLSERVER

In the output the IP address to which my.databaseserver.com/OUR_SQLSERVER resolves will be displayed.
See if you can connect directly via TCP to check for misconfiguration of the SQL clients.  On the notebooks, type this at a command prompt:

C:\> telnet 192.168.1.200 1433

Substitute your SQL Server's IP and the port it is using.  The reply you get will not be readable, but unless you get a message like Could not open connection to the host, on port 1433: Connect failed, you are at least able to connect to the server.

Hope this points you toward a resolution.


MySQL database design for Twitter-like timeline

Details not yet spelled out for the schema:

PKs -- What datatype?  INT UNSIGNED (4 bytes) tops out at 4 billion; think about whether you need to go above that or can shrink the size.

Collations -- For inclusion of Chinese and Emoji, be sure to use utf8mb4.

Password -- You should probably not store the plain-text password, but rather the encrypted version.

Latitude & Longitude -- How much precision is needed?  What queries will be applied.  This area can lead to some terribly inefficient code.  Your choices today impact your ability to even use lat/lng without a major rewrite, especially for your item 5.  http://mysql.rjweb.org/doc.php/latlng#representation_choices  and http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

Table: followers -- Does "x follows y" imply that "y follows x"?  The answer to this has an impact all the way back to the table definition.

"reference_id  (Can be report_id or follower_id or message_sender_id)" -- This is not easy to implement.  You may have to re-think the requirements.  Ponder whether to do it as 3 tables that are UNIONed together.

If you are talking about "massive scale", you will need to allow for "sharding".  I recommend postponing that until later -- but plan for a major rewrite in the near future.

If things will be "deleted", will they be "soft" or "hard" deletes or some form of "archiving".  If the quantity is small, this question does not matter; if it is large, it impacts performance and may resort to Partitioning, which is a big schema change.

For performance, consider turning off FKs for production, but keeping them in development.  Be sure to keep the indexes that are implicitly created for FKs.

Etc.


SQL Server 2000 Enterprise Manager - "Navigation to the webpage was canceled" error when trying to design a table

Most likely (or oddly?) a known issue after a Windows patch to do with IE zones because of your help settings.


You could categorise your dueDate values and then use PIVOT like this:

WITH categorised AS (
  SELECT
    Balance,
    Range = CASE
      WHEN dueDate < DATEADD(MONTH,  3, @Date) THEN '0-3 Months'
      WHEN dueDate < DATEADD(MONTH,  6, @Date) THEN '3-6 Months'
      WHEN dueDate < DATEADD(MONTH, 12, @Date) THEN '6-12 Months'
      ELSE '>12 Months'
    END
  FROM
    dbo.YourTable
  WHERE
    Company = @Company
    AND dueDate >= @Date
)
SELECT
  [0-3 Months],
  [3-6 Months],
  [6-12 Months],
  [>12 Months]
FROM
  categorised
PIVOT
  (
    SUM(Balance)
    FOR Range IN ([0-3 Months], [3-6 Months], [6-12 Months], [>12 Months])
  ) AS p
;


not a single-group group function when trying to add two sum() function

select
  (select sum(value_) from a)
  -
  (select sum(value_) from b)
from dual;


Grant Permission to Execute Set Transaction Isolation Level

First a quick point .. It's pretty unusual someone "needs" to use read uncommitted unless the need is to randomly get bad data.

Lecture done, permissions are generally found under individual commands.  In this case you are looking at SET TRANSACTION ISOLATION LEVEL.

Typically you would find a Security/Permissions section, although in this case there isn't one.  I believe that is because this is a command that anyone can run (although some isolation levels have to be enabled).  Another place you can look for permissions is under the properties of the database or server.  You will see a Permissions tab that will have a list of available permissions.  But again I didn't find anything related to the command.

I'm mostly stumped by the fact that you granted db_owner and they worked.  My guess is that it isn't the SET ISOLATION LEVEL that is actually causing the issue.  Can you please include the actual error you are getting?


Yes, that's the best I think. You have the benefit of both InnoDB (referential integrity) and MyISAM (spatial indexes.)

The only technical difficulty is to ensure that the MyISAM table is synchronized with the InnoDB one. This can be done either with triggers or by stored procedures (so every operation on the InnoDB table is done through a procedure.)

Another "benefit" is that since the coordinate values are duplicated at both tables, you can skip backing up the MyISAM table (or backing up separately) and have a procedure to recreate it after a database restore. You probably need a procedure to check periodically that that the two tables are in sync, too.

To speed up queries that need the spatial index - and if the table is not huge - you can make sure that the index is loaded in memory in advance, even in a separate cache. See LOAD INDEX INTO CACHE syntax and Multiple Key Caches


ISNULL on NTEXT causes fatal exception on SQL Server

I think you might be a victim to problem I have seen before, introduced with Cumulative update for Service Pack 2. Is the compatibility level of your database 80 (SQL 2000)?

See Microsoft KB 938102 for Hotfix details, but I suggest you upgrade to the latest service pack (or definitely a later cumulative service pack).

Essentially the Query Engine is evaluating your where clause and creating joins using non-ANSI operators such as *=, you then fall foul of this Sql bug. The problem occurs if the following conditions are true:

The query references a view that uses non-ANSI outer join operators,
For example, the view uses the = operator or the = operator.
The column list of the SELECT statement in the view contains a
subquery.
The version of SQL Server 2005 is SQL Server 2005 SP2 or a later
version.
You set the database of SQL Server 2005 to use compatibility level
80.


Shared entities across multiple databases

One instance of MySQL can have one or more databases.  Each database can have one or more tables.  It sounds like the info belongs "together", so one database makes sense.  It is almost always a "bad" idea to have "one database (or table) per user/product/company/item/etc".

This forum is littered with discussions of why "one per" is bad.


"Permission denied" in Postgres 12 trying to use to_regclass() on table partitions

The user that got the error does not have the USAGE permission on schema PUBLIC.
I recommend to grant that to everyone:
GRANT USAGE ON SCHEMA public TO PUBLIC;


Creating the same unique ID in multiple tables

Add EmployeeId  to columns to both tables.  Populate the master EmployeeId  with the query you have, and join the transaction details table to the employee master on EmployeeName  and lookup the EmployeeId .

eg

with q as
(
   select e.EmployeeId master_EmployeeId, t.EmployeeId
   from HREmployeeMaster e
   join EmployeeTransactions t
     on e.EmployeeName = t.EmployeeName
)
update q set EmployeeId  = master_EmployeeId;


SQL Server Distributed Availability Group databases not syncing after a server reboot

Please note, this is not a definitive answer but it's the best answer after chatting with Taryn.

  However, the primary was showing a very different story. It was reporting that the separate AG was syncing without any issues but the DAGs were in a Not Synchronzing / Not Healthy state

If the individual databases and AGs underlying the distributed ag say they healthy and synchronizing, there is a good chance this is just a hiccup in the DMVs and/or SSMS dashboards. Since there was nothing in the errorlog to suggest the replica didn't connect or was in a disconnected state.

Unfortunately since the issue has resolved, it's hard to say exactly what it was... but in the future if this occurs for someone:

Check sys.dm_hadr_database_replica_states on all clusters looking for anything that isn't healthy. If all shows healthy, it's possible the DMV hasn't updated yet
If it's unhealthy check the errorlog/DMVs for connectivity issues (such as not being able to connect to the forwarder/global primary)
Dan's answer mentions issues that could arise from database startup - though in this case the instance can't be read so that most likely wasn't an issue but could be in your case
If the database is readable, smoke test with a dummy table/insert or ...
Extended event session using the DEBUG channel items sqlserver.hadr_dump_log_block or sqlserver.hadr_apply_log_block to see if the secondary is actually receiving/applying the log blocks or ...
Perfmon object SQLServer:Database Replica\Log Bytes Received/sec

If you're receiving data on that secondary but the distributed ag still shows not synchronizing or not healthy then I'd let it go for a bit to see if the DMV values change since it's obviously receiving and processing log blocks.

If, however, it isn't then we'll need to investigate further which is out of scope of the answer.


Compare Timestamp Difference Between Consecutive Records

This works like this:

It gets numbers for each row of each ProductID ordered by SampleDate (ROW_NUMBER()).
It only keeps first 2 values (biggest SampleDate which are the last 2 rows of the sample)
It calculates the difference in minutes between the 2 SampleDate

Query:

WITH cte AS (
    SELECT ProductID, SampleDate
        , n = ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY SampleDate DESC)
    FROM @x
)
SELECT ProductID
    , CCVPassed = CASE WHEN DATEDIFF(minute, MIN(SampleDate), MAX(SampleDate)) < 15 THEN 'true' ELSE 'false' END
FROM cte
WHERE n <= 2
GROUP BY ProductID

SQL Fiddle

Output:

ProductID   CCVPassed
101 4       false

Note:

It partitions by ProductId because it supposed you have several ProductId in your real table.
Replace true/false by 1/0 or whatever is needed in the real query


Yes.  Simply grant the user that you create that lets them log into the SQL Server SELECT rights only on the view and not the table.

You could actually do all this through a single view.

Create a function that looks up the CustomerId based on the username.  Then call that function as part of the view.

create view YourView as
select *
from YourTable where CustomerID = dbo.YourFunction(suser_sname())


Any time you want to access a value more then once and you need to be sure that that value did not change in the mean time, you need to include the two accesses in a single transaction. But even more, you also need to make sure that appropriate locks are held.

If you are doing a SELECT followed by an UPDATE in a separate statement, you need to either switch the transaction isolation level to repeatable read or serializable or, even better, supply the WITH(UPDLOCK) hint on the first select. The UPDLOCK hint expresses the intend to later update this row. It will allow concurrent reads but prevent any other type of update request.

In SQL Server, other than in some competing RDBMSs, a stored procedure does not automatically provide transaction scope, so even with statements inside a procedure you still need to manage a surrounding transaction, either by always calling the procedure inside a transaction or by opening and committing a transaction inside the procedure.

All that being said however, in your case you don't even need multiple statements. Just combine the UPDATE with an OUTPUT clause:

UPDATE dbo.tblSystem
   SET LongValue += 1
OUTPUT INSERTED.LongValue
 WHERE [Key] = @Key;

Each statement in SQL Server is automatically executed within a "statement transaction" (no matter if called inside a procedure or not). Because you are using the UPDATE statement, SQL Server also takes care of the appropriate locking level. Therefore this is "threadsafe".

As an added bonus it should also execute faster.

Here is a SQL Fiddle to demonstrate this

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.tblSystem([Key] INT PRIMARY KEY CLUSTERED, LongValue BIGINT);

INSERT INTO dbo.tblSystem VALUES(42,0);

GO
CREATE PROCEDURE dbo.GetNextValue
  @Key INT
AS
BEGIN
  UPDATE dbo.tblSystem
     SET LongValue += 1
  OUTPUT INSERTED.LongValue
   WHERE [Key] = @Key;
END;

Query 1:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         1 |

Query 2:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         2 |

Query 3:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         3 |

Query 4:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         4 |


Something is totally wrong with my query. .. Slow. .. Why?

That query seems to fetch 4 columns from one particular row of one table:
select ((four columns))
    from        item_ledger_entry as z
        where  ( z.orgLine = l.id OR z.cargoLine = l.id )
          AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1

Correct?  If so, I suggest:
SELECT ....
    FROM ( SELECT a,b,c,d
               FROM item_ledger_entry z
               WHERE (   z.orgLine = l.id
                      OR z.cargoLine = l.id )
                 AND e.docNr = l.docNr
               ORDER  BY z.id DESC LIMIT  1
         ) AS z2
    JOIN FROM cargo_line_receive AS l
                     ON ( z2.orgLine = l.id
                      OR  z2.cargoLine = l.id )

    LEFT JOIN ...

That will help performance some.
The OR hurt performance; you use it often.  Is there some way to avoid it?  I hesitate to mention using UNION because I am not sure the transformation will work easily.
WHERE  h.clientCode = '999999999999'
AND h.agreements = 'LIG00067'

begs for h to have INDEX(clientCode, agreements).  (The order of those columns does not matter.)
GROUP  BY l.id

worries me.  It sounds like the "explode-implode" scenario.  Note that (at least logically), first all the JOINs are done.  This produces (potentially) a much larger table that any of the original ones.  Then the GROUP BY implodes down to the original size.  The typical remedy is to get the l.id values you ultimately need, then do the JOINs.
The high "Rows" for e confirms that OR is a big part of the performance issue.  It is doing a full table scan -- probably repeatedly -- because of
ON e.cargoline = l.id
OR e.orgline = l.id

But, before I address that, Please justify or get rid of LEFT.  Keep in mind that JOIN matches up rows from the two tables, whereas LEFT JOIN also takes any missing rows from the 'right' table, supplying NULLs to indicate that the row is missing.
If JOIN is warranted, then this might 'save the day':
JOIN  ( ( SELECT  FROM item_ledger_entry
            WHERE  e.cargoline = l.id )
        UNION DISTINCT
        ( SELECT  FROM item_ledger_entry
            WHERE  e.orgline = l.id )
      ) AS e

instead of
LEFT JOIN item_ledger_entry AS e
ON e.cargoline = l.id
OR e.orgline = l.id

Note, e will need two separate indexes:  INDEX(cargoline), INDEX(orgline)
(I'm sorry, but your query is so convoluted that each of my suggestions may fail for some reason.  You can add the indexes safely; at worst, they might not 'help'.)


Explaining the significant parts of that EXPLAIN:

Go to table TA and use FULLTEXT search.  I would expect this to be first since MATCH..AGAINST takes priority over other clauses in WHERE.  It estimated that "1" row would be found.  That is an estimate, regardless of the Engine.  (There are a few cases where it will be exact.  Mahesh's comment refers to SHOW TABLE STATUS.)
Reach into table TB via the PRIMARY KEY.  The PK consumes up to 752 bytes.  (Perhaps VARCHAR(250) CHARACTER SET utf8?).  It is using gensurv_isikota.TA.BusinessID for the value.  It expects to get 1 row.  (It can't find more than 1, since a PK is UNIQUE.)

Missing from the EXPLAIN is any useful hints about the rest of the WHERE (MBRContains and Prominent>15) other than to say "Using where".

Also missing is anything about the ORDER BY.

"Using temporary; Using filesort" is unnecessarily scary.  It means that it had to create a temporary table and sort it.  But it does not necessarily mean that it touched the disk.  It will first try to do the tmp table as a MEMORY table.  If that fails, it will use MyISAM, and probably hit the disk.  If it truly was only 1 row (or even 1000), then this step will be so fast as to be inconsequential.

But your real question is "why 12 seconds"?

First, find out how many rows MATCH 'res*'.  The speed of that query depends primarily on how many rows.

Next, provide SHOW TABLE STATUS and SHOW CREATE TABLE for each table.  Also, provide SHOW VARIABLES LIKE '%buffer%'.  I suspect you have default cache sizes -- see http://mysql.rjweb.org/doc.php/memory .

Another thing to note.  The query must gather all possible rows before it can start the ORDER BY.  Even if you added a LIMIT.

How to speed it up?

IF you are pretty sure that the MBRContains will filter down to fewer rows than the MATCH, then use a subquery to find ids from the MBR, then JOIN to the rest.  This might be faster.

Another thought...  Two subqueries of the form

    SELECT ...
    FROM ( SELECT id... MBRContains ...) s
    JOIN ( SELECT id ... MATCH ... ) f  ON f.id = s.id
    JOIN other tables, etc...

But that gets messy and iffy.


Can I safely delete .tmp files from a FoxPro 2.5?

You can safely delete them, if they are in use you won't be able to delete them because they will be open exclusively. Foxpro usually creates these temp files when creating temporary cursors during program execution. Normally it should clean them up when the program terminates, but if it crashes these files will be left over.


Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

Some of this information like index depth can be found in DMF dm_db_index_physical_stats().  Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
ixs.index_type_desc,
ixs.index_depth,
ixs.page_count,
ixs.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ixs
INNER JOIN sys.indexes ind
ON ind.object_id = ixs.object_id AND ind.index_id = ixs.index_id;

More information about this DMF can be found here https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15


Do I need to ANALYZE table after DROP INDEX?

No, it's not necessary. Statistics on the main table are not affected by indexes at all.
One thing to observe: expression indexes introduce new "derived" columns, and Postgres gathers separate statistics for those. Removing such indexes also removes the special statistics - which may lead to different query plans, beyond the fact that the index is gone.
Since you mention indexes on boolean columns: those hardly ever make sense and it's a good idea to remove those. boolean column typically only make sense as condition in a partial index. There is an example in the manual.


SQL server not listing records with NULL values

The reason for this is that a NULL represents an unknown value.

NULL is not LIKE anything and not NOT LIKE anything either.

Try running the following 2 statements on your system:

SELECT CASE WHEN null like '%a%' THEN 'true' ELSE 'false' END;
SELECT CASE WHEN null not like '%a%' THEN 'true' ELSE 'false' END;

That should return false for both statements.

So your query should look like this:

select * from sys.dm_exec_requests where session_id > 55
and (wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null)


Problem getting SQL Server 2016 R Services (In-Database) working

We wound up having to copy two folders (MSSQLSERVER01 and MSSQLSERVER02) from our Sql Server install directory, which for us was located here

E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ExtensibilityData

We copied the above folders to the new WORKING_DIRECTORY (C:\TEMP\R_SERVICES) we had specified in the rlauncher.config file.

After that, we were able to run the sample R_Services testing query

exec sp_execute_external_script  @language =N'R',
@script=N'OutputDataSet<-InputDataSet',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
go


High availability on a subset of the data

Switch the partition to a separate table in the main database, then use bulk copy to move those rows into the secondary database.  ALTER TABLE ... SWITCH can be used to make meta-data only moves between tables, not just partitions within a table.


CLI was the magic keyword which allowed me to locate:
Obtaining the IBM Data Server Driver for ODBC and CLI software
However, it is not updated as the search criteria ended up as:

Several steps later, you can finally locate the fix pack:

and several steps later download the file:
v11.5.5fp1_ntx64_odbc_cli.zip
The install, however, isn't straight forward, thus requires a guide:
Installing the IBM Data Server Driver for ODBC and CLI software on Windows operating systems
To streamline the process, it seems you have to write your own installer.


Single query to get data from multiple tables in one database

SSMS has a feature called Server Groups, which allows exactly that functionality.

As in the above image, add a new Server Group, then add each target SQL Server to the group.

Then right-click the newly created group, and choose "New Query".  Enter the following into the query window:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + 'USE ' + QUOTENAME(d.name) + ';
SELECT SECDB = ''' + d.name + '''
    , g.*
    , gd.*
FROM dbo.tblConnGroups g
    INNER JOIN dbo.tblConnGroupsDet gd ON g.ID = gd.GroupID
ORDER BY gd.GroupID, gd.DBName;
'
FROM sys.databases d
WHERE d.name LIKE '%_Sec';

EXEC sys.sp_executesql @cmd;

The code above will dynamically execute your query against any/all databases named like *_Sec on all SQL Servers in the group.


First thing is to make sure you DO NOT detach that database.

Restoring from the last known goodbackup is fine. Otherwise you will need to use the EMERGENCY repair mode (I am assuming you are running SQL 2005 or higher). Here are a couple of posts from Paul Randal on the subject. Read them both before you start taking any action.

Creating, detaching, re-attaching, and fixing a SUSPECT database

EMERGENCY-mode repair: the very, very last resort


Restrict SQL Server 2008 R2 User to restore only one database (but not others)

Log in as a server admin.
Create a stored procedure with execute as owner that restores a particular database.
Assign permission to execute it to the user.

As for where to put that stored procedure, I'm not sure it's possible to restore the DB from which you are running the SP, in which case you might want to create a completely blank database for the only purpose of storing this stored procedure.


Index fragmentation increase as more row being deleted

This isn't fragmentation.

Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.

If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.

Do you have an index on CreationDate
Do you have delete cascades?
Is the TOP 1000 in a single transaction?

For point 3, doing a loop inside a transaction is pointless: is this it?

At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.

Other options:

why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
use SYNONYMs for poor man's partitioning


Can SQL Server be configured to not silently truncate VARCHAR values?

No there's no way to change this behavior.  But this truncation only happens with variable and parameter assignments, not table inserts.

EG

declare @str varchar(8) = 'some very long string'

will be silently truncated

but an insert:

create table #t(a varchar(8))
insert into #t(a) values('some very long string')

will fail with

Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.

This is a consequence of TSQL's permissive use of implicit conversions, which is convenient in many cases, but sometimes troublesome.


In mongodb, is "sh.getBalancerState() is false" the same as "sh.isBanlancerRunning is false"?

Nothing bad happened even if you took the config server offline during a chunk migration. In order for a chunk to be marked as migrated, all three config servers need to be up (Contrary to popular belief they do not form a replica set.).

(The following is a tiny bit simplified for the sake of shortness.)
When a chunk is moved, a global (read cluster wide) lock is written to the config servers. Then, the chunk gets copied over to the target machine. Next, the metadata gets updated so that every mongos asking for data contained in that chunk is pointed to the new server. Only after a successful metadata update, the chunk gets deleted on the old machine. So if anything goes wrong during the chunk migration, the metadata will always point to the correct location, while the lock prevents any data to be written to the chunk. I am not sure when the lock is lifted, but iirc it is done after the metadata update.

So – since all three config servers need to be online for metadata updates – as soon as you put a config server offline, you prevent any chunk migrations from taking effect.


How to monitor a single db of an instance in SQL Server

You can accomplish this with the SQL Server Profiler.

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

First you will want to create a trace.

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

Then you will want to specify the events you want to track.

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

Then setup a filter for the database name.

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


SQL Server Long Running Transaction - WAITFOR(RECEIVE conversation....DatabaseMail)

From my experience, you should ignore it. This query is the one that comes from the program that uses SQL Broker. It's normal. The program is simply waiting on SQL Broker to notify it this way. (Of course waiting on SQL Broker to notify will generate a long running wait.)
We are using the Broker and I have the same thing.


What is the best index implementation for a really large databse

Can i replace the non-clustered indices with one covering index ?

No. Suppose you sometimes find people by LastName and sometimes by FirstName. An index on (LastName, FirstName) won't help you find people by FirstName.

  If not, is there any way to get rid of RID Lookup rather than a covering index ?

Not in a way that's particularly useful.

  Does each query require a different covering index depending on the columns in the select list and the search conditions ?

Pretty much. Your job is to come up with a compromise.

  I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?

A PK doesn't necessarily mean a clustered index. And having a CIX just means your RID Lookups will become Key Lookups, which are potentially worse. But without CIXs you have heaps which can fragment when you change or delete data. So CIXs are fine, but won't improve performance of your Lookups.


track down which application / procedure / function modified the value of a column in a specific table

This might get you going along the right track. You pass the sql_handle of the existing connection to the table valued function sys.dm_exec_sql_text, and it returns the SQL text being executed.  If you run this example you will see your own code, as @@SPID is your own process ID.

Source Article

SELECT t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = @@spid

Other system resources that may help you on your quest:

select * from sys.dm_exec_connections where session_id = @@spid
select * from sys.dm_exec_sessions where session_id = @@spid
select * from sys.dm_exec_requests where session_id = @@spid


What does k=v do in the context of INSERT INTO tbl VALUES (expr)

When you created the table with this command

CREATE TABLE foo ( x varchar(255) );

columnx was defined with an implicit default of NULL.

When you compare a non-existent variable with a real value as you did before you get NULL.

When it comes to the expression you gave, x=5, you are asking if NULL equals 5. In terms of symantics, NULL cannot by compared with anything:

mysql> select NULL = 5;
+----------+
| NULL = 5 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

not even with NULL itself

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

It needs a content context (if that makes any sense).

Anyway, when you tried to evaluate x=5, that gets processed internally as evaluating x, which of course, is NULL with the row coming into existence, followed by making a comparison with a non-NULL value of 5. Since the expression must evaluate as NULL, you essentially did this:

mysql> insert into foo values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql>

which has the same effect as as doing this

mysql> insert into foo values ();
Query OK, 1 row affected (0.01 sec)

mysql>


MySQL - Are column names stored in each row in full length?

The answer is of course an emphatic no. It would be a very silly thing to store them in each and every - of the possibly billion - rows.

Column names are metadata and are stored only once, in the system tables.

The format of the rows and how they know where a column ends and the next starts depend on the engine of the table. It differs between InnoDB, MyISAM, Memory, etc. Check the respective documentation for more details.

Having said that, nothing prevents you from writing - and using - an engine of your own that stores the column names in each and every row. It would still be a very silly thing to do. You should also consider where the names will be kept when the table has 0 rows, eg. immediatey after creation of the table or after it has been truncated.


Seting MAXDOP for SQLserver 2016 and above

With Hyperthreading enabled, you currently have 4 hard NUMA nodes with 10 physical cores and 20 logical cores each. On SQL Server 2012 and 2014 you will have 4 matching soft NUMA nodes with 20 logical cores each.

Upon upgrading to SQL Server 2016 or higher, auto soft NUMA will automatically kick in. You'll now have 2 soft NUMA nodes per hard NUMA node for a total of 8. The soft NUMA nodes will stripe across even and odd CPUs per hard NUMA node. For example, soft NUMA node 0 might contain schedulers 0, 2, 4, 6, 8, 10, 12, 14, 16, 18 and soft NUMA node 1 might contain schedulers 1, 3, 5, 7, 9, 11, 13, 15, 17, 19.

The blog post that you referenced is difficult to understand, but I believe you are meant to fall under the "Server with multiple NUMA nodes" category (you have 4) and the "Greater than 16 logical processors per NUMA node" category (you have 20). The recommendation as a result is "Keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16", so Microsoft is recommending that you change MAXDOP to 10.

That doesn't match my experience with large servers. I would leave MAXDOP set to 8 unless you run into some kind of issue. IMO changing it from 8 to 10 in your scenario isn't meaningful and is just introducing a new change without a clear benefit. I blogged about how I disagree with the published guidance here.


Using a DB2 merge with conditionals for matches?

The manual says WHEN MATCHED does not allow INSERT. However, you should be able to use the expire_date condition in the USING clause:

MERGE INTO products AS P
USING ( VALUES (
...
    )
)
AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
ON s.CUSTOMER = p.CUSTOMER
and s.REGION = p.REGION
and ...
and p.expire_date > current_date
WHEN MATCHED
THEN UPDATE ...
WHEN NOT MATCHED
THEN INSERT ...


Instance and database (Oracle vs SQL Server)

I'm a DBA on Oracle and SQL Server.

The confusion is due to a mismatch of semantic and an extra level in SQL Server's hierarchy but there's more than that!

Note that we will talk only about single instance database in both instances (pun intended).

Instances

In Oracle, one instance will have an amount of memory allocated to it (SGA/PGA) and processes spawned (smon,pmon dw0X and so on). There are system views to monitor all of the stuff that will be created and used (V$ and dba_ views).

This is the structure we are familiar with in oracle.

And you already mentioned it, the database is where the physical files are. Typically moving to RAC makes the difference glaring.

In SQL Server, the instance works the same way:
A bunch of memory is allocated and there are system views (sys.) to monitor all of that stuff.

The instance has 4 system databases:

system where the system views are.
msdb database which also holds system related information. Typically that's where you will find your backups related data. A bit of maintenance is required on it at least to flush old backups records.
model, the aptly named database which is used as a model for every new database you will create. It's a good idea to modify the data file settings (auto extend, initial size, backup mode) so all new database benefit from it.
tempdb developed below.

The system database holds meta data information for every other database on SQL Server.

Temp space

In Oracle:

Temp tablespace (create temporary tablespace).
In oracle the temp tablespace is a spill zone for operations which can't fit in the PGA (process memory, default at 200M (up to 11g), your hash join for example if bigger than 200M spills in temp tablespace, generally that's a cause of slow performing query).

In SQL Server, there is a tempdb database located in the "system databases" section and it's very actively used, much more than in oracle. Place it on very fast disks (SSD or fiber comes to mind)

Thus comes the 2nd large difference:

Objects

In SQL Server:

The objects (table,views), are not tied to a user as in oracle, but rather to a "database".
The "database" holds (not own), the objects. At the creation time of the database, a data file is mentioned to store these objects.

In oracle:

A user creates objects which will land in a schema of the same name. Naming the users carefully becomes important.

And here comes the 3rd big difference:

Security

In oracle:

Users will be created and they will be given the right at the very least to create sessions so they can access objects they have been allowed to.
Or, they will have the right to create objects in their own schema (resource role), which will then create a schema in that name.

You may chose, and it's a good practice, to create a tablespace and make it the  default for each users able to create objects. If that's not done, a default tablespace is used, generally "USER_DATA".

In SQL Server:

Since the objects are not owned by a particular user in a schema, users must be first created, then privilege assigned. Typically, whoever will be the main user to do the object creation will be granted the "owner" role on a given database.

That user in SQL Server would be the equivalent in oracle of a schema owner but there may be more than one owner.

Security is where SQL Server shines over Oracle: SQL Server security can be tied to Active Directory which manages the desktop users. These desktop users can be tied to a user group say "read only". It's possible to add that active directory group to SQL Server and give it a "read only" access to your database.

When a user leaves your company, the Active Directory Admin delete that user and access to the database is lost as well.

Oracle can use LDAP but let's face, it's not very often well tied to the windows desktop users.

The 4th big difference.

Semantic

SQL Server has an extra layer. In a SQL Server database, objects may be organized in schemas. Schemas may have owners too. It introduces more fine grain security as well but while it's used, it's not common. I would say it's also good practice to group your objects by schema early in the development stage to benefit later on from extra security abilities when needed.

Logs / Archived Logs

Finally archived logs work differently in oracle and SQL Server.
SQL Server has a log file created for each database, while oracle has archive log mode turned on at the instance level.

This is where the line blurs. For some aspects, SQL Server's databases really are databases. In other instances they look more like schemas.

The archiving in SQL server is set with the option backup mode set to full. Then they are archived by performing a log backup. The log backup will require a full backup to take place before it starts working.

In Oracle, archived logs are archived automatically, then we back them up to a remote location.

Summary

Oracle:

Instance->User1->Schema1(named User1)->Tablespace1
                                     ->Tablespace2
        ->User2->Schema2(named User2)->Tablespace3
        ->User3->Granted "read only"->schema2.table1

SQL Server:

Instance->Database1->Schema1
        ->Database2->Schema2
        ->User1
        ->User2

        ->User1->Granted "owner"->Database1
        ->User2->Granted "read only"->Database2->Schema2

These are the things that come to my mind when I get to compare both.
I would love to hear more from others and add on/correct this answer.

Also I don't have a particular preference for either.
I think they both do a great job and I enjoy working with both very much.


Using Select Statement to get column name and table name from the same table

Currently there is no syntax directly supporting what you are trying to do. As you probably know, names cannot be parametrised in a SQL statement. That means that when you need to substitute names from column values of another table, you have to use dynamic SQL: first build the query string and then execute it. There is just no working around using dynamic SQL in such cases. Furthermore, you have already established for yourself that you cannot use dynamic SQL in a function. So there you are, seemingly stumped.

However, if you insist on using a single SELECT statement for this, there is one way – provided you agree to bend over backwards slightly to achieve the goal, that is. And accept a major limitation of the method.

The solution involves creation of a loopback linked server and using the OPENQUERY function. But first you will need to make sure your dynamic SQL solution works as it is. For the purpose of this answer, I am going to assume that the dynamic SQL looks like this:

DECLARE @sql nvarchar (max) = '', @sqltemplate nvarchar(max) =
'UNION ALL
SELECT
  Column_Name = ''{Column_Name}'',
  Table_Name  = ''{Table_Name}'',
  Max_Length  = MAX(LEN([{Column_Name}]))
FROM
  [oil stop].dbo.[{Table_Name}]
';
SELECT
  @sql += REPLACE(
            REPLACE(
              @sqltemplate,
              '{Column_Name}',
              Column_Name
            ),
            '{Table_Name}',
            Table_Name
          )
FROM
  tempdb.dbo.YourMetaDataTable
;
SET @sql = STUFF(@sql, 1, 9, '');  -- remove the leading UNION ALL
EXECUTE sp_executesql @sql;

Once you have verified the script is working, and made sure the loopback linked server is created, just put the script inside the OPENQUERY function like this:

SELECT
  *
FROM
  OPENQUERY(
    YourLinkedServerName,
    '...'  -- the dynamic SQL script
  )
;

Remember to double each quotation mark (apostrophe) inside the script.

One other important change you will likely need to make is to add a WITH RESULT SETS clause to the EXECUTE statement to describe the result set, so that OPENQUERY can process the output correctly for you. When describing the result set, you will likely just repeat the same type for Column_Name and Table_Name as defined for them in the metadata table. For the example below I am assuming the type to be sysname in both cases. And as for the Max_Length column, I believe int would work well there. So, the modified EXECUTE statement would look like this:

EXECUTE sp_executesql @sql
WITH RESULT SETS
(
  (Column_Name sysname, Table_Name sysname, Max_Length int)
);

For completeness, and to make the lack of elegance in this solution more evident for the wider audience, this is what the final query would look like:

SELECT
  *
FROM
  OPENQUERY(
    [OIL STOP],
    'DECLARE @sql nvarchar (max) = '''', @sqltemplate nvarchar(max) =
    ''UNION ALL
    SELECT
      Column_Name = ''''{Column_Name}'''',
      Table_Name  = ''''{Table_Name}'''',
      Max_Length  = MAX(LEN([{Column_Name}]))
    FROM
      [oil stop].dbo.[{Table_Name}]
    '';
    SELECT
      @sql += REPLACE(
                REPLACE(
                  @sqltemplate,
                  ''{Column_Name}'',
                  Column_Name
                ),
                ''{Table_Name}'',
                Table_Name
              )
    FROM
      tempdb.dbo.MetaData
    ;
    SET @sql = STUFF(@sql, 1, 9, '''');  -- remove the leading UNION ALL
    EXECUTE sp_executesql @sql
    WITH RESULT SETS
    (
      (Column_Name sysname, Table_Name sysname, Max_Length int)
    );
    '
  )
;

The main problem, though, is that the query above still cannot be parametrised, and that is the principal limitation I was talking about. Even though the OPENQUERY script is specified as a string literal, it can only be a single string literal – not a variable, not a complex expression. That means that if you want to apply the query to a different subset of rows of the metadata table, you will have to use a new script for that.


Apache Foundation maintains a SQL parser called Calcite. It can comprehend several dialects including MySQL and Postgres. You could use it to translate a view from both sources to their tokenised parse trees, then compare the trees for interesting differences. Even easier would be to convert the trees back to SQL in  common dialect and perform a string compare on the two outputs.


In PostgreSQL URL I can't use a password containing special characters

You have to use URI escapes for all problematic characters.

For this user:

CREATE ROLE "weird@name" PASSWORD '\/ @&?' LOGIN;

You can login like this:

psql postgresql://weird%40name:%5C%2F%20%40%26%3F@localhost:5432/test

The documentation has it:

  Percent-encoding may be used to include symbols with special meaning in any of the URI parts, e.g. replace = with %3D.


There are only 4 steps to enable service broker for a Database and non of them use logical file name as parameter

Try this

USE MASTER
GO

IF EXISTS ( SELECT  is_broker_enabled, Service_Broker_Guid,*
            FROM    sys.databases
            WHERE   name = 'DB2'
                    AND is_broker_enabled = 0 )
BEGIN

    -- Step 1 ---  Rollback running transactions and brings the database in a single user mode.
    ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    -- Step 2 --- To avoid distributed conversation and OLD database GUID conflicts.
    ALTER DATABASE DB2 SET NEW_BROKER;

    -- Step 3 --- Set the is_broker_enabled flag to true
    ALTER DATABASE DB2 SET ENABLE_BROKER;

    -- Step 4 --- Brings the database back to Multi user mode and allow shared locks\connections.
    ALTER DATABASE DB2 SET MULTI_USER WITH ROLLBACK IMMEDIATE

END


Quest offers a free version of their Benchmark Factory that works for Oracle, SQL Server, and MySQL:

http://www.quest.com/benchmark-factory/

I recorded a video on getting started with it back when I worked for Quest:

http://www.toadworld.com/Videos/tabid/831/TID/559/cid/161/Default.aspx

You can call your own stored procs, replay traces, run TPC benchmark scripts, and more.  It's not exactly easy to use, though - thus the video.


In the version 1.8, the backup was optional as the documentation says. That version is quite old, the current version is 3.4 where backup is included to ops manager package.


Automatic migration failed on Azure Database

The simple answer is don't use Entity Framework for this sort of thing. You need to write a SQL migration script, test it, and then deploy it onto your production system.  And yes - changing a table with 3M rows will take more time and resources than migrating a table with 3 rows.

There are a few techniques for achieving this. First, judging by the error message, you're trying to do this in a single transaction. Better to add a new column of the correct precision, copy values to it in batches of, say, 50,000 at a time, then drop the old column and rename the new one.

If the system has to remain operational during the migration you can get creative with views and triggers.


How to hot-backup mysql slave database with read operations

Here is how I would do it (Similar to your suggestion)

Terminal 1: FLUSH TABLES WITH READ LOCK It is important to keep this session/terminal open till the end of the process. Wait till you get the message the tables are now locked.
Terminal 2: Create the snapshot. Depending on what technology you use, this may vary. LVM snapshot is a common one.
Terminal 1: UNLOCK TABLES
Copy the files from the snapshot location

It is essential not to close terminal 1 before you are done with step #2.

Notice please that there is no need to stop the slave.

This link would be very helpful. It explains with more details, using LVM.

HTH


What's the best way to backup DB files via network on Linux and Solaris machines?

Well theoretically you can issue the command "alter tablespace begin/end backup". Before and after you execute rsync. You will also need a backup copy of the control file. This approach was used many years ago before RMAN was introduced. The restore really requires Oracle experience. This approach is for example described in this book: "Unix Backup and Recovery".

A really recommend you to get familiar with RMAN. Even if you do not use backup agent like TSM/Netbackup you can still create local disk backups using RMAN and then you can synchronize this storage using rsync.


MySQL Read lock and query performance analyses

Don't use "prefix indexes" (status(3)); they usually hurt more than they help.
Shrink the table size...
What kind of values are in id VARCHAR(32).  (It is unusually to have such a bulky PRIMARY KEY.)
Change status from a multi-byte varchar to a 1-byte ENUM.
If there are other columns or indexes in the table, we need to see them in order to understand whether there are side issues.
What is the value of innodb_buffer_pool_size?  How much RAM do you have?
The query will (based on the EXPLAIN) scan through the table until it finds the first entry with "UNASSIGNED".  Without knowing the distribution of the data, I can't say when that would occur.
Eliminating the prefixing may speed things up.
Saying SELECT id instead of SELECT * may speed things up.
(I don't know how whether the rows before the first "UNASSIGNED" will be locked.)


Why are Denali sequences supposed to perform better than identity columns?

I'll answer here as well.  It has to do with the internals of how IDENTITY and SEQUENCE work.

With IDENTITY, SQL Server pre-caches values into memory so that they are readily available. See Martin Smith's answer for the details. As values are used, a background process generates more values.  As you can imagine this pool can run out pretty quickly, leaving the application at the mercy of the background process that is generating the values.

With SEQUENCE, SQL Server allows you to define how large the cache should be.  While SQL Server doesn't actually keep the values in the cache, it only keeps the current value and the top end value, this will greatly reduce the amount of IO that is needed to create values.

Don't set the cache too high, as this will reduce the number of numbers which can be used: if SQL Server were to crash, any values specified in the current cache range which weren't used would be lost.

As for row insertion, just specify a default value for the column, like so:

DEFAULT (NEXT VALUE FOR Audit.EventCounter),


Master-Master on a VIP with a multiple Slave

Do not connect a Slave to a pair of Masters via a VIP.

There is nothing to keep the binlogs synced up, so a Slave must communicate with only one Master.  (Multi-source Slave is a different topic.)

You could, however, have half your slaves connected to one Master, and half to the other.  Then if either Master goes down, you lose only half the Slaves.

If you are trying to limit the configuration to only 3 servers, then consider Galera (in PXC or MariaDB).  It has a lot more advantages than M<->M->S.

Even with multiple Slaves, consider Galera with 3 nodes, then evenly distribute the slaves off the Cluster nodes.  Now a single Master failure will lead to losing only 1/3 of the Slaves.

(Actually, you don't lose the Slaves of a dead Master, but they do get stale.)


Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL

try this one

CREATE OR REPLACE TRIGGER T_Insert
AFTER INSERT ON tag

for each row
declare
  v_cnt number := 0;
BEGIN
  -- selection number of rows
  -- where tag = :new.tag and AND brand_id = :new.brand_id
  -- the result will be stored in a variable v_cnt
  -- if there no rows matched the Count(1) will return 0
  Select count(1)
    into v_cnt
    from my_tags
   where tag = :new.tag
     AND brand_id = :new.brand_id;

  -- check if some rows Exits
  -- than do an update
  -- else do an insert
  if v_cnt > 0
  then
    UPDATE my_tags set count = count + 1
    -- if you want to update only one row
    WHERE tag=:new.tag AND brand_id=:new.brand_id
    ;

  ELSE
    insert into my_tags
      (sr_no, tag, count, brand_id) values
      (:new.sr_no, :new.tag, 1, :new.brand_id);
  end if;

END;


There is a MS KB article on it which say it happens because

  The full-text catalog folder is either deleted or corrupted.
  You did not enable the database for full-text indexing.
  The database is restored from a Microsoft SQL Server 2000 database backup. Therefore, the folder of the full-text catalog in the database does not exist on the server where you restore the database.
  The instance of SQL Server 2005 that you are running was upgraded from SQL Server 2000. However, the full-text search service cannot be accessed during the upgrade.
  The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

The article also has a "workaround" section


How can I tell when InnoDB recovery is complete/check its progress?

The right way to get MySQL status regarding recovery is monitoring its error log. There are 3 phases on InnoDB recovery:

Checking partially written pages (very fast)
REDOing committed and uncommitted transactions written to the transaction log. This can take some time, but its time is IO bound (as fast as reading/writing to the transaction log). If you are concerned about recovery time -and not about writing performance-, you can reduce the transaction log file size (or put those files on a faster media). After this, InnoDB will allow MySQL start accepting connections, so the server will continue as usual.
Rollbacking uncommitted transactions (potentially very slow). For example, if an alter table failed, it will have to rewrite all modified pages to its original state, but the server is up and running. The larger is your buffer pool, the faster this will execute.

All this phases are marked on the log.

There are not many 3rd party tools to monitor this. One I can think about is Percona XtraBackup, that provides you a percentage of the replayed log  but a) this is a backup/recovery tool for InnoDB and b) it does its own recovery process, independent of the mysql service (you cannot run both at the same time). It may not be what you are looking for, but it may allow you to do the recovery with a copy of the data to make sure it runs before starting mysql again.

Having said that, InnoDB has been very resilient to me, and the only reasons why recovery was to be done in a more "forced" level is because of harware/filesystem level corruption, or InnnoDB misconfiguration, never on normal operation. A different thing would be it taking a long time -rollbacking can be very expensive, an in some cases it is faster to recover from a backup than doing everything transactionally (DROP large_table).


SQL Server 2016 - Is the Microsoft R Client any different from a standalone Microsoft R Server?

Looks like I found an answer to my own question on digging further.

While Microsoft R standalone Server addresses the in-memory limitations of open source R by adding parallel and chunked processing of data, Microsoft R Client is in-memory bound; i.e. it can only process datasets that fit into the available local memory. However, it can operate on large volumes of data if the compute context is set to an instance of Microsoft R Server like the SQL Server R Services or R Server for Hadoop etc.

Microsoft R Client has almost all of the architectural features as a MS R Server - DistributedR, ScaleR (RevoScaleR package), ConnectR. DeployR is unavailable in the MS R Client.

Microsoft R Client also has the Math Kernel Library for the non-scaleR functions, and can use up to 2 threads for ScaleR functions in a local compute context.

One advantage that the MS R Client has on the MS R Server is that it is free for everyone. MS R Server (standalone) requires a commercial license.

More details here: The Microsoft R: A Product Comparison


In order to achieve it, you'll need to capture this information at run time using DMV's or Extended Events.


You cannot do encryption with express edition but can you can do it by other way

or file encryption only. For more reference follow http://sqlmag.com/database-security/sql-server-encryption-options.


Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error?

Sounds like you want a FOREIGN KEY constraint!

You should do something like this:

CREATE TABLE B (primary_key_value INTEGER PRIMARY KEY... other fields);

CREATE TABLE A (primary_key_field PRIMARY KEY, foreign_key_value INTEGER, other fields...
CONSTRAINT 'name_of_fk_constraint'
FOREIGN KEY (foreign_key_value) REFERENCES B (primary_key_value);

If you try to INSERT a record into table A and the foreign_key_value field doesn't contain the value you're trying to insert in table B's PRIMARY KEY field (it can also be UNIQUE and NOT NULL), you will get an error and your INSERT will fail.

The classic example of this is the orders table and its child the order_line table.

CREATE TABLE orders (order_id INTEGER PRIMARY KEY, client_id, other_fields...);

CREATE TABLE order_line (order_id, part_name, part_id, other_fields...
CONSTRAINT 'order_id_fk' FOREIGN KEY (order_id) REFERENCES orders (order_id);

The client_id field in the orders table could (and indeed should) be a FOREIGN KEY into the client table - the critical thing about FORIGN KEYs is that they establish relationships between the different tables so that you have consistently correct data with the correct client per order and every order_line belongs to one and only one order. Do NOT be tempted to eliminate FOREIGN KEYs for "so-called" performance reasons - they are VITAL for a functioning database system.


Cancelling / Stopping ALTER INDEX REORGANIZE

Yes, you can stop a reorganize and it won't cause a big rollback like you are talking about.  You will be left with where the operation left off (that's a good thing).  It's a rebuild that would have the rollback behavior.


List Postgresql foreign data wrappers without the use of the `psql` command

If you want to find out which SQL queries psql uses, start it with the --echo-hidden  parameter, then run the psql command you are interested in, and it will show the query on the console:

$ psql --echo-hidden
psql (11.4)
Type "help" for help.

postgres> \dew
********* QUERY **********
SELECT fdw.fdwname AS "Name",
  pg_catalog.pg_get_userbyid(fdw.fdwowner) AS "Owner",
  fdw.fdwhandler::pg_catalog.regproc AS "Handler",
  fdw.fdwvalidator::pg_catalog.regproc AS "Validator"
FROM pg_catalog.pg_foreign_data_wrapper fdw
ORDER BY 1;
**************************

   List of foreign-data wrappers
 Name | Owner | Handler | Validator
------+-------+---------+-----------
(0 rows)

This works for all internal commands that show information from the system catalogs.


MySQL query to select a record from each group that meets a user-defined criterion

select the record which the highest value for the weight field in each group.

WITH cte AS (SELECT id, categoryID, weight,
                    ROW_NUMBER() OVER (PARTITION BY categoryID ORDER BY weight DESC, id) rn
             FROM sample_table)
SELECT id, categoryID, weight
FROM cte
WHERE rn = 1;

fiddle
I.e. we enumerate rows in a group by categoryID ordering them by weight decrease, then get 1st row in each group.


History of database restore completion times?

This information is not tracked in MSDB, so there's no way to find it there (unless the restore operation is in its own step in a job, in which case you could get it from msdb.dbo.sysjobhistory).

If you can't rely on job history, and it was recent enough, you can approximate it from the default trace, with the assumption that the system does something to the database as soon as the restore is completed (which happens in all of my tests, though there may be scenarios where this doesn't happen, such as restoring with norecovery or restoring a read-only database - both scenarios I didn't test):

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT *, rn = ROW_NUMBER() OVER
  (PARTITION BY DatabaseName ORDER BY StartTime)
INTO #blat
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE DatabaseName IN (
  N'db1', N'db2' -- , ...
)
ORDER BY StartTime DESC;

SELECT b.DatabaseName, b.TextData,
  ApproximateRestoreTime = DATEDIFF(MILLISECOND, b.StartTime, b2.StartTime)
FROM #blat AS b
LEFT OUTER JOIN #blat AS b2
ON b.DatabaseName = b2.DatabaseName
AND b2.rn = b.rn + 1
WHERE b.EventClass = 115 AND b.EventSubClass = 2
ORDER BY b.StartTime DESC;

GO
DROP TABLE #blat;

(I tried the queries in this Stack Overflow answer, but they weren't quite as efficient - you're more than welcome to try those, too, to see if they better suit your needs.)

One scenario I did test where this strategy does not work is if all of the auto-stats options are disabled - since the activity that seems to happen immediately after restore is some auto-stats operations on system tables like syscolpars and sysschobjs. This doesn't happen - even for system tables - when auto-stats are disabled.

If you have timestamps from subsequent system events immediately following the restore, you will have a rough estimation of how long the restore took, but it won't necessarily be very precise. You don't get this type of more precise information, of course, which is what gets output to the messages tab if you run a restore interactively (and it also gets written to the error log, as long as trace flag 3226 is not enabled):

  RESTORE DATABASE successfully processed 291 pages in 0.021 seconds (108.258 MB/sec).

But you can still pull this info from the error log (again, as long as it happened recently enough that it hasn't been aged away). If you don't have trace flag 3226 enabled, you should see all of the messages like above in the current error log using the following query:

EXEC sp_readerrorlog 0, 1, N'Restore', N'MB/sec';

Now, you need to expand your search set once you've identified the timeframe you're after, since the message(s) above fail to include the database name. So you can trace down three sets of message from the log: when the database was marked as restoring, when the restore was complete, and the message that includes the pages and time (note that the latter might not line up with the delta between the first two if you are trying to get sub-second granularity).

CREATE TABLE #x(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #x EXEC sp_readerrorlog 0, 1, N'Restore', N'MB/sec';
-- repeat for 1, 2, 3, 4, 5, 6 if you want to capture more history:
--INSERT #x EXEC sp_readerrorlog 1, 1, N'Restore', N'MB/sec';
--INSERT #x EXEC sp_readerrorlog 2, 1, N'Restore', N'MB/sec';
--...

CREATE TABLE #y(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #y EXEC sp_readerrorlog 0, 1, N'is marked RESTORING';
-- again repeat for 1, 2, 3, 4, 5, 6 like above

CREATE TABLE #z(LogDate DATETIME, p SYSNAME, [Text] NVARCHAR(MAX));
INSERT #z EXEC sp_readerrorlog 0, 1, N'Restore is complete';
-- one more time, you may need to repeat if you want more history

SELECT LogDate, [Text]
FROM
(
  SELECT LogDate, [Text] FROM #x
  UNION ALL SELECT LogDate, [Text] FROM #y
  UNION ALL SELECT LogDate, [Text] FROM #z
) AS xyz
ORDER BY LogDate;

GO
DROP TABLE #x, #y, #z;

If you're only after a single database, or a database name pattern, you could add a second filter to the second and third queries, e.g.:

INSERT #y EXEC sp_readerrorlog 0, 1, N'is marked RESTORING', N'database_name';

If trace flag 3226 is enabled, you will only get the first two messages, and not the third.

If you want to troubleshoot slow restore times, first of all make sure Instant File Initialization is enabled (see here and here), and you can also actively monitor the process (in addition to the STATS option, which doesn't account for undo/redo, VLF work, or MSDB/history maintenance) by enabling trace flags 3604/3605, 3004, and 3014. See this article for more details.


Can I alter ENUM value from lowercase to studlyCase without reindex?

Enumerations are physically stored in the table has a number which maps to the positional value of the enumeration.

You can modify your column to change the enumeration and shouldn't experience issues with stored data values (it is always better to be safe than sorry - make a backup of the table just in case).

Enumerations come with disadvantages that you might not be aware of.  The following is a stackexchange.com link discussing the merits of enumerations.

Advantages and Disadvantages to using ENUM vs Integer types?

Here we create a table with one column of enumeration.

mysql> create table mathisr.test (c1 enum  ('webmasters', 'stackexchange', 'stackoverflow'));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webmasters    |
| stackexchange |
| stackoverflow |
+---------------+
3 rows in set (0.00 sec)

Notice, the enumeration presents new values after we modify the column to express the value with different case.

mysql> alter table test modify c1 enum   ('webMasters', 'stackExchange', 'stackOverflow');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webMasters    |
| stackExchange |
| stackOverflow |
+---------------+
3 rows in set (0.00 sec)

The following command demonstrates adding a value within the enumeration...

mysql mathisr> alter table test modify c1 ENUM  ('webMasters', 'stackExchange', 'somethingNew', 'stackOverflow');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------+
| c1            |
+---------------+
| webMasters    |
| stackExchange |
| stackOverflow |
+---------------+
3 rows in set (0.00 sec)

When we look at the data we will see MySQL actually changed the stored number to accommodate the new value inserted into the middle of the enumeration.  The introduction of the new value would have had indexing implications.

mysql> alter table test modify c1 tinyint;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| c1   |
+------+
|    1 |
|    2 |
|    4 |
+------+
3 rows in set (0.00 sec)


Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

A primary key will enforce uniqueness in your data, which is almost always highly desired.  One of the issues you'll run into if you allow yourself to have multiple copies of the same record is that any results you have will be skewed to the point of being unusable for many purposes.  Also, this will happen, no matter how much you try to say it won't.  You can either use a primary key or a unique index for this purpose, and a primary key in SQL Server, by default on a table without a clustered index, will create a unique clustered index.

The rule of "clustered index is not a good choice on columns that undergo frequent changes" is misquoted here.  You appear to have records that have a static value for date, you're just adding more records every day that have a new date field.

The date field is actually perfect as the first key field of your clustered index for a couple reasons.  It's added in order, so inserts will be quicker and cause less fragmentation.  Also, you always query by it, so queries will be able to take advantage of it.  Just add the key columns to the clustered index until you can make it unique and make it a unique index (primary key is optional, but preferred).

What the clustered index is going to do for you is actually store the data sorted by the key fields.  A phone book is nothing more than a clustered index with the first key field of "Last Name", and you're able to find the person you're looking for very quickly.  In your case, you're adding data according to the date making it easy for SQL Server to keep it sorted this way, then you're querying by the date making it so SQL Server can find the rows in question as quickly and efficiently as you look up names in the phone book.

To learn more about indexes, you can read my blog post on the topic here: http://simplesqlserver.com/2013/11/20/indexing-fundamentals/


How to model complex database role relationships

Here is another option.

Users

user_id (PK)
user_first_name
user_last_name
etc.

Articles

article_id (PK)
article_title
article_body
etc.

Groups

group_id (PK)
group_name

Article_Groups // this ties together the optional M:N articles and groups

group_id (PK)
article_id (PK)

Roles

role_id (PK)
role_description

User_Group_Permissions // users can have ability to view entire groups...

user_id (PK) (FK to Users.user_id)
group_id (PK) (FK to Groups.group_id)
role_id (FK to Roles.role_id)

User_Article_Permissions // or users can be given ability to view single articles

user_id (PK) (FK to Users.user_id)
article_id (PK) (FK to Articles.article_id)
role_id (FK to Roles.role_id)

Minimum cardinalities
Are all 0 on the child side, and 1 on the parent side.

Regarding the INSERTS into User_Group_Permissions and User_Article_Permissions:
I would take care of your permissions logic ("the role a user has for a given article or group of articles can only be given to them from a user who has the necessary role (abilities) to make that possible for the article or articles in question") in your code, since that's a business rule.

But, you could use the roles table to keep track of the levels. For example, you could also do something like this, in order to set a minimum role requirement for that article or group and then handle the ASSURANCE of that in your business rules:

Articles

article_id (PK)
article_title
article_body
**minimum_role_to_add (FK to Roles.role_id)**
etc.

Groups

group_id (PK)
group_name
**minimum_role_to_add (FK to Roles.role_id)**


If a meal consists of nothing but a name, and that name never changes, and there are no two meals that can have the same name, you don't need the meal table, because the name is de facto a primary key.
If any of these conditions are not guaranteed, or you might at some later time add other attributes to a meal (like a boolean column vegetarian), you need a separate table for the meals.


Query is executing without starting the PostgreSQL 9.6 Windows Service

PostgreSQL can be started using different methods.

You (or most probably some tool) are likely to have started PostgreSQL by using:

pg_ctl start -D /path/to/data

(Assuming pg_ctl.exe is in one directory in your PATH)

Note that this doesn't start PostgreSQL as a Windows Service; but it starts PostgreSQL anyhow (if all parameters and settings are correct).

Depending on which installer you used and which settings you chose, the installer created also a Windows Service that should start when your system starts. Normally, when you use this installation method, it also starts the service.

If you can restart your machine, it most probably will start the PostgreSQL service in the normal fashion. If it doesn't, it is probable that some other of your services is starting PostgreSQL behind the scenes.

We can probably be more precise if you let us know which installer you used (EnterpriseDB PostgreSQL installer tends to be the most commonly used for Windows).

References

pg_ctl
PostgreSQL Windows Installers


How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?

The problem came from an insufficient privileges on the server where the Access file is stored. Here's how you should configure your server depending of the login type that you are using:

If you are using SQL Server login:

Give to the SQL Server Database Engine service account permissions to read and write (depending if you want to make CRUD on that file) on the folder where the Access file reside.

If you are using Windows authentication login:

Give to this user login permissions to read and write :

on the folder where the Access file reside.
on the temp folder of the SQL Server Database Engine user account. For exemple, if the SQL Server Database Engine service account is domain\sqlEngSvc, the read/write permissions should be set to the C:\Users\sqlEngSvc\AppData\Local\Temp folder.


The two major benefits tend to be efficiency and flexibility.

Streams is a much more efficient architecture.  Rather than synchronously capturing changes in materialized view logs which adds overhead to the transaction, Streams mines the redo logs that are generated by the change in order to generate the change vector to send to the target system.  That also allows the change to be replicated to the other system(s) much more quickly.

Streams is a much more flexible architecture.  You can write your own custom apply processes which makes it relatively easy to inject additional processing or logging, to ignore certain changes, etc.  You can send a Streams logical change record (LCR) to a JMS queue or to a non-Oracle system (though, of course, you'd need to write code to apply the LCR on the non-Oracle system).


It sounds like the user that's executing the ALTER TABLE and the user who's running SHOW PROCESSLIST are not the same user, and the second user lacks the PROCESS privilege.

If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using).
— http://dev.mysql.com/doc/refman/5.6/en/show-processlist.html


Backup database by copying the MDF and LDF files

Is there a reason for you to do this? Why don't you use:

backup database x to disk...?

Try at least to list all files of your database:

select
   db_name(database_id) as Database_Name,
   physical_name,
   name,
   state_desc
from sys.master_files
where
   db_name(database_id) ='Your_Database'

After this, set your database OFFLINE (this will stop the database):

alter database [Your_Database] set offline

Then, copy your files with ctrv+c, ArcServe or whatever.
Set it Online again:

alter database [Your_Database] set online


Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC?

Yes.  Minimally you need a Foreign Key and an AFTER INSERT trigger.

eg

use tempdb
go
--create schema custom
go

--drop table if exists custom.AcademicKey
--drop table if exists dbo.Academic
go
create table dbo.Academic
(
   PEOPLE_CODE_ID varchar(20),
   ACADEMIC_YEAR int,
   ACADEMIC_TERM varchar(10),
   CURRICULUM varchar(20),
   constraint pk_Academic
     primary key (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
)
create table custom.AcademicKey
(
   Id uniqueidentifier default (newsequentialid()),
   PEOPLE_CODE_ID varchar(20),
   ACADEMIC_YEAR int,
   ACADEMIC_TERM varchar(10),
   CURRICULUM varchar(20),
   constraint pk_Academic
     primary key nonclustered (id),
   constraint ak_AcademicKey
     unique clustered (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM),
   constraint fk_AcademicKey_Adademic
     foreign key (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
     references Academic (PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
     on update cascade
     on delete cascade
)

go

create or alter trigger Academic_CreateAcademicKey
  on dbo.Academic after insert
as
begin
    set nocount on;

    insert into custom.AcademicKey(PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
    select PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM
    from inserted;
end

go

insert into Academic(PEOPLE_CODE_ID, ACADEMIC_YEAR, ACADEMIC_TERM, CURRICULUM)
values ('P000011111',     1996,          'SPRING',      ' URBAN')

select * from custom.AcademicKey

update Academic set CURRICULUM = 'CHEMIS' where PEOPLE_CODE_ID = 'P000011111'

select * from custom.AcademicKey

delete from Academic where PEOPLE_CODE_ID = 'P000011111'

select * from custom.AcademicKey


Why is SQL Server consuming more server memory?

SQL Server will consume as much memory as you will allow it.  By default, that number would encompass 100% of your numerical memory on your machine.  That's why you're seeing what you're seeing.  If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory.  Then you have SQL Server and the OS battling for resources, and it'll always result in poor performance.

When you set the max server memory configuration limit, you are limiting how much SQL Server can allocate for the buffer pool (virtually where it stores data pages and the procedure cache).  There are other memory clerks within SQL Server, so for your particular version (2008 R2 and below), max server memory just controls the buffer pool.  But this is always going to be the biggest memory consumer.

TechNet Reference on the Effects of min and max server memory

  The min server memory and max server memory configuration options establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine.

As far as the question how much memory should I leave for the OS, that is a commonly debated metric.  It really all depends, mostly on what other processes are running on the server.  Provided it's a truly dedicated server (and that's almost never the case).  I like to see at least 4 GB memory for the OS (and even more on big-hardware boxes).  But most importantly, monitor how much unused memory there is.  Be liberal, and give SQL Server more memory if you're noticing a lot of available (can be monitored through perfmon) and unused memory (of course, always leaving a small buffer for those corner situations).


CPU usage goes up at specific time of day probably because of Mysql

100% CPU very often means one particular query is running.  And it needs a better index.  Or a better formulation.

Turn on the slowlog, with long_query_time = 1.  If you can get to the file, then use mysqldumpslow -s t or pt-query-digest to find the worst coupld of queries.  Then we can discuss what 'composite' index to add (or whatever).

Swapping is terrible for MySQL.  Lower some settings to avoid it.  Let's see my.cnf and RAM size to help advise.


Running the DBT2 test results in 0.00 NOTPM

I have done the following to reproduce the output you are seeing from dbt2.

Installed Centos6.4
Installed MySQL 5.1.69 via YUM
Installed Innotop to view in real time all queries and open transactions

Ran the bench as you did:

[root@centos-cef dbt2-0.37.50.3]# scripts/run_mysql.sh --connections 20 --terminals 5 --time 300 --warehouses 3 --verbose

Innotop only showed 20 active clients but no open queries or transactions

DBT2 provides several logs. On the system I used for testing, there was a main directory /root/output and then a number for each execution.

/root/output/N where N is the execution number.

In the file /root/output/3/client/error.log, I found the following information

Tue Jul 30 16:57:23 2013
Microseconds : 998724
tid:-1511446784 mysql/dbc_payment.c:32
mysql reports SQL STMT: stmt ERROR: 1305 PROCEDURE dbt2.payment does not exist
Tue Jul 30 16:57:23 2013
Microseconds : 998753
tid:-1511446784 mysql/dbc_common.c:97
ROLLBACK INITIATED

Tue Jul 30 16:57:28 2013
Microseconds : 859693
tid:-1511713024 mysql/dbc_new_order.c:88
mysql reports: SQL: call new_order(2, 5, 984, 1, 9, 90056, 2, 8, 47872, 2, 3, 48592, 2, 7, 21925, 2,                                 7, 96574, 2, 5, 53239, 2, 8, 30640, 2, 4, 39619, 2, 10, 86015, 2, 8,                                 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,                          0, 0, @rc),  ERROR: 1305 PROCEDURE dbt2.new_order does not exist
Tue Jul 30 16:57:28 2013
Microseconds : 859735
tid:-1511713024 mysql/dbc_common.c:97
ROLLBACK INITIATED

I'm seeing ROLLBACK INITIATED throughout the log. I am also seeing that certain stored procedures are not installed such as dbt2.payment

So I looked a little further and there is another script that has to be run in order to create the stored procedures in the dbt2 database.

scripts/mysql/mysql_load_sp.sh --database dbt2 --client-path /usr/bin/

Which gives the following output:

Load SP: delivery
Load SP: new_order
Load SP: new_order_2
Load SP: order_status
Load SP: payment
Load SP: stock_level

Have you run

scripts/mysql/mysql_load_sp.sh --database dbt2 --client-path /usr/bin

on your server to load in the stored procedures?

After loading in DBT2's stored procedures, I ran DBT2 again and this time I did see transactions in Innotop's output

[RO] InnoDB Txns (? for help)                             localhost, 2h7m, InnoDB 1s :-), 765.68 QPS, 22/0/0 con/run/cac thds, 5.1.69

History  Versions  Undo  Dirty Buf  Used Bufs  Txns  MaxTxnTime  LStrcts
      6       102  0 0       1.56%     99.80%    21       00:00

ID   User  Host       Txn Status  Time   Undo  Query Text
217  root  localhost  COMMITTED   00:00     0  COMMIT

I noticed though as you did, that DBT2's output was still showing O's everywhere, so I increased the number of warehouses to 30. My thinking was that there wasn't enough transactions or activity to generate useful results

scripts/run_mysql.sh --connections 20 --time 300 --warehouses 30 --verbose

Stage 4. Processing of results...
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.32      0.004 :     0.001          136                0   0.00
   New Order  44.59      0.027 :     0.095         1826             1654  90.58
Order Status   4.20      0.002 :     0.001          172                0   0.00
     Payment  42.86      0.007 :     0.014         1755                0   0.00
 Stock Level   3.57      0.010 :     0.000          146                0   0.00

And as you can see, DBT2 sent back appropriate results this time.


How to get MAX numeric value from a column with char values in MySql

If the numbers are all Integer, that you want

SELECT MAX(CAST(col AS SIGNED)) from nextg WHERE col REGEXP '^[0-9]+$';

Like @Akina said in the comments MySQL knows another way

SELECT MAX(col + 0) from nextg WHERE col REGEXP '^[0-9]+$';

See example

If you have also Decimal Numbers.

SELECT MAX(CAST(col AS DECIMAL(8,2))) from nextg WHERE col
REGEXP '^[0-9]+\\.?[0-9]*$'


How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?


Update an entry in table when time expires

A trigger would not be the most appropriate solution for you particularly for a large table which could have lots of hits.

Right a simple stored procedure and just have a SQL Server Agent Job that runs it periodically (as often as is required for your business purposes).

Something simple like:

UPDATE
    custSubsTable
SET
    subscritionActive = 0
WHERE
    subscritionActive = 1
        AND
    expiryDate < GETDATE()

Would work.
I don't think the first half of the WHERE clause is strictly necessary, but it doesn't hurt. You could just stick the code straight into the Job, but it'll be more easily maintained as a stored procedure and can be manually run that way

This could easily be modified to delete instead of update, or even possible delete/update a different row in the same table (by joining the table with itself), although if that is the case then best practise would dictate denormalising your db and splitting the tables up.

Even with a large table, that should be a fairly quick statement to run if it's done frequently enough - obviously the more changes it has to make at once the longer it could be. Make sure you run it manually yourself the first time and add/update any indexes if necessary.

You can do this across multiple tables - I won't get into it here and it's beyond the scope of your question (and I know nothing about your DB), but my first impression, purely from what you say in your question, is that holding customer and subscription data in a single table could lead to lots of denormalisation, might be something to bear in mind.


Are there other sites forbidding SQL developer to use c style comments?

I never use C-style comments in SQL code.  First off, Management Studio gives a very easy way to comment out blocks of code with SQL-style commenting, and just as easy to uncomment it out.

And not to mention if another person opens a SQL script file not in SSMS, C-style comments have a tendency to be elusive and the commented out code doesn't look so commented.

Just my opinion, but I stay away from C-style commenting.  No gain.