Windows Azure Support

[Hatchman]

Hi everyone

First post and all. I am wondering if EA can be supported in Azure. While am reasonably confident that TFS in the cloud could be used for Source Control I am more interested in the usage of SQL Azure as the database to store the model(s)

Has anyone actually tried this or validated that it can work?

Many thanks

[smendonc]

I tested EA with SQL Azure a little over a year ago.  As I recall I needed to add clustered indexes to various tables to comply with Azure restrictions at the time.  Other then that I had no issues transfering a model to the SQL Azure repository and accessing it.  The connectivity information was the same as used for connecting SQL Management Studio to Azure.

I tested this with a model that was about 10M in size with only a handful of simultaneous users.  I have not used this configuration on a production repository.

Stan.

[Rhys Lewis]

Here are the additional lines that I added to SQLServer_EASchema to make it compatible with Azure's clustered index requirement.  

Code: [Select]

-- Create clustered index for Azure
CREATE CLUSTERED INDEX [IX_Azuret_attribute] ON [dbo].[t_attribute] (ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_attributeconstraints] ON [dbo].[t_attributeconstraints] (ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_attributetag] ON [dbo].[t_attributetag] (PropertyID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_authors] ON [dbo].[t_authors] (AuthorName)
GO
CREATE CLUSTERED INDEX [IX_Azuret_cardinality] ON [dbo].[t_cardinality] (Cardinality)
GO
CREATE CLUSTERED INDEX [IX_Azuret_category] ON [dbo].[t_category] (CategoryID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_clients] ON [dbo].[t_clients] (Name)
GO
CREATE CLUSTERED INDEX [IX_Azuret_complexitytypes] ON [dbo].[t_complexitytypes] (Complexity)
GO
CREATE CLUSTERED INDEX [IX_Azuret_connector] ON [dbo].[t_connector] (Connector_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_connectorconstraint] ON [dbo].[t_connectorconstraint] (ConnectorID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_connectortag] ON [dbo].[t_connectortag] (PropertyID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_connectortypes] ON [dbo].[t_connectortypes] (Connector_Type)
GO
CREATE CLUSTERED INDEX [IX_Azuret_constants] ON [dbo].[t_constants] (ConstantName)
GO
CREATE CLUSTERED INDEX [IX_Azuret_constrainttypes] ON [dbo].[t_constrainttypes] (Description)
GO
CREATE CLUSTERED INDEX [IX_Azuret_datatypes] ON [dbo].[t_datatypes] (DatatypeID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_diagram] ON [dbo].[t_diagram] (Diagram_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_diagramlinks] ON [dbo].[t_diagramlinks] (Instance_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_diagramobjects] ON [dbo].[t_diagramobjects] (Instance_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_diagramtypes] ON [dbo].[t_diagramtypes] (Diagram_Type)
GO
CREATE CLUSTERED INDEX [IX_Azuret_document] ON [dbo].[t_document] (DocID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_ecf] ON [dbo].[t_ecf] (ECFID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_efforttypes] ON [dbo].[t_efforttypes] (EffortType)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_files] ON [dbo].[t_files] (FileID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_genopt] ON [dbo].[t_genopt] (AppliesTo)
GO
CREATE CLUSTERED INDEX [IX_Azuret_glossary] ON [dbo].[t_glossary] (GlossaryID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_html] ON [dbo].[t_html] (Type)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_image] ON [dbo].[t_image] (ImageID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_implement] ON [dbo].[t_implement] (Type)
GO
CREATE CLUSTERED INDEX [IX_Azuret_issues] ON [dbo].[t_issues] (IssueID)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_lists] ON [dbo].[t_lists] (ListID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_mainttypes] ON [dbo].[t_mainttypes] (MaintType)
GO
CREATE CLUSTERED INDEX [IX_Azuret_method] ON [dbo].[t_method] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_metrictypes] ON [dbo].[t_metrictypes] (Metric)
GO
CREATE CLUSTERED INDEX [IX_Azuret_object] ON [dbo].[t_object] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectconstraint] ON [dbo].[t_objectconstraint] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objecteffort] ON [dbo].[t_objecteffort] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectfiles] ON [dbo].[t_objectfiles] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectmetrics] ON [dbo].[t_objectmetrics] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectproblems] ON [dbo].[t_objectproblems] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectproperties] ON [dbo].[t_objectproperties] (PropertyID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectrequires] ON [dbo].[t_objectrequires] (ReqID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectresource] ON [dbo].[t_objectresource] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectrisks] ON [dbo].[t_objectrisks] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objectscenarios] ON [dbo].[t_objectscenarios] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objecttests] ON [dbo].[t_objecttests] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objecttrx] ON [dbo].[t_objecttrx] (Object_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_objecttypes] ON [dbo].[t_objecttypes] (Object_Type)
GO
CREATE CLUSTERED INDEX [IX_Azuret_ocf] ON [dbo].[t_ocf] (ObjectType)
GO
CREATE CLUSTERED INDEX [IX_Azuret_operation] ON [dbo].[t_operation] (OperationID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_operationparams] ON [dbo].[t_operationparams] (OperationID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_operationposts] ON [dbo].[t_operationposts] (OperationID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_operationpres] ON [dbo].[t_operationpres] (OperationID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_operationtag] ON [dbo].[t_operationtag] (PropertyID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_package] ON [dbo].[t_package] (Package_ID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_palette] ON [dbo].[t_palette] (PaletteID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_paletteitem] ON [dbo].[t_paletteitem] (PaletteID)
GO

[Rhys Lewis]

...and the second half here (5000 character limit).  Note that some of the lines are commented out because those tables already have clustered PKs.

Code: [Select]

--CREATE CLUSTERED INDEX [IX_Azuret_phase] ON [dbo].[t_phase] (PhaseID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_primitives] ON [dbo].[t_primitives] (Datatype)
GO
CREATE CLUSTERED INDEX [IX_Azuret_problemtypes] ON [dbo].[t_problemtypes] (ProblemType)
GO
CREATE CLUSTERED INDEX [IX_Azuret_projectroles] ON [dbo].[t_projectroles] (Role)
GO
CREATE CLUSTERED INDEX [IX_Azuret_propertytypes] ON [dbo].[t_propertytypes] (Property)
GO
CREATE CLUSTERED INDEX [IX_Azuret_requiretypes] ON [dbo].[t_requiretypes] (Requirement)
GO
CREATE CLUSTERED INDEX [IX_Azuret_resources] ON [dbo].[t_resources] (Name)
GO
CREATE CLUSTERED INDEX [IX_Azuret_risktypes] ON [dbo].[t_risktypes] (Risk)
GO
CREATE CLUSTERED INDEX [IX_Azuret_roleconstraint] ON [dbo].[t_roleconstraint] (ConnectorID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_rtf] ON [dbo].[t_rtf] (Type)
GO
CREATE CLUSTERED INDEX [IX_Azuret_rtfreport] ON [dbo].[t_rtfreport] (TemplateID)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_rules] ON [dbo].[t_rules] (RuleID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_scenariotypes] ON [dbo].[t_scenariotypes] (ScenarioType)
GO
CREATE CLUSTERED INDEX [IX_Azuret_script] ON [dbo].[t_script] (ScriptID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secgroup] ON [dbo].[t_secgroup] (GroupID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secgrouppermission] ON [dbo].[t_secgrouppermission] (GroupID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_seclocks] ON [dbo].[t_seclocks] (EntityID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secpermission] ON [dbo].[t_secpermission] (PermissionID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secpolicies] ON [dbo].[t_secpolicies] (Property)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secuser] ON [dbo].[t_secuser] (UserID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secusergroup] ON [dbo].[t_secusergroup] (UserID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_secuserpermission] ON [dbo].[t_secuserpermission] (UserID)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_snapshot] ON [dbo].[t_snapshot] (SnapshotID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_statustypes] ON [dbo].[t_statustypes] (Status)
GO
CREATE CLUSTERED INDEX [IX_Azuret_stereotypes] ON [dbo].[t_stereotypes] (AppliesTo)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_taggedvalue] ON [dbo].[t_taggedvalue] (PropertyID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_tasks] ON [dbo].[t_tasks] (TaskID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_tcf] ON [dbo].[t_tcf] (TCFID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_template] ON [dbo].[t_template] (TemplateID)
GO
CREATE CLUSTERED INDEX [IX_Azuret_testclass] ON [dbo].[t_testclass] (TestClass)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_testplans] ON [dbo].[t_testplans] (PlanID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_testtypes] ON [dbo].[t_testtypes] (TestType)
GO
CREATE CLUSTERED INDEX [IX_Azuret_trxtypes] ON [dbo].[t_trxtypes] (TRX_ID)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_umlpattern] ON [dbo].[t_umlpattern] (PatternID)
--GO
--CREATE CLUSTERED INDEX [IX_Azuret_version] ON [dbo].[t_version] (ElementID)
--GO
CREATE CLUSTERED INDEX [IX_Azuret_xref] ON [dbo].[t_xref] (XrefID)
GO
--CREATE CLUSTERED INDEX [IX_Azuret_xrefsystem] ON [dbo].[t_xrefsystem] (XrefID)
--GO
--CREATE CLUSTERED INDEX [IX_Azuret_xrefuser] ON [dbo].[t_xrefuser] (XrefID)
--GO
CREATE CLUSTERED INDEX [IX_AzureusysTables] ON [dbo].[usysTables] (TableName)
GO
CREATE CLUSTERED INDEX [IX_Azureusys_system] ON [dbo].[usys_system] (Property)
GO
CREATE CLUSTERED INDEX [IX_AzureusysOldTables] ON [dbo].[usysOldTables] (FixCode)
GO
CREATE CLUSTERED INDEX [IX_AzureusysQueries] ON [dbo].[usysQueries] (FixCode)
GO

[Hatchman]

Champion. Thanks mate

[jdoran-anglo]

Time to bump this thread.

It should be simple enough to do this, but I keep getting stuck in random spots.

How did you connect to your SQL Azure? Which ODBC driver did you use? Did you try using SocketShifter or something to get through a corporate firewall?

I have quite a few questions, so a guide would be appreciated.

The latest error I am getting is: "Invalid Datasource... Check configuration"

I initially thought this was due to incorrect database structure... but now that I have run the above script (as well as the one off the EA repository page), this can no longer be the case.

At this stage, I can't even setup a blank EA repository, let alone think of bringing in data yet.  

EDIT: It turned out to be an ODBC driver issue. It only worked using "Microsoft OLE DB Provider for SQL".
http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1357885265