none
DB design question RRS feed

  • Question

  • Hello,

    I'm pretty well versed in .NET & C# but a complete newbie to client/server database programming.  In my app I want the user to be able to install either a local database on their machine or, in the case of an enterprise install, specify a database which exists somewhere else on the LAN.   From what I've read, SqlServer Express 2008 seems like a good option to go with.  I'm using Visual Studio 2010 and have successfully connected to a test database and can work with the tables within it. 

    My questions are:

    1) What does it take to get this to work across with a database somewhere else on a network? Do I just install and configure SQL Server Express 2008 on the remote machine hosting the db and change my connection string?

    2) Are there any special considerations I should be making with respect to the database design to get this to work smoothly across a network?  I don't expect a ton of traffic here....most operations do not return large datasets and I expect I'll probably try to do batch updates vice live-updates every time something needs to be persisted to the db.

    3) Any performance issues I should worry about up-front? 

    The database itself isn't too complicated.  It has user's table which relates to the other 8 or so tables to track data for each user.   I don't expect the size of the database to exceed the 4GB limit of SQLServer express but it could in rare cases.  If that occurs I expect that the data provider could be switched over to the full version of SQL Server without too much pain (no application or db design changes)...but please correct me if I'm wrong :)

    Just looking for tips on where to start...

    TIA!

    Saturday, April 24, 2010 9:56 PM

All replies

  • Hi Shane,

     

    If you want to use only one of these, then yes, bundle the SQL Express edition along with the installer. At the time of install, based on the user selection, change the connection string accordingly and you should be good to go.

    Wednesday, September 29, 2010 12:39 PM
  • 1) What does it take to get this to work across with a database somewhere else on a network? Do I just install and configure SQL Server Express 2008 on the remote machine hosting the db and change my connection string?

    In connection string you can specify the IP address of the remote server. Which will establish connection without much issue? Remember, you have to enable TCP/IP in client protocol under SQL server settings.

    2) Are there any special considerations I should be making with respect to the database design to get this to work smoothly across a network?  I don't expect a ton of traffic here....most operations do not return large datasets and I expect I'll probably try to do batch updates vice live-updates every time something needs to be persisted to the db.

    When you talk about 'Network' the first thing comes to the mind is SPEED. Yes you have made suitable precautions while implementing a client application. For example, optimize Queries, increase Stored Procedures rather than executing query in client, Execute most of computation/calculation in Server side, bring only those much data to the client which is required( comes under query optimization).

    3) Any performance issues I should worry about up-front? 

    When you are designing tables make sure that it should not exceed the column numbers more than a limit ( norm 240~250). This consideration will make big difference for large databases where numbers of rows are many millions.

     

    Wednesday, September 29, 2010 1:18 PM