New In Sql Server for SSIS
1. Introduced VSTA - Introduced Visual Studio Tools for Applications(VSTA) for writing 'Script tasks' and 'Script components'. Earlier VSA(Visual Studio for Applications) was used in Sql 2005 for writing Script tasks and components, by using VB Script as language. In Sql 2008 now its become more functional and secure, now C# and Vb.net can also be used as scripting language. All main features of visual studio added in VSTA like intellisence, build, debug, .net assemblies references, COM components, adding web services etc.. You can create Script task by Open a new or existing Integration Services package in Business Intelligence Development Studio (BIDS). Read article with details on http://www.sql-server-performance.com/2009/ssis-new-features-in-sql-server-2008-part5/ and http://msdn.microsoft.com/en-us/library/ms135952.aspx
2. Enhancements in Import / Export wizard - Smarter and improved with
A. Data Type Conversion - Smartly convert mismatched data type on the basis of xml mapping file. If data type of columns mismatch, a new page pops up that show matching, on clicking of mismatched row another popup opens with details of data type conversion.
B. Better Scaling - Creates multiple data flow tasks for a large number of tables, so divides tables in different data flow tasks. For example if taking seven tables, two data flow tasks will be created in the package, one data flow task will contain five data flow for five tables and second data flow task will contain two data flow for last two tables.
3. MERGE statement - Using MERGE statement you can perform insert, update and delete operations based on conditions given by you.
Syntax
MERGE <target_table>
USING <table_source>
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN [TARGET] NOT MATCHED
THEN <merge_not_matched> ]
[WHEN SOURCE NOT MATCHED
THEN <merge_ matched> ];
Example 1
MERGE
InventoryMaster AS im
USING
(SELECT InventoryID, Descr FROM NewInventory) AS src
ON
im. InventoryID = src. InventoryID
WHEN
MATCHED THEN
UPDATE
SET im.Descr = src.Descr
WHEN
NOT MATCHED THEN
INSERT
(InventoryID, Descr) VALUES (src. InventoryID, src.Descr);
Example 2
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
4. Data Flow task improvements - Smarter thread allocation and processing of execution trees. Multiprocessor m/c can get upto 80% of performance increase by improved CPU and memory allocation.
1. Policy-Based Management - It allows administrator to make and apply policies for one or more servers to enforce many kinds of equivalence b/w servers. Policy-Based Management is a new feature in SQL Server 2008 that allows administrators to define rules for one or more SQL Servers and enforce them. The goal of this feature is to make it easier for an administrator to manage one or more servers by preventing servers from being out of compliance with his or her policies. Policies can be used in a variety of ways. For example, you can use policies to enforce naming conventions in a database. You may have several servers that you want to have the same settings of various configuration options. Policy-Based Management will allow you to discover when one of those servers goes out of compliance. Even the use of SQL Server features can be managed with Policy-Based Management – you can use Policy-Based Management to ensure that SQL Mail is not enabled on any server in your enterprise. Policies can be applied to a single server or many servers.
The only tool that database administrators have had in the past to control the setup of servers and databases is a paper-based policy manual. Discovering policy violations and who was responsible for them was a manual task. It was often difficult to discover why a policy had been violated, and even more difficult to determine who violated it. Making sure that a large number of servers were all set up in the same way was a major headache.
The introduction of Policy-Based Management in SQL Server 2008 solves this problem and can be a significant time saver. It is now possible to define how servers are to be configured and have SQL Server reason over these policies to enforce the rules.
Policies - A policy is a rule based on a single condition and applied to one or more targets. A policy has an automation mode that describes what SQL Server should do when a policy is violated.
Conditions - A condition contains one or more Boolean expressions that can be combined with AND, OR, and NOT. The conditions can be quite complex.
Targets - A target is a SQL Server instance, one or more databases, one or more tables, or one or more indices. The targets form a hierarchy. If a policy is created at the server level, it applies to all the appropriate lower levels. A target is also called an “object set”.
Category - You can use categories to manage policies. Categories can be used simply to group related policies.
Can be handled by 'Policy Management' in object explorer inside 'management'.
2. Performance Data Collection - The Data Collector is a core component of Sql Server 2008 that collects different sets of data. Data collection either runs constantly or can be scheduled. Data collector stores collected data in a database called 'Management Data warehouse'. It collects data of disk usage, server activity, query statistics etc that can be reviewed in management studio. Is is automated and can be configured using setup wizard.
3. Data Compression - Data compression can be done for data bases, available even for individual tables or indexes.
A. Save disc space.
B. No need to reconfigure application, sql compress and decompress data automatically, applications can read / write data normally.
C. Data Compression Wizard makes configuration easy.
alter table articleinfo rebuild with(data_compression = page)
alter index PK_ChartColMap on ChartColMap rebuild with (data_compression = page)
4. Resource Governor - Using Resource Governor, you / administrator can handle sql server workload and resource consumption. You can limit or prioritize allocation of CPU and memory to coming applications. Can be configure by T-Sql or in object explorer inside management.
5. Transparent Data Encryption - Sql server implements strong encryption keys and certificates to encrypt data files.
A. Files containing sql data stored as encrypted format, so if files containing disc become compromised, data is safe, it can be decrypt by only an authorized agent.
B. No need to reconfigure application, sql encrypt and decrypt data automatically, applications can read / write data normally.
6. Data Auditing - Data Auditing provides a way to track and log events of databases and servers. You can Audit logons, password changes, data access and modifications and many other events. Tracking these events helps in security and problem trouble shooting. Simple to configure using management studio and no impact of performance because audit data files stored out side sql server database files. Audits can be created using T-Sql or under 'Audits' in 'Security'.
7. Backup Compression - Sql automatically encrypt data and log backup files at the time of backup and decrypt automatically at the time of restore. It leads data safety and saves storage space.
8. Table-Valued Parameters - You can create table types in sql, so that you can use this 'type' in procedures as parameter type and return type.
A. List of data can be passed to procedure, no need to pass large XML data.
B. List of data can be return to procedure, no need to return large XML data.
C. Reduces the complexity and simplifies to work with data sets/ data lists for developer.
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
DECLARE @LocationTVP
AS LocationTableType;
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
EXEC usp_InsertProductionLocation @LocationTVP;
The geometry Data Type : The geometry data type provides a storage structure for spatial data that is defined by coordinates on an arbitrary plane. This kind of data is commonly used in regional mapping systems, such as the state plane system defined by the United States government, or for maps and interior floor plans where the curvature of the Earth does not need to be taken into account. The geometry data type provides properties and methods that are aligned with the Open Geospatial Consortium (OGC) Simple Features Specification for SQL and enable you to perform operations on geometric data that produce industry-standard behavior.
CREATE TABLE Districts
A. DATETIME2 - User defind fraction(0 to 7) for seconds and accuracy upto 100 Neno seconds.
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
B. DATETIMEOFFSET - Also includes Offset like +05:30
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = ’2007-10-29 22:50:55 -1:00'
11. Partitioned Tables Parallelism - Sql allocates all partitioned tables of a query to all available threads and CPUs, so a query runs faster. It perform parallel processing of query parts in multi processor system.
A. Improves query response time for partitioned tables queries.
B. more efficient use of processor resources.
12. Improved Reporting Services - Processing and performance of reports of SSRS(Sql server reporting server) is improved, now large reports not consume all available resources and memory.
13. Microsoft Office 2007 - Sql Server 2008 tightly integrated with office 2007, so now SSRS reports can be directly exported to word or excel of Office 2007.
1. Introduced VSTA - Introduced Visual Studio Tools for Applications(VSTA) for writing 'Script tasks' and 'Script components'. Earlier VSA(Visual Studio for Applications) was used in Sql 2005 for writing Script tasks and components, by using VB Script as language. In Sql 2008 now its become more functional and secure, now C# and Vb.net can also be used as scripting language. All main features of visual studio added in VSTA like intellisence, build, debug, .net assemblies references, COM components, adding web services etc.. You can create Script task by Open a new or existing Integration Services package in Business Intelligence Development Studio (BIDS). Read article with details on http://www.sql-server-performance.com/2009/ssis-new-features-in-sql-server-2008-part5/ and http://msdn.microsoft.com/en-us/library/ms135952.aspx
2. Enhancements in Import / Export wizard - Smarter and improved with
A. Data Type Conversion - Smartly convert mismatched data type on the basis of xml mapping file. If data type of columns mismatch, a new page pops up that show matching, on clicking of mismatched row another popup opens with details of data type conversion.
B. Better Scaling - Creates multiple data flow tasks for a large number of tables, so divides tables in different data flow tasks. For example if taking seven tables, two data flow tasks will be created in the package, one data flow task will contain five data flow for five tables and second data flow task will contain two data flow for last two tables.
3. MERGE statement - Using MERGE statement you can perform insert, update and delete operations based on conditions given by you.
Syntax
MERGE <target_table>
USING <table_source>
ON <search_condition>
[WHEN MATCHED
THEN <merge_matched> ]
[WHEN [TARGET] NOT MATCHED
THEN <merge_not_matched> ]
[WHEN SOURCE NOT MATCHED
THEN <merge_ matched> ];
Example 1
MERGE
InventoryMaster AS im
USING
(SELECT InventoryID, Descr FROM NewInventory) AS src
ON
im. InventoryID = src. InventoryID
WHEN
MATCHED THEN
UPDATE
SET im.Descr = src.Descr
WHEN
NOT MATCHED THEN
INSERT
(InventoryID, Descr) VALUES (src. InventoryID, src.Descr);
Example 2
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
4. Data Flow task improvements - Smarter thread allocation and processing of execution trees. Multiprocessor m/c can get upto 80% of performance increase by improved CPU and memory allocation.
Top New Features of Sql Server 2008
1. Policy-Based Management - It allows administrator to make and apply policies for one or more servers to enforce many kinds of equivalence b/w servers. Policy-Based Management is a new feature in SQL Server 2008 that allows administrators to define rules for one or more SQL Servers and enforce them. The goal of this feature is to make it easier for an administrator to manage one or more servers by preventing servers from being out of compliance with his or her policies. Policies can be used in a variety of ways. For example, you can use policies to enforce naming conventions in a database. You may have several servers that you want to have the same settings of various configuration options. Policy-Based Management will allow you to discover when one of those servers goes out of compliance. Even the use of SQL Server features can be managed with Policy-Based Management – you can use Policy-Based Management to ensure that SQL Mail is not enabled on any server in your enterprise. Policies can be applied to a single server or many servers.
The only tool that database administrators have had in the past to control the setup of servers and databases is a paper-based policy manual. Discovering policy violations and who was responsible for them was a manual task. It was often difficult to discover why a policy had been violated, and even more difficult to determine who violated it. Making sure that a large number of servers were all set up in the same way was a major headache.
The introduction of Policy-Based Management in SQL Server 2008 solves this problem and can be a significant time saver. It is now possible to define how servers are to be configured and have SQL Server reason over these policies to enforce the rules.
Policies - A policy is a rule based on a single condition and applied to one or more targets. A policy has an automation mode that describes what SQL Server should do when a policy is violated.
Conditions - A condition contains one or more Boolean expressions that can be combined with AND, OR, and NOT. The conditions can be quite complex.
Targets - A target is a SQL Server instance, one or more databases, one or more tables, or one or more indices. The targets form a hierarchy. If a policy is created at the server level, it applies to all the appropriate lower levels. A target is also called an “object set”.
Category - You can use categories to manage policies. Categories can be used simply to group related policies.
Can be handled by 'Policy Management' in object explorer inside 'management'.
2. Performance Data Collection - The Data Collector is a core component of Sql Server 2008 that collects different sets of data. Data collection either runs constantly or can be scheduled. Data collector stores collected data in a database called 'Management Data warehouse'. It collects data of disk usage, server activity, query statistics etc that can be reviewed in management studio. Is is automated and can be configured using setup wizard.
3. Data Compression - Data compression can be done for data bases, available even for individual tables or indexes.
A. Save disc space.
B. No need to reconfigure application, sql compress and decompress data automatically, applications can read / write data normally.
C. Data Compression Wizard makes configuration easy.
alter table articleinfo rebuild with(data_compression = page)
alter index PK_ChartColMap on ChartColMap rebuild with (data_compression = page)
4. Resource Governor - Using Resource Governor, you / administrator can handle sql server workload and resource consumption. You can limit or prioritize allocation of CPU and memory to coming applications. Can be configure by T-Sql or in object explorer inside management.
5. Transparent Data Encryption - Sql server implements strong encryption keys and certificates to encrypt data files.
A. Files containing sql data stored as encrypted format, so if files containing disc become compromised, data is safe, it can be decrypt by only an authorized agent.
B. No need to reconfigure application, sql encrypt and decrypt data automatically, applications can read / write data normally.
6. Data Auditing - Data Auditing provides a way to track and log events of databases and servers. You can Audit logons, password changes, data access and modifications and many other events. Tracking these events helps in security and problem trouble shooting. Simple to configure using management studio and no impact of performance because audit data files stored out side sql server database files. Audits can be created using T-Sql or under 'Audits' in 'Security'.
7. Backup Compression - Sql automatically encrypt data and log backup files at the time of backup and decrypt automatically at the time of restore. It leads data safety and saves storage space.
8. Table-Valued Parameters - You can create table types in sql, so that you can use this 'type' in procedures as parameter type and return type.
A. List of data can be passed to procedure, no need to pass large XML data.
B. List of data can be return to procedure, no need to return large XML data.
C. Reduces the complexity and simplifies to work with data sets/ data lists for developer.
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
DECLARE @LocationTVP
AS LocationTableType;
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
EXEC usp_InsertProductionLocation @LocationTVP;
9. Virtual Earth Integration - New data types enables rich graphical presentation of physical locations stored in db. Gives meaningful presentation of graphical data.
The geography Data Type : The geography data type provides a storage structure for spatial data that is defined by latitude and longitude coordinates. Typical uses of this kind of data include defining roads, buildings, or geographical features as vector data that can be overlaid onto a raster-based map that takes into account the curvature of the Earth, or for calculating true great circle distances and trajectories for air transport where the distortion inherent in a planar model would cause unacceptable levels of inaccuracy.The geometry Data Type : The geometry data type provides a storage structure for spatial data that is defined by coordinates on an arbitrary plane. This kind of data is commonly used in regional mapping systems, such as the state plane system defined by the United States government, or for maps and interior floor plans where the curvature of the Earth does not need to be taken into account. The geometry data type provides properties and methods that are aligned with the Open Geospatial Consortium (OGC) Simple Features Specification for SQL and enable you to perform operations on geometric data that produce industry-standard behavior.
CREATE TABLE Districts
( DistrictId int IDENTITY (1,1),
DistrictName nvarchar(20),
DistrictGeo geometry);
INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Downtown', geometry::STGeomFromText
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
INSERT INTO Districts (DistrictName, DistrictGeo)
VALUES ('Green Park',geometry::STGeomFromText
('POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))', 0));
10. New Date / Time Data Types - Introduced DATETIME2 and DATETIMEOFFSET data types.A. DATETIME2 - User defind fraction(0 to 7) for seconds and accuracy upto 100 Neno seconds.
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
Fraction | Output |
0 | 2007-10-28 22:11:20 |
1 | 2007-10-28 22:11:19.7 |
2 | 2007-10-28 22:11:19.70 |
3 | 2007-10-28 22:11:19.703 |
4 | 2007-10-28 22:11:19.7030 |
5 | 2007-10-28 22:11:19.70300 |
6 | 2007-10-28 22:11:19.703000 |
7 | 2007-10-28 22:11:19.7030000 |
B. DATETIMEOFFSET - Also includes Offset like +05:30
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = ’2007-10-29 22:50:55 -1:00'
11. Partitioned Tables Parallelism - Sql allocates all partitioned tables of a query to all available threads and CPUs, so a query runs faster. It perform parallel processing of query parts in multi processor system.
A. Improves query response time for partitioned tables queries.
B. more efficient use of processor resources.
12. Improved Reporting Services - Processing and performance of reports of SSRS(Sql server reporting server) is improved, now large reports not consume all available resources and memory.
13. Microsoft Office 2007 - Sql Server 2008 tightly integrated with office 2007, so now SSRS reports can be directly exported to word or excel of Office 2007.
No comments:
Post a Comment