Need Advice

I just want to ask for any good advice here ragarding my project. I'm planning to create a program that will use an sql server as a database. I have 3 branches and I need the branches to be updated always when they create any transaction or changes. The sql server will be installed in a PC with Windows Server 2003 and with a public IP. the question is can I access the sql server from other branches by using the public IP of the ServerPC thru the Internet? If yes, how? if no, is there a best way to do this updating method?

Thanks!

It is not clear if you are considering that each branch will have a SQL Server and need to transfer data changes to the other branches, or if there is a 'central' SQL Server -with or without branch servers. That makes a 'big difference' with the kind of opinions you will recieve...

The 'best' method is Replication. That is what it is designed to handle. However, Replication between the branch SQL Servers is NOT included with SQL 2005 Express. (If you are using Express.)

Otherwise, you need to plan for service disruptions. What happens if the connection cannot be made?

Will you have a Queueing table that holds changes until their is confirmation they were recieved at all the branches?

IF you have a central SQL Server, and you expose it with a public IP, you will have every hacker in the world (literally) constantly attemping to get in. You MUST have a iron tight security plan and you will still be vulnerable.

|||I am planning to put a sql server for all branches where they will store per Branch Transaction so that I will not have a problem when there is no Internet. If ever there online already, the program will then connect to the central sql server to update the branches transaction. is that possible? how can i connect to the central sql server thru the internet?

Thanks for the advice.
|||If all machines are accessible to each other (e.g. you can ping, or telnet), then it's easy to connect to the central sql server(or each other). Just enable TCP on your central sql server and use TCP to connect from other machine. You can use DNS name of the server machine to connect. You may not be able to use Windows Authentication. I assume you will use some form of firewall to protect your system. You can generate a rule to only allow connections from your client machines.|||

In order to handle 'network outages', Transactional Replication would most likely be the preferred option. (Not available with SQL Express.)

And, as Xinwei indicates, you will have to open and protect the firewall for the appropriate IP ports.