Communication between 2 diferent servers

Hi,

I have 2 set of SQL servers and Web servers in different location. Is there anyway I can query data from SQL server at location A from Web server at location B?

 

Thanks.

 

Cheers 

probably, depends if there's no firewall blocking access to SQL Server A from location B. the politically correct approach is to expose data from SQL Server A using web services hosted in Web Server A, then Web Server B can connect to Web Server A's web services.

cruizer:

probably, depends if there's no firewall blocking access to SQL Server A from location B. the politically correct approach is to expose data from SQL Server A using web services hosted in Web Server A, then Web Server B can connect to Web Server A's web services.

Cruizer,

Please help me understand the answer given. If there is no firewall blocking, the answer would be to use Web Services ??!?! Arent Web Services used in areas where there are firewalls ? Please me understand the politics in this.

 Why would anyone want to query data in SQL using Web Services ? Have you ever taken a look at how verbose that is PLUS you have to create another application layer to parse and process the SOAP messages ? A XML infoset of 1000++ records (which is nothing in SQL) will probably caused your web server to time-out before it can be sent across

If it is all happening in the same local environment/subnet - then used native SQL Named Pipes (1433). This will ensure fast, optimized performance and setting all of them up (if they are all in the same subnet) is a no-brainer.

If this is not happening in the same local environment/subnet, then something is really wrong with your architecture. Data Tiers should be separated from application tiers - YES, but should not be via hard boundaries.

A building with a bad structure will collapse sooner than later regardless of the decorations and furniture you put inside of the buildings.

sorry if I had not been clear. what I was trying to say was that if there were no firewalls/access restrictions to the SQL Servers then Web Server A can directly access SQL Server B, and Web Server B can access SQL Server A via TCP connections.

I don't think that is commonly the case though, as network admins would usually seal off TCP port 1433 and 1434 to access from the Internet. in that case data access should be abstracted into web services so they can pass through firewalls. that's why I said it was the "politically correct approach" of course you shouldn't expose data access through web services in the same way you expose data access using direct database access. there are latency and bandwidth limitations (and protocol overhead, like you mentioned) so the design shouldn't allow doing something like passing hundreds of thousands of rows between networks. a more coarse-grained data access API will be better in such a situation.

As said -

2 different locations on the same subnet - named pipes

2 different locations on different dmzs - relook your architecture.

 

My initial problem is I am developing a project in my server, but my client want to host the web application on their server, every changes on the application and database design need to update to the client side server. I can simply duplication my database and application and paste on client side but the problem is on the client side they will keep updating the data in database, so how can I maintain the database structure same as the structure on my side without affecting their data?

Thanks. 

dude, I really have no idea what you are talking about and have trouble understanding what you are trying to do.

What is a "client side server" ? and what is "duplication my database and application and paste on client side but the problem is on the client side they will keep updating the data in database" ?

Are you talking about data mirroring, sync-ing or duplication techniques ? I am afraid no one here will be able to help if you cannot describe your architecture in more details. Your subject title is obviously wrong as well.

I assume you know details of the tables, views, stored procedures and user functions existing in your client database. When you have changes and updates to these database objects, it is possible that you can also write queries to "clean up" and "migrate" the existing data.

For example, if you add a new column to a table, you can also write a query to populate data for the column. If you make a great deal of changes to the database, you can also write a query to (i) store the old table data in a new (temporary) table, (ii) remove the old table, (iii) create the new table and (iv) populate the new table using the data from the temporary table. Finally you can remove the temporary table if you want or till everything test out fine or till some time later (just in case you want to roll back your changes).

I would argue if that is a scalable technique. Doing this for 100s of tables with a multiplying effect on columns and rows would seriously dampen the performance of a CRUD process.

But anyways ...

Sorry for some typo error cause the misunderstanding.

I am not sure what technique I am going to use. Let me describe my case.

I host a web server and sql server, my client now want to host their own server, so now i duplicate my web and sql server  to my client's server.

The problem is, this is a continuous development project, which means while I am still develop the project, the client is running the application on live.

The data on client server will keep growing and my sql server will keep changing as in adding/modifying of columns/tables, and the tables in the database is more than 100 tables. How can I synchronize the database structure, without lost of client data? Another constraint is there are a lot of foreign keys in the database.

 

Thanks. 

now that is tricky.

any sane person depending on some live/production application would know that nobody should touch it unless it's really needed. why not just have a separate "staging" site? it doesn't have to be in a different machine. then you can update the staging site from your development copy and see if it works fine. once it is, you'll be able to update the live application after momentarily shutting it down.

if it were up to me, that's how i'll do it.

The only problem is that the client has live data on the production server. Having a staging site helps, but only for testing and making sure that the new update works. The thing is that while there are tools out there to compare databases, I do not think that there are tools to make the changes. Hopefully I am wrong.

If this project is going to take a rather long time to do it, and that there are so many tables (and database objects), I would probably develop my own database migration tool to help me out. You would need the new database structure to be stored somewhere, or using the same tool to write out XML. Next is to iterate through the tables structures and compare for differences. Then for each table, do the necessary migration. How the new data is to be populated can be determined at runtime. Using defaults while defining the new tables structure will help too.

yes, care has to be taken to ensure that the existing data on the live server does not get corrupted when it's updated to the newer version.