Author Topic: Deploy or move SQL DB  (Read 3196 times)

0 Members and 1 Guest are viewing this topic.

Jeff H

  • Needs a day job
  • Posts: 6144
Deploy or move SQL DB
« on: February 17, 2011, 11:11:26 PM »
I have never figured this out
You make a application and create a SQL DB.

How do you move that DB to another computer and be able to connect to it or deploy it?

n.yuan

  • Bull Frog
  • Posts: 348
Re: Deploy or move SQL DB
« Reply #1 on: February 18, 2011, 10:30:39 AM »
The most likely reason to use a database, especially a server type DB, such as SQL Server (do you mean SQL Server 2000/5/8, or its Express version by "SQL DB"?), is to share data with the DB as central data store. In rare occasion, an user application may use a sort of database for single user/single app.

Firstly, assume you are talking the SQL Server (Express) as shared data store. In this case, the SQL Server itself should be installed in one computer in the network (usually a server computer), and all applications that need to access the data would connect to the DB when needed. As developer, you may have SQL Server installed in the same box as VS/Acad, where you design the schema of the database (table structure, queries, security...). Once your development is done, you have to make the DB or part of DB used by your apps availlable to the other users as central data store.

You can do things in different ways (firstly, a SQL Server should be already installed somewhere in the network, which can host many databases)

1. If the entire DB is newly designed, you can use detach/attach or backup/restore to move your database (contained in *.mdf/*.ldf file pairs, usually) to the taget hosting SQL Server.

2. If the DB is not new and your app just did some changed to the DB (adding tables/columns in table, adding or modifying queries...), usually, you write SQL script to reflet the changes to the DB, and run the script in the hosting SQL Server to modify the changes you made to the targeting DB. You do not want to attach/restore your developing DB to get production DB with real data replaced.

For the other computers that run your app (or CAD app), they would connect to the database the same way as your app connects in the same box. In MS world, the connection is via certain database provider, mostly specified in ConnectionString.

Your question is a bit vague and lack of detail, so it difficult to have a straight anwser.

Jeff H

  • Needs a day job
  • Posts: 6144
Re: Deploy or move SQL DB
« Reply #2 on: February 18, 2011, 11:29:09 PM »
Thanks for the reply Norman

Sorry for being vauge.

I should have been more descriptive so there is no guessing about what I am doing and,
by explaining what I am doing you could tell if I was using the wrong approach or trying to force the wrong solution to work.

For starters this I what I am doing and maybe saving to a file or .cvs may be a better solution than a database.

They have a excel file and have used Microsoft Word to keep a list of there projects for each year.

The pic below shows the first job in a list. The minium would be 30 a year and max would be under 100.

So there is not alot data and 3 to 5 people accessing the data and updating the data. They would probably at most update the data no more than a couple times a week.
Just adding the ability to query jobs by ones not completed or by certain client ......etc nothing complicated.

I am creating the Database using SQL Server Express or actually I use Microsoft Access to create the DB structure then use the Upsizing wizard to create a new SQL DB(seems quicker).

So not to waste any more of your time and to figure out what approach to use.
Is there any other information you would need know before you make a decision on to use a database or not? Asking so I know in the future what to look for or what information to ask client.
Should I use a SQL DB?

Thanks again 






« Last Edit: February 18, 2011, 11:33:58 PM by Jeff H »

n.yuan

  • Bull Frog
  • Posts: 348
Re: Deploy or move SQL DB
« Reply #3 on: February 22, 2011, 10:52:20 AM »
From the data usage you described, it seems that using a file based data store (Excel sheet, or even Xml/plain text) might be sufficient. A file based database, such as Access, or SQL Server Compact would alos do.

However, it is very likely, as most programmers/developers experienced, once you started collecting data, a snow ball starts rolling, and you would regret that you did not directly start with a good database system, be it for a small office or large office.

Many programmers working for a small office, especially when doing AutoCAD programming, tend to firstly look into Excel sheet, or maybe, MS Access, because it comes with MS Office and most people thought they know how to use it (especially for Excel sheet).

Data in Excel sheet/Access database can be accessed/queried with MS Jet Engine/Access Database Engine without running/installing Excel. However, AutoCAD 64-bit would causes some issues that would affect your decision of whether use them or not (e,g you need to install 64-bit MS Access Database Engine, which may be conflicting with MS Office installation).

There is other things that may affect your decision of whether you use SQL Server or other "simple" file-based data store. Such as data security, application security. With SQL Server, you can easily secure the production data, while using file-based data store, everyone who needs to use your app would have to have read/write permission. That means they are free to copy the file. As for the application security, since the app only be useful when connected to your custom database, which is secured in the SQL Server (that is, no one can easily get a copy of the database itself), so, even someone can take your app (exe or dll) away, they unlikely to get it work without connecting to the database. While with file based data store, someone could make a copy of both the app (EXE or DLL) and the data file and use it somewhere else.

So, in most cases, I'd go with SQL Server Express. In this case, I'd just do the development with SQL Server Express installed locally. Once the develoment is done, I'd duplicate the database to a production SQL Server Express instance installed in the office network, be either dethaching/attaching or backing up/restoring the database. Yes, there is quite steep learning curve of understanding how SQL Server is set up/managed.

huiz

  • Swamp Rat
  • Posts: 913
  • Certified Prof C3D
Re: Deploy or move SQL DB
« Reply #4 on: February 22, 2011, 02:37:20 PM »
In simple words: you don't deploy a database. When you install your software, you create a database (the tables with the colums you need) in an existing SQL server. Your application sends SQL commands to the SQL server to create the database, create tables and create colums. You could even fill some example data. Then you run your application and while using it, the database will grow.

If you need the data from one computer to another and the other doesn't have access to the database, you need to write some code to extract all data from the database and import it in the database on the other computer.
The conclusion is justified that the initialization of the development of critical subsystem optimizes the probability of success to the development of the technical behavior over a given period.

Jeff H

  • Needs a day job
  • Posts: 6144
Re: Deploy or move SQL DB
« Reply #5 on: February 23, 2011, 01:49:44 PM »
Thanks guys I thought it was simple, as copy the the file over. Having real data to be used I thought filling it in to test then use that database to let them start with, but it seems it would take a little more work.

Jeff H

  • Needs a day job
  • Posts: 6144
Re: Deploy or move SQL DB
« Reply #6 on: March 03, 2011, 03:46:14 AM »
Would you suggest using OEM(Object Entity Model) since that is what it looks like Microsoft is pushing although LINQ to SQL will still work fine?

n.yuan

  • Bull Frog
  • Posts: 348
Re: Deploy or move SQL DB
« Reply #7 on: March 04, 2011, 10:44:53 AM »
Would you suggest using OEM(Object Entity Model) since that is what it looks like Microsoft is pushing although LINQ to SQL will still work fine?

It depends on the situations.

In a smaller office/network, it may be simple to have application (CAd addin or not) to connect to database server directly, In bigger company/nework, for varous technical reasons, there tend to be service layer(s) between front application and the back end data store. LinqToSQL and Entity Framework are mainly oriented for service layer, but can still be used to directly connect to database.

LinqToSQL is older than Entity Framework. Someone thinks that LinqToSQL is dead because of Entity Framework's rising. IMO, both can be used as long as it does your job. Entity Framework provides a complete entity model that tracks the change set, so you do not have to submit the data back to data store with every change. You can let the client app do update the data in the entity model and only do the submisision in the end. Of course, then, you may have to handle concurrency issue.

If the client app just need read-only data, LinqToSQL would make building the data access layer very easy and the data access layer lighter.

By all means, you can still use the traditional ADO.NET's DataReader/DataTable/DataSet, or use them to populate your own custom object.

Whether the office/network is small or big, having a service layer between client apps and the backend data store would be better structure in most cases. That separates the client app and data storage, so if the storage changes for some reason, the client app may not need to be updated at all as long as the service contarct remains. Once you have service layer, you'd find Entity framework in conjunction with WCF data services, WCF RIA services make data access to SQL Server a lot easier to do.

Jeff H

  • Needs a day job
  • Posts: 6144
Re: Deploy or move SQL DB
« Reply #8 on: March 11, 2011, 08:49:28 PM »
I might be wrong and there is a very good chance I am, but

They have Windows Small Business Server 2008, and pretty sure is the standard version, unlike the premium version that comes with SQL Server.

Can you use SQL Express on a Network? I thought you could only use it on a local machine.
They have Access, but I played around with the Entity Framework and would rather use that, but I do not think I can use it?

vegbruiser

  • Guest
Re: Deploy or move SQL DB
« Reply #9 on: March 12, 2011, 08:24:34 AM »
AFAIK you can use SQL Express on a network - although you might have to fiddle with the configuration a little - I forget what exactly, but I think it has to do with the tcp/ip settings that are installed by default.

Jeff H

  • Needs a day job
  • Posts: 6144
Re: Deploy or move SQL DB
« Reply #10 on: March 12, 2011, 08:32:20 PM »
AFAIK you can use SQL Express on a network - although you might have to fiddle with the configuration a little - I forget what exactly, but I think it has to do with the tcp/ip settings that are installed by default.

Does that mean more than one person can access the database?
For some reason I thought the Express version only allowed the local computer to use a database.

Thanks

dgorsman

  • Water Moccasin
  • Posts: 2437
Re: Deploy or move SQL DB
« Reply #11 on: March 13, 2011, 09:35:30 PM »
I *think* that is the "Compact Edition" you are thinking of.  FWIW I see Express being quoted as useable for some piping programs so I'm pretty sure it can be multiple user.  The limitations that I know of are single-processor on the server, ~1GB RAM useage, and size limitations of the database.  There may be limits on the number of concurrent users but I think you would run into problems with the other limitations first.
If you are going to fly by the seat of your pants, expect friction burns.

try {GreatPower;}
   catch (notResponsible)
      {NextTime(PlanAhead);}
   finally
      {MasterBasics;}

vegbruiser

  • Guest
Re: Deploy or move SQL DB
« Reply #12 on: March 15, 2011, 12:05:14 PM »
AFAIK you can use SQL Express on a network - although you might have to fiddle with the configuration a little - I forget what exactly, but I think it has to do with the tcp/ip settings that are installed by default.

Does that mean more than one person can access the database?
For some reason I thought the Express version only allowed the local computer to use a database.

Thanks
Hi Jeff,

This is the link I was thinking of in regards to my post above; Strange that it doesn't mention SQL Server Express in that article - I thought it did.

Here are a couple of other posts I've found useful although they may not be related to your specific problem(s):

http://www.asql.biz/Articoli/SQLX08/Art3_1.aspx

http://blog.sqlauthority.com/2007/12/29/sql-server-change-password-of-sa-login-using-management-studio/

Cheers,

Alex.