Migrating A (Large ) Science Database To The Cloud

queries range from spatial (radial “cone” search) queries tocomplex joins between multiple tables. For each query executed,three performance counters are measured – the elapsed time, theCPU seconds and the physical IO.Although the production CAS site at FermiLab contains 25database servers, each server has one copy of a given SDSSdatabase, and load-balancing and performance is achieved bysegregating different workloads among different servers.Assuch, we only need to compare the performance of a singleinstance/server of the database inside and outside the cloud, atleast to a first approximation.The test query suite generally assumes that the server it is runningon is isolated and offline, and also that certain performanceenhancements are in installed, foremost among them being theHierarchical Triangular Mesh (HTM) spatial indexing library [7]that provides fast (O(log N)) spatial searching. The library isimplemented in C# and used the SQL-CLR (Common LanguageRuntime) binding along with some SQL glue functions.Figure 2 shows the comparison between running this test suite onour GrayWulf [8][9] server and on the instance of the database onEC2. Only the query elapsed time (in seconds) is shown in theplot, and the differences are large enough that a logarithmic scalewas required to plot the times. The EC2 elapsed times are onaverage an order of magnitude larger than the ones we obtainedon the GrayWulf (single) server instance. The difference could bedue a number of factors, such as the database settings on the EC2server (memory, recovery model, tempdb size etc.) as well as thedisk speeds.10000GW Elapseda) People who currently use the SDSS CASb) General AWS users who were interested in the dataFor a), we needed to be able to replicate the same services thatSDSS currently has, but using a SQL Server instance on EC2 andconnecting to it with the elastic IP resulting from the processdescribed in Figure 1. This should in theory work fine, althoughwe were unable to make the connection work during our tests.Although we were able to log in to the EC2 server fine using aWindows remote desktop client, we could not connect to theelastic IP using a SQL OLEDB (a Microsoft protocol that allowsapplications to connect to SQL Server using a connection string)connection, and hence could not connect a SkyServer Webinterface [6] to it.For b), users should have everything they need on the AWS publicdataset page for SDSS, but here was the rub: it was quite acomplex set of maneuvers that a potential user (by user here wemean someone who wants to provide access to the SDSSdatabase, not an end-user; so for example JHU would be a user)would have to execute is a quite daunting (see Figure 3). Themost difficult part by far is “porting the SDSS data to anotherdatabase (platform)”. SQL Server EC2 instances should not bedifficult to create and provide access to. (As an interesting aside,AWS also made the SDSS public dataset available as a LINUXsnapshot, which did not make sense to us since SQL Servercannot run on LINUX).EC2 ElapsedFigure 3. A description of the procedure required to use theSDSS public dataset as provided on the AWS blog.10002.3.1 Cost of Data Q17Q14Q15BQ12Q09Q10AQ07Q03Q05Q010.1Figure 2. Comparison of query elapsed time for the 100-GBSDSS subset on our GrayWulf server (GW) and EC2. We ran35 test queries (alternate query numbers are shown) from ourtest query suite on each database. The elapsed times are inseconds.The purpose of this comparison is not to draw a definitiveconclusion about the relative performance of the two types ofinstances, but rather to indicate that the performance in the cloudcan be disappointing unless it can be tuned properly. We onlyused the default settings for the most part on EC2, and it mighthave been possible (assuming the privileges were available) totweak the performance settings to our benefit.Another important aspect of using EC2 (or any other cloud)instances of datasets like SDSS is the cost-effectiveness of thedata access. We do not have any useful information to contributeon this as yet, partly because we were unable to get the Webinterface connection to work. We incurred charges of nearly 500for our experiments, without actually providing any remote dataaccess. Basically, all we did was create the instance and run ourperformance test queries on it. The duration of the tests was a fewweeks all told, and the database was idle for part of the time whilewe were busy with other commitments. Most of the charges werefor “EC2 running large Windows instance with AuthenticationServices”. The EBS storage and other miscellaneous chargeswere negligible by comparison, even though we had a 200 GBEBS volume in use for all that time. This would indicate thatlicensing costs for third party software (in this case WindowsServer and SQL Server) is the dominant factor. If that is indeedthe case, this could potentially make it infeasible to put sciencedatasets like SDSS in the cloud.3. SDSS DATA ON MICROSOFT SQLAZURE2.3 Data AccessThis is really a work in progress. At this time we are not able toreport on performance specifics, but hopefully soon. In themeantime, it is instructive to look at some of the difficulties wehave experienced in migrating the data to the Azure Cloud.The public SDSS dataset on AWS was meant to serve two kindsof users on:3.1 Migrating the Data

Although the creation of a SQL Azure project yields a SQLServer instance and an IP address for it, there currently appears noway to directly move a database “as is” or en masse into thecloud, even if the database is first upgraded to the proper SQLServer version (in this case SQL Server 2008). The SQL Azureinstructions and labs offer two options for moving data into thecloud: using the built-in database scripting facility in SQL Server2008 to script the schema and data export, and using the bulkcopy utility (BCP) to copy data from the on-premise database toAzure. Given the nature of the SDSS schema and data, forexample the heavy reliance on indexes, stored procedures anduser-defined functions as well as the CLR (common languageruntime) assemblies used for the spatial indexing library, both ofthese options are fraught with problems. Using the scriptingoption, we ran out of memory while generating the script! In fact,even if the script had been generated successfully, according tothe instructions it needs to be then edited manually to removefeatures not supported by SQL Azure.3.1.1 SQL Azure Migration WizardThere is, in fact, a third option that the instructions do notexplicitly mention (or if they did, we missed it!). At the moment,we are using the SQL Azure Migration Wizard (SAMW http://sqlazuremw.codeplex) to move the data into Azure. SAMWactually does an admirable job of automating the migration task.However, as we shall see, this does not eliminate our problems.control how certain commands or procedures are executed,e.g., to set the level of parallelism. These are mostlyperformance related, but for admin tasks rather than userqueries, so they can be ignored if necessary. Built-in T-SQL functions – these are also mostly in adminfunctions and procedures, so not a big concern for now. SQL-CLR function bindings – this is a big one, because thismeans we cannot use our HTM library to speed up the spatialsearches. Deprecated features – since our SQL code was mostlydeveloped on an earlier version (SQL Server 2000), itcontains some features deprecated in SQL Server 2008.These are not supported in Azure. We will have to removethem, which is not a major problem since there are very fewsuch cases, and it is good to remove them anyway in the longrun.The bottom line is that migrating the data to the SQL Azure cloudcurrently involves stripping out several features that will at thevery least impact performance of our database, and couldpotentially make some aspects of it unusable.As in the case of the first migration option (using SQL Serverbuilt-in scripts), SAMW removes any schema elements that arenot supported by Azure. Many functions and stored procedures inthe SDSS schema do not get ported to the Azure copy of thedatabase. This makes it very difficult to compare the deploymentto say the AWS version. One has to go through the voluminousSAMW trace (Figure 4) to find all the errors it encountered.Some of the unsupported features that prevent these functions andstored procedures from being migrated are: References to other databases – this is a minor inconveniencewhich we can work around by simply deleting references toother databases in most cases. In some cases, it is not soeasy to work around it, for example where it prevents the useof the command shell from within SQL Server (the shellmust be invoked via the master database). This means thatwe cannot run command (including SQL) script files fromSQL Server. However, for now we are ignoring these issuesand soldiering ahead. Global temp objects – this prevents the test query storedprocedure (spTestQueries) from being migrated in itsoriginal form. The procedure uses global temp variables torecord the performance metrics. A workaround for this is nottrivial because this is one of the main functions of the testscript. T-SQL directives – these are special directives in the SQLServer SQL dialect (Transact-SQL or T-SQL for short) toPermission to make digital or hard copies of all or part of this work forpersonal or classroom use is granted without fee provided that copies arenot made or distributed for profit or commercial advantage and thatcopies bear this notice and the full citation on the first page. To copyotherwise, or republish, to post on servers or to redistribute to lists,requires prior specific permission and/or a fee.Conference’10, Month 1–2, 2010, City, State, Country.Copyright 2010 ACM 1-58113-000-0/00/0010 10.00.Figure 4. Screen shot of SQL Azure Migration Wizard sessionshowing migration trace for SDSS DR6 10 GB subset. Here“Target Server” is the Azure database server. The wizard isrunning on our local SDSS server at JHU.3.2 Performance TestingSince this is a 10 GB subset (the actual size is actually closer to 6GB), the performance test results will be much more difficult tocompare with the 100 GB and full size databases. However, weaim to run the test query suite on the same database in and out ofthe cloud. The major problem here though is the anticipatedmodifications that will be needed during the migration processdue to the features not currently supported by SQL Azure (seeabove). If changes are made to settings which affect the

performance in a significant way, then it will not be possible toobtain a meaningful performance benchmark. This is indeedemblematic of the difficulties in deploying and benchmarkinglarge databases in the cloud at the moment.3.3 Data AccessThe IP-address that we created for the SQL Azure server allowsus to connect to the server, and we have been able to connect tothe Azure instance of the DR6 subset in two ways:1.2.We can hook up a SkyServer [6] client (Web interface) fromoutside the cloud using a SQL OLEDB connection.Although the full functionality of SkyServer queries is notavailable in the cloud version (due to the subset of supportedfeatures as mentioned above), we have been able to run agood fraction of the SDSS sample queries.We can also connect to the Azure instance as a databaseengine from a remote SQL Server Management Studio clientusing the admin user login that Azure provides. This allowsus to configure the database just as we would a localdatabase. We are using this mode of access to work aroundthe Azure limitations, by creating kosher versions offunctions and stored procedures as necessary.We will not have the data access costs until we address all themigration issues listed in §3.1. For these tests we purchased a 10GB developers’ package which costs 100/month and includes acertain amount of free data transfers in addition to the 10 GBstorage and licensing costs.4. CONCLUSIONSWe have so far migrated a 100 GB subset of a large astronomicaldatabase – the Sloan Digital Sky Survey science archive – to theAmazon EC2 cloud. EC2 has a size limit of 1 TB per instance, soit was not possible for us to migrate the whole SDSS database(several TB) to it and perform a full test. After much help fromthe Amazon experts, we were able to install an instance of theSDSS data in EC2 and run our test suite of 35 test queries on it.With only the default settings and a single EC2 instance, we foundthe query performance to be an order of magnitude slower thanour on-premise GrayWulf server. This was indicative of the needto either tine the EC2 performance settings or create more thanone instance to get better performance. Creating an EC2 instancewas a multi-step process that needed to be followed for eachinstance. After successfully creating an instance and testing itsperformance, we were unable to access the instance with thepublic IP-address (elastic IP) generated using the AWSinstructions. As such, the instance was not accessible from theoutside world.We are in the process of migrating a much smaller (10 GB) subsetof the same dataset to the Microsoft SQL Azure cloud (10 GB isthe current size limit for Windows/SQL Azure). The challengewith SQL Azure – other than the 10 GB size limit which is reallytoo small to do any realistic tests – is that direct migration of thedata is not possible at the moment, since SQL Azure supports asubset of database features and hence database migration must bescripted or done using special purpose utilities. Even with thesetools, the version of the database in the cloud is significantlyaltered and cannot support the full functionality of the originaldatabase. It certainly cannot match the performance of theoriginal version. In fact it is not even possible to measure theperformance of the migrated database in the same way as theoriginal so as to make a meaningful comparison.At this time, it is not possible to migrate and access a scientificSQL Server database in the Amazon and SQL Azure clouds, atleast based on our admittedly incomplete experiments. Even asthe limits on the database size expand in the near future, there areproblems with migrating the data itself, and then providing thetype of performance and access possible desired. Beyond that, thelicensing costs for software used in the cloud could become asignificant issue. We hope to have a more positive report soon aswe continue to explore migrating science data to the cloud.5. ACKNOWLEDGMENTSOur thanks to the Amazon Web Services folks, in particularSantiago Alonso Lord, Deepak Singh and Jeffrey Barr (whomaintains the AWS blog), for hosting a public dataset and for alltheir patient help in setting up and transferring SDSS data to EC2.Also thanks to Roger Barga (Microsoft Research) for his helpwith migrating data to SQL Azure, and pointing us to the SQLAzure Migration Wizard.6. REFERENCES[1] Thakar, A.R. 2008: “The Sloan Digital Sky Survey: Drinkingfrom the Fire Hose”, Computing in Science and Engineering,10, 1 (Jan/Feb 2008), 9.[2] M. Armbrust, A. Fox, R. Griffith, A. Joseph, R. Katz, A.Konwinski, G. Lee, D. Patterson, A. Rabkin, I. Stoica, M.Zaharia. Above the Clouds: A Berkeley View of Cloudcomputing. Technical Report No. UCB/EECS-2009-28,University of California at Berkley, USA, Feb. 10, 2009.[3] Thakar, A.R., Szalay, A.S., Fekete, G., and Gray, J. 2008:“The Catalog Archive Server Database ManagementSystem”, Computing in Science and Engineering, 10, 1(Jan/Feb 2008), 30.[4] Szalay, A.S., Thakar, A.R.,, and Gray, J. 2008: “ThesqlLoader Data Loading Pipeline”, Computing in Scienceand Engineering, 10, 1 (Jan/Feb 2008), 38.[5] Neilsen, Jr., E. H.,. 2008: “The Sloan Digital Sky SurveyData Archive Server”, Computing in Science andEngineering, 10, 1 (Jan/Feb 2008), 13.[6] J. Gray, A.S. Szalay, A. Thakar, P. Kunszt, C. Stoughton, D.Slutz, J. vandenBerg. “Data Mining the SDSS SkyServerDatabase,” Distributed Data & Structures 4: Records of the4th International Meeting, pp 189-210 W. Litwin, G. Levy(eds), Paris France March 2002, Carleton Scientific 2003,ISBN 1-894145-13-5, also MSR-TR-2002-01, Jan. 002-01.pdf.[7] Szalay,A., Gray, J., Fekete, G., Kunszt, P., Kukol, P., andThakar, A., “Indexing the Sphere with the HierarchicalTriangular Mesh”, Microsoft Technical Report lt.aspx?id 64531[8] Szalay, A.S. et al. 2008, “GrayWulf: Scalable ClusteredArchitecture for Data Intensive Computing” MicrosoftTechnical Report MSR-TR-2008-187.[9] Simmhan, Y. et al. 2008, “GrayWulf: Scalable SoftwareArchitecture for Data Intensive Computing” MicrosoftTechnical Report MSR-TR-2008-186.

2.1 Migrating the Data With most of the other databases on AWS, the assumption is that users will set up their own database first and then import the data into it. In our case, since we had a pre-existing (and large) database with a complex schema, it made much more sense for us to migrate the database in one piece to the EC2 virtual server.