Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
overlap
Starting Member
2 Posts |
 Posted - 2002-07-22 : 17:53:18
|
| I'll admit, after reading about so many problems with Replication, I'm a bit wary of using it. Books on SQL Server have entire chapters dedicted to dealing with the quirks that go along with Replication and so I'm not very inclined to work with it. I use IDENTITY alot and it seems like i'd have to jump through some hoops to get it to work properly.Additionally, even though the ISP gave me sa access (shame shame), I dont know if this policy will change and I dont know if *they* would even allow me to replicate in the future.I'm hoping that a Linked server will solve my problem but wondering if anyohne here had some advice.I'm working for a company that has a (virtually hosted SQL/web) e-commerce site at their ISP. They've got a 384k DSL link directly to the same ISP. This e-commerce site ties in strongly with some of the intranet applications I will be creating for them (inventory specifically, but probably more things in the future).I was hoping to use the e-commerce site at the ISP as a linked server for the intranet apps."What if the DSL Link goes down?" Well, I'd have a webpage setup that would allow them to "go offline" which basically means that i'd alter the linked server to be a read-only Access db. Once they are back online, they'd just click a button that says "We're back online" and a few stored procedures would execute.What do you think? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-22 : 18:12:41
|
Couple of things:1. Your ISP is pretty f---ing stoooooooooopid to grant you sa access. Did they actually give you the password for sa? If they did, then they probably gave that access to someone else too. If you value your data and its security, run away from this ISP ASAP.To test this, see if you can access the master database. If you can, then you're crazy to trust them to keep your data safe. Sorry, not your fault, but it's outrageous for them to allow that. That's just not leaving the bank vault open and unlocked; they're handing the money out to everyone.2. If I'm wrong about the above, I don't think that using an Access database as a replacement source is a good idea. A linked server to another SQL Server would be best, but avoid MS Access as a data source as much as possible. You don't want dozens or more web users to use an Access database, it'll crawl and very likely crash, read-only or not.3. Replication would probably be fine, even if the DSL link is down quite a bit. You should have your company look into a better service level if they can afford it. Most DSL providers will step up the availability for a few bucks more. If yours doesn't, and the line goes down more than once a week, take a serious look at another provider.As far as identity columns, well, if you must use them... Look at the NOT FOR REPLICATION setting under the "CREATE TABLE" statement in Books Online. It is also listed under the replication entries in BOL too.If you're anticipating a lot of new intranet apps, definitely get a more reliable DSL setup if you're going to use your ISP's SQL Server, or look at getting your own SQL Server. |
 |
|
|
overlap
Starting Member
2 Posts |
Posted - 2002-07-22 : 19:07:25
|
| hey Rob,Yep, they sure did give me sa access. The account was setup before I got there, so I'm poking around the ASP files looking for how to get in so I don't have to ask, and i see "sa" in the conn string. My jaw drops. I see that its a DSN and of course, that doesnt initially help because It doesnt give me the proper host name. So I use that DSN to login and excute xp_cmdshell ipconfig /all to gather more info about the sql server. I got the IP addresses and I now login remotely as sa.I'm a security fanatic and this really was a surprise to me. I had had written to them before asking if they could give me 2 accounts -- one that I use to develop and one that is only allowed to execute stored procedures. I *hate* when people use sa or even db_owners in web environments. It even makes me mad when Microsoft developers use it as an example in their webinars. So the situation does make my stomach turn..but I'll deal with that when the time is right.I did bring this to the attention of the company I work for, and they were surprisingly responsive. I'm sure everyone on that machine has sa access and I'm hoping that I can talk to them once I get all this going. (No CC numbers will be stored in the db. The data is sensitive, but not *too* sensitive. We'll see..)About the Acces thing, I'm sorry, I don't think I worded it correctly.I would use Access *only* when their Internet link goes down on the intranet side. Not on the e-commerce side since the e-commerce side will not depend on the intranet data.I am currently building them a SQL Server to use in-house. That SQL Server will link to the e-commerce server.As far as I know, the DSL has never gone down. But I wanted to prepare for the worst. Since their intranet apps would depend on some data from the e-commerce DB, I had to come up with a plan in case their link gets shot for a few hours. My plan was to use DTS to backup the db to an Access db each night and if ever they went down, use that Access on the Intranet side as read only until the link is up.Say the linked e-commerce server would be named THEREMOTEDB, I'd make some stored procs that would drop that linked server, and add the Access db as linked serveer THEREMOTEDB. They'd execute that if they ever went down, but once they came up, they'd execute it again and it drop the Access linked server and add the e-commerce server back as linked server THEREMOTEDB. This is so that my queries using a 4-part naming convention woudl still work.I wish that i could keep the db in SQL format, but they only have 1 sql server on the intranet, and I cant make it a linked server, so i dont know how the 4-part name could work with only 1 server.Any thoughts?ps. why do you dislike identities? what do you use instead? the UNIQUE UID thing? |
|
|
|
|
|
|
|
|
scared of replication