database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techin...scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger
1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon
|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinf...calability.asp
GAJ
|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it
|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)
|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ
|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegro ups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.
> Any input is greatly appreciated.
> Thanks
> Roger
>

Database security on a Local Network

This is regarding general protection of a database hosted on a network. I am developing a database application for my college library using VB.NET, that will reside on a network.
For some reasons, I did not want to hardcode the Database location in the application. Instead, when a user logs in, he can choose the database location using a folder browser control, if the location has changed.

Now, I realize that for this, I have to put the database in a shared folder, which makes it quite vulnerable. Having pondered over the problem for sometime, a solution that comes to my mind is to place a Text file in the same shared folder that always contains the correct path of the database. When a user chooses that folder, I will read the actual path of the database from the text file, and move the database to a non-shared folder.
I haven't yet implemented this approach, but felt it better to consult someone before. So, would this approach work, and is it a good idea.
For information purposes, I consider it important to mention that the database is in MS Access. I know this is not a place for discussing it, but this is a general security concern. So, I thought
people would not mind answering it....

Hi,

how aout securing the MDB file using the appropiate NTFS permissions and eventually additional Access password security or using an ldb file ? I don′t know if there can be concurrent users on the database, but coyping the database file to a shared folder will allow other users also to copy the file to another folder and working on it, for you having the trouble to bring the data together afterwards.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||More than the problem of bringing it together afterwards, I am worried about someone manipulating it mischievously. That's why I thought of putting the database in a non-shared folder on the server and a text file in a shared folder, which will always

contain the correct path of the DB on the server.

So, when a user logs in, he will select the path of the text file. As he

will do so with a folder chooser, he will not know that the folder contains a

text file & not the actual DB. Internally, I will read the database path from

that file in my application, & use that path to construct the connection

string...

I think this approach will shield the database from direct access on the network, using an explorer etc.

I already have Access password security, but still I dont want the database to be directly accessible on the network.
Can you elaborate a bit more on securing the Database on the server with NTFS permissions, in a way that my application can still access & manipulate it?|||

One thing, you cannot perform such move/copy with a SQL Server database as that will be in exclusive use of SQLengine.

Refer to KBA http://support.microsoft.com/kb/295234, http://support.microsoft.com/kb/307901 and link http://vb123.com/toolshed/links/map/opr.htm for more information.

|||There are two things I will mention again here...
1) My database is in Access
2) And, I am not moving the Database at run-time. The database will remain in its non-shared folder. And there will be a text file, that will act as a sort of pointer to the database location for my application, as I will read the DB path from the text file.|||

I suggest posting this question on a Microsoft Access or Microsoft Visual Basic forum instead of this one. This forum is used for posting questions related to Microsoft SQL Server security features, as you observed, and your question is Access specific.

Thanks
Laurentiu

|||

Hi,

You can do this well with NTFS permission with Read right for everyone in Group so that everyone can read the database files (this will not make user to able to copy files/folder too) , and give write/modify permission to specific users who need to insert/update/delete records in your access database. Refer www.windowsecurity.com/articles/Understanding-Windows-NTFS-Permissions.html to understand NTFS permission properly and with Advance you may restrict Take Ownership/traversing etc.

HTH

Hemantgiri S. Goswami

database recommendations....

I have been asked to look at a database and provide
ecommendations regarding architecture, performance, backup
and recovery, and support. The database owners need dba
support for their product. Can anyone please tell me what
type of questions to ask them? Thank you
Josea
First you should ask yourself "Am I the right person for this job?"
You did not put anything in your question to indicate that you have any
experience with SQL Server or that you have any idea where to start your
investigation. If you do not know what questions to ask or how to interpret
the answers to those questions you might not help the company and you could
cause them harm by giving them the "green light" on a database and/or
product that is not ready to ship.
Keith
"Josea Ating" <anonymous@.discussions.microsoft.com> wrote in message
news:3ae501c4ab16$b593d090$a301280a@.phx.gbl...
> I have been asked to look at a database and provide
> ecommendations regarding architecture, performance, backup
> and recovery, and support. The database owners need dba
> support for their product. Can anyone please tell me what
> type of questions to ask them? Thank you
> Josea
|||I'll second what Keith has said and just add that an online forum really
isn't the place to get advice on such a big set of questions.
Maybe you could suggest that they hire a database specialist on a short-term
contract to make some recommendations.
David Portas
SQL Server MVP

database recommendations....

I have been asked to look at a database and provide
ecommendations regarding architecture, performance, backup
and recovery, and support. The database owners need dba
support for their product. Can anyone please tell me what
type of questions to ask them? Thank you
JoseaFirst you should ask yourself "Am I the right person for this job?"
You did not put anything in your question to indicate that you have any
experience with SQL Server or that you have any idea where to start your
investigation. If you do not know what questions to ask or how to interpret
the answers to those questions you might not help the company and you could
cause them harm by giving them the "green light" on a database and/or
product that is not ready to ship.
--
Keith
"Josea Ating" <anonymous@.discussions.microsoft.com> wrote in message
news:3ae501c4ab16$b593d090$a301280a@.phx.gbl...
> I have been asked to look at a database and provide
> ecommendations regarding architecture, performance, backup
> and recovery, and support. The database owners need dba
> support for their product. Can anyone please tell me what
> type of questions to ask them? Thank you
> Josea|||I'll second what Keith has said and just add that an online forum really
isn't the place to get advice on such a big set of questions.
Maybe you could suggest that they hire a database specialist on a short-term
contract to make some recommendations.
--
David Portas
SQL Server MVP
--

Database Page Counter

Hi All,

I need some comments regarding my Hit Counter that I have created for my sites. Here is my case:

I have developed several websites which I host along with MS SQL server. I created a class that calls a stored procedure to add a count to the database field. The way that I am using it is that every page in the page_load event will call the class indicating which counter to use (i.e. LoginPage, HomePage, ProductPage, etc.). The class in turn will perform the database route to increment the counter. In the Store procedure before incrementing the counter, I first check to see if a record for the current date has been created, if not I add the record with a date stamp with all the different counter values as zero. I then will add 1 to the desired counter for the date.

Is this a good solution?

What I now want to do is create a page that allows the different website owners to access a page with the relevant page counters.

Any ideas will be greatly appreciated.

ThanksIt's hard to offer opinions without seeing the table structure(s) and data.

One of the fields in your table(s) should be something like "CustomerCode". This will enable you to use a WHERE clause to filter the counters appropriate to each customer.

Terri|||I don't think it's a bad way, but what I would do is just create a monthly SQL job to create the records for the next month or so. Currently, you'd only add a record one time per day, but you'd check for its existence perhaps thousands of times throughout the day. Kind of a waste. That way, the record is always there and you're just doing an update for each call rather than a check and then an update.