Database Sequence Objects with dbatools (2023-05-31)

SQLServerCentral Newsletter for May 31, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Database Sequence Objects with dbatools]( - [PASS Data Community Summit 2023 registrations are open!]( - [Why a monitoring tool is an essential tool for growing server estates]( - [From the SQL Server Central Blogs - Why you still need DBA resource in the cloud]( - [From the SQL Server Central Blogs - Using Docker on Windows with Persistent SQL Server Settings and Databases]( - [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Question of the Day - [Full Managed Instance Backups]( The Voice of the DBA  Scaling Up Monitoring Monitoring software is important when it's in production. Operations departments know that catching issues early, being proactive, and having data to troubleshoot make their job easier. Not having these things makes their job much more stressful. Most of us work with data in some way, and availability is important. Certainly, security, integrity, and performance matter as well, but many people don't have any monitoring systems set up. Instead, they troubleshoot problems when someone files a ticket or calls. I'm amazed at this, though I know building and managing monitoring is hard, and purchasing third-party products can be outside of your budget. Still, having something in place makes everyone's job easier. If you decide to build a system, then you can do it in many ways. I saw a description of how Amazon built a monitoring system for their Prime Video service. This is a more complex system than many of us deal with for databases, but I did find it interesting that they chose a distributed architecture that used multiple components. It didn't scale, so they started to move from small functions, almost like microservices, to a bit more monolithic structure. I am not saying that microservices or functions or serverless are bad choices. They meet certain needs, and they can work very well. Azure SQL Database Serverless can work well in some situations. However, I do think that this was a case of engineers trying to be too clever and making assumptions about production loads from PoC-type experimentation. I would say that far too many software engineers think that their solution will scale without actually testing it. Too often their view is if it works here, it will work there, but the history of software has shown that working on my machine doesn't mean working on another. That's why we use Continuous Integration: for independent validation and verification. This is also a problem when databases are involved, as the level of data used for development and testing doesn't do a good enough job of predicting how the system works under load. We need better [test data management]( which is becoming a whole new category of software practices and tools. We should ensure we include good instrumentation in our software for monitoring purposes, but we should also ensure that we start monitoring and evaluating how our system will perform in test and development environments, as that's the idea of shift-left. Lastly, I think monitoring in production is important, but I wouldn't build another system. I admit I'm biased, but I also think the build v buy debate doesn't make sense here unless your staff has a lot of spare time to spend maintaining a homegrown system. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Database Sequence Objects with dbatools]( Mikey Bronowski from SQLServerCentral This article delves into five new dbatools command that work with sequence objects. [External Article]( [PASS Data Community Summit 2023 registrations are open!]( Additional Articles from Redgate In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post and learn more. [External Article]( [Why a monitoring tool is an essential tool for growing server estates]( Additional Articles from SimpleTalk I enjoyed writing scripts to manage the servers, as it taught me a lot about the internals of SQL Server. Many of these scripts were eventually automated using SQL Server’s agent to run and save data on the different servers so we could review the results, looking for issues. [Blog Post]( From the SQL Server Central Blogs - [Why you still need DBA resource in the cloud]( Matthew McGiffen from Matthew McGiffen DBA Why a DBA, rather then being redundant, is even more valuable when your databases run in the cloud. [Blog Post]( From the SQL Server Central Blogs - [Using Docker on Windows with Persistent SQL Server Settings and Databases]( hellosqlkitty from SQLKitty This is an easy way to retain the data and settings you create and change on your SQL Server Docker instance. There’s a lot of talk about attaching databases... [SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey]( [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Grant Fritchey from SQLServerCentral Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Full Managed Instance Backups As of April 2023, if I have an Azure SQL Managed Instance, how often are the automated full backups run on my databases? 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) The Secure Enclave When using Always Encrypted in SQL Server 2019, where is the secure enclave located? Answer: In memory in the database engine Explanation: A secure enclave is a secure place where computations can be run. This exists in memory in the database engine, though the keys are often stored on a client machine or in a secure third-party location. Ref: Always Encrypted with secure enclaves - [ [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 [Best migration approach]( - Hi Colleagues, I am considering the across-the-wire migration method to SQL Server 2017. However, my source is an SQL Server 2014 Availability group with three nodes, one asynchronous. Is launching a new VM on the remote site and the same cluster to migrate this first node a good idea? Later, demote the old node, then […] SQL Server 2016 - Development and T-SQL [import from CSV - special chars]( - My data csv file sometimes comes in with special characters and French accents too (è ë à ç ) And then this special apostrophe in surnames e.g. O’Brien. They go in the database as this: O’brien I am using SSIS for import. Where do I need to make changes to allow these special chars? The tables have […] SQL Server 2019 - Administration [Does Query Store persist data across an upgrade from SQL 2016 to SQL 2019]( - This may be somewhere in the docs, but my Google-fu wasn't able to find it... We're testing an upgrade from SQL 2012 to SQL 2019, but tests indicate performance in SQL 2019 with compatibility level 110 is worse than native SQL 2012 for some critical queries and switching to compatibility level 150 (native 2019) is […] [adaptive memory grants]( - Hi we have a sql server we use as a data warehouse. It has 1TB of RAM but still suffers from memory pressure. Looking at the plans there are excessive memory grants. I changed the compatibility level to 150 to take advantage of the Adaptive Memory Grants. It's been in place for around a month […] [Upgrade to SQL2019 from 2012 and everything is slow - what to look for?]( - We have recently started a process of planning the upgrade of our four-node SQL2012 availability group to SQL2019. The main database is pretty large (a few TB) and we've spent years tuning the SQL to get the performance to where it needs to be such that huge complex procedures absolutely fly on SQL2012. We've had […] [DBCC CHECKDB error 2576 allocation errors]( - Getting the following after the execution of the DBCC CHECKDB:  Msg 2576, Level 16, State 1, Line 1 The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:17) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617208115200 (type Unknown), but it […] SQL Server 2019 - Development [Query to Insert Data Based on CustomerID from one table to another]( - Hello All, Below is the my existing table structure from which I need to insert records to a new table I need a query to insert records into a new table for each customer for same date.  For example considering above data structure query should insert records into a new table as below based on […] [Encryption insert driving me mad]( - Hi all, I'm wondering if someone can help me with this, I have a table (very simple) CREATE TABLE [api].[Employee]( [EmployeeID] [int] NULL, [CreatedDate] [datetime] NULL, [Title] [varchar](10) NULL ) ON [PRIMARY] GO I've then gone and encrypted the Title column in SSMS using Randomized encryption type with a key in our Azure Key vault, […] [Most performant way to join a DATE to an INT]( - Hello and thanks in advance if you can help. I guess its somewhat acceptable in dimensional modeling to use the INT data type when creating a date dimension surrogate key. I can't say I agree with this but here we go. I need to join tables on DATE and INT columns. What is the most […] [Cumulative Sum by Groups]( - HEY, I HAVE TWO TABLES: CUSTOMERS AND USERS PER CUSTOMER I NEED TO CREATE TWO CUSTOMERS GROUPS: DVIR & RON - 'CRNTER GROUP', AND DANNY & DAVID 'OTHER GROUP'. AFTER THAT I NEED TO SUM THE NUMBER OF USERS FOR EACH GROUP. I STARED BY USING UNION ALL AND PARTITION BY: WITH "CUST-CTE" AS ( […] [Trigger Email After the record is inserted/deleted/updates from a table]( - Hi, I have following requirement. There is Sql server DB table OrderDetails. It has date, UserName , Details, OrderID columns. Case 1: I want to send email alert whenever any new record is inserted then email should be sent with below details. The new records date and time of insertion, UserName who inserted the record, […] SQL Azure - Administration [Scheduled Jobs in Azure SQL Databases Failing for Auto-Paused DB]( - Hello! I have somewhat similar kind of PowerShell script which executes the stored procedure on a schedule. Since the database is set to auto-pause, job is failing at times with the error message below: "Exception calling "Open" with "0" argument(s): on server is not currently available. Please retry the connection later. " How can I […] Integration Services [Visual Studio 2019 Pro won't load my projects]( - HI all, For some reason I've been having issues with VS pro opening up my projects that are in my SIS Solution. I'm NOT having the same problem with my SSRS solution. That opens fine. I have both Solutions/projects under Azure Devops/Git. I had this issue about 2 weeks ago and I was able to […] Design Ideas and Questions [Database Design Help]( - Hello, I am looking for some input on a database I am attempting to design for a video game. I have most of the design basically done, but I am having trouble finishing this last part. The basis of the database is around the entities "item", "fluid", "node", and "building". The schema related to these […] SQL Server 2022 - Development [VNN and unique ports]( - Background TLDR: We are creating non-prod WFC clusters. Each node in the cluster will have a handful of sql instances and each instance will have many Always on groups separating logic database sets.  The Issue: I did some testing and while the warning about 1433 on VNN creation with unique IP's per VNN is […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -