Post reply

Basic SQL Server Questions

  • April 20, 2004 at 6:16 am

    #161807

    Hi,

    I have designed a local SQL Server database.  Now, I want to deploy it on the internet.  Our site is hosted by a provider that supports SQL Server.  I am using SQL Server Developer Edition.  I have the following questions:

    • I would like to have a copy of the database on the remote server and use replication to keep them in synch.  However, when I try to even start to set up replication, I get an error that replication will fail as my SQL Server is registered/mapped to the system account.  How can I change this?
    • I have 2 SQL Server groups.  In the one group, I have one registered SQL Server (on the system account).  I have tried and tried and tried to register another SQL Server in Enterprise manager, but I have not been able to do so.  In the Wizard, there are no SQL Servers in the drop down and if I try to type a name, I get an error that the SQL Server doesn't exist and/or access denied.  Where do I need to set up the SQL Server before I go to register it in Enterprise Manager?
    • Is it possible to connect to the remote SQL Server at our provider (with Developer edition)?  How can I do this?  I have the server name, database, username and password
    • Will I be able to set up replication with the Developer Edition?  I want to run the database off of our provider's server, but keep it in synch with the master database on my local machine.

    Sorry for such a long message and thank you in advance for any help or pointers!  I have tried for hours and seem to be getting no where!

    E

  • April 20, 2004 at 2:38 pm

    #503548

    • Security tab of the SQL Server Properties in Enterprise manager will tell you what account is being used to run SQL Server.  You can change this to a local or a domain account if you like.
    • Go to the Alias tab of the Client Network Utility (programs/Microsoft SQL Server/CNU) and add an alias to the other server. The network library is likely TCP/IP and you may want to use the IP address for the server name and set the alias name to whatever you want.  This alias name is what shows up the the drop down.  
    • Developer Edition is just like Enterprise edition except you can't use deeloper edition for production.  You should be able to connect
    • Replication with Developer edition?  Its supported but I think its meant for testing or developing not production.

    Francis

  • April 21, 2004 at 8:12 am

    #503648

    Good advice from fhanlon.

    Half of all squirrelly windows surprises are security related.  Want to dump back-ups to a network drive?  Batter have a network user account.  Want to use a mapi profile for messaging? Better have a user account.

    I tend to go a little overboard.  I create Deamon accounts for all sorts of services if I am doing more than the simplest application.  In your case, I would create a DataDaemon local account to run the database server unless you are part of a corporate network that you wish to be able to interact with.  BTW, this means that when you are doing network IO, the network can see that DataDaemon is the account probing whatever, and this can be a good thing.  It also means that you can protect your self from certain classes of hacks becuase you know and can set what access XP_SHELL, for example, has to your system.  "May read this file"  "May write to this directory"

    I question whether Replication is what you want to do here.  Replication is designed for keepin the two systems in synch over the long haul.  You more likely want a one time Export or Import (depending upon which side you are going to drive the process from. I would set up the connection to the remote server/database in Entreprise Manager as described by fhanlon.  Then Use Tools / Import to do a one-time pull. 

    If this is something you think you are going to do regularly, you may want to set up a Local Data Transsformation on the server in the Sky to let you more closely control the replication process.

     

    Of course, if you have only "The ISP's new exciting Web Interface" to work with, you will have to script the whole thing instead.

  • April 21, 2004 at 9:00 am

    #503655

    Hi,

    Thank you to fhanlon and tobias for responding to my post!

    A couple more questions for you or anyone out there:

    • Regarding my replication question: I want to keep the local database as the main database and export part of the database to the ISP.  Users will then log on and enter and update some information there.  Then, I will need to download (pull?) the data from the ISP and update the main database as well as update the ISP database with changes in the concerned tables etc in the local/main database.  I am guessing I would want to do this at least once a week and maybe more.  Would you advise replication or another solution?
    • As Tobias said, I only have the ISP's web interface to work with...any pointers as to where I could learn about scripting this stuff?

    I am a Newbie...I have been working with SQL Server locally for a couple years after migrating from Access, but I have never deployed the SQL database on the internet.

    Cheers!

  • April 21, 2004 at 9:08 am

    #503656

    Are the table to be updated on the ISP the same as the ones on your main database?  If they are different you could look into DTS to do the updates.  A DTS job could be scheduled to export/import tables as needed.  If the same tables are being updated in both places them this may not work easily. 

    I've never worked with a database that was hosted somewhere to where I didn't have full access so I'm not familiar with what's included in your web interface.

    Francis

  • April 21, 2004 at 9:28 am

    #503665

    fhanlon,

    Ideally, they will be the same tables.  I will have users login and be able to add records and make some changes/updates.  I want to download these changes and update the main (local) database.  Also, I want to upload any changes that have been made to the main database to the tables on the ISP database.

    Thanks for your help!

     

  • April 21, 2004 at 9:59 am

    #503670

    the same table....  Definitely not trivial.  You will need to have date last updated and know which columns were updates on which box to determine how to merge the rows.  From MSDN:

    When replicated data needs to be updated at the Subscribers, you can use snapshot replication or transactional replication with updatable subscription options or you can use merge replication. The method you choose depends on your replication topology and the needs of your application and its users.

    Use merge replication when . . .Use snapshot replication or transactional replication with immediate updating or queued updating when . . .

    • Replicated data is read and updated at the Subscriber.
    • Subscriber and Publisher are only occasionally connected.
    • Conflicts caused by multiple updates to the same data are handled and resolved.
    • You need updates to be propagated on a row-by-row basis, and conflicts to be evaluated and resolved at the row level.

    • Replicated data is mostly read-only at the Subscriber.
    • Subscriber, Distributor, and Publisher are connected most of the time, but this is not necessary for queued updating subscriptions.
    • Conflicts caused by multiple updates to the same data are infrequent.
    • You need updates to be propagated on a transaction basis, and conflicts to be evaluated and resolved on a transaction basis (the entire transaction is either committed or rolled back).

    Francis

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply