SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the Intranet will be using SharePoint 2013. The glitch is that SharePoint 2013 is configured to add databases automatically to our SQL Server 2012 back end, but NOT to AlwaysOn. In reading about this and in contacting Microsoft MSDN support, the default answer is "you must manually find, select, back-up and then add those new databases individually to get them into AlwaysOn."

But wait; that can be quite a task, constantly checking the SQL Server back-end servers to see what databases were created, then having to add them into AlwaysOn, 7/24!   I'm looking for a script or process that will check for new databases, back those new databases up in FULL mode, (for being added to AlwaysOn, of course) then add those databases to AlwaysOn, all automatically. Or have this run every...1-2 hours? (without user intervention)

What I've come up with so far is this script that actually identifies the newly-added databases, (not yet in AlwaysOn), and then backs them up to a shared location. My next task is to find those newly-added databases and through the various processes needed, get them added to AlwaysOn. This will involve some sort of looping action, I imagine. I'm not a T-SQL/scripting guru; is there any solution or script that I might access that would do this? (add databases to AlwaysOn automatically)?

Please advise, I'm sure I'm not the first person to have this issue. I have seen previous posts on various Internet Sites (including this one!) , and the solution is either incorrect, or states something like "sure, go ahead and just script that!". Thanks, but I need just a little more detail there.

Thanks again,

-Allen

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup

-- specify database backup directory
SET @path = '\\atel-web-be2\backups\'

DECLARE db_cursor CURSOR FOR
select name from sys.databases
where group_database_id is null and replica_id is null
    and name not in('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fileName = @path + @name + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Lots and lots of caveats here. I've tested this in a very limited way in a scenario where the data/log paths are congruent across all replicas, haven't added error handling, etc. You can call this stored procedure from the DDL trigger if you decide to go that way, as Kin suggested, or from a job, or what have you.

See additional comments inline.

CREATE PROCEDURE dbo.AddNewDBsToGroup
  @group SYSNAME = N'your_group_name', -- *** SPECIFY YOUR GROUP NAME HERE ***
  @path  SYSNAME = N'\\atel-web-be2\backups\',
  @debug BIT = 1
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE
    @sql        NVARCHAR(MAX) = N'',
    @remote_sql NVARCHAR(MAX) = N'';

  DECLARE @t TABLE(db SYSNAME);

  INSERT @t SELECT name FROM sys.databases
  WHERE replica_id IS NULL AND database_id > 4;

  DECLARE @r TABLE(s NVARCHAR(512));

  -- get the *healthy* replicas available for this group
  -- you'll need error handling to handle cases where any
  -- of the replicas is currently *not* healthy. This
  -- script does not tell you this happened.

  INSERT @r SELECT r.replica_server_name
  FROM sys.availability_groups AS g
  INNER JOIN sys.dm_hadr_availability_group_states AS s
  ON g.group_id = s.group_id
  INNER JOIN sys.availability_replicas AS r
  ON g.group_id = r.group_id
  AND r.replica_server_name <> @@SERVERNAME
  WHERE g.name = @group
  AND s.primary_replica = @@SERVERNAME
  AND s.primary_recovery_health_desc = 'ONLINE'
  AND s.synchronization_health_desc = 'HEALTHY';

  -- add the database to the group on the primary:

  SELECT @sql += N'ALTER AVAILABILITY GROUP '
    + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- back up the database locally:
  -- this assumes your database names don't have characters illegal for paths

  SET @sql = N'';

  SELECT @sql += N'BACKUP DATABASE ' + QUOTENAME(db) -- ** BACKUP HAPPENS HERE **
    + ' TO DISK = ''' + @path + db + '.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
    BACKUP LOG ' + QUOTENAME(db) +
    ' TO DISK = ''' + @path + db + '.TRN'' WITH INIT, COMPRESSION;'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- restore the database remotely:
  -- this assumes linked servers match replica names, security works, etc.
  -- it also assumes that each replica has the exact sime data/log paths
  -- (in other words, your restore doesn't need WITH MOVE)

  SET @sql = N'';

  SELECT @sql += N'RESTORE DATABASE ' + QUOTENAME(db) -- ** RESTORE HAPPENS HERE **
    + ' FROM DISK = ''' + @path + db + '.BAK'' WITH REPLACE, NORECOVERY;
    RESTORE LOG ''' + @path + db + '.TRN'' WITH NORECOVERY;
    ALTER DATABASE ' + QUOTENAME(db) + ' SET HADR AVAILABILITY GROUP = '
    + QUOTENAME(@group) + ';'
  FROM @t;

  SET @remote_sql = N'';

  SELECT @remote_sql += N'EXEC ' + QUOTENAME(s) + '.master..sp_executesql @sql;'
    FROM @r;

  IF @debug = 1
  BEGIN
    PRINT @sql;
    PRINT @remote_sql;
  END
  ELSE
  BEGIN
    EXEC sp_executesql @remote_sql, N'@sql NVARCHAR(MAX)', N'SELECT @@SERVERNAME;';
  END
END
GO

Once you've created the stored procedure, you can call it this way and look at the messages pane to see if it has identified the right group, databases, and servers before ever running it:

EXEC dbo.AddNewDBsToGroup @debug = 1;

When you are confident it is going to do the right thing (and you fully understand what 'the right thing" is), then change that to:

EXEC dbo.AddNewDBsToGroup @debug = 0;

If it fails, don't worry, it will tell you.


Grouping by date range in a column I have a table with data as:

ID    Action    Date
1     Action1   2014-01-20
2     Action1   2014-01-22
3     Action1   2014-01-28
4     Action2   2014-01-20
5     Action2   2014-01-22
6     Action2   2014-01-28

I need to group by Date, grouping with 3 days of difference:

Action       Count
Action1      2
Action2      2

If a record is grouped with a previous record, it won't be in another group:

ID    Action    Date
1     Action1   2014-01-20
2     Action1   2014-01-23
3     Action1   2014-01-24

Action       Count
Action1      2

My solution:

SELECT T1.Action, DATEDIFF(DAY,T1.Date,T2.Date) as Count
FROM Table T1
JOIN Table T2 ON T1.Action=T2.Action
WHERE DATEDIFF(DAY,T1.Date,T2.Date) BETWEEN 1 AND 3
GROUP BY T1.Action,T1.Date,T2.Date

Is this a correct solution?

In SQL-Server 2012 version, looks straightforward with the LAG() function:

SELECT
    Action,
    Number = COUNT(*)
FROM
    ( SELECT
          Action,
          Diff = DATEDIFF(day, LAG(Date) OVER (PARTITION BY Action ORDER BY Date), Date)
      FROM
          a
    ) AS t
WHERE
    Diff > 3 OR Diff IS NULL
GROUP BY
    Action ;

This should work, even in 2008 version:

SELECT
    Action,
    Number = COUNT(*)
FROM
    a
WHERE
    NOT EXISTS
    ( SELECT *
      FROM a AS b
      WHERE b.Action = a.Action
        AND ( (b.Date >= DATEADD(day, -3, a.Date) AND b.Date < a.Date)
           OR (b.Date = a.Date AND b.ID < a.ID)
            )
    )
GROUP BY
    Action ;


Should I delete disabled windows Active Directory accounts from SQL 2008? Is there a risk of not deleting unused Windows logins that have been disabled in AD or is it simply a housekeeping issue?

e.g. Windows authentication is used to provide database access. A user leaves the organisation. Their Active Directory account is disabled, but their SQL login remains.

The simplest scenario is to avoid managing individual AD accounts in SQL Server whenever possible. The most common solution is to use AD groups and provide the groups access to the correct SQL server database. Then when people join or leave the AD administrators control what their rights are within AD. This does assume that the AD team has a well defined process for managing accounts.
This was the solution suggested in the comment by @swasheck. I just thought it deserved to be an answer.


Periodic connection failure on SQL Server 2012 AlwaysOn cluster Since last wednesday (so a we're dealing with this for a week now) we're getting periodic connection problems on our production database cluster. (An alwayson cluster running on windows 2012, three machines, two sync and one async).

When it starting we noticed the following two messages in the SQL Event log that have been repeating ever since when the problem arise;

Error: 18456, Severity: 14, State: 46.
Login failed for user 'xxx' Reason: Failed to open the database 'xxx' configured in the login object while revalidating the login on the connection. [ CLIENT: xxx.xxx.xxx.xxx]

We've been trying just about everything and are reaching desperation currently. The things we've tried;

Restart our firewalls
Restart our switches
Failover and reboot the nodes (one by one)
Turn off the async node and see if anything changes
Turn off all but the primary node to see if anything changes
Replace the switch
Remove some of the network cables and run on one NCI
Remove the teaming from a server and failover to this server

Non of this seems to do anything.

The errors connecting code receives vary from a Timeout when beginning a transaction to a Timeout executing a query. (A query that does not show up in SQL Profiler when looking for queries running longer than 5000ms).

The strangest error however to me is the following;

System.Data.SqlClient.SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=0; handshake=14991;

Now I in no way consider myself a real DBA, hell I manage the software development department and am actually looking to hire one currently... However in my experience and with my limited knowledge I feel it is very odd to not see any of these queries that are timing out in my profiler...

As a matter of fact, I see no blocking queries or queries that raise my suspicion in any way...

Another thing to add here is that it seems to come in waves... Sometimes everything is fine for 15-25 minutes and then it's bad for ~2-5 minutes. This happens the whole day as well, so not during peak hours or when our normal processes run. (Basically I'm seeing errors from our API's trying to reach the database, very few processes run @ night and non of them this periodically).

It's the whole network experiencing this as well, meaning I can rule out code since we didn't publish any changes nor do we connect to the database on the same fashion. (Some through NHibernate, some ADO.NET, some old ADO)

The biggest changes happened on the network side but our sys admin seems adamant it can't be since non of the changes should affect this and they can't see anything on the switches that are suspicious.

Does anyone here have any idea what we could try / should look at next?

At least in part, this is going to be the answer found so far, and yes, it did take until last night to get anywhere at all with this problem.

Effectively we still don't really trust the network and we have engineers looking into this as we also have other complaints however despite the changes done being 100% revolving around the infrastructure and network the only thing we managed to fix and change so far was on the code end of things.

Effectively we have a piece of software that looks for files being dropped in a folder (hotfolder) and to make sure we never miss anything every ~15 minutes there is a check to see if there are any files there we're not yet processing. We have a lot of these things and they always run fine, nothing was changed in anything either.

Here is the scenario that happened;

Day we started having trouble a file was placed in a folder and processed
The same day the same file was placed there again

Now the code it self was horrible, it ran for 2-20 minutes depending and has today been rewritten quickly to run in ~20 seconds. There were two massive mistakes in the code that caused this problem;

If non of the lines in the file (it was an XML file) were meant to be processed the file would be left there. Triggering a new run every ~15 minutes (so the next problem would happen over and over again indefinitely)
Every line would call a stored procedure and use a DataReader to see if it had any result, if it did the call would move to the next line WITHOUT closing the reader

The combination of these two things (and the fact those files have 100s if not 1000s of rows) had our system crippled and on it's knees begging for the end.

Someone at the office blames 'connection pooling' and he might be right, he might not be. If i'm being honest I'm not sure why this would cause our systems to be so horribly affected (and the code causing it not to be by the way, never got an exception or problem on this piece of software. Which made it a pain to find).

So if anyone can explain why this would break our SQL server I'd be deligted, all I know is that ASYNC_NETWORK_IO as a wait type might be the biggest clue but I'm not enough of a DBA to find the culprit quickly. So again if anyone has any ideas/queries on how we could have pin pointed this I would love to give them the solution vote.


How to fix an odd LSBackup job error in Log Shipping? Configuration:

LSPrimary01 - Log Shipping Primary
LSSecondary01 - Log Shipping Secondary
OddServer03 - This server is not part of the LS configuration, the odd man out
Sean\LSServiceAccount - The domain user service account for log shipping on all servers

A colleague of mine and I were configuring Log Shipping in a test environment.  We took a full COPY_ONLY backup of a database on LSPrimary01, a log backup, copied to LSSecondary01 and restored the backup and log WITH NORECOVERY.  Then used the GUI on LSPrimary01 to configure Log Shipping.  We already configured file sharing and proper permissions for Sean\LSServiceAccount.

The following error was the result:

START OF TRANSACTION LOG BACKUP
Error: Could not retrieve backup settings for primary ID'cb1564b4-4ffd-a42d'.(Microsoft.SqlServer.Management.LogShipping)
Error: Failed to connect to server OddServer03.(Microsoft.SqlServer.ConnectionInfo)
Error: Login failed for user 'Sean\LSServiceAccount'.(Net SqlClient Data Provider)
END OF TRANSACTION LOG BACKUP

I ran the following query to verify my Log Shipping configuration:

SELECT
      [primary_database]
      ,[backup_directory]
      ,[backup_share]
      ,[backup_retention_period]
      ,[monitor_server]
      ,[user_specified_monitor]
      ,[monitor_server_security_mode]
      ,[last_backup_file]
      ,[last_backup_date]
  FROM [msdb].[dbo].[log_shipping_primary_databases]

Everything in the result set was correct, save monitor_server, it had the value of OddServer03!  As many of you know, when configuring Log Shipping through the GUI, it is damn near impossible to "accidentally" configure this option.

We never intended having a monitor server in the mix.  We use Quest Spotlight to monitor the aforementioned servers which will yield alerts when jobs fail or when log shipping gets behind.

One other perplexing matter, the job that is getting created is LSAlert_OddServer03 instead of LSAlert_LSPrimary01.

Why is the service account trying to authenticate to a server that isn't part of the configuration?

My suspicion would be that even after removing the Log Shipping configuration the Log Shipping system tables are not being cleaned up properly. I would suggest the following steps from blog.sqlauthority.com for your situation:

1. Update the arguments for the command below and execute on LSPrimary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'Primary_Database_Name'
,@secondary_server = N'Secondary_Server_Name'
,@secondary_database = N'Secondary_Database_Name'
GO

2. Update the arguments for the command below and execute on LSSecondary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_database @database = N'Primary_Database_Name'
GO

3. Run the query from your question again and examine the results.

If there are still improper entries, consider manually removing them.  An example of something you could do would be this:

SELECT * INTO Tempdb.dbo.TemporaryCopy_log_shipping_primary_databases
FROM msdb.dbo.log_shipping_primary_databases
--please note this only lasts until the database engine is restarted!

UPDATE dbo.log_shipping_primary_databases
SET monitor_server = 'LSPrimary01'
WHERE monitor_server = 'OddServer03'

Before running your own version of the commands above, which will try to clean out the system tables, it's worth considering whether you want to try skipping the GUI when trying to reset the Log Shipping configuration.  If you want to try that, I would recommend scripting out your current configuration (Right-click your database in SQL Server Management Studio --> Choose Properties --> Click Transaction Log Shipping in left sidebar -> Click Script Configuration button on the bottom right).

You can then examine the results for what commands it uses as well as whether you see it inserting the OddServer03 value anywhere.  At that point, you could clean everything up as described in the 3 steps above and then add Log Shipping back via the script you extracted and updated as needed.  That would give you 100% control over what is actually executing and it would be less likely for the OddServer03 to somehow end up in the configuration.

NOTE: If any of the above links break, use Google to search for them: - dba.stackexchange.com site for the title of 'Can I deactivate log shipping jobs without raising errors' - blog.sqlauthority.com site for the title of 'Removing Multiple Databases from Log Shipping'


How can I view the number of locks held by MySQL InnoDB, when the total is larger than 10? We have a problem where our ORM is locking more tables than I'd expect. This is causing deadlock on our production system which I'm struggling to diagnose.

I can see, using either SHOW ENGINE INNODB STATUS or by using the innotop tool the top 10 locks, but I'm unable to see the rest.

Here's the head and tail of an example output from SHOW ENGINE INNODB STATUS:

--TRANSACTION 1832883, ACTIVE 1728 sec
29 lock struct(s), heap size 3520, 14 row lock(s), undo log entries 10

...

10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

How can I view the locks beyond the first 10?

Perconda has the innodb_show_locks_held variable - is there an alternative for MySQL? I'm using MySQL 5.7 in development if that's relevant.

Thanks,
Ed

I recommend using the SYS schema that ships with MySQL 5.7.  This will show you lock waits (more useful than locks) in a pretty easy to diagnose way:

mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2014-11-11 13:39:20
                    wait_age: 00:00:07
               wait_age_secs: 7
                locked_table: `db1`.`t1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 867158
         waiting_trx_started: 2014-11-11 13:39:15
             waiting_trx_age: 00:00:12
     waiting_trx_rows_locked: 0
   waiting_trx_rows_modified: 0
                 waiting_pid: 3
               waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
             waiting_lock_id: 867158:2363:3:3
           waiting_lock_mode: X
             blocking_trx_id: 867157
                blocking_pid: 4
              blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
            blocking_lock_id: 867157:2363:3:3
          blocking_lock_mode: X
        blocking_trx_started: 2014-11-11 13:39:11
            blocking_trx_age: 00:00:16
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
1 row in set (0.01 sec)


Spotlight for SQL Server LockType explanation required I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes are blocked, I assume this happens because the awaiting command involves a lock on a table that the rest of processes are trying to update). But this is not the case actually.

In SQL Activity => Locks I've noticed that there's plenty of locks in S mode on DATABASE (Lock Type), would anyone explain me what that actually means, is it harmful for the database and what may be the cause of it?

I've used sys.dm_tran_locks and what I'm receiving are locks on resource_type DATABASE. This is basically what I'd like to get insight on. I don't understand what that means.

Shared database locks are normal, simply meaning a connection is open in that database context.  That lock prevents another session from performing an operation like dropping the database while it's being used.

A session AWAITING_COMMAND that is blocking other sessions is an indicator the session still has an open transaction holding a lock on a resource (probably not database) that is incompatible with the other session's requested lock type.  That resource is typically a table or row but you mentioned that's not the case here. You can query sys.dm_tran_locks to identify granted and waiting locks on resources for each session.  I expect Spotlight would use this or similar a DMV.


MySQL my.cnf: innodb_log_file_size is missing How to set innodb_log_file_size to MySQL database if there is no innodb_log_file_size in my.cnf configuration file? I'd like to backup via Percona Xtrabackup and the backup crashes at this point telling me that my innodb_log_file_size is different than it expects.

Login to mysql and run this

SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';

or this

SELECT variable_value FROM information_schema.global_variables
WHERE variable_name = 'innodb_log_file_size';

This will give you the size mysqld has. The default for MySQL 5.5 is 5M.

Whatever number that turns up should be placed in my.cnf. If it is not set, then place this

[mysqld]
innodb_log_file_size = (Whatever the filesize of ib_logfile0 is)

Try out xtrabackup after adding this to my.cnf.


Check if any of the values is in a subquery result I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one of two fields). I could just do stuff like this:

 select *
 from Customers
 where orderId in (select...)
 or secondaryOrderId in (select ...)

The problem is that the subquery is enormous, both in time it takes to execute, and in the screen space it takes. Is there a way to check if one of the fields contains one of the desired results?

Try:

where exists (select * ....
        where Customers.orderId = ...
        or Customers.secondaryId = ...
     )

Eg, if you were planning on:

where orderId in (select value from ...)
or secondaryorderid in (select value from ...)

Then you make it so that you only call your subquery once, and build your OR clause into it.

 where exists (select * from ...
        where Customers.orderId = value
        or Customers.secondaryOrderId = value
     )

The whole point of this is to ensure that the complicated subquery is only executed one time. That doesn't happen with a CTE, or by replacing two INs with two EXISTSs.


Issues on defining a PK on a nvarchar column and indexing the FK referring this PK Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255).

The value exists in this columns are quite short strings, such as 'A', 'B' and 'ABCDEF'.

These reference tables has been referenced by a operational table that have got many records, e.g: People.

To enhance the performance issue on the operational table (e.g.: People), two indexed have been defined on these FKs which each is a nvarchar(255) column.

CREATE TABLE RefCode1(
    [Code] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RefCode1] PRIMARY KEY CLUSTERED
(
    [Code] ASC
)
)

CREATE TABLE RefCode2(
    [Code] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RefCode1] PRIMARY KEY CLUSTERED
(
    [Code] ASC
)
)

CREATE TABLE People(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    ...
    [RefCode1] [nvarchar](255) NOT NULL,
    [RefCode2] [nvarchar](255) NOT NULL,
    ...
)

My question would be:

Does defining an index on a nvarchar column with this length causes any issues?
If the developer does not join the main table to the reference tables in the select queries written in the application, the performance issues will be avoided? (That's the justification he made when I raised a question about this design).

We use SQL Server 2008 r2.

Your thought will be appreciated.

To answer your first part, there is a performance impact because of disk space utilization and index maintenance.  The wider the column the heavier the read I/O will be upon record retrieval and the slower the index maintenance tasks will be.  Ideally, you want to create your field data type to be as narrow as possible to limit these two factors.

For your second part, unless you need to know what the value of the description field is, then there is no reason to join to the Ref tables when selecting out of the People table.  The FKs from the People table to the RefCode tables will guarantee valid values for the RefCode1 and RefCode2 columns upon insert/update into the People table.


Is mongoexport more optimized for querying than running the same query with a driver like "mongodb" for nodejs? I'm in charge of building a nodejs module that dumps varied amount of data from a mongodb into a file and then put it into S3 (sometimes a single document, sometimes thousands, like a whole collection).
The project where this module will be already uses "mongodb" driver for different use cases.
I was told that for larger dumps, using mongoexport could be better in performance, than running the query within nodejs, but this solution would imply spawning a new process and depending on a different tool (mongoexport), it certainly adds complexity to the solution.
My concern isn't the performance in terms of speed, but in terms of overhead to the DB.
Is it true that mongoexport will dump a whole collection faster (and with less "effort") than simply querying and writing the result with a fs stream?
are there guidelines or best practices to do things like this?

Mongoexport connects to the MongoDB server using the Go driver.
Any operation it performs can also be performed by any other user.
If there is no filter provided, it will hint to use {$natural:1} to read the documents in the order they appear on disk, which is more efficient than using an index when reading the entire collection.
You should be able to discover the exact queries and options used by any particular run of mongoexport by setting slowms to 0, or by increasing the logging verbosity of the query component.
Once you know what queries it uses for your use cases, you can compare that with what you could do with your driver to determine which is more efficient.


How can I restore a backup without overwriting ALL of the tables? I have a SQL Server database with 3 tables.  I need to restore a backup that contains 4 tables, while still keeping the old data in the original 3 tables.  Is this possible?

Restore the backup to a database with a new name.  You get to keep the old and new and can move tables between them with a little work


How to perform bulk delete and release free space? Good Afternoon,
We have a database that wasn't been an archive anytime. The database has grown tremendously and we are running short of space. Hence there is a need to do a bulk delete and free space. Based on my research, in order to free up space, I have 2 options: shrink DB or shrink files. The shrink DB is a bad option because it ruins the fragmentation. Can anyone guide me on how to perform a bulk delete and free space? Also, we are planning to implement this process on a monthly schedule job.
Based on the discussion, I want to clarify 3 things:

Bulk Delete Without Locking The Table
Reclaim Free Space
Schedule Monthly Job

Looking forward to expert opinion.

I suggest you take a step back and look at the overall picture:
You remove some data, and by next month you have added more data and yet again remove old data.
Let us play with some numbers:
Say you have 100 GB data file at end of month. You remove 50 GB. So you shrink the file and gain 50 GB disk. What usage do you have for these 50 GB? These 50 GB will be used over the month, so at end of next month the file is again 100 GB. You have temporarily gained 50 GB disk space, but you can't use this disk space - if you do you end up in a situation where the database is full when it need to grow.
And on top of that you have the downsides of shrinking.

It takes time.

It fragments you indexes. Sure, index fragmentation isn't as bad as
when we had spinning disks but it still come with some cost (mainly
read-ahead read crap into memory and you pollute memory with stuff
you don't need, pushing useful stuff out of memory). I've elaborated
and did some measurements regarding fragmentation here. So, frag
doesn't hurt that much, but even say 20% or 10% slowdown can be relevant - so why
fragment the indexes when it doesn't buy you anything?

And, of course, if you are in full recovery, the data that was moved by the shrink is now logged, requiring a large ldf file and also making the following log backup huge.

And all the other downsides of doing shrink - which has been elaborated in many places (mine is found here).

Bottom line is that you should consider these 100 GB as a part of the cost for this application/database and get financial approval for this. :-)


Recovery_model for backupset table null sqlserver Going through https://www.sqlshack.com/understanding-database-recovery-models/
I see that there are three types of recovery models:

SIMPLE
FULL
BULK_Logged

But for the table msdb.dbo.backupset I can see the recovery_model column can be null. In what case would this take a NULL value. How to create a backup such that it falls into this case.

Yes, it is nullable and you can run update statement on these tables. There is system view called sys.databases(Keeps attributes of database) which contains one row for each databases, you can't update it. If you do so, you will get error as below:
Msg 259, Level 16, State 1, Line 3
Ad hoc updates to system catalogs are not allowed.

So, only way to set recovery model is by running alter database and its also mentioned on the link you have provided. These options are driven by drop-down at recovery model for each database and there you have only three options i.e. Full, Simple and Bulk-logged.
This change can be done either by UI or through below command:
ALTER DATABASE DB_NAME SET RECOVERY SIMPLE  -- You may change last keyword.

There is no blank option which could set the value as null in sys.databases view.
recovery_model in table backupset can take only three values as mentioned at Official documentation:

Recovery model for the database:
FULL
BULK-LOGGED
SIMPLE

I hope this answers your question.


JSON aggregation in a self-join query I have a database in SQL Server. I need to make a join query on a table itself, the menutable.

However, the left table is missing altogether from the result set. I only get the results from the right table.

Also I cannot aggregate the right table into an array like object as I can do in PostgreSQL and cannot have a group byed left table id result set, I use group by for left table id.

I can achieve this in PostgreSQL I do the following query and get what I expect and want. I make use of PostgreSQL's json_agg aggregate function:

select mt1.*, json_agg(mt2.*)
from menutable mt1
join menutable mt2 on mt1.id = mt2.üstmenuid
group by mt1.id;

Result:

    {
      "results": [
        {
          "id": 4,
          "menuadi": "Ortak Haberler",
          "üstmenuid": null,
          "json_agg": [
            {
              "id": 5,
              "menuadi": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
              "üstmenuid": 4
            },
            {
              "id": 6,
              "menuadi": "Sed eget ipsum sit amet massa varius commodo",
              "üstmenuid": 4
            }
          ]
        },
        {
          "id": 1,
          "menuadi": "Ortak Konulu Mansetler",
          "üstmenuid": null,
          "json_agg": [
            {
              "id": 2,
              "menuadi": "In hac habitasse platea dictumst",
              "üstmenuid": 1
            },
            {
              "id": 3,
              "menuadi": "Nullam molestie augue sit amet magna bibendum imperdiet.",
              "üstmenuid": 1
            }
          ]
        }
      ]
    }

In SQL Server, I run the following, almost identical query apart form table names cases, lacking an aggregate function for the rows from right table:

SELECT  mt1.*, mt2.*
FROM dbo.MenuTable AS mt1
JOIN dbo.MenuTable AS mt2 ON mt1.ID = mt2.UstMenuID;
GROUP BY mt1.ID, mt1.MenuAdi, mt1.UstMenuID, mt2.ID, mt2.MenuAdi, mt2.UstMenuID

Result set (left table is missing altogether):

 {
  "results": [
    {
      "ID": 2,
      "MenuAdi": "In hac habitasse platea dictumst",
      "UstMenuID": 1
    },
    {
      "ID": 3,
      "MenuAdi": "Nullam molestie augue sit amet magna bibendum imperdiet.",
      "UstMenuID": 1
    },
    {
      "ID": 5,
      "MenuAdi": "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
      "UstMenuID": 4
    },
    {
      "ID": 6,
      "MenuAdi": "Sed eget ipsum sit amet massa varius commodo",
      "UstMenuID": 4
    }
  ]
}

What aggregation method should I do to have my intended result form the JOIN I have constructed in above PostgreSQL example?

Note: Menu names differ a bit in terms of case. Now I am trying to use Sequelize, but it gives the warning SQL Server does not support associating a table to itself, which I need to have JOIN.

postgres: http://rextester.com/TAYO71756

create table menutable (
    id int primary key
  , menuadi text
  , ustmenuid int null references menutable (id)
);
  insert into menutable values
 (1,'Ortak Konulu Mansetler',null)
,(2,'Kayseri Deki Bombalı Saldırının Yansımaları',1)
,(3,'Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları',1)
,(4,'Ortak Haberler',null)
,(5,'Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları',4)
,(6,'Türk Meslektaşlarımızın Yanındayız',4);

select mt1.*, json_agg(mt2.*)
from menutable mt1
join menutable mt2 on mt1.id = mt2.ustmenuid
group by mt1.id;

postgres results

+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
| id |        menuadi         | ustmenuid |                                                       json_agg                                                        |
+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+
|  1 | Ortak Konulu Mansetler | NULL      | [{"id":2,"menuadi":"Kayseri Deki Bombalı Saldırının Yansımaları","ustmenuid":1}                                       |
|    |                        |           | , {"id":3,"menuadi":"Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları","ustmenuid":1}]               |
|  4 | Ortak Haberler         | NULL      | [{"id":5,"menuadi":"Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları","ustmenuid":4} |
|    |                        |           | ,  {"id":6,"menuadi":"Türk Meslektaşlarımızın Yanındayız","ustmenuid":4}]                                             |
+----+------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------+

sql server 2014: http://rextester.com/DPK57600

rextester is currently running sql server 2014, which does not have native json support.
Instead, I will use dbo.FlattenedJSON() from Producing JSON Documents from SQL Server queries via TSQL - Phil Factor

create table menutable (
    id int primary key
  , menuadi nvarchar(128)
  , ustmenuid int null references menutable (id)
);
  insert into menutable values
 (1,'Ortak Konulu Mansetler',null)
,(2,'Kayseri Deki Bombalı Saldırının Yansımaları',1)
,(3,'Sayın Cumhurbaşkanımızın Trabzon Daki Açıklamalarının Yansımaları',1)
,(4,'Ortak Haberler',null)
,(5,'Sayın Hanımefendinin Trabzonda Şehit Aileleriyle Bir Araya Gelmesinin Yansımaları',4)
,(6,'Türk Meslektaşlarımızın Yanındayız',4);

select mt1.*, [json_agg]=dbo.FlattenedJson (
      ( select mt2.*
        from menutable mt2
        where mt2.ustmenuid = mt1.id
        for xml path, root
        )
    )
from menutable mt1
where mt1.ustmenuid is null

results:

+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+
| id |        menuadi         | ustmenuid |                                                         json_agg                                                          |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+
|  1 | Ortak Konulu Mansetler | NULL      | [                                                                                |
|    |                        |           | {"id":"2","menuadi":"Kayseri Deki Bombali Saldirinin Yansimalari","ustmenuid":"1"},                                       |
|    |                        |           | {"id":"3","menuadi":"Sayin Cumhurbaskanimizin Trabzon Daki Açiklamalarinin Yansimalari","ustmenuid":"1"}                  |
|    |                        |           | ]                                                                                |
|  4 | Ortak Haberler         | NULL      | [                                                                                |
|    |                        |           | {"id":"5","menuadi":"Sayin Hanimefendinin Trabzonda Sehit Aileleriyle Bir Araya Gelmesinin Yansimalari","ustmenuid":"4"}, |
|    |                        |           | {"id":"6","menuadi":"Türk Meslektaslarimizin Yanindayiz","ustmenuid":"4"}                                                 |
|    |                        |           | ]                                                                                |
+----+------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------+


Index cleanup/usage help My question is this:
When running sp_blitzindex, I get a lot of these 2 warnings/suggestions:

  Index Hoarder: Unused NC index

and

  Feature-Phobic Indexes: Potential filtered index (based on column name)

With index hoarder, these are indexes that I've built against a FK column.
I thought somewhere I read that was a good idea to have.

With feature-phobic, I had indexes set up to use these cols (where clause in the index definition), but when using sp_blitzindex, I got a lot of recommendations for indexes that didn't use the feature. (It either stuck the bit field in the includes or in the index itself.)

What are your thoughts on whether or not to keep the indexes on the FKs?
And, where do you think I could get the most bang for indexes (features or results of your blitzindex?)

Right now this is being used against an OLTP system that has some flat tables used for searching.

Index Hoarder is telling you that historically this index is not being used. Therefore, the maintenance of the index is a cost in CPU, I/O, and storage, but you are not getting any performance benefit from those indexes.

A general policy of considering Foreign Keys for indexes is good general plan, but an index that is not used should most likely be dropped.

Here is a link on examining the indexes yourself:
http://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/

A snippet of code from that post shows some query details:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

Consider dropping the unused indexes. If you have a yearly job that would really use the index, consider creating it just for that job.

Feature-Phobic Indexes is suggesting that a filtered index may be more valuable to you than a standard index.  A filtered index would only cover the rows that meet your search criteria.

This is a little more speculative, but the advice is to try some filtered indexes.  If they do  not work for you, you can drop them again.


Read Scale Availability Groups and Listener Is it possible to use a listener with a read scale availability group?

I have read on some sites you cannot use one but in others that you can.

If so, what IP address do you use for it?  I have tried to assign a new one in the subnet and also using the IP of the VM with the primary replica.  I can create the listener but cannot connect to it.

I am interested in using the listener to only connect to the primary replica, I am not planning to use read-only routing. My idea to use a read scale AG is to provide a limited degree of DR. I understand the listener would have to be redeployed if the AG was failed over.

Is it possible to use a listener with a read scale availability group?

Yes. Not to put a "The answer is in the blog" answer, but I did write it and it apparently survived the Blog migration fiasco. So, the answer is in this blog...

The requisite excerpt is:

Please Note: When creating the listener, you'll need to use the IP Address of the primary node. Since this does not use WSFC, any failovers will need to have the listener dropped and re-created with the current primary node's IP address to properly read only route.

  I can create the listener but cannot connect to it.

Again, the requisite information from my post:

Then we get to the listener, which I will be using the local node's IP address and default port. The value of this may not even matter if it won't be registered in DNS based on what the use cases are.

Since SQL Server doesn't create DNS records (that's the job of the network name resource in WSFC) you'll need to manually create one and manually update it should you have a failover. The Listener only facilitates the need for read only routing. If you're not using read only routing then there isn't any point to creating a listener.

  I am interested in using the listener to only connect to the primary replica, I am not planning to use read only routing.

Then if you continue to want to use a read-scale AG and not a WSFC based one (or Pacemaker/etc.) then you'll want to have a load balancer in front of the read-scale AG that can do scripted health checks to know which IP is the primary and is healthy. Otherwise, don't use read-scale AGs.


Force database to persist on commit? I was wondering whether databases such as SQL Server was writing changes to persistent medium (disk) whenever a operation is completed, but it seems not? Not even when committing a transaction it seem to do this but only commit to RAM. Correct me if I am wrong. The disk-write seem to happen on CHECKPOINT which is server-initiated and perhaps doesn't have to happen straight efter a COMMIT? In any case, my questions are:

If an update to the db is not persisted before returning OK to the client, what then will it do if the update fails in the end? How will the client know this? Can a client somehow instruct the db-server to really persist before saying "all is fine"?
Is there a good reason for not just persisting to disk directly instead of RAM in a modern server with really fast SSD-disk? Only if the performance is really critical? More so than persistence?

SQL Server does indeed write at commit. What it writes is to the transaction log, however. The data (dirty pages) can be written at a later time (for instance at checkpoint). Thanks to "force log write at commit" SQL server can re-construct a consistent view of the database at startup, where what should be committed is persisted.


Always On Availability Group, Always redirect user to read only instance We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are intending to put in the database.

The users only have rights to read from the database but when they connect (through SSMS) through the AG Listener they always connect to the active node.

I've tried to get them to access the read only instance directly but they're stuck in their ways and after a day or two they're all back on the active node again.

Is there a way for SQL Server to say this user is always going to be with intent readonly and redirect them there?

NOTE: I've tried setting 'ApplicationIntent = ReadOnly' in the additional connection parameters but this doesn't seem to redirect to the secondary node, and isn't the ideal solution as they will inevitably forget to set it up for new starters.

SQL Server 2012 Enterprise, Availability group 1 primary, 1 readable secondary with synchronous commit.

I'm not intending for a user to connect to a linked server, or through any other server. The users connect directly to the database through SSMS (no other application) and I would like the AG Listener (or something around there) to be able to direct that user to a secondary node if one is available (as it only has read access there is no point in it accessing primary) without the user having to do anything, since they move around machines and will forget to add the application intent. Also I find adding that to the additional connection parameters does not always direct you to the secondary node.

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica


Filter extended event xml_deadlock_report by database I have added an extended event log that logs blocked_process_report events and xml_deadlock_report events.

I can exclude certain databases from the blocked_process_report by adding WHERE ([database_name]<>N'XXXXXXXXXXXXXXX').

But how do I exclude a database's xml_deadlock_report events? The property "sqlserver.database_name" appears to be set to "master" for a deadlock event, no matter where the deadlock occured. I suppose this is because in theory multiple databases could be affected by the same deadlock.

The server in question contains a database which constantly has deadlocks, but which is managed by a different entity and does not concern me. I would like to prevent these events from filling up the log.

This is the script used to create the log:

CREATE EVENT SESSION [blocked_process] ON SERVER

ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([database_name]<>N'XXXXXXXXXXXXXXX')),

ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.username))

ADD TARGET package0.event_file(SET filename=N'D:\xxx\blocked_process.xel',max_file_size=(65536),max_rollover_files=(5),metadatafile=N'D:\xxx\blocked_process.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

I am using Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

SSMS: 13.0.16106.4

I can create a filter for deadlock without any error.

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([database_name]<>N'test')),

ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(package0.callstack,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([sqlserver].[database_name]<>N'test'))

ADD TARGET package0.event_file(SET filename=N'c:\blocked_process.xel',max_file_size=(65536),max_rollover_files=(5),metadatafile=N'c:\blocked_process.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Here is the picture where I am adding filter (if I am using GUI).


Benefits of making a sql server database as read-only? I am using a reporting database, which for all practical purposes is a read-only database. It is created data and dynamic reports are generated and viewed on this database.

I was thinking of making this database read-only.

Some of things I want to ask regarding read-only databases are

Is only the data read-only? Can we still create indexes or views on this database?
What are the performance benefits of using a read-only database? e.g. SELECT queries using shared locks, etc...
Is there any disadvantage of using a read-only database?
Are there any best practices to follow, while using read-only databases?

You cannot make schema changes when the database is read only but you could put all your user tables on a new file group and mark that as read only.
You can expect a modest performance benefit from absence of locking.
On versions of SQL Server prior to 2012 statistics can't be auto created or updated on read only databases.
Before making it read only you might as well remove all logical fragmentation and make page density as high as possible. Any non default FILL_FACTOR settings will not be of benefit in a read only environment. Additionally create/update any statistics anticipated to be of use for SELECT queries if on version < 2012.


GRANT permissions to all tables, views, procedures in SQL Server 2000 I was wondering if there was a fairly efficient way to use T-SQL with SQL Server 2000 syntax to GRANT SELECT, INSERT, UPDATE, DELETE, on all TABLES and VIEWS for a particular database while excluding 2 or 3 of the over 100 objects. I would also like to be able to grant EXEC privileges on all Stored Procedures.

Currently I'm using the below code to change them one by one.  Doing this for about 100 tables and 100 views is taking far too long and will take even longer through the GUI (Unless I'm doing that wrong too).

use [DATABASE_NAME]
GO
GRANT DELETE ON [dbo].[table_name]TO [user_name]
GO
use [DATABASE_NAME]
GO
GRANT INSERT ON [dbo].[table_name]TO [user_name]
GO
use [DATABASE_NAME]
GO
GRANT SELECT ON [dbo].[table_name]TO [user_name]
GO
use [DATABASE_NAME]
GO
GRANT UPDATE ON [dbo].[table_name]TO [user_name]
GO

How can I use T-SQL to cycle through ALL of the User Tables and Views to grant certain priveleges while excluding a couple objects?

Ugh, 2000.

Assuming all objects are owned by dbo, you can generate a script, e.g.:

SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type IN (N'U', N'V');

SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

Now you can copy and paste the output in case you want to exclude any entries or validate. If you just want to blindly execute, you can instead do this, but it relies on the content of any individual set of commands being < 4K:

DECLARE @sql NVARCHAR(4000) = N'';

SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'U';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'V';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

EXEC sp_executesql @sql;


Find columns with different values compared with a reference row My table, MARC, has more than 200 columns with WERKS (plant) as the key.
Most of the entries in MARC have values that come from a reference plant. So, usually, all the 200 columns will contain the same values.
I need to find the columns that have different values compared with the reference plant row, in order to find the deviations and show the names of the columns that had different values.
The main question is: how to do the select when it is not known which columns need to be selected.
Example

In the above, ABAS is the reference plant.
I expect only the first row to be returned, because only that row contains any differences compared with the reference row. Differences in that row occur in the following columns:

WERKS
PSTAT
DISMM
DISPO

The returned row should contain only those four columns.
I do not need any other columns to be able to show on the UI that these 3 columns have different values for that particular WERKS.
I am using SAP HANA.

This answer shows an implementation of the techniques mentioned by Michael Green in comments to the question. It uses SQL Server, but the basic idea is applicable to any SQL-compatible database engine that supports dynamic SQL. Support for UNPIVOT and PIVOT is not required.

The broad idea is:

Convert rows to key-value pairs
Compare with the reference row to identify differences
Convert the differences to one row per WERKS using dynamic SQL

It is the use of dynamic SQL that gets around the issue of not knowing which columns to SELECT - they are determined dynamically.

Sample table and data

CREATE TABLE #MARC
(
    c1 integer PRIMARY KEY,
    c2 integer NULL,
    c3 integer NULL,
    c4 integer NULL,
    c5 integer NULL
);

INSERT #MARC
    (c1,c2,c3,c4,c5)
VALUES
    (1, 2, 3, 4, 5), -- The reference row
    (2, 2, 3, 4, 5),
    (3, 2, 3, 4, 5),
    (4, 2, 3, 7, 9), -- Differences: 7 in c4; 9 in c5
    (5, 6, 3, 4, 8); -- Differences: 6 in c2; 8 in c5

Unpivot and find differences

-- Holds differences found (WERKS keys and column key-value pairs)
CREATE TABLE #Differences
(
    c1 integer NOT NULL,
    name varchar(2) NOT NULL,
    value integer NOT NULL
);

WITH
    Ref AS
    (
        -- Unpivoted reference row
        SELECT
            M.c1,
            CA.name,
            CA.value
        FROM #MARC AS M
        CROSS APPLY
        (
            VALUES
                ('c2', M.c2),
                ('c3', M.c3),
                ('c4', M.c4),
                ('c5', M.c5)
        ) AS CA (name, value)
        WHERE
            M.c1 = 1
    ),
    NonRef AS
    (
        -- Unpivoted non-reference rows
        SELECT
            M.c1,
            CA.name,
            CA.value
        FROM #MARC AS M
        CROSS APPLY
        (
            VALUES
                ('c2', M.c2),
                ('c3', M.c3),
                ('c4', M.c4),
                ('c5', M.c5)
        ) AS CA (name, value)
        WHERE
            M.c1 <> 1
    ),
    Differences AS
    (
        -- Find column value differences
        SELECT
            NonRef.c1,
            NonRef.name,
            NonRef.value
        FROM NonRef
        JOIN Ref
            ON Ref.name = NonRef.name      -- Same column name
            AND Ref.value <> NonRef.value  -- Different value
    )
INSERT #Differences
    (c1, name, value)
SELECT
    D.c1,
    D.name,
    D.value
FROM Differences AS D;

Differences

-- Show differences for illustrative purposes
SELECT
    D.c1,
    D.name,
    D.value
FROM #Differences AS D;

╔════╦══════╦═══════╗
║ c1 ║ name ║ value ║
╠════╬══════╬═══════╣
║  4 ║ c4   ║     7 ║
║  4 ║ c5   ║     9 ║
║  5 ║ c2   ║     6 ║
║  5 ║ c5   ║     8 ║
╚════╩══════╩═══════╝

Dynamic SQL

-- Header
DECLARE @sql varchar(max) =
    'SELECT D.c1' + CHAR(13);

-- Add aggregation
WITH D AS
(
    SELECT DISTINCT D2.name
    FROM #Differences AS D2
)
SELECT @sql +=
    (
        SELECT
            ', MAX(CASE WHEN D.name = ' + CHAR(39) + D.name + CHAR(39) +
            ' THEN D.value END) AS ' + QUOTENAME(D.name) + CHAR(13) AS [text()]
        FROM D
        FOR XML PATH (''), TYPE
    ).value('(./text())[1]', 'varchar(max)')

-- Footer
SET @sql +=
    'FROM #Differences AS D' + CHAR(13) +
    'GROUP BY D.c1;'

The constructed SQL statement in @sql is:

SELECT D.c1
, MAX(CASE WHEN D.name = 'c2' THEN D.value END) AS [c2]
, MAX(CASE WHEN D.name = 'c4' THEN D.value END) AS [c4]
, MAX(CASE WHEN D.name = 'c5' THEN D.value END) AS [c5]
FROM #Differences AS D
GROUP BY D.c1;

That is the manual PIVOT syntax. The final result is obtained by executing the SQL:

EXECUTE (@sql);

Results:

╔════╦══════╦══════╦════╗
║ c1 ║  c2  ║  c4  ║ c5 ║
╠════╬══════╬══════╬════╣
║  4 ║ NULL ║ 7    ║  9 ║
║  5 ║ 6    ║ NULL ║  8 ║
╚════╩══════╩══════╩════╝

This is the minimal set of columns describing the column differences with respect to the reference row, as required.

-- Clean up temporary tables
DROP TABLE
    #Differences,
    #MARC;

There is some SQL Server-specific syntax above, but the manual UNPIVOT can be achieved in other ways if APPLY or LATERAL is not supported e.g. with a cross join to a suitable identity matrix table. Generating the dynamic SQL uses FOR XML PATH as an efficient way to concatenate the strings, but again this can be done in any language.

Stack Exchange Data Explorer Demo


postgresql: how do I dump and restore roles for a cluster? Where are roles stored in a cluster, and how do I dump them?

I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors:

psql:mydump.sql:3621: ERROR:  role "myrole" does not exist

So apparently the dump of my db does not include roles.  I tried dumping the 'postgres' db, but I don't see the roles there either.

Do I need to use pg_dumpall --roles-only ?

Postgresql versions 8.4.8 and 9.1.4
OS: Ubuntu 11.04 Natty

From the CREATE ROLE documentation:

  Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

Since pg_dump dumps a single database, you can't extract roles with that utility.  The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only desired roles will be created in the new cluster.

Roles are stored in the pg_authid catalog, which is physically stored in the data/global/ subfolder of a PostgreSQL installation, together with the other cluster-wide tables. You can query the contents of pg_authid through the pg_roles view.

NOTE: you will need superuser rights to dump the roles.  Otherwise, you'd get a permission denied on SELECT on pg_authid - and even when a superuser grants SELECT rights, you'd get the same error.  In this case, however, you can list the roles by querying pg_authid directly, COPY it to a file and roll some magic to create the necessary CREATE ROLE and ALTER ROLE statements.


Does "Synchronous-Commit Availability Mode" ensure consistency between Replicas? If one refers to the MSDN documentation, on Synchronous-Commit Availability Mode, you may read:

  Under synchronous-commit availability mode (synchronous-commit mode),
  after being joined to an availability group, a secondary database
  catches up to the corresponding primary database and enters the
  SYNCHRONIZED state. The secondary database remains SYNCHRONIZED as
  long as data synchronization continues. This guarantees that every
  transaction that is committed on a given primary database has also
  been committed on the corresponding secondary database. When every
  secondary database on a given secondary replica is synchronized, the
  synchronization-health state of the secondary replica as a whole is
  HEALTHY.

Lets assume I have an three node Availability Group with a synchronized database in a HEALTHY state. All replicas are using synchronous-commit mode.

Additionally assume, I have configured read-only routing so that requests with ApplicationIntent=Read-Only connect to a secondary replica.

If I commit a change via a Read-Write connection then, very quickly, select the changed record via another connection using a ApplicationIntent=Read-Only connection, can I expect consistent results to be returned from both replicas, every time?

EDIT - Further Information supporting the accepted answer.

In the Microsoft Technical Paper "AlwaysOn: Offloading Read-Only Workloads to Secondary Replicas (Sunil Agarwal, July 2012)" The section under heading Data Latency reads (emphasis mine).

  The reporting workload running on the secondary replica will incur
  some data latency, typically a few seconds to minutes depending upon
  the primary workload and the network latency. The data latency exists
  even if you have configured the secondary replica to synchronous mode.
  While it is true that a synchronous replica helps guarantee no data
  loss in ideal conditions (that is, RPO = 0) by hardening the
  transaction log records of a committed transaction before sending an
  ACK to the primary, it does not guarantee that the REDO thread on
  secondary replica has indeed applied the associated log records to
  database pages. So there is some data latency. You may wonder if this
  data latency is more likely when you have configured the secondary
  replica in asynchronous mode. This is a more difficult question to
  answer. If the network between the primary replica and the secondary
  replica is not able to keep up with the transaction log traffic (that
  is, if there is not enough bandwidth), the asynchronous replica can
  fall further behind, leading to higher data latency. In the case of
  synchronous replica, the insufficient network bandwidth does not cause
  higher data latency on the secondary but it can slow down the
  transaction response time and throughput for the primary workload.

  If your reporting workload cannot tolerate any data latency, you must run
  it on the primary replica. The good news is that generally most
  reporting workloads can tolerate some data latency and therefore can
  safely be migrated to secondary replica.

Whilst the breadth of Microsoft documentation is not contradictory I feel it could be more explicit. "Synchronous" does not imply Atomicity and Consistency, as used in the ACID acronym.

SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed).

  can I expect consistent results to be returned from both replicas, every time?

Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a different meaning from the distributed (CAP) consistency. You are not guaranteed to read the most recent consistent state. Particularly, you are not guaranteed to read your own committed writes. And the reads from each replica, while each being consistent, may not match.


Q: It there a limit max amount of sleeping processes?

A: There's a limit on the number of connections to the MySQL server, that applies to all connections, not just connections in "Sleep" state.

The limit is specified in the max_connections user variable. (The default value is 151.)

There's also a limit on the number of connections from a single user account, either a non-zero value specified on the user account, otherwise derived from the max_user_connections system variable.

SHOW VARIABLES LIKE 'max%connections'

Variable_name         Value
--------------------  --------
max_connections       1000
max_user_connections  200

(On the Windows platform, there may be some additional limitations, beyond those imposed by MySQL itself, e.g. if you "churn" connections, you can temporarily exhaust the available ports, until Windows returns ports that are no longer being used.)

Reference:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections


Does querying fragmented tables in SQL Server Affect the database? On SQL Server 2008, I'm running the following query on a 220GB database to list out the indexes and their levels of fragmentation:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
ORDER BY indexstats.avg_fragmentation_in_percent DESC

It's taken 8 minutes and then I cancelled the query, as it's a production DB (no DEV to test on), on this page someone stated it affects the server and should be run at slow times:

https://serverfault.com/questions/84118/how-long-does-reindexing-take-on-sql-server

I'm just curious how it would impact the server, short of using memory/cpu for the query, is it causing locks on tables I'm not aware of?

The link you posted refers to rebuilding the indexes, which can have a performance impact as they may take indexes offline and they also cause a high amount of I.o when rebuilding.

As far as your query there will be a slight performance impact from the amount of I.o you are doing but I doubt it will be noticeable.
In your query --

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)

Specifies LIMITED mode which is the fastest mode and scans the smallest number of pages, as this is the default run level.

https://msdn.microsoft.com/en-us/library/ms188917.aspx

Paul Randal has a good post that shows the performance impact and how you can monitor if anything is happening on your system when you kick off this job.
http://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/

One other thing, it does look like you are going to be filtering out heaps in your database based on your join to index_id. I always like to confirm what is happening with heap tables and how many I have when looking at a database from a fragmentation level.

This query will return the schema name and table name for each heap table that you have...

SELECT SCH.name AS [Schema Name] ,
OBJ.name AS [Table_Name]
FROM sys.indexes INX
JOIN sys.objects OBJ
ON INX.object_id = OBJ.object_id
JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0 --filters SQL objects
AND INX.index_id = 0 --meaning it has none
AND OBJ.type = 'U'; --user table
GO


How can I join msdb.dbo.sysalerts to msdb.dbo.sysoperators? I am using SQL Server 2008 R2 and I want to create a script of my jobs.

I need to find all objects that exist in one job.

To achieve this goal, I get all operators with this query:

SELECT * FROM msdb.dbo.sysoperators

and I can find all Alerts with this query:

SELECT * FROM msdb.dbo.sysalerts

Each alert has several operators.

I want to join the two tables together.

How can I get all operators for all alerts?

You can use this query:

SELECT *
FROM msdb.dbo.sysalerts AS Ale
INNER JOIN msdb.dbo.sysnotifications AS Noti ON Ale.id = Noti.alert_id
INNER JOIN msdb.dbo.sysoperators AS Ope ON Noti.operator_id = ope.id


Creating a schema for various sizes of varchar I am redesigning a database schema for an application I am working on, and one of the major changes I am adding is the ability to dynamicly add new "fields" into a form based administration area.

Basically, a user can add a "large" field (varchar 4000), or a "small" field (varchar 255), and assign the field a name. The idea here is so I do not need to edit the code/database everytime we want new data to be featured

What would be the best way to incorperate this into a database schema? Doing something like adding a single varchar 4000 column would work, but that seems wasteful and I am looking for better ways.

Thanks

ability to dynamicly add new "fields" i guess you are talking about UDF (user defined fields)
to achieve this you have few methods but most well known is to have a table with 3 fields

Table
-AppName
-ColumnName
-Value

or Just

Table
-ColumnName
-Value

Where basically you store column name and value as rows, because you can easily add and delete rows, this method has allot of disadvantages and its ugly design, but it gets your job done.

you also have the method of adding few extra fields at the end of you table, and keeping them for incase the user decided to use them

e.g

Customers Table
-Name
-Tel
-Country
-....
-UDF1
-UDF2
-UDF3
-UDF4
-UDF5

in this method the user is only limited to a max of 5 user defined fields, but atleast you can make use of the RDBMS structure and table relations etc.
it has its pros and cons as well.

This question is asked on SO and lots of good answers were posted you can check it click here

i also suggest looking at NoSQL , because when it comes to requiring a capability to freely add columns it means you lose the benefit of an RDBMs of SQL and relation ships of tables, so its a good idea to look at the capabilities of NoSQL and its flexibility.

check out

Neo4J
Mongo DB
Couch DB


Is it possible to step through TSQL that uses variables, in SSMS? If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard.

It seems by default though, that session does not have an open transaction. Every execution is committed.

I can write my TSQL so that it begins with BEGIN TRAN and ends with a COMMIT or ROLLBACK - and this ensures that as I step through, I have the option of backing out of changes.

However the issue I have is when I declare variables. Consider:

declare @flag bit = 0;

select case when @flag = 0 then 'off' when @flag = 1 then 'on' end as FlagCheck;

Regardless of whether the above is wrapped up in a transaction, if I run the first statement on its own, then the next statement on its own, the second statement will fail because the variable has not been declared.

Is it possible to step through code like this and have SSMS understand that the effects of the earlier statement (declaring the variable, and assigning a value) should be carried over into the next step? (Again, even if wrapped in a transaction, this persistence of variables fails - even though the persistence of the data update is dependent on the transaction resolution - commit or rollback).

Every time you submit text from SSMS the batch will end when that finishes executing and any variables will go out of scope.

The only way to do this is using the debugger as illustrated below. Then you can step through line by line using the F10 or F11 keys. This should only be used in a development environment.

Also (as pointed out by @clifton_h) this feature has been removed from SSMS 18.x but is still available in the latest GA build (currently 17.9.1)


Query to test and return timed lockout in MySQL I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level).

My query looks like this:

SELECT date_last_updated FROM levels
WHERE user_id = 123 AND date_last_updated > date_sub(now(), INTERVAL 10 MINUTE);`

Please note that date_last_updated is DATETIME.

Currently, if a row is returned I know they're still in the lockout, so that's fine for a boolean result. But just a single column row of the remaining time, in minutes as an integer would be better. That is where anything > 0 indicates both lockout is in effect, and how much time is left.

How would I do this?

I think a more elegant solution is

SELECT
TIMESTAMPDIFF(MINUTE, date_last_updated, now()) as mins_since_last_update
FROM levels
WHERE id = 123

This gives me the time value as the OP requested, as an integer.


Optimizing a MySQL "IN" query I'm trying to optimize a MySQL table for a query of the form:

SELECT Col1, Col2 FROM MyTable WHERE Col3 IN('Val1','Val2','Val3',...)

I've added an index to the table:

ALTER MyTable ADD INDEX(`COL3`)

and verified that it was created, but when I analyze the query using EXPLAIN the type is still "ALL" (indicating that all rows are retrieved and searched). What am I doing wrong?

Update:

I've tried EXPLAIN SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val1'. Type is still "ALL". Here are the actual CREATE TABLE, ALTER TABLE and query:

CREATE TABLE RXNCONSO
(
   RXCUI             varchar(8) NOT NULL,
   LAT               varchar (3) DEFAULT 'ENG' NOT NULL,
   TS                varchar (1),
   LUI               varchar(8),
   STT               varchar (3),
   SUI               varchar (8),
   ISPREF            varchar (1),
   RXAUI             varchar(8) NOT NULL,
   SAUI              varchar (50),
   SCUI              varchar (50),
   SDUI              varchar (50),
   SAB               varchar (20) NOT NULL,
   TTY               varchar (20) NOT NULL,
   CODE              varchar (50) NOT NULL,
   STR               varchar (3000) NOT NULL,
   SRL               varchar (10),
   SUPPRESS          varchar (1),
   CVF               varchar(50)
)
;

ALTER TABLE `RXNCONSO` ADD INDEX(`TTY`);

EXPLAIN SELECT RXCUI, STR FROM RXNCONSO
  WHERE TTY IN ('SCD','SBD','GPCK','BPCK','SCDG','SBDG','SCDF','SBDF');

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | RXNCONSO | ALL  | NULL          | NULL | NULL    | NULL | 1139206 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Update 2:

Here's the result of running

SELECT TTY, count(1) FROM RXNCONSO
 WHERE TTY IN ('SCD','SBD','GPCK','BPCK','SCDG','SBDG','SCDF','SBDF')
 GROUP BY TTY WITH ROLLUP;

+------+----------+
| TTY  | count(1) |
+------+----------+
| BPCK |      526 |
| GPCK |      492 |
| SBD  |    21732 |
| SBDF |    14593 |
| SBDG |    21140 |
| SCD  |    36131 |
| SCDF |    14556 |
| SCDG |    16362 |
| NULL |   125532 |
+------+----------+
9 rows in set (0.38 sec)

Since the query optimizer decided a full table scan was best, you might need a new query
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val1'
UNION
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val2'
UNION
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val3'
UNION ...

This should force an indexed lookup per value followed by a merge of the query results
UPDATE 2015-12-04 15:35 EDT
Based on jkavalik's comment, I have a suggestion. Please run this
SELECT COUNT(1)/20 INTO @FivePercent FROM RXNCONSO;
SELECT * FROM
(
    SELECT TTY,COUNT(1) ValueCount
    FROM RXNCONSO GROUP BY TTY
) A WHERE ValueCount > @FivePercent
ORDER BY ValueCount DESC;

This will show which values will likely contribute to a table scan.


How can I add timestamp into my table when I use Write-DbaDbTableData? I am trying to use one table to store the SQL server disk space checking data by dbatools:
$allservers_PRD | Get-DbaDiskSpace | Write-DbaDbTableData -SqlInstance MySQL2019 -Database SQL_DBA_TASK -Table SQL_DISK_SPACE -AutoCreateTable

How can I add timestamps (what is the checking time) into my table rows as addition column when I use it?
Many thanks,

When I was arranging a similar setup in the past, I did the following:

Use -AutoCreateTable to generate the basic schema for any commands I wanted to write to the database
Modify the tables to use better data types + added a column with a default GETDATE() value
Pipe any results into the newly modified tables.

This is also helpful since any updates to the command output will break due to schema differences and force you to adjust the code and table as needed.


SQL Server 2012 compatibility with Microsoft Server 2016 This is just a really simple question before I steam roller ahead
and wreck everything.

I have ordered a server that will have Windows Server 2016 installed
however I have now realized that the tools we will be using only support
SQL Server 2012.

I'm not sure but I think Windows Server and SQL Server are completely
separate entities so would it still be OK for me to install
SQL Server 2012 on a Windows Server 2016 machine?

Yes, OS (Windows Server) and database (SQL Server) are separate entities. So the only question is compatibility between those two products.
I'm guessing it was tested and should be ok, but at this moment SQL Server 2012 doesn't have Windows Server 2016 listed (and won't have, at least not till September):

https://msdn.microsoft.com/en-us/library/ms143506%28v=sql.110%29.aspx

Best thing You can do is download latest Technical Preview and test it with SQL Server 2012 in Your environment:

https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-technical-preview


Questionnaire database design - which way is better? I have ONE long html page, several sets of questions divided into small sections (approx. 15 sub-sections in one page), the total of questions are about 100 questions: varies from input, multiple choice, checkboxes, radio buttons, textarea, and file upload. One question could contain many answers which obtained either from group of checkboxes, group of select list, group of multi-select, or all of them combined into one answer. I thought I would use this database design below but found out lately that it isn't the good approach after all.

One customer could only have one set of question: one customer per 100 questions.
For the old approach I don't keep question in the database but assign as constant in PHP coding instead. Problem is I have to compare the question in PHP to get it synchronize with the answer in the database. If one question had been altered/deleted/moved from PHP, I would definitely get lost to match it with the answer in Questionnaire database. Better solution?
Could I be able to keep multiple answers obtained from multiple elements in form into one field as one answer? How could I retrieve this field and display it again for customer viewing on form?
Which option down below should I go for?

OPTION 1: Old Approach (1 table)

TABLE: Questionnaire

ID (PK)
CustomerID
Status
A1
A2
A3
.
.
.
A100

OPTION 2: New Approach (2 tables)

TABLE: Question

QID (PK)
Question (varchar)

TABLE: Answer

AID (PK)
CustomerID
QID (int)
Answer (varchar)

Or OPTION 3?

Definitely do not hard code your questionnaire. Use a relational database or xml files.  I propose the following tables

Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.
Section: The sections a questionnaire is made up. Number of the section, section title, description.
Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).
Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.
Respondent: The persons answering the questions. Personal data, user number.
Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire (or if the survey is anonymous), this table is obsolete and can be merged with the Respondent table. Interview date (or test date or survey date), interviewer (if it applies).
Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).
Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach; however, you could decide to concatenate choices into one string or to store them as bit pattern or simplify it in some other way depending on your needs.


There's a SQL trace file being populated every day. How can I find the routine running the trace file populating it with data? So I found a strange folder inside my SQL Server. It's called Analysis and most of its files was not updated since 2015. but there are 2 trace files being updated daily. theres some useless information inside it ( only start date and end date of something ) and a TXT file with only errors. how can I find what is starting this trace ? can't find it under traces, or schedules taks on SO.

It looks like those files are coming from the Flight Recorder monitoring feature within SSAS.

To turn it off, connect to the Analysis Services server from SSMS, go to server properties, General tab, (you might need to select Show Advanced (All) Properties, and change the value under Log -> FlightRecorder -> Enabled to false.


Should I explicitly DENY UPDATE to columns that should not be updated? I am used to working in very secure environments and so I design my permissions to a very fine degree of granularity. One thing that I normally do is to explicitly DENY users the ability to UPDATE columns that should never be updated.

For example:

create table dbo.something (
    created_by varchar(50) not null,
    created_on datetimeoffset not null
);

These two columns should never be changed once the value has been set. Therefore I would explicitly DENY the UPDATE permission on them.

Recently, during a team meeting a developer raised the point that the logic to ensure the fields never get updated should be contained within the application layer and not the database layer in the event that "they need to update the values for some reason". To me that sounds like typical dev mentality (I know, I used to be one!)

I am the senior architect at my company and I have always worked on the principle of least amount of privileges required to get the app to work. All permissions are audited regularly.

What is the best practice in this scenario?

The argument doesn't make sense. I always want the controls and constraints as close to the data as possible. Putting it in the application layer means it only affects the people using the application layer, and also assumes that the code will be bug-free and the security around those code paths will be bulletproof. Those are big assumptions.

If they absolutely need to be updated, then that can be done by a person unaffected by the explicit DENY, or the person can be temporarily moved into a role that is unaffected, or the DENY can be temporarily removed. These are things that are easy for you, as the DBA, to set up auditing around. In the app? Not so much.


List objects in SQL file We are trying to migrate a database from SQL 2014 to Azure SQL.

In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..".

This task fails because the database uses a SECONDARY filegroup. I've gone through the process to move all indexes, heaps, etc. out of the SECONDARY file group into the PRIMARY filegroup.

However, I still can't delete the file in the SECONDARY filegroup because there is 43MB of data in it. Every script I've found to list all the objects in a filegroup say there is nothing remaining in the file.

How can I determine what's still in this filegroup so I can delete the file and remove the SECONDARY filegroup?

I found this script which lists LOB objects in each filegroup. Based on the results I was able to find the table still using the SECONDARY filegroup.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'


Bulk edit tables and columns descriptions In a question on StackOverflow I found this query, that displays table and column description for all the tables in current database:

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

This helps me a lot because I immediately see all the tables and columns that have no description.
Now, it would be really cool to be able to directly edit these descriptions.
Is there a software that allows bulk editing of descriptions for tables and/or columns?

You can't add them in mass but you can generate the commands to do so pretty easily.  I've added two extra columns to the query that have the sys.sp_addextendedproperty commands added if there isn't already a description.  You would still have to manually modify the description value for each but this should save you some time.

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            CASE WHEN td.value IS NULL THEN
                    'EXEC sys.sp_addextendedproperty @name=N''TableDescription'', ' +
                        '@value=N''This is the table description'' , ' +
                        '@level0type=N''SCHEMA'',@level0name=N'''+QUOTENAME(u.name)+''', ' +
                        '@level1type=N''TABLE'',@level1name=N'''+QUOTENAME(t.name)+'''' END AS CreateTableNotes,
            c.name AS [column],
            cd.value AS [column_desc],
            CASE WHEN cd.value IS NULL THEN
                    'EXEC sys.sp_addextendedproperty @name=N''ColumnDescription'', ' +
                        '@value=N''This is the column description'' , ' +
                        '@level0type=N''SCHEMA'',@level0name=N'''+QUOTENAME(u.name)+''', ' +
                        '@level1type=N''TABLE'',@level1name=N'''+QUOTENAME(t.name)+''', ' +
                        '@level2type=N''COLUMN'',@level2name=N'''+QUOTENAME(c.name)+'''' END AS CreateColumnNotes
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder


MongoDB - _id as String instead of ObjectId ? Relationship between collections or duplicate data? Scenario:
An entity called 'accessPoint' has an important attribute called 'network'. The application will display some parts of this network attribute, so I decided to store it inside accessPoint documents.
I also decided to store all networks documents (with additional attributes) in a separated collection (for performance reason: it will be used in an autocomplete input in the front end)
accessPoint: {
    id: ObjectId,
    name: String,
    uptime: Number,
    network: {
        subnet: String,
        netmask: Number
    }
}

network: {
    _id: ObjectId
    subnet: String,
    netmask: Number,
    size: Number,
    validatedAt: Date,
    attrX: String,
    attrY: String,
    attrZ: String
}

I have 3 major questions:
1 - Is it a good practice to store networks in a separated colletion? (attention to the requirement above)
2 - Is there any compliance/convention issues building a relationship between these two documents (like an SQL DBMS)?
3 - And, I was wondering... can I store _id as String instead of ObjectId?
I've read some advices, but I don't have a conclusive opinion about it.
e.g : ... network: { _id: "192.168.1.0/24" } (uniqueness guaranteed :D)
Thanks!

Is it a good practice to store networks in a separated colletion?
(attention to the requirement above)

It depends on your app and how do you use them, but I can say collection design is okay.

Is there any compliance/convention issues building a relationship
between these two documents (like an SQL DBMS)?

Generally, No-SQL databases don't use for relationship collections.

And, I was wondering... can I store _id as String instead of
ObjectId? I've read some advices, but I don't have a conclusive
opinion about it. e.g : ... network: { _id: "192.168.1.0/24" }
(uniqueness guaranteed :D)

Yes, you can. BTW, uniqueness guaranteed by mongodb. Because _id field has a unique index by default.
db.inspections.insert({ "_id": "192.168.1.0/24", "test": 1 })


SQL Server 2014 complex Stored Procedure Sorry for the Title but I haven't found a better explanation.

I need to generate a TrackingID number for shipments using data from 3 different tables:

Sales
ShippingAddress
Carriers

I built a query that increments the appropriate trackingNumber and returns it together with some extra columns. (In the real query data are stored in a temp table and other queries follow, but now the issue is here.)

Here it is

DECLARE @OrderID NVARCHAR(10) = 'B3'

UPDATE cc
SET NextSeed= IIF(nextSeed IS NULL, NewStartSeed + 1,
                  IIF(nextSeed = LastSeed, NewStartSeed, NextSeed + 1)),
    LastSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NewStopSeed, LastSeed),
    NewStartSeed = IIF(NextSeed = LastSeed OR NextSeed IS NULL, NULL, NewStartSeed),
    NewStopSeed = IIF(NextSeed=LastSeed OR NextSeed IS NULL, NULL, NewStopSeed)
OUTPUT
    COALESCE(deleted.NextSeed, inserted.nextSeed-1) CurrentSeed,
    inserted.Prefix, inserted.Suffix,
    inserted.ShippingService, inserted.CarrierCode
FROM
    Sales s
JOIN
    ShippingAddress ss ON s.shippingAddressID = ss.ShippingAddressID
JOIN
    Carriers cc ON cc.ShippingService = s.ShippingService
                AND cc.carriercode = s.carriercode
LEFT JOIN
    SurchargeAreas ssa ON ss.PostalCode = ssa.PostalCode
WHERE
    s.OrderID = @OrderID

Now I have to add an extra table that contains, for the different carriers, the PostalCodes with Surcharge:

SurchargeAreas Table

What I need is:

if Customer's PostalCode is in SurchargeArea for the selected Carrier I need to change carrier/shippingService to default Carrier/shippingService to avoid extra shipping costs.

I added to the above query the following extra condition:

and ssa.postalcode is null

That detects if the customer's postalcode is in a surcharge area, but returns zero result, and therefore does not select the default shipping service, does not increment the Seed and does not return a result.

Here is the sqlfiddle with all tables and some sample data to better understand.

In the example, the PostalCode of OrderID "B4" is in a surcharge area, while B1, B2 and B3 are not.

Therefore setting

@OrderID = 'B1' or 'B2' or 'B3'

returns data, while

@OrderID = 'B4'

does not return anything, while I would expect something like

31001     RS    NL   postnlrm    postnl

since postnlrm has 'default' column set to 1

Can you suggest how to achieve what I'm looking for?

Thanks.

Because you're essentially asking for the condition of joins to be changed through a lookup ("if my postalcode is in a surcharge area, change my carrier code joins to this, otherwise this"), I would recommend that you split this update into two sets. Here's some pseudocode to help get you started:

DECLARE @OrderID nvarchar(10)='B4'
DECLARE @mytable TABLE (CurrentSeed, Prefix, Suffix, ShippingService, CarrierCode)

-- Update Surchargeable Areas with Defaults; this assumes only ONE default for a postal code
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN SurchargeAreas     ssa on  ss.PostalCode=ssa.PostalCode
JOIN dbo.Carriers           cc  on cc.carriercode=ssa.carriercode
      and cc.[Default] = 1  -- terrible practice using a SQL keyword as a column name, by the way!!
WHERE s.OrderID=@OrderID;

-- Update non-Surchargeable Areas
UPDATE ...
OUTPUT ... INTO @mytable
FROM Sales                  s
JOIN ShippingAddress            ss  on  s.shippingAddressID=ss.ShippingAddressID
JOIN Carriers cc ON cc.ShippingService = s.ShippingService
       AND cc.carriercode = s.carriercode
LEFT JOIN SurchargeAreas        ssa on  ss.PostalCode=ssa.PostalCode
AND cc.[Default] = 0
WHERE s.OrderID=@OrderID
          AND ssa.ID IS NULL;


Resolving port 5500 not free issue during Oracle 12c Release 2 Database Configuration Assistant installation on CentOS Background

I am attempting to install Oracle 12c on CentOS 7 (deployed as a Hyper-V virtual machine) as outlined by this installation guide.

Problem

Everything with my installation goes well except for the Oracle Database Configuration Assistant.

The installation provides me with the following messages:

ACTION: Specify a free port
Action - Refer to the logs or contact Oracle Support Services.

Debuging Information and Actions Attempted

When I run a tail on the error log I get the following messages related to the installation:

INFO: ... GenericInternalPlugIn: getting configAssistantParmas.
INFO: ... GenericInternalPlugIn: checking secretArguments.
INFO: ... GenericInternalPlugIn: starting read loop.
INFO: Read: [FATAL] [DBT-06103] The port (5,500) is already in use.
INFO: [FATAL] [DBT-06103] The port (5,500) is already in use.
WARNING: Skipping line: [FATAL] [DBT-06103] The port (5,500) is already in use.
INFO: Read:    ACTION: Specify a free port.
INFO:    ACTION: Specify a free port.
WARNING: Skipping line:    ACTION: Specify a free port.
INFO: Completed Plugin named: Oracle Database Configuration Assistant

Running sudo netstat -anp | grep 550 returns the results below:

unix  3      [ ]         STREAM     CONNECTED     37299    5502/gnome-pty-help
unix  3      [ ]         STREAM     CONNECTED     37283    5501/gconfd-2
unix  3      [ ]         STREAM     CONNECTED     37287    5501/gconfd-2

To try and remedy the problem, prior to subsequent fresh installation attempts I commented out the following statements in my services file as displayed through running cat /etc/services | grep 550

new-rwho        550/tcp                 # new-who
new-rwho        550/udp                 # new-who
3m-image-lm     1550/tcp                # Image Storage license manager 3M Company
3m-image-lm     1550/udp                # Image Storage license manager 3M Company
ads             2550/tcp                # ADS
ads             2550/udp                # ADS
ssmpp           3550/tcp                # Secure SMPP
ssmpp           3550/udp                # Secure SMPP
gds-adppiw-db   4550/tcp                # Perman I Interbase Server
gds-adppiw-db   4550/udp                # Perman I Interbase Server
#fcp-addr-srvr1  5500/tcp                # fcp-addr-srvr1
#fcp-addr-srvr1  5500/udp                # fcp-addr-srvr1
#fcp-addr-srvr2  5501/tcp                # fcp-addr-srvr2
#fcp-addr-srvr2  5501/udp                # fcp-addr-srvr2
#fcp-srvr-inst1  5502/tcp                # fcp-srvr-inst1
#fcp-srvr-inst1  5502/udp                # fcp-srvr-inst1
#fcp-srvr-inst2  5503/tcp                # fcp-srvr-inst2
#fcp-srvr-inst2  5503/udp                # fcp-srvr-inst2
#fcp-cics-gw1    5504/tcp                # fcp-cics-gw1
#fcp-cics-gw1    5504/udp                # fcp-cics-gw1
checkoutdb      5505/tcp                # Checkout Database
checkoutdb      5505/udp                # Checkout Database
amc             5506/tcp                # Amcom Mobile Connect
amc             5506/udp                # Amcom Mobile Connect
fg-sysupdate    6550/tcp                # fg-sysupdate
fg-sysupdate    6550/udp                # fg-sysupdate
cloudsignaling  7550/udp                # Cloud Signaling Service

I have additionally attempted to do step 5.1 of the installation guide prior to the installation with no success.

Screenshot

Question

Why am I getting an error that the port isn't open when it doesn't seem like anything is using that port and how to I remedy this issue?

Is there are method that can be used to change the port from 5500 to a complete different one? I'm just trying to get a dummy database installed for the HR schema.

I encountered this exact error and found it was my mistake. Spent whole day for that.

Please, carefully observe your /etc/hostname and /etc/hosts.
In my case, "oem" was put in /etc/hosts and "eom" was in /etc/hostname.