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 |
|
solomo2k6
Starting Member
1 Post |
 Posted - 2010-07-24 : 02:30:46
|
| Hi,I am a new SQL DBA. I just joined this company. They have around 5 websites.The process is like the following.1) Use VB Program to update the local SQL Server databases.2) Generate HTML pages from the databases with predefined folder structures.3) Upload the tables from local SQL Server to the remote SQL server with different name (table_name_new).4) Delete the old table (table_name) from remote SQL server, and rename the new table name (table_name_new) to the old table name (table_name)5) Recreate Indexes and Full text catalogs.6) Upload the generated local zipped HTML files to the web server using Filezilla.7) Unzip the HTML files using RDC.8) The database in the web server is used only for Searching purposes.Our average database size is around 1gb.Our table's average record count is three hundred thousand.And HTML files for each domains maybe around 300mb.I found it very complicated work. Because they used to upload every single file, even if it is not modified since the last time, to the web server.Now I have this doubt. If I generate the web pages dynamically from the Web Server, Will it affect SQL server's performance?. What will be the best way to reduce the overhead?Will I get any performance gain and reduced work?Please help me.I know what I have provided is too little descriptive. But I don't know what are the important factors to be made available.Thanks in Advance. |
|
|
martinch
Starting Member
35 Posts |
Posted - 2010-07-28 : 04:18:02
|
| Hmm ... I'm guessing they couldn't they use rsync/robocopy/etc to copy only the changed files (i.e. a delta copy) due to the way the remote server is accessed? I'm sure a properly-written ASP.NET web app/site correctly implementing page caching (i.e. cache the rendered page into memory for a day on first load, with a new copy for every different value of the querystring arguments - have a look at 4GuysFromRolla's articles on ASP.NET's caching) would put a minimal load on the DB server (our web server gets tens of thousands of hits per day, and it's extremely rare for the CPU usage to go above 1% on the SQL Server box - actually, the thing that causes the most CPU usage is the daily backup and index maintenance!). |
 |
|
|
martinch
Starting Member
35 Posts |
Posted - 2010-07-28 : 04:22:55
|
| Forgot to mention, RedGate make a tool for synchronising database contents (SQL Data Compare), which will generate a script containing the necessary insert/update statements to make 2 tables contents the same without copying all the data - I believe the Pro version is scriptable via the commandline. |
|
|
|
|
|
|
Dynamic Page vs Static Page