| Author |
 Topic  |
|
bbraman
New Member
55 Posts |
 Posted - 19 July 2003 : 16:35:54
|
| Does archiving topics in any way make make them take up less space in the database (SQL Server 2000)? |
Edited by - ruirib on 11 January 2004 17:05:42 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 19 July 2003 : 16:48:50
|
| no, it just moves them to different tables |
 |
|
|
bbraman
New Member
55 Posts |
Posted - 19 July 2003 : 17:29:53
|
| If I moved archived topics away from my primary SQL Server to a secondary SQL Server database on a seperate database server. Would Snitz Forum be easily configured to look to that seperate secondary database server when an archived topic needed to be displayed? |
Edited by - bbraman on 19 July 2003 17:49:13 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 July 2003 : 17:45:50
|
| You'd need to change the code to have two connections, one for each database. Doesn't look an impossible thing, but you need be careful with the coding. It will take some effort, though. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 20 July 2003 : 02:20:23
|
I'm thinking if you had the two SQL Servers linked the right away, you could possibly pull it off with a single connection.
I'm just curious -- are you expecting some extremely major usage on your forum? |
|
|
|
bbraman
New Member
55 Posts |
Posted - 21 July 2003 : 00:32:13
|
I added the following after my primary SQL Server connection string in config.asp:
If Request.QueryString("ARCHIVE") Then
strConnString = "driver={SQL Server};server=secondary;uid=x;pwd=x;database=x"
End If
Both topic.asp and forum.asp, in archive mode, appears to work so far, does anyone see a problem with this?
Now that I have my archive tables off-loaded to a secondary SQL Server can I delete the contents of the TOPIC_A_REPLY and FORUM_A_TOPICS on the primary SQL Server without messing up my forums statistics or some such thing?
Thanks! |
Edited by - bbraman on 21 July 2003 00:39:44 |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 21 July 2003 : 04:15:29
|
quote:
Now that I have my archive tables off-loaded to a secondary SQL Server can I delete the contents of the TOPIC_A_REPLY and FORUM_A_TOPICS on the primary SQL Server without messing up my forums statistics or some such thing?
If you do you will need to rewrite the forum_count functions so that it still counts the archived topics, and the writestatistics function most likely (not sure though) |
|
|
|
bbraman
New Member
55 Posts |
Posted - 21 July 2003 : 09:01:56
|
| Could I just null out the T_MESSAGE and R_MESSAGE fields in the archive tables on my primary SQL Server to save space but not mess up the forum counts? |
|
|
|
bbraman
New Member
55 Posts |
Posted - 11 January 2004 : 12:51:12
|
quote:
Originally posted by work mule
I'm thinking if you had the two SQL Servers linked the right away, you could possibly pull it off with a single connection.
Would anyone have the slightest idea how to achieve what work mule is suggesting?
Basically linking my webhost SQL Server 2000 (which is limited to 150MB) with my home SQL Server 2000 in such a way that I would only need one database connection string in config.asp.
Note: bandwidth on my home broadband connection would not be an issue because my I don't mind archived messages loading slowly on visitors browsers. |
|
|
|
bbraman
New Member
55 Posts |
Posted - 11 January 2004 : 15:26:58
|
| My webhost does not support MSDTC. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 January 2004 : 17:27:49
|
You'd need to create a linked server in your hosts server, meaning that you'd need to use the sp_addlinkedserver stored proc, and then use sp_addlinkedsrvlogin to map user info between the servers. You can probably use Books online to find out how these work.
After doing this, you'd need to change the value for strActivePrefix, in whatever files it is defined, so that any reference to the archived tables would be adequately qualified: LinkedServerName.DatabaseName.OwnerName.TableName. This would probably be the part that should give the most work. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 11 January 2004 17:28:21 |
|
|
|
bbraman
New Member
55 Posts |
Posted - 11 January 2004 : 18:18:01
|
| As I understand it to get queries against a linked server to work like you are saying my SQL Server webhost would have to be running MSDTC (Microsoft Distributed Transaction Coordinator) which they are not. Does anyone know if this is true? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 January 2004 : 19:02:08
|
| I'm not sure about it, but I think that as long as you do not use transactions you do not need MS DTC. A quick test on my home PC and portable seems to confirm this. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
bollywoodlyrics
Starting Member
USA
3 Posts |
Posted - 28 January 2004 : 03:08:48
|
Along this same topic. My Host provider also allocates limited disk space and I also want to archive (or a better word, offload) old messages / topics to an offline database. I don't mind if users aren't able to search the archives that are way way old. Can anyone suggest how I can achieve archiving / offloading to another database server to reduce space usage and STILL not break the Forum in any way (even the archive feature).
Basically I want to do the same thing bbraman wants but in my case, I don't require the archive functionality.
Thanks in advance,
Mukesh |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 January 2004 : 05:37:47
|
I guess you could simply create a new blank database, run setup.asp so that the proper table structure is created and then use DTS, with a query (you'd probably need 2 queries, one for topics, another for replies), to store in that database all the topics (and replies) you want to archive. You can then run delete queries to delete the archive topics and replies.
With such a strategy you could, when and if required, restore the posts from the archive DB into the live one, again using DTS. Of course, DTS should be used with identity insert option enabled, so that the topic and replies IDs remain unchanged in the archive DB. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 29 January 2004 : 18:11:14
|
So I got curious if this would work and had a few extra minutes and here's what I found out. I used the SQL Server tools (Enterprise Manager and Query Analyzer) extensively for this. You can buy the developer edition of SQL Server for around $50 these days. That's a great way to get these tools and a full copy of Books Online. You can also install multiple instances of SQL Server Developer Edition on a computer which will allow you to test this in your environment. Which is something I strongly suggest.
Here's my setup:
Main Snitz Server: L30
Archive Snitz Server: SERVER9\I2. This is a second instance of SQL Server installed on SERVER9.
I used to my actual server names when writing this. I tried using generic names but got confused to quickly :(
1. Created a database on SERVER9\I2 called SnitzArchive
2. Created a user on SERVER9\I2 that matched the user for my main snitz database. Made this user a DBO of SnitzArchive. Username and password need to match. This should be the same username/password that's in your connection string to log in to the main SQL Server.
3. Disabled DTC on both servers. Most hosts don't support DTC so I disabled it.
4. On L30, Created a linked server to Server9\I2 using this:
sp_addlinkedserver @server = 'SERVER9\I2'
, @srvproduct = 'SQL Server'
5. On L30, ran the following query to test:
select *
from [server9\i2].SnitzArchive.dbo.sysobjects
6. On L30, Used Enterprise Manager to generate a script for FORUM_A_REPLY and FORUM_A_TOPICS. On my example script, these were both owned by dbo. In your situation they can either be owned by dbo or by the specific user you created in step #2. My examples assume they are owned by dbo on the archive server.
7. Used that script to create those tables on SERVER9\I2 int the SnitzArchive database.
8. On L30, dropped those two tables.
9. ON L30, created the following views:
CREATE VIEW dbo.FORUM_A_TOPICS
AS
SELECT [CAT_ID],
[FORUM_ID],
[TOPIC_ID],
[T_STATUS],
[T_MAIL],
[T_SUBJECT],
[T_MESSAGE],
[T_AUTHOR],
[T_REPLIES],
[T_VIEW_COUNT],
[T_LAST_POST],
[T_DATE],
[T_LAST_POSTER],
[T_IP],
[T_LAST_POST_AUTHOR]
FROM [SERVER9\I2].[SnitzArchive].[dbo].[FORUM_A_TOPICS]
GO
CREATE VIEW dbo.FORUM_A_REPLY
AS
SELECT [CAT_ID],
[FORUM_ID],
[TOPIC_ID],
[REPLY_ID],
[R_STATUS],
[R_MAIL],
[R_AUTHOR],
[R_MESSAGE],
[R_DATE],
[R_IP]
FROM [SERVER9\I2].[SnitzArchive].[dbo].[FORUM_A_REPLY]
GO
Since these views reference a single table you can insert, update, delete, etc. through them.
10. On L30, ran the following statement to test:
select *
from FORUM_A_REPLY
It didn't return any rows because the table is empty but it also didn't generate an error.
11. Logged into Snitz and ran the archive. It didn't report any problems and a quick check showed there were rows in the new tables on SERVER9\I2.
12. Ran a query in snitz to find a topic I knew had been archived. It found it and I was able to display the topic just fine.
I always THOUGHT I could do something like that but never know for sure. Thanks for giving me a reason to check  .
This was pretty slow when I did it. I'm guessing it would be worse over an internet connection. I'd check the timeout on the page before you try it for real. Or maybe someone here can address that.
|
SQLTeam.com - For SQL Server Developers and Administrators |
|
|
|
Topic |
|
Topic Locked
