data-konzept

'It is easy to get this one wrong. SSISDB is just a database, after all, and I’ve seen a number of instances where it was restored to another server and a number of errors/issues crop up. After all, SSISDB is the backend database for the SSIS Catalog. Restoring this database to another server is similar to migrating all the SSIS projects you have deployed to Integration Services for the SQL Server Instance. So what is the proper way to restore SSISDB?

1. Back up the SSISDB from the source SQL Instance.

2. If you know the password for the Database Master Key for SSISDB you can skip this step. If you no longer have the password used for the master key when the SSISDB was created, then back up the master key now:

backup master key to file = ‘C:\MSSQL\SQL_masterkey’ –Replace with the location where you can save it.
encryption by password = ‘REPLACE WITH PASSWORD’ –replace with password

3. If you are restoring the backup to a SQL Server instance where the SSIS Catalog was never configured, you’ll have a number of extra steps to perform. It will be easier if you just enable SSIS catalog on the new server first. You can do that through SSMS. Navigate to the Integration Services Catalog and right click to “Create Catalog.”'...

https://blog.pythian.com/how-to-restore-ssisdb-to-another-server-and-migrate-the-ssis-catalog/

'Part 1: Service Broker Basics

Microsoft first introduced Service Broker as part of the relational engine of SQL Server 2005. Service Broker is an asynchronous messaging framework with which you can implement scalable, distributed, high available, reliable, and secure database applications based on SQL Server. Here are some examples:

• With asynchronous database triggers you are able to defer long running tasks inside a trigger to a later time. This will improve the performance of your database applications.

• With distributed message-based database applications you are able to decouple related tasks within your database applications and process these tasks independently from each other at different times. This will help you with the scale out of your database applications.

As we start looking at the details of Service Broker, I want to give you a quick overview of the Service Broker objects with which you will be interacting, when implementing a basic Service Broker application.'...

https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics

'In this article, we will review triggers in SQL Server, different types of trigger events, trigger order and NOT FOR REPLICATION in triggers. A trigger is a database object that runs automatically when an event occurs. There are three different types of events. 

• DML Events 

• DDL Events

• LOGON Event – Logon trigger is fired when a LOGON event occurs i.e. when a user session is being established'...

https://www.sqlshack.com/triggers-in-sql-server/

'Have you ever wondered how much memory to assign your SQL Server instance?

Here’s a script to do it for you. If you want to understand how it works, read on!

This is based on an algorithm that Jonathan Kehayias wrote in his book, Troubleshooting SQL Server: A Guide for the Accidental DBA, and repeated in his blog post, How much memory does my SQL Server actually need?.

In broad strokes, you should give the operating system the following (quoted from the blog post):

• 1 GB of RAM for the OS

• plus 1 GB for each 4 GB of RAM installed from 4 – 16 GB

• plus 1 GB for every 8 GB RAM installed above 16 GB RAM

Whatever is left over, can go to your dedicated SQL Server instance. If you’re running anything else on the server, you will need to adjust the Max Server Memory downwards accordingly.

Keep in mind that some internal and peripheral devices may have additional memory requirements (see the blog post above).

Please feel free to use and share this link.'...

https://bornsql.ca/blog/proposed-sql-server-defaults-max-server-memory/

'A few months ago I suggested that the following settings should be the default for most SQL Server instances:

• Set cost threshold for parallelism to 50

• Disable lightweight pooling if it is enabled

• Disable priority boost if it is enabled

• Set optimize for ad hoc workloads to enabled

• Set max server memory (MB) to a custom value consistent with Jonathan Kehayias’s algorithm

• Set backup compression default to enabled

• Set the power saving settings on Windows to high performance if possible

• Provide an option to flush the plan cache as needed

Over the next few posts I will dive into the why. This week we look at setting max server memory correctly.'...

https://bornsql.ca/blog/proposed-sql-server-defaults-max-server-memory/

'The pros and cons of parallelism have always been with us in SQL Server and I blogged about this a couple of years ago. This is an updated version of that post to include details of the new wait stat related to parallelism that was added in 2017 (CXCONSUMER), as well as to discuss the options available for cloud based SQL Server solutions.

There’s no doubt that parallelism in SQL is a great thing. It enables large queries to share the load across multiple processors and get the job done quicker.

However it’s important to understand that it has an overhead. There is extra effort involved in managing the separate streams of work and synchronising them back together to – for instance – present the results.'...

https://matthewmcgiffen.com/2019/04/03/maxdop-parallelism-and-the-cloud/

'Wanna make your SQL Server applications go faster? Here are 9 simple tips that will give you a better chance for success. They’re not carved in stone, but they’re a good place to start.

Query Design Tips

1. Code for readability first. The easier your query is for humans to understand, the more likely SQL Server will be able to understand what you’re trying to do, and the better execution plan you’re likely to get.

2. Avoid scalar user-defined functions. Yes, functions are great for reusable app code, but SQL Server scalar functions are notoriously slow. They run row-by-row against your result set (possibly much more than that, depending on the query), and queries that call scalar functions are single-threaded, regardless of how much work they’re doing. Your functions might go faster in SQL Server 2019, but until then, don’t gamble.

3. Avoid table variables (@tablename). They sound like they’re going to be faster than temp tables, but they get bad row estimates, which drives the rest of your query to get a bad execution plan instead. If you need to store data somewhere temporarily, embrace temp tables (#mytable.) They’re the better of those two choices.

4. Never take user input to build a query. Crafty users will bypass your security using a technique called SQL injection. I know, you think you’ve built in a lot of security, but your code has to be perfectly bug-free every time. The hacker only has to be right once.

When you’re ready to learn more about query design, pick up the book T-SQL Fundamentals by Itzik Ben-Gan. The title sounds deceivingly easy, but this book will take you through several years’ worth of learning, and is great for veterans as well as beginners.'...

https://www.brentozar.com/archive/2019/05/9-tips-for-faster-sql-server-applications/

'Mit der anstehenden Umsetzung des Datenschutz-Grundverordnung (DSGVO) (European General Data Protection Regulation/GDPR) im Mai 2018 schreibt es im Grunde das Gesetz vor, ein Sicherheitskonzept zu haben. Microsoft SQL Server, genau wie andere Datenbanksysteme, enthält die wichtigsten Werte, die es zu schützen gilt: die Daten selbst. Deshalb ist es Zeit für einen Artikel aus einer strategischeren Perspektive in Bezug darauf, wie ich SQL Server und seine Daten schütze.Beim Design für Sicherheit gibt es mehrere bekannte Sicherheitsprinzipien in der IT-Technologie. Die gängigsten, und das sind die, die ich beachte, wenn ich für Sicherheit designe, sind wahrscheinlich die folgenden: