CODEWITHSUNDEEP
sql-serverssisetldatabase-administration
triplestones
triplestones

Reputation: 393

SQL Server for ETL only?

I have recently commissioned a new virtual server with SQL Server 2016 installed, on which I plan to schedule and run all (or at least all new) SSIS packages.

My question is... Would it be best practice to keep this server completely free of data? i.e. Avoid even staging data on this server and use it purely for processing data from other servers, or would staging data on this server (retained through runtime only) be acceptable/preferable?

I realise that's a pretty broad question and that there are probably benefits to both approaches but as this is a shiny new server which I have control over, I'd like to get a feeling for how strict I should be about this.

Thanks for any advice in advance!

Upvotes: 1

Views: 133

Answers (1)

ppijnenburg
ppijnenburg

Reputation: 153

I think it depends on the company policy and your judgement. I've worked with both solutions which both had legitimate reasons.

Storing data on the server itself might yield performance gains if you need to apply a lot of transformations or itterate through the data at some point, thus making many requests to the other server. The connection speed to the server might be a bottleneck in those cases so it might be a solution to store the data on the server and perform al these actions local. However, keep in mind that you follow your company's data guidelines regarding security and what not.

Currently I'm working with a server for processing and a big beefy datacluster. In this case we keep the processing server clean. It has the benefit of all your data in the same spot, it is organised and adheres to all data guidelines the company provided.

Upvotes: 1

Related Questions