September 3, 2024 at 6:51 am
For 15 years I worked with tens of SSIS instances with various customers, always from a remote session. I am used to setting up an RDP to the SSIS server and then using Visual Studio / SSDT (as installed on server) to develop.
At my current job I want to work with SSIS , but am not allowed to access the server as I would by first setting up RDP.
Is it even possible to develop from a client machine in this fashion ?
September 3, 2024 at 6:56 am
Yes, of course.
In fact I would say that you've been going against best practice for those 15 years.
Developing locally with VS and SQL Server and then checking in your changes to a VCS for auto-deployment to a server is the standard way of doing things.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 3, 2024 at 7:34 am
Don't think so. The customer would be 100 miles around the block and the solution and data itself only existed on the customers server.
No other option than to set up RDP to the server.
I can install Visual Studio on my client station, but I cannot install and run a complete SQL install with SSIS running on that workstation, let alone have data on the client
September 3, 2024 at 7:42 am
Don't think so. The customer would be 100 miles around the block and the solution and data itself only existed on the customers server.
No other option than to set up RDP to the server.
I can install Visual Studio on my client station, but I cannot install and run a complete SQL install with SSIS running on that workstation, let alone have data on the client
In which case you RDP to a client machine at the customer's site. Not to a server.
Firing up VS/SSDT on a customer's SQL Server box is bad practice, as it could easily affect database performance in a bad way.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 3, 2024 at 8:53 am
Okay, let me rephrase the question: What in you opinion should then be installed on that client (the one RDP'ed against) to get a workable development setup?
And yes, in some case running SSDT on a server added some overhead in terms over memory consumption. Not really a problem though in almost all cases
September 3, 2024 at 9:36 am
Okay, let me rephrase the question: What in you opinion should then be installed on that client (the one RDP'ed against) to get a workable development setup?
And yes, in some case running SSDT on a server added some overhead in terms over memory consumption. Not really a problem though in almost all cases
VS Pro + extensions (SSIS, SSRS etc), SQL Server Development Edition (matching whatever version the client uses), SSMS/ADS, VCS software matching whatever the client uses, plus your own favourite support utilities (SQL Prompt, VS Code, Notepad++, etc).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 3, 2024 at 2:19 pm
I work remote and do SSIS on my laptop which is 1600 miles from the server. I am on the network, so I have a direct connection to the server meaning my conn mgrs are setup to hit the dev server. I can test my ETLs with small amounts of data but for troubleshooting an issue or something that might require me to process more data I have a VM that I RDP into that is on prem and I have a full dev environment setup there as well. We don't install any dev tools on our SQL Servers, not even in test.
I would request a separate VM or virtual desktop from them where you can install all your tools. We have a consultant that has a virtual machine in our environment which they can RDP into and they have all their dev tools installed.
good luck
September 3, 2024 at 3:24 pm
That still wuld require install of all components on the client workstation. As the server mentioned is solely used for staging purposes during the early morning, then database activity is almost zero during working hours.
As this organization does not allow anything else than thin clients, I'll try pushing for installing components on the server.
In any case, my question is answered. Working outside server (through RDP) requires running SSIS / SQL locally
Thank you for contributions..
September 13, 2024 at 1:24 pm
And yes, in some case running SSDT on a server added some overhead in terms over memory consumption. Not really a problem though in almost all cases
It most certainly is a problem. Installing developer tools on your server (hopefully not production) is a security risk and a risk to the stability of your server, and a violation of separation of concerns. What happens if you unwittingly write some bad code that brings your server down? What happens if you accidentally delete or break some critical server component? Are you sure the components you need to install to do your dev work won't destabilize the server? It's just not a good practice to do development work on the server.
Obviously I don't know your setup though, nor your relationship with your client...do you have access to the client's network? If so, then you can do all your work on your PC and simply deploy to the target server. All you need is appropriate version of Visual Studio and the SSDT tools installed there. You don't need a local SQL Server installation, local SSDT can access a remote server, as long as you have network access to the server and know the appropriate connection settings/credentials. This is most certainly possible, it's how I do all my SSIS work. But then, I'm on my company's network, so I can do that. Another alternative: can your client provide you with network access or a PC that has network access? If so, you're set. I'm surprised that in your 15 years of working with SSIS that you've not considered, or been able, to do this
You may have no other choice but to put dev tools on the server, but were I you, I'd try to avoid that if possible, and were I your client, I'd say "No way are you doing that."
September 13, 2024 at 4:13 pm
A typical customer would be a small production company where in the past a consultant simply developed on one of the servers within the clients network. Back then it was business as usual to be on site , run a RDP to that server and develop within a SSIS solution where all code was developed and stored at the customers site. And in total we are talking about 60-80 clients throughout the country.
I feel a little sorry and annoyed that my technical question was transposed into a discussion what is right or wrong. Basically I maintained solutions developed by earlier consultants and we're talking about firms that literally would balk over buying 10 powerBI licences , let alone shelve out the money for seperate development servers or whatever.
And I never ever 'destabilized' a server during working hours by fixing a problem or changing some package. We always proposed a minimal of 2 BI servers where development would be done and tested on the non production server. But it would always be the customers choice . Anyway , 'breaking' something would have minimal consequenties with setups where ETL ran during the night and cubes were build before office hours.
Last year one of our customers still managed to run a BI server using Windows 2003 server with SSIS 2005. We do advice, but we are not responsible for customers choices infused by what they want to invest in their BI.
September 13, 2024 at 5:00 pm
A typical customer would be a small production company where in the past a consultant simply developed on one of the servers within the clients network. Back then it was business as usual to be on site , run a RDP to that server and develop within a SSIS solution where all code was developed and stored at the customers site. And in total we are talking about 60-80 clients throughout the country.
I feel a little sorry and annoyed that my technical question was transposed into a discussion what is right or wrong. Basically I maintained solutions developed by earlier consultants and we're talking about firms that literally would balk over buying 10 powerBI licences , let alone shelve out the money for seperate development servers or whatever.
And I never ever 'destabilized' a server during working hours by fixing a problem or changing some package. We always proposed a minimal of 2 BI servers where development would be done and tested on the non production server. But it would always be the customers choice . Anyway , 'breaking' something would have minimal consequenties with setups where ETL ran during the night and cubes were build before office hours.
Last year one of our customers still managed to run a BI server using Windows 2003 server with SSIS 2005. We do advice, but we are not responsible for customers choices infused by what they want to invest in their BI.
You asked one question in your initial post, which I answered in my response. That could have been end of thread.
But what followed was a discussion of best practice, which you did not seem to agree with & that's why (I believe) people chimed in.
No one here is going to suggest you did anything wrong by advising customers of good practice and having them decide not to follow it. At least they did so with their eyes open. You've done your due diligence – but that stance was not clear from your earlier posts.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 14, 2024 at 8:11 am
But what followed was a discussion of best practice, which you did not seem to agree with & that's why (I believe) people chimed in.
Sorry , but I never advocated that there is a 'best practice', nor did I state that our way of working with tools installed on a server is a preferable way of handling SSIS. I simply described how we work. Across the pond in the old world at least literally every company uses SQL server somewhere and very often someone decides on using the various services that are available .
But , let me again thank everyone for their reactions. Yesterday it was decided to employ an additional instance running development edition that I can take over to develop on.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply