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

Basic Storage Modes (MOLAP, ROLAP and HOLAP) in Analysis ServicesThere 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.


SQL Server 2008 R2 – SQL Azure Enhancements

SQL Server 2008 R2 – SQL Azure EnhancementsIf 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…


SQL Azure – Starting up…

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…


How to upgrade SSIS 2005 Packages to SSIS 2008

How to upgrade SSIS 2005 Packages to SSIS 2008There 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…


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

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…


Backup and Restore SQL Server databases programmatically with SMO

Backup and Restore SQL Server databases programmatically with SMO In this article I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases programmatically with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how…


Generate SQL Scripts for database objects with SMO

Generate SQL Scripts for database objects with SMO In this article I take about how you can generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically. Click here…


Accessing SQL Server programmatically with SQL Server Management Objects (SMO)

Accessing SQL Server programmatically with SQL Server Management Objects (SMO) SQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO)…


Database Impersonation with EXEC AS in SQL Server

Database Impersonation with EXEC AS in SQL ServerSQL 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…


Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement

Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using “EXEC AT” to execute a pass-through query…