We currently have a development database where we store lot of data (all of them are external data coming in .csv or excel files and stored via SSIS packages) and business users are directly connected to the development sql server database via excel. Since the reports derived from the development server are used in the business, we are planning to move the database to a production server.

  1. Can Business users connect to the production database via MS Excel? Does best practices allow that?

  2. Can I connect to the production database via SSMS and do data analysis on that? Currently I use a Windows 8 system.

  3. Can I connect to the production database from our SQL Server Data Tools or Business Intelligence Development studio? If I can't connect to the production server, how to develop ssis packages for Production server?

Thanks Vagan

1 Answer 1

  1. Our IT team is saying that if we move to production... [users] can access the database only via API or Tableau or reporting services. is that so?

This very much sounds like a policy. From technical point of view, there is no reason why direct connections would not be possible. That being said, production admins are responsible for the production environment not going haywire, so this kind of restrictions are quite common in the industry. The current buzzword DevOps is pushing this limit a bit, but on more traditionally oriented houses there is little if any change.

The reason why production staff are quite strict about the access limits is simple. If something breaks in the production, it's their fault. Even if the real culprit was a developer running a cross-join-o-matic, dropping indexes and generally being a clown. Production admins are supposed to have the environments running at top performance, and letting those down for any reason is a sign for problems.

For a solution, talk with your team lead and boss. Write a memo that outlines the pros and cons the new method would introduce. Don't just list the obstacles, tell what's good too and propose solutions. The memo should give an impression that you are a problem solver, not a whiny complainer. After that, it's your boss' call what to do.

  1. Can I connect to the production database via SSMS?

In theory, yes. There might be firewalling or company policy that prevents such an action. Do not try to work around policies or technical barriers on your own! You might get into serious trouble.

  1. If I can't connect to the production server, how to develop ssis packages ?

Develop the packages on development environment. Push those to the test and let production admins move packages into the production.

  • 1
    Don't forget about change control and environmental auditing! We simply can't risk having those sneaky developers making what-seems-to-be-tiny tweaks to anything in production without full documentation of a rollback procedure, KT documents for the support staff, and approval by the change control board which meets on the 5th Wednesday of every month.
    – beeks
    Feb 24, 2016 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.