Manage huge amount of data

Requirement:
1 billion records to be inserted every day
180 days data to be maintained
180 * 1 billion records = approx no of records = defines the size of the
database
Need to design the database/process to maintain so huge data?
- RHi Rakesh,
Need more info like an idea of the number of tables, size of rows, querying
which will reflect what indexes are required etc...
You will need some good kit though, lots of disks; what sort of fault
tolerance are you looking at because backups are going to be a problem.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R|||> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
With an average row size of 100 bytes, this calculates to about 18 TB of
usable space not including index overhead. You'll also need to sustain a
rate of over 10 thousand inserts per second 24x7.
Very large tables are often partitioned for manageability reasons and to
address backup issues like Tony mentioned. Attention to detail is very
important. Unless you have experience working with very large databases, I
suggest you engage consultants with VLDB experience to help you out. We can
help you with specific questions but a project of such magnitude requires
dedicated resources with specialized experience.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R

Major problem with @@IDENTITY, Treeview and GUID

Hi,

I'm trying to insert data into locally stored database (SQL Server).
The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset.
The treeview contains checkboxes allowing a user to select what to install in the locally stored database.

To sum up:

1. Get data from a webservice' not my problem
2. Present data in a Treview control' not my problem
3. Allow to user to select which data to install' not my problem
4. Insert data that the user has selected into my db' MY PROBLEM!!!!

The Treeview looks like this.

- Group1
| | -- Rule1.1
| | -- Rule1.2
|
- Group2
| | -- Rule2.1
| | -- Rule2.2
| | -- Rule2.3
....

The Treeview is generated with DataRelations between Group and Rule.

My locally stored database is designed by a third party provider and therefore the database must not be altered.
The table I want to store data in is called "Groups" and it looks like this:

GroupID uniqueidentifier ' (newid())
GroupName nvarchar(50)
ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value.
GroupType tinyint ' 0 = subgroup, 1 = "top"group
Data in the table "Groups" would look like this:
GroupID GroupNameParentGroupIDGroupType
------------------
{000001...}Group1<NULL>1
{000011...}Rule1.1{000001...}0
{000012...}Rule1.2{000001...}0
{000002...}Group2<NULL>1
{000021...}Rule2.1{000002...}0
{000022...}Rule2.2{000002...}0
{000023...}Rule2.3{000002...}0

The third party also created a stored procedure called pr_AddGroup taking the following parameters:

@.GroupName ' can be both the RuleName and the GroupName
@.GroupType ' can be 0 for subgroup or 1 for "top"group
@.ParentGroup ' GUID

The problem with this stored procedure is that it does not have return value, which is here my problem actually lies.
If it returned @.@.IDENTITY I could use this as the parameter for @.ParentGroup.
Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @.@.IDENTITY to get the newly created record).

My SQL Commands look like this

Dim cmd As SqlCommand
Dim Conn As SqlConnection = New SqlConnection
Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI"
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = Conn
cmd.CommandText = "pr_AddGroup"
cmd.Parameters.Add(New SqlParameter("@.GroupName", SqlDbType.NVarChar, 50, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.GroupType", SqlDbType.TinyInt, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.ParentGroup", SqlDbType.UniqueIdentifier, ParameterDirection.Input))
dim cmd2 as SqlCommand
cmd2 = new SqlCommand
cmd2.commandtype = commandtype.Text
cmd2.commandtext = "SELECT @.@.IDENTITY as ID FROM Groups"
cmd2.connection = Conn
dim ParentGroupGUID as system.guid

To get the data inserted in the Groups table I would something like the following, but the code is very ugly
(and it doesn't work either);

 For Each Group In TreeView1.Nodes ' Loop through Groups
 If Group.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.ExecuteNonQuery()
 ParentGroupGUID = cmd2.executescalar()
 End If
 For Each Rule In Group.Nodes ' Loop through Rules.
 If Rule.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.Parameters("@.ParentGroup").value = ParentGroupGUID
 cmd.ExecuteNonquery()
 End If
 Next
 Next

I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)I'd recommend getting the stored proc changes because the time gap between running the stored proc and the select stmt can return a wrong ID for multiple users. So to main integrity of the stored proc and the ID its best to add the select stmt inside the stored proc itself. Also use SCOPE_IDENTITY() instead of @.@.IDENTITY.

hth|||Hi ndinakar,

Thanks for replying - but what do mean by:
"I'd recommend getting the stored proc changes"

I'm not allowed to change the stored proc because it's provided by a third party and if I modify the stored proc, the third party application is likely to break (and I don't wanna risk this).

??|||I meant the change be made in the stored proc it self to get accurate results. Even if you make a call to the table to return the latest Id there is no guratantee you would get the id for the isnert you did. if there are 5 ppl doing the insert at the same time the id's returned could get mixed up. so if you need to get the accurate id that was generated due to your insert..do the select immediately after the insert IN the stored proc itself. you need to xplain to the third party people and sort it out with them. else take the risk of not getting the right value.

hth