From Raw Data to Automated Insights
Every machine learning (ML) expert knows that a good ML data pipeline is as good as the large amounts of data sets it can access. Therefore, data integration techniques are critical for:
- Connecting business users and data that is difficult to find
- Relating facts between intricate data sources with well-selected and curated datasets
- Providing large amounts of data
When considering data integration, we are implicitly creating an automated process. As a result, another important component is security, especially if the solution requires communication with a given application (in this case Oracle Cloud resources) to access a given resource.
OAuth is a security standard that applications can use to provide client applications with “secure delegated access.” OAuth works over HTTPS and authorizes devices, APIs, servers, and applications with access tokens instead of credentials.
After coupling our data integration process with a secure way to communicate with a remote application to access a given resource, the next main step is to process the data obtained from the given resource. This step involves an on-the-fly transformation and final disposition of the output. The destination of the processed data will be a database (here we chose Oracle Autonomous Datawarehouse – OADW).
Having the data in OADW opens up unlimited possibilities for analysis: typical ad-hoc (descriptive) analytical reporting or the advanced (predictive) analytics using ML algorithms.
The Business Case:
Oracle Identity Cloud Services (IDCS), among many other security related things, keeps a log of:
- Successful/unsuccessful login attempts
- IP address
- Browser
- Time
- User id
- etc
With the caveat of this audit information only for a maximum period of 90 days.
The Solution:
Fortunately, Oracle IDCS offers a rich set of APIs, and one in particular, “Audit Events REST APIs,” will help us resolve the inconvenience here.
Main steps:
1. Create an Application in Oracle IDCS and get Client ID, Client Secret
2. Verify the credentials work by using Postman
3. Develop an automated process to periodically access the remote resource:
- Apply the credentials to obtain the Access Token that will allow us access the remote resource
- Get the resource (apply on-the-fly transformation if needed) and disposition the result in given destination (OADW)
- Wrap all steps above in python script for automation
OAC Reporting and Analysis:
Now, all the data is ready for further analysis. You can simply create a project and include the attributes for pattern analysis.
One example is to perform a trend analysis by analyzing the number of successful, unsuccessful login events by date or by hour (of the day). Another type of analysis is to find out if selected attributes have any sort of similarity (Clustering). Your analysis can continue on each individual cluster for further detection of other specific characteristic in the cluster.
Want to learn more about building automated processes in OAC? Watch my webinar, “Oracle Analytics Cloud: From Raw Data to Automated Insights.”
The post From Raw Data to Automated Insights appeared first on Datavail.
Accelerated Database Recovery Alleviates DBAs’ Frustrations
Many DBAs, such as myself have gotten frustrated at some point or another by long-running transactions with improperly written queries that are filling up databases transaction logs, causing SQL dumps, etc. Then, it gets worse when such transaction gets aborted ending up into never-ending rollback recoveries. Accelerated Database Recovery feature is an enhanced recovery process that can help us all avoid such problems.
Accelerated Database Recovery (ADR) is a new feature that comes with SQL 2019 and Azure SQL database, it helps speed up the database recovery process and decreases rollback time for any long running large transactions when aborted. ADR feature is also available on Azure database and some of its major benefits include quicker and reliable recovery, quicker rollback. Plus, it doesn’t matter how much a longer running transaction you have or how many active processes you have on server, it keeps truncating log file to keep its growth under control.
The ADR Process
Let’s looks at the different elements of ADR. It comes with mainly three new structures: PVS, ATM, and S-Log and fourth one is Cleaner; these new components helps ADR more efficiently and quickly.
- Persistent Version Store (PVS) – this mechanism stores row versions within the database and not on tempdb. You can create a different file group on database and place PVS on it. It stores changes on every row, earlier versions of that row and if needed, a pointer to the earlier version in the version store can be saved too.
- Logical Revert – this component is responsible to perform row-level version-based undo as per the data stored on PVS to rollback any process in case of rollback command or in case of system crash.
- S-Log – “Secondary logs” are in-memory logs; SQL use them to store processes that cannot be versioned; like getting locks for DDL or bulk commands. If a database has ADR enabled on it, corresponding S-Log gets reconstructed in analysis stage of recovery and it gets used in redo phase instead of trn logs; as Slogs stored in-memory it speeds up the recovery. Slog also stores minimal data e.g. info about exclusive locks etc. It is also used during the undo stage.
And then last component which is Cleaner:
- Cleaner – this process run periodically and cleans up unwanted page versions. You can also run sp_persistent_version_cleanup and manually initiate cleanup process
Now let’s see how traditional recovery works vs ADR recovery process and what’s makes ADR faster and more efficient.
Below image illustrates a traditional Database recovery process; (Ref. – MS Documents)
-vs-
Below image depicts the ADR process; (Ref. – MS Documents)
This recovery process consists of three steps:
- Traditional Analysis Stage – brings all the transactions to the same state at the time of SQL Server crash\stop, it must read transaction log from last checkpoint and scan it in forward direction till the end of log.
-vs-
ADR Analysis Stage – it follows same steps as of traditional analysis stage; but it also creates Slog and stores non-versioned operation logs on it.
- Traditional Redo Stage – in this stage SQL scans transaction log into forward direction from oldest uncommitted transaction to the end of the log and bring all the transactions into the state where they were at the time of system crash.
-vs-
ADR Redo Stage – it is a 2-part stage, in the first part, SQL scans S-log into forward direction from oldest uncommitted transaction to the end of the S-log. It is a quicker operation as S-logs holds fewer records (it only holds non-versioned operation logs). In the second part, it reapplies all the Trn logs from last checkpoint.
- Traditional Undo Stage – like the name suggests, SQL goes into reading logs in the reverse direction and rolls back all changes made by the transaction active at the time of system crash.
-vs-
ADR Undo Stage – to rollback active transactions at the time of system crash, it uses S-log and PVS which stores non-versioned and row level versions of each transactions respectively. Hence it is a much quicker process. It uses the same Undo mechanism when someone cancels long running transactions as well.
With this comparison, it’s clear that with traditional recovery the time for recovering databases is greater because it depends on the size of active transaction when the server crashed or rolling back transactions. Where with ADR, it’s not the case; it uses PVS and S-log to speed up recovery. It also controls growth of Trn logs; by truncating logs in regular intervals; ADR need not have to wait until long transaction finishes to truncate log.
Microsoft specifically recommends ADR for databases having very long-running transactions, extreme transaction log growth, or if you have databases with high recovery time when SQL Server unexpectedly restarted or someone manually initiate rollback for any long transaction.
It is a nice feature that comes with SQL 2019, but it also slows down all DML operations due to versioning operations it needs to perform. That’s the reason, by default this database option is turned off and you can enable it by carefully evaluating your environment and needs. If your looking with ADR or SQL Server support, please reach out to our experts.
The post Accelerated Database Recovery Alleviates DBAs’ Frustrations appeared first on Datavail.
Key #1 to Remote Application Development: Collaboration
According to a survey by Gartner, 88 percent of companies have encouraged or required employees to work from home since March 2020. Application development is already challenging enough in the best of times—so how can you handle it when your entire development team is suddenly working from home?
Although remote work might be new to you, it’s not new to us: our applications development teams have been remote since Datavail was founded. With more than a decade of experience, we’d like to share the three keys to successful appdev in a remote workplace. Key #1? Collaboration.
Having strong processes and tools for collaboration, including dedicated collaboration platforms, is non-negotiable when doing remote application development. The research of professor Karen Sobel-Lojeski has shown that there are three kinds of “distance” that remote teams need to overcome for effective collaboration:
- Physical distance: the barriers posed by team members’ different locations and time zones.
- Operational distance: difficulties owing to the logistics of remote work, including the number of team members and the processes, methods, and tools used to collaborate.
- Affinity distance: the challenges of building relationships, rapport, and trust between team members in different locations.
By choosing the right collaboration solution, you can overcome these distances and start enabling remote AppDev. The heart of every successful remote team is a “digital workplace” that acts as the central hub of communication and collaboration. Whether they’re working from home, in the office, or on their daily commute, digital workplaces allow your employees to share ideas and ask questions, manage files and documents, and much more.
Here at Datavail, we use technologies such as Microsoft Office 365 and SharePoint to help foster collaboration among our remote AppDev teams. Office 365 comes packed with collaboration features such as:
- Office 365 Groups, for creating ad hoc groups of users with their own shared inbox, calendar, and file repository.
- Microsoft Planner, for assigning and scheduling tasks among team members.
- Office Delve, for finding and discovering important information across the Microsoft ecosystem.
SharePoint is also an invaluable collaboration tool. Employees can create SharePoint “instances” that are restricted to members of a given team or group. Once created, these SharePoint instances serve as central repositories for file storage, instant messaging, video chat, calendars and planning, and more.
Managing remote workers is a delicate balance. Team leaders need to give people the solutions and the independence they need to thrive, while providing opportunities for collaboration and team synergy.
Want to learn more strategies for successful remote appdev? Download my white paper, The 3 Keys to Successful Remote Application Development.
The post Key #1 to Remote Application Development: Collaboration appeared first on Datavail.
Keep These Preparations in Mind for Disaster Recovery
A critical aspect of building and maintaining enterprise database systems is to incorporate disaster recovery (DR). A properly planned and set up disaster recovery goes a long way in recovering enterprise database systems from a major fault(s) and helps in keeping the business within the desired recovery point objectives.
This blog aims to discuss some things to keep in mind, while designing and testing DR solutions for large- and small-scale systems along with giving a practical example of a DR plan, implemented for a mission critical SQL Server database server.
Let’s get started!
Disaster Recovery Planning Objectives
The key areas that enterprise and systems architects need to consider are:
- The recovery point and time objectives, in the event of a disaster. This is the point in the past and the time duration within which the recovery must happen.
- The infrastructure of IT systems like database servers, application servers, key network components located across datacenters.
- The human resources required to affect a successful recovery after a disaster.
- Testing parameters to evaluate the successful recovery of the systems, post disaster.
Recovery Point Objective and Recovery Time Objective
The most important aspect that decides the DR planning and implementation process is the Recovery Point Objective (RPO). RPOs are basically the measure of the amount of data loss that a system or an organization can withstand. This translates into how frequently data is either backed up or replicated from a production system. RPOs vary between organizations and amongst the systems within an organization.
Critical systems would have tighter RPOs and can tolerate very little data loss. For example, a critical client facing application server and its backend database server, might have an RPO of 15 minutes, meaning if either or both systems fail, they can tolerate data or activity loss for up to 15 minutes only in order to maintain business continuity.
On the other hand, a data warehouse or an analytics system might have an RPO of 24 hours or up to a few days, as it may not be very critical to business continuity. Recovery Time Objective (RTO), on the other hand, dictates how soon the systems must be recovered for business continuity. This is a critical measure, in that, meeting the RTO successfully, needs proper planning, coordination and testing of recovery procedures.
Infrastructure
Infrastructure is the next key aspect in planning for and implementing a solution that meets the RPO objectives at the organization and at the individual systems level.
Infrastructure is typically the provisioning of the following:
- Servers
- Network components like switches, routers
- Rented space in a data center or dedicated data centers for large organization
- Software media and their associated licenses
- Storage media like tapes or cloud storage locations etc.
Human Resources
This is a critical, yet often overlooked aspect of the DR process. While designing and implementing a fault tolerant system is critical to a DR so are the types of support that is needed from the IT staff.
The teams below play a critical role in effecting a DR process:
- Systems Engineers
- DBAs
- Application Admins
- Network Teams
- User community
Their roles, availability and alternative resources should preferably be identified as part of the DR planning process.
Testing
A good DR plan is considered complete, if it is tested regularly and adjusted as needed. Testing also brings to light unexpected challenges that may not have been accounted for during the DR planning process, and periodically help learn if the infrastructure and method decided upon, are still valid for systems and organizations that scale up and out constantly.
Testing typically involves the following:
- Identifying a disaster when it happens, such as having an alerting system, which can function independent of the systems and detect it being down.
- Contacting the required teams either directly or via a dedicated resource, such as an on-call person/system.
- Initiate failover, or in the case of systems with automatic failover, confirm the success and stability, after a failover happens.
- Carrying out associated tasks, such as reconfiguring connections, renaming DNS etc.
- A checklist of items that can be quickly performed, usually by any of the IT staff, to verify the systems have failed over and are functional, at a minimum level, at least. Examples would be testing logins, checking the functionality of application servers for their ability to connect to backend database systems.
Case in Point: Disaster Recovery Setup for a Mission-critical SQL Server Database Server
This section describes a DR set up for a mission critical SQL Server database server, identifying the hardware, the DR methodology implemented, the connectivity loss or disaster detection method, notification methods, failover and reconfiguration of systems, and testing.
Recovery Point Objective Requirements
The RPO for this database system is 30 minutes. The goal is to bring up the system at an alternative location, within 30 minutes of the primary site’s failure. Some failover methods that were considered to help achieve this are below:
- Log Shipping – A passive method to capture transactions from the primary server’s databases, using log backups and applying them to the corresponding databases on the secondary server every 15 minutes, so both servers are in near real-time sync.
- SQL Server Failover Clustering with a distributed node in the secondary database server. FCI is primarily a High Availability (HA) solution more suitable for instantaneous failover to mitigate a hardware failure. However, if parameters such as proximity of primary and secondary data centers, network bandwidth etc., are favorable, this can also be a DR solution.
- Taking and storing backups to an off-site location to be restored on an alternative SQL Server in the event of a disaster in the primary data center.
Among the above options, the first two meet the RPO needs while the third option is time consuming albeit consumes less resources. Based on the RPO of 30 mins, Log Shipping was chosen for its simplicity, robustness, and a minimal requirement of resources.
Infrastructure
The DR solution spanned across two data centers located in two different cities. The Windows and SQL Servers were identical in version, edition, and hardware configuration. This was critical, since upon a failover, the secondary server is expected to handle the same workload.
The network bandwidth allocation was sufficient to facilitate seamless and timely copy of large transaction log back up files from the primary to the secondary server.
Human Resources
For the database server, the DBA team was primarily responsible for detecting failures in the primary server, coordinating with the other teams, to perform and validate a failover. This is done by the following steps:
- SQL Server DBAs – DBAs build and maintain the Log Shipping solution for the SQL Server. They also ensure that Log Shipping is monitored in real time and failover tasks are clearly defined. They perform the database server’s failover.
- Systems Engineers – The systems’ team set up and configure Windows Servers, provisioned shared file space for copying and restoring log file backups.
- Network Engineers – The network teams role was to set up the network infrastructure between the primary and secondary data centers. They also tested and implemented a DNS remapping and implement them in the event of a failover.
- Application Admins – The applications team designed and implemented a similar solution for the application servers spanning the primary and secondary data center. They failover the application servers and do the necessary reconfiguration after a failover.
- Users – The user groups are members of the regular user community that evaluate the application for their individual functionality, performance etc., They laid out what they consider acceptable performance of the secondary server, after a failover.
Testing and Monitoring Process
- Monitoring – The primary database server is monitored through a monitoring tool (Datavail Delta in this case). The monitoring tool checks for the databases being online, SQL Server services being online and the physical server itself remaining available for connection. If any or all of these fail, priority 1 level alerts are generated immediately to intimate the DBA team.
- Intimation to other teams – The DBA team, while checking these alerts, simultaneously contacts the on-call systems and the application team notifying them of the issue. These teams have their own monitoring as well at some level.
- Once a consensus is reached that the systems cannot be recovered in the primary data center, a decision is made to perform a failover.
- The SQL Server DBA team, failovers over the databases by recovering them on the secondary server. They also perform other documented steps such as fixing any orphaned users, setting up maintenance and backup jobs on the secondary server etc., once initial testing completes.
- The applications team does the same on the application side, if the application systems have failed too that necessitates a failover.
- The network team does a DNS swap between the primary and secondary servers, enabling applications and users to connect using the same configured server names for the SQL Server.
- The users, usually a limited number of them, test and evaluate the applications.
When all the above steps are achieved, the process of testing the DR solution is deemed a success. Any issues or delays that happen, are documented and treated as lessons learned. These are then incorporated into tuning the solution better.
Conclusion
In conclusion, a good DR plan should continue not only with a design that can meet the RPO needs, but also a robust setup, testing methodology and communication/co-ordination amongst the various IT teams and the business users. This will go a long way in mitigating chaos and/or confusion and enable business continuity in the event of a major failure. If you’re looking for support in disaster recovery planning and implementation, please reach out. Our expert DBAs can provide a solution that meets your needs in the event of a disaster.
The post Keep These Preparations in Mind for Disaster Recovery appeared first on Datavail.
What Is Cloud Analytics – And Why Should You Use It?
Analytics is the process of turning raw data into valuable business insights through quantitative and statistical methods. There are three ways of classifying business analytics methods according to their use case:
- Descriptive methods examine historical data to identify meaningful trends and patterns.
- Predictive methods use historical and current data to make forecasts and predictions about the future.
- Prescriptive methods run simulations and create models in order to hypothesize the best path forward in a given scenario.
The use of business analytics is a critical component of organizations’ digital transformation initiatives. As big data continues to grow in size and complexity year after year, organizations need to efficiently cut through the massive data volumes they have on hand to find the hidden insights within. When implemented correctly, business analytics enables smarter decision-making, helping you apply your conclusions to help solve complex business issues.
Enterprise IT has moved to the cloud in recent years, and business analytics is no exception. In one report by Gartner, 97 percent of the analytics and BI platforms studied offered a cloud version of the software. The most common platforms are AWS, Azure/Power BI, and Oracle/OACDo.
So what’s all the fuss about? There are several very good reasons that organizations migrate their analytics workloads to the cloud, including:
- Lower costs: Cloud analytics saves users from having to purchase their own hardware and provide their own support and maintenance. The switch from one-time capital expenses to monthly operating expenses is also more convenient for many companies, especially small and medium-sized businesses.
- Greater flexibility: Moving analytics to the cloud lets users do their work at the time and place that’s most convenient for them—whether in the office, at home, commuting to work, or on the road.
- Increased scalability: Cloud analytics uses a subscription-based model rather than a hardware-based model, which makes it easier to scale as your business grows: just purchase more subscriptions for more users. You can also easily ramp up your compute and storage resources during times of peak activity, which is something you can’t easily do with in-house hardware.
- Better data governance: Uniting your enterprise data in a single centralized data warehouse in the cloud helps you make better use of the data sources at your fingertips. Consolidating your data in the cloud also facilitates sharing and collaboration with the people who can most benefit from this information.
- Maintenance and disaster recovery: The cloud provider, not you, is responsible for general support and maintenance, which frees you from spending valuable time and money. Storing your data in the cloud, and backing it up in multiple locations, also protects it in the event of a disaster that damages or destroys your on-premises IT infrastructure.
But cloud analytics isn’t just advantageous in and of itself: it acts as a corrective force for the analytics delivery challenges that have been impeding your productivity and holding your business back. To learn more about cloud analytics and how it can build efficiency and flexibility into your data management strategy, download my white paper, “7 Analytics Delivery Barriers That Cloud Analytics Can Solve.”
The post What Is Cloud Analytics – And Why Should You Use It? appeared first on Datavail.
