Challenge
The client faced a critical operational challenge as its EHR vendor, eClinicalWorks, began encouraging clients to migrate their application and database infrastructure to the cloud. While this shift promised scalability and vendor-managed infrastructure, it posed a significant risk to the client’s reporting operations.
The client’s reporting processes run on-premise, and eClinicalWorks disallows external connections to the cloud database. With eClinicalWorks only offering one-time full backups and daily transaction log backups via Azure Blob Storage, the client needed a way to maintain a read-only, up-to-date reporting database on-premises, without disrupting production systems.
Solution
Performance One Data Solutions engineered a robust, automated solution to bridge the gap between cloud-based databases and on-premises reporting needs.
Key Components:
- PowerShell Automation: Custom scripts were developed to:
- Query Azure Blob Storage using the Az.Storage module.
- Identify and download only new transaction log backups using AzCopy.
- Maintain an ordered list of logs based on Log Sequence Numbers (LSNs).
- SQL Server Integration:
- SQL Server Database Stored Procedures were created to read, order, and apply logs.
- Extracting metadata from transaction logs using RESTORE HEADERONLY and storing it in a temporary table.
- Applying the logs in the correct LSN order, ensuring database consistency.
-
- Once all logs are applied, the database is set to read-only, making it safe and efficient for reporting.
This solution ensures that the client’s reporting database is always current, consistent, and isolated from production workloads, delivering both performance and peace of mind.
Results
- Zero impact on production systems during reporting.
- Automated daily updates from the Azure blob.
- Reliable, read-only reporting environment on-premises.
- Compliance-ready data access for healthcare reporting needs.
Tools Explained
What is the Az.Storage Module?
The Az.Storage module is a PowerShell module provided by Microsoft that allows you to manage Azure Storage resources using PowerShell commands.
Think of it like this:
Imagine Azure Storage as a big digital warehouse where you can store files, databases, backups, and more. The Az.Storage module gives you a remote control (via PowerShell) to interact with that warehouse — to upload, download, organize, and manage your stuff.
What Can You Do with Az.Storage?
Here are some common tasks you can perform:
- Upload or download files (called blobs) to/from Azure Blob Storage.
- Create and manage containers (like folders for blobs).
- List files in a storage account.
- Set permissions and access policies.
- Manage storage accounts and their properties.
Why Use It?
- Automation: Great for scripting and automating storage tasks.
- Efficiency: Manage large-scale storage operations quickly.
- Integration: Works well with other Azure PowerShell modules.
What is AzCopy?
AzCopy is a command-line tool developed by Microsoft that helps you move and manage data between your computer and Microsoft Azure storage services, such as Blob Storage, File Storage, and more.
Think of it like this:
AzCopy is a super-efficient delivery truck for your files in the cloud. Suppose you’re working with large amounts of data, like uploading backups, downloading logs, or syncing folders. In that case, AzCopy can do it much faster and more reliably than manually dragging and dropping files.
- A command-line tool created by Microsoft.
- Used to transfer data to and from Azure Storage.
- Works with Blob Storage, File Storage, and Table Storage.
What Can You Do with It?
Here are some common tasks you can perform:
- Upload files or folders to Azure.
- Download data from Azure to your local machine.
- Copy data between Azure storage accounts.
- Synchronize folders (local ↔ cloud).
- Resume interrupted transfers.
- Perform high-speed, large-scale data transfers.
Why Use AzCopy?
- Fast and efficient for large files or lots of data.
- Great for automation and scripting.
- Doesn’t require writing complex code.
- Works well in batch operations or scheduled tasks.
Ready to Modernize Your Data Infrastructure?
Whether you’re navigating a cloud migration or need to optimize your reporting architecture, Performance One Data Solutions delivers tailored, high-performance solutions that work.
👉 Contact us today to schedule a free consultation and discover how we can help you turn complex data challenges into streamlined success.
