Database Schema Designing

Case 1:
A company is involved into e-commerce..hosting multiple websites for different products.

CAse 2:
The above scenario could also be implemented with a single site having multiple products for sale.

For Case 2 one would go for a single database for all the products.
While for CAse 1 ,a separate Database is developed for each Site.

What I fill is CAse2 is a more appropriate choice even if we have multiple sites for different products.

This would help us in rapid development of any ecommerce site...
ANd better ERP management for the Company.

I would appreciate some expert guidelines for the above scenario

Thanx in Advance
Warm Regards
GirijaUnless there is something to do with IIS (or whatever web server you are using) that would benefit from different databases, I would have all the data in one database, and have more than web site access the database.

Having all of your data in one database would allow provide much better information (not just straight forward data entry, but the trends you can find through relations, etc.) and make it easier to manage...in my oppinion. However, make sure that if you use one database (and these are high volume web sites) that you are writing your code carefully to avoid unneccessary record locks.

Just my oppinion...

Database Restore with NORECOVERY

Hi All,

im trying to set up database mirroring and in the initial phase of database restoring im getting some problem.

the scenario is .

1- i create a tempDB on server1(which will be principal server) and backup full and transaction logs.

2- create tempDB in server 2( going to be used as mirror) .

3- restore the full backup of "tempDB" from server1 with NORECOVERY option.

this step results in a message box showing that the restore was successfull...

but the database explorer( management studio) shows the tempDB in (restoring ...) state. and no more operations can be performed with this Database. ( because teh databse isin restoring mode).. please help what this issue is?

Thanks In Advance for any suggestions

hi amir,

1.you have a db in principal server and it shud be in full recovery mode to configure db mirroring

2.take a full a full backup and restore it with norecovery option in the mirror prior to configuring mirroring

3.take a tran log backup in principal and restore it with norecovery option in the mirror.

then start configuring db mirroring using the wizard............

norecovery option restores the db but it will be in restoring state i.e you can apply tran logs to it but you cant use it and no objects will be visible

normally there are 3 types while performing restoration,

1.Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
2.Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
3.Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

http://msdn2.microsoft.com/en-us/library/ms177429.aspx - refer this link for restoring options.........

refer ,

http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html - for configuring dbmirroring

http://deepakinsql.blogspot.com/2007/07/prerequisites-for-database-mirroring.html - prerequisites for mirroring

revert if you have nay concerns

thanks

|||

Hi Amir,

What Deepak xplained is right.

Follow the steps and if want to see the objects in mirror database just force a failover. The new mirror will be in restoring... state

Its quite confusing, it should have been "dont worry continue..." rather than "restoring..."

Goodluck

Vj

Database Restore with NORECOVERY

Hi All,

im trying to set up database mirroring and in the initial phase of database restoring im getting some problem.

the scenario is .

1- i create a tempDB on server1(which will be principal server) and backup full and transaction logs.

2- create tempDB in server 2( going to be used as mirror) .

3- restore the full backup of "tempDB" from server1 with NORECOVERY option.

this step results in a message box showing that the restore was successfull...

but the database explorer( management studio) shows the tempDB in (restoring ...) state. and no more operations can be performed with this Database. ( because teh databse isin restoring mode).. please help what this issue is?

Thanks In Advance for any suggestions

hi amir,

1.you have a db in principal server and it shud be in full recovery mode to configure db mirroring

2.take a full a full backup and restore it with norecovery option in the mirror prior to configuring mirroring

3.take a tran log backup in principal and restore it with norecovery option in the mirror.

then start configuring db mirroring using the wizard............

norecovery option restores the db but it will be in restoring state i.e you can apply tran logs to it but you cant use it and no objects will be visible

normally there are 3 types while performing restoration,

1.Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
2.Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
3.Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

http://msdn2.microsoft.com/en-us/library/ms177429.aspx - refer this link for restoring options.........

refer ,

http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html - for configuring dbmirroring

http://deepakinsql.blogspot.com/2007/07/prerequisites-for-database-mirroring.html - prerequisites for mirroring

revert if you have nay concerns

thanks

|||

Hi Amir,

What Deepak xplained is right.

Follow the steps and if want to see the objects in mirror database just force a failover. The new mirror will be in restoring... state

Its quite confusing, it should have been "dont worry continue..." rather than "restoring..."

Goodluck

Vj

database repair without data loss?

Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.

Database Partitioning - Partition switching

Hi,

I need replies immediately...

This is with regard to switching of partitions in a sliding window scenario.

I wrote a stored procedure which does the partition switching in a sliding window scenario. During one switching, if the stored procedure is half executed, if I re-execute the same procedure it throws errors like

'Filegroup is in a different partition switching failed'

So how to rollback all the transactions in the stored procedure that does switching of partitions? If it is not possible, then how to identify whether a file group is in its original partition before the stored procedure is part executed? How to bring it back to its original partition before executing the stored procedure again switching?

Can any one help me immediately...

Thanks

Sekharpc

Moving thread to appropriate forum.|||

hi sek,

you have to enclose your sp scripts in a begin transaction.... end transaction clause..

create sp swtichpartion

(

)

as

begin transaction

partition switch... code1

partition switch code 2.

partition switch code 3.

commit transaction

in this way all switch task must commit as a unit. a failure in one job

fails the entire operation.

for a more comprehensive solution you can also incorporate the 2005

error handling feature try.. catch block for more information

you can consult my blogs look for the topic

The SQL SERVER 2005 Try Catch Block look for sub topic

"try catch with transaction"

or BOL

regards,

joey