What Every DBA Should Know about Always On Readable Secondaries (2024-02-23)

SQLServerCentral Newsletter for February 23, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [What Every DBA Should Know about Always On Readable Secondaries]( - [Redgate Summit: The Database Landscape]( - [From the SQL Server Central Blogs - #TSQL2sday 171 – Describe the Most Recent Issue You Closed]( - [From the SQL Server Central Blogs - Security Justifications for Infrastructure as Code (IaC)]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Filestream on Linux]( The Voice of the DBA  Free SQL Server No, I don't have a way to license a SQL Server instance for your application for free. Microsoft still charges for production workloads. However, there are several ways that you can do development or testing for free, or at a greatly reduced cost. Bob Ward spent a bit of time researching options and he's written [a post that outlines a number of your options](. It's very comprehensive and I know a lot of us are grateful to Bob for summarizing our options. As I work with developers in organizations, there are often questions about the licensing for dev editions. This comes up more with Oracle than other platforms, but SQL Server does get asked about. We made a decision to support SQL Server Developer Edition in [Redgate Clone]( which is in line with what Bob's post above says: this edition is for developers (and it's free). There is one caveat here, which I never knew. The licensing guide says you cannot build test data and move that into production. I'm assuming this doesn't mean you can't create a list of US states, countries in the world, or statuses. I think this means something more substantial, but if anyone knows, let me know. One question I get asked a lot is about how do you develop locally if your production target is Azure SQL Database? The best option is a container, as outlined in [this MS Learn article](. It's not completely the same, or necessarily as up-to-date, but it's close and likely good enough for most of us. If you really want an Azure SQL DB instance for dev/test, it's not going to be free. Unless you can live within the monthly Azure credits, which are pretty low for this purpose. If you are a Visual Studio subscriber, you can get some discounts, however, and I didn't know this, you can't use Azure credits with the VS benefit. There is also a "[free offer]( for Azure SQL database. This is fairly limited, but you do get free compute and 32GB of data for the lifetime of your subscription. If you work with small development databases, and you aren't querying like crazy, that might work well for you. It's also a great way for students or job-seekers to get some experience with the platform at a low (or no) cost. You can even try Managed Instance for free for 12 months. The cloud isn't for everyone, every organization, or even every application, but it does work well in some situations. Whether that fits you or not can be tricky to determine, but you certainly should be working in the cloud in a dev/test manner to help inform the decision. I would hope that most of you have dev/test databases in place for on-premises/IaaS systems as well. I don't run into too many people developing on a production instance, but I do see some, and I certainly see some that work in heavily shared environments. Cost shouldn't be a factor here, at least not for SQL Server, and there are options to help you ensure you have all the dev and test instances you need. Read [Bob's post]( see if you can help make your developer's lives easier with some of the resources. And learn to follow good software development practices for your SQL code, just like the C# developers do. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [What Every DBA Should Know about Always On Readable Secondaries]( SQLSACT from SQLServerCentral Learn a bit about the impact of readable secondaries, which you might not expect. [External Article]( [Redgate Summit: The Database Landscape]( Additional Articles from Redgate A series of Redgate events across the globe for data professionals who want to improve their skills and knowledge about Database DevOps, learn about moving to the Cloud, AI, and working across multiple databases. Redgate Summit aims to bring together database professionals who want to level-up their skills to navigate the increasingly complex database landscape. Learn more about emergent technologies, and reliably solve the complex challenges of database management across the entire DevOps lifecycle. Each event will all feature three separate tracks covering New and Future Technologies, Deep Dive Solutions, and Leadership, all promising to equip senior database professionals with the tools and insights needed to navigate the intricate landscape of 2024. [Blog Post]( From the SQL Server Central Blogs - [#TSQL2sday 171 – Describe the Most Recent Issue You Closed]( Koen Verbeeck from Koen Verbeeck It’s #TSQL2sday time! This month’s invitation has been sent out by Brent Ozar and he asks us to describe the most recent issue – or the last ticket –... [Blog Post]( From the SQL Server Central Blogs - [Security Justifications for Infrastructure as Code (IaC)]( K. Brian Kelley from Databases – Infrastructure – Security Last week, the Cybersecurity & Infrastructure Security Agency (CISA) put out an emergency directive which ordered federal agencies to pull Ivanti Connect Secure and Ivanti Policy Secure installations from... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Filestream on Linux I want to implement a Filestream table on SQL Server 2022 running on Linux. Can I 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) A New Audit I have a brand new D: drive on my system that is formatted, but contains no files or folders. What happens when I execute this code? CREATE SERVER AUDIT audit2 TO FILE ( FILEPATH ='D:\audit' ); GO Answer: The audit is not created and an error is returned Explanation: If the path is not valid, this error is returned: Msg 33072, Level 16, State 5, Line 8 The audit log file path is invalid. Ref: CREATE SERVER AUDIT - [ [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 2016 - Administration [SSRS email via Subscriptions]( - All, Getting this error: Failure sending mail: The remote certificate is invalid according to the validation procedure.Mail will not be resent. Not sure what this means and I need to send to administrator team (3rd party) so they can correct.  Thank you, SQL Server 2016 - Development and T-SQL [throttle things so that use of certain linked servers get less resources]( - hi we use 2019 standard sql server for both our ssrs traffic and as a major homegrown dw/tabular cube source on the same server. We want to add a linked server to oracle on that server but would prefer that resource wise it get less horsepower/priority than everything else running on that server. If i […] [Combining Two Records in to 1]( - Good day Experts, How can i combine these two rows into one in sql .So i want it to be one row. Many thanks SQL Server 2019 - Administration [DataBase Already Exists Metadata Error]( - Ahoy, Seems like i renamed my SSAS Database accidently and only changed the CAPS (Old_NAME) to (Old_Name). Now my problem is that i cant deploy the Database with the Old_NAME anymore. I have tried serveral things but it always tells me the metadata manager says that the database already exists. Problem is the solutions i […] [Adding a 3rd (Reporting Node) to an 2 Node AG in Standard Edition.]( -  Hello, I am looking for an efficient method to add a 3rd Instance/Node C for reporting purposes. The solution has to be: efficient/not expensive, not Enterprise Edition, Minimal licensing, Will work on 2016/19/22 not affect the Nodes A & B performance when either is Primary. Easy to manage. So far it appears the only […] [Mirroring and database access problems during database migration]( - I have a system with two servers and three client workstations. Servers were configured to work as principal/mirror servers (mirroring was configured with certificates). I have two databases in SQL Server - Archive - database where we store all measurements from system and Process database. There was a need to move Archive database from disk […] [Combining Two Rows in to 1Row]( - Good day Experts, How can i combine these two rows into one in sql .So i want it to be one row. Many thanks [How to find the root cause for log .ldf file increased suddenly to 500 gb]( - How to find the root cause for log .ldf file increased suddenly to 500 gb we dont know what made increase in size is there any script to find the previous transactions occurred a day before or a week before to find out what transactions ran and what dll operations occurred or what long running […] SQL Server 2019 - Development [Calculate Sales with different start Dates]( - We have a rebate program where I need to calculate sales based on the date the customer joined the program. Ex: Customer A joins the program on October 5th, 2023 and Customer B joins on December 8th, 2023. I'd like results to look at the start date and show their sales by month for a […] Connecting [SPN's Validate"Good Status" in Kerberos Config Mgr- cannot gen SSPI]( - After Adding SPN's and Validating "Good Status" in Kerberos Config Manager attempt to log into SQL Sever Remotely Results in: The target principal name is incorrect. Cannot generate SSPI context. SQL 2017- Windows Server 2019 We have added 4 SPN entries for one of our SQL Server running as a domain account. It is a […] Integration Services [How to Connect to Azure Database for MySql]( - Dear, I need to retrieve data from Azure Database for MySql using SSIS. I am able to connect to previous MySql database. The team has recently migrated to Azure Database for MySql. I have given all the required info in the .Net Provider for MySql database I have given port 3306 but still can't connect. Error […] SQL Server 2022 - Administration [upgrade order of components]( - I am upgrade a SQL server 2019 instance, it has both DB engine, SSIS service , SSRS service in same server due to it only host a small application. Now I would like to upgrade it from 2019 to 2022 and CU11, My question is about the order, My order is like this, simplified: upgrade […] [Don't report error when one Maintenence Plan Task fails]( - I have a Job running a Maintenence Plan. The plan performs the Backup Database, Check database Integrity, Maintenence Cleanup and Reorganise Index tasks. Occasionally the Reorganise Index task fails with the error '"Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim....'. It runs in […] [IS there any script to find what transactions ran yesterday or week or mont]( - Is there any script to find what transactions ran yesterday or week or month before in sqlserver we are not using any tools and if any issue comes we can see sp_who2'active' but if log size is full suddenly before 2 days and if we want to find the root cause why log got increased […] SQL Server 2022 - Development [How to get Active counts between dates. Please]( - Good Morning ,  I have a policy table with below structure. I would like to get how many active policies by day (running total may be).  Create Table #Policy (PolNumber int, StartDate date, EndDate date, Status varchar(10))  Insert Into #Policy Values (100,'2023-01-04','2024-02-22','Active') Insert Into #Policy Values (100,'2021-01-04','2022-02-04','InActive') Insert Into #Policy Values (101,'2022-01-01','2025-12-26','Active') […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -