Monday, June 21, 2004

Rendezvous with YUKON

Resources Covered: -

· Introduction
· Review of Features
· Enhancements
· DTS Redefined
· Deployment and Management
· Hand Shake of Brothers (.NET and YUKON) or Marriage of Romeo and Juliet.


Author: - Veer Ji Wangoo

Applies to : - SQL SERVER “YUKON” Beta 1

Target Audience :- SQL DBA,Solution Architects,IT analysts ,Developers etc


Introduction

The world of Programming had never moved so fast as it did in last couple of years. After Y2K the things got so fast that it became increasingly difficult for lesser mortals to keep the pace with changing technologies (particularly if one got stuck on some Legacy project).

But As I saw the only one way to update was to keep on reading the white papers (I wish I could start a daily newspaper for all IT professionals like Washington post). So alternative is the white paper like this.

Few Months back I heard about the YUKON and sooner I brought it under my Microscopic thought. So here it an attempt for a successful failure or failed success. However I would reiterate the fact that this paper is for educational and awareness purpose only.

The new version of Microsoft SQL server, named “YUKON” is a next generation robust, scalable, Reliable database for windows.

We can write a new bible on its marketing propositions and value editions, but we will concentrate more on technical stuff.

Review of Features

There is lot of activity going between the middle tier ADO.NET and the backend SQL 2000.With DOT NET CLR, it became more important for the DBAs to know what lies above their head and same for the Architects/Analysts/programmers etc to know what lies below their feet. As all of us know net Dot net environment is simply outstanding. But for this beautiful Building to stand the cross current of competitiveness it required a strong foundation (better than SQL2000), So here is what YUKON gave it.

The infrastructure of YUKON is divided into following components and
Constituents ,all working in tandem.

Some of then are being listed as

· SQL SERVICE BROKER
· REPORTIG SERVICE
· NOTIFICATION SERVICE
· SQL SERVER MOBILE EDITION
· WORK BENTCH
· DTS-WIZARD


Lets take a small trip around this YUKON park one by one. We will compare it with some other features currently available as well.

SQL Service Broker:-

For all those who have worked on MSMQ /PMQ and all other asychrnous mechanisms to make the applications more fault tolerant and ended up writing huge lines of code here is a good new for all of them.

Service broker gives a more scalable and fault tolerant architecture of message routing. It allows internal and external processes to send and receive messages using normal T-SQL.

Reporting Service: -

It is a complete server based platform for creating and managing reports from the data. It has lots of new API s that can be used to by developers .data providers to integrate reporting with the legacy systems as well as some third party tools. Major leap forward is the expansion of Microsoft Business Intelligence vision.

It is shipped with the Yukon along with the tools for creating, managing and viewing reports. It also has separate engine to host and process reports. During the set up it self it will ask for the reporting server and the virtual sites for the IIS to be created for reports. It is one of the best features that one can utilize .

Notification Service :-

For operation and administration of SQL we have been using notifications through alerts and operators. Sending E-mail / pagers /Netsends have been in SQL since long. But whats new is that it is a service of its own now. Added to that it can be subscribed for particular trigged action and reaction on data modification, Job out comes etc.we can send the notification to various devices as Mobiles/PDAs/Messenger services and off course Emails.

SQL Server mobile Edition: -


All of us are aware of SQL CE which shipped along with SQL 2000 .It is now renamed as SQL Server Mobile Edition and can be developed and managed using SQL Server “WORK BENCH’.
We can create the Mobile Edition database on the desktop or on the devices directly from the SQL workbench.

As such any database manipulations become independent of the device/site where the Database is residing, this includes the subscription and publication of database, making queries across to-and –fro to the devices. DTS into the mobile edition from non-SQL databases using Whidbey Applications has been made feasible.

SQL Server Workbench: -

One of the greatest and revolutionary features of this system is WORKBENCH. It is actually a DBA suite but has been made fine for developers as well.
There are two types of workbench BI-workbench and SQL server –workbench. Both are capable of doing similar tasks like developing projects and solutions. Point to clear here is that solution can contains more than one projects, while projects contain data sources, data source views,dts packages and other miscellaneous things like dml/ddl files.

The BI workbench doesn’t require direct connection to the SQL server RDBMS to design packages etc nor any connection to save your work.it works just like VS.NET project studio. one can implement VSS on to it and check for code modifications as well

SQL Server workbench unlike BI- workbench is aimed primarily at DBAs to manage SQL.Analysis and reporting servers. It also supports designing, executing and scheduling DTS packages, but does not include source control for packages saved on sql server (rather than file system)

To chose between two is entirely your choice. However key is if you want to execute the package etc directly put it on sql server workbench otherwise to develop, test and do cross platform development before the deployment into production then your choice is definitely BI-workbench.

It supports back up to SQL 7.0 and can be effectively used to connect and work with SQL Servers, Analysis services and Mobile Editions as well.

It is focused primarily on development, deployment, and Management. there are lots of constituents that are .We will just name and define some here.

1. Query Editor: - Replaces Query Analyser.
2. Xquery Designer: - Introduces support to write queries for xml.
3. Windows installer: - used by Yukon for installation but no more typical and minimal modes are there. But simple customization options are available.
4. Consistence Checker for set up - Set up configuration checker validates the target machine for deployment. In case of any issues it guides the installer for necessary action before deployment.
5. Failure reporting for set up: - creates log and directs user for corrective action.
6. Computer Manger: - Replaces Client network utility/Server network utility and SQL service manager; It is accessible through WMI (Windows management instrumentation). It helps to work with lots of things like Analysis Services, Previous versions of SQL Servers, Reporting services, MS cum Full text search etc
7. Object Explorer: - It is a combination of Enterprise manager and Analysis service manager.
8. Profiler and Tuning Advisor :- Much like profiler and index tuning wizards but lots of features like profiling Analysis services, saving traces/show plans as XML etc
9. SQL Server agent :- Much like earlier one but with few changes like creation of agent user roles ,creation of proxy roles and assignment of users/groups to these Proxy groups for monitoring/creating/editing of Jobs.

Enhancements

The enhancements in YUKON can be divided into two categories namely the Database Engine enhancements and Reporting service enhancements.However we will not go along these lines reason being there are other generic enhancements in yukon as well.
Some of the genric problems that we used to face like Server Reboot incase of change in SQL system parameters.the Database affinity for CPU and RAM memory may also no \w be altered without requiring the restatrt.

so we follow a conventional line to enhancements .

When we talk of enhancements the following categories have been mainly enhanced :-
· .net framework programing
· batches
· Data types

Lets take them one by one

1 .Net Framework :- we will take it up in another chapter But briefly describing it here.Just like any other Object we can now define and create a database object inside an instance of sql server that can be programmed in the microsoft .NET Framework common language runtime.As such all the properties of this object like Stored Procedures ,model functions,triggers,user defined types and aggregates can be done in rich CLR languages.We can create,alter and drop assemblies

2. Batches :- YUKON introduces a new feature called multiple active result sets (MARS).it allows client drivers to have more than one pending request per connection.It can interact with ODBC/OLEDB and batch execution environment


3. DataTypes :- There are new data types as well as enhancements on several existing datatypes.This is very important fom the common usage point of view.I will take some leverage to explain them here.
XML datatype:- helps you store and interact with the xml document or fragment.This data type can be in Instances of this data type can be ued in tables,functions,Sps,etc.We can put constraints on it by refercing the validating schema .As such we can specify Xquery against the xml data stored and apply inserts/updates/deletes to the data.
UTCDatetime :-is a datetime datatatype which has intellisence of time zone.As such for global operations of Ecom sites it becomes handy
Date:- datatype has been modified to the precision of 100 NS
Varchar(max)-Nvarchar(max)-varbinary(max) can hold data upto 2GB.so be free of 8000 limit of varchar.

4. Failover Clustering :- Yukon provides a high availibility support for server scope failures.With FailOver clustering the OS ad SQL server work in tandom to protect from failure by providing reduntant hardware and an automated mechanism to ove the database server to secondary hardware in the event the primary failes.Upto eight nodes we can map in ,depending on the OS version. Yukon has brought Analysis,notification,replication environment into fail over clustering as well.

5.Database Mirroring :- The basic work it does is shiping the trnsaction log to another server ie mirroring
the work don on primary.As such durng planned downtime DBA s can save al the downtime incurred in the past.BY usage with service broker applications can connect with secondary server within no time.Ulike failover clustering ,the mirrored server is fully cached and ready to accept workloads because of synchronised state.
One of the most imporant feature sof this mirroring is two way udation,which means synchronising the flow in both directions.It requires no standard server controls or SCSI disk of array s etc..

6. Database View:- Database view provides a read only ,stable view of a database that can be created with out any overhead,in case of any divergence / accidental changes View can be used to reapply the pages.

7.Replication :- Although muuch of SQL 2000 replication methodologies seems to be excellent.but adding to what we have already new features like enabling replication through http:// and https:// increases the availability of datat for mobile scenerios which enables synchronisation over the internet.New features like “Peer-to-Peer” model have been introduced to to make applications more scalable for SQL read workload.

8.Online Index Operations :- Gone are the days when any online reindexing wuld cause havocs with exclusive locks and bring the application to stand still.we can continue to make updates and perform queries against the data even during the rebuilding of clustered index.
Additionally the concept of parallel processing allows server to take the workload of online indexing
9.Online Restore :- Sql Server introduces the ability to perform a restore operations without getting the database offline.Only the data to be restored becomes offline not the whole database.However depending on the criticality of the suituation we can chose between the offline and online restore.
10.Fast Recovery :- Users can reconnect to a recovery database after the transaction log has been rolled forward.Earlier versions of SQL Server required users to wait until the incomplete transactiosn have been rolled back,even if the y didn’t need to access those parts of data .But key is that if you are access the shady data of uncommited transaction then normal blocking can be expected.

11. Mirrored backup:- In Yukjon backup media can now be mirrored ,upto four sets .which can be used in case of backup failing.

12. DBCC CheckSum :- Dbcc has been modified a lot to take into consideration the new feaures of yukon.But the exemplary to metion here is the dbcc checksum which verifies data at the page level by doing a checksum of each page,report the partitioning correctness and error free of registered assemblies etc.

13. ShowPlan and Deadlock Enhancements:- Now we can have graphical represntationm of deadlock occurances collected through trace events.The graphical shows dead lock cycle of chains,providing you means to analyse dead occurances.These results can be saved in XML formatAs such we can ship and view the plan without the underlying database.

14. SQLCMD :- A new command line utility replacing OSQL/ISQL.It is coming out with rich set of commaands, yet to be reviewed for writing in this paper.(So lets watch out on this)

15. Dedicated Administrator Connnection:- to access a running server even if the server is in no mood to talk to anybody (Hung or otherwise unavalable-like my Girl friend) ,It is hot wire connection and activated by varous memebers of the sysadmin role and is only available through the SQLCMD commmand locally as well as on remote machine.thus DBAs have a special access to server for diagnosis and treatment of SQL issues.

16. SQL management Objects (SMO): - To enhacne and extend the functioanlity of SQL server database and replication management through programing ,we have SMO object model in .NET environment.it replaces and extends the current DMOs.(they will stillbe there but without new features.).SMO is implemented as .net assembly by using WMI xobjects as before.So now all CLR features can be used to create applications that can help in the Online maintenance and adminsitration of SQl server.

17. Transact SQL :- previously all T-SQL was as per ANSI 92 stadards now YUKON implements ANSI 99 standards so we must expect lots of changes as new releases of yukon come along.many of the improvenments are based on the assumption that we need to be more expressive in querries and as such Microsft has taken lot of feed back from customer using T-SQL.there are lots of updates on this section beyong the scope of this article.For mention only Common table Exoression,recursibe queries,pivot and un pivot operators,realtional operators,outer apply ,error handling capability through try catch constructs..
18. Security :- a beautiful paradigm for security, both for developers and admistrators.However more details are likely to come in beta 2 .Currently lot of investment is being made in number of features covering a broad spectrum like enforcing policies for sql login passwords(authentication scope) with finer granularity in permissions (authorization scope ),to seperation of owners and schemas (in security management space)


DTS Redefined



DTS has been raw defined in the since that it is totally new and matured for enterprise development and deployment. The overall architecture of the product has changed a lot.
The designers have built several new wizards into workbench environment to help the DTS.

DTS Import/Export wizard

Much like as we have in sql 2000 but with some new features like status monitoring, control flow check, saving the result file as .dtsx or text file ,mailing it etc and new GUI as well.
However for the DTS packages created in 2k and below and you want them to run in YUKON. Then we are sorry to say you w\cant cum toll free. You will have to use migration wizard for it become YUKON complaint and run tax free…(everything comes at a cost)

DTS Package installer wizard

Now this wizard is something new. Here you can use BI-workbench to create a new deployment project where in you can set the deployment out put path where in your deployment files will be kept. You can have DTS packages, the DTSInstall.exe, the DTSdeploymentutility.Msi as well manifest file. Simple clicking of exe will call wizard and you are ready to deploy.

DTS Configuration wizard


As the word of this topic sound CONFIGURATION wizard guides you through the set up for creating configuration but what exactly is configuration? Well it is same like Dynamic Properties tasks in 2000. to configure the package variables and properties at run time. Only difference is that format is XML, they are external to package and values get set before execution rather than during run time. In any package assigned to a wizard, there can be more than one configuration however configuration lower in the list will get priority-i.e. The largest one.


Deployment and Management


If you ask any body how to deploy any Windows Application, Answer will be “click next”. That is true but that’s where most of us make the first mistake.

Although MS gives us easy deployment wizards but still there are lots of place where we need to think,analyse ,estimate and make decision. It is the foundation for getting good performance out of any application.

To get the best out of YUKON we must set configuration options properly, basic parameters and locations of files provides greater flexibility for application security and resource management.

We have Windows Installer like SQL2000 but minimum and typical modes are no longer implemented. Instead we have a feature tree with default options enabled and administrators can then customize by selection and clearance of items involved on feature tree. Remote set up and Multiple installations are also available in this wizard.

Coolest feature is the Setup consistency checker which checks the target computer for all necessary infrastructure before setup begins. In case of any discrepancy, It will give necessary instructions and actions to be taken. It uses WMI technology to do the same.

Even if the setup fails the error codes are well trapped ,with full description of error along with recommended corrective action


Hand Shake of Brothers

As I mentioned in the review about the tighter integration of CLR and SQL in Yukon. Some of it got its mention in enhancements section also.
I am putting a separate article on this issue as I this is a vast area to summarize here.