Concurrency Control in SQL Server (2020-05-12)

 SQLServerCentral Newsletter for May 12, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Concurrency Control in SQL Server]( - [Using SQL Clone and SQL Change Automation to switch database branches]( - [Translating a SQL Server Schema into a Cassandra Table: Part III Many-to-Many, Attribute Closure and Solution Space]( - [From the SQL Server Central Blogs - Upcoming Webinar – SQL Server Business Continuity – May 21]( - [From the SQL Server Central Blogs - What is (local)?]( Question of the Day - [Tempdb Default Size]( The Voice of the DBA  Daily Coping Tip What are your most important values. Use them today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Defending Against Ransomware I don't manage production systems at Redgate, and I've not seen us have any disruption in the access to systems across the last few years. At a number of events, however, I've spoken to friends that have told me about the problems they've had with ransomware. I've been quite surprised how often this happens, as I see headlines, but none of the half dozen or so people I spoke with have worked at any company in the headlines. This is likely a bigger problem than I thought. I don't know if you've been attacked, but you should be prepared. Denny Cherry has had to deal with this at at least one client, and he wrote a few things down in [this article](. He doesn't necessarily recommend you pay, though that's certainly an option. If you do, you should be prepared for future attacks or other issues. Anyone that wants to hold your data hostage once might be inclined to do it again. Apart from backups, air gaps, and other preventative measures you might take, including limited access between some workstations and servers, you ought to be sure you can rebuild systems that work in a similar manner to the current ones. Do you know what the specs and settings are for all your systems now? So many people assume they could just go get that information from the system itself, but what if you VMs and VM host are compromised? What if your monitoring system is encrypted? Do you really know all the settings that have been changed from defaults? If your DR plan somewhere outside the network? This is a good reason why DevOps and [infrastructure as code]( (IaC) are important in a modern organizations. In these cases, a VCS can hold the data you need to rebuild systems. This is a good reason by using GitHub or some remote VCS might be better than self-hosting your code. If you routinely rebuild systems for dev/test, you'll have some idea of how to deploy, and if you think about IaC as a part of your DR process, you should be able to redeploy on new hardware (or existing hardware that's wiped clean). Ransomware is a part of the new world we live in, and we ought to be prepared to defend against it. Ensure you're ready today, because there will likely be other types of attacks in the future. The more prepared you are with better practices now, the more likely you'll be ready to adapt to some other security need in the future. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Concurrency Control in SQL Server]( saurabhhooda from SQLServerCentral Learn a bit about concurrency problems in SQL Server, the issues they create, and the different isolation levels that help you solve them. [Using SQL Clone and SQL Change Automation to switch database branches]( Additional Articles from SQLServerCentral Learn how to use SQL Clone together with Git hooks and SQL Change Automation to branch your database in Git as easily as your code. By enabling multiple copies of the same database to exist on a single instance, the integration of these tools allows seamless database branch switching. [Translating a SQL Server Schema into a Cassandra Table: Part III Many-to-Many, Attribute Closure and Solution Space]( Additional Articles from Redgate In the third and final article of the series, Shel Burkow demonstrates several designs that cover many-to-many relationships, attribute closure, and solution space with Cassandra tables. From the SQL Server Central Blogs - [Upcoming Webinar – SQL Server Business Continuity – May 21]( kleegeek from Technobabble by Klee from @kleegeek I’m proud to announce a new webinar that I’ll be presenting at 1pm Eastern on Thursday, May 21st, in conjunction with MSSQLTips and SIOS called “SQL Server Business Continuity... From the SQL Server Central Blogs - [What is (local)?]( Kenneth.Fisher from SQLStudies When connecting to a SQL Server instance sometimes you’ll see (local), localhost or even just a single period (hard to ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Tempdb Default Size I've installed SQL Server 2017 with all the defaults (clicking next, next, next where I can). I took the recommendations for tempdb. This gave me 8 data files. If I fill up a single file, what is the amount of growth that will take place? 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 Rounding Calculation What does this return? (guess, don't test) DECLARE @i DECIMAL(10,4) = 3.23; SELECT @i - ROUND(@i, 0) AS Guess_Dont_Test  Answer: 0.23 Explanation: A simple math test, just to see if you know how rounding works. This subtracts the rounded value from the original value. This should return the decimal part, 0.23. Ref: - Rounding Tips and Tricks - - ROUND() - [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 [Very odd behavior with parameter sniffing, overall poor performance post CU19]( - Hey all, Seeing something very strange. Overall performance on a SQL 2017 instance has dramatically worsen since CU19 was installed. Have historical records on nightly processing that shows that post CU19, report execution times went through the roof. One in specific, used to run consecutively in 1 minute. After CU19, it jumped up to 10 […] [Replication]( - I am thinking to configure Always on or replication from two databases to 1 single database for reporting and consolidating the dbs . Current prod is on sql 2016 and planning to configure the reporting server with SQL 2017 and configure Always on between SQL 2016 primary and SQL 2017 secondary. My understanding is lower […] [Connection time out SQL 2017]( - Hi Team, I am getting this message while I try to connect to a new SQL Sever 2017 server : Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration […] SQL Server 2016 - Administration [Keep track of who accessed\modified the database except application user\login]( - Hi, SQL version: sqlserver2016 standard . I am looking to keep track of the details for audit purpose who access to database, from which machine , and data modifications. event_time Client Machine Name\IP UserName\Loginname server_instance_name database_name schema_name object_name SQL statement Any help here ? Thanks.   [AD group access - persists after removed?]( - I have a strange problem regarding an Active Directory group that has access to a database. We have a AD group called APP_OP that was created a long time ago to give users access to specific databases used by an in-house application. So basically we created the group as a login on the server and […] [What Are Your Thoughts on Sections 302, 401, and 409 regarding Data Integrity]( - DELETED. [SQL Server Native client]( - Hi, Installed SQL Server2016 standard on Windows server2016 Standard, Later i observed that SQL Server2012 Native Clients also installed along with that. Is it safe to uninstall ? do i need to keep as it is ? Thanks. SQL Server 2016 - Development and T-SQL [Import documents Saved in AccessDB into SQL server]( - I wonder if anyone has tried to do this Having been asked to examine the ability to upscale a database from the AccessDB one of our teams had developed I started looking at the information in it In one of the columns they have stored "Supporting Documentation" such as word documents and emails with a […] SQL Server 2019 - Administration [Cannot create cloud witness for Windows cluster on Azure]( - All Windows firewalls are actually turned off. The Windows Cluster is the 1st step for my SQL AG setup but can't make it work. Any suggestions? Where else can I check? SQL Server 2019 - Development [MacOs User connection to SQL Server from Excel]( - We have a financial reporting tool , which gets data from SQL server via stored procedure and works fine on Windows computers. (Please forgive me if I am using the wrong terms in the next few lines, I am neither IT nor native speaker): If they work on a Windows netbook, they logon on their […] Reporting Services [Performance Dashboard and Server Dashboard]( - Hello All,  I am wondering if anyone has been able to successfully recreate or find the RDL file for the Server Dashboard? I have been able to publish the Performance Reports Dashboard but upper management wants to only see what the server dashboard has to offer. This is a add-on that Microsoft allows you […] [Date parameter input in UK format but at run-time changes to US format]( - Hi all I'm sure I've found a solution to this problem before but it may just have been updating the Language on the report to en-GB, which I've done on this report. I have a fetchxml report (yes I know...) where processingdate <= @ProcessingDate. The parameter input is in UK format but whilst running it's […] SQLServerCentral.com Website Issues [User Forum is Terrible]( - Today is not the first time I've had problems with the SQL Server Central forums since they were changed (not sure how long its been).  The new/current view is like a dumbed down view of a more complex user forum. There also is no FAQ or HOW-TO for the forum at least not that i […] COVID-19 Pandemic [Daily Coping 6 May 2020]( - Today’s tip is take a step towards one of your life goals, however small. My thoughts: Sarbanes-Oxley [What Are Your Thoughts on Sections 302, 401, and 409 regarding Data Integrity]( - MOVED TO SQL 2016 Administration section   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -