SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Control Permission Limits

The Voice of the DBA

Daily Coping Tip

Plan something fun and invite others to join you

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.

The Database Server Upgrade for Let's Encrypt

Let's Encrypt is a service the provides free SSL certificates for web servers. They have grown from a small project to a critical service on the web that issues over 235mm clients each year, and often 1.5mm each day. They depend on a MariaDB database to support this.

Recently they released a post on the database server upgrade they made. They run physical hardware on a managed services provider (Datto), rather than using a cloud platform service, which is fine. While cloud services are great, they aren't always the best choice. I'd like to see them talk about any evaluation as to what Datto is better than, say, AWS.

In any case, I like seeing how people upgrade hardware, as well as the impact of changes. I haven't always done this when I've upgraded systems, though I do notice the new one always runs faster. Good for me, but for production systems, we should have better metrics, if for no other reason than to better learn how to size the next upgrade.

In this case, Let's Encrypt has a great post that shows how the hardware changed. They moved from Intel to AMD, 48 threads to 128 threads, doubled RAM, and went from mid 500MB/s read/writes to 3200MB/s. Their response metrics also improved, with API requests going from 90ms to 9ms. CPU dropped from 90% to 25% and latency is a third of previous values. Quite an impressive change.

I like seeing the details of their change, and I think this is a nice pattern for others to think about. Whether you spec out machines, VMs in AWS, or DTUs in Azure. You ought to have some metrics that matter for your customers, and then compare the before and after. Prove your upgrade worked, or maybe prove that you need to alter your specs after the upgrade. One advantage with a cloud provider is that you can often scale up or down as needed. With an MSP, it might be harder, but I bet you can still change things fairly quickly.

While we all want to test a new configuration before we turn it on, simulating a workload can be challenging. Clients somehow always seem to find ways to stress systems we hadn't anticipated. When spec'ing out a new system, make sure you have room to grow if you've underestimated how the new system performs. You also might have targets that aim to improve your metrics to some level. Even if you don't hit the estimate, you will learn something about your methodology that can help you improve for the future.

Above all, publish something like Let's Encrypt. Share some data and help our industry move forward. We've love to have you write something for SQLServerCentral.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

PostgreSQL Python Integration

Shivayan Mukherjee from SQLServerCentral

Overview PostgreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language and Python is a high level, interpreted general purpose language. This article covers basic integration of Python with PostgreSQL apparently how we can establish connection with PostgreSQL database using Python program and perform CRUD operations on […]

Product Training Sessions from Redgate University

Additional Articles from Redgate

In 2020 Redgate introduced a series of live training sessions on a selection of their most popular tools, offering customers top tips and demonstrations of new features. If you didn't get a chance to join then check out the recordings available here and be sure to subscribe for news about upcoming sessions.

Simple way to find errors in SQL Server error log

Additional Articles from MSSQLTips.com

In this tip we look at how to parse the SQL Server error log to only extract the errors and corresponding error messages.

From the SQL Server Central Blogs - SELECT * Hurts Performance, Badly

Grant Fritchey from The Scary DBA

Quite a few years ago, I wrote a post about SELECT * and performance. That post had a bit of a click-bait title (freely admitted). I wrote the post...

From the SQL Server Central Blogs - Azure Platform Series “You have exceeded the maximum amount of scale changes within the past hour”

HamishWatson from The Hybrid DBA's Blog

This blog post relates to where you might be doing scale operations of your app services or VMs in Azure and get the following error after doing quite a...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Control Permission Limits
If I grant someone the CONTROL permission on a database object, can they then grant permissions to others to access the object?
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)

Pushing an Amend

I committed a change and then pushed the branch (featureSales23). However, I then found a problem in my message. I amended the commit message, but how do I get this up to the remote repository (origin)?

Answer: git push --force origin

Explanation: If I've pushed a commit and then amend the commit message, I need the --force flag along with the name of the remote. Ref: git push - https://git-scm.com/docs/git-push

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

Backup to AWS S3 - We backup over the weekend our full backups to a backup server with a large EBS volume. Once this is complete we have a second step to invoke a powershell script remotely on the backup server that uses Rclone to sync the backups to S3. Perhaps this is a daft idea - and I'd be […]

Multi subnet failover cluster - My goal is to have an FCI across 2 nodes with shared storage (node 1 & node 2) in primary data center on subnet1 and then 2 nodes with shared storage(node 3 & node4) in secondary data center as subnet2. Node1 & Node2 are connected to subnet1 and Node 3 & Node 4 are connected […]

Grant user access to view database only - Hi I need to grant a user access to a DB on the server with read only access to view all tables and views the user is a domain user so I set them up in logins and gave them data reader only to the db what else do I need to do as when […]

SQL Server 2017 - Development

Compare columns between 2 tables - Create TABLE #TableA ( TaskNameA nvarchar(100), GovernA bit, ToolTypeA nvarchar(10), ResourceNameA nvarchar(100) ) Create TABLE #TableB ( TaskNameB nvarchar(100), GovernB bit, ToolTypeB nvarchar(10), ResourceNameB nvarchar(100) ) INSERT INTO #TableA (TaskNameA,GovernA,ToolTypeA,ResourceNameA ) SELECT 'Gap Follow Up', 1 , 'Tool' ,'Regional/Local' UNION ALL SELECT 'Gap Extension', 1 , 'Excel' ,'Advanced Scoping' UNION ALL -- good SELECT 'Gap […]

SQL Server 2016 - Administration

Port 1433 does not have SSL certificate even after the necessary settings - Hi I have a clustered MS SQL server setup (2016, Enterprise, on AWS instance) with two nodes. I had configured SSL encryption on the two nodes using the guides provided by Microsoft as well as SQL tips. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15 https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/ Everything works absolutely fine. I've checked the SQL Server logs which shows the SSL certificate was […]

SQL Server 2016 - Development and T-SQL

Error in bcp query out - I've been playing around wiht bcp out the data into csv file but been getting this error. I've a setup a table where I read the table name, servername, database name, filepath and build a dynamic query through stored procedure. But I've been getting below error and can't seem to figure out. If anybody has […]

Calling remote powershell script through SQL server agent - I have a powershell script in remote server. When I go to remote server and execute the script, it works as indented. When I schedule it to run through SQL server agent, it just says succeeded but code really doesn't do anything. Any help would be appreciated. I've attached sql server agent image, how the […]

Administration - SQL Server 2014

Database Architecture - Hi Experts, Our architecture team come up with a new plan to have multiple databases for different microservices , that means for each project we will have a minimum of 3 databases only for the application, one for user management and another one for documents. As per them this is to make sure exclusive access […]

Development - SQL Server 2014

Need to strip off the beginning of a field in SELECT statement - I have this table that unfortunately has spaces in the field, which I am trimming using LTRIM(RTRIM(Field)) on. In addition, sometimes the field has "1101" at the beginning of the Field. Is there a way to remove it if it's there? Current SELECT statement: SELECT LTRIM(RTRIM(Field)) FROM myTable Example field values: 1101BRZ INSP etc.

SQL Server 2019 - Administration

Assigning user to Azure Active Directory enterprise application via python SDK - I have added slack to Azure Active Directory Enterprise application and running SSO. I want to assign users and groups to the app in azure. So, basically, it is really easy to use portal. Also , I provided the PowerShell script as well. #AzureAD Module #Azure login : Connect-AzureAD $app_name ="slack" $app_role_name = "user" $user […]

Powershell script giving the error "The specified value 'Patch' is invalid." - I am trying to patch my SQL server with CU8 and GDR for SQL Server 2019 on a windows 2019 server There is only the default instance and when I run the following command in powershell as administrator I get the error below and the summary files are attached: setup.exe /updatesource="E:\SQL_2019\patches" /Action=Patch /allinstances /IAcceptSQLServerLicenseTerms The funny […]

SQL Server 2019 - Development

Sequentially Adding Variable Number of Rows - I need to add a number rows together and I'm not sure how to do it. I have the following dataset. I want to add the previous rows together to get the following. Can this be done?

Sql Joins ! - Hi There Im pretty new to SQL ..and best describe my queries at this stage as basic. I wonder if I could get some thoughts on an sql query that for some reason, the results are missing out some records from one of my join tables. I've added some left joins pulling in fields from […]

Reporting Services

access registrykey from within SSRS report - I am trying to the code function to determine if a particular application is installed on the client machine with the intent that it would either result in one of two actions to occur. in reading materials online, it appears that Microsoft.Win32.Registry or Microsoft.Win32 should have the access to the RegistryKey object so that I […]

Integration Services

ispac file association broken - I updated my visual studio 2019 professional recently, and when I try to double click an ispac file now to deploy, then it lost the file association. I tried to manually add the file association back as the isdeploymentwizard.exe again, but now when I open it, I get a window which suggests that it needs […]

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.