👋 Hey,

As data professionals, we know how both relational and distributed databases, make all boring tasks easy. They help us with almost every data-related task imaginable, from building business models to creating reusable data structures and whatnot!

We at _datapro team have done our homework and got to know how important it is to stay up to date with the latest DB techniques. That's why we've put together a super-focused, distilled edition of all things databases, including tips, tricks, cheat sheets and other valuable resources. Join us over the next few weeks and become a real database ninja!

Key resources:

• Azure SQL Data Migration Assistant

• Long-Term Backups (up to 10 years) on Autonomous Database

• Stateful Stream Processing With Memphis and Apache Iceberg

If you are interested in sharing ideas to foster the growth of the professional data community, then this survey is for you. Consider it as your space to share your thoughts! Jump on in!

TELL US WHAT YOU THINK

Cheers,
Merlyn Shelley
Associate Editor in Chief, Packt

Latest Forks on GitHub!

• mybatis-3: The MyBatis SQL mapper framework makes it easier to use a relational database with object-oriented applications.

• etcd: It’s a distributed reliable key-value store for the most critical data of a distributed system.

• sqlx: Library which provides a set of extensions on go's standard database/sql library.

• sqlitebrowser: DB Browser for SQLite (DB4S) is a high quality, visual, open-source tool to create, design, and edit database files compatible with SQLite.

• tidb: An open-source distributed SQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads.

In the Industry

Microsoft

• Data Exposed: Rethink your backup and recovery strategy with T-SQL Snapshot Backup in SQL Server 2022: This weekly series of short videos discusses the new capability in SQL Server 2022 that introduces new cross-platform T-SQL commands to perform a snapshot backup of your user databases. Also, you will get to know when database administrators and developers would use this feature, along with end-to-end demos and usage considerations. Watch it here.

• SQL Server 2022: Intel® QuickAssist Technology overview: Intel® QuickAssist (Intel QAT) technology in SQL Server 2022, solves the greatest challenge of regular backup schedules by providing hardware acceleration and CPU offload capabilities for enhanced compression and decompression functions. QATzip currently supports offloading backups to hardware devices. Find here.

Oracle

• Long-Term Backups (up to 10 years) on Autonomous Dedicated! Up until now, all backups in Oracle's Autonomous Database were chained to the retention set on the Autonomous Container Database (ACD) for up to 60 days. With this new launch of Long-Term backups for Autonomous Databases (ADB), customers can fulfil their cybersecurity, DR plans, and compliance requirements for long-term storage and backups. And the best part? This new capability lets customers select a retention time between 90 days and 10 years. Check here!

Just for Laughs!

Source: Hongkiat

Quick Database Practice

Azure SQL DMA SKU Recommendations

– By Ahmad Osama, Shashikant Shakya

DMA is a free tool from Microsoft to facilitate migration from SQL Server (on-premises or IaaS) to SQL Database.

It can assess the source database to list out the compatibility issues between SQL Server and SQL Database. Once you fix the compatibility issues, you can use it to migrate the schema and data to SQL Database.

The activity requires DMA to be installed on your machine. You can download it here.

To get recommendations using DMA for the toystore database, perform the following steps:

Step 1: Open ~/DMA/RunWorkload.bat in Notepad. You should see the following code:

CD "C:\Program Files\Microsoft Corporation\RMLUtils"

ostress -SXENA\sql2016 -E -dtoystore -Q"Execute usp_Workload" -n10 -r100000 -q

@echo off

Pause

Modify the RMLUtils directory location if required. Change the ostress parameter to point to the toystore database in your local environment. Save and close the file. Double-click on the file to run the workload.

Step 2: Open DMA/RunSKURecommendation.bat. You should see the following code:

cd "C:\Program Files\Microsoft Data Migration Assistant\"

powershell.exe -File .\SkuRecommendationDataCollectionScript.ps1 -ComputerName XENA -OutputFilePath "C:\Professional-Azure-SQL-Database-Administration-Third-Edition\Chapter02\DMA\Counter.csv" -CollectionTimeInSeconds 7200 -DbConnectionString "Server=XENA\SQL2016;Initial Catalog=master;Integrated Security=SSPI;"

The preceding command runs the DMA SkuRecommendationDataColletionScript.ps1 PowerShell script to collect the required counters. The script is available at the DMA installation location.

Modify the parameter values to point the script to your SQL Server environment.

Save and close the file. Double-click RunSKURecommendation.batch to run the sku counter collection script.

The script will run for the time specified by the CollectionTimeInSeconds parameter and will write the counter values to the file specified by the OutputFilePath parameter.

To learn more about the process, head over to the book, Professional Azure SQL Managed Database Administration - Third Edition (packtpub.com)! The best part is that this chapter is available for free to read without paying a dime.

SIT BACK, RELAX & START READING!

Find Out What’s New?

• Stateful Stream Processing With Memphis and Apache Iceberg: When handling nested schemas and complex queries, processing and managing huge amounts of data in AWS S3 can be tedious. This blog speaks about how to use Apache Iceberg & Memphis on AWS S3 to manipulate large scale data, including code and images. If you are curious to find a scalable and cost-effective way to query loads of data, then follow the link here!

• pg_anonymize, a new extension for simple and transparent data anonymization: pg_anonymize is a PostgreSQL extension which allows simple, robust and transparent infrastructure for data anonymization. Its goal is to ensure that users connected with an anonymized role will only access the anonymized version of the data without any restriction. If you would like to give it a try, read here.

• Ontotext GraphDB 10.2 Makes it Easier to Manage and Store Data: Ontotext, recently released GraphDB 10.2, empowering users to easily monitor system health and diagnose problems. With improved cluster backup and cloud support, it lowers traditional memory requirements and facilitates a more transparent memory model to save on cost and power needs. Check here.

• pgEdge Launches Fully Distributed Database for the Network Edge Based on PostgreSQL: According to its recent launch of multi-active distributed database system, pgEdge helps in leveraging the power of PostgreSQL’s open-source architecture. This is executed by allowing compute functions and PostgreSQL to be deployed at, or close to, the network edge. Read here to know more about it.

• Dealing With “Too Many Connections” Error in MySQL 8: If you are using MySQL 8.0.14 or higher, you should enable the admin interface, to rule out “ERROR 1040 (08004): Too many connections”. This page discusses how to enable this feature and which in turn leverages a great feature by allowing access to the database. Also, it does not affect normal database performance, however, brings in great power. Check this!

See you next time!