Microsoft Drivers for PHP for SQL Server Team Blog

Preview Refresh of the Microsoft Driver for Node.js for SQL Server

Jonathan Guerin — Fri, 12 Oct 2012 19:04:00 GMT

Hi everyone,

We've been overwhelmed with the level of interest from the community since our first preview release of the Microsoft Driver for Node.js for SQL Server. We've had a total of 82 issues created, with 24 bugs closed, and 18 questions asked by our users. We've also marked our very first community contribution, which we are always grateful for. Now that a few months have passed since our first preview, we are now releasing a refresh of the driver, along with a few changes.

Name change to msnodesql

The very first thing you'll notice is that we've changed the name of the driver to 'msnodesql'. We wanted to make sure that the name was short and meaningful, and to this end, we've updated our name to reflect this. You will find that 'node-sqlserver' has been marked as deprecated on NPM, and we've published a new package name. We will be using this package going forward.

Github branch changes

Our community has been very eager to pick up the latest changes as we make them, and having a separate 'develop' branch has brought some difficulties. To address this, we've converged all of our development efforts into our 'master' branch. For the users out there familiar with SVN nomenclature, we'll be treating our 'master' branch as our 'trunk'. All development work which reaches an acceptable level of stability will be checked in to 'master'. If you like bleeding-edge bits, this is the branch you'll want to focus on.

We've also created a servicing branch named 'v0.2'. If you prefer stable, tested bits, you should track this branch. This is where we'll be making any changes to the existing stable release if they are required. Please keep in mind that this branch will see very little activity as we focus on our next version. You should only follow this branch if you require stability for your applications. Additionally, please keep in mind that as this driver is a preview release, we do not recommend using it inside a production environment. There will still be bugs in the servicing branch, and we will only address these if they are critical enough. Most of our changes will land in the 'master' branch as we progress towards our v1.0 release.

Finally, given our focus on a 'trunk' and 'service' branch, we've stopped all development work in our 'develop' branch, and have removed it from the repository. If you were previously tracking our progress in this branch, you should switch to 'master'.

Getting the Preview Refresh

You can find the latest bits on our Github repository, at our existing address. You can also download a pre-compiled binary on our download page at the Microsoft Download Center. We provide limited support while in preview on our Github Issues page.

As always, we welcome contributions of any kind, be they Pull Requests, or Feature Enhancements. You can find out how to get involved with our Contribution Guidelines.

Thank you,

Jonathan Guerin
Program Manager
Microsoft Corporation

Announcing Web Matrix 2

Jonathan Guerin — Fri, 07 Sep 2012 22:38:00 GMT

Today we are excited to announce the official release of WebMatrix 2! This version includes tons of new features and improvements. In this overview, we will show you how WebMatrix makes it easier to build websites with PHP and publish them.

Here is the download page if you want to skip the intro and dive right in.

Getting Started

Getting started with PHP in WebMatrix is simple and straightforward. There are 3 main options: you can open an existing site, create a new site from the built-in templates, or build your site based on the common open source applications such as WordPress, Joomla!, and Drupal.

By default, WebMatrix offers two built-in templates: Empty Site for those who want to start coding from scratch with just PHP and a blank template, and Starter Site which is a fully functional sample site with login and page management built on top of PHP and MySQL.

Alternatively, you can choose from dozens of open source applications in WebMatrix App Gallery.

The first time you create your site, WebMatrix will automatically detect any missing dependencies and install them for you. This is especially useful when you build sites which require the time-consuming task of setting up PHP and My SQL on your local box.

Generic and App-specific Code completion

When you are developing your site, WebMatrix offers PHP code completion to make the task easier. As you type, the editor suggests functions or keywords appropriate for the immediate context. The entire PHP function library is at your fingertips when you code!

When you're browsing other people's code (or even your own code from a while back), you might not recognize some of the functions or constructs that you see. Now, without leaving WebMatrix, you can quickly get information on an element by holding the mouse pointer over it.

If you like to work with web applications like WordPress, Joomla! or Drupal, you'll love the application-specific code completion.

Testing Your Sites Locally

Before publishing the changes you’ve made to your websites, it’s a good idea to test them locally. To do this, simply hit ‘Run’ and your site will be launched in the default browser on your local machine. You can also select a non-default desktop browser or mobile simulators to launch your site in.

Publishing Your Sites

Finally, when you are ready to publish your PHP website to Windows Azure or any hosting provider that supports FTP or Web Deploy, select Publish and enter the settings obtained from the hosting provider. If your provider enables you to download the publishing settings in a single profile, you can simply import it into WebMatrix.

The screenshots below show you how to publish to Windows Azure in simple steps.

Download publishing profile

Login to the Management Portal. If you don’t already have a
Windows Azure account, you can sign up for a no-obligation free trial.
Click the + New icon on the bottom left of the portal.
Click WEB SITE, then CREATE WITH DATABASE.
Enter a value for URL, select Create a New MySQL Database from the DATABASE dropdown, and select the data center for your website in the REGION dropdown. Click the arrow at the bottom of the dialog.
Enter a value for the NAME of your database, select the data center for your database in the REGION dropdown, and check the box that indicates you agree with the legal terms. Click the
checkmark at the bottom of the dialog.

When the website has been created you will see the text Creation of Web Site ‘[SITENAME]’ completed successfully.

Import publishing profile

Once your site has been created in Windows Azure, go to the site’s dashboard and select “Download publish profile”
Save the downloaded published profile in your local machine
Return to your site in WebMatrix and click the Publish button at the top left corner.
Select “Import Publish Profile”

Alternatively, you can also choose “Enter settings” to type in the information manually.

Publish

Click Save and then Continue to run the compatibility check in order to make sure the remote server has the required dependencies to host your site. You can skip this check, but it is not recommended.
Review the compatibility results. If any of the items display a warning icon and say “Not Available”, you can click the hyperlink to learn more.
The publish preview shows you what files have changed and are being published. If you are using the Web Deploy protocol, you will also have the option to publish the database along with content. Click Continue to begin publishing.
Publishing progress will show in the notification bar. When it’s done, you will see a link to browse to the remote site.

We’ve only skimmed through a few features which help you create, test, and publish your websites more easily with WebMatrix. You can learn more by visiting WebMatrix website and start building your sites today!

Thanks,

Thao Doan
Program Manager
Azure Applications Platform Team
Microsoft Corporation

Announcing Windows Azure Mobile Services

Jonathan Guerin — Tue, 28 Aug 2012 20:16:00 GMT

We are very excited to re-post the recent announcement for Windows Azure Mobile Services! Read up on this at Scott's blog:

I’m excited to announce a new capability we are adding to Windows Azure
today: Windows Azure Mobile Services

Windows Azure Mobile Services makes it incredibly easy to connect a scalable
cloud backend to your client and mobile applications. It allows you to easily
store structured data in the cloud that can span both devices and users,
integrate it with user authentication, as well as send out updates to clients
via push notifications.

Today’s release enables you to add these capabilities to any Windows 8 app in
literally minutes, and provides a super productive way for you to quickly build
out your app ideas. We’ll also be adding support to enable these same scenarios
for Windows Phone, iOS, and Android devices soon.

Read this getting started tutorial to walkthrough how you can build (in
less than 5 minutes) a simple Windows 8 “Todo List” app that is cloud enabled
using Windows Azure Mobile Services. Or watch this video
of me showing how to do it step by step.

Getting Started

If you don’t already have a Windows Azure account, you can sign up for a
no-obligation Free Trial. Once you are signed-up, click the “preview
features” section under the “account” tab of the www.windowsazure.com
website and enable your account to support the “Mobile Services” preview.
Instructions on how to enable this can be found here.

Once you have the mobile services preview enabled, log into the Windows Azure
Portal, click the “New” button and choose the new “Mobile Services” icon to
create your first mobile backend. Once created, you’ll see a quick-start page
like below with instructions on how to connect your mobile service to an
existing Windows 8 client app you have already started working on, or how to
create and connect a brand-new Windows 8 client app with it:

Read this getting started tutorial to walkthrough how you can build (in
less than 5 minutes) a simple Windows 8 “Todo List” app that stores data in
Windows Azure.

Storing Data in the Cloud

Storing data in the cloud with Windows Azure Mobile Services is incredibly
easy. When you create a Windows Azure Mobile Service, we automatically
associate it with a SQL Database inside Windows Azure. The Windows Azure Mobile
Service backend then provides built-in support for enabling remote apps to
securely store and retrieve data from it (using secure REST end-points utilizing
a JSON-based ODATA format) – without you having to write or deploy any custom
server code. Built-in management support is provided within the Windows Azure
portal for creating new tables, browsing data, setting indexes, and controlling
access permissions.

This makes it incredibly easy to connect client applications to the cloud,
and enables client developers who don’t have a server-code background to be
productive from the very beginning. They can instead focus on building the
client app experience, and leverage Windows Azure Mobile Services to provide the
cloud backend services they require.

Below is an example of client-side Windows 8 C#/XAML code
that could be used to query data from a Windows Azure Mobile Service.
Client-side C# developers can write queries like this using LINQ and strongly
typed POCO objects, which are then translated into HTTP REST queries that run
against a Windows Azure Mobile Service. Developers don’t have to write or
deploy any custom server-side code in order to enable client-side code below to
execute and asynchronously populate their client UI:

Because Mobile Services is part of Windows Azure, developers can later choose
to augment or extend their initial solution and add custom server functionality
and more advanced logic if they want. This provides maximum flexibility, and
enables developers to grow and extend their solutions to meet any needs.

User Authentication and Push Notifications

Windows Azure Mobile Services also make it incredibly easy to integrate user
authentication/authorization and push notifications within your applications.
You can use these capabilities to enable authentication and fine grain access
control permissions to the data you store in the cloud, as well as to trigger
push notifications to users/devices when the data changes. Windows Azure Mobile
Services supports the concept of “server scripts” (small chunks of server-side
script that executes in response to actions) that make it really easy to enable
these scenarios.

Below are some tutorials that walkthrough common
authentication/authorization/push scenarios you can do with Windows Azure Mobile
Services and Windows 8 apps:

Manage and Monitor your Mobile Service

Just like with every other service in Windows Azure, you can monitor usage
and metrics of your mobile service backend using the “Dashboard” tab within the
Windows Azure Portal.

The dashboard tab provides a built-in monitoring view of the API calls,
Bandwidth, and server CPU cycles of your Windows Azure Mobile Service. You can
also use the “Logs” tab within the portal to review error messages. This makes
it easy to monitor and track how your application is doing.

Scale Up as Your Business Grows

Windows Azure Mobile Services now allows every Windows Azure customer to
create and run up to 10 Mobile Services in a free, shared/multi-tenant hosting
environment (where your mobile backend will be one of multiple apps running on a
shared set of server resources). This provides an easy way to get started on
projects at no cost beyond the database you connect your Windows Azure Mobile
Service to (note: each Windows Azure free trial account also includes a 1GB SQL
Database that you can use with any number of apps or Windows Azure Mobile
Services).

If your client application becomes popular, you can click the “Scale” tab of
your Mobile Service and switch from “Shared” to “Reserved” mode. Doing so
allows you to isolate your apps so that you are the only customer within a
virtual machine. This allows you to elastically scale the amount of resources
your apps use – allowing you to scale-up (or scale-down) your capacity as your
traffic grows:

With Windows Azure you pay for compute capacity on a per-hour basis – which
allows you to scale up and down your resources to match only what you need.
This enables a super flexible model that is ideal for new mobile app scenarios,
as well as startups who are just getting going.

Summary

I’ve only scratched the surface of what you can do with Windows Azure Mobile
Services – there are a lot more features to explore.

With Windows Azure Mobile Services you’ll be able to build mobile app
experiences faster than ever, and enable even better user experiences – by
connecting your client apps to the cloud.

Visit the Windows Azure Mobile Services development center to learn
more, and build your first Windows 8 app connected with Windows Azure today.
And read this getting started tutorial to walkthrough how you can build (in
less than 5 minutes) a simple Windows 8 “Todo List” app that is cloud enabled
using Windows Azure Mobile Services.

Hope this helps,

Scott

Introducing the Microsoft Driver for Node.JS for SQL Server

Jonathan Guerin — Thu, 07 Jun 2012 20:30:00 GMT

Dear Node.JS Community,

We are excited to announce a preview release of the Microsoft Driver for Node.JS for SQL Server. This new driver enables access to SQL Server and Windows Azure SQL Database from any Node.JS applications. Over the last few months our team has been working diligently toward developing this driver and preparing for a preview release so we can gather your feedback early in our development cycle. Today, we are announcing that our Microsoft Driver for Node.JS for SQL Server is ready for public preview.

Open First

The Microsoft Driver for Node.JS for SQL Server is hosted on Github, accentuating our continued involvement with the Open Source community. We will integrate features into the repository as we complete them, and you’ll be able to see all check-ins, issues and future roadmap discussions as we work to further improve this driver. Of course, we are also accepting contributions from the community, in accordance with our contributor guidelines.

Given this is a preview release, we look forward to and encourage all feedback from the community. Please feel free to look at existing issues, file new ones, or contact me on Twitter with feedback.

Simplicity by Design

In designing the API for this new driver, we aimed to keep a simple surface which is intuitive to Node.JS developers. Here’s an example of connecting to SQL Server from Node.JS:

// Query with explicit connection
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";

sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM Person.Person", function (err, results) {
        if (err) {
            console.log("Error running query!");
            return;
        }
        for (var i = 0; i < results.rows.length; i++) {
            console.log("FirstName: " + results.rows[i][0] + " LastName: " + results.rows[i][1]);
        }
    });
});

There are two modes for retrieving query results. The first mode returns all rows at once as a parameter to a callback:

// Simple Query with parameters
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";

var match = "%crombie%";
sql.query(conn_str, "SELECT FirstName, LastName FROM Person.Person WHERE LastName LIKE ?", [match], function (err, results) {
    for (var i = 0; i < results.length; i++) {
        console.log("FirstName: " + results[i].FirstName + " LastName: " + results[i].LastName);
    }
});

Additionally, a statement object returned by a query can subscribe to events in order to receive individual rows and columns:

// Query with streaming
var sql = require('node-sqlserver');
var conn_str = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection={Yes}";

Use in Windows Azure Web Sites

As you’ve probably heard, the refreshed Windows Azure portal was also released as a preview today. You can use the Node.JS driver in Windows Azure Web Sites by following this simple tutorial, and prior to the publish phase, copying the node-sqlserver driver into your node_modules directory. In the end, the sample application in the tutorial will look like so:

./server.js
./node_modules/node-sqlserver/package.json
./node_modules/node-sqlserver/lib/sql.js
./node_modules/node-sqlserver/lib/sql.node

Additionally, here’s a small example which builds on the tutorial application to connect to Windows Azure SQL Database:

Documentation, Feedback and Download

We are documenting this driver as we develop it on our Github wiki. We’ll also be continuously monitoring issues on Github for your feedback and suggestions. We are releasing this as a preview so we can incorporate feedback about our design before we have a fully-fledged release.

To download the driver source, please visit our Github project page and clone or download the source onto your machine. You can then build the driver yourself using our freely-available Visual C++ Express compiler. Please take a look at the README included in the source tree for more information.

If you do not want to compile the driver yourself, you can download it from our Download Center. Once you download the package and have extracted it, please run node-sqlserver-install.cmd to generate the required directories. You can then drop the node-sqlserver directory into your node-modules directory and access SQL Server and Windows Azure SQL Database from your Node.JS application.

Please note that the driver requires the SQL Server Native Access Client – you can get the latest version from the SQL Server 2012 Feature Pack download page.

We’re pleased to show you our work to date and look forward to hearing from you!

Thank you,

Jonathan Guerin
Program Manager
SQL Server & Windows Azure SQL Database
Microsoft Corporation

Microsoft Drivers 3.0.1 for PHP for SQL Server with PHP 5.4 Support Released

Jonathan Guerin — Thu, 22 Mar 2012 23:04:00 GMT

Dear SQL Server Developer Community,

As you know, two weeks ago, we released our 3.0 drivers along with SQL Server 2012. It was around the same time that PHP 5.4 was also released to the web, and we have received many requests from our community members to support the new PHP runtime. It is my pleasure to announce that we have listened to you, and have updated our drivers to 3.0.1. The major feature added for this release is support for PHP 5.4, as well as some minor bug fixes.

As always, you can find documentation on the driver at our MSDN documentation page, provide feedback on our MSDN Forum and at the SQL Server Connect Site, and download the driver here. Also note that you will require the SQL Server 2012 Native Access Client, available as part of our SQL Server 2012 Feature Pack, viewable here. You can find the source code at our Codeplex Page.

Thanks,

Jonathan Guerin
Program Manager
SQL Connectivity
Microsoft Corporation

Microsoft Drivers 3.0 for PHP for SQL Server Released to Web!

Jonathan Guerin — Tue, 06 Mar 2012 18:48:00 GMT

Dear SQL Server Developer Community,

We wanted to extend a massive ‘thank you’ to the community in providing feedback for our release of our Customer Technology Preview back in September, and we’ve been working hard to incorporate the feedback you have provided us. You will find that we’ve fixed many issues you reported, and we are proud to be able to release the final build of our 3.0 drivers.

The major highlights of this release include: support for SQL Server 2012 features, and Buffered Queries. The 2012 features include support for AlwaysOn Connectivity, encompassing support for Multi-Subnet Failover, connectivity to AlwaysOn Availability Groups and Read-Only Routing, and support for SQL Express LocalDB.

SQL Server 2012: LocalDB

The Microsoft Drivers for PHP for SQL Server include support for SQL Express LocalDB, a new distribution of SQL Server. LocalDB is a lightweight install, providing only the database engine. This engine is brought up automatically when an application connects to the instance, and provides easy connection-time parameters for attaching file-based databases. This feature significantly simplifies the development experience, as well as reducing the installation components necessary to develop for SQL Server. Enabling support for LocalDB is as easy as changing the connection string. An example in the SQLSRV driver:

<?php $serverName = "(localdb)\v11.0"; $connectionOptions = array( "Database"=>"AdventureWorks", “AttachDBFileName”=>”AdventureWorks.mdf” ); /* Connect to SQL Express LocalDB. */ $conn = sqlsrv_connect( $serverName, $connectionOptions ); ?>

And an example in the PDO_SQLSRV driver:

<?php $conn = new PDO("sqlsrv:Server=(localdb)\v11.0 ; Database = AdventureWorks ; AttachDBFilename = AdventureWorks.mdf", "", ""); ?>

All functionality expected from a standard SQL Server Express instance will also function with LocalDB, as well as all standard driver functions. Additionally, databases attached at connection time will automatically be attached in the engine if they are not already, or redirected to the existing attached file if the engine already has it attached.

SQL Server 2012: AlwaysOn Connectivity

The Microsoft Drivers for PHP for SQL Server include support for the AlwaysOn Connectivity feature of SQL Server 2012:
SQL Server AlwaysOn is the new high availability and disaster recovery solution for the next release of SQL Server. Using AlwaysOn businesses can achieve increased application availability for their mission critical applications and get higher returns on their high availability investments through better utilization of hardware resources. AlwaysOn also increases productivity and lowers TCO by greatly simplifying high availability deployment and management.

The driver now supports the two new connection options “ApplicationIntent” and “MultiSubnetFailover” to support AlwaysOn connectivity.

Buffered Queries

Finally, the driver now provides support for resultsets to be buffered on the client-side by the driver. This feature is very common amongst PHP database drivers and we are proud to bring support for this in our 3.0 release. This is an opt-in feature which caches the entire resultset from a query into memory, therefore reducing subsequent round-trips to the server usually required to fetch the resultset row-by-row. An example where Buffered Queries is enabled in the SQLSRV driver is presented below:

<?php
/* Get products by querying against the product name.*/
$tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product";
/* Execute the query, buffered queries enabled */
$getProducts = sqlsrv_query( $conn, $tsql, null, array("Scrollable"=>"buffered")););
?>

And also in the PDO_SQLSRV driver:

<?php
/* Get products by querying against the product name.*/
$tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product";
/* Prepare and execute the query, buffered queries enabled */
$stmt = $conn->prepare( $tsql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED));
$stmt->execute();
?>

Other features

We also added supported for the PDO Attribute PDO::ATTR_EMULATE_PREPARES, which can be of assistance when using parameters in a query that would not normally be supported by Transact-SQL.

Finally, we incorporated many fixes reported by our community and hope that you find an improved experience with this new release.

Documentation, Feedback and Download

You can find documentation on the driver at our MSDN documentation page, provide feedback on our MSDN Forum and at the SQL Server Connect Site, and (most importantly!) download the driver here. Also note that you will require the SQL Server 2012 Native Access Client, available as part of our SQL Server 2012 Feature Pack, viewable here. You can find the source code at our Codeplex Page.

I’d like to thank everyone on behalf of the team for supporting us in our endeavors to provide you with a high-quality driver. Happy downloading!

Thanks,

Jonathan Guerin
Program Manager
SQL Connectivity
Microsoft Corporation

SAG Awards Drupal Website Moves to Windows Azure

Jonathan Guerin — Fri, 24 Feb 2012 22:34:38 GMT

This has been re-posted from the Microsoft Interoperability blog.

The success of the recent Screen Actors Guild (SAG) Awards ceremony was buoyed by the move of its Drupal-based website hosted on internal Linux servers to one hosted on Windows Azure.

The SAG Awards site is a highly visible, high-traffic website running on Drupal. Hosting it on Azure provides a scalable, public cloud environment for SAG team. They can tune up or down the compute and storage requirements according to expected website loads, thereby getting a more scalable, manageable and cost-effective solution for running their site.

SAG also gets the benefits of PaaS – no need to manage the operating system patches, virtual machine images, network topology etc. This is particularly useful for SAG as the site has stable traffic for nine months, but which spikes for the three months from when award nominations open to the night of the event itself.

The SAG Awards site was previously hosted on internal Linux boxes. In previous years, performance was negatively impacted by site outages and slow performance during peak-usage days, with SAG having to consistently upgrade their hardware to meet demand for those days. That upgraded hardware was then not optimally used during the rest of the year.

The usage pattern for the SAG Awards site fluctuates, but spikes between November and February when the site is used for SAG award nominations in early November to the actual announcement of nominations in in mid-December. Peak usage is on the night of the awards ceremony where multiple uploads of pictures, news articles, and site visits happen.

What is even more impressive is that both visits and page views almost doubled on the night of the event. In 2011, some 222,816 people visited the site and 434,743 pages were viewed, while this year there were some 325,303 site visits and 789,310 page views, reflecting the stability and performance of the site on Windows Azure.

Microsoft started working with the SAG Awards team in May 2011, when their CIO Erin Griffin joined the Interoperability Executive Council (IEC) - founded by Microsoft in 2006 with a goal of identifying the industry’s greatest areas of need and to work together to create solutions - and attended a council meeting.

In September Mike Story, SAG’s chief architect, attended an IEC work stream meeting and asked for Microsoft’s support in porting the site to Azure. The Business Platform Division’s Customer Experience (CAT) team, the Interoperability group and Windows Azure all started working with SAG in early October and, on December 20, 2011, the site went live on Windows Azure.

“We moved to Windows Azure after looking at the services it offered,” said Erin Griffin, CIO at SAG. “Understanding the best usage scenario for us took time and effort, but with help from Microsoft, we successfully moved our site to Windows Azure and the biggest traffic day for us went off with flying colors.”

This is just one real world outcome from the IEC, which has counseled Microsoft on many interoperability topics and introduced a number of real world scenarios for discussion. The IEC, working together with Microsoft, has developed a number of solutions for these scenarios, with this one for the SAG Awards being the latest.

Curt Peterson, Microsoft’s Principal Group Program Manager, BPD Customer Experience, notes that the success of Sunday’s SAG Awards ceremony underscores how Windows Azure is a scalable, open Cloud platform ready for production use. “We are committed to making it easier for all our customers to use cloud computing on their terms with Windows Azure,” he says.

Joomla! 2.5 Adds SQL Server Support!

Jonathan Guerin — Sat, 28 Jan 2012 13:58:00 GMT

This entry has been re-posted from Brian Swan's blog.

This is a short post to shareexciting news: Jooma! 2.5, which shipped earlier this week, now has support for SQL Server! Of course, this release contains lots more goodies, but the addition of a database abstraction layer that supports multiple databases is a huge step (support for other databases may arrive quickly now that Joomla! ison a 6-month ship cycle). This will remove headaches for anyone deploying Joomla! in multiplatform environments. You can read all about the release on the Joomla! Web site: Joomla! 2.5 Released with Extra Features and Easy Updates. I think this quote from the announcement sums up the impact of this release better than I could:

“Multi-database support is a huge step forward for Joomla, which is already powering more than 1.6 million websites worldwide. It ensures companies and organization, both large and small, will save even more money and time by adopting Joomla…No longer will Joomla developers be tied down to a particular database or have to spend more money and time integrating other software to get Joomla to communicate with their database. This enhances Joomla’s scalability immensely.” - Ryan Ozimek, President of Open Source Matters

On behalf of the folks here at Microsoft, I want to give a huge thanks to the many volunteers in the Joomla! community who spent countless hours testing the changes that went into this release, as well as to the Joomla! core team for ultimately getting the changes in. I’d also like to give a huge thanks to the team at Hooduku, who played a key role in adding the multi-database support and developed the Joomla! interface to SQL Server.

The last bit of exciting news is that you can use the Microsoft Web Platform Installer to install Joomla! 2.5. Download the Web PI here (it's a quick download): http://www.microsoft.com/web/downloads/platform.aspx. Once you’ve done that, launch the Web PI and search for “joomla” in the search box…

Click Add…

…then Install…

…choose your database…

…accept the terms (and note that the Web PI is also installing all the dependencies for Joomla!)…

…and in a few minutes, you’ll have Joomla! 2.5 installed!

Thanks.

-Brian

Available Today: Preview Release of the SQL Server [C/C++] ODBC Driver for Linux

Jonathan Guerin — Mon, 28 Nov 2011 19:48:49 GMT

This has been re-posted from the SQL Server Team Blog.

In our continued commitment to interoperability, we are very excited to announce the availability of a preview release of the SQL Server ODBC Driver for Linux! This release will allow native developers to access Microsoft SQL Server from Linux operating systems. To assist our customers with native applications on multi-platform we have ported our existing, reliable and enterprise-class ODBC for Windows driver (a.k.a. SQL Server Native Client, or SNAC) to the Linux platform. Download the driver today here!

In this release, the SQL Server ODBC Driver for Linux will be a 64-bit driver for Red Hat Enterprise Linux 5. We will support SQL Server 2008 R2 and SQL Server 2012 with this release of the driver. Notable driver features (in addition to what you would expect in an ODBC driver) include support for the Kerberos authentication protocol, SSL and client-side UTF-8 encoding. This release also brings proven and effective tools and the BCP and SQLCMD utilities to the Linux world.

This SQL Server ODBC Driver for Linux provides a Microsoft-supported solution for C and C++ applications that target SQL Server. It also provides a mechanism for applications and runtimes that leverage ODBC to access SQL Server from Linux platforms. For customers who want to move from Sybase to SQL Server, the SQL Server ODBC Driver for Linux allows C and C++ code to continue running in Linux environments. For additional information on the first release of Microsoft ODBC Driver for Linux please refer to our ODBC Driver on Linux Documentation.

This release marks a big milestone in Microsoft’s continued commitment to interoperability and our alignment with ODBC as the preferred way to access SQL Server. Download the driver today; please provide your feedback and ask us questions via our forums; or post a comment via Microsoft Connect. We look forward to hearing from you.

Thanks,

Shekhar Joshi
Senior Program Manager
Microsoft SQL Server ODBC Driver For Linux

Using SQL Azure to Store PHP Session Data

Jonathan Guerin — Wed, 19 Oct 2011 18:32:00 GMT

This entry is a re-post of Brian Swan's entry on The Silver Lining blog.

In my last post, I looked at the session handling functionality that is built into the Windows Azure SDK for PHP, which uses Azure Tables or Azure Blobs for storing session data. As I wrote that post, I wondered how easy it would be to use SQL Azure to store session data, especially since using a database to store session data is a common and familiar practice when building distributed PHP applications. As I found out, using SQL Azure to store session data was relatively easy (as I’ll show in this post), but I did run into a couple of small hurdles that might be worth taking note of.

Note: Because I’ll use the SQL Server Drivers for PHP to connect to SQL Azure, you can consider this post to also cover “Using SQL Server to Store PHP Session Data”. The SQL Server Drivers for PHP connect to SQL Azure or SQL Server by simply changing the connection string.

The short story here is that I simply used the session_set_save_handler function to map session functionality to custom functions. The biggest hurdle I ran into was that I had to heed this warning in the session_set_save_handler documentation: “As of PHP 5.0.5 the write and close handlers are called after object destruction and therefore cannot use objects or throw exceptions. The object destructors can however use sessions. It is possible to call session_write_close() from the destructor to solve this chicken and egg problem.” I got around this by putting my session functions in a class and including a __destruct method that called session_write_close(). A smaller hurdle was that I needed to write a stored procedure that inserted a new row if the row didn’t already exist, but updated it if it did exist.

The complete story follows. I’ll assume that you already have a Windows Azure subscription (if you don’t, you can get a free trial subscription here: http://www.microsoft.com/windowsazure/free-trial/). Keep in mind that this code is “proof of concept” code – it needs some refining to be ready for production.

1. Create the database, table, and stored procedure (the stored procedure described above). To keep my PHP code simple, it assumes that you have created a database called SessionsDB with a table called sessions and a stored procedure called UpdateOrInsertSession. (A TODO item is to add the creation of the table and stored procedure to the PHP code, but the creation of the database will have to be done separately.) To create these objects, execute the code below using the SQL Azure Portal or SQL Server Management Studio (details in this article – Overview of Tools to Use with SQL Azure):

Create table:

CREATE TABLE sessions

(

    id NVARCHAR(32) NOT NULL,

    start_time INT NOT NULL,

    data NVARCHAR(4000) NOT NULL,

    CONSTRAINT [PK_sessions] PRIMARY KEY CLUSTERED 

    (

        [id]

    )

)

Create stored procedure:

CREATE PROCEDURE UpdateOrInsertSession

( 

    @id AS NVARCHAR(32), 

    @start_time AS INT, 

    @data AS NVARCHAR(4000)

)

AS

BEGIN    

    IF EXISTS (SELECT id FROM sessions WHERE id = @id)    

        BEGIN       

            UPDATE  sessions        

            SET  data = @data        

            WHERE id = @id      

        END    

    ELSE    

        BEGIN             

            INSERT INTO sessions (id, start_time, data)       

            VALUES ( @id, @start_time, @data )    

        END

END

One thing to note about the table: the data column will contain all the session data in a serialized form. This allows for more flexibility in the data you store.

2. Add the SqlAzureSessionHandler class to your project. The complete class is attached to this post, but I’ll call out a few things here…

The constructor takes your server ID, username, and password. Formatting the connection options is taken care of, but will need to be changed if you are using SQL Server. (i.e. The username will not require the “@serverId” suffix and your server name will not require the “tcp” prefix and “.database.windows.net” suffix.)

Also note that session_set_save_handler is called in the constructor.

public function __construct($serverId, $username, $password)

{    

    $connOptions = array("UID"=>$username."@".$serverId, "PWD"=>$password, "Database"=>"SessionsDB");

    $this->_conn = sqlsrv_connect("tcp:".$serverId.".database.windows.net", $connOptions);

    if(!$this->_conn)

    {

        die(print_r(sqlsrv_errors()));

    }

    session_set_save_handler(

                             array($this, 'open'),

                             array($this, 'close'),

                             array($this, 'read'),

                             array($this, 'write'),

                             array($this, 'destroy'),

                             array($this, 'gc')

                             );

}

The write method serializes and base64 encodes all the session data before writing it to SQL Azure. Note that the InsertOrUpdateSession stored procedure is used here so that new session data is inserted, but existing session data is updated:

public function write($id, $data)

{

    $serializedData = base64_encode(serialize($data));

    $start_time = time();

    $params = array($id, $start_time, $serializedData);

    $sql = "{call UpdateOrInsertSession(?,?,?)}";

    $stmt = sqlsrv_query($this->_conn, $sql, $params);

    if($stmt === false)

    {

        die(print_r(sqlsrv_errors()));

    }

    return $stmt;

}

Of course, when session data is read, it must be base64 decoded and unserialized:

public function read($id)

{

    // Read data

    $sql = "SELECT data

            FROM sessions

            WHERE id = ?";

    $stmt = sqlsrv_query($this->_conn, $sql, array($id));

    if($stmt === false)

    {

        die(print_r(sqlsrv_errors()));

    }

    if (sqlsrv_has_rows($stmt))

    {

        $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);

        return unserialize(base64_decode($row['data']));

    }

    return '';

}

There are no surprises in the close, destroy, and gc methods. However, note that this __destruct method must be included:

function __destruct()

{

    session_write_close(); // IMPORTANT!

}

3. Instantiate SqlAzureSessionHandler before calling session functions as you normally would. After creating a new SqlAzureSessionHandler object, you can handle sessions as you normally would (but the data will be stored in SQL Azure):

require_once "SqlAzureSessionHandler.php";

$sessionHandler = new SqlAzureSessionHandler("serverId", "username", "password");

session_start();

if(isset($_POST['username']))

{

    $username = $_POST['username'];

    $password = $_POST['password'];

    $_SESSION['username'] = $username;

    $_SESSION['time'] = time();

    $_SESSION['otherdata'] = "some other session data";

    header("Location: welcome.php");

}

That’s it. Hope this is informative if not useful.

Thanks.

-Brian

Share this on Twitter

Announcing Microsoft SQL Server ODBC Driver for Linux

Jonathan Guerin — Thu, 13 Oct 2011 17:05:00 GMT

Greetings Developer community:

We heard yesterday and today at the PASS conference about the exciting new areas that we are investing in bringing the power of SQL Server to our customers. Many of our developers who rely on native connectivity to SQL Server primarily use ODBC for their connectivity needs. We have been supporting ODBC as a part of the SQL Native Access Client (SNAC) libraries. In our continued commitment to interoperability, today we also announced that we will be releasing the Microsoft SQL Server ODBC Driver for Linux. We will be releasing first community technology preview (CTP) around mid-November and will be available along with SQL Server 2012 when it is released. Please look for announcement on our SQL Connectivity home page and SQL Server blog page.

We will be showcasing Microsoft SQL Server ODBC Driver for Linux along with our Java and PHP solutions for SQL Server and Azure at PASS conference session “[AD-211-M] Developing Multi-Platform Applications for Microsoft SQL Server and Azure” on Thursday October 13th at 5:00PM at Washington State Convention Center Room #4C4. Also, if you have any questions or feedback on our multi-platform strategy as well as the entire gamut of support we provide to the application developers, I would encourage you to attend the PASS Panel Discussion with SQL Connectivity Leadership “[AD-101-M] SQL Connectivity Leadership Unplugged” on Friday, October 14, 2011, 2:30 PM - 3:45 PM at Washington State Convention Centre Room# 612 where I will be hosting a panel along with the rest of the leadership team that drives the strategy for our application platform.

Thanks,

Raghu Ram

Principal Group Program Manager

SQL Server

Microsoft Drivers 3.0 for PHP for SQL Server Released!

Jonathan Guerin — Fri, 23 Sep 2011 23:44:00 GMT

Dear SQL Server Developer Community, thank you very much for your feedback in driving our roadmap. Adding another milestone, the PHP Driver team is very excited to announce the availability of the Microsoft Drivers 3.0 for PHP for SQL Server Community Technology Preview 1!

The major highlights of this release include: support for SQL Server Codename “Denali” features, and Buffered Queries. The “Denali” features include support for AlwaysOn Connectivity, encompassing support for Multi-Subnet Failover, connectivity to AlwaysOn Availability Groups and Read-Only Routing, and support for SQL Express LocalDB.

SQL Server Codename “Denali”: LocalDB

<?php
$serverName = "(localdb)\v11.0";
$connectionOptions = array( "Database"=>"AdventureWorks", “AttachDBFileName”=>”AdventureWorks.mdf” );
/* Connect to SQL Express LocalDB. */
$conn = sqlsrv_connect( $serverName, $connectionOptions );
?>

And an example in the PDO_SQLSRV driver:

<?php $conn = new PDO("sqlsrv:Server=(localdb)\v11.0 ; Database = AdventureWorks ; AttachDBFilename = AdventureWorks.mdf", "", ""); ?>

SQL Server Codename “Denali”: AlwaysOn Connectivity

The Microsoft Drivers for PHP for SQL Server include support for the AlwaysOn Connectivity feature of SQL Server Codename “Denali”:
SQL Server AlwaysOn is the new high availability and disaster recovery solution for the next release of SQL Server. Using AlwaysOn businesses can achieve increased application availability for their mission critical applications and get higher returns on their high availability investments through better utilization of hardware resources. AlwaysOn also increases productivity and lowers TCO by greatly simplifying high availability deployment and management.

The driver now supports the two new connection options “ApplicationIntent” and “MultiSubnetFailover” to support AlwaysOn connectivity.

Buffered Queries

Finally, the driver now provides support for resultsets to be buffered on the client-side by the driver. This feature is very common amongst PHP database drivers and we are proud to bring support for this in our 3.0 CTP1 release. This is an opt-in feature which caches the entire resultset from a query into memory, therefore reducing subsequent round-trips to the server usually required to fetch the resultset row-by-row. An example where Buffered Queries is enabled in the SQLSRV driver is presented below:

<?php /* Get products by querying against the product name.*/ $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product"; /* Execute the query, buffered queries enabled */ $getProducts = sqlsrv_query( $conn, $tsql, null, array("Scrollable"=>"buffered"));); ?>

And also in the PDO_SQLSRV driver:

<?php /* Get products by querying against the product name.*/ $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product"; /* Prepare and execute the query, buffered queries enabled */ $stmt = $conn->prepare( $tsql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED)); $stmt->execute(); ?>

Documentation, Feedback and Download

You can find documentation on the driver at our MSDN documentation page, provide feedback on our MSDN Forum and at the SQL Server Connect Site, and (most importantly!) download the driver here. Also note that you will require the SQL Server Codename “Denali” Native Access Client, available here. We encourage you to download and play with this CTP and provide us feedback. Please note that we still continue to support our v2.0 release.

I’d like to thank everyone on behalf of the team for supporting us in our endeavors to provide you with a high-quality driver. Happy downloading!

Thanks,

Jonathan Guerin
Program Manager
Microsoft Drivers for PHP for SQL Server
SQL Connectivity
Microsoft Corporation

Annual SQL Connectivity Customer Survey [Date extended]

Jonathan Guerin — Wed, 07 Sep 2011 22:08:00 GMT

Greetings to the Developer community:

Thank you very much for an overwhelming response on our survey. Feedback is continuing to pour in. We are extending the deadline to complete the survey to September 16^th, 2011 to allow everyone to provide input. Your feedback helps us on the broad SQL Connectivity components that address major development platforms, including ODBC, ADO.NET, JDBC and PHP.

We view your organization as a key stakeholder in the process that we have to identify areas for future investments. The feedback you provide is valuable and each response will be read and will be treated with utmost confidence. The survey can be found in the link below and will be available until 16^th September, 2011 5:00 PM PST.

http://www.zoomerang.com/Survey/WEB22CS45XT9FE/

Thanks

Amina

Checking Your SQL Azure Server Connection

Jonathan Guerin — Mon, 29 Aug 2011 21:55:37 GMT

This entry has been cross-posted from the Windows Azure Team Blog.

As the SQL Azure July 2011 Service Release is being rolled out, we want to advise you of an important consideration for your application logic. Some customers may be programmatically checking to determine if their application is connected to SQL Azure or another edition of SQL Server. Please be advised, you should not base your application logic on the server version number, since it changes across upgrades and service releases. It is okay, however, to use the version number for logging purposes.

If you want to check to see if you’re connected to SQL Azure, use the following SQL statement:

SELECT SERVERPROPERTY('EngineEdition') == 5.

See the SQL Server Books Online for a full description of the SERVERPROPERTY function.

The current SQL Azure service release will upgrade the underlying SQL Azure database engine version from 10 to version 11 as it is rolled out across data centers.

You will see this server version number change show up in server-side APIs such as:

• SERVERPROPERTY('ProductVersion')

• @@VERSION

and also client-side APIs such as:

• ODBC: SQLGetInfo (SQL_DBMS_VER)

• SqlClient: SqlConnection.ServerVersion

There will be a period of time during the service upgrade, measured in days, during which some user sessions will connect to version 10.25.bbbb.bb SQL servers and other user sessions will connect to version 11.0.bbbb.bb SQL servers (bbbb.bb is the build #). One given user connection to one database may see different server version numbers reported at different times. At the end of the upgrade all sessions will be connecting to version 11.0.bbbb.bb SQL Servers.

User queries and applications should continue to function normally and should not be adversely affected by the upgrade.

The July 2011 Service Release is foundational, with significant upgrades to the underlying engine that are designed to increase overall performance and scalability. This upgrade also represents a big first step towards providing a common base and feature set between the cloud SQL Azure service and our upcoming release of SQL Server Code Name “Denali”. Please read the blog post, “Announcing: SQL Azure July 2011 Service Release” for more information.

Greg is a product manager on the SQL Server team, focused on SQL Azure and Microsoft cloud technologies.

Annual SQL Connectivity Customer Survey

Jonathan Guerin — Mon, 15 Aug 2011 23:17:00 GMT

Greetings to the Developer community:

Microsoft SQL Server team has been interacting on a regular basis with the developers and users in the form of surveys. During the last 2 years, we completed surveys that focused on the broad SQL Connectivity components that address major development platforms, including ODBC, ADO.NET, JDBC and PHP. These surveys provide us with an ability to validate some of the requests we have got from developers, users and partners such as you as well as ideas that we have gathered internally as a part of our development process. You have seen our roadmap for SQL Server evolve based on the feedback that we have received.

http://www.zoomerang.com/Survey/WEB22CS45XT9FE/

Thank you,

Raghu Ram
Principal Group Program Manager
SQL Connectivity
SQL Server RDBMS Group
Microsoft Corporation

Consuming SQL Server Result Sets as XML with PHP

Jonathan Guerin — Mon, 01 Aug 2011 17:36:20 GMT

After the obvious interest at last month's JumpIn Camp with the XML features of SQL Server, Brian Swan has written a great post on consuming result sets as XML in PHP. Go check it out!

PHP Driver for SQL Server now in the PHP Manual

Jonathan Guerin — Thu, 14 Jul 2011 00:02:00 GMT

Following our recent inclusion to the PHP Extension Community Library, we are also excited to announce that we have also uploaded the corresponding documentation to the PHP Manual! These are the missing pieces to our PECL extensions, and will provide PHP customers with a convenient place to find information on our extensions.

You can find our Native Driver (SQLSRV) manual here, and our PDO Driver (PDO_SQLSRV) manual here.

Thank you,

Jonathan Guerin
Program Manager
PHP Driver for SQL Server
Microsoft Corporation

PHP Driver for SQL Server Feature Feedback

Jonathan Guerin — Wed, 13 Jul 2011 23:52:00 GMT

We are currently hard at work on the development on our next version of the PHP Driver for SQL Server and would love to hear your thoughts on some features we are evaluating.

To this end, how much impact do you find from the following features, and which are most important to you?

Always On Connectivity Support
SQL Express LocalDB support
Buffered Queries
Large Objects (LOB) streaming support in PDO_SQLSRV

Update
Brian quite rightly pointed out that I did not clarify what Buffered Queries were, so here's a quick blurb from his post:
"If it’s not obvious what that feature is, it is simply functionality that allows you to bring an entire result set into memory all at once (which the SQLSRV driver doesn’t currently support – you currently have to use a scrollable cursor if you want to move back and forth within a result set)."

Your feedback is very important for us in delivering the right features for our customers. Feel free to leave a comment below, or I am available either by email (jguerin _at_ microsoft.com), twitter, or on our forums.

Thank you,

Jonathan Guerin
Program Manager
PHP Driver for SQL Server
Microsoft Corporation

SQL Server Migration Assistant 5.1 Supports SQL Server v-Next

Jonathan Guerin — Tue, 12 Jul 2011 18:49:00 GMT

This entry has been cross-posted from Brian Swan's blog.

On the heels of today’s announcement at Microsoft’s Worldwide Partner Conference of the availability of SQL Server code name “Denali” CTP3, the SQL Server Migration Assistant Team announced the release of the SQL Server Migration Assistant for MySQL 5.1. (You can download SSMA here.) This big news in this release of SSMA is support for SQL Server v-Next (code name “Denali”).

I am, of course, interested in how these releases might be of interest to PHP developers. For PHP developers looking to add support for SQL Server or SQL Azure to their applications or frameworks, SSMA allows you to migrate MySQL schemas and data to SQL Server 2005, 2008, “Denali”, or SQL Azure:

Migrating an existing MySQL database to SQL Server allows for easy validation and testing of your work toward building in SQL Server support. (The walkthrough I wrote a while back will show you how to convert schemas and data.)

However, schema and data migration is only part of the story towards adding support for additional database vendors. Converting queries to another database vendor’s syntax is another key step. Fortunately, SSMA can help with that as well. In this post, Converting MySQL Queries to MS SQL Server Queries, I walk through how to use SSMA to convert a MySQL LIMIT/OFFSET query to SQL Server syntax. If you read that post, you’ll notice that the converted query uses the ROW_NUMBER/OVER syntax (not exactly a simple, straightforward conversion). One of the nice things about SQL Server “Denali” is the new OFFSET/FETCH syntax. And, SSMA 5.1 leverages that new syntax in converting LIMIT/OFFSET queries when you are converting to SQL Server “Denali”. Here’s how to convert a query…

After creating a new project, connecting to MySQL, and selecting a database (details in this post), right click on Statements in the MySQL Metadata Explorer and select Add Statement:

In the SQL tab, type in the query you want to convert:

Back in the MySQL Metadata Explorer, right-click Statements and select Create Report:

When prompted to save changes to the MySQL Editor, click Yes. The resulting converted query is MUCH nicer than before:

In fact, the new OFFSET/FETCH syntax makes it so easy to convert queries that you might wonder why you would need SSMA for this. Well, the OFFSET/FETCH syntax only works for SELECT queries (unlike MySQL), and there are some corner cases in which the MySQL LIMIT/OFFSET syntax doesn’t convert to the SQL Server OFFSET/FETCH syntax. Fortunately, SSMA handles those corner cases nicely (although, in some cases it reverts to conversion using the ROW_NUMBER/OVER syntax). So, in some cases, SSMA might still come in handy when converting queries.

I’m excited to see the releases of SQL Server “Denali” CTP3 and SSMA for MySQL 5.1, And, as always, I’m eager to hear your questions and/or comments.

Thanks.

-Brian

SQL Server JumpIn! Camp Wrap Up

Jonathan Guerin — Mon, 27 Jun 2011 19:27:57 GMT

This entry has been cross-posted from Brian Swan's blog.

As I arrived yesterday for day 4 (the last day) of the SQL Server JumpIn! Camp, one participant said to me,“I’m starting to feel worn down.” I think that was the general sentiment of everyone that was there…and with good reason. During each day of the camp, PHP developers worked side-by-side with Microsoft developers to add SQL Server and SQL Azure support to their projects, but nearly everyone put in many extra hours late at night (and even early morning!) to add support for other Microsoft technologies (such as IIS, Web Platform Installer, and Windows Azure). The amount of work done by the participants was incredible. You can get a sense of just how much progress was made by this picture of our “progress” board taken on the last day of the camp:

I can’t say thanks enough to all the participants for being 100% invested in the camp and for going the extra mile to investigate how their projects might be able to integrate Microsoft technologies beyond SQL Server and SQL Azure. I know that the entire SQL Server team echoes my thanks.

And now, the hard work for Microsoft developers begins. A primary goal of the camp was for Microsoft developers to learn from PHP developers so that they can build Microsoft products that work better and better with PHP and PHP applications/frameworks. Throughout the camp, we tracked requests by asking participants to post their “wish list” items on a white board. This picture will give you a sense for some of the feedback we received, though we actually got much more feedback in one-on-one conversations:

So now it’s time to take this feedback, prioritize it, plan, and act on it. We did this after the last SQL Server JumpIn! Camp, and the progress we have made since then was well received at this camp. With more hard work, we will make similar progress by the next camp.

An observation: The camp wasn’t all about progress and feedback. Some of the best conversations centered on why something was the way it was. PHP developers learned why a Microsoft feature or API was designed the way it was, and Microsoft developers learned why those features/APIs might pose hurdles for PHP developers. In some cases, these conversations led to something actionable, but in other cases it just led to understanding. This, IMHO, was one of the most valuable aspects of the camp. That mutual understanding will eventually lead to better interoperability.

Finally, don’t get the impression that the camp was all work. It wasn’t. We had a BBQ competition, dinner at Seattle’s Space Needle, and wine tasting/dinner at the Columbia winery in Woodinville. In addition to all that, everyone found time (at the expense of enough sleep!) to catch up with old friends and make new ones. I, personally, had a great time getting to know new people or people I had only met briefly or know only through Twitter and/or blogs.

I think it’s safe to say that you will see more posts (but of a technical nature) that are the fruit of this camp (I learned a ton and want to share what I learned). In the mean time, another HUGE thanks to all the camp participants! I’m looking forward to the next camp.

Edit: If you are interested in more of the who, what, and why of the SQL Server JumpIn! Camp, read/listen to the blog posts/podcasts Cal Evans did during the camp...

-Brian

Looking Forward to Next Week’s SQL Server JumpIn! Camp

Jonathan Guerin — Wed, 15 Jun 2011 16:48:00 GMT

This entry has been cross-posted from Brian Swan's blog.

Next week, Microsoft will host its second SQL Server JumpIn! Camp, and I’m very excited to be a participant (take a look at the list (below) of PHP applications and frameworks that will be represented and you will understand why!). I was fortunate enough to attend the first JumpIn! Camp (last November), and what I liked most about that camp was that it was an excellent two-way learning experience. The main goal of the first camp was (and is for next week’s camp) to add SQL Server support to several PHP applications and frameworks. (To understand why this was the goal, read these two interviews: Interview with Julian Egelstaff, Technical Architect at Freeform Solutions and Interview: Ashay Chaudhary on the SQL Server JumpIn! Camp.) In the first camp (as will be in the second camp) PHP and Microsoft developers worked side-by-side to learn from each other in working toward this goal. The end result was that, in addition to the stated goal being achieved, both PHP and Microsoft developers learned a great deal about each other and their respective technologies. (To see some of what I learned at the camp, check out these articles: http://blogs.msdn.com/b/brian_swan/archive/tags/jumpin+camp/.) I have every reason to expect next week’s camp to provide a similar experience for all involved.

Here’s the list of PHP Applications and Frameworks that will be represented at next week’s SQL Server JumpIn! Camp:

Look for updates about the camp next week on Twitter (we’ll use the #jumpincamp hash tag).

-Brian

Release to PECL

dpblogs — Fri, 03 Jun 2011 21:23:00 GMT

On behalf of the Microsoft PHP Driver for SQL Server team, it is my great pleasure to announce that we have published both SQLSRV and PDO_SQLSRV to the PHP Extension Community Library!

You can find our packages at:
http://pecl.php.net/package/sqlsrv
http://pecl.php.net/package/pdo_sqlsrv

This has been a long-standing request from the PHP community, and we are pleased to finally be able to do this. At Microsoft, we have steadily increased our participation in the PHP community, and this is another great milestone towards that goal. On behalf of the team, I would like to say that we are very proud to be amongst some of the best PHP extensions published on PECL!

Thank you,

Jonathan Guerin
Program Manager
PHP Driver for SQL Server
Microsoft Corporation

Introduction and Removal of PHP Driver for SQL Server 1.1 download links.

dpblogs — Fri, 29 Apr 2011 23:26:24 GMT

I should precede the following post with an introduction. I recently started at Microsoft as the Program Manager for the PHP Driver for SQL Server. Since then, I’ve been ramping up on the many aspects of the job, not the least of which is learning what makes the PHP community great!

With our release of the PHP Driver for SQL Server 2.0 driver last year, we are planning to remove download links to the PHP Driver for SQL Server 1.1 May 31^st 2011. The PHP Driver for SQL Server 2.0 release includes the Native APIs present in the PHP Driver for SQL Server 1.1 release, as well as support for the PDO API.

Moving forward, the existing PHP Driver for SQL Server 1.1 download page will redirect to the PHP Driver for SQL Server 2.0 page. Please contact us if there are any compatibility issues you come across and we will work hard to resolve these.

I’d like to extend a hand to everyone and ask you to contact us at our forums if you have any problems with the existing version. Additionally, as we work on our next version, we’d love to hear from you if you find that there is anything you think we should implement.

Thank you,

Jonathan Guerin
Program Manager
PHP Driver for SQL Server
Microsoft Corporation

Drupal 7 and SQL Server – our PDO journey

Ashay Chaudhary [MSFT] — Wed, 26 Jan 2011 05:51:15 GMT

Drupal 7 completed a 3-year journey earlier this year by exiting beta stages and declaring itself ready for prime time. Our team (the SQL Server Drivers for PHP team) was fortunate to be part of that journey by working closely with the Drupal community to add SQL Server support to this release. We learned a great deal along the way and helped deliver in Drupal 7 release that benefits the Drupal community, Microsoft, and most importantly the Drupal users. In this post, I’d like to provide some insight to the part of the Drupal 7 journey that we shared.

The journey for us began almost a year ago (in February 2010) when an unusual request happened across my desk – Damien Tournoud, from the Drupal Core team, had sent an email to Microsoft expressing interest in adding support for SQL Server to Drupal 7. To be perfectly honest, we knew there had been a fair bit of interest for a while within the Drupal community to support SQL Server as evident in this thread and this thread. However, we also know that Drupal 7 had decided to move to PDO…and we didn’t have a PDO driver – yet. Since I had only recently joined the SQL Server Driver for PHP team and was ramping up fast on the team’s PDO effort, getting our work included in Drupal 7 was interesting proposition, albeit a daunting one.

The SQL Server team strongly believes in partnering with key customers during the development process via our Technology Adoption Program, but doing this in the PHP space is relatively new for us. Fortunately, because PDO is fairly well defined, we were able to move quickly on the technical side and didn't feel there was a need for such partnerships for this release. Of course, we did plan to have a CTP (i.e. Beta) available for broader testing and feedback on our implementation.

After doing the homework to make sure we could deliver on time, we welcomed the opportunity to work with Drupal 7 for early technology adoption for our PDO driver. As I mentioned before, there weren’t many precedents within Microsoft for open source projects and we had a fair amount of work in getting things worked out. Doing this on an extremely tight timeline wasn’t a trivial task, but we managed it. We must thank Pierre Couzy from Microsoft France for providing terrific support in getting things off the ground.

At the same time we were figuring out how to work with the Commerce Guys, Damien, Pierre and I were also working out the technical details and timelines - including an incredibly optimistic goal to jointly announce our releases (our PDO driver, their Drupal 7 SQL Server Module) at DrupalCon San Francisco 2010. On our end, we decided to modify our release plan into to include two CTPs (i.e. betas) instead of the originally planned single CTP. We were almost done with our code refactoring at that time and we were wiring up the PDO API layer to the functional layer (see this post on the new architecture), but we felt that two CTP’s would provide the opportunity for more valuable feedback. We converged on the completion dates and began sending Damien private builds to test with.

Working with Damien over the next two weeks was both intense and fun. That balance of intensity and fun was crucial to our success. We ran into several issues, but none that we couldn’t resolve or work around and continue our momentum. We didn’t let up and soon we were ready to release our new PDO driver via the first CTP in April along with the Drupal 7 SQL Server Module. The actual announcement at DrupalCon San Francisco 2010 was complicated by the ash cloud emanating from Iceland, but by most accounts the joint announcement was a success.

One of the best things to come out of the Drupal effort was some great feedback on our driver. Damien’s knowledge and expertise in database support for Drupal is impressive (Damien also drives/owns the PostgreSQL and SQLite database support in Drupal). Part of what made this interoperability effort a success was that we had the “rights” in place – attitude, environment, incentive, and people (read this interview on the SQL Server JumpIn! Camp for more of my thoughts on the “rights”).

The announcement at DrupalCon did not mark the completion of our work, however. We didn’t have time to incorporate all the feedback we had received, so we were back at the drawing board to incorporate some of the feedback that would require some parts of the PDO driver to be redesigned. We made those design changes and included the new PDO driver in our next CTP (good thing we decided on TWO CTPs!). We would realize later that we had some of the best feedback on our PDO driver in those early weeks! Our team received feedback from several sources that we made the right choice in the design changes and simplified their adoption of our PDO driver.

Drupal 7 in the meanwhile was making great progress, and our relationship with the Drupal community was now established and growing. At the JumpIn! Camp at Zurich we met another Drupal developer, Marcus Deglos, who later wrote the ZoomIt module when he learned about the Deep Zoom technology (he authored the module in a few hours!). The ZoomIt module is just one more example of interoperability between Microsoft and Drupal. This post on Drupal interoperability with Microsoft technologies lists even more modules that demonstrate great interoperability between Drupal and several Microsoft technologies.

Our relationship with Drupal has continued. We have worked with the Drupal Core group to figure out how Microsoft could assist in setting up an automated testing infrastructure for Drupal Core on Windows. Now, tests are automatically scheduled on Drupal 7 Core on Windows with SQL Server (see their test status dashboard).

Along the way, more people from Microsoft got involved in this journey that built on the initial momentum and special mention is deserved by Pierre Couzy and Grace Francisco for their fantastic efforts. We also got the opportunity to meet and work with some really great folks from the Drupal community.

With SQL Server support introduced in Drupal 7 and with PHP support or Windows significantly improved in recent years, Drupal 7 deployments now can benefit from the robust scalability and security offered by SQL Server, as well as its cool features like online backup, high availability, business intelligence, etc. The Commerce Guys have provided a Drupal 7 Developer Preview distribution available via the Web Platform Installer as well as WebMatrix. Having reached this foundational milestone, we still have some ground to cover – we don’t have a cloud solution yet. We have received several inquiries about running Drupal 7 on Windows Azure, and we are exploring this option.

Until then, we encourage you to try Drupal 7 with SQL Server and send us your feedback.

Ashay Chaudhary
Program Manager, SQL Server PHP Driver

BuzzNet Tags: drupal,php,sql server,jumpincamp,mssql,sqlsrv,pdo,pdo_sqlsrv

Update release of the Microsoft Drivers for PHP for SQL Server

Ashay Chaudhary [MSFT] — Wed, 01 Dec 2010 07:39:14 GMT

The recent version 2.0 of the driver has been very well received, with a fair amount of activity on our forum. Our team has been actively engaged with providing support to our developer community to adopt this version – especially the PDO driver.

Based on the issues reported so far, we found that a few critical bugs slipped thru our testing net, root caused them and then spent some time understanding their impact. After the due diligence, we have narrowed the list of bugs down to those that have broad impact and fixed them in this update release. They are:

Long column names allowed by SQL Server (up to 128 characters) were incorrectly truncated to 29 chars. Any application that attempted to use column names allowed by SQL Server stopped working. The update release correctly handles column names up to 128 characters.
PDO::quote() added a null terminator to the end of the string. The update release does not add a null terminator to the end of the quoted string.
An error could occur when binding null data to server columns of type varbinary, binary, or varbinary(max). The update release will not throw an error if one specifies binary encoding (PDO::SQLSRV_ENCODING_BINARY) using the $driver_options parameter of PDOStatement::bindParam().
An access violation could occur in the error handler routine for some error cases, causing the PHP process to abruptly stop. These error handler routines in the update release will no longer cause an access violation.

These are also listed in the readme file included in our download package. Do note that since the number of fixes are small, we have updated only the Product/File version of the driver binaries from 2.0.1426.0 (version 2.0 released August 5) to 2.0.1802.200 (version 2.0.1 released November 30). In addition to the package on the MSDN Download Center, we have also updated the package used by the Web Platform Installer.

In addition to the additional testing coverage from some of our close partners, we also gave this update release additional test coverage at the recent SQL Server JumpIn! Camp I blogged about yesterday.

We are confident that this update release is more stable and recommend all our customers to upgrade to this release very soon.

We thank you all for your continued support,

Ashay Chaudhary
Program Manager – PHP SQL Server

BuzzNet Tags: SQL Server,sqlsrv,pdo_sqlsrv,pdo,php,release