Creating a RESTful Web Service for Microsoft SQL Server

2. Creating a RESTful Web Service for Microsoft SQL Server Patrick Mundy, Managing Partner Albert Factora, Managing Partner

3. About The Presenters Patrick Mundy Albert Factora RocketWeave co-founder Angry Lumpia inventor • RocketWeave co-founder • Kick-ass drummer

4. About RocketWeave

5. Our mission: ensure that your business systems are primed for a successful take-off!

6. Problem Statement Web Service ?? MSSQL Database Server ONE AWS Instance PHP / IIS

7. Common Alternative Platforms Spin up a virtual LAMP stack Spin up a virtual WAMP stack Others…? This presentation presumes a Microsoft shop where MS SQL and IIS are the supported platforms…

8. Agenda Background RESTful architecture overview, JSON overview, Requirements Preparation Installation Process, Configuration Issues Demo Review SQL Data, PHP File architecture Fire off some GET, POST calls Observe behavior Conclusion It can be done App Design thoughts…

9. Background: RESTful principles for web services “Representational State Transfer” http://www.ics.uci.edu/~fielding/pubs/dissertation/rest_arch_style.htm • Architectural Properties • Performance • Scalability • Simplicity of interfaces • Modifiability of components • Visibility of communication between components • Portability of components • Reliability Made from: Components, Connectors, and Data

10. Background: RESTful principles for web services • ENDPOINTS • Collections / Methods • Elements / Methods Hypertext As The Engine Of Application State HATEOAS

11. Background: RESTful principles for web services • Client context not stored on Server • Client initiates state-change via request to Server • PUT and DELETE are idempotent • GET is nullipotent (i.e. safe method) Hypertext As The Engine Of Application State

12. RESTful Design of the Demo • /Categories • GET – returns list of Members of Category Collection • POST – (with data) creates a new Member of the Category Collection • /Categories/:catID • GET – returns data for just the selected Member • /Categories/Level/:lvl • GET – returns Members of “lvl” Level Collection

13. Transmittal Attributes Method GET PUT POST DELETE http://[URL]/Endpoint Optional: data to work with (JSON-encoded) $.post('http://192.168.1.100/api/?q=players', { username:'test1abc', password:'pwd123', email:'test1abc@example.com' });

14. Background: JSON Review “Javascript Object Notation”: Text-Based Data Interchange Format • { • Attribute1 / Value • Attribute2 / Value • } Address Attribute’s Value is a nested JSON object phoneNumbers Attribute’s Value is a LIST of two more small JSON objects

15. Background: System Architecture MSSQL Database Server • Windows 2008 R2 DataCenter • 7.5 Gig memory • 64 bit OS • IIS 7 • PHP VC9 binary, thread safe • v. 5.4.32ts PHP executable PHP index file PHP include files PHP extension files

16. Background: PHP File Architecture php.ini php extension files (.dlls) (including mssql support files) index.php db.php sql.php routes.php

17. Preparation: Installation Process Overview Assumptions: SQL Server and IIS are installed Install PHP Install SQL Server PHP extension Configure php.ini files for IIS Configure IIS for PHP Install SQL Server 2012 Client Build, test DB Connection logic Build t-SQL creation logic Build logic to parse “payload” of GET/PUT/POST

18. Preparation: Install PHP • Determine the version of PHP to use, based on IIS version • http://php.net/manual/en/install.windows.manual.php • Download binaries • Unzip • You may need to install C++ runtime

19. Preparation: Install MSSQL Drivers for PHP • PHP page about this: http://php.net/manual/en/sqlsrv.installation.php • Microsoft page about this: • http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx • Download drivers (most likely SQLSRV 3.0) • Place them in the IIS Extensions folder • Decide which one you will have IIS load: http://social.technet.microsoft.com/wiki/contents/articles/1258.accessing-sql-server-databases-from-php.aspx

20. Preparation: Configure php.ini • Instructions are on second half of PHP install page: • http://php.net/manual/en/install.windows.manual.php • Stipulate extension file location • Stipulate which extensions to use (especially correct MSSQL extension) • Set up logging and log output • CGI and FastCGI settings

21. Preparation: Configure IIS for PHP • PHP page about this: http://php.net/manual/en/install.windows.iis7.php • Enable FastCGI support in IIS • Configure IIS to process PHP requests • php.ini settings taken care of in prior slide • Set up “handler mapping” for PHP inside IIS Manager • Set up impersonation and file access • Set up IIS to use “index.php” as default document

22. Preparation: Install MSSQL 2012 Client • http://msdn.microsoft.com/en-us/library/cc296170.aspx • And from there to: • http://www.microsoft.com/en-us/download/confirmation.aspx?id=29065 • It’s about halfway down the page AFTER you expand the “installation instructions.” Most of us want x64 version

23. Demo: Review SQL Data, Procedures Select * from Categories Exec usp_ShowLevelMembers 2 Exec usp_NewCategory

24. Demo: Review PHP files • \PHP\php.ini • \inetpub\wwwroot\index.php • \inetpub\wwwroot\restful_routes.php • \inetpub\wwwroot\restful_sql.php • \inetpub\wwwroot\restful_db.php

25. Demo: Execute Some GETs • /Categories -- shows list of Members of Category Collection • /Categories/:catID -- shows data for just the selected Member • /Categories/Level/:lvl -- shows Members of “lvl” Level Collection

26. Demo: Execute a POST • /Categories {parentID=1, catName = ‘Coffee Shops’}

27. Recap • Background • RESTful architecture overview, JSON overview, Requirements • Preparation • Installation Process, Configuration Issues • Demo • Review SQL Data, PHP File architecture • Fire off some GET, POST calls • Observe behavior

28. Conclusions

29. (415) 652-3919 info@rocketweave.com pat@rocketweave.com albert@rocketweave.com