Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts

Friday, March 30, 2012

Help on transactional replication

Hi,
I am a newbie dba and relatively new to replication area.
I am in a process of setting up a transactional replication on sql 2000
for a reporting purpose to offload this activity on the production
server.
My basic goal is to enhance the performance on the production server. I
thought replicating to a reporting server would be an ideal choice
using transactional relication scheduled on an hourly basis.
following is the setup i am trying to achieve...
I am using Production server which is more like an OLTP server as a
publisher, Distributor is going to be on the same server as of
subscriber because of less resources. Moerever I was suggested that
running the Distributor & Pulling the subscription would result in
better performance for both the servers.
Now my questions are...
1. Am i going in a write direction to achieve my task?
2. what are the precautions one should take before making the
"production server" as publisher is transactional relication?
3. Do i need to increase the size of db's and logs on production
server?
4. How should we know that replication is causing stress on production
server?
5. what is the ideal size of database and the transaction log that is
going to hold the replicated data?
6. How to remove replcation from a database completely in case if it
fails and causing issues on productions?
Thanks very much all. I will really appretiate your tips and
suggestions.
AK
Firstly, the method you are following is fairly commonly used. The size of
the logs and databases depends entirely on your initial data size and
transaction volume, so as long as you have the logs set to a large size or
autogrow, you'll just have to monitor them for your circumstances. The
impact of replication on your system can be monitored using the normal
counters in windows system monitor (processor usage, ram usage and disk
usage - if you are not familiar with these I can dig out some links). To
prevent the use of the replication overhead in the event of a slowdown, you
could initially stop the log-reader agent then remove the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply paul,
I have left the database/log size to autogrow and will keep an eye on
their size.
also, you haven't answered clearly whether am i doing a right thing by
keeping the distributor on a subscriber server instead of keeping it
with publisher in production server?
Could you also please indicate what sort of implication the production
server will have with this kind of setup? I have to make it clear to my
manager before i setup this thing on live environment.
And the basic question, I would think the initial snapshot should be
done out of business hours? If yes, how often i need to take the
snapshot or just reading the transaction logs would be enough as long
as there are no schema changes on the published tables?
Will the snapshot agent gets the entire shcema and data from the
published tables every time it runs or only at once in the begining in
transactional replication?
and the last thing, If you please don't mind can you show me some links
on how to read and understand processor and memory usage things.
Thanks very much
AK.
Paul Ibison wrote:
> Firstly, the method you are following is fairly commonly used. The size of
> the logs and databases depends entirely on your initial data size and
> transaction volume, so as long as you have the logs set to a large size or
> autogrow, you'll just have to monitor them for your circumstances. The
> impact of replication on your system can be monitored using the normal
> counters in windows system monitor (processor usage, ram usage and disk
> usage - if you are not familiar with these I can dig out some links). To
> prevent the use of the replication overhead in the event of a slowdown, you
> could initially stop the log-reader agent then remove the publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||For a 1:1 system, putting the distributor on the subscriber box is a valid
choice. I prefer to have the distributor on the production box so i have
control over the backups centrally, and will therefore take the extra hit
there. Pull distribution agents or push will run on the subscriber in your
case. You could have the distributor on the production box and use a pull
subscriber to have the agent still run on the subscriber's box. However
these are not equivalent, and there will be a lot of disk read/write access
to the distribution database which generally contends with the production
database as it resides on the same disk array - hence this is why your
choice makes sense.
For the implications of the log-reader reading the transaction log and
writing the transactions to the distribution database - only you can
determine this for your setup. Best practices are to set up this in a lab
environment mimicing the production site.
Only run the snapshot agent ONCE and then disable it. You'll need to run it
manually for adding new articles and in the case of reinitialization
(hopefully never ).
For links, I'll dig some out for you...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
Thanks very much for your valuable tips. I really appreciate your time
on this.
Now one last thing i want to ask, once i have disabled the snapshot
agents, then how the log reader agent will read whenever there are any
transactions happend on publisher?
I want toschedule the job to read the transactions every hour. Shall i
schedule the log reader agent or the distribution agent to achieve this
task?
Many Thanks.
AK
Paul Ibison wrote:
> For a 1:1 system, putting the distributor on the subscriber box is a valid
> choice. I prefer to have the distributor on the production box so i have
> control over the backups centrally, and will therefore take the extra hit
> there. Pull distribution agents or push will run on the subscriber in your
> case. You could have the distributor on the production box and use a pull
> subscriber to have the agent still run on the subscriber's box. However
> these are not equivalent, and there will be a lot of disk read/write access
> to the distribution database which generally contends with the production
> database as it resides on the same disk array - hence this is why your
> choice makes sense.
> For the implications of the log-reader reading the transaction log and
> writing the transactions to the distribution database - only you can
> determine this for your setup. Best practices are to set up this in a lab
> environment mimicing the production site.
> Only run the snapshot agent ONCE and then disable it. You'll need to run it
> manually for adding new articles and in the case of reinitialization
> (hopefully never ).
> For links, I'll dig some out for you...
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log reader should run continuously and the distribution agent can be set
to run hourly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul,
Thanks very much for your help. I have successfully tested this setup
in test environment.It was spot on. I am hoping to implement this on
live very soon.
Thanks,
AK
Paul Ibison wrote:

> The log reader should run continuously and the distribution agent can be set
> to run hourly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Wednesday, March 28, 2012

Help on setting up Reporting Service...

I am new to Reporting Services. I set up the infrastruicture with two
computers to learn the Reporting Service, a stand alone server (No DNS and
AD and not a domain server) and a workstation, and both computers are in the
same workgroup. On the server computer I have a SQL 2000 SP4, mixed mode,
running on a Windows 2003 Server SP1. The second computer is installed with
the Reporting Service SP2 and is running on Windows XP Professional SP2 with
IIS enabled, also I have Visual Studio 2003 installed on this box to create
test reports. I can create and deploy reports using the Visual Studio 2003
and also view the report by typing the something like http://<IIS Server
Name>/ReportServer on the address bar in the Internet Explorer on the
workstation within the LAN. It opens the Report Manager UI and I can see all
the reports I deployed using the VS 2003. However if I try to access the
Report Manager from a remote location which I asked my friend try it from
his home computer by typing http://<My IIS public IP address>/ReportServer
and it did not work. I know that it is something to do with the security
setting with the IIS and the SQL 2000. How can I change the setting to allow
remote computer to access the reports directly or using the Report Manager
UI? Also how can I implment security to stop people access the reports or
Report Manager UI? Should I implement the security at the IIS server or to
the SQL 2000 or on individual database? Or should I implement the security
on both IIS and SQL 2000 and database? Thanks.I had a similar issue with the Firewall on XP SP2. You can add exclusions
to the firewall using the firewall administrator.
--
| From: "Paul" <paul_mak@.hotmail.com>
| Subject: Help on setting up Reporting Service...
| Date: Thu, 25 Aug 2005 10:38:13 -0700
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#UqJ3vZqFHA.272@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 64.114.25.49
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51138
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I am new to Reporting Services. I set up the infrastruicture with two
| computers to learn the Reporting Service, a stand alone server (No DNS
and
| AD and not a domain server) and a workstation, and both computers are in
the
| same workgroup. On the server computer I have a SQL 2000 SP4, mixed mode,
| running on a Windows 2003 Server SP1. The second computer is installed
with
| the Reporting Service SP2 and is running on Windows XP Professional SP2
with
| IIS enabled, also I have Visual Studio 2003 installed on this box to
create
| test reports. I can create and deploy reports using the Visual Studio
2003
| and also view the report by typing the something like http://<IIS Server
| Name>/ReportServer on the address bar in the Internet Explorer on the
| workstation within the LAN. It opens the Report Manager UI and I can see
all
| the reports I deployed using the VS 2003. However if I try to access the
| Report Manager from a remote location which I asked my friend try it from
| his home computer by typing http://<My IIS public IP
address>/ReportServer
| and it did not work. I know that it is something to do with the security
| setting with the IIS and the SQL 2000. How can I change the setting to
allow
| remote computer to access the reports directly or using the Report
Manager
| UI? Also how can I implment security to stop people access the reports or
| Report Manager UI? Should I implement the security at the IIS server or
to
| the SQL 2000 or on individual database? Or should I implement the
security
| on both IIS and SQL 2000 and database? Thanks.
|
|
|

Help on setting up connection string

I'm using VWD on SQL2005EXPRESS And i keep getting a connection string error

Can someone show me how to put a connection string in the web.config, or which connection string to use to connect to my host database which they're running SQL2000..

Thanks Newbie

Seehttp://www.connectionstrings.com.|||

I did went to that site, and i have try some their method and it didn't work. i still get a error

I'd like to see what the web.config file suppose to look like with the connection string written in it

|||<?xml version="1.0" ?>
<configuration>
<connectionStrings>
<add name="MyConnectionString" connectionString="server=(local)\SQLSERVEREXPRESS_INSTANCENAME;database=MyDatabase;uid=myusername;password=mypassword" />
</connectionStrings>
</configuration>|||

This is my web.config file.Could you please tell me what is the problem with it.

Thanks Newbie

<?xmlversion="1.0"?>

<configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<system.web>

<customErrorsmode="ON"/>

<compilationdebug="true" />

<tracelocalOnly="false"mostRecent="true"pageOutput="true" />

<roleManagerenabled="true"/>

<authenticationmode="Forms"/>

</system.web>

</configuration>

|||If you compare it to mine, you'll notice you're missing the connectionStrings element as well as a connection string.|||

Ok" i have seen the different. But how do i know which elemet and connection string to use . I have seen some many different connection string and element . So how do one detemine which one to use for their website and database.

I'm using SQL 05 EXPRESS and my host is using SQL 2000, so how would i know which string and element to use.? I' m new to this ,so some of the terms and language i dont understand.

|||The connection string I used in an example above will work for SQL Server Express. When using SQL Server Express, you have to name the instance (i.e. (local)\SQLEXPRESS). With SQL Server 2000, you should be fine without naming the instance as long as there are not multiple instances of SQL Server on the box (i.e. (local)).|||

One more thing. I did attemp to do scott configure on changing SQL express to SQL2000 or 2005 and i did settup up vwd to appservicedb.

Is there a way, to see if i'm running SQL express or SQL server 2005 express?

O, k if i'm running SQL EXPRESS i could use the string above and it would work?

Thanks you

|||

Just have question.How do i determine what my instance is?

And in your connection string i 'd put my database name where it stated my database, and for my (local)\SQLEXPRESS). INSTANCE how do i know what to put for the instance?

Wednesday, March 21, 2012

Help needed: Granting Create table permisions on specific Schema Options


Having some trouble getting my head around setting access to specific
schemas- here's my problem:

I've created a specific schema that I only want certain users to
control

Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):

GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'gdev (paul.afamdi.okeke@.gmail.com) writes:

Quote:

Originally Posted by

Having some trouble getting my head around setting access to specific
schemas- here's my problem:
>
I've created a specific schema that I only want certain users to
control
>
>
Problem: Even though I give them full access...the cannot create
tables under that schema...my code is below (flyer is the schema,
eflyerAdmin is the role, and eflyer is the user):
>
GRANT
ALTER,
CONTROL,
DELETE,
EXECUTE,
INSERT,
REFERENCES,
SELECT,
TAKE OWNERSHIP,
UPDATE,
VIEW DEFINITION
ON SCHEMA::flyer
TO eflyerAdmin
GO
-- Add an existing user to the role
EXEC sp_addrolemember N'eflyerAdmin', N'eflyer'


You also need:

GRANT CREATE TABLE TO eflyerAdmin

And it's sufficient to grant CONTROL on the schema, since CONTROL implies
the rest.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 12, 2012

Help Needed on Hardware Setup

I have been tasked with setting up a brand new PowerEdge 2650 for
SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
My question is in regards to the best possible RAID setup for SQL
performance as well as retaining as much disk space as possible.
If I read the docs correctly SQL is best suited to have the tempdb,
application files and log files all on separate physical disks.
I cannot find a way to do this without losing a lot of my available disk
space.
The way I see it, as well as my SQL admin, there are 3 factors to consider.
1. Speed (performance), 2. Reliability 3. Capacity.
If I configure everything in one big RAID 5 array I would have excellent
reliability, and have (73x4) 292GB free for data, probably 270GB after
application installations.
If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
and 5 for data I would have 219GB available for data with no redundancy, if
I add RAID 5 across those 3 disks I would get 146GB useable space,
dramatically less than the 292GB from the other config.
A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
problem with this is that I can only use 63GB of each disk, giving me 315GB
minus parity drive, for a total useable size of 252GB. However, I don't
think this configuration would give me much of a performance gain in SQL.
So my questions are this : what would you recommend, taking into account
that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
server will have about 50GB of local data *eventually*, and will be indexing
around 150GB of data residing on a NAS device.
Also, I have heard that SharePoint doesn't really tax SQL that bad and that
I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
done with it. Thought?
Sorry for the long post.Hi,
Follow the below URL which gives idea on What RAID Levels
to choose for SQL Boxes and why so. Of Course More replies
to come from experts.
http://www.sql-server-performance.com/q&a38.asp
HTH
THIRUMAL REDDY MARAM
System Admin / SQL DBA
>--Original Message--
>I have been tasked with setting up a brand new PowerEdge
2650 for
>SharePoint, SQL 2000 and Windows 2003. This server has 5
physical disks,
>all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
>My question is in regards to the best possible RAID setup
for SQL
>performance as well as retaining as much disk space as
possible.
>If I read the docs correctly SQL is best suited to have
the tempdb,
>application files and log files all on separate physical
disks.
>I cannot find a way to do this without losing a lot of my
available disk
>space.
>The way I see it, as well as my SQL admin, there are 3
factors to consider.
>1. Speed (performance), 2. Reliability 3. Capacity.
>If I configure everything in one big RAID 5 array I would
have excellent
>reliability, and have (73x4) 292GB free for data,
probably 270GB after
>application installations.
>If I configure disk 1 for OS and apps, disk 2 for log
files and disks 3,4
>and 5 for data I would have 219GB available for data with
no redundancy, if
>I add RAID 5 across those 3 disks I would get 146GB
useable space,
>dramatically less than the 292GB from the other config.
>A third option is to create a 10GB (or 20GB) partition on
disk 1, mirror
>that to disk 2 and configure all 5 disks in a RAID 5
configuration. The
>problem with this is that I can only use 63GB of each
disk, giving me 315GB
>minus parity drive, for a total useable size of 252GB.
However, I don't
>think this configuration would give me much of a
performance gain in SQL.
>So my questions are this : what would you recommend,
taking into account
>that this machine will be running W2k3, SQL 2000 and
SharePoint 2003. The
>server will have about 50GB of local data *eventually*,
and will be indexing
>around 150GB of data residing on a NAS device.
>Also, I have heard that SharePoint doesn't really tax SQL
that bad and that
>I'm over-analyzing the problem. Just set it up as one
big RAID 5 and be
>done with it. Thought?
>Sorry for the long post.
>
>.
>|||Every case is unique per the Budget , Scope, SLAs etc... to come up with teh
right configuration.
It also depends upon the Access Patterns, Number of Data files and groups,
Index and TempDB Placement.
I would recommend that you start at the MSFT Operations guide at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
And look at Chapter 6, which talks about Capacity Planning.
--
HTH
Satish Balusa
Corillian Corp.
"cyberpunk" <blah@.anon.com> wrote in message
news:eONEAKr3DHA.1404@.TK2MSFTNGP11.phx.gbl...
> I have been tasked with setting up a brand new PowerEdge 2650 for
> SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
> all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
> My question is in regards to the best possible RAID setup for SQL
> performance as well as retaining as much disk space as possible.
> If I read the docs correctly SQL is best suited to have the tempdb,
> application files and log files all on separate physical disks.
> I cannot find a way to do this without losing a lot of my available disk
> space.
> The way I see it, as well as my SQL admin, there are 3 factors to
consider.
> 1. Speed (performance), 2. Reliability 3. Capacity.
> If I configure everything in one big RAID 5 array I would have excellent
> reliability, and have (73x4) 292GB free for data, probably 270GB after
> application installations.
> If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
> and 5 for data I would have 219GB available for data with no redundancy,
if
> I add RAID 5 across those 3 disks I would get 146GB useable space,
> dramatically less than the 292GB from the other config.
> A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
> that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
> problem with this is that I can only use 63GB of each disk, giving me
315GB
> minus parity drive, for a total useable size of 252GB. However, I don't
> think this configuration would give me much of a performance gain in SQL.
> So my questions are this : what would you recommend, taking into account
> that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
> server will have about 50GB of local data *eventually*, and will be
indexing
> around 150GB of data residing on a NAS device.
> Also, I have heard that SharePoint doesn't really tax SQL that bad and
that
> I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
> done with it. Thought?
> Sorry for the long post.
>

Help Needed on Hardware Setup

I have been tasked with setting up a brand new PowerEdge 2650 for
SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
My question is in regards to the best possible RAID setup for SQL
performance as well as retaining as much disk space as possible.
If I read the docs correctly SQL is best suited to have the tempdb,
application files and log files all on separate physical disks.
I cannot find a way to do this without losing a lot of my available disk
space.
The way I see it, as well as my SQL admin, there are 3 factors to consider.
1. Speed (performance), 2. Reliability 3. Capacity.
If I configure everything in one big RAID 5 array I would have excellent
reliability, and have (73x4) 292GB free for data, probably 270GB after
application installations.
If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
and 5 for data I would have 219GB available for data with no redundancy, if
I add RAID 5 across those 3 disks I would get 146GB useable space,
dramatically less than the 292GB from the other config.
A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
problem with this is that I can only use 63GB of each disk, giving me 315GB
minus parity drive, for a total useable size of 252GB. However, I don't
think this configuration would give me much of a performance gain in SQL.
So my questions are this : what would you recommend, taking into account
that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
server will have about 50GB of local data *eventually*, and will be indexing
around 150GB of data residing on a NAS device.
Also, I have heard that SharePoint doesn't really tax SQL that bad and that
I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
done with it. Thought?
Sorry for the long post.Every case is unique per the Budget , Scope, SLAs etc... to come up with teh
right configuration.
It also depends upon the Access Patterns, Number of Data files and groups,
Index and TempDB Placement.
I would recommend that you start at the MSFT Operations guide at
http://www.microsoft.com/technet/tr...ide/default.asp
And look at Chapter 6, which talks about Capacity Planning.
HTH
Satish Balusa
Corillian Corp.
"cyberpunk" <blah@.anon.com> wrote in message
news:eONEAKr3DHA.1404@.TK2MSFTNGP11.phx.gbl...
quote:

> I have been tasked with setting up a brand new PowerEdge 2650 for
> SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
> all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
> My question is in regards to the best possible RAID setup for SQL
> performance as well as retaining as much disk space as possible.
> If I read the docs correctly SQL is best suited to have the tempdb,
> application files and log files all on separate physical disks.
> I cannot find a way to do this without losing a lot of my available disk
> space.
> The way I see it, as well as my SQL admin, there are 3 factors to

consider.
quote:

> 1. Speed (performance), 2. Reliability 3. Capacity.
> If I configure everything in one big RAID 5 array I would have excellent
> reliability, and have (73x4) 292GB free for data, probably 270GB after
> application installations.
> If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
> and 5 for data I would have 219GB available for data with no redundancy,

if
quote:

> I add RAID 5 across those 3 disks I would get 146GB useable space,
> dramatically less than the 292GB from the other config.
> A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
> that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
> problem with this is that I can only use 63GB of each disk, giving me

315GB
quote:

> minus parity drive, for a total useable size of 252GB. However, I don't
> think this configuration would give me much of a performance gain in SQL.
> So my questions are this : what would you recommend, taking into account
> that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
> server will have about 50GB of local data *eventually*, and will be

indexing
quote:

> around 150GB of data residing on a NAS device.
> Also, I have heard that SharePoint doesn't really tax SQL that bad and

that
quote:

> I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
> done with it. Thought?
> Sorry for the long post.
>