Two Things I Would Change in SSISDB Setup (SQLServerCentral 1/11/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Two Things I Would Change in SSISDB Setup]( - [Never Judge A Query By Its Cost]( - [Manage Data Across Multiple Sources with Azure Data Studio]( - [A New PiHole in the Sky]( (From the SQLServerCentral Blogs) - [Why I’m Breaking Up with Facebook]( (From the SQLServerCentral Blogs) The Voice of the DBA Do you have a security to-do list? I think data security is important, and it's one of those areas that I usually focus on early when I work with in an organization. Over time, I think data security has become more and more important, especially in the cases of sensitive information. Increasing legislation, as well as more fickle customers, are forcing those of us that work with data to be more careful with our data security practices. Often I've had an ad hoc review of security practices. As I notice areas where security seems lax, I'll try to make changes and tighten up the configuration. The goal is reducing the attack surface area, which should be something we do in a methodical fashion. Microsoft has given us a basic tool in the Baseline Configuration Analyzer, but this isn't quite enough. With that in mind, I wanted to ask if any of you have something better. Do you have a security to-do list that you move down to ensure that good (or best) practices are still being followed? A [security to-do list]( appeared in my feed recently, and it got me to think a bit about the items that are important for data security. While many of these items are general security issues, I do think that these are areas that we might use, and extend, for our data platform. Certainly long passwords and checking logs are important, but how many of you keep sessions open to production servers (or dev servers with production data) and walk away? Or you know others that do the same thing? The flash drive item is very interesting. I tend to not accept any flash drives from others, precisely because of these issues. It's gotten to the point where I'm afraid of malware and don't even want to use my own flash drive if someone else has plugged it into their machine. It's sad, but we have a fair amount of malware still be transferred with those types of connections. There are other items that are relevant, including perhaps sniffing your own network for malicious database traffic, but there are a few items that might apply specifically to the data platform, such as evolving and tightening our security with multiple roles and limits on access for different groups. This seems to be one of the more common areas where data professionals fall down. We give large groups access to our data because it's easier. We should be careful with access because social engineering and too much access for non-privileged users is one way we lose lots of data. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Provision]( NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps With SQL Provisions virtual cloning technology, databases can be created in seconds using just 1MB of storage, and sensitive data can be anonymized or replaced with realistic data to ensure it is protected as it moves between environments. [Download your free trial]( [SQL Compare]( The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. [Download your free trial]( Featured Contents  [] [Two Things I Would Change in SSISDB Setup]( Ben Kubicek from [SQLServerCentral.com]() I suggest these two changes to the SSISDB setup to prevent issues down the road.[More »]( ---------------------------------------------------------------  [] [Never Judge A Query By Its Cost]( Additional Articles from [Brent Ozar Unlimited Blog]( Erik shows an example where lower-cost queries take longer to run.[More »]( ---------------------------------------------------------------  [] [Manage Data Across Multiple Sources with Azure Data Studio]( Additional Articles from [Microsoft MSDN]( Azure Data Studio, now the official name of SQL Operations Studio, was released in September. The biggest news regarding the release is the extensions that are now available, as Julie Lerman describes.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [A New PiHole in the Sky]( Steve Jones from [SQLServerCentral Blogs]( Last year I set up a PiHole server on my RaspberryPi to help block some ads and malicious stuff (tracking,...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Why I’m Breaking Up with Facebook]( Tim Mitchell from [SQLServerCentral Blogs]( I have been in a serious relationship for more than 12 years. My partner in this relationship has brought me...[More »]( Question of the Day Today's Question (by Steve Jones): I have a table that contains a list of names. The DDL and data are shown here: CREATE TABLE Names (firstname VARCHAR(100)); GO INSERT dbo.Names (FirstName) VALUES ('Steve'), ('Andy'), ('Brian'); GO I now want to insert these names into my customer table. I run this code: SELECT IDENTITY(INT, 1,1) AS id,bt.FirstName INTO CustomerNames FROM Names AS bt What happens? 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: IDENTITY(). 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 [Microsoft SQL Server 2017 on Linux]( [This comprehensive guide shows, step-by-step, how to set up, configure, and administer SQL Server 2017 on Linux for high performance and high availability. Written by a SQL Server expert and respected author, Microsoft SQL Server 2017 on Linux teaches valuable Linux skills to Windows-based SQL Server professionals. You will get clear coverage of both Linux and SQL Server and complete explanations of the latest features, tools, and techniques. Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): On SQL Server 2017, I have this table: CREATE TABLE dbo.SalesOrderHeader ( OrderKey INT IDENTITY(1, 1) , CustomerName VARCHAR(30) ) GO I now insert some data in the table: INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Andy') INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Brian') INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Steve') If I now issue this command: DBCC CHECKIDENT(SalesOrderHeader, RESEED, 20) GO and then insert a new row: INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Azure') GO What is the value for OrderKey in the row with "Azure" as the CustomerName? Answer: 21 Explanation: The reseed sets the current identity value to 20, but the next insert will use this value + the increment when there are rows already in the table. Ref: DBCC CHECKIDENT - [click here]( Current doc says 2008 R2 or earlier adds one, but this is incorrect. --------------------------------------------------------------- [» 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 - Administration]( [SQL 2017 Dev edition]( - Yesterday I was chatting with the team about potentially installing SQL 2017 Dev edition on a non-production DR server to... [Can NULLs Exist in the Columns of a Non-Clustered Index?]( - A fairly credible source (SolarWinds, DPA help function) had this advice for determining the causes of table scans: Investigate Non-Clustered Indexes... [Availability group listener is not listening]( - I've successfully created an availability group with 2 nodes that one node is physical and another is VM. Also running... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [SQL Quiz]( - Removed as per the wish of a user ! --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Database in RECOVERY PENDING state - Access Denied]( - Hi I have s SQL2016 instance with 8 out of 10 databases in recovery pending state and I need assistance. I had... [Log file and data file growth]( - Hi Gurus, Is there anyway to query to see if log or/and data file growing NOT the auto growth (I can... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Dynamic creation of an ASCII "banner"]( - I've been searching around the Internet and have not found anything for this, therefore it is possible that it does... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [upgrade sql 2014 to sql 2017 and skipping sql 2016?]( - Hello, When upgrading sql 2014 to 2017 can we skip upgrading sql 2014 to 2016 and just upgrading from sql 2014... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [Stored Procedure]( - Hi, I have a stored procedure that runs for approx 5 mins , its developed by our developers. I need some suggestions... [Alternate Way Of Writing the Query]( - Hello Friends, We have been looking to optimize a query which at times run erratic and keeps changing its execution plan.... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Month T-1]( - I have year(YYYY) and Month(M01) as data. How can I have a logic where when user select year and month, it... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Claculated field in Where clause]( - Hello,  am looking for the best, or most efficient way to implement a store procedure where I have several calculated fields... [CTE to determine Course Level]( - Okay, before someone asks, this is not homework... I just can't figure out how to do it. :-) I have two... --------------------------------------------------------------- [SQL Server vNext]( : [SQL Server 15 - Development]( [insert with output clause]( - I have an insert with an output clause where one of the outputted columns is not in the inserted list,... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [SQL: Concatenate the rows based on Category and group by]( - Hi Experts, I would like to concatenate the desc column group by Row_Desc with in the group by ID.  I am... [Select convert to 2 decimal places, comma, right aligned]( - Hello The field below currently has an output that looks like this - select A. as from EQSA0345 A Replacement Cost 523.88000 60689.76000 48860.26000 77239.26000 132227.26000 65668.18000 71523.34000 I would... --------------------------------------------------------------- [Cloud Computing]( : [SQL Azure - Development]( [Renaming a primary key columns in the Azure DB]( - Summary: I have a DB that has combined keys as proimary keys and I have a task to rename one of... --------------------------------------------------------------- [Programming]( : [General]( [Is there a way to combine/merge multiple continuous rows by date into a single row without using a loop/cursor?]( - I work with a lot of data that is based on membership enrollment segments. Each segment has an effective and... --------------------------------------------------------------- [Programming]( : [XML]( [Problem reading all Attributes into SQL table]( - Hello everyone. I'm writing a proc that imports several XML files and stores them in a SQL table.  Here's a sample... --------------------------------------------------------------- [SQL Server 2005]( : [CLR Integration and Programming.]( [Translate Sql Query to Clr]( - Hello, How can I write the following query in c# clr ? CREATE FUNCTION SQL_HT ( @param1 INT, @param2 INT)RETURNS@tablo TABLE(  colon1... 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