Do Not Set Trustworthy On for the SSISDB Catalog (2020-01-28)

 SQLServerCentral Newsletter for January 28, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Do Not Set Trustworthy On for the SSISDB Catalog]( - [PASS Summit 2020]( - [Automate SFTP File Transfer with SQL Server Agent and WinSCP]( - [From the SQL Server Central Blogs - Extended Events Misperceptions: Profiler is Easier]( - [From the SQL Server Central Blogs - Coping with Self Doubt]( Question of the Day - [Creating a Node Table]( [Redgate SQL Provision]( The Voice of the DBA  MoSQL This editorial was originally published on December 6, 2018. It is being re-run as Steve is on sabbatical. Google is doing more SQL, or at least shifting towards relational SQL databases as a way of storing data. At least, some of their engineers see this as a better way to store data for many problems. Since I'm a relational database advocate, I found this to be interesting. When Google first started to publish information on BigTable and other new ways of dealing with large amounts of data, I felt that these weren't solutions I'd use or problems that many people had. The idea of Map Reduce is interesting and certainly applicable to the problem space Google had of a global database of sites, but that's not a problem I've ever encountered. Instead, most of the struggles I've had with relational systems are still better addressed in a relational system. Google feels the same way, and in a blog, they talk about [choosing strong consistency where possibl](. This is a post that promotes their relational SQL database (Cloud Spanner), but there is a good discussion of why consistency matters and where moving to popular NoSQL models, like eventual consistency, cause a lot of problems. Both for developers and clients. This quote caught me eye, and I may use this with developers that look to avoid RDBMS systems: "Put another way, data stores that provide transactions and consistency across the entire dataset by default lead to fewer bugs, fewer headaches and easier-to-maintain application code." I think that's true as I think many of the advantages promoted in non-RDBMS systems are often placing a greater burden on the application developer than they realize. A burden that grows over time as the techniques used cause more technical debt. I think more SQL based systems are the way to go for many problem domains. Google agrees, and if you read more about the Cloud Spanner team, you might agree as well. You'll also find them to be incredibly smart people that think deeply about the problems that are both raised and solved by relational systems. So go ahead and promote more SQL Server databases. Google thinks they're good for many applications, and that's good enough for most developers. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Provision](   Featured Contents [Do Not Set Trustwority On for the SSISDB Catalog]( Ronald Beuker from SQLServerCentral Learn how to correctly fix your SSISDB without using Trustworthy. [PASS Summit 2020]( Press Release from PASS PASS Summit is the must-attend conference for Microsoft data professionals; delivering 3 full days of technical training on data architecture, management, and analytics. Get the skills you need with in-depth sessions created and led by industry experts. Network and problem-solve with peers and Microsoft engineers, and get ahead of the curve with spotlight sessions on today’s hot topics. Join Redgate and the PASS community in Houston on November 10 - 13 to future-proof your data career. [Automate SFTP File Transfer with SQL Server Agent and WinSCP]( Additional Articles from MSSQLTips.com In this article author Joe Gavin covers how to transfer files using SFTP with a SQL Server Agent job. From the SQL Server Central Blogs - [Extended Events Misperceptions: Profiler is Easier]( Grant Fritchey from The Scary DBA I know, I know, I’m on #teamexevents so I think that Extended Events can do no wrong, but let’s address this thought that Profiler is easier. Now, if we’re... From the SQL Server Central Blogs - [Coping with Self Doubt]( SQLRNNR from SQL RNNR One of the best coping mechanisms for Impostor Syndrome is the use of learned behaviors. This article shares some of these coping mechanisms and learned behaviors. Related Posts: Passion, Challenges, and...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Creating a Node Table I am creating a member table that I will use in a new application. I want to use the graph query structures to analyze data and need this to be a node table. What is the correct syntax for this? -- 1 CREATE NODE TABLE Member ( MemberFirstName varchar(100), MemberLastName varchar(100), MemberTitle varchar(100) ) AS NODE -- 2 CREATE TABLE Member ( MemberFirstName varchar(100), MemberLastName varchar(100), MemberTitle varchar(100) ) AS NODE -- 3 CREATE TABLE Member ( MemberFirstName varchar(100), MemberLastName varchar(100), MemberTitle varchar(100) ) WITH (NODE=ON) -- 4 CREATE TABLE Member ( MemberFirstName varchar(100), MemberLastName varchar(100), MemberTitle varchar(100) ) AS GRAPH NODE 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) Auto Rounding I have this code: DECLARE @9s DECIMAL(38,12) = 0.9999999; SELECT @9s*@9S; GO DECLARE @9s DECIMAL(20,12) = 0.9999999; SELECT @9s*@9S; GO What results do I get back when I run this? Answer: 1st result set: 1.00000, 2nd result set: 0.999999800000010000000 Explanation: In the first case, SQL will round up because of precision. In the second, we get the result. Ref: Precision, scale, and Length - [ [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 - Administration [Error switch partition]( - hello Good morning all I want to switch the partition from one table to another I have this error message on a blocking because of the index  ALTER TABLE sales.SalesOrderDetail SWITCH PARTITION 1 TO sales.SalesOrderDetail_2014 PARTITION 1;  Warning: the partition specified 1 for the table 'produit.sales.SalesOrderDetail_2014' was ignored in the ALTER TABLE SWITCH […] SQL Server 2017 - Development [BULK insert several thousand files]( - I have a bunch of logging data that my sysadmin has downloaded from our elastic load balancers at amazon and I need to stick it in a completely bland table for a one off sales pitch. I've scripted up all of the unzip, copy, etc etc… but I've become so reliant on SSIS that I've […] SQL Server 2016 - Administration [upgrade plan common sense check please]( - Be grateful for a yay or nay on my plan: 4 nodes. 2012. 1 x AG. FCI. 1 local sync. 2 remote async. Upgrade to 2016 on all, failing over as required, doing remotes, then local secondary, then primary. Reboot all. Run checks DBCCs etc. Then upgrade to SP1 via the same method above. Would […] [SQL Patching implementation date]( - Hi I am looking at the script to get SQL Patching implementation date . Any script that I can execute using SSMS ? Thanks Your feedback is much appreciated   SQL Server 2016 - Development and T-SQL [Execute business rule runtime]( - Hi I have set of hundreds of business rules that I need to executed based on certain criteria. Just for example If product =A -         Code > 5  (code should be greater then 5) -         Height > 10 -         Width >5 If product = B -         Height > 8 -         Width >7 I want to […] [Trigger for only updating specific column]( - Hi, using Merge I can sync table A with Table B at ROW level. However 'how to update only specific column instead of the whole row'? Sincerely!  [Count of Distinct Clients in a list, in groups]( - Hello all, I have a dataset of trip information (the majority of which I got from another awesome forum member not long ago) where I need to be able to get a distinct count of passengers in the groups when someone is onboard the vehicle. This trip information shows when a vehicle is picking up […] [What could cause MS Access app as a front-end to SQL, periodically run slow?]( - We've got an old Microsoft Access pharmacy application which acts as a front-end to a SQL Server 2016 database. Periodically the application will just start running slowly. We're working on a modern replacement app, but that won't be available for months. In the meantime, what can we do about this application suddenly running slowly? Here's […] Development - SQL Server 2014 [Recover data log loss]( - Hi. I would like to know is it possible to recover data log table from SQL Server 2014 without backup file? SQL 2012 - General [What is spinlock ALLOC_CACHES_HASH?]( - Hi, Queries are slow, cpu usage at 80%. ALLOC_CACHES_HASH backoffs start at same time as problems are seen in application. "CACHES" would point to memory, but no obvious shortage there. This document says "internal use only"... What to check next? spinlock_name delta_spins delta_backoff delta_minuntes delta_spins_per_millisecond_per_CPU ALLOC_CACHES_HASH 72412901394 16796814 30156 100053 SOS_OBJECT_STORE 17456 1 30156 […] SQL Server 2019 - Administration [TDE / SQL server Database / SSIS]( - Dear Everyone We have implemented a data warehouse on-premises using SQL server 2019 enterprise edition running on VM Ware. SSIS, Database Warehouse and SSAS all reside on different servers in the same VLAN and now I am working on implementing TDE on the servers but I wanted to know the following: Is TDE required for […] [SQL Server Licencing Question]( - This question is about SQL Server Licencing and licencing for Maximum Virtualisation. I'm curious about the licence position for this scenario. You have one Hyper-V host (HOST1) with say 48 cores all core licenced with Enterprise Core and covered by SA. You can run an unlimited number of VMs. You have a second Hyper-V host […] SQL Server 2008 - General [Error converting data type varchar to float]( - All, I know this topic has been discussed numerous times and I tried all possible solutions but to no avail, so here I am. I have an app that stores values like this in a varchar(7) column called Utilization in a table called CC_Agent_State_Summary : 0.02% 16.2% 99.91% The app does nothing but store and display […] Reporting Services [SSRS 2016 install on FCI with AAAG.]( - Appreciate advice here as I am slightly confused. I need to install SSRS 2016 on an existing 4 node, cluster with AAAG. I don't particularly need HA here or DR. I guess I may as well read from my local secondary server as best practice. How would you implement this install? As SSRS is not […] Powershell [Remove comma inside of double quotes]( - I have a file comma delimited that has no header. How can I clean the extra comma inside of my double quotes? It sees that comma and adds and extra field to my output. This is a short example data, my file has 37 columns and have found it in 2 fields for now, but […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -