Showing posts with label SQL Server 2008. Show all posts
Showing posts with label SQL Server 2008. Show all posts

Monday, July 11, 2011

MS BI Architecture Design Layers - classifying layer specific logic

I'm reading: MS BI Architecture Design Layers - classifying layer specific logicTweet this !
Any architecture design diagram is composed of several layers vertically and horizontally. Horizontal layers are discrete logical areas and their association in the diagram describes the way they are connected to each other. Vertical layers run across the entire solution and all these logical areas, which means they are applicable throughout the solution. For example, data repository can be classified as a horizontal layer and auditing can be classified as a vertical layer. This is a very well known fact and most of us would be knowing this very well.

When it comes to implementation, the association of these layers are honored and the solution is developed keeping this layers in view. But this is only in terms of how these layers are associated with each other, i.e. data and process would flow vertically and horizontally as defined in the architecture diagram. One very vital point that many miss is where to deploy your logic. Below are few logic deployment challenges or confusions that most of us would have faced as decision makers at some point of time in our careers:

1) Should logic be stored in .NET App or in DB Stored Procs

2) Should logic be stored in Stored Procs in OLTP DB or in ETL package

3) Should logic be stored in scheduled batch jobs or in ETL driver package

4) Should logic be stored in Stored Proc or SSRS Report

5) Should logic be stored in SSAS MDX Script or Client App

6) Should logic be stored in Dashboard or SSAS Cube

I have seen many genius taking their comfort route to make their jobs easy and jeopardize the future of the solution, just by deploying the logic that belongs to one layer of the architecture into another layer.

Once I had come across a scenario where one genius project manager tried to defend a solution with the argument that as the application was designed as a reporting application, entire logic is stored at the report level. This means SSRS RDLs contained the entire query logic and formatting logic within it. The solution in discussion was developed as a reporting application, and after few months down the line the requirement came up to act as a data source for other systems. As the logic was completely embedded in reports, it was not reusable at all and the solution design fell flat. Looking at embedded SQL in RDLs, any logical developer would ask, what an uncompiled SQL is doing in SSRS report ? SQL belongs to DB inside a SP and formatting the UI of the report is the report specific logic that can be contained in reports.

I have also been evident of scenarios where an application architect is in the driver seat, and the approach pursued it to embed entire logic into .NET code and treat DB as a blackbox to pump-in and pump-out data. In any corporate IT systems history you would find that application layer i.e. the User Interface / Web Front End layers are changed like changing the curtains of your windows, but corporate DB are hardly changed and whenever DBs are migrated they are a result of a large scale corporate IT systems revamping exercise.

I do not intend to hint that all logic should reside in the DB. Whatever logic that belongs to a particular architecture design layer, it should be deployed in that layer only, which is one of the implicit communication of layering in an architecture design diagram. Entire functionality can be achieved by deploying code in a single layer of the solution, but in the long term it would defeat the very purpose of layering and design patterns. Dissecting the right part of the logic in the right layer, followed by best practices of developing the layer would provide the most ideal solution from a stabilized solution design perspective.

In the field of technical architecture design, my career experience has been that each piece of logic should remain with it's deserving layer. Feel free to prove me wrong !!

Friday, May 13, 2011

Using a scheduler in the technical design of your BI solution

I'm reading: Using a scheduler in the technical design of your BI solutionTweet this !
In Microsoft world when you think of scheduler, SQL Agent is one of the most famous name that would come up on the list. Scheduling can be seen as a very nominal and regular requirement in almost every project, and most professionals would think of addressing it by installing a scheduler on your target server. There is a difference between having a mission critical scheduler service and an enterprise scheduling service. If you see from the eye of an architect, you would never want a mission critical scheduler, as in the long run it becomes a maintenance bottleneck. There are two points to consider before designing solution for your scheduling needs. Firstly you need to figure our an enterprise class scheduler and secondly you need to get right design in place.

Personally, I do not see SQL Agent as an enterprise scheduler. It is very much useful for scheduling database related tasks, but even there I see some very serious shortcomings. For example, there is no file-watcher capability built-in with it, and watching for file / feeds is a very essential and common requirement with application as well as BI solutions. An example of enterprise class scheduler is Autosys, that I have seen in many enterprises class IT environments. Some of the strong features are scripting support, client-server architecture, multi-platform support, file-watcher event-watcher and other intercepting capabilities. ETL / Data warehousing solutions mostly need intercepting capabilities for data feeds, and if you need to depend on your ETL tool for interception rather than your scheduler, this is a shortcoming of your scheduler. In SSIS, developers use third-party File-Watcher and WMI tasks to watch for feeds, but I personally see it as a shortcoming of SQL Agent and not SSIS.

Now comes the question of how you frame your scheduler in the technical design of your solution. Mostly designers are concerned limited to the scope of the solution they are developing. But an architect needs to foresee how the solution would grow and fit in the enterprise. Any successful solution and business would mostly flourish, and so would the IT systems surrounding the same. With this growth whether it is in the form of scaling the solution and server across geographies, or more solutions getting rigged with your solution, your scheduling needs would also grow. So in case you have opted to use mission critical database or application or system schedulers, it would be extremely hard to manage your scheduling. Consider you have your BI solution spread across geographies with multiple servers and you intend to manage different scheduling needs on different servers, and you have opted to use scheduler on each server by installing SQL Agent. So how do you enforce policies, deploy scheduling routines, as well as control and monitor the same ?



The solution you need to have is a scheduler with a client server architecture. Generally the regular practice is to have a centralized scheduling batch server and scheduling agents are installed on each server. Scheduled jobs are always punched in the centralized batch server and agents synchronize with this server to retrieve jobs respective to their servers. In this way you can replicate your BI solution by creating different server clusters at different geographies without any maintenance , and scheduling of each cluster can be managed using the centralized batch server.

Enterprise class scheduler + Scheduling design pattern = High Performance. Delivered. (It's accenture punch line !).

Monday, February 14, 2011

Planning MS BI shared environment for production systems

I'm reading: Planning MS BI shared environment for production systemsTweet this !
Multinational Corporate Clients generally have data centers hosting their BI environments and different production systems are hosted on such environments. Edition upgrade is a hard fact of the IT world and everyone right from developers till production systems needs to go through this change. The reason why I term this as a hard fact, is because synchronizing with this upgrades is quite demanding.

Business analysis is becoming more competitive, predictive, and intelligent with the passage of time, and the same demands more intelligent means of analyzing data. In a corporate environment, there are many discrete business units, each requiring - developing - hosting their own set of applications on a common BI environment hosted in data centers, managed by a centralized operations team. I know of many clients who started hosting their BI environments with the SQL Server 2005 platform, but they forget the fact that this is not the ultimate version. In a shared environment many applications are hosted on the same SQL Server instance, which might be managed using a cluster on SAN volumes.

The real challenge is when a few applications require application upgrades and the rest do not vote in favor of upgrade. So the challenging question faced is, how to isolate your application and instance out of the 50 odd applications hosted on the same instance. Significant amount of hardware resources (Multi core motherboards, Dedicated servers, SAN volumes, Load Balancers, Clusters, etc..) and human resources (DBAs and Operations Support Staff) are invested for maintaining the environment. Added complexity arises from the security configuration, as corporate environments have Single SignOn configuration using mechanisms like NTLM / ADFS. Asking for a separate instance is almost guaranteed to fail approval.

In my experience, I have observed certain exercises that can help to avoid encountering such circumstances. I am sharing a few of them below:

1) Decouple each service to the maximum extent possible. In raw words, at least do not install all the services like SSIS / SSAS / SSRS all on same box.

2) Document the features used in all your artifacts, so that you are always in control of what an upgrade can cause to which entities. For example, documenting features used in each reports would be beneficial, when you are upgrading from 2005 to 2008 R2. If possible try to create entities having common feature usage on dedicated instances, without ending up with a dozen instances. Use your intelligence and create only as much as you can afford to manage.

3) As soon as a new edition is out, create a track of deprecated, discontinued, upgraded, breaking and value added features. Creating a risk analysis sheet should be regular exercise during the release of each new edition. Digging a well when you are faced with fire is a bad exercise. I mean that when you are faced with the urgency of upgrade and then you start planning the migration strategy is a considerably late exercise and lack of long term planning.

4) DBAs generally freak out with installation of a tool as tiny as Upgrade Advisor too, when it comes to installation on a cluster. Shared instance of SQL Server for different applications makes them completely paranoid. So it's always better to keep green zones on production servers, for installation of tools as a part of contracts / SLAs so that shared stakeholders do not convert your SQL Server production instance into a DMZ.

5) Take extra care for components that are considered as shared by SQL Server architecture itself. Most designers ignore the little detail that SSIS is a shared component in the MS BI Stack, and this can be catastrophic when you are faced with an upgrade on an environment where 20 applications are using the same SSIS instance.

6) Use this guide as a detailed reference material to assess the risk that you might be already running with your existing production systems.

If you have more tips to add to this post, please feel free to share your comments or email me your viewpoint.

Wednesday, October 27, 2010

Use of SQL Server 2008 R2 Express Edition with Microsoft and Non-Microsoft BI platforms

I'm reading: Use of SQL Server 2008 R2 Express Edition with Microsoft and Non-Microsoft BI platformsTweet this !
SQL Server Express Edition is one of the most admired databases for BI platforms. Before a couple of years, I would not have been able to make this statement, but through the due course of experiences of my career, I can make this statement quite confidently today. Non only on microsoft platform, but non-microsoft based BI solutions are quite happy to include express edition as one of the constituents of a BI solution for either housekeeping tasks or to catalyze data / data processing. Some of the interesting use of express editions are listed below.

1) Proof Of Concept development: During the POC phase, projects are least likely to procure licenses. One of the first benefits of express edition is that it's license free for use in development as well as production, and does not come with a time bomb in it. As far as programmability features are concerned, it comes with almost all the features that an enterprise edition contains. So if the requirement is just a database container, express edition is one of the best starters on the menu.

2) Test data store: Instead of using resources on the server with higher editions of sql server, express edition can be used as a warehouse to store test data. When the development team requires test data during the development phase, they can always synchronize their local instances with test data from an express edition which can be treated as a test data server. This express instance can be handed over to a business analyst who would generally prefer to own a database free of complexity.

3) Metadata provider: Often many legacy systems or systems like SAP or other CRM systems emit out data in text / CSV files. Now these files many times contain non-printable characters or other junk characters, and the issue occurs during the import process. As a part of structural testing, legacy data files can be imported into express instance which can be installed at the source. Importing full / sample data into express edition can provide concrete metadata structure to the data (which does not exist in text / csv files) as well as test the import process. This can be done right at the source free of cost with express edition, making ETL processes and data migration more smooth. Many non-microsoft platforms would consider express edition as a tempting choice for purposes similar to these.

4) Temporary Staging: SQL Server 2008 R2 express edition has the database size limit of 10GB. This is a reasonable size for considering express instance as a temporary staging server in your ETL solution to stage master and/or transactional data.

5) BI Housekeeping: A production / UAT / QA server DBA would not allow external applications to connect to these servers. A project might want to retain a repository of execution and performance data history, and express edition is one of the best choice for such purposes. One can use any number of analysis / reporting tools on this edition without any security concerns.

Above mentioned are some of the uses of express edition in a BI solution development environment. 10GB DB size, SSMS, almost full DB programmability features, and license free production use are the major features of express edition. Keeping these features in view, you can consider SQL Server Express edition to economically catalyze your BI solution development.

Friday, August 20, 2010

Microsoft Business Intelligence ( SSIS / SSAS / SSRS ) on Amazon Elastic Compute Cloud ( EC2 )

I'm reading: Microsoft Business Intelligence ( SSIS / SSAS / SSRS ) on Amazon Elastic Compute Cloud ( EC2 )Tweet this !
One of the reasons, SSIS is making it's entry into most of the data warehousing eco-systems, is due to it's competitive pricing and the immense support that it has inherently got from SQL Server as a database engine. But when you are in a competitive world, you need to hit the best possible estimates that wins over others. Pricing is a very big factor, especially when your organization is acting as a service provider as well as solution provider. Difference is that as a solution provider, you develop a solution for your client, deliver it and you are done. As a service provider you are expected to host the solution and provide it as a service for the agreed duration of time within the agreed Service Level Agreements (SLA).

Windows Azure and SQL Azure are growing consistently in the cloud computing environment, but still they are not mature enough to provide an integrated environment for SaaS flavor of BI solution on Azure platform. One such cloud computing environment is provided by Amazon Elastic Cloud Compute known as Amazon EC2. IT cannot work in isolation and neither can MS BI, so it's immature to consider just Azure for cloud requirements related to MS BI.

EC2 is a huge platform and has got a lot of services and products. Below is my flavor of understanding and explanation of same, considering the services of interest in alignment to MS BI.

1) Amazon Web Service (AWS) is the medium by which Amazon provides IaaS (Infrastructure as a Service) to it's clientele. Technically speaking, these services are made available in the form of a web service. All the services listed below fall under the classification of AWS. You should read this page to check out for the relational databases supported by Amazon.

2) Amazon EC2 : A web service that provides resizable compute capacity in the cloud. This can be considered a super version of Windows Azure. Consider Amazon EC2 as your processor and Amazon Elastic Block Storage as your hard drive on the cloud. Other services of interest available with EC2 are Amazon CloudWatch, Elastic-Load Balancing, and Elastic IPs.

3) Amazon SimpleDB : A web service for running queries on structured data in real time. It provides the core functionality of a database, real-time lookup and simple querying of structured data without the operational complexity. SQL Azure can be considered similar to this.

4) Amazon Simple Notification Services (SNS) : By using Amazon SNS, developers and businesses can send notifications or messages to applications or people using a “push” mechanism and have these messages delivered over protocol of choice. Probably there is no equivalent of this in Azure services stack.

5) Amazon Simple Storage Service (S3) : A simple web services interface that can be used to store and retrieve large amounts of data, at any time, from anywhere on the web. This is similar to Windows Azure Storage.

6) Amazon Virtual Private Cloud (VPC) : A secure and seamless bridge between a company’s existing IT infrastructure and the AWS cloud. Amazon VPC enables enterprises to connect their existing infrastructure to a set of isolated AWS compute resources via a Virtual Private Network (VPN) connection, and to extend their existing management capabilities such as security services, firewalls, and intrusion detection systems to include their AWS resources. This is an advanced / flexible version of what Windows Azure Appliance Solution is trying to provide.

The main service of our interest, if we intend to start our development or production using MS BI stack on Amazon cloud, is Amazon EC2 running Microsoft Windows Server and SQL Server. Express edition and Standard edition of SQL Server 2005 and SQL Server 2008 are available. This would mean that enterprise level applications can't be executed on this environment.

If I consider a scenario where I need MS BI capabilities for a few months with a handful of developers, instead of buying servers, hardware and software licenses, it would be a cost effective way to start piloting your solution with the kind of platform and services provided by Amazon EC2. If you are thinking about the cost differences, a Standard Edition SQL Server for a Workgroup license of 5 developers would cost at least a couple thousand dollars (at least in 5 digits), and with EC2 it would not cost you even in 4 digits for a couple of months. You don't believe this ? Well, check it out using AWS Simple Monthly Calculator. As I keep telling, cloud is all over the place and if you don't find cloud, cloud would find you !!!

Thursday, August 05, 2010

Spatial Data Resources

I'm reading: Spatial Data ResourcesTweet this !
Spatial data is one of the valued enhancements since SQL Server 2008. SSMS has also been enhanced to support viewing of spatial data. But still there are limitations on how you create spatial data, and generating Well Known Text (WKT) is something that can be done only visually. Visual tools are needed, using which one can draw shapes or plot points / shapes on locations and the WKT should get generated. For viewing WKT one can also use the spatial results tab in SSMS.

Two of the most useful resources for generating WKT by visually generating or pointing shapes for spatial or geospatial data are Mike Ormond's SQL Server 2008 Geospatial Data Generator and the newly released Spatial Viewer utility on codeplex by Simon Sabin. This spatial viewer is a C# application that is intended to create WKT out of the drawn shapes and vice-versa too. I have not tried this application, but this is definitely a valued utility. If this can be made to integrate in SSMS, it would serve as one of the most valued application as of now for spatial data support in SSMS.

Spatial Viewer Utility


Image Courtesy: Simon Sabin's Blog

Monday, May 17, 2010

SQL Server data types serialization format

I'm reading: SQL Server data types serialization formatTweet this !
This is the 200th post on my blog and I am blogging this at 2:00 AM in the morning, and see what a grand resource I have got to make this a grand post. When we look at data stored in spatial data types i.e. geometry or geography ( I know hierarchyid is also spatial data type, but I am ignoring it for the moment ), the binary format looks alien to us, but in fact is called as WKB (Well known binary) format. Also this format is very structured and if you know the serialization logic behind the same, you can easily decode this metadata just by looking at the binary values.

Though one would not be required to use these kind of manuals on a day to day basis, but this can help to deepen your fundamentals about SQL CLR Types, and spatial datatypes in particular. Keep it as a manual to understand storage and serialization of each and every SQL Server data type. It's a PDF available for public download from Microsoft and the same can be downloaded from here.

Monday, May 10, 2010

Free SQL Server Ebook : Defensive database programming with SQL Server

I'm reading: Free SQL Server Ebook : Defensive database programming with SQL ServerTweet this !
It's been raining free content and resources for SQL Server these days. Every next day when I think I would blog about a technical topic and I get hit by a new free resource on SQL Server, good enough to make me hold my technical post and blog about this free content. Today it's the favourite food for developers, a free ebook on SQL Server.

Most people look out for interview questions and answers ebooks to cross technical interviews. In my views, if you really want to cross a technical interview, read such books and check yourself whether you are up-to-date with your concepts and viewpoints by matching it with the viewpoint and guidelines presented in such books. I have not read this entire book completely, as the moment I came to know about this book, I took a 10-15 min glance of this book and made up my mind to blog about it. It's a nice ebook and the subject is relevant itself to reveal what the book is about. For dummies, this book mostly discusses how NOT to do database programming from a long and/or short term vision. Book contains many code examples which is like a feast for SQL Developers. Click here to download this book.


Reference: SQL Aloha

Monday, May 03, 2010

Lack of support for spatial data movement in SSMS and SSIS

I'm reading: Lack of support for spatial data movement in SSMS and SSISTweet this !
Spatial data has definitely got some level of support in SSMS, but that is more limited in terms of visualization of data in the results tab. When I consider to move the same data out of SQL Server i.e when I want to import and/or export this data using the Import Export Wizard, it breaks up as it is not upgraded with a corresponding data type in SSIS.

Even if you modify the config file that Import Export Wizard uses to map it to any data-type and facilitate the data movement, it does not help. Using varchar(max) or varbinary(max) also helps for some destination and breaks for others. To the best of my knowledge, I am not able to figure out a generic datatype that can take the helm of pulling or pushing any spatial datatype on it's back for a smooth import - export.

The same issue is faced in SSIS. As of date, there is no straight mapping for spatial data types. Though spatial datatypes got introduced in SQL Server 2008, SSIS has not been upgraded even in the R2 version to accommodate smooth movement of spatial data. DT_IMAGE is the best option as of now to move the same in SSIS, or the best approach is to convert spatial data in WKT format before it leaves SQL Server and read the same in SSIS. Converting in WKT makes it easier to analyze and manipulate this data in SSIS and then use a Script task or the same WKT to push data back into the spatial columns.

Acceptance of spatial datatypes and it's use would grow gradually, but R2 would have been good release to incorporate the support for spatial data movement in and out of SQL Server as well as in SSIS too.

Friday, April 23, 2010

Free SQL Server 2008 and SQL Sever 2005 Tools, Books, Downloads, Webcasts, E-Learning courses and Resource Collection

I'm reading: Free SQL Server 2008 and SQL Sever 2005 Tools, Books, Downloads, Webcasts, E-Learning courses and Resource CollectionTweet this !
There's a saying that "there are no free lunches", but fortunately there are lots of free resources available in the world of SQL Server. And for the same thanks to all those generous professionals out there who share these information with us. Do not worry, this is not a social message kind of post and quickly coming to the point, below are some of the great resource collection that comes up immediately to my mind.

Having the right material / tools at the right time can really make one's life easier as you do not have to start your quest to hunt for some tool or technique to resolve a highly pressing problem. Below are the posts that lists down links to lots of SQL Server resources. And before you figure out, I admit that I am also blowing my own horn in the first item of the list, but it's not without a reason. MS BI Project Booster Kit lists some very useful resources that can help you in your MS BI project. Read the article and you would agree with me.

1) Microsoft Business Intelligence Project Booster Kit

2) Free SQL Server Spatial Resources

3) SQL Server 2008 R2 Resources - Free Books, articles, webcasts, e-learning courses, and downloads

4) Free SQL Server Tools

Monday, April 19, 2010

Bing Maps Server and SQL Server 2008

I'm reading: Bing Maps Server and SQL Server 2008Tweet this !
Bing Maps layer or Bing Maps Webservices sounds like familiar terms, but the term that I have used in the subject line of this post "Bing Maps Server" is not a typo at all. Microsoft Virtual Earth Webserver is now known as Bing Maps Server. This is definitely a news, at least for me.

The next question which immediately strikes one's mind is what's is its use when we already have features like Bing Maps Layer in SSRS or Bing Maps Webservice for using in SSIS which I have explained in my BI-Satellite project. The answer to this is a question, can you use Bing Maps service without an Internet connection ? This server facilitates the same service for a offline or on-premise based requirements where one needs this features on a restricted or private server instead of querying data each time over the web. And this data is stored in SQL Server 2008. So this completes the circle.

An obvious question is who has got such requirements. Well, this world is quite huge and equally varied are the kind of requirements. Security agencies might not want their investigation data being tracked by some nasty hacking networks and might want to carry out their proprietary analysis within a closed premise. This product can be useful in such scenarios. BTW, this product is exclusive provided by Vexcel Corporation which states itself as the Microsoft's geospatial subsidiary.

Sunday, March 28, 2010

SSIS with Spatial / Geospatial Data

I'm reading: SSIS with Spatial / Geospatial DataTweet this !
Mind's biggest strength is it knows no barriers / boundaries and this is also mind's biggest weakness. This sounds highly philosophical, so let's go to something technical with the same philosophy. One of the area of data which knows no boundaries is Spatial data, as any point on this earth across geographical boundaries can be plotted with pin-point precision.

Like a salesman, when it comes to something new, my mind immediately starts selling the concept of how SSIS can be sold even in this side of technology and business. Before data lands down to SQL Server in geography or geometry data types, this data can be found in different formats which different applications generate or use. For example, Well Known Text ( WKT ), Well Known Binary ( WKB ), Keyhole Markup Language ( KML ), Geography Markup Language ( GML ), Data Geocoded in the form of latitudes and longitudes are all parent compounds of geography or geometry datatypes, which we eventually can convert to store or use in SQL Server. Also there can be a need for mutual exchange of data from SQL Server to other formats.

In these cases, apart from the CLR functions that can be used with spatial data types, SSIS has the possibility of playing a major role in crafting the conversions from one data type to another. Google maps still use KML and Microsoft Virtual Earth uses GML to the best of my knowledge. Think of the possibility where one needs a conversion directly from GML to KML without staging in SQL Server. In this case SSIS and SQL XML can definitely play a big role. SSRS has also got great support with maps which also needs spatial data to fuel itself.

And those who are curious to know how interesting can be exploring and working with spatial applications, just explore the GeoSpatial Data Generator application designed by Mike Ormond.

Thursday, March 11, 2010

DB Pro features not available in VSTS 2010 RC Professional Edition

I'm reading: DB Pro features not available in VSTS 2010 RC Professional EditionTweet this !
Today was a very very embarrassing day for me when I learned a lesson that how attention to detail can sometimes make your life very easy. Visual Studio Team System (VSTS) RC 2010 comes with all the DB Pro tools integrated into it. I was working on one such installation, and SQL Server 2008 was installed on the same machine.

I felt the need to compare two databases and I thought of trying out the Compare Schema feature. Surprisingly I was not able to find it out at all, and then my real issue started. I did all I can to figure out what would be the real cause of the same, and I also felt into a suspicion that, it can be the case that SQL Server 2008 R2 might not be supported by this tool like it was with SQL Server 2008 in the GDR 2 release. Out of my impulsive nature, I also posted a query on the forums.

Finally, I figured out a small point which solved this entire puzzle. The installation was of "Professional" edition !! You might not be able to figure out yet, what it has to do with this issue. My feeling is that VSTS 2010 Professional Edition is almost a paralyzed edition for DB development using VSTS. If you compare the database development features available in Professional vs Premium/Ultimate edition, almost no features of DB Pro are available. Check out the feature comparison between these editions from here.


Sunday, March 07, 2010

Visual Studio 2010 ( for Database Professionals ) compared to SSMS

I'm reading: Visual Studio 2010 ( for Database Professionals ) compared to SSMSTweet this !
I have been recently using Visual Studio 2010 RC, and the first question that would strike your mind if you have worked on the previous version is, "Are you working on VS 2010 for DB Professionals" ? The answer to this question is that, it seems that Microsoft has galvanized VSTE 2008 and VSTS 2008 for DB Professionals into a single integrated release in VSTS 2010.

This brings an added advantage to both the .NET developers and DB developers community. But I see it more advantageous to the .NET community, as SSMS has not done the same level of feature addition as VSTS team. If you go thru the feature list, you would find that entire toolset of DB Pro (Data Dude) is available now in VSTS 2010, but the same tools are more required for folks who work with SSMS, and the same is not available in SSMS. For example the "Compare Schema" feature of DB Pro/Data Dude is available in VSTS 2010 now but as a DB professional I would like it to be available is SSMS as I need to use it more often from there, than from VSTS. An almost full fledged query editor like the one we have in SSMS, is available right from VSTS. But the diagramming capabilities in SSMS are still not as strong as the ones availalbe in VSTS 2010.

As per my opinion, VSTS team has done a great job of fertilizing their platform by borrowing features from SSMS or SMO, but SSMS team has still got a great scope of improvement when compared to VSTS 2010. You can download Visual Studio 2010 RC from here.

Sunday, February 21, 2010

SQL Server 2008 and MS BI Setup for learning and training - The Cheap Way is VPC and VHD

I'm reading: SQL Server 2008 and MS BI Setup for learning and training - The Cheap Way is VPC and VHDTweet this !
Professionals who work in countries with stronger economy have an invisible advantage over professionals who come from countries with poorer economies - being able to buy or arrange licensed software, hardware and training content. Mostly those who are focused enough are able to self-learn technology these days, as in my belief, information is the cheapest available thing available over the web today and trial versions keep life easy for technology aspirants.

Hardware and software are two of the challenges that developers face in learning new technologies which demands huge resources in terms of hardware and installation know-how. Also a technology which you want to try out might not just have an operating system dependency. There might be lot of supporting software components which might be required to be setup before you can use the product that you want to install and work with. Luckily that is not the case with SQL Server but this is definitely the case with Sharepoint. Also it might come as a surprise, but many developers would not have personal laptops or machines upgraded enough to handle SQL Server 2008 installation. And if you want to develop and test some solution on the same with something like Performancepoint Services (which would require Sharepoint) installation, most of the developers would leave even the idea of thinking about it.

Here is one way that one can still continue to fulfill their dreams of working on a full fledged environment without great investments over hardware or knowhow of installation. Microsoft offers downloads of Virtual Hard Disks which comes with pre-installed softwares for a few products and it comes for free. One should just have a Virtual PC installed and attach this VHD to it. Below are the VHD available which you might feel interesting:


And if your requirement is a full install of all the MS BI related softwares, go for the MS BI VPC. Latest release in my knowledge as of this draft is version 7.1 and the links are available from here. This would save you from the pain of arranging high end hardware, installation know-how and other resources.

Monday, February 08, 2010

Download SQL Server 2008 R2 Videos , Tutorials , Presentations , Demos and Labs

I'm reading: Download SQL Server 2008 R2 Videos , Tutorials , Presentations , Demos and LabsTweet this !
SQL Server 2008 R2 is one of the most important milestone release in the BI history of SQL Server. It brings also with it, new feature/release like Master Data Services , StreamInsight, Powerpivot and some very interesting and useful enhancements to Reporting Services.

Many of us do pay for training on the new content, and hunt for information in the form of videos, tutorial and guides. Microsoft has made this easy for us to learn the new features of R2 release, by providing a SQL Server 2008 R2 Update for Developer Training Kit. This kit is full of resources you need for learning almost all the new features of R2 release as well as SQL Server 2008 also.

Start learning and get ready for R2 from this kit, as R2 official release is going to hit the market in May 2010. Make your CV and yourself R2 ready, and use this kit as an exercising platform for the same.

Tuesday, February 02, 2010

Download Free SSMS Add-in to search for any text within your database objects

I'm reading: Download Free SSMS Add-in to search for any text within your database objectsTweet this !
I am a collector and admirer of free add-ins and I have been a admirer of SSMS Tools Pack. But after that, there are not many great add-ins available for SSMS. A handy-search is always a nice feature to have in any development environment, and even this holds true for database objects too.

SSMS Tools pack provides great features for search within the results returned by your query. But if you want to search for any particular text within your database objects, it required developing some form of scripts to query some sys tables. Redgate software has come out with a FREE SSMS ADD-IN the is very simple to use, very efficient in text search across the database server. It has a very nice GUI, much more granularity and details that what you get in SSMS search and the it's of a pretty small size (less than 3 MB) download.

I would say that this is the next best SSMS freeware utility that I have seen after SSMS Tools Pack. This add-in in called SQL Search and it's still in Beta, but final version is expected to just add some more details on the help feature, thou I don't think any help is required to learn on how to use this add-in. Download it and make your life more easy with your database development.

The only feature missing in this add-in that I would wish to have, is the ability to save this search or export the results of this search to Excel. At least it should allow to select the results from this search and copy it out. Presently it just allows to select one row at a time, so if one wants to select all the results and copy it to Excel to create an Excel report out of it, it's not possible.


Tuesday, December 01, 2009

XML AUTO option vs Tabular Data Stream ( TDS ) in SQL Server

I'm reading: XML AUTO option vs Tabular Data Stream ( TDS ) in SQL ServerTweet this !
Comparative study is one of the best ways to benchmark, compare and conclude right option for the right job. I came across an interesting comparative study of size of data that gets created by results returned thru TDS compared to size of data that gets created by FOR XML AUTO option for the same piece of data.

Even to my surprise, there is a huge difference between the two options when this size of data is to be accessed over the network. So when you expect huge resultsets to be returned, you might want to think twice by looking at the results in the below image (Image courtesy: SQLServerPedia.com). It's not that XML option is not good, it has it's own place and set of requirements that it caters. But while designing your architecture, this piece of comparative study is worth considering and it can also prove helpful to carry out a Proof-Of-Concept ( POC ) before considering this option in your low-level designs.

Original article can be read from here.

Thursday, November 05, 2009

Free E-book : Sharepoint for SQL Server Database Administrators ( DBA )

I'm reading: Free E-book : Sharepoint for SQL Server Database Administrators ( DBA )Tweet this !
Below is the excerpt from the Microsoft Technet site, where I found the below information regarding the free download of this e-book:

This book is a guide for database administrators who implement or maintain Microsoft Office SharePoint Server 2007. It provides guidelines for improving the performance of Office SharePoint Server 2007 solutions that run on Microsoft SQL Server 2008. It also provides information about key considerations for configuring storage and SQL Server 2008 before installing Office SharePoint Server 2007; the required steps in installing Office SharePoint Server 2007 when using DBA-created databases; backup and recovery considerations for Office SharePoint Server 2007, including what can and cannot be protected by SQL Server 2008 backups; and maintaining and monitoring SQL Server 2008 databases that support a Office SharePoint Server 2007 implementation.

The content in this book is a copy of selected content in the Office SharePoint Server technical library (http://go.microsoft.com/fwlink/?LinkId=84739) as of the date above. For the most current content, see the technical library on the Web.

Download this book as a Microsoft Word document (.docx) (http://go.microsoft.com/fwlink/?LinkId=168786).

Download this book as a PDF file (http://go.microsoft.com/fwlink/?LinkId=168793).

Sunday, October 04, 2009

Data Cleansing Tutorial : What is Data Cleansing

I'm reading: Data Cleansing Tutorial : What is Data CleansingTweet this !
I have worked on data migration projects where I have been a part of data cleansing activities using SSIS. One of the reader of my blog, requested me to post some tutorials on Data Cleansing. So based on my experiences, I would like to answer a few questions on Data Cleansing. The point of interest is "What is Data Cleansing" and then the next question is "How can Data Cleansing be commenced and concluded" ?

When is Data Cleansing required ?

Generally Data Cleansing is required in a systems integration flavor of project. For example when different systems which might be logically related like back-office and front-office systems, or non-related systems like different business application that might be independent in its own respect but functioning under a common umbrella. In both these examples one thing is common, these systems operate differently and are subject to integration.

What is Data Cleansing ?

Data is the above mentioned systems are not always as expected, or the data is not of the quality that is required to be fed into new system. For example, there can be OLTP applications or front-office systems where data is not fed in the precise manner due to operator error or limited system design. So these data issues needs to be fixed, mostly in the host or intermediate system which is mostly effective to the requirements of the new system where data of a particular quality level is required.

How to do Data Cleansing, using SQL Server in specific ?

There are different ways of doing Data Cleansing, and there is not particular theory or formula to it, in the best of my knowledge. But one thing that applies universally to any data cleansing exercise is, the data should be profiled thoroughly. Thorough data profiling is directly proportional to level of data cleansing. Mostly the hard part is data profiling, than data cleansing. It takes more business knowledge combined with technical analysis of the data for profiling. Once the details about what to fix in the data is catalogued, it's generally not that hard to fix those issues using ETL tools like SSIS. SSIS comes with a dedicated task for Data Profiling and a dedicated tool for viewing the profiled data.
Related Posts with Thumbnails