0

I'm about to deploy an ASP.NET web application for testing in production. Essentially, this is just a table that is linked to a SQL Server table/view that is obviously dynamic when the data in the tables are updated. I have a hosting plan with a site that I'll be pushing the files to, which includes a SQL Server database in the hosting plan. I also host a SQL Server instance on the network.

My question is, what is the standard way of dealing with SQL Server in this deployment? I think I have the options below, but as someone who has only ever developed applications offline, this is new to me, so please excuse my novice ignorance.

  1. Have the application connect via IP address in the connection string to my SQL Server instance, as specifying the server name (SQLDEVSRV01 for example) won't be on the "network" when it's sitting in the site's FTP. Am I to assume my connection string will only work on the network, and once deployed to a website, will cease to function unless connecting via TCP/IP?

  2. Host the server on the web in the same site host location. So upload the site files to my website host and setup the SQL server there. Would I be able to just specify the Server name (SQLDEVSRV01 for example) in the connection string there, as whilst it wouldn't work in development as it wouldn't be on the same network, it would then work once deployed on the web?

  3. Embed a SQL Server instance within the app using localdB or something similar to hold the background data. If this is the case, can the data be accessed from the backend, either using SSMS or some other method?

Essentially, my aim here is to have the web app retrieve the data live from a view/table hosted in SQL server, where the backend data can be amended, manipulated and updated independently of the app, so the app can just pull the data when refreshed.

Improve this question

1 Answer 1

Reset to default
1

The standard way of doing this is to deploy your database to the hosting providers SQL Server instance and then connect your app to it using a connection string with their details. As it is normally a shared instance you do not have to normally worry about having the SQL instance machine patched etc.

Personally I would not be installing SQL Server myself unless I decided there was benefit to having an instance I wanted the control over nor would I be changing the structure or implementation of my application to suit a hosting providers setup.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your response @betelgeuce - it makes perfect sense. Can you connect to the database via SSMS and connect to it to develop the app, or is it a case of push it to the server to test? Can I just reference the server name?
Generally, yes, sometimes a host may have the MSSQL Firewalled away and you need to use a VPN client or such to access it. Sometimes as in the case with GoDaddy it is a paid for extra. ie.godaddy.com/help/connect-remotely-to-databases-4978

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.