Feed aggregator

Migrating Your Database to Oracle Cloud

Gerger Consulting - Thu, 2018-06-14 22:47
Oracle Database Cloud is increasingly becoming an attractive option to run databases. However, moving all our data to the cloud still represents an interesting problem. Attend the free webinar by Oracle ACE Director and OCM Kamran Aghayev and learn the eight ways you can migrate your databases to the Oracle Cloud. Register at this link.


About the Webinar

Are you planning to move your on-premise database to Oracle Cloud? Are you looking for the best way to achieve it?
In this session Oracle Certified Master and ACE Director Kamran Agayev will show you how you can migrate your production database to Oracle Public Cloud using various methods such as data pump, cross platform transportable tablespaces with incremental backups, Data Guard, Golden Gate etc...
During the presentation Kamran will be present step by step guides for eight different practical migration scenarios that will help you migrate your database to the Oracle Cloud easily.

Categories: Development

Real-time Sailing Yacht Performance - Kafka (Part 2)

Rittman Mead Consulting - Thu, 2018-06-14 08:18

In the last two blogs Getting Started (Part 1) and Stepping back a bit (Part 1.1) I looked at what data I could source from the boat's instrumentation and introduced some new hardware to the boat to support the analysis.

Just to recap I am looking to create the yachts Polars with a view to improving our knowledge of her abilities (whether we can use this to improve our race performance is another matter).

Polars give us a plot of the boat's speed given a true wind speed and angle. This, in turn, informs us of the optimal speed the boat could achieve at any particular angle to wind and wind speed.

Image Description

In the first blog I wrote a reader in Python that takes messages from a TCP/IP feed and writes the data to a file. The reader is able, using a hash key to validate each message (See Getting Started (Part 1)). I'm also converting valid messages into a JSON format so that I can push meaningful structured data downstream. In this blog, I'll cover the architecture and considerations around the setup of Kafka for this use case. I will not cover the installation of each component, there has been a lot written in this area. (We have some internal IP to help with configuration). I discuss the process I went through to get the data in real time displayed in a Grafana dashboard.

Introducing Kafka

I have introduced Kafka into the architecture as a next step.

Why Kafka?

I would like to be able to stream this data real time and don't want to build my own batch mechanism or create a publish/ subscribe model. With Kafka I don't need to check that messages have been successfully received and if there is a failure while consuming messages the consumers will keep track of what has been consumed. If a consumer fails it can be restarted and it will pick up where it left off (consumer offset stored in Kafka as a topic). In the future, I could scale out the platform and introduce some resilience through clustering and replication (this shouldn't be required for a while). Kafka therefore is saving me a lot of manual engineering and will support future growth (should I come into money and am able to afford more sensors for the boat).

High level architecture

Let's look at the high-level components and how they fit together. Firstly I have the instruments transmitting on wireless TCP/IP and these messages are read using my Python I wrote earlier in the year.

I have enhanced the Python I wrote to read and translate the messages and instead of writing to a file I stream the JSON messages to a topic in Kafka.

Once the messages are in Kafka I use Kafka Connect to stream the data into InfluxDB. The messages are written to topic-specific measurements (tables in InfluxdDB).

Grafana is used to display incoming messages in real time.

Kafka components

I am running the application on a MacBook Pro. Basically a single node instance with zookeeper, Kafka broker and a Kafka connect worker. This is the minimum setup with very little resilience.

Image Description

In summary

ZooKeeper is an open-source server that enables distributed coordination of configuration information. In the Kafka architecture ZooKeeper stores metadata about brokers, topics, partitions and their locations. ZooKeeper is configured in zookeeper.properties.

Kafka broker is a single Kafka server.

"The broker receives messages from producers, assigns offsets to them, and commits the messages to storage on disk. It also services consumers, responding to fetch requests for partitions and responding with the messages that have been committed to disk." 1

The broker is configured in server.properties. In this setup I have set auto.create.topics.enabled=false. Setting this to false gives me control over the environment as the name suggests it disables the auto-creation of a topic which in turn could lead to confusion.

Kafka connect worker allows us to take advantage of predefined connectors that enable the writing of messages to known external datastores from Kafka. The worker is a wrapper around a Kafka consumer. A consumer is able to read messages from a topic partition using offsets. Offsets keep track of what has been read by a particular consumer or consumer group. (Kafka connect workers can also write to Kafka from datastores but I am not using this functionality in this instance). The connect worker is configured in connect-distributed-properties. I have defined the location of the plugins in this configuration file. Connector definitions are used to determine how to write to an external data source.

Producer to InfluxDB

I use kafka-python to stream the messages into kafka. Within kafka-python there is a KafkaProducer that is intended to work in a similar way to the official java client.

I have created a producer for each message type (parameterised code). Although each producer reads the entire stream from the TCP/IP port it only processes it's assigned message type (wind or speed) this increasing parallelism and therefore throughput.

  producer = KafkaProducer(bootstrap_servers='localhost:9092' , value_serializer=lambda v: json.dumps(v).encode('utf-8'))
  producer.send(topic, json_str) 

I have created a topic per message type with a single partition. Using a single partition per topic guarantees I will consume messages in the order they arrive. There are other ways to increase the number of partitions and still maintain the read order but for this use case a topic per message type seemed to make sense. I basically have optimised throughput (well enough for the number of messages I am trying to process).

kafka-topics --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic wind-json

kafka-topics --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic speed-json

kafka-topics --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic gps-json 

When defining a topic you specify the replaication-factor and the number of partitions.

The topic-level configuration is replication.factor. At the broker level, you control the default.replication.factor for automatically created topics. 1 (I have turned off the automatic creation of topics).

The messages are consumed using Stream reactor which has an InfluxDB sink mechanism and writes directly to the measurements within a performance database I have created. The following parameters showing the topics and inset mechanism are configured in performance.influxdb-sink.properties.

topics=wind-json,speed-json,gps-json

connect.influx.kcql=INSERT INTO wind SELECT * FROM wind-json WITHTIMESTAMP sys_time();INSERT INTO speed SELECT * FROM speed-json WITHTIMESTAMP sys_time();INSERT INTO gps SELECT * FROM gps-json WITHTIMESTAMP sys_time()

The following diagram shows the detail from producer to InfluxDB.

If we now run the producers we get data streaming through the platform.

Producer Python log showing JSON formatted messages:

Status of consumers show minor lag reading from two topics, the describe also shows the current offsets for each consumer task and partitions being consumed (if we had a cluster it would show multiple hosts):

Inspecting the InfluxDB measurements:

When inserting into a measurement in InfluxDB if the measurement does not exist it gets created automatically. The datatypes of the fields are determined from the JSON object being inserted. I needed to adjust the creation of the JSON message to cast the values to floats otherwise I ended up with the wrong types. This caused reporting issues in Grafana. This would be a good case for using Avro and Schema Registry to handle these definitions.

The following gif shows Grafana displaying some of the wind and speed measurements using a D3 Gauge plugin with the producers running to the right of the dials.

Next Steps

I'm now ready to do some real-life testing on our next sailing passage.

In the next blog, I will look at making the setup more resilient to failure and how to monitor and automatically recover from some of these failures. I will also introduce the WorldMap pannel to Grafana so I can plot the location the readings were taken and overlay tidal data.

References
Categories: BI & Warehousing

Is adding a column to a typed table in PostgreSQL instant?

Yann Neuhaus - Thu, 2018-06-14 06:26

Today at the SOUG Day I did some little demos and one of them was about creating typed tables. In the demo the two tables did not contain any rows and one of the questions was: When these tables contain a million of rows would adding a column be instant as well? Lets do a quick test.

Same setup as in the post referenced above: Two schemas, one type, two tables based on the type:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create type typ1 as ( a int, b text );
CREATE TYPE
postgres=# create table a.t1 of typ1;
CREATE TABLE
postgres=# create table b.t1 of typ1;
CREATE TABLE
postgres=# insert into a.t1
postgres-# select a.*
postgres-#      , md5(a::text)
postgres-#   from generate_series(1,1000000) a;
INSERT 0 1000000
postgres=# insert into b.t1 select * from a.t1;
INSERT 0 1000000
postgres=# 

Both of the tables contain 1’000’000 rows so how much time would a modification of the type take?

postgres=# \timing
Timing is on.
postgres=# alter type typ1 add attribute c timestamp cascade;;
ALTER TYPE
Time: 9.338 ms
Time: 0.867 ms
postgres=# \d a.t1
                             Table "a.t1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 a      | integer                     |           |          | 
 b      | text                        |           |          | 
 c      | timestamp without time zone |           |          | 
Typed table of type: typ1

postgres=# \d b.t1
                             Table "b.t1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 a      | integer                     |           |          | 
 b      | text                        |           |          | 
 c      | timestamp without time zone |           |          | 
Typed table of type: typ1

Almost instant. Btw: Of course you can also remove an attribute from the type:

postgres=# alter type typ1 drop attribute c cascade;
ALTER TYPE
Time: 14.417 ms
postgres=# \d a.t1
                   Table "a.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Typed table of type: typ1

postgres=# \d b.t1
                   Table "b.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Typed table of type: typ1
 

Cet article Is adding a column to a typed table in PostgreSQL instant? est apparu en premier sur Blog dbi services.

Sizing clusters

Tom Kyte - Wed, 2018-06-13 22:46
My question is generally how to determine the size to set the <u>size</u> value in a create cluster statement. And specifically for a parent table 400 bytes wide with 15 million rows, and a child 120 bytes wide with 40 million rows. There may be a...
Categories: DBA Blogs

SQL with inline view Errors in 11g with ORA-00979 Not a Group BY expression, but runs in 12c

Tom Kyte - Wed, 2018-06-13 22:46
Hi Following SQL (correctly) errors with "ORA-00979 Not a Group BY Expression" when run on 11.2.0.4.0. But when run on 12.1.0.2.0, SQL runs without error and returns incorrect information for total_tablespace_size - All rows return 0.5 whereas i...
Categories: DBA Blogs

Leaving fake hints in queries

Tom Kyte - Wed, 2018-06-13 22:46
Hello, I'm creating a script to automatically generate plan reports usings DBMS_XPLAN.DISPLAY_CURSOR, and to do so I want to put in a standard comment in the table and query it via dba_source and v$sql. e.g. <code>select /* xplan_my_test_pkg01 ...
Categories: DBA Blogs

Oracle Can Generate 6 Password Hashes When a User is Added or Password Changed in 12.1.0.2 and Above

Pete Finnigan - Wed, 2018-06-13 22:46
In a 12.2.0.2 database it's possible that Oracle generates 6 different password hashes for one password for one user under certain circumstances when a password is changed or created (user is created). I will layout the 6 different ones first....[Read More]

Posted by Pete On 13/06/18 At 09:02 PM

Categories: Security Blogs

Oracle Launches Internet Intelligence Map Providing a Unique View into the Global Internet

Oracle Press Releases - Wed, 2018-06-13 11:25
Press Release
Oracle Launches Internet Intelligence Map Providing a Unique View into the Global Internet Free dashboard delivers insight into the impact of internet disruptions

VELOCITY, San Jose, Calif.—Jun 13, 2018

Oracle (NYSE:ORCL) today announced availability of the Internet Intelligence Map, providing users with a simple, graphical way to track the health of the internet and gain insight into the impact of events such as natural disasters or state-imposed interruptions. The map is part of Oracle’s Internet Intelligence initiative, which provides insight and analysis on the state of global internet infrastructure. To access the free Internet Intelligence Map, visit internetintel.oracle.com.

For more than a decade, members of Oracle’s Internet Intelligence team have broken some of the biggest stories about the internet. From BGP hijacks to submarine cable breaks, Oracle’s Internet Intelligence team frequently publishes objective data and analysis that informs public understanding of the technical underpinnings of the internet and its effects on topics like geopolitics and e-commerce. With today’s news, Oracle is now making core analytic capabilities available to everyone via the Internet Intelligence Map. Using one of the world’s most comprehensive internet performance data sets and backed by years of research and analytics, Oracle has developed the premier resource and authority for reliable information on the functioning of the internet.

“The internet is the world’s most important network, yet it is incredibly volatile. Disruptions on the internet can affect companies, governments, and network operators in profound ways,” said Kyle York, vice president of product strategy for Oracle Cloud Infrastructure and the general manager for Oracle’s Dyn Global Business Unit. “As a result, all of these stakeholders need better visibility into the health of the global internet. With this offering, we are delivering on our commitment to making it a better, more stable experience for all who rely on it.”

The Internet Intelligence Map presents country-level connectivity statistics based on traceroutes, BGP, and DNS query volumes on a single dashboard. By presenting these three dimensions of internet connectivity side-by-side, users can investigate the impact of an issue on internet connectivity worldwide.

“It’s important to have a global view of the internet in order to understand how external events prevent users from reaching your web-based applications and services. It is only when you have this insight that you can work around those issues to improve availability and performance,” said Jim Davis, Founder and Principal Analyst of Edge Research Group.

The Internet Intelligence Map is just one of many advanced awareness and visibility tools that help Oracle improve the experience of the cloud by making it better and more reliable every day. This offering is powered by Oracle Cloud Infrastructure, which offers a set of core infrastructure services to provide customers the ability to run any workload in the cloud. Only Oracle Cloud Infrastructure provides the compute, storage, networking, and edge services necessary to deliver the end-to-end performance required of today's modern enterprise.

Contact Info
Danielle Tarp
Oracle
+1.408.921.7063
danielle.tarp@oracle.com
Nicole Maloney
Oracle
+1.650.506.0806
nicole.maloney@oracle.com
About Oracle Cloud Infrastructure

Oracle Cloud Infrastructure combines the benefits of public cloud (on-demand, self-service, scalability, pay-for-use) with those benefits associated with on-premises environments (governance, predictability, control) into a single offering. Oracle Cloud Infrastructure takes advantage of a high-scale, high-bandwidth network that connects cloud servers to high-performance local, block, and object storage to deliver a cloud platform that yields the highest performance for traditional and distributed applications, as well as highly available databases. With the acquisitions of Dyn and Zenedge, Oracle Cloud Infrastructure extended its offering to include Dyn’s best-in-class DNS and email delivery solutions and Zenedge’s next-generation Web Application Firewall (WAF) and Distributed Denial of Service (DDoS) capabilities.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Danielle Tarp

  • +1.408.921.7063

Nicole Maloney

  • +1.650.506.0806

ORA-01704: string literal too long

Flavio Casetta - Wed, 2018-06-13 08:31
Categories: DBA Blogs

New Oracle Autonomous Cloud Services Ease Mobile Development, Data Integration

Oracle Press Releases - Wed, 2018-06-13 07:00
Press Release
New Oracle Autonomous Cloud Services Ease Mobile Development, Data Integration AI-based PaaS services cut costs and speed development of chatbots, data integration, and API management

Redwood Shores, Calif.—Jun 13, 2018

Oracle (NYSE: ORCL) today announced the availability of its next-generation Oracle Cloud Platform services featuring built-in autonomous capabilities, including Oracle Mobile Cloud Enterprise, Oracle Data Integration Platform Cloud, and Oracle API Platform Cloud. With embedded artificial intelligence (AI) and machine learning, these platform services automate operational tasks to enable organizations to lower cost, reduce risk, accelerate innovation, and get predictive insights.

To continuously innovate while keeping pace with dynamic business environments, enterprises need secure, comprehensive, integrated cloud services to build new applications and run their most demanding workloads. Only Oracle’s cloud services can intelligently automate key operational functions, including tuning, patching, backups, and upgrades giving organizations more time to focus on strategic business activities, while delivering maximum performance, high availability, and critical security features.

“There is tremendous value for our customers in embedding AI and machine learning capabilities throughout our entire cloud portfolio,” said Amit Zavery, executive vice president of development, Oracle Cloud Platform. “Customers have embraced our vision for an autonomous enterprise. With Oracle’s autonomous platform services, organizations can capitalize on AI to reduce costs, speed innovation, and transform how they do business.”

Last year, Oracle outlined its vision for an autonomous enterprise, unveiling the world’s first Autonomous Database. Since then, the company announced it would extend autonomous capabilities to its entire cloud platform portfolio. Delivering on that promise, Oracle recently made available a number of autonomous platform services, including Oracle Autonomous Data Warehouse Cloud, Oracle Analytics Cloud, Oracle Integration Cloud, and Oracle Visual Builder. With today’s availability of another set of autonomous platform services, Oracle continues to build momentum. Later in 2018, Oracle plans to release more autonomous capabilities focused on Blockchain, security and management, and additional database workloads, including OLTP and NoSQL.

Mutua Madrid Open Develops MatchBot with Oracle Cloud Platform

Mutua Madrid Open became the first ATP World Tour Masters 1000 and Premier WTA tournament to incorporate an AI-equipped chatbot to improve communication with tennis fans. Implemented with Oracle Cloud Platform, the chatbot, named “MatchBot,” used AI to maintain natural conversations that provided fans with information on the event, players, and results, as well as details on hospitality services, discounts on merchandise, ticket sales, access, and parking.

“We wanted to position the Mutua Madrid Open as the tournament of the 21st century,” said Gerard Tsobanian, president and CEO of Mutua Madrid Open. “Development of the MatchBot positions us at the forefront of technology and innovation. With this new technology, we were able to provide visitors with an amazing experience—a pleasant, simpler, and faster way to get the information they wanted about the tournament.”

New Oracle Cloud Platform Services Featuring Built-in AI

By adding autonomous capabilities to its latest set of Platform Cloud services, Oracle helps organizations easily develop new innovative user experiences with chatbot and voice capabilities, enables business users to perform intelligent data integration tasks, and exposes business logic and data via robust API design/management.

Oracle Mobile Cloud Enterprise

Oracle Mobile Cloud Enterprise provides a complete, open, and proven enterprise platform to develop, deliver, analyze, and manage mobile apps and AI-powered chatbots that connect and extend any backend system in a secure, scalable manner. Learn more here.

  • Self-learning chatbots observe interactions, preferences to automate frequently performed actions.
  • Automated learning from conversations ensures higher accuracy in the machine learning for the smart bots, including seamless handoffs to human agents via trained AI algorithms.

     

  • Automatic generation of QnA chatbots extract knowledge from unstructured data by leveraging machine learning.
  Oracle Data Integration Platform Cloud

Oracle Data Integration Platform Cloud helps organizations make better and faster decisions by providing access to all data, and unlocking value from data faster through a combination of machine learning and artificial intelligence powered features that stream, migrate, transform, enrich, and govern data from anywhere. Learn more here.

  • Simplifies and automates creation of big data lakes and data warehouses through one click self-defining tasks to deliver streaming or batch data, thereby improving standardization and efficiency for big data projects.
  • Delivers self-optimizing data pipelines for rapid data delivery into Oracle Autonomous Data Warehouse Cloud.
  • Enables trusted, governed cloud-based analytics through system-guided data sharing between SaaS, on-premises, and hybrid business applications.
  • Speeds up self-service data preparation through machine assisted data curation and eliminates manual work when creating data pipelines.
  Oracle API Platform Cloud

Oracle API Platform Cloud supports agile API-first design and development, enabling hybrid deployment of the API gateway across Oracle Cloud, on-prem and 3rd party clouds.  It provides insight on KPIs covering every aspect of the API lifecycle, while employing the most up-to-date security protocols. Learn more here.

  • Continuously learns about usage patterns, and recommends plans allocation limits and configurations.
  • Based on other policy usage and configurations, recommends policies and policy configurations using predictive algorithms to API Managers.
  Oracle Developer Cloud

Oracle Developer Cloud is a complete development platform included with all Oracle Cloud services that automates software development and delivery cycles, and helps teams manage agile development processes. With an easy-to-use web interface and integration with popular development tools, it helps deliver better applications faster. Learn more here

  • Builds automation for multiple development languages and environments supporting a variety of popular build frameworks such as Maven, Ant, Gradle, npm, Grunt, Gulp, and Bower.
  • Tests automation with popular testing frameworks such as JUnit and Selenium enables automation of both logic and UI testing.
  • Environment provisioning automation via command line interfaces for Docker, Kubernetes, and Terraform.
  • Continuous integration automation through visual pipeline flows. Monitor live execution of pipelines and build job execution history from a central location.
Contact Info
Nicole Maloney
Oracle
+1.650.506.0806
nicole.maloney@oracle.com
Kristin Reeves
Blanc and Otus
+1.925.787.6744
kristin.reeves@blancandotus.com
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Nicole Maloney

  • +1.650.506.0806

Kristin Reeves

  • +1.925.787.6744

Active clone recover needed arc files

Tom Kyte - Wed, 2018-06-13 04:30
Oracle 12.2 I am running the following command: *.db_file_name_convert='/db1/ifddb1/dbf/','/db1/ifdtest1/dbf/' *.log_file_name_convert='/redo/ifddb1/redologs/','/redo/ifdtest1/redologs/' sqlplus ' / as sysdba ' <<EOT shutdown abort startu...
Categories: DBA Blogs

Use DBMS_OUTPUT or HTP depending call origin

Tom Kyte - Wed, 2018-06-13 04:30
Hey, I have this procedure: <code>procedure output(i_msg in varchar2) is begin dbms_output.put_line(i_msg); -- if call is from apex i want to use htp.p end;</code> Is it possible to switch the "output chanel" to htp when the caller is...
Categories: DBA Blogs

Creating dll for executing external procedure('c' language)

Tom Kyte - Wed, 2018-06-13 04:30
Hi Tom, I am using Wint NT,Oracle 8i(server) and C language. My goal is calling 'c' routine thru stored procedure. For that I had made neccesary steps.I had modified Tnsnames and listener entry as follows. Tnsnames entry...
Categories: DBA Blogs

Avoid duplicates

Tom Kyte - Wed, 2018-06-13 04:30
Hi Tom, Thanks for your time. We have the scenario like this... DML on the table t should not populate the table with duplicate entries. So if we have table with data in it as : create table t ( x number); insert into t values (1);...
Categories: DBA Blogs

Introducing SQL managed instances on Azure

Yann Neuhaus - Wed, 2018-06-13 00:43

I never wrote about data platform solutions on Azure so far. The fact is in Switzerland we’re definitely late about the Cloud adoption and Azure data platform solutions. There are different reasons that are more or less valid but I don’t want to contribute to any debate here. In any case the last announcements in this field with Azure data centers in Switzerland could encourage customers to reconsider this topic in the near future. Don’t get me wrong here, it doesn’t mean that customers in Switzerland must move all their database infrastructures in the Cloud but this is just an opportunity for them to consider rearchitecting some pieces of their information system including databases that may lead to hybrid scenarios. It will be likely the first step to the cloud adoption for data platforms solutions. At dbi services we didn’t exclude Azure from our target but we just silently continued to keep an eye on the data platform stack awaiting the right opportunity to move up a gear.

blog 138 - 0 - banner

Why to begin with SQL managed instances (MI)? After all, this feature is still on preview and it already exists a lot of Azure solutions as Azure SQL Databases (singleton and with elastic pool as well) as well as SQL Server on Azure VMs.

The point is this new feature is interesting in many aspects. Firstly, it will address the gap that currently exists between IaaS infrastructures (SQL Server VMs) and fully-managed services (with Azure SQL DBs). The former still requires maintaining the operating system (and licenses) while the latter didn’t expose all the feature surface needed by various application scenarios.

At the same time, Microsoft introduced another purchasing model that based on VCore. I remembered a discussion with one of my customers sometimes ago about DTUs. He asked me what is exactly DTU and I pointed out the following sentence from the Microsoft documentation.

The amount of resources is calculated as a number of Database Transaction Units or DTUs and is a bundled measure of compute, storage, and IO resources

That is definitely a good way to simply resource management because it makes an abstraction of the physical resources but this is probably it weakness in some degrees. Indeed, how to translate what DBAs and infrastructure team usually manage from on-premises to the cloud? Obviously, Microsoft provided a calculator to help customers to address their questions before moving to the cloud but the fact is database administrators seem to not be comfortable to deal with DTUs . But now let’s talk about flexibility: in many databases scenarios we don’t want to increase / decrease resources in the same bundle but we want to get a better control of the resource configuration by dissociating compute (CPU / RAM) from the storage. From my experience, I had never seen one customer to scale compute and storage in the same manner regarding their workload. Indeed, some workloads require high-performance storage while others are more CPU-bound. This is where the new vCore-based model comes into play by and I believe it will get a better adoption from customers to smoothly move to the cloud. That’s at least my opinion!

So, let’s try to play with MI. As a reminder, currently it is in preview but that’s enough to get a picture of what you may expect in the future. In my demo, I will use intensively CLI tools with dedicated PowerShell cmdlets and mssql-cli as well. This is voluntary because the fact is more and more administration tasks are done in this way and Microsoft provided all the commands to achieve them.

[dab@DBI-LT-DAB:#]> Get-AzureRmResourceGroup -Name sql-mi-rg


ResourceGroupName : sql-mi-rg
Location          : westeurope
ProvisioningState : Succeeded
Tags              :
ResourceId        : /subscriptions/913528f5-f1f8-4d61-af86-30f2eb0839ba/resourceGroups/sql-mi-rg

[dab@DBI-LT-DAB:#]> Get-AzureRmResource -ResourceGroupName sql-mi-rg | ft Name, ResourceType, Location -AutoSize

Name                                                    ResourceType                             Location
----                                                    ------------                             --------
sql-mi-client_OsDisk_1_842d669310b04cbd8352962c4bda5889 Microsoft.Compute/disks                  westeurope
sql-mi-client                                           Microsoft.Compute/virtualMachines        westeurope
shutdown-computevm-sql-mi-client                        Microsoft.DevTestLab/schedules           westeurope
sql-mi-client453                                        Microsoft.Network/networkInterfaces      westeurope
sql-mi-client-nsg                                       Microsoft.Network/networkSecurityGroups  westeurope
sqlmiclientnsg675                                       Microsoft.Network/networkSecurityGroups  westeurope
sql-mi-client-ip                                        Microsoft.Network/publicIPAddresses      westeurope
sqlmiclientip853                                        Microsoft.Network/publicIPAddresses      westeurope
sql-mi-routetable                                       Microsoft.Network/routeTables            westeurope
sql-mi-vnet                                             Microsoft.Network/virtualNetworks        westeurope
sql-mi-dbi                                              Microsoft.Sql/managedInstances           westeurope
sql-mi-dbi/ApplixEnterprise                             Microsoft.Sql/managedInstances/databases westeurope
sql-mi-dbi/dbi_tools                                    Microsoft.Sql/managedInstances/databases westeurope
VirtualClustersql-mi-subnet                             Microsoft.Sql/virtualClusters            westeurope
sqlmirgdiag947                                          Microsoft.Storage/storageAccounts        westeurope

My MI is composed of difference resources including:

  • VirtualClustersql-mi-subnet – a logical container of managed instances?
  • sql-mi-dbi as managed instance
  • sql-mi-dbi/ApplixEnterprise and sql-mi-dbi/dbi_tools as managed databases.
  • Network components including sql-mi-vnet, sql-mi-routetable

Here some more details of my MI:

[dab@DBI-LT-DAB:#]> Get-AzureRmSqlManagedInstance | ft ManagedInstanceName, Location, ResourceGroupName, LicenseType, VCores, StorageSizeInGB -AutoSize

ManagedInstanceName Location   ResourceGroupName LicenseType     VCores StorageSizeInGB
------------------- --------   ----------------- -----------     ------ ---------------
sql-mi-dbi          westeurope sql-mi-rg         LicenseIncluded      8              32

 

I picked up a GEN4 configuration based on General Purpose pricing that includes 8 VCores and 32GB of storage.

My managed databases are as follows:

[dab@DBI-LT-DAB:#]> Get-AzureRmSqlManagedDatabase -ManagedInstanceName sql-mi-dbi -ResourceGroupName sql-mi-rg | ft Name, ManagedInstanceName, Location, DefaultSecondaryLoc
ation, Status, Collation  -AutoSize

Name             ManagedInstanceName Location   DefaultSecondaryLocation Status Collation
----             ------------------- --------   ------------------------ ------ ---------
dbi_tools        sql-mi-dbi          westeurope northeurope              Online Latin1_General_CS_AS_KS
ApplixEnterprise sql-mi-dbi          westeurope northeurope              Online SQL_Latin1_General_CP1_CI_AS

 

Other resources are related to my other virtual client machine to connect to my MI. Indeed, the latter is not exposed through a public endpoint and it is reachable only from an internal network. I didn’t setup a site-to-site VPN to connect the MI from my remote laptop.

Another point that drew my attention is the high availability feature which is based on remote storage and Azure Service Fabric.

blog 138 - 5 - azure - sql managed instances - HA

Do you remember of the VirtualClustersql-mi-subnet described earlier? In fact, my MI is built upon a service fabric. Referring to the Microsoft documentation a Service Fabric enables you to build and manage scalable and reliable applications composed of microservices that run at high density on a shared pool of machines, which is referred to as a cluster.

We may get a picture of this underlying cluster from a set of dedicated sys.dm_hadr_fabric_* DMVs with a high-level view of the underlying cluster …

blog 138 - 6 - azure - sql managed instances - DMVs

… and a more detailed view including my managed databases:

blog 138 - 7 - azure - sql managed instances - DMVs detail

Now let’s get basic information from my MI:

blog 138 - 1 - azure - sql managed instances - engine version

The MI version may be easily identified by the engine_sql number equal to 8.

As said previously I have two user databases that exist and they were restored from backups hosted on my blob storage container.

Here an example of commands I used to restore them. You probably recognize the native RESTORE FROM URL syntax. Note also that you have different ways to restore / migrate your databases from on-premises environment with BACPAC and Azure Database Migration Service as well.

RESTORE FILELISTONLY 
FROM URL = 'https://mikedavemstorage.blob.core.windows.net/backup/ApplixEnterprise2014.bak'

RESTORE DATABASE [ApplixEnterprise] 
FROM URL = 'https://mikedavemstorage.blob.core.windows.net/backup/ApplixEnterprise2014.bak';

 

Here a list of my existing user databases:

blog 138 - 2 - azure - sql managed instances - databases

Let’s go further with database files configuration:

SELECT 
	DB_NAME(database_id) AS [db_name],
	file_id,
	type_desc,
	name AS [logical_name],
	physical_name,
	state_desc AS [state],
	size / 128 AS size_MB,
	max_size
FROM
	sys.master_files;
GO

 

blog 138 - 3 - azure - sql managed instances - database files

 

Some interesting points here:

1 – tempdb is pre-configured with 12 data files and 16MB each? Probably far from our usualk recommendation but anyway the preview allows to change it by using DBA usual scripts.

2- We may also notice that the user databases are placed on a different storage types (premium disk according to the Microsoft documentation). System databases are hosted to a local path C:\WFRoot\DB 3\Fabric\work\data\ as well as well the tempdb database. I use a standard tier meaning that system DBs are all on an attached SSD included in the VCore price.

Just for fun, I tried to install our DMK maintenance tool which basically creates a dbi_tools database with maintenance objects (tables and stored procedures) and related SQL Server agent jobs. A databasemail configuration step is also part of the DMK installation and the good news is the feature is available with MIs. However, I quickly ran into was about some ALTER DATABASE commands we use at the beginning of the deployment script:

Msg 5008, Level 16, State 14, Line 72
This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.
Msg 5069, Level 16, State 1, Line 72
ALTER DATABASE statement failed.
Msg 5008, Level 16, State 14, Line 89
This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.
Msg 5069, Level 16, State 1, Line 89
ALTER DATABASE statement failed.

 

The fix was quite easy and I finally managed to deploy the tool as show below:

blog 138 - 4 - azure - sql managed instances - dbi tools

The next step consisted in testing our different maintenance tasks:

  • Database integrity check task
  • Index maintenance task
  • Update statistics maintenance task
  • Backup task

The first 3 tasks worked well without any modification. However, for backups, I needed to used URL-based backups because it is the only method supported so far. Unfortunately, the current version of our DMK maintenance tool doesn’t not handled correctly it does shared access signatures that come with BACKUP TO URL command since SQL Server 2016. The fix will be included soon to the next release for sure :). For the context of my test I modified a little bit the statement generated by the maintenance objects and it worked perfectly:

-- Backup database dbi_tools
BACKUP DATABASE  [dbi_tools] 
TO URL = 'https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK'
WITH COPY_ONLY, CHECKSUM, INIT, FORMAT;

--Verification of the backup https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK
RESTORE VERIFYONLY FROM URL = 'https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK'
WITH STATS = 100, CHECKSUM;

 

And to finish this blog post as a good DBA, let’s have a look at the resource allocation management. First time I took a look at the resources available on the MI I was very surprised. To get an idea, let’s run some DMVs as sys.dm_os_schedulers, sys.dm_os_sys_memory or sys.dm_os_sys_info DMVs to get a real picture of these aforementioned resources:

blog 138 - 8 - azure - sql managed instances - resources

Given the number of visible online schedulers only 8 may be used by the MI. This is an expected outcome according to my initial configuration. Concerning the memory configuration, the theorical amount of memory available I can get should be 8 x 7GB = 56GB according the Microsoft documentation and the sys.dm_os_sys_memory DMV doesn’t really indicate such capping while the sys.dm_os_sys_info DMV does (at least closer to the reality)

blog 138 - 10 - azure - sql managed instances - memory capping

Are the CPU and memory resources managed differently on MI? I found out the right answer in this article from the SQL Server customer advisor team. For MI, this mechanism that is responsible of resource management is called Job Objects. That’s very interesting! Without going into details, this is exactly the same mechanism used by Docker on Windows and it is similar (at least in the concept) to existing CGroups on Linux.

Therefore, we may also benefit from another DMV to get details of resource management:

blog 138 - 11 - azure - sql managed instances - job object dmv

Thanks to this SQL Server customer advisor team article, the situation becomes clearer with the following parameter values:

  • cpu_rate 100% indicates my vCores are used at 100% of their capacity
  • cpu_affinity_mask indicates we are limited to 8 OS level processors
  • process_memory_limit_mb is self-explanatory and corresponds to my previous theorical assumptions :)
  • non_sos_mem_gap_mb corresponds to a safe amount of available memory for non-SQLOS activity

 

Conclusion

I think Microsoft is doing a great strategic play by introducing this solution for customers. Indeed, change is always a challenge and moving towards something almost similar to what we already know allows a smooth transition and a better adoption from humans. We will see what happens!

 

Cet article Introducing SQL managed instances on Azure est apparu en premier sur Blog dbi services.

6 Easy Steps to Get You Started on Wireframing

Nilesh Jethwa - Tue, 2018-06-12 23:08

A wireframe is a blueprint of sorts that serves as the initial design of a website. In web development, the main purpose of wireframing is to provide an optimal and efficient design for a website without being hindered or affected … Continue reading ?

Origin: MockupTiger Wireframes

OAC - Thoughts on Moving to the Cloud

Rittman Mead Consulting - Tue, 2018-06-12 12:43

Last week, I spent a couple of days with Oracle at Thames Valley Park and this presented me with a perfect opportunity to sit down and get to grips with the full extent of the Oracle Analytics Cloud (OAC) suite...without having to worry about client requirements or project deadlines!

As a company, Rittman Mead already has solid experience of OAC, but my personal exposure has been limited to presentations, product demonstrations, reading the various postings in the blog community and my existing experiences of Data Visualisation and BI cloud services (DVCS and BICS respectively). You’ll find Francesco’s post a good starting place if you need an overview of OAC and how it differs (or aligns) to Data Visualisation and BI Cloud Services.

So, having spent some time looking at the overall suite and, more importantly, trying to interpret what it could mean for organisations thinking about making a move to the cloud, here are my top three takeaways:

Clouds Come In Different Shapes and Flavours

Two of the main benefits that a move to the cloud offers are simplification in platform provisioning and an increase in flexibility, being able to ramp up or scale down resources at will. These both comes with a potential cost benefit, depending on your given scenario and requirement. The first step is understanding the different options in the OAC licensing and feature matrix.

First, we need to draw a distinction between Analytics Cloud and the Autonomous Analytics Cloud (interestingly, both options point to the same page on cloud.oracle.com, which makes things immediately confusing!). In a nutshell though, the distinction comes down to who takes responsibility for the service management: Autonomous Analytics Cloud is managed by Oracle, whilst Analytics Cloud is managed by yourself. It’s interesting to note that the Autonomous offering is marginally cheaper.

Next, Oracle have chosen to extend their BYOL (Bring Your Own License) option from their IaaS services to now incorporate PaaS services. This means that if you have existing licenses for the on-premise software, then you are able to take advantage of what appears to be a significantly discounted cost. Clearly, this is targeted to incentivise existing Oracle customers to make the leap into the Cloud, and should be considered against your ongoing annual support fees.

Since the start of the year, Analytics Cloud now comes in three different versions, with the Standard and Enterprise editions now being separated by the new Data Lake edition. The important things to note are that (possibly confusingly) Essbase is now incorporated into the Data Lake edition of the Autonomous Analytics Cloud and that for the full enterprise capability you have with OBIEE, you will need the Enterprise edition. Each version inherits the functionality of its preceding version: Enterprise edition gives you everything in the Data Lake edition; Data Lake edition incorporates everything in the Standard edition.

alt

Finally, it’s worth noting that OAC aligns to the Universal Credit consumption model, whereby the cost is determined based on the size and shape of the cloud that you need. Services can be purchased as Pay as You Go or Monthly Flex options (with differential costing to match). The PAYG model is based on hourly consumption and is paid for in arrears, making it the obvious choice for short term prototyping or POC activities. Conversely, the Monthly Flex model is paid in advance and requires a minimum 12 month investment and therefore makes sense for full scale implementations. Then, the final piece of the jigsaw comes with the shape of the service you consume. This is measured in OCPU’s (Oracle Compute Units) and the larger your memory requirements, the more OCPU’s you consume.

Where You Put Your Data Will Always Matter

Moving your analytics platform into the cloud may make a lot of sense and could therefore be a relatively simple decision to make. However, the question of where your data resides is a more challenging subject, given the sensitivities and increasing legislative constraints that exist around where your data can or should be stored. The answer to that question will influence the performance and data latency you can expect from your analytics platform.

OAC is architected to be flexible when it comes to its data sources and consequently the options available for data access are pretty broad. At a high level, your choices are similar to those you would have when implementing on-premise, namely:

  • perform ELT processing to transform and move the data (into the cloud);
  • replicate data from source to target (in the cloud) or;
  • query data sources via direct access.

These are supplemented by a fourth option to use the inbuilt Data Connectors available in OAC to connect to cloud or on-premise databases, other proprietary platforms or any other source accessible via JDBC. This is probably a decent path for exploratory data usage within DV, but I’m not sure it would always make the best long term option.

alt

Unsurprisingly, with the breadth of options comes a spectrum of tooling that can be used for shifting your data around and it is important to note that depending on your approach, additional cloud services may or may not be required.

For accessing data directly at its source, the preferred route seems to be to use RDC (Remote Data Connector), although it is worth noting that support is limited to Oracle (including OLAP), SQL Server, Teredata or DB2 databases. Also, be aware that RDC operates within WebLogic Server and so this will be needed within the on-premise network.

Data replication is typically achieved using Data Sync (the reincarnation of the DAC, which OBIA implementers will already be familiar with), although it is worth mentioning that there are other routes that could be taken, such as APEX or SQL Developer, depending on the data volumes and latency you have to play with.

Classic ELT processing can be achieved via Oracle Data Integrator (either the Cloud Service, a traditional on-premise implementation or a hybrid-model).

Ultimately, due care and attention needs to be taken when deciding on your data architecture as this will have a fundamental effect on the simplicity with which data can be accessed and interpreted, the query performance achieved and the data latency built into your analytics.

Data Flows Make For Modern Analytics Simplification

A while back, I wrote a post titled Enabling a Modern Analytics Platform in which I attempted to describe ways that Mode 1 (departmental) and Mode 2 (enterprise) analytics could be built out to support each other, as opposed to undermining one another. One of the key messages I made was the importance of having an effective mechanism for transitioning your Mode 1 outputs back into Mode 2 as seamlessly as possible. (The same is true in reverse for making enterprise data available as an Mode 1 input.)

One of the great things about OAC is how it serves to simplify this transition. Users are able to create analytic content based on data sourced from a broad range of locations: at the simplest level, Data Sets can be built from flat files or via one of the available Data Connectors to relational, NoSQL, proprietary database or Essbase sources. Moreover, enterprise curated metadata (via RPD lift-and-shift from an on-premise implementation) or analyst developed Subject Areas can be exposed. These sources can be ‘mashed’ together directly in a DV project or, for more complex or repeatable actions, Data Flows can be created to build Data Sets. Data Flows are pretty powerful, not only allowing users to join disparate data but also perform some useful data preparation activities, ranging from basic filtering, aggregation and data manipulation actions to more complex sentiment analysis, forecasting and even some machine learning modelling features. Importantly, Data Flows can be set to output their results to disk, either written to a Data Set or even to a database table and they can be scheduled for repetitive refresh.

For me, one of the most important things about the Data Flows feature is that it provides a clear and understandable interface which shows the sequencing of each of the data preparation stages, providing valuable information for any subsequent reverse engineering of the processing back into the enterprise data architecture.

alt

In summary, there are plenty of exciting and innovative things happening with Oracle Analytics in the cloud and as time marches on, the case for moving to the cloud in one shape or form will probably get more and more compelling. However, beyond a strategic decision to ‘Go Cloud’, there are many options and complexities that need to be addressed in order to make a successful start to your journey - some technical, some procedural and some organisational. Whilst a level of planning and research will undoubtedly smooth the path, the great thing about the cloud services is that they are comparatively cheap and easy to initiate, so getting on and building a prototype is always going to be a good, exploratory starting point.

Categories: BI & Warehousing

PDB AWR Host CPU

Tom Kyte - Tue, 2018-06-12 10:06
Does the "Host CPU" in the PDB AWR mean the CPU usage for the PDB, CDB or the host?
Categories: DBA Blogs

Insert multiple csv in a zip

Tom Kyte - Tue, 2018-06-12 10:06
Hello, My requirement is to 1. Read two query result and write into two different CSV files 2. Zip these two CSV files in a single zip file. There is a similar qn posted that tells who to create csv file and store the result (clob and blob) i...
Categories: DBA Blogs

Sql Execution Time v/s Elapsed time v/s CPU Time

Tom Kyte - Tue, 2018-06-12 10:06
Hello , I have been working on Database monitoring stuff where we are looking for long running queries in DB . From the inbuilt setup which i have received from DBA , its showing CPU_TIME and ELAPSED_TIME but none of them is matching with Oracle E...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator