Anyone know table limits in multi-schema environment?

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!

any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?

thanks

-c

You definately can get over 1000 tables, since certain complex ERP systems and such have that just on their own, and that was even in older version of SQL Server.

According to :http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

The amount of tables is actualy only limited by the amount of objects (which means all triggers, tables, stored procedures, etc count toward this limit). The limit is... 2,147,483,647

Good luck busting that. Oh, and thats SQL Server 2000, was too lazy to find the 2005 specs :)|||

Sounds responable to me. I was going off this article from MS that suggested no more than 100 tables per client schema in a single db but they don't specify why :)

http://msdn2.microsoft.com/en-us/library/aa479086.aspx

Any idea how to do updates to the schema? my current thinking is to get my app to login as each schema owner and execute the update script.

thanks

-c

Any way logging or tracking method for dropped tables

Most of my tables is dropped after they are created. I cannot tell when this
happen. This include stored procedures as well. I checked the maintenance job
and none are dropping tables or stored procedures.
The funny part is my tables and stored procedure which is published out as
articles remain and not dropped as others. Why?
Is there any logs or tracking that can be put in place to find out who
deleted my tables?
Paul,
I'm using SQL Server 2000 so I guess I would need to use the log explorer
tool. Are there any recommendation which I can try first?
Another question which hopefully can narrow down my suspect here is..Do you
think replication dropped the tables?
Cheers,
Philip
"Paul Ibison" wrote:

> If you're on SQL Server 2005 you could set up ddl triggers to log who/what
> is performing the drops. Alternatively a 3rd party log explorer tool will
> have the info in it.
> HTH,
> Paul Ibison
>
>
|||Replication by default will drop tables on the subscriber, but never on the
publisher so i'd look elsewhere. The reason replicated objects haven't been
dropped is probably because once they're published, they have to be removed
from the publication to be allowed to be dropped, so that distinguishes them
from your other objects. (this is a bit of guesswork, but it makes sense).
Lumigent Log Explorer will audit DDL commands to help you figure out who did
the drop (http://lumigent.com/products/le_sql_faq.html#_I_do_not). If it
happens regularly then you could use profiler yourself to monitor for these
drops.
HTH,
Paul Ibison

Any scripts that will create maintenance plans in SQL 2005

Hi
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
Thanks
You can connect Object Explorer to Integration Services and Export the maint plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Agent jobs as well...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plans that are on the
> server?
> We need to rebuild the server with a different collation, so the master, msdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to create them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>

Any scripts that will create maintenance plans in SQL 2005

Hi
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
ThanksYou can connect Object Explorer to Integration Services and Export the maint
plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Ag
ent jobs as well...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plan
s that are on the
> server?
> We need to rebuild the server with a different collation, so the master, m
sdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to cr
eate them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>

Any scripts that will create maintenance plans in SQL 2005

Hi
Using SQL Server 2005 SP1
Are ther any way to create scripts that will recreate the maintenance plans
that are on the server?
We need to rebuild the server with a different collation, so the master,
msdb and model will get recreated.
Also where are the configurations for database mail kept. No scripts to
create them.
Any place that has scripts that can regenerate the data that one requires?
ThanksYou can connect Object Explorer to Integration Services and Export the maint plan to a file. After
the rebuild, import that file, and just re-create the jobs (or script the Agent jobs as well...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raji Arulambalam" <rajian0spam@.ebop.gov0t.nz> wrote in message
news:O7P4Kd44GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Hi
> Using SQL Server 2005 SP1
> Are ther any way to create scripts that will recreate the maintenance plans that are on the
> server?
> We need to rebuild the server with a different collation, so the master, msdb and model will get
> recreated.
> Also where are the configurations for database mail kept. No scripts to create them.
> Any place that has scripts that can regenerate the data that one requires?
> Thanks
>

Any free sql backup compression utility ?

Hi,
I perform backup by maintenance plan tasks and think about add additional
task to compress backups and delete original backups.
Do you know some script base resolution (winzip, winrar, 7zip...)?
Regards,
anxcomp
From what I understand there is a command line version of winzip. But that
does not give you the same thing as using one of the tools from Red-gate or
Quest. They compress the backup as it is being read and when written it is
already compress. You would have to do it in two steps and it would require
much more CPU & Disk I/O than with Red-Gate. Not to mention you now have to
have extra disk space to store the initial backup before you compress it.
The same is true for restores. It makes the process much longer and more
difficult than it needs to be.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:F6756C86-51F1-4344-85ED-A20ADA7E32F8@.microsoft.com...
> Hi,
> I perform backup by maintenance plan tasks and think about add additional
> task to compress backups and delete original backups.
> Do you know some script base resolution (winzip, winrar, 7zip...)?
> --
> Regards,
> anxcomp
|||Hello,
This is good for me, I don't want live compression and decompression. My
databases are on simple mode I perform only full nightly backup.
Do you use some scripts (vbs, sql with xp_cmdshell or something...) to
perform this task?
Thank you inadvice
Regards,
anxcomp
|||What does the fact this is in simple mode have to do with this? In any case
have a look here:
http://www.winzip.com/prodpagecl.htm
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:6195075F-3BB9-4D62-8088-A107309DC75E@.microsoft.com...
> Hello,
> This is good for me, I don't want live compression and decompression. My
> databases are on simple mode I perform only full nightly backup.
> Do you use some scripts (vbs, sql with xp_cmdshell or something...) to
> perform this task?
> Thank you inadvice
> --
> Regards,
> anxcomp