We have a production database (SQL Server 2019) with existing code (tables, procedures, functions, synonyms) that was deployed without a source repository. Our client wants to introduce a new schema with new code, but keep all the old code and data intact. We plan to deploy the database changes via Azure DevOps pipeline - SQL Database Project in VS (new approach for all new changes and projects). How do we create and deploy a database project solution that contains only the new schema and its related code, without including or modifying the existing database objects? We want to avoid touching the old schema/code to prevent any risk or downtime. The new schema should be deployed independently, coexisting with the old schema and code. What tools handle such partial database deployments? How do we manage this in source control and CI/CD pipelines, especially considering the old code is not in a repo?

When publishing a database from SSDT, there are several options to "do not drop" an object if it is not in the SSDT project. However, it seems that there is no option to avoid dropping schemas. Does anyone know if this is possible to achieve, or do I have to manually edit the published script? This is the problem when I choose to "do not drop tables", but then it will try to drop schemas that have tables in them. br Gert

I want to create a table, this table's name is "Prod", in his table, I have the: **ID**, **name**, **Buying_price** and **selling_price** when the user inserts the data, I want him to enter "name, and buying_price" and I'd like the column of **selling_price** to be calculated depending on the user's input by this method (buying_price + 20% Buying_price) I don't know how to do this, I thought that a function/procedure could help but I have no idea how to do it, since I am new to database and SQL I would so much appreciate any help

I just imported an existing SQL Server 2008R2 production database into a VS 2013 database project. I now get a number of errors along the lines of Error SQL71501: User: [mydbuser] has an unresolved reference to Login [mydbuser]. I don't really need my VS DB project to manage users, but I'm concerned that it would try to remove them upon deploy if they weren't there. The files themselves are generated as CREATE USER [mydbuser] FOR LOGIN [mydbuser]; or CREATE USER [mydomainuser] FOR LOGIN [MYDOMAIN\mydomainuser]; The error marker shows that it's specifically for the **Login**. As that's a system-level object, I can understand it being outside the scope of the db project. Is it preferred that I change them all to CREATE USER [mydbuser] WITHOUT LOGIN; or add the CREATE LOGIN clause to the beginning of each file? Removing the Login reference seems to be simpler and removing the users altogether would be the simplest. I want to make sure that I'm using the tool the way it was intended. - Will there be any issues in re-publishing any of those back to production? - What is the proper procedure for adding a user/login via a project?

I am using Visual Studio Pro 2019 (16.9.4) and SQL Server Data Tools (16.0.62103.10080) but this happens since the beginning of Visual Studio 2019. I use the Schema Comparison tool to compare databases/dacpac vs SQL projects. And everything goes fine until one day the comparison does not match the objects from the source and target and suggests a creation and drop of ALL the object in the database as shown here: I believe that something gets corrupted in the project, because from that moment on, the comparison tool is unable to match objects any more. I've tried to erase *.dbmdl file, change the codification of the files (UTF-8 with/without BOM), erase the project and download it again, etc. but nothing works. The same database/dacpac against other projects works fine... so it's something about the project! Any suggestions?

Hello i'm trying to model a simple banking scheme where I have 3 transactions: withdraw, transfer, deposit. of these 3 operations, the transfer is the only one that needs more attributes, I need to know the accounts participating in the transfer, and who received and who sent it. At first I thought of something simple like that, but it seems very strange: but this seems very vague to me, and when do I need the statement from the two accounts participating in the transfer? could someone help me with this?

I'm trying to port my system to SQL Database (Azure instance) from SQL Server 2012. I'm using Visual Studio 2013 and I have my .sqlproj with all the definition of my database inside. Given that a number of SQL statements are not available in SQL Database (like "ON [PRIMARY]", filegroups, etc.), I should change a huge number of *.index.sql , *.pkey.sql, *.table.sql files. Unfortunately I still need to cope with SQL Server 2012 installations (some customers are still on that infrastructure), so I would like to have instead a simple way to switch between the "SQLAzure" vs. "OLD-2012" syntax. E.g. something like Compilation Symbols would be useful, or similar tricks. Anyone has a brilliant idea on how to manage such an issue? Thank you very much! cghersi

I have a Postgres db server setup on my raspberry pi. With this I am working on two distinct projects: - Project1 - Project2 For these projects I have different users and the users of each should not be able to see the data of the other project. What i did to enable this was create two databases on the server: - project1_db - project2_db So my setup on pgadmin4 looks like: => Servers =====> Projects ==========>project1_db ==========>project2_db I also created two users: - project1_user - project2_user I made each of these users the owners of their respective db. By doing this i expected when logged in as project1_user to not be able to see project2_db info (basically be told when clicking the db in pgadmin4 that i dont have permissions). However, I was still able to click in and then dig into schema/public/tables. I noticed when looking at privileges for the db in pgAdmin4 that PUBLIC kept being added even after I deleted it - and my understanding is that this makes it viewable by all. So my questions are: 1. Is this server/db setup appropriate for what im trying to achieve? If it is, why am i not getting expected privileges? 2. Is my user setup appropriate / what is the correct user roles setup to use to achieve my goal? And how does the PUBLIC role tie into this? 3. Am I missing something obvious / or do i have misunderstanding of the tools? UPDATE: I just found this https://dba.stackexchange.com/questions/108975/postgresql-who-or-what-is-the-public-role on the public role. Which i guess answers the question on public. I also realized that after I was making updates I was only refreshing my databases not the whole server so the changes werent being pushed down. After I removed public access and refreshed the server the permissions worked as expected. I am now getting my expected behavior.

I'm looking for a way to move all our SQL Server databases (on one server) into source control. I tried creating a solution, and then adding VS database projects to the solution that correspond to the SQL Server databases. As I was creating the projects (via importing existing databases) I chose to import security objects. These security objects are users that need to reference logins of some kind. i.e. in a database project I have a folder Security with the SQL script: CREATE USER xxx. The VS debugger underlines this in red and warns me: > SQL71501: User: xxx has an unresolved reference to Login xxx. To fix this I tried creating a new project called SERVER, in which I would include server-related SQL files. - How do I reference this new project from the other database project?

I am using SQLPackage.exe with database publish profiles to deploy database changes to DEV and QC instances. I have databases in simple recovery mode. But when I deploy changes using SQLPackage, it reverts them to full recovery mode. This is what I am using, > "C:\Program Files (x86)\Microsoft SQL > Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"FILE > PATH TO .DACPAC" /Profile:"PUBLISH PROFILE.XML" If I use Visual Studio to deploy project changes using same publish project, same happens. What am I missing here? As far as I can tell, there is no such flag in Profile. Is this expected behavior of SQLPackage?

I have a large project tracking database used to track PMs, status, help needed, action items, reports etc. I keep the project schedule in a large MS-Project file. I've exhausted all efforts to have my organization invest in an MS-Project server. How can I use the data found in my project file: Task Name, Resource, Start Date, End Date... etc as source data into my access database. Can these files be linked somehow if I put the project ID# into my project file as a foreign key? I just need to make the data available to people other than the PM. Ideas?

In a SSDT Database project, I know how to publish the Self-Database to different locations. I can change eg, the Customer database name in the Publish profile. Example In: - Dev Environment: will publish profile to CustomerDevDB - QA Environment: will publish profile to CustomerQADB - Prod Environment: will publish profile to CustomerProdDB (This is legacy system, I did not design it like this) Now in our Customer Database, I have stored procedures which point to a Product database reference. How do I change my Project reference to be variable for each Publish Profile? Dev Environment: will publish to CustomerDevDB and will point to ProductDev QA Environment: will publish to CustomerQADB and will point to ProductQA Prod Environment: will publish to CustomerProd and will point to ProductProd I am trying to test around with Project DB Reference, and SQL Command variables. * I can point the Database Reference to different databases names, however I cannot change the reference in the publish profile yet (which is what I am really looking for). If someone can print screen images of how to do this, it would be helpful. Thanks

I am very new with encryption in SQL Server so I am stumbling over things that require prior experience. I have done my googling, but I need reassurance so here we go: Scenario ======== I have a SQL Server Project (*.sqlproj) where my team and I put all of our: - schema related changes - post deployment scripts - seed scripts Best practices? ================ However I have run into a best practices situation: 1. Should I store my MASTER KEY in my SQL Project? - I'm leaning towards no because of the password. 2. Should I store my CERTIFICATE in my SQL Project? - Seems okay to me 3. Should I store my SYMMETRIC KEY in my SQL Project? - Seems okay to me Overall =============== 1. Should these sensitive items be a part of my SQL Server Project and checked into source control? - It is usually best practice to NOT store anything with a password in Source Control. 2. Does the Master Key qualify for what I am talking about? On a side note - I am not storing column level seed data passwords in source control. I have already decided this would be a bad idea. Example for context ====================== I am pulling these examples from Microsoft's examples [here](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017) . USE AdventureWorks2012; GO -- 1. Should this be stored in my SQL Server Project? The password concerns me. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; GO -- 2. Should this be stored in my SQL Server Project? Seems harmless. CREATE CERTIFICATE HumanResources037 WITH SUBJECT = 'Employee Social Security Numbers'; GO -- 3. Should this be stored in my SQL Server Project? Seems harmless. CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE HumanResources037;

At work we are developing our latest module in C# Entity framework code-first approach. I have been given the role of developing databases from the DBContexts from within the project/s. I generated the tables/entities from the DBContext and created database projects and all is fine. My question is: When a developer changes the DBContext entities or changes a property of an entity, how can I become aware of such changes and update the database projects?

I have a Visual Studio database project with several stored procedures. I deleted one of them and then published the project. But the SP was still present on the published database and I had to delete manually. I would want the VS project to be the point of truth for the database. But at the same time, I'm publishing to a production database that has data. Is there a way to delete files in a VS database project so that those changes reflect on the published database?

I designed and maintained a database used to track my organizations projects. Access frontend with a SQL backend. I have a table for all the scheduling events. Task, Start, Finish, duration etc. Really basic. I am looking to create a form or report that displays a Gantt chart. At the same time I'd also like to create what we call a Bird on Wire Chart. Not sure it that is a term unique to my group. It is a line divided up in months with triangles associated with events and dates. Project managers use power point to move the triangles around to show major milestones for their project. I'd like to automate this somehow using the data from the schedule table. First is it possible to do these things? and second and example would be great.

I have a VS 2013 solution with two database projects. One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers. The other one, that is supposed to work as an "Controller" instance, is basically supposed to run different stored procedures on the "worker" database on the 10 production servers. So to realize this, I have scripted 10 Linked Server objects within the "controller" and call the different SPs in the "worker" databases. Here is a sample for the Linked Server scripts: EXEC sp_addlinkedserver @server = N'PROD1', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'1.2.3.4\prod1' GO EXEC sp_addlinkedsrvlogin @rmtsrvname=N'PROD1',@useself=N'False',@locallogin=NULL,@rmtuser=N'aaaaa',@rmtpassword='aaaaa' GO EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc', @optvalue=N'true' GO EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc out', @optvalue=N'true' GO So far so good, this works and the Linked Servers are created on the controller SQL Server and I can use them. BUT: Within the SSDT project / solution, I get broken references whenever I use the Linked server objects. Sure, the schema is not aware of the linked server names, since they are only in a string constant in the above script. Sample: (I translated the message on my own, no guarantee it's exactly right) > Warning 14 SQL71562: "Procedure: [dbo].[SP1]" contains an unresolved rerference to object [PROD1].[db1].[dbo].[SP1]". So my problem is that I have kind of a reference from the Controller database schema to the "worker" database schema, but I can't use / insert a database-reference, because I use several different references (10 production servers) to "access" the worker's schema... I don't want to add 10 database references to my project with the "OtherServer" option (even if I could, I guess it won't allow to add 10 references to the same database project...) I have a feeling that it should be possible to address the linked server objects in a correct manner in the solution, but I can't figure out how. I tried Aaron's suggestion with Synonyms, but there I end in the same problem as soon as I use the linked server's name resulting in unknown objects "behind" the name. I edited the question to make the problem more clear and hope anyone has an idea, because I want a clean solution without hundreds of WARNING messages.

We have SQL Server 2014 Standard Edition and would like to use Visual Studio 2012 Database Projects for version control. Do we need to purchase a license to use VS database projects? Or is it included with our standard edition license; we just have to download it separately?

Can I have multiple database projects to the same SQL Server database? Like, one main project referencing multiple subprojects. I have an application with multiple modules and each module has its own database structure living in a schema. I want to reuse the modules in another application, for example, the security module, which has the user, roles, groups, etc. I want to know if there is a way I can define those schemas and all of its objects (tables, stored procedures, indexes, views, etc) in one database project and then reference this "base" database project in the database project of the actual application.

I am using SQL Server 2008 for product development Currently application is live with 20 users now if I want to check how my database is growing and how much it will grow in month or year projection to determine the cost of hardware. Is there any application or SQL Server built-in function? Using SQL Server profiler I am able to monitor database queries and fine tuning.