Unit Testing Databases Using VSTS DB Edition

 

As Part of MSDN Webcast series today i hosted my third webcast on “ Unit Testing Databases Using VSTS DB Edition”.

you can download my presentation from following link…

http://www.cognitioninfotech.com/downloads/Unit%20Testing%20Databases%20Using%20VSTS%20DB%20Edition.pdf

Installing TFS 2010 on Windows 7

Team Foundation Server or TFS is now well know tool for Application Lifecycle management. It almost support everything which you need during your application development process. starting from requirement gathering documentation to deployment. Bug tracking, source control, testing, build management etc.  during my couple of assignments and client meets many people asked a question Why TFS and why not Visual SourceSafe or any other open source tool? this question is really difficult to answer as everyone carry there own features and drawback.  But many time i also thought what about small organizations, or small teams , do they really need all TFS features and if not then why they should invest so much money to deploy TFS ? as TFS deployment was not at all easy job. and it required huge investment also. if this is case then  what about a person who want to use Source control on his personal machine ? he can not install TFS on his machine as TFS only support Server OS and not any client OS. But now we have a good news for all those people who want to use TFS just for source control and on their own machine, and for small Teams or small organizations too…

How to access SQL Azure from SQL Management Studio

It now more than a month Microsoft announced CTP of SQL Azure… SQL Azure a service to host full RDBMS database on Cloud. currently this service is available as CTP, so have very limited access and less features, but still it is a SQL server running there at cloud and very soon we can expect all SQL server features on SQL Azure.

Today when i started working on it, the best thing i found in it is, “you can access SQL Azure from your machine using your favorite SQL IDE tool, i.e.  “SQL Server Management Studio 2008”.

But very first time when i tried to access it, i got a nice error but thanks to my friend Jacob, he redirected to me to a site where that author mentioned a small but funny trick to use it.

so what i did -

I simply open SQL Server management studio 2008 from start menu, I entered full server name as “servername.ctp.database.windows.net”, my login name and password. and said connect. a simple and standard process which we follow every time to access SQL Server database.

this Error saying “Invalid Object name sys.configurations. (Microsoft SQL Server Error: 208)”

now how to connect to SQL Azure, i asked one of my friend Jacob who is a SQL MVP, and thanks to him he redirected me to a nice site where athlete gave one funny trick to make it work.

basically what you have to do is when you start SQL Server Management studio it prompt you a Connect to Server Windows (like this)

 

Now don’t Enter SQL Azure server name and login details here… simply say cancel

and now say “New Query” it will again open a similar windows like previous one. now click on option button.

Now at login tab enter full server name, then select authentication type as SQL Authentication as SQL Azure only support SQL authentication and the Enter you login and password.

 

after this move to connection Properties Tab and Enter Database name which you created at SLQ Azure portal http://sql.azure.com

Please note this is important to enter this name as, SQL Azure don't allow you to connect  Server, it allow you to connect to Database.

after your enter database name them simply click “connect”

if everything goes right then you will prompted with following error message

“Unable to apply connection settings, The detailed error message is “ANSI_NULLS” is not a recognized SET option.

don't worry about this error, simply click “OK” .

Now you are ready to run your queries. try and Enjoy SQL Azure.

SQL Azure Explorer Add-in for VS2010

New version (0.1.1) of SQL Azure Explorer, an Add-in for VS2010 has been released with better UI performance

you can download new build at http://sqlazureexplorer.codeplex.com/

some of features mentioned on CodePlex site about SQL Azure Explore

• SQL Azure Explorer which contains:
  • Databases
  • Tables with columns
  • Views with columns
  • Stored procs with parameters
  • Functions with parameters
• Context menues for:
  • Open Sql Editor Window
  • Select Top 100 Rows
  • Script as CREATE for all tables, views, stored procs and functions
• SQL Editor Window with built in:
  • SQL Execute
  • Off line parser
  • Script formatter

Configuring Windows Azure development machine

(TechStart: Microsoft Windows Azure Training Program )

This article explain you about installing and configuring your machine for Windows Azure development. I was preparing a help file for TechStart-windows Azure Program students so that they can prepare their PCs/Laptops for windows Azure development on their own, same article am sharing with you all. please note this document was written for students.

I will be publishing this in 3 separate posts, first for beginners where we will start from installing windows vista, VS, SP1, SQL Server , KB patches , Azure SDK etc. later on second post for Windows 7 with VS 2008 and third one for all OS and VS versions.

now lets start with first one - fresh installation on vista machine. I assume that you have a good configuration machine / laptop with at least 1 GB of RAM.

so lets make a list of all required software

1. Windows Vista (Ultimate, Enterprise, business etc.).
2. Windows Vista Service Pack 1 - Download here
3. Visual Studio 2008 Professional Edition - Download Trial OR Microsoft Visual Web Developer 2008 Express with SP1
4. Microsoft Visual Studio 2008 Service Pack 1 - Download here
5. Microsoft SQL Server 2005 - Download Express edition here OR Download Trial Editions
6. Hot fix patches for windows vista and visual studio (download from following links)
   1. Hotfix: Native Debugging Improvements
   2. Hotfix: Improve Visual Studio Stability
   3. Hotfix: Support for FastCGI on the Development Fabric
7. Windows Azure Tools for Microsoft Visual Studio July 2009 CTP - Download here

that's all what you need to keep ready before you start preparing your machine.

so very Important thing is we are going to do a fresh installation of Windows Vista,  either on your default C drive or on another drive of your hard disk. so before we start take a backup of your data on external device or another drive of your disk.

Installing Windows Vista

Now Put Windows vista DVD in your DVD ROM and reboot your machine from DVD. it will automatically start the Windows vista setup from DVD. Ref following screen. if you don't know about operating system installation then you can read more detail steps about how to Install windows vista at http://support.microsoft.com/kb/918884

follow the instructions in setup wizard carefully and complete the installation. It may take around 30 minutes (depend of your machine configuration) and  on completion it will display login screen. ( Ref. following ) which means you can done with Vista installation.

now provide the password and enter into the system.

after that Next step is install Windows Vista Service pack 1.

after installing Service Pack 1 it may ask you for reboot the system. reboot the system to complete the installation.

ones you are done with Service Pack installation then next step is

Enable IIS7 with ASP.NET and WCF HTTP Activation

start -> Control Panel -> Programs -> Click on Turn windows feature on or Off. it will show a security confirmation window , say Continue to that. it will open another windows just like this.

 

Under Microsoft .NET Framework 3.0, select Windows Communication Foundation HTTP Activation.

Under Internet Information Services, expand World Wide Web Services, then Application Development Features, then select ASP.NET and CGI.

then click ok to Install the selected features.

Next step is Installing Visual Studio 2008 and SQL Server

Put Visual Studio 2008 DVD (Professional edition Trial DVD) in DVD Rom and start the setup. follow the wizard to complete the setup.

(Note: as I am using Team System edition it shows Team system, following screens may differ depend on your Visual studio 2008 edition.)

 

 

it will complete installation of Visual Studio 2008 and SQL Server 2005 Express edition.

if you noticed in above screen that Microsoft SQL Server 2005 Express edition is selected by default, so Now you don't need to to install it separately on your machine, only if you are installing Visual Studio Web developer Express edition then only you need to install SQL Server Express edition separately.

ones you are done with Visual Studio 2008 Professional edition Trial or Visual studio visual web developer express edition then start installing Service Pack 1 for Visual Studio 2008.

many times this take more than 30 minutes,  so sit back and have some snacks :)

after installation of SP1 for VS2008, time to install some important Hot fix for Vista and VS2008

Install the Hotfix: Native Debugging Improvements (KB967631 - update for VS2008 SP1 debugger)

Install the Hotfix: Support for FastCGI on the Development Fabric (KB967131 - Update for Windows Vista)

Install the Hotfix: Improve Visual Studio Stability (KB963676)

after installing Hotfix for windows vista it will ask you for reboot, reboot the system.

Now system is Ready to install Azure SDK and its tools for VS. since we downloaded Windows Azure Tools for Microsoft Visual Studio July 2009 CTP, we DO NOT NEED to download and install Azure SDK July CTP separately. SDK is part of same installer.

installing Windows Azure Tools for Microsoft Visual Studio July 2009 CTP

ones setup complete open Visual studio 2008 from start menu, go to new project and check if Cloud service template is available or not, if its available then you are done with your installation and ready for Azure development. now how to write hello word program that we are going to see in our first lecture. till that you can go through http://www.azure.com and read about azure development.

Connect Remotely to SQL Server Express.

If you are using MS SQL Server 2005 standard edition or enterprise edition this remote connection feature is enabled by default but for SQL Server Express edition by default this feature is disabled , if your application need to connect SQL server express database remotely then you need to enable remote connection manually, here are those 8 simple steps to enable the feature and allow you application to connect remotely

1. Go to start menu, browse to Microsoft SQL Server 2005 – > Configuration Tools –> and open SQL Sever Surface Area Configuration

2. Click on “Surface Area Configuration for Services and connections”

 

 

3. Select “Remote Connection” , you can see there the option “Local connection only” selected as default selection.

 

4. change this to Local and Remote connections.

5. it will show you a message windows indicating that this change  need SQL Server restart. say “OK”

 

6. now again Browse to main menu-> Microsoft SQL Server 2005 –> Configuration Tools-> and select SQL Server Configuration Manager

 

 

7. In SQL Server Configuration Manager Select Protocols for SQLEXPRESS and double click on TCPIP Protocol.

8. Open IP Address Tag in IP One set Enabled  as ‘Yes’, enter IP of you machine in IP1, and select TCP Port as 1433 ( you may use other port, 1433 is default port on which SQL Server communicates") in all IP section you can set same port no as 1433. ( no need to set dynamic port values, leave them with default values)

 

Click Apply and then ok, you are done with settings, now in SQL SERVR 2005 Services node right click on SQLEXPRESS and say restart to restart SQLEXPRESS Service.

Now from remote machine simply Enter IP address of this machine on which you did above configuration as a Server name, you don't need to specify instance name if it is default instance. Enter SQL Server Login name and password to connect to database.

Microsoft SQL Server 2008 Report Builder 2.0 released

On October 17^th, Microsoft SQL Server 2008 Report Builder 2.0 was released as part of the SQL Server 2008 Feature Pack.

Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 Reporting Services. including:

• Flexible report layout capabilities of SQL Server 2008 Report Definition Language

• Data Visualizations including charts and gauges

• Richly formatted textboxes

• Export to Microsoft Office Word format

Features specific to Report Builder 2.0 are focused on simplifying the process of creating and editing reports and queries and include the following:

• Easy to use wizards for creating table, matrix and chart data regions

• Support for directly opening and editing reports stored on the report server

• Support for using server resources such as shared data sources

• Query designers for multiple data sources including a Microsoft SQL Server-specific query designer

The download provides a stand-alone installer for Report Builder 2.0

Download here: http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6

Source: www.microsoft.com

An Overview of SQL Server 2005 - I

An Overview of SQL Server 2005 - I

for the Database Developer

.NET Framework Integration
With the release of Microsoft SQL Server 2005 Beta 2, database programmers can now take full advantage of the Microsoft® .NET Framework class library and modern programming languages to implement functionality within the server. Using common language runtime (CLR) integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice. Microsoft® Visual Basic® .NET and the C# programming language both offer object-oriented constructs, structured exception handling, arrays, namespaces, and classes. In addition, the .NET Framework provides thousands of classes and methods that have extensive built-in capabilities that you can easily use on the server-side. Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two new types of database objects—aggregates and user-defined types—are available. You can now better use the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005 Beta 2 enables you to extend the database server to more easily perform appropriate computation and operations on the back end.

This integration between SQL Server and the CLR provides several major benefits:

· Enhanced programming model: Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL developers.

· Enhanced safety and security: Managed code runs in a CLR environment, hosted by the database engine. This allows .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server.

· User defined types and aggregates: Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.

· Common development environment: Database development is integrated into the Microsoft® Visual Studio® 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.

· Performance and scalability: Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.



You can capitalize on the CLR integration to write code that has more complex logic and is more suited for computation tasks, by using languages such as Visual Basic .NET and C#. In addition, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can now easily organize related code into classes and namespaces, which means you can more easily organize and maintain your code investments when you are working with large amounts of code. This ability to logically and physically organize code into assemblies and namespaces is a huge benefit, and will allow you to better find and relate different pieces of code in a large database implementation.

Managed code is more efficient than Transact-SQL at processing numbers and managing complicated execution logic, and provides extensive support for string handling, regular expressions, and so on. Also, with the functionality that is available in the .NET Framework class library, you now have full access to thousands of pre-built classes and routines that you can access easily from any stored procedure, trigger, or user-defined function. Everything from improved string handling functions, math functions, date operations, access to system resources, advanced encryption algorithms, file access, image processing, and XML data manipulation is easily accessible from managed stored procedures, functions, triggers, and aggregates.

One of the major benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks through a process known as "verification" to ensure that the code is safe to run. For example, the code is checked to ensure that memory is not read that has not been written to.

An Overview of SQL Server 2005 - I

An Overview of SQL Server 2005 - I

for the Database Developer

.NET Framework Integration
With the release of Microsoft SQL Server 2005 Beta 2, database programmers can now take full advantage of the Microsoft® .NET Framework class library and modern programming languages to implement functionality within the server. Using common language runtime (CLR) integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice. Microsoft® Visual Basic® .NET and the C# programming language both offer object-oriented constructs, structured exception handling, arrays, namespaces, and classes. In addition, the .NET Framework provides thousands of classes and methods that have extensive built-in capabilities that you can easily use on the server-side. Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two new types of database objects—aggregates and user-defined types—are available. You can now better use the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005 Beta 2 enables you to extend the database server to more easily perform appropriate computation and operations on the back end.

This integration between SQL Server and the CLR provides several major benefits:

· Enhanced programming model: Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL developers.

· Enhanced safety and security: Managed code runs in a CLR environment, hosted by the database engine. This allows .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server.

· User defined types and aggregates: Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.

· Common development environment: Database development is integrated into the Microsoft® Visual Studio® 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.

· Performance and scalability: Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.



You can capitalize on the CLR integration to write code that has more complex logic and is more suited for computation tasks, by using languages such as Visual Basic .NET and C#. In addition, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can now easily organize related code into classes and namespaces, which means you can more easily organize and maintain your code investments when you are working with large amounts of code. This ability to logically and physically organize code into assemblies and namespaces is a huge benefit, and will allow you to better find and relate different pieces of code in a large database implementation.

Managed code is more efficient than Transact-SQL at processing numbers and managing complicated execution logic, and provides extensive support for string handling, regular expressions, and so on. Also, with the functionality that is available in the .NET Framework class library, you now have full access to thousands of pre-built classes and routines that you can access easily from any stored procedure, trigger, or user-defined function. Everything from improved string handling functions, math functions, date operations, access to system resources, advanced encryption algorithms, file access, image processing, and XML data manipulation is easily accessible from managed stored procedures, functions, triggers, and aggregates.

One of the major benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks through a process known as "verification" to ensure that the code is safe to run. For example, the code is checked to ensure that memory is not read that has not been written to.







_______________________________________

NOTE : This information Published here is ONLY FOR INFORMATIVE PURPOSE of the User Group Members and some part or the full article may be copyrighted or won by the respective owner.

The above Information is either collected from Microsoft website or the various resource kits published by Microsoft or Microsoft partner and this is preliminary information and may be changed substantially prior to final commercial release of the software and cannot guarantee the accuracy of any information Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document.

Source: Article by Matt Nunn published in a SQL Server 2005 Resource Kit

Also available at http://dotnetvally.blogspot.com

SQL Server Shortcut keys from the MSDN ...

SQL Server Shortcut keys from the MSDN ...


Bookmarks: Clear all bookmarks. CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle). CTRL+F2
Bookmarks: Move to next bookmark. F2
Bookmarks: Move to previous bookmark. SHIFT+F2
Cancel a query. ALT+BREAK
Connections: Connect. CTRL+O
Connections: Disconnect. CTRL+F4
Connections: Disconnect and close child window. CTRL+F4
Database object information. ALT+F1
Editing: Clear the active Editor pane. CTRL+SHIFT+DEL
Editing: Comment out code. CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT. CTRL+C
Editing: Cut. You can also use SHIFT+DEL. CTRL+X
Editing: Decrease indent. SHIFT+TAB
Editing: Delete through the end of a line in the Editor CTRL+DEL
Editing: Find. CTRL+F
Editing: Go to a line number. CTRL+G
Editing: Increase indent. TAB
Editing: Make selection lowercase. CTRL+SHIFT+L
Editing: Make selection uppercase. CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT. CTRL+V
Editing: Remove comments. CTRL+SHIFT+R
Editing: Repeat last search or find next. F3
Editing: Replace. CTRL+H
Editing: Select all. CTRL+A
Editing: Undo. CTRL+Z
Execute a query.
You can also use CTRL+E (for backward compatibility). F5
Help for SQL Query Analyzer. F1
Help for the selected Transact-SQL statement. SHIFT+F1
Navigation: Switch between query and result panes. F6
Navigation: Switch panes. Shift+F6
Navigation: Window Selector. CTRL+W
New Query window. CTRL+N
Object Browser (show/hide). F8
Object Search. F4
Parse the query and check syntax. CTRL+F5
Print. CTRL+P
Results: Display results in grid format. CTRL+D
Results: Display results in text format. CTRL+T
Results: Move the splitter. CTRL+B
Results: Save results to file. CTRL+SHIFT+F
Results: Show Results pane (toggle). CTRL+R
Save. CTRL+S
Templates: Insert a template. CTRL+SHIFT+INSERT
Templates: Replace template parameters. CTRL+SHIFT+M
Tuning: Display estimated execution plan. CTRL+L
Tuning: Display execution plan (toggle ON/OFF). CTRL+K
Tuning: Index Tuning Wizard. CTRL+I
Tuning: Show client statistics CTRL+SHIFT+S
Tuning: Show server trace. CTRL+SHIFT+T
Use database. CTRL+U