I need some help to determine what the best solution would be for the
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xml
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seems
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?
Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?
Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts
Tuesday, March 27, 2012
Best solution: SQLXML, XML Data Binding, or MSXML?
I need some help to determine what the best solution would be for the
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xm
l
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seem
s
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?
following scenarios where the dataset is large and performance is key:
Scenario #1
I am trying to retrieve relational data in XML from a SQL Server 2000 stored
proc. My client is a VBA Access application. I want to be able to map the xm
l
elements to db tables/columns and use a stored proc to collect the data and
build the xml. Validate it against the xsd. I am assuming that with large
datasets it would be faster to create the xml on the server than on the
client - desktop pcs - since I would have to loop through data rows to
create the xml using MSXML on the client, thoughts. What about annotated
schemas. Do you have to use along with XPath queries even if your result is
already filtered in an SQL sproc.
Scenario #2
I have a .Net webservice that will be the recepient of this xml and validate
it and persisit it to SQL 2K. After looking at all available options it seem
s
to me that using XML Data Binding & creating .Net classes based on the xsd &
loading this .Net object from xml is the cleanest solution. Reading into
SQLXML, it seems as though every method to persist data to SQL Server uses
the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR to
COM. Cant see how that is good. And MSXML is also COM based parser.
Thoughts?Scenario #1: I would look at the FOR XML functionality of SQL Server 2000 if
you want to do it in the database. Annotated schemas do not work with stored
procs. You can look into the client-side FOR XML of SQLXML 3.0 if you want
to run FOR XML over the result of a stored proc.
Re Scenario #2: The SQLXML Bulkload object can be called through the managed
providers (in the latest SP of SQLXML 3.0). You will go through COM interop,
which you don't have to for the data binding. But I think the loading may
still be faster due to the bulkload mechanism. I would suggest doing some
perf tests for your specific data...
Best regards
Michael
"Aazihh" <nowayjose@.newsgroupms.com> wrote in message
news:D56903DB-3984-4EE4-9CE1-5E1CCC9FBEA6@.microsoft.com...
>I need some help to determine what the best solution would be for the
> following scenarios where the dataset is large and performance is key:
> Scenario #1
> I am trying to retrieve relational data in XML from a SQL Server 2000
> stored
> proc. My client is a VBA Access application. I want to be able to map the
> xml
> elements to db tables/columns and use a stored proc to collect the data
> and
> build the xml. Validate it against the xsd. I am assuming that with large
> datasets it would be faster to create the xml on the server than on the
> client - desktop pcs - since I would have to loop through data rows to
> create the xml using MSXML on the client, thoughts. What about annotated
> schemas. Do you have to use along with XPath queries even if your result
> is
> already filtered in an SQL sproc.
> Scenario #2
> I have a .Net webservice that will be the recepient of this xml and
> validate
> it and persisit it to SQL 2K. After looking at all available options it
> seems
> to me that using XML Data Binding & creating .Net classes based on the xsd
> &
> loading this .Net object from xml is the cleanest solution. Reading into
> SQLXML, it seems as though every method to persist data to SQL Server uses
> the COM-based SQLXMLOLEDB provider and my code will have to jump from CLR
> to
> COM. Cant see how that is good. And MSXML is also COM based parser.
> Thoughts?
Best solution, iterate over millions records and call extended sp
Hi,
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql
Best solution, iterate over millions records and call extended
In fact, the functionarity needs to be available on the server. So I will
created a program, maybe C# or C++ program to do the looping and calculate
and put the executible on the server so it can be launched via xp_cmdshell..
.
It should be better than big TSQL cursor?
"JT" wrote:
> If you have a situation that calls for looping through a cursor, then it's
> better to implement the cursor on the client side than on the server. Open
a
> read-only, forward only ADO recordset and Command.Execute the stored
> procedure for each row.
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
>
>In fact, my question is
Transact-SQL script "declare cursor" (fast forward) vs. Client side ADO code
with fast forward server cursor
Which one is better for very large rows?
"nick" wrote:
> In fact, the functionarity needs to be available on the server. So I will
> created a program, maybe C# or C++ program to do the looping and calculate
> and put the executible on the server so it can be launched via xp_cmdshell
..
> It should be better than big TSQL cursor?
> "JT" wrote:
>
created a program, maybe C# or C++ program to do the looping and calculate
and put the executible on the server so it can be launched via xp_cmdshell..
.
It should be better than big TSQL cursor?
"JT" wrote:
> If you have a situation that calls for looping through a cursor, then it's
> better to implement the cursor on the client side than on the server. Open
a
> read-only, forward only ADO recordset and Command.Execute the stored
> procedure for each row.
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
>
>In fact, my question is
Transact-SQL script "declare cursor" (fast forward) vs. Client side ADO code
with fast forward server cursor
Which one is better for very large rows?
"nick" wrote:
> In fact, the functionarity needs to be available on the server. So I will
> created a program, maybe C# or C++ program to do the looping and calculate
> and put the executible on the server so it can be launched via xp_cmdshell
..
> It should be better than big TSQL cursor?
> "JT" wrote:
>
Best solution for this "problem"
Alright, me and mine are planning of creating a big community, ( no futher details possible )
The point is that, we got 1 webserver with php who's reading out a huge database.
That database is beeing updated by some of a 100 users, all these users are updating the db at the same time. so as you can see, what we need is a good and reliable database witch can easily hold up this lot of data.
Wich database type do you guys recommend?
Need to know more before recommending anything, but if you are thinking about SQL Server check this link
http://www.microsoft.com/sql/editions/default.mspx
All the info about various editions of SQL Server and what they can and can not support.
Best solution
Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.
ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?
Terri|||If I understand correctly, can you do several UNIONs and get them in turn?
Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.
What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)
Let say I have a row that consist of the following:
TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null
key column contains the rowID
ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."
Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.
SELECT
table1.key,
table2key.name,
table2key.value,
table1.ele1,
table2ele1.name,
table2ele1.value,
table1.ele2,
table2ele2.name,
table2ele2.value,
table1.ele3,
table2ele3.name,
table2ele3.value,
table1.ele4,
table2ele4.name,
table2ele4.value,
table1.ele5,
table2ele5.name,
table2ele5.value
FROM
table1
LEFT OUTER JOIN
table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
table2 AS table2ele5 ON table1.ele5 = table2ele5.key
Terri|||Thanks so much for all your help Terri!
Sunday, March 25, 2012
Best Replication Type please
Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> Hello,
>> We are implementing a high availablity solution to our
SQL
>> Servers, so if our production server goes down we have a
>> standby server ready to go.
>> So we would like to know the best type of replication,
>> bascially we want to update the stand-by server every 5
>> minutes. We cannot use the log shipping as we backup the
>> transaction log every 10 minutes.
>> Personally I think we should be using snapshot
>> replication, does anyone have any major disagreements
with
>> this ?
>> Thanks
>> Peter
>>
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> >--Original Message--
> >Snapshot replication would give you a complete copy of
> the publication ....
> >Probably some sort of incremental thing might be
> better... perhaps
> >transactional...
> >
> >But be aware-- replication does NOT replicate system
> tables, so
> >permissions, etc will not be replicated..
> >
> >Also, SQL Replication is not very friendly about Schema
> changes ,
> >adding/dropping columns, or changing data types...
> >
> >For these reasons, may people use log shipping.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are implementing a high availablity solution to our
> SQL
> >> Servers, so if our production server goes down we have a
> >> standby server ready to go.
> >>
> >> So we would like to know the best type of replication,
> >> bascially we want to update the stand-by server every 5
> >> minutes. We cannot use the log shipping as we backup the
> >> transaction log every 10 minutes.
> >>
> >> Personally I think we should be using snapshot
> >> replication, does anyone have any major disagreements
> with
> >> this ?
> >>
> >> Thanks
> >> Peter
> >>
> >>
> >
> >
> >.
> >|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
>> Thanks Wayne,
>> I would love to use Log Shipping, but I'm assuming that
it
>> produces the same internal result to the log file as a
log
>> file backup, if that the case then the backup we have in
>> place will not work. This is somewhat of a 'legacy'
>> backup, and one I have been specifically told not to
touch.
>> Again thanks for help and I will look into what you have
>> said.
>> Peter
>> >--Original Message--
>> >Snapshot replication would give you a complete copy of
>> the publication ....
>> >Probably some sort of incremental thing might be
>> better... perhaps
>> >transactional...
>> >
>> >But be aware-- replication does NOT replicate system
>> tables, so
>> >permissions, etc will not be replicated..
>> >
>> >Also, SQL Replication is not very friendly about Schema
>> changes ,
>> >adding/dropping columns, or changing data types...
>> >
>> >For these reasons, may people use log shipping.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are implementing a high availablity solution to
our
>> SQL
>> >> Servers, so if our production server goes down we
have a
>> >> standby server ready to go.
>> >>
>> >> So we would like to know the best type of
replication,
>> >> bascially we want to update the stand-by server
every 5
>> >> minutes. We cannot use the log shipping as we backup
the
>> >> transaction log every 10 minutes.
>> >>
>> >> Personally I think we should be using snapshot
>> >> replication, does anyone have any major disagreements
>> with
>> >> this ?
>> >>
>> >> Thanks
>> >> Peter
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bcp files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option which also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future
Regards
Paul Ibison
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> Hello,
>> We are implementing a high availablity solution to our
SQL
>> Servers, so if our production server goes down we have a
>> standby server ready to go.
>> So we would like to know the best type of replication,
>> bascially we want to update the stand-by server every 5
>> minutes. We cannot use the log shipping as we backup the
>> transaction log every 10 minutes.
>> Personally I think we should be using snapshot
>> replication, does anyone have any major disagreements
with
>> this ?
>> Thanks
>> Peter
>>
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> >--Original Message--
> >Snapshot replication would give you a complete copy of
> the publication ....
> >Probably some sort of incremental thing might be
> better... perhaps
> >transactional...
> >
> >But be aware-- replication does NOT replicate system
> tables, so
> >permissions, etc will not be replicated..
> >
> >Also, SQL Replication is not very friendly about Schema
> changes ,
> >adding/dropping columns, or changing data types...
> >
> >For these reasons, may people use log shipping.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are implementing a high availablity solution to our
> SQL
> >> Servers, so if our production server goes down we have a
> >> standby server ready to go.
> >>
> >> So we would like to know the best type of replication,
> >> bascially we want to update the stand-by server every 5
> >> minutes. We cannot use the log shipping as we backup the
> >> transaction log every 10 minutes.
> >>
> >> Personally I think we should be using snapshot
> >> replication, does anyone have any major disagreements
> with
> >> this ?
> >>
> >> Thanks
> >> Peter
> >>
> >>
> >
> >
> >.
> >|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
>> Thanks Wayne,
>> I would love to use Log Shipping, but I'm assuming that
it
>> produces the same internal result to the log file as a
log
>> file backup, if that the case then the backup we have in
>> place will not work. This is somewhat of a 'legacy'
>> backup, and one I have been specifically told not to
touch.
>> Again thanks for help and I will look into what you have
>> said.
>> Peter
>> >--Original Message--
>> >Snapshot replication would give you a complete copy of
>> the publication ....
>> >Probably some sort of incremental thing might be
>> better... perhaps
>> >transactional...
>> >
>> >But be aware-- replication does NOT replicate system
>> tables, so
>> >permissions, etc will not be replicated..
>> >
>> >Also, SQL Replication is not very friendly about Schema
>> changes ,
>> >adding/dropping columns, or changing data types...
>> >
>> >For these reasons, may people use log shipping.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are implementing a high availablity solution to
our
>> SQL
>> >> Servers, so if our production server goes down we
have a
>> >> standby server ready to go.
>> >>
>> >> So we would like to know the best type of
replication,
>> >> bascially we want to update the stand-by server
every 5
>> >> minutes. We cannot use the log shipping as we backup
the
>> >> transaction log every 10 minutes.
>> >>
>> >> Personally I think we should be using snapshot
>> >> replication, does anyone have any major disagreements
>> with
>> >> this ?
>> >>
>> >> Thanks
>> >> Peter
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bcp files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option which also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future
Regards
Paul Ibison
Best Replication Type please
Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
Peter
Snapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>
|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with
>
>.
>
|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with
|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the
>
>.
>
|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option whic
h also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibison
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
Peter
Snapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>
|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with
>
>.
>
|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with
|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the
>
>.
>
|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option whic
h also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibison
Best Replication Type please
Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with[vbcol=seagreen]
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and
Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a6
01280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
>
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, t
hen transactional with queued updating subscribers may be used in this situa
tion. Snapshot could be used but this depends on the size of your system - c
reating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By d
efault, standard transactional will not take defaults and identity columns (
although the schema can be scripted), so it's easier to use the queued updat
ing subscribers option whic
h also gives the ability to send back to the publisher any changes subsequen
tly made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibisonsql
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
with[vbcol=seagreen]
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and
Practices" web site.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a6
01280a@.phx.gbl...[vbcol=seagreen]
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
>
> the publication ....
> better... perhaps
> tables, so
> changes ,
> Charlotte, NC
> (PASS) and it's
> message
> SQL
> with|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
it[vbcol=seagreen]
log[vbcol=seagreen]
touch.[vbcol=seagreen]
our[vbcol=seagreen]
have a[vbcol=seagreen]
replication,[vbcol=seagreen]
every 5[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, t
hen transactional with queued updating subscribers may be used in this situa
tion. Snapshot could be used but this depends on the size of your system - c
reating and sending over bc
p files of your complete database every 5 mins might not be acceptable. By d
efault, standard transactional will not take defaults and identity columns (
although the schema can be scripted), so it's easier to use the queued updat
ing subscribers option whic
h also gives the ability to send back to the publisher any changes subsequen
tly made to the subscriber, if the publisher becomes online in the future.
Regards,
Paul Ibisonsql
Best Primary Key Solution?
Hi there,
Looking for a bit of help with my problem:
Say i have 3 tables-
tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)
tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)
tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)
A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.
I thought is would be easier to make up a unique key for each client with a script eg
client name: PJ Computers
Unique key Generated: PJCOMP58784
Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.
So my question is what's the best way of accomplishing this?
Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.
"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.
If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.
-PatP
Looking for a bit of help with my problem:
Say i have 3 tables-
tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)
tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)
tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)
A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.
I thought is would be easier to make up a unique key for each client with a script eg
client name: PJ Computers
Unique key Generated: PJCOMP58784
Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.
So my question is what's the best way of accomplishing this?
Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.
"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.
If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.
-PatP
Thursday, March 8, 2012
Best practice configuring Visual Studio Solution for .SDF databases
Hello,
In our company we haven't tamed Visual Studio yet for the part of configuring
the application database. Therefore I was wondering if there is someone that
can give me hints or clues on what the best practice is for configuring Visual
Solution to handle an SDF database (SQLCE 3.0). I have search a bit on the
internet and this forum, but couldn't find a decent how-to or best practices
guide that copes with this specific problem.
As
a matter of facts, i am also curious for guidelines and tips on the best way to
configure and manage SQL Server databases under Visual Studio. I guess (read as
hope
database will have some overlaps somehow.
I've found the Database Project which with to manage (Create/Alter) databases,
but is this also the best way to store versions of the database in an
source repository? Can such a project allow me to 'automagically' have a
correct database (so with tables and data) when i want to deploy or debug my
device application?
I am asking this because now our database designers and software engineers have
to do allot of manual actions to update the application with the most recent
database version. SDF databases are flying around all over the place, and in
order to test a specific version of the application, the related database has
to be copied manually on the device. We are not searching for replication
related solutions, nor adding the SDF itself to the source repository, but because
most of our applications are server-client based, it would be really super cool
if we could somehow couple both database definitions and generation together. My
feeling says me there ought to be some feature embedded somewhere deep into
Visual Studio that we missed that would (partially) simplify and automate this
whole process for us.
Another
related question is if it is possible to couple XSD generated datasets to an
SDF database. Is it possible to update the generated code (or the describing
XSD documents) from an SDF database? i again guess that this somehow should be
possible in oderder to keep both code and data in sync, and i do not like the
alternative to always update the XSD when the SDF architecture changes. Somehow
i cannot find out how to do this. i was initially searching for the other way
around: Use an XSD to generate both the DataSet codewrappers and the database
itself.
Any
information is welcome and many thanks in advance!
Peter
Vrenken
Too bad there is no one that can give more information
regarding these issues. Can I assume that allot of people/companies haven’t got
a decent SDF database (configuration) setup or thought about it?
I would really like to open a dialog about these issues. Anyone
wants to join me?
Greetings from the rainy Netherlands,
Peter Vrenken
|||Do all you developers out there have got a decent database setup or never thought about it yet? I am hoping that VTS will solve some of the riddles for us but until that time i would really like to know how other companies manage their SDF databases.Is there not a single developer (maybe a MVP) that wants to shed some light on it and describe how he does it (or how it should be done)?
Thanks in advance,
Peter Vrenken|||I used to put an .mdb in vss. No reason why this couldn't be done with the .sdf|||Hello and thanks for your response!
I know that as of VS2K5 SP1 the management of .SDF files from within a solution has been greatly enhanced.
You say that you ‘used’ to put an .mdb in VSS. Is this because you found a better solution?
Peter Vrenken|||Peter, there are several questions here and I'll try to help where I can. As I understand your issues you're trying to build SDF databases in a way that can be better managed through developer tools like Visual Studio. At this point VS can help, but not as much as it could. The VS team is working on an updated version of the tools that can address some (but not nearly all) of your issues. The SQL Server Management Studio can also do more to help in this regard. As far as scripting, there is little to no support in any of the tools. I too felt your frustration so I wrote my first EBook to supplement my just completed Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). This is available at WWW.Hitchhikerguides.net. In the book I walk through the process of creating a database using a script reader that I wrote (and provide with the book), with replication and using the APIs. I expect it will help answer many more of your questions.|||"Used to" only in that I no longer use .mdb files.
I moved to MSDE where I kept the create scripts in vss.
I have migrated these to SQL Express (with the scipts in vss) but am now working on a new application with CE. Unfortunately the scripting that was created from SQL Server Management Tool does not work with CE so I am planning on using vss.
Best practice configuring Visual Studio Solution for .SDF databases
Hello,
In our company we haven't tamed Visual Studio yet for the part of configuring
the application database. Therefore I was wondering if there is someone that
can give me hints or clues on what the best practice is for configuring Visual
Solution to handle an SDF database (SQLCE 3.0). I have search a bit on the
internet and this forum, but couldn't find a decent how-to or best practices
guide that copes with this specific problem.
As
a matter of facts, i am also curious for guidelines and tips on the best way to
configure and manage SQL Server databases under Visual Studio. I guess (read as
hope
database will have some overlaps somehow.
I've found the Database Project which with to manage (Create/Alter) databases,
but is this also the best way to store versions of the database in an
source repository? Can such a project allow me to 'automagically' have a
correct database (so with tables and data) when i want to deploy or debug my
device application?
I am asking this because now our database designers and software engineers have
to do allot of manual actions to update the application with the most recent
database version. SDF databases are flying around all over the place, and in
order to test a specific version of the application, the related database has
to be copied manually on the device. We are not searching for replication
related solutions, nor adding the SDF itself to the source repository, but because
most of our applications are server-client based, it would be really super cool
if we could somehow couple both database definitions and generation together. My
feeling says me there ought to be some feature embedded somewhere deep into
Visual Studio that we missed that would (partially) simplify and automate this
whole process for us.
Another
related question is if it is possible to couple XSD generated datasets to an
SDF database. Is it possible to update the generated code (or the describing
XSD documents) from an SDF database? i again guess that this somehow should be
possible in oderder to keep both code and data in sync, and i do not like the
alternative to always update the XSD when the SDF architecture changes. Somehow
i cannot find out how to do this. i was initially searching for the other way
around: Use an XSD to generate both the DataSet codewrappers and the database
itself.
Any
information is welcome and many thanks in advance!
Peter
Vrenken
Too bad there is no one that can give more information
regarding these issues. Can I assume that allot of people/companies haven’t got
a decent SDF database (configuration) setup or thought about it?
I would really like to open a dialog about these issues. Anyone
wants to join me?
Greetings from the rainy Netherlands,
Peter Vrenken
|||Do all you developers out there have got a decent database setup or never thought about it yet? I am hoping that VTS will solve some of the riddles for us but until that time i would really like to know how other companies manage their SDF databases.Is there not a single developer (maybe a MVP) that wants to shed some light on it and describe how he does it (or how it should be done)?
Thanks in advance,
Peter Vrenken|||I used to put an .mdb in vss. No reason why this couldn't be done with the .sdf|||Hello and thanks for your response!
I know that as of VS2K5 SP1 the management of .SDF files from within a solution has been greatly enhanced.
You say that you ‘used’ to put an .mdb in VSS. Is this because you found a better solution?
Peter Vrenken|||Peter, there are several questions here and I'll try to help where I can. As I understand your issues you're trying to build SDF databases in a way that can be better managed through developer tools like Visual Studio. At this point VS can help, but not as much as it could. The VS team is working on an updated version of the tools that can address some (but not nearly all) of your issues. The SQL Server Management Studio can also do more to help in this regard. As far as scripting, there is little to no support in any of the tools. I too felt your frustration so I wrote my first EBook to supplement my just completed Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). This is available at WWW.Hitchhikerguides.net. In the book I walk through the process of creating a database using a script reader that I wrote (and provide with the book), with replication and using the APIs. I expect it will help answer many more of your questions.|||"Used to" only in that I no longer use .mdb files.
I moved to MSDE where I kept the create scripts in vss.
I have migrated these to SQL Express (with the scipts in vss) but am now working on a new application with CE. Unfortunately the scripting that was created from SQL Server Management Tool does not work with CE so I am planning on using vss.
Best Possible Solution
Hi folks, guidance required!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.
Howdy!What do you mean downloaded from you website?|||Hi, sir.
An offline scheduler type application that could be downloaded from our website. All the data of the clients is at the db server running our website. Now for the first time when the log on to the app they could be offered the facility to download all their data locally from the db. I suspect there could b a better way around to give the data to each provider in an automated way rather than selecting the data for each client; creating a database for each client and shipping it manually with the software.
Howdy!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.
Howdy!What do you mean downloaded from you website?|||Hi, sir.
An offline scheduler type application that could be downloaded from our website. All the data of the clients is at the db server running our website. Now for the first time when the log on to the app they could be offered the facility to download all their data locally from the db. I suspect there could b a better way around to give the data to each provider in an automated way rather than selecting the data for each client; creating a database for each client and shipping it manually with the software.
Howdy!
Saturday, February 25, 2012
Best filtering solution for performance?
Hello,
i have a report with approximately 20 reportparameters from queries. This is really slowing down the system, although the dataload (rows sent back) is not that huge. I read on the internet that there are basicly three approaches.
1, Query parameter with alot of diffrent datasources
2, Table filtering
3, Stored procedures
I wonder which approach is the best and why it takes such a long time to get the report with the report parameters (before generating the report itself).
Thank you for your help!
I would think stored procedures would be the best approach. Have you tried using SQL profile to trace where the slowdown is?
cheers,
Andrew
|||Hello,
thank you for you fast reponse! I now changed to Stored Procedures, i thin in long term it is better to use SP. But now i Have problems with multivalued parameters, which will be sent as an array to SQL Server. I found some articles about this matter an will try to solve it. The performance problem seems to be solved although I don't know why. Will look into that later. Thanks fpr the profiler tip! Best regards!
Subscribe to:
Posts (Atom)