Database Design - different objects with shared tagging My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here.  I'm trying to figure out the best way to design the database for an application I'll be coding.

The situation: I've got Reports in one table and Recommendations in another table.  Each Report can have many Recommendations.  I also have a separate table for Keywords (to implement tagging).  However, I want to have just one set of keywords that gets applied to both Reports and Recommendations so that searching on keywords gives you Reports and Recommendations as results.

Here's the structure I started out with:

Reports
----------
ReportID
ReportName

Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)

Keywords
----------
KeywordID
KeywordName

ObjectKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)
RecommendationID (foreign key)

Instinctively, I feel like this isn't optimal and that I should have my taggable objects inherit from a common parent, and have that comment parent be tagged, which would give the following structure:

BaseObjects
----------
ObjectID (primary key)
ObjectType

Reports
----------
ObjectID_Report (foreign key)
ReportName

Recommendations
----------
ObjectID_Recommendation (foreign key)
RecommendationName
ObjectID_Report (foreign key)

Keywords
----------
KeywordID (primary key)
KeywordName

ObjectKeywords
----------
ObjectID (foreign key)
KeywordID (foreign key)

Should I go with this second structure?  Am I missing any important concerns here?  Also, if I do go with the second, what should I use as a non-generic name to replace "Object"?

Update:

I'm using SQL Server for this project.  It's an internal application with a small number of non-concurrent users, so I don't anticipate a high load.  In terms of usage, the keywords will likely be used sparingly.  It's pretty much just for statistical reporting purposes.  In that sense, whatever solution I go with will probably only affect any developers that will need to maintain this system down the line... but I figured it's good to implement good practices whenever I can.  Thanks for all the insight!

The problem with your first example is the tri-link table.  Is that going to require one of the foreign keys on either report or recommendations to always be NULL so that keywords link only one way or the other?

In the case of your second example, the joining from the base to the derived tables now may require use of the type selector or LEFT JOINs depending on how you do it.

Given that, why not just make it explicit and eliminate all the NULLs and LEFT JOINs?

Reports
----------
ReportID
ReportName

Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)

Keywords
----------
KeywordID
KeywordName

ReportKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)

RecommendationKeywords
----------
KeywordID (foreign key)
RecommendationID (foreign key)

In this scenario when you add something else which needs to be tagged, you just add the entity table and the linkage table.

Then your search results look like this (see there is still type selection going on and turning them into generics at the object results level if you want a single results list):

SELECT CAST('REPORT' AS VARCHAR(15)) AS ResultType
    ,Reports.ReportID AS ObjectID
    ,Reports.ReportName AS ObjectName
FROM Keywords
INNER JOIN ReportKeywords
    ON ReportKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Reports
    ON Reports.ReportID = ReportKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'
UNION ALL
SELECT 'RECOMMENDATION' AS ResultType
    ,Recommendations.RecommendationID AS ObjectID
    ,Recommendations.RecommendationName AS ObjectName
FROM Keywords
INNER JOIN RecommendationKeywords
    ON RecommendationKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Recommendations
    ON Recommendations.RecommendationID = RecommendationKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'

No matter what, somewhere there is going to be type selection and some kind of branching going on.

If you look at how you would do this in your option 1, it's similar but with either a CASE statement or LEFT JOINs and a COALESCE.  As you expand your option 2 with more things being linked, you have to keep adding more LEFT JOINs where things are typically NOT being found (an object that is linked can only have one derived table which is valid).

I don't think there is anything fundamentally wrong with your option 2, and you could actually make it look like this proposal with a use of views.

In your option 1, I have some difficulty seeing why you opted for the tri-link table.


Using SQL Server 2012 for Business Intelligence Features Only, No Database Engine I would like to implement SQL Server 2012 purely for the Business Intelligence Features: SSIS, SSAS and SSRS on its own server.

I current have SQL Server 2008R2 Database Engine installed on another server. It is maintained by a vendor, so it cannot be upgraded to 2012.

From what I understand within SQL Server 20102 Business Intelligence tools, you are able to use 2008R2 databases as database targets/sources. Ideally, I would like to be able to create 2012 BI Projects: SSIS, SASS, and SRSS and deploy the solutions to Sql Server 2012, but transform the data in the 2008R2 database.

Is this setup possible? If so, other than licensing are there any drawbacks or potential problems?

Perfectly reasonable proposal. No technical issues from your description of what you're aiming for.

I'm wary of commenting on licensing issues and would encourage you to verify you are correctly covered with either Microsoft or your reseller. In principle, the same licensing would apply for this arrangement as would apply if you were also deploying the database engine to that server.


pgAgent installation with Stack Builder in PostgreSQL I have installed PostgreSQL 9.1 on ubuntu 12.04 64-bit. I try to run Aplication Stack Builder and install pgAgent.

At first I am asked for root password, which I provide, after I choose PostgreSQL 9.1 on port 5433 as the only option. On next screen I tick pgAgent and click Next a few times to get to the Setup screen with verification the postgradeSQL installation details. The Host is local, the User Name is postgres and I type my password for postgrade which I use when open pgAdmin III. When I click next I have a warning:

psql.bin: could not connect to server: Connection refused

Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connection on port 5432?

When I try pgAdmin it does work with password I provide but it looks for me like this password is not accepted by Stack Builder. I also provide my root password on the beginning so it should be able to do everything with sudo privilege. I don't know what to do now and what is the problem. Should I run something else before Stack Builder?

I know it seems like password problem but I tried all and it is not a simple password case. Did anyone have similar problem or know what to do?

You write:

  I choose PostgreSQL 9.1 on port 5433

The error message says:

  Is the server running on host "localhost" (127.0.0.1) and accepting
  TCP/IP connection on port 5432?

Emphasis mine. Who is wrong? Seems like you are connection to the wrong port / db cluster.


MariaDB startup failure on Ubuntu 18.04 I have installed MariaDb hundreds of times on previous Ubuntu versions, but on Ubuntu 18.04 I'm struggling. After the installation finishes, once I restart the PC, MariaDB starts for around 2 minutes and then stops.

When I try to login to MySQL, I get the below error:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

I was able to login before I restarted the PC with my username root and the root password. Why does everything change after the restart?

error log from /var/log/mysql/error.log

usr/sbin/mysqld: Too many arguments (first extra is 'start').

2018-09-02 17:35:48 139917209496704 [ERROR] Aborting

2018-09-02 17:38:00 140363564260480 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: The InnoDB memory heap is disabled
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Compressed tables use zlib 1.2.11
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Using Linux native AIO
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Using SSE crc32 instructions
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Completed initialization of buffer pool
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Highest supported file format is Barracuda.
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: The log sequence number 1683676 in ibdata file do not match the log sequence number 1683686 in the ib_logfiles!
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: 128 rollback segment(s) are active.
2018-09-02 17:38:00 140363564260480 [Note] InnoDB: Waiting for purge to start
2018-09-02 17:38:00 140363564260480 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.39-83.1 started; log sequence number 1683686
2018-09-02 17:38:00 140362895062784 [Note] InnoDB: Dumping buffer pool(s) not yet started
2018-09-02 17:38:00 140363564260480 [Note] Plugin 'FEEDBACK' is disabled.
2018-09-02 17:38:00 140363564260480 [Note] Recovering after a crash using tc.log
2018-09-02 17:38:00 140363564260480 [Note] Starting crash recovery...
2018-09-02 17:38:00 140363564260480 [Note] Crash recovery finished.
2018-09-02 17:38:00 140363564260480 [Note] Server socket created on IP: '127.0.0.1'.
2018-09-02 17:38:00 140363564260480 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.34-MariaDB-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 18.04
2018-09-02 17:39:30 140363563476736 [Note] /usr/sbin/mysqld: Normal shutdown
2018-09-02 17:39:30 140363563476736 [Note] Event Scheduler: Purging the queue. 0 events
2018-09-02 17:39:30 140362953770752 [Note] InnoDB: FTS optimize thread exiting.
2018-09-02 17:39:30 140363563476736 [Note] InnoDB: Starting shutdown...
2018-09-02 17:39:30 140363563476736 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2018-09-02 17:39:32 140363563476736 [Note] InnoDB: Shutdown completed; log sequence number 1683696
2018-09-02 17:39:32 140363563476736 [Note] /usr/sbin/mysqld: Shutdown complete
`

mysqld is the server.  You don't log into it as a user.

mysql is the client.  You do log into it as a user; it then connects to mysqld for you.


Log shipping always failing with NUL I have configured a log shipping for our company's primary database to restore on a separate server, with the restored database serving as report database.

The problem is that every few days, the log shipping breaks and unfortunately, more often than not, a full backup happens after the break which prevents me from doing a differential restore to "un-break" the LSN chain.  Here are the results of the LSN chain for your reference (primary server and database):

I can usually restart the LSN chain if I can do differential restore before the full backup happens but I should not really depend on that.  The main issue here is I am getting a "NUL" message in the following query, and I am 100% sure that the LSN breaks where the "NUL" happens, as the last restored log is the one with the green highlight.  I would really appreciate anyone's input.  Thanks.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'BESIDE'

SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'  AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
  THEN 'Full'
  WHEN 'I'
  THEN 'Differential'
  WHEN 'L'
  THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date

First of all, thanks to @Dan Guzman for his inputs, somehow it sparked something in my head. :)

I found out the reason for the break in the LSN chain!  As I mentioned, the chain breaks after a backup is made to 'NUL', and I just came to know that 'NUL' is a sort-of file system, where data is written to it but then deleted afterwards. So obviously, if a transaction log is written to 'NUL' it creates a valid LSN but then since it gets deleted it cannot be restored to the secondary database and therefore breaking the LSN chain.

Now, i have this quote from this white paper,

  If you select “Truncate logs” inside the SQL tab then Veeam will perform a log backup to the file name “nul” after the snapshot was produced. It will do this for all databases that are in full or bulk logged recovery model. This will render your own log backups performed after this useless.

So it is a third-party backup software, VEEAM, that is causing the issue.  Right now, what I did was I still let VEEAM manage its own backups of the primary database, but disable VEEAM from truncating logs and let it just copy the transaction logs instead.  Problem solved, now my log shipping is running without a hitch. :)


Hide Schemas and Tables I have the following structure:
Views

dbo.vSales
Sec.vSales

Tables

dbo.Security
Sec.Products
Sec.Countries
Sec.Users

I have 2 groups accessing my Database:

Administrators
Standard_User

The users are connecting using SQL Server Management Studio.
I want the Standard_User group to only to see the Sec schema, and only be able to do stuff in this Schema. At the moment, when they login they can see everything inside the database even if they don't have rights to execute or select.
Is this possible?

The "do stuff" part

Create a role, and give it the permissions you want:

USE YourDatabase;
GO
CREATE ROLE StandardUsers;
GO
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
  ON SCHEMA::Sec TO StandardUsers;
-- you have to grant object-level rights at the database level
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  TO StandardUsers;

Now add the users you want to that role:

EXEC sys.sp_addrolemember @rolename = N'StandardUsers',
  @membername = N'database_principal_name';
-- rinse, repeat

(Note that ALTER ROLE is much better syntax for managing role members, but that syntax wasn't added until SQL Server 2012.)

The "see stuff" part

Management Studio for the most part will not show users objects they are prohibited from seeing. If you do the following to the letter, and then:

CREATE LOGIN splat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE USER splat FROM LOGIN splat;
GO
EXEC sys.sp_addrolemember @rolename = N'StandardUsers', @membername = N'splat';

Now, connect to Object Explorer with this new login, and go into your database. If you expand the tables node, you should only see tables in the Sec schema.

If you see others, then it might be that this security layer was implemented in a later version of Management Studio (I'm using 2016 RC0, and don't have a 2008 R2 version anywhere within reach). If that's the case, you could always upgrade (the 2012 and 2014 versions can manage 2008 R2 just fine, as long as they aren't doing exotic things like SSIS packages).


cassandra nodes have diff value I have 3 node cluster, problem is that for some rows , updatetime column have different value on 3 node: like :
1st node : updated_on          | 2018-08-22 17:01:30+0530
2nd node : updated_on          | 2018-08-22 17:02:30+0530
3rd node : updated_on          | 2018-08-22 17:03:30+0530

Please suggest , where i need to digging.

Depending on the version of the driver you're using, the timestamp of the data could be generated either on server, or on client (Java driver 3.x, for example). You need to check that all servers and client machines are synchronized via ntp.


Linked Server Problem I have a MSSQL2008 and MSSQL2005 in my system.All SP's is up-to-date.

2008;

  Microsoft SQL Server 2008 (SP2) -
  10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft
  Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1
  (Build 7600: )

2005;

  Microsoft SQL Server 2005 -
  9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005
  Microsoft Corporation Workgroup
  Edition on Windows NT 5.2 (Build 3790:
  Service Pack 2)

Now,My question is; I setup to linked to SQL Server 2005 from SQL Server 2008 under the Server Object.But,When I query , I am receiving the following error.

select * from [192.x.x.x].DBName.dbo.TableName

Result;

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "SQLNCLI10" for
  linked server "192.x.x.x" reported an
  error. The provider did not give any
  information about the error.

  Msg 7312, Level 16, State 1, Line 1

  Invalid use of schema or catalog for
  OLE DB provider "SQLNCLI10" for linked
  server "192.x.x.x". A four-part name
  was supplied, but the provider does
  not expose the necessary interfaces to
  use a catalog or schema.

Thanks in advance,

Cheers,

I once came across a similar problem - I've just dug out the MSDN Blog article I used to fix it. Don't know if this will help you too?


Postgres: get total number of matched records using UNION I'm running a paginated SELECT ... UNION query, and I need to determine the number of pages that are in the resultset. For example, consider the query:

(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT 3

When I get the following resultset, I can determine the total number of matched records:

| id | type   | matches |
|  1 | table1 |      12 |
|  2 | table1 |      12 |
|  1 | table2 |     150 |

Summing the distinct matches columns will then give me the total number of records returned, 162. But then if my current LIMIT/OFFSET doesn't give me any records from table2, I can no longer tell how many pages of data to expect:

 | id | type   | matches |
 |  1 | table1 |      12 |
 |  2 | table1 |      12 |
 |  3 | table1 |      12 |

How can I find the total number of matched records from all clauses in a UNION even if not all clauses return records within the current LIMIT/OFFSET scope?

I think you just need to change your query...

(SELECT
  NULL AS id,
  'allTables' AS type,
  (SELECT COUNT(*) OVER() FROM table1) + (SELECT COUNT(*) OVER() FROM table2) AS matches)
UNION
(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT (1 + 3)

But I can't speak to how efficient it will be....

-- Additional

This may work, as well.  But again, efficiency will likely be poor (since it will get all results, regardless if you use it or not)

WITH results as (
  (SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
  UNION
  (SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
) SELECT NULL AS id, 'allTables' AS type, COUNT(matches) FROM results
UNION
SELECT id, type, matches FROM results
LIMIT (1 + 3)

Not very friendly to the DB, in either case.  But you only have to have your WHERE clause twice (as opposed to 4 times)


Can no longer connect to localdb after updating from Sql Server 2016 to 2019 I need to restore a backup for an assignment, but SMSS said I was running v13 while the backup was made in v15. So I download and installed SQL Server Express 2019 and uninstalled the older version.
However, SMSS can't connect anymore to (localdb)\MSSQLLocalDB.
I read somewhere to add a firewall rule for sqlservr.exe. When I try to run that file manually, it says the SQL Server installation is corrupt.
So I ran the Repair from SQL Server Installation Center, everything was okay in the output log but still no luck.
I'm pretty clueless, any help is appreciated.
Edit - the available services:

Status
Name
DisplayName

Running
MSSQL$SQLEXPRESS
SQL Server (SQLEXPRESS)

Stopped
SQLAgent$SQLEXP...
SQL Server Agent (SQLEXPRESS)

Stopped
SQLBrowser
SQL Server Browser

Running
SQLTELEMETRY$SQ...
SQL Server CEIP service (SQLEXPRESS)

Running
SQLWriter
SQL Server VSS Writer

The list of LocalDb instances:
MSSQLLocalDB

PS C:\Program Files\Microsoft SQL Server\150\Tools\Binn> SqlLocalDb.exe s "MSSQLLocalDB"
Start of LocalDB instance "MSSQLLocalDB" failed because of the following error:
Cannot create an automatic instance. See the Windows Application event log for error details.

Event viewer details:
LocalDB parent instance version is invalid: MSSQL13E.LOCALDB

check the
SQL Server Configuration Manager see manual

On the right side you see which server are running and how to call them in the brackets.
and see if it is called MSSQLLocalDB any longer


Access ('07 -'10) form is not showing entered records I have a form that I enter data into that is broken into a number of different tables for normalization.

However, once I reopen the form, only the first record is shown.
I have it set as "single form" and Data Entry is set to "no".
I've tried some macros to open the form to the last record but those don't seem to work.

Try setting it to continuous forms instead of single.

Also, if it's set to single form you should be able to navigate thorough all the records, one at time, with the navigation buttons in the lower left of the form.


BLOBs storage in a dedicated table I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into NUMBER FK columns referencing a table dedicated to BLOBs only (i.e. a PK ID column and a BLOB column). This would allow our ORM library to do lazy initialization of objects much more easily with less code.

What are the performance or data organization implications to doing this? The database is quite large in size due to these BLOBs.

Generally, if you move a BLOB column to another table, and the new BLOB column has the same attributes (such as "DISABLE STORAGE IN ROW", see my other answer), performance wouldn't change much. How Oracle internally stores LOB/BLOB/CLOB columns is, as I understand, not relevant to you, as your question deals more with the user-visible data model.

One obvious performance implication of using a foreign key is that it slows access down a little bit.

But throwing multiple BLOB columns into a single column is a very bad idea in terms of design. Although I don't know Hibernate ORM, to me your question sounds here as "Is it a good idea to spoil the design of database to fix a problem with Hibernate?". Well, it's not a good idea.

What if you need to export or import blobs referenced from only one of your tables?

How would you partition your blobs if they are in a single table? (If your database is "large", you need to plan for that already!)

What if the BLOB updates/inserts that were concurrent previously, now get serialized because you have a single BLOB column?


AlwaysOn DDL and Schema Changes Can anyone discuss how to exactly incorporate DDL Schema changes with Always On Availability Groups, step by step? We have a Primary Replica in one state, and Secondary Replica in another state location. The secondary replica will be read-only asynchronous.
If I were to make schema changes, including any wide variety...
Examples

Add/modify/delete columns on table
Add/modify/delete table foreign key constraints
Add/modify/delete clustered and non-clustered indexes
Add/modify/delete default constraints
Add/modify/delete stored procedures and functions
Add/modify/delete triggers
Add/modify/delete view

... what would I need to ensure Primary Replica DDL flows to the Secondary Replica smoothly?
My Hypothesis
Read queries on secondary replica will not affect Primary DML (data modification, inserts, updates, deletes) and will flow smoothly, since secondary replica is set to Read Snapshot Isolation.
Read queries on secondary replica will affect primary DDL (schema Changes, table structure changes), since read queries will place a schema lock.
The solution
Stop all queries on secondary replica, conduct primary replica DDL, and then DDL changes will flow over to secondary replica.

Are there any other steps needed in this process?
What else needs to carefully be done?
What is best way to close all read queries on secondary replica? Kill/Close Spid, SET SINGLE_USER WITH ROLLBACK IMMEDIATE?

This example is only for secondary read-only asynchronous. What if the secondary replica is read-only synchronous?

Other Background:
The Primary Replica is OLTP, transactions all day and night. Our company has a deployment change management time window. Say I only have 1 hour during the morning to conduct schema and DDL changes. I cannot wait for long queries to finish. In asynchronous mode, the log redo will never be current.
I remember reading the Microsoft article, Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)

The redo thread that services the AlwaysOn log stream will block trying to acquire the required SCH-M lock (is a SCH-M for any of the cases you cite, it makes no difference what DDL it is).

New queries on the secondaries that reference the locked object(s) will coalesce behind the waiting SCH-M lock (they cannot sneak ahead, to prevent starvation of the waiter). Eventually the last query holding the SCH-S will drain and unblock the redo thread. The redo thread will get the lock and resume, applying the log records.

When the log record of the DDL commit will be replayed the the lock will be released and the secondary queries will resume. All this is described also in AlwaysON – HADRON Learning Series: lock_redo_blocked/redo worker Blocked on Secondary Replica.

Sync or Async AlwaysOn make no difference, since the sync part applies to received log durability, not to replay (redo) of the received log.

Normally there is no need to stop queries on the secondary. The mechanism described will handle the situation. In the rare event that you have secondary queries that may last a really long time (+30 min) with a potential to block log redo, you should monitor for the blockage and maybe take measures.

You need to ensure that log redo is current (ie. not much log received and pending redo), but that is the normal state. Then just apply the DDL. If you notice redo blockage on the secondaries you can kill the individual SPID blocking the redo.
Note: The reason why I say 'redo is current' is simply to have control when things occur. If the redo is, say, 30 minutes behind, then you apply the DDL now and it will be replayed in 30 minutes, so killing secondary queries now is rather useless. Is not that you have to be exactly up to date, a small delay (redo queue size) should do no harm.

In async mode log redo can get current. It all depends on how fast is the primary writing. Think, what if the primary has no writes for 1h, will the secondary refuse to apply the last log received?


MySQL and window functions It seems that MySQL does not support window functions.
E.g. the simple: COUNT(*) OVER() AS cnt does not work.
What I am not sure is if this applies to commercial version as well (I assume the community version is limited subset).
If not, how does one work around this missing feature?

MySQL does not support Window Functions(*). There is what we call "a poor man's window function" in the form of GROUP_CONCAT().

There are plenty of tricks using GROUP_CONCAT to emulate window functions. They are not as pretty (syntactically) and are sometimes too limited. I've written a few. See my blog post complaining about the missing window functions, and linking to various solutions based on GROUP_CONCAT.

In particular, Selecting a specific non aggregated column data in GROUP BY and SQL: selecting top N records per group, another solution might be of interest to you and could give you a kick start.

Things you should note about GROUP_CONCAT():

Can use DISTINCT
Can use ORDER BY ... ASC/DESC
Can set SEPARATOR
As any aggregation function - it discards NULL values; plenty tricks on that.

(*) Support for Window Functions has been added in MySQL 8


Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed? I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering either not adding this field to the covering index, or indexing only the first 800 characters.

When retrieving rows from this table, will MySQL know that the matched rows have values that fit inside the indexed portion? If this is so, then I'll just index the first 800 characters and for most queries MySQL will not have to hit the table.

If MySQL does not know that the entire field was indexed for all matched rows, and thus must hit the table for this field anyway, am I doing any good with having the other SELECTed fields in the covering index?

These are my options:

# baz is a text field
SELECT foo, bar, baz FROM users WHERE id=42;

# If MySQL will know that the matched rows have values that
# fit inside the indexed portion
ALTER TABLE users ADD INDEX (id, foo, bar, baz(800));

# If MySQL will not know that the matched rows have values
# that fit inside the indexed portion and indexing the
# other SELECT fields helps
ALTER TABLE users ADD INDEX (id, foo, bar);

# If MySQL will not know that the matched rows have values
# that fit inside the indexed portion and indexing
# the other SELECT fields does not help
ALTER TABLE users ADD INDEX (id);

It depends.

Prefix indexing INDEX baz(800) is rarely useful.
MyISAM and InnoDB work dramatically differently in the area you are in.
In InnoDB, if id is the PRIMARY KEY, it is almost useless to have an index starting with id.  This is because the PK is clustered with the data.  For MyISAM, the PK is a separate BTree, so the analysis is quite different.
If you are using InnoDB, which version, etc, is the table?  Antelope, ... COMPRESSED, ...?
Adding extra indexes adds more stuff to cache, thereby possibly slowing down other queries by bumping things out of cache.  Since your 'optimization', if it works, is not likely to save more than one disk hit, you may not actually be speeding up the system any.


Keep files in Mongo as part of the data I want to create a news app that works in the following way:

The site will contain articles, and each article with have multiple versions. Each version is a docx file. The article itself does not have any content apart from the versions. The collection will look like this:

{
   Id:123,
   Versions: [{id:321, docx:"blob"},{id:444, docx:"blob"}]
}

I've read that Mongo allows you to save files in a format called gridFs, but from my understanding it keeps a different collection for the files and does not allow you to keep the files as a part of a custom collection.

Can someone suggest what is the best way to store the data in the way I described?

The problem is that MongoDB does not support file versions natively. So all we can do is to store the different versions and have some sort of metadata indicating the versions, then find the according file.

Adding the complete metadata to fs.files (the wrong way)

GridFS can store custom data in the metadata field of the files collection.

> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}
> db.fs.files.update(
    {"_id" : ObjectId("571217193380e920f9b57e8a")},
    {$set:{metadata:{Versions: [{id:321, docx:"blob"},{id:444, docx:"blob"}]}}}
  )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "metadata" : {
        "Versions" : [
            {
                "id" : 321,
                "docx" : "blob"
            },
            {
                "id" : 444,
                "docx" : "blob"
            }
        ]
    }
}

Of course, this would not make much sense, since you'd have the versions attached to a single file.

Use the uploadDatefield (the easy way)

Each time a file is saved into GridFS, MongoDB automatically adds the field uploadDate, which can be used to determine specific versions:

> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}

Note that for both files, the filename is the same, as is the md5 checksum.

Now, getting the latest version is pretty easy:

> db.fs.files.find({filename:"winstone.jar"}).sort({uploadDate:-1}).limit(1).pretty()
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar"
}

Add the version to the fs.files document (the custom way)

When storing the file, you can add the version as an arbitrary field, which, as the documentation states, is perfectly ok:

  Applications may create additional arbitrary fields.

> db.fs.files.find().pretty()
{
    "_id" : ObjectId("571217193380e920f9b57e8a"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:42:34.109Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "version" : 1
}
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "version" : 2
}

Note the version field.

Getting the latest version of a file is as straightforward as it can be

> db.fs.files.find({filename:"winstone.jar"}).sort({version:-1}).limit(1).pretty()
{
    "_id" : ObjectId("57121ac13380e9212719bc5d"),
    "chunkSize" : 261120,
    "uploadDate" : ISODate("2016-04-16T10:58:09.542Z"),
    "length" : 358383,
    "md5" : "49ea7daa98666d87e5ca3208855eed0d",
    "filename" : "winstone.jar",
    "version" : 2
}

The general problem

But there is a serious drawback with all of these approaches: You do not save the diff in case a document is saved twice, you save the whole document again.

This leads me to the conclusion that MongoDB is not really suited for your use case, unless you can live with the fact that each version will be a full copy of the data.

Personally, I'd probably use Git Large File Storage to store the files and have a metadata collection. Without knowing a lot more about your use cases, it is hard to say if that suits you.


Getting Nested query result from Cosmos DB with property named "Value" I have Cosmos Db collection, I am trying to query the "Key" "Value" pair from.
Working query:

SELECT ed
From c
JOIN ed IN c.ExtendedData
Where ed.Key = "MembershipTypeId"

And got result:

[
    {
        "ed": {
            "Key": "MembershipTypeId",
            "Value": "224"
        }
    }
]

I need to filter by "Key" and "Value", but "Value" appears to be a reserved keyword
Not working query:

SELECT ed
From c
JOIN ed IN c.ExtendedData
Where ed.Value = "224"

Got HTTP 400 with message: Syntax error, incorrect syntax near 'Value'

If property name is the same as reserved word (in this case Value),
following syntax could be used:

["Value"]

In the case above here is working query:

SELECT ed
FROM c
JOIN ed IN c.ExtendedData
WHERE ed.Key = "MembershipTypeId"
AND ed["Value"] = "224"


Calling SELECT DISTINCT on multiple columns I am unclear on exactly on,

SELECT DISTINCT col1, col2, ...
FROM table_name

When called on one column it gives back only distinct values of that column. What happens when we use it with multiple columns and should we ever do this?

How does this work exactly?

It gives you distinct combinations of all the expression in the SELECT list.

SELECT DISTINCT col1, col2, ...
FROM table_name ;

is also equivalent to:

SELECT col1, col2, ...
FROM table_name
GROUP BY  col1, col2, ... ;

Another way to look at how it works - probably more accurate - is that it acts as the common bare SELECT (ALL) and then removes any duplicate rows. See Postgres documentation about SELECT: DISTINCT clause.

  Should we ever do this?

Of course. If you need it, you can use it.


Pausing AlwaysOn Availability Group replication for failover We are currently implementing a new software package which has one part of its system using database replication (but doesn't support AlwaysOn Availability Groups) replicate from another database that does support AOAG's.

Currently, they only support replication setup via their installer, which creates the entire config on both the publisher and subscriber servers in one go.

It is using transactional replication, and there are both pull and push subscriptions.

The installer creates the distributor on the publisher, which I assume will break replication after a failover and require generation of new snapshots and reinitializion of subscriptions.

Assuming we are doing a planned failover (say for O/S patching), looking to pause replication until we can failover back to the node with the distributor.

Am I on the right track in saying the most reliable method of "pausing" replication would be to use sp_MSstopdistribution_agent on the publisher (for PUSH) and on subscriber (for PULL) before failover to the (non-distributor) node, then do a sp_MSstartdistribution_agent after we fail back again?

We are using SQL 2014 on both publisher and subscriber.

I think you are absolutely right on here:

  The installer creates the distributor on the publisher, which I assume
  will break replication after a failover and require generation of new
  snapshots and reinitializion of subscriptions.

a good way to relate the replication to the availability group is:

The primary is SP1 and the secondary replica is SS2.
The distributor is on another server SD3. The replication is working fine.
We redirected the Original Publisher to the AG Listener Name using this script:

USE distribution;
GO
EXEC sys.sp_redirect_publisher
    @original_publisher = 'MyPublisher',
    @publisher_db = 'MyPublishedDB',
    @redirected_publisher = 'MyAGListenerName';

You can find more details on this link:

Replication log reader not updated to new primary after availability group failover

In an AlwaysOn availability group a secondary database cannot be a publisher.

The failover of a distributor on an availability database is not supported.

I would suggest you use another server as a distributor, as you are probably already doing.

I normally use another server as a distributor and several distributor databases depending on the size and how busy your publications are.

I am not entirely sure about the push subscriptions but for the other stuff
you could have a look at this link:

Setting up Replication on a database that is part of an AlwaysOn Availability Group

  SRV1: Original Publisher

  SRV2: Publisher Replica

  SRV3: Publisher Replica

  SRV4: Distributor and Subscriber (You can choose a completely new
  server to be the distributor as well, however do not have a
  distributor on any of the publishers in this case as the failover of a
  distributor is not supported in this case).

maybe this will not apply on your case, but just for reference:

Configure Replication for Always On Availability Groups (SQL Server)

  Am I on the right track in saying the most reliable method of
  "pausing" replication would be to use sp_MSstopdistribution_agent on
  the publisher (for PUSH) and on subscriber (for PULL) before failover
  to the (non-distributor) node, then do a sp_MSstartdistribution_agent
  after we fail back again?

I would say in most cases yes, I used to use those procedures as you can see (scripts included) on this link:

How to restart the distributor agent of transactional replication?

Now I prefer to use the following method:

-- on the publisher
-- this gives you the distributor server name and distributor database name

sp_helpdistributor

-- now connect to the distributor server and database
-- get the job name

-- get the distributor job name for the publication you want
-- in this example my publication is called 'DEOrder'
use distribution
go

my database and publication are called ATOrder in the example below.

-- the snapshot agent
select  job_name=name, publisher_db, publication from  distribution.dbo.mssnapshot_agents
where publisher_db = N'ATOrder'

-- the distributor jon transactional replication
select job_name=name, publisher_db, publication from  distribution.dbo.MSlogreader_agents
where publisher_db = N'ATOrder'

-- if it is merge replication
select  job_name=name, publisher_db, publication  from MSmerge_agents
where publisher_db = N'ATOrder'

-- who are my publishers and who are my subscribers
select * from MSsubscriber_info

-- just checking dbs
select * from MSpublisher_databases

-- get the distributor job (log reader) for a specific publication
select job_name=name, publisher_db, publication from  distribution.dbo.MSlogreader_agents
where publisher_db = N'ATOrder'

-- take note of the job name
--my_publication_server_name_and_instance2-ATOrder-25

-- check that the job is running at the moment - it must be!!
sp_runningjobs 'my_publication_server_name_and_instance2-ATOrder-25'

--check I am targeting the right job
exec msdb.dbo.sp_help_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'

--stop the job
exec msdb.dbo.sp_stop_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'

--start the job
exec msdb.dbo.sp_start_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'

for more information, as to see if everything is going on alright, or who and what is getting a bit slow, you can check this link:

Replication Monitor Information using T-SQL

  We are using SQL 2014 on both publisher and subscriber.

Lucky you


Installing SQL Server with Virtual Account / (managed local accounts) I am installing SQL Server 2016 by using virtual accounts (which I suppose is managed local accounts) and I cannot see any limitation, drawback of using this simpler method vs. using MSAs.

Would a more experience DBA educate us?

My understanding is that: as long as you do not have any cross-server operations, you are fine. I will not cross servers, but I will cross SQL SERVER INSTANCES (on the same server). Are there any issues that I may face?

I've read these resources and another similar question.

Based on your description, I don't think you'll have any issues with either account type. One important consideration is whether you have any internal/corporate or external compliance requirements with regards to the service account used by databases.

Depending on the actual policy or compliance requirement, you might find explicit wordings about password management which doesn't exist with VAs. Real security impact is almost never the issue as most of these policies (that I've worked with anyway) are either really dated or written by a "general IT professional" or both. Trying to convince whoever manages these policies or the auditors has never worked for me. Just educating them on how VAs work can be an exercise in futility.

If you aren't affected by such policies/requirements and are unlikely to be in the foreseeable future then you'll be fine with VAs. Else, you're pretty much pushed down the path of MSA or some other form of human/tool managed account.


How to set foreign key in table in one database that references a table in another database Msg 1768, Level 16, State 0, Line 1
  Foreign key 'FK_id' references object 'dbo.student' which is not a user table.

  Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.

A FOREIGN KEY constraint cannot reference a table in a different database. It's by design.

Here's an example:

CREATE DATABASE test1
GO

USE test1;
GO

CREATE TABLE tableA (
     PK int PRIMARY KEY CLUSTERED
    ,someColumn varchar(50)
)
GO

CREATE DATABASE test2
GO

USE test2;
GO

CREATE TABLE tableB (
     PK int PRIMARY KEY CLUSTERED
    ,aForeignKey int NULL FOREIGN KEY REFERENCES test1.dbo.tableA(PK)
)
GO

This will throw an error:

Msg 1763, Level 16, State 0, Line 19
Cross-database foreign key references are not supported. Foreign key 'test1.dbo.tableA'.
Msg 1750, Level 16, State 0, Line 19
Could not create constraint. See previous errors.

An (ugly) workaround is a trigger that checks whether the data is present in the referenced table:

CREATE TRIGGER TR_Fake_FK ON dbo.tableB
    FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT *
        FROM INSERTED AS I
        WHERE NOT EXISTS (
            SELECT *
            FROM test1.dbo.tableA AS A
            WHERE I.aForeignKey = A.PK
        )
    )
    BEGIN
        RAISERROR('Violation of fake constraint',16,1);
        ROLLBACK;
    END

END

The trigger won't handle cascading behavior of foreign keys and you'll also need another trigger on the referenced table to handle UPDATEs and DELETEs. In other words: don't do it.

EDIT: In case you're wondering if a VIEW or a SYNONYM to "copy" the referenced table to the second database will work (which seems to be what you're trying to do), it won't.


How to get a COUNT for both IN and NOT IN a table I have a table that contains a master list of items and an associated table that contains a unique text string only on specific values (determined by the application that inserts the data).

Example:
    Master Table (Assets)

Asset_ID    IP Address           Operating System    Host Name
1           123.456.789.125      Windows             BobsComputer
2           987.654.321.126      Windows             TomsComputer
3           456.123.789.127      Windows             JennysComputer

The associated table Unique_ID looks like this, joined on the Asset_ID

Special_ID  Asset_ID    Unique_ID
1           1           xhsieriyfh0308487
2           3           098uindifnoei8384

What I am trying to get is a query that gives me a count of all items both IN Unique_ID and NOT IN Unique_ID
Essentially the output I'm looking for is:

With_Unique_ID      Without_Unique_ID
2                   1

I know it has something to do with making subqueries but I can't seem to find the right syntax/command order to make it work.
It's postgresql if that makes a difference.

You can use conditional aggregation with an outer join

select count(*) filter (where at.asset_id is not null) with_unique_id,
       count(*) filter (where at.asset_id is null) as without_unique_id
from assets a
  left join associated_table at on at.asset_id = a.asset_id;


SQL Server Redundancy without Client Application Changes We have a custom .NET build application that has different clients which access the DB residing on SQL Server 2005 Standard. Now we want to add another SQL Server to create redundancy so that in case of failure of the existing SQL Server, the secondary server is able to cater the requests from the clients.

I have gone through Replication (and its different types), Mirroring and Clusters. But i am a bit confused about selecting the right option. My requirement is to have a secondary server without having any major changes to the client applications. Any suggestions ???

Clustering:
For minimal changes to your client application clustering is transparent, you wouldn't need to change a thing.  Although it will require the most work to setup.  To go from a stand-alone installation which you have to a clustered setup, this will be a lot of work.  You'd basically have to build a new clustered instance & move the database across.  You'll also need shared storage to make this work.

Mirroring:
With SQL Server Standard you are stuck with High Safety mirroring which can mean a performance hit. Good news is that you don't need shared storage and you should be able to build a new 2005 instance and then setup mirroring. Bad news is you'll  need to change your connection string to include a fail-over partner if you want to have a seamless fail-over.  This also only fails over the user database, so you'll need a process to manage moving/synching SQL Agent jobs and logins.

I wouldn't recommend using SQL Server replication for redundancy.

You might also want to consider log-shipping. This would be a lower-complexity alternative to mirroring although failover is only manual.


MySQL Online DDL Index Creation I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is showing that MySQL has high disk usage and data directory is increasing in size. Can anyone explain why this is happening.

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

This is working as intended. The indexes are objects written to the disk. The index creation is also written to the online logs. Creating indexes is expected to take up disk space and cause I/O operations.


With MyISAM is there any index size savings when using INT vs BIGINT? There is obviously a four byte per row space savings in a data column when using INT vs a BIGINT type.

But I have been trying to figure out if there is also any space savings for myisam indexes on integers since they are typically unpacked.

MySQL doesn't say what the structure type is for indexes - it only calls the units "nodes" and then doesn't mention their actual size.

So given the same set of numbers, say a million rows of integers, does using INT instead of BIGINT reduce the node size? Or because there can still be an unlimited number of rows despite the integer range, does the node size always have to be rather large?

I guess I could whip up a demo to test this question but maybe someone already understands this properly on a technical level and can explain it.

Decided this was easy enough to investigate myself, even though I still do not understand the underlying specifics of why (feel free to elaborate?)

The answer is:  YES  int most definitely creates smaller indexes than BIGINT

I made two tables, first with four unsigned INT columns, second with four unsigned BIGINT

I made a compound index across all four columns for each table.

Then I added a million rows of random unsigned smallints 0-65535  to each table.

(each table has identical data, both numbers and row order)

Then I optimized and flushed both tables just to be certain.

INT

Data    17,000  KiB
Index   31,610  KiB
Total   48,610  KiB

BIGINT

Data    33,000  KiB
Index   56,921  KiB
Total   89,921  KiB

17,408,000 int.MYD
32,368,640 int.MYI

33,792,000 bigint.MYD
58,287,104 bigint.MYI

added:

I was concerned the random data repeated (I found some cases).

So I added a primary column with auto-increment to each table and emptied them. Then I filled each with the numbers from 1 to 1,000,000 in each column, incrementing for each row sequentially.

INT

Data    20,508  KiB
Index   32,301  KiB
Total   52,809  KiB

BIGINT

Data    40,039  KiB
Index   54,178  KiB
Total   94,217  KiB

So not quite 50% savings but definitely adds up, even for index storage.


Create a PK for a catalog w/o PK I have a catalog-like huge table at my client's infrastructure, that I pull into my db: no pk, and I wasn't able to find a way to create a pk from the existing columns - the table has no restrictions on duplicates etc. Also the row-number approach does not work, as the source is being overwritten with truncate daily, and it will create a wrong data-structure.

The only solution I came up with is a creation of all-field-pk, which would solve this particular issue, but it would introduce the unsolvable performance issue.

So the actual question is:
How to create a pk for a table with such data?

UPD: I really look for a solution, that generates the pk from the data, not row number etc.

You wrote: I really look for a solution, that generates the pk from the data, not row number etc.  - Would calculating a checksum and using it as a PK value be suitable for you?

Consider the following: a table with over 9 million rows, loads of duplicates, no primary key (this is just what I imagine your raw data set looks like ... I've used MySQL 5.7).  If we concatenate all values of a row, and have the SHA1 sum calculated, we get a rather good "fingerprint" of each row (yes, I know that SHA1 collisions have been found.  However, maybe this algorithm will be good enough to satisfy your requirements. Article on SHA1 collisions: https://www.theregister.co.uk/2017/02/23/google_first_sha1_collision/).

-- small table
create table t (
  n1 int
, v1 varchar(32)
, n2 int
, v2 varchar(32)
, n3 int
);

-- 25 rows, 23 unique rows
insert into t values
(1,'aaa', 1000, 'ttttt', 1111),
(1,'bbb', 1001, 'ttttt', 2222),
(1,'ccc', 1000, 'ttttt', 3333),
(1,'ddd', 1001, 'ttttt', 1111),
(1,'eee', 1000, 'ttttt', 2222),
(1,'aaa', 1001, 'ttttt', 3333), -- duplicate
(1,'eee', 1000, 'ttttt', 4444),
(1,'aaa', 1001, 'ttttt', 3333), -- duplicate
(1,'eee', 1000, 'ttttt', 5555),
(1,'aaa', 1001, 'ttttt', 3333), -- duplicate
(1,'bbb', 1000, 'x', 1111),
(1,'aaa', 1000, 'xx', 1111),
(1,'bbb', 1001, 'xxx', 2222),
(1,'ccc', 1000, 'xxxx', 3333),
(1,'ddd', 1001, 'xxxxx', 1111),
(1,'eee', 1000, 'y', 2222),
(1,'aaa', 1001, 'yy', 3333),
(1,'eee', 1000, 'yyy', 4444),
(1,'aaa', 1001, 'yyyy', 3333),
(1,'eee', 1000, 'yyyyy', 5555),
(1,'aaa', 1001, 'z', 3333),
(1,'bbb', 1000, 'zz', 1111),
(1,'aaa', 1001, 'zzz', 3333),
(1,'bbb', 1000, 'zzzz', 1111),
(1,'aaa', 1001, 'zzzzz', 3333);

Use the small table to create a bigger table -> we get the actual test data.

create table tbig
as
select t1.n1, t1.v1, t1.n2, t1.v2, t1.n3
from t t1, t t2, t t3, t t4, t t5;

-- output:
Query OK, 9765625 rows affected (24.23 sec)
Records: 9765625  Duplicates: 0  Warnings: 0

Create a table that has an additional column, containing the SHA1 for each row.

create table tbig_sha1
as
select
  sha1(concat(n1, v1, n2, v2, n3)) sha1
, n1, v1, n2, v2, n3
from tbig;

-- output:
Query OK, 9765625 rows affected (38.22 sec)
Records: 9765625  Duplicates: 0  Warnings: 0

Detect and eliminate duplicates (this may need: SET sql_mode = '';).

create table t_sha1
as
select sha1, n1, v1, n2, v2, n3
from tbig_sha1
group by sha1
order by sha1;

-- output:
Query OK, 23 rows affected (18.86 sec)
Records: 23  Duplicates: 0  Warnings: 0

Final result:

mysql> select * from t_sha1;
+------------------------------------------+------+------+------+-------+------+
| sha1                                     | n1   | v1   | n2   | v2    | n3   |
+------------------------------------------+------+------+------+-------+------+
| 0401e846eac37342dd8de769f3385d3090b9c47c |    1 | aaa  | 1000 | xx    | 1111 |
| 09c837f5a4dfa77dc25e37151ba279410ab82cd4 |    1 | aaa  | 1001 | yy    | 3333 |
| 2cde8e392e5f20c7735add5cc073466bbee77830 |    1 | aaa  | 1001 | z     | 3333 |
| 479de10da975efa02a407709d42bf6bc82c08a5e |    1 | bbb  | 1001 | ttttt | 2222 |
| 59e7b2fd58dcd2047289daf41ed165a19f2d5caf |    1 | ccc  | 1000 | xxxx  | 3333 |
| 6222c427cb2c014f982a25a2b684d3c989975164 |    1 | aaa  | 1001 | zzzzz | 3333 |
| 65715bccb92e92b5e95df308bc30aa1e70ec308c |    1 | aaa  | 1001 | zzz   | 3333 |
| 726a867623269a178fa002287448278038e92140 |    1 | eee  | 1000 | yyyyy | 5555 |
| 750054b78741acab58ba474be62ce2bcd9976ed8 |    1 | eee  | 1000 | y     | 2222 |
| 87c720132853afa3ce728caa425482a37038e617 |    1 | aaa  | 1000 | ttttt | 1111 |
| 8bcc29485d571c865de8f62d779dd1b474ddc9de |    1 | bbb  | 1000 | zz    | 1111 |
| 9df67a1d57fa702e4ce2f903ae312153aee09400 |    1 | bbb  | 1001 | xxx   | 2222 |
| a725ff1ae11a9b468392450ef1bffb28991c3b8d |    1 | bbb  | 1000 | zzzz  | 1111 |
| a8b3d416da6c222572ce63dd4ff3212a31a8b339 |    1 | eee  | 1000 | ttttt | 2222 |
| b232dd78cf258899c6df9535013c3fff2b1a835c |    1 | ddd  | 1001 | ttttt | 1111 |
| bb7e24d2df9bc71df1596a081576324ae214273a |    1 | eee  | 1000 | ttttt | 5555 |
| c062cca2e1b3d6d72c4c8420d4810115d5107136 |    1 | aaa  | 1001 | ttttt | 3333 |
| ce210358b4f24e767be6ca223cfe22d51abd563f |    1 | eee  | 1000 | ttttt | 4444 |
| e9cf971e943bcb885bd8ec10a15cb7f1325ad9f7 |    1 | aaa  | 1001 | yyyy  | 3333 |
| f57524ca9bd6a64e155b35f660a0170cdf59e518 |    1 | bbb  | 1000 | x     | 1111 |
| f7e905f522eeb34e57e87ba9e2b5e298fbe00246 |    1 | ccc  | 1000 | ttttt | 3333 |
| f8b900611fdba9592fd28417614ef93fb59dbe6f |    1 | eee  | 1000 | yyy   | 4444 |
| fe64ffc09e699b927f9e01ad790662e2de1026f5 |    1 | ddd  | 1001 | xxxxx | 1111 |
+------------------------------------------+------+------+------+-------+------+
23 rows in set (0.00 sec)

Then, we can use the "sha1" column as "PK".  Notice that we only have 23 rows now (the duplicates have been removed).

alter table t_sha1
add constraint pksha1
primary key (sha1);


How long is the output of EncryptByPassPhrase(), relative to the input? I'm starting an encryption project, using EncryptByPassPhrase(), mostly on VARCHAR values.  Naturally, the encrypted values are longer than the originals.  Is there a formula I can use to calculate how long I need to make the new VARBINARY fields, so as to hold any possible value of the original VARCHAR field?

E.g., the first field I examined has values of up to 37 characters in length, and the encrypted values are up to 100 bytes; another has values up to 50 chars and encrypted values are up to 124 bytes.  However, values as short as two or three characters can encrypt to 76 bytes.  If I size the new fields as 75 + X bytes, will I have room to store the encrypted version of any possible text value of length X or less?

As far as my testing goes (using SQL Server Express 2014, SP1 and SQL Server Developer 2012 SP2, both 64-it), the formula when not using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything})

is:

28 + (8 * (DATALENGTH(@ClearText) / 8))

Try the following:

DECLARE @ClearText VARCHAR(8000);
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';

SELECT LEN(ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', @ClearText)) AS [ActualLength],
       28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLength];

Returns:

ActualLength    EstimatedLength
92              92

And, if you change the datatype of @ClearText to instead be NVARCHAR(4000), and run it again, it returns:

ActualLength    EstimatedLength
156             156

Note: the formula looks like it can be reduced by cancelling out the 8s. However, that will cause it to not work correctly since it is banded-ranges of datalengths that fit into a "bucket":

input bytes    result length
-----------    -------------
 1 -  7        28
 8 - 15        36
16 - 23        44
24 - 31        52

So the (DATALENGTH(@ClearText) / 8) portion of the formula is enforcing that decimal values are ignored rather than the value be rounded. And that is accomplished by default behavior of dividing two INT values ;-).

UPDATE:

The testing done above does not use an option available to ENCRYPTBYPASSPHRASE: specifying an "authenticator". Doing so adds 16 bytes to the minimum length, and then while the increments are still in 8-byte steps, and while the banded-ranges are still 8 bytes each, the initial range is only 4 bytes so the ranges are offset by 4 as compared to the range boundaries when not using an authenticator. To help illustrate, the following chart shows the ranges and their corresponding result lengths:

input bytes    result length
-----------    -------------
 0 -  3        44
 4 - 11        52
12 - 19        60
20 - 27        68

The formula when using an authenticator for the return value (VARBINARY) length of:

ENCRYPTBYPASSPHRASE('_My_PassPhr@zE_yo_', {anything}, 1, {anything_1-128_bytes})

is:

44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8))

NOTES:

The length of PassPhrase has no effect on the result length
An empty string for PassPhrase has no effect on the result length
In order for an Authenticator value to have any effect, the value of Add_Authenticator (the 3rd input parameter) must be set to 1
The length of Authenticator has no effect on the result length as long as it is at least 1.
An empty string for Authenticator will have an effect on the result length, and that effect is the same as setting Add_Authenticator to 0.
If either Add_Authenticator is set to 0, or Authenticator is an empty string or NULL, then the formula is the same as if there was no "authenticator".

The following is an expanded and improved test that shows both with and without an "authenticator", and makes it easier to change the @PassPhrase value:

DECLARE @ClearText NVARCHAR(4000),
        @Authenticator sysname,
        @PassPhrase VARCHAR(100);

SET @PassPhrase = '_My_PassPhr@zE_yo_';
SET @ClearText = 'testdfdf gkdj flkgjdlfkgjdlf gjlf gklf TE%$%^&^%HFGHFhg fkgh jfgkhæ';
SET @Authenticator = REPLICATE(N' ', 128);

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText))
                                         AS [ActualLengthSansAuthenticator],
       28 + (8 * (DATALENGTH(@ClearText) / 8)) AS [EstimatedLengthSansAuthenticator];

SELECT LEN(ENCRYPTBYPASSPHRASE(@PassPhrase, @ClearText, 1, @Authenticator))
                                         AS [ActualLengthWithAuthenticator],
       44 + (8 * ((DATALENGTH(@ClearText) + 4) / 8)) AS [EstimatedLengthWithAuthenticator];


How to insert big blob data in Oracle? I'm trying the following:

INSERT INTO (blob_column) VALUES (HEXTORAW('very big hex here'))

But I'm getting the following error:

  ORA-01704: string literal too long

How can I workaround that? Is there any other syntax to insert a binary without converting from string?

If you are doing this from an application (VB.Net or C++ or php or whatever), then you must load the data in chunks, if the data is larger than 4000 bytes (thanks, mustaccio) OR if the data contains values that can't be sent as a string (Binary data).

The process is to take your data, split it into an array of byte "chunks" that are each somewhere less than 4000 bytes per chunk. The examples below provide ways to do this in a few languages.

Here is a PHP example from Oracle for using byte chunks.

Here is a C++ example from Stack Overflow.

Here is a C#.Net example from Code Project.

If you're doing this from scripts, check out SQL*Loader.

LOAD DATA
INFILE example13.dat
INTO TABLE EXAMPLE13
FIELDS TERMINATED BY ','
( EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR(60),
"IMAGE" BFILE(CONSTANT "ORDIMGDIR", RES_FILE)
)

If one of these examples isn't the right programming language or doesn't help you, let me know and I'll try to find you one for that language.


How does Postgres store many varchars on disk? I have a table that is +80 gigs with 200 million rows. I'm trying to speed it up and I noticed that it has a large number of varchar columns. In the schema, their lengths range from 15 chars to 250 chars with most set at 50. The total length of all the varchars is 850 bytes. In actual usage, a lot of the fields are null or the strings are really short.

I know that Postgres uses an 8k page size. Now if I have to do a full table scan and assuming worst case scenario, 8k / 850 = 9.6 records per page. Going through my full table is going to (and does) take a long time. In actuality however, since most of those fields are empty, how much space will be allocated on disk for those varchars? Will there be more records per page or does Postgres put in a little padding just to make things easy for updates later?

The reason I'm asking is because I'm exploring the idea of improving performance by kicking as many of the infrequently accessed varchars columns as possible out of this table and into another one that we would access via a join.

Any confirmation or denial of my logic is appreciated.

mj

Your considerations are sound, but all of this is already taken care of automatically by PostgreSQL:

A NULL value takes up no space at all in a PostgreSQL table row.
A varchar will only occupy as much space as the value actually has bytes. The length limit (type modifier) does not waste any space.
For rows that are really wide (2000 bytes and above), the varchars are first compressed and then, if the result is still too large, stored externally in a TOAST table. If you SELECT from such a row, the toasted values are not read unless you specify the column values are requested.

But if the size limit for a row is 850 bytes, that is too small to get in that range.

You can check the estimated average row size in bytes by looking at the width column in the output of EXPLAIN SELECT * FROM tablename.

PostgreSQL v12 introduced the toast_tuple_target storage parameter which would enable you to reduce the limit for TOASTing data. If you tune that, and after rewriting the table with VACUUM (FULL), PostgreSQL can store the data in the way you want to. Then you can see if that actually gets you better performance.


Reason for @@TRANCOUNT check? we're using the SQL functions from Ola Hallengren since many years. Sometime between 2015 and 2018 the check for open transactions was added in IndexOptimize. So now, after upgrade to the current version, the sp fails with "The transaction count is not 0."

Question: What's the functional reason for the check? It seems to me, that the old version w/o the check was running fine too. Is it safe to remove the check?

Regards
Holger

My guess is that you are running IndexOptimize within an existing transaction.  Ola's script is checking for an open transaction close to the very start of the procedure.

Would you really want the IndexOptimize process running under ONE transaction and possibly filling up the log along with possible blocking.

Ola's code has a variety of begin transaction and commit statements.  If the process is already running under a transaction, additional begin transaction statements create nested transactions and the subsequent commit statements only decrement the @@trancount, but don't actually commit the work.

Try running the process without an explicit begin transaction.


Split data across databases/machines I have two questions about how to partition data across servers.

Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third (or fourth), separate database to link them? For example, suppose I wanted to store a family tree with info such as date of birth, mother's maiden name, citizenship/immigration status, ethnic/religious affiliation, and so on for each member, but I wanted to partition the info across databases (and machines) to reduce the amount of personally identifiable information (PII) exposure that might occur through a single breach. I want to link a mapping table (stored in its own separate database) with another small database that includes tables of DOB and ethnic/religious affiliation and a few other details, and with a third database that contains the rest of the sensitive information (e.g., mother's maiden name, citizenship/immigration status, and place of birth). Bottom line: if I insert or pull a record, I want to insert/acquire ALL information about a single entity, but as noted above, I don't want to store all of the info in one database. Is it possible to store each database independently, with nothing in common, using only a mapping table from a separate database to link them? If not, what is the minimum that I must include in each database to achieve what I describe?
How would I determine the increase in time/space complexity due to the use of remote databases/multiple machines? The database system I'm contemplating is MySQL, but I'm assuming the answer would tend to be the same across the board.

EDIT: Because the point is to avoid storing all data in any single database/machine, database replication is not what I'm looking for. I want to actually partition the data across servers. Thanks so much for your help!

P.S. Please also note that this measure is not replacing encryption or any combination of security measures -- all of which are also being applied. This is in addition to those protective measures. We have multiple recent examples of millions of people losing entire sets of PII through the breach of a single database.

What you describe sounds much like horizontal sharding of sorts. That, of course, can be done. To answer your second question, you determine the performance (time/space) impact of your design by building a prototype system and measuring what's important to you.

However, security by obscurity, which seems to be your ultimate goal, is rarely worth the effort.

There are better ways to protect sensitive data, which include encryption (e.g. storage level or transparent database encryption, which obviously should include encrypting backups); row- and column-level access control at the database level; thorough security policies; audit, including possibly wire-level analysis of database traffic; network hardening and intrusion detection; etc. All these methods work well only in combination; looking only at one aspect of your security landscape is counterproductive.


how to stop using sql server login credentials in a linked server? I have a linked server based in Italy server_italia that connects to a server based in Oregon USA ORDB1

we are on different but trusted domains.

The connection from server_italia to ORDB1 is made through a linked server using a sql server account that has a login and the required permissions on ORDB1.

I am a sysadmin and even domain admin in my domain, and my server_italia but I am only sysadmin inside the sql server on ORDB1.

There is a similar question:

How can I get my linked server working using Windows authentication?

but it does not provide the answer.

Here is my linked server:

when I run the following queries on server_italia I get the following results:

SELECT [the_server]=@@servername,
auth_scheme
FROM sys.dm_exec_connections WHERE session_id = @@spid ;

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

In Oregon I get this:

If my American colleagues get:

the Service Principal Names registered for the services being run by your service account
set up Kerberos Authentication  on their server
Configure Constrained Delegation as that will allow the credentials to be passed along, otherwise you may still run into the double-hop issue. – added on the comments by John Eisbrener

Would I be able to use my linked server without the sql server account login impersonation?

I don't know if you got this resolved or not, but after recently going through our own (re)discovery of the requirements to get delegation running, I figured I could at least provide an answer in the event others come across your post with similar questions.
To help make my post clear (especially to anyone coming across it and not reading through your question), delegation is the ability of an intermediary service to hand off client credentials to a secondary service on behalf of the client.  Represented visually, as follows:

In this case Server A is the intermediary service handing off the Client credentials to the secondary service, Server B.  In your case, server_italia would be considered the intermediary server and ORDB1 would be the secondary service.
ACTIVE DIRECTORY (AD) FUNCTIONAL LEVEL
First and foremost, your Domain Functional Level heavily dictates the options available to you.  If your Functionl Level is at any version prior to Windows 2012, delegation between domains is limited to Unconstrained Delegation only.  Prior to 2012, constrained delegation was limited to within a single-domain only and would not have cross-domain capabilities.
If you're running a 2012 Functional Level or later, you have additional options, so it'll depend on your preference for administration, access control, etc.  A 2012 Functional Level does open up the ability to delegate credentials across domains via Unconstrained Delegation, Traditional Constrained Delegation, or Resource-Based Constrained Delegation.  The rest of my answer will assume you're using a 2012+ Functional Level, but if not, the Unconstrained Delegation configuration instructions are the same, regardless the functional level.
COMMON REQUIREMENTS
Regardless the type of delegation you choose (all options will be listed below), all configurations will require the following basic setup for the account you will be using to run the "intermediary" service (e.g. Server A in the image above, but this can apply to any intermediary service such as a Database, a Web Site hosted on IIS, SSIS, a .NET app, etc.).
On the Domain, the account running the service delegating credentials to other services will require the following AD properties be set:

ServicePrincipalNames: This needs to have any SPNs for Server A listed that will be delegating credentials.  In your example, those would look like the following (assuming you're on the EURO domain of the CONTOSO forest): MSSQLSvc/server_italia.euro.contoso.com, MSSQLSvc/server_italia.euro.contoso.com:1433
KerberosEncryptionType: AES256 (and AES128 if you choose to use that encryption type) should be enabled.  This is important later on, but this setting will ensure Kerberos Encrypted tokens can be delegated.

NOTE: The aforementioned properties can be viewed and set via a variety of methods.  The Active Direcotory Users and Computers MMC Snapin is the most common Graphical User Interface you can use to adjust these properties, BUT this interface obfuscates a lot of these properties and is total garbage when it comes to managing Group Managed Service Accounts.  As a result, I suggest you use the ActiveDirectory PowerShell module as it allows you to set these properties explicitly via either the Set-ADUser or Set-ADServiceAccount commands.  Additionally, you can view properties of a user/service account via the Get-ADUser or Get-ADServiceAccount commands.
In addition, all SQL Servers should have a registered SPN.  You can manually create these, but if you nest the service accounts running your SQL Servers into a proper OU, self-registered SPNs will happen automatically.  Instructions on that process can be found here.
TYPES OF DELEGATION
Again, assuming you're using an AD Functional Level of 2012 or later, you have 3 options for performing cross-domain delegation.  I will list them in the easiest to configure/least secure to most frustrating to configure/most secure approaches.  Any of these will work, and how much administration/security exposure you wish to deal with will be completely up to you.
One other tidbit here, is that there is nothing preventing you from configuring an account to use multiple or all types of delegation listed below.  These configurations are not mutually exclusive.  You should do your best to avoid having any account setup under multiple delegation schemes though, as it will likely use the least secure configuration first (section on determining how your accounts are setup is below).
UNCONSTRAINED DELEGATION
Unconstrained Delegation is the easiest configuration to setup, yet least restrictive approach.  This configuration allows the Service Account running Server A to delegate any credential to any secondary service. This is visually represented in the image above with the blue arrows denoting no limitation on inbound client connection or secondary service that credentials can be delegated to.  To configure this type of delegation on the intermediary account, you only need to set the following AD property (in addition to those listed above):

TrustedForDelegation – Set this to True

This used to be all that there was to it, but with July 2019's Windows Security patch, you will need to re-enable delegation across trusts as well, as outlined in that link.  That security patch was really designed to address the serious downside of this setup, which is much better explained in this post: Hunting in Active Directory: Unconstrained Delegation & Forests Trusts.  Again, unless you're using an AD Functional Level prior to 2012, it's not recommended you use this type of delegation.
TRADITIONAL CONSTRAINED DELEGATION
Traditional Constrained Delegation limits the services Server A can delegate permissions to, such that the Service Account running Server A is only allowed to delegate credentials to services that are explicitly listed.  For instance, it can still delegate anyone’s credentials but it can only pass along said credentials to the services that are explicitly listed.  So visually speaking, we’re limiting the “destinations” we can delegate to, as denoted by the yellow arrow:

The AD Properties (in addition to those listed in the Common Requirements section above) you have to configure on the intermediary account are as follows:

msDS-AllowedToDelegateTo - This needs to be a list of all applicable SPNs that this account is allowed to delegate credentials to.  In this example, those would look like the following: MSSQLSvc/ORDB1.usa.contoso.com, MSSQLSvc/ORDB1.usa.contoso.com:1433 (assuming the ORDB1 server is on a USA domain of the CONTOSO forest).

With this configuration, you are limiting delegation to AD KERBEROS tokens only.  If you wish to delegate non-KERBEROS credentials, such as those passed via NTLM etc., you can configure this account to delegate ANY protocol by setting the following property as well:

TrustedToAuthForDelegation – Set this to True

Using the ANY protocol configuration is inherently risky though.  This blog article digs further into these properties and their ramifications, and unless there is a need to set this to true, it's recommended you don't.
From a personal standpoint, we often use Traditional Constrained Delegation because it's easy to setup and relatively secure (unless you decide to use delegation for ANY protocol, then you're setting yourself up for issues, in my opinion).
RESOURCE-BASED CONSTRAINED DELEGATION
Resource-Based Constrained Delegation limits the intermediary services that the secondary service allows delegated credentials from.  This is slightly different from the other configurations in that instead of configuring Server A like in the prior configurations, we will be configuring Server B by listing the services that it will trust delegated credentials from.  This is done without having to make any additional changes to the intermediary account running Server A. So in this scenario, when Server A attempts to pass along Bob’s credentials, Server B will identify that Server A is a “trusted” service that can delegate permissions and authentication will occur without issue.  Represented visually, it would look as follows:

To be absolutely clear here, the intermediary account (e.g. Server A) still requires the properties in the Common Requirements section above be configured.  In addition though, the destination service (i.e. Server A) requires the additional AD Property be set:

PrincipalsAllowedToDelegateToAccount - This needs to have all applicable SPNs listed of those services that are trusted to delegate credentials from.  In this example, those would look like the following: MSSQLSvc/server_italia.euro.contoso.com, MSSQLSvc/server_italia.euro.contoso.com:1433

That’s it… conceptually.  Test this out to be certain, but from the articles I’ve found outlining this approach, this is really all that’s required.  In fact, this MS article runs through the process and lists out the applicable PowerShell commands necessary to set this up so a sample PowerShell program can be run to confirm the double-hop and that Resource-based Constrained Delegation will work for you.  Additionally, this delegation configuration should work across domains without issue (or requirement of a trust, from what I've read).  The downside here is that we have to configure all the “end points” which may be more administration work in the long run depending upon how your setup is.
HOW DO I KNOW WHAT DELEGATION SETTINGS I HAVE CONFIGURED?
In my opinion, identifying what delegation configuration an account is running under is about as clear as mud.  Thankfully
Willem Kasdorp posted an article along with a PowerShell script to show you what your accounts are currently configured with.  If you run this from a computer with the ActiveDirectory PowerShell module installed, you should be good to go.  Hopefully the combination of the types of delegation configurations I mention above and the matrix this script generates will give you a clear look at what you've currently got configured.
Finally, if you want some help with the AD PowerShell commands, this article does a decent-enough job of showing the proper syntax when setting some of the properties I talked about with lists of SPNs, etc.
If you have any questions, please post a comment and I'll try my best to answer promptly.


What DSN file is MS Access using for ODBC Linked Tables? I set up a linked tables in Microsoft Access to connects to an remote database using ODBC with a File DSN. But which DSN file is Access using now? I want to examine it and compare it with the other versions.

The Linked Table Manager only lists the tables and the name of the database.

I tried renaming all of the DSN files I could find, and the ODBC connection still runs. Is Access storing the connection string itself?

IIRC, Access uses either a machine DSN or a file DSN. I only have Access 2016 installed but I don't think this has changed much. There should be an External Data tab in the ribbon, click on ODBC Database. This shows you two tabs: File Data Source and Machine Data Source.

This image shows the MS Access Database source but I generally see folks create one specific to the Access database they created.

You can also views this by opened the ODBC Data Sources from Administrator tools. There is a 32 and a 64 bit version of this and you may need to check both.

Also, with regards to Access storing the connection itself. It is possible to setup a DSN-less connection using macros. You can see this KB on how that is done to know where to check in your Access database.


SQL Server 2016 SP1 Replication: creation of publication stops with nvarchar(max) not supported with memory optimized tables We're trying the following setup:

SQL Server replication on SQL Server 2016 SP1.
We're using Transactional replication on this server to push transactions to the subscriber.
We can select the table we're trying to replicate. We check the checkbox telling the publication that it needs to be memory optimized. The table contains one column of the NVARCHAR(MAX) type, which is supported in SQL 2016.

So far so good.

But when we create the publication, the following error occurs:

The type 'nvarchar(max)' is not supported with memory optimized tables.
Changed database context to 'StackExchange'. (.Net SqlClient Data Provider).

Any thoughts on a solution? My fallback will be transaction log shipping.

Found some links that indicate the nvarchar(max) datatype should be replicationable:
https://technet.microsoft.com/en-us/library/ms151254%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/table-and-row-size-in-memory-optimized-tables

Found another resource telling me that the @schema_option should be set to 0x40000000000. Still the same error...

use [StackExchange]
exec sp_addarticle @publication = N'T04'
    , @article = N'Badges'
    , @source_owner = N'3DPRINTING'
    , @source_object = N'Badges'
    , @type = N'logbased'
    , @description = N''
    , @creation_script = null
    , @pre_creation_cmd = N'drop'
    , @schema_option = 0x40000000000
    , @identityrangemanagementoption = N'manual'
    , @destination_table = N'Badges'
    , @destination_owner = N'3DPRINTING'
    , @vertical_partition = N'false'
    , @ins_cmd = N'CALL sp_MSins_3DPRINTINGBadges'
    , @del_cmd = N'CALL sp_MSdel_3DPRINTINGBadges'
    , @upd_cmd = N'SCALL sp_MSupd_3DPRINTINGBadges'
GO

I've created a Connect item with Microsoft for this issue.
https://connect.microsoft.com/SQLServer/feedback/details/3133643/replication-to-memory-optimized-table-fails

Fixed it. The @schema_option shouldn't be  0x40000000000 but 0x80000000000. This means the replication converts clustered index to nonclustered index for memory-optimized articles. It's nowhere close to error message.

So, if you need to create a replication from a disk table to a memory optimized table, do not let the wizard do the work but create the script en alter the @schema_option.
At least this lets you create the publication and the subscriber can connect to the publication.

Bugger, not fixed. Yes, the creation of the entire replication procedure works great. But then, the new memory optimized tables do not get created. The only thing this stupid feature does is drop the table (whether they exist or not). And then starts complaining the target table does not exist.

Now trying to hack the @schema_option to combine the 0x40000000000 and 0x80000000000. Somehow these values cross a threshold that makes them readable to SQL Server. One or the other works (and results in failures or weird behavior) together nothing...

Still not working in version 17.1 of Management Studio.

-- edit november 2017

The newer versions of Management Studio seem to support the in-memory to in-memory replication. And the error handling has improved.
So question marked as solved. If anyone still encounters issues, please add to this question :)


Read insert statements from text file Good day All,

I have a 1000 text files that each contain 1000 insert into statements. What is the most efficient way to read and execute each insert statement into a MySQL DB table?

Thanks for any assistance here.

You can do this two ways.

Use the mysql client SOURCE command.

mysql> SOURCE mysql_stuff.txt or mysql> \. mysql_stuff.txt

or, you can use the mysql client from the shell.

$> mysql -u my_user -pmy_password my_schema < mysql_stuff.txt

Note - no space between -p and the actual password. If you don't put in the password, you will be prompted for it, but you must include the -p or else the client will throw an error.

Now, if you have multiple files and you wish to go through them and execute each one, then look here.

You could use sed, awk and grep in inventive ways to create SQL text files with a 1000 lines, each with a different file name - you could start with (in the directory containing your SQL files).

ls --format single-column > mysql_stuff.txt (bash or cygwin)

and then add SOURCE (+ space) to the beginning of each line

sed -i -e 's/^/SOURCE /' mysql_stuff.txt

1) -e not strictly necessary in this case, see here. It's used
in the case where one wishes to run multiple commands at once.
2) -i means perform the edit "inplace" - no new file is created. If you're not sure of your regular expressions (either search and/or replace), then backup your file.
3) the circumflex (^) is part of the regular expression search pattern. It means start the replace at the beginning of the line.

and then run SOURCE mysql_stuff.txt from the mysql client and it will go through the file executing each line. Oh, yes, don't forget to add a semi-colon ; to the end of each line also.

sed -i -e 's/$/;/' mysql_stuff.txt

the $ means perform the substitution at the end of the line.


How to backup IBM DB2 Database online? I tried to backup the IBM DB2 (LUW) Database using this command:

./db2 backup database DBEMP to /home/user1/db2bkup

But I got this message:

  SQL1035N  The database is currently in use.  SQLSTATE=57019

Then I tried this:

./db2 backup database DBEMP online to /home/user1/db2bkup

And I got this message:

  SQL2413N  Online backup is not allowed because the database is not
  recoverable or a backup pending condition is in effect.

What does it mean? Is there alternative way to backup the database online? I can't stop the database because it's being used.

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created.

You can check whether your database is using circular logging by issuing:

./db2 get db cfg for dbemp | grep LOGARCH

If both LOGARCHMETH options are switched off...

First log archive method       (LOGARCHMETH1) = OFF
Second log archive method      (LOGARCHMETH2) = OFF

... then you'll need to change your database configuration to use archive logging before backups can be taken. I'm not 100% sure of this but I believe you'll need to restart your database and take an offline backup before you can start taking online backups.

There's an overview of how to do this in IBM's "configuring database logging options" documentation.

Note that if you change from CIRCULAR LOGGING to ARCHIVE LOGGING, you'll need to figure out what to do with your archived log files. Storing them on SAN/NAS/Tape to allow roll-forward recovery can be a really good idea if this data is important to you.


Secure MySQL-Server completely (disable ability to reset root-password) is there a way to secure a mysql-server so no one can reset a password via --init-file-startup?

there should only be a:

"root" user (password not known to anyone else than person a)
"system" user (no password, full access to one database restricted to
localhost-connection)
"reader" user (password, read only access to the "one" database and
no host-restrction)

and absolutely no way to bypass this setting.

i did not find a way till now - but maybe there is a hidden one.

thank you!

As indicated there is no option.

The person that has the ability to trigger the mysqld with --init-file is either root or the mysql user who can overwrite all of the mysqldatadir including all permission tables of the mysql. That is why there isn't even a considered option to achieve this.


How To change the column order? How To change the column order in sql server 2008 R2?

Eg: My table order is Eid------Ename-----Esalary

Know i want this format like   Eid---Salary----Ename

Thanks.

You shouldn't care enough about the physical column ordering of an existing table that you would want to invite the risks involved with changing it. The only case where this might be a concern is if you're using SELECT * and want that output arranged in a certain way - and you shouldn't be doing that, you should be listing the column names explicitly (and you can then put the columns in any order you want, or create a view that does that).

You can drop and re-create the table, like others have said, with the columns in your preferred order. But this is going to be very disruptive (blocking, I/O) if your table is large, since all of the other users on the system will have to wait for that to complete. If you want to see exactly what Management Studio does, create this table:

CREATE TABLE dbo.foo(b INT, a INT, r INT);

INSERT dbo.foo(b,a,r) VALUES(1,2,3),(4,5,6);

Now, in Object Explorer, right-click the table and choose Design. Select the box to the left of the column name that you want to reorder. Drag the column to another location within the table. I moved a above b because I'm OCD and wanted the columns in alphabetical order:

Then I clicked the "Generate Change Script" button, and here is the monstrosity that it created (and note this is a heap with no keys, constraints, triggers etc. - this re-create can get pretty complex).

/*
   To prevent any potential data loss issues, you should review
   this script in detail before running it outside the context
   of the database designer.
*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_foo
    (
    a int NULL,
    b int NULL,
    r int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_foo SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.foo)
     EXEC('INSERT INTO dbo.Tmp_foo (a, b, r)
        SELECT a, b, r FROM dbo.foo WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.foo
GO
EXECUTE sp_rename N'dbo.Tmp_foo', N'foo', 'OBJECT'
GO
COMMIT

For fun, let's create something a little more complex:

CREATE TABLE dbo.flarb
(
  a INT PRIMARY KEY,
  b INT NOT NULL CHECK (b>1),
  c ROWVERSION,
  d DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  e UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
);

CREATE TABLE dbo.blarf
(
  a INT NOT NULL FOREIGN KEY REFERENCES dbo.flarb(a)
);
GO

CREATE TRIGGER dbo.flarb_tr
ON dbo.flarb
FOR UPDATE
AS
  SELECT 1;
GO

CREATE VIEW dbo.flarb_v
WITH SCHEMABINDING
AS
  SELECT a FROM dbo.flarb;

Now we try the same thing:

  'flarb' table
   - Error validating the default for column 'e'.
  - Warning: The following schema-bound objects will be modified:
    - View 'dbo.flarb_v': schema binding will be removed.

Oops. Thankfully, we can still proceed to see what the script will look like:

/*
  To prevent any potential data loss issues, you should review
  this script in detail before running it outside the context
  of the database designer.
*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER VIEW dbo.flarb_v

AS
  SELECT a FROM dbo.flarb;GO
ALTER TABLE dbo.flarb
    DROP CONSTRAINT DF__flarb__d__1B7E091A
GO
ALTER TABLE dbo.flarb
    DROP CONSTRAINT DF__flarb__e__1C722D53
GO
CREATE TABLE dbo.Tmp_flarb
    (
    a int NOT NULL,
    b int NOT NULL,
    d datetime NOT NULL,
    c timestamp NOT NULL,
    e uniqueidentifier NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_flarb SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_flarb ADD CONSTRAINT
    DF__flarb__d__1B7E091A DEFAULT (getdate()) FOR d
GO
ALTER TABLE dbo.Tmp_flarb ADD CONSTRAINT
    DF__flarb__e__1C722D53 DEFAULT (newsequentialid()) FOR e
GO
IF EXISTS(SELECT * FROM dbo.flarb)
     EXEC('INSERT INTO dbo.Tmp_flarb (a, b, d, e)
        SELECT a, b, d, e FROM dbo.flarb WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.blarf
    DROP CONSTRAINT FK__blarf__a__1E5A75C5
GO
DROP TABLE dbo.flarb
GO
EXECUTE sp_rename N'dbo.Tmp_flarb', N'flarb', 'OBJECT'
GO
ALTER TABLE dbo.flarb ADD CONSTRAINT
    PK__flarb__3BD0198EF8B8CFAF PRIMARY KEY CLUSTERED
    (
    a
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.flarb ADD CONSTRAINT
    CK__flarb__b__1A89E4E1 CHECK (([b]>(1)))
GO
CREATE TRIGGER dbo.flarb_tr
ON dbo.flarb
FOR UPDATE
AS
  SELECT 1;
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.blarf ADD CONSTRAINT
    FK__blarf__a__1E5A75C5 FOREIGN KEY
    (
    a
    ) REFERENCES dbo.flarb
    (
    a
    ) ON UPDATE  NO ACTION
     ON DELETE  NO ACTION

GO
ALTER TABLE dbo.blarf SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Yeah, that really looks like something I want to run in production, even if the table is small.

The other alternative is to do this quite manually. Say you have this table:

CREATE TABLE dbo.Employee
(
  EmployeeID INT,
  Name NVARCHAR(255),
  Salary INT
);
INSERT ...

Theoretically, and leaving out the complications caused by keys, constraints, triggers, and schemabound objects, you could "change" the order of the columns by - for each column to the left of the one you want to move - creating a new column, moving the data, dropping the old column, and renaming the new column. This works because the new column is always added to the "end" of the table. Leaving out error handling and any of the other complications:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
ALTER TABLE dbo.Employee ADD Nametmp NVARCHAR(255);
UPDATE dbo.Employee SET Nametmp = Name;
ALTER TABLE dbo.Employee DROP COLUMN Name;
EXEC sp_rename N'dbo.Employee.Nametmp', N'Name', N'COLUMN';
COMMIT TRANSACTION;

This of course has the potential to be as disruptive (or worse) as the Management Studio approach of just swapping out the entire table, and gets really complicated if you want to move more than one column.

Now, do you really need to change the column order?


How to backup a Galera node I have a new production 3-node Galera (MariaDB 10.2) cluster, and am looking for advice on backups. My current plan is twofold:

Replicate one node to a non-clustered slave, and schedule mysqldumps on the slave.
Do full VM backups of one Galera node with a pre-freeze / post-thaw script which stops and then restarts mysqld.

I am particularly interested for feedback on the second point (VM backup). Is this an acceptable strategy?

Thanks,
Sera

This would work, although you might want to consider a physical backup solution with percona-xtrabackup (innobackupex) or the new mariabackup. Depending on the load on your cluster, you can even do these physical backups directly on one of the cluster nodes. (Though in my experience, xtrabackup does lock (FLUSH TABLES WITH READ LOCK) for about 15-20 seconds during backup of our 30G database.)
I don't have experience with this, but it sounds like it should work, obviously provided that when you restore from this backup you bootstrap the cluster from this node.