jump to navigation

SQL Server Integration Services ( SSIS ) – Best Practices November 25, 2009

Posted by Arshad Ali in Database Administration, SQL Server Integration Services, SSIS, SSIS 2008.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SSAS.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
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 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
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.

User-Defined Table Type and Table Valued Parameter (TVP) in SQL Server 2008 October 28, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2008.
add a comment
User-Defined Table Type and Table Valued Parameter (TVP) in SQL Server 2008
With SQL Server 2008, you can create a user-defined table type which represents the definition of a table structure. To ensure that the data in a user-defined table type meets specific requirements, you can also create unique constraints and primary keys on this type. Further, to send multiple rows of data to a stored procedure or a function without creating a temporary table or many parameters, you can use a user-defined table type to declare table-valued parameters for stored procedures or functions.
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits; for example it does not acquire locks for the initial population of data from a client, it does not cause a statement to recompile, reduce round trips to the server, enable the client to specify sort order and unique keys etc. To learn more about these new exciting features and how to use it from .Net application, clich here.

Backup and Restore SQL Server databases programmatically with SMO October 6, 2009

Posted by Arshad Ali in Database Administration, DBA, SQL Server, SQL Server 2005, SQL Server 2008.
add a comment

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 to restore them when required programmatically using SMO. Click here for more details….
Follow

Get every new post delivered to your Inbox.