separate servers prod/Dev...

  • Hello,

    The present architecture is that there is one sql server 2008 R2 (ServerA ) which has the DEV database as well as the Prod database.

    There are SSIS packages that run every 30 minutes or so...

    1-

    Do you think it is a good idea to have one server i.e. ServerD for Dev, another one i.e. ServerU for UAT and leave the current one (ServerA) as is but remove the Dev database.

    2-

    Should the SSIS/ETL be done on the same server as the production i.e. ServerA or should that be a separate server too? i.e. ServerS?

    3-

    There is also SSAS. should they be on another server?

    Thanks

  • In short, yes, maybe and yes.

    Dev and Prod absolutely have to be separate. Permissions is one reason, people who have high privileges on dev often shouldn't have elevated rights on prod. Stability is the other, if something is broken in dev, it shouldn't be able to affect prod. With both DBs on the same server it can.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I agree with Gila.

    It's always recommended to have a dev/test port to minimize disruption and catastrophic failure in development. This extends beyond database development too.

    When it comes to SSAS or many other services for that matter, it's good to keep services separate especially if they will see a lot of activity. The same may go for SSRS or other reporting services you may use in the future. I have had them all on one box. Database, SSAS and SSRS. Didn't end well when usage increased due to the limited resources of one box.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply