Skip navigation.

Pythian Group

Syndicate content
Love Your Data
Updated: 1 hour 5 min ago

Running SQL Server Jobs Against an Azure SQL Database

Tue, 2015-10-27 07:50

 

I recently had a client ask how to run SQL Server jobs against an Azure SQL Database, and because SQL DB doesn’t have an SQL Agent like most other versions of SQL Server, it isn’t always obvious how to implement. Fortunately, we have several options in Azure and within a “normal” instance.

Options to Run Jobs

The first three options require a non-Azure version of SQL Server to be running and to have connectivity to the Azure SQL DB.

  1. Linked Server
  2. Maintenance Plan w/new connection
  3. Powershell Script
  4. Azure Services
Elastic DB Jobs

As mentioned by Warner Chaves in the comments, and currently (Nov 2015) in preview, the new Elastic Database Jobs might also work well.

Linked Server

To create a linked server to an Azure DB, you just need to get the ODBC connection string from the Azure portal and use it as the “Provider String” when defining the LS.

ODBCConnectionString

ODBCConnectionString

 

Maintenance Plan w/New Connection

Simply create your maintenance plan with all the required logic for your job, then click the “Manage Connections” menu at the top of the screen and define the new connection to be used for this plan.

MaintenancePlan

MaintenancePlan

 

Powershell Script

You can use the Powershell Invoke-SqlCmd functionality and run it as a Powershell job step.


Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "AzurreInstance" -U "UserName" -P "Password"

Azure Services

These change, it seems like, almost daily, so I’ve listed some of the common ways this is currently done.

  1. The Azure Scheduler used in conjunction with mobile services.
  2. Azure Automation

 

Discover more about our expertise in Cloud and SQL Server.

Categories: DBA Blogs

Log Buffer #446: A Carnival of the Vanities for DBAs

Fri, 2015-10-23 15:25

This Log Buffer Edition covers some more Oracle, SQL Server and MySQL blog posts from across the world.

Oracle:

  • Should I monitor the MGMTDB database?
  • Connecting MAF Applications to Mobile Cloud Service
  • JRE 6, 7, and 8 now certified with EBS 11i, 12.1, and 12.2
  • The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1
  • How To Install Latest Verisign G5 Root Certificates

SQL Server:

  • Dynamic Pivot, Passing Temp Table Variables to Dynamic SQL
  • Oracle offers a results cache in the database (from 11.2 onwards) and in PL/SQL (again, from 11.2 onwards) which can greatly reduce execution time of repeated statements when insert/update/delete activity is not heavy.
  • Maintaining a grouped running MAX (or MIN)
  • Harnessing SQL Server Metadata- Disabling and Rebuilding Indexes
  • Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

MySQL:

  • ClusterControl Tips & Tricks: Updating your MySQL Configuration
  • Become a MySQL DBA blog series – Optimizer Hints for faster query execution
  • Loading Arbitary XML documents into MySQL tables with p_load_xml
  • Use MySQL and Perl to automatically find, follow and unfollow twitter users
  • Great Advice on Monitoring Amazon RDS For MySQL From Datadog

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

SQL On The Edge #4 – SQL 2016 Stretch Database

Wed, 2015-10-21 09:49

 

As the Microsoft SQL Server team continues to build very interesting integrations between the full SQL Server box product we all know and the Azure cloud platform. Stretch database is another integration that will be included as part of SQL Server 2016 and it will help customers solve these issues:


– Running out of space in your own data center?
– Have long archival requirements for your data?
– Users still query data from many years ago on occasion?
– You don’t want to do code changes to deal with these scenarios?

 

Stretch database attacks those problems by allowing us to “stretch” tables from on-premises (or full VM) SQL Servers into a table living in Azure SQL Database. Your users and applications will not be aware of where the data is coming from, they just have to query and let SQL Server handle the behind the scenes magic to either go to the local data files or go to the cloud to get the records.

So if you’re running out of space, you can stretch to a Standard tier db and get 250GB instantaneously or 500GB on Premium or even 1TB if you get a P11! Of course you will have to pay for the Azure SQL database cost but considering it’s a fully managed service, in some scenarios this can easily offset the cost of the on-premises storage plus managing all the archival if your system is busy and large enough.

The feature also works transparently with the backup and restore operations. When you backup your SQL Server database it will contain the metadata for the Stretch database and if you have the proper credentials, you can re-enable the connection to the Azure SQL database on RESTORE. If you just want to RESTORE as let’s say a DEV copy then you can either not reconnect the stretch database at all or reconnect it to some other DEV Azure copy for example.

As is usually the case, this feature does come with some limitations. For example, it can’t be used on replicated tables, In-Memory tables or tables using the CLR based data types (hierarchy, XML, spatial types, etc). The main limitation now is that UPDATE and DELETE are not supported so it’s very much for ‘append-only’ type of data. However, for many scenarios these limitations can be more than acceptable. You can see the full list here.

To see how we enable this feature and how it works, let’s jump to the video, enjoy!

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Gotta Love English

Wed, 2015-10-21 07:28

 

I do love to write, and have always enjoyed the idiosyncrasies of this wonderful language. One of my favourite features of English is how I struggle with the following items almost daily:

  1. I am doing a few things to alleviate the need to worry about something. Do I write There is a handful of things … or There are a handful of things …? I recently asked a handful of colleagues their feelings on the above and the results are in … 5-5. I guess the jury is still out on that one, but I still maintain there is a handful of questions as yet unanswered.
  2. I hope my decision does not affect (or is that effect) others’ opinion of my writing skills. Even though classically I use affect as a verb and effect as a noun … hold on … wait a sec … maybe it’s the other way around. I will still struggle and every time hope when I use one of them wrong, the effect of that mistake will not affect my feelings for the wonders of this fine language.
  3. It’s time for me to take its advice. I am not sure if it stands on its own with respect to ownership. Most words in this insane language use the ‘s to show possession but alas not all. Needless to say, it’s not obvious and I pause for a moment of thought every time I use this word, with or without the s on the end and/or the ‘s.
  4. That pesky ing ending to so many words. I saw him walking into a store. Is this a gerund or a present participle? I am not sure if anyone cares maybe except me :). Google says that  a gerund is “is a noun made from a verb by adding “-ing.” Thus, I believe walking, in the context of the above statement, is a gerund, being a verb behaving like a noun. Pencil is a noun and, when someone loses one, we say Have you seen his pencil not Have you seen him pencil. If this is the case, why would one say I saw him walking rather than I saw his walking? A noun is a noun.

To sum up this musing, suffice to say, a handful of questions has its effect on my studying more about this quirky language we all love.

Categories: DBA Blogs

Errors in a Pluggable Database?

Wed, 2015-10-21 06:23

 

There might be a situation where executing some DDL in pluggable database may cause the following error:

ORA-65040: operation not allowed from within a pluggable database

This error could occur if a tablespace is being dropped from within PDB and this tablespace is a former default tablespace having some of the system objects. Even system objects cannot be moved with simple alter statements from within PDBs.

So in order to move these objects from within PDBs, you should be using procedure dbms_pdb.exec_as_oracle_script which is undocumented so far.

For example:

exec dbms_pdb.exec_as_oracle_script(‘alter table <owner>.<table_name> move tablespace <tablespace name>’);

From My Oracle Support, Doc ID 1943303.1 lists:

—   This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB.

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

The 2nd Annual PASS Summit Bloggers Meetup (2015)

Tue, 2015-10-20 17:20

I’m excited to announce the second annual PASS Summit Bloggers Meetup! We began this last year and it was cool but this year will be even cooler!

What: PASS Summit Bloggers Meetup 2015
When: Thursday, October 29th, 5:30pm
Where: Sports Restaurant & Bar, 140 4th Ave North, Suite 130, Seattle, WA 98109.
How: Please comment with “COUNT ME IN” if you’re coming — we need to know attendance numbers.

We’re excited to meet old friends, and make new ones in Seattle this year. Pythian will sponsor munchies and drinks as usual. There will be a networking contest with some cool prizes, plus you will get your very own Love Your Data t-shirt (at least the first 50 people). Meetups are a great way to make new friends in the community, so come on out — all bloggers are welcome!

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Make sure you comment here if you are attending so that we have enough room, food, and drinks.

See the photos from the last year’s meetup courtesy to Pat Wright.

SQL PASS 2014 Bloggers Meetup photo

The location is perfect to get ready for the Community Appreciation Party — a few minutes walk from EMP Museum! Snacks and drinks before the big event and mingling with fellow bloggers. What can be better?

Of course, do not forget to blog and tweet about this year’s bloggers meetup using #Summit15 #sqlpass. See you there!

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Oracle OpenWorld 2015 – Bloggers Meetup

Tue, 2015-10-20 16:51

Oracle OpenWorld Bloggers Meetup Many of you are coming to San Francisco next week for Oracle OpenWorld 2015 and many of you have already booked time on your calendars on Wednesday evening before the appreciation event. You are right — the Annual Oracle Bloggers Meetup, one of your favorite events of the OpenWorld, is happening at usual place and time!

What: Oracle Bloggers Meetup 2015.

When: Wed, 28-Oct-2015, 5:30pm.

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103 (street view). Please comment with “COUNT ME IN” if you’re coming — we need to know the attendance numbers.


As usual, Oracle Technology Network and Pythian sponsor the venue, drinks and cool fun social stuff. This year we are dropping a cool app and resurrecting traditions — you know what it means and if not, come and learn. All blogger community participants are invited — self qualify is what that means ;).

As usual, vintage t-shirts, ties, or bandanas from previous meetups will make you look cool — feel free to wear them.

For those of you who don’t know the history: The Bloggers Meetup during Oracle OpenWorld was started by Mark Rittman and continued by Eddie Awad, and then I picked up the flag in 2009. This year we have Oracle Technology Network taking more leadership on the organization of the event in addition to just being a “corporate sponsor”.

The meetups have been a great success for making new friends and catching up with the old, so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and OOW09 meetup blog post update from myself, and a super cool video by a good blogging friend, Bjorn Roest from OOW13.

While the initial meetings were mostly targeted to Oracle database folks, guys and gals from many Oracle technologies — Oracle database, MySQL, Apps, Sun technologies, Java and more join in the fun. All bloggers are welcome. Last year we crossed 150 attendees and I expect this year we may set a new record.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL (or whatever you consider a replacement of that — I’ll leave it to your interpretation) with your comment — it’s a Bloggers Meetup after all! Please do make sure you comment here if you are attending so that we have enough room, food, and (most importantly) drinks.

Of course, do not forget to blog, tweet, linkedin, G+, instagram, email and just talk about this year’s bloggers meetup. See you there — it will be fun!

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs