Sql-server – Moving to Production database for first time

sql server

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

Best Answer

  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.

Related Question