Dynamic horizontal partitioning

I have a database which requires merge replication which can be horizontally
partitioned into 25 branches.
Would this create 25 distribution databases and is there a high impact on
the performance on updating these databases?
Or is the best solution to use is dynamic horizontal partitioning?
Thanks
mm,
there is no need for more than one distribution database. Your options are
to create 25 separate publications and a distinct subscriber for each or (as
you say) you can use dynamic horizontal partitioning. I would favour the
latter as it is less administration, especially if any schema changes are
needed. To implement, you can easily add -HOSTNAME as a merge agent
parameter for each subscriber, and use this to filter.
Regards,
Paul Ibison

Dynamic disk

Does clustering supports dynamic disk mirroring with SAN? As cluster
has single point of failure of disk, we want to mirror the databases.
Any ideas would be appreciated.
Thanks
Clustering does not support dynamic disks.
http://support.microsoft.com/default...b;EN-US;284134
http://support.microsoft.com/default...b;en-us;237853
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"tram" <tram_e@.hotmail.com> wrote in message
news:1108518044.708003.277010@.z14g2000cwz.googlegr oups.com...
> Does clustering supports dynamic disk mirroring with SAN? As cluster
> has single point of failure of disk, we want to mirror the databases.
> Any ideas would be appreciated.
> Thanks
>
|||As Rodney pointed out, Clustering does not support Windows Dynamic Disks.
SAN vendors may provide some LUN mirroring capabilities, but that is outside
the scope of SQL server.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"tram" <tram_e@.hotmail.com> wrote in message
news:1108518044.708003.277010@.z14g2000cwz.googlegr oups.com...
> Does clustering supports dynamic disk mirroring with SAN? As cluster
> has single point of failure of disk, we want to mirror the databases.
> Any ideas would be appreciated.
> Thanks
>

Dynamic database with MS ACCESS

Hi,
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>

Dynamic database creation

Hi All,

I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed

However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.

I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).

Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.

Thanks & regards,

Paul

Hi Paul,

As the simplest solution you can run the entire DB creation script through code, for e.g.:

using (SqlConnection cn = new SqlConnection("connectionString from config file"))
{
StringBuilder sql = new StringBuilder();
sql.Append(" use master; create database " + txtDatabase.Text);

SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cn.Open();
cmd.ExecuteNonQuery();
}

Hope this helps,

Vivek

|||

Hi Vivek,

Thanks for your reply. I haven't seen / used sql.Append before, so that definitely could be a way around the problem. I had hoped that there would be a way of running the .sql script, so that if I make any changes to the database layout then I can simply issue a new script, but it wouldm't be the end of the world if I have to issue a new vb file instead. I'll let you know how I get on over the next few days!

Thanks again,

Paul

Dynamic connection to the Database

Hi,
I want to connect to several databases, is it possible to pass the
connection string as a parameter and change connection to the database
dynamically ?
Thanks.Try this
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Of course you can change anything in the connection string... In your case,
you'd provide the catalog instead of the servername...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"yfat" wrote:
> Hi,
> I want to connect to several databases, is it possible to pass the
> connection string as a parameter and change connection to the database
> dynamically ?
> Thanks.|||I believe you want the SetDataSourceContents method. You can find an
explanation of it in BOL.
"yfat" wrote:
> Hi,
> I want to connect to several databases, is it possible to pass the
> connection string as a parameter and change connection to the database
> dynamically ?
> Thanks.|||Ok, I tried to create a dynamic data source with two parameters - the server
name and the database name like this :
="data source=" &Parameters!ServerName.Value & ";initial
catalog=" &Parameters!DBName.Value
but when I try to continue with that data source i get this error about
trying to connect the data base :
"A connection cannot be made to the database. Set and check the connection
string."
sice the connection is a dynamic one, I cannot know at the design time the
user and the password for the data base.
"Wayne Snyder" wrote:
> Try this
> ="data source=" &Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
>
> Of course you can change anything in the connection string... In your case,
> you'd provide the catalog instead of the servername...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "yfat" wrote:
> > Hi,
> > I want to connect to several databases, is it possible to pass the
> > connection string as a parameter and change connection to the database
> > dynamically ?
> >
> > Thanks.|||I just saw that the connection string on Reporting Services version 1.0
cannot be based on expressions, Is it possible on the next version ?
"yfat" wrote:
> Ok, I tried to create a dynamic data source with two parameters - the server
> name and the database name like this :
> ="data source=" &Parameters!ServerName.Value & ";initial
> catalog=" &Parameters!DBName.Value
> but when I try to continue with that data source i get this error about
> trying to connect the data base :
> "A connection cannot be made to the database. Set and check the connection
> string."
> sice the connection is a dynamic one, I cannot know at the design time the
> user and the password for the data base.
> "Wayne Snyder" wrote:
> > Try this
> >
> > ="data source=" &Parameters!ServerName.Value & ";initial
> > catalog=AdventureWorks
> >
> >
> > Of course you can change anything in the connection string... In your case,
> > you'd provide the catalog instead of the servername...
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "yfat" wrote:
> >
> > > Hi,
> > > I want to connect to several databases, is it possible to pass the
> > > connection string as a parameter and change connection to the database
> > > dynamically ?
> > >
> > > Thanks.

Dynamic connection to sql database via Access 2k7 project

I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:

>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.

Dynamic connection to sql database via Access 2k7 project

I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:

>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.