MS BI Architecture Design Layers - classifying layer specific logic

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 !!

Using a scheduler in the technical design of your BI solution

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 !).

Planning MS BI shared environment for production systems

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.

SSRS 2005 to SSRS 2008 R2 Migration Strategy , SSIS 2005 to SSIS 2008 R2 Migration Strategy, SSAS 2005 to SSAS 2008 R2 Migration Strategy

Product edition upgrade and migration of solution artifacts from lower to higher edition is quite a challenge and needs careful planning. The more experience you have on different migrations, the more you would have anticipation of possible problems for migration. However deep may be one's experience, data and platform migration is one such area where one can always expect surprises. Everyone has a first time, and in migration you would want to be sure that you have all the supporting tools and some higher level strategy in mind to design your migration. Below are some guidelines which I had found useful in my career experiences.

1) Firstly collect all the tools, at least freewares that can help you in your migration analysis. SQL Server 2008 R2 ships with SQL Server Upgrade Advisor, which can be the best starting point. This tool is also a part of the SQL Server 2008 R2 Features Pack. You can learn more about the same from here. This tool covers all areas, right from database engine till SSAS.

2) When you start your design, you would have to make a clear distinction between whether you want to perform an in-place upgrade or create a new instance -> deploy solution on the new instance -> ensure synchronization between old and new instance -> abandon old instance. Check out this article for some more info.

3) You should keep in view where you plan to do the upgrade, i.e. on the same box, in the same domain, or across different servers and different domains. This would throw up the challenge of security configuration.

4) Environment configuration needs to be planned for each service separately. For example, SSIS packages can be expected to use configuration settings from different sources like environment variables, configuration files, database and other sources. SSRS configuration might reside in config files for reports server as well as reports manager. Virtualization is the key factor is testing all such scenarios.

5) Finally the biggest risk factor needs to be calculated, i.e. identifying the right sampling to test on the targeted edition. SQL Server 2005 came with it's first mature BI offering. Several components of different services have undergone architectural changes, several features are discontinued, several features have behavioral changes and several features are guaranteed to break when migrating from lower to higher editions.

a) Deprecated Features in SQL Server Reporting Services

b) Discontinued Functionality in SQL Server Reporting Services
c) Breaking Changes in SQL Server Reporting Services
d) Behavior Changes in SQL Server Reporting Services

I prefer creating out a consolidated list of these features. Then all the reports should be analysed to check if any reports have used these features, which would mean that these reports qualify to be considered as a sample to test on the targeted edition. This sampling exercise would not only generate right size of samples to test, but also the same sample would act as the Acceptance Testing Procedure.

Generally production environments are handled by operations team, and they remain in charge of migration too. Development teams need to confirm whether migration was successful and works as expected. The successful functioning of sampling identified from the above exercise would act as the Acceptance Testing routine, which is a contract that needs to be agreed between development and operations team in advance before migration is performed. Keep in view, that this exercise needs to be performed for each service individually - DB Engine, SSIS, SSAS and SSRS.

It's a very brief list, but these points can at least help you align your strategy in some direction when you are totally blank on how you would plan your migration. If you have better tips that can add value to this post, please feel free to share your comments.

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

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.

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

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 !!!

Free SQL Server Ebook : Defensive database programming with SQL Server

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

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

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

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

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.

Reference : SQLServerCentral (Aloha DBA)

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

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.

Different ways of importing Data into SQL Server for Data Migration

There are many ways of importing data into SQL Server, depending upon the source from where data is to be imported. When working with legacy systems or non-relational systems like SAP, CRM applications and Mainframe systems, the source is generally text files or feeds which are in textual format. Professionals having knowledge of SSIS can import data with more fine tuning.

All the different ways of importing data, with the caveats of each of the methods are listed on an article that I found on lessthanadot.com. I had the opportunity to place the issue of date conversion with Import Export wizard to the database professionals, and this post is one of the best places where this issue can get it's deserved attention. This article would be of most use specifically in a SSIS Data migration project, where you need to put all the options on the table for importing data from source to SQL Server using SSIS as a part of migration strategy.

It's a useful post and can come handy while thinking of options in the way one would want to import data during the technical design planning of any solution. The article also provides the statistics of the test results for various volumes of data with all the methodologies followed for importing the data. If the author would had specified the hardware configuration used and with a little bit change in proforma, it would had been a nice tiny whitepaper in itself. Still nice article worth reading along with comments.

Free Ebooks , Study Guide and Interview Questions on SQL Server Security, Performance and Administration

Another Ebook bundle from Red-Gate.com containing some of the best technical material of SQL Server 2005 and SQL Server 2008 for free. It also includes one of the must have books for those preparing for a SQL Server developer profile interview. Mind it, it doesn't contain an major SSIS, SSAS or SSRS interview questions, except a few questions on SSIS, SSAS, and SSRS questions which may be less than 10 to 15 collectively in number out of the total 100 interview questions.

Ebook on SQL Server Execution Plans (Dissecting SQL Server Execution Plans.pdf)
Takeaway: In depth technical explanation on SQL Server Query plan execution

Ebook on SQL Server XML Schema Collections (The Art of XSD.pdf)
Takeaway: Technical content to master your skills on usage on XML and XSD with SQL Server

Ebook on SQL Server Interview Questions (SSC Stumper Vol 5.pdf)

Takeaway: Interview questions, answers and links to reference material put together by Red Gate and SQLServerCentral.com. It's one of the best SQL Server Interview Material, that I have came across on the web which is available in such an informative and structured manner for free.

Ebook on SQL Server Security and Encryption (Protecting SQL Server Data.pdf)

Takeaway: Best technical material available on the web for free on SQL Server Security and Encryption.

Download Courtsey: www.red-gate.com

Download Free SQL Server 2005 Ebooks , Download Free SQL Server 2008 Ebooks

RedGate has a very unique way of promoting it's product range of SQL Server tools and utilities, and I think that it's working quite well too. RedGate is giving out a free short ebook with a few of its tools, and also makes a few ebook bundles available for free on SQL Server.

This marketing strategy is keeping this company closer to the SQL Server community of professionals and also it shows how well this company has reach and insight into the product. Though I have not used many of SQL Server tools, as they are more concentrated on a DBA or Developer level of profile, and I work more onto the Business Intelligence side of SQL Server, but I am confident that they are one of the healthy market competitors.

Below is one of the EBook Bundle available for free from RedGate.

Free Ebook on DBA Best Practices (DBA_Best_Practices_ebook.pdf)

Takeaway: A kind of cramsheet of best practices for a SQL Server DBA

Free Ebook on SQL Server 2008 New Features (Brads Sure Guide to SQL Server 2008.pdf)

Takeaway: Higher level introduction to almost all the new features of SQL Server 2008 with examples

Free Ebook on Mastering SQL Server 2005 or SQL Server 2008 - Profiler Tool (Mastering Profiler eBook.pdf)

Takeaway: In depth technical content on the Profiler Tool

Free Ebook on How to be an Exceptional DBA by Brad McGehee (Exceptional DBA 2ndEd.pdf)

Takeaway: How to grow your image and career as a DBA, with complete focus on softskills

Free Ebook on SQL Server Tools and How to do with SQL Server (SQL_Server_Tacklebox_Ebook.pdf)

Takeaway: A SQL Server cookbook that shows how to use different SQL Server features and tools for different purpose

Download Courtsey: www.red-gate.com

Database Modelling Reverse Engineering , LINQ to SQL - Entity Developer Express

One of the things that I have learned and experienced in my career is, IT cannot work in isolation. One needs to be versatile to understand and learn technology, to grow big. It need not be a universal truth, but it has been my career experience.

Coming to the point, there is new tool out called Entity Developer Express. This is a tool not exactly for MS BI or SQL Server development or maintenance. But I am listing it as it's foundations are laid on SQL Server. While working on projects that have DotNet, SQL Server and/or Microsoft Business Intelligence features involved, this kind of tools makes a developer life easy.

I have neither evaluated this tool, nor I am going to do it as presently I am working on system integration project involving SAP and SSIS. So to be honest, no time and less interest to evaluate it.

The description on the product page is quite sufficient to describe itself, which is as below:

Entity Developer is a free of charge powerful modeling and code generation tool for ADO.NET Entity Framework and LINQ to SQL. You can design an entity model from scratch or reverse-engineer an existing database, in which case you will have the classes and the relations with just several clicks. The model is used to generate C# or Visual Basic code with flexible predefined code templates. You can also customize the templates to fit all your needs. Entity Developer for SQL Server uses standard Microsoft SqlClient, LINQ to SQL, and Entity Framework, all of them bundled with .NET Framework 3.5 SP1.

Key Features:
- ADO.NET Entity Framework support
- LINQ to SQL support
- Visual schema modelling
- Customizable and intuitive user interface
- Reverse engineering (database object to entity model)
- Well-thought predefined templates for class generation
- GUI for predefined templates
- State-of-art diagram with zooming
- Diagram overview and printing
- Undo/Redo support
- One-click help system
- Visual Studio is not required
- Free of charge

SQL Server Performance Webcasts

• Top Performance Counters for Analyzing SQL Server Performance Issues using Performance Monitor


• SQL Server 2008 Performance Management Data Warehouse


• Learn how to migrate your DTS packages to SSIS


• SQL Server Security and Compliance

SQL Server Tools : Qure Product Review

Today I received an email from Ami Levin. He is a SQL Server MVP and having an experience of working more than a decade on SQL Server. He introduced me to a product called "Qure".

I went through the product information page of Qure, and found that it focuses on 4 areas for SQL Server Performance Optimization. They are as below:

1) Smart Indexing - Create, Drop and Modify Indexes for Best Overall Performance

Details can be found on product page.

My View: The product page says that Qure has a patented algorithm which they call as Smart Indexing, which suggests code and places where indexes should be changed. I would like to review and compare, how better is this than Index Tuning Wizard or Database Tuning Wizard results. If it's better than that, may be Microsoft should have review theirs.

2) Query Syntax Optimization

Details can be found on product page.

My View: This is one of nice feature which I like, provided it results in better performance. Generally it's not feasible to change lots of code in production, as there are a lot of approval cycles that goes on. And if just for syntax if I am asked for an approval of modifying 100 objects, I won't approve it. But for example changing 35-50 odd objects can reap me at least 5% performance gain, I would be more than happy to see it happening.

By using a kind of work-around on DBPro this can be achieved. But it won't tell you the performance gain.

3) Schema Optimization

Details can be found on product page.

My View: This feature is like combining data profiling capabilities of SSIS with Database Tuning Advisor. This really makes me curious of exploring, how effective this feature is. If it works to my expectation, it can revolutionize database modeling.

4) Additional Optimizations

Details can be found on product page.

My View: I would like to compare performance of this feature with that of Best Practices Analyzer tool.

By and large, it seems like this is a big thing in a small package. Pricing of this product in alignment with the features offered seems to be a high, compared to Quest software products. Allow me some time to evaluate and compare it with other tools and I would be back with results shortly.

Performance Tuning SQL Server Data Types : SQL Server Interview Questions

Selecting inappropriate data types, especially on large tables with millions or billions of rows, can have significant performance implications. Recently I found an article that discusses data type performance to a decent depth. I believe this article would be very beneficial for starters who are new to SQL Server 2005 / 2008. This article can also be a very handy reference for SQL Server Interview questions for data types.

Reference: PASS Performance SIG

Performance Counters Analysis : SQL Server , IIS , MOSS , BizTalk , Exchange , and Active Directory

Logs are one of the fundamental resource where data profiling and collection starts for performance tuning. Reading logs which can exist in a variety of formats starting from text files to Windows Event Logs, and then aggregating information and making sense out of it is not an easy task. This can become more tedious if your job is to keep a constant watch in production environment thru logs.

In case of SQL Server there are two tools that can be of use, if you are dealing heavily with logs. Log Parser Tool and Performance Analysis of Logs Tool. Mentioned below are the description of the tools from their respective project sites:

Log Parser

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

PAL Tool

Ever have a performance problem, but don't know what performance counters to collect or how to analyze them? The PAL (Performance Analysis of Logs) tool is a new and powerful tool that reads in a performance monitor counter log (any known format) and analyzes it using complex, but known thresholds (provided). The tool generates an HTML based report which graphically charts important performance counters and throws alerts when thresholds are exceeded. The thresholds are originally based on thresholds defined by the Microsoft product teams and members of Microsoft support, but continue to be expanded by this ongoing project. This tool is not a replacement of traditional performance analysis, but it automates the analysis of performance counter logs enough to save you time. This is a VBScript and requires Microsoft LogParser (free download).

PAL Tool Features

• Thresholds files for most of the major Microsoft products such as IIS, MOSS, SQL Server, BizTalk, Exchange, and Active Directory.
• An easy to use GUI interface which makes creating batch files for the PAL.vbs script.
• A GUI editor for creating or editing your own threshold files.
• Creates an HTML based report for ease of copy/pasting into other applications.
• Analyzes performance counter logs for thresholds using thresholds that change their criteria based on the computer's role or hardware specs.

SQL Server Standards , SQL Server Naming Conventions , SQL Server Coding Guidelines

Generally once a project kicks off, general guidelines and standards document is treated like a de-facto document for each project (in order to save the project from getting jinxed by ignorant knowledge workers :) ). If you are looking for any standards and guidelines document from the below list, here is the SQL Server Standards Document for download.

SQL Server Naming Conventions and Standards

• Databases, Files, and File Paths
• Tables and Views
• Columns
• Indexes
• Stored Procedures
• Triggers
• Variables

SQL Server Programming Guidelines

• Introduction
• Code Readability and Format
• Datatypes
• Stored Procedures
• Performance Considerations
• Miscellaneous Topics

SQL Server Security Model

• General Access Requirements
• SQL Server Roles

SQL Server Migration

• RACF Requirements
• Development Environment
• Production Migration

SQL Server Execution Environment

• Non-Web Applications
• Web Applications

SQL Server Performance Auditing , Profiling , Monitoring , Logging , Troubleshooting Tools

There are a handful of monitoring tools from Microsoft includes Profiler, Sysmon\Perfmon and the Database Engine Tuning Advisor\Index Tuning Wizard which are already well-known.

Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server. This is similar to Task Manager for Windows that provides overview displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second), which is shown with graphical representation. Also the Active User Tasks, Resource waits, Data file I/O and recent expensive queries.

Using SQL Server Management Studio (SSMS) you can monitor the current activity of all defined jobs on an instance of SQL Server by using SQL Server Agent Job Activity Monitor.

You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures.

Replication monitor provides details on the status of SQL Server replication and allows you to configure replication alerts.

SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction, enabling you to monitor server and database activity (for example, deadlocks, fatal errors, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on SQL Server step by step, to see exactly what happened.

Monitoring the logs is also another tool to provide you the lead to see the activity on the SQL Server, the information in these event logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server problems.

In addition to the SQL Server error log Windows application/system/security event log provides an overall picture of events occurring on the Windows Server and Windows operating systems as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search. It contains information about events in SQL Server that is not available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems.

The information in SQL Server Agent Logs allows you to view informational, auditing, warning, and error messages that can help you troubleshoot SQL Server Agent problems.

The best way to get server state, Dynamic Management Views & DMFs return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions: Server-scoped dynamic management views and functions that requires VIEW SERVER STATE permission on the server. Database-scoped dynamic management views and functions that requires VIEW DATABASE STATE permission on the database. All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema.

DataBase Console Commands (DBCC) are commonly used statements by every DBA and many times the users whenever a problem is repoted, these are grouped into 4 categories: Maintenance, Informational, Validation and Miscellaneous.

For many administrative and informational purpose the stored procedures that consists in SQL Server system database 'master'. Few of these are listed below that are commonly used in day-to-day monitoring activity, such as sp_helpdb, sp_helpserver, sp_spaceused and sp_who or sp_who2 (undocumented). There are many more system and DB engine related stored procedures that are used for general maintenance of an instance of SQL Server.

Reference: Above article is a summarized excerpt of the original article on SSQA.net