Could replication be the answer?

We have a US online business that is growing
internationally. We have a data model that allows us
to easily split countries to their own instance or own
physical server. A high volume of reads are expected
from our web servers so we feel that by spliting reads
and writes to separate servers, we could ease the
pressure on one server (i.e. have 1 SQL write server
where everything is written to; 1 or more SQL read
servers with a denormalized copy of the SQL write
data, populated via transactional replication). Our
database is quite large and could not afford the time
it takes to re-initialize the subscriber. Is there a
way to just replay specific transactions to the read
server? Also, we are thinking of maintaining a global
copy of the data (write data from all countries SQL
write servers). These servers would use
bi-directional queued updating transactional
replication. Not all data (vertically filtered) from
the individual country write servers will be
replicated to the global copy. If the copies get out
of sync, what is the best way to re-sync them? Again,
our databases are large enough that it would take
hours to reinitialize the data. Is there anyway to
just replay specific transactions to the subscribers?
Also, do you have any figures on the speed of
transactional replication in a high volume
environment, i.e. millions of transactions a day?
Is there a way to just replay specific transactions to the read server?
Once the publication has been created and subscribed to there's no official
way to filter them retrospectively. An unofficial way I have used is if I
already have a filter. The filter is implemented as a stored procedure and I
have edited the text of the stored procedure. This is not ideal as it isn't
reflected in the publication details, but as a temp fix it works ok.
If the copies get out of sync, what is the best way to re-sync them?
Firstly, I'm not sure I'd use bidirectional queued transactional
replication - I'd prefer to use merge in this situation. If there is
non-convergence the standard method is to reinitialize. Sometimes you can
manually converge the data then do a noinit initialization.
As for high-volume data, I can vouch for the use of TR to maintain a standby
server when there was 1000 transactions/minute (>1million per day) - we had
the latency down to 6 seconds. The only data I've seen on this is
http://www.microsoft.com/technet/pro.../tranrepl.mspx
HTH,
Paul Ibison
|||Define replay specific transactions to the read server.
There is a way to do this, but it depends on what you are trying to do. A
media company contacted me about something like this. Basically by adjusting
the a value in one of the replication metadata tabels using pure
transactional replication you can get all the transactions stored in the
distributor replays.
You can use sp_browsereplcmds to display a range or a transaction ID and
then replay this proc displayed by this commands on the subscriber.
When your data gets out of sync, you can run a validation to determine
exactly which tables are out of sync, and then decide whether to bcp/dts the
tables from the publisher to the subcriber to sync them up.
Or if your tables are too large you can do some comparison using a checksum
to determine exactly which rows are not in sync and then make a decision on
how to proceed.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ozzie" <Ozzie@.discussions.microsoft.com> wrote in message
news:BDC6BBB1-74DD-4010-9F25-F36C612C343A@.microsoft.com...
> We have a US online business that is growing
> internationally. We have a data model that allows us
> to easily split countries to their own instance or own
> physical server. A high volume of reads are expected
> from our web servers so we feel that by spliting reads
> and writes to separate servers, we could ease the
> pressure on one server (i.e. have 1 SQL write server
> where everything is written to; 1 or more SQL read
> servers with a denormalized copy of the SQL write
> data, populated via transactional replication). Our
> database is quite large and could not afford the time
> it takes to re-initialize the subscriber. Is there a
> way to just replay specific transactions to the read
> server? Also, we are thinking of maintaining a global
> copy of the data (write data from all countries SQL
> write servers). These servers would use
> bi-directional queued updating transactional
> replication. Not all data (vertically filtered) from
> the individual country write servers will be
> replicated to the global copy. If the copies get out
> of sync, what is the best way to re-sync them? Again,
> our databases are large enough that it would take
> hours to reinitialize the data. Is there anyway to
> just replay specific transactions to the subscribers?
> Also, do you have any figures on the speed of
> transactional replication in a high volume
> environment, i.e. millions of transactions a day?

Corrupted PDF attachments in Reporting Services

We use subscriptions to send a list of people the same report in PDF format.
Most are our own employees (using Microsoft Exchange/Outlook) and the report
is fine. We are, however, having problems sending the report to Yahoo, AOL
and Hotmail users. Here are some tests we've done...
Test #1: I have run the report and exported it as a PDF, opening it
directly into Adobe fine.
Test #2: I then exported it, saved it to my drive, then emailed the report
as a attachment using Outlook /Exchange to my Hotmail account and the report
opens fine.
Test #3: I set up a subscription to email it to simultaneously to both my
Hotmail and Exchange accounts. The report attachment that went to my
Exchange account opens fine in Adobe. The one that arrived attached to the
email to my Hotmail account arrived in a format that Adobe does not
recognize. ("... either not a supported file type or because the file has
been corrupted. For example, it was sent as an email attachment and wasn't
corrected decoded.")
The problem isn't my computer nor a problem with my Adobe Reader. It's not
my Exchange server that is delivering it to my Exchange account (it's the
same server). It's not my Outlook email with attachment sent by Exchange to
Hotmail.
Considering the common path that each of these tests followed and
determining which one didn't work, it appears to me that most likely the
Report Server is not submitting the report to Exchange as an attachment in a
way that services such as Hotmail, AOL, Yahoo, etc can work with or it's
interaction with my mail server when preparing it to send outside my mail
server.
Any help on this? Is this acknowledged by Microsoft?
JonThere is a known issue with a Unicode byte order mark being written out to
the attachment part of the email. This has been fixed in a QFE posted here:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
Please note, this fix fixes an attachment problem and not merely a problem
with LOTUS so don't be fooled by the title.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jon Limmer" <jlimmer@.gmbeverage.com> wrote in message
news:ug8%23kLH5EHA.1192@.tk2msftngp13.phx.gbl...
> We use subscriptions to send a list of people the same report in PDF
> format. Most are our own employees (using Microsoft Exchange/Outlook) and
> the report is fine. We are, however, having problems sending the report
> to Yahoo, AOL and Hotmail users. Here are some tests we've done...
> Test #1: I have run the report and exported it as a PDF, opening it
> directly into Adobe fine.
> Test #2: I then exported it, saved it to my drive, then emailed the
> report as a attachment using Outlook /Exchange to my Hotmail account and
> the report opens fine.
> Test #3: I set up a subscription to email it to simultaneously to both
> my Hotmail and Exchange accounts. The report attachment that went to my
> Exchange account opens fine in Adobe. The one that arrived attached to
> the email to my Hotmail account arrived in a format that Adobe does not
> recognize. ("... either not a supported file type or because the file has
> been corrupted. For example, it was sent as an email attachment and
> wasn't corrected decoded.")
> The problem isn't my computer nor a problem with my Adobe Reader. It's
> not my Exchange server that is delivering it to my Exchange account (it's
> the same server). It's not my Outlook email with attachment sent by
> Exchange to Hotmail.
> Considering the common path that each of these tests followed and
> determining which one didn't work, it appears to me that most likely the
> Report Server is not submitting the report to Exchange as an attachment in
> a way that services such as Hotmail, AOL, Yahoo, etc can work with or it's
> interaction with my mail server when preparing it to send outside my mail
> server.
> Any help on this? Is this acknowledged by Microsoft?
> Jon
>|||Thanks, Daniel.
I've downloaded, installed, and tested the package. I works great now!
I wish the package ended with a message that it was successful, though. It
just closes and doesn't leave me feeling that it even completed, much less
successfully.
The DLLs in the .NET Framework area (temporary ASP.NET Files) were updated
but it worries me that the copies in the Reporting Serices folders
(..\ReportManager & ..\ReportServer) are still showing the old SP1 versions.
When will these get updated to versions 8.0.892?
Jon|||The QFE only updates the files necessary for the fix. You should notice
that the ReportingServicesEmailDeliveryProvider.dll assembly was updated.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jon Limmer" <jlimmer@.gmbeverage.com> wrote in message
news:%23n3xu6H5EHA.2608@.TK2MSFTNGP10.phx.gbl...
> Thanks, Daniel.
> I've downloaded, installed, and tested the package. I works great now!
> I wish the package ended with a message that it was successful, though.
> It just closes and doesn't leave me feeling that it even completed, much
> less successfully.
> The DLLs in the .NET Framework area (temporary ASP.NET Files) were updated
> but it worries me that the copies in the Reporting Serices folders
> (..\ReportManager & ..\ReportServer) are still showing the old SP1
> versions. When will these get updated to versions 8.0.892?
> Jon
>|||Shouldn't I expect all instances of this dll to be updated?
I found that this .dll was updated in two places under the
C:\Windows\Microsoft.NET\Framework directory but not in the two places under
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\... The .dll
in the two \bin folders of both \ReportManager and \ReportServer still
reflected the older version, even after a reboot of the server.
Jon
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eQvUOJI5EHA.4092@.TK2MSFTNGP14.phx.gbl...
> The QFE only updates the files necessary for the fix. You should notice
> that the ReportingServicesEmailDeliveryProvider.dll assembly was updated.
> --
> -Daniel|||I'm a little confused. Where exactly do you see the .dlls? There should
not be any at C:\Windows\Microsoft.NET\Framework (buried a little deeper are
some copies of the .dlls that live in the bin folders)
After the reboot, is the problem still fixed? If it is then that means the
correct .dlls should be installed.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jon Limmer" <jlimmer@.gmbeverage.com> wrote in message
news:uQiGC5p5EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Shouldn't I expect all instances of this dll to be updated?
> I found that this .dll was updated in two places under the
> C:\Windows\Microsoft.NET\Framework directory but not in the two places
> under C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\...
> The .dll in the two \bin folders of both \ReportManager and \ReportServer
> still reflected the older version, even after a reboot of the server.
> Jon
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:eQvUOJI5EHA.4092@.TK2MSFTNGP14.phx.gbl...
>> The QFE only updates the files necessary for the fix. You should notice
>> that the ReportingServicesEmailDeliveryProvider.dll assembly was updated.
>> --
>> -Daniel
>|||I just took a closer look and don't think I still have a problem. Still,
there are a couple of oddities.
First, there really are four copies of these files on my system:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\Bin
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\Bin
> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
> Files\reports\5fc3e315\219a7509\assembly\dl2\1463834f\0052e96b_be6ac401
> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
> Files\reportserver\94eb9a55\49df36c9\assembly\dl2\3547c5bb\0052e96b_be6ac401
Second, the first two have a "date modified" of 07/15/04 and the last two
have dates of 12/17/04, the day I applied the patch. That was the confusing
part.
When I had initially looked at the file versions, the first two had older
file versions (ie, not the 8.0.892.0 that the new patch was to apply). When
I looked just now, however, (which is after a reboot), all the file versions
are 8.0.892.0, which is great.
Sorry to get us chasing what is apparently not an issue. I really
appreciate the quickness and accuracy of your replies.
Jon|||The last two files are the shadow copied files used by ASP.Net. By shadow
copying the files and using these files, this allows the original files to
be modified while the application is running. The modified dates are a
reflection of this copying.
I am not sure why you didn't see the new version info immediately after the
install. They should have been updated then.
Anyway, glad to hear that everything is up and running.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jon Limmer" <jlimmer@.gmbeverage.com> wrote in message
news:%23leMrLs5EHA.3120@.TK2MSFTNGP12.phx.gbl...
>I just took a closer look and don't think I still have a problem. Still,
>there are a couple of oddities.
> First, there really are four copies of these files on my system:
>> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\ReportManager\Bin
>> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\ReportServer\Bin
>> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
>> Files\reports\5fc3e315\219a7509\assembly\dl2\1463834f\0052e96b_be6ac401
>> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
>> Files\reportserver\94eb9a55\49df36c9\assembly\dl2\3547c5bb\0052e96b_be6ac401
> Second, the first two have a "date modified" of 07/15/04 and the last two
> have dates of 12/17/04, the day I applied the patch. That was the
> confusing part.
> When I had initially looked at the file versions, the first two had older
> file versions (ie, not the 8.0.892.0 that the new patch was to apply).
> When I looked just now, however, (which is after a reboot), all the file
> versions are 8.0.892.0, which is great.
> Sorry to get us chasing what is apparently not an issue. I really
> appreciate the quickness and accuracy of your replies.
> Jon
>