Oracle SQL Complications With Multiple Triggers

I am generally opposed to triggers.  They have their place but surprising behaviour, when you do one simple thing and the change ripples out across the world, will give you headaches later.

Instead of having storage_size and memory_usage on the users table, how about creating a view that displays the data you are looking for?  Then you don't need triggers at all.


How to suppress an informational raiserror message

No. Informational messages cannot be suppressed.


For anyone who stumble upon this question and wondering, even without upgrade of RAM, why sending data was taking so much longer. It is because sending data actually include time of searching the data that is to be sent.

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

  The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.


Get records not updated in last 30 minutes

The same server logs multiple updates into the VPX_HOST table (which looks like VMware's Virtual Center, I'm guessing) so this gets a little tricky.

This is technically what you're asking for:

SELECT DISTINCT DNS_NAME
FROM dbo.VPX_HOST no_updates
WHERE NOT EXISTS (SELECT * FROM dbo.VPX_HOST updates WHERE no_updates.DNS_NAME = updates.DNS_NAME AND updates.LAST_PERF_TIME > DATEADD(mm, -30, GETDATE()))
ORDER BY DNS_NAME

But you won't actually like it because if you ever have a DNS_NAME drop offline permanently, like if you throw away a host, it'll keep showing up in here. So let's modify the query so that it only includes hosts that added an entry in the last day, but not in the last 30 minutes:

SELECT DISTINCT DNS_NAME
FROM dbo.VPX_HOST no_updates
WHERE no_updates.LAST_PERF_TIME > DATEADD(dd, -1, GETDATE())
AND NOT EXISTS (SELECT * FROM dbo.VPX_HOST updates WHERE no_updates.DNS_NAME = updates.DNS_NAME AND updates.LAST_PERF_TIME > DATEADD(mm, -30, GETDATE()))
ORDER BY DNS_NAME

That first WHERE clause on the 3rd line says only give me servers who have updated in the last day. If you wanted to go farther back, you could change the dd, -1 to something like dd, -3 to go 3 days back.

Edit about performance - be aware that this is going to scan the VPX_HOST table, so the bigger that table gets, the slower this query will get. If you're going to run it frequently, you'd probably want an index on (DNS_NAME, LAST_PERF_TIME).


Scaling Percona datacenters: setup and replication

In this instance, you actually have two choices
CHOICE #1 : Percona XtraDB Cluster
I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but WITH GREAT POWER, THEIR MUST ALSO ALWAYS BE GREAT RESPONSIBILITY (19:16 - 19:25 of the Video).
You ultimately become responsible for

sizing memory requirements and disk configuration for InnoDB
remembering that DDL/DML on MyISAM is not replicated in the Galera Write Set Replicator Libraries. Since GRANT commands is storage-engine neutral, MyISAM table in the mysql schema is handled with no problem. Any DML against mysql.user is not replicated.
adding provisioning new Cluster Nodes for Reads/Writes

CHOICE #2 : Amazon RDS
Amazon RDS makes MySQL Database Cloud Services a snap. You must spend some time deploying Servers with one of 7 server models. By default, all InnoDB log files are 128M. Here are the only options that are unique to each Server Model:
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

You are not given SUPER privilege and there is no direct access to my.cnf. In light of this, in order to change my.cnf options for startup, you must first create a MySQL-based DB Parameter Option List and use the RDS CLI (Command Line Interface) to change the desired  Options. Then, you must do this to import the new options:

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=whateveroption,value=whatevervalue,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

As for scaling out to data centers, you have the option to create read replicas. Since the default storage engine is InnoDB, making a read replica becomes seamless because data can be sync'd to Slaves without interrupting the Master.
Higher Server Models means you can have more Memory, more IOPs. Don't forget the cliche I mentioned because when it comes to Amazon RDS, with GREAT POWER COMES GREAT MONEY.


The original query

SELECT DISTINCT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE t2.id NOT IN (
    SELECT id
      FROM receipt
      WHERE paid_till_date > Now()
    )
UNION
  SELECT current_order.*
    FROM receipt
      RIGHT JOIN current_order USING (paper_id, subscriber_id)
    WHERE receipt.id IS NULL
    ORDER BY subscriber_id, paper_id

is more complex than necessary.

The base tables are

CURRENT ORDER
  paper_id
  subscriber_id

and

RECEIPT
  id
  receipt_date
  paid_till_date
  paper_id
  subscriber_id

The OP stated that receipt.id is a primary key and I think that (paper_id,subscriber_id) is the primary key of current order.

Both queries

SELECT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE t2.id NOT IN (
    SELECT id
      FROM receipt
      WHERE paid_till_date > Now()
    )

and

SELECT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE paid_till_date <= Now()
    or paid_till_date  is NULL

are equivalent: The resultset f both queries contains exactly the following tuples:

all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date <= Now()
all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date is NULL
all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore paid_till_date  is NULL)

Therefore the queries are equivalent. And they are also equivalent if an DISTINCT is added to the select clause.

The resultset of the query

  SELECT current_order.*
    FROM receipt
      RIGHT JOIN current_order USING (paper_id, subscriber_id)
    WHERE receipt.id IS NULL
    ORDER BY subscriber_id, paper_id

contains the following tuples

all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore receipt.id is NULL)

So the original query can be changed to the simpler query

SELECT DISTINCT t1.*
  FROM current_order AS t1
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE paid_till_date <= Now()
    or paid_till_date  is NULL

Perhaps an index on (paper_id,subscriber_id,paid_till_date) will be usefull. The query uses only columns found in the index (receipt.id is not used anymore). An Index on (paper_id,subscriber_id) of "current order" already exists because this is the primary key.


I would like to get your input on what can be better.

There is no way to judge the design of a Dimensional Data Model without an understanding of the business context and the kinds of questions it's intended to answer.
So make a list of the questions you would like to answer from the data model, providing a specific example for each type of question.
Translate each question to a SQL query against your model.
The design is good to the extent the SQL queries are simple for a human to write and understand, and for an RDBMS to execute.


How mysqlcluster can be durable and consistent?

Q1:  Data is both sharded and duplicated.  Probably each of your two datanodes has the entire dataset.  If you had, say, 5 nodes, each one could have 2/5 of the data, thereby spreading across 5 machines, yet having 2 copies of everything.  And loss of any one node can be recovered from.

Consistency is handled by a "eventual consistency" model.  If you write conflicting things to different nodes, the system will 'eventually' discover this.  Then some rule (which you established) will kick in to decide which data to keep.


This works for me:

"User Id=/;Data Source=DBNAME"

Please note that many people (including myself) are having issues getting an Oracle Wallet to work with the 12c managed driver.  You may need to enable SSL client authentication.  See the following thread on the Oracle Community site for details.

https://community.oracle.com/thread/2590420

As an aside, I'd recommend not overriding the app.config for powershell.exe, and instead using the machine.config.  If you don't see the entries in there to edit, run the configure.bat script found in ORACLE_HOME\odp.net\managed\(x64|x86)\

ise ([System.Configuration.ConfigurationManager]::OpenMachineConfiguration().FilePath)

And then in your Powershell script, you can also load the driver without having to know the path to the DLL:

[void][reflection.assembly]::Load("Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342")


This is probably best to insert all items at once or at least avoid have to split string on the SQL Server side.

You could convert json to xml similar to this:

<Order OrderId="" OrderDate="" CustomerId="" Price="300">
    <Items>
        <Item ItemId="103" Qty="2" Price="100" />
        <Item ItemId="123" Qty="2" Price="100" />
        <Item ItemId="122" Qty="2" Price="100" />
    </Items>
</Order>

You can then execute this stored procedure with this xml parameter:

CREATE PROC InsertItems(
    @xml xml
) AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO data(ItemId, Qty, Price)
    SELECT x.value('@ItemId', 'int') as ItemId
        , x.value('@Qty', 'int') as Qty
        , x.value('@Price', 'int') as Price
    FROM @xml.nodes('/Order/Items/Item') t(x)
END

You must add the nodes and value path to your xml.

It should work fine for a few rows but it may not be very efficient with hundreds or thousands of Items.

You can also create a Table Type and pass is to the stored procedure using a Table-Valued Parameters:

CREATE TYPE dbo.ItemsTableType AS TABLE
    ( ItemId int, Qty int, Price int)

CREATE PROCEDURE insertItems
    (@items dbo.ItemsTableType READONLY)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO data(ItemId, Qty, Price)
    SELECT ItemId, Qty, Price
    FROM @items
END

You then call it from your code using one of the .Net options and examples on this page: Table-Valued Parameters.

If you have to insert too many rows at once, you can split it in batch of 100, 1000 or whatever works best on your system.


Transfer Oracle 10g Database From Linux to Windows

Restore should not be an issue. Both Linux and Windows are little endian so you are OK there. Are you using rman? You can always recreate the database on Windows and perform an export/import to the new database.
Ideally you want to just restore using RMAN directly to the Windows server.
Check out Chapter 4 of Oracle® Database Backup and Recovery Advanced User's Guide or even go with a duplicate or standby database depending on your needs, downtime time frame and licensing.
Please provide more information about your situation with respect to your backup type and tools.


Dynamic execution query produces error when called from the procedure, but works when executed manually

Replacing exec @sqlQuery with exec (@sqlQuery) must work. However, sp_executesql is recommended in your case as it has more flexibility.
Following is a typical example of sp_executesql:
Declare   @DatabaseName nvarchar(128) = 'MyDB'
        , @schema_name nvarchar(128) = 'dbo'
        , @table_name nvarchar(128) = 'MyTbl'
        , @column_name nvarchar(128) = 'MyTblColumn'
        , @ReadColumnInfo nvarchar(max)
        , @datatypename varchar(128);

select @ReadColumnInfo = 'Use ' + QUOTENAME(@DatabaseName) + '; ';
set @ReadColumnInfo =  @ReadColumnInfo + '
SELECT @datatype = TYPE_NAME(c.user_type_id)
FROM sys.objects AS o
JOIN sys.columns AS c  ON o.object_id = c.object_id
WHERE o.name = @TblName and o.schema_id = SCHEMA_ID (@scmName) and c.name = @columnName';

exec sp_executesql
          @ReadColumnInfo -- Statement
        , N'@TblName varchar(128), @columnName varchar(128), @scmName varchar(128), @datatype varchar(128) OUTPUT' --- Params within Statement
        , @TblName = @table_name, @columnName = @column_name, @scmName = @schema_name, @datatype = @datatypename OUTPUT;  --- Param Values within Statement
select @datatypename;


Do all entities in Chen notation have to be diagrammatically linked?

Yes, Chen though so and explicitly calls for it his 1976, and 2002 papers. From his 1976 paper, The Entity-Relationship Model-Toward a
Unified View of Data.

From his paper, Entity-Relationship Modeling: Historical Events, Future Trends, and Lessons Learned 2002

  ER Model has Explicit Linkage between Entities. As can be seen in Figures 2 and 4, the linkage between
  entities is explicit in the ER model while in the relational model is implicit. In addition, the cardinality
  information is explicit in the ER model, and some of the cardinality information is not captured in the
  relational model.

Here is Figure 2,

His contemporaries that cite him also seem to be using explicit links  Hartmann, Sven. "Reasoning about participation constraints and Chen's constraints".  Proceedings of the 14th Australasian database conference-Volume 17. Australian Computer Society, Inc., 2003.

Without the links, you have no relationship. It's a dangling entity.


Is it possible to use part of a composite primary key as a foreign key to another table?

This is certainly possible and even very reasonable in many situations. Think for instance to the classical Order and OrderLine tables, in which the OrderLine has as primary key the order number and the line number, with order number foreign key for the Order table.


Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server.

See How to: Enable Transact-SQL Debugging for details on the requirements.

There also several Connect items discussing this and listing some possible scenarios:

Debugging without sysadmin permissions

  While I understand the frustration behind lack of debugging support for non-sysadmin members, there are lot of challenges in enabling such support in the engine. We looked at in the past & didn't find a clean way to isolate the debugging experience in the server without affecting other processes.
  Below are some key issues:

  Debugging in general requires higher privileges, this is true of SQL Server or OS for that matter
  Debugging SQLCLR code in SQL Server for example can freeze other SQLCLR tasks in the server severely affecting performance
  Debugging endpoints are DCOM based today and it has its own restrictions
  Lastly, debugging T-SQL code in a production server is not something we recommend. The scenario is primarily for development purposes and there are alternatives which I will describe below

  You can use Visual Studio or SQL Server Management Studio or SQL Server Data Tools (SSDT)to debug against a local copy of SQL Server. In SQL Server 2012, we also have the SQL Server Express LocalDB SKU which is used by SSDT. Today, the surface area / features supported in LocalDB is a subset of SQL Server but our long-term goal is to enable all features that are supported in SQL Server including SQL Azure.
  So our recommendation is to use the local dev environment to debug the T-SQL logic. Additionally, you can obtain clone database (which includes statistics without data) to repro query plan related issues too. This will work for most cases other than environment specific problems or missing features in LocalDB. Hope this helps.
  Umachandar, SQL Programmability Team

T-SQL debugging only with sa privileges!?
Debugging SQL Query fails with an error

  The minimum security requirement is sysadmin. This is by design from SQL engine security.


How to Deny permissions for newly created user in DDL Trigger?

I think you'll have to use dynamic SQL for this. Something like

create TRIGGER [NewUserCreation] ON DATABASE
After Create_User
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x xml = EVENTDATA();
    declare @UserName Varchar(30)
    SELECT @UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(max)');
    declare @Cmd nvarchar(100)
    set @cmd = 'DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO ' + QUOTENAME(@UserName)
    exec sp_executesql @cmd
END
GO

ENABLE TRIGGER [NewUserCreation] ON DATABASE
GO

The reason your original code did not work is because your select username merely gave an alias UserName to the value returned from the select.  You are not able to use that column alias in the DENY.  Note that my example puts the value into a variable @UserName.  Then, I build a dynamic DENY statement and externalize the value of @UserName which is concatenated to the DENY command.

You can see this in action with this code snippet.  Assume I'm adding NewUser as a login and that is the value from the xml.

Declare @UserName varchar(100)
SELECT @UserName = (select 'NewUser')
declare @Cmd nvarchar(100)
set @cmd = 'DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO ' + QUOTENAME(@UserName)
print @cmd

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO [NewUser]


How to replace column if not null in select statement?

It's great that a NULL plus a value produces a NULL*, means you can do stuff like this...

SELECT FirstName + ' ' + ISNULL(MiddleName + '. ', '') + LastName
FROM Contact;

Hopefully, this works for what you want.

* Assuming CONCAT_NULL_YIELDS_NULL is set ON, as it should be;


Limiting Generated Sequence Values in Multi-table Insert

It can't be done because you're mixing curval and nextval (happy to be proven incorrect, by the way). You also can't use WITH xxxx AS in an INSERT ALL, which was my first thought as a way around this.

Anyway, this is a logical workaround for you:

insert all
    when mod(x,2) = 0 then
        into b (val) values (my_seq.nextval/2)
        into c (val) values (trunc(my_seq.currval/2+0.5))
    else
        into c (val) values (trunc(my_seq.currval/2+0.5))
select x from a;

Obviously the sequence number is always going to be double what you expect if you use it in other code, but I'm assuming that this is a 1-off statement.


I don't think there's anything wrong with storing JSON objects in a DB record. Though I would use the actual JSON type, rather than just varchar or something of that sort. Using a JSON field type allows you to use MySQL functions like JSON_CONTAINS to search that object.

Depending on what, and how frequently you need to search this data, I would consider just pulling out the particular data you need to search through, and store that as it's own column alongside the whole JSON object. This way, you can easily search for what you need, and still keep the entire object in the event you need to reference it in the future.


Can't we make a non-primary key or non-unique key in a table as a foreign key in another table?

First don't use varchar(x) in PostgreSQL, just use text. Second, all of your char(x) are likely wrong. In SQL, that means store whitespace padding. This is wasteful and a bad idea. You likely do not want char(x). Also, your id columns should probably be int (if you're designing this schema new).

You just need to drop your tables and try again. Your code works fine.


Restoring SQL Server 2012 database onto 2008

Compatibility level only controls what features may or may not be used on a given database and has no bearing on the restoration of DBs from different versions.

As pointed out by others, it can't be done by any means other than scripts or, if you can get it to work, the built in Copy Database tool. However that uses SSIS and I have never managed to get it to work successfully as it requires a whole heap of fiddly security apparatus, and usually consumes far more time than it's worth.

If you truly don't want to use scripting, then pretty much your only option is to upgrade your target DB server to 2012, or install a new one (using developer or Express editions is an option provided that a) It isn't a production instance if you use Developer Edition and b) the limitations of Express don't affect you).


Does restoring a full backup bring it to the backup START time or the backup FINISH time?

Check out this link

  A full database backup provides a complete copy of the database and
  provides a single point-in-time to which the database can be restored.
  Even though it may take many hours for the backup process to run, you
  can still only restore the backup to a single point (effectively at
  the end of the backup, but I'll discuss exactly what that point is
  later in this article). A full backup does not allow recovery to any
  point in time while the backup was running.


Update Table 1 taking value from table 2

UPDATE tbl1, tbl2
SET tbl1.Salary = tbl2.Salary
WHERE tbl1.IDNo = tbl2.IDNo


MySQL will not run after exhausting disk space while indexing

There could be about 3 things going on here...

You ran out of disk space.  Can you scrounge some space, even a little?
You cannot connect because of a socket problem.  Remove the socket and try again.
The attempt to add an index could have been performed in a couple of different ways.  Was the table MyISAM?  Or InnoDB?  If InnoDB, was innodb_file_per_table ON?

In any case, look for #sql... files they were the tmp tables being built before "renaming" them into place.  They are useless no; remove them.

If file_per_table was off, then probably ibdata1 got a lot larger in this failed operation.  This will call more a messier repair.

Since you have an old version of MySQL, I won't point out ALTER TABLE .. ALGORITHM=INPLACE, ADD INDEX ..;


No worries my friend! Your database was not compromised.

The systems administrator had this problem as well this week. We found out after he cursed across the room! After sending a call to our hosting provider, Heroku, it turned out that they were doing a test on a selected amount of machines (most likely on their Cedar stack) and created test schemas as a result.


How can I get all rows of data not included in a TVP

Using NOT EXISTS should work, eg

SELECT *    --!!TODO add column list
FROM dbo.CompanyDomains cd
WHERE CompanyId = @CompanyId
AND NOT EXISTS (
    SELECT *
    FROM @domains t
    WHERE cd.Domain = t.domain
    )


MySQL import LOCK doesn't prevent PRIMARY key issues

Whenever you dump a mysql table that has an AUTO_INCREMENT column, the next value is always attached to the definition of the table. You should see something like:
) ENGINE=InnoDB AUTO_INCREMENT=<some-number> ...

You may want to consider one of three(3) things
SUGGESTION #1 : mysqldump the database with table structure
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database > my_database_data.sql

That way, the auto_increment is attached to the table definition
SUGGESTION #2 : mysqldump the data and table structure separately
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data > my_database_schema.sql

You would load my_database_schema.sql first, then my_database_data.sql
SUGGESTION #3 : Read the next auto_increment of every table into a script
You can use a query like this to make the SQL script:
SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,' AUTO_INCREMENT=',autoinc,';')
FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc
FROM information_schema.tables WHERE table_schema='my_database') A;

In the OS, you can do it like this:
SQLSTMT="SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;"
mysql -uroot -p -Ane"${SQLSTMT}" > autoinc.sql

Simply run autoinc.sql before my_database_data.sql
Give it a Try !!!


First thing you should do is Apply SQL Server 2008 R2 SP3 ASAP. This is because SQL Server 2008 R2 RTM version is not supported at all by Microsoft. There are lots of fixes which have been included in this Service pack release and that would surely benefit SQL Server query performance.

  You asked, So rebuild all indexes is good idea ?

NO IT IS NOT. One should only rebuild index which is fragmented above certain value. Probably widely used value is, if index fragmentation is >30 you should go for rebuild and it is in between 10 and 30 you should go for reorganize. Please note you have standard edition so index rebuild would not be online. If you rebuild index blindly it would cause more downtime and produce more logs and thus more overhead on system. If you have narrow maintenance window you need to be highly selective with index rebuild and all this can be taken care if you go for Ola Hallengren Index rebuild solution

Other thing to note is if page_count for index, page_count can be seen from  be seen from sys.dm_db_index_physical_stats , is less than 1000 you don't need to rebuild such indexes. The reason is since page count is less pages allocated to index would be from mixed extent and these mixed pages can be lying anywhere and hence producing Logical fragmentation even after rebuild. But rest assured such small page count indexes would not affect query performance at all. You can read more about why such indexes still remain fragmented even after rebuil here

There are various options you can choose with index rebuild you can see more details here. Lot depends on your environment and configuration

You can read more about query plans from This Article. You should focus more on actual query plan than estimated query plan. Actual would tell you what exactly SQL Server is doing and what resources it is using.

You can also try SQL Sentry Plan explorer tool to get better insight into SQL Server execution plan


AlwaysOn High Availability MDF and LDF file locations

In our primary replica the drive space is running low with 10% left.
  Can we move the ldf file to the J drive which is 80% free?

  Please note that in all 03 read replicas we do not have a J drive.

In theory, you can use different disks. (Unless you are using direct seeding to seed the databases to the secondaries).

However, I would prefer to have the same disks and disk throughput for transparency and reducing the probability of I/O issues when a secondary becomes primary, among other considerations such as adding data files to your database.

a quick test:

On primary, create a database on two disks

CREATE DATABASE [test]
 ON  PRIMARY
( NAME = N'test', FILENAME = N'F:\DATA\test.mdf' , SIZE = 105472KB , FILEGROWTH = 102400KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'E:\LOG\test_log.ldf' , SIZE = 11264KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [test] SET RECOVERY FULL
GO

BACKUP DATABASE [test] TO DISK = '\\Share\test.bak'
WITH STATS=5;

Restore on secondary on one disk

RESTORE DATABASE [test]
FROM DISK = '\\Share\test.bak'
WITH MOVE 'test' to  'F:\DATA\test.mdf',
MOVE 'test_log' to 'F:\DATA\test_log.ldf',
NORECOVERY,STATS=5;

Add primary to ag (execute on primary)

ALTER AVAILABILITY GROUP AGName ADD DATABASE test;

Add secondary to ag (execute on secondary)

ALTER DATABASE test SET HADR AVAILABILITY GROUP = AGName;

Validate the databases on all replicas

Change the data file on the primary

ALTER DATABASE test
MODIFY FILE (name='test',FILENAME = N'H:\DATA\test.mdf')

ALTER AVAILABILITY GROUP AGName REMOVE DATABASE test;
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE

-- copy file

ALTER DATABASE test SET ONLINE

ALTER AVAILABILITY GROUP AG ADD DATABASE test;

Add the secondary to the AG again

ALTER DATABASE test SET HADR AVAILABILITY GROUP = AGName;

Validate the databases on all replicas

Changing the secondary to reside on completely different disks

On the primary

ALTER AVAILABILITY GROUP AGName REMOVE DATABASE test;
BACKUP DATABASE [test] TO DISK = '\\BEUAT0563\T$\DATA\test.bak'
WITH STATS=5;

On the secondary

RESTORE DATABASE [test]
FROM DISK = 'T:\DATA\test.bak'
WITH MOVE 'test' to  'T:\DATA\test.mdf',
MOVE 'test_log' to 'T:\DATA\test_log.ldf',
NORECOVERY,STATS=5,REPLACE;

On the primary

ALTER AVAILABILITY GROUP DA4EMS_BEUAT_AG ADD DATABASE test;

On the secondary

ALTER DATABASE test SET HADR AVAILABILITY GROUP = DA4EMS_BEUAT_AG;

Extra considerations

If you where to add extra files to the database on a disk that does not exist on the secondary, you will get an error:

USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test2', FILENAME = N'F:\DATA\test2.ndf' , SIZE = 105472KB , FILEGROWTH = 102400KB ) TO FILEGROUP [PRIMARY]
GO

This attempts to create an extra database file on all replica's, expecting the disk to exist.


Left outer join to itself to find outliers?

Try the following query.

SELECT t1.ItemType, t2.Itemtype, COUNT(t1.X)
FROM Tab t1, Tab t2
WHERE
    t1.ItemType < t2.ItemType
    AND ABS(t1.X - t2.X) > 2
GROUP BY t1.ItemType, t2.Itemtype
;

It should produce the following results.

+----------+----------+-------------+
| ItemType | ItemType | COUNT(t1.X) |
+----------+----------+-------------+
|     1    |     2    |      7      |
+----------+----------+-------------+


Its a defensive programming practice to only do an action (rollback) if it's possible to do.


Looking at your query plan, it seems that the optimizer has chosen a Stream Aggregate operator to handle your GROUP BY. The downside of these is that they require ordered data, which a HEAP is not.

David's suggestion is perfectly valid. Adding a clustered index may remove the Sort operator, so performance testing that vs. my suggestion is up to you.

If indexing the temp table doesn't give you the performance you need, applying an OPTION(HASH GROUP); hint to the query will influence the optimizer to use a Hash Match Aggregate rather than a Stream Aggregate.

Hash Match Aggregates don't require sorted data, but may also spill to disk the way the Sort spilled in the Stream Aggregate plan.


SQL Server Job failing to delete html files out of a folder

It looks like the problem is that, while there is always some file present (so the COPY always succeeds), there is not always a file with the specified extensions.

One quick-and-dirty solution - add the following lines between the COPY and the first DELETE:

ECHO z >> C:\DATA\LOGS\ZZZ_tmp.txt
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.rpt
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.html
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.log

This will create one file with each required extension (or append a z to an existing file with the name).

If you want to go this way, make sure you:

Pick a base file name that you will never see in reality, so you don't corrupt an existing file; and
Document the purpose of the extra files.

Ideally, you'd want to create a solution where you don't fail the DELETE step if there's no matching files, instead of creating dummy files just to get deleted. This can probably be done with CMD.EXE; however, I'd suggest you implement a script in whatever language your department uses (or, at least in whatever language you're comfortable with). Unless you're comfortable with DOS (or whatever you call what CMD.EXE uses now), that's probably not your best solution.

Also - note the COPY will fail if there are no files in C:\DATA\LOGS\ to begin with. This could also be handled by a script, as could what to do if both source and destination directory have a file with the same name.


What are the security implications of sp_executesql?

None. sp_executesql executes SQL, under exactly the same context and privileges as the original caller would execute the same SQL. There are many, many, cases when dynamic SQL is unavoidable.


I will answer the last question first: Yes, you can change it while the server is running without issue. If you want to change the value via SQL you can do it with the following query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

See this page for more details about setting memory on SQL server.

Your first question, unfortunately the answer is: I can't tell you, I'm not there.

There is a 1,000,000 things you need to factor in when allocating memory. How big are result sets from the queries, how often are they run, would a query that used to take 20 ms be ok to now take 200 ms?

Sql's defaults assumes that it is the only thing running on the server, so it just sets the memory to MAX_VALUE and it stops growing when all available memory is in use (and on dedicated hardware that is fairly close to what you want to happen(see Aarons comment for a possible caveat)). Normally any web server or other software interacting with the database would be on different hardware communicating to it over the network.

You really just need to just set it to a value you think is sane, and if your webserver is still memory choked lower it. If SQL is not giving you enough performance after you give the webserver the memory it needs you will need to either buy more ram or move the SQL to dedicated hardware.


SQL Server 2016 - unexpected privileged time bursts

From an article on the internet:

(emphasis mine)

  The Processor(_total)\ % Privileged Time counter shows the percent of time that the processor is spent executing in Kernel (or Privileged) mode. Privileged mode includes services interrupts inside Interrupt Service Routines (ISRs), executing Deferred Procedure Calls (DPCs), Device Driver calls and other kernel-mode functions of the Windows® Operating System.

  Most of the time a processor should be executing User mode operations, a high % privileged time might indicate a poorly written device driver or a faulty piece of hardware.

Reference: Windows Performance Counters Explained (AppAdminTools)

You might want to have a look at the DLL executing in the context of the CLR. It may not be releasing all its resources.

Equally interesting the information from Microsoft | SQL Docs:

(emphasis mine)

  Processor: % Privileged Time

  Corresponds to the percentage of time the processor spends on execution of Microsoft Windows kernel commands, such as processing of SQL Server I//O requests. If this counter is consistently high when the Physical Disk counters are high, consider installing a faster or more efficient disk subsystem.

    Note

    Different disk controllers and drivers use different amounts of kernel processing time. Efficient controllers and drivers use less privileged time, leaving more processing time available for user applications, increasing overall throughput.

Reference: [Monitor CPU Usage][] (Microsoft | SQL Docs)

Ensure your disk drivers are up to date
Ensure your disk I/O is ok
Remove any other services/applications from the SQL Server (instances)


InnoDB error: exceeded log capacity...but only during import

You are probably trying to import very large rows. The 5 MB default log file size (10MB in total for the log) may be too small in many cases. The default for 5.6 is 50MB (100Mb in total), which is probably a more sane default in most cases.

If you do not have much load, the only problem of augmenting the log is having to handle a larger set of files, nothing else should change (this is not true in heavy-write environments). it will also make the load process much faster, assuming you have enough memory size for the buffer pool. You may want to change the innodb_log_buffer_size if you are loading lots of large rows.

Check a tutorial on how to change the transaction log size in MySQL 5.1.


oracle null value in quotes - yet another newbie question

Simple! Put NULL without quotes.

E.g.

INSERT INTO tableName (columnName1, columnName2) VALUES ('Value1', NULL)

We must always use NULL without quotes when we intend to use NULL values. Putting quotes around it, like 'NULL' makes it a string literal and it is treated as character data. Hence for using NULL values, we must always use NULL without quotes, be it a null string, a null number or a null BLOB. For every data type, we must use NULL without quotes.


I can't recommend any books in particular but I have several locations of free books on this page: http://sqlstudies.com/study-and-reference-tools/

For example this page has something like 30 free SQL books.

In general though you pick up the various DMOs and system views/functions as you study associated technologies.  For example you will learn about the replication DMOs etc as you study replication.

The Periodic Table of DMOs by Tim Ford is a great place to start on the DMOs however.  It has all of the DMOs broken down by category and functional area.


What is right datatype with numbers along with '-'

With hyphens, it's a string, full stop!

You could store it as a BIGINT (without the hyphens) and format it using the hyphens on the client side - putting hyphens at 4 and 12.

However, if the position of the hyphens varies, you'll have to store it as a string and not any form of number.

You should only store values as numbers if you're going to add/subtract/multiply or divide them - otherwise, they are strings and not "numbers" anyway! How do you perform mathematical operations with "numbers" which have hyphens?


PowerBi Line Graph: Tooltip has different decimal places

The answer is embarrasingly simple. in PBIRS:

Click on the datasource field
Click modelling in the tool bar
Look for the formatting options

In PowerBI

Click on the datasource field
Click Columns tools in the tool bar
Look for the formatting options.


How to set up Amazon RDS parameter group for Postgres?

Amazon RDS documentation provides a table of units for each parameter under the "Working with PostgreSQL Parameters" topic.  Units are also usually included in the description of each parameter in the parameter group edit UI from the Amazon RDS web console.

min_wal_size (Postgres 9.5 Parameter Group Family)

Default: 16
Units: MB
Allowed Values: 2-201326592
Description: (16MB) Sets the minimum size to shrink the WAL to.

shared_buffers (Postgres 9.5 Parameter Group Family)

Default: {DBInstanceClassMemory/32768}
Units: 8kB
Allowed Values: 16-1073741823
Description: (8kB) Sets the number of shared memory buffers used by the server.

Regarding shared_buffers specifically, the documentation has this to say:

  Note that some parameters use units that you might not be familiar with; for example, shared_buffers sets the number of 8 KB shared memory buffers used by the server.


Fast recovery vs Accelerated database recovery in SQL server 2019

Here's the excerpt from the Recovery and the transaction log documentation that describes the Fast recovery feature:

Note
To maximize the availability of databases in an enterpirse
environment, SQL Server Enterprise Edition can bring a database online
after the Redo Phase, while the Undo Phase is still executing. This is
known as Fast Recovery.


PostgreSQL cannot start after replacing contents inside its Data folder

I ran into the same problem.  To clarify what Nyxynyx hints towards in his comment, the resolution was to give permissions to access the db cluster dir (the path that follows -D in the error):

chown -R postgres:postgres /var/lib/postgresql/9.1/main


The best answer to  this question is a bit of a short one, but go look at the books online articles that talk about what features are due to be removed in future versions and are no longer there in various versions.

For instance.. In SQL Server 2008 the "old-style" left outer join syntax (WHERE Table2.col1 *= Table1.Col1) is listed as deprecated (there but on the way out in a future version) and it is finally gone in SQL Server 2012. So if you kept that syntax in 2008 it would work, but would require work after upgrading to SQL 2012 or greater or you would have a failure.

To get you started, check out these links that take you to the articles that describe what features are no longer supported in the listed version and what features are due to be removed in the future.. Then look for these in your code.. I would also say you may want to look at future compatibility beyond SQL Server 2012 if you are holding off 3+ years.. For instance TEXT fields will still work in SQL Server 2012, but they won't likely work in the version out after SQL Server 2012 according to the links below.

SQL Server 2012 Deprecated Feature List
SQL Server 2008R2 Deprecated Feature List

You should also make sure that you let your database run in SQL Server 2008 compatibility mode after your upgrade to 2008 and ensure it works well in 2008 and to allow you to upgrade it to 2012 - SQL Server 2000 Databases, or databases operating in SQL Server 2000 compatibility mode will not migrate to SQL Server 2012.

I also wrote an answer that talks extensively about upgrades and migrations and general preparedness steps.


determine maximum advisory locks supported by Postgres

The lock table is a shared memory area with room for max_locks_per_transaction * (max_connections + max_prepared_transactions) locks. It is allocated at server startup time and never resized.
This is the limit for advisory and all other locks. Like the documentation says, row locks are stored on the row and not (permanently) in the lock table.


PostgreSQL created a PostgreSQL user on mac. Is this necessary?

Usually, the Postgres server process is owned by the accompanying system user (not by root or the installing user), so it only has a limited set of rights, making possible attacks somewhat less dangerous.

Additionally, the Postgres system user is normally used to carry the default privileges to initialize db clusters and access newly created databases without password. But you can change all that, of course.

What MySQL does is hardly relevant to these considerations. It is in no way role model in security related affairs.


Prevent a stored procedure from being executed outside SSMS

Instead of relying on shaky assumptions on the application name, why don't you just revoke permissions from those users that are not supposed to run the procedure?

You said that developers should not run it accidentally: revoke EXECUTE permissions from them and force them to use a different login to perform this task (if they are supposed to perform it, otherwise keep this privilege for you or the application administrator).


I would start with:

SELECT id1, id2
FROM pairs
WHERE id1 IN (1,5,3) AND id2 IN (2,4,1)

The '1,5,3' and '2,4,1' can also be results from another query.


Data should never be directly inserted into a MySQL slave [except for edge cases ignored here].  To prevent this accidentally happening you should set
    read_only
in your my.ini.  Details on this option are here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_only

MySQL Proxy might be an option for read/write splitting, but the last time I checked there were bugs that may mean this is currently unworkable. (MySQL Proxy: http://dev.mysql.com/downloads/mysql-proxy/, R/W split: https://wikis.oracle.com/display/mysql/MySQL+Proxy+RW+Splitting)

A third option (if you are looking for more HA than performance, and wish to avoid rewriting your application) would be to configure Master Master replication and put a loadbalancer such as haproxy in front of it - there are some excellent tutorials on the web on configuring haproxy in this kind of environment. (HAProxy: http://haproxy.1wt.eu)

(edited for spelling/links)


fn_dblog not showing records while LDF file is large

the overall issue with your endeavor, while I commend it, is that getting the log isn't the hard part. Reading the log is, as it isn't documented at all. There are 3rd party products that can help you, but you reading the log and asking these questions isn't going to work out very well. This wasn't written to put you off, merely to express that the log isn't very human readable, documented, or otherwise made for people to go slogging through it.

With that, the below should help you:

  I know the fn_dblog function is only searching the active part of the log:

Unless you set the proper trace flag. I'm not going to reinvent the wheel, here, Paul already has a great post on this.

  •But what does that mean?

It means without the TF it won't look at inactive parts of the log... inactive VLFs.

  •When does a part of the transactionlog becomes inactive?

When it's not longer needed by recovery (and/or, depending on recovery model) when it's been successfully backed up. See the log architecture link, above.

  •And how can I search the inactive part?

See Paul's blog.


What is the potential fallout of changing SQL Server's log on account?

Changing the service account for SQL Server or changing the service account password (if you were already using a domain account) is common practice.

The NT SERVICE\MSSQLSERVER is a service account and not a formal user login in Windows. When you update the service account to a domain user account it will get linked to that service account and retain the permission required on the OS and file system.

Simply ensure any adjustment you make to the service account (change it or updating the password) is done via SQL Server Configuration Manager. This tool handles applying the permissions properly to a new service account.


Why is row-level access needed when views can limit a person's access to rows and/or columns?

I'm assuming you're asking this question in the context of Microsoft SQL Server since Row-Level Security is a feature they offer. While there are similarities between using a View to implement a mock row-level security and using the Row-Level Security feature of SQL Server, there are a few differences as well:

Separation of Concerns - Row-Level Security abstracts out the security portion from your entities to allow for cleaner and simpler code.

Manageability - With Row-Level Security, you create a Policy entity that Users can be added to. That Policy leverages a Function where you define your logic for the security Policy. This eliminates the need for custom Table(s) to store your users and their access roles.

Reusability - You can use the same Policy for multiple Tables without the need of coding repeated logic.

Flexibility - You can easily turn the Policy ON and OFF for any Table by setting the STATE of that Policy.

Performance - Generally the custom logic used to emulate security at the Row-Level in Views can negatively affect performance if the predicates are too complicated. I've seen great performance improvements by removing this logic from your View's predicates and creating an Inline Table Valued Function with the same exact logic used in a Policy for Row-Level Security. (I don't fully know why but by looking at the Execution Plan in both scenarios, there is definitely a difference which I think is related to the security logic being abstracted out and applied separately from the predicates in the View.)

For more information you can read on Microsoft's Row-Level Security docs.


SqlLocalDB.msi and DBNETLIB / PowerShell SqlServer ServerConnections

SQL Express LocalDb is not going to work with the old ODBC driver that comes with Windows.  Either download and use a newer ODBC driver, or install the service-based SQL Server Express Edition.


What does "thread" mean in "thread checkpoints" context?

Yes, a thread in this case is the redo generated by an instance. If you had a 4-node RAC, you would have 4 redo threads. This is thread# in v$archived_log. The certain target is almost certainly referring to an SCN.


If I have interpreted correctly your question, here is a query that produces the result given the storeid X:

SELECT codproduct,
       product_name,
       COALESCE(l.price1, p.price1) AS price1,
       COALESCE(l.price2, p.price2) AS price2
FROM Product p LEFT JOIN PriceList l ON p.codproduct = l.codproduct
WHERE storeid = X


My database keeps crashing on startup how can i repair it without it running?

I take it you are using REPAIR TABLE by hand.
I also take it your are doing an automatic table repair for MyISAM. (You have to remove  or comment out the option of my.cnf to disable automatic MyISAM table repair).
You can actually repair the table without using MySQL but mysqld cannot be accessing it. How?
You have to use the offline utility myisamchk
Suppose you have the following

datadir is /var/lib/mysql
database is mydb
table is mytable

The table files would be

/var/lib/mysql/mydb/mytable.MYD
/var/lib/mysql/mydb/mytable.MYI

You can repair it with something like this
cd /var/lib/mysql/mydb
cp mytable.MY[ID] /tmp
cd /tmp
myisamchk -r mytable.MYD mytable.MYI
cd /var/lib/mysql/mydb
mv mytable.MY[ID] ..
mv /tmp/mytable.MY[ID] .

Afterwards, you can either login to mysql and do this
mysql> flush tables;

or restart mysql
service mysql restart

Give it a Try !!!
UPDATE 2014-01-08 15:46 EST
Based on the last comment

Hi Rolando, It is an Linux OS Virtual Machine. 2 Intel Xeon Core 2048 Mbyte Memory. The tables are MyISAM. I will post the rest off the data in a sec (it's hard because it keeps crashing remember)

I would stronly recommend that you get more memory. Why ?
MyISAM caches index pages to the MyISAM key buffer. MyISAM does not cache data. The mysqld process defers all data caching to the OS. That can be a little dangerous for all open MyISAM tables in a low memory DB Server.
A MyISAM table keeps a count of the number of times a file handle has been open against it. When MySQL is down, a MyISAM table with a nonzero file handle count is considered crashed.
Although I provided the answer to the question in fixing a crashed a MyISAM table without mysqld running, you have a bigger problem with a 2GB VM. It should be more like 8GB. You should also run mysqltuner.pl against the VM by doing this:
# wget mysqltuner.pl
# perl mysqltuner.pl

You can then learn if /etc/my.cnf is underconfigured.


If performance is your primary concern, I suggest the second approach.  No need to touch the parent User table.  Use the serializable isolation level to address concurrency if the same user can have multiple sessions.

CREATE TABLE Vote(
     userId int NOT NULL
   , contentId int NOT NULL
   , quantity int NOT NULL
   , CONSTRAINT PK_Vote PRIMARY KEY CLUSTERED (userId, contentId)
   );
GO

CREATE PROC dbo.CastVote
    @userId int
   ,@contentId int
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

BEGIN TRAN;

INSERT INTO dbo.Vote (userId, contentId, quantity)
SELECT @userId, @contentId, 1
WHERE NOT EXISTS(
    SELECT *
    FROM dbo.Vote WITH(UPDLOCK)
    WHERE
        userId = @userId
        AND contentId = @contentId
        );

IF @@ROWCOUNT = 1
BEGIN
    COMMIT;
END
ELSE
BEGIN
    UPDATE dbo.Vote
    SET quantity = quantity + 1
    WHERE
        userId = @userId
        AND contentId = @contentId
        AND quantity <= 2;

    IF @@ROWCOUNT = 1
    BEGIN
        COMMIT;
    END
    ELSE
    BEGIN
        RAISERROR('Nor more than 3 votes may be cast', 16, 1);
        ROLLBACK;
    END;
END;

GO

If the more common case is that users will cast more than 2 votes , you could reverse the logic and update first:

CREATE PROC dbo.CastVote
    @userId int
   ,@contentId int
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

BEGIN TRAN;

UPDATE dbo.Vote
SET quantity = quantity + 1
WHERE
    userId = @userId
    AND contentId = @contentId
    AND quantity <= 2;

IF @@ROWCOUNT = 1
BEGIN
    COMMIT;
END
ELSE
BEGIN

    INSERT INTO dbo.Vote (userId, contentId, quantity)
    SELECT @userId, @contentId, 1
    WHERE NOT EXISTS(
        SELECT *
        FROM dbo.Vote
        WHERE
            userId = @userId
            AND contentId = @contentId
            );

    IF @@ROWCOUNT = 1
    BEGIN
        COMMIT;
    END
    ELSE
    BEGIN
        RAISERROR('Nor more than 3 votes may be cast', 16, 1);
        ROLLBACK;
    END;
END;

GO


Is there any reason for an index to contain the primary key?

You are right to be suspicious of the two indexes that you listed in your question, but there are a few narrow use cases for including the primary key in an index as a key column. By default SQL Server will make your primary key into a clustered key which is the more important concept for performance for this question, so instead if referring to primary keys to answer I'll refer to clustered keys instead.

For the index on id, sent_date, and group_id, queries that only select those columns may benefit because the index will be a covering index for that query. You say that if you know id then you've got the row but what you really have is a very efficient way to read all of the data in data pages for that row. Suppose your (rowstore) table just has a clustered index on the id column. Consider the following query:

SELECT id, sent_date, group_id
FROM your_table
ORDER BY id;

SQL Server is likely to perform a clustered index scan to satisfy that data. All of the data from the data pages will be read which includes columns in the table that aren't referenced by the query.

After creating the _dta_index_survey_25_135059617__K1_K10_K19 index then SQL Server will be able to satisfy that query with an index scan on the new index. It is possible that the index will take up less pages than the table so that could be more efficient from an IO perspective. For example, suppose that you had a VARCHAR(2000) column that was heavily populated with data. That column will be included in the data pages for the clustered index but will not be included in the nonclustered index. That means that satisfying the query using the nonclustered index could incur fewer logical reads.

Note that sent_date and group_id aren't useful as key columns for this example. They could be INCLUDED columns for the same benefit. Note that without the ORDER BY clause an index on just sent_date and group_id would also be a covering index, so including the id column wouldn't be useful for that example without ORDER BY.

That index could also be marginally useful if your clustered key was defined as id DESC (id ASC is the default). SQL Server cannot do backwards clustered index scans in parallel, but the following query could be run in parallel with a scan on _dta_index_survey_25_135059617__K1_K10_K19:

SELECT id
FROM your_table
ORDER BY id DESC;

Again, the key columns on sent_date and group_id aren't useful for this query, but the query optimizer could use the first column of the defined index. I cannot think of a query that benefits from all three key columns.

It is more difficult to think of queries that would benefit from the index on group_id, id, campaign_id, and sent_date. Assuming that the clustered key is defined as id ASC, I believe that ORDER BY group_id, id, campaign_id, sent_date can be satisfied by an index on just group_id because id is a unique clustering key and that index will be created in group_id then id order. As before, you could benefit if your index on id is sorted in a different order than the clustering key on id.

I suspect there are also some data distributions that could benefit from the _dta_index_survey_21_364632442__K18_K1_K2_K9 index when filtering on both group_id and id, even though id is a unique clustered key. Suppose that each data page in the table contains 2 rows and half the rows in the table have group_id = 1. In theory, SQL Server could use that index to satisfy the following query:

SELECT group_id, id, campaign_id, sent_date
FROM your_table
WHERE group_id = 1 AND id IN (1,3,5,7,9,...);

and possibly do less IO than using the clustered index. However, I do not know if the query optimizer would actually do this.

In summary, there are some some corner cases with covering indexes and sort orders which could benefit from the indexes that you listed in the question. However, in my experience it will be pretty unlikely those indexes will provide a noticeable (or even any) benefit for your workload. If those indexes do happen to provide a benefit it's likely that there will be smaller indexes that could provide the same benefit.


How to resolve the error for "Alter Subscription disable"?

I figured out that the below functions will work:

     pctest1=> select pglogical.alter_subscription_disable('sandbox_rds_pg10_subscriber');
-[ RECORD 1 ]--------------+--
alter_subscription_disable | t

pctest1=> select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+------------------------------------------------------------------------------------------
subscription_name | sandbox_rds_pg10_subscriber
status            | disabled
provider_node     | sandbox_provider
provider_dsn      | host=10.xx.xx.xx port=xxxx dbname=sandbox_uat user=admin password=xxxxxxxxxx
slot_name         | pgl_pctest1_sand2c004cb_sand98307b9
replication_sets  | {default}
forward_origins   | {all}

pctest1=> select pglogical.alter_subscription_enable('sandbox_rds_pg10_subscriber');
-[ RECORD 1 ]-------------+--
alter_subscription_enable | t

pctest1=> select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+------------------------------------------------------------------------------------------
subscription_name | sandbox_rds_pg10_subscriber
status            | replicating
provider_node     | sandbox_provider
provider_dsn      | host=10.x.x.x port=xxxx dbname=sandbox_uat user=admin password=xxxxxxxx
slot_name         | pgl_pctest1_sand2c004cb_sand98307b9
replication_sets  | {default}
forward_origins   | {all}


In order to restore a contained database into a different instance of sql server, in this case my local server, the "Enable Contained Databases" property must be set to True.

You can do this from management studio:

Right-Click on the server instance, select Properties
Select Advanced page, set  under Containment the property value to True
Proceed to restore the database backup.
ALTER AUTHORIZATION ON DATABASE::ReplaceThisWithYourDatabaseName TO ReplaceThisWithLeastPrivilegeUser;

Here are the script lines I actually used for enabling/disabling containment:

-- Enable "contained database authentication"
EXEC sp_configure 'contained', 1;
RECONFIGURE;

-- Disable "contained database authentication"
EXEC sp_configure 'contained', 0;
-- Force disabling of "contained database authentication"
RECONFIGURE WITH OVERRIDE;

For additional information please refer to:
http://www.sqlsoldier.com/wp/sqlserver/protectionfromrestoringabackupofacontaineddatabase


How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too!

My gripes with this proc go back a long way:

The Annals of Hilariously Bad Code, Part 1: Critique the Code
The Annals of Hilariously Bad Code, Part 2

The problem you run into when deleting large amounts of data is the crappy estimate you get from the table variables.

I've had pretty good luck creating a new version of the proc using temp tables. You could also try just adding recompile hints, but hey, this way we get useful indexes.

As a side note: if you still run into this blocking because this is running long, you can try either removing the transaction code, or changing it to encapsulate each individual delete (though at that point the benefits are negligible).

CREATE   PROCEDURE [dbo].[sp_delete_backuphistory_pro]
   @oldest_date datetime
 AS
 BEGIN
   SET NOCOUNT ON

   CREATE TABLE #backup_set_id      (backup_set_id INT PRIMARY KEY CLUSTERED)
   CREATE TABLE #media_set_id       (media_set_id INT PRIMARY KEY CLUSTERED)
   CREATE TABLE #restore_history_id (restore_history_id INT PRIMARY KEY CLUSTERED)

   INSERT INTO #backup_set_id WITH (TABLOCKX) (backup_set_id)
   SELECT DISTINCT backup_set_id
   FROM msdb.dbo.backupset
   WHERE backup_finish_date < @oldest_date

   INSERT INTO #media_set_id WITH (TABLOCKX) (media_set_id)
   SELECT DISTINCT media_set_id
   FROM msdb.dbo.backupset
   WHERE backup_finish_date < @oldest_date

   INSERT INTO #restore_history_id WITH (TABLOCKX) (restore_history_id)
   SELECT DISTINCT restore_history_id
   FROM msdb.dbo.restorehistory
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)

   BEGIN TRANSACTION

   DELETE FROM msdb.dbo.backupfile
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.backupfilegroup
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorefile
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorefilegroup
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.restorehistory
   WHERE restore_history_id IN (SELECT restore_history_id
                                FROM   #restore_history_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE FROM msdb.dbo.backupset
   WHERE backup_set_id IN (SELECT backup_set_id
                           FROM   #backup_set_id)
   IF (@@error > 0)
     GOTO Quit

   DELETE msdb.dbo.backupmediafamily
   FROM msdb.dbo.backupmediafamily bmf
   WHERE bmf.media_set_id IN (SELECT media_set_id
                              FROM   #media_set_id)
     AND ((SELECT COUNT(*)
           FROM msdb.dbo.backupset
           WHERE media_set_id = bmf.media_set_id) = 0)
   IF (@@error > 0)
     GOTO Quit

   DELETE msdb.dbo.backupmediaset
   FROM msdb.dbo.backupmediaset bms
   WHERE bms.media_set_id IN (SELECT media_set_id
                              FROM   #media_set_id)
     AND ((SELECT COUNT(*)
           FROM msdb.dbo.backupset
           WHERE media_set_id = bms.media_set_id) = 0)
   IF (@@error > 0)
     GOTO Quit

   COMMIT TRANSACTION
   RETURN

 Quit:
   ROLLBACK TRANSACTION

 END


Select when group by contains more than one row with the same value?

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id,
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;


v$ tables not recognized in a sql statement of a shell script

Try: sqlplus -S / as sysdba <<'EOFILE' ...

Putting quotes tells the shell to not interpret the contents of the here document: it does not try to interpret $XXX in the here document as a shell variable.


Importing large SQL dump with millions of INSERT statements

This dump was dumped as individual statements (with pg_dump --inserts)

INSERT INTO esa2010_codes VALUES (11002, 'Národn
INSERT INTO esa2010_codes VALUES (11003, 'Nefina
INSERT INTO esa2010_codes VALUES (12502, 'Národn
INSERT INTO esa2010_codes VALUES (11001, 'Verejn
INSERT INTO esa2010_codes VALUES (12602, 'Národn
INSERT INTO esa2010_codes VALUES (12603, 'Finanč
INSERT INTO esa2010_codes VALUES (12503, 'Ostatn

This is documented as being slow (from man pg_dump)

  --inserts Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

That's why it's so slow. What you're going to want to do is to turn off some of the durability settings, specifically synchronous_commit, though fsync will help too

You can do this very simply by running the following command before you run your \i file.sql.

SET synchronous_commit TO off;

That will do a lot to speed it up. Don't forget to turn back on the durability options after you're done. I bet it'll finish in a few hours, after you're set that. If you need more speed though don't hesitate to turn off fsync and full_page_writes on the cluster until you get the data up -- though I won't do it if the DB had data you needed in it, or if it was production. As a last note, if you need the speed and this is a production DB you can go all out on your own copy and dump it with the default options by pg_dump, which you'll be able to load much faster.


Best practices for SQL Server block size while formatting disks for data and log files

Nothing has changed since that article. You should still use 64KB as your unit size.
That is how the default storage in SQL Server VMs in Azure are configured:


Create a function/tsql to get strings from the 1st table/data and compare and convert if from the value of second table

Byron,

Following our small conversation in the comments, I would recommend making a table structure change instead of trying to make a code change to accommodate this.

I believe this is the solution for you because it will:

Limit the possibility for this kind of mismatch to happen again in the future
Eases the level of effort to correct an issue in the future if it comes up again
Removes the need for any complicated Macros or Queries to retrieve the data you are actually looking for

This will however require a couple of steps of initial effort from you:

Make table changes
Make application changes to support both the new table structures and probably a slight change to data entry processes
Data Cleanup Project

1. Make Table Changes

One thing Relational Databases are really good at is creating was I have heard call either a Master List or an List Of Values (LOV) table.  The benefit is this forces your data and your application to only store certain allowed value for data integrity purposes.  It then also allows you to make a change in one place and have it update everything.

Let's assume your two tables look something like this:

Table_1
ID INT,
Company VarChar(50),
CreateDateTime DATETIME

Table_2
Company VarChar(50)

If we rebuild the tables to be something like this we get to create a Foreign Key relationship which will ensure that the data stays consistent over time. (Table_1.CompanyID must be a value found in Table_2.CompanyID)

Table_1
ID INT,
CompanyID INT,
CreateDateTime DATETIME

Table_2
CompanyID INT,
CompanyName VarChar(50)

2. Make application changes to support both the new table structures and probably a slight change to data entry processes

When you wanted to create the original output of Table_1 you would run a query like below.  This kind of change would need to be made in anything that tries to retrieve this data (reports, applications, automated processes, etc...)

SELECT T1.ID,
T2.CompanyName,
T1.CreateDateTime
FROM Table_1 T1
    INNER JOIN Table_2 T2
        ON T1.CompanyID = T2.CompanyID

When it comes to Insert and Update processes, you will first need to know that whatever the CompanyName value is you are trying to store.  If that value does not exist already, than you have to create that value in Table_2 before writing your record to Table_1.  For the sake of simplicity lets assume that the value ABC already exists and Table_1.ID is an Identity/Auto-Incriminating column (IE: I don't need to create a value for it on the Insert).  A simple Insert and Update script would look something like:

--Insert
INSERT INTO Table_1 (CompanyID, CreateDateTime)
SELECT CompanyID,
GETDATE()
FROM Table_2
WHERE CompanyName = @CompanyName

--Update
DECLARE @CompanyID INT
SET @CompanyID = (SELECT TOP(1) CompanyID
                    FROM Table_2
                    WHERE CompanyName = @CompanyName
                )

UPDATE Table_1
SET CompanyID = @CompanyID
FROM Table_1
WHERE ID = @Table_1ID

Your application will probably need to have some sort of separate screen that allows certain users to create new Table_2 records when appropriate.  Otherwise you may have a similar problem down the road with just a different implementation.  You can do a check and when needed insert into Table_2 when trying to run insert or update operation for Table_1.  I can provide some examples for those if you would like but I would recommend against it.

3. Data Cleanup Project

Depending on the amount of data in your database, this may be the biggest effort.  You will need to take the data you have right now and transpose it into this new table structure.

--Create Table_2 Records
INSERT INTO Table_2 (CompanyName)
SELECT DISTINCT Company
FROM Table_1_Original

--Create Table_1 records with correct references to values in Table_2
INSERT INTO Table_1 (CompanyID, CreateDateTime)
SELECT T2.CompanyID,
T1O.CreateDateTime
FROM Table_2 T2
    INNER JOIN Table_1_Original T1O
        ON T2.CompanyName = T1O.Company

Then correct the records in Table_2 so that only the correct values you are looking for exist.  You could do this directly in the original Table_1 before running the above script or make changes on Table_2 directly after everything is said and done.  There are a couple of different ways to make it happen and I can provide some guidance if needed.

When This is All Said and Done

You should be in a position where your application self-regulates much better and your reporting process is as simple as a single SELECT with a simple JOIN and no need for any kind of conversion in either SQL or Excel.

Hopefully this helps.  Let me know if this solution won't work for some reason and we can see about creating a different solution.


Unable to create a datasource. Datasource fails to 'test successfully' for SSRS

The issue had to due with Windows GPO it was unknown to me at the time
http://technet.microsoft.com/en-us/library/bb326317%28v=sql.90%29.aspx


Query Rows That Have a Following Row That Meets Criteria

I've used MySQL, but it should apply to virtually all database servers.

CREATE TABLE `test1`
(
  `user` varchar(10) DEFAULT NULL,
  `logintype` int(11) DEFAULT NULL
);

mysql> SELECT * FROM test1;
+------+-----------+
| user | logintype |
+------+-----------+
| Bob  |         1 |
| Ryan |         1 |
| Tom  |         1 |
| Tom  |         3 |  <<== ALSO WORKS IF YOU HAVE ANOTHER
+------+-----------+       RECORD TOM, 5
4 rows in set (0.00 sec)

mysql>

SELECT t1.user, t1.logintype
FROM test1 t1
INNER JOIN test1 t2
ON t1.user = t2.user
AND t1.logintype != t2.logintype
GROUP BY t1.user, t1.logintype
ORDER BY logintype;

+------+-----------+
| user | logintype |
+------+-----------+
| Tom  |         1 |
| Tom  |         3 |
+------+-----------+


What is the proper way to backup MySQL database with rsnapshot?

By default, your usage of mysqldump will lock each table as it dumps it and there will not be any consistency among tables.  You will also not get any triggers, stored procedures, stored functions, or events backed up, and if any of your view definitions are invalid (referencing nonexistent tables or columns that you may have dropped or renamed since the view was defined), the dump process will terminate when that error is encountered.

If all of your tables are InnoDB, you can get a consistent snapshot by adding this:

--single-transaction

This option disables the default --lock-tables which locks each table as it is dumped, then unlocks it when done dumping it, and instead issues the following statements to the server at the beginning of the dump (which you can observe by enabling the general query log):

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

Of course, this only helps with InnoDB.  If your tables are MyISAM or any other non-transactional engine, you only have one option:

--lock-all-tables

...which locks all of the tables for the entire dump, since that's the only way to ensure consistency without transactions to assist.

To back up your stored procedures, events, and triggers, add:

--routines --events --triggers

To prevent mysqldump from failing when there's an invalid view definition

--force

To capture your binlog position and store it in the backup file, add:

--master-data=2

This option doesn't work if your machine isn't a slave.

Your question was about backing up the database "with rsnapshot" although to put a fine point on it, you're proposing backing up the database with mysqldump and then backing up the dumpfile with rsnapshot... It's hard to tell from a brief look at their site whether that's going to be a good choice, because rsnapshot appears to have file-level granularity.  It doesn't appear to store diffs, but instead stores full files for "each" file that changes between backup cycles:

  "The amount of space required is roughly the size of one full backup, plus a copy of each additional file that is changed."
  -- http://www.rsnapshot.org/rsnapshot.html

I welcome correction if I'm wrong on this point.   In this case, you only have 1 file, your dump file from mysqldump, which will of course change every time.

Whatever you do, don't entertain the thought of trying to back up the actual files (.frm, .ibd, .MYD, etc.) underlying your tables in MySQL.  That does not work with the server running even though under rare conditions you might get a backup that seems to be intact.  It isn't.

Update:

There's an important distinction between what mylvmbackup is doing and "backing up the actual files."  To use mylvmbackup, you have to be using LVM, which can be visualized as a layer of abstraction between your filesystem and your hard drive.  With LVM, you have the ability to freeze the file system and make a point-in-time snapshot of the entire filesystem.

It looks like mylvmbackup is doing a FLUSH TABLES followed by a FLUSH TABLES WITH READ LOCK prior to looking up the replication coordinates (if specified in configuration), which is the same process mysqldump does when --single-transaction and --master-data are both used.  This stablizes MySQL but it does not fully quiesce InnoDB, so when a backup like this is restored, InnoDB will think it crashed and will do crash recovery... but it should be a clean recovery.

If your web site's data files were in the same filesystem, then an LVM snapshot would contain everything consistent to a single point in time, but whether having your other web site files in the same filesystem as the database is good practice (if, indeed running your web server on the same server is good practice) is another discussion.

Whatever the approach, it's vitally important in any backup strategy to periodically verify that your backups can actually be restored (on a different machine).  The truly safest method would be to be sure innodb_fast_shutdown = 0 and actually shut down MySQL but that's probably not practical.

Of course, if you want with something like rsnapshot, you could always snapshot the web site files, then snapshot the database, then snapshot the web site files again.  If nothing changes in the site files while the database is being backed up, rsnapshot will use very little space for that second run.  If things do change, it should be fairly trivial to understand the differences if you needed to recover.


If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

From an answer by Remus Rusanu:

  Dynamic SQL runs in the same session as the calling code. The issue is
  not session, but scope. Your dynamic SQL creates the temporary tables
  inside the sp_executesql call and thus the created #temp table is only
  visible within that sp_executesql call, as documented in MSDN:

You can validate that you are using the same session in the sp_executesql  call by running:

DECLARE @DS NVARCHAR(MAX) = 'SELECT @@SPID'
EXEC sp_EXECUTESQL @DS

Knowing that sp_executesql runs under a different scope but not a different session, adding information found in the docs on temporary tables:

  Temporary tables are automatically dropped when they go out of scope,
  unless explicitly dropped by using DROP TABLE:

And that same source on the visibility of temporary tables:

  Local temporary tables are visible only in the current session

These differences in scope vs. session explain why you are able to access the temporary table from the sp_executesql call but not the other way around.


Filter ingredients choices for recipe database by category, or dynamic search?

That data model seems right for meeting the requirements you listed.

Your other questions are more about UI than about data modeling. Yes, a dynamic search box could work and might be the best solution here, especially since you don't want to make it impossible to use an unusual ingredient in a recipe. (Some of the best recipes are made with unusual ingredients!)

Regarding “driving database design from the UI”: My opinion is that you start with product and UI design, and then you build whatever database is needed to implement that. If you don't know exactly what you're trying to build as your first iteration, you're going to have a hard time with the database. But don't build more than you need for that first iteration. You can always migrate the database to a new schema later, when your requirements change.


MySQL: Grant Read-Only DB Access for one DB and Create/Drop Access for other DBs

No.  Instead use the following as a general way to provide limited actions.

Here's how to "grant" things to the user indirectly.  Create a Stored Procedure with "security definer" and be "root" when you define it.  In the SP, first check that the user is not trying to touch mysql, information_schema, or the readonly db.  Then proceed to do the CREATE or DROP.

This can obviously be extended to a variety of actions.  You should probably have one SP per action -- one for CreateDb('dbname'), one for DropDb('dbname'), and whatever else you might need.

Then train the users to use

CALL CreateDb('...');

instead of

CREATE DATABASE ...;

Note: you might need extra parameters for what should be added to the creation, such as CHARACTER SET utf8mb4.  Or you could assert more control by not giving the user a choice.


XtraBackup fails with my.cnf Percona Configuration

the hint is in last error messages as follows

xtrabackup: innodb_log_file_size = 50331648
InnoDB: Error: log file ./ib_logfile0 is of different size 268435456 bytes

my.cnf implicitly refereced by xtrabackup has innodb_log_file_size = 48M (50331648 bytes), but actual log file size is 256MB (as shown your my.cnf)

I think you should specify my.cnf explicitly like this

xtrabackup --defaults-file=/path/to/my.cnf


Oracle file permissions expdp vs dbms_datapump

I assume that the database runs on a Linux OS.
I assume that you connect with a tool like sqldeveloper to the database using a tns-connection string (maybe from a machine different)
I assume that you start expdp from the database server using not a tns-connection string but set ORACLE_SID and create a local connection.

Then, yes, it is possible that such strange things happen.

In one of your comments you wrote "both run as a job in the database". That is not the correct terminology. Both are sessions in the database And both sessions have a Linux process that corresponds to the session: the so called shadow processes. There are more complex configuration but I assume this one.

So what is the difference between your two sessiosn? The way you connect to the database and the shadow processes are created.

In the case of the sqldeveloper that uses a tns-connectstring the tool contacts the listener and the listener-process runs the oracle binary to create a process (the shadow process). If you  run the expdp tool directly on the Server using a local connection then the expdp tool runs the Oracle binary to create a process.

There are other situations were you can have such a result, e.g. with a RAC-Database. The database Directory Points on both nodes to a loacal Directory with different permissions.

But without more detailed Information it makes not sense to investigate this further.

Use a tns-connection in both Situation so that your shadow process is created in the same way.


SQL Server - How to determine ideal memory for instance?

A good starting point is best outlined by Glenn in his article : Suggested Max Memory Settings for SQL Server 2005/2008

Best is to baseline your database server usage during your full business cycle as that will give you the best number based on your workload using below PERFMON counters :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Note: If you are going to use any formula or online calculator to calculate SQL Server memory configuration then best is to read Beaware of Wow… An online calculator to misconfigure your SQL Server memory ! - by Jonathan Kehayias  first.

For Page Life Expectancy refer to Page Life Expectancy isn’t what you think… by Paul Randall.

An excellent reference :

How much memory does my SQL Server actually need? - by Jonathan Kehayias


Do I need to learn how to create a Macro/ a Routine/ a Sub Routine?

Yes. See the Stack Overflow question below for an example of how to do it using VBA code and QueryDef objects.

Is it possible to pass parameters programmatically in a Microsoft Access update query?

If you require further assistance please as a new question on Stack Overflow.


•Mirror option is greyed out with the following message: "Database mirroring is not supported for a database with a MEMORY_OPTIMIZED_DATA filegroup. Use AlwaysOn Availability Groups instead". I was hoping to stay away from AlwaysOn Availability groups by using Mirroring.

The error message is pretty self explanatory. Mirroring doesn't support memory optimized objects (In Memory OLTP you may see it called [IMOLTP] or Hekaton).

Mirroring as noted in the comments is deprecated - it's only AGs going forward. Since 2016 supports a multitude of new options in terms of clustering and availability groups, you should be able to do the exact same thing with AGs as you can with mirroring - albeit a few more steps in setup and configuration.

If you want to use mirroring, then I would suggest using a blank, new, database and configuring it there. Also, don't rely on the GUI.

  The issue with them (correct me if I'm wrong) is that they work with High Availability Cluster service.

Yes, you're correct; they do require Windows Server Failover Clustering. However, I don't see how that's a show stopper. Does it take a little extra configuration - yes. Does it require a little more understanding about your actual failure scenarios and overall architecture - sure... but it also beats having to deal with mirroring.

In fact, there were many, many, updates done to availability groups in 2016 that it is much more performant than mirroring.


optimize mysqldump for approx. 20GB databases for 8GB RAM

I'm not sure about serious improvement of total time, but below some recommendations:

I suggest You - convert all tables into InnoDB format, and adjust memory for InnoDB:

Innodb_buffer_pool_size = 4096M

To main reasons - optimisation for single engine more easy to tune and MyISAM not affected by --single-transaction

Split dump and GZIP

I do not know, what part of table You have in MyISAM, but it always better - finish all works with database as fast as possible, than continue other steps

GZIP is may be exactly slowest part of Your script and it really not optimised. It use single core not depending how many of them You have.


In MMAPv1 write locks are collection level (version 3.0 and forward; Before 3.0, locks where database level). So, if you have 1174 clients reading "db.collection" and one client who is writing that same collection, readers will wait! Especially if you have write concern w:2 (or w:"majority") and you (only) secondary is lagging.

With wiredTiger locking mechanism is on document level.

You check here how concurrency works.


It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as:

https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter
http://tav.espians.com/paving-the-way-to-securing-the-python-interpreter.html
https://stackoverflow.com/q/525056/398670

The limitations aren't anything to do with PostgreSQL its self, they're inherent to the CPython interpreter implementation or possibly even the Python language its self.

Some other languages have checked runtimes, like Perl, Java, JavaScript and Lua. Most of them have faced a series of security issues as such confined execution environments are very hard to protect against all possible jailbreak exploits.

There's really nothing stopping PostgreSQL from adding a semitrusted Python interpreter, since rexec is "good enough" for many purposes. PostgreSQL doesn't tend to be keen on only-mostly-kinda-good-enough-maybe though. It would probably only be accepted if marked superuser-only, but you could always then grant access to it for specific users. It'd be better than untrusted Python.

Personally I think PL/V8 or similar is the future here, and would like to see it move toward being supported in core.

I've also vaguely explored the idea of a trusted Mono that can load "safe" assemblies written in C#, VB.NET, IronPython, or whatever but haven't been able to do much on that topic.


I believe the answer is you cannot. I tried doing so but you get below message on Azure portal which rules out the possibility

Disks can be resized or account type changed only when they are unattached or the owner VM is de-allocated.

So you have to either stop the VM or detach the disk, change its size and attach it again. With VM being online it is not possible.


Sql Server Enterprise 2008 R2 Partition Switch with Xml Index

This is straight from BOL:  Transferring Data Efficiently by Using Partition Switching

XML columns must have the same schema. Any xml columns must by typed to the same XML schema collection.
No XML indexes allowed on the target table. There can be no XML indexes on the target table.

It appears that you need to ensure both XML columns are typed against the same schema and drop the XML index from the target partition, if applicable.
I have never personally performed a partition switch when an XML index is involved, so there could be more to it than this.
Hope this helps,
Matt


Need DDL for an Oracle Table that would be 1 row = 1 8K Block

If all you want to do is waste a lot of space, you don't need a fancy table at all. A single varchar(4000) (filled up) should be enough:

SQL> create table foo(a varchar(4000));

Table created.

SQL> insert into foo select rpad(to_char(rownum), 4000, '*')
                     from dual connect by level <= 1024 ;

1024 rows created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126

Reason: there's no room to put another 4000 byte row in a 8k block given the block and row headers.

Or play with a small row but a stupid PCT_FREE. For example:

SQL>  create table foo (a varchar(128)) pctfree 99 ;

Table created.

SQL> insert into foo select rpad(to_char(rownum), 128, '*')
                     from dual connect by level <= 1024 ;

1024 rows created.

SQL> commit ;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126

Add an ordinary primary key and you can select whatever you want from there, with one row eating up about one block in SGA.

What you must really avoid is full-scanning that table (or anything that goes to direct reads and bypasses the SGA).

That being said, have you looked into simply reducing the SGA on your test system? If your volume of data (or the "width" of the dataset you can test) is X times smaller than production, start testing with X times less SGA and tweak (up or down) to get your IO subsystem to work. (You'll probably want more than X times less if your production SGA isn't large.)

I believe you'll have a better chance of having the SGA contents more representative of a real system (especially index/data block mix).


What does SSRS technically do when it expires the cache?

I see that it's an old thread but you can essentially have different Shared Schedules to expire and refresh the cache executed in that order to solve the problem.


How to model a table in a relational database where all attributes are foreign keys to another table?

Frame challenge: unless this is a toy project not intended for a real-life application I suggest you study existing models that represent healthcare data instead of reinventing the wheel. There are more complexities in this area than you might think. One example would be the HL7 FHIR model, which, incidentally, has multiple open-source server implementations that are ready to be used.
This intro on Coursera might be helpful too.


Problem creating a transaction in an SSIS package

I solved the problem.  I had read in several places that DTC needs to be started on the source machine as well as the destination.  So in my place my workstation as well as the server the instance is on.

Once I had turned MSDTC on I received a new error:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

With the important part being The transaction manager has disabled its support for remote/network transactions.

Once there I did additional research and finally realized I hadn't configured my local DTC to allow Network Access etc.  Once I configured it to allow Network DTC Access, Allow Remote Connections and Allow Output connections everything worked smoothly.

I'm not sure this is the minimum permissions required for the local connection but it appears to be.


As Aaron Bertrand has pointed out on his comment, the only reason you has no records could be related to the order. But IMHO first query should return one record unless there are no coincidences on the related table.

CREATE TABLE T
(
    ID INT,
    F1 CHAR(1),
    PRIMARY KEY (ID DESC)
);

INSERT INTO T VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D');

CREATE TABLE TREF
(
    ID INT,
    F1 CHAR(1),
    PRIMARY KEY (ID DESC)
);

INSERT INTO TREF VALUES (1,'A'),(2,'B'),(5,'C'),(6,'D');

CREATE TABLE T1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    RES NVARCHAR(MAX)
);
GO

8 rows affected

CREATE TRIGGER TRG_T_DELETE
ON T
AFTER DELETE
AS
BEGIN

    INSERT INTO T1
    SELECT 'COUNT: ' + CAST(COUNT(*) AS VARCHAR(10)) FROM deleted;

    INSERT INTO T1
    SELECT 'deleted: ' + (SELECT * FROM deleted WHERE ID = d.ID FOR XML RAW)
    FROM   deleted d;

    INSERT INTO T1
    SELECT TOP 1
           'join: ' + (SELECT * FROM TREF WHERE ID = T.ID FOR XML RAW)
    FROM   deleted d
    JOIN   TREF T
    ON     T.ID = D.ID;

    INSERT INTO T1
    SELECT TOP 1 'IN: ' + (SELECT * FROM TREF WHERE ID = T.ID FOR XML RAW)
    FROM   TREF T
    WHERE  T.ID = (SELECT TOP 1 ID FROM deleted ORDER BY ID DESC)
END
GO

✓

DELETE FROM T WHERE ID >= 2;
SELECT * FROM T1;
GO

ID | RES
-: | :----------------------------
 1 | COUNT: 3
 2 | deleted: <row ID="2" F1="B"/>
 3 | deleted: <row ID="3" F1="C"/>
 4 | deleted: <row ID="4" F1="D"/>
 5 | join: <row ID="2" F1="B"/>

dbfiddle here


Generating Error when data size is larger then max text repl size

The SQL Server error log only captures errors having a significant severity (generally 19-25, with exceptions). See the documentation here:

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities

Imagine if all divide by zero, ambiguous column, or syntax errors flooded the error log? That log would quickly become an unmanageable mess.

What you can do is use an Alert, which can trigger notifications and other actions whenever an exception with a certain severity (or, in your case, error number) is raised:

https://docs.microsoft.com/en-us/sql/ssms/agent/alerts

For example:

EXEC sys.sp_add_alert @name = 'repl size error',
  @message_id = 7139, -- I believe this is the right message
  ...

See this doc for sp_add_alert usage:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-alert-transact-sql


Is it possible to convert .trn (transaction log) file to human readable form

I've used 3rd party tools in the past when it got too difficult to track down the log file modifications, especially as the file is minimally logged to mostly record the page modifications.  Here is a tool I've used, you can search for others if you like.  It has a functional free trial.  Sometimes I install these on a VM then remove it as it does tend to embed itself sometimes.

http://www.apexsql.com/sql_tools_log.aspx


How can I convert an Oracle dump file into SQL Server?

"is there any possibility to use another way in converting oracle dump file?"

Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication)

Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse engineering it then have to start over again when the format changes. If you want to convert Oracle data to SQL, you aren't going to be able to use a dump file unless you import into Oracle first.


unable to insert utf8mb4 characters in mysql 5.6

I had the same problem with an Ubuntu installation of MySQL 5.6.23.

I had to edit /etc/mysql/my.conf and add these entries to these sections:

[client]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4

[mysql]
default-character-set=utf8mb4

Then as root execute: service mysql restart

Both my webserver connections and my local (shell) mysql connections worked with utf8mb4.

INSERT INTO some_table_name
(some_index_id,value_to_be_utf8mb4)
VALUES (55, x'F09F9A8A');

select * from some_table_name;
| some_index_id | value_to_be_utf8mb4 |
|            55 |                   |
1 rows in set (0.00 sec)

The following are from a fresh command line instantiation of mysql:

mysql> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As a final note, the "character_set_system" is always "utf8" according to the mysql 5.x specifications, so that is normal.


Microsoft Distributed Transactions from Informix with Linux

From the comments, it sounds like you're trying to:

Load from Informix into SQL Server
Use a distributed transaction to wrap the entire load
Roll back and retry the loads if they fail

In that case, the answer isn't a distributed transaction - it's a staging database and a 2-step extract/transform/load (ETL) process:

Select the data out of Informix into a temporary staging database (this could be in SQL Server - but don't bother with a transaction)
Move the data from your staging database into your final destination - and if you do want to use a transaction to accomplish this, that's a separate debate, but at least it'll work

Benefits of this approach include:

If the Informix read portion fails for some reason and you have to retry it, the production data warehouse users still aren't affected
If the production data warehouse (SQL Server) write portion fails for some reason and you have to retry it, the Informix users aren't affected either

I know that you're going to say, "But I need to write it this way." Thing is, the reason you're not finding any results for your question out there is that people don't write it that way. As your data grows, your server grows, and your career grows, you're going to need to extract data from multiple sources and you won't be able to take locks across all of them at once to accomplish the load. That's why the ETL (extract/transform/load) design is so popular.