Arshad Ali

Standard Report Subscription in SSRS 2008 R2

2012-01-19T06:29:00.000-08:00

You can set the subscription for the report in which case report server processes the subscription, generates the reports and delivers it to its intended audience via email delivery or file share delivery on the defined schedule, to learn more about it click here.

Understanding SSRS Report Definition Language (RDL)

2012-01-10T10:17:00.000-08:00

SSRS reports are defined in Report Definition Language (RDL), which is nothing but Extensible Markup Language (XML) adhering to a defined report definition language schema. When we use Report Designer or Report Builder to create reports, it defines the report in Report Definition Language (RDL), which is an XML representation adhering to a defined report definition language schema (defines where an RDL element should exist), for more information click here.

Report Snapshots in SSRS 2008 R2

2012-01-10T10:10:00.000-08:00

You can set SSRS to create report snapshots, which show data at a point of time or you can subscribe to your published reports.

An SSRS report snapshot represents a report that contains data retrieved at a specific point of time along with layout information in the form of intermediate rendering format. SSRS allows you to create report snapshots on defined schedule or on demand whenever you need to; a report snapshot is stored in the ReportServer database, for more information click here.

Report Caching in SQL Server Reporting Services 2008 R2

2012-01-10T10:09:00.000-08:00

SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free form reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in a variety of formats. You can specify to create report snapshots, which show data at a point of time or you can even subscribe to your published reports.

To improve the performance of report processing, SSRS lets you enable caching for the report so that if the same report request comes again, the stored copy can be rendered in the desired format and served instead of processing it from scratch, for more information click here.

Creating and using Report Parts in Report Builder 3.0

2012-01-10T09:03:00.000-08:00

Report Builder 3.0, which comes with SQL Server 2008 R2, is a new enhanced report development and report authoring tool intended to be used by business users for ad-hoc reporting. Report Builder 3.0 has numerous new features and one of them is Report Part Gallery, which allows you to create and publish report parts and reuse them in other reports. In other words, you can publish different report parts on the report server and reuse them in different reports when required, without recreating them from scratch, for more information click here.

Getting Started with SQL Server Report Builder 3.0

2012-01-10T08:56:00.000-08:00

SQL Server Reporting Services (SSRS) has a report development/authoring tool called Report Designer (Business Intelligence Development Studio) for creating managed reports. Though Report Designer has an intuitive interface for creating simple to complex reports, it can be a little complex for business users who want to create ad-hoc reports. SQL Server Reporting Services has a separate tool called Report Builder (separate downloadable) created especially for business users to do ad-hoc reporting.

Report Builder 1.0 first came with SQL Server 2005 and has grown up with each subsequent release. We are going to deep dive with Report Builder 3.0, which comes with SQL Server 2008 R2. For more information click here.

Getting Started with Utility Control Point in SQL Server 2008 R2

2012-01-10T08:51:00.000-08:00

In my last article I talked about SQL Server Utility, Utility Control Point, what it is, how it helps SQL Server DBAs in multi-server administration, and finally I demonstrated creating Utility Control Point step-by-step.

To learn more about enrolling multiple SQL Server instances in an already created UCP for health data collection and monitoring purposes and how to administer SQL Server Utility and Utility Control Point, click here.

Understanding Utility Control Point (UCP) in SQL Server 2008 R2

2012-01-10T08:48:00.000-08:00

As your business grows, the number of applications grows as well, as do the SQL Server instances to support these applications. As a SQL DBA, you need to have a multi-server management dashboard that proactively tells you about the resource utilization on each SQL Server instance.

SQL Server 2008 R2 introduced the SQL Server Utility and Utility Control Point, which lets you have a consolidated dashboard-type view of resource utilization on all the servers in your multi-server environments. It helps SQL DBAs to proactively monitor their SQL Server instances' resource utilization, for more information click here.

Managing and Monitoring SQL Server Service Broker Environments

2012-01-10T08:42:00.000-08:00

Service Broker applications run in the background. You send a message and your command returns immediately. In the background, Service Broker keeps on trying to send the message to the destination service (Queue) until it puts the message there, times out or you end the conversation. These things all happen transparently to you and your sending application. So how would you troubleshoot your Service Broker applications if they're not working as expected, and how would you identify if something goes wrong? For for information click here.

Understanding Sequence Object in SQL Server Denali

2012-01-10T08:34:00.000-08:00

Until SQL Server 2008 R2, the Identity column was used to generate sequential numbers to identify records in a table, mostly used as a primary/foreign key. The scope of the identity column is the table on which it has been created, and the next sequential number is created when the DML statement is executed. But what if you want to have sequential generation of numbers across tables (instead of tying the numbers with just one table), and you want to have the next sequence number even before execution of the DML statement? SQL Server Denali has a new feature called Sequence object for these purposes, while retaining Identity column functionality too, for information click here.

SQL Server Service Broker Security

2011-12-14T05:42:00.000-08:00

When we talk of communicating messages back and forth between Initiator and Target we need to ensure that communication is secure and no one on the way can temper with messages being sent. So what are the security features available for Service Broker to ensure secured communications?

To explore it click here.

Understanding SQL Server Service Broker Authentication

2011-12-14T05:39:00.000-08:00

Service Broker endpoint supports both Windows Authentication and Certificate Based Authentication. Windows Authentication is preferred if the distributed Service Broker endpoints are in the same windows domains, and Certificate Based Authentication is used if these endpoints are in two different windows domains, for information click here.

Executing a SSIS Package from Stored Procedure in SQL Server

2011-12-14T05:36:00.000-08:00

Recently we had a requirement to execute a SSIS package from one of our user stored procedures, but there is no direct way available in SQL Server for executing a SSIS package from a stored procedure. SQL Server has some undocumented system stored procedures for SSIS package management, but none is available that can be used to execute a SSIS package from the stored procedure. Now the question is, how can we execute a SSIS package from the user stored procedure? If the direct method is not available, is there any alternative for this?

Here are two different ways a SSIS package can be executed from a stored procedure. In the first approach, we will create a job, making SSIS package call a job step and executing it by calling the sp_start_job system stored procedure from the user defined function. In the second approach, we will enable xp_cmdshell to execute the DTEXEC command line utility from the user defined stored procedure, for information click here.

SQL Server Integration Services ( SSIS ) Interview Questions

2011-12-14T05:24:00.000-08:00

When preparing for a SQL Server interview, it is helpful to understand what questions may be asked related to SSIS. In this series, I will try to cover as much as I can to help you prepare.

SSIS Interview Questions - Basics Concepts, Control flow and Data flow

SSIS Interview Questions for Basic Concepts and Event Logging

SSIS Interview Questions on Transactions, Event Handling and Validation

SSIS Interview Questions on Architecture and Internals

SSIS Interview Questions for Memory Management and Deployment

more to follows....

SSIS Package Deployment Model in SQL Server 2012 - Denali

2011-12-14T05:20:00.000-08:00

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012 code name Denali. In these articles I cover what it is and how to get started to simplify your SSIS package deployments.

SSIS Package Deployment Model in SQL Server Denali (Part 1 of 2)

SSIS Package Deployment Model in SQL Server Denali (Part 2 of 2)

SSIS Enhancements in SQL Server 2012 - Denali

2011-12-14T04:18:00.000-08:00

I heard there are couple of enhancements in SQL Server Denali for SSIS. What are these enhancements and how does improves the usability, productivity, manageability and performance? Check out this articles to learn more.

SQL Server Denali SSIS Enhancements Part 1 of 2

SQL Server Denali THROW statement to raise an exception

2011-08-26T18:29:00.000-07:00

Raising exception with THROW statement in SQL Server Denali

Prior to SQL Server 2005, writing code for exception handling was painful for T-SQL developers. SQL Server 2005 introduced structured exception handling with BEGIN TRY...BEGIN CATCH blocks. Though this inclusion made managing exceptions in T-SQL programming quite easier, it has some limitations. In this tip I am going to talk about some of these challenges and limitations, then show how a new command THROW in SQL Server Denali overcomes those items, click here for more info.

Understanding Ad-hoc paging in SQL Server Denali

2011-08-26T18:27:00.000-07:00

SQL Server Denali Server Side Paging

When designing a User Interface (UI) you normally display 10, 20, 50 or 100 rows on the screen for readability purposes and easy navigation. One way to do this, is to bring all the data from SQL Server to local memory and do the navigation locally. This approach looks simple, but has a couple of issues. First, it could choke the network during a huge data transfer from the server to the local machine (or machine running UI). Second, you could need much more memory to accommodate the data locally. Third, when you navigate to the next page you are actually viewing data stored locally and you will not be able to see updated records on the server until you re-fetch all the data again. So what are some options for server side paging and how does SQL Server Denali improves on this process? Check out this tip to learn more.

Diagnostic logging in SharePoint 2010

2011-06-30T11:08:00.000-07:00

For diagnostic logging, the Unified Logging Service (ULS) has been available since previous versions of SharePoint. But what improvements and refinements have been done in SharePoint 2010? What are the options available for diagnostic logging in SharePoint 2010? How better we can control the amount of diagnostic information to be logged in SharePoint 2010? For more details click here.

Getting Started with the SharePoint Logging and Reporting Database in SharePoint 2010

2011-06-30T11:06:00.000-07:00

In my last tip I talked about diagnostic logging in SharePoint 2010 and how it helps in troubleshooting. Troubleshooting with trace files is good but wouldn't it be cool if we have all the logging (usage and health) information on single place in consolidated form which help you to build reports and do analysis on collected data from all the servers of the farm? I heard SharePoint 2010 has new logging database. What is it, what does it store? How we can control the amount of information to be logged in the SharePoint logging database? For more details, click care.

Health Analyzer in SharePoint 2010

2011-06-30T11:04:00.000-07:00

As a SharePoint Administrator you always wanted to have something in place which alerts you about the running issues or potential problems in your farm. SharePoint 2010 has come up with an 'out of the box' Health Analyzer feature which alerts you about the potential problems, and the locations where they occurred, the cause and how to resolve these issues. Let's explore it more.

Getting started with Code Snippets feature of SQL Server Denali

2011-06-30T10:59:00.000-07:00

In SQL Server Denali under Manageability enhancements, code snippets feature has been introduced. In this tip I am going to talk about what it is, how to use it and if required how can we customize/add code snippet (script template) as per our need, for more details click here.

Short-cut keys for commonly used tasks in SSMS - Part 2

2011-06-30T10:56:00.000-07:00

We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more...click here

Short-cut keys for commonly used tasks in SSMS - Part 1

2011-06-30T10:50:00.000-07:00

As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys, for more details click here.

Understanding SQL Server Net-Libraries

2011-06-30T10:47:00.000-07:00

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? SQL Server provides different Net-Libraries, known as Dynamic-Link Library (DLL), to let clients communicate with SQL Server using different network protocols. These Net-Libraries specify which networking protocols to use while exchanging data back and forth between SQL Server and the clients. To learn more about it click here.

Service Broker and Poison Message Handling

2011-04-02T08:15:00.000-07:00

Have you ever figured out why your Service Broker queue gets disabled automatically? What causes your Service Broker queue to get disabled in the first place? What is a poison message with respect to Service Broker? Is there anything new in SQL Server 2008 R2 for managing poison messages in Service Broker? To explore it more click here.

SQL Server 2008 Service Broker - Conversation Priorities

2011-04-02T08:11:00.000-07:00

In a recent article I talked about creating Service Broker application and communicating between Initiator and Target. Service Broker ensures Exactly Once in Order delivery for messages inside a conversation. But what if you want some important messages/conversations (with higher priority) to be processed earlier than messages/conversations with lower priority? Is there any way to define priority in sending or receiving messages in Service Broker? To learn more click here.

SQL Server Service Broker - External Activation

2011-03-07T23:08:00.000-08:00

SQL Server Service Broker allows for setting up two types of activation, Internal Activation or External Activation. To handle increased message traffic, in internal activation you specify a stored procedure (multiple instances might be created depending on your setting) to be called and this way you scale up your distributed application, whereas in case of external activation Service Broker sends notification (QUEUE_ACTIVATION event) to an external application/program outside SQL Server to read the message from the queue and process it. This way you actually scale out your distributed application. External activation allows putting heavy weight processing logic outside SQL Server in a separate process than SQL Server which gives better performance and scalability or might run under different credential than the SQL Server service account.

In my last article I talked about setting up internal activation, writing a stored procedure which will be called upon on activation. In this article, I will be talking about external activation in detail. For more details click here.

Service Broker and Poison Message Handling

2011-03-07T23:05:00.000-08:00

Have you ever figured out why your service broker queue gets disabled automatically? What causes your service broker queue to get disabled in the first place? What is a poison message with respect to service broker? Is there anything new in SQL Server 2008 R2 for managing poison messages in Service Broker? To explore it more click here.

Understanding SQL Server Net-Libraries

2011-03-07T23:03:00.000-08:00

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? To learn about it click here.

SQL Azure - Getting Started With Database Manager

2011-03-07T22:55:00.000-08:00

The Database Manager for SQL Azure is a lightweight, easy to use, thin web-based database management tool for connecting to a SQL Azure database and managing different database management tasks like; creating and editing tables, views and stored procedures, editing table data, writing and executing T-SQL queries, etc...for more information click here.

SQL Server Service Broker – Internal Activation

2011-03-07T22:52:00.000-08:00

SQL Server Service Broker allows you to create activation stored procedures, which are called automatically (by Service Broker - Queue Monitor) whenever a message arrives in the queue (very much like a trigger but not exactly the same). Not only this, you can specify the number of instances of the stored procedure to be created to process messages from different conversation groups in parallel if the queue is overloaded with the arrival of lots of messages. In this article, I will be talking about setting up internal activation and writing a stored procedure, which will be called on activation, for more information click here.

SQL Azure - Creating backups and copies of your databases

2011-01-26T10:21:00.000-08:00

As a DBA you always followed a practice to back up your database (or take a snapshot of your database) before making any changes so that you can revert to your old database state if something goes wrong. Also to setup a development or test environment you use a backup of your database and restore it in the respective environment. If you are moving to SQL Azure, what would you do in these cases as backup / restore and database snapshots are not supported as of now? For more details, click here.

Writing SSBS Applications Across SQL Server Instances - Getting Environments Ready

2011-01-26T10:17:00.000-08:00

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is, how it works, its different components and how are they related to each other. Then I talked about writing SSBS application when both Initiator (Sender) and Target (Receiver) are in same database and SSBS application when both Initiator (Sender) and Target (Receiver) are in different databases on the same SQL Server instance. Now let’s move on and see how to write an SSBS application when the Initiator and Target are in two different databases on two different SQL Server instances (machines), for more details click here.

Writing SQL Server Service Broker Applications Across Databases - Initiator in one database and Target in another database on same instance

2011-01-26T09:51:00.000-08:00

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is and how it works, what its different components are and how they are related to each other. Then I talked about writing an SSBS application when both Initiator (Sender) and Target (Receiver) are in same database. Now let’s move on and see how to write SSBS applications when Initiator and Target are in two different databases on the same SQL Server instance, for more details click here.

How the Recycle Bin Works in SharePoint

2011-01-26T03:01:00.000-08:00

The idea behind SharePoint Recycle Bin is the same as Windows' Recycle Bin, but we have more granular control in SharePoint. So how does the Recycle Bin work in SharePoint? How do you configure it? What are the different stages of the Recycle Bin (item deletion) in SharePoint? For more details click here.

Generating SQL Server Test Data with Visual Studio 2010

2011-01-26T02:12:00.000-08:00

As a database developer or tester sometimes you need to have production like data in your environment for your development or testing, but you cannot have the production data because of security and privacy issues. So how you can generate test data or replicate similar data as in production for your development or test environment, for more details click here.

Database Unit Testing with Visual Studio 2010

2011-01-26T02:08:00.000-08:00

As a database developer you always want to ensure stored procedures, UDFs (User Defined Function) and triggers perform as expected. And more importantly you want to ensure that a change in an existing SP, UDF or trigger does not break the functionality. That is to say, you want to have smooth and fast regression testing for your database code. How would you do that, how would you write database Unit Test Cases (UTC)? In this article we cover how you can do this in Visual Studio, click here.

Register and Delete Data-tier Applications in SQL Server 2008 R2

2010-12-27T04:31:00.000-08:00

SQL Server 2008 R2 allows its databases to be registered as Data-tier Applications In-Place; in other words without creating a DAC package and then deploying it. You can delete a registered Data-tier Application from SQL Server 2008 R2 instance in the same way. There are two ways to register and delete a Data-tier Application i.e. using the wizards available in SQL Server 2008 R2 Management Studio (SSMS) and using PowerShell commands, though in this article I will be talking about using these wizards, click here for more details.

Upgrading Data Tier Applications in SQL Server 2008 R2

2010-12-27T04:27:00.000-08:00

In my last article, Creating Data Tier Applications in SQL Server 2008 R2, I gave you an overview of Data Tier Applications, then I talked about how it differs from database projects and finally I showed how you can create a DAC package using Visual Studio 2010 and SQL Server 2008 R2 Management Studio (SSMS). In another article, Deploying Data Tier Applications of SQL Server 2008 R2, I talked about the different methods of deploying Data Tier Applications.
Changes are inevitable and like many other things in life, your application will also change over time, either to add new objects, update existing objects, drop deprecated objects, etc. So now, the question is how to upgrade an already deployed Data Tier Application to a newer version; what are different methods available for upgrade and what considerations should you take? Click here for more detail.

Deploying Data-Tier Applications of SQL Server 2008 R2

2010-12-27T04:23:00.001-08:00

Data-Tier Applications of SQL Server 2008 R2 make database development, deployment and management much easier. When you create a Data Tier Application and build it, it creates a single, self-contained unit of deployment called a DAC package in compressed format that contains SQL Server instance objects that are associated with the database, database objects and deployment intents.
In my last article Creating Data-Tier Applications in SQL Server 2008 R2 I gave you an overview of Data-tier applications, then I talked about how it differs from database projects and finally I showed how you can create a DAC package using Visual Studio 2010 and SQL Server 2008 R2 Management Studio (SSMS). In this article my focus is to deploy the created DAC package and discuss the different methods of deployment, click here for more detail.

Creating Data-Tier Applications in SQL Server 2008 R2

2010-12-27T03:37:00.000-08:00

SQL Server 2008 R2 with Visual Studio 2010 has introduced a new feature called Data-Tier Applications (called DAC for short), which makes database development, deployment and management much easier. When you create a Data Tier Applications project and build it, it creates a self-contained unit of deployment called the DAC package (which has a dacpack extension) that contains SQL Server instance objects, which are associated with the database, database objects and deployment intent (also called pre-requisite checks or deployment requirements of the application), for more information click here.

Developer Dashboard in SharePoint 2010

2010-11-29T04:39:00.000-08:00

Prior to SharePoint 2010, tracking down performance bottlenecks might involve attaching a debugger to code and enabling a trace from the SQL Server Profiler. Now, with SharePoint 2010, you can use a cool new feature called Developer Dashboard. The SharePoint 2010 Developer Dashboard displays tracing and diagnostic information about page rendering and includes information about page components, controls, queries and execution time (an analogy of ASP.NET page tracing), for more details click here.

Unattached Content Database Recovery in SharePoint 2010

2010-11-29T04:36:00.000-08:00

In earlier versions of SharePoint, to restore or recover content from a backup file, we had to restore the backed up file to a database server and had to attach that restored database to a another SharePoint farm. Then we needed to export the required content from this new temporary farm and then migrate it to the original farm where we wanted to recover it. This whole process required a huge time investment and rigorous planning.

SharePoint 2010 removes much of the administrative overhead with the inclusion of a Granular Backup and Restore feature. In the above mentioned scenario, we don't need to setup another temporary farm just to restore some content from the backup. We can simply restore a content database backup on any SQL Server instance (or can create database snapshot of the current database before making changes to the web application) and then use the unattached content database recovery feature from the Central Administration to browse the content of the backup. Then we can export it to a file and import it whenever we want, for more details click here.

Exporting/Importing Site/Library/List in SharePoint 2010

2010-11-29T04:29:00.000-08:00

As a SharePoint Administrator, you would often need to deploy content from one site to another. So what are different options available to export the contents of a site, library, or list from SharePoint and to import it back whenever required?
Granular Backup and Restore functionalities in SharePoint 2010 have grown up tremendously from previous versions. Now you can export sites, libraries, and lists from the Central Administration tool itself. You can even use new PowerShell cmdlets to script out export and import operations. PowerShell cmdlets are optimized and provide more new features and options than the old STSADM tool. For more details, click here.

Document Versioning in SharePoint 2010

2010-11-29T04:24:00.000-08:00

There might be a time when you would need to restore files back to an older version if any inadvertent change happens. You would also like to track changes for auditing purposes. So how does SharePoint allow creating different versions of documents? How does version history work? What are the different types of versions we can maintain for our library or list and how can we revert back to an older version? For more details click here.

Granular Backup and Restore in SharePoint 2010

2010-11-29T04:20:00.000-08:00

In my last article, Backup and Restore in SharePoint 2010, I talked about options to backup a SharePoint farm, web applications and different components and then restore them back whenever required. But what if I have a web application with multiple site collections in it and one of the them is only getting changed frequently hence I want to backup only a specific site collection instead of backing up the complete farm or web application on regular basis? What if I want to create a copy of the site collection to another SharePoint server? What new options are available in SharePoint 2010 for site collection backup and restore?

How Check-in and Check-out work in SharePoint

2010-11-29T04:18:00.000-08:00

SharePoint is a collaborative platform. A central feature is the ability to share documents with other users. But what if multiple users try to edit the same document at the same time? This can lead to versioning conflicts and confusion about changes. How can this be prevented?
SharePoint provides a feature called document Check-In and Check-Out. The purpose is to exclusively lock a document for modification by a user. Once the changes are complete the user can check-in the updated document to release the lock and make the changes visible to other users. For more details click here.

Running a SSIS Package from SQL Server Agent Using a Proxy Account

2010-11-29T04:10:00.000-08:00

When we try to execute a SSIS package from a SQL Server Agent job it fails with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account." if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the box or the job step is not set to run under a proxy account associated with the SSIS subsystem. What is this exception about? What causes it and how do I resolve it? For more details, click here.

Getting Started with Extended Events in SQL Server 2008

2010-11-29T04:04:00.001-08:00

In my last article, "An Overview of Extended Events in SQL Server 2008" I gave you an overview of Extended Events (XEvents) in SQL Server 2008, then I talked about its capability in terms of the number of events to trace and its deeper level of troubleshooting and finally I talked about the different components of Extended Events.
In this article, I am going to give some hands on examples to understand how this powerful tracing feature can assist you with your performance problems, for more details click here.

An Overview of Extended Events in SQL Server 2008

2010-10-28T04:55:00.000-07:00

As a DBA, we are faced with performance problems which we need to diagnose, trace and troubleshoot. Although there are several different tracing and troubleshooting mechanisms (DBCC, SQL Trace, Profiler, etc...) available but none of these tools provide deep levels of tracing/troubleshooting, like identifying page splits, high CPU utilization, etc... So how can we diagnose, trace and troubleshoot these kinds of performance problems in SQL Server 2008. In this tip I talk about Extended Events of SQL Server 2008 and how it is different from earlier tracing and troubleshooting methods, for more details click here.

Backup and Restore in SharePoint 2010

2010-10-28T04:50:00.000-07:00

As a SharePoint Administrator, you need to set up a backup plan so that you can restore your SharePoint farm or SharePoint web applications without any hassles if a disaster happens or even to create a replica of your production environment for QA and development purposes.
So what are the 'out of the box' Backup and Restore options available in SharePoint 2010? How do we backup the SharePoint farm, web applications and different components? How have Backup and Restore processes have improved from previous versions of SharePoint to SharePoint 2010? for details click here.

Master Data Services (MDS) model Versioning, Permission and Security

2010-10-28T04:46:00.000-07:00

Master Data Services (MDS) is a master data management platform, which allows you to create a centralized hub for your master data. This single and centralized authoritative master data source provides consistent master data to all the business applications aligned to it. In this article I am going to demonstrate how model versioning works in MDS and then I will be talking about how security works in MDS, for more details click here.

How to Import, Browse And Export Master Data Service's Data

2010-10-08T11:03:00.000-07:00

Master Data Services (MDS) is a master data management platform, which allows you to create a centralized hub for your master data. This single and centralized authoritative master data source provides consistent master data to all the business applications aligned to it. In this article, I am going to demonstrate how you can import data to MDS, browse it and export it via subscription views for consumption by reporting and analytical applications, for more details click here.

Creating Master Data Services Applications/Objects

2010-09-30T09:45:00.000-07:00

Master Data Services (MDS) is a master data management platform that allows you to create a centralized hub for your master data. Keeping master data in an external centralized system as a single authorative source helps to remove several master data challenges. This single authorative master data source acts as a System of Record by integrating master data from all the upstream sources and as a System of Entry by providing consistent master data to all the downstream applications. This article talks about how to get started on Master Data Services, understand different components of it and create master data services' objects. For details, click here.

Event Notifications in SQL Server

2010-09-27T04:18:00.000-07:00

SQL Server 2005 and onwards provides event notifications mechanism for tracking all events or changes happening at the database or instance level. This can be done with DDL triggers or SQL traces, but event notifications have the advantage of being asynchronous in nature and running outside of the scope of the transaction and hence it can run inside (or be a part of) a database application to capture defined events without using the resources allocated to the transaction. For more details, click here.

SQL Server - Physical Joins Operators

2010-09-16T11:38:00.000-07:00

We use logical operators when we write queries to define a relational query at the conceptual level (what needs to be done). SQL implements these logical operators with different physical operators to implement the operation defined by the logical operators (how it needs to be done). Although there are dozens of physical operators but for logical joins it uses three physical join operators. Although we have different kinds of logical joins at the conceptual/query level, but SQL Server implements them all with three different physical join operators, click here for details.

Getting started with SQL Server 2008 R2's Master Data Services

2010-09-16T11:32:00.000-07:00

The Master Data Management (MDM) platform, called Master Data Service (MDS), is built on top of the SQL Server 2008 R2 database and Windows Communication Foundation (WCF). This article talks about what it is in detail, then offers a step-by-step guide to installing and configuring this new capability, called Master Data Services (MDS), click here for more details.

A First Look at SQL Server 2008 R2 Master Data Services

2010-09-16T11:28:00.000-07:00

Redundancy, caused by integrating multiple definitions of master data across different lines of business into a data warehouse can cause reports to depict inaccurate results. Master Data Services, introduced in SQL Server 2008 R2, overcomes this problem, allowing you to create, organize and manage master data centrally. This article talks about Master Data Management, Master Data Services (MDS), MDS Architecture, MDS components etc click here for detail.

Configuring Proactive Caching in SQL Server

2010-09-07T07:37:00.000-07:00

The proactive caching feature ensures a cube or a measure group partition or dimension reflects current data on its own. Depending on your choice of proactive caching enabled storage mode, interception of data change notification, storage location and update frequency will vary.

You use the Storage Settings dialog box in BIDS (Business Intelligence Development Studio) to set the proactive caching feature, storage location, and notification settings for a dimension, cube, measure group, or measure group partition, for more details click here.

Introduction to SQL Server Proactive Caching

2010-09-07T07:32:00.000-07:00

Mostly data warehousing and Business Intelligence (BI) applications work on historical data, which are normally refreshed once in a daily, weekly or monthly basis. Although there are some near real time data warehousing and BI applications, their query performance is painfully slow. To help in this scenario, Analysis Services (Microsoft SQL Server 2005 Analysis Services and later versions) provides an amazing feature called "Proactive Caching". This new feature gives a query performance, similar to what you had with historical data, with near real time data and minimal impact on the overall system. As a BI developer, you just need to configure proactive caching, and then SQL Server Analysis Services (SSAS) will ensure that you get the performance of a fully processed data warehouse on near real time data, for more details click here.

Manage Database Projects With Visual Studio 2010

2010-09-07T07:24:00.000-07:00

Prior to Microsoft Visual Studio 2005, managing database projects had been a real challenge. Microsoft Visual Studio 2005 Database Professional Edition (also known as Data dude) simplified this process and Microsoft Visual Studio 2008 Database Edition GDR 2 further, along with several bug fixes, enhanced its capability to support newest feature of SQL Server 2008. New release i.e. Microsoft Visual Studio 2010 brings a whole lot of new features and enhancements compared to its earlier versions. For example, you can now connect to SQL Server from Visual Studio IDE (Integrated Development Environment) itself with new in-built T-SQL Editor without actually going to SQL Server Management Studio (SSMS), you will get T-SQL Intellisence support while writing or modifying your T-SQL code etc. To learn more click here.

SQL Data Comparison with Visual Studio 2010

2010-08-31T13:52:00.000-07:00

We often need to compare and synchronize the data that tables (which have the same structure) contain. For example we normally define some master data in a development environment during development and would like the same data to be deployed/inserted/updated to Test/QA/UAT/Production environments after development. So how can we do this, how we can compare data of the tables between different databases and generate data synchronization or incremental data deployment scripts? Click here for more details...

SQL Schema Comparison with Visual Studio 2010

2010-08-31T13:48:00.000-07:00

Often we need to compare databases across two different environments (or even sometimes in the same environment) to identify the schema object differences, so that one database can be brought in sync with another one. For example, we normally do development on the development box; then during the build preparation we compare the development database with the QA/UAT/Production database to identify differences and generate schema objects for synchronization or incremental deployment scripts. How we can compare schema objects between databases and generate synchronization or incremental deployment scripts? Click here for more details...

Understanding and creating libraries in SharePoint 2010

2010-08-31T13:37:00.000-07:00

A library is kind of container/list for creating, organizing and managing different types of documents/contents and share it with other team members. It allows to store files and meta information about the files so that it can be used among different team members. You can create, store and manage almost every type of file in different kind of libraries for example word documents, spreadsheets, presentations, forms etc. By default a Shared Library is created when you create a site and then on additionally you can create as many as type of libraries you want. This article talks abour different types of libraries and covers creating/managing document library in details, for more details click here.

Creating Web Applications, Site Collections and Sites in SharePoint 2010

2010-08-31T13:29:00.000-07:00

SharePoint 2010 introduced several new features, one of them is enhancement to Central Administation site. This article discusses about using SharePoint 2010 Central Administration to create web application, site collections and sites and how they relate to each other. To learn more, click here.

Instant File Initialization for SQL Server 2005/2008

2010-08-31T13:27:00.000-07:00

This article discusses Database Instant File Initialization, which instructs the OS to allocate the required disk space immediately before actually zero-ing out the content from the allocated space. This means the content of the allocated disk area remains there until SQL overwrites it, improving the performance and reducing the downtime. Read on to learn more...

Developing Service Broker Application when Initiator and Target are in same database

2010-08-05T07:06:00.000-07:00

SQL Server Service Broker (SSBS), introduced with SQL Server 2005 and enhanced in SQL Server 2008, allows you to write queuing/message based applications within the database itself. In this article, I will be creating an Service Broker application in which Initiator and Target both are in the same database, for more details click here.

Transfer Jobs Task and Transfer Logins Task in SSIS

2010-08-05T07:04:00.000-07:00

Making a replica of your production server to create a dev or test environment or moving your production data to new hardware are tasks a DBA needs to perform. So what are different methods to perform these tasks efficiently and without much effort? In this article I am going to demonstrate how you can use the Transfer Jobs Task and the Transfer Logins Task to perform some of these operations, for more details click here.

Transfer Database Task and Transfer SQL Server Objects Task in SSIS

2010-08-05T06:59:00.000-07:00

Making copies of databases, moving databases to another SQL Server instance and transferring SQL Server objects to another database are frequent tasks a DBA performs. This article discusses how you can perform these operations by using the Transfer Database Task and Transfer SQL Server Objects Task in SSIS without having to write any code, for more details click here.

Installing and Configuring Windows SharePoint Service (WSS) 3.0

2010-07-09T23:51:00.000-07:00

Windows SharePoint Service is an add-on service of the Windows Server OS which provides collaborative development, content management, search features, etc., without any additional licensing. So how do we install this service, configure it and get started to leverage these powerful features? Installation cane be done either in...click here for details.

Window SharePoint Service (WSS) 3.0 Vs Microsoft Office SharePoint Server (MOSS) 2007

2010-07-09T23:48:00.000-07:00

Window SharePoint Service (WSS) 3.0 and Microsoft Office SharePoint Server (MOSS) 2007 both provide a platform for communication, collaborative development, content management, search capability etc. But how do they differ from each other, what benefits do each of them provide over other, in which scenario to use WSS and in which to use MOSS? To learn about these, click here.

Using a Data Access Application Block (DAAB)

2010-07-06T07:24:00.000-07:00

Microsoft Enterprise Library provides several reusable software components (application blocks) which help developers simplify common development tasks, minimize development efforts and help address common problems related to performance/optimization/reusability release over release. These components are provided along with source code which developers can use "as-is" or customize per their projects need. Data Access Application Block (DAAB) is one of these components which simplifies the data access mechanism. Data Access Application Block provides a consistent data access mechanism to execute both Stored Procedures and inline SQL statements across projects/enterprises along with transaction support also. For more details, click here for Part1 article and click here for Part 2 article.

SQL Server Service Broker Components - Service, Conversation, Conversation Groups, Routes, SEND and RECEIVE Commands

2010-07-06T07:16:00.000-07:00

In my last article SQL Server Service Broker Components - Message Types, Contracts and Queue, I discussed the Initiator, Target, Message Types, Contract and Queue components of SSBS. In this article I will be talking about the Service, Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components are related to each other. For more details, click here.

SSIS Multicast Transformation vs Conditional Split Transformation

2010-06-29T00:28:00.000-07:00

In a data warehousing scenario, it's not rare to replicate data of a source table to multiple destination tables, sometimes it's even required to distribute data of a source table to two or more tables depending on some condition. For example splitting data based on location etc. So how we can achieve this with SSIS? SSIS provides several built-in transformation tasks to achieve these kinds of , for details click here.

Writing a Data Access Layer (DAL) in ADO.NET

2010-06-08T04:45:00.000-07:00

Almost every application being developed stores data in some or other form to make it persistent, this could be either storing it in file system, Excel sheet, XML format or in relational database. So each time, when you develop an application, you are required to write code for storing and retrieving data from these data sources.
In this article, I am going to show you how you can create a Data Access Layer (to store, retrieve and manage data in relational database) in ADO .NET. I will show how you can make it data provider independent, so that you don't have to re-write your data access layer if the data storage source changes overtime and also you can reuse it in other applications that you develop. For details, click here.

SQL Server 2008 System Functions to Research Tables, Views, Indexes, Columns, Stored Procedures, etc.

2010-06-04T02:52:00.000-07:00

SQL Server provides several system meta data functions to get property values for different object types. Each system meta data function has a set of properties you can query using the system meta data function. In this article, I am going to outline examples on the following functions, for details click here.

OBJECTPROPERTY and OBJECTPROPERTYEX
INDEXPROPERTY
COLUMNPROPERTY

SQL Server Service Broker Components - Initiator, Target, Message Types, Contracts and Queue

2010-06-04T02:46:00.000-07:00

In my last article, I talked about SQL Server Service Broker at introductory level. In this article I am going to cover some of the components of Service Broker viz. Initiator, Target, Message Types, Contract and Queue, for more details click here.

SQL Server Service Broker - An Introduction

2010-05-05T10:57:00.000-07:00

SQL Server Service Broker (SSBS or SSB) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008) which allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. This integration of queuing and message based infrastructure into the database provides several advantages from other message/queuing based applications out of the database. In this introductory article on SSBS, I discuss SSBS on a very basic level, how it differs from MSMQ, SSBS architecture and its different components. For more details, click here.

SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.

2010-04-29T06:18:00.000-07:00

SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables, for example

SERVERPROPERTY
DATABASEPROPERTY
FILEGROUPPROPERTY
FILEPROPERTY
OBJECTPROPERTY
COLUMNPROPERTY
INDEXPROPERTY
CONNECTIONPROPERTY
SESSIONPROPERTY

Although you can also use the SQL Server catalog views or Dynamic Management Views to obtain much of this information, in some circumstances the system meta data functions simplify the process. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios, for more details click here.

Understanding Dynamic Management Views and Functions in SQL Server 2008

2010-04-29T06:14:00.000-07:00

Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues. For details, click here.

SQL Server CROSS APPLY and OUTER APPLY

2010-04-29T05:56:00.000-07:00

SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications, for more details click here.

Understanding Catalog Views in SQL Server 2005 and 2008

2010-03-04T10:19:00.000-08:00

Understanding Catalog Views in SQL Server 2005 and 2008
SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options. The option I have selected in this tip is the Catalog Views. These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data. In this article, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc click here for more details.

SQL Server 2005 and 2008 Ranking Functions - DENSE_RANK and NTILE

2010-03-04T10:15:00.000-08:00

SQL Server 2005 and 2008 Ranking Functions - DENSE_RANK and NTILE
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With these new functions, we are no longer required to write several lines of code to get ranking data. It does not only help in simplifying the query but also improves the performance of the query. So now the questions are: what are these ranking functions, how they work and how they differ from each others? This article talks about DENSE_RANK and NTILE in detail, click here for more details.

SQL Server 2005 and 2008 Ranking Functions - Row_Number and Rank

2010-03-04T10:09:00.000-08:00

SQL Server 2005 and 2008 Ranking Functions - Row_Number and Rank
SQL Server introduced four different ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With this inclusion we are no longer required to write several lines of code to get ranking. It does not only help in simplifying the query, but also improves the performance of the query. So now the questions are, what are these ranking functions, how do they work and how do they differ from each other? This article talks about Row_Number and Rank in detail, click here for more details.

Different strategies for removing duplicate records in SQL Server

2010-03-04T10:04:00.000-08:00

Different strategies for removing duplicate records in SQL Server
In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this article I am going to discuss different strategies which you can take for this, along with its pros and cons. Click here for more details.

Iterating through SQL Server databases and database objects without cursors

2010-03-04T10:00:00.000-08:00

Iterating through SQL Server databases and database objects without cursors
There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works? Click here for more details.

How to upgrade SSIS 2005 Packages to SSIS 2008

2010-03-04T06:31:00.000-08:00

How to upgrade SSIS 2005 Packages to SSIS 2008
There are several enhancements in SSIS 2008 such as enhanced lookup transformation, the development environment for Script Task and Script Component changing from VSA to VSTA, etc. If you plan to upgrade your SSIS 2005 packages to SSIS 2008 it may be a challenging task especially if it uses the Script Task or Script Component because of the development environmental changes (from VSA to VSTA) . SSIS provides several different options to upgrade SSIS packages, for more details click here.

Transfer SQL Server database schema objects and data to another server or database with SMO

2010-02-12T04:42:00.000-08:00

Transfer SQL Server database schema objects and data to another server or database with SMO
Have you wondered how Database Copy Wizard works behind the scenes? Do you have a requirement to create a copy of your database (say copy of your production database for development or testing) programmatically? In this article, I am going to show you how you can use SMO (SQL Server Management Objects) classes to transfer database objects and data to another server or database, to learn detail click here.

Designing an ODS / DW with high availability and consistency

2009-12-17T01:45:00.000-08:00

Designing an ODS / DW with high availability and consistency

It's widely recognized that database sizes are growing significantly, and that the growth is being forced by many factors, such as companies requiring more data to be available online for longer (e.g. to comply with government regulations) or an increasing amount of data being digitized for storage. This extent of data explosion has given a momentum for business intelligence application as well. The business intelligence application gathers and stores data for analyzing historical, current, and predictive views of business operations. The gathering and storage of data, on which these business analytics are done, is done by either data warehouse, data mart or ODS (Operational Data Store). Because now-a-days the success of the business is heavily influenced by these business intelligence applications for better and informed business decisions which further rely on data warehouse or ODS for its data feed, it becomes very essential to design a highly available data warehouse or ODS which provides consistent data all the time.

In this video/paper I am going to discuss different approaches (or some of the many available approaches) which you can take to design an ODS for its high availability and data consistency, I will start my discussion with a very basic approach and will list down its pros and cons. Gradually I will move on to the better approach than previous one in terms of its availability and consistency. And finally I provide you some strategic ODS design decision choices and best practices to consider while designing and maintaining it.

Though going forward I will be referring to an ODS design only but same approaches can also be applied for data warehouse as well. You can watch the video or download the deck and article as per your convenience, click here for more details.

SQL Server 2008 R2 - SQL Azure Enhancements

2009-12-17T01:28:00.000-08:00

SQL Server 2008 R2 - SQL Azure Enhancements
If you were unhappy with the capabilities of SQL Server Management Studio (SSMS) while working with SQL Azure, then there is good news for you. Microsoft has announced the November CTP for Microsoft SQL Server 2008 R2. The SSMS of this version allows you to work with SQL Azure in almost the same way as when you are connected to a local SQL Server. In other words, now you can use your favorite Object Explorer in SSMS to browse through the database objects hosted in SQL Azure as well. In this article, I am going to show how you can use SSMS’s Object Browser to connect/browse to SQL Azure database. For more details click here.

SQL Azure - Starting up...

2009-12-17T01:10:00.000-08:00

SQL Azure - Learning from scratch....

There has been lots of buzz about cloud computing lately and looking at the benefits it provides (in terms of cost savings, high availability, scalability (scale up/down) etc.) it is now evident that cloud computing is the future for next generation applications. Many of tomorrow's applications will be designed and hosted in the cloud. Microsoft realizes this potential and provides a cloud computing solution with Windows Azure. Windows Azure platform, which is hosted inside Microsoft data centers, offers several services which you can leverage while developing your application if you target them for the cloud. One of them is Microsoft SQL Azure, it's a cloud based relational database service built on Microsoft SQL Server technologies. In this article, I am going to show how you can start creating databases and database objects on the cloud with SQL Azure. For more details, click here.

SQL Server Integration Services ( SSIS ) - Best Practices

2009-11-24T22:36:00.000-08:00

SQL Server Integration Services ( SSIS ) - Best Practices
Part 1 briefly talks about SSIS and its capability in terms of enterprise ETL. Then it gives you an idea about what consideration you need to take while transferring high volume of data. Effects of different OLEDB Destination Settings, Rows Per Batch and Maximum Insert Commit Size Settings etc. For more details click here.
Part 2 covers best practices around using SQL Server Destination Adapter, kinds of transformations and impact of asynchronous transformation, DefaultBufferMaxSize and DefaultBufferMaxRows, BufferTempStoragePath and BLOBTempStoragePath as well as the DelayValidation properties. For more details click here.
Part 3 covers best practices around how you can achieve high performance with achieving a higher degree of parallelism, how you can identify the cause of poorly performing packages, how distributed transaction work within SSIS and finally what you can do to restart a package execution from the last point of failure. For more details click here.
Part 4 talks about best practices aspect of SSIS package designing, how you can use lookup transformation and what consideration you need to take while using it, impact of implicit type cast in SSIS, changes in SSIS 2008 internal system tables and stored procedures and finally some general guidelines. For more details click here.

Basic Storage Modes (MOLAP, ROLAP and HOLAP) in Analysis Services

2009-10-28T01:37:00.000-07:00

Basic Storage Modes (MOLAP, ROLAP and HOLAP) in Analysis Services
There are three standard storage modes (MOLAP, ROLAP and HOLAP) in OLAP applications which affect the performance of OLAP queries and cube processing, storage requirements and also determine storage locations. To learn more about these standard storage modes, pros and cons of each one, click here.

Database Impersonation with EXEC AS in SQL Server

2009-10-28T01:28:00.000-07:00

Database Impersonation with EXEC AS in SQL Server
SQL Server 2005/2008 provides the ability to change the execution/security context with the EXEC or EXECUTE AS clause. You can explicitly change the execution context by specifying a login or user name in an EXECUTE AS statement for batch execution or by specifying the EXECUTE AS clause in a module (stored procedure, triggers and user-defined functions) definition. Once the execution context is switched to another login or user name, SQL Server verifies the permission against the specified login or user (specified with EXECUTE AS statement) for subsequent execution instead of the execution context of current user. To learn more about this feature and how it works click here.

Spatial Data Types (GEOMETRY and GEOGRAPHY) in SQL Server 2008

2009-10-28T00:49:00.000-07:00

Spatial Data Types (GEOMETRY and GEOGRAPHY) in SQL Server 2008
SQL Server 2008 provides support for geographical data through the inclusion of new spatial data types, which you can use to store and manipulate location-based information. These native data types come in the form of two new data types viz. GEOGRAPHY and GEOMETRY. These two new data types support the two primary areas of spatial model/data viz. Geodetic model and Planar model. Geodetic model/data is sometimes called round earth because it assumes a roughly spherical model of the world using industry standard ellipsoid such as WGS84, the projection used by Global Position System (GPS) applications whereas Planar model assumes a flat projection and is therefore sometimes called flat earth and data is stored as points, lines, and polygons on a flat surface. To learn more about this new feature click here.

FILESTREAM Data Type in SQL Server 2008

2009-10-28T00:23:00.000-07:00

FILESTREAM Data Type in SQL Server 2008
The new SQL Server 2008 FILESTREAM data type enables SQL Server applications to store unstructured data, such as documents and images, on the file system with a pointer to the data in the database. This enables client applications to leverage the rich NTFS streaming APIs and performance of the file system while maintaining transactional consistency between the unstructured data and corresponding structured data with same level of security. Backups can include or exclude the binary data, and working with the data is with the standard SELECT, INSERT, UPDATE, and DELETE statements in T-SQL. FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system. To learn more about this new feature click here.

Large User Defined Types in SQL Server 2008

2009-10-28T00:16:00.000-07:00

Large User Defined Types in SQL Server 2008
With SQL Server 2005, Microsoft integrated the .NET Common-Language Runtime (CLR) into the database engine itself, with that now you were allowed to create used defined type (UDT) and use it in SQL Server in a similar way as you use any in-built data type once assembly containing UDT is registered into the database. It was good starting point, but the problem with it is, the size of UDT is limited up to 8000 bytes only. SQL Server 2008 overcomes this limitation by introducing Large User Defined Type and increases size all the way to go upto 2GB. Learn more here.

HIERARCHYID Data Type in SQL Server 2008

2009-10-28T00:05:00.000-07:00

HIERARCHYID Data Type in SQL Server 2008
SQL Server 2008 has introduced a new data type HIERARCHYID to store hierarchical data in database table. HIERARCHYID is a variable length system data type, and used to locate the position in the hierarchy of the element. The HIERARCHYID data type is optimized for representing trees, which are the most common type of hierarchal data. The HIERARCHYID data type should be used to represent the position in a hierarchy, that is, a column of type HIERARCHYID does not represent a tree itself, but rather it simply represents the position of a row/node within a defined tree. HIERARCHYID data type exposes many different methods which can be used to retrieve a list of ancestors and descendants as well as a means of traversing a tree etc. For more details click here.

New Date and Time Data Types in SQL Server 2008

2009-10-27T23:53:00.000-07:00

New Date and Time Data Types in SQL Server 2008

SQL Server 2008 introduces four new DATETIME data types which are more optimized for type of usage and memory requirement, along with DATATIME2 which is now SQL compliant and compatible with .Net type DATETIME. To learn more about it and how it works click here.