SQL Development in SSMS, Azure Data Studio or Visual Studio (confusion)

Question

I have always used SSMS to do all my administration and sql development

But after coming to another company I have been told that I have to develop locally in Visual Studio. Then publish to the development database and to GIT. To me that means I have to just write the core without being able to look at the data which is really confusing to me. I was also told that SSMS is ad admin tool only, but it says on the introduction page that its an administration and development tool. So even more confusion.

So the question is, where is the usual place to do your development. SSMS, Data Studio or Visual Studio? and is SSMS now administration only. Ive got really confused about all this because Ive been working with SQL in SSMS for about 18 years and now Im being told to work very differently.

I cant imagine being able to just write code in Vis Studio without doing lots of data checking and SELECT statements first?

Answer 1

I have been in the same situation. My recomendation:

1. Clone the database project using Visual STudio Git.
2. Develop you T-SQL objects using SSMS and test them.
3. When your T-SQL objects have passed testing
4. Create a new branch using Visual Studio Git
5. Change or Add T-SQL objects you developed on SSMS
6. Commit them using "git commit -m"
7. Push the changes using VS Git to the database project
8. Create a pull request.

Basically, keep using SSMS to develop but when changes are ready, use Visual Studio Git to submit your changes to the database project source control.

Hope this helps.

Answer 2

It is however still a little (A lot) vague after reading your recommendations. Are there any good videos I could watch that does this process? I'm basically working with a Dev Database at this point. We havent even moved to Test or Prod.

Clone the database project using Visual STudio Git. (As in create a local copy?)

Develop you T-SQL objects using SSMS and test them. (In my dev database?)

When your T-SQL objects have passed testing

Create a new branch using Visual Studio Git (I'm not actually sure what this means. Any documentation would be good)

Change or Add T-SQL objects you developed on SSMS (How do you do this step? And do you mean add to your local copy?)

Commit them using "git commit -m" (Again how do you do this step. This commits it to Git?)

Push the changes using VS Git to the database project (What do you mean by database project? I developed in SSMS straight onto the dev SQL DB so im lost on this step)

Create a pull request.

Answer 3

When you clone a project you basically make a local copy of your database objects (tables with their indexes, stored procedures, user-define functions, etc) but its only the definition of those objects as .SQL files on your local PC. Each object will have a local copy as .SQL file.

First you need to add Git to your skills. On this dociumentation you will learn how to clone a repository (clone the database project the whole team is using), create a branch (prepare your changes that will be later merge with the database project used by the team), commit changes, sync your local copy with the database project use by the team of developers/DBAs (using fetch/pull), and create pull requests to validate and merge your changes to the team project.

Here you will learn the concept of database projects using Git.

Let me know if there are more concepts that need clarification. Be patient learning git and integrating that to your daily work.