Forums  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Member List  Search  FAQ  Ticket List  Log Out

 

Splitting SQL Databases Script

  		Logged in as: Guest
Users viewing this topic: none
  Printable Version
All Forums >> [Archiving & Fax] >> GFI MailArchiver >> Splitting SQL Databases Script Page: [1] 2   next >   >>
Login
Message << Older Topic   Newer Topic >>
Splitting SQL Databases Script - 17.Dec.2007 4:37:27 PM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		Hello Everyone,

We are currently using MARC5 and the databases are way too big.  We are creating DBs every three months, and this last quarter is not over yet and the DB size is over 100GB.  We need a script that can split/move the DBs to smaller ones on a per month basis instead of per quarter. 

I found a script here: http://forums.gfi.com/m_900731853/mpage_1/key_/tm.htm#900731853 but I'm not sure how to use it.  I'm new to SQL, but if someone can explain to me how the script works I could run it.  I'm not sure if it deletes/move data or copy, what information has to be customized, do I need to create the destination DBs on SQL and add them to MARC prior running the script?

Any help will be greatly appreciated.

JD
Post #: 1
RE: Splitting SQL Databases Script - 18.Dec.2007 3:58:11 AM   
sandro

 

Posts: 1345
Joined: 26.Jul.2007
Status: offline 		Hi braishfield,
 
You need to create another database manually to where you want to move the emails on SQL.
 
To run the script, you need to create a query from SQL Server Management Studio (SQL 2005).
 
Also, read carefully through the script as you need to replace values such as : "DestinationDatabaseName"
 
It is important to take a backup as well of your databases prior to running any scripts.
 
Regards

_____________________________

Sandro Pace
GFI Software - www.gfi.com
Messaging, Content Security & Network Security Software

(in reply to braishfield)
Post #: 2
RE: Splitting SQL Databases Script - 18.Dec.2007 4:02:45 AM   
Idealshopping

 

Posts: 76
Joined: 6.Apr.2006
Status: offline 		Hi Sandro,

Is the script he links to ok for Marc5 I think we used it on ver3 / 4?

(in reply to sandro)
Post #: 3
RE: Splitting SQL Databases Script - 18.Dec.2007 6:07:27 AM   
sandro

 

Posts: 1345
Joined: 26.Jul.2007
Status: offline 		Hi,
 
Just a couple of further notes:
 
- The script was written by the person who provided it in the forum post referenced above, not by GFI.
- There were no changes in the SQL tables in MailArchiver 5, hence if the script worked with MailArchiver 4 it will work with version 5 as well.
- After you split the database you would need to rebuild the indexes for the databases you perform the changes on.
 
Regards

_____________________________

Sandro Pace
GFI Software - www.gfi.com
Messaging, Content Security & Network Security Software

(in reply to Idealshopping)
Post #: 4
RE: Splitting SQL Databases Script - 18.Dec.2007 8:36:26 AM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		Sandro,

Thanks for replying.  So, after I create each one of the databases for each months, do I have to add them to MARC5 and go through their settings so the tables can be created?

I'm assuming that using the script is faster than the method GFI recommends, right?
http://ftp.gfisoftware.com/support/GFI_MailArchiver_4-Moving_emails_between_databases.pdf

What other information do I have to change or customize besides the DB destination name?

Does this script actually moves records to the new databases or does it make copies leaving the original DB intact?

(in reply to sandro)
Post #: 5
RE: Splitting SQL Databases Script - 20.Dec.2007 5:48:47 AM   
Nicks

 

Posts: 2769
Joined: 17.Mar.2003
Status: offline 		Hi braishfield,

Using the script is probably faster, however it is not supported by GFI.

The script is designed to delete the entries from the original database.

_____________________________

Nicholas Sciberras
GFI Software
Blog-Twitter-YouTube-Facebook

(in reply to braishfield)
Post #: 6
RE: Splitting SQL Databases Script - 20.Dec.2007 4:30:44 PM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		I tried to run the script and I got the following error:

(0 row(s) affected)
Msg 2627, Level 14, State 1, Line 38
Violation of PRIMARY KEY constraint 'PK_arc_users'. Cannot insert duplicate key in object 'dbo.arc_users'.
The statement has been terminated.


(in reply to Nicks)
Post #: 7
RE: Splitting SQL Databases Script - 24.Dec.2007 8:30:40 AM   
Rodion

 

Posts: 834
Joined: 15.Nov.2004
Status: offline 		Hi

Try to change the arc_users part of the script to:

INSERT INTO [DestinationDatabaseName].[dbo].[arc_users]([usrid], [name], [hash])
select [usrid], [name], [hash]
from arc_users
where usrid > 0

_____________________________

Best regards

Rodion Denisyuk
GFI Software Ltd

(in reply to braishfield)
Post #: 8
RE: Splitting SQL Databases Script - 26.Dec.2007 9:14:13 AM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		Rodion,

I changed "where usrid <> 0" to "where usrid > 0" and I got a Successful, (65 row(s) affected).  I'm not sure what was affected and nothing else happened after that.  One thing I did not do was to uncomment neither one of these:

--Make sure you have backed up your database before un-commenting this section!!!!!
/*
delete from arc_sources where id in (select id from @tbl)
delete from arc_bodies where id in (select id from @tbl)
delete from arc_att where id in (select id from @tbl)
delete from arc_add where id in (select id from @tbl)
delete from arc_owners where id in (select id from @tbl)
delete from arc where id in (select id from @tbl)
 
delete from arc_threads
where NOT EXISTS(select id from arc where thread_id=arc_threads.thread_id)
 
*/
 
 
--Script to clear out a database in case you need to start again
--Make sure you have backed up your database before un-commenting this section!!!!!
/*
use [DatabaseName]
 
delete from arc_sources
delete from arc_owners
delete from arc_users where usrid<>0
delete from arc_add
delete from arc_bodies
delete from arc_att
delete from arc
delete from arc_threads
 
*/
It seems like the only table that was affected was the arc_users table.  When I compare the arc_users tables (old db and destination db), I noticed that the old db has data in the following columns: ursid, name, hash, username and the new db only has data on the usrid, name, hash column.

http://www.4shared.com/file/33047835/d39ef786/arc_users.html?

Can you or someone explain step by step what I have to do to run the script and make it work.  I tried using GFI method which is by using the bulk export tool, but it times out every time run it.  I have over 500,000 items to move and they recommend doing no more than 5,000 at a time.

Thanks,

JD

(in reply to Rodion)
Post #: 9
RE: Splitting SQL Databases Script - 26.Dec.2007 10:43:07 AM   
Rodion

 

Posts: 834
Joined: 15.Nov.2004
Status: offline 		Hi

Please note that this script was neither created nor verified by GFI.

It looks like it was created for MAR3 and does not include certain fields. Change the arc_users section as following.

INSERT INTO [DestinationDatabaseName].[dbo].[arc_users]([usrid], [name], [hash],[username])
select [usrid], [name], [hash], [username]
from arc_users
where usrid>0

Similarly there are fields missing in the arc_att part

INSERT INTO [DestinationDatabaseName].[dbo].[arc_att]([id], [filename], [attsize], [attid], contentId, contentLocation)
select [id], [filename], [attsize], [attid], contentId, contentLocation
from arc_att
where id in (select id from @tbl)


Most probably you forgot to alter the range selection part according to your needs. The sample will only move emails sent in April 2006

--get all ids for a date range
insert into @tbl
 select id from arc where month([archdate])=4 and year([archdate])=2006

< Message edited by Rodion -- 26.Dec.2007 10:47:37 AM >

_____________________________

Best regards

Rodion Denisyuk
GFI Software Ltd

(in reply to braishfield)
Post #: 10
RE: Splitting SQL Databases Script - 26.Dec.2007 3:44:42 PM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		I noticed that MARC5 databases have 15 tables: arc, arc_add, arc_att, arc_bodies, arc_delete, arc_owners, arc_sources, arc_tags, arc_tags_msg, arc_tags_msg_updates, arc_threads, arc_tx, arc_users, gfi_marc_dle_db_version

Am I supposed to have the same tables on my script and follow the same format?


--arc att

INSERT INTO [MAJanuary2006].[dbo].[arc_att]([id], [filename], [attsize], [attid], contentId, contentLocation)

select [id], [filename], [attsize], [attid], contentId, contentLocation

from arc_att

where id in (select id from @tbl)

--arc add

INSERT INTO [MAJanuary2006].[dbo].[arc_add]([id], [email], [type], [name])

select [id], [email], [type], [name]

from arc_add

where id in (select id from @tbl)

--arc owners

INSERT INTO [MAJanuary2006].[dbo].[arc_owners]([id], [usrid], [primary])

select [id], [usrid], [primary]

from arc_owners

where id in (select id from @tbl)
 
etc.
 

 

(in reply to Rodion)
Post #: 11
RE: Splitting SQL Databases Script - 27.Dec.2007 3:19:21 AM   
Rodion

 

Posts: 834
Joined: 15.Nov.2004
Status: offline 		Right.
However there is no need to replicate arc_delete, arc_tx and gfi_marc_dle_db_version tables. Also timestamp fields shall not be copied.

arc_tags* tables are new to MAR5 and hold assigned labels. Replicate them if needed

_____________________________

Best regards

Rodion Denisyuk
GFI Software Ltd

(in reply to braishfield)
Post #: 12
RE: Splitting SQL Databases Script - 27.Dec.2007 8:56:56 AM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		Rodion,

First, let me take a moment to let you know that I really appreciate your help.  I understand that the script is not a GFI script, but it seems to be the only way for me to be able to split my DBs. 

Can you explain the IDENTITY_INSERT? Why only to table has it and do I need it for all tables or what table should I use it with?
 
Also, do I need this?
 
--tidy up afterwards.
--Make sure you have backed up your database before un-commenting this section!!!!!
/*
delete from arc_sources where id in (select id from @tbl)
delete from arc_bodies where id in (select id from @tbl)
delete from arc_att where id in (select id from @tbl)
delete from arc_add where id in (select id from @tbl)
delete from arc_owners where id in (select id from @tbl)
delete from arc where id in (select id from @tbl)
 
delete from arc_threads
where NOT EXISTS(select id from arc where thread_id=arc_threads.thread_id)
 
*/
 
 
--Script to clear out a database in case you need to start again
--Make sure you have backed up your database before un-commenting this section!!!!!
/*
use [DatabaseName]
 
delete from arc_sources
delete from arc_owners
delete from arc_users where usrid<>0
delete from arc_add
delete from arc_bodies
delete from arc_att
delete from arc
delete from arc_threads
 
*/
 
Or can I just delete the old DBs once I know that I have everything that I need.  I have a backup of the 2006 DB that I need to split.

Thanks again!

(in reply to Rodion)
Post #: 13
RE: Splitting SQL Databases Script - 28.Dec.2007 2:40:32 AM   
Rodion

 

Posts: 834
Joined: 15.Nov.2004
Status: offline 		Hi

IDENTITY_INSERT is used to manually update auto increment fields. It's not always required.

The original script was designed in a way to move (copy and then delete) certain emails from an original database to another one. So it's not exactly a "split" because you keep the original database but it will contain less data. But it's up to you how to use this script

_____________________________

Best regards

Rodion Denisyuk
GFI Software Ltd

(in reply to braishfield)
Post #: 14
RE: Splitting SQL Databases Script - 28.Dec.2007 8:18:41 AM   
braishfield

 

Posts: 9
Joined: 16.Jul.2007
Status: offline 		This is what I have so far, what modifications do I have to do to the script in order to be able to copy the emails from one DB the other?  I would rather not to delete anything from the original DB.  I have enought storage to copy the contents of the 2006 DB to 12 DBs and then delete the 2006 DB.


--Script to move data between GFI mail archive databases
--Always back up databases before running any scripts

declare @tbl table
(id int)
--get all ids for a date range
insert into @tbl
select id from arc where month([archdate])=1 and year([archdate])=2006
--Arc Threads

INSERT INTO [MAJanuary2006].[dbo].[arc_threads]([thread_id], [topic])
select thread_id,topic from arc_threads
where thread_id in (select thread_id from arc where arc.id in (select id from @tbl))


--Arc
set IDENTITY_INSERT [MAJanuary2006].[dbo].arc ON
insert into [MAJanuary2006].[dbo].arc
([id], [thread_id], [parent_id], [thread_index], [date], [priority], [size], [full_subject], [attach_count], [encrypted], [signed], [recipients], [archdate], [message_id])
select [id], [thread_id], [parent_id], [thread_index], [date], [priority], [size], [full_subject], [attach_count], [encrypted], [signed], [recipients], [archdate], [message_id]
from arc
where id in (select id from @tbl)

set IDENTITY_INSERT [MAJanuary2006].[dbo].arc OFF

--arc users , I just copy over all users, its easier.
--usrid of 0 is created when the database is added through the GFI Interface
set IDENTITY_INSERT [MAJanuary2006].[dbo].arc_users ON
INSERT INTO [MAJanuary2006].[dbo].[arc_users]([usrid], [name], [hash],[username])
select [usrid], [name], [hash],[username]
from arc_users
where usrid > 0

set IDENTITY_INSERT [MAJanuary2006].[dbo].arc_users OFF

--Arc Sources
INSERT INTO [MAJanuary2006].[dbo].[arc_sources]([id], [data], [compressed], [signature])
select [id], [data], [compressed], [signature]
from arc_sources
where id in (select id from @tbl)

--arc bodies
INSERT INTO [MAJanuary2006].[dbo].[arc_bodies]([id], [texttype], [charset], [ts], [body])
select [id], [texttype], [charset], [ts], [body]
from arc_bodies
where id in (select id from @tbl)

--arc att
INSERT INTO [MAJanuary2006].[dbo].[arc_att]([id], [filename], [attsize], [attid], [contentId], [contentLocation])
select [id], [filename], [attsize], [attid], [contentId], [contentLocation]
from arc_att
where id in (select id from @tbl)
--arc add
INSERT INTO [MAJanuary2006].[dbo].[arc_add]([id], [email], [type], [name])
select [id], [email], [type], [name]
from arc_add
where id in (select id from @tbl)

--arc owners
INSERT INTO [MAJanuary2006].[dbo].[arc_owners]([id], [usrid], [primary])
select [id], [usrid], [primary]
from arc_owners
where id in (select id from @tbl)


--arc retention
INSERT INTO [MAJanuary2006].[dbo].[arc_retention]([id], [ruleid])
select [id], [ruleid]
from arc_retention
where id in (select id from @tbl)


--arc tags
INSERT INTO [MAJanuary2006].[dbo].[arc_tags]([tagid], [usrid], [tagtype], [tagname], [tagvalue])
select [tagid], [usrid], [tagtype], [tagname], [tagvalue]
from arc_tags
where id in (select id from @tbl)

--arc_tags_msg
INSERT INTO [MAJanuary2006].[dbo].[arc_tags_msg]([tagid], [msgid])
select [tagid], [msgid]
from arc_tags_msg
where id in (select id from @tbl)

--arc_tags_msg_update
INSERT INTO [MAJanuary2006].[dbo].[arc_tags_msg_update]([msgid])
select [msgid]
from arc_tags_msg_update
where id in (select id from @tbl)



(in reply to Rodion)
Post #: 15
Page:   [1] 2   next >   >>
All Forums >> [Archiving & Fax] >> GFI MailArchiver >> Splitting SQL Databases Script Page: [1] 2   next >   >>
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts