Systems Engineering and RDBMS

Deep Web

Posted by decipherinfosys on March 16, 2011

Yesterday, while having a discussion with a friend on search engines, the topic of Deep Web was raised. Deep web (or Deepnet) is essentially the part of the internet that is not indexed by the search engines for example the dynamically generated site content. It is an interesting topic – here is the wikipedia link in case you are interested in learning further:

http://en.wikipedia.org/wiki/Deep_Web

Posted in Technology | Leave a Comment »

Converting PL/SQL code into web services

Posted by decipherinfosys on February 25, 2011

Native XML DB Web Services in Oracle 11g makes the conversion of PL/SQL code into web services much easier.  Here are some links on this topic to get you started:

  • Oracle documentation – here.
  • Oracle-Base post – here.

Posted in Oracle | Leave a Comment »

Back to the Basics: Restoring an Analysis Services Database

Posted by decipherinfosys on February 24, 2011

Not every DBA or Database Developer has had experience with Analysis Services so it did not come as a surprise when a DBA at a client site approached us for quick help in restoring the Analysis Services Database on-to the development environment.  In our back to the basics series of posts, we will be covering this topic today.

Let’s use SSMS to connect to Analysis Services instance first.  In case you have never done that before, when you connect, select “Analysis Services” from the drop-down in the Server Type, the proper Server Name (your instance) and the authentication and you will be connected to the Analysis Services instance in which we will restore the back-up of the Analysis Services Database:

Once connected, right click on “Databases” and select “Restore”:

You will be presented with a very simple “Restore Database” window – most of the items are self explanatory – you would browse to select the location of your backup file (we selected the production backup file in the example below):

Once that is done, you need to then give a name for the restored database (in our example, we are calling it PRODCOPY), select a storage location using the browse button, Allow for the database overwrite if it already exists on the instance, choose to overwrite the security information and if the backup was encrypted, provide the password so that you can move ahead with the restore process:

Once that is done, you would be able to restore the database on that instance and then do configuration changes, data source changes etc.

Another way of doing the restore: In case you are not a GUI kind of guy and like to us scripts, you can also use XMLA.  You can read up on the command here.  You can invoke XMLA by right clicking on the Analysis Services instance and choosing “New Query” and XMLA.  The command parameters are the same as you saw in the GUI option above so add the values appropriately and then execute it in order to complete the restore process.

Resources:

  • XMLA – here.
  • Backup and Restore Analysis Services Databases – article on SQLServerPerformance.com by Ashish Kumar Mehta – here.
  • MSDN post on backing up and restoring Analysis Services Database – here.
  • Database Journal Article on restoring Analysis Services Database using Windows Powershell and SQL Server 2008 AMO – here.

Posted in Business Intelligence, SQL Server | Leave a Comment »

Next Release of SQL Server – Denali (SQL Server 2011)

Posted by decipherinfosys on February 23, 2011

MSFT had announced the first CTP of the next version of SQL Server at PASS last November.  Since then, there has been quite some buzz on the new features of the next version.  We were at a client site last week who had just now finished migrating to SQL Server 2008 and will be moving to SQL Server 2008 R2 shortly.  So, it was an interesting discussion with the CIO of the company who was putting together a presentation for the board of directors which showed the technology road-map for the company.  It becomes difficult to justify another migration in 2 years time (assuming they move to Denali in late 2012) unless there are key benefits to the business.

Here are some of the URLs to help you get up-to-speed on Denali:

  • You can download the CTP from here.
  • BOL for Denali – here.
  • What’s new in Denali – here.
  • MVP Michael Otey’s post on the new features in Denali – here.
  • Future SQL Server Editions page on MSFT site – here.
  • Analysis Services Roadmap for Denali – post on blogs.technet.com – here.

Posted in SQL Server | Leave a Comment »

OVER clause – much needed enhancements

Posted by decipherinfosys on February 23, 2011

For those of us who work in both Oracle and SQL Server, it is always a natural reaction to look for something equivalent in the other RDBMS in our day to day work.  One of the areas in which Oracle has excelled far ahead of SQL Server has been it’s rich support for analytics.  OVER clause capabilities is just one of those things.  MVP Itzik has posted several suggestions/requests on connect for such enhancements – you can see them here.  It also has links from other readers as well which point to the rich functionality of Oracle and DB2 in that regard. You should also read this whitepaper from Itzik and Sujata on this topic.

As per this update on connect, these did not make it in SQL Server 2008 and might be included in the next release – Denali.

This came up again when a client who is responsible for releasing their product on Oracle as well as SQL Server needed to simulate some of that rich analytic function capability in SQL Server.  There are always workarounds available and we have covered some of them in our previous blog posts (example – for first_value(), last_value() workaround) but it is time that SQL Server had that support now.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

Ghost Cleanup task in SQL Server

Posted by decipherinfosys on February 23, 2011

This topic of “Ghost Cleanup” background process came up while trying to resolve an issue at a client site recently.  In case you have never heard of the ghost cleanup in SQL Server, here is an excellent post by MVP Paul Randal on this topic.  And another post by him on the performance benefits of using a trace flag to turn it off in systems which have heavy delete workloads.

Posted in SQL Server | Leave a Comment »

NoSQL Databases making inroads in mobile market as well

Posted by decipherinfosys on February 23, 2011

Good post by Sean Gallagher on NoSQL databases making inroads into the mobile market – here.

Posted in Technology | Leave a Comment »

Data Architectures holding back the cloud?

Posted by decipherinfosys on February 22, 2011

Read a nice article on GigaOm by Dr. John Busch that talks about the need for architecture innovation to effectively address QoS and TCO – you can read it here.

Posted in Cloud Computing | Leave a Comment »

High Availability and Disaster Recovery

Posted by decipherinfosys on January 28, 2011

These two terms are often used very interchangeably.

High Availability typically refers to the solutions that use fault tolerance and /or load balancing in order to provide high availability of the applications.  HA is essentially the ability to continue operations when a component fails – could be a CPU, memory failure, disk failure, complete failure of a server etc..  With HA, there is usually no loss of service.  Clustering servers together is a form of HA, having redundant power supplies or redundant network controllers is a form of HA, having proper RAID arrays is a form of HA, having proper load balancing in place is a form of HA.  The primary goal of a HA environment is uptime/providing continuous service w/o disruption.

Disaster Recovery provides increased availability.  It is the process/ability to restore operations that are critical for the business to run after a disaster (human induced or natural causes example – power failure in the production site, floods, earthquake, hurricane etc.).  The key difference between DR and HA is that the recovery time taken in the case of DR is typically more than a HA.  With DR, there is a small loss of service while the DR systems are activated and take over the load in the DR site.

Here are some posts/articles which delve into these differences in more detail:

http://www.channeldb2.com/profiles/blogs/disaster-recovery-high

http://nexus.realtimepublishers.com/sgudb.php

http://www.drj.com/2010-articles/online-exclusive/understanding-high-availability-and-disaster-recovery-in-your-overall-recovery-strategy.html

http://gestaltit.com/all/tech/storage/bas/define-high-availability-disaster-recovery/

Posted in DB2 LUW, Oracle, SQL Server, Technology, Unix | 1 Comment »

Insert data in one table with data from other table(s)

Posted by decipherinfosys on January 27, 2011

This post is in response to this question from a reader:

“Your post on updating data in one table with data from another table was very helpful.  I need to also do an insert into a table (a summary table) with data from a bunch of other tables which are in the same database and also in database(s) on other instances.  Right now, I have a very convoluted process of scheduled jobs, row by row scripts to help me do that.  There is some transformation (mostly lookups) involved as well.  Is there an easier/simpler way to do this?  Appreciate your thoughts on this”

The post that the question above referred to was this one – here.  In that post, we had covered how you can update the data in a table with the data from another table.  Doing an insert into a table with data from other tables (within the same schema or separate schemas or separate databases or separate databases on separate instances) is also fairly simple.  There are many ways of doing it – we will cover the “INSERT INTO …. SELECT FROM … ” approach in this blog post.

Let’s consider this hypothetical scenario to illustrate this (using SQL Server lingo though the same approach is available in Oracle and DB2 as well) – Say the summary table “tblEmpSummary” that this reader wanted to insert the data into resides in a database called EXECUTIVE in a schema called ANALYSIS.  It contains summary information on the employees – their name, their department, their performance review rating, their expense reports per week and the weekly benefit expenses for those employees.

And the source of the data are these:

a) Other table called EMP_PERF_REVIEW in the same schema in the same database (contains EMP_PERF_RATING column that we want and can join on EMP_MASTER_ID column),

b) Other table(s) called EMP_MASTER, EMP_DEPT_XREF and DEPT_MASTER from the schema EMPLOYEE on the same database (contains FIRST_NAME, LAST_NAME, DEPT_NAME information and we can join on EMP_MASTER_ID and DEPT_MASTER_ID),

c) Other table called EMP_EXPENSE_RPT from the EMP Schema in the ACCOUNTING database in the same SQL Server instance (contains WEEK_ENDING_DATE and TOTAL_AMOUNT fields that we are interested in and can join in using EMP_MASTER_ID column), and

d) A view called VW_EMP_PTO from a schema called BENEFIT from the HR database that resides on another instance (Contains PTO_ACCRUED and TOTAL_AMOUNT field for the benefits that we are interested in and can join in using EMP_MASTER_ID).

So, we have data coming from:

a) table in the same schema/same database,
b) tables in the same database but a different schema,
c) tables in a separate database on the same SQL Server instance, and
d) a view from a database in a separate SQL Server instance all together.

If suppose all of our data was coming from (a) alone, all we would have needed to do would be this:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from one source>
FROM Analysis.EMP_PERF_REVIEW
WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

Please do note that the WHERE clause above can contain a NOT EXISTS check on the tblEmpSummary to ensure that no dupes are being inserted – needless to state, this INSERT code would be wrapped in a TRY…CATCH block with proper error handling.

If we now extend it to include the data from (b) as well, the above SQL would change to:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from the two sources – use the proper aliases>
FROM Analysis.EMP_PERF_REVIEW as EPR
INNER JOIN Employee.EMP_MASTER as EM ON EPR.EMP_MASTER_ID = EM.EMP_MASTER_ID
INNER JOIN Employee.EMP_DEPT_XREF as EDX ON EM.EMP_MASTER_ID = EDX.EMP_MASTER_ID
INNER JOIN Employee.DEPT_MASTER as DM ON EDX.DEPT_MASTER_ID = DM.DEPT_MASTER_ID
WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

As can be seen from above, we have assumed that the select privileges are present and you would be able to join the tables easily then.

Now, if we have to include (c) scenario as well and assuming that we have select privileges in place already on that object, we can either create views/synonyms for those objects in our schema or simply refer to those objects if the permissions are available using a three part naming convention:

<database_name>.<schema_name>.<object_name>

So, the above SQL then gets modified to:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from the three sources – use the proper aliases>
FROM Analysis.EMP_PERF_REVIEW as EPR
INNER JOIN Employee.EMP_MASTER as EM ON EPR.EMP_MASTER_ID = EM.EMP_MASTER_ID
INNER JOIN Employee.EMP_DEPT_XREF as EDX ON EM.EMP_MASTER_ID = EDX.EMP_MASTER_ID
INNER JOIN Employee.DEPT_MASTER as DM ON EDX.DEPT_MASTER_ID = DM.DEPT_MASTER_ID
INNER JOIN Accounting.Emp.EMP_EXPENSE_RPT as EER ON EM.EMP_MASTER_ID = EER.EMP_MASTER_ID

WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

Now, that leaves us with the last part – (d).  Since it resides in a database on a separate instance, we can first create a linked server to it first and then either reference it using a couple of ways as was shown in that blog post on linked server.  Or we can simply create a synonym for it and use it.  Assuming we are using a four part naming convention to reference it, the above SQL now becomes:

INSERT INTO Analysis.tblEmpSummary (<list of column names here>)
SELECT <list of the column names from the four sources – use the proper aliases>
FROM Analysis.EMP_PERF_REVIEW as EPR
INNER JOIN Employee.EMP_MASTER as EM ON EPR.EMP_MASTER_ID = EM.EMP_MASTER_ID
INNER JOIN Employee.EMP_DEPT_XREF as EDX ON EM.EMP_MASTER_ID = EDX.EMP_MASTER_ID
INNER JOIN Employee.DEPT_MASTER as DM ON EDX.DEPT_MASTER_ID = DM.DEPT_MASTER_ID
INNER JOIN Accounting.Emp.EMP_EXPENSE_RPT as EER ON EM.EMP_MASTER_ID = EER.EMP_MASTER_ID
INNER JOIN LinkedSrvHR.HR.Benefit.VW_EMP_PTO as VWP ON EM.EMP_MASTER_ID = VWP.EMP_MASTER_ID

WHERE <filter conditions>
<any other conditions like GROUP BY, ORDER BY etc. that we need>

This should cover all the scenarios that you can possibly run into.  You can also try this with MERGE command.  Any transformations like look-up of values that you can do in T-SQL, you can do over the record set that you get using the above SQL.  It is essentially now a data-set for you that is coming from 4 different sources.  What you do with that data-set is entirely up to you – use a CASE statement over it, do aggregation over it, dump it into a temporary table/table variable for further processing etc. – all the normal T-SQL operations you would be able to do including inserting it into the summary table.

In addition to the technique from above, if this is something that you need to do as a quick and dirty thing and you do not intend to keep the new table around post the analysis, you can also do:

select … into <new table> from <old table or set of tables from one or multiple sources> where <the where condition over here>

The “select into” is the same as CTAS in Oracle.  You can read more on that here.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 83 other followers