while waiting for the Superbowl, I thought I would write this.....

2014 will not only see the release of a new version of SQL Server, but also accompanying this is the release of courses and certification tracks from Microsoft Learning Experience – formerly Microsoft Learning -- that will support the education of SQL Server and related technologies.

The notable addition in the curriculum, is substantial material on cloud and big data features that pertain to data and business intelligence. There are entire module/chapters that are dedicated Power BI, SQL Azure and HDInsight.

Certifications and courses from Microsoft can get stick – sometimes fair and sometimes unfairly. Whilst I am a massive advocate of community to get information and education. Microsoft’s new courses will bring clarity to the burning topics of the moment and help you to understand the capabilities of Power BI and HDInsight.

From a business intelligence perspective there will be three courses:

20463C: Data warehousing in SQL Server 2014

20466C: data models and reports in SQL Server 2014

20467A: Designing Self-Service Business Intelligence and Big Data Solutions

These are not the exact titles of the course, but will be confirmed prior to the release. And if you have already completed the SQL Server 2012 or 2008 curriculum, there is an upgrade course from

10977A: Upgrading business intelligence skills from 2008 to 2014.

Again this is not the exact title, but these should give you an idea.

Look out for announcements from Microsoft Learning Experience….

 

CHRIS

Happy New Year SQL Server land.

I begin this years blog with a reference to a white paper that I think will be useful to anyone installing and configuring data models.

This white paper will provide you with very useful information on capacity planning tabular data models and can be found on

http://msdn.microsoft.com/en-us/library/jj874401.aspx

Please remember to send feedback, it is always useful for the team to get this n such papers

 Thanks

 Chris

Posted by Testas | with no comments

Back in April I started a blog series on SQL Server High Availability BI.

These are currently hosted on Technet UK

Part I

http://blogs.technet.com/b/uktechnet/archive/2012/05/03/guest-post-seven-worlds-will-collide-high-availability-bi-is-not-such-a-distant-sun.aspx

Part II

http://blogs.technet.com/b/uktechnet/archive/2012/05/30/guest-post-providing-the-foundation-for-a-high-availability-bi-infrastructure.aspx

Part III

http://blogs.technet.com/b/uktechnet/archive/2012/07/16/guest-post-part-3-highly-available-bi-me-myself-and-i.aspx

The final part

http://blogs.technet.com/b/uktechnet/archive/2012/09/24/something-so-strong-completing-the-high-availability-bi-infrastructure.aspx

It has been a great experience and thanks to the UK Technet team for support over the summer

Thanks

Chris

Posted by Testas | with no comments

 

Hi Everyone

Firstly I want to let you know that I finally found the LINQ book prize winners and the list of people at the bottom of this email are owed a LINQ book. This will be given out at next week’s UG meeting

Live meeting with Carolyn Chau, Program Manager at Microsoft on Monday!

It is very rare that we get the opportunity to have a Live meeting with a Program Manager in Redmond. Carolyn Chau will be presenting PowerView next Monday at 8pm. Live meeting details can be found on

http://sqlserverfaq.com/events/388/Live-Meeting-on-SQL-Server-2012-PowerView-with-Carolyn-Chau-Principal-Program-Manager-in-the-Reporting-Services-in-association-with-SQLPASS-SQLServerFAQ-and-SQLBits.aspx

Next week’s UG!!

We welcome Mark Broadbent to Manchester next week where he will be presenting his session on SQL Server 2012 on Windows Core. We also hand out the unclaimed prizes. Register at

http://sqlserverfaq.com/events/369/Thursday-night-meeting-at-BSS-with-Chris-TestaONeill-and-Mark-Broadbent.aspx

Chris Webb is in Manchester!!!

Chris Webb will be speaking at the Manchester SQL Server UG on 4th July. He will also be running his Real World Cube Design and Performance Tuning with Analysis Services between the 3rd – 5th July. If you want to attend then you can sign up at the link below

http://www.technitrain.com/coursedetail.php?c=13&trackingcode=FAQ

SQLRelay and a Special Prize and Jamie Thomson comes to Manchester!!!!

SQLRelay takes place in Manchester on the 22nd. We have a special guest, after years of asking Jamie Thomson is coming to Manchester. The SSIS Junkie will be gracing us with his presence with a talk on SSIS 2012.

Also we have a prize. Know a friend or colleague who would benefit from SQLRelay? Get them to register at www.sqlserverfaq.com and then register for the event

http://sqlserverfaq.com/events/373/ALL-DAY-TUESDAY-EVENT-12-hours-of-SQL-Server-2012-at-the-SQLRelay-meeting-at-the-COOP-Manchester.aspx

Then send an email to ctesta-oneill@hotmail.co.uk with the subject of SQLFriend with the name of your friend.

If you are both at the SQLRelay event on the day and your names are pulled out of the hat you will win a PASS 2011 DVD and your friend will win the “Best of PASS DVD 2011” worth  $1000 courtesy of SQLPASS. The draw will take place between 4.30pm – 5pm on the day.

SQLBits feedback!!!!!

Attended SQLBits? We really need to know your opinion. Please fill out the survey for the days you attended

If you attended any of the days at SQLBits please can you all fill out the following survey

http://www.sqlbits.com/SQLBitsX

If you attended the Thursday Training day then please fill out the following survey:

http://www.sqlbits.com/SQLBitsXThursday

If you attended the Friday Deep Dives day then please fill out the following survey:

http://www.sqlbits.com/SQLBitsXFriday

If you attended the Saturday Community day then please fill out the following survey:

http://www.sqlbits.com/SQLBitsXSaturday

Thanks

 

Chris and Martin

 

LINQ BOOK winners

Andrew Birds

Chris Kennedy

Dave Carpenter

David Forrester

Ian Ringrose

James Cullen

James Simpson

Kevan Riley

Kirsty Hunter

Martin Bell

Martin Croft

Michael Docherty

Naga Anand Ram Mangipudi

Neal Atkinson

Nick Colebourn

Pavel Nefyodov

Ralph Baines

Rick Hibbert

saad saleh

Simon Enion

Stan Venn

Steve Powell

Stuart Quinn

Posted by Testas | with no comments

Over the last 5 years I have observed Microsoft persevere with the notion of Self Service BI over a series of conferences as far back as SQLBits V in Newport. The release of SQL Server 2012, improvements in Excel and the integration with SharePoint 2010 is making this a reality.

Business users are now empowered to create their own BI reports through a number of different technologies such as PowerPivot, PowerView and Report Builder. This opens up a whole new way of working; improving staff productivity, promoting efficient decision making and delivering timely business reports.

There is, however; a serious question to answer.

What happens should any of these applications become unavailable? More to the point, how would the business react should key business users be unable to fulfil reporting requests for key management meetings when they require it?  While the introduction of self-service BI will provide instant access to the creation of management information reports, it will also cause instant support calls should the access to the data become unavailable.

These are questions that are often overlooked when a business evaluates the need for self-service BI. But as I have written in other blog posts, the thirst for information is unquenchable once the business users have access to the data. When they are unable to access the information, you will be the first to know about it and will be expected to have a resolution to the downtime as soon as possible.

The world of self-service BI is pushing reporting and analytical databases to the tier 1 application level for some of Coeo’s customers. A level that is traditionally associated with mission critical OLTP environments. There is recognition that by making BI readily available to the business user, provisions also need to be made to ensure that the solution is highly available so that there is minimal disruption to the business.

This is where High Availability BI infrastructures provide a solution.

As there is a convergence of technologies to support a self-service BI culture, there is also a convergence of technologies that need to be understood in order to provide the high availability architecture required to support the self-service BI infrastructure. While you may not be the individual that implements these components, understanding the concepts behind these components will empower you to have meaningful discussions with the right people should you put this infrastructure in place.

There are 7 worlds that you will have to understand to successfully implement a highly available BI infrastructure

 

1.       Server/Virtualised server hardware/software

2.       DNS

3.       Network Load Balancing

4.       Active Directory

5.       Kerberos

6.       SharePoint

7.       SQL Server

I have found myself over the last 6 months reaching out to knowledge that I learnt years ago when I studied for the Windows 2000 and 2003 (MCSE) Microsoft Certified System Engineer. (To the point that I am resuming my studies for the Windows Server 2008 equivalent to be up to date with newer technologies) This knowledge has proved very useful in the numerous engagements I have undertaken since being at Coeo, particularly when dealing with High Availability Infrastructures.

As a result of running my session at SQLBits X and SQL Saturday in Dublin, the feedback I have received has been that many individuals desire to understand more of the concepts behind the first 6 “worlds” in the list above.

Over the coming weeks, a series of blog posts will be put on this site to help understand the key concepts of each area as it pertains to a High Availability BI Infrastructure. Each post will not provide exhaustive coverage of the topic. For example DNS can be a book in its own right when you consider that there are so many different configuration options with Forward Lookup, Reverse Lookups, AD Integrated Zones and DNA forwarders to name some examples. What I want to do is share the pertinent points as it pertains to the BI infrastructure that you build so that you are equipped with the knowledge to have the right discussion when planning this infrastructure.

Next, we will focus on the server infrastructure that will be required to support the High Availability BI Infrastructure, from both a physical box and virtualised perspective.

Thanks

 

Chris

Posted by Testas | with no comments

Quick announcement

Microsoft Learning today announced the certification tracks for the upcoming SQL Server 2012 exams.

You begin by acheiving the MCSA - Microsoft Certified Solutions Associate (Not to be confused by the old Microsoft Certified System Administrator)

If you are starting out this includes taking the following three exams:

  • Exam 70-461: Querying Microsoft SQL Server 2012
  • Exam 70-462: Administering Microsoft SQL Server 2012 Databases
  • Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

If you have an MCTS in SQL Server 2008 already you can take the following path

  • A pass in a SQL Server 2008 (MCTS) Microsoft Certified Technology Specialist exam
  • Exam 70-457: transisitioning your MCTS on SQL Server 2008 to MCSA on SQL Server 2012 part 1
  • Exam 70-458: transisitioning your MCTS on SQL Server 2008 to MCSA on SQL Server 2012 part 2

Once you have achieved you MCSA status you can then start for your MCSE - Microsoft Certified Solutions Expert certification

You have a choice, to do the MCSE: SQL Server 2012 Data Platform, MCSE: SQL Server 2012 Business Intelligence or you could do both

MCSE: SQL Server 2012 Data Platform involves

  • Obtain your SQL Server 2012 MCSA
  • Exam 70-464: Developing Microsoft SQL Server 2012 Databases
  • Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012

There is also an upgrade path

  • A pass in a SQL Server 2008 (MCITP) Microsoft Certified IT Professional Database Administrator or Database Developer Certification
  • Exam 70-457: transisitioning your MCTS on SQL Server 2008 to MCSA on SQL Server 2012 part 1
  • Exam 70-458: transisitioning your MCTS on SQL Server 2008 to MCSA on SQL Server 2012 part 2
  • Exam 70-459: transisitioning your MCITP on SQL Server 2008 Database Administrator or Database Developer to MCSE:Data Platform

MCSE: SQL Server 2012 Business Intelligence involves

  • Obtain your SQL Server 2012 MCSA
  • Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
  • Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

The upgrade path involves:

  • A pass in a SQL Server 2008 (MCITP) Microsoft Certified IT Professional Business Intelligence Certification
  • Exam 70-457: transisitioning your MCTS on SQL Server 2008 to MCSA on SQL Server 2012 part 1
  • Exam 70-458: transisitioning your MCTS on SQL Server 2008 to MCSA on SQL Server 2012 part 2
  • Exam 70-460: transisitioning your MCITP on SQL Server 2008 Business Intelligence Developer to MCSE:Business Intelligence

As a result if you want to achieve the MCSE in either Data Platform or Business Intelligence and you are starting from scratch there will be 5 exams to take

If you have the ability to upgrade your certification because you have an MCITP already then it will be three exams

Full details and questions can be found at http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx

Thanks

Chris

Posted by Testas | with no comments
Hello , 

We (Martin Bell and Chris Testa-O’Neill)  started writing an email and didn’t realise how big it would get so we stuck it on a blog instead 

So in summary this blog covers:

  • Microsoft and the MVPs roll into Manchester  on the 22nd May  for SQLRelay 2012
  • Leeds and Manchester User Group Dates for 2012
  • Workshops in the North from the best SQL Server Experts.
  • SQLBits – spaces are running out 
Microsoft and the MVPs roll into Manchester  on the 22nd May  for SQLRelay 2012 

May 21st – 25th 2012 sees the second instalment of the SQLRelay as user groups across the UK liaise with Microsoft to bring you the best speakers from the UK and Microsoft. Manchester will host a full day/evening event on the 22nd May at the Coop in Manchester. Microsoft will be speaking as well as recognised MVPs and also members of the local Manchester and Leeds SQL Community. Furthermore, breakfast, lunch and dinner will be provided. Whats more the event is free. You can register for the event
here before an official announcement comes from Microsoft and SQLServerFAQ next month. 

Leeds and Manchester User Group Dates for 2012 

We want to thank you all for your support over the years. This has led to what I believe is the best line up so far in 2012. This is not possible without your continued attendance and support 
You can view the event details and register by clicking on the links

14th Mar – Manchester:  Martin Bell and Tony Rogerson
15th Mar – Leeds: Chris Testa-O’Neill and speaker TBC

25th Apr – Leeds: Tony Rogerson and Mark Broadbent
26th Apr – Manchester: Chris Testa-O’Neill and Mark Broadbent

21st -25th May – SQL Relay (Manchester 22nd May)

4th Jul – Manchester:  Chris Webb and speaker TBC
5th Jul – Leeds:  Chris Webb and speaker TBC 

5th Sep – Leeds:  Alex Whittles and speaker TBC
6th Sep– Manchester : Alex Whittles and speaker TBC 

17th Oct – Manchester: Christian Bolton and speaker TBC
18th Oct – Leeds: Christian Bolton and speaker TBC 

28th Nov – Leeds:  Allan Mitchell and Jenny Stirrup
29th Nov – Manchester:  Allan Mitchell and Jenny Stirrup 

Each speakers content will be going up on the site in the due course 

There are also chances for people from the User Group to take up the mantle and do a presentation to your fellow attendees. Anyone wishing to put their name down for any of these evenings please let me know. It doesn’t have to be a full 1hr slot, if you want more or less time we can accommodate both! Anyone can bring a question or SQL Nugget to any meeting.
 

Workshops in the North from the best SQL Server Experts.
 

You will note that for the first time we have Christian Bolton and Chris Webb speaking in Manchester and Leeds. We have tried to get them over the last couple of years but their calendars are usually full. They will be running workshops around the dates that they speak at the user groups. If you are interested let me know, as soon as I get more details I will send them out to you. 
 

SQLBits – spaces are running out
 

SQLBits X is the official UK Technical Launch for SQL Server 2012 and is taking place at the London Novotel West hotel in Hammersmith. You may have seen communication about it nearly being full. If you want to attend, I would urge you to
sign up in the next week as it will be full very soon 

Thanks
 

Chris and Martin

 

SQLBits X is proving to be the most popular SQLBits yet. The team is working very hard to bring the best speakers from around the world. For those who know me you will know my focus is on BI. However I wanted to point out a number of non BI sessions that I would recommend based on my experience of these speakers at the MVP Summit in Seattle or TechED.


Michael Rys


Anyone working with non relational data such as XML or Geospatial data will not want to miss the fact that Michael Rys is in town for SQLBits X.
Michael is a great guy but more importantly he is principal program manager in the SQL Server RDBMS team. He is responsible for the Beyond Relational Data and Services scenario that includes unstructured and semi-structured data management, search, Spatial, XML and others. He has been program manager for the XML features in SQL Server 2000 to 2008 such as FOR XML, the XML data type and XQuery and for Spatial Indexing in SQL Server 2008. He also represents Microsoft Corp. in the W3C XML Query working group and the ANSI SQL standardization effort

Michael is also a senior member of ACM and a member of IEEE and has given many presentations and contributed to several books on XQuery and XML and databases. His weblog can be found at http://sqlblog.com/blogs/michael_rys/default.aspx and can be followed at @SQLServerMike (when he finds time to tweet).
I have seen Michael’s sessions on more than one occasion. It was particularly useful at a time when my head was involved with geospatial data with reporting services. His sessions provided so much clarity that without his insight I would have never have got a project completed. His XML sessions are as equally insightful and delivered with ease.


So if you want insight into these technologies, I would recommend that you sign up for his training day session at www.SQLBits.com  His session is Beyond Relational in SQL Server 2012 and SQL Futures with Michael Rys and you would not want to miss this


Tobias Ternstrom


Equally, Tobias Ternstrom gives fantastic presentations. Tobias has been an avid customer of SQL Server since the mid-90s starting out with SQL Server 6. He fell in love with the product and grew into a passionate developer, instructor and architect over the next decade or so. A few years back Tobias joined the program management discipline of the SQL Server Engine. He is currently a Lead Program Manager managing a team of program managers who own the engine-side of the SQL Server & Azure development experience. His areas include T-SQL language, type system, libraries as well as the data-tier application model.
He gives great presentations that I have been privileged to see at the MVP Summits. I would wholly recommend his session that focuses on programmability features that are new to SQL Server 2012. There are exciting features such as the Windowing function, Lead and Lag and iof you want to know more about these I would recommend this session Programmability in SQL Server 2012 and SQL Azure with Tobias Ternstrom.


I am very fortunate that I am given time to go and see these people in action. I work for a company that fully supports my development with the people who are at the cutting edge of SQL Server. The SQLBits committee try to bring these people to the UK for the benefit of the community. SO if you have had thoughts about attending these sessions. I would go for it. It is not often you get to spend a full day with the Program Managers of SQL Server technologies on your doorstep

 

Oh and someone let the Queen know...... Buck Woody is in town.... I better feed him this time :-)

June represents an exciting time for the SQL Server community with events all over the country in the next few months and there is plenty of knowledge to be gained from willing speakers enthusiastically sharing their knowledge. Furthermore, Paul Randall and Kimberley Trip will be conducting their highly recommended immersion events at London Heathrow in June.

There are other big names within SQL Server that will be teaching this year. The company I used to work for, QA, has excellent trainers teaching SQL Server who I would always recommend. Occasionally a big name speaker will be take a course, unknowingly to the community. Solid Quality Mentors is such a company where their staff will teach at QA offices from time to time. And I know from conversation with Itzik Ben-Gan that he will be teaching Advanced TSQL within QA offices in London during the week of Oct 3-7. A link to the course details can be found here.

http://www.qa.com/training-courses/technical-it-training/microsoft/microsoft-sql-server/microsoft-sql-server-2008-and-r2/advanced-t-sql-querying,-programming-and-tuning-for-sql-server-2005--2008

So if you want to be taught by the best experts, consider checking www.QA.com for their advanced SQL courses, you could find yourself being taught by the best in the business in their field.


Chris

 

Posted by Testas | with no comments
I am at the beginning of touring user groups around the UK so on train journeys back from a user group meetings I have had time to write out my thoughts on some of the new features that are available in SQL Server C TP 1.

The vision from Microsoft regarding this release looks the same as it was in SQL Server 2008 R2

Empowering IT – Making it easier for IT departments to manage the scalability and availability of SQL Server

Dynamic Development - Making it easier for database developers to develop SQL Server databases in a consistent application

Pervasive Insight - extending the reach of BI to include business users as well as IT professionals and developers

What is notable is the fact that Microsoft will scale SQL Server to different platforms including

Appliances - SQL Server editions including Parallel Data Warehouse and Fast track that have a dedicated purpose using SQL Server

Box - the traditional implementation that most will be familiar with running a specific edition of SQL Server on a server.

Cloud - SQL Azure, a cloud-based service offering data storage capabilities

The capabilities that are available on each platform will vary, in this blog I will concentrate on the new features that will be available on the box with the release of SQL Server Denali CTP ( Community Technical Preview) 1 available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

In no particular order, the following are a summary on some features of SQL Server Denali CTP 1

HADRON

One features that addresses the scalability and availability vision is HADRON or High Availability Disaster Recovery Always ON. This is a technology that tightly integrates the database mirroring architecture with Windows Clustering. The key benefit that this has over database mirroring is that up to 5 secondary servers can be defined, which are readable and can be queried. Like database mirroring, endpoints are used to connect between primary /secondary instances like in Database mirroring. Furthermore as the architecture is very similar to database mirroring, Database Mirroring configuration from SQL Server 2005 onwards can be upgraded to HADR.

CREATE SERVER ROLE

You now have the ability to create user defined server level role on an instance of SQL Server. This provides a greater degree of granularity when setting security at an instance level. As a result the  sp_addsrvrolemember and sp_dropsrvrolemember are being deprecated and being replaced with the ALTER SERVER ROLE instead. Server roles can be created using Transact-SQL or within SQL Server Management Studio.

Startup Options

You can now graphically configure SQL Server startup options within the instance properties in SQL Server Configuration Manager

Contained databases

Contained databases are databases that contain all the objects and metadata for a database without having dependencies on the SQL Server instance. This makes it easier to move databases between different instances of SQL Server. Contained databases can be defined in one of three configurations:

NONE – This means that the database does have dependencies on the SQL Server instance. This is how all databases created on a SQL Server instance are defined by default
FULL – As yet not available in Denali CTP 1, my understanding is that objects defined in these databases do not have dependencies on the SQL Server Instance
PARTIAL – Some objects have dependencies on the SQL Server instance, other objects are contained within the database

Transact-SQL additions and enhancements

There are a number of T-SQL additions and enhancements in this release of SQL Server including Paging, SEQUENCE and THROW

Paging

The ORDER BY clause has been enhanced to include the ability to page results. Example from Books Online include:

USE AdventureWorks2008R2;
GO
--Using Order by to return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

-- Use OFFSET to skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

-- Use OFFSET to skip 0 rows and return only the first 10 rows from the sorted result set using FETCH --NEXT.

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID     OFFSET 0 ROWS    FETCH NEXT 10 ROWS ONLY;   

-- OFFSET and FETCH values  can be defined within variables

DECLARE @StartingRowNumber tinyint = 1      ,
@FetchRows tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC     OFFSET @StartingRowNumber ROWS FETCH NEXT @FetchRows ROWS ONLY;
GO

SEQUENCE

A feature available to ORACLE database professionals is now available to SQL Server database professionals. SEQUENCE enables you to define a sequence of numbers which is stored and retained in memory. My good friend Aaron Bertrand has two excellent blog pieces on how to implement SEQUENCE and the performance impact that I would recommend:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/13/sql-server-v-next-denali-take-one-part-sequence-and-one-part-set.aspx

THROW

You now have greater flexibility with the TRY…CATCH Transact SQL statements with THROW being introduced in SQL Server Denali. This raises an exception and then transfers execution to a CATCH block.   

SSIS enhancements

I will be blogging more on these changes in the future however to areas stand out regarding SSIS which will make life a lot easier

Usability

The usability of SSIS has been improved within Business Intelligence Development Studio. The ability to undo and redo changes in the designer is a small change, but of massive benefit while developing SSIS packages. Copy objects between packages is a lot easier and you can customise the toolbox more easily.

Deployment

There is a new project deployment methodology that will deploy SSIS at a project level rather than the package level that we have been previously used to. The package method of deployment still exists and is referred to as the legacy deployment model.  The following tables is taken form Technet Wiki
http://social.technet.microsoft.com/wiki/contents/articles/project-deployment-overview-in-sql-server-quot-denali-quot-ctp1-ssis.aspx  
that shows the differences and similarity between the project deployment methodology and the legacy deployment methodology

When Using the Project Deployment Model... When Using the Legacy Deployment Model...
A project is the unit of deployment. A package is the unit of deployment.
Parameters are used to assign values to package properties. Configurations are used to assign values to package properties.
A project, containing packages and parameters, is built to a project deployment file (.ispac extension). Packages (.dtsx extension) and configurations (.dtsConfig extension) are saved individually to the file system.
A project, containing packages and parameters, is deployed to the Integration Services catalog on an instance of SQL Server. Packages and configurations are copied to the file system on another computer. Packages can also be saved to the MSDB database on an instance of SQL Server.
CLR integration is required on the database engine. CLR integration is not required on the database engine.
Environment-specific parameter values are stored in environment variables. Environment-specific configuration values are stored in configuration files.
Projects and packages in the catalog can be validated on the server before execution. You can use SQL Server Management Studio, stored procedures, or managed code to perform the validation. Packages are validated just before execution. You can also validate a package with dtExec or managed code.
Packages are executed by starting an execution on the database engine. A project identifier, explicit parameter values (optional), and environment references (optional) are assigned to an execution before it is started. Packages are executed with the dtExec and DTExecUI execution utilities. Applicable configurations are identified by command-prompt arguments (optional).
During exeuction, events that are produced by the package are captured automatically and saved to the catalog. You can query these events with Transact-SQL views. During execution, events that are produced by a package are not captured automatically. A log provider must be added to the package to capture events.
Packages are run in a separate Windows process. Packages are run in a separate Windows process.
SQL Server Agent is used to schedule package execution. SQL Server Agent is used to schedule package execution.
 
BISM

BISM or Business Intelligence Semantic Model is one of the major additions of SQL Server Denali that will change the way we potentially work with Business Intelligence. The most common misconception of BISM is that people think that it is specific to Analysis Services. It is not, it is hosted by SQL Server Analysis Service, however Crescent (the new adhoc reporting tool complementing SSRS) must have a BISM model in order to work. A second misconception is that the introduction of BISM marks the end of traditional Analysis Services solutions that uses UDM. Again this is not the case. UDM will still play an important role that can be summarised by the following graphic which I do not know who created but I believe came from a TechED session 
 

UDM versus BISM






The BISM is designed to combine the UDM model used by SSAS with the SMDL model used by previous Report Builder applications, representing the model in a relational manner while getting the BI sophistication from UDM. However BISM will evolve over time and as UDM is a more mature model you will still see the relevance of UDM within organisations for years to come. Specifically if you require advance calculation that cannot be currently handled by DAX, (the new querying language for PowerPivot that extends to BISM). Then you will be likely to use UDM.
BISM is designed to store the data in the VertiPaq in-memory column store. VertiPaq uses compression algorithms along with multi-threaded query processing that delivers fast performance in retrieving huge data volumes. So much so that this technology is also being introduced in the Database Engine in SQL Server Denali with the new feature of columnar indexes - this is Vetipaq again. So if you have data that cannot fit into the memory, then UDM becomes a consideration. I say that as Vertipaq can be configured to query against data sources therefore preserving the memory that vertipaq uses, but that is for another blog.

To quote the SSAS team from the following blog http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx

“You might ask – Why do we have two types of models in Analysis Services? Which one should I use? Is the UDM going to be deprecated now that we have the BISM? The answer is NO! Analysis Services just got a lot better in SQL Server “Denali” with the BISM and VertiPaq and DAX! However the UDM is a mature and industry leading technology and is here to stay. Let me repeat – UDM (OLAP) models are not being deprecated!”

So there are exciting times in this area and I watch and partake with interest as it develops through time
 

Crescent

In February 2010, I was sat in a room with two other MVPs (Microsoft Valuable Professionals) in Redmond as 5 members of the SSRS team wanted to show us the future of SSRS adhoc reporting . While the demonstration was being given, the MVPs where like kids in a sweetie shop. I don’t mind admitting I was drooling, and it has been very difficult to keep quiet about it since then. Like BISM, Project Crescent is designed to complement the existing report designer in Business Intelligence Development Studio and Report Builder applications that is provided by SQL Server Reporting Services. It relies on a BISM model to work, but its compelling selling point is that if present reports in a Silverlight front end browser that is extremely dynamic. For example, rather than looking at a static chart, the chart can be replayed like a DVD over time to see the result grow within the chart. This is certainly a very useful tool that can be stored in a SharePoint gallery, and while it may be touted as an adhoc reporting tool, I envisage BI professionals using it to create some standard reports that are currently created in Business Intelligence Development Studio.  Check out http://www.youtube.com/watch?v=FfRpfCav9hg for your first taster…its awesome! 

So there are some of my thoughts on what is available in SQL Server Denali CTP1, I know that I have missed out some feature but I hope that future blogs will give you more information about specific topics 

Thanks 

Chris    

 

More Posts Next page »