Upgrade From SQL 2012 to SQL 2016 - Problem with Certificates (SQLServerCentral 7/23/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Upgrade From SQL 2012 to SQL 2016 - Problem with Certificates]( - [How to be Accountable for Handling Personal Data]( - [How to change an Azure SQL Database Collation]( - [Monitoring Azure SQL Database]( (From the SQLServerCentral Blogs) - [Book Review – Factfulness by Hans Rosling]( (From the SQLServerCentral Blogs) The Voice of the DBA How much database normalization is enough? Did you know you can take a database to the [sixth normal form](? Have you ever seen a database in sixth normal form? First, before you answer, I think it is important to note that I do believe in normalization, and I think it is very important in relational databases. Second, I have recognized over the years that sometimes you can have too much of a good thing and databases can be over normalized. Clearly, normalization helps reduce redundant data. This makes sense, I don’t know many people that want to copy the same data over and over again. Also, data integrity is important. Having foreign keys can protect your systems from deleting needed data and can also improve performance. Normalization can also help with lookups and populating dropdowns with the only valid data for columns. The issue I sometimes see with over normalization is when the number of tables explodes since there are only one or two columns on each table. The queries of that highly normalized system end up having ten or more joins for every query. It also seems like old data is nearly impossible to delete since all the foreign keys make it extremely difficult to actually delete any data. Systems do things like soft delete, but the data is still there and the database size keeps growing. In short, highly normalized systems can become difficult to work with. Clearly, de-normalized databases also have major issues. I have seen tables that have hundreds of columns. Basically, everything is thrown into one table. In this situation there tends to be data issues and a lot of repeated data in the rows. So is there some happy medium? I don’t want a completely de-normalized system, but I also don’t want one so highly normalized that I have too many joins and have trouble figuring out what table has the data I need. I think there can be a more middle ground, but it depends on the system. Smaller systems that you can exert more control over are a good example. With these systems you can only allow data access via stored procedures. In this case some of the data validation can exist not via normalization and foreign keys, but by controlling how and when the data gets inserted and updated into the system. I have a feeling this may be an unpopular view, but I have built a number of these smaller systems now with great success. The tables are still normalized, just not to the degree where they become difficult to work with. I carefully pick and choose which tables to use normalization to force data integrity and which ones to control via stored procs. So what do you think? Do databases and tables always need to be as normalized as possible? Have you noticed issues in highly normalized databases? Have you found ways to mitigate these issues? Ben Kubicek from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Prompt]( Become a more efficient SQL developer with SQL Prompt Learn how to write SQL faster and more efficiently with these exclusive short videos from Data Platform MVPs and SQL Server experts. With SQL Prompt you can strip out the repetition of coding and write SQL 50% faster. [Check out the tips]( [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( Featured Contents  [] [Upgrade From SQL 2012 to SQL 2016 - Problem with Certificates]( Mike Good from [SQLServerCentral.com]() You may need to drop and recreate your certificates after migrating from SQL 2012 to SQL 2016+[More »]( ---------------------------------------------------------------  [] [How to be Accountable for Handling Personal Data]( Additional Articles from [SimpleTalk]() The GDPR is in full effect but meeting the requirements may still be confusing for many companies. In this article, William Brewer discusses what is needed to ensure compliance, including when a Data Protection Impact Assessment is required. He also explains the Data Protection Officer role.[More »]( ---------------------------------------------------------------  [] [How to change an Azure SQL Database Collation]( Additional Articles from [SQLServerCentral.com]() In this tip we look at the steps you can follow to change the collation for an Azure SQL Database.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Monitoring Azure SQL Database]( James Serra from [SQLServerCentral Blogs]( There are a number of options to monitor Azure SQL Database. In this post I will briefly cover the built-in...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Book Review – Factfulness by Hans Rosling]( Koen Verbeeck from [SQLServerCentral Blogs]( I recently bought the book Factfulness – Ten Reasons we’re wrong about the world – And why things are better than you...[More »]( Question of the Day Today's Question (by Evgeny Garaev): What is this query doing? DECLARE @a INT = -1, @b INT = 4; SELECT @a = @b + @a, @b = @a - @b, @a = @a - @b; SELECT @a, @b; Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: T-SQL. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. Pick up your copy of this great book today at [Amazon]( today. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I need a new table that contains an XML column. I run this DDL: CREATE TABLE dbo.PurchaseOrder ( PurchaseOrderKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT PurchaseOrderPK PRIMARY KEY , PONumber VARCHAR(100) , CustomerName VARCHAR(100) , PODate DATETIME , PODetails XML ); GO I now want to index the XML column. I run this: CREATE XML INDEX PurchaseOrderXML ON dbo.PurchaseOrder (PODetails); GO What happens? Answer: An error is returned because there is no PRIMARY XML index on the table Explanation: This error is returned: Msg 6343, Level 15, State 1, Line 12 Cannot create secondary xml or secondary selective xml index 'PurchaseOrderXML' without a USING XML INDEX clause. The first XML index created must have the PRIMARY column. Subsequent XML indexes would be based on this index and include the USING XML INDEX phrase. Ref: CREATE XML INDEX - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Update table using inner join]( - SELECT x.pkproductid         ,x.supplierbarcodecorrect         ,x.Code         ,x.pksupplierid from Entertainer.dbo. x left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID and spk.fkSupplierID = x.pkSupplierID The above quer --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Failover AG to DR server in asynchronous mode]( - We had a DR test. We had to failover our AGs to our DR server. We have two replicas for... [SSIS Package Executed from Server C.]( - Morning Guys, I have a dedicated SSIS server and SSISDB hosted on Server C. Server C executes an SSIS package that is... [MIgrating from Failover Cluster to Alwayson]( - We are planning to migrate our SQL Failover configuration to Alwayson AG. The plan is that there should be no connection... [option to add database in second replica]( - Hello everyone Question of a beginner I want to add a database to a configuration alwayos on I was in front of this... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Recommended Implementation]( - I have two tables. Table A and Table B. Table A is the main table with all my main data... [Intermittent sql_variant problem on COMPATIBILITY_LEVEL >= 120]( - I have encountered a reproducible but intermittent problem when working with sql_variant values in SQL 2016. The problem does not... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [RBS and SharePoint on SQL Server 2012]( - Hi Everyone We have been using RBS / Filestreaming on SQL server 2012 with SharePoint 2013 for about a year now... [SQL Server 2012 : Error while scheduling database backup with multiple backup files]( - Hi, Receiving attached Error while scheduling database backup with multiple backup files. Job was working fine till last week but I wanted... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Removing Plan with SQL_Handle vs Plan_handle.]( - Morning, I have an issue where a recurring query occasionally uses a bad query plan and can take hours to run... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Backup blocked by system SPID]( - Hello, Has anyone ever seen a full backup blocked by a system spid? I have a nightly job that runs and backups... [View creation: Using UNION ALL on dynamic schemas]( - Hi, I want to create a VIEW that combines same as well as different tables from different as well as same... [Comparison website (table design)]( - Hi, I'm designing a plugin for wordpress that will hold product details for comparison.. the results will look similar to this... [Flat File csv with double quotes]( - I am running into an issue and I can not figure it out. I have an SSIS package I am working... [how can i get the top 3, after i count the column ?]( - how can i get the top 3, after i count the column ? here is my code. to count the no.... --------------------------------------------------------------- [Cloud Computing]( : [General Cloud Computing Questions]( [Push data from on-prem sql server view into cloud based MS Dynamics CRM database]( - Hello, First, I apologize, I don't speak cloud. But I'll do my best :) In short, I have a sql server 2012... --------------------------------------------------------------- [Programming]( : [General]( [Retrieving distinct values from two tables in vb.net]( - This email has been sent to {EMAIL}. To be removed from this list, please click [here.]( If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. --------------------------------------------------------------- This newsletter was sent to you because you signed up at [SQLServerCentral.com](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com