SSISDB Catalog Defaults Best Practices (2019-12-26)

 SQLServerCentral Newsletter for December 26, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SSISDB Catalog Defaults Best Practices]( - [ASP.NET Core with GitOps: Deploying Infrastructure as Code]( - [SQL Server Transparent Data Encryption vs. NetLib Encryptionizer]( - [From the SQL Server Central Blogs - Azure Synapse Analytics & Power BI performance]( - [From the SQL Server Central Blogs - Reflection on Talking at SQLSaturday Charlotte About Mental Health]( Question of the Day - [Preparing Encryption with Trace Flags]( [Redgate Data Masker]( The Voice of the DBA  A Dearth of Data Most of us feel that our data volumes are constantly growing. A lack of data isn't something we worry about, at least not in production. In development, far too many people use sensitive data for developers, which certainly ensures there is enough data, but at a potential increase in liability if the data is mishandled. If we require developers to create their own data, often there is a problem with them not having enough, or certainly not a representative set that helps ensure the software meets enough of the specifications. This is a challenge, and while random data helps, it doesn't always work well. This problem might be hard to solve with OLTP systems, but what about an AI or ML scenario, where we might need lots of data to train a system? Microsoft Research is working on ways to solve this problem. They presented a paper on [Icebreaker]( a technique that uses minimal data to train a model. I'm not sure I completely understand how this works, but the idea is to be able to work with very little training data and somehow still train the model. I'm guessing there is some ML inside of the process itself. There are all sorts of downsides with using existing data to train models. Sometimes we have inherent bias, or otherwise skewed data. Allowing a model to work with less data, and perhaps then working to change, or even skew, the data to meet our goals might help. Certainly this likely requires input and feedback from a data scientist of some sort, but that might be where we take advantage of the skills and knowledge of that staff. There will be more use of the AI/ML technologies in the future, if for no other reason than people are very interested in how this can improve the way that systems can help analyze data. Of course, techniques like this might help us deal with the challenges of doing so when we don't have all the data we would like to have while building the model. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate Database Devops](   Featured Contents [SSISDB Catalog Defaults Best Practices]( Steve Rezhener from SQLServerCentral Introduction The SSISDB database (a.k.a. the SSISDB catalog) was introduced back in SQL Server 2012 to de-clutter the MSDB database and provide an in-house logging and reporting infrastructure. In a nutshell, SSISDB is an SSIS framework making SQL Server Integration Services more robust and enterprise-friendly by providing the following features: Database backup Database encryption Support […] [ASP.NET Core with GitOps: Deploying Infrastructure as Code]( Additional Articles from SimpleTalk Automation of server builds minimizes human error, ensures that environments are identical, and saves time building servers. This article from Mircea Opera demonstrates provisioning one server or multiple load-balanced servers in AWS with code. [SQL Server Transparent Data Encryption vs. NetLib Encryptionizer]( Additional Articles from SQLServerCentral Between the legislation over the years (HIPAA, GLBA, GDPR, CCPA, etc.) and data breaches from large organizations that seem to pop-up in the news on a monthly basis, SQL Server database encryption is critical for our industry. SQL Server ships with a few options for a native encryption implementation (Column Level Encryption, Transparent Data Encryption, Data Masking, Always Encrypted), that all provide value in particular situations, but none of the options all seem to address all of the needs. What is the best way to encrypt our SQL Server data? From the SQL Server Central Blogs - [Azure Synapse Analytics & Power BI performance]( James Serra from James Serra's Blog With two new relational database features (Result-set caching and Materialized Views) just GA’d in Azure Synapse Analytics (formally called Azure SQL Data Warehouse), it makes for some very compelling reporting... From the SQL Server Central Blogs - [Reflection on Talking at SQLSaturday Charlotte About Mental Health]( taboggiano@gmail.com from Database Superhero’s Blog On December 7, I did a session on mental illness and mental health problems being more common in IT than you think. Before the event the PASS WIT Virtual...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Preparing Encryption with Trace Flags I need to use the older encryption hash for a symmetric key in SQL Server 2017. The Knowledge Base article says I need to enable trace flag 4631 globally. What do I run to do this? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Merry Christmas 2019 I want to calculate the total number of gifts from the song, The Twelve Days of Christmas. I have this code: WITH myTally(n) AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null)) FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n) CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n) ) SELECT SUM(x) FROM myTally WHERE n <= 12 With which piece of code should I replace SUM(x)? Answer: SUM(n * (13-n)) Explanation: The song presents a new gift during each of the verses. It also repeats each previous set of gifts in the verse. This means that the first gift, partridge in a pear tree, is given 12 times. We can calculate out the number of gifts, by multiplying the number of each of the gifts by the number of times that it is repeated. In this case, each is repeated 13 - current verse. Ref: The Twelve Days of Christmas - How Many Presents? - Merry Christmas! [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 - Development [Need Correct Query Code Please? Thx. merry Christmas SQL Kids]( - Compose a SQL query to retrieve all products with its most recent two orders? Products that haven’t been ordered should be listed also. Use ERD below. Whats Correct Query Code?  [importing text file, bulk insert to populate table]( - I have to import a text file to build a table and all of the date fields currently are in integer form. It comes in three different ways. The first two are like '20191223' or '0' if there is no date. The third way in the text file is like '2170915', missing the second character. […] [Working with variable length number]( - please assist I have table 1 and table 2, join them on account number. the problem is that table 1 has extra zeros in the middle and while table 2 does not have extra zeros in the middle, the account number is not always of the same length. see below I need to match the […] [Hadoop for SQL Developers]( - Hello People I am a newbie in the field of SQL Development and have a silly query. I am doubtful about a thing that if Hadoop is easy to learn for SQL Developers. Is it essential to be aware of Hadoop if I'm totally into the field of SQL Development? How much SQL is required […] SQL Server 2016 - Administration [Basic Availability Group in Sql Server 2016 Standard edition setup]( - Hi, I am trying to setup Basic Availability Group in Sql Server 2016 Standard edition SP-2-CU11 and i'm having few issues/questions:My Primary DB is encrypted with Master Key.We have built another Sql server for Always ON but no user DB yet.On Primary Sql server DB, We are taking Transaction Log Backup every hour and FULL DB every […] [Finding Auto Increment value for each table in DB]( - Hi, I am trying to get the max value of auto identity column with table name and column name. Below is the query I am using which GIVES me table and column name BUT NOT the MAX value. SELECT OBJECT_SCHEMA_NAME(tables.object_id, db_id()) AS SchemaName, tables.name As TableName, columns.name as ColumnName FROM sys.tables tables JOIN sys.columns columns […] SQL Server 2016 - Development and T-SQL [Truncate or Insert data with conditions]( - Good day, I have two tables where A is the source and B is the destination, so what needs to be achieved, when A has no data don't truncate B when A has data truncate B and insert data from A. Need help if (select count(*) from [A]) > 0 truncate table B Insert into […] Administration - SQL Server 2014 [Offline vs Online index operation]( - As I know, while online index rebuilding, SQL Server virtually creates an index and then at the end it swaps the existing old index with the new created one and then finally drops the old index. I would like to know if these operation same for offline index rebuild. Will SQL Server create virtually an […] [Language conversion Japanese to English for MSSQL Server DB]( - Hi All, I have MSSQL Server Database installed on windows in Japanese language. Also OS ( windows ) installed in Japanese language. I am as English user not able to any administration on MSSQL Server Database. So Can any one please let me know how I can change the MSSQL Server Database console language to […] [High Page File Usage]( - Page File Usage in one of our production database server is continuously up-to more than 200% of the total available memory. Is it a signal to add more physical memory? Development - SQL Server 2014 [Reusable Variable]( - hello, I know there's gotta be a way here, but I'm struggling. Let's say I have a last name parsing sql statement (using the first few letters) but the rules are complex in the event of hyphenated names, Sr's Jr's etc... Anyway, I have all that sorted out using SELECT CASE. This works great, but […] SQL Server 2012 - T-SQL [Migrating databases - error on login 4064 (cannot open default database)]( - Hello, I am trying to figure out how to create a script to generate Login mappings to databases. I've search a lot, plenty of cases and examples, but none seem to match my problem. Here a brief explanation: I have daily backups of databases I copy to my new server. No Logins, etc. I restore […] Reporting Services [VS 2019 TargetServerVersions]( - I am trying to find out which SQL Server versions are supported in VS 2019 /SSDT for Reporting Services. We have SQL Server 2008 R2. My Infrastructure team is building a new computer with Windows 10 and it will have VS2019 on it. Our Report Server is SQL Server 2008 R2 and I want to […] Integration Services [Has anyone used BIML to create Memory optimized tables.]( - I was hoping to experiment with memory optimized tables for my staging layer to speed up the process. Googling "BIML Create Memory Optimized Table MEMORY_OPTIMIZED=ON" is not giving me nay results. This seems like it would be a common setting for a table used in a staging layer. I am using the table.GetDropAndCreateDdl() code to […] Article Discussions by Author [Performance Tuning Using Extended Events: Part 2]( - Comments posted to this topic are about the item Performance Tuning Using Extended Events: Part 2   [RSS Feed]([Twitter]( 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. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -